Chapter 3 Design Theory for Relational Databases 1
1 Chapter 3 Design Theory for Relational Databases
Contents Functional Dependencies ●Decompositions Normal Forms(BCNF,3NF) Multivalued Dependencies (and 4NF) Reasoning About FD's MVD's 2
2 Contents z Functional Dependencies z Decompositions z Normal Forms (BCNF, 3NF) z Multivalued Dependencies (and 4NF) z Reasoning About FD’s + MVD’s
Our example of chapter 2 Beers(name,manf) Some questions: Bars(name,addr,license)1.Why do we design Drinkers(name,addr,phone) relations like the example? Likes(drinker,beer) 2.What makes a good Sells(bar,beer,price) relational database Frequents(drinker,bar) schema? 3.what we can do if it has A theory:“dependencies”will be flaws? talked first 3
3 Our example of chapter 2 Beers(name, manf) Bars(name, addr, license) Drinkers(name, addr, phone) Likes(drinker, beer) Sells(bar, beer, price) Frequents(drinker, bar) Some questions: 1. Why do we design relations like the example? 2. What makes a good relational database schema? 3. what we can do if it has flaws? A theory : “dependencies” will be talked first
Functional Dependencies .X->Y is an assertion about a relation R that whenever two tuples of R agree on all the attributes of X,then they must also agree on all attributes in set Y. Say "X->Y holds in R." Convention:...X,Y,Z represent sets of attributes;A,B, C,...represent single attributes. Convention:no set formers in sets of attributes,just ABC. rather than [A,B,C 4
4 Functional Dependencies z X ->Y is an assertion about a relation R that whenever two tuples of R agree on all the attributes of X, then they must also agree on all attributes in set Y. – Say “X ->Y holds in R.” – Convention: …, X, Y, Z represent sets of attributes; A, B, C,… represent single attributes. – Convention: no set formers in sets of attributes, just ABC, rather than {A,B,C }
Functional Dependency (cont.) Exist in a relational schema as a constraint. Agree for all instances of the schema (t and u are any two tuples) AsBs+ We have functional L dependency like this A1A2.今B1B2… u Ift and!Then they u agree Why we call "functional" 5 must agree here here dependency?
5 Functional Dependency (cont.) z Exist in a relational schema as a constraint. z Agree for all instances of the schema (t and u are any two tuples) A’s B’s If t and u agree here Then they must agree here t u We have functional dependency like this A1A2…ÆB1B2… Why we call “functional” dependency?