· 8 years ago · Mar 07, 2018, 09:22 AM
1SET FOREIGN_KEY_CHECKS=0;
2DROP DATABASE IF EXISTS `foto`;
3CREATE DATABASE `foto`
4CHARACTER SET 'utf8'
5COLLATE 'utf8_general_ci';
6
7USE `foto`;
8
9
10CREATE TABLE Manufacturer
11(
12 IDManufacturer INTEGER not NULL AUTO_INCREMENT,
13 Name VARCHAR(20) NULL,
14 PRIMARY KEY (IDManufacturer)
15)
16;
17
18
19
20CREATE UNIQUE INDEX XPKManufacturer ON Manufacturer
21(
22 IDManufacturer
23)
24;
25
26
27
28CREATE TABLE Product
29(
30 IDProduct INTEGER not NULL AUTO_INCREMENT,
31 model VARCHAR(20) NULL,
32 Price INTEGER NULL,
33 IDManufacturer INTEGER NOT NULL ,
34 PRIMARY KEY (IDProduct),FOREIGN KEY R_29 (IDManufacturer) REFERENCES Manufacturer(IDManufacturer)
35)
36;
37
38
39
40CREATE UNIQUE INDEX XPKProduct ON Product
41(
42 IDProduct
43)
44;
45
46
47
48CREATE TABLE customer
49(
50 IDCustomer INTEGER NULL,
51 Address VARCHAR(20) NULL,
52 Name VARCHAR(20) NULL,
53 Lastname VARCHAR(20) NULL,
54 Email VARCHAR(20) NULL,
55 Patronimyc VARCHAR(20) NULL,
56 PRIMARY KEY (IDCustomer)
57)
58;
59
60
61
62CREATE UNIQUE INDEX XPKcustomer ON customer
63(
64 IDCustomer
65)
66;
67
68
69
70CREATE TABLE Payment
71(
72 IDPayment INTEGER not NULL AUTO_INCREMENT,
73 Type INTEGER NULL,
74 PRIMARY KEY (IDPayment)
75)
76;
77
78
79
80CREATE UNIQUE INDEX XPKPayment ON Payment
81(
82 IDPayment
83)
84;
85
86
87
88CREATE TABLE Status
89(
90 IDStatus INTEGER not NULL AUTO_INCREMENT,
91 Status VARCHAR(20) NULL,
92 PRIMARY KEY (IDStatus)
93)
94;
95
96
97
98CREATE UNIQUE INDEX XPKStatus ON Status
99(
100 IDStatus
101)
102;
103
104
105
106CREATE TABLE Extradition
107(
108 IDExtradition INTEGER not NULL AUTO_INCREMENT,
109 Number INTEGER NULL,
110 Address VARCHAR(20) NULL,
111 PRIMARY KEY (IDExtradition)
112)
113;
114
115
116
117CREATE UNIQUE INDEX XPKExtradition ON Extradition
118(
119 IDExtradition
120)
121;
122
123
124
125CREATE TABLE Orderlist
126(
127 IDOrder INTEGER NULL,
128 IDCustomer INTEGER NOT NULL,
129 IDPayment INTEGER NOT NULL,
130 IDStatus INTEGER NOT NULL,
131 IDExtradition INTEGER NOT NULL,
132 PRIMARY KEY (IDOrder),FOREIGN KEY R_24 (IDCustomer) REFERENCES customer(IDCustomer),
133 FOREIGN KEY R_26 (IDPayment) REFERENCES Payment(IDPayment),
134 FOREIGN KEY R_27 (IDStatus) REFERENCES Status(IDStatus),
135 FOREIGN KEY R_28 (IDExtradition) REFERENCES Extradition(IDExtradition)
136)
137;
138
139
140
141CREATE UNIQUE INDEX XPKorder ON Orderlist
142(
143 IDOrder
144)
145;
146
147
148
149CREATE TABLE order_product
150(
151 IDProduct INTEGER NOT NULL,
152 IDOrder INTEGER NOT NULL,
153 Amount INTEGER NULL,
154 PRIMARY KEY (IDProduct,IDOrder),FOREIGN KEY R_21 (IDProduct) REFERENCES Product(IDProduct),
155 FOREIGN KEY R_25 (IDOrder) REFERENCES Orderlist(IDOrder)
156)
157;
158
159
160
161CREATE UNIQUE INDEX XPKorder_product ON order_product
162(
163 IDProduct,
164 IDOrder
165)
166;
167
168
169
170CREATE TABLE lens
171(
172 IDProduct_lens INTEGER not NULL AUTO_INCREMENT ,
173 F_length VARCHAR(20) NULL,
174 Angle VARCHAR(20) NULL,
175 Aperture VARCHAR(20) NULL,
176 PRIMARY KEY (IDProduct_lens),FOREIGN KEY is_a (IDProduct_lens) REFERENCES Product(IDProduct)
177 ON DELETE CASCADE
178)
179;
180
181
182
183CREATE UNIQUE INDEX XPKlens ON lens
184(
185 IDProduct_lens
186)
187;
188
189
190
191CREATE TABLE accessories
192(
193 IDProduct_accessories INTEGER not NULL AUTO_INCREMENT,
194 Type VARCHAR(20) NULL,
195 Description VARCHAR(20) NULL,
196 PRIMARY KEY (IDProduct_accessories),FOREIGN KEY is_a (IDProduct_accessories) REFERENCES Product(IDProduct)
197 ON DELETE CASCADE
198)
199;
200
201
202
203CREATE UNIQUE INDEX XPKaccessories ON accessories
204(
205 IDProduct_accessories
206)
207;
208
209
210
211CREATE TABLE Cameras
212(
213 IDProduct INTEGER NOT NULL,
214 IDProduct_lens INTEGER NULL,
215 IDProduct_accessories INTEGER NULL,
216 Matrix VARCHAR(20) NULL,
217 Iso VARCHAR(20) NULL,
218 Weight VARCHAR(20) NULL,
219 Resource INTEGER NULL,
220 PRIMARY KEY (IDProduct),FOREIGN KEY is_a (IDProduct) REFERENCES Product(IDProduct)
221 ON DELETE CASCADE,
222 FOREIGN KEY R_19 (IDProduct_lens) REFERENCES lens(IDProduct_lens),
223 FOREIGN KEY R_20 (IDProduct_accessories) REFERENCES accessories(IDProduct_accessories)
224)
225;
226
227
228
229CREATE UNIQUE INDEX XPKCameras ON Cameras
230(
231 IDProduct
232)
233;