/*
Shops (shopID, shopAddress, shopTown, shopPostcode, shopPhone, shopEMail)
*/
INSERT INTO SHOPS VALUES ('S1', '5 Leviathan St', 'Voss', 1312, 95917258, 'SuperFitVoss@gmail.com');
INSERT INTO SHOPS VALUES ('S2', '28 Harvest Lane', 'Durj', 2312, 96194919, 'SuperFitDurj@gmail.com');
INSERT INTO SHOPS VALUES ('S3', '42 University Road', 'Tass', 3312, 90031952, 'SuperFitTass@gmail.com');
/*
Suppliers (suppID, suppName, suppAddress, suppTown, suppPostcode, suppPhone, suppEMail)
*/
INSERT INTO SUPPLIERS VALUES ('Su1', 'Bics Warehouse', '1151 Cardinal Lane', 'Rollosville', 4312, 11111111, 'bic@gmail.com');
INSERT INTO SUPPLIERS VALUES ('Su2', 'Shirts R Us', '4164 Southern Avenue', 'Mason City', 5312, 22222222, 'shirts@gmail.com');
INSERT INTO SUPPLIERS VALUES ('Su3','Martins Wares', '3128 James Martin Circle', 'Colomi', 6312, 33333333, 'martin@gmail.com');
INSERT INTO SUPPLIERS VALUES ('Su4', 'WaldoMart', '3033 Delaware Avenue', 'Francis', 7312, 44444444, 'waldo@gmail.com');
INSERT INTO SUPPLIERS VALUES ('Su5', 'Sport Goods', '1866 Wyatt Street', 'Lauderville', 8312, 55555555, 'sport@gmail.com');
/*
Customers (custID, shopNO, custLName, custFName, custAddress, custTown, custPostcode, custPhone,
custEMail)
*/
INSERT INTO CUSTOMERS VALUES ('C1', 'S1', 'Badge', 'Alex', '2105 Ripple Street', 'Oscoda', 1111, 11111110, 'abadge@gmail.com');
INSERT INTO CUSTOMERS VALUES ('C2', 'S1', 'Sutherland', 'Charles', '4987 Adams Drive', 'Hutson', 2222, 22222220, 'csuth@gmail.com');
INSERT INTO CUSTOMERS VALUES ('C3', 'S2', 'Coughlin', 'Thomas', '1934 Gambler Lane', 'Satt', 3333, 33333330, 'tcog@gmail.com');
INSERT INTO CUSTOMERS VALUES ('C4', 'S2', 'Gruen', 'Max', '3604 Drainer Avenue', 'Hassee', 4444, 44444440, 'mgruen@gmail.com');
INSERT INTO CUSTOMERS VALUES ('C5', 'S2', 'Mezger', 'Alexander', '1349 Kuhl Avenue', 'Gaines', 5555, 55555550, 'amez@gmail.com');
INSERT INTO CUSTOMERS VALUES ('C6', 'S3', 'Low', 'Jon!', '2696 Sycamore Road', 'Sutherlin', 6666, 66666660, 'jon!@gmail.com');
INSERT INTO CUSTOMERS VALUES ('C7', 'S3', 'Wylie', 'Tas', '4349 Parker Drive', 'Cleve', 7777, 77777770, 'twylie@gmail.com');
INSERT INTO CUSTOMERS VALUES ('C8', 'S3', 'Podolak', 'Bic', '4916 Timber Oak Drive', 'Plainview', 8888, 88888880, 'boddlegak@gmail.com');
INSERT INTO CUSTOMERS VALUES ('C9', 'S3', 'Xue', 'Simon', '776 Walnut Avenue', 'Rochelle Park', 9999, 99999990, 'sxue@gmail.com');
INSERT INTO CUSTOMERS VALUES ('C10', 'S3', 'Iwakura', 'Lain', '', '', '', '', '');
/*
Items (itemID, itemDescription, itemSize, itemColour, itemPrice, itemQuantityAvailable)
*/
INSERT INTO ITEMS VALUES ('I1', 'shoes', '8', 'BLUE', 150, 200);
INSERT INTO ITEMS VALUES ('I2', 'shirt', 'M', 'GREY', 70, 150);
INSERT INTO ITEMS VALUES ('I3','shorts', 'L', 'BLACK', 120, 180);
INSERT INTO ITEMS VALUES ('I4', 'socks', 'M', 'WHITE', 15, 220);
INSERT INTO ITEMS VALUES ('I5', 'singlet', 'S', 'RED', 90, 300);
INSERT INTO ITEMS VALUES ('I6', 'glasses', '', 'BLACK', 90, 250);
INSERT INTO ITEMS VALUES ('I7', 'tracksuit pants', 'M', 'PINK', 40, 500);
INSERT INTO ITEMS VALUES ('I8','dumbells', '', 'BLACK', 30, 60);
INSERT INTO ITEMS VALUES ('I9', 'foam roller', '', 'BLUE', 40, 80);
INSERT INTO ITEMS VALUES ('I10', 'resistance bands', '', 'RED', 20, 100);
INSERT INTO ITEMS VALUES ('I11', 'treadmill', '', 'BLACK', 1000, 20);
INSERT INTO ITEMS VALUES ('I12', 'exercise bike', '', 'GREY', 500, 40);
INSERT INTO ITEMS VALUES ('I13','jumper', 'L', 'WHITE', 100, 90);
INSERT INTO ITEMS VALUES ('I14', 'sports drink', '', '', 5, 300);
INSERT INTO ITEMS VALUES ('I15', 'water bottle', '', 'BLUE', 8, 250);
/*
Orders (orderNO, custNo, orderDate, dispatchDate)
*/
INSERT INTO ORDERS VALUES ('O1', 'C1', '01/MAR/52', '10/MAR/52');
INSERT INTO ORDERS VALUES ('O2', 'C1', '01/MAR/52', '10/MAR/52');
INSERT INTO ORDERS VALUES ('O3', 'C2', '01/APR/52', '10/APR/52');
INSERT INTO ORDERS VALUES ('O4', 'C2', '01/APR/52', '10/APR/52');
INSERT INTO ORDERS VALUES ('O5', 'C3', '01/MAY/52', '10/MAY/52');
INSERT INTO ORDERS VALUES ('O6', 'C3', '01/MAY/5', '10/MAY/52');
INSERT INTO ORDERS VALUES ('O7', 'C4', '01/JUN/5', '10/JUN/52');
INSERT INTO ORDERS VALUES ('O8', 'C4', '01/JUN/5', '10/JUN/52');
INSERT INTO ORDERS VALUES ('O9', 'C5', '01/JUL/5', '10/JUL/52');
INSERT INTO ORDERS VALUES ('O10', 'C5', '01/JUL/5', '10/JUL/52');
INSERT INTO ORDERS VALUES ('O11', 'C6', '01/AUG/5', '10/AUG/52');
INSERT INTO ORDERS VALUES ('O12', 'C6', '01/AUG/5', '10/AUG/52');
INSERT INTO ORDERS VALUES ('O13', 'C7', '01/SEP/5', '10/SEP/52');
INSERT INTO ORDERS VALUES ('O14', 'C7', '01/SEP/5', '10/SEP/52');
INSERT INTO ORDERS VALUES ('O15', 'C8', '01/OCT/5', '10/OCT/52');
INSERT INTO ORDERS VALUES ('O16', 'C8', '01/OCT/5', '10/OCT/52');
INSERT INTO ORDERS VALUES ('O17', 'C9', '01/NOV/5', '10/NOV/52');
INSERT INTO ORDERS VALUES ('O18', 'C9', '01/NOV/5', '10/NOV/52');
INSERT INTO ORDERS VALUES ('O19', 'C10', '01/DEC/5', '10/DEC/52');
INSERT INTO ORDERS VALUES ('O20', 'C10', '01/DEC/5', '10/DEC/52');
/*
orderQuantity (orderNo*, itemID*)
*/
INSERT INTO ORDERQUANTITY VALUES ('O1', 'I15');
INSERT INTO ORDERQUANTITY VALUES ('O2', 'I14');
INSERT INTO ORDERQUANTITY VALUES ('O3', 'I13');
INSERT INTO ORDERQUANTITY VALUES ('O4', 'I12');
INSERT INTO ORDERQUANTITY VALUES ('O5', 'I11');
INSERT INTO ORDERQUANTITY VALUES ('O6', 'I10');
INSERT INTO ORDERQUANTITY VALUES ('O7', 'I9');
INSERT INTO ORDERQUANTITY VALUES ('O8', 'I8');
INSERT INTO ORDERQUANTITY VALUES ('O9', 'I7');
INSERT INTO ORDERQUANTITY VALUES ('O10', 'I6');
INSERT INTO ORDERQUANTITY VALUES ('O11', 'I5');
INSERT INTO ORDERQUANTITY VALUES ('O12', 'I4');
INSERT INTO ORDERQUANTITY VALUES ('O13', 'I3');
INSERT INTO ORDERQUANTITY VALUES ('O14', 'I2');
INSERT INTO ORDERQUANTITY VALUES ('O15', 'I1');
INSERT INTO ORDERQUANTITY VALUES ('O16', 'I2');
INSERT INTO ORDERQUANTITY VALUES ('O17', 'I3');
INSERT INTO ORDERQUANTITY VALUES ('O18', 'I4');
INSERT INTO ORDERQUANTITY VALUES ('O19', 'I5');
INSERT INTO ORDERQUANTITY VALUES ('O20', 'I6');
/*
suppQuantity (itemID*, suppID*)
*/
INSERT INTO SUPPQUANTITY VALUES ('I1', 'Su1');
INSERT INTO SUPPQUANTITY VALUES ('I2', 'Su1');
INSERT INTO SUPPQUANTITY VALUES ('I3', 'Su1');
INSERT INTO SUPPQUANTITY VALUES ('I4', 'Su1');
INSERT INTO SUPPQUANTITY VALUES ('I5', 'Su1');
INSERT INTO SUPPQUANTITY VALUES ('I6', 'Su2');
INSERT INTO SUPPQUANTITY VALUES ('I7', 'Su2');
INSERT INTO SUPPQUANTITY VALUES ('I8', 'Su2');
INSERT INTO SUPPQUANTITY VALUES ('I9', 'Su2');
INSERT INTO SUPPQUANTITY VALUES ('I10', 'Su2');
INSERT INTO SUPPQUANTITY VALUES ('I11', 'Su3');
INSERT INTO SUPPQUANTITY VALUES ('I12', 'Su3');
INSERT INTO SUPPQUANTITY VALUES ('I13', 'Su3');
INSERT INTO SUPPQUANTITY VALUES ('I14', 'Su3');
INSERT INTO SUPPQUANTITY VALUES ('I15', 'Su3');
INSERT INTO SUPPQUANTITY VALUES ('I14', 'Su4');
INSERT INTO SUPPQUANTITY VALUES ('I14', 'Su5');
INSERT INTO SUPPQUANTITY VALUES ('I13', 'Su4');
INSERT INTO SUPPQUANTITY VALUES ('I1', 'Su4');
INSERT INTO SUPPQUANTITY VALUES ('I1', 'Su5');

