CREATE TABLE Kamion (
    IDKam INTEGER NOT NULL,
    Marka CHAR(15) NOT NULL,
    Nosivost INTEGER NOT NULL CHECK (Nosivost>0),
    Godiste INTEGER CHECK (Godiste>1900),
    BrPopravljanja INTEGER DEFAULT 0 NOT NULL,
    PRIMARY KEY (IDKam)
);



CREATE TABLE Zaposlen (
    IDZap INTEGER NOT NULL,
    ImePrezime CHAR(40) NOT NULL,
    Staz INTEGER CHECK (Staz>=0),
    PRIMARY KEY (IDZap)
);



CREATE TABLE Vozac (
    Kategorija CHAR(5),
    IDZap INTEGER NOT NULL,
    PRIMARY KEY (IDZap)
);




CREATE TABLE Mehanicar (
    Specijalnost CHAR(15),
    IDZap INTEGER NOT NULL,
    PRIMARY KEY (IDZap)
);



CREATE TABLE Firma (
    IDFir INTEGER NOT NULL,
    Naziv CHAR(20) UNIQUE NOT NULL,
    Adresa VARCHAR(60),
    Tel1 CHAR(15),
    Tel2 CHAR(15),
    PRIMARY KEY (IDFir)
);



CREATE TABLE Posiljka (
    IDPos INTEGER NOT NULL,
    Tezina INTEGER NOT NULL,
    Vrednost INTEGER,
    MestoOd CHAR(25),
    MestoDo CHAR(25),
    IDFir INTEGER,
    PRIMARY KEY (IDPos)
);



CREATE TABLE Putovanje (
    IDPut INTEGER NOT NULL,
    Status CHAR(1),
    MestoOd CHAR(25),
    MestoDo CHAR(25),
    IDKam INTEGER,
Duzina INTEGER,
    PRIMARY KEY (IDPut)
);



CREATE TABLE Popravlja (
    Dana INTEGER,
    IDZap INTEGER NOT NULL,
    IDKam INTEGER NOT NULL,
    PRIMARY KEY (IDZap, IDKam)
);


CREATE TABLE Vozi (
    IDZap INTEGER NOT NULL,
    IDPut INTEGER NOT NULL,
    PRIMARY KEY (IDZap, IDPut)
);

CREATE TABLE sePrevozi (
    IDPos INTEGER NOT NULL,
    IDPut INTEGER NOT NULL,
    PRIMARY KEY (IDPos, IDPut)
);



/*==================================================================*/
/*  Foreign Keys                                                    */
/*==================================================================*/

ALTER TABLE Vozac
    ADD FOREIGN KEY (IDZap) REFERENCES Zaposlen (IDZap) ON DELETE CASCADE ON UPDATE NO ACTION;



ALTER TABLE Mehanicar
    ADD FOREIGN KEY (IDZap) REFERENCES Zaposlen (IDZap) ON DELETE CASCADE ON UPDATE NO ACTION;



ALTER TABLE Posiljka
    ADD FOREIGN KEY (IDFir) REFERENCES Firma (IDFir) ON UPDATE NO ACTION ON DELETE NO ACTION;



ALTER TABLE Putovanje
    ADD FOREIGN KEY (IDKam) REFERENCES Kamion (IDKam) ON UPDATE NO ACTION ON DELETE NO ACTION;



ALTER TABLE Popravlja
    ADD FOREIGN KEY (IDZap) REFERENCES Mehanicar (IDZap) ON DELETE NO ACTION;



ALTER TABLE Popravlja
    ADD FOREIGN KEY (IDKam) REFERENCES Kamion (IDKam) ON DELETE CASCADE ON UPDATE NO ACTION;


ALTER TABLE Vozi
    ADD FOREIGN KEY (IDZap) REFERENCES Vozac (IDZap) ON UPDATE NO ACTION ON DELETE NO ACTION;


ALTER TABLE Vozi
    ADD FOREIGN KEY (IDPut) REFERENCES Putovanje (IDPut) ON UPDATE NO ACTION ON DELETE NO ACTION;



ALTER TABLE sePrevozi
    ADD FOREIGN KEY (IDPos) REFERENCES Posiljka (IDPos) ON UPDATE NO ACTION ON DELETE NO ACTION;



ALTER TABLE sePrevozi
    ADD FOREIGN KEY (IDPut) REFERENCES Putovanje (IDPut) ON UPDATE NO ACTION ON DELETE NO ACTION;


