[PDF] - W astes space and encourages inconsistency . KISS = k eep PDF Document

SLIDE 1 Design Principles Setting: clien t has (p

ssibly

v ague) idea

f

what he/she w an ts. Y

u

m ust design a database that represen ts these though ts and

nly

these though ts.

Av
id

redundancy .

✦

W astes space and encourages inconsistency .

KISS

= k eep it simple, studen ts.

✦

Av

id

in termediate concepts.

F

aithfulness to requiremen ts.

✦

Remem b er the design schema should enforce as man y constrain ts as p

ssible.

Don't rely

n

future data to follo w assumptions.

✦

Example: If registrar w an ts to asso ciate

nly
ne

instructor with a course, don't allo w sets

f

instructors and coun t

n

departmen ts to en ter

nly
ne

instructor p er course. 1

SLIDE 2 Example Go

d:

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

SLIDE 3 Bad (needless in termediate): name Beers Manfs ManfBy name

Question:

Wh y is it OK to ha v e Be ers with just its k ey as attribute? Wh y not mak e set

f

b eers an attribute

f

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

SLIDE 4

F
r

states, w e wish to record the name, p

pulation,

and state capital (whic h is a cit y).

F
r

coun ties, w e wish to record the name, the p

pulation,

and the state in whic h it is lo cated.

F
r

cities, w e wish to record the name, the p

pulation,

the state in whic h it is lo cated and the coun t y

r

coun ties in whic h it is lo cated. 4

SLIDE 5

Uniqueness

assumptions:

✦

Names

f

states are unique.

✦

Names

f

coun ties are

nly

unique within a state (e.g., 26 states ha v e W ashington Coun ties).

✦

Cities are lik ewise unique

nly

within a state (e.g., there are 24 Springelds 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

rk

Cit y) extend

v

er sev eral coun ties. 5

SLIDE 6 Another Design Problem W e wish to design a database consisten t with the follo wing facts.

T

rains are either lo cal trains

r

express trains, but nev er b

th.
A

train has a unique n um b er and an engineer.

Stations

are either express stops

r

lo cal stops, but nev er b

th.
A

station has a name (assumed unique) and an address.

All

lo cal trains stop at all stations.

Express

trains stop

nly

at express stations.

F
r

eac h train and eac h station the train stops at, there is a time. 6

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

SLIDE 8 In the Begining . . . (Historical data mo dels: Net w

rk

and Hierarc hical)

The

mid 1960's sa w the rst systems that used secondary storage for querying = retriev al b y v alue, not b y le name.

Big

dierence: se c

ndary

stor age mo del

f

data = data in blo c ks/pages, and ma jor cost is retrieving

r

storing a blo ck.

Unsolve

d pr

blem

: Storing man y-man y relationships so they can b e tra v ersed ecien tly in b

th

directions.

✦

Easy in RAM mo del: link ed lists

f

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

n
ne
r

a few blo c ks. A.B. BudLite Bud Mic helob ... ... 8

SLIDE 9 Net w

rk

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

man

y-one relationships.

En

tit y set ! L

gic

al R e c

r

d T yp e (LR T).

Man

y-one relationship ! Link.

✦

T erminology useful to this da y:

wner

=

ne,

memb er = man y , e.g., a man ufacturer record \o wns" b eer records. Beers(name,manf ) Bars(name,addr) BB(price) TheBeer TheBar 9

SLIDE 10 Hierarc hical Mo del Used in ma jor early DBMS's, including IBM's IMS, whic h is still supp

rted

to da y .

Net

w

rk

mo del, restricted to a forest, where

wners

are paren ts

f

c hildren.

Adds

Virtual LR T to handle man y-man y relationships.

✦

Think

f

V. LR T as represen ting p

in

ters. V.Beers V.Drink ers Bars Beers Drink ers V.Bars V.Beers V.Bars V.Drink ers 10

SLIDE 11 T ypical Bar Record Jo e'sBar P

in

ters P

in

ters to Beer records to Drink er records

No

help in secondary storage mo del when going from a bar to either its b eers

r

its drink ers. 11

SLIDE 12 Example Where Hierarc hical Mo del Wins Depts Emplo y ees Budget items Pro jects

T

ypical stored records mak e ecien t queries that go Dept ! Budget Item

r

Pro ject ! Emplo y ees. Pro j2 Dept1 Pro j1 E11 E12 E13 E21 E22 BI1 BI2 BI3 12