CS457 Syllabus & Progress

Designing a database using decomposition to the 3NF

Designing a database

At the start of the course, we have learn the Entity-Relationship modelling technique to design a database

Fact:

The decomposition algorithm into relations that satisfy the 3NF criteria can be used to design a database !!!

Furthermore:

The decomposition algorithm will only use the given set of functional dependencis
The decomposition algorithm does not depend on the meaning of attributes

Example of designing a database using decomposition into 3NF

Consider the following universal relation:

R = (SSN, FName, LName, DNumber, DName, MgrSSN) Functional Dependencies: SSN → FName, LName, DNumber DNumber → DName, MgrSSN

To show you that the decomposition algorithm does not depend on the meaning of the attributes, we will replace the attribute names with meaningless letters:

R = (SSN, FName, LName, DNumber, DName, MgrSSN) SSN → FName, LName, DNumber DNumber → DName, MgrSSN Replace: SSN ==> A FName ==> B LName ==> C DNumber ==> D DName ==> E MgrSSN ==> F
Re-written as: R = (A, B, C, D, E, F) Functional dependencies: A → B, C, D D → E, F

Example designing a (small) database using decomposition to 3NF

Problem description:

Given a database consisting of the universal relation R:

R = (A, B, C, D, E, F)

and a set of functional dependencies on attributes in R:

A → B, C, D D → E, F

Find a set of relations that satisfy the 3NF

Step 1: Find all keys of the relation R

Necessary attribute in a key: A Check sufficiency: A⁺ = A = A B C D (Because A → B, C, D) = A B C D E F (Because D → E, F) Sufficient !

Therefore, the key of the relation is:

A

Recall: how to Check for violation of the 3NF criteria:

For every functional dependency X → Y:
If the answer to both questions is no, then the 3NF condition has been violated

Step 2: check the functional dependency A → B, C, D

Information to make the 3NF decision:

R = (A, B, C, D, E, F) Key(s): (A)
Checking: A → B, C, D

Is A a super key ?

Consider next functional dependency

Step 3: check D → E, F

Information to make the 3NF decision:

R = (A, B, C, D, E, F) D → E, F Key(s): (A)

Is D a super key
Is E or F a key attribute ?
3NF violation:

Decompose:

Extraction relation:

Decompose R into:

R = (A, B, C, D, E, F)
R1 = (D, E, F) Keys(R1) = { (D) } R2 = (A, B, C, D) Keys(R2) = { (A) } Lossless guarantee: R1 ∩ R2 = (D) → R1

Step 4: check if R1 and R2 is in 3NF

Check R1

Information for 3NF decision:

R1 = (D, E, F) A → B, C, D D → E, F Key(s) of R1: (D)

3NF decision:

Check R2

Information for 3NF decision:

R2 = (A, B, C, D) A → B, C, D D → E, F Key(s) of R2: (A)

3NF decision:

DONE

Replace back the orginal attribute names:

A = SSN D = DNumber B = FName E = DName C = LName F = MgrSSN A -> B, C, D, E, F D -> E, F
Result: (SSN, FName, LName, DNumber, DName, MgrSSN) Decomposed into: (SSN, FName, LName, DNumber) (DNumber, DName, MgrSSN)

Note:

We have a well designed database:

(SSN, FName, LName, DNumber) represents information on Employees (DNumber, DName, MgrSSN) represents information on Departments