RelationModel: kreiranje.2.sql

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS Objekt;
DROP TABLE IF EXISTS Vo_Izgradba;
DROP TABLE IF EXISTS Gotov;
DROP TABLE IF EXISTS Nadzor;
DROP TABLE IF EXISTS Podizveduvac;
DROP TABLE IF EXISTS Rabotnik;
DROP TABLE IF EXISTS Dobavuvac;
DROP TABLE IF EXISTS Nabavka;
DROP TABLE IF EXISTS Korisnik;
DROP TABLE IF EXISTS Sopstvenik;
DROP TABLE IF EXISTS Raboti_Na;
DROP TABLE IF EXISTS Raboti_Za;
DROP TABLE IF EXISTS Nadgleduva;
DROP TABLE IF EXISTS Pobaruva;
DROP TABLE IF EXISTS Nameneta_Za;
DROP TABLE IF EXISTS Odobruva;
DROP TABLE IF EXISTS Oddrzuva;
DROP TABLE IF EXISTS Ispolnuva;



CREATE TABLE Objekt (
        Objekt_ID INT AUTO_INCREMENT UNIQUE NOT NULL,
        Tip VARCHAR(100),
        Adresa VARCHAR(100),
        CONSTRAINT Objekt PRIMARY KEY (Objekt_ID)
);

CREATE TABLE Vo_Izgradba (
        Objekt_ID INT  NOT NULL UNIQUE,
        Budzet FLOAT,
        CONSTRAINT Vo_Izgradba FOREIGN KEY (Objekt_ID) REFERENCES Objekt (Objekt_ID)
);

CREATE TABLE Gotov (
        Objekt_ID INT AUTO_INCREMENT NOT NULL UNIQUE,
        CONSTRAINT Gotov FOREIGN KEY (Objekt_ID) REFERENCES Objekt (Objekt_ID)
);

CREATE TABLE Nadzor (
    UID INT NOT NULL UNIQUE,
        Nadzor_Ime VARCHAR(50) NOT NULL,
        Plata INT,
        Kvalifikacii VARCHAR(50),
        Firma VARCHAR(50),
    CONSTRAINT Nadzor PRIMARY KEY (UID),
        CONSTRAINT Nadzor FOREIGN KEY (UID) REFERENCES Korisnik (UID)
);

CREATE TABLE Podizveduvac (
        Podizveduvac_Ime VARCHAR(50) NOT NULL UNIQUE,
        Struka VARCHAR(50) NOT NULL,
        Br_vraboteni INT NOT NULL,
        Saatnica FLOAT NOT NULL,
        UID INT NOT NULL UNIQUE,
        CONSTRAINT Podizveduvac PRIMARY KEY (UID),
        CONSTRAINT Podizveduvac FOREIGN KEY (UID) REFERENCES Korisnik (UID)
);

CREATE TABLE Rabotnik (
        Maticen_br VARCHAR(13) NOT NULL UNIQUE,
        Ime VARCHAR(50) NOT NULL,
        Seniornost VARCHAR(50) NOT NULL,
        CONSTRAINT Rabotnik PRIMARY KEY (Maticen_br)
);

CREATE TABLE Dobavuvac (
        Dobavuvac_Ime VARCHAR(50) NOT NULL UNIQUE,
        Klasa VARCHAR(50) NOT NULL,
        Tip VARCHAR(50) NOT NULL,
        Poteklo VARCHAR(50) NOT NULL,
        CONSTRAINT Dobavuvac PRIMARY KEY (Dobavuvac_Ime)
);

CREATE TABLE Nabavka (
        Nabavka_ID INT AUTO_INCREMENT NOT NULL UNIQUE,
        Datum DATE NOT NULL,
        Cena FLOAT NOT NULL,
        Kolicina FLOAT NOT NULL,
        Predmet VARCHAR(100) NOT NULL,
        CONSTRAINT Nabavka PRIMARY KEY (Nabavka_ID)
);

