DROP DATABASE IF EXISTS OOS;
CREATE DATABASE OOS;
USE OOS;

Create Table Category (
  ctid INT PRIMARY KEY,
  description VARCHAR(64)
);

Insert into Category(ctid,description) Values(1,'Office Supply');
Insert into Category(ctid,description) Values(2,'Computers Accessories ');
Insert into Category(ctid,description) Values(3,'Computer Games');
Insert into Category(ctid,description) Values(4,'Data Storage');
Insert into Category(ctid,description) Values(5,'Optical Disc Storage');
Insert into Category(ctid,description) Values(6,'Computer Input Device');
Insert into Category(ctid,description) Values(7,'Electronics');
Insert into Category(ctid,description) Values(8,'Computer Camera');
Insert into Category(ctid,description) Values(9,'Wearable Technology');
Insert into Category(ctid,description) Values(10,'Tracking Device');

Create Table Product (
  pid INT PRIMARY KEY,
  description VARCHAR(64),
  brand VARCHAR(32),
  ctid INT,
  rp INT,
  qoh INT,
  price FLOAT,
  FOREIGN KEY (ctid) REFERENCES Category (ctid)
);

Insert into Product(pid,description,brand,ctid,rp,qoh,price) Values(1,'2.4G Nano Wireless Mouse','TeckNet',1,30,20,9.95);
Insert into Product(pid,description,brand,ctid,rp,qoh,price) Values(2,'G230 Stereo Gaming Headset','Logitech',3,20,5,39.95);
Insert into Product(pid,description,brand,ctid,rp,qoh,price) Values(3,'64GB USB 3.0 Flash Drive','Samsung',4,50,75,17.95);
Insert into Product(pid,description,brand,ctid,rp,qoh,price) Values(4,'External USB DVD/CD','Epartsdom',5,20,10,12.45);
Insert into Product(pid,description,brand,ctid,rp,qoh,price) Values(5,'WaveRest Gel Mouse Pad','Belkin',2,20,25,15.95);
Insert into Product(pid,description,brand,ctid,rp,qoh,price) Values(6,'Blu-Ray/DVD External Burner Drive','Epartsdom',5,10,7,35.55);
Insert into Product(pid,description,brand,ctid,rp,qoh,price) Values(7,'4GB Cool Pizza Style USB Flash Drive','Panda',4,10,12,7.95);
Insert into Product(pid,description,brand,ctid,rp,qoh,price) Values(8,'Wireless Bluetooth Keyboard','TeckNet',6,10,8,12.95);
Insert into Product(pid,description,brand,ctid,rp,qoh,price) Values(9,'Ultra-Compact 14W Dual-Port Solar Charger','TeckNet',7,5,8,39.95);
Insert into Product(pid,description,brand,ctid,rp,qoh,price) Values(10,'Virtual Reality Headset','Samsung',3,8,12,95.95);
Insert into Product(pid,description,brand,ctid,rp,qoh,price) Values(11,'HD Webcam C310','Logitech',8,15,20,45.95);
Insert into Product(pid,description,brand,ctid,rp,qoh,price) Values(12,'HD Pro Webcam C920','Logitech',8,10,18,75.45);
Insert into Product(pid,description,brand,ctid,rp,qoh,price) Values(13,'16GB Mini Camera Pen','Dragonpad',8,5,7,21.95);
Insert into Product(pid,description,brand,ctid,rp,qoh,price) Values(14,'Moto 360 Sport - 45mm','Motorola',9,8,10,225.45);
Insert into Product(pid,description,brand,ctid,rp,qoh,price) Values(15,'Duet Bluetooth Tracker','PROTAG',10,10,15,31.95);

Create Table Customer (	
  cid INT PRIMARY KEY,	
  fn	VARCHAR(32),
  ln	VARCHAR(64),
  email	VARCHAR(64),
  phone	VARCHAR(32),
  address	VARCHAR(64),
  city	VARCHAR(64),
  state	CHAR(2),
  zip	CHAR(5)
);	