----------

DROP TABLE shops CASCADE CONSTRAINTS;
DROP TABLE items CASCADE CONSTRAINTS;
DROP TABLE suppliers CASCADE CONSTRAINTS;
DROP TABLE customers CASCADE CONSTRAINTS;
DROP TABLE orders CASCADE CONSTRAINTS;
DROP TABLE orderQuantity CASCADE CONSTRAINTS;
DROP TABLE suppQuantity CASCADE CONSTRAINTS;

CREATE TABLE shops(
shopID VARCHAR2(3),
shopAddress VARCHAR2(30),
shopTown VARCHAR2(30),
shopPostcode number(9),
shopPhone number(9),
shopEMail VARCHAR2(30),
CONSTRAINT pk_shops PRIMARY KEY (shopID));

CREATE TABLE items(
itemID VARCHAR2(3),
itemDescription VARCHAR2(30),
itemSize VARCHAR2(30),
itemColour VARCHAR2(30),
itemPrice number(9),
itemQuantityAvailable number(9),
CONSTRAINT pk_items PRIMARY KEY (itemID));

CREATE TABLE suppliers(
suppID VARCHAR2(3),
suppName VARCHAR2(30),
suppAddress VARCHAR2(30),
suppTown VARCHAR2(30),
suppPostcode number(9),
suppPhone number(9),
suppEMail VARCHAR2(30),
CONSTRAINT pk_suppliers PRIMARY KEY (suppID));

