E-R model HW
E-R model HW
Basically you will be completeing several ER Models.
the entire textbook, and powerpoint slides from the class. It can be however long you want, just saitisfy the questions. 4 questions in length.
The questions involve a picture and the attached PDF shows the questions and models
1. Chapter 6, Exercise 5
2. Chapter 6, Exercise 7
3. Chapter 6, Exercise 8
4. Chapter 6, Exercise 10
106 Developing Windows-Based and Web-Enabled Information Systems
5. 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.
Employee
ID Name Address
Phone Email
DateOfHire EmHistory Salary
Contact
Customer
Ticket
Buy
TicketNo
Price
BuyDate
Member
LoginName Password
Person
O
Type
Customer
ID Type TicketNo BuyDate
Ticket
TicketNo Price
6. Transform the E-R model in Exercise 5 to a relational model.
7. The transformation of an E-R model to a relational model is shown in the following.
Is this transformation correct? If not, explain what is incorrect and why it is
incorrect, and make a correction.
DependentID
StudentID
StudentName
Address
Student Has Dependent
DateOfBirth
Logical Database Design 107
Student
StudentID StudentName Address
Dependent
DependentID DateOfBirth StudentID
8. Transform the E-R model of the movie theater database from Exercise 8 in
Chapter 5 to the relational model of the movie theater database.
9. Transform the following E-R model to a relational model.
Customer
Phone Name Address
Room
RoomNo Type Size View Smoking Floor
Make Reservation
Assign
| | | <
ReservationNo Check-InTime Check-OutTime PaymentMethod Charges
ChargeID Amount
FinalBillAmount
Price
10. 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.
PatientVisit
PatientID Name Address VisitDate DoctorID DoctorName
P1209 J. Johns 134 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 223 South 6/7/02 D6712 G. Kelly
P8912 F. Dong 45 East 9/12/02 D8917 M. Julius