Changes between Version 1 and Version 2 of Normalization

Timestamp:

02/01/26 22:45:29 (3 weeks ago)

Author:

213257

Comment:

--

Normalization

@@ -24 +24 @@
 
 
-FD1:  admin_id → admin_name, admin_email, admin_password
+FD1:  admin_id -> admin_name, admin_email, admin_password
 
 FD2:  building_id -> building_name, building_address, building_description, admin_id
@@ -44 +44 @@
 FD10: appointment_id -> appointment_status, client_id, unit_id, timeslot_id
 
+
+== 1NF decomposition
+
+'''Relation:''' PROPERTY_MANAGEMENT_SYSTEM
+
+'''Functional dependencies in this relation:''' FD1-FD10
+
+'''Candidate Keys:''' 
+* {appointment_id, architect_id}
+
+'''Primary Key:''' {appointment_id, architect_id}
+
+'''Normal Form Status:''' Relation is in 1NF, all attributes are atomic, there are no repeating groups and we have a primary key
+
+'''Issues:''' Due to the existance of partial dependencies this relation does not conform to 2NF. In FD1 the admin_id is not part of key but determines attributes, in FD2 the building_id is not part of key, in FD3 the architect_id is part of key but determines attributes independently, in FD4-FD10 all have determinants that are not the full composite key.
+
+'''Decomposition strategy:''' Each entity is extracted into its own relation based on defined functional dependencies, starting with entities that have single-attribute determinants.
+
+'''Decomposition:'''
+
+{{{
+R1_ADMIN(admin_id, admin_name, admin_email, admin_password)
+    PK: admin_id, FD: FD1
+
+R2_BUILDING(building_id, building_name, building_address, building_description, admin_id)
+    PK: building_id, FK: admin_id, FD: FD2
+
+R3_ARCHITECT(architect_id, architect_full_name, architect_contact_info)
+    PK: architect_id, FD: FD3
+
+R4_DESIGNS(building_id, architect_id)
+    PK: {building_id, architect_id}, FK: building_id, architect_id
+
+R5_FLOOR(floor_id, floor_number, floor_layout_image, building_id)
+    PK: floor_id, FK: building_id, FD: FD4
+
+R6_UNIT(unit_id, unit_number, unit_room_number, unit_floor_area, unit_status, 
+        unit_price, unit_image, unit_floorplan, unit_vector_image, floor_id)
+    PK: unit_id, FK: floor_id, FD: FD5
+
+R7_AGENT(agent_id, agent_name, agent_email, agent_phone)
+    PK: agent_id, FD: FD6
+
+R8_TIMESLOT(timeslot_id, timeslot_date, timeslot_time_start, timeslot_time_end, 
+            timeslot_status, agent_id)
+    PK: timeslot_id, FK: agent_id, FD: FD7
+
+R9_CLIENT(client_id, client_name, client_email, client_phone)
+    PK: client_id, Candidate Key: client_email, FD: FD8, FD9
+
+R10_APPOINTMENT(appointment_id, appointment_status, client_id, unit_id, timeslot_id)
+    PK: appointment_id, FK: client_id, unit_id, timeslot_id, FD: FD10
+}}}
+
+'''Preservation of functional dependencies:''' All FD1-FD10 preserved.
+
+'''Lossless join:''' Foreign keys enable the reconstruction through natural joins.
+
+== 2NF decomposition
+
+'''Relations to analyze:''' R1_ADMIN - R10_APPOINTMENT (All 1NF relations)
+
+'''Analysis:'''
+
+All relations from the 1NF decomposition have single attribute primary keys, only exception is R4_DESIGNS which contains only key attributes. 
+
+'''Relation R1_ADMIN:'''
+* Primary Key: admin_id
+* Functional Dependencies: admin_id -> admin_name, admin_email, admin_password
+* Candidate Keys: {admin_id}
+* Normal Form Status: In 2NF
+
+'''Relation R2_BUILDING:'''
+* Primary Key: building_id
+* Functional Dependencies: building_id -> building_name, building_address, building_description, admin_id
+* Candidate Keys: {building_id}
+* Normal Form Status: In 2NF
+
+'''Relation R3_ARCHITECT:'''
+* Primary Key: architect_id
+* Functional Dependencies: architect_id -> architect_full_name, architect_contact_info
+* Candidate Keys: {architect_id}
+* Normal Form Status: In 2NF
+
+'''Relation R4_DESIGNS:'''
+* Primary Key: {building_id, architect_id}
+* Functional Dependencies: None
+* Normal Form Status: In 2NF
+
+'''Relation R5_FLOOR:'''
+* Primary Key: floor_id
+* Functional Dependencies: floor_id -> floor_number, floor_layout_image, building_id
+* Candidate Keys: {floor_id}
+* Normal Form Status: In 2NF
+
+'''Relation R6_UNIT:'''
+* Primary Key: unit_id
+* Functional Dependencies: unit_id -> unit_number, unit_room_number, unit_floor_area, unit_status, unit_price, unit_image, unit_floorplan, unit_vector_image, floor_id
+* Candidate Keys: {unit_id}
+* Normal Form Status: In 2NF
+
+'''Relation R7_AGENT:'''
+* Primary Key: agent_id
+* Functional Dependencies: agent_id -> agent_name, agent_email, agent_phone
+* Candidate Keys: {agent_id}
+* Normal Form Status: In 2NF
+
+'''Relation R8_TIMESLOT:'''
+* Primary Key: timeslot_id
+* Functional Dependencies: timeslot_id -> timeslot_date, timeslot_time_start, timeslot_time_end, timeslot_status, agent_id
+* Candidate Keys: {timeslot_id}
+* Normal Form Status: In 2NF
+
+'''Relation R9_CLIENT:'''
+* Primary Key: client_id
+* Functional Dependencies: client_id -> client_name, client_email, client_phone; client_email -> client_id
+* Candidate Keys: {client_id}, {client_email}
+* Normal Form Status: In 2NF
+
+'''Relation R10_APPOINTMENT:'''
+* Primary Key: appointment_id
+* Functional Dependencies: appointment_id -> appointment_status, client_id, unit_id, timeslot_id
+* Candidate Keys: {appointment_id}
+* Normal Form Status: In 2NF
+
+'''Issues:''' There are no issues, all relations conform to 2NF.
+
+'''Decomposition:''' There is no need for decomposition.
+
+'''Preservation of functional dependencies:''' All FD1-FD10 preserved.
+
+'''Lossless join:''' Foreign keys enable the reconstruction through natural joins.
+