== Определување функционални зависности

R = {user_id, first_name, last_name, phone, email, password, reservation_id, start_date, end_date, reserv_date, status, building_id, address, city, floors, num_rooms, room_number, room_type, number_of_beds, price_per_night, available, service_id, service_type, service_date, service_status, p_id, p_method, amount, p_date}

=== Функционални зависности

- user_id → first_name, last_name, phone, email, password. (FD1)

- customer_id → first_name, last_name, phone, email, password. (FD1a; исто како user_id)

- staff_id → first_name, last_name, phone, email, password. (FD1b)

- manager_id → first_name, last_name, phone, email, password. (FD1c)

- building_id → address, city, floors, num_rooms, manager_id. (FD2)

- (building_id, room_number) → room_type, number_of_beds, price_per_night, available. (FD3)

- reservation_id → start_date, end_date, reserv_date, status, room_number, building_id, customer_id, manager_id. (FD4)

- p_id → p_method, amount, p_date, reservation_id, customer_id. (FD5)

- service_id → service_type, service_date, service_status, room_number, building_id, staff_id. (FD6)

- staff_id → staff_building_id. (FD7) (секој ред на персоналот ја чува зградата каде што работи тој персонал)

(За јасност, атрибутите customer_id, manager_id, staff_id се сите user_id само поделени според класификацијата на различните видови на корисници на апликацијата (нивната улога е определена според user_id - бројот)
 
=== Класификација на атрибути

Лево – атрибути кои одредуваат други \\
user_id, building_id, (building_id, room_number), reservation_id, service_id, p_id

Лево и десно – атрибути кои се и детерминанти и зависни \\
room_number (во комбинација со building_id)

Десно – атрибути кои се само зависни \\
first_name, last_name, phone, email, password, address, city, floors, num_rooms, room_type, number_of_beds, price_per_night, available, start_date, end_date, reserv_date, status, p_method, amount, p_date, service_type, service_date, service_status

=== Покривачи на примарни клучеви

User
user_id+ = {user_id, first_name, last_name, phone, email, password}

Hotel Building
building_id+ = {building_id, address, city, floors, num_rooms}

Room
(building_id, room_number)+ = {room_number, building_id, room_type, number_of_beds, price_per_night, available, address, city, floors, num_rooms}

Reservation
reservation_id+ = {reservation_id, start_date, end_date, reserv_date, status, user_id (customer), manager_id, room_number, building_id}

Payment
p_id+ = {p_id, p_method, amount, p_date, reservation_id, customer_id}

Service
service_id+ = {service_id, service_type, service_date, service_status, staff_id, room_number, building_id}

=== Анализа според покривачи

Гледајќи ги функционалните зависности и врски, кандидатскиот примарен клуч за целата релација R може да се заклучи како:

**{user_id, building_id, room_number, reservation_id, p_id, service_id}**

- user_id е потребен за да се утврдат атрибутите на корисникот.

- building_id + room_number е потребен за да се утврдат атрибутите на собата.

- reservation_id е потребен за да се утврдат атрибутите на резервацијата (и преку резервација → соба може да се утврди соба/зграда, но атрибутите на собата постојат и надвор од резервациите).

- p_id е потребен за атрибути на плаќање (ниту еден друг FD не го одредува p_id).

- service_id е потребен за атрибути на услугата.

Дали некои атрибути може да се отстранат поради транзитивност? На пример, reservation_id → (room_number, building_id), па во принцип ако reservation_id постои за сите соби, тогаш reservation_id може да го замени (building_id, room_number). Но, собите постојат независно од резервациите, па затоа не можете да се потпрете на reservation_id за уникатно да ги одредите атрибутите на собата за сите соби. Бидејќи глобалната релација вклучува редови за соби кои не се поврзани со ниедна резервација, клучот-кандидат мора да го вклучува композитниот дел од собата. Затоа е потребен целиот сет на ПК-ви на ентитетите.

=== Анализа на нормални форми
==== 1 НФ

Услов за 1NF: вредности на атомски атрибути (без низи / вгнездени релации).

Нема повторувачки групи или низи, значи R е во 1NF.

==== 2NF (отстранување на делумни зависности)

2NF тест: ако релацијата има композитен кандидатски клуч, секој не-примарен атрибут мора да зависи од целиот клуч, а не од дел.

Бидејќи започнавме од голем композитен суперклуч K, многу FD како user_id -> first_name се делумни зависности во однос на тој суперклуч. Стандардниот пристап е да се извлечат детерминантите и нивните зависни во нивните сопствени релации. Овој чекор обично ги дава табелите со природни ентитети.

Декомпозиции (за отстранување на делумни зависности):

Ги извлекуваме детерминантите и нивните зависни во посебни релации:

===== Users

R1: Users(user_id, first_name, last_name, phone, email, password)

FDs во R1: user_id -> first_name, last_name, phone, email, password

PK: user_id 

R1 е во BCNF.

===== Hotel_Building

R2: Hotel_Building(building_id, address, city, floors, num_rooms, manager_id)

FDs во R2: building_id -> address, city, floors, num_rooms, manager_id

PK: building_id

R2 е во BCNF.

===== Room

R3: Room(room_number, building_id, room_type, number_of_beds, price_per_night, available)

PK: (room_number, building_id)

FDs: (room_number, building_id) -> room_type, number_of_beds, price_per_night, available

R3 е во BCNF.

===== Staff

R4: Staff(user_id /*staff_id*/, building_id)

FDs: staff_id -> building_id

PK: user_id (staff_id)

R4 е во BCNF.

===== Reservation

R5: Reservation(reservation_id, start_date, end_date, reserv_date, status, room_number, building_id, customer_id, manager_id)

FDs: reservation_id -> start_date, end_date, reserv_date, status, room_number, building_id, customer_id, manager_id

PK: reservation_id

R5 е во BCNF.

===== Service

R6: Service(service_id, service_type, service_date, service_status, room_number, building_id, staff_id)

FDs: service_id -> service_type, service_date, service_status, room_number, building_id, staff_id

PK: service_id

R6 е во BCNF.

===== Payment

R7: Payment(reservation_id PK, p_id UNIQUE, p_method, amount, p_date, customer_id)

FD: reservation_id -> p_id, p_method, amount, p_date, customer_id (1:1 means reservation determines payment)

PK: reservation_id

R7 е во BCNF.

По извлекувањето на овие релации, сите делумни зависности во однос на големиот суперклуч се отстрануваат - атрибутите што не се клуч на секоја релација сега целосно зависат од клучот на таа релација. Значи, сме во 2NF.

==== 3NF (отстранување на транзитивни зависности) преку 3NF Синтеза

Цел на 3NF: елиминирање на транзитивни зависности (не клучен атрибут кој зависи од друг не клучен атрибут). Канонскиот алгоритам за синтеза на 3NF е најдобар бидејќи ги зачувува зависностите.

Имплементирана постапка:

Пресметај канонична покриеност Fc на FDs. За секоја лева страна X соберете ги сите атрибути A т.е. X -> A во една релација R_X.

Креирај релации за секоја детерминанта:

Users(user_id, first_name, last_name, phone, email, password) — oд user_id FDs.

Hotel_Building(building_id, address, city, floors, num_rooms, manager_id) — oд building_id.

Room(room_number, building_id, room_type, number_of_beds, price_per_night, available) — од (room_number, building_id).

Staff(user_id, building_id) — од staff_id.

Reservation(reservation_id, start_date, end_date, reserv_date, status, room_number, building_id, customer_id, manager_id) — од reservation_id.

Service(service_id, service_type, service_date, service_status, room_number, building_id, staff_id) — од service_id.

Payment(reservation_id, p_id, p_method, amount, p_date, customer_id) — од reservation_id (1:1).

Се осигуруваме дека релацијата содржи клуч за оригиналниот R (имаме многу клучеви, но унијата на креирани релации ги вклучува сите детерминанти и со тоа ги зачувува клучевите).

3NF статус: секоја FD X -> A во Fc е локален на една од креираните релации (зачувување на зависноста). Бидејќи ги отстранивме атрибутите што предизвикуваа транзитивни ФЗ од нивните релации што содржат (на пр., не го задржавме price_per_night во Reservation), нема преостанати транзитивни зависности во релациите. Значи, сите релации се во 3NF.

==== BCNF правило: за секој FD X -> A во релацијата Ri, X мора да биде суперклуч на Ri.

Ја проверуваме секоја релација произведена во 3NF:

Users(user_id, ...) — FD лево од user_id е PK ⇒ BCNF.

Hotel_Building(building_id, ...) — building_id е PK ⇒ BCNF.

Room(room_number, building_id, ...) — (room_number, building_id) е сложен PK ⇒ BCNF.

Staff(user_id, building_id) — user_id е PK ⇒ BCNF.

Reservation(reservation_id, ...) — reservation_id е PK ⇒ BCNF.

Service(service_id, ...) — service_id е PK ⇒ BCNF.

Payment(reservation_id PK, ...) — reservation_id е PK ⇒ BCNF.

Бидејќи секоја детерминанта во секоја релација е примарен клуч на релацијата (или суперклуч), секоја релација е во BCNF под овие избори на клучеви.

Забелешка (компромис): BCNF декомпозицијата понекогаш може да ве принуди да изберете клуч кој не е најзгоден (на пр., ако Payment го воспостави p_id како PK, но reservation_id -> p_id постои, Payment би го прекршил BCNF — решивме со тоа што reservation_id го направивме PK за да го задоволиме BCNF без понатамошно декомпозиција). Ако претпочитате p_id како PK за да дозволите повеќекратни плаќања по резервација (1:N), тогаш p_id би бил детерминанта во Payment, а BCNF исто така важи.