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:

POP -- Mail files
HTTP -- Directories and files
FTP -- Directories and files

Services which do not use a database include:

Time of day service
Computational services

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:

Unix password file
IRS records system
Rolodex(TM)
Computer file system
Library (object files)
Student grades
Telephone directory

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	Andrew	W	42	45026	none
Over	Ben	D	57	411	a@b

Doc 28 Databases Slide # 4

Fixed vs. Variable length records

Records can be fixed-length or variable length.

Variable length records:

Number of fields is variable, or
Fields themselves are variable length.

Examples:

/etc/group has a variable number of usernames with each group
/etc/passwd has variable length fields

Doc 28 Databases Slide # 5

Variable length records

Advantages:

Use only as much storage as is needed
Can accomodate unusual data not originally planned
Most natural way for humans.

Disadvantages:

Difficult to insert or delete
Hard to search through
Cannot simply map a structure to a record.
Fragmentation

Doc 28 Databases Slide # 6

Fixed length records

Advantages:

Easy allocation/deallocation
Easy searching
Easy management

Disadvantages:

Not flexible
Hard to change field lengths (change structure)
Waste space in fields if the data in the fields is shorter than the field length

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:

One or more fields of a record
Independent from a record

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

Sorted for binary search
Tree (btree or some other efficient tree structure)
Hash table

Multiple indexes can be created on a single table

Doc 28 Databases Slide # 8

Indexing issues

Issues to consider when using indexes:

Speed/cost of update of the table and index(es)
Duplicate keys

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:

Record locking (complex)
Table locking (easier...)
Access serialization (frequently not possible)
Allow only one "updater" per table (One writer, Many readers)

Existing database systems

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

Oracle
ndbm
mSQL (Mini SQL)

Oracle:

Professional database system
Expensive
Uses SQL (Structured Query Language)

ndbm:

Standard Unix library
Only hashed indexing
Simple to use from C, C++, Perl, etc.
No direct java interface

mSQL:

Free (for education) database system
Uses SQL
Has java interface (MsqlJava)

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
SELECT	Retrieves data from table(s)
INSERT	Adds row(s) to a table
UPDATE	Changes field(s) in record(s)
DELETE	Removes row(s) from a table
Data Definition
CREATE TABLE	Define a table and its columns(fields)
DROP TABLE	Deletes a table
ALTER TABLE	Adds a new column, add/drop primary key
CREATE INDEX	Create an index
DROP INDEX	Deletes an index
CREATE VIEW	Define a logical table from other table(s)/view(s)
DROP VIEW	Deletes 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)
)

firstname

lastname

phone

code

name

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')

firstname	lastname	phone	code
John	Smith	555-9876	1516
Ben	Over	555-1212	9500

code	name
1516	Good guy
9500	Bad 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'

firstname	lastname	phone	code
John	Smith	555-9876	1516

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:

`_' matches any single character
`%' matches 0 or more characters of any value
`\' escapes special characters (e.g. `\%' matches % and `\\' matches \ )
all other characters match themselves

Doc 28 Databases Slide # 18

SQL: SELECT examples

Simple example:

SELECT firstname, lastname FROM students

will produce the following :

firstname	lastname
John	Smith
Ben	Over

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
John	Good guy
Ben	Bad 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

firstname	lastname	phone	code
Johnny	Smith	555-9876	1516
Ben	Over	555-1212	9500

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

Contents of Doc 28 Databases

Database

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