CS457 Syllabus & Progress

## The Second Normal Form (2NF) - continued

• Summary....

• Recall that we were in the process of decomposing "Employee1" into 2NF:

 ``` Employee1(SSN, Fname, LName, PNumber, PName, Hours) ```

when we made a deviation to discuss lossless decomposition ....

• We have covered a guideline that guarantees that a decomposition of a relation R into the relations R1 and R2 is lossless:

Let R1 and R2 be a decomposition of R

If either R1 ∩ R2 → R1 or R1 ∩ R2 → R2, then:

 content(R) = content(R1) * content(R2)

decompose relations losslessly into normal forms.

• Review: Second Normal Form (2NF):

• A relation is in 2NF iff:

• Relation is in 1NF (i.e., every attribute is atomic), and

• every non-key attribute is fully functionally determined by every key of the relation

In other words:

 There must not be any non-key attribute in the relation that is functionally determined by only a subset of a key

• Decomposition of Employee1 into 2NF

• Relation:

 ``` Employee1(SSN, Fname, LName, PNumber, PName, Hours) Key(s): (SSN, PNumber) - Employee1 is not in 2NF ```

• 2NF violation:

 ``` Non-key attribute: LName Key subset: SSN SSN → LName ```

• Decompose to remove 2NF violation:

 ``` Original relation: R = Employee1(SSN, FName, LName, PNumber, PName, Hours) Violation: SSN → LName Decompose: R1 = SSN+ = (SSN, FName, LName) R - R1 = (PNumber, PName, Hours) Lossless decomposistion requirement: R1 ∩ R2 → R1 or R1 ∩ R2 → R2 Decomposition: R1 = (SSN, FName, LName) R2 = (SSN, PNumber, PName, Hours) (R1 ∩ R2 → R1) ```

Repeat !!
• Relations:

 ``` R1(SSN, FName, LName) Key(s): (SSN) - R1 is in 2NF R2(SSN, PNumber, PName, Hours) Key(s): (SSN, PNumber) - R2 is not in 2NF ```

• 2NF violation:

 ``` Non-key attribute: PName Key subset: PNumber PNumber → PName ```

• Decompose to remove 2NF violation:

 ``` Original relation: R2 (SSN, PNumber, PName, Hours) Violation: PNumber → PName Decompose: R21 = PNumber+ = (PNumber, PName) R2 - R21 = (SSN, Hours) Lossless decomposistion requirement: R21 ∩ R22 → R21 or R21 ∩ R22 → R22 Decomposition: R21 = (PNumber, PName) R22 = (SSN, PNumber, Hours) (R21 ∩ R22 → R21) ```

• Result:

 ``` R1 = (SSN, FName, LName) R21 = (PNumber, PName) R22 = (SSN, PNumber, Hours) ```

• An abstract example of decomposition into 2NF

• Consider the following relation:

 ``` R (a, b, c, d, e, f) Functional Dependencies: a → b,c d → e ad → f ```

• Decompose R   into the 2NF

• First:

 Does R violate 2NF ?

To answer this question, we must find all the keys of R.

 ``` Functional Dependencies: a → b,c d → e ad → f Necessary attributes to include in key: a, d Test for sufficiency: ad+ = a, d, b, c, e, f - sufficient ! Keys: (a, d) is the only key ```

• Now we can test for 2NF violation :

 ``` R (a, b, c, d, e, f) Functional Dependencies: a → b,c d → e ad → f Key: (a, d) 2NF violation: a → b b is non-key attribute b is subset of a key ```

• Decomposition: remove the a → b violation:

 ``` R (a, b, c, d, e, f) Functional Dependencies: a → b,c d → e ad → f Key: (a, d) Compute closure: a+ = a, b, c Decomposition: R1 = (a, b, c) R2 = (a, d, e f) Losslessnes guarantee: R1 ∩ R2 = (a) → R1 ```

Repeat : are R1 and R2 in 2NF ?

 ``` Functional Dependencies: a → b,c d → e ad → f R1 = (a, b, c) Key(s): a R2 = (a, d, e f) Key(s): (a, d) ```

• Test R1 and R2 for 2NF violation : ( R1 will be in 2NF by the way we constructed it)

 ``` Functional Dependencies: a → b,c d → e ad → f R1 = (a, b, c) Key(s): a R1 is in 2NF R2 = (a, d, e f) Key(s): (a, d) 2NF violation: d → e e is non-key attribute d is subset of a key ```

• Decomposition: remove the d → e violation:

 ``` R1 = (a, b, c) R2 = (a, d, e, f) Functional Dependencies: a → b,c d → e ad → f Key: (a, d) Compute closure: d+ = d, e Decomposition: R21 = (d, e) R22 = (a, d, f) Losslessnes guarantee: R21 ∩ R22 = (d) → R21 Result: R1 = (a, b, c) R21 = (d, e) R22 = (a, d, f) ```

Repeat : are R21 and R22 in 2NF ?

 ``` Functional Dependencies: a → b,c d → e ad → f R1 = (a, b, c) Key(s): a R21 = (d, e) Key(s): d R22 = (a, d, f) Key(s): (a, d) ```

• Test for 2NF violation : ( R21 will be in 2NF by the way we constructed it)

 ``` Functional Dependencies: a → b,c d → e ad → f R1 = (a, b, c) Key(s): a R1 is in 2NF R21 = (d, e) Key(s): (a, d) R21 is in 2NF R22 = (a, d, f) Key(s): (a, d) R22 is in 2NF ```

• DONE !

• By the way....

• You will find this example more familiar when you replace:

 a   → SSN b   → FName c   → LName d   → PNumber e   → PName f   → Hours

• Using the technique above, without knowing the meaning of the attributes, but only their functional dependencies, we were able to decompose the original relations into:

 R1 = (a, b, c) = (SSN, FName, LName) R21 = (d, e) = (PNumber, PName) R22 = (a, d, f) = (SSN, PNumber, Hours)

Can't be more objective than this....