So I created normalization steps for this pet and owner table in a database class :
Started with Candidate keys, followed by functional dependency break the tables up into tables , then the referential integrity and the end .
PET (Petname,PetType,PetBreed,PetDOB,OwnerLastnameOwnerFirstName,
OwnerPhone,OwnerEmail,Service,Date,Charge)
Step 1: Candidate Keys
The last functional dependency assumes a pet is seen at most on one day and that there is no standard charge for a service.
(PetName,Date)
Step 2 : Functional Dependecies
PetName í (PetType, PetBreed, PetDOB, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail)
OwnerPhone í (OwnerLastName, OwnerFirstName, OwnerEmail)
(PetName, Date) í (Service, Charge)
Step 3:
Dividing the relation if determinant is not a candidate key
Is every determinant a candidate key ?
PetName and OwnerPhone are determinants but not candidate keys
OWNER (OwnerPhone, OwnerLastName, OwnerFirstName, OwnerEmail)
PET (PetName, PetType, PetBreed, PetDOB, OwnerPhone)
SERVICE (PetName, Date, Service, Charge)
OwnerPhone in PET must exist in OwnerPhone in OWNER
PetName in SERVICE must exist in PetName in PET
i need help with Appling the normalization process to the following relations. I need the following steps like the Table above for each relation:
1. Show the candidate keys from the original relation.
2. Show the functional dependencies from the original relation.
3. Is any determinant not a candidate key? If so, show the following:
1. The new normalized relations.
2. The primary key in each new relation.
3. The foreign keys in the new relations.
4. The referential integrity constraints for the foreign keys.
Relation 1:
This relation is about home appliances, such as refrigerators, stoves, ovens, washers, dryers, etc.
" APPLIANCE (ApplianceType, BrandName, ModelNumber, SerialNumber, OwnerName, OwnerAddress, OwnerPhone)
" Assumptions:
o Each model number is unique to its brand and type. For example, a Kenmore washer could have a model number of KEN-WASH-990.
o Serial numbers are just numbers, like 12345678, so could be duplicated.
o Owner phone numbers are unique.
o State any other assumptions you make.
Relation 2:
This relation is about playgroups for children. Any child can belong to any playgroup. Each playgroup meets at a certain parent's home. That parent is the playgroup leader.
" PLAYGROUP (ParentName, ParentEmail, ParentAddress, ChildName, PlayGroup, GroupLeader)
" Assumptions:
o Each parent has a unique e-mail address.
o Each playgroup has a unique name.
o Each playgroup has only one leader.