Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
This course is about database management systems. Main topics covered in this course are: manipulate data, use standard query language, creating a database and logical query language.Normalization, Boyce-codd Normal Form, Nontrivial FD, Redundancy, Update Anomalies, Deletion Anomalies, Decomposition, 3NF, Multivalued Dependencies, 4NF
Typology: Slides
1 / 16
This page cannot be seen from the preview
Don't miss anything!
Normalization
Goal = BCNF = Boyce-Co dd Normal Form = all FD's follow from the fact \key! everything."
Formally, R is in BCNF if every nontrivial FD for R , say X! A, has X a sup erkey. F \Nontrivial" = right-side attribute not in left side.
Why?
Example of Problems
Drinkers(name , addr, beersLiked , manf, favoriteBeer)
name addr b eersLiked manf favoriteBeer Janeway Voyager Bud A.B. WickedAle Janeway ??? WickedAle Pete's ??? Sp o ck Enterprise Bud ??? Bud
FD's:
???'s are redundant, since we can gure them out from the FD's.
Up date anomalies: If Janeway gets transferred to the Intrepid, will we rememb er to change addr in each of her tuples?
Deletion anomalies: If nob o dy likes Bud, we lose track of Bud's manufacturer.
Decomp osition to Reach BCNF
Setting: relation R , given FD's F. Supp ose relation R has BCNF violati on X! B.
We need only lo ok among FD's of F for a BCNF violati on, not those that follow from F. Why?
F Cannot b e all attributes | why?
R X X +
F Pro ject the FD's from F = calculate all consequents of F that involve only attributes from X +^ or only from (R X +^ ) [ X.
Example
R = Drinkers(name , addr, beersLiked , manf, favoriteBeer)
F =
Pick BCNF violati on name! addr.
Close the left side: name +^ = name addr favoriteBeer.
Decomp osed relations:
Drinkers1(name , addr, favoriteBeer) Drinkers2(name , beersLiked , manf)
Pro jected FD's (skipping a lot of work that leads nowhere interesting): F For Drinkers1: name! addr and name! favoriteBeer. F For Drinkers2: beersLiked! manf.
3NF
One FD structure causes problems:
If you decomp ose, you can't check the FD's in the decomp osed relations.
If you don't decomp ose, you violate BCNF.
Abstractly: AB! C and C! B.
In b o ok: title city! theatre and theatre ! city.
Another example: street city! zip, zip! city.
Keys: fA; B g and fA; C g, but C! B has a left side not a sup erkey.
Suggests decomp osition into B C and AC.
F But you can't check the FD AB! C in these relations.
Example
A = street, B = city, C = zip.
street zip 545 Tech Sq. 02138 545 Tech Sq. 02139
city zip Cambridge 02138 Cambridge 02139
Join:
city street zip Cambridge 545 Tech Sq. 02138 Cambridge 545 Tech Sq. 02139
What 3NF Gives You
There are two imp ortant prop erties of a decomp osition:
Without pro of, we assert that it is always p ossible to decomp ose into BCNF and satisfy (1).
Also without pro of, we can decomp ose into 3NF and satisfy b oth (1) and (2).
But it is not p ossible to decomp ose into BNCF and get b oth (1) and (2). F Street-city-zi p is an example of this p oint.
Multivalued Dep endencies
The multivalued dependency X !! Y holds in a relation R if whenever we have two tuples of R that agree in all the attributes of X , then we can swap their Y comp onents and get two new tuples that are also in R.
X Y others
MVD Rules
F Because if X! Y , then swapping Y 's b etween tuples that agree on X do esn't create new tuples. F Example, in Drinkers: name !! addr.
Splitting Do esn't Hold
Sometimes you need to have several attributes on the right of an MVD. For example:
Drinkers(name, areaCode, phones, beersLiked, beerManf)
name areaCo de phones BeersLiked b eerManf Sue 650 555-1111 Bud A.B. Sue 650 555-1111 WickedAle Pete's Sue 415 555-9999 Bud A.B. Sue 415 555-9999 WickedAle Pete's
name !! areaCode phones holds, but neither name !! areaCode nor name !! phones do.
Example
Drinkers(name, addr, phones, beersLiked)
FD: name! addr
Nontrivial MVD's: name !! phones and name !! beersLiked.
Only key: fname, phones, beersLikedg
All three dep endencies ab ove violate 4NF.
Successive decomp osition yields 4NF relations:
D1(name , addr) D2(name , phones ) D3(name , beersLiked )
