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)
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)
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)
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

Get Data Analysis for Database Design, 3rd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.