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

За табелите од нашата база на податоци, со цел разликување на самите 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
property_id -> property_title, property_description, created_at, user_id, property_type_id, address_id
property_type_id -> property_type_name
property_image_id -> property_image, property_id
unit_id -> unit_number, floor, bedrooms, bathrooms, area_sq_m, unit_rent_amount, property_id
listing_id -> listing_title, available_from, available_to, listing_status, listing_description, unit_id
lease_id -> start_date, end_date, lease_rent_amount, deposit_amount, listing_id, tenant_profile_id, landlord_profile_id
payment_id -> amount, payment_status, payment_date, lease_id, payment_method_id
payment_method_id -> payment_method_name
landlord_profile_id -> managed_properties_count, is_agent
service_request_id -> service_description, request_date, service_status, lease_id, service_category_id
service_category_id -> service_category_name
maintenance_log_id -> maintenance_description, maintenance_date, service_request_id
inspection_id -> inspection_date, notes, lease_id, landlord_profile_id
document_id -> file_type, file_url, uploaded_at, user_id, lease_id
address_id -> street, municipality, city, number, country
message_id -> sent_at, content, user_id, user_id, lease_id
unit_image_id -> unit_image, unit_id
tenant_profile_id -> /
listing_id, tenant_profile_id -> /

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 }

Лево property_image_id payment_id maintenance_log_id inspection_id document_id message_id unit_image_id tenant_profile_id

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

Десно 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