W eak En tit y Sets Sometimes an E.S. 's k ey comes not (completely) E from its o wn attributes, but from the k eys of one or more E.S's to whic h is link ed b y a supp orting E man y-one relationship. Called a E.S. we ak � Represen ted b y putting double rectangle � around and a double diamond around eac h E supp orting relationship. Man y-one-ness of supp orting relationship � (includes 1-1) essen tial. ✦ With man y-man y , w e w ouldn't kno w whic h en tit y pro vided the k ey v alue. \Exactly one" also essen tial, or else w e migh t � not b e able to extract k ey attributes b y follo wing the supp orting relationship. 1
Example: Logins (Email Addresses) Login name = user name + host name, e.g., edu . ullman@shalmaneser .stan ford. A \login" en tit y corresp onds to a user name � on a particular host, but the passwd table do esn't record the host, just the user name, e.g. ullman . Key for a login = the user name at the host � (whic h is unique for that host only) + the IP address of the host (whic h is unique globally) . name name @ Logins Hosts Design issue: Under what circumstances could � w e simply mak e login-name and host-name b e attributes of logins, and disp ense with the w eak E.S.? 2
Example: Chain of \W eakness" Consider IP addresses consisting of a primary domain (e.g., edu ) sub domain (e.g., stanford ), and host (e.g. shalmaneser ). name name name 2ndary Primary Hosts In2 In1 Domains Domains Key for primary domain = its name. � Key for secondary domain = its name + name � of primary domain. Key for host = its name + k ey of secondary � domain = its name + name of secondary domain + name of primary domain. 3
All \Connecting" En tit y Sets Are W eak BBP The- The- The- Bar Beer Price Bars Beers Prices name addr name manf price In this sp ecial case, where bar and b eer � determine a price, w e can omit from price the k ey , and remo v e the double diamond from ThePrice . Better: is attribute of BBP . price � 4
Design Principles Setting: clien t has (p ossibly v ague) idea of what he/she w an ts. Y ou m ust design a database that represen ts these though ts and only these though ts. Av oid Redundancy = sa ying the same thing more than once. W astes space and encourages inconsistency . � Example Go o d: name name addr Beers ManfBy Manfs 5
Bad: rep eats man ufacturer address for eac h b eer they man ufacture. name manf manf Beers addr Bad: man ufacturer's name said t wice. name name addr Beers ManfBy Manfs manf 6
Use Sc hema to Enforce Constrain ts The design should enforce as man y schema constrain ts as p ossible. Don't rely on users to follo w assumptions. � Example If registrar w an ts to asso ciate only one instructor with a course, don't allo w sets of instructors and coun t on departmen ts to en ter only one instructor p er course. 7
En tit y Sets Vs. A ttributes Y ou ma y b e unsure whic h concepts are w orth y of b eing en tit y sets, and whic h are handled more simply as attributes. Esp ecially tric ky for the class design pro ject, � since there is a temptation to create needless en tit y sets to mak e pro ject \larger." W rong: name name Beers ManfBy Manfs Righ t: name manf Beers 8
In tuitiv e Rule for E.S. Vs. A ttribute Mak e an en tit y set only if it either: 1. Is more than a name of something; i.e., it has nonk ey attributes or relationships with a n um b er of di�eren t en tit y sets, or 2. Is the \man y" in a man y-one relationship. 9
Example The follo wing design illustrate s b oth p oin ts: name name addr Beers ManfBy Manfs deserv es to b e an E.S. b ecause w e Manfs � record addr , a nonk ey attribute. deserv es to b e an E.S. b ecause it is at Be ers � the \man y" end. ✦ If not, w e w ould ha v e to mak e \set of b eers" an attribute of | something Manfs w e a v oid doing, although some ma y tell y ou it is OK in E/R mo del. 10
Don't Ov eruse W eak E.S. There is a tendency to feel that no E.S. has its � en tities uniquely determined without follo wing some relationships. Ho w ev er, in practice, w e almost alw a ys create � unique ID's to comp ensate: so cial-securit y n um b ers, VIN's, etc. The only times w eak E.S.'s seem necessary are � when: a) W e can't easily create suc h ID's; e.g., no one is going to accept a \sp ecies ID" as part of the standard nomenclature (sp ecies is a w eak E.S. supp orted b y mem b ership in a gen us). b) There is no global authorit y to create them, e.g., crews and studios. 11
Classro om Design Exercise Imagine w e are creating a database for a dorm, whic h includes a co op erativ e kitc hen. W e w an t to record certain information ab out � eac h residen t. What? Not all residen ts b elong to the kitc hen co op. � Those that do in teract in v arious w a ys: 1. They tak e turns at v arious jobs: preparer, clean up, buy er (for supplies). No one should ha v e t w o jobs on one da y . 2. They ma y or ma y not b e v egetarian. Eac h meal m ust ha v e at least one v egetarian en try . 3. They pa y fees to the co op. F or eac h meal, there is a men u. Eac h men u � item requires certain ingredien ts, whic h m ust b e on hand. 12
If There's Time � � � Supp ose w e only need to ha v e a v egetarian c hoice for a giv en meal if there is at least one v egetarian taking that meal. Ho w w ould w e mo dify the database sc hema? 13
Recommend
More recommend
