Напиши, являются ли таблицы в 1НФ, 2НФ, 3НФ
DROP DATABASE restaurant;
CREATE DATABASE restaurant;
USE restaurant;

--  Таблица отдыхающих -- 

CREATE TABLE vacationers
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Persons_name VARCHAR(20) NOT NULL,
    Persons_surname VARCHAR(20) NOT NULL,
    Age TINYINT UNSIGNED NOT NULL,
    Phone VARCHAR(11) UNIQUE NOT NULL,
    Email VARCHAR(30) UNIQUE,
    Passport VARCHAR(10) NOT NULL UNIQUE,
    Chek_in_date DATETIME NOT NULL,
    Time_of_remain TINYINT UNSIGNED NOT NULL
);

--  Таблица видов блюд -- 

CREATE TABLE dish_type
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	Dish_name VARCHAR(30) NOT NULL,
	Start_time TIME NOT NULL,
	End_time TIME NOT NULL
);

--  Таблица рецептов -- 

CREATE TABLE recipe
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Action_list TEXT NOT NULL,
    Cooking_time TIME NOT NULL
);

-- Таблица продукта -- 

CREATE TABLE foodstuff
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Food_name VARCHAR(20) NOT NULL,
	Calorific DECIMAL(6,2) NOT NULL
);

-- Таблица склада -- 

CREATE TABLE food_storage
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Foodstuff_id INT UNSIGNED NOT NULL,
    Amount DECIMAL(5, 2) NOT NULL,
    FOREIGN KEY (Foodstuff_id) REFERENCES foodstuff(Id)
);

-- Таблица блюда -- 

CREATE TABLE dish
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Recipe_id INT UNSIGNED NOT NULL,
    Dish_name VARCHAR(20) NOT NULL,
    Calorific DECIMAL(6,2) NOT NULL,
    Weight DECIMAL(6,2) NOT NULL,
    Cost_price DECIMAL(6, 2) NOT NULL,
    FOREIGN KEY (Recipe_id) REFERENCES recipe(Id)
);

-- Таблица состав блюда -- 

CREATE TABLE composition
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Dish_id INT UNSIGNED NOT NULL,
    Food_id INT UNSIGNED NOT NULL,
    Amount DECIMAL(5, 2) NOT NULL,
    FOREIGN KEY (Dish_id) REFERENCES dish(Id),
    FOREIGN KEY (Food_id) REFERENCES foodstuff(Id)
);

-- Таблица меню -- 

CREATE TABLE menu
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Dish_id INT UNSIGNED NOT NULL,
    Type_id INT UNSIGNED NOT NULL,
    Dish_description TINYTEXT NOT NULL,
    Price DECIMAL(6, 2) NOT NULL,
    FOREIGN KEY (Dish_id) REFERENCES dish(Id),
    FOREIGN KEY (Type_id) REFERENCES dish_type(Id)
);

-- Таблица трапеза -- 

CREATE TABLE repast
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Vacationer_id INT UNSIGNED NOT NULL,
    Menu_id INT UNSIGNED NOT NULL,
    Repast_date DATETIME NOT NULL,
    Cost DECIMAL(7, 2) NOT NULL,
    FOREIGN KEY (Vacationer_id) REFERENCES vacationers(Id),
    FOREIGN KEY (Menu_id) REFERENCES menu(Id)
);

-- Таблица касса -- 

CREATE TABLE till
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Profit DECIMAL(10, 2) NOT NULL,
    Decline DECIMAL(10, 2) NOT NULL,
    Total DECIMAL(10, 2) NOT NULL 
);

--  Таблица поставщиков -- 

CREATE TABLE provider
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Provider_name VARCHAR(20) NOT NULL,
    Provider_type VARCHAR(40) NOT NULL,
    Phone VARCHAR(11) UNIQUE NOT NULL,
    TIN VARCHAR(12) UNIQUE NOT NULL,
    Email VARCHAR(30) UNIQUE,
    Adress TINYTEXT
);

-- Таблица поставок -- 

CREATE TABLE delivery
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Foodstuff_id INT UNSIGNED NOT NULL,
    Amount DECIMAL(5, 2) NOT NULL, 
    Price DECIMAL(7, 2) NOT NULL,
    FOREIGN KEY (Foodstuff_id) REFERENCES foodstuff(Id)
);

