RelationalDesign: kreiranje.sql

drop table if exists workers cascade;
drop table if exists customers cascade;
drop table if exists salaries cascade;
drop table if exists films cascade;
drop table if exists events cascade;
drop table if exists users cascade;
drop table if exists cinemas cascade;
drop table if exists categories cascade;
drop table if exists discounts cascade;
drop table if exists seats cascade;
drop table if exists projections cascade;
drop table if exists projection_rooms cascade;
drop table if exists tickets cascade;
drop table if exists customer_rates_film cascade;
drop table if exists cinema_plays_film cascade;
drop table if exists cinema_organizes_event cascade;
drop table if exists projection_is_played_in_room cascade;
drop table if exists customer_is_interested_in_event cascade;
drop table if exists work_hours_weekly cascade;
drop schema if exists project cascade;

create schema project;


create table users(
        ID_user serial primary key,
        password varchar(200) not NULL,
        first_name varchar(200) not null,
        last_name varchar(200) not null,
        address  varchar(20000) not null,
        contact_number  varchar(20000) not null,
        date_created date not null
); 
create table cinemas(
        ID_cinema serial primary key,
        name varchar(200) not null,
        location varchar(200) not null
);
create table workers(
        ID_worker serial primary key references users(ID_user),
        position varchar(200) not NULL,
        work_hours_from varchar(200) not null,
        work_hours_to varchar(200) not null,
        ID_cinema integer references cinemas(ID_cinema) not null
); 

create table customers(
        ID_customer serial primary key references users(ID_User),
        points integer
);

create table salaries(
        ID_salary serial primary key,
        sum integer not null,
        date_from date not null,
        date_to date,
        ID_worker integer references workers(ID_worker) not null
);
create table films(
        ID_film serial primary key,
        name varchar(200) not null,
        duration integer not null,
        actors varchar(20000) not null,
        genre varchar(2000) not null,
        age_category varchar(2000) not null,
        director varchar(2000) not null,
        description varchar(20000),
        start_date date not null,
        end_date date not null
);
create table events(
        ID_event serial primary key,
        theme varchar(2000),
        duration varchar(2000) not null,
        repeating varchar(200),
        start_date date not null
);
create table discounts(
        ID_discount serial primary key,
        validity date not null,
        code varchar(2000) not null,
        type varchar(2000),
        percent integer not null
);

create table projections(
        ID_projection serial primary key,
        date_time_start date not null,
        type_of_technology varchar(200) not null,
        date_time_end date not null,
        ID_film integer references films(ID_film) not null,
        ID_discount integer references discounts(ID_discount),
        ID_event integer references events(ID_event)
        );
create table projection_rooms(
        ID_room serial primary key,
        number_of_seats integer not null,
        projection_room_number integer not null,
        ID_cinema integer references cinemas(ID_cinema) not null
);
create table categories(
        ID_category serial primary key,
        name varchar(2000) not null,
        extra_amount integer
);

create table seats(
        ID_seat serial primary key,
        seat_number integer not null,
        ID_room integer references projection_rooms(ID_room) not null,
        ID_category integer references categories(ID_category)
);
create table tickets(
        ID_ticket serial primary key,
        price bigint not null,
        ID_customer integer references customers(ID_customer) not null,
        date_reserved date not null,
        ID_projection integer references projections(ID_projection) not null,
        ID_discount integer references discounts(ID_discount),
        ID_seat integer references seats(ID_seat) not null
);

create table customer_rates_film(
ID_customer integer references customers(ID_customer) not null,
ID_film integer references films(ID_film) not null,
rating float not null,
CONSTRAINT pk_customer_film PRIMARY KEY (ID_customer, ID_film)
);

create table cinema_plays_film(
ID_cinema integer references cinemas(ID_cinema),
ID_film integer references films(ID_film),
CONSTRAINT pk_cinema_film PRIMARY KEY (ID_cinema, ID_film)
);

create table cinema_organizes_event(
ID_cinema integer references cinemas(ID_cinema),
ID_event integer references events(ID_event),
CONSTRAINT pk_cinema_event PRIMARY KEY (ID_cinema,ID_event)
);

create table projection_is_played_in_room(
ID_projection integer references projections(ID_projection),
ID_room integer references projection_rooms(ID_room),
CONSTRAINT pk_projection_room PRIMARY KEY (ID_projection,ID_room)
);
create table customer_is_interested_in_event(
id_customer integer references customers(ID_customer),
id_event integer references events(id_event),
constraint ce_customer_event PRIMARY KEY (id_customer,id_event)
);
create table work_hours_weekly(
id_work_hours serial primary key,
id_worker integer references workers(id_worker) not null,
week_number integer not null,
year_num varchar(200) not null,
hours_from timestamp not null,
hours_to timestamp not null,
check_in bool
);