# Модуль № 2: Соадминистрирование баз данных и серверов

## Текст задания

**Создайте базу данных на основании разработанной ER-диаграммы**, используя предпочтительную платформу, на сервере баз данных, который вам предоставлен. Создайте таблицы основных сущностей, атрибуты, отношения и необходимые ограничения. После создания базы данных требуется **импортировать данные** из файла `Номерной фонд.xlsx`.

**Создайте запрос вычисляющий процент загрузки номерного фонда** – это отношение количества проданных ночей к общему количеству номеров в отеле.

Необходимые приложения: `Текст брифинга.pdf`, `Документы заказчика.zip` (в этом репозитории уже распаковано в одноименный каталог)

## Реализация

### Создание базы данных и таблиц

**MySQL Workbench** позволяет создавать DDL скрипт из ER-диаграммы.

Можно делать экспорт сразу в базу данных, но лично мне **MySQL Workbench** как менеджер БД не нравится, поэтому рассмотрю вариант с генерацией DDL скрипта без записи в базу

1. Запускаем процесс экспорта: _File -> Export -> Forward Engineer SQL CREATE Script..._

    ![](../solution/export01.png)

1. Задайте параметры экспорта

    ![](../solution/export02.png)

    - Название файла можно не вводить, сохранять мы его не будем
    - _Generate DROP Statements Before Each CREATE Statement_ - удалять сущности перед созданием. При первой итерации не надо, но если что-то придётся менять, то надо включить
    - _Generate DROP SHEMA_ - удалять базу данных. На практике можно, а на демо экзамене нет - там у вас не будет прав на удаление базы
    - _Omit Shema Qualifier in Object Names_ - убрать название базы данных из запросов. По-умолчанию скрипт генерируется с названиями БД. Можно этот параметр не устанавливать, но в этом случае нужно задать название модели как у вашей БД (на экзамене `userNN`, на практике что-то типа `ekolesnikov_de2025`)

1. Фильтр экспорта. Тут ничего менять не нужно

    ![](../solution/export03.png)

1. Правим скрипт

    Если мы выбрали пункт _Omit Shema Qualifier in Object Names_ на первом шаге, то необходимо добавить команду выбора базы данных (предполагается, что БД уже существует)

    ```sql
    use ekolesnikov_de2025;
    ```
    
    ![](../solution/export04.png)

    Теперь скрипт можно скопировать и выполнить его в любом менеджере БД (мне больше нравится **DBeaver**)

1. Создание базы данных из скрипта (в **DBeaver**)

    ![](../solution/export05.png)

    - вставляем созданный скрипт в окно SQL скрипта
    - выполняем весь скрипт

### Импорт данных

>После создания базы данных требуется **импортировать данные** из файла `Номерной фонд.xlsx`.

В данных никакой нормализации не требуется, поэтому можем сразу делать экспорт в CSV

![](../solution/import01.png)

Процесс импорта не расписываю, это вы уже должны уметь. В итоге должна получиться **таблица импорта**

![](../solution/import02.png)

Из **таблицы импорта** нам нужно разнести данные в таблицы **Room**, **RoomType**, **RoomFloor**, **RoomStatus**

Сначала заполняем словари:

1. **RoomType**

    Сначала пишем запрос выбора уникальных категорий

    ```sql
    select distinct ri.`Категория` 
    from room_import ri;
    ```

    ![](../solution/import03.png)

    Затем добавляем команду вставки:

    ```sql
    insert into RoomType (title)
    select distinct ri.`Категория` 
    from room_import ri;
    ```

    При текущих типах данных получим ошибку - названия категорий длинее 45 символов, придётся поменять длину поля `title` (можно прямо в базе, но не забыть поправить и в ERD, чтобы выгрузить правильный PDF)

    ![](../solution/import04.png)

1. **RoomFloor**

    Делаем аналогично

    ```sql
    insert into RoomFloor (title)
    select distinct ri.`Этаж` 
    from room_import ri;
    ```

1. **RoomStatus**

    Исходных данных для этой таблицы нет, но мы должны добавить хотя бы один статус "Свободен" (или "Чистый"), иначе не сможем добавить номера в таблицу **Room**

Теперь, имея все словари, можно заполнять таблицу **Room**

