CREATE DATABASE partsExample;
USE partsExample;

CREATE TABLE Suppliers (
SupplierNum VARCHAR(5) PRIMARY KEY,
SupplierName VARCHAR(20) NOT NULL,
Status NUMERIC NOT NULL,
City VARCHAR(15) NOT NULL); 

INSERT INTO Suppliers
   VALUES ('S1', 'Smith', 20, 'London');
INSERT INTO Suppliers
   VALUES ('S2', 'Jones', 10, 'Paris');
INSERT INTO Suppliers
   VALUES ('S3', 'Blake', 30, 'Paris');
INSERT INTO Suppliers
   VALUES ('S4', 'Clark', 20, 'London');
INSERT INTO Suppliers
   VALUES ('S5', 'Adams', 10, 'Athens');


CREATE TABLE Parts (
PartNum VARCHAR(6) PRIMARY KEY,
PartName VARCHAR(20) NOT NULL,
Color VARCHAR(6) NOT NULL,
Weight NUMERIC NOT NULL,
City VARCHAR(15) NOT NULL); 

INSERT INTO Parts 
   VALUES ('P1', 'Nut', 'Red', 12, 'London');
INSERT INTO Parts 
   VALUES ('P2', 'Bolt', 'Green', 17, 'Paris');
INSERT INTO Parts 
   VALUES ('P3', 'Screw', 'Blue', 17, 'Rome');
INSERT INTO Parts 
   VALUES ('P4', 'Screw', 'Red', 14, 'London');
INSERT INTO Parts 
   VALUES ('P5', 'Cam', 'Blue', 12, 'Paris');
INSERT INTO Parts 
   VALUES ('P6', 'Cog', 'Red', 19, 'London');
INSERT INTO Parts 
   VALUES ('P7', 'Nail', 'Black', 11, 'New York');

CREATE TABLE Projects (
ProjectNum VARCHAR(4) PRIMARY KEY,
ProjectName VARCHAR(10) NOT NULL,
City VARCHAR(15) NOT NULL); 

INSERT INTO Projects 
   VALUES ('J1', 'Sorter', 'Paris');
INSERT INTO Projects 
   VALUES ('J2', 'Punch', 'Rome');
INSERT INTO Projects 
   VALUES ('J3', 'Reader', 'Athens');
INSERT INTO Projects 
   VALUES ('J4', 'Console', 'Athens');
INSERT INTO Projects 
   VALUES ('J5', 'Collator', 'London');
INSERT INTO Projects 
   VALUES ('J6', 'Terminal', 'Oslo');
INSERT INTO Projects 
   VALUES ('J7', 'Tape', 'London');


CREATE TABLE Shipments (
SupplierNum VARCHAR(5),
PartNum VARCHAR(6),
ProjectNum VARCHAR(4),
Qty INTEGER NOT NULL,
PRIMARY KEY (SupplierNum, PartNum, ProjectNum)); 

INSERT INTO Shipments
   VALUES ('S1', 'P1', 'J1', 200);
INSERT INTO Shipments
   VALUES ('S1', 'P1', 'J5', 300);
INSERT INTO Shipments
   VALUES ('S2', 'P3', 'J1', 300);
INSERT INTO Shipments
   VALUES ('S2', 'P3', 'J2', 200);
INSERT INTO Shipments
   VALUES ('S2', 'P3', 'J3', 500);
INSERT INTO Shipments
   VALUES ('S2', 'P3', 'J4', 500);
INSERT INTO Shipments
   VALUES ('S2', 'P3', 'J5', 230);
INSERT INTO Shipments
   VALUES ('S2', 'P3', 'J6', 210);
INSERT INTO Shipments
   VALUES ('S2', 'P5', 'J7', 110);
INSERT INTO Shipments
   VALUES ('S3', 'P3', 'J2', 250);
INSERT INTO Shipments
   VALUES ('S3', 'P4', 'J1', 270);
INSERT INTO Shipments
   VALUES ('S4', 'P6', 'J2', 600);
INSERT INTO Shipments
   VALUES ('S4', 'P6', 'J3', 200);
INSERT INTO Shipments
   VALUES ('S5', 'P2', 'J1', 200);