· 9 years ago · Oct 21, 2016, 12:26 AM
1-- Inventory-DDL.sql
2-- script to create Inventory database, create TABLEs
3-- CST 8215
4-- Year: 2016
5
6DROP TABLE IF EXISTS COUNTRY_T;
7DROP TABLE IF EXISTS Customer_T;
8DROP TABLE IF EXISTS Invoice_Line_T;
9DROP TABLE IF EXISTS Product_T;
10DROP TABLE IF EXISTS Invoice_T;
11
12
13CREATE TABLE COUNTRY_T (
14Cntry_Code CHAR(3),
15Cntry_Name VARCHAR(30) NOT NULL,
16Cntry_Population INTEGER,
17CONSTRAINT PK_COUNTRY PRIMARY KEY( Cntry_Code )
18);
19
20CREATE TABLE Customer_T (
21Cust_Id CHAR( 4 ),
22Cust_FCname VARCHAR( 30 ) NULL,
23Cust_Lname VARCHAR( 30 ) NOT NULL,
24Cust_Phone VARCHAR( 15 ) NOT NULL,
25Cust_Address VARCHAR( 20 ) NOT NULL,
26Cust_City VARCHAR( 15 ) NOT NULL,
27Cust_Prov CHAR( 2 ) NULL,
28Cust_PostCode CHAR( 6 ) NOT NULL,
29Cust_Balance DECIMAL( 9,2 ),
30Cust_Country CHAR( 3 ),
31CONSTRAINT PK_Customer PRIMARY KEY( Cust_Id ),
32CONSTRAINT FK_Cust_Country FOREIGN KEY( Cust_Country ) REFERENCES COUNTRY_T( Cntry_Code )
33);
34
35CREATE TABLE Invoice_T (
36Invoice_Number CHAR(6),
37Cust_Id CHAR(4) NOT NULL,
38Invoice_Date DATE,
39CONSTRAINT PK_Invoice PRIMARY KEY( Invoice_Number ),
40CONSTRAINT FK_Cust_ID FOREIGN KEY( Cust_Id ) REFERENCES Customer_T( Cust_ID )
41);
42
43CREATE TABLE Product_T (
44Prod_Code CHAR(5),
45Prod_Description VARCHAR(60) NOT NULL,
46Prod_Indate DATE NOT NULL,
47Prod_QOH INTEGER NOT NULL,
48Prod_Min INTEGER,
49Prod_Price DECIMAL (5,2) NOT NULL,
50Prod_Discount INTEGER,
51Cntry_Origin CHAR (3),
52CONSTRAINT PK_Product PRIMARY KEY( Prod_Code ),
53CONSTRAINT FK_Cntry_Origin FOREIGN KEY( Cntry_Origin ) REFERENCES COUNTRY_T( Cntry_Code )
54);
55
56CREATE TABLE Invoice_Line_T (
57Invoice_Number CHAR(6),
58Invoice_Line INTEGER,
59Prod_Code CHAR(5) NOT NULL,
60Line_units INTEGER NOT NULL,
61Line_Price DECIMAL (5,2) NOT NULL,
62CONSTRAINT PK_Invoice_Line PRIMARY KEY( Invoice_Number, Invoice_Line ),
63CONSTRAINT FK1_Invoice_Line FOREIGN KEY( Invoice_Number ) REFERENCES Invoice_T( Invoice_Number ),
64CONSTRAINT FK2_Invoice_Line FOREIGN KEY( Prod_Code ) REFERENCES Product_T( Prod_Code )
65);
66
67
68
69-- Inventory-DML.sql
70-- script to populate Inventory Database
71-- CST 8215
72-- Year: 2016
73
74
75INSERT INTO COUNTRY_T ( Cntry_Code, Cntry_Name, Cntry_Population ) VALUES ( 'RUS', 'Russian Federation', 144192450 );
76INSERT INTO COUNTRY_T ( Cntry_Code, Cntry_Name, Cntry_Population ) VALUES ( 'MEX', 'Mexico', 119530753 );
77INSERT INTO COUNTRY_T ( Cntry_Code, Cntry_Name, Cntry_Population ) VALUES ( 'DZA', 'Algeria', 40400000 );
78INSERT INTO COUNTRY_T ( Cntry_Code, Cntry_Name, Cntry_Population ) VALUES ( 'CHN', 'China', 1376049000 );
79INSERT INTO COUNTRY_T ( Cntry_Code, Cntry_Name, Cntry_Population ) VALUES ( 'CHL', 'Chile', 18006407 );
80INSERT INTO COUNTRY_T ( Cntry_Code, Cntry_Name, Cntry_Population ) VALUES ( 'CAN', 'Canada', 36155487 );
81INSERT INTO COUNTRY_T ( Cntry_Code, Cntry_Name, Cntry_Population ) VALUES ( 'JAP', 'Japan', 126919659 );
82INSERT INTO COUNTRY_T ( Cntry_Code, Cntry_Name, Cntry_Population ) VALUES ( 'EGY', 'Egypt', 88487396 );
83INSERT INTO COUNTRY_T ( Cntry_Code, Cntry_Name, Cntry_Population ) VALUES ( 'TUK', 'Turkey', 79414269 );
84INSERT INTO COUNTRY_T ( Cntry_Code, Cntry_Name, Cntry_Population ) VALUES ( 'FRA', 'France', 66553766 );
85
86INSERT INTO Customer_T VALUES ('C001', 'Galen', 'Reid', '613-727-4723', '1385 Woodroffe Ave', 'Ottawa','ON','K2G1V8', 540,'CAN');
87INSERT INTO Customer_T VALUES ('C002', 'Bob', 'Jones', '613-727-4723', '1385 Woodroffe Ave', 'Ottawa','ON','K2G1V8', 55,'CAN');
88INSERT INTO Customer_T VALUES ('C003', 'Vlad', 'Petrov', '613-727-4723', '1385 Woodroffe Ave', 'Moscow','K2G1V8', 60,'RUS');
89INSERT INTO Customer_T VALUES ('C004', 'alain', 'Moreau', '613-727-4723', '854 Younge Street' , 'paris','K2G1V8', 7,'FRA');
90INSERT INTO Customer_T VALUES ('C005', 'Yuma', 'Miyagi', '613-727-4723', '264 Main Street' , 'Santiago','K2G1V8', 345,'CHL');
91INSERT INTO Customer_T VALUES ('C006', 'Antonio', 'GARCIA', '613-727-4723', '357 Rue Catherine' , 'Mexico city' ,'K2G1V8', 84,'MEX');
92
93
94INSERT INTO Invoice_T VALUES ('I23001', 'C001', '2011-02-15');
95INSERT INTO Invoice_T VALUES ('I23002', 'C001', '2011-04-25');
96INSERT INTO Invoice_T VALUES ('I23003', 'C004', '2011-06-12');
97INSERT INTO Invoice_T VALUES ('I23004', 'C002', '2011-07-08');
98INSERT INTO Invoice_T VALUES ('I23005', 'C005', '2011-08-24');
99INSERT INTO Invoice_T VALUES ('I23006', 'C006', '2011-09-07');
100INSERT INTO Invoice_T VALUES ('I23007', 'C006', '2010-12-28');
101INSERT INTO Invoice_T VALUES ('I23008', 'C006', '2011-12-15');
102
103
104INSERT INTO Product_T VALUES ('P2011', 'Compac Presario', '2011-02-14', 20, 5, 499.99, 0,'RUS');
105INSERT INTO Product_T VALUES ('P2012', 'HP laptop', '2010-09-25', 40, 5, 529.99, 0,'CHN');
106INSERT INTO Product_T VALUES ('P2013', 'Samsung LCD', '2010-02-15', 22, 8, 329.99, 0,'CHL');
107INSERT INTO Product_T VALUES ('P2014', 'Brother Network All-In-One Laser Printer', '2010-10-10', 50, 10, 159.99, 0,'RUS');
108INSERT INTO Product_T VALUES ('P2015', 'Western Digital External Hard drive', '2010-04-08', 30, 10, 149.99, NULL,'CAN');
109INSERT INTO Product_T VALUES ('P2016', 'Apple iPad 2 with Wi-Fi + 3G', '2011-02-23', 90, 200, 849.00, 0,'JAP');
110INSERT INTO Product_T VALUES ('P2017', 'iPAD 2 Smart Cover', '2011-02-14', 70, 10, 45.00, 10,'EGY');
111
112
113INSERT INTO Invoice_Line_T VALUES ('I23001', 1, 'P2011', 1, 650.75);
114INSERT INTO Invoice_Line_T VALUES ('I23001', 2, 'P2014', 3, 159.99);
115INSERT INTO Invoice_Line_T VALUES ('I23002', 1, 'P2012', 1, 529.99);
116INSERT INTO Invoice_Line_T VALUES ('I23003', 1, 'P2015', 3, 140.75);
117INSERT INTO Invoice_Line_T VALUES ('I23004', 1, 'P2014', 1, 159.99);
118INSERT INTO Invoice_Line_T VALUES ('I23005', 1, 'P2016', 1, 798.99);
119INSERT INTO Invoice_Line_T VALUES ('I23006', 1, 'P2011', 1, 499.99);
120INSERT INTO Invoice_Line_T VALUES ('I23007', 1, 'P2012', 1, 529.99);
121INSERT INTO Invoice_Line_T VALUES ('I23008', 1, 'P2016', 3, 689.00);
122INSERT INTO Invoice_Line_T VALUES ('I23008', 2, 'P2017', 3, 35.99);