A2.1 PARTS Database Normalization

The following table lists suppliers that are expecting parts deliveries from various locations on a particular day. The table lists suppliers, parts and the delivery quantity all in one table. The first four columns that start with S have to do with the supplier. The second five columns that begin with P have to do with the part. The last column is the quantity of parts that supplier expects to be delivered that day.

The table is at least in first normal form because there are no redundant rows and each cell has exactly one value. But is it second normal form?

Notice that the (composite) primary key for the table is both SNO and PNO combined, and SPQTY is different depending on SNO and PNO combined. However, SNAME, SSTATUS and SCITY depend only on SNO. Similarly with the columns that start with P that depend only on PNO. To be in second normal form, all non-key columns must depend on the whole primary key, not just part of it. We see that there are three columns that depend only on SNO, and four others that depend only on PNO. Therefore, the table is only in first normal form.

For this assignment you will first upload this table into a MySQL table. Then you will normalize it into three tables in BCNF. Step by step instructions on how to produce a normalized set of tables by projection (using SELECT and INSERT statements) are given below. You can use this process on this and the following three similar assignments.