Normalization

Initial De-Normalized Relation and Functional Dependencies

Unified De-Normalized Relation

This represents the initial de-normalized relation before any normalization is applied.

R(
user_id, username, user_email, password_hash, fullname, name, surname, user_created_at,
blocked_reason, blocked_at, is_blocked,

animal_id, animal_name, species, breed, sex, date_of_birth, photo_url, owner_id,

listing_id, listing_status, listing_price, listing_description, listing_created_at,

appointment_id, appointment_date_time, appointment_status, appointment_notes,

clinic_id, clinic_name, clinic_address, clinic_location, clinic_city,
clinic_email, clinic_phone,

application_id, application_name, application_email, application_phone,
application_city, application_address, submitted_at, reviewed_at,
reviewed_by, application_status, denial_reason,

healthrecord_id, health_type, health_description, health_date,

review_id, rating, review_comment, review_created_at,
review_updated_at, is_deleted, reviewer_id,
target_user_id, target_clinic_id,

notification_id, notification_type, notification_message,
notification_created_at, is_read,

favorite_user_id, favorite_listing_id
)

Initial Set of Functional Dependencies

User dependencies

user_id → username
user_id → user_email
user_id → password_hash
user_id → fullname
user_id → name
user_id → surname
user_id → user_created_at
user_id → blocked_reason
user_id → blocked_at
user_id → is_blocked

Animal dependencies

animal_id → animal_name
animal_id → species
animal_id → breed
animal_id → sex
animal_id → date_of_birth
animal_id → photo_url
animal_id → owner_id

Listing dependencies

listing_id → listing_status
listing_id → listing_price
listing_id → listing_description
listing_id → listing_created_at
listing_id → animal_id

Appointment dependencies

appointment_id → appointment_date_time
appointment_id → appointment_status
appointment_id → appointment_notes
appointment_id → animal_id
appointment_id → clinic_id

Vet Clinic dependencies

clinic_id → clinic_name
clinic_id → clinic_address
clinic_id → clinic_location
clinic_id → clinic_city
clinic_id → clinic_email
clinic_id → clinic_phone

Vet Clinic Application dependencies

application_id → application_name
application_id → application_email
application_id → application_phone
application_id → application_city
application_id → application_address
application_id → submitted_at
application_id → reviewed_at
application_id → reviewed_by
application_id → application_status
application_id → denial_reason

Health Record dependencies

healthrecord_id → health_type
healthrecord_id → health_description
healthrecord_id → health_date
healthrecord_id → animal_id

Review dependencies

review_id → rating
review_id → review_comment
review_id → review_created_at
review_id → review_updated_at
review_id → is_deleted
review_id → reviewer_id
review_id → target_user_id
review_id → target_clinic_id

Notification dependencies

notification_id → notification_type
notification_id → notification_message
notification_id → notification_created_at
notification_id → is_read
notification_id → user_id

Fav listing dependencies

(favorite_user_id, favorite_listing_id) → ∅

Candidate Keys and Primary Key Selection