數(shù)據(jù)庫系統(tǒng)基本教學教程第四章答案解析

1、SolutionsChapter .2a)(33Nc5 J( Name 1y 1balanceCu st-omera | 程e count aHr phone 1 C addxeaa 丿f type Jb)c)In c we assume that a phone and address can only bel ong to a sin gle customer (1-m relati on shi prep rese nted by arrow into customer).CustomersphoneOfown 3niinnberAccountabalancftPhLo

2、nesAd_res3e33ta ted)In d we assume that an address can only belong to one customer and a phone can exist at only one address.If the multiplicity of above relationships were m-to-n, the entity set becomes weak and the key ssNo of customers will be needed as part of the composite key of the entity set

3、.In c&d, we convert attributes phones and addresses to en tity sets. Since en tity sets often become relati ons in relati onal desig n.we must con sider more efficie nt alter natives.In stead of query ing mult iple tables where key values are dup licated, we can also modify attributes: (i) Phones at

4、tribute can be con verted into Home Phone, Office Phone and Cell Phone.(ii) A multivalued attribute such as alias can be kept as an attribute where a sin gle colu mn can be used in relati onal desig n i.e. con cate nate all values. SQL allows a query like %Ju ni us% to searchthe multi ple values in

5、a colu mn alias.C呂曰 UCJJf Mame )Cus-tomersadcLreasOfownsniuftbsrAccountsbalancephoneAtsPhoneaZldje3 3-&a I_ _ _areacode.4a)b)c)The relati onship p layed betwee n Teams and P layers is similar to relati on shi p p lays betwee nTeams and Pl ayers..6 The in formatio n about childre n ca

6、n be ascerta ined from motherOf and fatherOf relati onships.Attribute ssNo is required since n ames are not uni que.4.1.8a)motherfather4.1.9Assu mp ti onsA P rofessor only works in at most one dep artme nt.A course has at most one TA.A course is only taught by one p rofessor and offered by one dep a

7、rtme nt.Stude nts and p rofessors have bee n assig ned uni que email ids.A course is uni quely ide ntified by the course no, secti on no, and semester (e.g. cs157-3 sp ri ng 09).4.1.10Given that for each movie, a unique studio exists that produces the movie. Each star is contracted to at most one st

8、udio.But stars could be unemployed at a given time. Thus the four-way relationship in fig 4.6 can be easily into converted equivalent relationships.Redu ndan cy: The owner address is rep eated in AccSets and Addresses en tity sets.421Simp licity: AccSets does not serve any useful purp ose and the de

9、sig n can be more simply rep rese nted by creati ng man y-to-ma ny relati on shi p betwee n Customers and Accou nts.Right kind of eleme nt: The en tity set Addresses has a sin gle attribute address. A customer cannot have more tha n one address.Hence address should be an attribute of en tity set Cus

10、tomers.Faithfu ln ess: Customers cannot be uni quely ide ntified by their n ames. In real world Customers would have a uni que attribute such as ssNo or customerNo4.2.2Studios and P reside nts can be comb ined into one en tity set Studios with Pr eside nts beco ming an attribute of Studios un der fo

11、llow ing circumsta nces: 1. The Pr eside nts en tity set only contains a sim ple attribute vi z. p reside ntName. Additi onal attributes sp ecific to Preside nts might justify making Preside nts into an en tity set.4.2.4 The en tity sets should have sin gle attribute.a) Stars: starNameb) Movies: mov

12、ieNamec) Studios: studioName. However there exists a man y-to-ma ny relati onship betwee n Studios andCon tracts. Hen ce, in additi on, we n eed more in formati on about studios in volved. If a con tract always in volves two studios, two attributes such as pr oduc in gStudio and starStudio can rep l

13、ace theStudios en tity set. If a con tact can be associated with at most five studios, it may be p ossible to rep lace the Studios en tity set by five attributes viz. studio1, studio2, studio3, studio4, and studio5.Alter nately, a comp osite attribute containing con cate nati on of all studio n ames

