Create Tables and Add Constrains.sql

/* Because the query is pretty long, so I devided it into three parts, 
which are "tables creating", "constrains adding“ and "data inserting"*/

/*First Part - Table Creating*/

CREATE TABLE EMPLOYEE 
   (	EMPLOYEEID VARCHAR2(5) NOT NULL, 
	EMPLOYEEFIRSTNAME VARCHAR2(25) NOT NULL, 
	EMPLOYEELASTNAME VARCHAR2(25) NOT NULL, 
  EMPLOYEEPHONE NUMBER(10), 
	EMPLOYEESTREET VARCHAR2(50), 
	EMPLOYEECITY VARCHAR2(25), 
	EMPLOYEESTATE CHAR(2), 
	EMPLOYEEZIP_CODE VARCHAR2(9), 
	EMPLOYEEEMAIL VARCHAR2(50), 
	EMPLOYEEDOB DATE, 
	EMPLOYEEHIROFEDATE DATE DEFAULT SYSDATE NOT NULL, 
	EMPLOYEESSN NUMBER(9) NOT NULL, 
	EMPLOYEESTATUS CHAR(2), 
	EMPMANAGERID VARCHAR2(5), 
	 CONSTRAINT "EMPLOYEE_PK" PRIMARY KEY ("EMPLOYEEID"));
   
   
CREATE TABLE STORE 
   (	STOREID VARCHAR2(5) NOT NULL, 
	STORESTREET VARCHAR2(50), 
	STORECITY VARCHAR2(25), 
	STORESTATE CHAR(2), 
	STOREZIP_CODE VARCHAR2(9), 
	 CONSTRAINT "STORE_PK" PRIMARY KEY ("STOREID"));
   

CREATE TABLE TAX 
   (	STATE CHAR(2) NOT NULL, 
	TAX_RATE NUMBER(2,2), 
	 CONSTRAINT TAX_PK PRIMARY KEY (STATE));


CREATE TABLE HOLD
   (	STOREID VARCHAR2(5) NOT NULL, 
	PRODUCTID VARCHAR2(5) NOT NULL, 
	QTYHELD NUMBER(4), 
	 CONSTRAINT HOLD_PK PRIMARY KEY (STOREID, PRODUCTID));


CREATE TABLE REVIEW 
   (	REVIEWID VARCHAR2(5) NOT NULL, 
	REVIEWDATE DATE DEFAULT SYSDATE NOT NULL, 
	REVIEWRATING CHAR(1) NOT NULL, 
	REVIEWDESCRIPTION VARCHAR2(250), 
	PRODUCTID VARCHAR2(5) NOT NULL, 
	CUSTOMERID VARCHAR2(5) NOT NULL, 
	 CONSTRAINT REVIEW_PK PRIMARY KEY (REVIEWID));


CREATE TABLE CUSTOMER 
   (	CUSTOMERID VARCHAR2(5) NOT NULL, 
	CUSTOMERFIRSTNAME VARCHAR2(25) NOT NULL, 
	CUSTOMERLASTNAME VARCHAR2(25) NOT NULL, 
	CUSTOMERPHONE NUMBER(10), 
	CUSTOMERSTREET VARCHAR2(50), 
	CUSTOMERCITY VARCHAR2(25), 
	CUSTOMERSTATE CHAR(2), 
	CUSTOMERZIP_CODE VARCHAR2(9), 
	CUSTOMEREMAIL VARCHAR2(50), 
	 CONSTRAINT CUSTOMER_PK PRIMARY KEY (CUSTOMERID));


CREATE TABLE MEMBER
   (	MEMBERID VARCHAR2(5) NOT NULL, 
	MEMBERDRIVERLICENSENO VARCHAR2(9), 
	ENROLLOFDATE DATE DEFAULT SYSDATE, 
	 CONSTRAINT MEMBER_PK PRIMARY KEY (MEMBERID));