-- Таблица квитанция -- 

CREATE TABLE estimates
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Provider_id INT UNSIGNED NOT NULL,
    Delivery_id INT UNSIGNED NOT NULL,
    Cost DECIMAL(8, 2) NOT NULL,
    Receipt_date DATETIME NOT NULL,
    FOREIGN KEY (Provider_id) REFERENCES provider(Id),
    FOREIGN KEY (Delivery_id) REFERENCES delivery(Id)
);

-- Таблица столов -- 

CREATE TABLE places
(
    Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    SeatCount TINYINT UNSIGNED NOT NULL,
    IsEmpty BOOL DEFAULT TRUE
);

-- Таблица бронирования -- 

CREATE TABLE booking
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Place_id INT UNSIGNED NOT NULL,
    Vacationers_id INT UNSIGNED NOT NULL,
    Visibility TINYINT UNSIGNED NOT NULL,
    FOREIGN KEY (Place_id) REFERENCES places(Id),
    FOREIGN KEY (Vacationers_id) REFERENCES vacationers(Id)
);

-- Заполнение таблицы отдыхающих

INSERT INTO vacationers (Persons_name, Persons_surname, Age, Phone, Email, Passport, Chek_in_date, Time_of_remain) VALUES
('Иван', 'Иванов', 34, '89101112233', 'ivanov@example.com', '1234567890', '2024-05-01 14:00:00', 10),
('Кирилл', 'Коротыш', 20, '89231820255', 'korotysh@gmail.com', '5017456312', '2024-05-17 10:00:00', 180),
('Матвей', 'Полухин', 20, '89137721052', 'matvey@gmail.com', '5017456493', '2024-02-01 12:30:12', 1),
('Александр', 'Башкиров', 19, '89137396510', 'sanya@gmail.com', '1488228228', '2023-01-01 00:00:00', 23),
('Мария', 'Петрова', 28, '89102223344', 'petrova@example.com', '0987654321', '2024-05-02 11:48:00', 7);

-- Заполнение таблицы видов блюдa

INSERT INTO dish_type (Dish_name, Start_time, End_time) VALUES
('Суп', '12:00', '16:00'),
('Салат', '12:00', '16:00'),
('Горячее', '16:00', '21:00'),
('Десерт', '10:00', '16:00');

-- Заполнение таблицы рецептов 

INSERT INTO recipe (Action_list, Cooking_time) VALUES
('Очистить картофель, нарезать кубиками, варить 20 минут', '00:30:00'),
('Нарезать овощи, заправить маслом, перемешать', '00:15:00'),
('Нарезать яблоки, замешать муку, залить в форму, выложить яблоки, запекать 40 минут', '00:40:00');

-- Заполнение таблицы продуктов

INSERT INTO foodstuff (Food_name, Calorific) VALUES
('Картофель', 77.00),
('Морковь', 41.00),
('Красный перец', 20.00),
('Огурец', 10.30),
('Говядина', 250.00),
('Молоко', 42.00),
('Сыр', 300),
('Хлеб', 250),
('Курица', 500),
('Рыба', 220),
('Масло', 900),
('Мука', 10),
('Яблоки', 52);

-- Заполнение таблицы склада

INSERT INTO food_storage (Foodstuff_id, Amount) VALUES
(1, 100.00),
(2, 50.00),
(3, 200.00),
(4, 100.00),
(5, 50.00),
(6, 200.00),
(7, 100.00),
(8, 50.00),
(9, 200.00),
(10, 100.00),
(11, 50.00),
(12, 200.00),
(13, 50.00);

-- Заполнение таблицы блюда

INSERT INTO dish (Recipe_id, Dish_name, Calorific, Weight, Cost_price) VALUES
(1, 'Картофельное пюре', 150.00, 200.00, 50.00),
(2, 'Овощной салат', 80.00, 150.00, 30.00),
(3, 'Яблочный пирог', 300.00, 100.00, 100.00);

-- Заполнение таблицы состав блюда

INSERT INTO composition (Dish_id, Food_id, Amount) VALUES
(1, 1, 200.00),
(2, 2, 40.00),
(2, 3, 50.00),
(3, 6, 30.00),
(3, 13, 60.00),
(3, 12, 10.00),
(2, 4, 60.00);

