== Нормализација

За табелите од нашата база на податоци, со цел разликување на самите id за секој ентитет, ќе ги преименуваме id атрибутите во <име_на_релација>_id. Со тоа добиваме:
user_id, property_id, property_type_id, property_image_id, unit_id, listing_id, lease_id, payment_id, payment_method_id, tenant_profile_id, landlord_profile_id, service_request_id, service_category_id, maintenance_log_id, inspection_id, document_id, address_id, message_id, unit_image_id

**user_id** -> first_name, last_name, email, password_hash, date_of_birth, rating, address_id, bio [[br]]
**property_id** -> property_title, property_description, created_at, user_id, property_type_id, address_id [[br]]
**property_type_id** -> property_type_name [[br]]
**property_image_id** -> property_image, property_id [[br]]
**unit_id** -> unit_number, floor, bedrooms, bathrooms, area_sq_m, unit_rent_amount, property_id [[br]]
**listing_id** -> listing_title, available_from, available_to, listing_status, listing_description, unit_id [[br]]
**lease_id** -> start_date, end_date, lease_rent_amount, deposit_amount, listing_id, tenant_profile_id, landlord_profile_id [[br]]
**payment_id** -> amount, payment_status, payment_date, lease_id, payment_method_id [[br]]
**payment_method_id** -> payment_method_name [[br]]
**landlord_profile_id** -> managed_properties_count, is_agent [[br]]
**service_request_id** -> service_description, request_date, service_status, lease_id, service_category_id [[br]]
**service_category_id** -> service_category_name [[br]]
**maintenance_log_id** -> maintenance_description, maintenance_date, service_request_id [[br]]
**inspection_id** -> inspection_date, notes, lease_id, landlord_profile_id [[br]]
**document_id** -> file_type, file_url, uploaded_at, user_id, lease_id [[br]]
**address_id** -> street, municipality, city, number, country [[br]]
**message_id** -> sent_at, content, user_id, user_id, lease_id [[br]]
**unit_image_id** -> unit_image, unit_id [[br]]
**tenant_profile_id** -> / [[br]]
**listing_id, tenant_profile_id** -> / [[br]]

user_id се спомнува два пати кај ФЗ со message_id од левата страна поради тоа што имаме испраќач и примач на порака.

**R** = { user_id, first_name, last_name, email, password_hash, date_of_birth, rating, address_id, bio, tenant_profile_id, property_id, property_title, property_description, created_at, property_type_id, property_type_name, property_image_id, property_image, unit_id, unit_number, floor, bedrooms, bathrooms, area_sq_m, unit_rent_amount, unit_image_id, unit_image, listing_id, listing_title, available_from, available_to, listing_status, listing_description, lease_id, start_date, end_date, lease_rent_amount, deposit_amount, landlord_profile_id, managed_properties_count, is_agent, payment_id, amount, payment_status, payment_date, payment_method_id, payment_method_name, service_request_id, service_description, request_date, service_status, service_category_id, service_category_name, maintenance_log_id, maintenance_description, maintenance_date, inspection_id, inspection_date, notes, document_id, file_type, file_url, uploaded_at, street, municipality, city, number, country, message_id, sent_at, content }

**Лево** [[br]]
property_image_id [[br]]
payment_id [[br]]
maintenance_log_id [[br]]
inspection_id [[br]]
document_id [[br]]
message_id [[br]]
unit_image_id [[br]]
tenant_profile_id [[br]]

**Лево и десно** [[br]]
user_id [[br]]
property_id [[br]]
property_type_id [[br]]
unit_id [[br]]
listing_id [[br]]
lease_id [[br]]
landlord_profile_id [[br]]
payment_method_id [[br]]
service_request_id [[br]]
service_category_id [[br]]
address_id [[br]]



**Десно** [[br]]
first_name, last_name, email, password_hash, date_of_birth, rating, bio, property_title, property_description, created_at, property_type_name, property_image, unit_number, floor, bedrooms, bathrooms, area_sq_m, unit_rent_amount, listing_title, available_from, available_to, listing_status, listing_description, start_date, end_date, lease_rent_amount, deposit_amount, amount, payment_status, payment_date, payment_method_name, managed_properties_count, is_agent, service_description, request_date, service_status, service_category_name, maintenance_description, maintenance_date, inspection_date, notes, file_type, file_url, uploaded_at, street, municipality, city, number, country, sent_at, content, unit_image


