Database Reference
In-Depth Information
he value in the column Dean is the faculty ID. he relationship is a nonidentifying rela-
tionship. Since each faculty member is related to the dean, the column Dean cannot have a
null value.
Sometimes, you may encounter a many-to-many recursive relationship. For example, in a
course catalog, a course may have several prerequisite courses and many courses may require
the same course as a prerequisite. he relationship between the courses and the prerequisites
is an
M
:
N
recursive relationship. To represent the
M
:
N
recursive relationship, you have to
add an intersection relation in the relationship as shown below:
COURSE (
CourseId
, CourseName)
COURSE_ PREREQUISITE (
CourseId
,
Prerequisite
)
he intersection relation PREREQUISITE_COURSE contains two columns, CourseId
and Prerequisite (which is CourseId itself ), as the combination key. Like other intersection
tables, these two columns are the foreign keys from two parent tables, which, in this exam-
ple, are the same table COURSE. Each row in the relation PREREQUISITE_COURSE
has a course ID and its prerequisite course ID. If a course has no prerequisite or a course is
not the prerequisite of any other course, it will not be listed in the table PREREQUISITE_
COURSE. he table structures for COURSE and PREREQUISITE_COURSE are deined
in Figures 3.19 and 3.20.
For the
M
:
N
recursive relationship, two tables are involved in the relationship and one of
them is the intersection table. For a given course ID, search the intersection table to ind all
matching course ids in the CourseId column. From all the rows that contain the matching
course ids, you can ind the prerequisite course ids. With these prerequisite course ids, you
can obtain the course names in the COURSE table.
6.
Ternary relationship
: A ternary relationship relates multiple entities. Often, a number of
binary relationships are used to implement the ternary relationship. Since database design-
ers are more familiar with binary relationships, converting a ternary relationship to binary
relationships is a better design. Figure 3.21 shows the ternary relationship introduced in
Chapter 2.
Figure 3.19
Table design of COURSE.
