1. Transform the following E-R model to a relational model.
![1581_1 ER diagram.jpg](https://secure.tutorsglobe.com/CMSImages/1581_1 ER diagram.jpg)
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.
![1679_2 Diagram.jpg](https://secure.tutorsglobe.com/CMSImages/1679_2 Diagram.jpg)
3. Transform the following E-R model to a relational model.
![1326_3 diagram.jpg](https://secure.tutorsglobe.com/CMSImages/1326_3 diagram.jpg)
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
|