Kroenke Ch2 Solutions Essay - 1318 Words

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†¦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: 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