= Logical and Physical Design, DB Creation (DDL)

== Notation

 * Primary Key - '''bold''' and __underline__
 * Required/`NOT NULL` - '''bold'''
 * Foreign Key - Asterisk ( * )
 * Optional (nullable) attributes - regular characters

== Tables
USERS (__'''user_id'''__, '''username''', '''first_name''', '''last_name''', '''password''', '''email''')

ADMINS (__'''user_id'''__*)

PET_OWNERS (__'''user_id'''__*)

PET_SITTERS (__'''user_id'''__*)

PET_TYPES (__'''pettype_id'''__, '''species''', average_lifespan, '''needs_outdoor_walk''')

PETS (__'''pet_id'''__, '''name''', photo, '''age''', special_needs, description, '''owner_id'''*, '''pettype_id'''*)

SERVICES (__'''service_id'''__, '''type''', description)

BOOKINGS (__'''booking_id'''__, '''status''', '''date_from''', '''date_to''', '''address''', '''owner_id'''*, '''sitter_id'''*, '''service_id'''*)

REVIEWS (__'''review_id'''__, '''rating''', comment, '''booking_id'''*)

PAYMENTS (__'''payment_id'''__, '''amount''', '''payment_type''', '''booking_id'''*)

ADMIN_MANAGEMENT (__'''admin_id'''__*, __'''user_id'''__*)

BOOKING_PETS (__'''booking_id'''__*, __'''pet_id'''__*)

SITTER_SERVICES (__'''sitter_id'''__*, __'''service_id'''__*)

== DDL script for creating the database schema and objects:
[attachment:schema_creation.sql]

== DML script for filling tables with data:
[attachment:data_load.sql]

== Relational diagram
[[Image(relational_schema.png)]]

== Version history

[https://develop.finki.ukim.mk/projects/petsitter/wiki/RelationalDesign_v01 v01]
* Initial version

[https://develop.finki.ukim.mk/projects/petsitter/wiki/RelationalDesign v02]
* Current version; based on version [https://develop.finki.ukim.mk/projects/petsitter/wiki/ERModel v03] of the ER model
* Added the '''service_id''' foreign key to the '''BOOKINGS''' table, declared as `NOT NULL` since every booking requires exactly one service
* Removed the '''BOOKING_SERVICES''' table; not needed because '''requires''' relation is N:1
* Updated `schema_creation.sql` and `data_load.sql` accordingly