SQL

CREATE TABLE Profesor
(
    sifra INT,
    ime nvarchar(20),
    prezime nvarchar(50),
    datumZaposlenja DATE,
    zvanje nvarchar(30),
    specijalnostIstrazivanja nvarchar(30),

    CONSTRAINT Profesor_PK PRIMARY KEY(sifra)
);

CREATE TABLE Projekat
(
    brojProjekta INT,
    imeSponzora nvarchar(20),
    datumPocetka DATE,
    datumKraja DATE,
    budzet INT,

    CONSTRAINT Projekat_PK PRIMARY KEY(brojProjekta)
);

CREATE TABLE Radi
(
    brojProjekta INT,
    sifra INT,

    CONSTRAINT Radi_PK PRIMARY KEY(brojProjekta),
    CONSTRAINT Radi_FK FOREIGN KEY(sifra) REFERENCES Profesor,
    CONSTRAINT Radi_FK2 FOREIGN KEY(brojProjekta) REFERENCES Projekat
);

CREATE TABLE StudentPostdiplomac
(
    sifra INT,
    ime nvarchar(20),
    prezime nvarchar(50),
    datumRodjenja DATE,
    nazivUsmerenja nvarchar(30),

    CONSTRAINT StudentPostdiplomac_PK PRIMARY KEY(sifra)
);

CREATE TABLE Angazovan
(
    brojProjekta INT,
    sifra INT,
    datum DATE,

    CONSTRAINT Angazovan_PK PRIMARY KEY(brojProjekta,sifra),
    CONSTRAINT Angazovan_FK FOREIGN KEY(brojProjekta) REFERENCES Projekat,
    CONSTRAINT Angazovan_FK2 FOREIGN KEY(sifra) REFERENCES StudentPostdiplomac
);

CREATE TABLE Usmerenje
(
    sifra INT,
    smer nvarchar(50) NOT NULL,

    CONSTRAINT Usmerenje_PK PRIMARY KEY(sifra),
    CONSTRAINT Usmerenje_FK FOREIGN KEY(sifra) REFERENCES StudentPostdiplomac,
    CONSTRAINT smer_CHK CHECK(smer IN('automatika','elektronika','energetika','merenja','mikroelektronika','racunarstvo','telekomunikacije'))
);

INSERT INTO Profesor VALUES(1,'Petar','Petrovic','1.1.2000','Direktor','Farmacija');
INSERT INTO Profesor VALUES(2,'Mitar','Stankovic','11.2.2002','Direktor','Informacione tehnologije');
INSERT INTO Profesor VALUES(3,'Marko','Markovic','1.3.2003','Direktor','Hemija');
INSERT INTO Profesor VALUES(4,'Stefan','Zivkovic','5.4.2004','Direktor','Agrokultura');

INSERT INTO Projekat VALUES(1,'Marko','2.9.2010','1.1.2015',50000);
INSERT INTO Projekat VALUES(2,'Stefan','4.5.2009','4.1.2015',60000);
INSERT INTO Projekat VALUES(3,'Dusan','6.12.2012','6.2.2017',70000);
INSERT INTO Projekat VALUES(4,'Zoran','5.9.2015','7.1.2012',80000);

INSERT INTO Radi VALUES(1,1);
INSERT INTO Radi VALUES(2,3);
INSERT INTO Radi VALUES(3,4);
INSERT INTO Radi VALUES(4,4);

INSERT INTO StudentPostdiplomac VALUES(1,'Mirko','Stojanovic','1.1.2001','automatika');
INSERT INTO StudentPostdiplomac VALUES(2,'Petar','Stankovic','1.1.2002','merenja');
INSERT INTO StudentPostdiplomac VALUES(3,'Marko','Stevanovic','1.1.2003','racunarstvo');
INSERT INTO StudentPostdiplomac VALUES(4,'Mirko','Petrovic','1.1.2004','energetika');

INSERT INTO Angazovan VALUES(1,2,'1.2.2002');
INSERT INTO Angazovan VALUES(2,3,'2.3.2003');
INSERT INTO Angazovan VALUES(3,4,'3.4.2004');
INSERT INTO Angazovan VALUES(4,4,'5.6.2006');

INSERT INTO Usmerenje VALUES(1,'automatika');
INSERT INTO Usmerenje VALUES(2,'elektronika');
INSERT INTO Usmerenje VALUES(3,'merenja');
INSERT INTO Usmerenje VALUES(4,'mikroelektronika');