Functional Dependencies, Normalization
description
Transcript of Functional Dependencies, Normalization
![Page 1: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/1.jpg)
Functional Dependencies,Normalization
Rose-Hulman Institute of Technology
Curt Clifton
![Page 2: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/2.jpg)
Or…
![Page 3: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/3.jpg)
Fixing Broken Database DesignsThis material will almost certainly appear on Exam II next week.
![Page 4: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/4.jpg)
Outline Functional Dependencies Keys Revisited Redundancy and Anomalies Normalization
![Page 5: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/5.jpg)
Functional Dependencies (FD) Let X be a set of attributes of a relation R Let A be a single attribute of R X A holds for R if:
whenever two tuples of R agree on all the attributes of X,
then they must also agree on the attribute A. We say X “uniquely determines” A in R
![Page 6: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/6.jpg)
Example Customer(Name, Addr, SodaLiked, Manf,
FavSoda), with name identifying a unique person
Lots of redundancy here…Name Addr SodaLiked Manf FavSoda
Janeway Voyager Pepsi PepsiCo Coke
Janeway Voyager Sprite CocaCola Coke
Spock Enterprise Pepsi PepsiCo Coke
![Page 7: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/7.jpg)
FDs from Data Does Name Addr?
Name Addr SodaLiked Manf FavSoda
Janeway Voyager Pepsi PepsiCo Coke
Janeway Voyager Sprite CocaCola Coke
Spock Enterprise Pepsi PepsiCo Coke
![Page 8: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/8.jpg)
FDs from Data Does Name Addr? Yes, since we assumed unique names
Name Addr SodaLiked Manf FavSoda
Janeway Voyager Pepsi PepsiCo Coke
Janeway Voyager Sprite CocaCola Coke
Spock Enterprise Pepsi PepsiCo Coke
![Page 9: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/9.jpg)
FDs from Data Does Name FavSoda?
Name Addr SodaLiked Manf FavSoda
Janeway Voyager Pepsi PepsiCo Coke
Janeway Voyager Sprite CocaCola Coke
Spock Enterprise Pepsi PepsiCo Coke
![Page 10: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/10.jpg)
FDs from Data Does Name FavSoda? Yes, we want just one favorite per person
Name Addr SodaLiked Manf FavSoda
Janeway Voyager Pepsi PepsiCo Coke
Janeway Voyager Sprite CocaCola Coke
Spock Enterprise Pepsi PepsiCo Coke
![Page 11: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/11.jpg)
FDs from Data Does SodaLiked Manf?
Name Addr SodaLiked Manf FavSoda
Janeway Voyager Pepsi PepsiCo Coke
Janeway Voyager Sprite CocaCola Coke
Spock Enterprise Pepsi PepsiCo Coke
![Page 12: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/12.jpg)
FDs from Data Does SodaLiked Manf? Yes, since each soda has just one manf.
Name Addr SodaLiked Manf FavSoda
Janeway Voyager Pepsi PepsiCo Coke
Janeway Voyager Sprite CocaCola Coke
Spock Enterprise Pepsi PepsiCo Coke
![Page 13: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/13.jpg)
FDs from Data Does FavSoda Name?
Name Addr SodaLiked Manf FavSoda
Janeway Voyager Pepsi PepsiCo Coke
Janeway Voyager Sprite CocaCola Coke
Spock Enterprise Pepsi PepsiCo Coke
![Page 14: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/14.jpg)
FDs from Data Does FavSoda Name? No, two people might have the same favorite
Name Addr SodaLiked Manf FavSoda
Janeway Voyager Pepsi PepsiCo Coke
Janeway Voyager Sprite CocaCola Coke
Spock Enterprise Pepsi PepsiCo Coke
![Page 15: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/15.jpg)
FDs from ER Diagrams From entity sets
(Key of entity set) other attributes of entity set From many-one relationship
(Key of “many” set) attributes of “one” set
![Page 16: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/16.jpg)
Drawing FDs Use arrows to indicate FDs on schemas:
Customer(Name, Addr, SodaLiked, Manf, FavSoda)
![Page 17: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/17.jpg)
Notation Shorthand Technically FDs go from sets to single
attributes { Name } Addr { Name } FavSoda
Often just combine to write: Name Addr, FavSoda
Usually omit set braces on left side also: Restaurant, Soda Price
![Page 18: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/18.jpg)
Keys Revisited Let K be a set of attributes of a relation R K is a super key for R if:
For all attributes A in R, K A K is a key for R if:
No proper subset of K is a super key for R An attribute B is a prime attribute of R if:
B is an element of some key of R
![Page 19: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/19.jpg)
Example What is the key here? What are the prime attributes?
Customer(Name, Addr, SodaLiked, Manf, FavSoda)
![Page 20: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/20.jpg)
Two Ways to Find Keys Guess a superkey K:
Show that K A for all attributes A Show that no subset of K is a superkey
Find all functional dependencies Check all possible keys
![Page 21: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/21.jpg)
Why Talk About FDs? Let us formally identify redundancy Tell us how to fix it!
![Page 22: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/22.jpg)
Redundancy Leads to Anomalies Update anomaly: one occurrence of a fact is
changed, but not all occurrences Deletion anomaly: valid fact is lost when a
tuple is deleted
![Page 23: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/23.jpg)
Example
Name Addr SodaLiked Manf FavSoda
Janeway Voyager Pepsi PepsiCo Coke
Janeway Voyager Sprite CocaCola Coke
Spock Enterprise Pepsi PepsiCo Coke
Redundant with first row since Name Addr, FavSoda
Redundant with first row since SodaLiked Manf
![Page 24: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/24.jpg)
Normalization Using functional dependencies to eliminate
redundancy An extremely powerful technique
![Page 25: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/25.jpg)
Third Normal Form A relation R is in Third Normal Form (3NF)
if whenever X A is a nontrivial functional dependency that holds in R, then either: X is a superkey for R, or A is a prime attribute of R
![Page 26: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/26.jpg)
Normalization Algorithm To normalize a relation R:
Find the functional dependencies for R Check that whether each FD satisfies 3NF
If so, we’re done and R is normalized
Otherwise let X A be an FD that violates 3NF Find the closure of X in R, denoted X+
Split R into new relations (R - X+ + X) and X+
Repeat algorithm for each new relation
![Page 27: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/27.jpg)
Example: Grades Relation Grade(Term, Yr, C#, Sec#, IName,
SName, SAddr, S#, SSSN, Gr)
![Page 28: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/28.jpg)
Step 1: Find the FDs
![Page 29: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/29.jpg)
Step 2: Check for 3NF Violations A relation R is in Third Normal Form (3NF)
if whenever X A is a nontrivial functional dependency that holds in R, then either: X is a superkey for R, or A is a prime attribute of R
![Page 30: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/30.jpg)
Step 3: Pick a Violating FD, Find Closure
For X A the closure of X, denoted X+, is: The set of all attributes that can be reached from
any subset of X by following any FDs Or, just follow the arrows
![Page 31: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/31.jpg)
Step 4: Split R into Two Relations
R-X + X X +-X
R2
R1
R
![Page 32: Functional Dependencies, Normalization](https://reader035.fdocument.pub/reader035/viewer/2022062323/5681525c550346895dc08f1b/html5/thumbnails/32.jpg)
Repeat for the New Relations Find FDs Check for 3NF violations …