Answer
OWNER (OwnerPhone, OwnerLastName, OwnerFirstName, OwnerEmail)
PET (PetName, PetType, PetBreed, PetDOB, OwnerPhone)
SERVICE (PetName, Date, Service, Charge)
Work Step by Step
STEP ONE:
PET-AND-OWNER (PetName, PetType, PetBreed, PetDOB, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail, Service, Date, Charge)
Functional Dependencies:
PetName->(PetType, PetBreed, PetDOB, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail)
OwnerEmail->(OwnerLastName, OwnerFirstName, OwnerPhone) OwnerPhone->(OwnerLastName, OwnerFirstName, OwnerEmail)
(PetName, Date)->(Service, Charge)
The last functional dependency assumes a pet is seen at most on one day and that there is no standard charge for a service.
PET-AND-OWNER Candidate Keys: (PetName, Date)
Is every determinant a candidate key?
NO—PetName, OwnerEmail and OwnerPhone are NOT candidate keys.
STEP TWO:
Break into two relations OWNER and PET-SERVICE
OWNER (OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail)
PET-SERVICE (PetName, PetType, PetBreed, PetDOB, {Foreign Key}, Service, Date, Charge)
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 primary key. We will use OwnerPhone.
IF WE USE OwnerPhone as primary key, THEN:
OWNER (OwnerPhone, OwnerLastName, OwnerFirstName, OwnerEmail)
PET-SERVICE (PetName, PetType, PetBreed, PetDOB, OwnerPhone, Service, Date, Charge)
FOR PET-SERVICE:
Functional Dependencies:
PetName->(PetType, PetBreed, PetDOB, OwnerPhone)
(PetName, Date)->(Service, Charge)
The last functional dependency assumes a pet is seen at most on one day and that there is no standard charge for a service.
PET-AND-SERVICE Candidate Keys: (PetName, Date)
Is every determinant a candidate key?
NO—PetName is NOT a candidate key.
STEP THREE:
Break PET-SERVICE into two relations: PET and SERVICE
OWNER (OwnerPhone, OwnerLastName, OwnerFirstName, OwnerEmail)
PET (PetName, PetType, PetBreed, PetDOB, OwnerPhone)
SERVICE (PetName, Date, Service, Charge)
PET 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!
SERVICE Functional Dependencies:
(PetName, Date)->(Service, Charge)
The functional dependency assumes a pet is seen at most on one day and that there is no standard charge for a service.
SERVICE Candidate Keys: (PetName, Date)
Is every determinant a candidate key?
YES—(PetName, Date) is a candidate key—Normalization complete!
FINAL NORMALIZED REALTIONS:
OWNER (OwnerPhone, OwnerLastName, OwnerFirstName, OwnerEmail)
PET (PetName, PetType, PetBreed, PetDOB, OwnerPhone)
SERVICE (PetName, Date, Service, Charge)
