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
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
|
CREATE TABLE Payments (
id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Payments PRIMARY KEY,
order_id INT NOT NULL,
amount INT NOT NULL,
date DATETIME NOT NULL
)
GO
CREATE TABLE Boxes (
id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Boxes PRIMARY KEY,
order_id INT NOT NULL,
warehouse_id INT NOT NULL,
wrapping_id INT NOT NULL,
volume REAL NOT NULL,
weight REAL NOT NULL
)
GO
CREATE TABLE Wrappings (
id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Wrappings PRIMARY KEY,
type NVARCHAR(30) NOT NULL,
price INT NOT NULL
)
GO
CREATE TABLE Customers (
id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Customers PRIMARY KEY,
name NVARCHAR(50) NOT NULL,
email NVARCHAR(96) NOT NULL
)
GO
CREATE TABLE Orders (
id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Orders PRIMARY KEY,
customer_id INT NOT NULL,
city_id INT NOT NULL,
date DATETIME NOT NULL
)
GO
CREATE TABLE Warehouses (
id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Warehouses PRIMARY KEY,
city_id INT NOT NULL,
area INT NOT NULL
)
GO
CREATE TABLE Shipments (
id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Shipments PRIMARY KEY,
order_id INT NOT NULL,
delivery_id INT NOT NULL
)
GO
CREATE TABLE Deliveries (
id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Deliveries PRIMARY KEY,
driver_id INT NOT NULL,
vehicle_id INT NOT NULL,
route_id INT NOT NULL,
date DATETIME NOT NULL
)
GO
CREATE TABLE Cities (
id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Cities PRIMARY KEY,
name NVARCHAR(30) NOT NULL
)
GO
CREATE TABLE Drivers (
id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Drivers PRIMARY KEY,
city_id INT NOT NULL,
salary INT NOT NULL,
on_delivery BIT NOT NULL
)
GO
CREATE TABLE Vehicles (
id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Vehicles PRIMARY KEY,
name NVARCHAR(30) NOT NULL,
tonnage INT NOT NULL,
gas_consumption FLOAT NOT NULL
)
GO
CREATE TABLE Routes (
id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Routes PRIMARY KEY,
city1_id INT NOT NULL,
city2_id INT NOT NULL,
distance FLOAT NOT NULL
)
GO
ALTER TABLE Payments ADD FOREIGN KEY (order_id) REFERENCES Orders (id)
GO
ALTER TABLE Boxes ADD FOREIGN KEY (order_id) REFERENCES Orders (id)
GO
ALTER TABLE Boxes ADD FOREIGN KEY (warehouse_id) REFERENCES Warehouses (id)
GO
ALTER TABLE Boxes ADD FOREIGN KEY (wrapping_id) REFERENCES Wrappings (id)
GO
ALTER TABLE Orders ADD FOREIGN KEY (customer_id) REFERENCES Customers (id)
GO
ALTER TABLE Orders ADD FOREIGN KEY (city_id) REFERENCES Cities (id)
GO
ALTER TABLE Warehouses ADD FOREIGN KEY (city_id) REFERENCES Cities (id)
GO
ALTER TABLE Shipments ADD FOREIGN KEY (order_id) REFERENCES Orders (id)
GO
ALTER TABLE Shipments ADD FOREIGN KEY (delivery_id) REFERENCES Deliveries (id)
GO
ALTER TABLE Deliveries ADD FOREIGN KEY (driver_id) REFERENCES Drivers (id)
GO
ALTER TABLE Deliveries ADD FOREIGN KEY (vehicle_id) REFERENCES Vehicles (id)
GO
ALTER TABLE Deliveries ADD FOREIGN KEY (route_id) REFERENCES Routes (id)
GO
ALTER TABLE Drivers ADD FOREIGN KEY (city_id) REFERENCES Cities (id)
GO
ALTER TABLE Routes ADD FOREIGN KEY (city1_id) REFERENCES Cities (id)
GO
ALTER TABLE Routes ADD FOREIGN KEY (city2_id) REFERENCES Cities (id)
GO
-- Values
-- Wrappings
INSERT INTO Wrappings (type, price)
VALUES (N'Жёсткая', 100),
(N'Полужёсткая', 100),
(N'Мягкая', 100),
(N'Воздушно-Пузырьковая', 100),
(N'Рулон', 100);
GO
-- Customers
DECLARE @customers_count INT = 100000;
DECLARE @cities_count INT = 300;
DECLARE @orders_count INT = 100000;
DECLARE @payments_count INT = 100000;
DECLARE @warehouses_count INT = 400;
DECLARE @boxes_count INT = 100000
DECLARE @n INT = 0;
while (@n < @customers_count)
begin
DECLARE @customer_name NVARCHAR = CAST(@n AS NVARCHAR);
INSERT INTO Customers (name, email) VALUES (@customer_name, @customer_name + '@example.ru');
SET @n = @n + 1;
end
GO
-- Cities
DECLARE @customers_count INT = 100000;
DECLARE @cities_count INT = 300;
DECLARE @orders_count INT = 100000;
DECLARE @payments_count INT = 100000;
DECLARE @warehouses_count INT = 400;
DECLARE @boxes_count INT = 100000
DECLARE @n INT = 0;
while (@n < @cities_count)
begin
DECLARE @name NVARCHAR(30) = N'Подольск-' + CAST(@n AS NVARCHAR);
INSERT INTO Cities (name) VALUES (@name);
SET @n = @n + 1;
end
GO
-- Orders
DECLARE @customers_count INT = 100000;
DECLARE @cities_count INT = 300;
DECLARE @orders_count INT = 100000;
DECLARE @payments_count INT = 100000;
DECLARE @warehouses_count INT = 400;
DECLARE @boxes_count INT = 100000
DECLARE @n INT = 0;
while (@n < @orders_count)
begin
DECLARE @customer_id INT = 1 + RAND() * (@customers_count - 1);
DECLARE @city_id INT = 1 + RAND() * (@cities_count - 1);
INSERT INTO Orders (customer_id, city_id, date) VALUES (@customer_id, @city_id, '2021-12-05');
SET @n = @n + 1;
end
GO
-- Payments
DECLARE @customers_count INT = 100000;
DECLARE @cities_count INT = 300;
DECLARE @orders_count INT = 100000;
DECLARE @payments_count INT = 100000;
DECLARE @warehouses_count INT = 400;
DECLARE @boxes_count INT = 100000
DECLARE @n INT = 0;
declare @order_id int, @amount int, @date datetime;
while (@n < @payments_count / 2)
begin
SET @order_id = 1 + RAND() * (@orders_count - 1);
SET @amount = RAND() * 50000 + 20000;
SET @date = '2021-12-05'
INSERT INTO Payments (order_id, amount, date) VALUES (@order_id, @amount, @date);
SET @n = @n + 1;
end
set @n = 0;
while (@n < @payments_count / 2)
begin
SET @order_id = 1 + RAND() * (@orders_count - 1);
SET @amount = RAND() * 50000 + 20000;
SET @date = '2021-10-02'
INSERT INTO Payments (order_id, amount, date) VALUES (@order_id, @amount, @date);
SET @n = @n + 1;
end
GO
-- Warehouses
DECLARE @customers_count INT = 100000;
DECLARE @cities_count INT = 300;
DECLARE @orders_count INT = 100000;
DECLARE @payments_count INT = 100000;
DECLARE @warehouses_count INT = 400;
DECLARE @boxes_count INT = 100000
DECLARE @n INT = 0;
while (@n < @warehouses_count)
begin
DECLARE @city_id INT = 1 + RAND() * (@cities_count - 1);
DECLARE @area INT = RAND() * 100000 + 20000;
INSERT INTO Warehouses (city_id, area) VALUES (@city_id, @area);
SET @n = @n + 1;
end
GO
-- Boxes
DECLARE @customers_count INT = 100000;
DECLARE @cities_count INT = 300;
DECLARE @orders_count INT = 100000;
DECLARE @payments_count INT = 100000;
DECLARE @warehouses_count INT = 400;
DECLARE @boxes_count INT = 100000
DECLARE @n INT = 0;
while (@n < @boxes_count)
begin
declare @order_id int, @warehouse_id int, @wrapping_id int;
declare @volume float, @weight float;
SET @order_id = 1 + RAND() * (@orders_count - 1);
SET @warehouse_id = 1 + RAND() * (@warehouses_count - 1);
SET @wrapping_id = 1 + RAND() * 4;
SET @volume = rand() + 0.1;
SET @weight = @volume * 800;
INSERT INTO Boxes (order_id, warehouse_id, wrapping_id, volume, weight)
VALUES (@order_id, @warehouse_id, @wrapping_id, @volume, @weight);
SET @n = @n + 1;
end
GO
|
