Normalization

Seite 1

Table Zero

S1 SMITH MIS C1, C2, C3, C4, C5, C6

S2 DOE MGMT C2, c4

S3 LOPEZ MIS C3

Table Zero is bad, we fix it by making it atomic.

Tables 1

Ex:

S1 SMITH MIS C1

S1 SMITH MIS C2

.

.

S2 DOE MGMT C2

.

.

S4 LOPEZ MIS C3

Problem with Table One, Smith is Duplicated, and if you delete Lopez' C3, you delete Lopez all together.

CREATE VIEW ONE AS

SELECT A.SN,SNAME, MAJOR,

FROM A INNER JOIN B

ON A.SN=B.SN;

Functional dependency - if you have an sn, you can determine major. If you have a major, you can' determine sn because of duplicates

2nd Normal Form Table - if all non-key attributes are functionally dependent on the primary key.

TABLE 2

S# SNAME CITY STATE ZIP

Primary Key: Sn

Sn - sname, city, state, zip are all determine by sn

ZIP - determines city and state

Non key attributes: sname, city, state, zip

Solution make two tables:

C

SN sname zip

D

zip city state

3rd Normal Form Tables - all non key attributes are non-transitively functionally dependent on the primary key

Non transitively - there is no direct path

Tables 3

sn ssn cn date

Candidate keys: sn+cn, ssn

Non-key attributes: date

Sn and SSN point to Date

Sn and SSN point to each other. A change in either will cause problems

Solution:

sn ssn sn cn date

This is called the Boyce Codd Normal Form

A table is BCNF if all FDs start from a key