14、 in a con tact can be con sidered. A sep arator character such as $ can be used. SQL allows search ing of such an attribute using query like %keyword%425From Augme ntati on rule of Fun cti onal Dependency,give nB - M(B=Baby, M=Mother)the nBND - M (N=Nurse, D=Doctor)Hence we can just put an arrow en

15、teri ng mother.a) Put an arrow en teri ng en tity set Mothers for the simp lest soluti on (As in fig. 4.4, where a multiway relati onship was allowed, eve n though Movies alone could ide ntify the Studio). However, we can dis play more accurate in formatio n with below figure.c)Again from Augmentati

16、on rule of Functional Dependency, givenBM - D thenBMN - DThus we can just add an arrow en teri ng Doctors to fig 4.15. Below figure repr ese nts more accurate426b) Tran sitivity and Augme ntati on rules of Fun cti onal Dependency allow arrow en teri ng Mothersfrom Births. However, a new relati onshi

17、p in below figure rep rese nts more accurate in formati on.c)Desig n flaws in abc above 1. As suggested above, using Tran sitivity and Augme ntati on rules ofFun cti onal Dependen cy, much simpler desig n is po ssible.427In below figure there exists a many-to-one relati on shi p betwee n Babies and

18、Births and ano ther many-to-one relati onship betwee n Births and Mothers. From tran sitivity of relatio nshi ps, there is amany-to-one relati onship betwee n Babies and Mothers. Hence a baby has a uni que mother while a4.3.1a)b)A cap tai n cannot exist without a team. However a pl ayer can (free ag

19、e nt). A recen tly formed (or defu net) team can exist without p layers or colors.c)Childre n can exist without mother and father (u nknown).4.3.2a)The keys of both E1 and E2 are required for uni quely ide ntify ing tup les in Rb)The key of E1c)The key of E2d)The key of either E1 or E24.3.3Sp ecial

20、Case: All en tity sets have arrows going into them i.e. all relati onships are 1-to-1Any KiOtherwise: Comb in ati on of all Kis where there does not exist an arrow going from R to Ei.441No, grade is not part of the key for en rollme nts. The keys of Stude nts and Courses become keys of4.4.2It is p o

21、ssible to make assig nment n umber a weak key of Enr ollme nts but this is not good desig n (redu ndancy since mult iple assig nments corres pond to a course). A new en tity set Assig nment is created and it is also a weak en tity set. Hence the key attributes of Assig nment will come from the stro

22、ng en tity sets to which Enr ollme nts is conn ected i.e. stude ntID, dept, and CourseNo.443a)b)c)a)444b)4.5.1Customers(SSNo,name,addr,phone)Flights(number,day,aircraft)Bookings(custSSNo,flightNo,flightDay,row,seat)Relations for toCust and toFlt relationships are not required since the weak entity s

23、et Bookings already contains the keys of Customers and Flights.4.5.2(a)SSHqSSMotoCuatVnameaddx# I Bookings IFlightsmunBei:seattoFlttoCQStCustomersFlightanumbeT：(b)Schema is cha nged. Since toCust is no Ion ger an ide ntify ing relati on shi p, SSNo is no Ion ger a p art ofBook ings relati on.Booki n

24、gs(flightNo,flightDay,row,seat)ToCust(custSSNO,flightNo,flightDay,row,seat)The above relati ons are merged intoBookings(flightNo,flightDay,row,seat,custSSNo)However custSSNo is no longer a key of Bookings relation. It becomes a foreign key instead.4.5.3Ships(name, yearLaunched)SisterOf(name, sisterN

25、ame)4.5.4(a)Stars(name,addr)Studios(name,addr)Movies(title,year,length,genre)Contracts(starName,movieTitle,movieYear,studioName,salary)Depending on other relationships not shown in ER diagram, studioName may not be required as a key of Contracts (or not even required as an attribute of Contracts).(b

26、)Students(studentID)Courses(dept,courseNo)Enrollments(studentID,dept,courseNo,grade)(c)Departments(name)Courses(deptName,number)(d)Leagues(name)Teams(leagueName,teamName)Players(leagueName,teamName,playerName)4.6.1The weak relation Courses has the key from Depts along with number. Hence there is no

