RelationalDesign: kreiranje_v1.2.sql

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

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

CREATE TABLE project.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 project.Korisnik(Id)
);

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

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

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

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

CREATE TABLE project.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 project.Manager(korisnik_id)
);

CREATE TABLE project.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 project.Potrosuvac(korisnik_id),
FOREIGN KEY (restoran_id) references project.Restoran(Id),
FOREIGN KEY (vozac_id) references project.Vozac(Id),
FOREIGN KEY (admin_id) references project.Administrator(Id)
);

CREATE TABLE project.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 project.Potrosuvac(korisnik_id),
FOREIGN KEY (naracka_id) references project.Naracka(Id)
);

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

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