RelationalDesign: kreiranje.sql

create table user_table(
        id_user serial primary key,
        date_created_user date not null, 
        name_user varchar(100) not null, 
        email_user varchar(100) not null unique, 
        password_user varchar(20) not null, 
        telephone_user varchar(20) 
);

create table surendee(
        id_user integer primary key, 
        constraint fk_surendee_id_user foreign key (id_user)
                references user_table (id_user) on delete cascade
);

create table admin_table(
        id_user integer primary key,
        constraint fk_admin_table_id_user foreign key (id_user)
                references user_table (id_user) on delete cascade
);

create table donor(
        id_user integer primary key,
        is_from_organisation bool not null,
        name_organisation_donor varchar(100),
        constraint fk_donor_id_user foreign key (id_user)
                references user_table (id_user) on delete cascade
);

create table organisation(
        id_organisation serial primary key,
        name_organisation varchar(100) not null, 
        email_organisation varchar(100) not null unique,
        billing_information varchar(20) not null unique
);

create table shelter(
        id_shelter serial primary key,
        address_shelter varchar(100) not null, 
        telephone_shelter varchar(20) not null,
        id_organisation integer,
        name_shelter varchar(100) not null,
        email_shelter varchar(100) not null unique,
        constraint fk_shelter_organisation foreign key (id_organisation)
                references organisation (id_organisation)
);

create table employee(
        id_user integer primary key,
        position_employee varchar(20) not null,
        id_shelter integer not null,
        is_verified bool not null,
        verified_by_admin integer,
        constraint fk_employee_id_user foreign key (id_user)
                references user_table (id_user) on delete cascade,
        constraint fk_employee_id_shelter foreign key (id_shelter)
                references shelter (id_shelter) on delete cascade,
        constraint fk_employee_id_admin foreign key (verified_by_admin)
                references admin_table (id_user)
);

create table adopter(
        id_user integer primary key, 
        free_time integer, 
        funds integer, 
        has_other_pets bool, 
        has_kids bool, 
        housing integer,  
        physical_activity_adopters integer, 
        will_foster bool, 
        is_verified bool not null,
        verified_by_employee integer,
        constraint fk_adopter_id_user foreign key (id_user)
                references user_table (id_user) on delete cascade,
        constraint fk_adopter_id_employee foreign key (verified_by_employee)
                references employee (id_user)
);

create table adoption(
        id_adoption serial primary key,  
        start_date date not null, 
        end_date_foster date,
        approved bool not null,
        id_adopter integer not null,
        constraint fk_adoption_adopter foreign key (id_adopter)
                references adopter(id_user)
);

create table pet(
        id_pet serial primary key,
        url_pet_image varchar(200) , 
        age_group integer not null, 
        size_pet integer not null, 
        breed varchar(100) , 
        name_pet varchar(20) , 
        species integer not null, 
        gender integer not null, 
        can_be_fostered bool not null,
        id_adoption integer,
        id_shelter integer,
        constraint fk_pet_adoption foreign key (id_adoption)
                references adoption(id_adoption),
        constraint fk_pet_shelter foreign key (id_shelter)
                references shelter(id_shelter)
);

create table post(
        id_post serial primary key, 
        date_post date not null, 
        url_thumbanail varchar(200),
        id_pet integer not null,
        id_surendee integer,
        id_employee integer,
        constraint fk_post_pet foreign key (id_pet)
                references pet (id_pet) on delete cascade,
        constraint fk_post_surendee foreign key (id_surendee)
                references surendee (id_user),
        constraint fk_post_employee foreign key (id_employee)
                references employee (id_user),
        constraint chk_post_poster check (
            (id_surendee is not null and id_employee is null)
            or 
            (id_surendee is null and id_employee is not null)
        )
);

create table category(
        id_category serial primary key,
        name_category varchar(100) not null
);

create table food(
        id_food serial primary key, 
        manufacturer varchar(100) not null, 
        name_food varchar(20) not null, 
        type_food integer not null
);

create table therapy(
        id_therapy serial primary key, 
        health_problem varchar(100) not null, 
        start_date date, 
        end_date date 
);

create table vet_clinic(
        id_vet_clinic serial primary key,
        telephone_vet_clinic varchar(20) not null, 
        address_vet_clinic varchar(100) not null,
        name_vet_clinic varchar(100) not null
);

create table personal_profile(
        id_pet integer primary key, 
        friendly_to_kids integer not null, 
        friendly_to_pets integer not null, 
        attention integer not null, 
        physical_activity integer not null, 
        grooming_needed integer not null,
        constraint fk_personal_profile_id_pet foreign key (id_pet)
                references pet (id_pet) on delete cascade
);

create table donor_donates_to_organisation(
        id_user integer not null,
        id_organisation integer not null,
        constraint pk_donor_donates_to_organisation primary key
                (id_user, id_organisation),
        constraint fk_donor_donates_to_organisation_user foreign key (id_user)
                references donor(id_user) on delete cascade,
        constraint fk_donor_donates_to_organisation_organisation foreign key (id_organisation)
                references organisation(id_organisation) on delete cascade
);

create table pet_belongs_to_category(
        id_pet integer not null,
        id_category integer not null,
        constraint pk_pet_belongs_to_category primary key
                (id_pet, id_category),
        constraint fk_pet_belongs_to_category_pet foreign key (id_pet)
                references pet(id_pet) on delete cascade,
        constraint fk_pet_belongs_to_category_category foreign key (id_category)
                references category(id_category) on delete cascade
);

create table pet_needs_therapy(
        id_pet integer not null,
        id_therapy integer not null,
        constraint pk_pet_needs_therapy primary key
                (id_pet, id_therapy),
        constraint fk_pet_needs_therapy_pet foreign key (id_pet)
                references pet(id_pet) on delete cascade,
        constraint fk_pet_needs_therapy_therapy foreign key (id_therapy)
                references therapy(id_therapy) on delete cascade
);

create table pet_preferably_eats_food(
        id_pet integer not null,
        id_food integer not null,
        quantity_a_day integer not null,
        constraint pk_pet_preferably_eats_food primary key
                (id_pet, id_food),
        constraint fk_pet_preferably_eats_food_pet foreign key (id_pet)
                references pet(id_pet) on delete cascade,
        constraint fk_pet_preferably_eats_food_food foreign key (id_food)
                references food(id_food) on delete cascade
);

create table pet_needs_intervention_in_vet_clinic(
        id_pet integer not null,
        id_vet_clinic integer not null,
        date_of_interventing date not null,
        description varchar(100),
        constraint pk_pet_needs_intervention_in_vet_clinic primary key
                (id_pet, id_vet_clinic),
        constraint fk_pet_needs_intervention_in_vet_clinic_pet foreign key (id_pet)
                references pet(id_pet) on delete cascade,
        constraint fk_pet_needs_intervention_in_vet_clinic_vet_clinic foreign key (id_vet_clinic)
                references vet_clinic(id_vet_clinic) on delete cascade
);