= Normalization

== De-normalized database form ==

'''Unified Relation:'''
{{{
PROPERTY_MANAGMENT_SYSTEM(
    admin_id, admin_name, admin_email, admin_password,
    building_id, building_name, building_address, building_description,
    architect_id, architect_full_name, architect_contact_info,
    floor_id, floor_number, floor_layout_image,
    unit_id, unit_number, unit_room_number, unit_floor_area, unit_status, 
    unit_price, unit_image, unit_floorplan, unit_vector_image,
    agent_id, agent_name, agent_email, agent_phone,
    timeslot_id, timeslot_date, timeslot_time_start, timeslot_time_end, timeslot_status,
    appointment_id, appointment_status,
    client_id, client_name, client_email, client_phone
)
}}}

Note: some attributes were renamed due to having multiple attributes with the same name, e.g. email...

'''Functional dependencies:'''


FD1:  admin_id -> admin_name, admin_email, admin_password

FD2:  building_id -> building_name, building_address, building_description, admin_id

FD3:  architect_id -> architect_full_name, architect_contact_info

FD4:  floor_id -> floor_number, floor_layout_image, building_id

FD5:  unit_id -> unit_number, room_number, floor_area, status, price, image, floorplan, vector_image, floor_id

FD6:  agent_id -> agent_name, agent_email, agent_phone

FD7:  timeslot_id -> date, time_start, time_end, status, agent_id

FD8:  client_id -> client_name, client_email, client_phone

FD9:  client_email -> client_id

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.