In order to uniquely associate quantity supplied (qty) with part (p#) and supplier (s#), a composite primary keycomposed of s# and p# is used.4.2.First Normal FormA relational table, by definition, is in first normal form. All values of the columns are atomic. That is, they containno 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’slocation and the location’s status have to be repeated for every part supplied. Redundancy causes what are calledupdate 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 untilthey 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 withthis new information.
Basic and Advanced Database Courses21s#statuscitys120Londons210Pariss310Pariss420Londons530AthensTable 13: SUPPLIER table.4.3.Second Normal FormThe definition of second normal form states that only tables with composite primary keys can be in 1NF but not in2NF.A relational table is in second normal form 2NF if it is in 1NF and every non-key column is fully dependent uponthe primary key. That is, every non-key column must be dependent upon the entire primary key. FIRST is in 1NFbut 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 aforeign key.The original table may be renamed to maintain semantic meaning. To transform FIRST into 2NF we move thecolumns s#, status, and city to a new table called SUPPLIER. The column s# becomes the primary key of this newtable. 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 untilthere is a supplier in the city.