3.58 Consider the table: STAFF_MEETING (EmployeeName, ProjectName, Date) The row

Question

3.58 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 a 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 in STAFF_MEETING.

B. Transform this table into one or more tables in BCNF. State the primary keys,

candidate keys, foreign keys, and referential integrity constraints.

C. Is your design in part B an improvement over the original table? What advantages

and disadvantages does it have?

3.59 Consider the table:

STUDENT (StudentNumber, StudentName, 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 in STUDENT.

B. State the functional dependencies in STUDENT.

C. Transform this table into two or more tables such that each table is in BCNF and in

4NF. State the primary keys, candidate keys, foreign keys, and referential integrity

constraints.

Explanation / Answer

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) ? NULL STAFF_MEETING_2 CANDIDATE KEYS: (ProjectName, Date) Is every determinant a candidate key? YES, therefore the relation is in BCNF 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 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. 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

Related Questions

Navigate

• Browse (All)
• Browse 3
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.