27、relation forGivenBy relationship.(a)Depts(name, chair)Courses(number, deptName, room)LabCourses(number, deptName, allocation)(b) LabCourses has all the attributes of Courses.Depts(name, chair)Courses(number, deptName, room)LabCourses(number, deptName, room, allocation)(c) Courses and LabCourses are

28、combined into one relation.Depts(name, chair)Courses(number, deptName, room, allocation)4.6.2(a)Person(name,address)ChildOf(personName,personAddress,childName,childAddress)Child(name,address,fatherName,fatherAddress,motherName,motherAddresss)Father(name,address,wifeName,wifeAddresss)Mother(name,addr

29、ess)Since FatherOf and MotherOf are many-one relationships from Child, there is no need for a separate relation for them. Similarly the one-one relationship Married can be included in Father (orMother). ChildOf is a many-many relationship and needs a separate relation.However the ChildOf relation is

30、 not required since the relationship can be deduced from FatherOf and MotherOf relationships contained in Child relation.(b)A person cannot be both Mother and Father.Person(name,address)PersonChild(name,address)PersonChildFather(name,address)PersonChildMother(name,address)PersonFather(name,address)P

31、ersonMother(name,address)ChildOf(personName,personAddress,childName,childAddress)FatherOf(childName,childAddress,fatherName,fatherAddress)MotherOf(childName,childAddress,motherName,motherAddress)Married(husbandName,husbandAddress,wifeName,wifeAddress)The many-many ChildOf relationship again requires

32、 a relation.An entity belongs to one and only one class when using object-oriented approach. Hence, the many-one relations MotherOf and FatherOf could be added as attributes toPersonChild,PersonChildFather, and PersonChildMother relations.Similarly the Married relation can be added as attributes to

33、PersonChildMother and PersonMother (or the corresponding father relations).(c) For the Person relation at least one of husband and wife attributes will be null.Person(personName,personAddress,fatherName,fatherAddress,motherName,motherAddresss,wifeN ame,wifeAddresss,husbandName,husbandAddress)ChildOf

34、(personName,personAddress,childName,childAddress)4.6.3 (a)People(name,fatherName,motherName)Males(name)Females(name)Fathers(name)Mothers(name)ChildOf(personName,childName) (b)People(name)PeopleMale(name)PeopleMaleFathers(name)PeopleFemale(name)PeopleFemaleMothers(name)ChildOf(personName,childName)Fa

35、therOf(childName,fatherName)MotherOf(childName,motherName)People cannot belong to both male and female branch of the ER diagram.Moreover since an entity belongs to one and only one class when using object-oriented approach, no entity belongs to People relation.Again we could replace MotherOf and Fat

36、herOf relations by adding as attributes toPeopleMale,PeopleMaleFathers,PeopleFemale, and PeopleFemaleMothers relations.(c)People(name,fatherName,motherName)ChildOf(personName,childName)4.6.4 (a)Each entity set results in one relation. Thus both the minimum and maximum number of relations is e.The ro

37、ot relation has a attributes including k keys. Thus the minimum number of attributes is a. All other relations include the k keys from root along with their a attributes. Thus the maximum number of attributes is a+k.(b)The relati on for root will have a attributes. The relati on repr ese nti ng the

38、whole tree will have e*a attributes.The n umber of relati ons will depend on the sha pe of the tree. A tree of e en tities where only one child exists(say left child only) would have the mini mum n umber of relati ons. Thus below figure will only con tain 4 subtrees that con tain root E1,E1E2,E1E2E3

39、, and E1E2E3E4. With e en tity sets, mi ni mum e relati ons are po ssible.The maximum n umber of subtrees result whe n all the en tities(exce pt root) are at depth 1. Thus below figure will c on tain 8 subtrees that co ntain root E1,E1E2,E1E3,E1E4,E1E2E3,E1E3E4,E1E2E4,a ndE1E2E3E4. With e en tity se

40、ts, maximum 2(e-1) relati ons are po ssible.attributes from all e en tities i.e. e*a(C)The n ulls method always results in one relati on and contains attributes.Summariz ing for a,b, and c above;#Co mponents#Relati onsMinMaxMinMaxMethod straight-E/Robject-orie ntede*a2A(e-1)nullse*ae*a.2a)b)

