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

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}

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

Од функционалните зависности и шемата, можеме да идентификуваме дека релацијата R вклучува различни ентитети кои се поврзани преку надворешни клучеви и делумни зависности. За да го идентификуваме кандидатскиот примарен клуч за целата релација R, мора да се осигураме дека: сите не-примарни атрибути се функционално зависни од кандидатскиот примарен клуч и кандидатскиот клуч мора да биде уникатен низ целата релација.

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

{reservation_id, p_id, service_id, room_number, building_id}

• reservation_id: Уникатно идентификува резервација.

• p_id: Уникатно идентификува плаќање.

• service_id: Уникатно идентификува услуга.

• room_number и building_id: Заедно, тие идентификуваат одредена соба во одредена зграда.

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

Анализа на нормални форми

1 НФ

За да постигнеме 1NF, мора да се осигураме дека сите атрибути во секоја торка се атомски, што значи дека не постојат повеќевредносни атрибути или композитни атрибути.

Проблем во тековната шема:

Композитни атрибути како address (кои може да имаат повеќе компоненти како што се улица, град итн.).

Решение:

Разделување на повеќевредносните атрибути во посебни колони.

Применето 1NF:

Адресата во Hotel_Building треба да се подели на атомски компоненти: улица, град и поштенски код.

Табела Hotel_Building:

building_id, street, city, zip_code, floors, num_rooms

Сите атрибути во секоја табела ќе бидат атомски, што значи дека нема да постојат дополнителни сложени или повеќевредносни атрибути.

2 НФ

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

Проблем во тековната шема:

Во Reservation, атрибутите како user_id, manager_id и room_number се делумно зависни само од дел од примарниот клуч (т.е. reservation_id), а не од целиот примарен клуч.

Решение:

Преместување на атрибутите што зависат од дел од примарниот клуч во посебни табели за да ги елиминирате делумните зависности.

Применето 2NF:

Креирање табела Customer со user_id како примарен клуч и атрибути поврзани со клиентот.

Креирање табела Manager со user_id како примарен клуч и атрибути поврзани со менаџерот.

Изменување на Reservation за да ги чува само атрибутите што зависат од reservation_id и преместување на user_id (клиент) и manager_id во нивните соодветни табели.

По 2NF:

Customer: user_id, first_name, last_name, phone, email, password

Manager: user_id, first_name, last_name, phone, email, password

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

3 НФ

За да постигнеме 3NF, мора да ги отстраниме транзитивните зависности. Непримарниот атрибут не смее да зависи од друг непримарен атрибут.

Проблеми во тековната шема:

Во Payment, user_id индиректно одредува атрибути како first_name и last_name, создавајќи транзитивна зависност.

Решение:

Отстранување на транзитивните зависности со креирање посебни табели за User (веќе направено во 2NF) и осигурувајќи се дека секоја табела ги складира само атрибутите што директно зависат од примарниот клуч.

Применето 3NF:

Во табелата Payment, складираме само p_id, p_method, amount, p_date и reservation_id. До user_id може да се пристапи преку reservation_id, па затоа не треба да се чува во табелата Payment.

По 3NF:

Payment: p_id, p_method, amount, p_date, reservation_id

BCNF

За да се постигне BCNF, мора да се осигураме дека секоја детерминанта во релацијата е клуч-кандидат, односно За секоја функционална зависност: A→B, A мора да биде клуч-кандидат.

Проблем во тековната шема:

Во табелата Service, функционалната зависност:

service_id → service_type, service_date, service_status, staff_id, room_number, building_id

Оваа функционална зависност покажува дека service_id ги одредува service_type, service_date и service_status, но room_number и building_id не се дел од клучот-кандидат. Така, room_number и building_id се не-примарни атрибути кои зависат од service_id, создавајќи потенцијално прекршување на BCNF.

Решение за BCNF:

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

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

Треба да ја отстраниме зависноста од room_number и building_id од табелата Service бидејќи овие атрибути се веќе одредени од табелата Room.

По примена на BCNF:

Табелата Service ќе ги чува само:

service_id, service_type, service_date, service_status и staff_id.

Табелата Room ќе продолжи да ги чува деталите поврзани со собата, вклучувајќи го room_number и building_id.

Со ова, сите не-примарни атрибути во табелата Service зависат единствено од клучот-кандидат service_id.

Конечни табели во BCNF:

По примената на BCNF и решавањето на сите проблеми, конечната шема за засегнатите табели изгледа вака:

Hotel_Bidling: building_id, street, city, zip_code, floors, num_rooms

Room: room_number, building_id, room_type, number_of_beds, price_per_night, available

Customer: user_id, first_name, last_name, phone, e-mail, password

Manager: user_id, first_name, last_name, phone, e-mail, password

Reservation: reservation_id, start_date, end_date, reserv_date, status, customer_id, 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