数据库系统基础教程第四章答案SolutionsChapter 44、1、 1In c we assume that a phone and address can only belong to a single customer (1-m relationship represented by arrow into customer)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、In c&d, we convert attributes phones and addresses to entity sets、 Since entitysets often become relations in relational 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 andCellPhone 、(ii) A multivalued attribute such as alias can be kept as an attribute where asingle column can be used in relational design i、e、concatenate all valuesSQL allows a query like %Junius% to search the multiple values in a column alias 、4、1、4 a)b)c)The relationship played between Teams and Players is similar to relationshipplays between Teams and Players、4、1、 54、1、6 The information about children can be ascertained from motherOf andfatherOf relationships、 Attribute ssNo is required since names are not unique4、1、7(b)FamilyPeople4、1、9AssumptionsA Professor only works in at most one departmentA course has at most one TAA course is only taught by one professor and offered by one department Students and professors have been assigned unique email idsA course is uniquely identified by the course no, section no, and semester (eGiven that for each movie, a unique studio exists that produces the movie、 Eachstar is contracted to at most one studio、数据库系统基础教程第四章答案But stars could be unemployed at a given time、 Thus the four-way relationshipRedundancy: The owner address is repeated 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 addressA customer cannot have more than one address、Hence address should be an attribute of entity set Customers数据库系统基础教程第四章答案Faithfulness: Customers cannot be uniquely identified by their names、 In realworld Customers would have a unique attribute such as ssNo or customerNo 4、2、2Studios and Presidents can be combined into one entity set Studios withPresidents becoming an attribute of Studios under following circumstances:1、 The Presidents entity set only contains a simple attribute viz、presidentName 、 Additional attributes specific to Presidents might justifymaking Presidents into an entity set、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 relationshipbetween Studios and Contracts、 Hence, in addition, we need more informationabout studios involved、 If a contract always involves two studios, twoattributes such as producingStudio and starStudio can replace theStudios entity set、 If a contact can be associated 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 compositeattribute containing concatenation of all studio names in a contact can be considered 、A separator character such as $ can be used、 SQL allows searchingof such an attribute using query like %keyword% 4、2、5From Augmentation rule of Functional Dependency, givenB - M (B=Baby, M=Mother) thenBND - 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 allowed, even though Movies alone could identify the Studio)、 However, we can display more accurate informationwith below figure、childofMothersNursesb)MothersBabiesNuraeaBabiesDoctorsDoctorsc)Again from Augmentation rule of Functional Dependency, given BM - D then BMN - D、15、 Below figureThus we can just add an arrow entering Doctors to fig 4a)b) Transitivity and Augmentation rules of Functional Dependency allow arrowentering Mothers from Births、 However, a new relationship in below figurerepresents more accurate informationDesign flaws in abc above 1、 As suggested above, using Transitivity andAugmentation rules of Functional Dependency, much simpler design is possible 4