Database Reference
In-Depth Information
Q&A
Question: Assume the following columns exist in a relation named Student:
StudentNum (student number)
StudentLast (student last name)
StudentFirst (student first name)
HighSchoolNum (number of the high school from which the student graduated)
159
HighSchoolName (name of the high school from which the student graduated)
AdvisorNum (number of the student
'
s advisor)
AdvisorLast (last name of the student
'
s advisor)
s advisor)
Student numbers, high school numbers, and advisor numbers are unique; no two students have the same
number, no two high schools have the same number, and no two advisors have the same number. Use this
information to determine the functional dependencies in the Student relation.
Answer: Because student numbers are unique, any given student number in the database is associated with
a single last name, first name, high school number, high school name, advisor number, advisor last name, and
advisor first name. Thus, all the other columns in the Student relation are functionally dependent on
StudentNum, which is represented as follows:
StudentNum
→
StudentLast, StudentFirst, HighSchoolNum, HighSchoolName,
AdvisorNum, AdvisorLast, AdvisorFirst
Because two students can have the same first and last names, StudentFirst and StudentLast do not
determine anything else. Because high school numbers are unique, any given high school number is
associated with exactly one high school name. If high school 128 is Robbins High, for example, any student
whose high school number is 128 must have the high school name Robbins High. Thus, HighSchoolName
is functionally dependent on HighSchoolNum, which is represented as follows:
HighSchoolNum
→
HighSchoolName
Because advisor numbers are unique, any given advisor number is associated with exactly one advisor
first name and exactly one advisor last name. If advisor 20 is Mary Webb, for example, any student whose
advisor number is 20 must have the advisor
AdvisorFirst (first name of the student
'
s last name Webb. Thus,
AdvisorFirst and AdvisorLast are functionally dependent on AdvisorNum, which is represented as follows:
AdvisorNum
→
AdvisorLast, AdvisorFirst
As with students, an advisor
'
s first name Mary and the advisor
'
s first and last names are not necessarily unique, so AdvisorFirst and
AdvisorLast do not determine anything. The complete collection of functional dependencies is as follows:
StudentNum
→
StudentLast, StudentFirst, HighSchoolNum, HighSchoolName,
AdvisorNum, AdvisorLast, AdvisorFirst
HighSchoolNum
→
HighSchoolName
AdvisorNum
→
AdvisorLast, AdvisorFirst
'
KEYS
A second underlying concept of the normalization process is that of the primary key. You already encoun-
tered the basic concept of the primary key in earlier chapters. In this chapter, however, you need a more
precise definition.
Definition:
Column A (or a collection of columns) is the primary key for a relation (table) R, if:
Property 1. All columns in R are functionally dependent on A.
Property 2. No subcollection of the columns in A (assuming A is a collection of columns and not just a
single column) also has Property 1.
