SolutionstoExercises文档格式.docx

1、1. The Addresses entity set is nothing but a single address, so we would prefer to make address an attribute of Customers. Were the bank to record several addresses for a customer, then it might make sense to have an Addresses entity set and make Lives-at a many-many relationship. 2. The Acct-Sets e

2、ntity set is useless. Each customer has a unique account set containing his or her accounts. However, relating customers directly to their accounts in a many-many relationship conveys the same information and eliminates the account-set concept altogether. Exercise 2.2.2: Under what circumstances （re

3、garding the unseen attributes of Studios and Presidents） would you recommend combining the two entity sets and relationship in Fig. 2.3 into a single entity set and attributes? When there is exactly one Studios and exactly one President, we can combine the two entity sets into a single entity set an

4、d the president can be one attribute of entity set Studios.Exercise 2.3.1: For your E/R diagrams of: a） Exercise 2.1.1.（i） Select and specify keys, and （ii） Indicate appropriate referential integrity constraints.P.53Keys ssNo and number are appropriate for Customers and Accounts, respectively. Also,

5、 we think it does not make sense for an account to be related to zero customers, so we should round the edge connecting Owns to Customers. It does not seem inappropriate to have a customer with 0 accounts; they might be a borrower, for example, so we put no constraint on the connection from Owns to

6、Accounts. Here is the E/R diagram, showing underlined keys and the numerocity constraint.Exercise 2.4.1: One way to represent students and the grades they get in courses is to use entity sets corresponding to students, to courses, and to “enrollments”, Enrollment entities form a “connecting” entity

7、set between students and courses and can be used to represent not only the fact that a student is taking a certain course, but the grade of the student in the course. Draw an E/R diagram for this situation, indicating weak entity sets and the keys for the entity sets. Is the grade part of the key fo

8、r enrollment? P.58Here is the E/R diagram. We have omitted attributes other than our choice for the key attributes of Students and Courses. Also omitted are names for the relationships. Attribute grade is not part of the key for Enrollments. The key for Enrollements is studID from Students and dept

9、and number from Courses.Chapter 3 The Relational Data ModelExercise 3.1.1: In Fig. 3.3 are instances of two relations that might constitute part of a banking database. Indicate the following: P.64a） The attribute of each relation.Accounts: acctNo, type, balance Customers: firstName, lastName, idNo,

10、accountb） The tuples of each relation. Accounts: （12345, savings, 12000）, （23456, checking, 1000）, （34567, savings, 25） （Robbie, Banks, 901-222, 12345）, （Lena, Hand, 805-333, 12345）, （Lena, Hand, 805-333, 23456）c） The components of one tuple from each relations. The first of the three tuples has thr

11、ee components 12345, savings, and 12000 for attributes acctNo, type, and balance of relation Accounts. The first of the three tuples has four components Robbie, Banks, 901-222, and 12345 for attributes firstName, lastName, idNo, and account of relation Customers.d） The relation schema for each relat

12、ion. The relation schema for Accounts: Accounts（acctNo, type, balance）The relation schema for Customers: Customers（firstName, lastName, idNo, account）e） The database schema. The database schema is Accounts（acctNo, type, balance）, and Customers（firstName, lastName, idNo, account）f） A suitable domain

13、for each attribute.For Accounts: acctNo string;type string;balance real For Customers: firstName string; lastName string; idNo string; account stringg） Another equivalent way to present each relation. For Accounts:acctNoTypeBalance12345Savings1200034567savings2523456checking1000firstNamelastNameidNo

14、accountRobbieBanks901-222LenaHand805-333Exercise 3.1.2: How many different ways （considering orders of tuples and attributes） are there to represent a relation instance if that instance has three attributes and three tuples. tuples: 3!=6 columns: number of presentation is 6*6=36Exercise 3.2.1: Conve

15、rt the E/R diagram of Fig. 3.11 to a relational database schema. P.75Customers（ssNo, name, addr, phone）Flights（number, day, aircraft）Bookings（ssNo, number, day, row, seat）Being a weak entity set, Bookings relation has the keys for Customers and Flights and Bookings own attributes.Exercise 3.2.3: The

16、 E/R diagram of Fig. 3.12 represents ships. Ships are said to be sisters if they were designed from the same plans. Convert this diagram to a relational database schema.Ships（name, yearLaunched）SisterOf（name, sister_name）Exercise 3.3.1: convert the diagram of Fig. 3.14 to relational database schema,

17、 using each of the following approaches: P.80 a） The straight-E/R method. b） The object-oriented method. c） The nulls method.Since Courses is weak, its key is number and the name of its department. We do not have a relation for GivenBy. In part （a）, there is a relation for Courses and a relation for

18、 LabCourses that has only the key and the computer-allocation attribute. It looks like: Depts（name, chair） Courses（number, deptName, room） LabCourses（number, deptName, allocation）For part （b）, LabCourses gets all the attributes of Courses, as: LabCourses（number, deptName, room, allocation）And for （c

19、）, Courses and LabCourses are combined, as: Courses（number, deptName, room, allocation）Exercise 3.4.2: Consider a relation representing the present position of molecules in a closed container. The attributes are an ID for the molecule, the x, y, and z coordinates of the molecule, and its velocity in

20、 the x, y, and z dimensions. What FDs would you expect to hold? What are the keys? P.89 Surely ID is a key by itself. However, we think that the attributes x, y, and z together form another key. The reason is that at no time can two molecules occupy the same point. ID x y z ID vx, vy, vz x y z vx, v

21、y, vzExercise 3.4.4: In your database schema constructed for Exercise 3.2.1, indicate the keys you would expect for each relation.The key attributes are indicated by capitalization in the schema below: Customers（SSNO, name, address, phone） Flights（NUMBER, DAY, aircraft） Bookings（SSNO, NUMBER, DAY, r

22、ow, seat）Exercise 3.5.1: consider a relation with schema R（A, B, C, D） and FDs AB C, C D, and D A. P.100a） What are all the nontrivial FDs that follow from the given FDs? You should restrict yourself to FDs with single attributes on the right side.b） What are all the keys of R?c） What are all the su

23、perkey for R that are not keys?For （a）, We could try inference rules to deduce new dependencies until we are satisfied we have them all. A more systematic way is to consider the closures of all 15 nonempty sets of attributes. For the single attributes we have A+ = A, B+ = B, C+ = ACD, and D+ = AD. T

24、hus, the only new dependency we get with a single attribute on the left is C-A. Now consider pairs of attributes:AB+ = ABCD, so we get new dependency AB-D. AC+ = ACD, and AC-D is nontrivial. AD+ = AD, so nothing new. BC+ = ABCD, so we get BC-A, and BC-D. BD+ = ABCD, giving us BD-A and BD-C. CD+ = AC

25、D, giving CD-For the triples of attributes, ACD+ = ACD, but the closures of the other sets are each ABCD. Thus, we get new dependencies ABC-D, ABD-C, and BCD-Since ABCD+ = ABCD, we get no new dependencies. The collection of 11 new dependencies mentioned above is: C-A, AB-D, AC-D, BC-A, BC-D, BD-A, BD-C, CD-A, ABC-For （b）, From the analysis of closures above, we find that AB, BC, and BD are keys. All other sets either do not have ABCD as the closure or contain one of these three sets.For （c）, The superkeys are all those that contain one of those thre