Database Concepts Chapter 2 - The Relational Model - Exercises - Page 108 2.38

Answer

OWNER (OwnerPhone, OwnerLastName, OwnerFirstName, OwnerEmail) PET (PetName, PetType, PetBreed, PetDOB, OwnerEmail)

Work Step by Step

STEP ONE: PET-AND-OWNER (PetName, PetType, PetBreed, PetDOB, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail) Functional Dependencies: PetName->(PetType, PetBreed, PetDOB, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail) OwnerEmail->(OwnerLastName, OwnerFirstName, OwnerPhone) OwnerPhone->(OwnerLastName, OwnerFirstName, OwnerEmail) PET-AND-OWNER Candidate Keys: PetName Is every determinant a candidate key? NO—OwnerEmail and OwnerPhone are NOT candidate keys. STEP TWO: Break into two relations: OWNER and PET OWNER (OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail) PET (PetName, PetType, PetBreed, PetDOB, {Foreign Key}) FOR OWNER: Functional Dependencies: OwnerEmail->(OwnerLastName, OwnerFirstName, OwnerPhone) OwnerPhone->(OwnerLastName, OwnerFirstName, OwnerEmail) OWNER Candidate Keys: OwnerPhone, OwnerEmail Is every determinant a candidate key? YES—OwnerEmail and OwnerPhone are candidate keys—Normalization complete! We can choose either candidate key as a primary key. (A) IF WE USE OwnerPhone as primary key, THEN: OWNER (OwnerPhone, OwnerLastName, OwnerFirstName, OwnerEmail) PET (PetName, PetType, PetBreed, PetDOB, OwnerPhone) Functional Dependencies: PetName->(PetType, PetBreed, PetDOB, OwnerPhone) PET Candidate Keys: PetName Is every determinant a candidate key? YES—PetName is a candidate key—Normalization complete! FINAL NORMALIZED REALTIONS: OWNER (OwnerPhone, OwnerLastName, OwnerFirstName, OwnerEmail) PET (PetName, PetType, PetBreed, PetDOB, OwnerPhone) (B) IF WE USE OwnerEmail as primary key, THEN: OWNER (OwnerPhone, OwnerLastName, OwnerFirstName, OwnerEmail) PET (PetName, PetType, PetBreed, PetDOB, OwnerEmail) Functional Dependencies: PetName->(PetType, PetBreed, PetDOB, OwnerEmail) PET Candidate Keys: PetName Is every determinant a candidate key? YES—PetName is a candidate key—Normalization complete! FINAL NORMALIZED RELATIONS: OWNER (OwnerPhone, OwnerLastName, OwnerFirstName, OwnerEmail) PET (PetName, PetType, PetBreed, PetDOB, OwnerEmail)

Update this answer!

You can help us out by revising, improving and updating this answer.

Update this answer

After you claim an answer you’ll have 24 hours to send in a draft. An editor will review the submission and either publish your submission or provide feedback.