CS457 Syllabus & Progress

A concrete example of decomposition into 3NF

Recall: the Third Normal Form (3NF):

Definition of the 3NF :

A relation R is in 3NF iff:

every

functional dependency

X → B

in relation R, one of the following must be true:

X is a superkey , or
B is a key attribute (i.e., B is part of some key )

(If one of the above is true, then the functional dependency is "good" (= harmless))

Decomposition algorithm to obtain a lossless decomposition using Lemma 2

Decomposition algorithm:

for ( each relation R ) { if ( R is not in 3 NF ) { Let X → B be a violating function dependency; Decompose R into: (1) R1 = X⁺; (2) R2 = R - R1 ∪ X; } }

I will illustrate the decomposition algorithm using an example

Example decomposition into 3NF - using a concrete example

Input relation:

Employee1(SSN, Fname, LName, PNumber, PName, Hours)
Functional dependencies: SSN → FName, LName PNumber → PName SSN, PNumber → Hours
The key of Employee1 is: (SSN, PNumber)

Relation Employee1 has 3NF violation because of the following functional dependency:

FD: SSN → FName, LName Where: SSN is not a superkey

Decomposition algorithm: (explained using an example)

Input relation:

R = Employee1(SSN, FName, LName, PNumber, PName, Hours) Key: (SSN, PNumber)

with violating functional dependency:

SSN → LName

Step 1:

Compute SSN⁺

Result of step 1:

SSN⁺ = (SSN, FName, LName)

Step 2:

Decompose the input relation into the follow 2 relations:

R1 = (all attributes in SSN⁺) R2 = R − R1

Result of step 2:

Employee1(SSN, FName, LName, PNumber, PName, Hours) / \ / \ R1 (= SSN⁺) R2 (= R - R1) | | R1(SSN, FName, LName) R2(PNumber, PName, Hours)

Notice that:

SSN is a key in relation R1 !!!

Step 3: to obtain a lossless decomposition :

Add SSN to R2 !!!

Final decomposition:

Employee1(SSN, FName, LName, PNumber, PName, Hours) / \ / \ R1(SSN, FName, LName) R2(SSN, PNumber, PName, Hours)

Note:

R1 ∩ R2 = SSN
Since: SSN → R1, by Lemma 2:

Decomposition:

Employee1(SSN, FName, LName, PNumber, PName, Hours) / \ / \ R1(SSN, FName, LName) R2(SSN, PNumber, PName, Hours)

Wait, we are not finished !!!
- Wait !!!

Applying functional dependencies to decomposed relations

Fact:

A functional dependency is applicable to a relation R iff:

All attributes in the Left Hand Side of the functional dependency is found in the relation R and
At least one attribute in the Right Hand Side of the functional dependency is found in the relation R

Example:

We have the following decomposed relations in the example:

R1(SSN, FName, LName) R2(SSN, PNumber, PName, Hours)

The given set of functional dependencies are:

SSN → FName, LName PNumber → PName SSN, PNumber → Hours

Then:

The functional dependency SSN → FName, LName is applicable to the following relation:
The functional dependency PNumber → PName is applicable to the following relation:
The functional dependency SSN, PNumber → Hours is applicable to the following relation:

Checking the decomposed relations

Decomposed relations:

R1(SSN, FName, LName) R2(SSN, PNumber, PName, Hours)

We verify if the decomposed relations satisfy the 3NF criteria !!!

Recall: the functional dependencies are:

SSN → FName, LName PNumber → PName SSN, PNumber → Hours

Check for 3NF condition in each decomposed relation:

R1( SSN, FName, LName):
Functional dependencies that apply to R1:
Conclusion:

R2(SSN, PNumber, PName, Hours):

R2(SSN, PNumber, PName, Hours) Key(s): (SSN, PNumber)

Functional dependencies that apply to R2:

SSN, PNumber → Hours
Since (SSN, PNumber) is a key --- it's a good functional dependency !
PNumber → PName
Since PNumber is not a key, this is a 3NF violation !!!

Conclusion:

R2( SSN, PName, PNumber, Hours) is not in 3NF !!!!

Decompose R2 to to remove 3NF violation:

Input relation:

R2(SSN, PNumber, PName, Hours)

with violating functional dependency:

PNumber → PName

Compute PNumber⁺ to remove all functional dependent attributes:

Initial decomposition:

R2(SSN, PNumber, PName, Hours) / \ / \ R21 (= PNumber⁺) R22 (= R2 - R21) | | R21(PNumber, PName) R22(SSN, Hours)

To obtain a lossless decomposition:

Add PNumber to R22 !!!

Final decomposition:

R2(SSN, PNumber, PName, Hours) / \ / \ R21 (= PNumber⁺) R22 (= R2 - R21) | | R21(PNumber, PName) R22(SSN, PNumber, Hours)

Note:

R21 ∩ R22 = PNumber
Since: PNumber → R21: the decomposition is lossless !!!

Post-post processing

Decomposed relations:

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

Question:

Are R1, R21 and R22 in 3NF ???

Answer:

Yes !!!

Final result of the decomposition:

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