Chapter 8: The Enhanced Entity-Relationship Entity-Relationship (EER) Model
CHAPTER 8: THE ENHANCED ENTITY-RELATIONSHIP (EER) MODEL Answers to Selected Eerc!ses 8"#$ – 8"#$ – No solution provided.
8"#% 8"#% - Consider the BN! ER sche"a o# $i%ure &.' and suppose that it is necessary to *eep trac* o# di##erent types o# CC+,NT ( (/N01CCT /N01CCT C2EC!/N01CCT ...) and 3+N (CR13+N 2+ME13+N ...). uppose that it is also desira4le to *eep trac* o# each account5s TRNCT/+Ns (deposits 6ithdra6als chec*s ...) and each loan5s 7MENTs 7MENTs99 4oth o# these include the a"ount date ti"e ... Modi#y the BN! sche"a usin% ER and EER concepts o# specialiation and %eneraliation. tate any assu"ptions you "a*e a4out the additional re;uire"ents. Answer:
8"#8 8"#8 - No solution provided. 8"#& 8"#& - /denti#y all the i"portant concepts represented in the li4rary data4ase case study descri4ed here. /n particular identi#y the a4straction o# classi#ication (entity types and relationship types) a%%re%ation identi#ication and specialiation<%eneraliation. peci#y ("in "a=) cardinality constraints 6henever possi4le. 3ist details that 6ill a##ect the eventual desi%n 4ut 6hich have no 4earin% on the conceptual desi%n. 3ist the se"antic separately. >ra6 an EER dia%ra" o# the li4rary data4ase. Answer:
Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley.
'
Chapter 8: The Enhanced Entity-Relationship (EER) Model
8"' - No solution provided. 8"'# - $i%ure 8.' sho6s an e=a"ple o# an EER dia%ra" #or a s"all private airport data4ase that is used to *eep trac* o# airplanes their o6ners airport e"ployees and pilots. $ro" the re;uire"ents #or this data4ase the #ollo6in% in#or"ation 6as collected: Each airplane has a re%istration nu"4er ?Re%@A is o# a particular plane type ?o#1typeA and is stored in a particular han%ar ?stored1inA. Each plane1type has a "odel nu"4er ?ModelA a capacity ?CapacityA and a 6ei%ht ?ei%htA. Each han%ar has a nu"4er ?Nu"4erA a capacity ?CapacityA and a location ?3ocationA. The data4ase also *eeps trac* o# the o6ners o# each plane ?o6nsA and the e"ployees 6ho have "aintained the plane ?"aintainA. Each relationship instance in o6ns relates an airplane to an o6ner and includes the purchase date ?7dateA. Each relationship instance in "aintain relates to an e"ployee to a service record ?serviceA. Each plane under%oes service "any ti"es9 hence it is related 4y ?plane1serviceA o a nu"4er o# service records. service record includes as attri4utes the date o# "aintenance ?>ateA the nu"4er o# hours spent on the 6or* ?2oursA and the type o# 6or* done ?or*codeA. e use a 6ea* entity type ?serviceA to represent airplane service 4ecause the airplane re%istration nu"4er is used to identi#y a service record. n o6ner is either a person or a corporation. 2ence 6e use a union type (cate%ory) ?o6nerA that is a su4set o# the union o# corporation ?CorporationA and person ?7ersonA entity types. Both pilots 7ilotA and e"ployees ?E"ployeeA are su4classes o# person. Each pilot has speci#ic attri4utes license nu"4er ?3ic1Nu"A and restrictions ?RestrA9 each e"ployee has speci#ic attri4utes salary ?alaryA and shi#t hi#tA. ll Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley.
Chapter 8: The Enhanced Entity-Relationship (EER) Model
D
person entities in the data4ase have data *ept on their social security nu"4er ?snA na"e ?Na"eA address ?ddressA and telephone nu"4er ?7honeA. $or corporation entities the data *ept includes na"e ?Na"eA address ?ddressA and telephone nu"4er ?7honeA. The data4ase also *eeps trac* o# the types o# planes each pilot is authoried to #ly ?$liesA and the types o# planes each e"ployee can do "aintenance 6or* on ?or*s1onA. ho6 ho6 the s"all airport EER sche"a o# $i%ure 8.' "ay 4e represented in ,M3 notation. (Note: e have not discussed ho6 to represent cate%ories (union types) in ,M3 so you do not have to "ap the cate%ories in this and the #ollo6in% ;uestion.) Answer:
8"'' 8"'* – No solutions provided.
8"'+ - >ra6 a ,M3 dia%ra" #or storin% a played %a"e o# chess in a data4ase. ou "ay loo* at http:<<666.chess%a"es.co" #or an application si"ilar to 6hat you are desi%nin%. tate Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley.
Chapter 8: The Enhanced Entity-Relationship (EER) Model
clearly any assu"ptions you "a*e in your ,M3 dia%ra". sa"ple o# assu"ptions you can "a*e a4out the scope is a #ollo6s: . The %a"e o# chess is played 4et6een t6o players. '. The %a"e is played on an 8=8 4oard li*e the one sho6n 4elo6:
D. The players are assi%ned a color o# 4lac* or 6hite at the start o# the %a"e. . Each player starts 6ith the #ollo6in% pieces (traditionally called chess"en): a. *in% 4. ;ueen c. ' roo*s d. ' 4ishops e. ' *ni%hts #. 8 pa6ns F. Every piece has its o6n initial position. G. Every piece has its o6n set o# le%al "oves 4ased on the state o# the %a"e. ou do not need to 6orry a4out 6hich "oves are le%al and 6hich are not other than the #ollo6in% issues: a. piece "ay "ove to an e"pty s;uare or capture an opposin% piece. 4. /# a piece is captured it is re"oved #ro" the 4oard. c. /# a pa6n "oves to the last ro6 it is Hpro"otedI 4ut convertin% it into another piece (a ;ueen a roo* a 4ishop or a *ni%ht). Note: o"e
o# these #unctions "ay 4e spread over "ultiple classes.
8"', – No solution provided. 8"'$ - hich o# the #ollo6in% EER dia%ra"(s) is
E1 a)
E
o 1
A
E2
R
N
E3 B
Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley.
Chapter 8: The Enhanced Entity-Relationship (EER) Model
F
E1 E
b)
d
R E2
A
o
c) E1
E3 M
R
N
A
C
Answer:
+nly (c) is incorrect.
8"'% - Consider the #ollo6in% EER dia%ra" that descri4es co"puter syste"s at a co"pany. 7rovide your o6n attri4utes and *ey #or each entity type. upply "a= cardinality constraints Kusti#yin% your choice. rite a co"plete narrative description o# 6hat this EER dia%ra" represents. (nsta%%ed
Softare
Computer (nsta%%ed!S
d
, Laptop
So%d&it'
Desktop
!peratin" System
Supports !ptions
!ptions
Accessory
Component
d
d
, Keyboard
,
,
, ,
Monitor
, Mouse
Memory
,
,
$ideo card
Sound card
Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley.
G
Chapter 8: The Enhanced Entity-Relationship (EER) Model
Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley.
