/*
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 SEQUENCE SchronsikaSeq
 INCREMENT BY 1
 START WITH 1
 NOMAXVALUE
 NOMINVALUE
 CACHE 20
;
-- 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
  FOR EACH ROW
  BEGIN
    select SchronsikaSeq.nextval into :new.Id_Schroniska 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_lapania Char(1 ) NOT NULL
        CHECK (Uprawnienia_do_lapania 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')),
  Stopien_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 (Uprawnienia_do_leczenia 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 (Kuweta IN ('1','0')),
  Miska Char(1 ) NOT NULL
        CHECK (Miska IN ('1','0')),
  Legowisko Char(1 ) NOT NULL
        CHECK (Legowisko 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 (Zdolnosc_adopcyjna 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);