Data Analysis for Database Design, 3rd Edition

Patient (patientNo, . . .)

Contains (wardName, patientNo, . . .)

As dateOfAdmission applies only to in-patients it cannot be assigned to Patient

without incurring nulls. However, it can be assigned to Contains, since this table is

restricted to in-patients only. The resulting set of tables is:

Ward (wardName, wardType, numberOfBeds)

Patient (patientNo, patientName, dateOfBirth)

Contains (wardName, patientNo, dateOfAdmission)

Question

1. A library keeps information about its books and borrowers, including a record of

which books are currently on loan to which borrowers. Each copy is identified by an

accessionNo, and each borrower by a borrowerNo. Other attributes required are title,

acquisitionDate, acquisitionPrice, loanDate, borrowerName, borrowerLimit. A copy

has only one title. The acquisitionDate and acquisitionPrice refer to the date the library

acquired the copy and the purchase price at that time. The borrowerLimit, which is the

maximum number of books a borrower may have on loan at any one time, is not

necessarily the same for all borrowers. Design an E-R model using Copy and Borrower

for the entity types. (5 min)

 0DQ\PDQ\UHODWLRQVKLSV

Suppose the attributes roomNo, studentName, lecturerHours, studentHours,

attendanceHours are to be added to the skeleton tables for Lecturer, Student and

Tutorship shown in Fig. 12.6. Every student has one name, and lecturers may share the

same room. Attribute lecturerHours is the total tutorial hours on a lecturer’s timetable,

and may vary between lecturers. Attribute studentHours is the total tutorial hours on a

student’s timetable, and may vary between students. Attribute attendanceHours is the

total hours a given student has actually been tutored by a given lecturer.

The assignment of attributes is straightforward:

Lecturer (lecturerName, roomNo, lecturerHours)

Student (studentNo, studentName, studentHours)

Tutorship (lecturerName, studentNo, attendanceHours)

Questions

1. Suppose lecturerHours is redefined as meaning the total hours a lecturer has

actually tutored students, and studentHours is redefined as meaning the total hours a

student has been tutored by lecturers. Would any of the attributes in the Lecturer,

Student or Tutorship table types be redundant? (2 min)

2. Suppose all lecturers are always timetabled for exactly the same number of tutorial

hours. Should lecturerHours be assigned to Lecturer? How else might it be included in

the model? (2 min)

Attribute assignment 151