tabele_kadry.txt

Drop table osoby cascade constraints;
Drop table wydzialy cascade constraints;
Drop table kierownicy cascade constraints;
Drop table zatrudnienia cascade constraints;
Drop table stanowiska cascade constraints;

CREATE TABLE Osoby (
    id_os NUMBER(4) NOT NULL,
    nazwisko VARCHAR2(15) NOT NULL,
    imie1 VARCHAR2(15) NOT NULL,
    imie2 VARCHAR2(15),
    d_ur DATE NOT NULL,
    plec CHAR(1) NOT NULL CHECK (plec='M' OR plec='K'),
    PRIMARY KEY (id_os)
);

CREATE TABLE Wydzialy (
    id_w NUMBER(4) NOT NULL,
    nazwa VARCHAR2(15) NOT NULL,
    PRIMARY KEY (id_w)
);

CREATE TABLE stanowiska (
    id_s NUMBER(2) NOT NULL,
    stanowisko VARCHAR2(10) NOT NULL,
    CONSTRAINT stanowiska_stanowisko_UN UNIQUE(stanowisko),
    PRIMARY KEY (id_s)
);

CREATE TABLE Zatrudnienia (
    id_z NUMBER(4) NOT NULL,
    id_os NUMBER(4) NOT NULL,
    od DATE NOT NULL,
    do DATE,
    id_w NUMBER(4) NOT NULL,
    pensja NUMBER(8) NOT NULL,
    id_s NUMBER(2) NOT NULL,
    PRIMARY KEY (id_z),
    CONSTRAINT Wydzialy_Zatrudnienia FOREIGN KEY (id_w) REFERENCES Wydzialy (id_w),
    CONSTRAINT Osoby_Zatrudnienia FOREIGN KEY (id_os) REFERENCES Osoby (id_os),
    CONSTRAINT Stanowiska_Zatrudnienia FOREIGN KEY (id_s) REFERENCES stanowiska (id_s)
);

CREATE TABLE Kierownicy (
    id_w NUMBER(4) NOT NULL,
    id_os NUMBER(4) NOT NULL,
    od DATE NOT NULL,
    do DATE,
    CONSTRAINT Wydzialy_Kierownicy FOREIGN KEY (id_w) REFERENCES Wydzialy (id_w),
    CONSTRAINT Osoby_Kierownicy FOREIGN KEY (id_os) REFERENCES Osoby (id_os)
);




/*==========================================================================*/
/*  Indexes                                                                 */
/*==========================================================================*/

CREATE UNIQUE INDEX IDX_Wydzialy1 ON Wydzialy (nazwa);

CREATE INDEX IDX_Zatrudnienia_id_os ON Zatrudnienia (id_os ASC);

CREATE INDEX IDX_Zatrudnienia_id_wydz ON Zatrudnienia (id_w ASC);

CREATE INDEX IDX_Zatrudnienia3 ON Zatrudnienia (id_s);

CREATE INDEX IDX_Kierownicy1 ON Kierownicy (id_w);

CREATE INDEX IDX_Kierownicy2 ON Kierownicy (id_os);

/*==========================================================================*/





Prompt ========================================================
Insert into osoby Values (1,'Lis','Jan',Null,To_Date('21/10/1978','dd/mm/yyyy'),'M');
Insert into osoby Values (2,'Kot','Adam','Marek',To_Date('21/11/1980','dd/mm/yyyy'),'M');
Insert into osoby Values (3,'Norek','Tadeusz',Null,To_Date('23/10/1982','dd/mm/yyyy'),'M');
Insert into osoby Values (4,'Krawczyk','Adam',Null,To_Date('02/06/1982','dd/mm/yyyy'),'M');
Insert into osoby Values (5,'Lis','Anna',Null,To_Date('23/12/1968','dd/mm/yyyy'),'K');
Insert into osoby Values (6,'Kowalska','Ewa',Null,To_Date('11/05/1979','dd/mm/yyyy'),'K');
Insert into osoby Values (7,'Nowak','Maria','Anna',To_Date('12/07/1979','dd/mm/yyyy'),'K');
Insert into osoby Values (8,'Duda','Barbara',Null,To_Date('20/12/1984','dd/mm/yyyy'),'K');
Insert into osoby Values (9,'Kula','Katarzyna',Null,To_Date('12/11/1983','dd/mm/yyyy'),'K');
Insert into osoby Values (10,'Murek','Danuta',Null,To_Date('17/08/1975','dd/mm/yyyy'),'K');

Prompt ========================================================
Insert into osoby Values (11,'Dudek','Jan',Null,To_Date('22/10/1948','dd/mm/yyyy'),'M');
Insert into osoby Values (12,'Nowak','Adam','Marek',To_Date('11/11/1970','dd/mm/yyyy'),'M');
Insert into osoby Values (13,'Korek','Tadeusz',Null,To_Date('21/10/1978','dd/mm/yyyy'),'M');
Insert into osoby Values (14,'Wojtczak','Adam',Null,To_Date('12/06/1985','dd/mm/yyyy'),'M');
Insert into osoby Values (15,'Piecha','Anna',Null,To_Date('25/11/1965','dd/mm/yyyy'),'K');
Insert into osoby Values (16,'Socha','Ewa',Null,To_Date('13/07/1977','dd/mm/yyyy'),'K');
Insert into osoby Values (17,'Turecka','Maria','Anna',To_Date('22/03/1979','dd/mm/yyyy'),'K');
Insert into osoby Values (18,'Dyba','Barbara',Null,To_Date('22/05/1983','dd/mm/yyyy'),'K');
Insert into osoby Values (19,'Wilk','Katarzyna',Null,To_Date('16/11/1943','dd/mm/yyyy'),'K');
Insert into osoby Values (20,'Grzesik','Danuta',Null,To_Date('11/06/1976','dd/mm/yyyy'),'K');

Prompt ========================================================
Insert into osoby Values (21,'Polak','Jan',Null,To_Date('22/10/1958','dd/mm/yyyy'),'M');
Insert into osoby Values (22,'Czech','Adam','Marek',To_Date('11/04/1971','dd/mm/yyyy'),'M');
Insert into osoby Values (23,'Kuna','Tadeusz',Null,To_Date('02/10/1953','dd/mm/yyyy'),'M');
Insert into osoby Values (24,'Waniek','Adam',Null,To_Date('12/06/1985','dd/mm/yyyy'),'M');
Insert into osoby Values (25,'Plich','Anna',Null,To_Date('21/10/1978','dd/mm/yyyy'),'K');
Insert into osoby Values (26,'Sowa','Ewa',Null,To_Date('13/07/1957','dd/mm/yyyy'),'K');
Insert into osoby Values (27,'Turek','Maria','Anna',To_Date('22/03/1949','dd/mm/yyyy'),'K');
Insert into osoby Values (28,'Drops','Barbara',Null,To_Date('22/05/1963','dd/mm/yyyy'),'K');
Insert into osoby Values (29,'Wilga','Katarzyna',Null,To_Date('17/11/1953','dd/mm/yyyy'),'K');
Insert into osoby Values (30,'Got','Danuta',Null,To_Date('12/06/1946','dd/mm/yyyy'),'K');

commit;

Insert into stanowiska Values (1,'Asystent');
Insert into stanowiska Values (2,'Adiunkt');
Insert into stanowiska Values (3,'Profesor');


commit;

Prompt ========================================================


Prompt ========================================================

Insert into wydzialy Values (1,'Matematyka');
commit;
Insert into wydzialy Values (2,'Fizyka');
commit;
Insert into wydzialy Values (3,'Prawo');
commit;
Insert into wydzialy Values (4,'Ekonomia');
commit;
Insert into wydzialy Values (5,'Filologia');
commit;


Prompt ========================================================

Insert into zatrudnienia Values (1,1,To_Date('21/10/1998','dd/mm/yyyy'),To_Date('11/05/1999','dd/mm/yyyy'),1,1500,1);



Insert into zatrudnienia Values (2,2,To_Date('21/11/1999','dd/mm/yyyy'),To_Date('30/09/2005','dd/mm/yyyy'),1,1500,1);
Insert into zatrudnienia Values (3,2,To_Date('01/10/2005','dd/mm/yyyy'),NULL,5,2500,1);



Insert into zatrudnienia Values (4,3,To_Date('23/10/2002','dd/mm/yyyy'),NULL,1,1500,1);



Insert into zatrudnienia Values (5,4,To_Date('02/06/2004','dd/mm/yyyy'),NULL,1,1500,1);


Insert into zatrudnienia Values (6,5,To_Date('23/12/1988','dd/mm/yyyy'),NULL,1,4000,3);

Insert into kierownicy Values (1,5,To_Date('23/12/1988','dd/mm/yyyy'),NULL);

Prompt ========================================================

