DB_Project

Project for DataBase coure at MIPT

Презентация

Ссылка на презентацию

Введение

Семестровый проект по курсу Базы Данных.

Постановка задачи

Нужно написать базу данных для администрирования отелей с применением Postgresql. Также можно использовать NOSQL технологии - JSON.

Актуальность

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

Что уже реализованно?

1. Выбрать предметную область, выделить в ней 4-6 сущностей, согласовать с семинаристом.

2. Спроектировать базу данных:

   1. Концептуальная модель: все сущности из п.1 с указанием связей в нотации «воронья лапка».
   2. Логическая модель: описание таблиц и их атрибутивного состава, а также указание связей в нотации «воронья лапка». БД должна находиться во 2НФ или в 3НФ (аргументировать свой выбор). После нормализации ~ 8-10 сущностей. Для описания использовать ER-диаграмму. Хотя бы одну таблицу необходимо сделать версионной. На выбор представляются SCD типа 2 и 4. Для именования полей и сущностей использовать multitran. В полях использовать стандартные суффикса из соответствующего файла.
   3. Физическая модель: описание хранения таблиц в СУБД. Для каждой сущности из п.2.ii подготовить таблицу вида (при желании можно добавить столбцы PRIMARY KEY, FOREIGN KEY со ссылкой на внешнюю таблицу):

   STUDENT

   Название	Описание	Тип данных	Ограничение
   STUDENT_ID	Идентификатор	INTEGER	PRIMARY KEY
   STUDENT_NAME	Имя студента	VARCHAR(128)	NOT NULL
   ...	...	...	...

3. Подготовить DDL скрипты и создать свою базу в СУБД.

4. Наполнить созданную базу данными, ~10 записей в каждой таблице. По крайней мере по 1 строке в каждой таблице должно быть добавлено с использованием INSERT.

5. Написать не менее 10 INSERT, SELECT, UPDATE, DELETE запросов. Загуглить, что такое CRUD-запросы. Найти соответствие.

6. Написать не менее 6 осмысленных SELECT-запросов с использованием:

   1. GROUP BY + HAVING
   2. ORDER BY
   3. <func>(...) OVER(...):
      1. PARTITION BY
      2. ORDER BY
      3. PARTITION BY + ORDER BY
      4. <func> - все 3 типа функций - агрегирующие, ранжирующие, смещения

7. К коду приложить ваши ожидания от работы запроса в виде комментария в CI. Пример: «В результате выполнения Запроса 1 будет получена сводная таблица с информацией о суммарном балле каждого студента XXX группы по итогам кр1».

8. Создать индексы для таблиц, аргументировав выбор поля, по которому будет создан индекс.

9. Подготовить не менее 6 представлений:

   1. 2-3 получаются сокрытием полей с персональными данными клиентов из таблицы, а также сокрытием технических полей. Для сокрытия полей с персональными данными недостаточно просто целиком удалить столбец с данными. Например, для поля CARD_NO можно использовать маскировку вида 4276********0000.
   2. 3-4 получаются соединением нескольких таблиц с целью получения осмысленной сводной таблицы, например, хранящей некоторую статистику продаж/частот обращения клиента и т.д. Вместе с кодом приложить описание представления.

10. Создать не менее 2 хранимых процедур. Логика процедур согласовывается с семинаристом.

11. Создать не менее 2 триггеров. Логика согласовывается с семинаристом.

12. Используя любимый язык программирования и библиотеку, сгенерировать данные и с их помощью вставить данные в уже оформленную БД. Теми же инструментами извлечь данные [из таблицы на выбор], возможно, предварительно агрегированные средствами СУБД, и провести анализ.

Что осталось реализовать?

*Ожидает расширения*

Концептуальная модель

Описание отношений

1. Работник - Заказ: один ко многим включая ноль. Поскольку у работника может как и не быть заказов, так и быть их несколько, в то время как заказ привязывается к определенному работнику.
2. Апартаменты - Заказ: многие включая один к одному. Поскольку в заказе можно указать несколько апартаментов, а конкертный апартамент привязан к заказу.
3. Постоялец - Заказ: один ко многим включая один. Аналогично с работником, только в данном случае, постоялец не может не сделать заказа
4. Постоялец - Бронирование Заказа: один ко многим включая один. Поскольку постоялец может бронировать несколько заказов.
5. Бронирование Заказа - Заказ: один к одному. Так как заказ можно забранировать только одним человеком.
6. Бронирование Заказа - Оплата Заказа: один к одному. Так как заказ забранированный заказ можно оплатить только один раз.