Сначала пробуем выбрать данные со всеми связями. В значение **roomStatusId** пишем константу `1` (тут должен быть реально существующий `id` из таблицы **RoomStatus**)

```sql
-- insert into Room (roomTypeId, `number`, roomStatusId, roomFloorId)
select rt.id, ri.Номер, 1, rf.id 
from room_import ri, RoomType rt, RoomFloor rf
where ri.Категория = rt.title 
	AND ri.Этаж = rf.title;
```

![](../solution/import05.png)

Убедившись, что всё нормально, раскомментируем **import** и выполняем вставку

После импорта удалите таблицу **room_import**, т.к. она нарушает **3НФ**

### Создание запроса

**Создайте запрос вычисляющий процент загрузки номерного фонда** – это отношение количества проданных ночей к общему количеству номеров в отеле.

Перед выполнением этого задания нужно добавить пару записей в таблицу **Booking** (а перед этим и в **User**)

В информации о брони у нас есть даты заезда и выезда. Для нахождения количества дней между двумя датами в SQL есть функция **DATEDIFF**

```sql
SELECT DATEDIFF(b.dateTo, b.dateFrom)
FROM Booking b;
```

Для суммы всех бронирований применяется аггрегирующая функция **SUM**:

```sql
SELECT SUM( DATEDIFF(b.dateTo, b.dateFrom) )
FROM Booking b; 
```

Остаётся просто разделить на общее количество номеров:

```sql
SELECT COUNT(*) FROM Room;
```

Итоговый скрипт будет выглядеть так:

```sql
SELECT (
    SELECT SUM( DATEDIFF(b.dateTo, b.dateFrom) )
    FROM Booking b 
) / (
    SELECT COUNT(*) FROM Room 
);
```

Для демо-экзамена этого достаточно, но желательно добавить выборку за указанный диапазон.

В исходных данных два бронирования:

* с `2025-01-10` по `2025-02-17`
* с `2025-01-01` по `2025-01-09`

Допустим хотим выбрать данные только за февраль.

Для учёта даты использовать WHERE нельзя, нужно обрезать лишние данные в DATEDIFF:

```sql
SELECT SUM(
	DATEDIFF(
		LEAST(b.dateTo, '2025-02-28'), 
		GREATEST(b.dateFrom, '2025-02-01')
	)
) as res
from Booking b; 
```

где функция **LEAST** возвращает минимум из двух значений, а функция **GREATEST** возвращает максимум из двух значений (**MIN** и **MAX** являются аггрегатными функциями)

Если запустить этот скрипт, то получим `-7` дней. если убрать SUM, то увидим такое:

![](../solution/import06.png)

Дело в том, что для январской брони у нас получился `DATEDIFF('2025-01-09', '2025-02-01')`

Чтобы не учитывать отрицательные данные можно добавить ещё один **GREATEST**

```sql
SELECT SUM(
	GREATEST( 
        DATEDIFF(
		    LEAST(b.dateTo, '2025-02-28'), 
		    GREATEST(b.dateFrom, '2025-02-01')
	    ), 
        0
    )
) as res
from Booking b; 
```

Ещё нужно учитывать количество ночей в знаменателе:

```sql
SELECT (SUM(
	GREATEST( 
        DATEDIFF(
		    LEAST(b.dateTo, '2025-02-28'), 
		    GREATEST(b.dateFrom, '2025-02-01')
	    ), 
        0
    )
)) / (
    SELECT count(*) * DATEDIFF('2025-02-28', '2025-02-01') FROM Room
) as res
from Booking b; 
```

Можно завернуть это в пользовательскую функцию

```sql
DROP FUNCTION IF EXISTS PercentLoad;
DELIMITER //
CREATE FUNCTION PercentLoad(df DATE, dt DATE) 
returns real
DETERMINISTIC
BEGIN
    DECLARE result real DEFAULT 0;
    SELECT (SUM(
	    GREATEST( 
            DATEDIFF(
		        LEAST(b.dateTo, dt), 
		        GREATEST(b.dateFrom, df)
	        ), 
            0
        )
    )) / (
        SELECT count(*) * DATEDIFF(dt, df) 
        FROM Room
    ) 
    INTO result
    from Booking b;
    return result;
END//
DELIMITER ; 
```

И использовать её при необходимости

```sql
select PercentLoad('2025-02-01', '2025-02-28');
```