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 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