Логическая модель

Физическая модель

Order Заказ

Название	Описание	Тип данных	Ограничение
ID_order	Индентификатор заказа	BIGINT	NOT NULL
ID_client	Индентификатор постояльца	INT	NOT NULL
ID_employee	Индентификатор сотрудника	INT	NOT NULL

Client Постоялец

Название	Описание	Тип данных	Ограничение
ID_client	Индентификатор постояльца	BIGSERIAL	NOT NULL
client_firstname	Имя постояльца	VARCHAR(50)	NOT NULL
client_lastname	Фамилия постояльца	VARCHAR(50)	NOT NULL
client_mobile_phone	Номер моибльного телефона постояльца	VARCHAR(12)	NOT NULL
client_passport	Номер пасспорта постояльца	VARCHAR(30)	NOT NULL
client_email	Электронная почта постояльца	VARCHAR(200)
client_card_number	Кредитная карта постояльца(Для оплаты)	VARCHAR(50)	NOT NULL
client_birthday	Дата рождения постояльца	DATE	NOT NULL

Employee Работник

Название	Описание	Тип данных	Ограничение
ID_employee	Индентификатор работника	BIGSERIAL	NOT NULL
employee_firstname	Имя работника	VARCHAR(50)	NOT NULL
employee_lastname	Фамилия работника	VARCHAR(50)	NOT NULL
employee_mobile_phone	Номер моибльного телефона работника	VARCHAR(12)	NOT NULL
employee_passport	Номер пасспорта работника	VARCHAR(30)	NOT NULL
employee_email	Электронная почта работника	VARCHAR(200)
employee_salary	Зарплата работника	DECIMAL	NOT NULL
employee_birthday	Дата рождения работника	DATE	NOT NULL

OrderDetails Детали Заказа

Название	Описание	Тип данных	Ограничение
ID_order_details	Индентификатор детали заказа	BIGSERIAL	NOT NULL
ID_order	Индентификатор заказа	BIGINT	NOT NULL
ID_client	Индентификатор клиента	BIGINT	NOT NULL
ID_apartment	Индентификатор апартаментов	INT	NOT NULL
order_details_arrival_time	Время прибытия в отель	TIMESTAMP	NOT NULL
order_details_departure_time	Когда отель был покинут	TIMESTAMP	NOT NULL
order_details_surcharge	Стоимость доплаты	DECIMAL	DEFAULT 0
order_details_people_amount	Коилчество людей проживающих в номере	INT	NOT NULL
order_details_is_running	Активен ли заказ	BOOLEAN	NOT NULL
order_details_prolong_count	Количество доплат заказа	INT	DEFAULT 0

BookOrder Бронирование Заказа

Название	Описание	Тип данных	Ограничение
ID_booked_order	Индентификатор забранированного заказа	BIGSERIAL	NOT NULL
ID_order	Индентификатор заказа	BIGINT	NOT NULL
ID_client	Индентификатор клиента	BIGINT	NOT NULL
booked_order_is_booked	Забронирован ли заказ	BOOLEAN	NOT NULL
booked_order_is_canceled	Отменен ли заказ	BOOLEAN	NOT NULL
booked_order_status	Статус бронирования заказа	BOOLEAN	NOT NULL

PayForOrder Оплата Заказа

Название	Описание	Тип данных	Ограничение
ID_pay_for_order	Индентификатор оплаченного заказа	BIGSERIAL	NOT NULL
ID_booked_order	Индентификатор забранированного заказа	INT	NOT NULL
pay_for_order_is_paid	Оплачен ли был заказ	BOOLEAN	NOT NULL
pay_for_order_is_received	Дошла ли оплата	BOOLEAN	NOT NULL
pay_for_order_paid_sum	Какая сумма была оплачена	DECIMAL	NOT NULL
pay_for_order_remaining_sum	Какую сумму осталось заплатить	DECIMAL	NOT NULL
pay_for_order_payment_method	Способ оплаты	VARCHAR(20)
pay_for_order_payment_count	Количество произведенных оплат	INT	NOT NULL

Apartment Апартаменты

Название	Описание	Тип данных	Ограничение
ID_apartment	Индентификатор апартоментов	BIGSERIAL	NOT NULL
apartment_capacity	Вместимость апартаментов	INT
apartment_number	Номер апартаментов	INT	NOT NULL
apartment_class	Класс апартаментов	VARCHAR(50)	NOT NULL
apartment_price_for_night	Цена за ночь	DECIMAL	NOT NULL
apartment_is_available	Доступны ли апартаменты	BOOlEAN	NOT NULL