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 =======================...
BobBudowniczyW