Changes between Initial Version and Version 1 of Normalization

Timestamp:

09/15/25 00:45:56 (6 weeks ago)

Author:

215010

Comment:

--

Normalization

@@ +1 @@
+== Определување функционални зависности
+
+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, customer_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), manager_id, staff_id, customer_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}
+
+Следува дека { user_id, building_id, room_number, reservation_id, payment_id, service_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);