W astes space and encourages inconsistency . KISS = k eep - PDF document

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 . � ✦ W astes space and encourages inconsistency . KISS = k eep it simple, studen ts. � ✦ Av oid in termediate concepts. F aithfulness to requiremen ts. � ✦ Remem b er the design should schema enforce as man y constrain ts as p ossible. Don't rely on future data 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. 1

Example Go o d: name name addr Beers ManfBy Manfs Bad (redundancy): rep eats man ufacturer address for eac h b eer they man ufacture. name manf manf addr Beers 2

Bad (needless in termediate): name name Beers ManfBy Manfs Question: Wh y is it OK to ha v e with Be ers � just its k ey as attribute? Wh y not mak e set of b eers an attribute of man ufacturers? ✦ Questionable in E/R, but clearly legal in ODL: interface Manfs (key name) { attribute string name; attribute string addr; attribute Set<string> beersManfed; } A Design Problem W e wish to design a database represen ting cities, coun ties, and states in the US. 3

F or states, w e wish to record the name, � p opulation, and state capital (whic h is a cit y). F or coun ties, w e wish to record the name, � the p opulation, and the state in whic h it is lo cated. F or cities, w e wish to record the name, the � p opulation, the state in whic h it is lo cated and the coun t y or coun ties in whic h it is lo cated. 4

Uniqueness assumptions: � ✦ Names of states are unique. ✦ Names of coun ties are only unique within a state (e.g., 26 states ha v e W ashington Coun ties). ✦ Cities are lik ewise unique only within a state (e.g., there are 24 Spring�elds among the 50 states). ✦ Some coun ties and cities ha v e the same name, ev en within a state (example: San F rancisco). ✦ Almost all cities are lo cated within a single coun t y , but some (e.g., New Y ork Cit y) extend o v er sev eral coun ties. 5

Another Design Problem W e wish to design a database consisten t with the follo wing facts. T rains are either lo cal trains or express trains, � but nev er b oth. A train has a unique n um b er and an engineer. � Stations are either express stops or lo cal stops, � but nev er b oth. A station has a name (assumed unique) and � an address. All lo cal trains stop at all stations. � Express trains stop only at express stations. � F or eac h train and eac h station the train stops � at, there is a time. 6

Stra wman: What's W rong With This? n um b er name addr T rains Stations t yp e StopsA t engineer t yp e time 7

In the Begining . . . (Historical data mo dels: Net w ork and Hierarc hical) The mid 1960's sa w the �rst systems that used � secondary storage for = retriev al b y querying v alue, not b y �le name. Big di�erence: se c ondary stor age mo del of � = data in blo c ks/pages, and ma jor cost data is retrieving or storing a ck . blo : Storing man y-man y Unsolve d pr oblem � relationships so they can b e tra v ersed e�cien tly in b oth directions. ✦ Easy in RAM mo del: link ed lists of successors, predecessors, e.g. ✦ Man y-one is easy in secondary-storage mo del, e.g., store eac h b eer follo wing its man ufacturer, so \�nd the Anheuser- Busc h b eers" can b e answ ered b y retrieving them all on one or a few blo c ks. A.B. BudLite ... Bud Mic helob ... 8

Net w ork Mo del Essen tially en tit y sets and binary , man y-one relationships. Replace a man y-man y relationship b y � a connecting E.S. and t w o man y-one relationships. En tit y set (LR T). L o gic al R e c or d T yp e � ! Man y-one relationship Link . � ! ✦ T erminology useful to this da y: = owner one, = man y , e.g., a man ufacturer memb er record \o wns" b eer records. Beers(name,manf ) TheBeer BB(price) TheBar Bars(name,addr) 9

Hierarc hical Mo del Used in ma jor early DBMS's, including IBM's IMS, whic h is still supp orted to da y . Net w ork mo del, restricted to a forest, where � o wners are paren ts of c hildren. Adds to handle man y-man y Virtual LR T � relationships. ✦ Think of V. LR T as represen ting p oin ters. Bars V.Beers V.Drink ers Beers Drink ers V.Bars V.Drink ers V.Beers V.Bars 10

T ypical Bar Record Jo e'sBar P oin ters P oin ters to Beer to Drink er records records No help in secondary storage mo del when � going from a bar to either its b eers or its drink ers. 11

Example Where Hierarc hical Mo del Wins Depts Budget Pro jects items Emplo y ees T ypical stored records mak e e�cien t � queries that go Dept Budget Item or ! Pro ject Emplo y ees. ! Dept1 Pro j1 E11 E12 E13 Pro j2 E21 E22 BI1 BI2 BI3 12