{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

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

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

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

View Full Document
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 Number Æ Name where name is not unique (i.e., there may be more than one “John Smith”, each with a different student number). Then the multivalued dependencies are: Number ÆÆ Club Number ÆÆ Sibling Number ÆÆ Nickname Note: We can not assume that Name ÆÆ Nickname because Name is not unique. For example, one John Smith may have the nickname “Johnny” while another John Smith has the nickname ”Joe.”
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}