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.

Suppliers each have an ID (SNO), name (SNAME), status (SSTATUS) and city (SCITY). These fields will be contained in table S.

Parts have a part ID (PNO), name (PNAME), color (PCOLOR), weight (PWEIGHT) and city (PCITY). These fields will be contained in table P.

Suppliers are currently expecting a quantity of parts (SPQTY). These will be contained in table SP.

IMPORT SPREADSHEET TO DATABASE TABLE

1. Copy table from HTML page into an Excel worksheet pasting values only (i.e., without formatting)
2. Save the worksheet as a .csv file. Remember where you save it.
3. Open cPanel
4. Click to open the MySQL Databases icon under DATABASES.
5. Create the database.
6. Return to cPanel. Click to open phpMyAdmin under DATABASES.
7. On the left sidebar, click to open the database you just created.
8. In the database you just created, click the Import tab at the top.
9. In the File to import section, click the Browse button. Find the .csv file you just saved and click to open.
10. Scroll to the bottom of the Import page and click the "The first line of the file contains the table column names" checkbox. THIS IS IMPORTANT.
11. Click Go at the bottom.
12. Once the import is complete, your imported table will be named the same as the name as the uploaded file and should appear on the left side bar. Click the Structure tab at the top to see the list of tables. Click the table you just imported to select it.
13. In the table you just created, click the Operations tab at the top.
14. Scroll down the Table options section and rename the table to "ORIGINAL." Set the Storage Engine to "InnoDB." THIS IS IMPORTANT.
15. Click Go to save your changes.

SET FIELD TYPES

1. In your ORIGINAL table, click the Structure tab at the top.
2. Set the field types. For the Suppliers Parts table, set the field sizes slightly larger then the defaults. Set the Supplier and Part numbers to VARCHAR(5). Set SNAME, PNAME, SCITY, PCITY and PCOLOR to VARCHAR(25). SSTATUS and SPQTY can default to INT(). PWEIGHT can default to FLOAT(). (Size parameters don't need to be specified for INT and FLOAT.)

CREATE NORMALIZED TABLE SCHEMAS

1. Make copies of the Structure of the ORIGINAL table for tables S, P and SP. To do so, click to the ORIGINAL table.
2. From ORIGINAL, click the Operations tab at the top.
3. Scroll down to the Copy table section. Click the Structure Only radio button. Make a copy for each of what will be the normalized tables, S, P and SP. Make sure the tables use the InnoDB Storage Engine.
4. For each of the tables you created, from the Structure tab, delete the fields you don't need and set the primary keys. For the S table, delete the PNO, PNAME, PWEIGHT, PCITY and SPQTY fields. Then make SNO the primary key. For the P table, delete the SNO, SNAME, SSTATUS, SCITY and SPQTY fields. Then make PNO the primary key. For the SP table, delete all the fields except the SNO, PNO and SPQTY fields. Then make SNO and PNO the composite primary key.
5. Foreign key constraints must be added to the SP table to protect against a supplier or part from being deleted while still being referenced in SP. Click to the Structure tab for the SP table. Click the Relation view button at the top. You will add two foreign key constraints:
   1. In the form to add a constraint, leave the first field blank (it will be filled in automatically). For ON DELETE select RESTRICT. For ON UPDATE select RESTRICT. For Column, select SNO. Make sure the PARTS database is selected. Select table S. Select column SNO
   2. Click the +Add constraint link below the form to add a second constraint. Add one for column PNO table P column PNO. Click Save at the bottom.

IMPORT ROWS FROM ORIGINAL

1. To fill table S from ORIGINAL, execute the statement INSERT INTO S SELECT DISTINCT SNO, SNAME, SSTATUS, SCITY FROM ORIGINAL
2. To fill table P from ORIGINAL, execute the statement INSERT INTO P SELECT DISTINCT PNO, PNAME, PCOLOR, PWEIGHT, PCITY FROM ORIGINAL
3. To fill table SP from ORIGINAL, execute the statement INSERT INTO SP SELECT DISTINCT SNO, PNO, SPQTY FROM ORIGINAL

When you are through, confirm you created your database correctly using the autograder.