Insert into Customer(cid,fn,ln,email,phone,address,city,state,zip) Values(1,'Fox','Kran','fk@hbox.com','617-524-2692','392 Digital Avenue','San','TX','75960');
Insert into Customer(cid,fn,ln,email,phone,address,city,state,zip) Values(2,'Gem','Fran','gf@cbox.com','781-386-5375','831 Decision Branch','Tacoma','WA','98026');
Insert into Customer(cid,fn,ln,email,phone,address,city,state,zip) Values(3,'Ike','Pick','ip@bbox.com','508-961-3232','208 Speed Drive','Syracuse','NY','10523');
Insert into Customer(cid,fn,ln,email,phone,address,city,state,zip) Values(4,'Win','Miggy','wm@hbox.com','508-578-4130','163 Command Lane','Stockton','CA','90096');
Insert into Customer(cid,fn,ln,email,phone,address,city,state,zip) Values(5,'Yak','Ceron','yc@cbox.com','857-568-2158','521 Relational Drive','Vancouver','WA','98088');
Insert into Customer(cid,fn,ln,email,phone,address,city,state,zip) Values(6,'Zak','Beron','zb@cbox.com','508-387-2463','856 Command Lane','Bakersfield','CA','90048');
Insert into Customer(cid,fn,ln,email,phone,address,city,state,zip) Values(7,'Gin','Rum','gr@dbox.com','781-544-5154','660 Cyclical Loop','Pomona','CA','90070');
Insert into Customer(cid,fn,ln,email,phone,address,city,state,zip) Values(8,'Hal','Bar','hb@bbox.com','413-754-3796','471 Cyclical Loop','Lexington','KY','40401');
Insert into Customer(cid,fn,ln,email,phone,address,city,state,zip) Values(9,'Ian','Yan','iy@gbox.com','617-821-6219','173 Critical Path','Phoenix','AZ','85274');
Insert into Customer(cid,fn,ln,email,phone,address,city,state,zip) Values(10,'Cir','Cus','cc@gbox.com','978-743-2220','441 Critical Path','Las Cruces','NM','87030');
Insert into Customer(cid,fn,ln,email,phone,address,city,state,zip) Values(11,'Que','Book','qb@dbox.com','781-362-3479','286 Justice Place','McKinney','TX','75748');
Insert into Customer(cid,fn,ln,email,phone,address,city,state,zip) Values(12,'Xer','Xes','xx@bbox.com','617-525-9615','575 Speed Drive','Riverside','CA','90082');

Create Table ShoppingCart(
  scid INT PRIMARY KEY,
  poDate DATETIME,
  cid INT,
  FOREIGN KEY (cid) REFERENCES Customer (cid)
);

