Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create database wiezienie_fox_river;
- use wiezienie_fox_river;
- create table dane_wieznia(
- id_wieznia varchar(10) primary key,
- imie_wieznia varchar(20),
- naziwsko_wieznia varchar(20),
- plec varchar(1),
- data_urodzenia date,
- data_przybycia date,
- data_zakonczenia_wyroku date null
- );
- create table widzenia(
- id_widzenia varchar(10) primary key,
- data_widzenia datetime,
- id_wieznia varchar(10),
- nazwisko_odwiedzajacego varchar(20),
- foreign key(id_wieznia) REFERENCES dane_wieznia(id_wieznia)
- );
- create table izolatka(
- nr_izolatki varchar(10),
- id_wieznia varchar(10),
- data date,
- powod varchar(20),
- primary key(nr_izolatki),
- foreign key(id_wieznia) REFERENCES dane_wieznia(id_wieznia)
- );
- create table pracownicy(
- id_pracownika varchar(10),
- imie_pracownika varchar(20),
- nazwisko_pracownika varchar(20),
- typ_zmiany varchar(20),
- miejsce_pracy varchar(3),
- primary key(id_pracownika)
- );
- create table magazyn(
- id_magazynu varchar(10),
- ilosc_zarekwirowanych_przedmiotow int,
- opis varchar(30),
- id_wieznia varchar(10),
- id_pracownika varchar(10),
- primary key(id_magazynu),
- foreign key(id_wieznia) REFERENCES dane_wieznia(id_wieznia),
- foreign key(id_pracownika) REFERENCES pracownicy(id_pracownika)
- );
- create table zakwaterowanie(
- nr_zakwaterowania varchar(10),
- nr_celi int,
- nazwisko_wieznia varchar(20),
- nazwisko_wspolwieznia varchar(20),
- id_bloku varchar(1),
- id_wieznia varchar(10),
- primary key(nr_zakwaterowania),
- foreign key(id_wieznia) REFERENCES dane_wieznia(id_wieznia)
- );
- create table blok(
- id_bloku varchar(1) primary key,
- id_kierownika varchar(10),
- foreign key (id_kierownika) REFERENCES pracownicy(id_pracownika)
- );
- create table stolowka(
- nr_stolowki varchar(10) primary key,
- id_pracownika varchar(10),
- id_bloku varchar(1),
- foreign key(id_bloku) REFERENCES blok(id_bloku),
- foreign key(id_pracownika) REFERENCES pracownicy(id_pracownika)
- );
- create table szpital(
- id_wizyty varchar(10) primary key,
- id_pracownika varchar(10),
- id_wieznia varchar(10),
- data datetime,
- opis varchar(40),
- foreign key(id_pracownika) REFERENCES pracownicy(id_pracownika),
- foreign key(id_wieznia) REFERENCES dane_wieznia(id_wieznia)
- );
- insert into dane_wieznia(id_wieznia, imie_wieznia, naziwsko_wieznia, plec, data_urodzenia, data_przybycia)
- values
- ('SCP004','Fernando','Alonzo','M','1995-01-11','2010-05-26'),
- ('SCP005','Sara','Tumadredy','K','1995-02-27','2017-05-26'),
- ('SCP007','Bartosz','Paduch','M','1992-06-23','2012-05-26'),
- ('SCP018','Kuba','Klakowak','M','1999-03-12','2015-05-26'),
- ('SCP029','Michal','Karnas','M','1995-01-07','2015-04-07');
- insert into dane_wieznia(id_wieznia, imie_wieznia, naziwsko_wieznia, plec, data_urodzenia, data_przybycia, data_zakonczenia_wyroku)
- values
- ('SCP001','Michael','Trocky','M','1990-09-13','2008-10-23','2015-10-23'),
- ('SCP002','Artur','Szypyr','M','1999-04-25','2018-04-15','2023-04-15'),
- ('SCP003','Kacper','Sywruk','M','2000-01-23','2018-12-01','2023-12-01'),
- ('SCP006','Wiktoria','Mazur','K','1990-09-15','2005-09-15','2020-09-15'),
- ('SCP008','Michal','Rychlik','M','2000-01-31','2018-11-20','2023-11-20'),
- ('SCP009','Krzysztof','Killer','M','1990-01-31','2013-03-17','2018-03-17'),
- ('SCP010','Dawid','Mazur','M','1997-09-22','2018-07-23','2025-07-23'),
- ('SCP011','Monika','Kampinska','K','1995-09-11','2019-04-22','2023-04-22'),
- ('SCP012','Ida','Mack','K','1970-07-07','2000-03-30','2015-03-30'),
- ('SCP013','Mateusz','Azyk','M','1999-05-21','2019-05-15','2025-05-15'),
- ('SCP014','Magdalena','Kowalska','K','1996-09-17','2009-06-21','2019-06-21'),
- ('SCP015','Julia','Niska','K','1990-04-10','2009-08-12','2019-08-12'),
- ('SCP016','Karolina','Kracz','K','1999-05-21','2018-09-22','2020-09-22'),
- ('SCP017','Maja','Bach','K','1995-04-27','2018-04-20','2021-04-20'),
- ('SCP019','Martyna','Macioszke','K','1990-02-25','2013-08-25','2018-03-25'),
- ('SCP020','Kamil','Boken','M','2000-11-17','2018-07-07','2023-08-07'),
- ('SCP021','Michalina','Bozium','K','1995-05-11','2015-06-25','2018-06-25'),
- ('SCP022','Dariusz','Gloska','M','1997-06-11','2016-04-20','2023-04-20'),
- ('SCP023','Magdalena','Nima','K','1996-02-29','2015-03-25','2023-03-25'),
- ('SCP024','Maciek','Bogil','M','2000-05-11','2018-01-01','2025-01-01'),
- ('SCP025','Kacper','Gonil','M','1995-02-15','2018-02-07','2025-02-07'),
- ('SCP026','Julia','Masno','K','1990-07-19','2015-11-17','2025-11-17'),
- ('SCP027','Grzegorz','Nijak','M','1991-04-01','2009-05-07','2019-05-07'),
- ('SCP028','Josh','Gibon','M','1995-07-19','2016-11-17','2019-11-17'),
- ('SCP030','Mikolaj','Swiety','M','1995-04-16','2015-06-22','2025-07-22');
- insert into widzenia(id_widzenia,data_widzenia,id_wieznia,nazwisko_odwiedzajacego)
- values
- ('W501','2010-08-20 12:30','SCP015','Galkiewicz'),
- ('W502','2019-09-30 11:00','SCP016','Derenqowska'),
- ('W503','2020-01-10 15:00','SCP024','Mickiewicz'),
- ('W504','2006-09-25 12:30','SCP006','Szopik'),
- ('W505','2019-10-30 13:00','SCP016','Derenqowska'),
- ('W506','2001-04-09 11:00','SCP012','Benko'),
- ('W507','2010-07-23 12:30','SCP004','McKenzie'),
- ('W508','2009-09-20 11:00','SCP015','Goska'),
- ('W509','2019-11-29 12:00','SCP016','Derenqowska'),
- ('W510','2006-10-25 12:00','SCP006','Herb'),
- ('W511','2010-08-23 12:30','SCP004','McKenzie'),
- ('W512','2016-11-27 16:00','SCP028','Martinez'),
- ('W513','2009-10-20 12:00','SCP015','Goska'),
- ('W514','2019-12-22 11:00','SCP016','Derenqowska'),
- ('W515','2020-01-29 12:30','SCP016','Derenqowska'),
- ('W516','2020-02-07 12:00','SCP016','Los'),
- ('W517','2020-02-27 12:00','SCP016','Los'),
- ('W518','2020-11-20 15:30','SCP015','Goska'),
- ('W519','2020-02-01 13:30','SCP016','Derenqowska'),
- ('W520','2019-02-10 12:30','SCP024','Kowalski'),
- ('W521','2015-10-25 12:00','SCP006','Mikolajczuk'),
- ('W522','2019-02-01 12:00','SCP016','Derenqowska'),
- ('W523','2014-05-09 10:00','SCP012','Benko'),
- ('W524','2014-08-23 11:30','SCP004','Bogun'),
- ('W525','2017-12-01 13:00','SCP015','Goska'),
- ('W526','2015-12-20 12:30','SCP016','Derenqowska'),
- ('W527','2018-11-25 16:30','SCP006','Herb'),
- ('W528','2019-09-23 12:00','SCP004','Martinez'),
- ('W529','2016-12-27 15:00','SCP028','Martinez'),
- ('W530','2016-12-20 13:30','SCP015','Goska');
- insert into zakwaterowanie(nr_zakwaterowania,nr_celi,nazwisko_wieznia,nazwisko_wspolwieznia,id_bloku,id_wieznia)
- values
- ('z101','1','Trocky','Szypyr','A','SCP001'),
- ('z102','1','Szypyr','Trocky','A','SCP002'),
- ('z103','2','Sywruk','Alonzo','A','SCP003'),
- ('z104','2','Alonzo','Sywruk','A','SCP004'),
- ('z105','3','Tumadredy','Mazur','B','SCP005'),
- ('z106','3','Mazur','Tumadredy','B','SCP006'),
- ('z107','4','Paduch','puste','A','SCP007'),
- ('z108','5','Rychlik','puste','A','SCP008'),
- ('z109','6','Killer','Mazur','A','SCP009'),
- ('z110','6','Mazur','Killer','A','SCP010'),
- ('z111','7','Kampinska','Mack','B','SCP011'),
- ('z112','7','Mack','Kampinska','B','SCP012'),
- ('z113','8','Azyk','puste','A','SCP013'),
- ('z114','8','Kowalska','Niska','B','SCP014'),
- ('z115','8','Niska','Kowalska','B','SCP015'),
- ('z116','9','Kracz','puste','B','SCP016'),
- ('z117','10','Bach','puste','B','SCP017'),
- ('z118','11','Klakowak','puste','A','SCP018'),
- ('z119','12','Nima','Bozium','B','SCP019'),
- ('z120','13','Boken','Gloska','A','SCP020'),
- ('z121','12','Bozium','Nima','B','SCP021'),
- ('z122','13','Gloska','Boken','A','SCP022'),
- ('z123','13','Nima','Bogil','B','SCP023'),
- ('z124','14','Bogil','puste','A','SCP024'),
- ('z125','15','Gonil','puste','A','SCP025'),
- ('z126','16','Masno','puste','B','SCP026'),
- ('z127','17','Nijak','Gibon','A','SCP027'),
- ('z128','17','Gibon','Nijak','A','SCP028'),
- ('z129','18','Karnas','Swiety','A','SCP029'),
- ('z130','18','Swiety','Karnas','A','SCP030');
- insert into izolatka(nr_izolatki,id_wieznia,data,powod)
- values
- ('L1','SCP004','2010-06-07','zamieszki'),
- ('L2','SCP004','2010-07-14','stawianie_oporu'),
- ('L3','SCP018','2015-05-30','bojka'),
- ('L4','SCP029','2015-05-01','posiadanie_broni'),
- ('L5','SCP029','2015-05-25','stawianie_oporu');
- insert into pracownicy(id_pracownika,imie_pracownika,nazwisko_pracownika,typ_zmiany)
- values
- ('P101','Wieslawa','Warchak','Magazyn'),
- ('P102','Brajanek','Paczosek','Straznik'),
- ('P103','Mirek','Pacza','Straznik'),
- ('P104','Arturina','Szpryt','Straznik'),
- ('P105','Brajan','Paczosa','Straznik'),
- ('P106','Bozena','Nowotyminska','Straznik'),
- ('P107','Komieslaw','Dariel','Sprzatacz'),
- ('P108','Zdzislaw','Maczywoda','Elektryk'),
- ('P109','Choleslaw','Chleb','Sprzatacz'),
- ('P110','Boleslaw','Grachamka','Konserwator'),
- ('P111','Miloslaw','Dorytos','Kucharz'),
- ('P112','Waclaw','Lewus','Kucharz'),
- ('P113','Marcin','Prawus','Kierownik'),
- ('P114','Andrzej','Bomba','Magazyn'),
- ('P115','Jakub','Kubiak','Konserwator'),
- ('P116','Pawel','Albrychowicz','Hydraulik'),
- ('P117','Piotr','Trzepacz','Konserwator'),
- ('P118','Czeslaw','Petarda','Hydraulik'),
- ('P119','Artur','Skur','Doktor'),
- ('P120','Eryk','Rabarbar','Doktor'),
- ('P121','Maryk','Byk','Hydraulik'),
- ('P122','Radoslaw','Lokiec','Kierownik'),
- ('P123','Kamil','Poziomka','Kucharz'),
- ('P124','Mihael','Jegany','Elektryk'),
- ('P125','Yumi','Jaglana','Kucharz'),
- ('P126','Tomasz','Kasza','Kucharz'),
- ('P127','Zdzislaw','Miesien','Sprzatacz'),
- ('P128','Mariusz','Pudzianowski','Doktor'),
- ('P129','Wladyslaw','Bartoimadlo','Doktor'),
- ('P130','Maryk','Polamiecz','Sprzatacz');
- insert into magazyn(id_magazynu, id_wieznia, ilosc_zarekwirowanych_przedmiotow,opis,id_pracownika)
- values
- ('M801','SCP001','2','przedmioty latwopalne','P101'),
- ('M802','SCP003','5','bron biala','P101'),
- ('M803','SCP004','3','narkotyki','P101'),
- ('M804','SCP005','1','bron palna','P114'),
- ('M805','SCP006','3','pistolet','P101'),
- ('M806','SCP007','2','narzedzia','P101'),
- ('M807','SCP008','1','telefon','P101'),
- ('M808','SCP009','1','zegarek','P101'),
- ('M809','SCP010','12','gwozdzie','P114'),
- ('M810','SCP011','1','wkretak philipsowy','P101'),
- ('M811','SCP012','2','szklo','P101'),
- ('M812','SCP013','1','nunchako','P114'),
- ('M813','SCP014','1','kusza','P101'),
- ('M814','SCP015','14','belty do kuszy','P101'),
- ('M815','SCP016','1','katana','P101'),
- ('M816','SCP017','3','narzedzia','P101'),
- ('M817','SCP018','4','pilnik','P114'),
- ('M818','SCP019','2','bron biala','P114'),
- ('M819','SCP020','1','luk','P101'),
- ('M820','SCP021','23','strzaly do luku','P101'),
- ('M821','SCP022','1','telefon','P114'),
- ('M822','SCP023','2','telefon','P101'),
- ('M823','SCP024','3','wiertlo','P101'),
- ('M824','SCP025','2','lopata','P114'),
- ('M825','SCP026','1','kilof','P101'),
- ('M826','SCP027','1','ladunek wybuchowy','P114'),
- ('M827','SCP028','2','telewizor','P101'),
- ('M828','SCP029','2','bron biala','P101'),
- ('M829','SCP030','28','alkohol','P101'),
- ('M830','SCP002','4','telefon','P114');
- insert into szpital(id_wizyty,id_pracownika,id_wieznia,data,opis)
- values
- ('wizyta_1','P129','SCP001','2015-01-20 12:00','wypadek drogowy'),
- ('wizyta_2','P119','SCP002','2019-01-21 13:00','zlamana kostka'),
- ('wizyta_3','P120','SCP004','2015-01-21 12:00','zlamana reka'),
- ('wizyta_4','P128','SCP028','2017-01-22 16:00','obita twarz'),
- ('wizyta_5','P129','SCP017','2019-01-23 12:00','zlamana piesc');
- insert into blok(id_bloku,id_kierownika)
- values
- ('A','P113'),
- ('B','P122');
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement