Preparation.plsql

From Studia Informatyczne


--
-- ustawienia wyswietlania
--
set pages 10000
set linesize 150
column val format 9999999999
column bin format a20

--
-- utworzenie tabel z pracownikami
--
DROP TABLE PRACOWNICY cascade constraints;
DROP TABLE ZESPOLY cascade constraints;
DROP TABLE ETATY cascade constraints;

DROP TABLE PRACOWNICY cascade constraints;
DROP TABLE ZESPOLY cascade constraints;
DROP TABLE ETATY cascade constraints;
 
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-68','DD-MM-RR'),1730.00,420.50,10);
INSERT INTO PRACOWNICY VALUES (110,'BLAZEWICZ'  ,'PROFESOR'  ,100 ,to_date('01-05-73','DD-MM-RR'),1350.00,210.00,40);
INSERT INTO PRACOWNICY VALUES (120,'SLOWINSKI'  ,'PROFESOR'  ,100 ,to_date('01-09-77','DD-MM-RR'),1070.00,  NULL,30);
INSERT INTO PRACOWNICY VALUES (130,'BRZEZINSKI' ,'PROFESOR'  ,100 ,to_date('01-07-68','DD-MM-RR'), 960.00,  NULL,20);
INSERT INTO PRACOWNICY VALUES (140,'MORZY'      ,'PROFESOR'  ,130 ,to_date('15-09-75','DD-MM-RR'), 830.00,105.00,20);
INSERT INTO PRACOWNICY VALUES (150,'KROLIKOWSKI','ADIUNKT'   ,130 ,to_date('01-09-77','DD-MM-RR'), 645.50,  NULL,20);
INSERT INTO PRACOWNICY VALUES (160,'KOSZLAJDA'  ,'ADIUNKT'   ,130 ,to_date('01-03-85','DD-MM-RR'), 590.00,  NULL,20);
INSERT INTO PRACOWNICY VALUES (170,'JEZIERSKI'  ,'ASYSTENT'  ,130 ,to_date('01-10-92','DD-MM-RR'), 439.70, 80.50,20);
INSERT INTO PRACOWNICY VALUES (190,'MATYSIAK'   ,'ASYSTENT'  ,140 ,to_date('01-09-93','DD-MM-RR'), 371.00,  NULL,20);
INSERT INTO PRACOWNICY VALUES (180,'MAREK'      ,'SEKRETARKA',100 ,to_date('20-02-85','DD-MM-RR'), 410.20,  NULL,10);
INSERT INTO PRACOWNICY VALUES (200,'ZAKRZEWICZ' ,'STAZYSTA'  ,140 ,to_date('15-07-94','DD-MM-RR'), 208.00,  NULL,30);
INSERT INTO PRACOWNICY VALUES (210,'BIALY'      ,'STAZYSTA'  ,130 ,to_date('15-10-93','DD-MM-RR'), 250.00,170.60,30);
INSERT INTO PRACOWNICY VALUES (220,'KONOPKA'    ,'ASYSTENT'  ,110 ,to_date('01-10-93','DD-MM-RR'), 480.00,  NULL,20);
INSERT INTO PRACOWNICY VALUES (230,'HAPKE'      ,'ASYSTENT'  ,120 ,to_date('01-09-92','DD-MM-RR'), 480.00, 90.00,30);
 
COMMIT;

--
-- usuniecie tabel do dyskretyzacji
--
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE bin_cat';
  EXECUTE IMMEDIATE 'DROP TABLE bin_num';
  EXECUTE IMMEDIATE 'DROP TABLE norm_lin';
  EXECUTE IMMEDIATE 'DROP TABLE clip';
EXCEPTION
  WHEN OTHERS THEN Null;
END;
/

--
-- utworzenie perspektywy ukrywajacej atrybuty typu DATE
--
CREATE OR REPLACE VIEW v_pracownicy AS
SELECT id_prac, nazwisko, etat, id_szefa, 
  TO_NUMBER(TO_CHAR(zatrudniony,'YYYY')) AS rok_zatrudnienia,
  placa_pod, placa_dod, id_zesp
FROM pracownicy;
/
SELECT * FROM v_pracownicy;

-- 
-- utworzenie tabeli do dyskretyzacji atrybutow kategorycznych
--
BEGIN
  DBMS_DATA_MINING_TRANSFORM.CREATE_BIN_CAT(
    bin_table_name=>'bin_cat',
    bin_schema_name=>'*****');
END;
/

--
-- dyskretyzacja atrybutu kategorycznego ETAT
--
BEGIN
  DBMS_DATA_MINING_TRANSFORM.INSERT_BIN_CAT_FREQ(
    bin_table_name=>'bin_cat',
    data_table_name=>'v_pracownicy',
    bin_num=>3,
    exclude_list=>DBMS_DATA_MINING_TRANSFORM.Column_List('NAZWISKO'));
END;
/

--
-- obejrzenie wyniku dyskretyzacji
--
SELECT * FROM bin_cat;

-- 
-- utworzenie tabeli do dyskretyzacji atrybutow numerycznych
--
BEGIN
  DBMS_DATA_MINING_TRANSFORM.CREATE_BIN_NUM(
    bin_table_name=>'bin_num',
    bin_schema_name=>'*****');
END;
/

--
-- dyskretyzacja atrybutu numerycznego ROK_ZATRUDNIENIA
-- podzial na 4 przedzialy o rownej szerokosci
--
BEGIN
  DBMS_DATA_MINING_TRANSFORM.INSERT_BIN_NUM_EQWIDTH(
    bin_table_name=>'bin_num',
    data_table_name=>'v_pracownicy',
    bin_num=>4,
    exclude_list=>DBMS_DATA_MINING_TRANSFORM.Column_List
      ('ID_PRAC','ID_SZEFA','PLACA_POD','PLACA_DOD','ID_ZESP'),
    round_num=>4);
END;
/

--
-- dyskretyzacja atrybutu numerycznego PLACA_POD
-- podzial na 3 przedzialy o rownej glebokosci
--
BEGIN
  DBMS_DATA_MINING_TRANSFORM.INSERT_BIN_NUM_QTILE(
    bin_table_name=>'bin_num',
    data_table_name=>'v_pracownicy',
    bin_num=>3,
    exclude_list=>DBMS_DATA_MINING_TRANSFORM.Column_List
      ('ID_PRAC','ID_SZEFA','ROK_ZATRUDNIENIA','PLACA_DOD','ID_ZESP'));
END;
/

--
-- obejrzenie wyniku dyskretyzacji
--
SELECT * FROM bin_num;

-- 
-- utworzenie tabeli do normalizacji
--
BEGIN
  DBMS_DATA_MINING_TRANSFORM.CREATE_NORM_LIN(
    norm_table_name=>'norm_lin',
    norm_schema_name=>'*****');
END;
/

--
-- normalizacja atrybutu numerycznego ID_PRAC
-- wykorzystana metoda: MIN-MAX
--
BEGIN
  DBMS_DATA_MINING_TRANSFORM.INSERT_NORM_LIN_MINMAX(
    norm_table_name=>'norm_lin',
    data_table_name=>'v_pracownicy',
    exclude_list=>DBMS_DATA_MINING_TRANSFORM.Column_List
      ('ID_SZEFA','ROK_ZATRUDNIENIA','PLACA_POD','PLACA_DOD','ID_ZESP'));
END;
/

--
-- normalizacja atrybutu numerycznego ID_SZEFA
-- wykorzystana metoda: Z-SCORE
--
BEGIN
  DBMS_DATA_MINING_TRANSFORM.INSERT_NORM_LIN_ZSCORE(
    norm_table_name=>'norm_lin',
    data_table_name=>'v_pracownicy',
    exclude_list=>DBMS_DATA_MINING_TRANSFORM.Column_List
      ('ID_PRAC','ROK_ZATRUDNIENIA','PLACA_POD','PLACA_DOD','ID_ZESP'));
END;
/

--
-- obejrzenie wyniku normalizacji
--
SELECT * FROM norm_lin;


-- 
-- utworzenie tabeli do przycinania
--
BEGIN
  DBMS_DATA_MINING_TRANSFORM.CREATE_CLIP(
    clip_table_name=>'clip',
    clip_schema_name=>'*****');
END;
/

--
-- obciecie 30% wartosci atrybutu numerycznego PLACA_DOD
-- wypelnienie obcietych wartosci wartoscia brzegowa
--
BEGIN
  DBMS_DATA_MINING_TRANSFORM.INSERT_CLIP_WINSOR_TAIL(
    clip_table_name=>'clip',
    data_table_name=>'v_pracownicy',
    tail_frac=>0.3,
    exclude_list=>DBMS_DATA_MINING_TRANSFORM.Column_List
      ('ID_PRAC','ID_SZEFA','ROK_ZATRUDNIENIA','PLACA_POD','ID_ZESP'));
END;
/

--
-- obciecie 30% wartosci atrybutu numerycznego ID_ZESP
-- usuniecie obcietych wartosci
--
BEGIN
  DBMS_DATA_MINING_TRANSFORM.INSERT_CLIP_TRIM_TAIL(
    clip_table_name=>'clip',
    data_table_name=>'v_pracownicy',
    tail_frac=>0.3,
    exclude_list=>DBMS_DATA_MINING_TRANSFORM.Column_List
      ('ID_PRAC','ROK_ZATRUDNIENIA','PLACA_POD','PLACA_DOD','ID_SZEFA'));
END;
/

--
-- obejrzenie wyniku przycinania
--
SELECT * FROM clip;

--
-- utworzenie perspektyw przedstawiajacych dane wejsciowe po dyskretyzacji,
-- normalizacji i obcinaniu
--
BEGIN
  DBMS_DATA_MINING_TRANSFORM.XFORM_BIN_CAT(
    bin_table_name=>'bin_cat',
    data_table_name=>'v_pracownicy',
    xform_view_name=>'v_bin_cat_pracownicy');
END;
/
SELECT * FROM v_bin_cat_pracownicy;

BEGIN  
  DBMS_DATA_MINING_TRANSFORM.XFORM_BIN_NUM(
    bin_table_name=>'bin_num',
    data_table_name=>'v_bin_cat_pracownicy',
    xform_view_name=>'v_bin_num_pracownicy');
END;
/    
SELECT * FROM v_bin_num_pracownicy;

BEGIN  
  DBMS_DATA_MINING_TRANSFORM.XFORM_NORM_LIN(
    norm_table_name=>'norm_lin',
    data_table_name=>'v_bin_num_pracownicy',
    xform_view_name=>'v_norm_lin_pracownicy');
END;
/    
SELECT * FROM v_norm_lin_pracownicy;

BEGIN  
  DBMS_DATA_MINING_TRANSFORM.XFORM_CLIP(
    clip_table_name=>'clip',
    data_table_name=>'v_norm_lin_pracownicy',
    xform_view_name=>'v_clip_pracownicy');
END;
/    
SELECT * FROM v_clip_pracownicy;