Order of attributes is arbitrary , but in practice w e - PDF document

Relational Mo del � T able = relation. � Column headers = attributes . � Ro w = tuple name manf Win terBrew P ete's BudLite A.B. � � � � � � Beers � = name(attributes) + other R elation schema structure info., e.g., k eys, other constrain ts. Example: manf) . Beers(name, ✦ Order of attributes is arbitrary , but in practice w e need to assume the order giv en in the relation sc hema. � is curren t set of ro ws for a R elation instanc e relation sc hema. � = collecti on of relation Datab ase schema sc hemas. 1

Wh y Relations? � V ery simple mo del. � a go o d matc h for the w a y w e think Often ab out our data. � Abstract mo del that underlies SQL, the most imp ortan t language in DBMS's to da y . ✦ But SQL uses \bags," while the abstract relational mo del is set-orien ted. 2

Relational Design Simplest approac h (not alw a ys b est): con v ert eac h E.S. to a relation and eac h relationship to a relation. ! En tit y Set Relation E.S. attributes b ecome relational attributes. name manf Beers Becomes: Beers(name, manf) 3

! E/R Relationships Relations Relation has attribute for attributes of eac h key E.S. that participates in the relationship. � Add an y attributes that b elong to the relationship itself. � Renaming attributes OK. ✦ Essen tial if m ultiple roles for an E.S. 4

name name manf addr Drink ers Beers Lik es 1 2 F a v orite Buddies h usband wife Married Lik es(drink er, b eer) F a v orite(drink er, b eer) Buddies(name1, name2) Married(h usband, wife) 5

