CREATE TABLE Skolarina(
    brTrans CHAR(11) PRIMARY KEY,
    ECTSbroj INT,
    iznos DECIMAL(5,3),
    CONSTRAINT fkSkolarina FOREIGN KEY(OIB) REFERENCES Student(OIB)
);

CREATE TABLE Student(
    OIB CHAR(13) PRIMARY KEY,
    ime VARCHAR(11),
    prezime VARCHAR(11),
    dat_rod DATETIME,
    smjer VARCHAR(50),
    god_upis DATETIME,
    spol CHAR(1) DEFAULT 'F',
    status VARCHAR(12),
    ukupna_uplata  DECIMAL(7,5)
    CONSTRAINT chk_status CHECK(status in('redovan', 'izvanredan')),
);

CREATE TABLE Kolegij(
    SifraKol CHAR(6) PRIMARY KEY,
    naziv VARCHAR(50),
    ECTS INT,
    nositelj VARCHAR(50)
);

CREATE TABLE Ispit(
    datum DATETIME,
    ocjena INT,
    CONSTRAINT pk PRIMARY KEY(SifraKol, OIB),
    CONSTRAINT fkik FOREIGN KEY(SifraKol) REFERENCES Kolegij(SifraKol),
    CONSTRAINT fkis FOREIGN KEY(OIB) REFERENCES Student(OIB),
);

CREATE TABLE Profesor(
    mbr CHAR(6) PRIMARY KEY,
    ime VARCHAR(30),
    prezime VARCHAR(30),
    titula VARCHAR(10),
    katedra VARCHAR(50)
);

CREATE TABLE Predaje(
    mbr CHAR(6),
    SifraKol CHAR(6),
    CONSTRAINT pkp Predaje PRIMARY KEY(mbr, SifraKol),
    CONSTRAINT fkm FOREIGN KEY(mbr) REFERENCES Profesor(mbr),
    CONSTRAINT fkk FOREIGN KEY(SifraKol) REFERENCES Kolegij(SifraKol)
);