=== Покривачи за 8-те атрибути:
1.** property_image_id+** = {property_image_id, property_image, property_id, property_title, property_description, created_at, user_id, property_type_id, address_id, property_type_name, street, municipality, city, number, country, first_name, last_name, email, password_hash, date_of_birth, rating, bio} **-Не ги содржи сите атрибути.** 

2.** payment_id+** = {payment_id, amount, payment_status, payment_date, lease_id,payment_method_id, payment_method_name, start_date, end_date, lease_rent_amount,
deposit_amount, listing_id, tenant_profile_id, landlord_profile_id, listing_title, available_from,
available_tod, listing_status, listing_description, unit_id, unit_number, floor, bedrooms,
bathrooms, area_sq_m, unit_rent_amount, property_id, managed_properties_count, is_agent,
user_id, property_title, property_description, created_at, property_type_id, address_id,
property_type_name, street, municipality, city, number, country, first_name, last_name, email,
password_hash, date_of_birth, rating, bio} **-Не ги содржи сите атрибути.** 

3.** maintenance_log_id+** = {maintenance_log_id, maintenance_description,maintenance_date,service_request_id, service_description, request_date, service_status, lease_id,
service_category_id, service_category_name, start_date, end_date, lease_rent_amount,
deposit_amount, listing_id, tenant_profile_id, landlord_profile_id, managed_properties_count,
is_agent, user_id, first_name, last_name, email, password_hash, date_of_birth, rating,
address_id, bio, street, municipality, city, number, country, listing_title, available_from,
available_to, listing_status, listing_description, unit_id, unit_number, floor, bedrooms,
bathrooms, area_sq_m, unit_rent_amount, property_id, property_title, property_description,
created_at, property_type_id, property_type_name} **-Не ги содржи сите атрибути.** 

4.** inspection_id+** = {inspection_id, inspection_date, notes, lease_id, landlord_profile_id,
managed_properties_count, is_agent, start_date, end_date, lease_rent_amount,
deposit_amount, listing_id, tenant_profile_id, user_id, first_name, last_name, email,
password_hash, date_of_birth, rating, address_id, bio, street, municipality, city, number, country,
listing_title, available_from, available_to, listing_status, listing_description, unit_id, unit_number,
floor, bedrooms, bathrooms, area_sq_m, unit_rent_amount, property_id, property_title,
property_description, created_at, property_type_id, property_type_name} **-Не ги содржи сите
атрибути. **

5.**document_id+** = {document_id, file_type, file_url, uploaded_at, user_id, lease_id,
first_name, last_name, email, password_hash, date_of_birth, rating, address_id, bio, street,
municipality, city, number, country, start_date, end_date, lease_rent_amount, deposit_amount,
listing_id, tenant_profile_id, landlord_profile_id, managed_properties_count, is_agent,
listing_title, available_from, available_to, listing_status, listing_description, unit_id, unit_number,
floor, bedrooms, bathrooms, area_sq_m, unit_rent_amount, property_id, property_title,
property_description, created_at, property_type_id, property_type_name} **-Не ги содржи сите
атрибути.**

6.** message_id+** = {message_id, sent_at, content, user_id, lease_id, first_name, last_name,
email, password_hash, date_of_birth, rating, address_id, bio, street, municipality, city, number,
country, start_date, end_date, lease_rent_amount, deposit_amount, listing_id, tenant_profile_id,
landlord_profile_id, managed_properties_count, is_agent, listing_title, available_from,
available_to, listing_status, listing_description, unit_id, unit_number, floor, bedrooms,
bathrooms, area_sq_m, unit_rent_amount, property_id, property_title, property_description,
created_at, property_type_id, property_type_name} **-Не ги содржи сите атрибути.**

