DROP TABLE PRACOWNICY;
DROP TABLE ZESPOLY;
DROP TABLE ETATY;
 
CREATE TABLE ZESPOLY
	(ID_ZESP NUMBER(2) CONSTRAINT PK_ZESP PRIMARY KEY,
	NAZWA VARCHAR2(20),
	ADRES VARCHAR2(20) );

CREATE TABLE ETATY
      ( NAZWA VARCHAR2(10) CONSTRAINT PK_ETAT PRIMARY KEY,
	PLACA_MIN NUMBER(6,2),
	PLACA_MAX NUMBER(6,2));

CREATE TABLE PRACOWNICY
       (ID_PRAC NUMBER(4) CONSTRAINT PK_PRAC PRIMARY KEY,
	NAZWISKO VARCHAR2(15),
	ETAT VARCHAR2(10) CONSTRAINT FK_ETAT REFERENCES ETATY(NAZWA),
	ID_SZEFA NUMBER(4) CONSTRAINT FK_ID_SZEFA REFERENCES PRACOWNICY(ID_PRAC), 
	ZATRUDNIONY DATE,
	PLACA_POD NUMBER(6,2) CONSTRAINT MIN_PLACA_POD CHECK(PLACA_POD>100),
	PLACA_DOD NUMBER(6,2),
	ID_ZESP NUMBER(2) CONSTRAINT FK_ID_ZESP REFERENCES ZESPOLY(ID_ZESP));
  
INSERT INTO ZESPOLY VALUES (10,'ADMINISTRACJA',      'PIOTROWO 3A');
INSERT INTO ZESPOLY VALUES (20,'SYSTEMY ROZPROSZONE','PIOTROWO 3A');
INSERT INTO ZESPOLY VALUES (30,'SYSTEMY EKSPERCKIE', 'STRZELECKA 14');
INSERT INTO ZESPOLY VALUES (40,'ALGORYTMY',          'WLODKOWICA 16');
INSERT INTO ZESPOLY VALUES (50,'BADANIA OPERACYJNE', 'MIELZYNSKIEGO 30');

INSERT INTO ETATY VALUES ('PROFESOR'  ,800.00,1500.00);
INSERT INTO ETATY VALUES ('ADIUNKT'   ,510.00, 750.00);
INSERT INTO ETATY VALUES ('ASYSTENT'  ,300.00, 500.00);
INSERT INTO ETATY VALUES ('STAZYSTA'  ,150.00, 250.00);
INSERT INTO ETATY VALUES ('SEKRETARKA',270.00, 450.00);
INSERT INTO ETATY VALUES ('DYREKTOR' ,1280.00,2100.00);
 
INSERT INTO PRACOWNICY VALUES (100,'WEGLARZ'    ,'DYREKTOR'  ,NULL,to_Date('01-01-1968','DD-MM-YY'),1730.00,420.50,10);
INSERT INTO PRACOWNICY VALUES (110,'BLAZEWICZ'  ,'PROFESOR'  ,100 ,to_Date('01-05-1973','DD-MM-YY'),1350.00,210.00,40);
INSERT INTO PRACOWNICY VALUES (120,'SLOWINSKI'  ,'PROFESOR'  ,100 ,to_Date('01-09-1977','DD-MM-YY'),1070.00,  NULL,30);
INSERT INTO PRACOWNICY VALUES (130,'BRZEZINSKI' ,'PROFESOR'  ,100 ,to_Date('01-07-1968','DD-MM-YY'), 960.00,  NULL,20);
INSERT INTO PRACOWNICY VALUES (140,'MORZY'      ,'PROFESOR'  ,130 ,to_Date('15-09-1975','DD-MM-YY'), 830.00,105.00,20);
INSERT INTO PRACOWNICY VALUES (150,'KROLIKOWSKI','ADIUNKT'   ,130 ,to_Date('01-09-1977','DD-MM-YY'), 645.50,  NULL,20);
INSERT INTO PRACOWNICY VALUES (160,'KOSZLAJDA'  ,'ADIUNKT'   ,130 ,to_Date('01-03-1985','DD-MM-YY'), 590.00,  NULL,20);
INSERT INTO PRACOWNICY VALUES (170,'JEZIERSKI'  ,'ASYSTENT'  ,130 ,to_Date('01-10-1992','DD-MM-YY'), 439.70, 80.50,20);
INSERT INTO PRACOWNICY VALUES (190,'MATYSIAK'   ,'ASYSTENT'  ,140 ,to_Date('01-09-1993','DD-MM-YY'), 371.00,  NULL,20);
INSERT INTO PRACOWNICY VALUES (180,'MAREK'      ,'SEKRETARKA',100 ,to_Date('20-02-1985','DD-MM-YY'), 410.20,  NULL,10);
INSERT INTO PRACOWNICY VALUES (200,'ZAKRZEWICZ' ,'STAZYSTA'  ,140 ,to_Date('15-07-1994','DD-MM-YY'), 208.00,  NULL,30);
INSERT INTO PRACOWNICY VALUES (210,'BIALY'      ,'STAZYSTA'  ,130 ,to_Date('15-10-1993','DD-MM-YY'), 250.00,170.60,30);
INSERT INTO PRACOWNICY VALUES (220,'KONOPKA'    ,'ASYSTENT'  ,110 ,to_Date('01-10-1993','DD-MM-YY'), 480.00,  NULL,20);
INSERT INTO PRACOWNICY VALUES (230,'HAPKE'      ,'ASYSTENT'  ,120 ,to_Date('01-09-1992','DD-MM-YY'), 480.00, 90.00,30);
 