INSERT INTO Kamion
(IDKam,Marka, Nosivost, Godiste, BrPopravljanja)
VALUES  
(0,'Mercedes',4,1990,8);

INSERT INTO Kamion
(IDKam,Marka, Nosivost, Godiste, BrPopravljanja)
VALUES  
(1,'Mercedes',6,1994,8);

INSERT INTO Kamion
(IDKam,Marka, Nosivost, Godiste, BrPopravljanja)
VALUES  
(2,'Mercedes',5,2000,8);

INSERT INTO Kamion
(IDKam,Marka, Nosivost, Godiste, BrPopravljanja)
VALUES  
(3,'Mercedes',8,2001,8);

INSERT INTO Kamion
(IDKam,Marka, Nosivost, Godiste, BrPopravljanja)
VALUES  
(4,'Mercedes',15,2005,0);

INSERT INTO Kamion
(IDKam,Marka, Nosivost, Godiste, BrPopravljanja)
VALUES  
(5,'FAP',5,2002,3);

INSERT INTO Kamion
(IDKam,Marka, Nosivost, Godiste, BrPopravljanja)
VALUES  
(6,'FAP',3,2000,4);

INSERT INTO Kamion
(IDKam,Marka, Nosivost, Godiste, BrPopravljanja)
VALUES  
(7,'MAN',12,2004,0);

INSERT INTO Kamion
(IDKam,Marka, Nosivost, Godiste, BrPopravljanja)
VALUES  
(8,'MAN',10,1999,3);

INSERT INTO Kamion
(IDKam,Marka, Nosivost, Godiste, BrPopravljanja)
VALUES  
(9,'MAN',8,1997,3);


INSERT INTO Zaposlen 
(IDZap,ImePrezime, Staz)
VALUES
(0,'Marko Jagodic',12);
INSERT INTO Vozac
(IDZap, Kategorija)
VALUES
(0,'BCDE');

INSERT INTO Zaposlen 
(IDZap, ImePrezime, Staz)
VALUES
(1,'Mihajlo Janjic',4);
INSERT INTO Vozac
(IDZap, Kategorija)
VALUES
(1,'BC');

INSERT INTO Zaposlen 
(IDZap,ImePrezime, Staz)
VALUES
(2,'Dragan Milutinovic',6);
INSERT INTO Vozac
(IDZap, Kategorija)
VALUES
(2,'BC');

INSERT INTO Zaposlen 
(IDZap,ImePrezime, Staz)
VALUES
(3,'Milos Miljkovic',7);
INSERT INTO Vozac
(IDZap, Kategorija)
VALUES
(3,'BCD');



INSERT INTO Zaposlen 
(IDZap,ImePrezime, Staz)
VALUES
(4,'Dusan Rajic',4);
INSERT INTO Mehanicar
(IDZap,Specijalnost)
VALUES
(4,'Mercedes');

INSERT INTO Zaposlen 
(IDZap,ImePrezime, Staz)
VALUES
(5,'Marko Pesic',8);
INSERT INTO Mehanicar
(IDZap,Specijalnost)
VALUES
(5,'FAP');

INSERT INTO Zaposlen 
(IDZap,ImePrezime, Staz)
VALUES
(6,'Obrad Zimonjic',7);
INSERT INTO Mehanicar
(IDZap,Specijalnost)
VALUES
(6,'MAN');

INSERT INTO Zaposlen 
(IDZap,ImePrezime, Staz)
VALUES
(7,'Drasko Petkovic',8);
INSERT INTO Mehanicar
(IDZap,Specijalnost)
VALUES
(7,'MAN');



INSERT INTO Firma
(IDFir,Naziv, Adresa, Tel1, Tel2)
VALUES
(0,'Petrolept','Beograd, Milana Rakica 5','011/258-221','063/223-223');

INSERT INTO Firma
(IDFir,Naziv, Adresa, Tel1)
VALUES
(1,'Radoje Dakic', 'Podgorica, Njegoseva 10/1','069/123-123');

INSERT INTO Firma
(IDFir,Naziv, Adresa, Tel1, Tel2)
VALUES
(2,'Tropik', 'Beograd, Dusana Vukasovica 50/1','011/123-1234','064/111-1111');



