RelationalDesign: kreiranje.sql

drop schema if exists project cascade;
create schema project;

drop table if exists pilots cascade;
drop table if exists pilot_videos cascade;
drop table if exists pilot_images cascade;
drop table if exists organizers cascade;
drop table if exists drones cascade;
drop table if exists countries cascade;
drop table if exists cities cascade;
drop table if exists races cascade;
drop table if exists participants cascade;


create table pilots(
        pilot_id serial not null,
        pilot_name varchar(100) not null,
        first_name varchar(100) not null,
        last_name varchar(100) not null,
        birth_date date null,
        email varchar(100) not null,
        "password" varchar not null,
        member_since date not null,
        bio varchar(2000) null,
        constraint pilot_id_pk primary key (pilot_id)
);

---------------------------------------------------------------

create table pilot_videos(
        video_id serial not null,
        video_link varchar(100) not null,
        pilot_id integer not null,
        CONSTRAINT pilot_video_pk primary key (video_id),
        constraint pilot_videos_pilot_id_fk foreign key (pilot_id) references pilots(pilot_id)
);

---------------------------------------------------------------


create table pilot_images(
        image_id serial not null,
        image_data bytea not null,
        is_profilePicture boolean not null,
        pilot_id integer not null,
        constraint image_id_pk primary key (image_id),
        constraint pilot_images_pilot_id_fk foreign key (pilot_id) references pilots(pilot_id)
);


---------------------------------------------------------------

create table organizers(
        organizer_id serial not null,
        organizer_name varchar(100) not null,
        constraint organizer_id_pk primary key (organizer_id)
);

---------------------------------------------------------------

create table drones(
        drone_id serial not null,
        motors varchar(100) null, 
        esc varchar(100) null,
        frame varchar(100) null,
        flight_controller varchar(100) null,
        battery varchar(100) null,
        drone_pilot_id integer not null,
        constraint drone_id_pk primary key (drone_id),
        constraint drone_pilot_id_fk foreign key (drone_pilot_id) references pilots(pilot_id)
);

---------------------------------------------------------------

create table countries(
        country_id serial not null,
        country_name varchar(100) not null,
        constraint country_id_pk primary key (country_id)
        
);



---------------------------------------------------------------

create table cities(
        city_id serial not null,
        city_name varchar(100) not null,
        country_id integer not null,
        constraint city_id_pk primary key (city_id),
        constraint country_id_fk foreign key (country_id) references countries(country_id)
);



---------------------------------------------------------------


create table races(
        race_id serial not null,
        track_photo bytea null,
        race_description varchar(100) null,
        start_date date not null,
        end_date date not null,
        city_id integer not null,
        organizer_id integer not null,
        constraint organizer_id_fk foreign key (organizer_id) references organizers(organizer_id),
        constraint race_id_pk primary key (race_id),
        constraint race_location_fk foreign key (city_id) references cities(city_id)
);

---------------------------------------------------------------

create table participants(
        pilot_id integer not null,
        race_id integer not null,
        constraint participating_pilot_id_fk foreign key (pilot_id) references pilots(pilot_id),
        constraint participating_race_id_fk foreign key (race_id) references races (race_id)
);