CREATE TABLE NUTRITION 
   (	PRODUCTID VARCHAR2(5), 
	TTLCALORIES NUMBER(4), 
	FATCALORIES NUMBER(4), 
	TTLFAT NUMBER(4), 
	SATFAT NUMBER(4), 
	TRANSFAT NUMBER(4), 
	CHOLESTEROL NUMBER(4), 
	SODIUM NUMBER(4), 
	TTLCARBOHYDRATES NUMBER(4), 
	DIETARYFIBER NUMBER(4), 
	SUGARS NUMBER(4), 
	PROTEIN NUMBER(4), 
  PRIMARY KEY (PRODUCTID));
  

CREATE TABLE PRODUCT
   (	PRODUCTID VARCHAR2(5) NOT NULL, 
	PRODUCTNAME VARCHAR2(25), 
  PRODUCTDESCRIPTION VARCHAR2(50);

	


CREATE TABLE SUPPLIER
   (	SUPPLIERID VARCHAR2(2) NOT NULL, 
	SUPPLIERNAME VARCHAR2(20) NOT NULL, 
  SUPPLIERPHONE NUMBER(10), 
	SUPPLIERSTREET VARCHAR2(50), 
	SUPPLIERCITY VARCHAR2(20), 
	SUPPLIERSTATE CHAR(2), 
	SUPPLIERZIP_CODE VARCHAR2(9), 
	EMPLOYEEEMAIL VARCHAR2(50), 
	 CONSTRAINT SUPPLIER_PK PRIMARY KEY (SUPPLIERID));
   

CREATE TABLE DEPARTMENT
   (	"DEPARTMENTID" VARCHAR2(5) NOT NULL, 
	"DEPARTMENTNAME" VARCHAR2(30), 
	 CONSTRAINT "DEPT_PK" PRIMARY KEY ("DEPARTMENTID"));



/*Second part - Constrains Adding*/


ALTER TABLE EMPLOYEE 
ADD CONSTRAINT EMPLOYEE_FK FOREIGN KEY (EMPMANAGERID)
	  REFERENCES EMPLOYEE (EMPLOYEEID);

ALTER TABLE EMPLOYEE 
ADD STOREID VARCHAR2(5);

ALTER TABLE EMPLOYEE 
ADD	CONSTRAINT EMPLOYEE_STORE_FK FOREIGN KEY (STOREID)
	  REFERENCES STORE (STOREID);



ALTER TABLE STORE
ADD MANAGERID VARCHAR2(5);

ALTER TABLE STORE
ADD CONSTRAINT STORE_FK FOREIGN KEY (MANAGERID)
	  REFERENCES EMPLOYEE (EMPLOYEEID);



ALTER TABLE HOLD
ADD CONSTRAINT HOLDSTORE_FK FOREIGN KEY (STOREID)
	  REFERENCES STORE (STOREID);

ALTER TABLE HOLD
ADD CONSTRAINT HOLDPRODUCT_FK FOREIGN KEY (PRODUCTID)
    REFERENCES PRODUCT (PRODUCTID);



ALTER TABLE REVIEW
ADD CONSTRAINT REVIEW_PRODUCT_FK FOREIGN KEY (PRODUCTID)
	  REFERENCES PRODUCT ("PRODUCTID);

ALTER TABLE REVIEW
ADD CONSTRAINT REVIEW_CUSTOMER_FK FOREIGN KEY (CUSTOMERID)
	  REFERENCES CUSTOMER (CUSTOMERID);



ALTER TABLE CUSTOMER
ADD MEMBERID VARCHAR2(5);

ALTER TABLE CUSTOMER
ADD CONSTRAINT CUSTOMER_FK FOREIGN KEY (MEMBERID)
	  REFERENCES MEMBER (MEMBERID);
    


ALTER TABLE NUTRITION
ADD CONSTRAINT NUTRITION_FK FOREIGN KEY (PRODUCTID)
	  REFERENCES PRODUCT (PRODUCTID);
    
    
    
ALTER TABLE PRODUCT
ADD	SUPPLIERID VARCHAR2(5);

ALTER TABLE PRODUCT
ADD CONSTRAINT PRODUCTSUPPER_FK FOREIGN KEY (SUPPLIERID)
	  REFERENCES SUPPLIER (SUPPLIERID); 


ALTER TABLE PRODUCT
ADD	STOREID VARCHAR2(5);

ALTER TABLE PRODUCT
ADD CONSTRAINT PRODUCTSTORE_FK FOREIGN KEY (STOREID)
	  REFERENCES STORE (STOREID);
    
ALTER TABLE PRODUCT
ADD DEPARTMENTID VARCHAR2(5);

ALTER TABLE PRODUCT
ADD CONSTRAINT PRODUCTDEPT_FK FOREIGN KEY (DEPARTMENTID)
	  REFERENCES DEPARTMENT (DEPARTMENTID);



/*Third Part - Data Inserting*/
   
INSERT INTO CUSTOMER (CUSTOMERID, CUSTOMERFIRSTNAME, CUSTOMERLASTNAME, CUSTOMERPHONE, CUSTOMERSTREET, CUSTOMERCITY, 
CUSTOMERSTATE, CUSTOMERZIP_CODE,CUSTOMEREMAIL)
VALUES ('11811', 'Sabrina', 'Fernandez', '6168663901', '49 Blackstone Dr', 'Lexington', 'MA', '02421', 'smaloney2@milocal123.com');

INSERT INTO CUSTOMER (CUSTOMERID, CUSTOMERFIRSTNAME, CUSTOMERLASTNAME, CUSTOMERPHONE, CUSTOMERSTREET, CUSTOMERCITY, 
CUSTOMERSTATE, CUSTOMERZIP_CODE,CUSTOMEREMAIL)
VALUES ('11112', 'Derek', 'Schwimmer', '6163920622', '102 Pineview Rd', 'Concord', 'MA', '01742', 'ohawes7@milocal123.com');

INSERT INTO CUSTOMER (CUSTOMERID, CUSTOMERFIRSTNAME, CUSTOMERLASTNAME, CUSTOMERPHONE, CUSTOMERSTREET, CUSTOMERCITY, 
CUSTOMERSTATE, CUSTOMERZIP_CODE,CUSTOMEREMAIL)
VALUES('23653', 'Melissa', 'Caputo', '2699851122', '302 Main St', 'Somerville', 'MA', '02143', 'mcaputo12@milocal123.com' );

INSERT INTO CUSTOMER (CUSTOMERID, CUSTOMERFIRSTNAME, CUSTOMERLASTNAME, CUSTOMERPHONE, CUSTOMERSTREET, CUSTOMERCITY, 
CUSTOMERSTATE, CUSTOMERZIP_CODE,CUSTOMEREMAIL)
VALUES('34532', 'Amol', 'Mehta', '6163961972', '54 Lakeshore Ave', 'North Billerica', 'MA', '01862', 'amehta3@milocal123.com');

INSERT INTO CUSTOMER (CUSTOMERID, CUSTOMERFIRSTNAME, CUSTOMERLASTNAME, CUSTOMERPHONE, CUSTOMERSTREET, CUSTOMERCITY, 
CUSTOMERSTATE, CUSTOMERZIP_CODE,CUSTOMEREMAIL)
VALUES('11321','John', 'Weiss', '6166377783', '456 Winchester St,','Reading', 'MA', '01867',  'jweiss72@milocal123.com');

INSERT INTO CUSTOMER (CUSTOMERID, CUSTOMERFIRSTNAME, CUSTOMERLASTNAME, CUSTOMERPHONE, CUSTOMERSTREET, CUSTOMERCITY, 
CUSTOMERSTATE, CUSTOMERZIP_CODE,CUSTOMEREMAIL)
VALUES('13462', 'Karen', 'Brien', '5174839244', '38 Langley Rd', 'Maynard', 'MA', '01754', 'kobrien4@milocal123.com');

INSERT INTO CUSTOMER (CUSTOMERID, CUSTOMERFIRSTNAME, CUSTOMERLASTNAME, CUSTOMERPHONE, CUSTOMERSTREET, CUSTOMERCITY, 
CUSTOMERSTATE, CUSTOMERZIP_CODE,CUSTOMEREMAIL)
VALUES('19868', 'Devin', 'Finnerty', '6163931228', '78 E 8th St', 'Framingham',   'MA', '01701', 'dfinnerty1@finns78.com');

INSERT INTO CUSTOMER (CUSTOMERID, CUSTOMERFIRSTNAME, CUSTOMERLASTNAME, CUSTOMERPHONE, CUSTOMERSTREET, CUSTOMERCITY, 
CUSTOMERSTATE, CUSTOMERZIP_CODE,CUSTOMEREMAIL)
VALUES('16738','Kathy', 'Rowe', '6168424603', '29 Grahams Ln', 'Withrop', 'MA', '02152', 'krowe47@hhdc123.com');

INSERT INTO CUSTOMER (CUSTOMERID, CUSTOMERFIRSTNAME, CUSTOMERLASTNAME, CUSTOMERPHONE, CUSTOMERSTREET, CUSTOMERCITY, 
CUSTOMERSTATE, CUSTOMERZIP_CODE,CUSTOMEREMAIL)
VALUES('10985', 'Pam', 'Wallner', '2698571276', '80 Cedar St', 'Lexington', 'MA', '02421', 'pwallner2@milocal123.com');

INSERT INTO CUSTOMER (CUSTOMERID, CUSTOMERFIRSTNAME, CUSTOMERLASTNAME, CUSTOMERPHONE, CUSTOMERSTREET, CUSTOMERCITY, 
CUSTOMERSTATE, CUSTOMERZIP_CODE,CUSTOMEREMAIL)
VALUES('14420', 'Alex', 'Engber', '5174821400', '505 Washington Ave',   'Concord', 'MA', '01742', 'aengber2@mgrant5.com');

INSERT INTO CUSTOMER (CUSTOMERID, CUSTOMERFIRSTNAME, CUSTOMERLASTNAME, CUSTOMERPHONE, CUSTOMERSTREET, CUSTOMERCITY, 
CUSTOMERSTATE, CUSTOMERZIP_CODE,CUSTOMEREMAIL)
VALUES('25786', 'Susan', 'Darcy', '6169880777', '144 E Tower Ave', 'Somerville', 'MA', '02143', 'sdarcy33@rvdc3.com');

INSERT INTO CUSTOMER (CUSTOMERID, CUSTOMERFIRSTNAME, CUSTOMERLASTNAME, CUSTOMERPHONE, CUSTOMERSTREET, CUSTOMERCITY, 
CUSTOMERSTATE, CUSTOMERZIP_CODE,CUSTOMEREMAIL)
VALUES('37711', 'Michael', 'Ingram', '2693379208', '10 Lincoln Dr', 'Boston', 'MA', '02135',   'mingram8@milocal123.com');

INSERT INTO CUSTOMER (CUSTOMERID, CUSTOMERFIRSTNAME, CUSTOMERLASTNAME, CUSTOMERPHONE, CUSTOMERSTREET, CUSTOMERCITY, 
CUSTOMERSTATE, CUSTOMERZIP_CODE,CUSTOMEREMAIL)
VALUES('29975', 'Rachel', 'Kirk', '6169881320', '40 Monroe St', 'North Billerica', 'MA', '01862', 'rkirk9@msc25.edu');

INSERT INTO CUSTOMER (CUSTOMERID, CUSTOMERFIRSTNAME, CUSTOMERLASTNAME, CUSTOMERPHONE, CUSTOMERSTREET, CUSTOMERCITY, 
CUSTOMERSTATE, CUSTOMERZIP_CODE,CUSTOMEREMAIL)
VALUES('27758', 'James', 'Blackhawk', '2693437509', '3047 Millbrook Ave', 'Reading', 'MA', '01867', 'jblackhawk7@knd89.gov');

INSERT INTO CUSTOMER (CUSTOMERID, CUSTOMERFIRSTNAME, CUSTOMERLASTNAME, CUSTOMERPHONE, CUSTOMERSTREET, CUSTOMERCITY, 
CUSTOMERSTATE, CUSTOMERZIP_CODE,CUSTOMEREMAIL)
VALUES('25557', 'Olivia', 'Pappas', '6166376591', '4 N Orchard St', 'Wakefield', 'MA', '01880', 'opappas2@milocal123.com');



INSERT INTO STORE (STOREID, STOREZIP)
VALUES ('08', '02145');

INSERT INTO STORE (STOREID, STOREZIP)
VALUES ('05', '02265');

INSERT INTO STORE (STOREID, STOREZIP)
VALUES ('06', '02262');

INSERT INTO STORE (STOREID, STOREZIP)
VALUES ('21', '02267');

INSERT INTO STORE (STOREID, STOREZIP)
VALUES ('22', '02221');

INSERT INTO STORE (STOREID, STOREZIP)
VALUES ('15', '02271');

INSERT INTO STORE (STOREID, STOREZIP)
VALUES ('26', '02495');

INSERT INTO STORE (STOREID, STOREZIP)
VALUES ('23', '03565');

INSERT INTO STORE (STOREID, STOREZIP)
VALUES ('52', '02465');

INSERT INTO STORE (STOREID, STOREZIP)
VALUES ('50', '05465');




INSERT INTO SUPPLIER (SUPPLIERID, SUPPLIERNAME)
VALUES ('01', 'NEW ENGLAND FOOD');

INSERT INTO SUPPLIER (SUPPLIERID, SUPPLIERNAME)
VALUES ('02', 'AMERICAN FOOD CORP');

INSERT INTO SUPPLIER (SUPPLIERID, SUPPLIERNAME)
VALUES ('03', 'AXE FOOD SUPPLLY');

INSERT INTO SUPPLIER (SUPPLIERID, SUPPLIERNAME)
VALUES ('04', 'FARMERS CHOICE');

INSERT INTO SUPPLIER (SUPPLIERID, SUPPLIERNAME)
VALUES ('05', 'BEST CHOICE');

INSERT INTO SUPPLIER (SUPPLIERID, SUPPLIERNAME)
VALUES ('06', 'WALTHAM FOOD CORP');

INSERT INTO SUPPLIER (SUPPLIERID, SUPPLIERNAME)
VALUES ('07', 'WORLDWIDE FOODCHAIN');

INSERT INTO SUPPLIER (SUPPLIERID, SUPPLIERNAME)
VALUES ('08', 'BOSTON DRINK');

INSERT INTO SUPPLIER (SUPPLIERID, SUPPLIERNAME)
VALUES ('09', 'NEW FRUIT CORP');

INSERT INTO SUPPLIER (SUPPLIERID, SUPPLIERNAME)
VALUES ('10', 'GAS CORP');



INSERT INTO DEPARTMENT (DEPARTMENTID, DEPARTMENTNAME) 
VALUES ('1', 'GAS');

INSERT INTO DEPARTMENT (DEPARTMENTID, DEPARTMENTNAME) 
VALUES ('2', 'BEVERAGE');

INSERT INTO DEPARTMENT (DEPARTMENTID, DEPARTMENTNAME) 
VALUES ('3', 'FOOD');



INSERT INTO PRODUCT (PRODUCTID, PRODUCTNAME, SUPPLIERID, STOREID, PRODUCTDEPTID)
VALUES ('CZsa2', 'Sour Apple Shock - 20 oz', '08', '21','2');

INSERT INTO PRODUCT (PRODUCTID, PRODUCTNAME, SUPPLIERID, STOREID, PRODUCTDEPTID)
VALUES ('CZsa3', 'Sour Apple Shock - 30 oz', '08', '21','2');

INSERT INTO PRODUCT (PRODUCTID, PRODUCTNAME, SUPPLIERID, STOREID, PRODUCTDEPTID)
VALUES ('CZsp2', 'Sucka Punch - 20 oz', '08', '15','2');

INSERT INTO PRODUCT (PRODUCTID, PRODUCTNAME, SUPPLIERID, STOREID, PRODUCTDEPTID)
VALUES ('CZsp3', 'Sucka Punch - 30 oz', '08', '15','2');

INSERT INTO PRODUCT (PRODUCTID, PRODUCTNAME, SUPPLIERID, STOREID, PRODUCTDEPTID)
VALUES ('CZps2', 'Pinka-Saurus - 20 oz', '08', '15','2');


INSERT INTO PRODUCT (PRODUCTID, PRODUCTNAME, SUPPLIERID, STOREID, PRODUCTDEPTID)
VALUES ('SIct3', ' Chicken Tenders-3 piece', '01', '08','3');

INSERT INTO PRODUCT (PRODUCTID, PRODUCTNAME, SUPPLIERID, STOREID, PRODUCTDEPTID)
VALUES ('SIct5', ' Chicken Tenders-5 piece', '01', '08','3');

INSERT INTO PRODUCT (PRODUCTID, PRODUCTNAME, SUPPLIERID, STOREID, PRODUCTDEPTID)
VALUES ('SIcw1', ' Bone-In Chicken Wings ', '01', '08','3');

INSERT INTO PRODUCT (PRODUCTID, PRODUCTNAME, SUPPLIERID, STOREID, PRODUCTDEPTID)
VALUES ('SIcf3', ' Chicken Fingers-3 piece', '01', '08','3');

INSERT INTO PRODUCT (PRODUCTID, PRODUCTNAME, SUPPLIERID, STOREID, PRODUCTDEPTID)
VALUES ('BegBc', ' Egg, Bacon Croissant', '02', '08','3');

INSERT INTO PRODUCT (PRODUCTID, PRODUCTNAME, SUPPLIERID, STOREID, PRODUCTDEPTID)
VALUES ('BeaAb', ' Muen Cheese onAsiago ', '02', '08','3');

INSERT INTO PRODUCT (PRODUCTID, PRODUCTNAME, SUPPLIERID, STOREID, PRODUCTDEPTID)
VALUES ('BeaBa', ' Angus Steak on a Bagel', '02', '08','3');

INSERT INTO PRODUCT (PRODUCTID, PRODUCTNAME, SUPPLIERID, STOREID, PRODUCTDEPTID)
VALUES ('BeeBm', 'Bacon English Muffin ', '02', '08','3');

INSERT INTO PRODUCT (PRODUCTID, PRODUCTNAME, SUPPLIERID, STOREID, PRODUCTDEPTID)
VALUES ('BwcEm', 'Canadian Bacon Egg Whites', '02', '08','3');



/* I list what I left as below, I will keep working on those problems*
1. The constrain for PRODUCTNAME limited the lengh of the original name, need find a way to change it.
2. The subtype for department I didn't add yet, I know on BB, there is a document for it. 
3. I check the price for what I insert for product, I paste as below. I find the price via internet picture of cumberlandfarms,
   so they are very closed to the real world. 

Chill Zone

CZsa2   Sour Apple Shock - 20 oz        0.99
CZsa3   Sour Apple Shock - 30 oz        0.99
CZsp2   Sucka Punch - 20 oz     0.99
CZsq3   Sucka Punch - 30 oz     0.99
CZps2   Pinka-Saurus - 20 oz    0.99

 SIDES

 SIct3   Chicken Tenders - 3 piece       1.39 
 SIct5   Chicken Tenders - 5 piece       2.39 
 SIcw1   Bone-In Chicken Wings   2.39 
 SIcf3   Spicy Chicken Fingers - 3 piece 1.39 
 SIcf5   Spicy Chicken Fingers - 5 piece 2.39 
BREAKFAST

 BegBc   Egg, Sausage and Bacon Croissant        2.39
 BeaAb   Angus Steak, Egg & Muenster Cheese on an Asiago Bagel   3.49
 BeaBa   Angus Steak, Egg & Cheese on a Bagel    3.49
 BeeBm   Egg, Cheese and Bacon English Muffin    2.29

 BwcEm   Canadian Bacon, Egg Whites & Cheese on an English Muffin        2.39

PASTRY CASE

PCafr   Apple Fritter   1.39
PCatu   Apple Turnover  1.39
PCbbm   Berry Blast Muffin      1.49
PCbcm   Blueberry Crumb Muffin  1.49
PCbld   Blueberry Donut 0.99




 SUBS & SANDWICHES

SSabc   Angus Bacon Cheeseburger        2.39
SSacb   Angus Cheeseburger      2.19
SSbcs   Buffalo Chicken Sandwich        2.39
SSbcw   Buffalo Chicken Wrap    2.19
SSscs   Southern Style Chicken Sandwich 2.39 */