The following is the relation notation of a table for a veterinary office. VETER
ID: 3871799 • Letter: T
Question
The following is the relation notation of a table for a veterinary office.
VETERINARY_OFFICE (PetID, PetName, PetType, PetBreed, OwnerID, OwnerLastName, OwnerFirstName, ServiceDescription, ServiceDate, ServiceCharge) The functional dependencies are given below: PetID -> PetName, PetType, PetBreed, OwnerID, OwnerLastName, OwnerFirstName OwnerID -> OwnerLastName, OwnerFirstName ServiceDescription -> ServiceCharge PetID, ServiceDate > ServiceDescription, ServiceCharge
Assumption: A pet belongs to only one owner, while an owner may have more than one pet. A pet receives at most one treatment on any given date. Use the functional dependencies above to answer the following three questions. Use relational notation for all relations in your answers. (Note: do not make additional assumptions and functional dependencies)
2.1. Show the 1NF in relational notation (indicate the primary key).
2.2. Show all partial dependency in the relation only (if any) and identify and show the 2NF. Use relational notation and show the partial dependencies, all relations and their references.
2.3. Show all transitive dependencies (if any) in your answer to question 2.2. Identify and show 3NF (show all relations). Use relational notation. You need to show the transitive dependencies and all relations and their references.
Explanation / Answer
2.1 The table should currently be in 1NF since there is only one table. The primary keys are PetID and OwnerID.
(A database is in first normal form if it satisfies the following conditions:
2.2 The OwnerLastName and OwnerFirstName are exclusive to the unique OwnerID, and the PetName, PetType, PetBreed are unique attributes to the PetID. Additionally, each PetID is uniquely attached to an OwnerID. Bringing this table to 2NF would mean pulling the owners into their own table:
Owner: OwnerID, OwnerName
Pet: OwnerID, PetID, PetName, PetType, PetBreed, ServiceDescription, ServiceDate, ServiceCharge
however, this is still not ideal since a pet who has received service multiple times will have redundancy, but it shoudl comply with 2NF
2.3 Since the service is attached to a particular pet and the pet may be repeated in the table, it would be necessary to create a third table, that might look something like this:
Owner: OwnerID, OwnerName
Pet: OwnerID, PetID, PetName, PetType, PetBreed
Service: PetID, ServiceDescription, ServiceDate, ServiceCharge
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.