/*
Created: 09.12.2019
Modified: 12.12.2019
Project: Schronisko
Model: Logical model
Company: Politechnika Warszawska
Author: Maciej Dabkowski,Kamil Stepien
Version: 1.0
Database: Oracle 19c
*/


-- Create sequences section -------------------------------------------------

CREATE SEQUENCE SchronsikaSeq
 INCREMENT BY 1
 START WITH 1
 NOMAXVALUE
 NOMINVALUE
 CACHE 20
/

-- Create tables section -------------------------------------------------

-- Table Schroniska

CREATE TABLE Schroniska(
  Id_Schroniska Integer NOT NULL,
  Nazwa Varchar2(30 ) NOT NULL,
  Miejscowosc Varchar2(30 ) NOT NULL,
  Ulica Varchar2(30 ) NOT NULL,
  Nr_Lokalu Varchar2(5 ) NOT NULL
)
/

-- Add keys for table Schroniska

ALTER TABLE Schroniska ADD CONSTRAINT Unique_Identifier1 PRIMARY KEY (Id_Schroniska)
/

-- Create triggers for table Schroniska

CREATE TRIGGER SchroniskaT1
  BEFORE INSERT
  ON Schroniska
  BEGIN
    select SchroniskaSeq.nextval into :new.Id_schronisko from dual;
END;
/

-- Table Pracownicy

CREATE TABLE Pracownicy(
  Id_pracownika Integer NOT NULL,
  Imie Varchar2(15 ) NOT NULL,
  Nazwisko Varchar2(30 ) NOT NULL,
  Plec Char(1 )
        CHECK (Plec IN ('K','M')),
  PESEL Varchar2(11 ) NOT NULL,
  Nr_Telefonu Varchar2(9 ) NOT NULL,
  Data_zatrudnienia Date NOT NULL,
  Id_Schroniska Integer NOT NULL,
  Id_stanowiska Integer NOT NULL
)
/

-- Create indexes for table Pracownicy

CREATE INDEX IX_Schronisko_zatrudnia ON Pracownicy (Id_Schroniska)
/

CREATE INDEX IX_Relationship2 ON Pracownicy (Id_stanowiska)
/

-- Add keys for table Pracownicy

ALTER TABLE Pracownicy ADD CONSTRAINT Unique_Identifier2 PRIMARY KEY (Id_pracownika)
/

-- Table Hycle

CREATE TABLE Hycle(
  Id_pracownika Integer NOT NULL,
  Prawo_jazdy Varchar2(5 ) NOT NULL,
  Data_waznosci_prawa_jazdy Date NOT NULL,
  Uprawnienia_do_łapania Char(1 ) NOT NULL
        CHECK (CzyJest IN ('1','0'))
)
/

-- Add keys for table Hycle

ALTER TABLE Hycle ADD CONSTRAINT Unique_Identifier3 PRIMARY KEY (Id_pracownika)
/

-- Table Opiekunowie

CREATE TABLE Opiekunowie(
  Id_pracownika Integer NOT NULL,
  Przyzwolenie_na_opieke Char(1 ) NOT NULL
        CHECK (Przyzwolenie_na_opieke IN ('1','0')),
  Stopień_zaawansowania Char(1 )
        CHECK (Stopien_zaawansowania IN ('A','B','I'))
)
/

-- Add keys for table Opiekunowie

ALTER TABLE Opiekunowie ADD CONSTRAINT Unique_Identifier4 PRIMARY KEY (Id_pracownika)
/

-- Table Weterynarze

CREATE TABLE Weterynarze(
  Id_pracownika Integer NOT NULL,
  Uprawnienia_do_leczenia Char(1 ) NOT NULL
        CHECK (CzyJest IN ('1','0')),
  Data_ważnosci_uprawnien Date,
  Opis_uprawnien Varchar2(800 )
)
/

-- Add keys for table Weterynarze

ALTER TABLE Weterynarze ADD CONSTRAINT Unique_Identifier5 PRIMARY KEY (Id_pracownika)
/

-- Table Wynagrodzenia

CREATE TABLE Wynagrodzenia(
  Id_Wynagrodzenia Integer NOT NULL,
  Data Date NOT NULL,
  Kwota Number(8,2) NOT NULL,
  Premia Number(8,2),
  Id_pracownika Integer NOT NULL
)
/

