上海交通大学：《数据库系统原理 The principle of Database System》课程教学资源（课件讲稿）chapter3 Design Theory for Relational Databases

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?

Functional Dependency (cont.) ● Some examples Beers(name,manf) name→manf manf→name? Sells(bar,beer,price) Bar,beer→price 6

6 Functional Dependency (cont.) z Some examples Beers(name, manf) nameÆmanf manfÆname ? Sells(bar, beer, price) Bar,beer Æ price

Splitting Right Sides of FD's X->A,A2...A holds for R exactly when each of X->A1,X->A2,...,X->A hold for R. Example:A->BC is equivalent to A->B and A->C. ● There is no splitting rule for left sides. We'll generally express FD's with singleton right sides 7

7 Splitting Right Sides of FD’s z X->A1A2…An holds for R exactly when each of X->A1, X->A2,…, X->An hold for R. z Example: A->BC is equivalent to A->B and A->C. z There is no splitting rule for left sides. z We’ll generally express FD’s with singleton right sides

Example:FD's Drinkers(name,addr,beersLiked,manf, favBeer) Reasonable FD's to assert: 1. name->addr favBeer (combining rule) Note this FD is the same as name->addr and name -favBeer.(splitting rule) 2.beersLiked -manf 8

8 Example: FD’s Drinkers(name, addr, beersLiked, manf, favBeer) z Reasonable FD’s to assert: 1. name -> addr favBeer (combining rule) Š Note this FD is the same as name -> addr and name -> favBeer. (splitting rule) 2. beersLiked -> manf

Example:Possible Data name addr beersLiked manf favBeer Janeway Voyager Bud A.B. WickedAle Janeway Voyager WickedAle Pete's WickedAle Spock Enterprise Bud A.B. Bud Because name -addr Because name -favBeer Because beersLiked -manf 9

9 Example: Possible Data name addr beersLiked manf favBeer Janeway Voyager Bud A.B. WickedAle Janeway Voyager WickedAle Pete’s WickedAle Spock Enterprise Bud A.B. Bud Because name -> addr Because name -> favBeer Because beersLiked -> manf

Keys of Relations K is a superkey for relation R if K functionally determines all of R. ● K is a key for R if K is a superkey,but no proper subset of K is a superkey. (minimality) 10

10 Keys of Relations z K is a superkey for relation R if K functionally determines all of R. z K is a key for R if K is a superkey, but no proper subset of K is a superkey. (minimality)