2023年数据库系统基础教程第四章超详细解析超详细解析答案.pdf

《2023年数据库系统基础教程第四章超详细解析超详细解析答案.pdf》由会员分享，可在线阅读，更多相关《2023年数据库系统基础教程第四章超详细解析超详细解析答案.pdf（35页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库系统基础教程第四章答案 Solutions Chapter 4 4、1、1 4、1、2 a)b)c)In c we assume that a phone and address can only belong to a single customer(1-m relationship represented by arrow into customer)、数据库系统基础教程第四章答案 d)In d we assume that an address can only belong to one customer and a phone can exist at only one add

2、ress、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、In c&d,we convert attributes phones and addresses to entity sets、Since entity sets often become relations in relational

3、design,we must consider more efficient alternatives、Instead of querying multiple tables where key values are duplicated,we can also modify attributes:(i)Phones attribute can be converted into HomePhone,OfficePhone and CellPhone、(ii)A multivalued attribute such as alias can be kept as an attribute wh

4、ere a single column can be used in relational design i、e、concatenate all values、SQL allows a query like%Junius%to search the multiple values in a column alias、据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据

5、库系统基础教程第四章答案数据库系统基础教程第四章答案 4、1、3 4、1、4 a)据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案 b)c)The relationship played between Teams and Players is similar to relationship plays betw

6、een Teams and Players、4、1、5 据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案 4、1、6 The information about children can be ascertained from motherOf and fatherOf relationships、Attribu

7、te ssNo is required since names are not unique、4、1、7 据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案 4、1、8 a)(b)据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第系统基础教程第四章答案数据库系统基

8、础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案 4、1、9 Assumptions A Professor only works in at most one department、A course has at most one TA、A course is only taught by one professor and offered by one department、Students and professors have been

9、assigned unique email ids、A course is uniquely identified by the course no,section no,and semester(e、g、cs157-3 spring 09)、4、1、10 Given that for each movie,a unique studio exists that produces the movie、Each star is contracted to at most one studio、据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第系

10、统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案 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、4、2、1 Redundancy:The owner address is repe

11、ated in AccSets and Addresses entity sets、Simplicity:AccSets does not serve any useful purpose and the design can be more simply represented by creating many-to-many relationship between Customers and Accounts、Right kind of element:The entity set Addresses has a single attribute address、A customer c

12、annot have more than one address、Hence address should be an attribute of entity set Customers、据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案 Faithfulness:Customers cannot be uniqu

13、ely identified by their names、In real world Customers would have a unique attribute such as ssNo or customerNo 4、2、2 Studios and Presidents can be combined into one entity set Studios with Presidents becoming an attribute of Studios under following circumstances:1、The Presidents entity set only cont

14、ains a simple attribute viz、presidentName、Additional attributes specific to Presidents might justify making Presidents into an entity set、4、2、3 4、2、4 The entity sets should have single attribute、a)Stars:starName b)Movies:movieName c)Studios:studioName、However there exists a many-to-many relationship

15、 between Studios and Contracts、Hence,in addition,we need more information about studios involved、If a contract always involves two studios,two attributes such as producingStudio and starStudio can replace the Studios entity set、If a contact can be associated with at most five studios,it may be possi

16、ble to replace the Studios entity set by five attributes viz、studio1,studio2,studio3,studio4,and studio5、Alternately,a composite attribute containing concatenation of all studio names in a contact can be considered、A separator character such as$can be used、SQL allows searching of such an attribute u

17、sing query like%keyword%4、2、5 From Augmentation rule of Functional Dependency,given B-M (B=Baby,M=Mother)then BND-M(N=Nurse,D=Doctor)Hence we can just put an arrow entering mother、a)Put an arrow entering entity set Mothers for the simplest solution(As in fig、4、4,where a multi-way relationship was al

18、lowed,even though Movies alone could identify the Studio)、However,we can display more accurate information with below figure、据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案 b)c)Aga

19、in from Augmentation rule of Functional Dependency,given BM-D then BMN-D 据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案 Thus we can just add an arrow entering Doctors to fig 4、15、

20、Below figure represents more accurate information however、4、2、6 a)b)Transitivity and Augmentation rules of Functional Dependency allow arrow entering Mothers from Births、However,a new relationship in below figure represents more accurate information、据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程

21、第系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案 c)Design flaws in abc above 1、As suggested above,using Transitivity and Augmentation rules of Functional Dependency,much simpler design is possible、4、2、7 In below figure there exists

22、 a many-to-one relationship between Babies and Births and another many-to-one relationship between Births and Mothers、From transitivity of relationships,there is a many-to-one relationship between Babies and Mothers、Hence a baby has a unique mother while a birth can allow more than one baby、据库系统基础教程

23、第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案 4、3、1 a)b)A captain cannot exist without a team、However a player can(free agent)、A recently formed(or defunct)team can exist without players

24、 or colors、c)Children can exist without mother and father(unknown)、据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案 4、3、2 a)The keys of both E1 and E2 are required for uniquely iden

