数据库系统基础教程答案.docx

1、数据库系统基础教程答案SolutionsChapter 4 4.1.1 4.1.2a)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 address.If the multipl

2、icity 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 design,we must c

3、onsider 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 where a sin

4、gle 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. 4.1.3 4.1.4 a)b)c)The relationship played between Teams and Players is similar to relationship plays between Teams and Players. 4.1.5 4.1.6 The i

5、nformation about children can be ascertained from motherOf and fatherOf relationships. Attribute ssNo is required since names are not unique.4.1.74.1.8a)(b)4.1.9AssumptionsA Professor only works in at most one department.A course has at most one TA.A course is only taught by one professor and offere

6、d by one department.Students and professors have been 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.10Given that for each movie, a unique studio exists that produces the movie. Each star is contracted to at most one

7、studio. 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.1Redundancy: The owner address is repeated in AccSets and Addresses entity sets.Simplicity: AccSets does not serve any useful purpose and the des

8、ign 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 cannot have more than one address. Hence address should be an attribute of entity set Customers.Faithfuln

9、ess: Customers cannot be uniquely identified by their names. In real world Customers would have a unique attribute such as ssNo or customerNo4.2.2Studios and Presidents can be combined into one entity set Studios with Presidents becoming an attribute of Studios under following circumstances:1. The P

10、residents entity set only contains 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: starNameb) Movies: movieNamec) Studios: studioName. However the

11、re exists a many-to-many relationship 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 associate

12、d with at most five studios, it may be possible 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 $ c

13、an be used. SQL allows searching of such an attribute using query like %keyword%4.2.5From Augmentation rule of Functional Dependency,givenB - 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 simples

14、t solution (As in fig. 4.4, where a multi-way relationship was allowed, even though Movies alone could identify the Studio). However, we can display more accurate information with below figure. b)c) Again from Augmentation rule of Functional Dependency,givenBM - DthenBMN - D Thus we can just add an

15、arrow entering Doctors to fig 4.15. Below figure represents more accurate information however.4.2.6a)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.c)Design fla

16、ws 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 a many-to-one relationship between Babies and Births and another many-to-one relationship between Births and Mothers. From tra

17、nsitivity 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.4.3.1a)b)A captain cannot exist without a team. However a player can (free agent). A recently formed (or defunct) team can exist wit

18、hout players or colors.c)Children can exist without mother and father (unknown).4.3.2a)The keys of both E1 and E2 are required for uniquely identifying tuples in Rb)The key of E1c)The key of E2d)The key of either E1 or E24.3.3Special Case: All entity sets have arrows going into them i.e. all relatio

19、nships are 1-to-1 Any KiOtherwise: Combination of all Kis where there does not exist an arrow going from R to Ei.4.4.1No, grade is not part of 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 make assignment number a wea

20、k 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 Enrollments is connected i.e

21、. studentID, dept, and CourseNo. 4.4.3a)b)c)4.4.4a)b)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 set Bookings already contains the keys of Customers a

22、nd Flights.4.5.2(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,flightDay,row,seat)The above relations are merged intoBookings(flightNo,flightDay,row,seat,custS

23、SNo)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)Studios(name,addr)Movies(title,year,length,genre)Contracts(starName,movieTitle,movieYear,studioName,salary)Depending on oth

24、er 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)Students(studentID)Courses(dept,courseNo)Enrollments(studentID,dept,courseNo,grade)(c)Departments(name)Courses(deptName,number)(d)Leagues(name)Teams(l

25、eagueName,teamName)Players(leagueName,teamName,playerName)4.6.1The 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 al

26、l the attributes of Courses. Depts(name, chair) Courses(number, 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,person

27、Address,childName,childAddress)Child(name,address,fatherName,fatherAddress,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-o

28、ne relationship Married can be included in Father (or Mother). ChildOf is a 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 canno

29、t be both Mother and Father.Person(name,address)PersonChild(name,address)PersonChildFather(name,address)PersonChildMother(name,address)PersonFather(name,address)PersonMother(name,address)ChildOf(personName,personAddress,childName,childAddress)FatherOf(childName,childAddress,fatherName,fatherAddress)

30、MotherOf(childName,childAddress,motherName,motherAddress)Married(husbandName,husbandAddress,wifeName,wifeAddress)The many-many ChildOf relationship again requires a relation.An entity belongs to one and only one class when using object-oriented approach. Hence, the many-one relations MotherOf and Fa

31、therOf 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 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,wifeName,wifeAddresss,husbandName,husbandAddress)ChildOf(personName,personAddress,childName,childAddress)4.6.3(a)People