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*)

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*)

BOOKING_SERVICES (booking_id*, service_id*)

DDL script for creating the database schema and objects:

schema_creation.sql​

DML script for filling tables with data:

data_load.sql​

Relational diagram