25、tifying tuples in R b)The key of E1 c)The key of E2 d)The key of either E1 or E2 4、3、3 Special Case:All entity sets have arrows going into them i、e、all relationships are 1-to-1 Any Ki Otherwise:Combination of all Kis where there does not exist an arrow going from R to Ei、4、4、1 No,grade is not part o

26、f the key for enrollments、The keys of Students and Courses become keys of the weak entity set Enrollments、据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案 4、4、2 It is possible to ma

27、ke assignment number a weak key of Enrollments but this is not good design(redundancy since multiple assignments correspond to a course)、A new entity set Assignment is created and it is also a weak entity set、Hence the key attributes of Assignment will come from the strong entity sets to which Enrol

28、lments is connected i、e、studentID,dept,and CourseNo、据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案 4、4、3 a)b)据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第系统基础教程第四章答案数据库系统基础教

29、程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案 c)4、4、4 a)据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案 b)4、5、1 Customers(SSNo,n

30、ame,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 set Bookings already contains the keys of Customers and Flights、4、5、2 据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第系统基础教程

31、第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案(a)(b)Schema is changed、Since toCust is no longer an identifying relationship,SSNo is no longer a part of Bookings relation、Bookings(flightNo,flightDay,row,seat)ToCust(custSSNO,flightNo,flig

32、htDay,row,seat)The above relations are merged into Bookings(flightNo,flightDay,row,seat,custSSNo)However custSSNo is no longer a key of Bookings relation、It becomes a foreign key instead、4、5、3 Ships(name,yearLaunched)SisterOf(name,sisterName)4、5、4(a)Stars(name,addr)据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础

33、教程第四章答案数据库系统基础教程第系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案 Studios(name,addr)Movies(title,year,length,genre)Contracts(starName,movieTitle,movieYear,studioName,salary)Depending on other relationships not shown in ER diagram,st

34、udioName may not be required as a key of Contracts(or not even required as an attribute of Contracts)、(b)Students(studentID)Courses(dept,courseNo)Enrollments(studentID,dept,courseNo,grade)(c)Departments(name)Courses(deptName,number)(d)Leagues(name)Teams(leagueName,teamName)Players(leagueName,teamNam

35、e,playerName)4、6、1 The weak relation Courses has the key from Depts along with number、Hence there is no relation for GivenBy 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(nu

36、mber,deptName,room)LabCourses(number,deptName,room,allocation)(c)Courses and LabCourses are 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,fatherAddr

37、ess,motherName,motherAddresss)Father(name,address,wifeName,wifeAddresss)Mother(name,address)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(or Mother)、ChildOf is a

38、 many-many relationship and needs a separate relation、However the ChildOf relation is 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)Pers

39、onChildFather(name,address)PersonChildMother(name,address)PersonFather(name,address)PersonMother(name,address)ChildOf(personName,personAddress,childName,childAddress)FatherOf(childName,childAddress,fatherName,fatherAddress)MotherOf(childName,childAddress,motherName,motherAddress)Married(husbandName,

40、husbandAddress,wifeName,wifeAddress)The many-many ChildOf relationship again requires a relation、据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案 An entity belongs to one and only o

41、ne class when using object-oriented approach、Hence,the many-one relations MotherOf and FatherOf could be added as attributes to PersonChild,PersonChildFather,and PersonChildMother relations、Similarly the Married relation can be added as attributes to PersonChildMother and PersonMother(or the corresp

42、onding 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,wifeName,wifeAddresss,husbandName,husbandAddress)ChildOf(personName,personAddress,childName,childAddress)4、6

43、、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)FatherOf(childName,fatherName)MotherOf(childName,motherN

44、ame)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 FatherOf relations by adding as attributes to PeopleMale,P

45、eopleMaleFathers,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 root relation has a attributes including k keys、Thus the mi

46、nimum 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 relation for root will have a attributes、The relation representing the whole tree will have e*a attributes、The number of relations will d

47、epend on the shape of the tree、A tree of e entities where only one child exists(say left child only)would have the minimum number of relations、Thus below figure will only contain 4 subtrees that contain root E1,E1E2,E1E2E3,and E1E2E3E4、With e entity sets,minimum e relations are possible、据库系统基础教程第四章答

48、案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案 The maximum number of subtrees result when all the entities(except root)are at depth 1、Thus below figure will contain 8 subtrees that contain ro

49、ot E1,E1E2,E1E3,E1E4,E1E2E3,E1E3E4,E1E2E4,and E1E2E3E4、With e entity sets,maximum 2(e-1)relations are possible、(c)The nulls method always results in one relation and contains attributes from all e entities i、e、e*a attributes、Summarizing for a,b,and c above;#Components#Relations Min Max Min Max Metho

50、d straight-E/R a a e e object-oriented a e*a e 2(e-1)据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案 nulls e*a e*a 1 1 4、7、1 4、7、2 a)b)c)据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据