-- Заполнение таблицы меню

INSERT INTO menu (Dish_id, Type_id, Dish_description, Price) VALUES
(1, 3, 'Вкусное картофельное пюре', 100.00),
(2, 2, 'Свежий овощной салат', 60.00),
(3, 4, 'Нежный яблочный пирог', 199.99);

-- Заполнение таблицы трапеза

INSERT INTO repast (Vacationer_id, Menu_id, Repast_date, Cost) VALUES
(1, 1, '2024-05-01 19:00:00', 100.00),
(2, 2, '2024-05-03 14:00:00', 60.00),
(2, 2, '2024-02-02 12:36:00', 4000.00);

-- Заполнение таблицы касса

INSERT INTO till (Profit, Decline, Total) VALUES
(1600.00, 600.00, 1000.00);

-- Заполнение таблицы поставщиков

INSERT INTO provider (Provider_name, Provider_type, Phone, TIN, Email, Adress) VALUES
('ООО Продукты', 'Продукты питания', '89011234567', '123456789012', 'products@mail.ru', 'ул. Примерная, д. 1'),
('ООО Фастфуд', 'Продукты быстрого приготовления', '89011234566', '098765432107', 'nyamnyam@mail.ru', 'ул. Примерная, д. 3'),
('ООО Весёлый молочник', 'Молокочная продукция', '89012234566', '198765432107', 'milk@mail.ru', 'ул. Молочная, д. 4'),
('ООО Мясо', 'Мясная продукция', '89019876543', '098765432109', 'meat@mail.ru', 'ул. Примерная, д. 2');

-- Заполнение таблицы поставок

INSERT INTO delivery (Foodstuff_id, Amount, Price) VALUES
(1, 10.00, 300.00),
(2, 15.00, 375.00),
(3, 25.00, 1250.00),
(4, 12.00, 180.00),
(5, 20.00, 1000.00),
(6, 10.00, 600.00),
(7, 5.00, 1000.00),
(8, 5.00, 150.00),
(9, 15.00, 1350.00),
(10, 10.00, 1700.00),
(11, 8.00, 880.00),
(12, 10.00, 200.00),
(6, 15.00, 300.00),
(6, 15.00, 300.00),
(6, 15.00, 300.00),
(6, 15.00, 300.00),
(6, 15.00, 300.00),
(6, 15.00, 300.00),
(6, 15.00, 300.00),
(6, 15.00, 300.00),
(6, 15.00, 300.00),
(6, 15.00, 300.00),
(6, 15.00, 300.00),
(6, 15.00, 300.00),
(5, 15.00, 300.00),
(8, 15.00, 300.00),
(9, 15.00, 300.00),
(5, 15.00, 300.00),
(8, 15.00, 300.00),
(9, 15.00, 300.00),
(5, 15.00, 300.00),
(8, 15.00, 300.00),
(9, 15.00, 300.00),
(5, 15.00, 300.00),
(8, 15.00, 300.00),
(9, 15.00, 300.00);

-- Заполнение таблицы квитанций

