RelationalDesign: kreiranje.sql

set search_path = project;

drop table if exists Se_simnuva_na;
--drop table if exists Se_validira;
drop table if exists KaznaZaRegistriran;
drop table if exists KaznaZaNeregistriran;
drop table if exists Kazna;
drop table if exists Kontroli;
drop table if exists Vozenje;
drop table if exists Bilet;
drop table if exists TipBilet;
drop table if exists PostojkaNaLinija;
drop table if exists Postojka;
drop table if exists Mesto;
drop table if exists InstancaNaLinija;
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 Korisnik;

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 Korisnik(
        k_id bigserial primary key,
        k_ime string_dolg not null,
        k_adresa string_dolg, 
        k_telefon string_kratok not null, 
        k_email string_kratok not null,
        k_embg varchar(13),
        k_is_admin bool not null,
        k_lozinka string_kratok not null
);

create table Patnik(
        k_id bigint primary key references Korisnik(k_id)
);
--      *k_id referencira od Korisnik(k_id)


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


create table Vozac(
        k_id bigint primary key references Korisnik(k_id)
);
--      *k_id referencira od Korisnik(k_id)

create table Kondukter(
        k_id bigint primary key references Korisnik(k_id)
);
--      *k_id referencira od Korisnik(k_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,
--      k_id bigint not null,
--      constraint dokument_za_covek foreign key (k_id) references Korisnik(k_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,
        li_pravec string_kratok not null
);

create table InstancaNaLinija (
    inl_id bigserial primary key,
    inl_datum_start timestamp not null,
    inl_datum_end timestamp,
    vozac_k_id bigint not null,
    a_registracija varchar(8),
    li_id integer not null,
    foreign key (vozac_k_id) references Korisnik(k_id),
    foreign key (a_registracija) references Avtobus(a_registracija),
    foreign key (li_id) references Linija(li_id)
);
--      *k_id referencira od Korisnik(k_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 TipBilet (
    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,
    b_status string_kratok not null,
    patnik_k_id bigint,
    tb_id bigint,
    foreign key (patnik_k_id) references Korisnik(k_id),
    foreign key (tb_id) references TipBilet(tb_id)
);


create table Vozenje (
    vozenje_id bigserial primary key,
    vozenje_start timestamp not null,
    vozenje_end timestamp,
    vozenje_status string_kratok not null,
    patnik_k_id bigint,
    kacuva_pnl_id bigint not null,
    inl_id bigint not null,
    b_id bigint not null,
    foreign key (b_id) references Bilet(b_id),
    foreign key (patnik_k_id) references Korisnik(k_id),
    foreign key (kacuva_pnl_id) references PostojkaNaLinija(pnl_id),
    foreign key (inl_id) references InstancaNaLinija(inl_id)
);

create table Kontroli (
    kontrola_id bigserial primary key,
    kontrola_datum timestamp NOT NULL,
    kondukter_k_id bigint,
    inl_id bigint,
    foreign key (kondukter_k_id) references Korisnik(k_id),
    foreign key (inl_id) references InstancaNaLinija(inl_id)
);

create table Kazna (
    kz_id bigserial primary key,
    kz_iznos float8 NOT NULL,
    kz_plateno boolean NOT NULL,
    kz_datum timestamp NOT NULL,
    kz_datum_plateno timestamp,
    kz_dokument string_kratok not null,
    kondukter_k_id bigint,
    kontrola_id bigint,
    foreign key (kondukter_k_id) references Korisnik(k_id),
    foreign key (kontrola_id) references Kontroli(kontrola_id)
);

create table KaznaZaRegistriran(
        kz_id bigint primary key references Kazna(kz_id),
        patnik_k_id bigint,
    foreign key (patnik_k_id) references Korisnik(k_id)
);

create table KaznaZaNeregistriran(
        kz_id bigint primary key references Kazna(kz_id),
    kzn_telefon string_kratok,
    kzn_ime string_dolg not null,
    kzn_adresa string_dolg not null
);



--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)
);