---------------------------------DROP------------------------------------------------
DROP TABLE Zlecenia CASCADE CONSTRAINTS;
DROP TABLE Uslugi CASCADE CONSTRAINTS;
DROP TABLE Pracownicy CASCADE CONSTRAINTS;
DROP TABLE Stanowiska CASCADE CONSTRAINTS;
DROP TABLE Klienci CASCADE CONSTRAINTS;
DROP TABLE Osoby CASCADE CONSTRAINTS;
DROP TABLE Adresy CASCADE CONSTRAINTS;

--------------------------------CREATE-----------------------------------------------
CREATE TABLE Adresy (
Id_adresu NUMBER CONSTRAINT adres_pk PRIMARY KEY,
Miasto VARCHAR2(25) NOT NULL,
Ulica VARCHAR2(30) NOT NULL,
Nr VARCHAR2(10) NOT NULL
);

CREATE TABLE Osoby (
Id_osoby NUMBER CONSTRAINT osoby_pk PRIMARY KEY,
Imie VARCHAR2(15) NOT NULL,
Nazwisko VARCHAR2(30) NOT NULL,
Wiek NUMBER NOT NULL CONSTRAINT ch_wiek CHECK((Wiek>=0) AND (Wiek<=125)),
Stan_cywilny VARCHAR2(12) NOT NULL,
Telefon VARCHAR2(20),
Pesel CHAR(11) NOT NULL CONSTRAINT osoba_uni UNIQUE,
Id_adresu NUMBER NOT NULL,
CONSTRAINT os_ad_fk FOREIGN KEY (Id_adresu) REFERENCES Adresy(Id_adresu)
);

CREATE TABLE Klienci (
Id_klienta NUMBER CONSTRAINT klient_pk PRIMARY KEY,
Id_osoby NUMBER NOT NULL CONSTRAINT kl_unique UNIQUE,
Znizka NUMBER,
CONSTRAINT kl_os_fk FOREIGN KEY (Id_osoby) REFERENCES Osoby(Id_osoby)
);

CREATE TABLE Stanowiska (
Id_stanowiska NUMBER CONSTRAINT stanowisko_pk PRIMARY KEY,
Nazwa VARCHAR2(20) NOT NULL,
Opis VARCHAR2(500)
);

CREATE TABLE Pracownicy (
Id_pracownika NUMBER CONSTRAINT pracownik_pk PRIMARY KEY,
Id_osoby NUMBER NOT NULL CONSTRAINT pr_unique UNIQUE,
Id_stanowiska NUMBER NOT NULL,
Staz NUMBER NOT NULL CONSTRAINT ch_staz CHECK((Staz>=0) AND (Staz<=45)),
Pensja_br NUMBER NOT NULL CONSTRAINT pen_staz CHECK(Pensja_br>=1226),
CONSTRAINT pr_os_fk FOREIGN KEY (Id_osoby) REFERENCES Osoby(Id_osoby),
CONSTRAINT pr_st_fk FOREIGN KEY (Id_stanowiska) REFERENCES Stanowiska(Id_stanowiska)
);

CREATE TABLE Uslugi (
Id_uslugi NUMBER CONSTRAINT uslugi_pk PRIMARY KEY,
Opis VARCHAR2(500),
Cena NUMBER NOT NULL
);

CREATE TABLE Zlecenia (
Id_zlecenia NUMBER CONSTRAINT zlecenie_pk PRIMARY KEY,
Id_uslugi NUMBER NOT NULL,
Id_klienta NUMBER NOT NULL,
Id_pracownika NUMBER NOT NULL,
CONSTRAINT zl_us_fk FOREIGN KEY (Id_uslugi) REFERENCES Uslugi(Id_uslugi),
CONSTRAINT zl_kl_fk FOREIGN KEY (Id_uslugi) REFERENCES Klienci(Id_klienta),
CONSTRAINT zl_pr_fk FOREIGN KEY (Id_uslugi) REFERENCES Pracownicy(Id_pracownika)
);