Database Reference
In-Depth Information
STUDENT
FACULT Y
StudentID
FacultyID
FirstName
FirstName
LastName
LastName
FacultyID (FK)
Figure 3.11
Nonidentifying relationship.
he corresponding relations with the shared column FacultyID are given as the following:
FACULTY(
FacultyID
, FirstName, LastName)
STUDENT(
StudentID
, FirstName, LastName,
FacultyID
)
To ind a student's advisor's name for a given student ID from the above relations, you irst
look for the faculty ID that is in the same row as the given student ID in the STUDENT
relation. hen, search for the faculty ID in the FACULTY relation that matches the faculty
ID from the STUDENT relation. Once the faculty ID is found in the FACULTY relation,
you can get the advisor's name in the same row. On the other hand, to ind all the students
who are advised by a faculty whose ID is given, irst look up the FacultyID column in the
STUDENT relation to ind all the values that match the given faculty ID. hen, you can
get the students' names in those rows that have the matching faculty ID.
As pointed out in the previous chapter, a nonidentifying relationship is often used to rep-
resent a 1:
N
relationship where
N
can be 0, 1, or any positive integer. When
N
is equal to 1,
you have a 1:1 relationship. In such a case, you can put the primary key values of one relation
to the other relation to represent the relationship. When
N
is a positive integer that is greater
than 1, you have a one-to-many relationship. In this case, the foreign key must be placed in
the relation on the many side, not the other way around. In Windows Azure SQL Database,
the two tables implementing the relations involved in this nonidentifying relationship are
shown in Figures 3.12 and 3.13.
For the FacultyID column in the table STUDENT, no check mark under the Is Required
heading indicates that the faculty ID is optional, which matches the speciication deined in
the nonidentifying relationship shown in Figure 3.11.
3.
Nonspeciic relationship
: For the nonspeciic relationship described in Chapter 2, there
is a many-to-many (
M
:
N
) relationship between the entities STUDENT and CLASS.
You need an intersection entity to implement a many-to-many relationship as shown in
Figure 3.14, where the intersection entity STUDENT_CLASS contains the key attri-
butes from the entities on both sides of the
M
:
N
relationship. With the intersection
entity, a many-to-many relationship can be represented with a 1:
M
relationship between
STUDENT and STUDENT_CLASS and a 1:
N
relationship between CLASS and
STUDENT_CLASS.
With Windows Azure SQL Database, you can deine the tables STUDENT, CLASS, and
STUDENT_CLASS. In Figures 3.15 and 3.16, the tables CLASS and STUDENT_CLASS
are deined. he table STUDENT has been deined in Figure 3.13.
In Figure 3.16, the combination key (
StudentID
,
ClassID
) is created. In Figures
3.15 and 3.16, notice that the deinitions of the ClassID column in both CLASS and
