Normalizing a Veterinary's Database

As we fulfill our first two contracts, we are approached by a new customer who manages a new veterinary facility and wants to improve how their animals history is managed. Looking at their files, we find the following table:

Our veterinary client has noted that for each clients visit, they aim to record information on the client, which pet is being brought for treatment, the diagnosis, and the treatment ordered and cost for a given visit and a for a given diagnosis.

Key notes:

Based on this, we have identified the combination of the Visit_ID and the Treatment_ID as being the key that uniquely identifies each row of data in the table presented.

• The ordered treatment and its corresponding costs were also explained to be for a given visit and a given diagnosis as implied above.
• Illness_name represents the diagnosis name as suggested by the diagnosis ID.
• A particular visit is for only one client, though each client can have more than one pet as shown by John having both a Dog and a Cat.

GOAL:

To improve the quality, we need to identify in which normal form this table is and normalize it further to minimize duplicate and redundant information. Therefore, we ask:

1. In what normal form is the table that was presented to us?
2. Is there a full dependency here? If so, what is the full dependency?
3. Are there 1 or more partial dependencies? If so, what are they?
4. Is there any transitive dependency? What is/are they?

Optional: Draw the corresponding logical model for this veterinary in 3^rd normal form (3NF). :)

VISIT Visit_Client Client_Client_A Pet_ID PetName Pet _ID Date _ID Name ddress Species PetRace Diagnosis Ilness_N Ordered_Visit_ _ID ame Treatment Cost v1 01/15 c1 John 123, CA P1 Paco Ulcer Pilli $20 V2 02/02 C2 Perla Surgery2 $800 Dog Lhasa_A 01 phso Dog Dalmati D2 an Cat Mix D3 Parrot Guacam 04 ayo V3 Jennifer 14 ST, P3 CA John 123, CA P2 Tian 23 ST, P4 NY 02/03c1 02/15 C3 Lucky Broken Leg Allergy Hip injury Pill3 Surgery1 $250 $950 V4 Luna