Changes between Initial Version and Version 1 of BuildBoardF6

Timestamp:

07/07/25 23:02:20 (4 months ago)

Author:

221071

Comment:

--

BuildBoardF6

@@ +1 @@
+== Нормализација
+
+За табелите од нашата база на податоци, со цел разликување на самите 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 }
+
+Лево 
+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