CREATE TABLE SELLERS(	sellerID	INTEGER,
			rating	VARCHAR(2),
			email VARCHAR(64),
			PRIMARY KEY (sellerID));

CREATE TABLE ITEMS(	itemID	INTEGER,
			typeID	INTEGER,
			PRIMARY KEY (itemID));

CREATE TABLE STOCK(	itemID	INTEGER,
			startBid	FLOAT,
			qty INTEGER,
			PRIMARY KEY (itemID),
			FOREIGN KEY (itemID) REFERENCES ITEMS);

CREATE TABLE SOLDBY(	itemID	INTEGER,
			sellerID	INTEGER,
			FOREIGN KEY (itemID) REFERENCES ITEMS,
			FOREIGN KEY (sellerID) REFERENCES SELLERS);

CREATE TABLE DESCRIPTION(	itemID	INTEGER,
				descr	VARCHAR(256),
			PRIMARY KEY (itemID),
			FOREIGN KEY (itemID) REFERENCES ITEMS);

CREATE TABLE CUSTOMERS(	custID	INTEGER,
			address VARCHAR(128),
			PRIMARY KEY (custID));

CREATE TABLE PURCHASES(	purchaseID	INTEGER,
			itemID	INTEGER,
			custID	INTEGER,
			soldFor	FLOAT,
			quant	INTEGER,
			PRIMARY KEY (purchaseID),
			FOREIGN KEY (itemID) REFERENCES ITEMS,
			FOREIGN KEY (custID) REFERENCES CUSTOMERS);

INSERT INTO SELLERS(sellerID, rating, email)
VALUES (1, 'A+', 'zives@cis.upenn.edu');

INSERT INTO SELLERS(sellerID, rating, email)
VALUES (2, 'C', 'tjgreen@cis.upenn.edu');

INSERT INTO ITEMS(itemID, typeID)
VALUES (1, 1);

INSERT INTO ITEMS(itemID, typeID)
VALUES (2, 2);

INSERT INTO ITEMS(itemID, typeID)
VALUES (3, 1);

INSERT INTO STOCK(itemID, startBid, qty)
VALUES (1, 10.0, 100);

INSERT INTO STOCK(itemID, startBid, qty)
VALUES (2, 1.0, 50); 

INSERT INTO STOCK(itemID, startBid, qty)
VALUES (3, 50.0, 2);

INSERT INTO SOLDBY(itemID, sellerID)
VALUES (1, 1);

INSERT INTO SOLDBY(itemID, sellerID)
VALUES (2, 2);

INSERT INTO SOLDBY(itemID, sellerID)
VALUES (3, 1);

INSERT INTO DESCRIPTION(itemID, descr)
VALUES (1, 'Strong Bad Sings and other type hits');

INSERT INTO DESCRIPTION(itemID, descr)
VALUES (2, 'Homestarrunner coasters');

INSERT INTO DESCRIPTION(itemID, descr)
VALUES (3, 'The Cheat hoodie');

INSERT INTO CUSTOMERS(custID, address)
VALUES (1, '3330 Walnut St');

INSERT INTO PURCHASES(purchaseID, itemID, custID, soldFor, quant)
VALUES (1, 1, 1, 11.0, 1);


CREATE TABLE Region (	RegionID	INTEGER,
			Name	VARCHAR(32),
			PRIMARY KEY (RegionID));

CREATE TABLE Nation (	NationID	INTEGER,
			Name	VARCHAR(32),
			RegionID	INTEGER,
			PRIMARY KEY (NationID),
			FOREIGN KEY (RegionID) REFERENCES Region);

CREATE TABLE Part(	PartID	INTEGER,
			Name	VARCHAR(32),
			Mfgr	VARCHAR(32),
			Brand	VARCHAR(32),
			Type	VARCHAR(32),
			RetailPrice	FLOAT,
			PRIMARY KEY (PartID));

CREATE TABLE Supplier(	SuppID	INTEGER,
			Name	VARCHAR(32),
			Address	VARCHAR(128),
			NationID	INTEGER,
			Phone	VARCHAR(32),
			AcctBal	FLOAT,
			PRIMARY KEY (SuppID),
			FOREIGN KEY (NationID) REFERENCES Nation);

CREATE TABLE PartSupp(	PartID	INTEGER,
			SuppID	INTEGER,
			AvailQty	INTEGER,
			SupplyCost	FLOAT,
			FOREIGN KEY (PartID) REFERENCES Part,
			FOREIGN KEY (SuppID) REFERENCES Supplier);

INSERT INTO Region(RegionID, Name)
VALUES (1, 'Asia');

INSERT INTO Region(RegionID, Name)
VALUES (2, 'North America');

INSERT INTO Nation(NationID, Name, RegionID)
VALUES (1, 'China', 1);

INSERT INTO Nation(NationID, Name, RegionID)
VALUES (2, 'India', 1);

INSERT INTO Nation(NationID, Name, RegionID)
VALUES (3, 'Japan', 1);

INSERT INTO Nation(NationID, Name, RegionID)
VALUES (4, 'USA', 2);

INSERT INTO Nation(NationID, Name, RegionID)
VALUES (5, 'Canada', 2);

INSERT INTO Nation(NationID, Name, RegionID)
VALUES (6, 'Mexico', 2);

INSERT INTO Supplier(SuppID, Name, Address, NationID, Phone, AcctBal)
VALUES (1, 'Cava International', '20th and Washington, Phila, PA', 4, '555-5555', 40000);

INSERT INTO Supplier(SuppID, Name, Address, NationID, Phone, AcctBal)
VALUES (2, 'Donatucci Kitchens', '20th and Washington, Phila, PA', 4, '555-1212', 80000);

INSERT INTO Supplier(SuppID, Name, Address, NationID, Phone, AcctBal)
VALUES (3, 'Carter Tile', 'Shanghai', 1, '123-456-7890', 30000);

INSERT INTO Part(PartID, Name, Mfgr, Brand, Type, RetailPrice)
VALUES (1, 'Widget', 'Dupont', 'Dupont Home', 'generic', 10.0);

INSERT INTO Part(PartID, Name, Mfgr, Brand, Type, RetailPrice)
VALUES (2, 'toto', 'Dupont', 'Dupont Home', 'generic', 5.0);

INSERT INTO Part(PartID, Name, Mfgr, Brand, Type, RetailPrice)
VALUES (3, 'zaza', 'Dupont', 'Dupont Home', 'generic', 8.0);

INSERT INTO PartSupp(PartID, SuppID, AvailQty, SupplyCost)
VALUES (1, 1, 25, 75.0);

INSERT INTO PartSupp(PartID, SuppID, AvailQty, SupplyCost)
VALUES (2, 2, 13, 26.0);