Database Reference
In-Depth Information
Figure 3.20
Table design of PREREQUISITE_COURSE.
CLASS
ClassID
CLASS_CLASSROOM
FACULT Y_CLASS
ClassID (FK)
ClassroomID (FK)
FacultyID (FK)
ClassID (FK)
CourseID
BuildingID (FK)
Credit
TimeID
SemesterID
DayID
FACULT Y
CLASSROOM
FacultyID
ClassroomID
BuildingID
FirstName
LastName
Capacity
Figure 3.21
Ternary relationship.
he ternary relationship consists of two nonspeciic binary relationships. he corresponding
relations involved in this ternary relationship are
FACULTY(
FacultyID
, FirstName, LastName)
FACU LT Y_CL ASS(
FacultyID
,
ClassID
)
CLASS(
ClassID,
CourseID, Credit, TimeID, SemesterID, DayID)
CLASS_CLASSROOM(
ClassID
,
ClassroomID
,
BuildingID
)
CLASSROOM(
ClassroomID
,
BuildingID
,
Capacity
)
here are two intersection relations, FACULTY_CLASS and CLASS_CLASSROOM, used to
implement the two
M
:
N
relationships. To ind the classrooms for a given professor, the search starts
in the intersection relation FACULTY_CLASS to ind all the class ids related to the given faculty
ID. With these class ids, you can look for the classroom ids in the intersection relation CLASS_
CLASSROOM. Once these classroom ids are available, you can use them to query the information
about these classrooms for the given professor. On the other hand, if you want to know which profes-
sors are teaching in a given classroom, you can irst look for the class ids that match the given class-
room ID in the relation CLASS_CLASSROOM. With these matching class ids, you will be able
to ind the corresponding faculty ids in the relation FACULTY_CLASS. By using the faculty ids,
you can get the professors' names in the relation FACULTY. In Figures 3.22 through 3.24, the table
