![Page 1: Functional Dependencies and Relational Schema Design](https://reader035.vdocuments.us/reader035/viewer/2022070412/5681492a550346895db6638e/html5/thumbnails/1.jpg)
Functional Dependencies and Relational Schema Design
![Page 2: Functional Dependencies and Relational Schema Design](https://reader035.vdocuments.us/reader035/viewer/2022070412/5681492a550346895db6638e/html5/thumbnails/2.jpg)
Decompositions in GeneralA , A , … A 1 2 n
Let R be a relation with attributes
Create two relations R1 and R2 with attributes
B , B , … B 1 2 m C , C , … C 1 2 l
Such that:B , B , … B 1 2 m C , C , … C 1 2 l
A , A , … A 1 2 n
And -- R1 is the projection of R on
-- R2 is the projection of R on
B , B , … B 1 2 m
C , C , … C 1 2 l
![Page 3: Functional Dependencies and Relational Schema Design](https://reader035.vdocuments.us/reader035/viewer/2022070412/5681492a550346895db6638e/html5/thumbnails/3.jpg)
Incorrect Decomposition
Name Category
Gizmo Gadget
OneClick Camera
DoubleClick Camera
Price Category
19.99 Gadget
24.99 Camera
29.99 Camera
Name Price Category
Gizmo 19.99 Gadget
OneClick 24.99 Camera
OneClick 29.99 Camera
DoubleClick 24.99 Camera
DoubleClick 29.99 Camera
When we put it back:
Cannot recover information
Name Price Category
Gizmo 19.99 Gadget
OneClick 24.99 Camera
DoubleClick 29.99 Camera
Decompose on : Name, Category and Price, Category
![Page 4: Functional Dependencies and Relational Schema Design](https://reader035.vdocuments.us/reader035/viewer/2022070412/5681492a550346895db6638e/html5/thumbnails/4.jpg)
Normal Forms
First Normal Form = all attributes are atomic
Second Normal Form (2NF) = old and obsolete
Third Normal Form (3NF) = this lecture
Boyce Codd Normal Form (BCNF) = this lecture
Others...
![Page 5: Functional Dependencies and Relational Schema Design](https://reader035.vdocuments.us/reader035/viewer/2022070412/5681492a550346895db6638e/html5/thumbnails/5.jpg)
Boyce-Codd Normal Form
A simple condition for removing anomalies from relations:
A relation R is in BCNF if and only if:
Whenever there is a nontrivial dependency for R , it is the case that { } a super-key for R.
A , A , … A 1 2 n
BA , A , … A 1 2 n
In English (though a bit vague):
Whenever a set of attributes of R is determining another attribute, should determine all the attributes of R.
![Page 6: Functional Dependencies and Relational Schema Design](https://reader035.vdocuments.us/reader035/viewer/2022070412/5681492a550346895db6638e/html5/thumbnails/6.jpg)
ExampleName SSN Phone Number
Fred 123-321-99 (201) 555-1234
Fred 123-321-99 (206) 572-4312Joe 909-438-44 (908) 464-0028Joe 909-438-44 (212) 555-4000
What are the dependencies?SSN Name
What are the keys?
Is it in BCNF?
![Page 7: Functional Dependencies and Relational Schema Design](https://reader035.vdocuments.us/reader035/viewer/2022070412/5681492a550346895db6638e/html5/thumbnails/7.jpg)
Decompose it into BCNF
SSN Name
123-321-99 Fred
909-438-44 Joe
SSN Phone Number
123-321-99 (201) 555-1234
123-321-99 (206) 572-4312909-438-44 (908) 464-0028909-438-44 (212) 555-4000
SSN Name
![Page 8: Functional Dependencies and Relational Schema Design](https://reader035.vdocuments.us/reader035/viewer/2022070412/5681492a550346895db6638e/html5/thumbnails/8.jpg)
What About This?
Name Price Category
Gizmo $19.99 gadgetsOneClick $24.99 camera
Name Price, Category
![Page 9: Functional Dependencies and Relational Schema Design](https://reader035.vdocuments.us/reader035/viewer/2022070412/5681492a550346895db6638e/html5/thumbnails/9.jpg)
BCNF Decomposition
Find a dependency that violates the BCNF condition:
A , A , … A 1 2 n B , B , … B 1 2 m
A’sOthers B’s
R1 R2
Heuristics: choose B , B , … B “as large as possible”
1 2 m
Decompose:
Find a 2-attribute relation that isnot in BCNF.
Continue untilthere are noBCNF violationsleft.
![Page 10: Functional Dependencies and Relational Schema Design](https://reader035.vdocuments.us/reader035/viewer/2022070412/5681492a550346895db6638e/html5/thumbnails/10.jpg)
Example Decomposition
Name SSN Age EyeColor PhoneNumber
Functional dependencies: SSN Name, Age, Eye Color
What if we also had an attribute Draft-worthy, and the FD: Age Draft-worthy
Person:
BNCF: Person1(SSN, Name, Age, EyeColor), Person2(SSN, PhoneNumber)
![Page 11: Functional Dependencies and Relational Schema Design](https://reader035.vdocuments.us/reader035/viewer/2022070412/5681492a550346895db6638e/html5/thumbnails/11.jpg)
Correct Decompositions A decomposition is lossless if we can recover: R(A,B,C)
{ R1(A,B) , R2(A,C) }
R’(A,B,C) = R(A,B,C)
R’ is in general larger than R. Must ensure R’ = R
Decompose
Recover
![Page 12: Functional Dependencies and Relational Schema Design](https://reader035.vdocuments.us/reader035/viewer/2022070412/5681492a550346895db6638e/html5/thumbnails/12.jpg)
Decomposition Based on BCNF is Necessarily Lossless
R(A, B, C), A C
BCNF: R1(A,B), R2(A,C)
Some tuple (a,b,c) in R (a,b’,c’) also in R decomposes into (a,b) in R1 (a,b’) also in R1 and (a,c) in R2 (a,c’) also in R2
Recover tuples in R: (a,b,c), (a,b,c’), (a,b’,c), (a,b’,c’) also in R ?
Can (a,b,c’) be a bogus tuple? What about (a,b’,c’) ?
![Page 13: Functional Dependencies and Relational Schema Design](https://reader035.vdocuments.us/reader035/viewer/2022070412/5681492a550346895db6638e/html5/thumbnails/13.jpg)
3NF: A Problem with BCNFUnit Company Product
Unit Company
Unit Product
FD’s: Unit Company; Company, Product UnitSo, there is a BCNF violation, and we decompose.
Unit Company
No FDs
![Page 14: Functional Dependencies and Relational Schema Design](https://reader035.vdocuments.us/reader035/viewer/2022070412/5681492a550346895db6638e/html5/thumbnails/14.jpg)
So What’s the Problem?
Unit Company Product
Unit Company Unit Product
Galaga99 UW Galaga99 databasesBingo UW Bingo databases
No problem so far. All local FD’s are satisfied.Let’s put all the data back into a single table again:
Galaga99 UW databasesBingo UW databases
Violates the dependency: company, product -> unit!
![Page 15: Functional Dependencies and Relational Schema Design](https://reader035.vdocuments.us/reader035/viewer/2022070412/5681492a550346895db6638e/html5/thumbnails/15.jpg)
Solution: 3rd Normal Form (3NF)
A simple condition for removing anomalies from relations:
A relation R is in 3rd normal form if :
Whenever there is a nontrivial dependency A1, A2, ..., An Bfor R , then {A1, A2, ..., An } a super-key for R, or B is part of a key.
A relation R is in 3rd normal form if :
Whenever there is a nontrivial dependency A1, A2, ..., An Bfor R , then {A1, A2, ..., An } a super-key for R, or B is part of a key.
![Page 16: Functional Dependencies and Relational Schema Design](https://reader035.vdocuments.us/reader035/viewer/2022070412/5681492a550346895db6638e/html5/thumbnails/16.jpg)
Multi-valued Dependencies
SSN Phone Number Course
123-321-99 (206) 572-4312 CSE-444 123-321-99 (206) 572-4312 CSE-341123-321-99 (206) 432-8954 CSE-444123-321-99 (206) 432-8954 CSE-341
The multi-valued dependencies are:
SSN Phone Number SSN Course
![Page 17: Functional Dependencies and Relational Schema Design](https://reader035.vdocuments.us/reader035/viewer/2022070412/5681492a550346895db6638e/html5/thumbnails/17.jpg)
Definition of Multi-valued Dependecy
Given R(A1,…,An,B1,…,Bm,C1,…,Cp)
the MVD A1,…,An B1,…,Bm holds if:
for any values of A1,…,An the “set of values” of B1,…,Bm is “independent” of those of C1,…Cp
Given R(A1,…,An,B1,…,Bm,C1,…,Cp)
the MVD A1,…,An B1,…,Bm holds if:
for any values of A1,…,An the “set of values” of B1,…,Bm is “independent” of those of C1,…Cp
![Page 18: Functional Dependencies and Relational Schema Design](https://reader035.vdocuments.us/reader035/viewer/2022070412/5681492a550346895db6638e/html5/thumbnails/18.jpg)
Definition of MVDs Continued
Equivalently: the decomposition into
R1(A1,…,An,B1,…,Bm), R2(A1,…,An,C1,…,Cp)
is lossless
Note: an MVD A1,…,An B1,…,Bm
Implicitly talks about “the other” attributes C1,…Cp
![Page 19: Functional Dependencies and Relational Schema Design](https://reader035.vdocuments.us/reader035/viewer/2022070412/5681492a550346895db6638e/html5/thumbnails/19.jpg)
Rules for MVDs
If A1,…An B1,…,Bm
then A1,…,An B1,…,Bm
Other rules in the book
![Page 20: Functional Dependencies and Relational Schema Design](https://reader035.vdocuments.us/reader035/viewer/2022070412/5681492a550346895db6638e/html5/thumbnails/20.jpg)
4th Normal Form (4NF)
R is in 4NF if whenever:
A1,…,An B1,…,Bm
is a nontrivial MVD, then A1,…,An is a superkey
R is in 4NF if whenever:
A1,…,An B1,…,Bm
is a nontrivial MVD, then A1,…,An is a superkey
Same as BCNF with FDs replaced by MVDs
![Page 21: Functional Dependencies and Relational Schema Design](https://reader035.vdocuments.us/reader035/viewer/2022070412/5681492a550346895db6638e/html5/thumbnails/21.jpg)
Confused by Normal Forms ?
3NF
BCNF
4NF
In practice: (1) 3NF is enough, (2) don’t overdo it !