# Kroenke Ch2 Solutions Essay

1318 Words Nov 22nd, 2013 6 Pages
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
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:
Number  Sibling
Number  Nickname
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:
Number  Name
Number  Dorm
Number  RoomType
RoomType  DormCost
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