Question

Normalization In-Class Exercise 1. Convert the Big Patient Table into 3rd normal form. The functional dependencies are shown in Table 2 for your reference. Draw the functional dependency diagram, and show the result of each step in the normalization process. Table 1 Sample Data for the Big Patient Table VisitNo VisitDate PatNo PatAge PatCity ProvNo ProvSpecialty Diagnosis V10020 1/13/2007 P1 35 DENVER D1 INTERNIST EAR INFECTION V10020 1/13/2007 P1 35 DENVER D2 NURSE PRACTIONER INFLUENZA V93030 1/20/2007 P3 17 ENGLEWOOD D2 NURSE PRACTIONER PREGNANCY V82110 1/18/2007 P2 60 BOULDER D3 CARDIOLOGIST MURMUR Table 2 Functional Dependency in the Big Patient Table PatNo \rightarrow PatAge, PatCity ProvNo \rightarrow ProvSpecialty VisitNo \rightarrow PatNo, VisitDate, PatAge, PatCity VisitNo, ProvNo \rightarrow Diagnosis

          Normalization In-Class Exercise
1. Convert the Big Patient Table into 3rd normal form. The functional dependencies are shown
in Table 2 for your reference. Draw the functional dependency diagram, and show the result
of each step in the normalization process.
Table 1 Sample Data for the Big Patient Table
VisitNo VisitDate PatNo PatAge PatCity ProvNo ProvSpecialty Diagnosis
V10020 1/13/2007 P1 35 DENVER D1 INTERNIST EAR INFECTION
V10020 1/13/2007 P1 35 DENVER D2 NURSE PRACTIONER INFLUENZA
V93030 1/20/2007 P3 17 ENGLEWOOD D2 NURSE PRACTIONER PREGNANCY
V82110 1/18/2007 P2 60 BOULDER D3 CARDIOLOGIST MURMUR
Table 2 Functional Dependency in the Big Patient Table
PatNo \rightarrow PatAge, PatCity
ProvNo \rightarrow ProvSpecialty
VisitNo \rightarrow PatNo, VisitDate, PatAge, PatCity
VisitNo, ProvNo \rightarrow Diagnosis

Normalization In-Class Exercise
1. Convert the Big Patient Table into 3rd normal form. The functional dependencies are shown
in Table 2 for your reference. Draw the functional dependency diagram, and show the result
of each step in the normalization process.
Table 1 Sample Data for the Big Patient Table
VisitNo VisitDate PatNo PatAge PatCity ProvNo ProvSpecialty Diagnosis
V10020 1/13/2007 P1 35 DENVER D1 INTERNIST EAR INFECTION
V10020 1/13/2007 P1 35 DENVER D2 NURSE PRACTIONER INFLUENZA
V93030 1/20/2007 P3 17 ENGLEWOOD D2 NURSE PRACTIONER PREGNANCY
V82110 1/18/2007 P2 60 BOULDER D3 CARDIOLOGIST MURMUR
Table 2 Functional Dependency in the Big Patient Table
PatNo →PatAge, PatCity
ProvNo →ProvSpecialty
VisitNo →PatNo, VisitDate, PatAge, PatCity
VisitNo, ProvNo →Diagnosis

Added by Bobby W.

Question

Please give Ace some feedback