Chapter 3 and 4 and Relational Algebra

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 Jones drops the Hobby of model boats, three lines will have to be added – one for Sibling Fred, one for Sibling Sally and one for Sibling Frank.
Modification anomaly:
If a Hobby is changed for a Name with multiple siblings, we will have data integrity problems if the Hobby is not changed in every associated row. For example, if Jones switches from model boats to model airplanes, we will have to modify three rows.
Insertion anomaly:
If a Name adds a new Hobby, a line must be inserted for each Sibling.
For example, if Chau takes up the Hobby of model boats, two lines will have to be added – one for Sibling Jonathan and one for Sibling Eileen.

b. Place the PERSON_2 relation into 4NF.
To put PERSON_2 into 4NF, put the multivalued dependencies into their own relations.
PERSON_4 (Name, Shoe_Size)
PERSON_4_SIBLING (Name, Sibling)
PERSON_4_HOBBY (Name, Hobby)
Note that in the relation PERSON_4_SIBLING, the primary key is the composite key (Name, Sibling) and the column Name functions as a foreign key.
Note that in the relation PERSON_4_HOBBY, the primary key is the composite key (Name, Hobby) and the column Name