In order to uniquely associate quantity supplied (qty) with part (p#) and supplier (s#), a composite primary key
composed of s# and p# is used.
4.2.
First Normal Form
A relational table, by definition, is in first normal form. All values of the columns are atomic. That is, they contain
no repeating values. Table 12 shows the table FIRST in 1NF.
Although the table FIRST is in 1NF it contains redundant data. For example, information about the supplier’s
location and the location’s status have to be repeated for every part supplied. Redundancy causes what are called
update anomalies. Update anomalies are problems that arise when information is inserted, deleted, or updated.
For example, the following anomalies could occur in FIRST:
1. INSERT. The fact that a certain supplier (s5) is located in a particular city (Athens) cannot be added until
they supplied a part.
2. DELETE. If a row is deleted, then not only is the information about quantity and part lost but also informa-
tion about the supplier.
3. UPDATE. If supplier s1 moved from London to New York, then six rows would have to be updated with
this new information.
Basic and Advanced Database Courses
21
s#
status
city
s1
20
London
s2
10
Paris
s3
10
Paris
s4
20
London
s5
30
Athens
Table 13: SUPPLIER table.
4.3.
Second Normal Form
The definition of second normal form states that only tables with composite primary keys can be in 1NF but not in
2NF.
A relational table is in second normal form 2NF if it is in 1NF and every non-key column is fully dependent upon
the primary key. That is, every non-key column must be dependent upon the entire primary key. FIRST is in 1NF
but not in 2NF because status and city are functionally dependent only on the column s# of the composite key (s#,
p#). This can be illustrated by listing the functional dependencies in the table:
s
#
→
(
city, status
)
,
city
→
status,
(
s
#
, p
#)
→
qty.
If we wanted to transform the table FIRST from 1NF to 2NF we could:
1. Identify any determinants other than the composite key, and the columns they determine.
2. Create and name a new table for each determinant and the unique columns it determines.
3. Move the determined columns from the original table to the new table.
4. The determinate becomes the primary key of the new table.
5. Delete the columns we just moved from the original table except for the determinate which will serve as a
foreign key.
The original table may be renamed to maintain semantic meaning. To transform FIRST into 2NF we move the
columns s#, status, and city to a new table called SUPPLIER. The column s# becomes the primary key of this new
table. The results are shown at tables 13 and 14.
Tables in 2NF but not in 3NF still contain modification anomalies. In the example of SUPPLIER, they are:
1. INSERT. The fact that a particular city has a certain status (Rome has a status of 50) cannot be inserted until
there is a supplier in the city.
