Transcript notes
THIRD NORMAL FORM (3NF)
A relation R is in BCNF if whenever a FD XA holds in R,
one of the following statements is true:
•XA is a trivial FD, or
•X is a superkey, or
•A is part of some key
Why 3NF?
A relation R is in 3NF if whenever a FD XA holds in R,
one of the following statements is true:
•XA 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.
Cases of 3NF Violation
If XY causes a violation of 3NF, there are two cases:
•X is a proper subset of some key (partial dependency)
KEY
Attributes X
Attribute Y
Partial dependency causes data redundancy: Since XY and X
is not a key, X could be redundant, so Y.
•X is not a proper subset of any key (transitive dependency)
KEY
KEY
Attributes X
Attributes X
Attribute Y
Attribute Y
Transitive dependency KXY makes it impossible to record the values of
(K, X, Y) unless all of them are known: Since KXY, we cannot associate an
X value with a K value unless we also associate an X value with Y value
Dependency-Preserving Decomposition
Inputs:
A relation R with a set Fmin of FDs that is minimum cover
D(R1, R2, …, Rn) is a lossless-join decomposition of R
1. Find all dependencies in Fmin that are not preserved
2. For each such dependency XA, create a relation schema
XA and add it to the decomposition of R
• Every dependency in Fmin is now preserved
Proof: XA is in 3rd NF
1.
2.
X must be a key for XA, Since XA is in a minimal cover, YA does
not hold for any Y that is a subset of X
For any other dependencies hold over XA, say YZ, 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
Top-Down Approach: Lossless-Join and Dependency
Preserving Decomposition into 3NF
A. Lossless-Join Decomposition
1. Set D{R}
2. 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 XY in Q that violates BCNF;
• Replace Q in D by two schemas (Q-Y) and (XUY)
end;
B. Dependency-Preserving Decomposition
1.
Assume the decomposition is D(R1, R2, …, Rn) and the FD sets
are accordingly F1, F2, …, and Fn (let their union be F’)
2. For each dependency XA 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
Exercise
R(ABCDE)
F={ABCDE,ED,AB,ACD}
Top-down approach
1.
Loss-less join decomposition: R(ACBDE) is not in BCNF
ED
R1(ACBE)
R2(ED)
AB
R1(ACE)
R2(AB)
3. Dependency-preserving decomposition:
{ABCDE, ED, ABB, ACD}+ == {ACE, AB, ED}+ ??
/* Find a minimum cover first */
/* If XY is not preserved, add (XY) into the decomposition */
Bottom-up Approach: Lossless-Join and Dependency
Preserving Decomposition into 3NF
Inputs:
1.
A relation R and a set of functional dependencies F on the
attributes of R.
Find a minimal cover G of F.
2. For each left-hand side X of a FD G, create a relation
schema in D with attributes { X A1 A2 A3... Am}
where XA1, XA2, …, XAm are the only dependencies
in G with X as the left-hand side.
• Prove that this relation is in 3rd NF
3. 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
Exercise
R(ABCDE)
F={ABCDE,ED,AB,ACD}
Bottom up approach (Synthesis):
Step 1: Find a minimum cover, G={ACE,ED,AB}
Step 2: R1(ACE), R2(ED), R3(AB)
Step 3: Is this a lossless-join decomposition?
Normalization Review
1. Functional Dependency
Conceptual design
a)
b)
c)
d)
Amstrong’s axioms
Attribute closure (A+)
Dependency closure (F+)
Minimum cover (Fmin)
2. Normal Forms
Schemas
ICs
a) BCNF
b) 3NF
3. Decomposition
a) Lossless join
b) Dependency preserving
Determine Normal Forms
1) BCNF
– For each XA, is it a trivial dependency?
– Is X a superkey?
2) 3NF
– Suppose XA violate BCNF
– Is A part of some key?
Exercise 1
For each of the following relation schemas and sets of FDs
1. R(ABCD) with FDs ABC, CD, and DA
2. R(ABCD) with FDs BC and BD.
3. R(ABCD) with FDs ABC, BCD, CDA, and
ADB
Check if they are in BCNF or 3NF, if not, perform a
lossless join and dependency preserving decomposition
1)
BCNF
–
For each XA, is it a trivial dependency?
–
Is X a superkey?
2)
3NF
–
Suppose XA violate BCNF
–
Is A part of some key?
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.
Quiz
1. For each of the following relation schemas Indicate
the strongest normal form of each of the following
relations
– R1(ABCDE) F1={AB, CD, ACEABCDE}
– R2(ABCEF) F2={ABC, BF, FE}
2. Consider a relation R with five attributes: ABCDE.
F={AB, BCE, EDA}
– Are {ECD}, {ACD}, {BCD} keys for R?
– Is R in BCNF? Why?
– Is R in 3NF? Why?
Conceptual Schema ( ER diagram )
DBMS independent
DBMS specific
Data Model Mapping
Conceptual Schema ( Relations )
Normalization
• BCNF/3NF?
• Decomposition
- Lossless join
- Dependency preservation