7.** unit_image_id+** = {unit_image_id, unit_image, unit_id, unit_number, floor, bedrooms,
bathrooms, area_sq_m, unit_rent_amount, property_id, property_title, property_description,
created_at, user_id, property_type_id, address_id, first_name, last_name, email,
password_hash, date_of_birth, rating, bio, street, municipality, city, number, country,
property_type_name}**-Не ги содржи сите атрибути.**

8.** tenant_profile_id+** = {tenant_profile_id, user_id, first_name, last_name, email,
password_hash, date_of_birth, rating, address_id, bio, street, municipality, city, number,
country} **-Не ги содржи сите атрибути.**
 

==== Започнуваме со комбинирање на покривачите за да дојдеме до сите атрибути:
1.** {property_image_id, payment_id}+** = {property_image_id, property_image, property_id,
property_title, property_description, created_at, user_id, property_type_id, address_id,
property_type_name, street, municipality, city, number, country, first_name, last_name, email,
password_hash, date_of_birth, rating, bio, payment_id, amount, payment_status,
payment_date, lease_id, payment_method_id, payment_method_name, start_date, end_date,
lease_rent_amount, deposit_amount, listing_id, tenant_profile_id, landlord_profile_id,
listing_title, available_from, available_to, listing_status, listing_description, unit_id, unit_number,
floor, bedrooms, bathrooms, area_sq_m, unit_rent_amount, managed_properties_count,
is_agent} **-Не ги содржи сите атрибути.**

2.** {property_image_id, payment_id, maintenance_log_id}+** = {property_image_id,
property_image, property_id, property_title, property_description, created_at, user_id,
property_type_id, address_id, property_type_name, street, municipality, city, number, country,
first_name, last_name, email, password_hash, date_of_birth, rating, bio, payment_id, amount,
payment_status, payment_date, lease_id, payment_method_id, payment_method_name,
start_date, end_date, lease_rent_amount, deposit_amount, listing_id, tenant_profile_id,
landlord_profile_id, managed_properties_count, is_agent, listing_title, available_from,
available_to, listing_status, listing_description, unit_id, unit_number, floor, bedrooms,
bathrooms, area_sq_m, unit_rent_amount, maintenance_log_id, maintenance_description,
maintenance_date, service_request_id, service_description, request_date, service_status,
service_category_id, service_category_name} **-Не ги содржи сите атрибути. **

3.** {property_image_id, payment_id, maintenance_log_id, inspection_id}+** = Со ова на
претходните се додаваат: inspection_id, inspection_date, notes **-Не ги содржи сите
атрибути.**

4.** {property_image_id, payment_id, maintenance_log_id, inspection_id, document_id}+**= Со
ова на претходните се додаваат: document_id, file_type, file_url, uploaded_at -Не ги
содржи сите атрибути.

5.** {property_image_id, payment_id, maintenance_log_id, inspection_id, document_id,
message_id}+** = Со ова на претходните се додаваат: message_id, sent_at, content ** -Не ги
содржи сите атрибути.**

6.** {property_image_id, payment_id, maintenance_log_id, inspection_id, document_id,
message_id, unit_image_id}+**= Со ова на претходните се додаваат: message_id, sent_at,
content unit_image_id, unit_image **- Ги содржи сите атрибути!**

Во овааа релација нема повеќекратни зависности и истата ја задоволува 1НФ.
Оваа релација не задоволува 2НФ поради постоење на парцијални зависности.
== Декомпозиција во 2НФ
За да ги отстраниме парцијалните зависности потребно е да ги оделиме атрибутите во
релации во кои целосно ќе зависат од нивниот клуч па тоа би го сториле на следен
начин:
1. property_image_id → property_image, property_id

2. payment_id → amount, payment_status, payment_date, lease_id, payment_method_id

3. maintenance_log_id → maintenance_description, maintenance_date,service_request_id

4. inspection_id → inspection_date, notes, lease_id, landlord_profile_id

5. document_id → file_type, file_url, uploaded_at, user_id, lease_id

6. message_id → sent_at, content, user_id, lease_id

7. unit_image_id → unit_image, unit_id

