SHARE
TWEET

Untitled

a guest May 25th, 2019 74 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. create database wiezienie_fox_river;
  2. use wiezienie_fox_river;
  3.  
  4. create table dane_wieznia(
  5. id_wieznia varchar(10) primary key,
  6. imie_wieznia varchar(20),
  7. naziwsko_wieznia varchar(20),
  8. plec varchar(1),
  9. data_urodzenia date,
  10. data_przybycia date,
  11. data_zakonczenia_wyroku date null
  12. );
  13.  
  14. create table widzenia(
  15. id_widzenia varchar(10) primary key,
  16. data_widzenia datetime,
  17. id_wieznia varchar(10),
  18. nazwisko_odwiedzajacego varchar(20),
  19. foreign key(id_wieznia) REFERENCES dane_wieznia(id_wieznia)
  20. );
  21.  
  22.  
  23. create table izolatka(
  24. nr_izolatki varchar(10),
  25. id_wieznia varchar(10),
  26. data date,
  27. powod varchar(20),
  28. primary key(nr_izolatki),
  29. foreign key(id_wieznia) REFERENCES dane_wieznia(id_wieznia)
  30. );
  31.  
  32. create table pracownicy(
  33. id_pracownika varchar(10),
  34. imie_pracownika varchar(20),
  35. nazwisko_pracownika varchar(20),
  36. typ_zmiany varchar(20),
  37. miejsce_pracy varchar(3),
  38. primary key(id_pracownika)
  39. );
  40.  
  41. create table magazyn(
  42. id_magazynu varchar(10),
  43. ilosc_zarekwirowanych_przedmiotow int,
  44. opis varchar(30),
  45. id_wieznia varchar(10),
  46. id_pracownika varchar(10),
  47. primary key(id_magazynu),
  48. foreign key(id_wieznia) REFERENCES dane_wieznia(id_wieznia),
  49. foreign key(id_pracownika) REFERENCES pracownicy(id_pracownika)
  50. );
  51.  
  52. create table zakwaterowanie(
  53. nr_zakwaterowania varchar(10),
  54. nr_celi int,
  55. nazwisko_wieznia varchar(20),
  56. nazwisko_wspolwieznia varchar(20),
  57. id_bloku varchar(1),
  58. id_wieznia varchar(10),
  59. primary key(nr_zakwaterowania),
  60. foreign key(id_wieznia) REFERENCES dane_wieznia(id_wieznia)
  61. );
  62.  
  63. create table blok(
  64. id_bloku varchar(1) primary key,
  65. id_pracownika varchar(10),
  66. foreign key (id_pracownika) REFERENCES pracownicy(id_pracownika)
  67. );
  68.  
  69. create table stolowka(
  70. nr_stolowki varchar(10) primary key,
  71. id_pracownika varchar(10),
  72. id_bloku varchar(1),
  73. foreign key(id_bloku) REFERENCES blok(id_bloku),
  74. foreign key(id_pracownika) REFERENCES pracownicy(id_pracownika)
  75. );
  76.  
  77. create table szpital(
  78. id_wizyty varchar(10) primary key,
  79. id_pracownika varchar(10),
  80. id_wieznia varchar(10),
  81. data datetime,
  82. opis varchar(40),
  83. foreign key(id_pracownika) REFERENCES pracownicy(id_pracownika),
  84. foreign key(id_wieznia) REFERENCES dane_wieznia(id_wieznia)
  85. );
  86.  
  87.  
  88.  
  89. insert into dane_wieznia(id_wieznia, imie_wieznia, naziwsko_wieznia, plec, data_urodzenia, data_przybycia)
  90. values
  91. ('SCP004','Fernando','Alonzo','M','1995-01-11','2010-05-26'),
  92. ('SCP005','Sara','Tumadredy','K','1995-02-27','2017-05-26'),
  93. ('SCP007','Bartosz','Paduch','M','1992-06-23','2012-05-26'),
  94. ('SCP018','Kuba','Klakowak','M','1999-03-12','2015-05-26'),
  95. ('SCP029','Michal','Karnas','M','1995-01-07','2015-04-07');
  96.  
  97. insert into dane_wieznia(id_wieznia, imie_wieznia, naziwsko_wieznia, plec, data_urodzenia, data_przybycia, data_zakonczenia_wyroku)
  98. values
  99. ('SCP001','Michael','Trocky','M','1990-09-13','2008-10-23','2015-10-23'),
  100. ('SCP002','Artur','Szypyr','M','1999-04-25','2018-04-15','2023-04-15'),
  101. ('SCP003','Kacper','Sywruk','M','2000-01-23','2018-12-01','2023-12-01'),
  102. ('SCP006','Wiktoria','Mazur','K','1990-09-15','2005-09-15','2020-09-15'),
  103. ('SCP008','Michal','Rychlik','M','2000-01-31','2018-11-20','2023-11-20'),
  104. ('SCP009','Krzysztof','Killer','M','1990-01-31','2013-03-17','2018-03-17'),
  105. ('SCP010','Dawid','Mazur','M','1997-09-22','2018-07-23','2025-07-23'),
  106. ('SCP011','Monika','Kampinska','K','1995-09-11','2019-04-22','2023-04-22'),
  107. ('SCP012','Ida','Mack','K','1970-07-07','2000-03-30','2015-03-30'),
  108. ('SCP013','Mateusz','Azyk','M','1999-05-21','2019-05-15','2025-05-15'),
  109. ('SCP014','Magdalena','Kowalska','K','1996-09-17','2009-06-21','2019-06-21'),
  110. ('SCP015','Julia','Niska','K','1990-04-10','2009-08-12','2019-08-12'),
  111. ('SCP016','Karolina','Kracz','K','1999-05-21','2018-09-22','2020-09-22'),
  112. ('SCP017','Maja','Bach','K','1995-04-27','2018-04-20','2021-04-20'),
  113. ('SCP019','Martyna','Macioszke','K','1990-02-25','2013-08-25','2018-03-25'),
  114. ('SCP020','Kamil','Boken','M','2000-11-17','2018-07-07','2023-08-07'),
  115. ('SCP021','Michalina','Bozium','K','1995-05-11','2015-06-25','2018-06-25'),
  116. ('SCP022','Dariusz','Gloska','M','1997-06-11','2016-04-20','2023-04-20'),
  117. ('SCP023','Magdalena','Nima','K','1996-02-29','2015-03-25','2023-03-25'),
  118. ('SCP024','Maciek','Bogil','M','2000-05-11','2018-01-01','2025-01-01'),
  119. ('SCP025','Kacper','Gonil','M','1995-02-15','2018-02-07','2025-02-07'),
  120. ('SCP026','Julia','Masno','K','1990-07-19','2015-11-17','2025-11-17'),
  121. ('SCP027','Grzegorz','Nijak','M','1991-04-01','2009-05-07','2019-05-07'),
  122. ('SCP028','Josh','Gibon','M','1995-07-19','2016-11-17','2019-11-17'),
  123. ('SCP030','Mikolaj','Swiety','M','1995-04-16','2015-06-22','2025-07-22');
  124.  
  125. insert into widzenia(id_widzenia,data_widzenia,id_wieznia,nazwisko_odwiedzajacego)
  126. values
  127. ('W501','2010-08-20 12:30','SCP015','Galkiewicz'),
  128. ('W502','2019-09-30 11:00','SCP016','Derenqowska'),
  129. ('W503','2020-01-10 15:00','SCP024','Mickiewicz'),
  130. ('W504','2006-09-25 12:30','SCP006','Szopik'),
  131. ('W505','2019-10-30 13:00','SCP016','Derenqowska'),
  132. ('W506','2001-04-09 11:00','SCP012','Benko'),
  133. ('W507','2010-07-23 12:30','SCP004','McKenzie'),
  134. ('W508','2009-09-20 11:00','SCP015','Goska'),
  135. ('W509','2019-11-29 12:00','SCP016','Derenqowska'),
  136. ('W510','2006-10-25 12:00','SCP006','Herb'),
  137. ('W511','2010-08-23 12:30','SCP004','McKenzie'),
  138. ('W512','2016-11-27 16:00','SCP028','Martinez'),
  139. ('W513','2009-10-20 12:00','SCP015','Goska'),
  140. ('W514','2019-12-22 11:00','SCP016','Derenqowska'),
  141. ('W515','2020-01-29 12:30','SCP016','Derenqowska'),
  142. ('W516','2020-02-07 12:00','SCP016','Los'),
  143. ('W517','2020-02-27 12:00','SCP016','Los'),
  144. ('W518','2020-11-20 15:30','SCP015','Goska'),
  145. ('W519','2020-02-01 13:30','SCP016','Derenqowska'),
  146. ('W520','2019-02-10 12:30','SCP024','Kowalski'),
  147. ('W521','2015-10-25 12:00','SCP006','Mikolajczuk'),
  148. ('W522','2019-02-01 12:00','SCP016','Derenqowska'),
  149. ('W523','2014-05-09 10:00','SCP012','Benko'),
  150. ('W524','2014-08-23 11:30','SCP004','Bogun'),
  151. ('W525','2017-12-01 13:00','SCP015','Goska'),
  152. ('W526','2015-12-20 12:30','SCP016','Derenqowska'),
  153. ('W527','2018-11-25 16:30','SCP006','Herb'),
  154. ('W528','2019-09-23 12:00','SCP004','Martinez'),
  155. ('W529','2016-12-27 15:00','SCP028','Martinez'),
  156. ('W530','2016-12-20 13:30','SCP015','Goska');
  157.  
  158.  
  159.  
  160. insert into zakwaterowanie(nr_zakwaterowania,nr_celi,nazwisko_wieznia,nazwisko_wspolwieznia,id_bloku,id_wieznia)
  161. values
  162. ('z101','1','Trocky','Szypyr','A','SCP001'),
  163. ('z102','1','Szypyr','Trocky','A','SCP002'),
  164. ('z103','2','Sywruk','Alonzo','A','SCP003'),
  165. ('z104','2','Alonzo','Sywruk','A','SCP004'),
  166. ('z105','3','Tumadredy','Mazur','B','SCP005'),
  167. ('z106','3','Mazur','Tumadredy','B','SCP006'),
  168. ('z107','4','Paduch','puste','A','SCP007'),
  169. ('z108','5','Rychlik','puste','A','SCP008'),
  170. ('z109','6','Killer','Mazur','A','SCP009'),
  171. ('z110','6','Mazur','Killer','A','SCP010'),
  172. ('z111','7','Kampinska','Mack','B','SCP011'),
  173. ('z112','7','Mack','Kampinska','B','SCP012'),
  174. ('z113','8','Azyk','puste','A','SCP013'),
  175. ('z114','8','Kowalska','Niska','B','SCP014'),
  176. ('z115','8','Niska','Kowalska','B','SCP015'),
  177. ('z116','9','Kracz','puste','B','SCP016'),
  178. ('z117','10','Bach','puste','B','SCP017'),
  179. ('z118','11','Klakowak','puste','A','SCP018'),
  180. ('z119','12','Nima','Bozium','B','SCP019'),
  181. ('z120','13','Boken','Gloska','A','SCP020'),
  182. ('z121','12','Bozium','Nima','B','SCP021'),
  183. ('z122','13','Gloska','Boken','A','SCP022'),
  184. ('z123','13','Nima','Bogil','B','SCP023'),
  185. ('z124','14','Bogil','puste','A','SCP024'),
  186. ('z125','15','Gonil','puste','A','SCP025'),
  187. ('z126','16','Masno','puste','B','SCP026'),
  188. ('z127','17','Nijak','Gibon','A','SCP027'),
  189. ('z128','17','Gibon','Nijak','A','SCP028'),
  190. ('z129','18','Karnas','Swiety','A','SCP029'),
  191. ('z130','18','Swiety','Karnas','A','SCP030');
  192.  
  193.  
  194. insert into izolatka(nr_izolatki,id_wieznia,data,powod)
  195. values
  196. ('L1','SCP004','2010-06-07','zamieszki'),
  197. ('L2','SCP004','2010-07-14','stawianie_oporu'),
  198. ('L3','SCP018','2015-05-30','bojka'),
  199. ('L4','SCP029','2015-05-01','posiadanie_broni'),
  200. ('L5','SCP029','2015-05-25','stawianie_oporu');
  201.  
  202.  
  203. insert into pracownicy(id_pracownika,imie_pracownika,nazwisko_pracownika,typ_zmiany)
  204. values
  205. ('P101','Wieslawa','Warchak','Magazyn'),
  206. ('P102','Brajanek','Paczosek','Straznik'),
  207. ('P103','Mirek','Pacza','Straznik'),
  208. ('P104','Arturina','Szpryt','Straznik'),
  209. ('P105','Brajan','Paczosa','Straznik'),
  210. ('P106','Bozena','Nowotyminska','Straznik'),
  211. ('P107','Komieslaw','Dariel','Sprzatacz'),
  212. ('P108','Zdzislaw','Maczywoda','Elektryk'),
  213. ('P109','Choleslaw','Chleb','Sprzatacz'),
  214. ('P110','Boleslaw','Grachamka','Konserwator'),
  215. ('P111','Miloslaw','Dorytos','Kucharz'),
  216. ('P112','Waclaw','Lewus','Kucharz'),
  217. ('P113','Marcin','Prawus','Elektryk'),
  218. ('P114','Andrzej','Bomba','Magazyn'),
  219. ('P115','Jakub','Kubiak','Konserwator'),
  220. ('P116','Pawel','Albrychowicz','Hydraulik'),
  221. ('P117','Piotr','Trzepacz','Konserwator'),
  222. ('P118','Czeslaw','Petarda','Hydraulik'),
  223. ('P119','Artur','Skur','Doktor'),
  224. ('P120','Eryk','Rabarbar','Doktor'),
  225. ('P121','Maryk','Byk','Hydraulik'),
  226. ('P122','Radoslaw','Lokiec','Elektryk'),
  227. ('P123','Kamil','Poziomka','Kucharz'),
  228. ('P124','Mihael','Jegany','Elektryk'),
  229. ('P125','Yumi','Jaglana','Kucharz'),
  230. ('P126','Tomasz','Kasza','Kucharz'),
  231. ('P127','Zdzislaw','Miesien','Sprzatacz'),
  232. ('P128','Mariusz','Pudzianowski','Doktor'),
  233. ('P129','Wladyslaw','Bartoimadlo','Doktor'),
  234. ('P130','Maryk','Polamiecz','Sprzatacz');
  235.  
  236.  
  237.  
  238.  
  239. insert into magazyn(id_magazynu, id_wieznia, ilosc_zarekwirowanych_przedmiotow,opis,id_pracownika)
  240. values
  241. ('M801','SCP001','2','przedmioty latwopalne','P101'),
  242. ('M802','SCP003','5','bron biala','P101'),
  243. ('M803','SCP004','3','narkotyki','P101'),
  244. ('M804','SCP005','1','bron palna','P114'),
  245. ('M805','SCP006','3','pistolet','P101'),
  246. ('M806','SCP007','2','narzedzia','P101'),
  247. ('M807','SCP008','1','telefon','P101'),
  248. ('M808','SCP009','1','zegarek','P101'),
  249. ('M809','SCP010','12','gwozdzie','P114'),
  250. ('M810','SCP011','1','wkretak philipsowy','P101'),
  251. ('M811','SCP012','2','szklo','P101'),
  252. ('M812','SCP013','1','nunchako','P114'),
  253. ('M813','SCP014','1','kusza','P101'),
  254. ('M814','SCP015','14','belty do kuszy','P101'),
  255. ('M815','SCP016','1','katana','P101'),
  256. ('M816','SCP017','3','narzedzia','P101'),
  257. ('M817','SCP018','4','pilnik','P114'),
  258. ('M818','SCP019','2','bron biala','P114'),
  259. ('M819','SCP020','1','luk','P101'),
  260. ('M820','SCP021','23','strzaly do luku','P101'),
  261. ('M821','SCP022','1','telefon','P114'),
  262. ('M822','SCP023','2','telefon','P101'),
  263. ('M823','SCP024','3','wiertlo','P101'),
  264. ('M824','SCP025','2','lopata','P114'),
  265. ('M825','SCP026','1','kilof','P101'),
  266. ('M826','SCP027','1','ladunek wybuchowy','P114'),
  267. ('M827','SCP028','2','telewizor','P101'),
  268. ('M828','SCP029','2','bron biala','P101'),
  269. ('M829','SCP030','28','alkohol','P101'),
  270. ('M830','SCP002','4','telefon','P114');
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top