· 9 years ago · Oct 18, 2016, 11:28 AM
1
2/****************************************************
3Checks to see if the database exists
4If so deletes it
5Creates the database DBJohnDoe
6****************************************************/
7 DROP DATABASE IF EXISTS DBJohnDoe;
8 CREATE DATABASE DBJohnDoe;
9 SHOW DATABASES;
10/****************************************************
11Use DBJohnDoe and add a table tblCustomer
12****************************************************/
13 Use DBJohnDoe;
14 DROP table IF EXISTS tblCustomer;
15 CREATE TABLE tblCustomer
16 (
17 CustomerID int(10) NOT NULL AUTO_INCREMENT,
18 CustCustFirstName varchar(20),
19 CustLastName varchar(20),
20 CustAddress varchar(40),
21 CustCity varchar(20),
22 CustPhone varchar(20),
23 CustAge varchar(20),
24 CustGender varchar(10),
25 PRIMARY KEY (CustomerID),
26
27 INDEX IndexCustLastName (CustLastName),
28 UNIQUE IndexFirstLastNames (CustFirstName,CustLastName);
29 )
30 SHOW TABLES;
31 Describe tblOrder;
32/****************************************************************
33Import the text file export.csv
34Export.csv is a spreadsheet saved as a
35****************************************************************/
36 load data local infile 'c:\\export.csv' into table tblCustomer
37 fields terminated by ','
38 enclosed by '"'
39 lines terminated by '\n'
40 (CustFirstName,CustLastName);
41/****************************************************
42 Creates the Order table
43 ****************************************************/
44 Use DBJohnDoe;
45 DROP table IF EXISTS tblOrder;
46 CREATE TABLE tblOrder
47 (
48 frnOrderID int(10) NOT NULL AUTO_INCREMENT,
49 frnStaffID int(10) NOT NULL,
50 frgnCheckOpID int(10) NOT NULL,
51 Datetime TIMESTAMP,
52 OrderCost decimal,
53 PRIMARY KEY (frnOrderID),
54
55 FOREIGN KEY (frnStaffID) REFERENCES tblCheckOutOp(frnStaffID),
56 FOREIGN KEY (frgnCheckOpID) REFERENCES tblCheckOutOp(frgnCheckOpID),
57
58 INDEX IndexfrnOrderID (frnOrderID);
59 )
60 SHOW TABLES;
61 Describe tblOrder;
62/****************************************************************
63Import the text file export.csv
64Export.csv is a spreadsheet saved as a
65****************************************************************/
66 load data local infile 'c:\\export.csv' into table tblOrder
67 fields terminated by ','
68 enclosed by '"'
69 lines terminated by '\n'
70 (CustFirstName,CustLastName);
71/****************************************************
72Creates the CheckOutOp table
73****************************************************/
74 Use DBJohnDoe;
75 DROP table IF EXISTS tblCheckOutOp;
76 CREATE TABLE tblCheckOutOp
77 (
78 CheckOpID int(10) NOT NULL AUTO_INCREMENT,
79 frgnCheckOpID varchar(20) NOT NULL,
80 StaffCustFirstName varchar(20),
81 StaffCustLastName varchar(20),
82 StaffCustAddress varchar(40),
83 StaffCustCity varchar(20),
84 StaffCustPhone varchar(20),
85 StaffCustAge varchar(20),
86 StaffCustGender varchar(10),
87 PRIMARY KEY (CheckOpID),
88
89 FOREIGN KEY (frgnCheckOpID) REFERENCES tblCheckOutOp(frgnCheckOpID),
90
91 INDEX IndexCustLastName (StaffCustLastName),
92 UNIQUE IndexFirstLastNames (StaffCustLastName,StaffCustFirstName);
93 )
94 SHOW TABLES;
95 Describe tblOrder;
96/****************************************************************
97Import the text file export.csv
98Export.csv is a spreadsheet saved as a
99****************************************************************/
100 load data local infile 'c:\\export.csv' into table tblCheckOutOp
101 fields terminated by ','
102 enclosed by '"'
103 lines terminated by '\n'
104 (CustFirstName,CustLastName);
105/****************************************************
106Creates the OrderLine table
107****************************************************/
108 Use DBJohnDoe;
109 DROP table IF EXISTS tblOrderLine;
110 CREATE TABLE tblOrderLine
111 (
112 OrderLineID int(10) NOT NULL AUTO_INCREMENT,
113 ProductID int(10) NOT NULL,
114 frnOrderID int(10) NOT NULL,
115 Quantity int(255) unsigned,
116 OrderlineCost decimal,
117 PRIMARY KEY (OrderLineID),
118
119 FOREIGN KEY (ProductID) REFERENCES tblProduct(ProductID),
120 FOREIGN KEY (frnOrderID) REFERENCES tblProduct(ProductID),
121
122 INDEX IndOrderLine (OrderLineID);
123 )
124 SHOW TABLES;
125 Describe tblOrder;
126/****************************************************************
127Import the text file export.csv
128Export.csv is a spreadsheet saved as a
129****************************************************************/
130 load data local infile 'c:\\export.csv' into table tblOrderLine
131 fields terminated by ','
132 enclosed by '"'
133 lines terminated by '\n'
134 (CustFirstName,CustLastName);
135/****************************************************
136Creates the Product table
137****************************************************/
138 Use DBJohnDoe;
139 DROP table IF EXISTS tblProduct;
140 CREATE TABLE tblProduct
141 (
142 ProductID int(10) NOT NULL AUTO_INCREMENT,
143 ProductName varchar(20),
144 ProductUnitPrice decimal,
145 PRIMARY KEY (ProductID),
146
147 INDEX IndexProductName (ProductName);
148 )
149 SHOW TABLES;
150 Describe tblOrder;
151/****************************************************************
152Import the text file export.csv
153Export.csv is a spreadsheet saved as a
154****************************************************************/
155 load data local infile 'c:\\export.csv' into table tblProduct
156 fields terminated by ','
157 enclosed by '"'
158 lines terminated by '\n'
159 (CustFirstName,CustLastName);
160/****************************************************
161Inserts Customer data
162****************************************************/
163 INSERT INTO tblCustomer
164 (
165 CustomerID,
166 CustFirstName,
167 CustLastName,
168 CustAddress,
169 CustCity,
170 CustPhone;
171 )
172/****************************************************
173Inserts Order data
174****************************************************/
175 INSERT INTO tblOrder
176 (
177 frnOrderID, frnStaffID,
178 CheckOp_ID,
179 Datetime,
180 OrderCost;
181 )
182/****************************************************
183Inserts CheckOutOp data
184****************************************************/
185 INSERT INTO tblCheckOutOp
186 (
187 CheckOpID,
188 frgnCheckOpID,
189 StaffCustFirstName,
190 StaffCustLastName,
191 StaffCustAddress,
192 StaffCustCity,
193 StaffCustPhone,
194 StaffCustAge,
195 StaffCustGender;
196 )
197/****************************************************
198Inserts OrderLine data
199****************************************************/
200 INSERT INTO tblOrderLine
201 (
202 OrderLineID,
203 ProductID, frnOrderID,
204 Quantity,
205 OrderLineCost;
206 )
207/****************************************************
208Inserts Product data
209****************************************************/
210 INSERT INTO tblProduct
211 (
212 ProductID,
213 ProductName,
214 ProductUnitPrice;
215 )