RelationalDesign: kreiranje_v3.sql

set search_path = project;

drop table if exists Naracka_sodrzi_MenuItem cascade;
drop table if exists MenuItem cascade;
drop table if exists Naplata cascade;
drop table if exists Naracka cascade;
drop table if exists Restoran cascade;
drop table if exists Manager cascade;
drop table if exists Administrator cascade;
drop table if exists Vozac cascade;
drop table if exists Vozilo cascade;
drop table if exists Potrosuvac cascade;
drop table if exists Korisnik cascade;

drop schema if exists project;

create schema project;
set search_path = project;

CREATE TABLE Korisnik (
Id INT PRIMARY KEY,
Email VARCHAR(255) NOT NULL,
Username VARCHAR(50) NOT NULL,
Pword VARCHAR(50) NOT NULL
);

CREATE TABLE Potrosuvac (
korisnik_id INT PRIMARY KEY,
Address VARCHAR(255) NOT NULL,
br_telefon VARCHAR(255) NOT NULL,
constraint fk_Korisnik foreign key (korisnik_id) references Korisnik(Id)
);

CREATE TABLE Vozilo (
Id INT PRIMARY KEY,
tip VARCHAR(255),
br_registracija VARCHAR(255) NOT NULL
);

CREATE TABLE Vozac (
korisnik_id INT PRIMARY KEY,
vozilo_id INT NOT NULL,
constraint fk_Korisnik foreign key (korisnik_id) references Korisnik(Id),
constraint fk_Vozilo foreign key (vozilo_id) references Vozilo(Id)
);

CREATE TABLE Administrator (
korisnik_id INT PRIMARY KEY,
constraint fk_Korisnik foreign key (korisnik_id) references Korisnik(Id)
);

CREATE TABLE Manager (
korisnik_id INT PRIMARY KEY,
constraint fk_Korisnik foreign key (korisnik_id) references Korisnik(Id)
);

CREATE TABLE Restoran (
Id INT PRIMARY KEY,
ime VARCHAR(255) NOT NULL,
lokacija VARCHAR(255) NOT NULL,
rabotno_vreme VARCHAR(255) NOT NULL,
manager_id INT NOT NULL,
FOREIGN KEY (manager_id) references Manager(korisnik_id)
);

CREATE TABLE Naracka (
Id INT PRIMARY KEY,
Datum DATE,
potrosuvac_id INT NOT NULL,
restoran_id INT NOT NULL,
vozac_id INT NOT NULL,
admin_id INT NOT NULL,
FOREIGN KEY (potrosuvac_id) references Potrosuvac(korisnik_id),
FOREIGN KEY (restoran_id) references Restoran(Id),
FOREIGN KEY (vozac_id) references Vozac(korisnik_id),
FOREIGN KEY (admin_id) references Administrator(korisnik_id)
);

CREATE TABLE Naplata (
Id INT PRIMARY KEY,
Iznos INT NOT NULL,
nacin_na_plakjane VARCHAR(255),
potrosuvac_id INT NOT NULL,
naracka_id INT NOT NULL,
FOREIGN KEY (potrosuvac_id) references Potrosuvac(korisnik_id),
FOREIGN KEY (naracka_id) references Naracka(Id)
);

CREATE TABLE MenuItem (
Id INT PRIMARY KEY,
ime VARCHAR(255) NOT NULL,
cena INT NOT NULL,
restoran_id INT NOT NULL,
FOREIGN KEY (restoran_id) references Restoran(Id)
);

CREATE TABLE Naracka_sodrzi_MenuItem (
naracka_id INT NOT NULL,
menuItem_id INT NOT NULL,
FOREIGN KEY (naracka_id) references Naracka(Id),
FOREIGN KEY (menuItem_id) references MenuItem(Id)
);