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

with man y man y w e w ouldn t kno w whic h en tit y pro
SMART_READER_LITE
LIVE PREVIEW

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

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.


slide-1
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
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
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
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
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
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
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
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
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
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
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
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
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