Com bining Relations Sometimes it mak es sense to com bine relations. � Common case: Relation for an E.S. plus the E relation for some man y-one relationship from E to another E.S. Example Com bine with Drinker(name, addr) to get Favorite(drinker, beer) favBeer) . Drinker1(name, addr, � Danger in pushing this idea to o far: redundancy . � e.g., com bining with causes the Drinker Likes drink er's address to b e rep eated viz.: name addr beer Sally 123 Maple Bud Sally 123 Maple Miller � Notice the di�erence: is man y-one; Favorite is man y-man y . Likes 6

! W eak En tit y Sets, Relationships Relations � Relation for a w eak E.S. m ust include its full k ey (i.e., attributes of related en tit y sets) as w ell as its o wn attributes. � A supp orting (double-diamond) relationship yields a relation that is actually redundan t and should b e deleted from the database sc hema. 7

Example name name @ Logins Hosts Hosts(hostName) Logins(loginName, hostName) A t(loginName, hostName, hostName2) � In At , and m ust b e the hostName hostName2 same host, so delete one of them. � Then, and b ecome the same Logins At relation; delete one of them. � In this case, Hosts ' sc hema is a subset of Logins ' sc hema. Delete Hosts ? 8

! Sub classes Relations Three approac hes: 1. Ob ject-orien ted: eac h en tit y is in one class. Create a relation for eac h class, with all the attributes for that class. ✦ Don't forget inherited attributes. 2. E/R st yle: an en tit y is in a net w ork of classes related b y isa . Create one relation for eac h E.S. ✦ An en tit y is represen ted in the relation for eac h sub class to whic h it b elongs. ✦ Relation has only the attributes attac hed to that E.S. + k ey . 3. Use n ulls. Create one relation for the ro ot class or ro ot E.S., with all attributes found an ywhere in its net w ork of sub classes. ✦ Put in attributes not relev an t to a NULL giv en en tit y . 9

Example name manf Beers isa color Ales 10

OO-St yle name manf Bud A.B. Beers name manf color SummerBrew Pete's dark Ales E/R St yle name manf Bud A.B. SummerBrew Pete's Beers name color SummerBrew dark Ales 11

Using Nulls name manf color Bud A.B. NULL SummerBrew Pete's dark Beers 12

F unctional Dep endencies ! = assertion ab out a relation that X A R whenev er t w o tuples agree on all the attributes of , then they m ust also agree on attribute A . X Example Drinkers(name, addr, beersLiked, manf, favoriteBeer) name addr b eersLik ed manf fa v oriteBeer Janew a y V o y ager Bud A.B. Wic k edAle Janew a y V o y ager Wic k edAle P ete's Wic k edAle Sp o c k En terprise Bud A.B. Bud � Reasonable FD's to assert: 1. ! name addr 2. ! name favoriteBeer 3. ! beersLiked manf 13

� Shorthand: com bine FD's with common left side b y concatenating their righ t sides. � Sometimes, sev eral attributes join tly determine another attribute, although neither do es b y itself. Example: ! beer bar price 14

Keys of Relations is a for relation if: K key R 1. ! all attributes of . K R 2. F or no prop er subset of is (1) true. K � If at least satis�es (1), then is a K K erkey . sup Con v en tio ns � Pic k one k ey; underline k ey attributes in the relation sc hema. � , etc., represen t sets of attributes; etc., X A represen t single attributes. � No set formers in FD's, e.g., instead of AB C f A; g . B ; C 15

Example Drinkers(name , addr, beersLiked , manf, favoriteBeer) � f name, beersLiked g FD's all attributes, as seen. ✦ f name, beersLiked g Sho ws is a sup erk ey . � ! is false, so not a name beersLiked name sup erk ey . � ! also false, so beersLiked name beersLiked not a sup erk ey . � Th us, f name, beersLiked g is a k ey . � No other k eys in this example. ✦ Neither nor is on the name beersLiked righ t of an y observ ed FD, so they m ust b e part of sup erk ey . any � Imp ortan t p oin t: \k ey" in a relation refers to tuples, not the en tities they represen t. If an en tit y is represen ted b y sev eral tuples, then en tit y-k ey will not b e the same as relation-k ey . 16

Who Determines Keys/FD's? � W e could assert a k ey . K ✦ Then the only FD's asserted are that ! for ev ery attribute A . K A ✦ No surprise: is then the only k ey K for those FD's, according to the formal de�nition of \k ey ." � Or, w e could assert some FD's and one de duc e or more k eys b y the formal de�nition. ✦ E/R diagram implies FD's b y k ey declarations and man y-one relationship declarations. � Rule of th um b: FD's either come from k eyness, man y-1 relationship, or from ph ysics. ✦ E.g., \no t w o courses can meet in the same ro om at the same time" yields ! course . room time 17

Inferring FD's And this is imp ortan t b ecause . . . � When w e talk ab out impro ving relational designs, w e often need to ask \do es this FD hold in this relation?" ! ! � � � ! Giv en FD's 1 A 1, 2 A 2 An , X X X n do es FD ! necessarily hold in the same Y B relation? � Start b y assuming t w o tuples agree in . Use Y giv en FD's to infer other attributes on whic h they m ust agree. If is among them, then B y es, else no. 18

Algorithm + De�ne = of = set of attributes Y closur e Y functionally determined b y : Y + � Basis: := . Y Y + � Induction: If � , and ! is a giv en X Y X A + FD, then add to . A Y A X + + new Y Y � End when + cannot b e c hanged. Y 19

Example ! ! , . A B B C D + � = . A AB + � = . C C � ( AC ) + = . AB C D B A C D 20

Finding All Implied FD's Motiv atio n: Supp ose w e ha v e a relation AB C D with some FD's . If w e decide to decomp ose F in to and , what are the FD's for AB C D AB C AD , ? AB C AD � Example: = ! , ! , ! A . F AB C C D D It lo oks lik e just ! holds in , but AB C AB C in fact ! follo ws from and applies to C A F relation . AB C � Problem is exp onen tial in w orst case. 21

Algorithm + � F or eac h set of attributes compute . X X ✦ But skip = ; , = all attributes. X X ✦ ! + � Add X A for eac h A in X X . � ! ! Drop if holds. X Y A X A � Finally , pro ject the FD's b y selecting only those FD's that in v olv e only the attributes of the pro jection. ✦ Notice that after w e pro ject the disco v ered FD's on to some relation, the eliminated FD's can b e inferred in the elation . pr oje cte d r 22