1. Transform the following E-R model to a relational model.
2. The Buy relationship in the following E-R model is transformed into a relational model as follows. Is there any problem with this transformation? If so, discuss the problem(s) and make corrections.
3. Transform the following E-R model to a relational model.
4. The PatientVisit relation keeps information about the patients' visits to doctors.
a. Identify the functional dependencies in the relation.
b. Is this relation in 2NF, 3NF, and BCNF, and why? If not, show your steps of performing the normalization to transform the relation into 2NF, 3NF, and BCNF. Show all referential integrity constraints in the relational model that result from the normalization.
c. Identify the functional dependency in each relation in the final relational model produced in b.
Patient Visit
|
PatientlD
|
_Naine
|
Address
|
VisitDate
|
DoctorID
|
DoctorName
|
|
|
|
|
P1209
|
1. Johns
|
11-1 West
|
3/3/02
|
D1256
|
A. Frank
|
|
P1209
|
J. Johns
|
134 West
|
9/10/02
|
D1256
|
A. Frank
|
|
P1215
|
J. Johns
|
200 West
|
3/3/02
|
D4523
|
D. Gomez
|
|
P1221
|
F.Brown
|
2,23 South
|
6/7/02
|
D6712
|
G. Kelly
|
|
P8912
|
F.Dong
|
45 East
|
9/12/02
|
D8917
|
M. Julius
|