8QtyjGDg

1SET FOREIGN_KEY_CHECKS = 0;
2
3DROP TABLE IF EXISTS orderdetails, orders, productlines, products;
4
5CREATE TABLE orders(
6	orderNumber int(11) primary key auto_increment,
7	orderDate datetime not null,
8	requiredDate datetime not null,
9	shippedDate datetime,
10	status varchar(15) not null,
11	comments text,
12	customerNumber int(11) not null
13);
14
15CREATE TABLE orderdetails(
16	orderNumber int(11) not null,
17	productCode varchar(15) not null,
18	quantityOrdered int(11) not null,
19	priceEach double not null,
20	orderLineNumber smallint(6) not null,
21	PRIMARY KEY(orderNumber, productCode),
22	FOREIGN KEY(orderNumber) REFERENCES orders(orderNumber) ON UPDATE CASCADE,
23	FOREIGN KEY(productCode) REFERENCES products(productCode) ON UPDATE CASCADE
24);
25
26CREATE TABLE productlines(
27	productLine varchar(50) primary key,
28	textDescription varchar(4000),
29	htmlDescription mediumtext,
30	image mediumblob
31);
32
33CREATE TABLE products(
34	productCode varchar(15) primary key,
35	productName varchar(70) not null,
36	productLine varchar(50) not null,
37	productScale varchar(10) not null,
38	productVendor varchar(50) not null,
39	productDescription text not null,
40	quantityInStock smallint(6) not null,
41	buyPrice double not null,
42	FOREIGN KEY(productLine) REFERENCES productlines(productLine) ON UPDATE CASCADE
43);
44
45SET FOREIGN_KEY_CHECKS = 1;