41、c)d)4.7.3D由p lays1.0. . *0. .1Havers0. .*0*TeamsjJ.lbCapt。丄星+EiajrLe : PKO Plays0. .*Roots For0*Fans0, ,*+nfeline * PK+ UQ lomaiTbe :PRAdmiresFavors4.7.4People+33Nb: PR-hname1MotherOfFatherOf4.7.5Males and Females subclasses are compi ete. Mothers and Fathers are p artial. All subclasses are disjoi

42、nt..7Shi噲麗+name: PE.血 shadoriginal Ship4.7.8We con vert the ternary relati on shi p Con tracts into three binary relati on shi ps betwee n a new en tity set Con tracts and existi ng en tity sets.StarsMovies+rLaiiie: PR+口円円+title: PR +yeai: PK length +crenrea,Contracts o.ITT+aala工yStudios+nai

43、ne: PRaddre3s4.7.9a)b)c)4.7.10A self-associati on Pare ntOf for en tity set people has mult ip licity 0.2 at parent role end.In a Library database, if a p atr on can loa n at most 12 books, them mult ip licity is 0.12.For a FullTimeStude nts en tity set, a relati onship of mult ip licity 5.* must ex

44、ist with Courses (A stude nt must take at least 5 courses to be classified FullTime.4.8.1Customers(SSNo, name,addr, phone)Flights (n umber,day,aircraft)Booki ngs(row,seat,custSSNo,FlightNumber,FlightDay)Customers(SSNo, name,addr, phone)Flights( number,day,aircraft)Booki ngs(row,seat,custSSNo,FlightN

45、umber,FlightDay)4.8.2a)Movies(title,year,length,genre)Studios(name,address)Presidents(cert#,name,address)Owns(movieTitle,movieYear,studioName)Runs(studioName,presCert#)Movies(title,year,length,genre)Studios(name,address)Presidents(cert#,name,address)Owns(movieTitle,movieYear,studioName)Runs(studioNa

46、me,presCert#)b)Since the subclasses are disjoint, Object Oriented Approach is used.The hierarchy is not complete. Hence four relations are requiredMovies(title,year,length,genre)MurderMysteries(title,year,length,genre,weapon)Cartoons(title,year,length,genre)Cartoon-MurderMysteries(title,year,length,

47、genre,weapon)Movies(title,year,length,genre)MurderMysteries(title,year,length,genre,weapon)Cartoons(title,year,length,genre)Cartoon-MurderMysteries(title,year,length,genre,weapon)c)Customers(ssNo,name,phone,address)Accounts(number,balance,type)Owns(custSSNo,accountNumber)Customers(ssNo,name,phone,ad

48、dress)Accounts(number,balance,type)Owns(custSSNo,accountNumber)d)Teams(name,captainName)Players(name,teamName)Fans(name,favoriteColor)Colors(colorname)For Displays association,TeamColors(teamName,colorname)RootsFor(fanName,teamName)Admires(fanName,playerName)Teams(name,captainName)Players(name,teamN

49、ame)Fans(name,favoriteColor)Colors(colorname)For Displays association,TeamColors(teamName,colorname)RootsFor(fanName,teamName)Admires(fanName,playerName)e)People(ssNo,name,fatherSSNo,motherSSNo)People(ssNo,name,fatherssNo,motherssNo)f)Students(email,name)Courses(no,section,semester,professorEmail)De

50、partments(name)Professors(email,name,worksDeptName)Takes(letterGrade,studentEmail,courseNo,courseSection,courseSemester)Students(email,name)Courses(no,section,semester,professorEmail)Departments(name)Professors(email,name,worksDeptName)Takes(letterGrade,studentEmail,courseNo,courseSection,courseSemester)4.8.3a)Each and every object is a member of exactly one subclass at leaf level. We have nine classes at the leaf of hierarchy. Hence we need nine relations.b)All objects only belong to one subclass and its ancestors. Hence, we need not consider every possib