SDSU CS 596: Client-Server Programming
Spring Semester, 1997
Doc 28 Databases

To Lecture Notes Index
San Diego State University -- This page last updated Apr 29, 1997

Contents of Doc 28 Databases

Databases
Database slide # 1
...Fixed vs. Variable length records slide # 4
...Indexing slide # 7
...Roll your own vs. using existing slide # 8
...Concurrency slide # 9
Interacting with a database - SQL slide # 12
...SQL: CREATE slide # 13
...SQL: DROP slide # 14
...SQL: INSERT slide # 15
...SQL: DELETE slide # 16
...SQL: SELECT slide # 17
...SQL: UPDATE slide # 20


Doc 28 Databases Slide # 1

Database

Why databases in a client-server course?

95% or more of all servers will use some sort of database.

Examples we have looked at so far:

Services which do not use a database include:

Jargon Watch - 3-tier






Doc 28 Databases Slide # 2
What is a database?

Andrew's definitions:

A database holds information and provides for a mechanism to access this information.

Examples of some common (electronic) databases:



Doc 28 Databases Slide # 3
Database terms

Table:

A collection of records

Record:

A collection of fields which belong together

Field:

A piece of data (datum)


Example record:

Lastname   Firstname  MI  ID  Phone Email

Example table of these records:


Scherpbier
AndrewW4245026none
OverBenD57411a@b

Doc 28 Databases Slide # 4

Fixed vs. Variable length records

Records can be fixed-length or variable length.

Variable length records:

Examples:


Doc 28 Databases Slide # 5

Variable length records


Advantages:


Disadvantages:


Doc 28 Databases Slide # 6

Fixed length records

Advantages:

Disadvantages:

Example of wasted space:

PC with 1.2GB disk:

512 bytes per sector
64 sectors per cluster
minimum of one cluster per file.


Result: A 10 byte file takes up 32K on disk.


Doc 28 Databases Slide # 7

Indexing

Once we store data, we need a way to get at the data.

Indexing provides a method to find records.

An index maps keys to records

Keys can be:

Keys are normally stored in a separate data structure.
Some common data structures used:

Multiple indexes can be created on a single table


Doc 28 Databases Slide # 8

Indexing issues

Issues to consider when using indexes:

The decision of what to use depends on what needs to be accomplished and what resources are available.

(Some data structures do not (easily) allow for duplicate keys.)



Roll your own vs. using existing

There are many database management systems available.

Most of them commercial but others are free or come with the OS.

In many cases it is quite possible to design and implement your own database.

For a concurrent server, you have to worry about concurrency.


Doc 28 Databases Slide # 9

Concurrency

Goal:

The table and its indexes must always be in sync.

Some solutions:


Other problems:


Doc 28 Databases Slide # 10

Existing database systems


We will look at several databases. Each has its own advantages and disadvantages.

Oracle:

ndbm:

mSQL:


Doc 28 Databases Slide # 11
Jargon Watch - Database

Sometimes database means a program for managing data

Oracle Corporation is a database company.
MS Access is database.

Sometimes database means a collection of data

I keep a database of my CD collection on 3 by 5 cards

Sometimes database means a set of tables, indexes, and views

My program needs to connect to the Airline Reservation database, which uses Oracle
Doc 28 Databases Slide # 12

Interacting with a database - SQL

Structure Query Language (SQL) is the standard language for handling data in a relational database

Database programs tend to extend SQL to produce an "improved" version

Oracle has sqlplus, mSQL has msql

Data Manipulations
SELECTRetrieves data from table(s)
INSERTAdds row(s) to a table
UPDATEChanges field(s) in record(s)
DELETERemoves row(s) from a table
Data Definition
CREATE TABLEDefine a table and its columns(fields)
DROP TABLEDeletes a table
ALTER TABLEAdds a new column, add/drop primary key
CREATE INDEXCreate an index
DROP INDEXDeletes an index
CREATE VIEWDefine a logical table from other table(s)/view(s)
DROP VIEWDeletes a view

Doc 28 Databases Slide # 13

SQL: CREATE

The create clause is used to create a table.

CREATE TABLE table_name (
    col_name    col_type [ NOT NULL | PRIMARY KEY]
    [, col_name col_type [ NOT NULL | PRIMARY KEY]]*
)

Example:

CREATE TABLE students
(
     firstname  CHAR(20) NOT NULL,
     lastname   CHAR(20),
     phone      CHAR(10),
     code       INTEGER
)
CREATE TABLE codes
(
     code       INTEGER,
     name       CHAR(20)
)

firstnamelastnamephonecode

codename

Doc 28 Databases Slide # 14

SQL: DROP

Drop is used to remove a table definition from the database.

DROP TABLE table_name

Example:
DROP TABLE students



Doc 28 Databases Slide # 15

SQL: INSERT

Putting data into a table is done with the INSERT clause:

INSERT INTO table_name [(column [, column ]*)]
VALUES (value [, value]*)

example:
INSERT INTO students VALUES (`John', `Smith', `555-9876', 1516)

INSERT INTO students VALUES (`Ben', `Over', `555-1212', 9500)

INSERT INTO codes VALUES (1516, `Good guy')

INSERT INTO codes VALUES (9500, `Bad guy')


firstnamelastnamephonecode
JohnSmith555-98761516
BenOver555-12129500

codename
1516Good guy
9500Bad guy

Doc 28 Databases Slide # 16

SQL: DELETE

Records can be deleted from a table using the DELETE clause. Note that we need to specify which record or records need to be deleted:

DELETE FROM table_name
WHERE column OPERATOR value
    [ AND | OR column OPERATOR value ]*

OPERATOR can be <, >, =, <=, >=, <>, or LIKE

Example:

DELETE FROM students WHERE firstname = `Ben'

firstnamelastnamephonecode
JohnSmith555-98761516

Doc 28 Databases Slide # 17

SQL: SELECT

Accessing data with SQL is done using a query.

A query always starts with SELECT:

SELECT [table.]column [, [table.]column]*
FROM table [=alias][, table [= alias]]*
[ WHERE [table.]column OPERATOR VALUE
    [ AND | OR [table.]column OPERATOR VALUE]*]
[ ORDER BY [table.]column [DESC][, [table.]column [DESC]]

OPERATOR can be <, >, =, <=, >=, <>, or LIKE
VALUE can be a literal value or a column name

The expression syntax supported by the LIKE clause can have the following special characters:


Doc 28 Databases Slide # 18

SQL: SELECT examples

Simple example:

SELECT firstname, lastname FROM students

will produce the following :

firstnamelastname
JohnSmith
BenOver

Queries can be more complex by adding keywords:

SELECT * FROM students WHERE code < 5000


firstname
lastname
phone
code
John
Smith
555-9876
1516


Doc 28 Databases Slide # 19

SQL: SELECT examples (cont.)

Relations can be made between tables: SELECT students.firstname, codes.name FROM students, codes WHERE students.code = codes.code

firstname
name
JohnGood guy
BenBad guy


Doc 28 Databases Slide # 20

SQL: UPDATE

Modifying a record is done with the UPDATE clause:

UPDATE table_name SET column=value
    [,column=value]*
WHERE column OPERATOR value
    [ AND | OR column OPERATOR value]*

OPERATOR can be <, >, =, <=, >=, <>, or LIKE
UPDATE students SET firstname=`Johnny' WHERE code=1516

firstnamelastnamephonecode
JohnnySmith555-98761516
BenOver555-12129500