Chapter 3:

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