RelationalDesign: kreiranje.sql

--drop schema if exists project cascade;
--create schema project;

drop table vozaci_ima_kategorii;
drop table rezervacii_ima_za_vozila;
drop table vozaci_e_na_klienti;
drop table Avtomobili;
drop table Motori;
drop table Kombinja;
drop table MSteti;
drop table Parkirani;
drop table Servisi;
drop table ServisKompBr;
drop table ServisKomp;
drop table Vozila;
drop table Kategorii;
drop table IzdavSaloniBr;
drop table Lokacii;
drop table FizickiLBr;
drop table FizickiL;
drop table PravniL;
drop table Rezervacii;
drop table Naplati;
drop table Smetki;
drop table Dogovori;
drop table Klienti;
drop table VraboteniBr;
drop table Prodavachi;
drop table Menadzeri;
drop table Vraboteni;
drop table IzdavSaloni;
drop table VozaciBr;
drop table Vozaci;

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

create table Kategorii (
        idKat serial primary key,
        tip varchar(3)
);

create table Vozila (
        idVozi integer primary key,
        idKat integer,
        model varchar(255),
        marka varchar(255),
        regTab varchar(255),
        cenaVozi decimal,
        constraint fk_idKat foreign key (idKat) references Kategorii (idKat)
);

create table Avtomobili (
        idVoziA integer primary key,
        brSedista integer,
        constraint fk_idVozi foreign key (idVoziA) references Vozila(idVozi)
);

create table Motori (
        idVoziM integer primary key, 
        kubikaza integer,
        constraint fk_idVozi foreign key (idVoziM) references Vozila(idVozi)
);

create table Kombinja (
        idVoziK integer primary key,
        brSedista integer,
        nosivost integer,
        constraint fk_idVozi foreign key (idVoziK) references Vozila(idVozi)
);

create table IzdavSaloni (
        idIS serial primary key
);

create table IzdavSaloniBr (
        idIS integer,
        telBrIs char(9),
        constraint pk_IzdavSaloniBr primary key (idIs, telBrIs),
        constraint fk_IzdavSaloniBr foreign key (idIS) references IzdavSaloni(idIS)
);

create table Klienti (
        EMBGK char(13) primary key,
        imeK varchar(255),
        adresaK varchar(255),
        primiumK boolean
);

create table FizickiL (
        EMBGKFL char(13) primary key,
        constraint fk_FizickiL foreign key (EMBGKFL) references Klienti (EMBGK)
);

create table FizickiLBr (
        EMBGKFL char(13),
        telBrFL char(9),
        constraint pk_FizickiLBr primary key (EMBGKFL, telBrFL),
        constraint fk_FizickiLBr foreign key (EMBGKFL) references FizickiL(EMBGKFL)
);

create table PravniL (
        EMBGKPL char(13) primary key,
        direktor varchar(255),
        constraint fk_PravniL foreign key (EMBGKPL) references Klienti (EMBGK)
);

create table Dogovori (
        idDog serial primary key,
        EMBGK char(13) references Klienti(EMBGK),
        idIS integer references IzdavSaloni(idIS),
        cenaDog decimal,
        sklucenNa date,
        vaziDo date
);

create table Lokacii (
        idLok serial primary key,
        idIS integer references IzdavSaloni(idIS),
        adresaLok varchar(255),
        gradLok varchar(255)
);

create table Vozaci (
        EMBGVoza char(13) primary key, 
        imeVoza varchar(255),
        adresaVoza varchar(255),
        vozackaD char(8)
);

create table VozaciBr (
        EMBGVoza char(13) references Vozaci(EMBGVoza),
        telBrVoza char(9),
        constraint pk_VozaciBr primary key (EMBGVoza, telBrVoza)
);

create table MSteti (
        idMS serial primary key,
        idVozi integer references Vozila(idVozi),
        EMBGVoza char(13) references Vozaci(EMBGVoza),
        cenaMS decimal
);

create table Vraboteni (
        EMBGV char(13) primary key,
        idIS integer references IzdavSaloni(idIS),
        imeV varchar(255)
);

create table VraboteniBr (
        EMBGVBr char(13) references Vraboteni(EMBGV),
        telBrV char(9),
        constraint pk_VraboteniBr primary key (EMBGVBr, telBrV)
);

create table Menadzeri (
        EMBGVM char(13) primary key,
        constraint fk_Menadzeri foreign key (EMBGVM) references Vraboteni(EMBGV)
);
 
create table Prodavachi (
        EMBGVP char(13) primary key,
        EMBGVM char(13) references Menadzeri(EMBGVM),
        constraint fk_Prodavachi foreign key (EMBGVP) references Vraboteni(EMBGV)
);

create table Rezervacii (
        idRez serial primary key,
        EMBGVoza char(13) references Vozaci(EMBGVoza),
        idDog integer references Dogovori(idDog),
        naDatumRez date,
        doDatumRez date
);

create table ServisKomp (
        brSK integer primary key,
        imeSK varchar(255),
        adresaSK varchar(255)
);

create table ServisKompBr (
        brSK integer references ServisKomp(brSK), 
        telBrSK char(9),
        constraint pk_ServisKompBr primary key (brSK, telBrSK)
);

create table Servisi (
        brS serial primary key,
        brSk integer references ServisKomp(brSK),
        idVozi integer references Vozila(idVozi),
        cenaS decimal,
        naDatumS date
);

create table Smetki (
        brSm serial primary key,
        idDog integer references Dogovori(idDog), 
        cenaSm decimal,
        datumSm date
);

create table Parkirani (
        od date,
        doD date,
        idVozi integer references Vozila(idVozi),
        idIS integer references IzdavSaloni(idIS),
        constraint pk_Parkirani primary key (od, idVozi, idIS)
);

create table Naplati (
        idNap serial,
        brSM integer references Smetki(brSm),
        EMBGVP char(13) references Prodavachi(EMBGVP),
        kesh boolean,
        kartica boolean,
        datumNap date,
        constraint pk_Naplati primary key (idNap, brSM)
);

create table rezervacii_ima_za_vozila (
        idRez integer references Rezervacii(idRez),
        idVozi integer references Vozila (idVozi),
        constraint pk_rez_ima_za_vozi primary key (idRez, IdVozi)
); 

create table vozaci_e_na_klienti (
        EMBGVoza char(13) references Vozaci(EMBGVoza),
        EMBGK char(13) references Klienti(EMBGK),
        constraint pk_voz_e_na_klienti primary key (EMBGVoza, EMBGK)
);

create table vozaci_ima_kategorii (
        EMBGVoza char(13) references Vozaci(EMBGVoza),
        idKat integer references Kategorii(idKat),
        constraint pk_voz_ima_kat primary key (EMBGVoza, idKat)
);

alter table izdavsaloni
add imeSal varchar(255);

alter table fizickilbr
alter column telbrfl type varchar(20);

alter table izdavsalonibr
alter column telbris type varchar(20);

alter table serviskompbr
alter column telbrsk type varchar(20);

alter table vrabotenibr
alter column telbrv type varchar(20);

alter table vozacibr
alter column telbrvoza type varchar(20);