INSERT INTO estimates (Provider_id, Delivery_id, Cost, Receipt_date) VALUES
(1, 1, 300.00, '2024-06-01 09:00:00'),
(1, 2, 375.00, '2024-06-02 09:30:00'),
(1, 3, 1250.00, '2024-06-03 10:00:00'),
(1, 4, 180.00, '2024-06-04 10:30:00'),
(1, 5, 8000.00, '2024-06-05 11:00:00'),
(1, 6, 600.00, '2024-06-06 11:30:00'),
(1, 7, 1000.00, '2024-06-07 12:00:00'),
(1, 8, 150.00, '2024-06-08 12:30:00'),
(1, 9, 1350.00, '2024-06-09 13:00:00'),
(1, 10, 1700.00, '2024-06-10 13:30:00'),
(1, 11, 880.00, '2024-06-11 14:00:00'),
(1, 12, 200.00, '2024-06-12 14:30:00'),
(3, 13, 300.00, '2024-06-13 15:00:00'),
(3, 14, 300.00, '2024-06-13 15:00:00'),
(3, 15, 300.00, '2024-06-13 15:00:00'),
(3, 16, 300.00, '2024-06-13 15:00:00'),
(3, 17, 300.00, '2024-06-13 15:00:00'),
(3, 18, 300.00, '2024-06-13 15:00:00'),
(3, 19, 300.00, '2024-06-13 15:00:00'),
(3, 20, 320.00, '2024-06-13 15:00:00'),
(3, 21, 21.00, '2024-06-13 15:00:00'),
(3, 22, 3333.00, '2024-06-13 15:00:00'),
(3, 23, 4444.00, '2024-06-13 15:00:00'),
(3, 24, 45.00, '2024-06-13 15:00:00'),
(4, 25, 999.00, '2024-06-13 15:00:00'),
(4, 26, 234.00, '2024-06-13 15:00:00'),
(4, 27, 34534.00, '2024-06-13 15:00:00'),
(4, 28, 2.00, '2024-06-13 15:00:00'),
(4, 29, 232.00, '2024-06-13 15:00:00'),
(4, 30, 5949.00, '2024-06-13 15:00:00'),
(4, 31, 1230.00, '2024-06-13 15:00:00'),
(4, 32, 34234.00, '2024-06-13 15:00:00'),
(4, 33, 32434.00, '2024-06-13 15:00:00'),
(4, 34, 3333.00, '2024-06-13 15:00:00'),
(4, 35, 1230.00, '2024-06-13 15:00:00'),
(4, 36, 3000.00, '2024-06-13 15:00:00');

-- Заполнение таблицы столов

INSERT INTO places (SeatCount, IsEmpty) VALUES
(3, 0),
(4, 0),
(5, 0),
(1, 0),
(2, 0),
(2, 0),
(4, 0),
(5, 0),
(3, 0),
(3, 0);

-- Заполнение таблицы бронирования

INSERT INTO booking (Vacationers_id, Place_id, Visibility) VALUES
(1, 1, 3),
(2, 9, 3),
(3, 5, 2),
(4, 10, 3),
(5, 4, 1);

-- Запрос. Определить долю веса продукта в общем весе блюда в процентах
 
SELECT 
    d.Dish_name AS BLUDO,
    f.Food_name AS NAME_PROD,
    d.Weight AS VEC,
    (c.Amount / d.Weight * 100) AS PROC
FROM 
    composition c
JOIN 
    dish d ON c.Dish_id = d.Id
JOIN 
    foodstuff f ON c.Food_id = f.Id
ORDER BY 
    d.Dish_name ASC;

--  Запрос. Определить первых 3 поставщиков, осуществивших поставки на наибольшую сумму

SELECT 
    p.Provider_name AS NAME_POSTAVSHIK,
    SUM(e.Cost) AS SUMMA
FROM 
    provider p
JOIN 
    estimates e ON p.Id = e.Provider_id
JOIN 
    delivery d ON e.Delivery_id = d.Id
GROUP BY 
    p.Provider_name
HAVING 
    COUNT(DISTINCT d.Id) >= 12 AND SUM(d.Amount) >= 5
ORDER BY 
    SUMMA DESC
LIMIT 3;

-- Запрос. Показать относительный ранг веса продукта среди других продуктов этого же блюда

SELECT 
    d.Dish_name AS BLUDO,
    f.Food_name as NAME_PROD,
    c.Amount AS VEC,
    RANK() OVER (PARTITION BY c.Dish_id ORDER BY c.Amount ASC) AS RANK_
FROM 
    composition c
JOIN 
    foodstuff f ON c.Food_id = f.Id
JOIN 
    dish d ON c.Dish_id = d.Id
ORDER BY 
    d.Dish_name ASC;

-- Запрос. Подсчет скользящего значения. Получить среднее значение цены продукта в ходе поступления продуктов

SELECT 
    f.Food_name AS NAME_PROD,
    e.Receipt_date AS DATA_POSTAVKI,
    d.Price AS CENA,
    SUM(d.Price * d.Amount) OVER (PARTITION BY f.Food_name ORDER BY e.Receipt_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / SUM(d.Amount) OVER (PARTITION BY f.Food_name ORDER BY e.Receipt_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS AVG_C
FROM 
    delivery d
JOIN 
    foodstuff f ON d.Foodstuff_id = f.Id
JOIN
	estimates e ON e.Delivery_id = d.Id
ORDER BY 
    e.Receipt_date ASC;