RelationalDesign: kreiranje.sql

drop schema if exists project cascade;
create schema project;

--use project

drop table if exists users cascade;
drop table if exists movie cascade;
drop table if exists employee cascade;
drop table if exists genre cascade;
drop table if exists movieProjection cascade;
drop table if exists firm cascade;
drop table if exists auditorium cascade;
drop table if exists seat cascade;
drop table if exists client cascade;
drop table if exists payment cascade;
drop table if exists Payment_Type cascade;
drop table if exists is_ cascade;
drop table if exists controles cascade;

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


create table users(
    
    user_id serial primary key,
    user_username varchar(150) not null,
    user_password varchar(150) not null,
    user_phone_number varchar(150),
    user_name varchar(150) not null,
    user_surname varchar(150) not null,
    user_email varchar(150) ,
    user_role varchar(100)
    
);


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

create table employee(
    
    employee_id serial,
    user_id integer not null,
    constraint fk_employee_users_ foreign key (user_id) references users(user_id),
    constraint pk_employee_users primary key (user_id)
    
);



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


create table client(
    client_id serial,
    user_id integer not null,
    constraint fk_client_users_ foreign key (user_id) references users(user_id),
    constraint pk_client_users primary key (user_id)
);


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


create table genre(
     genre_id serial primary key,
     genre_name varchar (150) not null
);


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


create table movie(

    movie_id integer primary key,
    movie_name varchar(150) not null,
    movie_age_category varchar(150) not null,
    movie_production varchar(150) not null,
    movie_film_director varchar(150) not null,
    movie_cast varchar(250) not null,
    movie_time_duration varchar(150) not null
 
);

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


create table firm(

firm_id serial primary key, 
firm_city varchar(100) not null, 
firm_adress varchar(100) not null, 
firm_name varchar(100) not null

);


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


create table auditorium(
auditorium_id serial primary key,
auditorium_name varchar(100) not null, 
auditorium_capacity integer not null,

firm_id integer not null,
    constraint fk_auditorium_firm foreign key (firm_id) references firm(firm_id)
);


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


create table movieProjection(
  projection_id serial primary key,
  projection_movie_start varchar(150) not null,
  projection_movie_end varchar(150) not null,
  projection_screening_date varchar(150) not null,
  projection_type varchar(150) not null,
  projection_price float8 not null,
  
  movie_id integer not null,
 constraint fk_movieProjection_movie foreign key (movie_id) references movie(movie_id)
  
);  

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


create table works_in(
works_from varchar(150) not null,
works_to varchar(150),

user_id integer not null,
firm_id integer not null,

constraint pk_works_in_firm primary key (user_id,firm_id),
constraint fk_works_in_firm foreign key (firm_id) references firm(firm_id),
constraint fk_works_in_employee foreign key (user_id) references employee(user_id)

);


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


create table Payment_Type(


payment_type_id serial primary key,
payment_type varchar(150) not null


);


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

create table seat(
seat_id serial ,
seat_number integer not null,
auditorium_id integer not null,
constraint pk_seat_auditorium primary key (auditorium_id,seat_id),
constraint fk_seat_auditorium foreign key (auditorium_id) references auditorium(auditorium_id)
);


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


create table reservation(
reservation_id serial primary key,
reservation_date date not null,
payment_type_id integer not null,
constraint fk_reservation_Payment_Type foreign key (payment_type_id) references Payment_Type(payment_type_id),
user_id integer not null,
constraint fk_reservation_client foreign key (user_id) references client(user_id),
auditorium_id integer not null,
seat_id integer not null,
constraint fk_seat_id foreign key (auditorium_id,seat_id) references seat(auditorium_id,seat_id),
projection_id integer not null,
constraint fk_reservation_movieProjection foreign key (projection_id) references movieProjection(projection_id)
);



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



create table payment(
payment_id serial primary key,
payment_date date not null,
user_id integer not null,
constraint fk_payment_client foreign key (user_id) references client(user_id),
payment_type_id integer not null,
constraint fk_payment_payment_type foreign key (payment_type_id) references payment_type(payment_type_id),
reservation_id integer not null,
constraint fk_payment_reservation foreign key (reservation_id) references reservation(reservation_id)
);

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


create table controles(
user_id integer not null,
projection_id integer not null,
constraint pk_controles_user primary key (user_id,projection_id),
constraint fk_controles_employee foreign key (user_id) references employee(user_id),
constraint fk_controles_movieProjection foreign key (projection_id) references movieProjection(projection_id)
);


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

create table is_(
genre_id integer not null,
movie_id integer not null,
constraint pk_is_genre primary key (movie_id,genre_id),
constraint fk_is_movie foreign key (movie_id) references movie(movie_id),
constraint fk_controles_genre foreign key (genre_id) references genre(genre_id)
);

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

create table gives(
seat_limitation integer not null,
projection_id integer not null,
auditorium_id integer not null,
constraint fk_gives_movieProjection foreign key (projection_id) references movieProjection(projection_id),
constraint fk_gives_auditorium foreign key (auditorium_id) references auditorium(auditorium_id),
constraint pk_gives_movieProjection primary key (auditorium_id,projection_id)
);

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