In order to uniquely associate quantity supplied qty with part p and supplier s

# In order to uniquely associate quantity supplied qty

• 25

This preview shows page 20 - 22 out of 25 pages.

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.