Database 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.
161
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 table
s 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 defini-
tion for primary key also defines a candidate key. From all the candidate keys, one is chosen to be the pri-
mary 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 continue to use the term
primary key to
distinguish between the different definitions of a key that you will encounter
throughout this text.
FIRST NORMAL FORM
A relation (table) that contains a repeating group (or multiple entries for a single record) is called an unnor-
malized 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/2013
AT94
11
21610
10/20/2013
DR93
1
DW11
1
21613
10/21/2013
KL62
4
21614
10/21/2013
KT03
2
21617
10/23/2013
BV06
2
CD52
4
21619
10/23/2013
DR93
1
21623
10/23/2013
KV29
2
FIGURE 5-5
Sample unnormalized table
The notation for describing the Orders table is as follows:
Orders (OrderNum, OrderDate, (PartNum, NumOrdered) )
