Question & Answer: 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……

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.