-- Create indexes for table Wynagrodzenia

CREATE INDEX IX_Pracownik_otrzymuje ON Wynagrodzenia (Id_pracownika)
/

-- Add keys for table Wynagrodzenia

ALTER TABLE Wynagrodzenia ADD CONSTRAINT Unique_Identifier6 PRIMARY KEY (Id_Wynagrodzenia)
/

-- Table Grafiki

CREATE TABLE Grafiki(
  Id_Grafiku Varchar2(5 ) NOT NULL,
  Data_od Date NOT NULL,
  Data_do Date,
  Id_pracownika Integer NOT NULL
)
/

-- Create indexes for table Grafiki

CREATE INDEX IX_Pracownik_posiada ON Grafiki (Id_pracownika)
/

-- Add keys for table Grafiki

ALTER TABLE Grafiki ADD CONSTRAINT Unique_Identifier7 PRIMARY KEY (Id_Grafiku)
/

-- Table Zwierzeta

CREATE TABLE Zwierzeta(
  Id_Zwierzecia Integer NOT NULL,
  Data_przyjecia Date NOT NULL,
  Imie Varchar2(20 ),
  Opis Varchar2(800 ),
  Id_Schroniska Integer NOT NULL,
  Id Rasa Integer,
  Id_Gatunku Integer NOT NULL,
  Id_klatki Integer NOT NULL
)
/

-- Create indexes for table Zwierzeta

CREATE INDEX IX_Schronisko_utrzymuje ON Zwierzeta (Id_Schroniska)
/

CREATE INDEX IX_Relationship4 ON Zwierzeta (Id Rasa)
/

CREATE INDEX IX_Relationship5 ON Zwierzeta (Id_Gatunku)
/

CREATE INDEX IX_Relationship6 ON Zwierzeta (Id_klatki)
/

-- Add keys for table Zwierzeta

ALTER TABLE Zwierzeta ADD CONSTRAINT Unique_Identifier8 PRIMARY KEY (Id_Zwierzecia)
/

-- Table Klatki

CREATE TABLE Klatki(
  Id_Klatki Integer NOT NULL,
  Rozmiar_klatki Char(1 ) NOT NULL,
  Kuweta Char(1 ) NOT NULL
        CHECK (CzyJest IN ('1','0')),
  Miska Char(1 ) NOT NULL
        CHECK (CzyJest IN ('1','0')),
  Legowisko Char(1 ) NOT NULL
        CHECK (CzyJest IN ('1','0')),
  Id_Schroniska Integer NOT NULL
)
/

-- Create indexes for table Klatki

CREATE INDEX IX_Schronisko_posiada ON Klatki (Id_Schroniska)
/

-- Add keys for table Klatki

ALTER TABLE Klatki ADD CONSTRAINT Unique_Identifier9 PRIMARY KEY (Id_Klatki)
/

-- Table Uslugi

CREATE TABLE Uslugi(
  Id_Uslugi Integer NOT NULL,
  Nazwa Varchar2(20 ) NOT NULL,
  Opis_uslugi Varchar2(800 ),
  Charakter_uslugi Char(1 ) NOT NULL
        CHECK (Charakter_uslugi IN ('D','P','K')),
  Id_Schroniska Integer NOT NULL
)
/

-- Create indexes for table Uslugi

CREATE INDEX IX_Schronisko_dostarcza ON Uslugi (Id_Schroniska)
/

-- Add keys for table Uslugi

ALTER TABLE Uslugi ADD CONSTRAINT Unique_Identifier10 PRIMARY KEY (Id_Uslugi)
/

-- Table Klienci

CREATE TABLE Klienci(
  Id_Klienta Integer NOT NULL,
  Imie Varchar2(15 ) NOT NULL,
  Nazwisko Varchar2(20 ) NOT NULL,
  Nr_dowodu Varchar2(30 ) NOT NULL,
  Zdolnosc_adopcyjna Char(1 ) NOT NULL
        CHECK (CzyJest IN ('1','0')),
  Id_Schroniska Integer NOT NULL
)
/

-- Create indexes for table Klienci

CREATE INDEX IX_Schronisko ON Klienci (Id_Schroniska)
/

-- Add keys for table Klienci

