149 lines
4.6 KiB
SQL
149 lines
4.6 KiB
SQL
-- Demo banking schema used by the first lecture.
|
|
-- The accounts_clients table models a many-to-many relationship between clients and accounts.
|
|
-- TABLES
|
|
CREATE TABLE banks (
|
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
code VARCHAR(10) NOT NULL UNIQUE
|
|
);
|
|
|
|
CREATE TABLE clients (
|
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE accounts (
|
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
bank_id BIGINT NOT NULL,
|
|
account_number VARCHAR(50) NOT NULL UNIQUE,
|
|
balance NUMERIC(12,2) DEFAULT 0,
|
|
currency VARCHAR(10) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (bank_id) REFERENCES banks(id)
|
|
);
|
|
|
|
CREATE TABLE accounts_clients (
|
|
client_id BIGINT NOT NULL,
|
|
account_id BIGINT NOT NULL,
|
|
PRIMARY KEY (client_id, account_id),
|
|
FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE cards (
|
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
account_id BIGINT NOT NULL,
|
|
client_id BIGINT NOT NULL,
|
|
card_number VARCHAR(30) NOT NULL UNIQUE,
|
|
expiration DATE NOT NULL,
|
|
cvv VARCHAR(4) NOT NULL,
|
|
type VARCHAR(20) NOT NULL,
|
|
FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
|
|
);
|
|
|
|
|
|
-- INSERTS
|
|
-- Seed data intentionally mixes banks, currencies, shared accounts, and cards for SELECT exercises.
|
|
INSERT INTO banks (name, code) VALUES
|
|
('Ceska sporitelna', '0800'),
|
|
('CSOB', '0300'),
|
|
('Komercni banka', '0100'),
|
|
('Moneta', '0600'),
|
|
('Fio banka', '2010');
|
|
|
|
INSERT INTO clients (name, email) VALUES
|
|
('Jan Novak', 'jan.novak1@mail.cz'),
|
|
('Petr Svoboda', 'petr.svoboda@mail.cz'),
|
|
('Karel Dvorak', 'karel.dvorak@mail.cz'),
|
|
('Martin Vesely', 'martin.vesely@mail.cz'),
|
|
('Tomas Prochazka', 'tomas.prochazka@mail.cz'),
|
|
('Jakub Horak', 'jakub.horak@mail.cz'),
|
|
('Filip Cerny', 'filip.cerny@mail.cz'),
|
|
('Adam Kriz', 'adam.kriz@mail.cz'),
|
|
('Lukas Malek', 'lukas.malek@mail.cz'),
|
|
('Ondrej Novak', 'ondrej.novak@mail.cz'),
|
|
('David Ruzicka', 'david.ruzicka@mail.cz'),
|
|
('Radek Fiala', 'radek.fiala@mail.cz');
|
|
|
|
INSERT INTO accounts (bank_id, account_number, balance, currency) VALUES
|
|
(1, '100001/0800', 15000.00, 'CZK'),
|
|
(1, '100002/0800', 250.50, 'CZK'),
|
|
(1, '100003/0800', 88.50, 'CZK'),
|
|
(1, '100004/0800', 3200.00, 'USD'),
|
|
(2, '200001/0300', 99999.99, 'CZK'),
|
|
(2, '200002/0300', 1200.00, 'EUR'),
|
|
(2, '200003/0300', 15000.00, 'USD'),
|
|
(3, '300001/0100', 5400.00, 'CZK'),
|
|
(3, '300002/0100', 780.00, 'CZK'),
|
|
(3, '300003/0100', 780.00, 'USD'),
|
|
(4, '400001/0600', 300.00, 'CZK'),
|
|
(4, '400002/0600', 45000.00, 'CZK'),
|
|
(4, '400003/0600', 19546.00, 'CZK'),
|
|
(4, '400004/0600', 0.00, 'USD'),
|
|
(5, '500001/2010', 999.00, 'EUR'),
|
|
(5, '500002/2010', 123456.78, 'CZK'),
|
|
(5, '500003/2010', 250.00, 'USD');
|
|
|
|
|
|
|
|
|
|
|
|
INSERT INTO accounts_clients (client_id, account_id) VALUES
|
|
(1, 1),
|
|
(1, 2),
|
|
(1, 13),
|
|
(2, 1),
|
|
(2, 3),
|
|
(2, 14),
|
|
(3, 3),
|
|
(3, 4),
|
|
(3, 11),
|
|
(3, 15),
|
|
(4, 4),
|
|
(4, 5),
|
|
(4, 16),
|
|
(5, 5),
|
|
(5, 6),
|
|
(5, 17),
|
|
(6, 6),
|
|
(6, 7),
|
|
(7, 7),
|
|
(7, 8),
|
|
(8, 8),
|
|
(8, 9),
|
|
(9, 9),
|
|
(9, 10),
|
|
(10, 10),
|
|
(10, 12),
|
|
(11, 1),
|
|
(12, 2),
|
|
(12, 3);
|
|
|
|
|
|
|
|
INSERT INTO cards (account_id, client_id, card_number, expiration, cvv, type) VALUES
|
|
(1, 1, '1111222233334441', '2028-12-31', '123', 'debit'),
|
|
(1, 2, '1111222233334442', '2027-11-30', '234', 'credit'),
|
|
(2, 1, '1111222233334451', '2026-10-31', '345', 'debit'),
|
|
(3, 2, '1111222233334461', '2029-09-30', '456', 'credit'),
|
|
(3, 3, '1111222233334462', '2027-08-31', '567', 'debit'),
|
|
(4, 3, '1111222233334471', '2028-07-31', '678', 'debit'),
|
|
(5, 4, '1111222233334481', '2026-06-30', '789', 'credit'),
|
|
(5, 5, '1111222233334482', '2027-05-31', '890', 'debit'),
|
|
(6, 6, '1111222233334491', '2028-04-30', '901', 'debit'),
|
|
(7, 7, '1111222233334501', '2029-03-31', '012', 'credit'),
|
|
(8, 8, '1111222233334511', '2026-02-28', '111', 'debit'),
|
|
(9, 9, '1111222233334521', '2027-01-31', '222', 'credit'),
|
|
(9, 12, '1111222233334533', '2027-10-31', '555', 'debit'),
|
|
(10, 10, '1111222233334531', '2028-12-31', '333', 'debit'),
|
|
(10, 11, '1111222233334532', '2029-11-30', '444', 'credit'),
|
|
(13, 1, '1111222233334541', '2028-12-31', '111', 'debit'),
|
|
(13, 1, '1111222233334542', '2027-11-30', '112', 'credit'),
|
|
(14, 2, '1111222233334551', '2029-10-31', '221', 'debit'),
|
|
(14, 2, '1111222233334552', '2028-09-30', '222', 'credit'),
|
|
(15, 3, '1111222233334561', '2027-08-31', '331', 'debit'),
|
|
(15, 3, '1111222233334562', '2026-07-31', '332', 'credit');
|