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 providers 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.