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

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

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

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

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

Реализация

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

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

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

Запускаем процесс экспорта: File -> Export -> Forward Engineer SQL CREATE Script...
Задайте параметры экспорта
- Название файла можно не вводить, сохранять мы его не будем
- Generate DROP Statements Before Each CREATE Statement - удалять сущности перед созданием. При первой итерации не надо, но если что-то придётся менять, то надо включить
- Generate DROP SHEMA - удалять базу данных. На практике можно, а на демо экзамене нет - там у вас не будет прав на удаление базы
- Omit Shema Qualifier in Object Names - убрать название базы данных из запросов. По-умолчанию скрипт генерируется с названиями БД. Можно этот параметр не устанавливать, но в этом случае нужно задать название модели как у вашей БД (на экзамене userNN, на практике что-то типа ekolesnikov_de2025)
Фильтр экспорта. Тут ничего менять не нужно
Правим скрипт

Если мы выбрали пункт Omit Shema Qualifier in Object Names на первом шаге, то необходимо добавить команду выбора базы данных (предполагается, что БД уже существует)
```
use ekolesnikov_de2025;
```
Теперь скрипт можно скопировать и выполнить его в любом менеджере БД (мне больше нравится DBeaver)
Создание базы данных из скрипта (в DBeaver)
- вставляем созданный скрипт в окно SQL скрипта
- выполняем весь скрипт

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

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

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

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

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

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

RoomType

Сначала пишем запрос выбора уникальных категорий
```
select distinct ri.`Категория` 
from room_import ri;
```
Затем добавляем команду вставки:
```
insert into RoomType (title)
select distinct ri.`Категория` 
from room_import ri;
```
При текущих типах данных получим ошибку - названия категорий длинее 45 символов, придётся поменять длину поля title (можно прямо в базе, но не забыть поправить и в ERD, чтобы выгрузить правильный PDF)

RoomFloor

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

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

RoomStatus

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

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

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

-- 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;

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

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

В задании не сказано, но в будущем всё-равно понадобится, поэтому самостоятельно импортируйте "Постояльцев" (при этом создайте и бронирования)

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

% загрузки номерного фонда = количество проданных ночей за расчётный период / общее количество номеров за тот же период * 100%.

Пример расчёта:

Гостиница на 100 номеров, расчёт загрузки за месяц (30 дней). Продано (занято) было за месяц 2100 номер ночей. Всего в месяце = 100 номеров * 30 дней = 3 000 номер ночей. Загрузка за расчётный период = 2100 / 3000 * 100% = 70%

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

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

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

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

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

Остаётся просто разделить на общее количество номеров помноженное на количество дней (пока подставляем константу 30 дней):

SELECT COUNT(*) * 30 FROM Room;

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

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

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

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

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

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

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

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, то увидим такое:

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

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

SELECT SUM(
	GREATEST( 
        DATEDIFF(
		    LEAST(b.dateTo, '2025-02-28'), 
		    GREATEST(b.dateFrom, '2025-02-01')
	    ), 
        0
    )
) AS res
FROM Booking b;

Ещё нужно учитывать количество ночей в знаменателе. Итоговый запрос будет таким:

SELECT 100 * (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;

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

DROP FUNCTION IF EXISTS PercentLoad;
DELIMITER //
CREATE FUNCTION PercentLoad(df DATE, dt DATE) 
RETURNS REAL
DETERMINISTIC
BEGIN
    DECLARE result REAL DEFAULT 0;
    SELECT 100 * (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 ;

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

SELECT PercentLoad('2025-02-01', '2025-02-28');

module2.md 12 KB Постоянная ссылка История Исходник