DiagnosisTbl(VisitNo

, ProvNo

, Diagnosis)

FOREIGN KEY (VisitNo) REFERENCES Visit

FOREIGN KEY (ProvNo) REFERENCES Provider

Step 3: The Provider and ProviderEmail tables are merged and a unique constraint is added.

Patient (PatNo

, PatAge, PatZip9)

FOREIGN KEY (PatZip9) REFERENCES ZipCode

ZipCode(PatZip9

, PatCity)

Provider(ProvNo

, ProvSpecialty, ProvEmail)

UNIQUE ProvEmail

Visit(VisitNo

, VisitDate, PatNo)

FOREIGN KEY (PatNo) REFERENCES Patient

DiagnosisTbl(VisitNo

, ProvNo

, Diagnosis)

FOREIGN KEY (VisitNo) REFERENCES Visit

FOREIGN KEY (ProvNo) REFERENCES Provider

3.

You should determine if the

Student

,

Lender

, and

Institution

tables are in BCNF. In the

Lender

table,

LenderName

is unique. In the

Institution

table,

InstName

is unique. In the

Student

table,

StdEmail

is unique. The primary key of each table is underlined. You should

explain your decision and modify the table design by splitting tables or adding constraints if

necessary.

Student (StdNo

, StdName, StdEmail, StdAddress, StdCity, StdState, StdZip )

Lender(LenderNo

, LenderName)

Institution(InstNo

, InstName, InstMascot)

Answer

LenderName, InstName, and StdEmail are determinants. The Student, Lender, and Institution

tables are in BCNF because these determinants are unique. The tables should not be split

10/8/2019

Solutions for the Module 11 Assignment

Page 4

because they are in BCNF. Unique constraints should be added for

LenderName

,

InstName

,

and

StdEmail

. Here is the revised table design with the unique constraints.