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