Создаём таблицу магазин CREATE TABLE shop id INTEGER 11 UNSIGNED NOT N

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
# Создаём таблицу "магазин"
CREATE TABLE shop
(
id INTEGER(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(100),
inn INTEGER(11) UNSIGNED UNIQUE
);
# Создаём таблицу "клиент"
CREATE TABLE client
(
c_id INTEGER(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
sh_id INTEGER(11) UNSIGNED NOT NULL,
first_name VARCHAR(50),
last_name VARCHAR(70) NOT NULL,
middle_name VARCHAR(50),
login VARCHAR(50) NOT NULL,
password VARCHAR(150) NOT NULL,
status VARCHAR(10),
FOREIGN KEY (sh_id)
REFERENCES shop(id)
ON DELETE CASCADE
);
# Создаём таблицу "производитель"
CREATE TABLE manufacturer
(
id INTEGER(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(100) NOT NULL,
country VARCHAR(100)
);
# Создаём таблицу "заказ"
CREATE TABLE user_order
(
id INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
sh_id INTEGER(11) UNSIGNED NOT NULL,
c_id INTEGER(11) UNSIGNED,
items TEXT,
total_sum DECIMAL(12, 2) UNSIGNED NOT NULL,
total_quantity INTEGER(11) UNSIGNED NOT NULL,
DISCOUNT DECIMAL(10, 2) UNSIGNED,
FOREIGN KEY (sh_id)
REFERENCES shop(id)
ON DELETE CASCADE,
FOREIGN KEY (c_id)
REFERENCES client(c_id)
ON DELETE SET NULL
);
# Создаём таблицу "телефон"
CREATE TABLE phone
(
id INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
manuf_id INTEGER(11) UNSIGNED NOT NULL,
price DECIMAL(10,2) UNSIGNED,
model VARCHAR(70) NOT NULL UNIQUE,
features TEXT,
FOREIGN KEY (manuf_id)
REFERENCES manufacturer(id)
);
# Создаём таблицу "аксессуар"
CREATE TABLE accessory
(
id INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
p_id INTEGER(11) UNSIGNED NOT NULL,
title VARCHAR(50) NOT NULL,
price DECIMAL(10,2) UNSIGNED,
FOREIGN KEY (p_id)
REFERENCES phone(id)
);
# Создаём таблицу отношений "магазин-телефон"
CREATE TABLE phone_shop_relations
(
p_id INTEGER(11) UNSIGNED NOT NULL,
sh_id INTEGER(11) UNSIGNED NOT NULL,
FOREIGN KEY (p_id)
REFERENCES phone(id),
FOREIGN KEY (sh_id)
REFERENCES shop(id),
UNIQUE (p_id, sh_id)
);