Databases Reference
In-Depth Information
Q&A
Question:
You already determined the functional dependencies in a Student relation containing the following
columns: StudentNum, StudentLast, StudentFirst, HighSchoolNum, HighSchoolName, AdvisorNum, AdvisorLast,
and AdvisorFirst. The functional dependencies you determined were as follows:
StudentNum StudentLast, StudentFirst, HighSchoolNum, HighSchoolName, AdvisorNum,
AdvisorLast, AdvisorFirst
HighSchoolNum HighSchoolName
AdvisorNum AdvisorLast, AdvisorFirst
What is the primary key for the Student relation?
Answer:
The only column that determines all the other columns is StudentNum, so it is the primary key for
the Student relation.
155
Occasionally (but not often), there might be more than one possibility for the primary key. For example,
if the Premiere Products database included an Employee table to store employee numbers and Social Secu-
rity numbers, either the employee number or the Social Security number could serve as the primary key. In
this case, both columns are referred to as candidate keys. Like a primary key, a
candidate key
is a column or
a collection of columns on which all columns in the table are functionally dependent; the definition for pri-
mary key also defines a candidate key. From all the candidate keys, one is chosen to be the primary key. The
candidate keys that are not chosen as the primary key are often referred to as
alternate keys
.
NOTE
The primary key is often called the
key
in other studies on database management and the relational model. This text will con-
tinue to use the term
primary key
to distinguish between the different concepts of a key that you will encounter throughout
this text.
A relation (table) that contains a
repeating group
(or multiple entries for a single record) is called an
unnormalized relation
. Removing repeating groups is the starting point in the quest to create tables that are
as free of problems as possible. Tables without repeating groups are said to be in first normal form.
Definition:
A table (relation) is in
first normal form (1NF)
when it does not contain repeating groups.
As an example, consider the Orders table shown in Figure 5-5, in which there is a repeating group con-
sisting of PartNum and NumOrdered.
Orders
OrderNum
OrderDate
PartNum
NumOrdered
21608
10/20/2010
AT94
11
21610
10/20/2010
DR93
1
DW11
1
21613
10/21/2010
KL62
4
21614
10/21/2010
KT03
2
21617
10/23/2010
BV06
2
CD52
4
21619
10/23/2010
DR93
1
21623
10/23/2010
KV29
2
FIGURE 5-5
Sample unnormalized table
The notation for describing the Orders table is as follows:
Orders (OrderNum, OrderDate, (PartNum, NumOrdered) )