ALTER TABLE Klienci ADD CONSTRAINT Unique_Identifier11 PRIMARY KEY (Id_Klienta)
/

-- Table Badania

CREATE TABLE Badania(
  Id_Badania Integer NOT NULL,
  Data_badania Date NOT NULL,
  Data_waznosci Date,
  Status_badania Char(1 ) NOT NULL,
  Szczegoly_badania Varchar2(800 ),
  Id_zwierzecia Integer NOT NULL
)
/

-- Create indexes for table Badania

CREATE INDEX IX_Zwierze_jest_badane ON Badania (Id_zwierzecia)
/

-- Add keys for table Badania

ALTER TABLE Badania ADD CONSTRAINT Unique_Identifier12 PRIMARY KEY (Id_Badania)
/

-- Table Klient1

CREATE TABLE Klient1(
  Id_klienta Integer NOT NULL,
  Imie Varchar2(20 ),
  Nazwisko Varchar2(30 ),
  Nr_dowodu Varchar2(30 )
)
/

-- Add keys for table Klient1

ALTER TABLE Klient1 ADD CONSTRAINT Unique_Identifier13 PRIMARY KEY (Id_klienta)
/

-- Table Szkolenia

CREATE TABLE Szkolenia(
  Id_Szkolenia Integer NOT NULL,
  Nazwa_szkolenia Varchar2(30 ) NOT NULL,
  Opis_szkolenia Varchar2(800 ),
  Data_odbycia_szkolenia Date NOT NULL,
  Data_waznosci_szkolenia Date,
  Id_pracownika Integer NOT NULL
)
/

-- Create indexes for table Szkolenia

CREATE INDEX IX_Pracownik_odbywa ON Szkolenia (Id_pracownika)
/

-- Add keys for table Szkolenia

ALTER TABLE Szkolenia ADD CONSTRAINT Unique_Identifier14 PRIMARY KEY (Id_Szkolenia)
/

-- Table Wlasciciele

CREATE TABLE Wlasciciele(
  Id_Wlasciciel Integer NOT NULL,
  Imie Varchar2(15 ) NOT NULL,
  Nazwisko Varchar2(30 ) NOT NULL,
  Id_Schroniska Integer NOT NULL
)
/

-- Create indexes for table Wlasciciele

CREATE INDEX IX_Relationship1 ON Wlasciciele (Id_Schroniska)
/

-- Add keys for table Wlasciciele

ALTER TABLE Wlasciciele ADD CONSTRAINT PK_Wlasciciele PRIMARY KEY (Id_Wlasciciel)
/

-- Table Stanowiska

CREATE TABLE Stanowiska(
  Id_Stanowiska Integer NOT NULL,
  Nazwa Varchar2(30 ) NOT NULL,
  Opis Varchar2(400 )
)
/

-- Add keys for table Stanowiska

ALTER TABLE Stanowiska ADD CONSTRAINT PK_Stanowiska PRIMARY KEY (Id_Stanowiska)
/

-- Table Gatunki

CREATE TABLE Gatunki(
  Id_Gatunku Integer NOT NULL,
  Nazwa Varchar2(20 ) NOT NULL,
  Opis Varchar2(400 )
)
/

-- Add keys for table Gatunki

ALTER TABLE Gatunki ADD CONSTRAINT PK_Gatunki PRIMARY KEY (Id_Gatunku)
/

ALTER TABLE Gatunki ADD CONSTRAINT Nazwa UNIQUE (Nazwa)
/

-- Table Rasy

CREATE TABLE Rasy(
  Id_Rasa Integer NOT NULL,
  Nazwa Varchar2(30 ) NOT NULL,
  Opis Varchar2(400 ),
  Id_Gatunku Integer
)
/

-- Create indexes for table Rasy

CREATE INDEX IX_Relationship3 ON Rasy (Id_Gatunku)
/

-- Add keys for table Rasy

ALTER TABLE Rasy ADD CONSTRAINT PK_Rasy PRIMARY KEY (Id_Rasa)
/

-- Table Zakupy_uslug

CREATE TABLE Zakupy_uslug(
  Id_Klienta Integer NOT NULL,
  Id_Uslugi Integer NOT NULL,
  Data Char(20 ) NOT NULL
)
/

-- Add keys for table Zakupy_uslug

