LogicalandPhysicalDesign: kreiranje_v2.sql

drop table if exists LTel_Broj;
drop table if exists ITel_Broj;
drop table if exists Klasificira;
drop table if exists Napisal;
drop table if exists Rezervacija;
drop table if exists Pozajmica;
drop table if exists Dodava;
drop table if exists InstancaKniga;
drop table if exists Kategorija;
drop table if exists Ocena;
drop table if exists Avtor;
drop table if exists Knigi;
drop table if exists Izdavac;
drop table if exists Bibliotekar;
drop table if exists Clen;
drop table if exists Lugje;
drop table if exists Biblioteka;







create table Biblioteka
(
        bibliotekaID integer primary key not null,
        bAdresa varchar (100) not null,
        bIme varchar (100) not null
);


create table Lugje
(
        lugjeID integer primary key not null,
        ime varchar (100) not null,
        prezime varchar (100) not null,
        adresa varchar (100),
        email varchar (100) not null
);

create table Clen
(
        clenskiBr integer not null references Lugje(lugjeID),
        datumZaclenuvanje date not null,
        passwordClen varchar (100) not null,
        constraint pk_clen primary key(clenskiBr)
);

create table Bibliotekar
(
        bibliotekarID integer not null references Lugje(lugjeID),
        bibliotekaID integer not null references Biblioteka(bibliotekaID),
        constraint pk_bibliotekar primary key (bibliotekarID)
);

create table LTel_Broj
(
        lugjeID integer references Lugje(lugjeID),
        lTel_Broj varchar (100) not null,
        constraint pk_ltel_broj primary key(lugjeID, lTel_Broj)
);


create table Izdavac
(
        izdavacID integer primary key not null,
        iAdresa varchar (100),
        iIme varchar (100) not null,
        iEmail varchar (100) not null
);





create table ITel_Broj
(
        izdavacID integer not null references Izdavac(izdavacID),
        lTel_Broj varchar (100) not null,
        constraint pk_itel_broj primary key(izdavacID, lTel_Broj)
);


create table Knigi
(
        knigaID integer primary key not null,
        naslov varchar (100) not null,
        kFormat varchar (100) not null,
        kOpis varchar (1000) not null,
        izdavacID integer not null references Izdavac(izdavacID)
        
        
);


create table Dodava
(
        bibliotekarID integer not null references Bibliotekar(bibliotekarID),
        knigaID integer not null references Knigi(knigaID),
        constraint pk_dodava primary key (bibliotekarID, knigaID)
);


create table Avtor
(
        avtorID integer primary key not null,
        aEmail varchar (100) not null,
        aIme varchar (100) not null
);


create table Napisal
(
        avtorID integer not null references Avtor(avtorID),
        knigaID integer not null references Knigi(knigaID),
        constraint pk_napisal primary key (avtorID, knigaID)
);

create table Kategorija
(
        kategorijaID integer primary key not null,
        naslovKategorija varchar (100) not null
);

create table Klasificira
(
        kategorijaID integer not null references Kategorija(kategorijaID),
        knigaID integer not null references Knigi(knigaID),
        constraint pk_ima primary key (kategorijaID, knigaID)
);

create table Ocena
(
        ocenaID integer primary key not null,
        ocenka numeric CHECK(ocenka >= 0 AND ocenka <= 10),
        komentar varchar (1000),
        avtorID integer  not null references Avtor(avtorID),
        knigaID integer not null references Knigi(knigaID),
        clenskiBr integer not null references Clen(clenskiBr)
);

create table InstancaKniga
(

        knigaID integer not null references Knigi(knigaID),
        seriskiBr integer not null,
        iStatus varchar(1000) not null,
        bibliotekaID integer not null references Biblioteka(bibliotekaID),
        constraint pk_instanca_kniga primary key(knigaID, seriskiBr)
);


create table Pozajmica
(
        pozajmicaID integer primary key not null,
        pStatus varchar(1000) not null,
        pocetokDatum date not null,
        krajDatum date not null,
        knigaID integer not null,
        seriskiBr integer not null,
        constraint validenIntervalDatumi check (pocetokDatum < krajDatum),
        foreign key(knigaID, seriskiBr) references InstancaKniga(knigaID, seriskiBr)
);


create table Rezervacija
(
        rezervacijaID integer primary key not null,
        rStatus varchar(1000) not null,
        denNaRezervacija date not null,
        pozajmicaID integer not null references Pozajmica(pozajmicaID),
        bibliotekarID integer not null references Bibliotekar(bibliotekarID),
        knigaID integer not null references Knigi(knigaID),
        clenskiBr integer not null references Clen(clenskiBr)
);