RelationalDesign: kreiranje_v1.sql

set search_path = project;

drop table if exists Se_simnuva_na;
--drop table if exists Se_validira;
drop table if exists Kazna;
drop table if exists Kontroli;
drop table if exists Vozenje;
drop table if exists Bilet;
drop table if exists KategorijaNaBilet;
drop table if exists PostojkaNaLinija;
drop table if exists Postojka;
drop table if exists Mesto;
drop table if exists DnevniRuti;
drop table if exists Linija;
drop table if exists Avtobus;
drop table if exists Dokument;
drop table if exists Kondukter;
drop table if exists Vozac;
drop table if exists Vraboten;
drop table if exists Patnik;
drop table if exists Lugje;

drop domain if exists string_dolg;
drop domain if exists string_kratok;

drop schema if exists project;

create schema project;

set search_path = project;

create domain string_dolg AS character varying(4000);
create domain string_kratok AS character varying(500);

create table Lugje(
        l_id bigserial primary key,
        l_ime string_kratok not null,
        l_prezime string_kratok not null,
        l_adresa string_kratok, 
        l_telefon string_kratok not null, 
        l_email string_kratok not null,
        l_embg varchar(13),
        l_is_admin bool not null,
        l_lozinka string_kratok not null
);

create table Patnik(
        l_id bigint primary key references Lugje(l_id)
);
--      *l_id referencira od Lugje(l_id)


create table Vraboten(
        l_id bigint primary key references Lugje(l_id),
        v_plata float8 not null, 
        v_datum_na_vrabotuvanje date not null,
        v_datum_prekin_vrabotuvanje date
);
--      *l_id referencira od Lugje(l_id)


create table Vozac(
        l_id bigint primary key references Lugje(l_id)
);
--      *l_id referencira od Lugje(l_id)

create table Kondukter(
        l_id bigint primary key references Lugje(l_id)
);
--      *l_id referencira od Lugje(l_id)

create table Dokument(
        d_broj_na_dokumnet string_kratok primary key, 
        d_datum_na_izdavanje date not null, 
        d_datum_ist date not null, 
        d_koj_go_izdal string_kratok not null,
        l_id bigint not null,
        constraint dokument_za_covek foreign key (l_id) references Lugje(l_id)
);

create table Avtobus(
        a_registracija varchar(8) primary key, 
        a_seriski_broj string_kratok not null, 
        a_broj_sedista smallint
);

create table Linija(
        li_id integer primary key, 
        li_ime string_kratok not null
);

create table DnevniRuti (
    dr_id bigserial primary key,
    dr_datum timestamp not null,
    vozac_l_id bigint not null,
    a_registracija varchar(8),
    li_id integer not null,
    foreign key (vozac_l_id) references Lugje(l_id),
    foreign key (a_registracija) references Avtobus(a_registracija),
    foreign key (li_id) references Linija(li_id)
);
--      *l_id referencira od Lugje(l_id)
--      ^a_registracija referncira od Avtobus(a_registracija)
--      #li_id referencira od Linija(li_id)
        
create table Mesto(
        m_id serial primary key,  
        m_grad string_kratok not null, 
        m_opstina string_kratok not null, 
        m_ulica string_kratok not null
);

create table Postojka(
        p_id serial primary key, 
        p_ime string_kratok not null, 
        m_id integer,
        constraint postojka_na_mesto foreign key (m_id) references Mesto(m_id)
);
        --*m_id referncira od Mesto(m_id)

        
create table PostojkaNaLinija(
        pnl_id bigserial primary key, 
        pnl_reden_broj smallint not null, 
        li_id integer not null, 
        p_id integer not null,
        constraint linija_na_postojka foreign key (li_id) references Linija(li_id),
        constraint postojka_na_linija foreign key (p_id) references Postojka(p_id)
);
--      *li_id referencira od Linija(li_id)
--      ^p_id referencira od Postojka(p_id)

create table KategorijaNaBilet (
    tb_id serial primary key,
    tb_trajnost bigint not null,
    tb_ime string_kratok not null
);


create table Bilet (
    b_id bigserial primary key,
    b_datum_na_kupuvanje timestamp NOT NULL,
    patnik_l_id bigint,
    tb_id bigint,
    foreign key (patnik_l_id) references Lugje(l_id),
    foreign key (tb_id) references KategorijaNaBilet(tb_id)
);


create table Vozenje (
    vozenje_id bigserial primary key,
    vozenje_start timestamp not null,
    vozenje_end timestamp,
    patnik_l_id bigint,
    kacuva_pnl_id bigint not null,
    dr_id bigint not null,
    b_id bigint not null,
    foreign key (b_id) references Bilet(b_id),
    foreign key (patnik_l_id) references Lugje(l_id),
    foreign key (kacuva_pnl_id) references PostojkaNaLinija(pnl_id),
    foreign key (dr_id) references DnevniRuti(dr_id)
);

create table Kontroli (
    kontrola_id bigserial primary key,
    kontrola_datum timestamp NOT NULL,
    kondukter_l_id bigint,
    vozenje_id bigint,
    foreign key (kondukter_l_id) references Lugje(l_id),
    foreign key (vozenje_id) references Vozenje(vozenje_id)
);

create table Kazna (
    k_id bigserial primary key,
    k_iznos float8 NOT NULL,
    k_plateno boolean NOT NULL,
    k_datum timestamp NOT NULL,
    kondukter_l_id bigint,
    kontrola_id bigint,
    patnik_l_id bigint,
    foreign key (kondukter_l_id) references Lugje(l_id),
    foreign key (kontrola_id) references Kontroli(kontrola_id),
    foreign key (patnik_l_id) references Lugje(l_id)
);


--create table Se_validira (
--    vozenje_id bigint not null,
--    b_id bigint not null,
--    primary key (vozenje_id, b_id),
--    foreign key (vozenje_id) references Vozenje(vozenje_id),
--    foreign key (b_id) references Bilet(b_id)
--);
----    * vozenje_id referencira od Vozenje(vozenje_id)
----    ^ b_id referencira od Bilet(b_id)


create table Se_simnuva_na (
    pnl_id bigint,
    vozenje_id bigint,
    primary key (pnl_id, vozenje_id),
    foreign key (pnl_id) references PostojkaNaLinija(pnl_id),
    foreign key (vozenje_id) references Vozenje(vozenje_id)
);