4YeRNJyE

1drop database if exists lab02;
2create database lab02;
3use lab02;
4
5create table if not exists productlines (
6	productLine VARCHAR(50) NOT NULL PRIMARY KEY,
7	textDescription VARCHAR(4000),
8	htmlDescription MEDIUMTEXT,
9	image MEDIUMBLOB
10);
11
12create table if not exists products (
13	productCode VARCHAR(15) NOT NULL PRIMARY KEY,
14	productName VARCHAR(70) NOT NULL,
15	productLine VARCHAR(50) NOT NULL,
16	productSale VARCHAR(10) NOT NULL,
17	productVendor VARCHAR(50) NOT NULL,
18	productDescription TEXT NOT NULL,
19	quantityInStock SMALLINT(6) NOT NULL,
20	buyPrice DOUBLE NOT NULL,
21	FOREIGN KEY (productLine) REFERENCES productlines (productLine) ON DELETE RESTRICT ON UPDATE CASCADE
22);
23
24create table if not exists orders (
25	orderNumber INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
26	orderDate DATETIME NOT NULL,
27	requiredDate DATETIME NOT NULL,
28	shippedDate DATETIME,
29	status VARCHAR(15) NOT NULL,
30	comments TEXT,
31	customerNumber INT(11) NOT NULL
32);
33
34create table if not exists orderdetails (
35	orderNumber INT(11) NOT NULL AUTO_INCREMENT,
36	productCode VARCHAR(15) NOT NULL,
37	quantityOrdered INT(11) NOT NULL,
38	priceEach DOUBLE NOT NULL,
39	orderLineNumber SMALLINT(6) NOT NULL,
40	PRIMARY KEY (orderNumber, productCode),
41	FOREIGN KEY (orderNumber) REFERENCES orders(orderNumber) ON UPDATE CASCADE,
42	FOREIGN KEY (productCode) REFERENCES products(productCode) ON UPDATE CASCADE
43);