Chapter 3 and 4 and Relational Algebra
1. Consider the relation (20 points):
PERSON_2 (Name, Sibling, Shoe_Size, Hobby)
Assume that the following functional dependencies exist:
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
If a Name drops a Hobby, one line must be deleted for each Sibling.
For example, if…show more content… 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 “John Smith”, each with a different student number). Then the multivalued dependencies are:
Note: We can not assume that Name Nickname because Name is not unique. For example, one John Smith may have the nickname “Johnny” while another John Smith has the nickname “Joe”. If Name Nickname then John Smith (“Johnny”, “Joe”) which means that both nicknames apply to both John Smiths. But this is not the case – each John Smith has only one nickname, and they are not the same.
b. State the functional dependencies.
We will assume that Number Name where name is not unique (i.e., there may be more than one “John Smith”, each with a different student number). Then the functional dependencies are:
Note: This assumes that only RoomType DormCost – that is, the cost of a certain type of dorm room is the same no matter what dorm the student is living in. An alternate assumption would be that (Dorm, RoomType) DormCost, where the cost of the type of dorm room varies form dorm to dorm.
c. Transform this table into two or more tables such that each table is