Insert into ShoppingCart(scid,poDate,cid) Values(1,'2018-01-01 23:03:54',2);
Insert into ShoppingCart(scid,poDate,cid) Values(2,'2018-01-01 19:53:52',2);
Insert into ShoppingCart(scid,poDate,cid) Values(3,'2018-01-02 18:44:54',8);
Insert into ShoppingCart(scid,poDate,cid) Values(4,'2018-01-02 18:20:24',12);
Insert into ShoppingCart(scid,poDate,cid) Values(5,'2018-01-02 22:06:56',5);
Insert into ShoppingCart(scid,poDate,cid) Values(6,'2018-01-04 16:19:14',11);
Insert into ShoppingCart(scid,poDate,cid) Values(7,'2018-01-05 08:16:58',5);
Insert into ShoppingCart(scid,poDate,cid) Values(8,'2018-01-05 14:13:05',2);
Insert into ShoppingCart(scid,poDate,cid) Values(9,'2018-01-05 06:49:36',1);
Insert into ShoppingCart(scid,poDate,cid) Values(10,'2018-01-05 06:41:23',2);
Insert into ShoppingCart(scid,poDate,cid) Values(11,'2018-01-05 10:42:19',11);
Insert into ShoppingCart(scid,poDate,cid) Values(12,'2018-01-06 23:53:30',10);
Insert into ShoppingCart(scid,poDate,cid) Values(13,'2018-01-07 05:43:47',3);
Insert into ShoppingCart(scid,poDate,cid) Values(14,'2018-01-07 15:41:46',10);
Insert into ShoppingCart(scid,poDate,cid) Values(15,'2018-01-08 15:25:27',10);
Insert into ShoppingCart(scid,poDate,cid) Values(16,'2018-01-08 08:00:26',1);
Insert into ShoppingCart(scid,poDate,cid) Values(17,'2018-01-10 23:49:05',5);
Insert into ShoppingCart(scid,poDate,cid) Values(18,'2018-01-12 05:59:18',6);
Insert into ShoppingCart(scid,poDate,cid) Values(19,'2018-01-14 14:36:00',2);
Insert into ShoppingCart(scid,poDate,cid) Values(20,'2018-01-14 09:56:15',10);

Create Table OrderItem (
  scid INT NOT NULL,
  pid INT NOT NULL,
  qty INT,
  FOREIGN KEY (scid) REFERENCES ShoppingCart (scid),
  FOREIGN KEY (pid) REFERENCES Product (pid),
  PRIMARY KEY(scid,pid)
);

Insert into OrderItem(scid,pid,qty) Values(1,1,2);
Insert into OrderItem(scid,pid,qty) Values(1,2,2);
Insert into OrderItem(scid,pid,qty) Values(2,7,2);
Insert into OrderItem(scid,pid,qty) Values(3,8,1);
Insert into OrderItem(scid,pid,qty) Values(3,9,1);
Insert into OrderItem(scid,pid,qty) Values(3,12,2);
Insert into OrderItem(scid,pid,qty) Values(4,1,1);
Insert into OrderItem(scid,pid,qty) Values(4,8,1);
Insert into OrderItem(scid,pid,qty) Values(5,12,1);
Insert into OrderItem(scid,pid,qty) Values(6,13,2);
Insert into OrderItem(scid,pid,qty) Values(7,3,1);
Insert into OrderItem(scid,pid,qty) Values(8,5,1);
Insert into OrderItem(scid,pid,qty) Values(8,8,1);
Insert into OrderItem(scid,pid,qty) Values(10,9,2);
Insert into OrderItem(scid,pid,qty) Values(10,13,1);
Insert into OrderItem(scid,pid,qty) Values(10,15,1);
Insert into OrderItem(scid,pid,qty) Values(11,14,2);
Insert into OrderItem(scid,pid,qty) Values(12,10,1);
Insert into OrderItem(scid,pid,qty) Values(13,7,3);
Insert into OrderItem(scid,pid,qty) Values(14,4,1);
Insert into OrderItem(scid,pid,qty) Values(14,9,2);
Insert into OrderItem(scid,pid,qty) Values(15,8,2);
Insert into OrderItem(scid,pid,qty) Values(16,8,1);
Insert into OrderItem(scid,pid,qty) Values(17,5,1);
Insert into OrderItem(scid,pid,qty) Values(17,8,1);
Insert into OrderItem(scid,pid,qty) Values(17,13,4);
Insert into OrderItem(scid,pid,qty) Values(17,15,1);
Insert into OrderItem(scid,pid,qty) Values(18,9,2);
Insert into OrderItem(scid,pid,qty) Values(19,7,2);
Insert into OrderItem(scid,pid,qty) Values(20,1,5);
Insert into OrderItem(scid,pid,qty) Values(20,3,2);
Insert into OrderItem(scid,pid,qty) Values(20,8,1);
Insert into OrderItem(scid,pid,qty) Values(20,13,1);