Wine Database System
SQL Relational Database: Table Creation

Database Table Creation

CREATE TABLE CUSTOMER_ZIP_T
(Zip_Code VARCHAR(9) ,
Customer_City VARCHAR(25) ,
Customer_State VARCHAR(2) ,
CONSTRAINT CUSTOMER_ZIP_PK PRIMARY KEY (Zip_Code));

CREATE TABLE CUSTOMER_T
(Customer_Id NUMBER NOT NULL,
Customer_Name VARCHAR(25) ,
Customer_Email VARCHAR(40) ,
DOB DATE ,
Customer_Address VARCHAR(30) ,
Zip_Code VARCHAR(9) ,
CONSTRAINT CUSTOMER_PK PRIMARY KEY (Customer_Id),
CONSTRAINT CUSTOMER_FK FOREIGN KEY (Zip_Code) REFERENCES CUSTOMER_ZIP_T (Zip_Code));

CREATE TABLE ORDER_T
(Order_ID VARCHAR(25) NOT NULL,
Order_Date DATE ,
Customer_ID number ,
CONSTRAINT ORDER_PK PRIMARY KEY (Order_ID),
CONSTRAINT ORDER_FK FOREIGN KEY (Customer_ID) REFERENCES CUSTOMER_T (Customer_ID));

CREATE TABLE PRODUCT_T
(Bar_Code number NOT NULL,
Product_Quantity number,
Varietal VARCHAR(30),
Product_name VARCHAR(30),
Description VARCHAR (300),
Price number,
Alcohol number,
Appellation VARCHAR(50),
Vintage number,
CONSTRAINT PRODUCT_PK PRIMARY KEY (BAR_CODE));

CREATE TABLE ORDER_LINE_T
(Order_Line_ID number NOT NULL,
Bar_Code number,
order_ID VARCHAR(25),
order_quantity number,
CONSTRAINT ORDER_LINE_PK PRIMARY KEY (ORDER_LINE_ID),
CONSTRAINT Order_line_FK1 FOREIGN KEY (Order_Id) REFERENCES ORDER_t(Order_Id),
CONSTRAINT Order_line_FK2 FOREIGN KEY (BAR_CODE) REFERENCES PRODUCT_t(BAR_CODE));

CREATE TABLE PRODUCT_RATING_T
(BAR_CODE NUMBER NOT NULL,
RATING NUMBER,
CONSTRAINT PRODUCT_RATING_PK PRIMARY KEY (BAR_CODE),
CONSTRAINT PRODUCT_RATING_FK FOREIGN KEY (BAR_CODE) REFERENCES PRODUCT_T(BAR_CODE));

CREATE TABLE SUPPLIER_ZIP_T
(Zip_Code VARCHAR(9) ,
SUPPLIER_City VARCHAR(25) ,
SUPPLIER_State VARCHAR(2) ,
CONSTRAINT SUPPLIER_ZIP_PK PRIMARY KEY (Zip_Code));

CREATE TABLE SUPPLIER_T
(SUPPLIER_Id number NOT NULL,
SUPPLIER_Name VARCHAR(25) ,
SUPPLIER_Email VARCHAR(40) ,
SUPPLIER_Address VARCHAR(30) ,
Zip_Code VARCHAR(9) ,
CONSTRAINT SUPPLIER_PK PRIMARY KEY (SUPPLIER_Id),
CONSTRAINT SUPPLIER_FK FOREIGN KEY (Zip_Code) REFERENCES SUPPLIER_ZIP_T (Zip_Code));

CREATE TABLE SHIPMENT_T
(SHIPMENT_ID NUMBER NOT NULL,
SHIPMENT_DATE DATE,
SUPPLIER_ID NUMBER,
CONSTRAINT SHIPMENT_PK PRIMARY KEY (SHIPMENT_ID),
CONSTRAINT SHIPMENT_FK FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIER_T (SUPPLIER_ID));

CREATE TABLE INCLUDES_T
(BAR_CODE NUMBER NOT NULL,
SHIPMENT_ID NUMBER,
CONSTRAINT INCLUDES_PK PRIMARY KEY (BAR_CODE, SHIPMENT_ID),
CONSTRAINT INCLUDES_FK1 FOREIGN KEY (BAR_CODE) REFERENCES PRODUCT_RATING_T (BAR_CODE),
CONSTRAINT INCLUDES_FK2 FOREIGN KEY (SHIPMENT_ID) REFERENCES SHIPMENT_T (SHIPMENT_ID));

Database Data Insertion

INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (32601, 'St. Paul', 'VA');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (94574, 'Hampton', 'NY');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (92115, 'San Diego', 'CA');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (26559, 'Los Angeles', 'CA');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (46887, 'Detroit', 'MI');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (33668, 'Cleveland', 'OH');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (11254, 'Sacramento', 'CA');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (53421, 'Washington', 'WA');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (23445, 'Fairfield', 'CA');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (19854, 'Maui', 'HA');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (56448, 'Springfield', 'MA');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (61237, 'Boston', 'MA');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (62738, 'Hometown', 'WI');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (92345, 'Houston', 'TX');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (23465, 'Ashland', 'OR');

INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (1, 'Greg Banks', 'gbanks@spamail.com', '12/OCT/1972', '2078 W. 17th St.', 32601);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (2, 'Kelly Slater', 'kslater@spamail.com', '02/NOV/1948', '1955 4th Ave.', 94574);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (3, 'John Deer', 'jdeer@spamail.com', '10/DEC/1939', '2665 Dove St.', 92115);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (4, 'Mohammad Oskoorouchi', 'moskoorouchi@spamail.com', '03/SEP/1954', '1514 Castelar St.', 26559);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (5, 'Monique Johnson', 'mjohnson@spamail.com', '07/FEB/1978', '2054 El Cajon Blvd.', 46887);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (6, 'Kimihiko Nakamura', 'knakamura@spamail.com', '05/MAY/1974', '188 University Ave.', 33668);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (7, 'Mason Dixon', 'mdixon@spamail.com', '03/OCT/1767', '6542 Hunt Ave.', 11254);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (8, 'Fred Cummings', 'fcummings@spamail.com', '10/FEB/1981', '1955 Allyn Ave.', 53421);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (9, 'Yi Sun', 'ysun@spamail.com', '14/AUG/1962', '1744 Jason St.', 23445);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (10,'Ernest Wendt', 'ewendt@spamail.com', '03/SEP/1958', '85 Spring St.', 19854);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (11,'Shaoyi He', 'she@spamail.com', '19/JAN/1962', '85 East Hwy.', 56448);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (12,'William Burns', 'wburns@spamail.com', '17/JUL/1955', '2233 Space St.', 61237);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (13,'Jack Leu', 'jleu@spamail.com', '28/JUN/1960', '4652 Mason Blvd.', 62738);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (14,'Jim Hamerly', 'jhamerly@spamail.com', '29/MAR/1959', '2386 First St.', 92345);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (15,'Robert Aboolian', 'raboolian@spamail.com', '01/APR/1955', '6687 Second Ave.', 23465);

INSERT INTO ORDER_t (Order_Id, Order_Date, Customer_Id)
VALUES (1001, '21/AUG/2004', 1);
INSERT INTO ORDER_t (Order_Id, Order_Date, Customer_Id)
VALUES (1002, '16/SEP/2004', 8);
INSERT INTO ORDER_t (Order_Id, Order_Date, Customer_Id)
VALUES (1003, '20/SEP/04', 15);
INSERT INTO ORDER_t (Order_Id, Order_Date, Customer_Id)
VALUES (1004, '04/Oct/04', 5);
INSERT INTO ORDER_t (Order_Id, Order_Date, Customer_Id)
VALUES (1005, '10/Oct/04', 3);
INSERT INTO ORDER_t (Order_Id, Order_Date, Customer_Id)
VALUES (1006, '13/Oct/04', 2);
INSERT INTO ORDER_t (Order_Id, Order_Date, Customer_Id)
VALUES (1007, '25/Oct/04', 11);
INSERT INTO ORDER_t (Order_Id, Order_Date, Customer_Id)
VALUES (1008, '30/Oct/04', 12);
INSERT INTO ORDER_t (Order_Id, Order_Date, Customer_Id)
VALUES (1009, '03/Nov/04', 4);
INSERT INTO ORDER_t (Order_Id, Order_Date, Customer_Id)
VALUES (1010, '06/Nov/04', 9);