CREATE TABLE customers(
custID VARCHAR2(3),
shopNO VARCHAR2(3),
custLName VARCHAR2(30),
custFName VARCHAR2(30),
custAddress VARCHAR2(30),
custTown VARCHAR2(30),
custPostcode number(9),
custPhone number(9),
custEMail VARCHAR2(30),
CONSTRAINT pk_customers PRIMARY KEY (custID),
CONSTRAINT fk_shopNO FOREIGN KEY (shopNO)
REFERENCES shops(shopID));

CREATE TABLE orders (
orderID VARCHAR2(3),
custNO VARCHAR2(3),
orderDate VARCHAR2(30),
dispatchDate VARCHAR2(30),
CONSTRAINT pk_orders PRIMARY KEY (orderID),
CONSTRAINT fk_custNO FOREIGN KEY (custNO)
REFERENCES customers (custID));

CREATE TABLE orderQuantity(
orderNO VARCHAR2(3),
itemNO VARCHAR2(3),
CONSTRAINT pk_itemorders PRIMARY KEY (orderNO, itemNO),
CONSTRAINT fk_IO1 FOREIGN KEY (orderNO)
REFERENCES orders (orderID),
CONSTRAINT fk_IO2 FOREIGN KEY (itemNO)
REFERENCES items (itemID));

CREATE TABLE suppQuantity(
itemNO VARCHAR2(3),
suppNO VARCHAR2(3),
CONSTRAINT pk_suppNUM PRIMARY KEY (suppNO, itemNO),
CONSTRAINT fk_suppNO FOREIGN KEY (suppNO)
REFERENCES suppliers (suppID),
CONSTRAINT fk_itemNO FOREIGN KEY (itemNO)
REFERENCES items (itemID));