7830TextProjectAnswersCh3

7830TextProjectAnswersCh3 - Chapter 3 ANSWERS TO PROJECT...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
Page 3-1 Chapter 3 ANSWERS TO PROJECT QUESTIONS 3.1 Consider the table: STAFF_MEETING (EmployeeName, ProjectName, Date) The rows of this table record the fact that an employee from a particular project attended a meeting on the given date. Assume that a project meets at most once per day. Also, assume that only one employee represents a given project, but that employees can be assigned to multiple projects. a. State the functional dependencies. Since there can only be one project meeting for a particular project per day, we have: (ProjectName, Date) Æ EmployeeName Since there is one only one employee assigned to the meetings for each project, we have: ProjectName Æ EmployeeName b. Transform this table into one or more tables in BCNF. State the primary keys, candidate keys, foreign keys, and referential integrity constraints. STAFF_MEETING FUNCTIONAL DEPENDENCIES: (ProjectName, Date) Æ EmployeeName ProjectName Æ EmployeeName STAFF_MEETING CANDIDATE KEYS: (ProjectName, Date) Is every determinant a candidate key? NO, therefore the relation is NOT in BCNF Therefore, move ProjectName Æ Employee into another table STAFF_MEETING_2 (ProjectName , Date ) STAFF_MEETING_EMPLOYEE ( ProjectName, EmployeeName) STAFF_MEETING_2 FUNCTIONAL DEPENDENCIES: (ProjectName, Date) Æ EmployeeName STAFF_MEETING_2 CANDIDATE KEYS: (ProjectName, Date) Is every determinant a candidate key? YES, therefore the relation is in BCNF
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Page 3-2 STAFF_MEETING_EMPLOYEE FUNCTIONAL DEPENDENCIES: ProjectName Æ EmployeeName STAFF_MEETING_2 CANDIDATE KEYS: ProjectName Is every determinant a candidate key? YES, therefore the relation is in BCNF The tables are now all in BCNF. FINAL SET OF TABLES: STAFF_MEETING_2 (ProjectName , Date ) STAFF_MEETING_EMPLOYEE ( ProjectName, EmployeeName) REFENTIAL INTEGRITY CONSTRAINTS: ProjectName in STAFF_MEETING_EMPLOYEE must exist in STAFF_MEETING_2. c. Is your design in part b an improvement over the original table? What advantages and disadvantages does it have? Yes, the design in part b is an improvement over the original table. The advantage is that is not subject to modification anomalies since all tables are in BCNF. The only disadvantage it has is that there must be staff meeting data entered (ProjectName and Date in STAFF_MEETING_2) before an EmployeeName can be entered in STAFF_MEETING_EMPLOYEE. This may seem illogical to someone entering the data. 3.2 Consider the table: STUDENT (Number, Name, Dorm, RoomType, DormCost, Club, ClubCost, Sibling, Nickname) Assume that students pay different dorm costs, depending on the type of room they have, but that all members of a club pay the same cost. Assume that students can have multiple nicknames. a. State any multivalued dependencies. We will assume that
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 21

7830TextProjectAnswersCh3 - Chapter 3 ANSWERS TO PROJECT...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online