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.