Assignment 1 Normalization

In this assignment you will create a normalized database from an un-normalized one. The database contains the same information as the running Suppliers-Parts database, except (a) CITY attributes have been renamed, and (b) a supplier’s status is functionally dependent on the city.

Complete the following steps.

  1. In cPanel, using the MySQL Databases app create a database named ASSIGNMENT01.
  2. In cPanel, open PhpMyAdmin and open the ASSIGNMENT01 database.
  3. In PhpMyAdmin, click the SQL tab to open a window for entering SQL statements. Copy the schema below into the window and click Go.
  4. Decompose the SSPP table into as many tables needed so all are in BCNF and using the InnoDB database engine format. Make sure the attribute names and types are exactly the same. Name the tables S, SP, P and CS (for the { SCITY, STATUS } table).
  5. Add the integrity constraints prohibiting dangling foreign key references.

This assignment is due in one week and should take you less than 1/2 hour to complete.

CREATE TABLE IF NOT EXISTS SSPP (
  SNO varchar(6) NOT NULL DEFAULT '',
  PNO varchar(6) NOT NULL DEFAULT '',
  QTY int(11) NOT NULL,
  SNAME varchar(25) NOT NULL,
  STATUS int(3) NOT NULL,
  SCITY varchar(25) NOT NULL,
  PNAME varchar(50) NOT NULL,
  COLOR varchar(25) NOT NULL,
  WEIGHT decimal(6,1) NOT NULL,
  PCITY varchar(25) NOT NULL,
  PRIMARY KEY (SNO,PNO)
);

INSERT INTO SSPP (SNO, PNO, QTY, SNAME, `STATUS`, SCITY, PNAME, COLOR, WEIGHT, PCITY) VALUES
('S1', 'P1', 300, 'Smith', 20, 'London', 'Nut', 'Red', '12.0', 'London'),
('S1', 'P2', 200, 'Smith', 20, 'London', 'Bolt', 'Green', '17.0', 'Paris'),
('S1', 'P3', 400, 'Smith', 20, 'London', 'Screw', 'Blue', '17.0', 'Oslo'),
('S1', 'P4', 200, 'Smith', 20, 'London', 'Screw', 'Red', '14.0', 'London'),
('S1', 'P5', 100, 'Smith', 20, 'London', 'Cam', 'Blue', '12.0', 'Paris'),
('S1', 'P6', 100, 'Smith', 20, 'London', 'Cog', 'Red', '19.0', 'London'),
('S2', 'P1', 300, 'Jones', 10, 'Paris', 'Nut', 'Red', '12.0', 'London'),
('S2', 'P2', 400, 'Jones', 10, 'Paris', 'Bolt', 'Green', '17.0', 'Paris'),
('S3', 'P2', 200, 'Blake', 10, 'Paris', 'Bolt', 'Green', '17.0', 'Paris'),
('S4', 'P2', 200, 'Clark', 20, 'London', 'Bolt', 'Green', '17.0', 'Paris'),
('S4', 'P4', 300, 'Clark', 20, 'London', 'Screw', 'Red', '14.0', 'London'),
('S4', 'P5', 400, 'Clark', 20, 'London', 'Cam', 'Blue', '12.0', 'Paris');