wMHJiVjk

1CREATE TABLE IF NOT EXISTS Location (
2	shop_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
3	city VARCHAR(45) NOT NULL,
4	street VARCHAR(45) NOT NULL,
5	house CHAR(4) NOT NULL,
6	phone CHAR(16) NOT NULL,
7	PRIMARY KEY (shop_id)
8) ENGINE=InnoDB;
9
10CREATE TABLE IF NOT EXISTS Vendor (
11	vendor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
12	name VARCHAR(100) NOT NULL,
13	phone CHAR(16) NOT NULL,
14	PRIMARY KEY (vendor_id)
15) ENGINE=InnoDB;
16
17CREATE TABLE IF NOT EXISTS Products (
18	product_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
19	name VARCHAR(100) NOT NULL,
20	amount SMALLINT UNSIGNED NOT NULL,
21	price SMALLINT UNSIGNED NOT NULL,
22	discount TINYINT UNSIGNED NOT NULL,
23	date_of_receipt DATETIME NOT NULL,
24	shelf_life TINYINT UNSIGNED NOT NULL,
25	shop_id SMALLINT UNSIGNED NOT NULL,
26	vendor_id SMALLINT UNSIGNED NOT NULL,
27	PRIMARY KEY (product_id),
28	FOREIGN KEY (shop_id) REFERENCES Location(shop_id),
29	FOREIGN KEY (vendor_id) REFERENCES Vendor(vendor_id)  
30) ENGINE=InnoDB;
31
32CREATE TABLE IF NOT EXISTS Sales (
33	sell_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
34	product_id INT UNSIGNED NOT NULL,
35	shop_id SMALLINT UNSIGNED NOT NULL,
36	amount SMALLINT UNSIGNED NOT NULL,
37	date DATETIME NOT NULL,
38	PRIMARY KEY (sell_id),
39	FOREIGN KEY (product_id) REFERENCES Products(product_id),
40	FOREIGN KEY (shop_id) REFERENCES Location(shop_id)
41) ENGINE=InnoDB;