ALTER TABLE Zakupy_uslug ADD CONSTRAINT PK_Zakupy_uslug PRIMARY KEY (Id_Klienta,Id_Uslugi,Data)
/

-- Trigger for sequence SchronsikaSeq for column Id_Schroniska in table Schroniska ---------
CREATE OR REPLACE TRIGGER ts_Schroniska_SchronsikaSeq BEFORE INSERT
ON Schroniska FOR EACH ROW
BEGIN
  :new.Id_Schroniska := SchronsikaSeq.nextval;
END;
/
CREATE OR REPLACE TRIGGER tsu_Schroniska_SchronsikaSeq AFTER UPDATE OF Id_Schroniska
ON Schroniska FOR EACH ROW
BEGIN
  RAISE_APPLICATION_ERROR(-20010,'Cannot update column Id_Schroniska in table Schroniska as it uses sequence.');
END;
/


-- Create foreign keys (relationships) section -------------------------------------------------

ALTER TABLE Wynagrodzenia ADD CONSTRAINT Pracownik_otrzymuje FOREIGN KEY (Id_pracownika) REFERENCES Pracownicy (Id_pracownika)
/



ALTER TABLE Grafiki ADD CONSTRAINT Pracownik_posiada FOREIGN KEY (Id_pracownika) REFERENCES Pracownicy (Id_pracownika)
/



ALTER TABLE Klatki ADD CONSTRAINT Schronisko_posiada FOREIGN KEY (Id_Schroniska) REFERENCES Schroniska (Id_Schroniska)
/



ALTER TABLE Pracownicy ADD CONSTRAINT Schronisko_zatrudnia FOREIGN KEY (Id_Schroniska) REFERENCES Schroniska (Id_Schroniska)
/



ALTER TABLE Zwierzeta ADD CONSTRAINT Schronisko_utrzymuje FOREIGN KEY (Id_Schroniska) REFERENCES Schroniska (Id_Schroniska)
/



ALTER TABLE Uslugi ADD CONSTRAINT Schronisko_dostarcza FOREIGN KEY (Id_Schroniska) REFERENCES Schroniska (Id_Schroniska)
/



ALTER TABLE Klienci ADD CONSTRAINT Schronisko_ma FOREIGN KEY (Id_Schroniska) REFERENCES Schroniska (Id_Schroniska)
/



ALTER TABLE Badania ADD CONSTRAINT Zwierze_jest_badane FOREIGN KEY (Id_zwierzecia) REFERENCES Zwierzeta (Id_Zwierzecia)
/



ALTER TABLE Szkolenia ADD CONSTRAINT Pracownik_odbywa FOREIGN KEY (Id_pracownika) REFERENCES Pracownicy (Id_pracownika)
/



ALTER TABLE Wlasciciele ADD CONSTRAINT Ma_wlasciciela FOREIGN KEY (Id_Schroniska) REFERENCES Schroniska (Id_Schroniska)
/



ALTER TABLE Pracownicy ADD CONSTRAINT Ma_stanowisko FOREIGN KEY (Id_stanowiska) REFERENCES Stanowiska (Id_Stanowiska)
/



ALTER TABLE Rasy ADD CONSTRAINT Ma_rase FOREIGN KEY (Id_Gatunku) REFERENCES Gatunki (Id_Gatunku)
/



ALTER TABLE Zwierzeta ADD CONSTRAINT Jest_rasy FOREIGN KEY (Id Rasa) REFERENCES Rasy (Id_Rasa)
/



ALTER TABLE Zwierzeta ADD CONSTRAINT Jest_gatunku FOREIGN KEY (Id_Gatunku) REFERENCES Gatunki (Id_Gatunku)
/



ALTER TABLE Zwierzeta ADD CONSTRAINT Jest_przypisane FOREIGN KEY (Id_klatki) REFERENCES Klatki (Id_Klatki)
/



ALTER TABLE Zakupy_uslug ADD CONSTRAINT Klient_kupuje FOREIGN KEY (Id_Klienta) REFERENCES Klienci (Id_Klienta)
/



ALTER TABLE Zakupy_uslug ADD CONSTRAINT Jest_oferowna FOREIGN KEY (Id_Uslugi) REFERENCES Uslugi (Id_Uslugi)
/