8. tenant_profile_id → user_id
== Декомпозиција во релации
Овдека првин ќе ги извдоиме самите релации од погоре, но за самата декомпозиција да
може да се изведе подоцна, ќе ги издвоиме и релациите со примарни клучеви:
payment_method_id, service_category_id и service_request_id, односно релациите R1, R2 и
R5. Со ваква организација ќе ги тргнеме атрибутите пред самите клучеви кои ќе бидат
отстранети подоцна во релациите R5 (за service_category_id), R6 (за service_request_id) и
R4 (за payment_method_id). Конкретно, релациите ќе ги издвоиме поради следните
транзитивни зависности со кои не постигнуваме 3НФ:
*  payment_method_id → payment_method_name
*  service_category_id → service_category_name
*  service_request_id → service_description, request_date, service_status, lease_id, service_category_id
**R1 {payment_method_id, payment_method_name}**

**R2 {service_category_id, service_category_name}**

**R3 {property_image_id, property_image, property_id} - во BCNF

**R4 {payment_id, amount, payment_status, payment_date, lease_id, payment_method_id}-во BCNF

**R5 {service_request_id, service_description, request_date, service_status, lease_id,
service_category_id }

**R6 {maintenance_log_id, maintenance_description, maintenance_date,
service_request_id} - во BCNF

**R7 {inspection_id, inspection_date, notes, lease_id, landlord_profile_id} - во BCNF

**R8 {document_id, file_type, file_url, uploaded_at, user_id, lease_id} - во BCNF

**R9 {message_id, sent_at, content, user_id, lease_id} - во BCNF

**R10 {unit_image_id, unit_image, unit_id} - во BCNF

**R11 {tenant_profile_id, user_id} - во BCNF

**R12** - остатокот од атрибутите: {property_image_id, payment_id, maintenance_log_id,
inspection_id, document_id, message_id, unit_image_id, tenant_profile_id, property_id,
property_type_id, unit_id, listing_id, lease_id, landlord_profile_id, address_id, user_id,
property_title, property_description, created_at, property_type_name, unit_number, floor,
bedrooms, bathrooms, area_sq_m, unit_rent_amount, listing_title, available_from, available_to,
listing_status, listing_description, start_date, end_date, lease_rent_amount, deposit_amount,
managed_properties_count, is_agent, street, municipality, city, number, country, first_name,
last_name, email, password_hash, date_of_birth, rating, bio}

=== Проверка на 3НФ за R12
R12 не е во 3НФ поради следните транзитивни зависности:

**Транзитивна зависност : property_id → user_id, property_type_id, address_id,
property_title, property_description, created_at

**R12.1** {property_id, user_id, property_type_id, address_id, property_title, property_description,
created_at} - не е во 3НФ
R12.1 не е во 3НФ поради:
• property_type_id → property_type_name
• address_id → street, municipality, city, number, country
• user_id → first_name, last_name, email, password_hash, date_of_birth, rating, bio

**R12.1.1 {property_type_id, property_type_name} - во BCNF

**R12.1.2 {address_id, street, municipality, city, number, country} - во BCNF

**R12.1.3 {user_id, first_name, last_name, email, password_hash, date_of_birth, rating,
bio} - во BCNF

**R12.1.4 {property_id, user_id, property_type_id, address_id, property_title,
property_description, created_at} - во BCNF

Во R12.2 остануваат сите атрибути кои ги нема во претходните релации.

**R12.2** - {property_image_id, payment_id, maintenance_log_id, inspection_id, document_id,
message_id, unit_image_id, tenant_profile_id, property_id, unit_id, listing_id, lease_id,
landlord_profile_id, unit_number, floor, bedrooms, bathrooms, area_sq_m, unit_rent_amount,
listing_title, available_from, available_to, listing_status, listing_description, start_date, end_date,
lease_rent_amount, deposit_amount, managed_properties_count, is_agent}.
--------------------------------------------------------------------------------------------------
R12.2 не е во 3НФ бидејќи unit_id → unit_number, floor, bedrooms, bathrooms, area_sq_m,
unit_rent_amount, property_id.

**R12.2.1 {unit_id, unit_number, floor, bedrooms, bathrooms, area_sq_m,**
**unit_rent_amount, property_id} - во BCNF**

