· 8 years ago · Dec 02, 2017, 10:48 PM
1CREATE SCHEMA bank;
2
3 CREATE TABLE `bank`.`Klient` ( `numerNIK` INT(8) NOT NULL ,
4 `hasło` VARCHAR(15) NOT NULL , `imie` VARCHAR(15) NOT NULL ,
5 `nazwisko` VARCHAR(45) NOT NULL , `wiek` INT(3) NOT NULL ,
6 PRIMARY KEY (`numerNIK`))
7 ENGINE = InnoDB;
8
9CREATE TABLE `bank`.`Konto` ( `numerRachunku` INT(20) NOT NULL ,
10 `nazwaRachunku` VARCHAR(15) NOT NULL ,
11 `typRachunku` VARCHAR(15) NOT NULL ,
12 `saldo` FLOAT(10) NOT NULL ,
13 `k_numerNIK` INT(8) NULL ,
14 UNIQUE (`numerRachunku`),
15 CONSTRAINT `fk_konta`
16 FOREIGN KEY (`k_numerNIK`)
17 REFERENCES `bank`.`Klient`(`numerNIK`)
18 ON DELETE RESTRICT
19 ON UPDATE RESTRICT)
20ENGINE = InnoDB;
21
22
23
24 CREATE TABLE `bank`.`Kierownik`( `kierownik_id` INT(4) NOT NULL ,
25 `hasło` VARCHAR(15) NOT NULL , `imie` VARCHAR(15) NOT NULL ,
26 `nazwisko` VARCHAR(45) NOT NULL , PRIMARY KEY (`kierownik_id`))
27 ENGINE = InnoDB;
28
29
30
31 CREATE TABLE `bank`.`Bankier` ( `bankier_id` INT(4) NOT NULL ,
32 `hasło` VARCHAR(15) NOT NULL , `imie` VARCHAR(15) NOT NULL ,
33 `nazwisko` VARCHAR(45) NOT NULL , PRIMARY KEY (`bankier_id`))
34 ENGINE = InnoDB;
35
36
37
38
39 CREATE TABLE `bank`.`Wyplaty` ( `numerNIK` INT(8) NOT NULL ,
40 `kwota` FLOAT(10) NOT NULL ,
41 `data` DATE NOT NULL ,
42 `zatwierdzone` BIT NULL ,
43 `bankier_id` INT(4) NULL ,
44 `k_numerNIK` INT(8) NULL ,
45 `kierownik_id` INT(4) NULL ,
46 PRIMARY KEY (`numerNIK`),
47 INDEX (`data`),
48 CONSTRAINT `fk_wyplaty`
49 FOREIGN KEY (`bankier_id`)
50 REFERENCES `bank`.`Bankier`(`bankier_id`)
51 ON DELETE RESTRICT
52 ON UPDATE RESTRICT,
53 CONSTRAINT `fk_zatwierdzanie`
54 FOREIGN KEY (kierownik_id)
55 REFERENCES `bank`.`Kierownik`(kierownik_id)
56 ON DELETE RESTRICT
57 ON UPDATE RESTRICT,
58 CONSTRAINT `fk_wyplaty1`
59 FOREIGN KEY (`k_numerNIK`)
60 REFERENCES `bank`.`Klient`(`numerNIK`)
61 ON DELETE RESTRICT
62 ON UPDATE RESTRICT)
63 ENGINE = InnoDB;
64
65 CREATE TABLE `bank`.`Wplaty` ( `numerNIK` INT(8) NOT NULL ,
66 `kwota` FLOAT(10) NOT NULL ,
67 `data` DATE NOT NULL ,
68 `zatwierdzone` BIT NULL ,
69 `bankier_id` INT(4) NULL ,
70 `k_numerNIK` INT(8) NULL ,
71 `kierownik_id` INT(4) NULL ,
72 PRIMARY KEY (`numerNIK`),
73 INDEX (`data`),
74 CONSTRAINT `fk_wplaty`
75 FOREIGN KEY (`bankier_id`)
76 REFERENCES `bank`.`Bankier`(`bankier_id`)
77 ON DELETE RESTRICT
78 ON UPDATE RESTRICT,
79 CONSTRAINT `fk_zatwierdzanie2`
80 FOREIGN KEY (kierownik_id)
81 REFERENCES `bank`.`Kierownik`(kierownik_id)
82 ON DELETE RESTRICT
83 ON UPDATE RESTRICT,
84 CONSTRAINT `fk_wplaty1`
85 FOREIGN KEY (`k_numerNIK`)
86 REFERENCES `bank`.`Klient`(`numerNIK`)
87 ON DELETE RESTRICT
88 ON UPDATE RESTRICT)
89 ENGINE = InnoDB;
90
91
92 CREATE TABLE `bank`.`Przelewy` (
93 `numerNIK1` INT(8) NOT NULL ,
94 `numerNIK2` INT(8) NOT NULL ,
95 `kwota` FLOAT(10) NOT NULL ,
96 `data` DATE NOT NULL ,
97 `zatwierdzone` BIT NULL ,
98 `bankier_id` INT(4) NULL ,
99 /*`k_numerNIK` INT(8) NOT NULL ,*/
100 `kierownik_id` INT(4) NULL ,
101 PRIMARY KEY (`numerNIK1`, `numerNIK2`),
102 INDEX (`data`),
103 CONSTRAINT `fk_przelewu`
104 FOREIGN KEY (`bankier_id`)
105 REFERENCES `bank`.`Bankier`(`bankier_id`)
106 ON DELETE RESTRICT
107 ON UPDATE RESTRICT,
108 CONSTRAINT `fk_zatwierdzanie1`
109 FOREIGN KEY (kierownik_id)
110 REFERENCES `bank`.`Kierownik`(kierownik_id)
111 ON DELETE RESTRICT
112 ON UPDATE RESTRICT)
113 /*
114 CONSTRAINT `fk_przelewu1`
115 FOREIGN KEY (`k_numerNIK`)
116 REFERENCES `bank`.`Klient`(`numerNIK1`)
117 ON DELETE RESTRICT
118 ON UPDATE RESTRICT)
119 */
120 ENGINE = InnoDB;
121
122
123 CREATE TABLE `bank`.`Lokata` (`id` INT(4) NOT NULL AUTO_INCREMENT,
124 `stan` FLOAT(10) NOT NULL ,
125 `oprocentowanie` INT(3) NOT NULL ,
126 `k_numerNIK` INT(8) NULL,
127 PRIMARY KEY (`id`),
128 CONSTRAINT `fk_lokaty`
129 FOREIGN KEY (`k_numerNIK`)
130 REFERENCES `bank`.`Klient`(`numerNIK`)
131 ON DELETE RESTRICT
132 ON UPDATE RESTRICT )
133 ENGINE = InnoDB;
134
135 CREATE TABLE `bank`.`Kredyt` ( `numerRachunku` INT(20) NOT NULL ,
136 `kwota` FLOAT(10) NOT NULL ,
137 `k_numerNIK` INT(8) NULL ,
138 UNIQUE (`numerRachunku`),
139 CONSTRAINT `fk_kredytu`
140 FOREIGN KEY (`k_numerNIK`)
141 REFERENCES `bank`.`Klient`(`numerNIK`)
142 ON DELETE RESTRICT
143 ON UPDATE RESTRICT)
144 ENGINE = InnoDB;
145
146
147
148/*
149
150 -- ----------------------------------------------------------------------
151 -- PlaceHolder table for view `bank`.`informacje_o_koncie`
152 -- ----------------------------------------------------------------------
153CREATE TABLE IF NOT EXISTS `bank`.`informacje_o_koncie`(`'numerNIK'` INT, `'haslo'`
154INT, `'imie'` INT, `'nazwisko'` INT, `'wiek'` INT, `'numerRachunku'` INT,
155`'nazwaRachunku'` INT, `'typRachunku'` INT);
156
157 -- ----------------------------------------------------------------------
158 -- View `bank`.`informacje_o_koncie`
159 -- ----------------------------------------------------------------------
160DROP VIEW IF EXISTS `bank`.`informacje_o_koncie`;
161DROP TABLE IF EXISTS `bank`.`informacje_o_koncie`;
162USE `bank`;
163CREATE OR REPLACE VIEW `informacje_o_koncie` AS
164SELECT
165 kl.numerNIK 'numerNIK',kl.haslo 'haslo',kl.imie 'imie',kl.nazwisko 'nazwisko',
166 kl.wiek 'wiek',k.numerRachunku 'numerRachunku',
167 k.nazwaRachunku 'nazwaRachunku',k.typRachunku 'typRachunku'
168FROM
169 Klient kl
170 JOIN
171 Konto k ON kl.numerNIK = k.numerNIK;
172
173
174
175 -- ----------------------------------------------------------------------
176 -- Data for table `bank`.`Klient`
177 -- ----------------------------------------------------------------------
178START TRANSACTION;
179USE `bank`;
180INSERT INTO `bank`.`Klient`( `numerNIK`, `hasło`, `imie`, `nazwisko`, `wiek`)
181VALUES ('13253265', 'a5f495f02af0c779116342cff78dfbbc', 'Sebastian', 'Szkoluda', '21');
182INSERT INTO `bank`.`Klient`( `numerNIK`, `hasło`, `imie`, `nazwisko`, `wiek`)
183VALUES ('13253265', '6656910800c58e1e9e6bc8230805a381', 'Radek', 'Auguscik', '21');
184INSERT INTO `bank`.`Klient`( `numerNIK`, `hasło`, `imie`, `nazwisko`, `wiek`)
185VALUES ('57253365', '1d7c2923c1684726dc23d2901c4d8157', 'Adam', 'Jan', '42');
186INSERT INTO `bank`.`Klient`( `numerNIK`, `hasło`, `imie`, `nazwisko`, `wiek`)
187VALUES ('25258215', 'fa27ef3ef6570e32a79e74deca7c1bc3', 'Jan', 'Adam', '36');
188COMMIT;
189
190
191 -- ----------------------------------------------------------------------
192 -- Data for table `bank`.`Konto`
193 -- ----------------------------------------------------------------------
194START TRANSACTION;
195USE `bank`;
196INSERT INTO `bank`.`Konto`( `numerRachunku`, `nazwaRachunku`, `typRachunku`, `saldo`, `k_numerNIK`)
197VALUES ('09249010440000320094007370', 'Rachunek główny', 'standard', '2321','13253265');
198INSERT INTO `bank`.`Konto`( `numerRachunku`, `nazwaRachunku`, `typRachunku`, `saldo`, `k_numerNIK`)
199VALUES ('84 2490 0005 0000 4566 9889 2694', 'Rachunek dodatkowy', 'VIP', '25321','13253265');
200INSERT INTO `bank`.`Konto`( `numerRachunku`, `nazwaRachunku`, `typRachunku`, `saldo` ,`k_numerNIK`)
201VALUES ('30 1540 1056 2069 5000 0804 0001', 'Rachunek walutowy', 'VIP', '41232','57253365');
202INSERT INTO `bank`.`Konto`( `numerRachunku`, `nazwaRachunku`, `typRachunku`, `saldo`, `k_numerNIK`)
203VALUES ('09 2490 1044 2069 5000 0804 0001', 'Rachunek walutowy', 'Standard', '36436','25258215');
204COMMIT;
205
206 -- ----------------------------------------------------------------------
207 -- Data for table `bank`.`Bankier`
208 -- ----------------------------------------------------------------------
209START TRANSACTION;
210USE `bank`;
211INSERT INTO `bank`.`Bankier`( `bankier_id`, `hasło`, `imie`, `nazwisko`)
212VALUES ('1325', 'a5f495f02af0c779116342cff78dfbbc', 'Sebastian', 'Kowalski');
213INSERT INTO `bank`.`Bankier`( `bankier_id`, `hasło`, `imie`, `nazwisko`)
214VALUES ('1265', '6656910800c58e1e9e6bc8230805a381', 'Radek', 'Nowak');
215INSERT INTO `bank`.`Bankier`( `bankier_id`, `hasło`, `imie`, `nazwisko`)
216VALUES ('3365', '1d7c2923c1684726dc23d2901c4d8157', 'Adam', 'Adam');
217INSERT INTO `bank`.`Bankier`( `bankier_id`, `hasło`, `imie`, `nazwisko`)
218VALUES ('2525', 'fa27ef3ef6570e32a79e74deca7c1bc3', 'Jan', 'Jan');
219COMMIT;
220
221 -- ----------------------------------------------------------------------
222 -- Data for table `bank`.`Kierownik`
223 -- ----------------------------------------------------------------------
224START TRANSACTION;
225USE `bank`;
226INSERT INTO `bank`.`Kierownik`( `kierownik_id`, `hasło`, `imie`, `nazwisko`)
227VALUES ('1265', 'a5f495f02af0c779116342cff78dfbbc', 'Jarek', 'Szkoluda');
228INSERT INTO `bank`.`Kierownik`( `kierownik_id`, `hasło`, `imie`, `nazwisko`)
229VALUES ('1265', '6656910800c58e1e9e6bc8230805a381', 'Mateusz', 'Auguscik');
230INSERT INTO `bank`.`Kierownik`( `kierownik_id`, `hasło`, `imie`, `nazwisko`)
231VALUES ('5365', '1d7c2923c1684726dc23d2901c4d8157', 'Jerzy', 'Jan');
232INSERT INTO `bank`.`Kierownik`( `kierownik_id`, `hasło`, `imie`, `nazwisko`)
233VALUES ('2215', 'fa27ef3ef6570e32a79e74deca7c1bc3', 'Adam', 'Adam');
234COMMIT;
235
236 -- ----------------------------------------------------------------------
237 -- Data for table `bank`.`Wplaty`
238 -- ----------------------------------------------------------------------
239START TRANSACTION;
240USE `bank`;
241INSERT INTO `bank`.`Wplaty`( `numerNIK1`, `kwota`, `data`)
242VALUES ('13253265', '2341', '2016-01-21 15:32:43');
243INSERT INTO `bank`.`Wplaty`( `numerNIK1`, `kwota`, `data`)
244VALUES ('33253265', '2456', '2016-02-26 11:23:43');
245INSERT INTO `bank`.`Wplaty`( `numerNIK1`, `kwota`, `data`)
246VALUES ('32253265', '6532', '2017-05-24 21:42:11');
247INSERT INTO `bank`.`Wplaty`( `numerNIK1`, `kwota`, `data`)
248VALUES ('14253265', '7621', '2014-08-24 22:11:45');
249COMMIT;
250
251 -- ----------------------------------------------------------------------
252 -- Data for table `bank`.`Wyplaty`
253 -- ----------------------------------------------------------------------
254START TRANSACTION;
255USE `bank`;
256INSERT INTO `bank`.`Wyplaty`( `numerNIK1`, `kwota`, `data`)
257VALUES ('13253265', '2641', '2016-11-24 15:32:43');
258INSERT INTO `bank`.`Wyplaty`( `numerNIK1`, `kwota`, `data`)
259VALUES ('33253265', '6456', '2016-03-16 11:23:43');
260INSERT INTO `bank`.`Wyplaty`( `numerNIK1`, `kwota`, `data`)
261VALUES ('32253265', '6582', '2017-09-14 21:42:11');
262INSERT INTO `bank`.`Wyplaty`( `numerNIK1`, `kwota`, `data`)
263VALUES ('14253265', '621', '2014-05-24 22:11:45');
264COMMIT;
265
266
267 -- ----------------------------------------------------------------------
268 -- Data for table `bank`.`Przelewy`
269 -- ----------------------------------------------------------------------
270START TRANSACTION;
271USE `bank`;
272INSERT INTO `bank`.`Przelewy`( `numerNIK1`,`numerNIK2`, `kwota`, `data`)
273VALUES ('13253265','33253265', '2341', '2016-01-21 15:32:43');
274INSERT INTO `bank`.`Przelewy`( `numerNIK1`,`numerNIK2`, `kwota`, `data`)
275VALUES ('33253265','13253265', '2456', '2016-02-26 11:23:43');
276INSERT INTO `bank`.`Przelewy`( `numerNIK1`,`numerNIK2`, `kwota`, `data`)
277VALUES ('32253265','14253265', '6532', '2017-05-24 21:42:11');
278INSERT INTO `bank`.`Przelewy`( `numerNIK1`,`numerNIK2`, `kwota`, `data`)
279VALUES ('14253265','32253265', '7621', '2014-08-24 22:11:45');
280COMMIT;
281
282 -- ----------------------------------------------------------------------
283 -- Data for table `bank`.`Kredyt`
284 -- ----------------------------------------------------------------------
285START TRANSACTION;
286USE `bank`;
287INSERT INTO `bank`.`Kredyt`( `numerRachunku`, `kwota`)
288VALUES ('84 2490 0005 0000 4566 9889 2694', '26141');
289INSERT INTO `bank`.`Kredyt`( `numerRachunku`, `kwota`)
290VALUES ('30 1540 1056 2069 5000 0804 0001', '64256');
291INSERT INTO `bank`.`Kredyt`( `numerRachunku`, `kwota`)
292VALUES ('32 1541 1056 2069 5000 0804 0001', '65482');
293INSERT INTO `bank`.`Kredyt`( `numerRachunku`, `kwota`)
294VALUES ('34 1545 1056 2069 5000 0804 0001', '11621');
295COMMIT;
296
297 -- ----------------------------------------------------------------------
298 -- Data for table `bank`.`Lokata`
299 -- ----------------------------------------------------------------------
300START TRANSACTION;
301USE `bank`;
302INSERT INTO `bank`.`Lokata`( `stan`,`oprocentowanie`)
303VALUES ('3265','15');
304INSERT INTO `bank`.`Lokata`( `stan`,`oprocentowanie`)
305VALUES ('3565','4');
306INSERT INTO `bank`.`Lokata`( `stan`,`oprocentowanie`)
307VALUES ('5325','17');
308INSERT INTO `bank`.`Lokata`( `stan`,`oprocentowanie`)
309VALUES ('1425','3');
310COMMIT;
311
312 -- ----------------------------------------------------------------------
313 -- Data for table `bank`.`Zatwierdzenie_transakcji`
314 -- ----------------------------------------------------------------------
315START TRANSACTION;
316USE `bank`;
317INSERT INTO `bank`.`Zatwierdzenie_transakcji`( `data`)
318VALUES ('2016-06-13 15:42:43');
319INSERT INTO `bank`.`Zatwierdzenie_transakcji`( `data`)
320VALUES ('2016-03-01 17:52:43');
321INSERT INTO `bank`.`Zatwierdzenie_transakcji`( `data`)
322VALUES ('2016-09-11 11:31:43');
323INSERT INTO `bank`.`Zatwierdzenie_transakcji`( `data`)
324VALUES ('2016-11-30 18:22:43');
325COMMIT;
326
327*\