[PDF] - With man y-man y , w e w ouldn't kno w whic h en tit PDF Document

SLIDE 1 W eak En tit y Sets Sometimes an E.S. E 's k ey comes not (completely) from its

wn

attributes, but from the k eys

f
ne
r

more E.S's to whic h E is link ed b y a supp

rting

man y-one relationship.

Called

a we ak E.S.

Represen

ted b y putting double rectangle around E and a double diamond around eac h supp

rting

relationship.

Man

y-one-ness

f

supp

rting

relationship (includes 1-1) essen tial.

✦

With man y-man y , w e w

uldn't

kno w whic h en tit y pro vided the k ey v alue.

\Exactly
ne"

also essen tial,

r

else w e migh t not b e able to extract k ey attributes b y follo wing the supp

rting

relationship. 1

SLIDE 2 Example: Logins (Email Addresses) Login name = user name + host name, e.g., ullman@shalmaneser .stan ford. edu.

A

\login" en tit y corresp

nds

to a user name

n

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

nly)

+ the IP address

f

the host (whic h is unique globally) . @ Logins Hosts name name

Design

issue: Under what circumstances could w e simply mak e login-name and host-name b e attributes

f

logins, and disp ense with the w eak E.S.? 2

SLIDE 3 Example: Chain

f

\W eakness" Consider IP addresses consisting

f

a primary domain (e.g., edu) sub domain (e.g., stanford), and host (e.g. shalmaneser). name name name Primary Domains In1 In2 Hosts Domains 2ndary

Key

for primary domain = its name.

Key

for secondary domain = its name + name

f

primary domain.

Key

for host = its name + k ey

f

secondary domain = its name + name

f

secondary domain + name

f

primary domain. 3

SLIDE 4 All \Connecting" En tit y Sets Are W eak Beers Bars The- Beer Prices Bar The- The- Price BBP name addr name manf price

In

this sp ecial case, where bar and b eer determine a price, w e can

mit

price from the k ey , and remo v e the double diamond from ThePrice.

Better:

price is attribute

f

BBP. 4

SLIDE 5 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 = sa ying the same thing more than

nce.
W

astes space and encourages inconsistency . Example Go

d:

Beers Manfs ManfBy name name addr 5

SLIDE 6 Bad: rep eats man ufacturer address for eac h b eer they man ufacture. name manf addr Beers manf Bad: man ufacturer's name said t wice. Beers Manfs ManfBy name name addr manf 6

SLIDE 7 Use Sc hema to Enforce Constrain ts The design schema should enforce as man y constrain ts as p

ssible.
Don't

rely

n

users 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. 7

SLIDE 8 En tit y Sets Vs. A ttributes Y

u

ma y b e unsure whic h concepts are w

rth

y

f

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 Beers Manfs ManfBy name Righ t: name Beers manf 8

SLIDE 9 In tuitiv e Rule for E.S. Vs. A ttribute Mak e an en tit y set

nly

if it either: 1. Is more than a name

f

something; i.e., it has nonk ey attributes

r

relationships with a n um b er

f

dieren t en tit y sets,

r

2. Is the \man y" in a man y-one relationship. 9

SLIDE 10 Example The follo wing design illustrate s b

th

p

in

ts: Beers Manfs ManfBy name name addr

Manfs

deserv es to b e an E.S. b ecause w e record addr, a nonk ey attribute.

Be

ers deserv es to b e an E.S. b ecause it is at the \man y" end.

✦

If not, w e w

uld

ha v e to mak e \set

f

b eers" an attribute

f

Manfs | something w e a v

id

doing, although some ma y tell y

u

it is OK in E/R mo del. 10

SLIDE 11 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
nly

times w eak E.S.'s seem necessary are when: a) W e can't easily create suc h ID's; e.g., no

ne

is going to accept a \sp ecies ID" as part

f

the standard nomenclature (sp ecies is a w eak E.S. supp

rted

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

SLIDE 12 Classro

m

Design Exercise Imagine w e are creating a database for a dorm, whic h includes a co

p

erativ e kitc hen.

W

e w an t to record certain information ab

ut

eac h residen t. What?

Not

all residen ts b elong to the kitc hen co

p.

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

ne

should ha v e t w

jobs
n
ne

da y . 2. They ma y

r

ma y not b e v egetarian. Eac h meal m ust ha v e at least

ne

v egetarian en try . 3. They pa y fees to the co

p.
F
r

eac h meal, there is a men u. Eac h men u item requires certain ingredien ts, whic h m ust b e

n

hand. 12

SLIDE 13 If There's Time

Supp
se

w e

nly

need to ha v e a v egetarian c hoice for a giv en meal if there is at least

ne

v egetarian taking that meal. Ho w w

uld

w e mo dify the database sc hema? 13