R12.2.2 {property_image_id, payment_id, maintenance_log_id, inspection_id, document_id,
message_id, unit_image_id, tenant_profile_id, unit_id, listing_id, lease_id, landlord_profile_id,
listing_title, available_from, available_to, listing_status, listing_description, start_date, end_date,
lease_rent_amount, deposit_amount, managed_properties_count, is_agent}

R12.2.2 не е во 3НФ бидејќи listing_id → listing_title, available_from, available_to,
listing_status, listing_description, unit_id

**R12.2.2.1 {listing_id, listing_title, available_from, available_to, listing_status,
listing_description, unit_id} - во BCNF

**R12.2.2.2** {property_image_id, payment_id, maintenance_log_id, inspection_id, document_id,
message_id, unit_image_id, tenant_profile_id, listing_id, lease_id, landlord_profile_id,
start_date, end_date, lease_rent_amount, deposit_amount, managed_properties_count,
is_agent}

**R12.2.2.2** не е во 3НФ бидејќи landlord_profile_id → managed_properties_count, is_agent

**R12.2.2.2.1 { landlord_profile_id, managed_properties_count, is_agent } - во BCNF

**R12.2.2.2.2** { property_image_id, payment_id, maintenance_log_id, inspection_id, document_id,
message_id, unit_image_id, tenant_profile_id, listing_id, lease_id, landlord_profile_id,
start_date, end_date, lease_rent_amount, deposit_amount}
-------------------------------------------------------
R12.2.2.2.2 не е во 3НФ бидејќи lease_id -> start_date, end_date, lease_rent_amount,
deposit_amount, listing_id, tenant_profile_id, landlord_profile_id

**R12.2.2.2.2.1 {lease_id, start_date, end_date, lease_rent_amount, deposit_amount,
listing_id, tenant_profile_id, landlord_profile_id} - во BCNF

**R12.2.2.2.2.2 { property_image_id, payment_id, maintenance_log_id, inspection_id,
document_id, message_id, unit_image_id} - во BCNF

== Финални релации во BCNF
1.** Property_Image** {property_image_id, property_image, property_id} - во BCNF

2.** Payment** {payment_id, amount, payment_status, payment_date, lease_id,
payment_method_id} - во BCNF

3.** Maintenance_Log** {maintenance_log_id, maintenance_description, maintenance_date,
service_request_id} - во BCNF

4.** Inspection** {inspection_id, inspection_date, notes, lease_id, landlord_profile_id} - во
BCNF

5.** Document** {document_id, file_type, file_url, uploaded_at, user_id, lease_id} - во BCNF

6.** Message** {message_id, sent_at, content, from_user_id, to_user_id, lease_id} - во BCNF

7.** Unit_Image** {unit_image_id, unit_image, unit_id} - во BCNF

8.** Tenant_Profile** {tenant_profile_id, user_id} - во BCNF

9.** Property_Type** {property_type_id, property_type_name} - во BCNF

10.** Address** {address_id, street, municipality, city, number, country} - во BCNF

11** User** {user_id, first_name, last_name, email, password_hash, date_of_birth, rating, bio}- во BCNF

12.** Property** {property_id, user_id, property_type_id, address_id, property_title,
property_description, created_at} - во BCNF

13.** Unit** {unit_id, unit_number, floor, bedrooms, bathrooms, area_sq_m, unit_rent_amount,
property_id} - во BCNF

14.** Listing** {listing_id, listing_title, available_from, available_to, listing_status,
listing_description, unit_id} - во BCNF

15.** Lease** {lease_id, start_date, end_date, lease_rent_amount, deposit_amount, listing_id,
tenant_profile_id, landlord_profile_id} - во BCNF

16.** Landlord_Profile** {landlord_profile_id, managed_properties_count, is_agent} - во BCNF

17.** Payment_Method** {payment_method_id, payment_method_name} - во BCNF

18.** Service_Request** {service_request_id, service_description, request_date,
service_status, lease_id, service_category_id} - во BCNF

19.** Service_Category** {service_category_id, service_category_name} - во BCNF

20.** Main_Relation** {property_image_id, payment_id, maintenance_log_id, inspection_id,
document_id, message_id, unit_image_id} - во BCNF

21.** Interested** {listing_id, tenant_profile_id} - во BCNF