Database Reference
In-Depth Information
This new design seems to be a possibility. Not only are both tables in third normal form but joining them
together based on LastName and FirstName seems to reconstruct the data in the original table. Or does it?
Suppose two different sales reps, with sales rep numbers 20 and 65, have the same name, Valerie Kaiser. In
this case, when you join the two new tables, there would be no way to correctly identify which Valerie Kaiser
represents which customers. Thus, you would get a row on which customer 148 (Al
s Appliance and Sport) is
associated with sales rep 20 (Valerie Kaiser) and another row on which customer 148 is associated with sales
rep 65 (the other Valerie Kaiser). Because you obviously want decompositions that preserve the original
information, this design is not appropriate.
'
172
Q&A
Question: Using the types of entities found in a college environment (faculty, students, departments, courses,
and so on), create an example of a table that is in first normal form but not in second normal form and an
example of a table that is in second normal form but not in third normal form. In each case, justify your
solutions and show how to convert to the higher forms.
Answer: There are many possible solutions. Your answer may differ from the following solution, but that
does not mean it is an unsatisfactory solution.
To create a first normal form table that is not in second normal form, you need a table that has no
repeating groups and that has at least one column that is dependent on only a portion of the primary key.
For a column to be dependent on a portion of the primary key, the primary key must contain at least two
columns. Following is a picture of what you need:
(1, 2, 3, 4)
This table contains four columns, numbered 1, 2, 3, and 4, in which the combination of columns 1 and 2
functionally determines both columns 3 and 4. In addition, neither column 1 nor column 2 can determine all
other columns; if either one could, the primary key would contain only this one column. Finally, you want
part of the primary key (say, column 2) to determine another column (say, column 4).
Now that you know the pattern you need, you would like to find columns from within the college
environment to fit it. One example is as follows:
(StudentNum, CourseNum, Grade, CourseDescription)
In this example, the concatenation of StudentNum and CourseNum determines both Grade and
CourseDescription. Both columns are required to determine Grade; thus, the primary key consists of their
concatenation. CourseDescription, however, is dependent only on CourseNum, which violates second
normal form. To convert this table to second normal form, you would replace it with two tables:
(StudentNum, CourseNum, Grade)
(CourseNum, CourseDescription)
You would, of course, now give these tables appropriate names.
To create a table that is in second normal form but not in third normal form, you need a second normal
form table in which there is a determinant that is not a candidate key. If you choose a table that has a single
column as the primary key, it is automatically in second normal form, so the real problem is the determi-
nant. You need a table like the following:
(1, 2, 3)
This table contains three columns, numbered 1, 2, and 3, in which column 1 determines each of the
others and thus is the primary key. When column 2 also determines column 3, column 2 is a determinant.
When column 2 does not also determine column 1, column 2 is not a candidate key. One example that fits
this pattern is as follows:
(StudentNum, AdvisorNum, AdvisorName)
In this case, the StudentNum determines both the student
s AdvisorNum and AdvisorName. AdvisorNum
determines AdvisorName, but AdvisorNum does not determine StudentNum because one advisor can have
many advisees. This table is in second normal form but not in third normal form. To convert it to third
normal form, you would replace it with the following:
(StudentNum, AdvisorNum)
(AdvisorNum, AdvisorName)
'
