model.sql

drop table Wynajem;
drop table Wypozyczenia;
drop table Sale cascade constraints;
drop table Uzytkownicy;
drop table Egzemplarze;
drop table Polki;
drop table Regaly;
drop table Autorstwo;
drop table Publikacje;
drop table Autorzy;

CREATE TABLE Autorzy(
   id_autora NUMBER(6) GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
   nazwisko VARCHAR(50) check(length(nazwisko)>0) NOT NULL,
   imie VARCHAR(50) check(length(imie)>0),
   tytul_naukowy VARCHAR(50) check(length(tytul_naukowy)>0)
);

create TABLE Publikacje (
   isbn varchar(13) PRIMARY KEY check(length(isbn)=13),
   typ VARCHAR(1) NOT NULL,
   tytul VARCHAR(100) NOT NULL CHECK(LENGTH(tytul) > 0),
   gatunek VARCHAR(100),
   temat VARCHAR(50),
   id_czasopisma varchar(13)  constraint fk_czasopismo_artykul references Publikacje(isbn),
   CHECK(typ in ('A', 'C', 'K')) -- Artykuł/Czasopismo/Książka
);

CREATE TABLE Autorstwo(
   id_autora NOT NULL REFERENCES Autorzy(id_autora),
   isbn NOT NULL REFERENCES Publikacje(isbn)
);

CREATE TABLE Regaly (
  kod_regalu VARCHAR2(3) PRIMARY KEY
);

CREATE TABLE Polki(
  nr_polki NUMBER(3, 0) PRIMARY KEY,
  kod_regalu VARCHAR2(3) NOT NULL REFERENCES Regaly(kod_regalu)
);

CREATE TABLE Egzemplarze(
  id_egzemplarza NUMBER(6, 0) GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
  isbn varchar(13) NOT NULL REFERENCES Publikacje(isbn),
  czy_wypozyczony NUMBER(1,0) DEFAULT ON NULL 0 NOT NULL,
  nr_polki NUMBER(3, 0) REFERENCES Polki(nr_polki),
  CHECK(id_egzemplarza > 0),
  CHECK(czy_wypozyczony in (0, 1))
);

CREATE TABLE Uzytkownicy(
  id_uzytkownika varchar2(20) PRIMARY KEY,
  imie VARCHAR2(20),
  nazwisko VARCHAR2(20)
);


CREATE TABLE Sale (
  id_sali NUMBER(3, 0) PRIMARY KEY,
  liczba_miejsc NUMBER(3, 0),
  jest_tablica NUMBER(1, 0),
  CHECK(jest_tablica in (0, 1))
);

CREATE TABLE Wypozyczenia(
   isbn VARCHAR(13) NOT NULL REFERENCES Publikacje(isbn),
   id_egzemplarza NOT NULL REFERENCES Egzemplarze(id_egzemplarza),
   id_uzytkownika NOT NULL REFERENCES Uzytkownicy(id_uzytkownika),
   data_startu DATE,
   deadline DATE NOT NULL,
   data_oddania DATE,
   wartosc_kary NUMBER(5,2) CHECK(wartosc_kary >= 0),
   czy_splacona NUMBER(1,0) CHECK(czy_splacona in (0, 1)),
   CHECK(deadline >= data_startu),
   CHECK(data_oddania >= data_startu),
   PRIMARY KEY (isbn, id_egzemplarza, id_uzytkownika, data_startu)
);

CREATE TABLE Wynajem(
   data DATE,
-- czas w minutach od północy
   godz_rozpoczecia NUMBER(4,0) CHECK(godz_rozpoczecia BETWEEN 0 AND 1439),
   deadline NUMBER(4,0) CHECK(deadline BETWEEN 0 AND 1439),
   godz_zakonczenia NUMBER(4,0) CHECK(godz_zakonczenia BETWEEN 0 AND 1439),
   id_uzytkownika NOT NULL REFERENCES Uzytkownicy(id_uzytkownika),
   id_sali NOT NULL REFERENCES Sale(id_sali),
   CHECK(deadline >= godz_rozpoczecia),
   CHECK(godz_zakonczenia >= godz_rozpoczecia),

   PRIMARY KEY(data, godz_rozpoczecia, id_uzytkownika, id_sali)
);


CREATE BITMAP INDEX eg_po_regal_bmp_idx
ON
    Regaly(kod_regalu)
FROM Egzemplarze e, Polki p, Regaly r
WHERE e.nr_polki = p.nr_polki AND p.kod_regalu = r.kod_regalu;


--Sort
CREATE INDEX au_nazwisko_idx
ON Autorzy(nazwisko DESC);
--Foreing Key
CREATE INDEX eg_isbn_idx ON Egzemplarze(isbn);
CREATE INDEX eg_numer_polki_idx ON Egzemplarze(nr_polki);

CREATE INDEX wyp_isbn_idx ON Wypozyczenia(isbn);
CREATE INDEX wyp_id_uzytkownika_idx ON Wypozyczenia(id_uzytkownika);

CREATE  index  wyn_id_uzytkownika_idx ON WYNAJEM(id_uzytkownika);
CREATE  index  wyn_id_sali_idx ON WYNAJEM(id_sali);

CREATE INDEX pu_isbn_idx ON Publikacje(isbn);
CREATE INDEX po_kod_regulu_idx ON Polki (kod_regalu);
CREATE BITMAP INDEX pu_typ_bmp_idx ON Publikacje(typ);
--Laczenie
CREATE BITMAP INDEX eg_pub_idx
ON Publikacje(tytul)
FROM Egzemplarze e, Publikacje p
WHERE e.isbn = p.isbn;