CS 580 Client-Server Programming Fall Semester, 2002 SQL & Normalization
	Previous    Lecture Notes Index    Next     © 2002, All Rights Reserved, SDSU & Roger Whitney San Diego State University -- This page last updated 19-Nov-02

Some Data Modeling
An Example

Primary Key
Indices
Adding Values

Result

Office_Hours adding
Getting Office Hours

Normalization

First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Other Normal Forms

Doc 22, SQL & Normalization Slide # 2

Some Data Modeling

A distinct class of things about which something is known

Particular instance of an entity class

In a database entity occurrences are records in a table

An abstraction belonging to or characteristic of an entity

An attribute (or set of attributes) that uniquely define an entity

An abstraction belonging to or characteristic of two entities or parts together

A unique identifier in a record representing another record

Doc 22, SQL & Normalization Slide # 3

Attributes (Color, make, model, serial number)

Primary key (serial number)

Car must have one and only one owner

Person may own zero, one or more cars

Person can own many cars

Doc 22, SQL & Normalization Slide # 4

An Example

Primary Key

CREATE TABLE faculty (
   name  CHAR(20) NOT NULL,
   faculty_id  SERIAL   PRIMARY KEY
);

NOTICE:  CREATE TABLE will create implicit sequence 'faculty_faculty_id_seq' for SERIAL column 'faculty.faculty_id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'faculty_pkey' for table 'faculty'
CREATE
Doc 22, SQL & Normalization Slide # 5

Indices

• B-Tree (default)
• R-Tree
• Hash
• GIST – Generalized Index Search Tree

CREATE INDEX faculty_name_key on faculty (name);

Doc 22, SQL & Normalization Slide # 6

Adding Values

INSERT INTO  faculty VALUES    ('Whitney');
INSERT INTO  faculty VALUES    ('Beck');
INSERT INTO  faculty VALUES    ('Anantha');

INSERT INTO faculty ( name) VALUES ('Vinge');
select * from faculty;

Result

         name         | faculty_id 
----------------------+-------------
 Whitney              |           1
 Beck                 |           2
 Anantha              |           3
 Vinge                |           4
(4 rows)

Doc 22, SQL & Normalization Slide # 7

CREATE TABLE office_hours (
    start_time    TIME NOT NULL,
    end_time    TIME NOT NULL,
    day    CHAR(3) NOT  NULL,
    faculty_id INTEGER REFERENCES faculty,
    office_hour_id    SERIAL    PRIMARY KEY
);

Doc 22, SQL & Normalization Slide # 8

Office_Hours adding

INSERT  
    INTO office_hours 
    VALUES ( '10:00:00',  '11:00:00' ,  'Wed',  1 );

INSERT INTO 
   office_hours  (start_time, end_time, day, faculty_id ) 
SELECT 
   '8:00:00' AS start_time, 
   '12:00:00' AS end_time, 
   'Mon' AS day, 
   faculty_id AS faculty_id 
FROM 
   faculty 
WHERE 
   name = 'Beck';

Doc 22, SQL & Normalization Slide # 9

Getting Office Hours

SELECT 
    name, start_time, end_time, day 
FROM 
    office_hours, faculty
WHERE
    faculty.faculty_id = office_hours.faculty_id

Doc 22, SQL & Normalization Slide # 10

SELECT 
    name AS Instructor,
    TEXT(start_time) || ' to ' || TEXT(end_time) AS Time, 
    day AS Day 
FROM 
    office_hours, faculty
WHERE
    faculty.faculty_id = office_hours.faculty_id
ORDER BY
    Name;

Doc 22, SQL & Normalization Slide # 11

SELECT 
    name, start_time, end_time, day 
FROM 
    office_hours, faculty
WHERE
    faculty.faculty_id = office_hours.faculty_id
 AND
    start_time > '09:00:00'
 AND
    end_time < '16:30:00'
ORDER BY
    Name;

Doc 22, SQL & Normalization Slide # 12

Normalization

Reduce redundant data and inconsistencies

First Normal Form (1NF)

Doc 22, SQL & Normalization Slide # 13

Doc 22, SQL & Normalization Slide # 14

Second Normal Form (2NF)

• It is in 1NF and
• All non-key attributes must be fully dependent on the entire primary key

• Can have the same artists
• Can have same music type

Doc 22, SQL & Normalization Slide # 15

Doc 22, SQL & Normalization Slide # 16

Doc 22, SQL & Normalization Slide # 17

Doc 22, SQL & Normalization Slide # 18

Third Normal Form (3NF)

• It is in 2NF and
• All non-key attributes must only be dependent on the primary key

Doc 22, SQL & Normalization Slide # 19

Other Normal Forms

• Boyce-Codd normal form (BCNF)
• Fourth normal form (4NF)
• Fifth normal form (5NF)

Previous    visitors since 19-Nov-02    Next