The big patient table as depicted in the following table contains facts about patients, health care providers, patient visits to a clinic, and diagnoses made by health care providers.
VisitNo VisitDate PatNo PatAge PatCity PatZip ProvNo ProvSpeciality Diagnosis
V10020 1/13/2000 P1 35 DENVER 80217 D1 INTERNIST EAR INFECTION
V10020 1/13/2000 P1 35 DENVER 80217 D2 NURSE INFLUENZA
V93030 1/20/2000 P3 17 MADISON 57042 D2 OBGYN PREGANCY
V82110 1/18/2000 P2 60 BOULDER 85932 D3 CARDIOLOGIST MURMUR
Attribute Name Descriptions:
VisitNo – Visit Identification Number
VisitDate – Date of Visit
PatNo – Patient Identification Number
PatAge – Patient Age
PatCity – The city where the patient lives
PatZip – Zip code of the patient
ProvNo – Health care provider Identification Number
ProvSpeciality – Health care provider’s specialty
Diagnosis – The identification of a disease
The Functional Dependencies for the table are as follows:
Primary Key: VisitNo + ProvNo
PatNo — > PatAge, PatCity, PatZip
PatZip — > PatCity
ProvNo — > ProvSpeciality
VisitNo — > VisitDate, PatNo, PatAge, PatCity, PatZip
a. Based on the given conditions, write the relational schema and draw its dependency diagram. Label all transitive and/or partial dependencies.
b. Decompose the dependency diagram to create the table structures that are in 3NF.
