Database Reference
In-Depth Information
Time schedule: List all sections of all courses offered during a given semester. Each section
has a unique four-digit schedule code. The time schedule lists the schedule code; the department offering
the course; the course
User View 6
—
331
s number, section letter, and title; the instructor teaching the course; the time the
course meets; the room in which the course meets; the number of credits generated by the course; and
the prerequisites for the course. In addition to the information shown in the figure, the time schedule
includes the date the semester begins and ends, the date final exams begin and end, and the last withdrawal
date. The attributes on the time schedule are as follows: term (which is a synonym for semester), department
code, department name, location, course number, course title, number of credits, schedule code, section
letter, meeting time, meeting place, and instructor name.
You could create a single relation containing all these attributes and then normalize that relation, or you
could apply the tips presented in Chapter 8 for determining the collection of relations. In either case, you
ultimately create the following collection of relations:
'
Department (DepartmentCode, DepartmentName, Location)
Course (DepartmentCode, CourseNum, CourseTitle, NumCredits)
Section (SemesterCode, ScheduleCode, DepartmentCode, CourseNum,
SectionLetter, Time, Room, FacultyNum)
Faculty (FacultyNum, LastName, FirstName)
Semester (SemesterCode, StartDate, EndDate, ExamStartDate,
ExamEndDate, WithdrawalDate)
NOTE
Actually, given the attributes in this user view, the Section relation would contain the instructor
'
s name (LastName and
FirstName). There was no mention of instructor number. In general, as you saw earlier, it
'
s a good idea to include determinants
for attributes whenever possible. In this example, because FacultyNum determines LastName and FirstName, you add
FacultyNum to the Section relation, at which point the Section relation is not in third normal form. Converting to third normal
form produces the collection of relations previously shown.
Q&A
Question: Determine the tables and keys required for User View 6. Merge the result into the cumulative
design and draw the E-R diagram for the new cumulative design.
Answer: Figure A-15 shows the new cumulative design.
