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

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 исто така важи.