/**************************************************
	 Suppliers Parts Projects Database 
	 From _An Introduciton to Database Systems_ by C.J. Date

**************************************************/

/* First time only 
CREATE DATABASE SuppliersPartsProjects 

USE SuppliersPartsProjects; 
*/

/* Attempt to remove each of the tables in case this script has already been run. */

/*
Not sure why the following doesn't work. 
DROP TABLE S IF EXISTS
DROP TABLE P IF EXISTS
DROP TABLE J IF EXISTS
DROP TABLE SPJ IF EXISTS 
*/
/******* S (Supplier) Table **********************/

CREATE TABLE S
(sNo CHAR(4) PRIMARY KEY,
sName CHAR(30),
status INT,
city CHAR(20))

INSERT INTO S
VALUES ('S001', 'Smith', 20, 'London')

INSERT INTO S
VALUES ('S002', 'Jones', 10, 'Paris')

INSERT INTO S
VALUES ('S003', 'Blake', 20, 'Paris')

INSERT INTO S
VALUES ('S004', 'Clark', 20, 'London')

INSERT INTO S
VALUES ('S005', 'Adam', 30, 'Athens')

INSERT INTO S
VALUES ('S006', 'Baker', 40, 'London')

GRANT SELECT ON S TO PUBLIC


/******* P (Part) Table **********************/

CREATE TABLE P
(pNo CHAR(3) PRIMARY KEY,
pName CHAR(30),
pColor CHAR(20),
weight INT,
city CHAR(20))

INSERT INTO P
VALUES ('P01', 'Nut', 'Red', 12, 'London')

INSERT INTO P
VALUES ('P02', 'Bolt', 'Green', 17, 'Paris')

INSERT INTO P
VALUES ('P03', 'Screw', 'Blue', 17, 'Rome')

INSERT INTO P
VALUES ('P04', 'Screw', 'Red', 14, 'London')

INSERT INTO P
VALUES ('P05', 'Cam', 'Blue', 12, 'Paris')

INSERT INTO P
VALUES ('P06', 'Cog', 'Red', 19, 'London')

INSERT INTO P
VALUES ('P07', 'Cog', 'Black', 15, 'Florence')

GRANT SELECT ON P TO PUBLIC


/******* J (Projects) Table **********************/

CREATE TABLE J
(jNo CHAR(4) PRIMARY KEY,
jName CHAR(30),
city CHAR(20))
INSERT INTO J
VALUES ('J001', 'Sorter', 'Paris')

INSERT INTO J
VALUES ('J002', 'Display', 'Rome')

INSERT INTO J
VALUES ('J003', 'OCR', 'Athens')

INSERT INTO J
VALUES ('J004', 'Console', 'Athens')

INSERT INTO J
VALUES ('J005', 'RAID', 'London')

INSERT INTO J
VALUES ('J006', 'EDS', 'Oslo')

INSERT INTO J
VALUES ('J007', 'Tape', 'London')

GRANT SELECT ON J TO PUBLIC


/**** SPJ (Supplier-Part-Project) Table ****************/

CREATE TABLE SPJ
(sNo CHAR(4) NOT NULL REFERENCES S(sNo),
pNo CHAR(3) NOT NULL REFERENCES P(pNo),
jNo CHAR(4) NOT NULL REFERENCES J(jNo),
qty INT,
PRIMARY KEY (sNo, pNo, JNo))

INSERT INTO SPJ
VALUES ('S001', 'P01', 'J001', 200)

INSERT INTO SPJ
VALUES ('S001', 'P01', 'J004', 700)

INSERT INTO SPJ
VALUES ('S002', 'P03', 'J001', 400)

INSERT INTO SPJ
VALUES ('S002', 'P03', 'J002', 200)

INSERT INTO SPJ
VALUES ('S002', 'P03', 'J003', 200)

INSERT INTO SPJ
VALUES ('S002', 'P03', 'J004', 500)

INSERT INTO SPJ
VALUES ('S002', 'P03', 'J005', 600)

INSERT INTO SPJ
VALUES ('S002', 'P03', 'J006', 400)

INSERT INTO SPJ
VALUES ('S002', 'P03', 'J007', 800)

INSERT INTO SPJ
VALUES ('S003', 'P03', 'J001', 200)

INSERT INTO SPJ
VALUES ('S003', 'P05', 'J002', 100)

INSERT INTO SPJ
VALUES ('S004', 'P06', 'J007', 300)

INSERT INTO SPJ
VALUES ('S005', 'P01', 'J004', 100)

INSERT INTO SPJ
VALUES ('S005', 'P01', 'J005', 300)

INSERT INTO SPJ
VALUES ('S005', 'P02', 'J002', 200)

INSERT INTO SPJ
VALUES ('S005', 'P02', 'J004', 100)

INSERT INTO SPJ
VALUES ('S005', 'P03', 'J004', 200)

INSERT INTO SPJ
VALUES ('S005', 'P04', 'J004', 400)

INSERT INTO SPJ
VALUES ('S005', 'P05', 'J004', 500)

INSERT INTO SPJ
VALUES ('S005', 'P05', 'J005', 500)

INSERT INTO SPJ
VALUES ('S005', 'P05', 'J007', 100)

INSERT INTO SPJ
VALUES ('S005', 'P06', 'J002', 200)


GRANT SELECT ON SPJ TO PUBLIC


