| Version 5 (modified by , 5 days ago) ( diff ) |
|---|
Нормализација
За табелите од нашата база на податоци, со цел разликување на самите 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НФ
За да ги отстраниме парцијалните зависности потребно е да ги оделиме атрибутите во релации во кои целосно ќе зависат од нивниот клуч па тоа би го сториле на следен начин:
- property_image_id → property_image, property_id
- payment_id → amount, payment_status, payment_date, lease_id, payment_method_id
- 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
- message_id → sent_at, content, user_id, lease_id
- unit_image_id → unit_image, unit_id
- 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} - во BCNF
R2 {service_category_id, service_category_name} - во BCNF
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