INSERT INTO PRODUCT_t (Bar_Code, Product_Quantity, Varietal, Product_name, Description, Price, Alcohol, Appellation, Vintage)
VALUES (12113, 48, 'Merlot', 'Dynamite Vineyards Merlot', 'Explosive fruit flavors and intriguing aromas of plums, black cherries and spice.', 15.99, 13.5, 'California', 2001);
INSERT INTO PRODUCT_t (Bar_Code, Product_Quantity, Varietal, Product_name, Description, Price, Alcohol, Appellation, Vintage)
VALUES (23445, 24, 'Sangiovese', 'Louis Martini 1999 Sangiovese', 'Ripe, sweet, plummy nose that mingles with the varietal spice and an oak bouquet of Eastern European woods. Deep strawberry/raspberry fruit with citrus and cashew edges.', 14.99, 13.5, 'California', 1999);
INSERT INTO PRODUCT_t (Bar_Code, Product_Quantity, Varietal, Product_name, Description, Price, Alcohol, Appellation, Vintage)
VALUES (36748, 48, 'Pinot Noir', 'Acacia Beckstoffer Pinot Noir', 'Dark, healthy red. Superripe aromas of bitter cherry, flowers and cola, with a suggestion of aged steak.', 18.99, 12.5, 'Carneros, California', 2002);
INSERT INTO PRODUCT_t (Bar_Code, Product_Quantity, Varietal, Product_name, Description, Price, Alcohol, Appellation, Vintage)
VALUES (46247, 48, 'Chardonnay', 'Amberhill 2002 Chardonnay', 'Wonderful fruit flavors of peach, pear, and apple. Balanced by crisp acidity and soft oak in the finish.', 8.99, 13.3, 'California', 2002);
INSERT INTO PRODUCT_t (Bar_Code, Product_Quantity, Varietal, Product_name, Description, Price, Alcohol, Appellation, Vintage)
VALUES (53125, 12, 'Cabernet Sauvignon', 'Alexander Valley 2002 Cabernet', 'Dark and rich, this is a bottle that delivers.', 13.99, 13.4, 'California', 2002);
INSERT INTO PRODUCT_t (Bar_Code, Product_Quantity, Varietal, Product_name, Description, Price, Alcohol, Appellation, Vintage)
VALUES (66498, 96, 'Rosso', 'Coppola 2002 Rosso', 'Fancy and fruity, oh what a great wine it is.', 7.99, 11.2, 'California', 2002);
INSERT INTO PRODUCT_t (Bar_Code, Product_Quantity, Varietal, Product_name, Description, Price, Alcohol, Appellation, Vintage)
VALUES (71347, 24, 'Sauvignon Blanc', 'Kenwood 2002 Sauvignon Blanc', 'Bright and vibrant, fresh and crisp, boasting tropical fruit, pear and citrus flavors.', 7.99, 13.1, 'Napa Valley', 2002);
INSERT INTO PRODUCT_t (Bar_Code, Product_Quantity, Varietal, Product_name, Description, Price, Alcohol, Appellation, Vintage)
VALUES (83225, 12, 'Zinfandel', 'Buehler 2001 Zinfandel Napa', 'Zincredible! This mid to full-bodied zin has got some nice spice to it and will pair well with grilled foods.', 9.59, 13.8, 'Napa', 2001);

INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (001, 12113, '1001', 2);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (002, 23445, '1001', 1);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (003, 36748, '1002', 1);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (004, 12113, '1003', 3);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (005, 46247, '1004', 6);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (006, 12113, '1004', 2);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (007, 23445, '1004', 1);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (008, 46247, '1005', 1);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (009, 83225, '1006', 1);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (010, 83225, '1007', 4);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (011, 12113, '1007', 6);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (012, 12113, '1007', 1);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (013, 46247, '1008', 1);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (014, 23445, '1008', 2);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (015, 71347, '1009', 3);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (016, 36748, '1009', 3);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (017, 53125, '1010', 1);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (018, 71347, '1010', 2);

INSERT INTO PRODUCT_RATING_t (Bar_Code, Rating)
VALUES (12113, 3);
INSERT INTO PRODUCT_RATING_t (Bar_Code, Rating)
VALUES (23445, 5);
INSERT INTO PRODUCT_RATING_t (Bar_Code, Rating)
VALUES (36748, 3);
INSERT INTO PRODUCT_RATING_t (Bar_Code, Rating)
VALUES (46247, 5);
INSERT INTO PRODUCT_RATING_t (Bar_Code, Rating)
VALUES (53125, 4);
INSERT INTO PRODUCT_RATING_t (Bar_Code, Rating)
VALUES (66498, 4);
INSERT INTO PRODUCT_RATING_t (Bar_Code, Rating)
VALUES (71347, 5);
INSERT INTO PRODUCT_RATING_t (Bar_Code, Rating)
VALUES (83225, 5);
INSERT INTO SUPPLIER_ZIP_t (Zip_Code, Supplier_City, Supplier_State)
VALUES ('91111', 'National City', 'CA');
INSERT INTO SUPPLIER_ZIP_t (Zip_Code, Supplier_City, Supplier_State)
VALUES ('91911', 'Chula Vista', 'CA');
INSERT INTO SUPPLIER_ZIP_t (Zip_Code, Supplier_City, Supplier_State)
VALUES ('92107', 'El Cajon', 'CA');
INSERT INTO SUPPLIER_ZIP_t (Zip_Code, Supplier_City, Supplier_State)
VALUES ('99210', 'San Diego', 'CA');
INSERT INTO SUPPLIER_ZIP_t (Zip_Code, Supplier_City, Supplier_State)
VALUES ('99213', 'San Diego', 'CA');

