1. Consider the relation (20 points):
PERSON_2 (Name, Sibling, Shoe_Size, Hobby)
Assume that the following functional dependencies exist:
Name Sibling
Name Shoe_Size
Name Hobby
a. Describe deletion, modification, and insertion anomalies for this relation.
Consider the following relation:
Name Sibling Shoe_Size Hobby
Jones Fred E Model boats
Jones Fred E Scuba Diving
Jones Sally E Model boats
Jones Sally E Scuba Diving
Jones Frank E Model boats
Jones Frank E Scuba Diving
Greene Nikki B Embroidery
Chau Jonathan C Scuba diving
Chau Eileen C Scuba diving
Deletion anomaly:
If a Name drops a Hobby, one line must be deleted for each Sibling.
For example, if…show more content… We’ll move the obvious multivalued dependencies into their own tables, and then check for BCNF. IF we have BCNF and no multivalued dependencies, we also have 4NF:
STEP ONE: MOVE MUTIVALUED DEPENDENCIES INTO SEPARATE TABLES: STUDENT_2 (Number, Name, Dorm, RoomType, DormCost) STUDENT_SIBLING (Number, Sibling) STUDENT_NICKNAME (Number, Nickname)
STEP TWO: CHECK EACH OF THE RESULTING TABLES FOR BNCF: STEP TWO (A): CHECK STUDENT STUDENT_2 FUNCTIONAL DEPENDENCIES: STUDENT_2 (Number, Name, Dorm, RoomType, DormCost) Number Name Number Dorm Number RoomType RoomType DormCost STUDENT_2 CANDIDATE KEYS: Number Is every determinant a candidate key? NO, RoomType is NOT a candidate key. Therefore the relation is NOT in BCNF. Therefore, move RoomType DormCost into another table STUDENT_3 (Number, Name, Dorm, RoomType) DORM_RATE (RoomType, DormCost) STEP TWO (A) (1): CHECK STUDENT_3: STUDENT FUNCTIONAL DEPENDENCIES: STUDENT_3 (Number, Name, Dorm, RoomType) Number Name Number Dorm Number RoomType STUDENT CANDIDATE KEYS: Number Is every determinant a candidate key? YES, Therefore STUDENT_3 is in BNCF. STEP TWO (A) (2): CHECK STUDENT: DORM_RATE FUNCTIONAL DEPENDENCIES: DORM_RATE (RoomType, DormCost) RoomType DormCost DORM_RATE CANDIDATE KEYS: RoomType Is every determinant a candidate key? YES, Therefore DORM_RATE is in BNCF. STEP TWO (B): CHECK