CREATE TABLE Korisnik(
        UID INT AUTO_INCREMENT NOT NULL UNIQUE,
        Username VARCHAR(50) NOT NULL UNIQUE,
        Password VARCHAR(72) NOT NULL,
        Privilegii INT NOT NULL,
        CONSTRAINT Korisnik PRIMARY KEY (UID)
);

CREATE TABLE Sopstvenik(
        UID INT AUTO_INCREMENT NOT NULL UNIQUE,
        CONSTRAINT Sopstvenik FOREIGN KEY (UID) REFERENCES Korisnik (UID)
);

CREATE TABLE Raboti_Na (
    Objekt_ID INT,
    UID_p INT NOT NULL,
    Od_vreme DATE,
    Do_vreme DATE,
    PRIMARY KEY (Objekt_ID, UID_p),
    FOREIGN KEY (Objekt_ID) REFERENCES Objekt(Objekt_ID),
    FOREIGN KEY (UID_p) REFERENCES Podizveduvac(UID)
);

CREATE TABLE Raboti_Za (
    Maticen_br VARCHAR(13),
    UID_p INT NOT NULL,
    PRIMARY KEY (Maticen_br, UID_p),
    FOREIGN KEY (Maticen_br) REFERENCES Rabotnik(Maticen_br),
    FOREIGN KEY (UID_p) REFERENCES Podizveduvac(UID)
);

CREATE TABLE Nadgleduva (
    Objekt_ID INT,
    UID_n INT NOT NULL,
    PRIMARY KEY (Objekt_ID, UID_n),
    FOREIGN KEY (Objekt_ID) REFERENCES Objekt(Objekt_ID),
    FOREIGN KEY (UID_n) REFERENCES Nadzor(UID)
);

CREATE TABLE Pobaruva (
    Nabavka_ID INT,
    UID_p INT NOT NULL,
    PRIMARY KEY (Nabavka_ID, UID_p),
    FOREIGN KEY (Nabavka_ID) REFERENCES Nabavka(Nabavka_ID),
    FOREIGN KEY (UID_p) REFERENCES Podizveduvac(UID)
);

CREATE TABLE Odobruva (
    Nabavka_ID INT,
    UID_n INT NOT NULL,
    Datum_Odobruvanje DATE,
    PRIMARY KEY (Nabavka_ID, UID_n),
    FOREIGN KEY (Nabavka_ID) REFERENCES Nabavka(Nabavka_ID),
    FOREIGN KEY (UID_n) REFERENCES Nadzor(UID)
);

CREATE TABLE Nameneta_Za (
    Nabavka_ID INT,
    Objekt_ID INT,
    PRIMARY KEY (Nabavka_ID, Objekt_ID),
    FOREIGN KEY (Nabavka_ID) REFERENCES Nabavka(Nabavka_ID),
    FOREIGN KEY (Objekt_ID) REFERENCES Objekt(Objekt_ID)
);

CREATE TABLE Ispolnuva (
    Nabavka_ID INT,
    Dobavuvac_Ime VARCHAR(50),
    PRIMARY KEY (Nabavka_ID, Dobavuvac_Ime),
    FOREIGN KEY (Nabavka_ID) REFERENCES Nabavka(Nabavka_ID),
    FOREIGN KEY (Dobavuvac_Ime) REFERENCES Dobavuvac(Dobavuvac_Ime)
);

CREATE TABLE Oddrzuva (
    Objekt_ID INT,
    UID_p INT NOT NULL,
    Od_vreme DATE,
    Do_vreme DATE,
    PRIMARY KEY (Objekt_ID, UID_p, Od_vreme),
    FOREIGN KEY (Objekt_ID) REFERENCES Objekt(Objekt_ID),
    FOREIGN KEY (UID_p) REFERENCES Podizveduvac(UID)
);

SET FOREIGN_KEY_CHECKS = 1;