INSERT INTO Firma
(IDFir,Naziv, Adresa, Tel1)
VALUES
(3,'Meldovo', 'Beograd, Dusana Kovacevica 15/bb','064/222-3334');



INSERT INTO Posiljka(IDPos,Tezina,Vrednost,MestoOd,MestoDo,IDFir)
VALUES (0,1900,2000,'Kragujevac','Kraljevo',2);

INSERT INTO Posiljka(IDPos,Tezina,Vrednost,MestoOd,MestoDo,IDFir)
VALUES (1,5000,10000,'Kragujevac','Kraljevo',1);

INSERT INTO Posiljka(IDPos,Tezina,Vrednost,MestoOd,MestoDo,IDFir)
VALUES (2,2500,25000,'Beograd','Berlin',0);

INSERT INTO Putovanje(IDPut,Status,MestoOd,MestoDo,Duzina,IDKam)
VALUES (0,'T','Beograd','Berlin',1500,6);

INSERT INTO sePrevozi(IDPos,IDPut)
VALUES (2,0);

INSERT INTO vozi(IDZap,IDPut)
VALUES(3,0);

INSERT INTO vozi(IDZap,IDPut)
VALUES(2,0);

INSERT INTO Posiljka(IDPos,Tezina,Vrednost,MestoOd,MestoDo,IDFir)
VALUES (3,4150,8000,'Beograd','Sombor',0);

INSERT INTO Putovanje(IDPut,Status,MestoOd,MestoDo,Duzina,IDKam)
VALUES (1,'O','Beograd','Sombor',190,1);

INSERT INTO vozi(IDZap,IDPut)
VALUES(3,1);

INSERT INTO sePrevozi(IDPos,IDPut)
VALUES (3,1);

INSERT INTO Posiljka(IDPos,Tezina,Vrednost,MestoOd,MestoDo,IDFir)
VALUES (4,1900,4000,'Beograd','Sombor',2);

INSERT INTO sePrevozi(IDPos,IDPut)
VALUES (4,1);








INSERT INTO Posiljka(IDPos,Tezina,Vrednost,MestoOd,MestoDo,IDFir)
VALUES (5,12000,12000,'Beograd','Subotica',3);

INSERT INTO Putovanje(IDPut,Status,MestoOd,MestoDo,Duzina,IDKam)
VALUES (2,'P','Beograd','Subotica',240,7);

INSERT INTO sePrevozi(IDPos,IDPut)
VALUES (5,2);

INSERT INTO Posiljka(IDPos,Tezina,Vrednost,MestoOd,MestoDo,IDFir)
VALUES (6,7500,8000,'Beograd','Arandjelovac',3);

INSERT INTO Putovanje(IDPut,Status,MestoOd,MestoDo,Duzina,IDKam)
VALUES (3,'O','Beograd','Arandjelovac',80,9);

INSERT INTO sePrevozi(IDPos,IDPut)
VALUES (6,3);

INSERT INTO vozi(IDZap,IDPut)
VALUES(2,3);


INSERT INTO Posiljka(IDPos,Tezina,Vrednost,MestoOd,MestoDo,IDFir)
VALUES (7,2900,4500,'Beograd','Leskovac',2);

INSERT INTO Putovanje(IDPut,Status,MestoOd,MestoDo,Duzina,IDKam)
VALUES (4,'O','Beograd','Leskovac',300,5);

INSERT INTO sePrevozi(IDPos,IDPut)
VALUES (7,4);

INSERT INTO vozi(IDZap,IDPut)
VALUES(0,4);

INSERT INTO Posiljka(IDPos,Tezina,Vrednost,MestoOd,MestoDo,IDFir)
VALUES (8,9000,11400,'Beograd','Niksic',0);

INSERT INTO Putovanje(IDPut,Status,MestoOd,MestoDo,Duzina,IDKam)
VALUES (5,'O','Beograd','Niksic',460,4);

INSERT INTO sePrevozi(IDPos,IDPut)
VALUES (8,5);

INSERT INTO vozi(IDZap,IDPut)
VALUES(1,5);

INSERT INTO vozi(IDZap,IDPut)
VALUES(3,5);

INSERT INTO Popravlja(IDZap,IDKam,Dana)
VALUES(6,9,3);

INSERT INTO Popravlja(IDZap,IDKam,Dana)
VALUES(7,9,3);

INSERT INTO Popravlja(IDZap,IDKam,Dana)
VALUES(4,0,2);