Добавил часть четвёртого задания

3 files changed, 354 insertions, 0 deletions

diff --git a/task2_init-databases.sql b/task2_init-databases.sql
index 6955130..0e76801 100644
--- a/task2_init-databases.sql
+++ b/task2_init-databases.sql
@@ -143,6 +143,7 @@ GO
 
 ALTER TABLE Routes ADD FOREIGN KEY (city2_id) REFERENCES Cities (id)
 GO
+
 INSERT INTO Wrappings (type, price)
 VALUES (N'Жёсткая', 100),
        (N'Полужёсткая', 100),
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
diff --git a/task4_requests.sql b/task4_requests.sql
new file mode 100644
index 0000000..c364b95
--- /dev/null
+++ b/task4_requests.sql
@@ -0,0 +1,75 @@
+-----------------------
+-- Кластерный индекс --
+-----------------------
+
+SELECT amount FROM Payments WHERE order_id=12412;
+
+CREATE CLUSTERED INDEX Payments_Clustered ON Payments (order_id);
+GO
+DROP INDEX Payments_Clustered ON Payments;
+GO
+
+--------------------------
+-- Некластерные индексы --
+--------------------------
+
+-- составной (средне)
+
+SELECT Boxes.id, Orders.id, date
+FROM Boxes JOIN Orders
+ON Boxes.order_id = Orders.id
+WHERE Boxes.warehouse_id = 12465
+GO
+
+CREATE INDEX Boxes_Composite ON Boxes (order_id, warehouse_id);
+GO
+DROP INDEX Boxes_Composite ON Boxes;
+GO
+
+-- покрывающий (не очень)
+
+SELECT * FROM Orders
+WHERE EXISTS(
+    SELECT * FROM Customers
+    WHERE customer_id = Customers.id
+        AND Customers.email LIKE N'%@mail.ru'
+);
+
+CREATE INDEX Orders_Index_Covering ON Orders (id)
+    INCLUDE (customer_id, city_id, date);
+GO
+DROP INDEX Orders_Index_Covering ON Orders;
+GO
+
+-- уникальный (норм)
+
+SELECT email FROM Customers WHERE name=N'customer-12451';
+
+CREATE UNIQUE INDEX Customers_Unique ON Customers (name);
+GO
+DROP INDEX Customers_Unique ON Customers;
+GO
+
+-- индекс с включеными столбцами (не очень)
+
+SELECT date FROM Orders
+WHERE EXISTS(
+    SELECT * FROM Customers
+    WHERE customer_id = Customers.id
+        AND Customers.email LIKE N'%@mail.ru'
+);
+
+CREATE INDEX Orders_Index_Columns ON Orders (id)
+    INCLUDE (customer_id, date);
+GO
+DROP INDEX Orders_Index_Columns ON Orders;
+GO
+
+-- отфильтрованный индекс (средне)
+
+SELECT SUM(amount) FROM Payments WHERE date >= '2021-12-01';
+
+CREATE INDEX Payments_Filtered ON Payments (amount) WHERE date >= '2021-12-01';
+GO
+DROP INDEX Payments_Filtered ON Payments;
+GO