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 ```

• Step 1: Find all keys of the relation

 ``` 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:

 Check if X is a super key Check if Y is a key attribute

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 ?

 Yes,      OK, no violation     Done

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

 No....

• Is E or F a key attribute ?

 No

• 3NF violation:

 D → E, F

Decompose:

• Extraction relation:

 D+ = DEF

• 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:

 R1 is in 3NF

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:

 R2 is in 3NF

• 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 ```