drop table pracownicy_o;
drop table zespoly_o;
drop table etaty_o;

drop type pracownicy_tab force;
drop type pracownik force;
drop type zespol force;
drop type etat force;

create or replace type pracownik;
/

create or replace type pracownicy_tab as table of ref Pracownik;
/

create or replace type zespol as object (
	ID_ZESP number(2),
	NAZWA VARCHAR2(20),
	ADRES VARCHAR2(20),
	PRACOWNICY PRACOWNICY_TAB
)
/

create or replace type etat as object (
	NAZWA VARCHAR2(10),
	PLACA_MIN NUMBER(6,2),
	PLACA_MAX NUMBER(6,2),
	PRACOWNICY PRACOWNICY_TAB
)
/


create or replace type pracownik as object (
	ID_PRAC number(4),
	NAZWISKO VARCHAR2(15),
	etat_ varchar2(10),
	ETAT_PRAC REF ETAT,
        ID_SZEFA number(4),
	SZEF REF PRACOWNIK,
	ZATRUDNIONY DATE,
	PLACA_POD NUMBER(6,2),
	PLACA_DOD NUMBER(6,2),
        ID_ZESP number(2),
	ZESPOL_PRAC REF ZESPOL
)
/

sho err;

create table pracownicy_o of pracownik;

create table zespoly_o of zespol
nested table pracownicy store as pracownicy_w_zespole;

create table etaty_o of etat
nested table pracownicy store as pracownicy_na_etacie;


insert into pracownicy_o 
select pracownik(id_prac,nazwisko,etat,null,id_szefa,null,zatrudniony,placa_pod,placa_dod,id_zesp,null)
from pracownicy;

insert into etaty_o
select etat(nazwa,placa_min,placa_max,
 cast(multiset(
  select ref(p)
  from pracownicy_o p
  where p.etat_=e.nazwa
 ) as pracownicy_tab))
from etaty e;

insert into zespoly_o
select zespol(id_zesp,nazwa,adres,
 cast(multiset(
  select ref(p)
  from pracownicy_o p
  where p.id_zesp=z.id_zesp
 ) as pracownicy_tab))
from zespoly z;

update pracownicy_o p
set p.szef=(
 select ref(x)
 from pracownicy_o x
 where x.id_prac=p.id_szefa)
where p.id_szefa is not null;

update pracownicy_o p
set p.etat_prac=(
 select ref(x)
 from etaty_o x
 where x.nazwa=p.etat_);

update pracownicy_o p
set p.zespol_prac=(
 select ref(x)
 from zespoly_o x
 where x.id_zesp=p.id_zesp);
 


alter type pracownik drop attribute id_prac cascade including table data;
alter type pracownik drop attribute id_szefa cascade including table data;
alter type pracownik drop attribute id_zesp cascade including table data;
alter type pracownik drop attribute etat_ cascade including table data;

alter type zespol drop attribute id_zesp cascade including table data;

COMMIT;