Insert into zatrudnienia Values (7,6,To_Date('11/05/2001','dd/mm/yyyy'),NULL,2,1900,1);
Insert into zatrudnienia Values (8,7,To_Date('12/07/2002','dd/mm/yyyy'),NULL,2,1900,1);
Insert into zatrudnienia Values (9,8,To_Date('20/12/2003','dd/mm/yyyy'),NULL,2,2000,1);
Insert into zatrudnienia Values (10,9,To_Date('12/11/2004','dd/mm/yyyy'),NULL,2,2100,1);
Insert into zatrudnienia Values (11,10,To_Date('17/08/1995','dd/mm/yyyy'),To_Date('30/09/1999','dd/mm/yyyy'),2,1800,1);
Insert into zatrudnienia Values (12,10,To_Date('01/10/1999','dd/mm/yyyy'),NULL,2,2500,2);
Insert into zatrudnienia Values (13,11,To_Date('22/10/1988','dd/mm/yyyy'),To_Date('12/03/2002','dd/mm/yyyy'),2,1800,1);



Insert into zatrudnienia Values (14,12,To_Date('11/11/1990','dd/mm/yyyy'),To_Date('30/09/1996','dd/mm/yyyy'),2,1600,1);
Insert into zatrudnienia Values (15,12,To_Date('01/10/1996','dd/mm/yyyy'),To_Date('30/09/2002','dd/mm/yyyy'),2,2400,2);
Insert into zatrudnienia Values (16,12,To_Date('01/10/2002','dd/mm/yyyy'),NULL,2,3400,3);


Insert into zatrudnienia Values (17,13,To_Date('21/10/1998','dd/mm/yyyy'),To_Date('30/09/2003','dd/mm/yyyy'),2,1750,1);
Insert into zatrudnienia Values (18,13,To_Date('01/10/2003','dd/mm/yyyy'),NULL,2,2750,2);



Insert into zatrudnienia Values (19,14,To_Date('12/06/2005','dd/mm/yyyy'),NULL,2,1900,1);



Insert into zatrudnienia Values (20,15,To_Date('25/11/1985','dd/mm/yyyy'),To_Date('20/10/1988','dd/mm/yyyy'),2,2600,2);
Insert into zatrudnienia Values (21,15,To_Date('21/10/1988','dd/mm/yyyy'),NULL,2,3600,3);


Insert into kierownicy Values (2,15,To_Date('25/11/1985','dd/mm/yyyy'),NULL);



Prompt ========================================================


Insert into zatrudnienia Values (22,16,To_Date('13/07/1997','dd/mm/yyyy'),NULL,3,2500,2);



Insert into zatrudnienia Values (23,17,To_Date('22/03/1999','dd/mm/yyyy'),To_Date('26/04/2003','dd/mm/yyyy'),3,1900,1);



Insert into zatrudnienia Values (24,18,To_Date('22/05/2003','dd/mm/yyyy'),NULL,3,2000,1);



Insert into zatrudnienia Values (25,19,To_Date('16/11/1985','dd/mm/yyyy'),To_Date('16/11/1989','dd/mm/yyyy'),3,2600,3);
Insert into zatrudnienia Values (26,19,To_Date('17/11/1989','dd/mm/yyyy'),To_Date('15/11/1995','dd/mm/yyyy'),3,3600,3);
Insert into zatrudnienia Values (27,19,To_Date('16/11/1995','dd/mm/yyyy'),NULL,3,4500,3);


Insert into kierownicy Values (3,19,To_Date('16/11/1985','dd/mm/yyyy'),NULL);


Prompt ========================================================

Insert into zatrudnienia Values (28,20,To_Date('11/06/1996','dd/mm/yyyy'),NULL,4,2000,2);
Insert into zatrudnienia Values (29,21,To_Date('22/10/1985','dd/mm/yyyy'),NULL,4,4000,3);



Prompt ========================================================

Insert into zatrudnienia Values (30,22,To_Date('11/04/1991','dd/mm/yyyy'),NULL,4,5000,3);
Insert into zatrudnienia Values (31,23,To_Date('02/10/1985','dd/mm/yyyy'),To_Date('01/10/1995','dd/mm/yyyy'),4,1300,1);
Insert into zatrudnienia Values (32,23,To_Date('02/10/1995','dd/mm/yyyy'),To_Date('30/09/2005','dd/mm/yyyy'),4,3300,2);
Insert into zatrudnienia Values (33,23,To_Date('01/10/2005','dd/mm/yyyy'),NULL,4,4300,3);
Insert into kierownicy Values (4,23,To_Date('01/10/2005','dd/mm/yyyy'),NULL);





Prompt =======================...