INSERT INTO SUPPLIER_t (Supplier_ID, Supplier_Name, Supplier_Email, Supplier_Address, Zip_Code)
VALUES (1, 'Steve Wentzel', 'steve@hotmail.com', '3375 Naple st.', 99213);
INSERT INTO SUPPLIER_t (Supplier_ID, Supplier_Name, Supplier_Email, Supplier_Address, Zip_Code)
VALUES (2, 'Jeff Hanks', 'jh@hotmail.com', '1125 Palm St.', 99210);
INSERT INTO SUPPLIER_t (Supplier_ID, Supplier_Name, Supplier_Email, Supplier_Address, Zip_Code)
VALUES (3, 'Mike Metroyanis', 'mike@yahoo.com', '556 L St.', 92107);
INSERT INTO SUPPLIER_t (Supplier_ID, Supplier_Name, Supplier_Email, Supplier_Address, Zip_Code)
VALUES (4, 'Wilman Banks', 'wilman@gmail.com', '25116 Fletcher Dr.', 91911);
INSERT INTO SUPPLIER_t (Supplier_ID, Supplier_Name, Supplier_Email, Supplier_Address, Zip_Code)
VALUES (5, 'Peter Chestfield', 'Peter@hotmail.com', '4213 Broadway', 91111);

INSERT INTO SHIPMENT_t (Shipment_ID, Shipment_Date, Supplier_ID)
VALUES (01, '05/AUG/2004', 3);
INSERT INTO SHIPMENT_t (Shipment_ID, Shipment_Date, Supplier_ID)
VALUES (02, '26/AUG/2004', 1);
INSERT INTO SHIPMENT_t (Shipment_ID, Shipment_Date, Supplier_ID)
VALUES (03, '01/SEP/2004', 5);
INSERT INTO SHIPMENT_t (Shipment_ID, Shipment_Date, Supplier_ID)
VALUES (04, '15/SEP/2004', 2);
INSERT INTO SHIPMENT_t (Shipment_ID, Shipment_Date, Supplier_ID)
VALUES (05, '20/SEp/2004', 1);
INSERT INTO SHIPMENT_t (Shipment_ID, Shipment_Date, Supplier_ID)
VALUES (06, '08/OCT/2004', 4);
INSERT INTO SHIPMENT_t (Shipment_ID, Shipment_Date, Supplier_ID)
VALUES (07, '11/OCT/2004', 5);
INSERT INTO SHIPMENT_t (Shipment_ID, Shipment_Date, Supplier_ID)
VALUES (08, '12/OCT/2004', 2);
INSERT INTO SHIPMENT_t (Shipment_ID, Shipment_Date, Supplier_ID)
VALUES (09, '08/NOV/2004', 1);
INSERT INTO SHIPMENT_t (Shipment_ID, Shipment_Date, Supplier_ID)
VALUES (10, '30/NOV/2004', 3);

INSERT INTO INCLUDES_t (Bar_Code, Shipment_ID)
VALUES (12113, 01);
INSERT INTO INCLUDES_t (Bar_Code, Shipment_ID)
VALUES (12113, 04);
INSERT INTO INCLUDES_t (Bar_Code, Shipment_ID)
VALUES (23445, 10);
INSERT INTO INCLUDES_t (Bar_Code, Shipment_ID)
VALUES (36748, 09);
INSERT INTO INCLUDES_t (Bar_Code, Shipment_ID)
VALUES (46247, 08);
INSERT INTO INCLUDES_t (Bar_Code, Shipment_ID)
VALUES (53125, 05);
INSERT INTO INCLUDES_t (Bar_Code, Shipment_ID)
VALUES (66498, 02);
INSERT INTO INCLUDES_t (Bar_Code, Shipment_ID)
VALUES (71347, 07);
INSERT INTO INCLUDES_t (Bar_Code, Shipment_ID)
VALUES (83225, 03);
INSERT INTO INCLUDES_t (Bar_Code, Shipment_ID)
VALUES (83225, 06);

< Previous Next >

Topics

Tech Business Computer Engineering Electrical Engineering Selected Topics

Wine Database System SQL Relational Database: Table Creation

Wine Database System
SQL Relational Database: Table Creation