THIRD NORMAL FORM (3NF)

1. THIRD NORMAL FORM (3NF) • A relation R is in BCNF if whenever a FD XA holds in R, one of the following statements is true: • XA is a trivial FD, or • X is a superkey, or • A is part of some key

2. Why 3NF? • A relation R is in 3NF if whenever a FD XA holds in R, one of the following statements is true: • XA is a trivial FD, or • X is a superkey, or • A is part of some key By making an exception for certain dependencies involving some key attributes, we can ensure that every relation schema can be decomposed into a collection of 3NF with two desirable properties: lossless-join and dependency-preserving.

3. Attributes X Attribute Y KEY Cases of 3NF Violation • If XY causes a violation of 3NF, there are two cases: • X is a proper subset of some key (partial dependency) • Partial dependency causes data redundancy: Since XY and X is not a key, X could be redundant, so Y. • X is not a proper subset of any key (transitive dependency) KEY Attributes X Attribute Y Attributes X Attribute Y KEY Transitive dependency KXY makes it impossible to record the values of (K, X, Y) unless all of them are known: Since KXY, we cannot associate an X value with a K value unless we also associate an X value with Y value

4. Dependency-Preserving Decomposition Inputs: A relation R with a set Fminof FDs that is minimum cover D(R1, R2, …, Rn) is a lossless-join decomposition of R • Find all dependencies in Fminthat are not preserved • For each such dependency XA, create a relation schema XA and add it to the decomposition of R • Every dependency in Fminis now preserved • Proof: XA is in 3rd NF • X must be a key for XA, Since XA is in a minimal cover, YA does not hold for any Y that is a subset of X • For any other dependencies hold over XA, say YZ, in Fmin, it must satisfy 3rd NF conditions • If Z is A, Y must be X • If Z is not A, Z must be part of X

5. Top-Down Approach:Lossless-Join and Dependency Preserving Decomposition into 3NF • A. Lossless-Join Decomposition • Set D{R} • While there is a relation schema Q in D that is not in BCNF do • begin • Choose a relation schema Q in D that is not in BCNF; • Find a functional dependency XY in Q that violates BCNF; • Replace Q in D by two schemas (Q-Y) and (XUY) • end; • B. Dependency-Preserving Decomposition • Assume the decomposition is D(R1, R2, …, Rn) and the FD sets are accordingly F1, F2, …, and Fn (let their union be F’) • For each dependency XA in the original F (needs to be a minimum cover), check if it can be inferred from F’ • If not, create a relation schema XA and add it to the decomposition of R

6. ED R2(ED) R1(ACBE) AB R2(AB) R1(ACE) Exercise R(ABCDE) F={ABCDE,ED,AB,ACD} Top-down approach • Loss-less join decomposition: R(ACBDE) is not in BCNF 3. Dependency-preserving decomposition: {ABCDE, ED, ABB, ACD}+ == {ACE, AB, ED}+ ?? /* Find a minimum cover first */ /* If XY is not preserved, add (XY) into the decomposition */

7. Bottom-up Approach:Lossless-Join and Dependency Preserving Decomposition into 3NF Inputs: A relation R and a set of functional dependencies F on the attributes of R. • Find a minimal cover G of F. • For each left-hand side X of a FD G, create a relation schema in D with attributes • where XA1, XA2, …, XAm are the only dependencies in G with X as the left-hand side. • Prove that this relation is in 3rd NF • If none of the relation schemas in D contains a key of R, then create one more relation schema in D that contains attributes that form a key of R. • Prove that this decomposition is lossless-join

8. Exercise R(ABCDE) F={ABCDE,ED,AB,ACD} Bottom up approach (Synthesis): Step 1: Find a minimum cover, G={ACE,ED,AB} Step 2: R1(ACE), R2(ED), R3(AB) Step 3: Is this a lossless-join decomposition?

9. Conceptual design Schemas ICs Normalization Review • Functional Dependency • Amstrong’saxioms • Attribute closure (A+) • Dependency closure (F+) • Minimum cover (Fmin) • Normal Forms • BCNF • 3NF • Decomposition • Lossless join • Dependency preserving

10. Determine Normal Forms • BCNF • For each XA, is it a trivial dependency? • Is X a superkey? • 3NF • Suppose XA violate BCNF • Is A part of some key?

11. Exercise 1 For each of the following relation schemas and sets of FDs • R(ABCD) with FDs ABC, CD, and DA • R(ABCD) with FDs BC and BD. • R(ABCD) with FDs ABC, BCD, CDA, and ADB Check if they are in BCNF or 3NF, if not, perform a lossless join and dependency preserving decomposition • BCNF • For each XA, is it a trivial dependency? • Is X a superkey? • 3NF • Suppose XA violate BCNF • Is A part of some key?

12. Exercise 2 • Prove that, if R is in 3NF and every key is simple (i.e, a single attribute), then R is in BCNF • Prove that, if R has only one key, it is in BCNF if and only if it is in 3NF.

13. Quiz • For each of the following relation schemas Indicate the strongest normal form of each of the following relations • R1(ABCDE) F1={AB, CD, ACEABCDE} • R2(ABCEF) F2={ABC, BF, FE} • Consider a relation R with five attributes: ABCDE. F={AB, BCE, EDA} • Are {ECD}, {ACD}, {BCD} keys for R? • Is R in BCNF? Why? • Is R in 3NF? Why?

14. Conceptual Schema ( ER diagram ) DBMS independent Data Model Mapping DBMS specific Conceptual Schema ( Relations ) • BCNF/3NF? • Decomposition • Lossless join • Dependency preservation Normalization