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

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, туку да се пресметува.

ALTER TABLE Payment DROP COLUMN amount;

Да се додава VIEW:

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 – историја на статуси

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) е единствен идентификатор.

ALTER TABLE Hotel_User ADD CONSTRAINT uq_phone UNIQUE(phone);

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