= Relational Design =

==== Notation

* Primary keys - underlined and bold
* NOT NULL attributes - bold
* Foreign keys - marked with a * after the foreign key name
* Other attributes - no marking

==== Tables

- ADMIN ('''__admin_id__''', '''name''', '''email''', '''password''')

- ARCHITECT ('''__architect_id__''', '''full_name''')

- BUILDING ('''__building_id__''', '''name''', '''address''', description, '''admin_id*''')

- DESIGNS ('''__architect_id*__''', '''__building_id*__''')

- FLOOR ('''__floor_id__''', '''floor_number''', '''layout_image''', '''building_id*''', '''admin_id*''')

- UNIT ('''__unit_id__''', '''unit_number''', '''room_number''', '''floor_area''', '''status''', '''price''', image, floorplan, '''vector_image''', '''floor_id*''', '''admin_id*''')

- AGENT ('''__agent_id__''', '''name''', '''email''', '''password''')

- CLIENT ('''__client_id__''', '''name''', '''email''', '''phone''')

- TIMESLOT ('''__timeslot_id__''', '''date''', '''time_start''', '''time_end''', '''status''', '''agent_id*''')

- INQUIRY ('''__inquiry_id__''', '''message''', '''status''', '''created_at''', '''unit_id*''', '''client_id*''' , '''agent_id*''', '''response''', '''responded_at''')

- APPOINTMENT ('''__appointment_id__''', '''status''', '''client_id*''', '''unit_id*''', '''timeslot_id*''', '''agent_id*''')


===== After Normalization from Phase 5 - Tables

- ADMIN ('''__admin_id__''', '''name''', '''email''', '''password''')

- ARCHITECT ('''__architect_id__''', '''full_name''')

- BUILDING ('''__building_id__''', '''name''', '''address''', description, '''admin_id*''')

- DESIGNS ('''__architect_id*__''', '''__building_id*__''')

- FLOOR ('''__floor_id__''', '''floor_number''', '''layout_image''', '''building_id*''')

- UNIT ('''__unit_id__''', '''unit_number''', '''room_number''', '''floor_area''', '''status''', '''price''', image, floorplan, '''vector_image''', '''floor_id*''')

- AGENT ('''__agent_id__''', '''name''', '''email''', '''password''')

- CLIENT ('''__client_id__''', '''name''', '''email''', '''phone''')

- TIMESLOT ('''__timeslot_id__''', '''date''', '''time_start''', '''time_end''', '''status''', '''agent_id*''')

- INQUIRY ('''__inquiry_id__''', '''message''', '''status''', '''created_at''', '''unit_id*''', '''client_id*''' , '''agent_id*''', '''response''', '''responded_at''')

- APPOINTMENT ('''__appointment_id__''', '''status''', '''client_id*''', '''unit_id*''', '''timeslot_id*''')

=== DDL script for creating the database schema and objects

[[https://develop.finki.ukim.mk/projects/C307/attachment/wiki/RelationalDesign/schema_creation_v3.sql|DDL Script Version 3]]

After Phase 5 - Normalization

[[https://develop.finki.ukim.mk/projects/C307/attachment/wiki/RelationalDesign/schema_creation_v4.sql|DDL Script Version 4]]

=== DML script for filling tables with data

[[https://develop.finki.ukim.mk/projects/C307/attachment/wiki/RelationalDesign/data_load.sql|DML Script]]

After Phase 5 - Normalization

[[https://develop.finki.ukim.mk/projects/C307/attachment/wiki/RelationalDesign/data_load_v2.sql|DML Script Version 2]]


=== Relational Diagram

[[Image(https://develop.finki.ukim.mk/projects/C307/raw-attachment/wiki/RelationalDesign/RelationalDiagram_v2.png)]]

= Relational Design History =

* Version 01 
 * Initial version
* Version 02
 * Updated schema_creation, data_load and relational model files to reflect post normalization state
* Version 03
 * Expanded Inquiry to include response and responded_at attributes.
 * Updated the schema_creation scripts to reflect above mentioned changes.