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

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}

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

Users
user_id → first_name, last_name, phone, email, password

Customer (extends User)
user_id → {customer role}

Manager (extends User)
user_id → {manager role}

Staff (extends User)
user_id → {staff role}

Hotel Building
building_id → address, city, floors, num_rooms

Room (слаб ентитет)
(building_id, room_number) → room_type, number_of_beds, price_per_night, available

Reservation
reservation_id → start_date, end_date, reserv_date, status, user_id (customer), manager_id, room_number, building_id

Payment
p_id → p_method, amount, p_date, reservation_id

Service
service_id → service_type, service_date, service_status, staff_id, room_number, building_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}

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

Од анализата се забележуваат:

room_number е уникатен само во комбинација со building_id → слаб ентитет.

Во Reservation нема експлицитно модел на врска со Customer, Manager и Room → треба релации за интегритет.

Во Payment, amount е транзитивно зависно од Reservation (amount = цена * број на ноќи).

Во Service, моментално нема детална историја (само статус).

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

Тековниот дизајн е во 1NF: атомски атрибути, нема повеќевредносни атрибути складирани како едно поле.

==== 2 НФ

Нема парцијални зависности (освен кај Room, каде клуч е составен (building_id, room_number)).

==== 3 НФ

Проблеми:

Reservation – amount (треба да се изведува од Room.price_per_night × број на ноќи → не треба да стои во Payment како атрибут).

Reservation моментално чува и customer_id, и manager_id → транзитивна зависност преку User.

Подобрувања за да се задоволи 3 НФ
Reservation – Order Cost

Да не се чува amount во Payment, туку да се пресметува.

{{{#!sql
ALTER TABLE Payment DROP COLUMN amount;
}}}
Да се додава VIEW:
{{{#!sql
CREATE VIEW reservation_total AS
SELECT r.reservation_id,
       (EXTRACT(DAY FROM r.end_date - r.start_date) * rm.price_per_night) AS total_cost
FROM Reservation r
JOIN Room rm ON r.room_number = rm.room_number AND r.building_id = rm.building_id;
}}}
Service – историја на статуси
{{{#!sql
CREATE TABLE service_history (
    history_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    service_id BIGINT NOT NULL,
    status VARCHAR(70) NOT NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (service_id) REFERENCES Service(service_id)
);
}}}
==== BCNF
Прекршоци

email во Hotel_User треба да е уникатен → веќе има UNIQUE.

phone исто така треба да е уникатен (еден број = еден корисник).

Во Room, комбинацијата (building_id, room_number) е единствен идентификатор.
{{{#!sql
ALTER TABLE Hotel_User ADD CONSTRAINT uq_phone UNIQUE(phone);

ALTER TABLE Room ADD CONSTRAINT uq_room UNIQUE(building_id, room_number);
}}}