summaryrefslogtreecommitdiff
path: root/task4_init-database.sql
diff options
context:
space:
mode:
Diffstat (limited to 'task4_init-database.sql')
-rw-r--r--task4_init-database.sql278
1 files changed, 278 insertions, 0 deletions
diff --git a/task4_init-database.sql b/task4_init-database.sql
new file mode 100644
index 0000000..7c54e60
--- /dev/null
+++ b/task4_init-database.sql
@@ -0,0 +1,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
generated by cgit v1.2.3 (git 2.25.1) at 2025-12-17 11:02:14 +0000