DDL, податоци, погледи : ddl.sql

File ddl.sql, 12.9 KB (added by 231068, 2 days ago)
Line 
1CREATE TABLE Role
2(
3 role_id SERIAL PRIMARY KEY,
4 role_name VARCHAR(50) NOT NULL UNIQUE
5);
6
7CREATE TABLE Currency
8(
9 currency_id SERIAL PRIMARY KEY,
10 code VARCHAR(10) NOT NULL UNIQUE,
11 name VARCHAR(50) NOT NULL
12
13);
14
15CREATE TABLE Employee
16(
17 employee_id SERIAL PRIMARY KEY,
18 first_name VARCHAR(50) NOT NULL DEFAULT 'UNKNOWN',
19 last_name VARCHAR(50) NOT NULL DEFAULT 'UNKNOWN',
20 position VARCHAR(50),
21 hire_date DATE DEFAULT CURRENT_DATE
22);
23
24CREATE TABLE Branch
25(
26 branch_id SERIAL PRIMARY KEY,
27 branch_name VARCHAR(100) NOT NULL,
28 address VARCHAR(255),
29 city VARCHAR(50)
30);
31
32CREATE TABLE Client
33(
34 client_id SERIAL PRIMARY KEY,
35 first_name VARCHAR(50) NOT NULL DEFAULT 'UNKNOWN',
36 last_name VARCHAR(50) NOT NULL DEFAULT 'UNKNOWN',
37 embg VARCHAR(130) UNIQUE,
38 phone VARCHAR(20),
39 email VARCHAR(100) UNIQUE,
40 address VARCHAR(255),
41 status VARCHAR(20) DEFAULT 'ACTIVE',
42 date_registered DATE DEFAULT CURRENT_DATE,
43 branch_id INT,
44 CONSTRAINT chk_client_status
45 CHECK (status IN ('ACTIVE', 'INACTIVE', 'BLOCKED')),
46 CONSTRAINT chk_client_email
47 CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
48 CONSTRAINT chk_client_phone
49 CHECK (phone ~ '^\+?[0-9]{7,15}$'),
50 FOREIGN KEY (branch_id) REFERENCES Branch (branch_id) ON DELETE RESTRICT ON UPDATE CASCADE
51);
52
53CREATE TABLE Client_user
54(
55 client_user_id SERIAL PRIMARY KEY,
56 username VARCHAR(50) NOT NULL UNIQUE,
57 password_hash VARCHAR(255) NOT NULL,
58 last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
59 status VARCHAR(20) DEFAULT 'ACTIVE',
60 client_id INT NOT NULL,
61 CONSTRAINT chk_client_user_status
62 CHECK (status IN ('ACTIVE', 'LOCKED', 'DISABLED')),
63 CONSTRAINT chk_password_hash_length
64 CHECK (length(password_hash) >= 60),
65 CONSTRAINT chk_password
66 CHECK (
67 password_hash LIKE '$2a$%' OR
68 password_hash LIKE '$2b$%' OR
69 password_hash LIKE '$2y$%'
70 ),
71 FOREIGN KEY (client_id) REFERENCES Client (client_id) ON DELETE CASCADE ON UPDATE CASCADE
72);
73
74CREATE TABLE Loan
75(
76 loan_id SERIAL PRIMARY KEY,
77 loan_type VARCHAR(20) NOT NULL,
78 amount DECIMAL(15, 2) NOT NULL CHECK (amount > 0),
79 interest_rate DECIMAL(5, 2) NOT NULL CHECK (interest_rate >= 0),
80 start_date DATE NOT NULL,
81 end_date DATE,
82 status VARCHAR(20) DEFAULT 'PENDING',
83 approved_date DATE,
84 client_id INT NOT NULL,
85 employee_id INT,
86 CONSTRAINT chk_loan_status
87 CHECK (status IN ('PENDING', 'APPROVED', 'REJECTED', 'CLOSED')),
88 CONSTRAINT chk_loan_dates
89 CHECK (end_date IS NULL OR end_date > start_date),
90 CONSTRAINT chk_interest_range
91 CHECK (interest_rate BETWEEN 0 AND 100),
92 CONSTRAINT chek_status
93 CHECK ((status = 'APPROVED' AND approved_date IS NOT NULL) OR (status <> 'APPROVED')),
94 FOREIGN KEY (client_id) REFERENCES Client (client_id) ON DELETE RESTRICT ON UPDATE CASCADE,
95 FOREIGN KEY (employee_id) REFERENCES Employee (employee_id) ON DELETE RESTRICT ON UPDATE CASCADE
96);
97
98CREATE TABLE Account
99(
100 account_id SERIAL PRIMARY KEY,
101 account_number VARCHAR(30) NOT NULL UNIQUE,
102 account_type VARCHAR(20) NOT NULL,
103 balance DECIMAL(20, 2) DEFAULT 0,
104 currency_id INT,
105 open_date DATE DEFAULT CURRENT_DATE,
106 status VARCHAR(20) DEFAULT 'ACTIVE',
107 client_id INT NOT NULL,
108 client_user_id INT,
109 loan_id INT,
110 branch_id INT NOT NULL,
111 CONSTRAINT chk_account_status
112 CHECK (status IN ('ACTIVE', 'CLOSED', 'BLOCKED')),
113 CONSTRAINT chk_balance_positive
114 CHECK (balance >= 0),
115 CONSTRAINT chk_account_type
116 CHECK (account_type IN ('SAVINGS', 'CHECKING', 'CREDIT', 'LOAN', 'INVESTMENT')),
117 FOREIGN KEY (client_id) REFERENCES Client (client_id) ON DELETE RESTRICT ON UPDATE CASCADE,
118 FOREIGN KEY (client_user_id) REFERENCES Client_user (client_user_id) ON DELETE SET NULL ON UPDATE CASCADE,
119 FOREIGN KEY (loan_id) REFERENCES Loan (loan_id) ON DELETE RESTRICT ON UPDATE CASCADE,
120 FOREIGN KEY (currency_id) REFERENCES Currency (currency_id) ON DELETE SET NULL ON UPDATE CASCADE,
121 FOREIGN KEY (branch_id) REFERENCES Branch (branch_id) ON DELETE RESTRICT ON UPDATE CASCADE
122);
123
124CREATE TABLE SavingsAccount
125(
126 account_id INT PRIMARY KEY REFERENCES Account (account_id) ON DELETE CASCADE ON UPDATE CASCADE,
127 interest_rate DECIMAL(5, 2) NOT NULL CHECK (interest_rate BETWEEN 0 AND 100),
128 interest_period VARCHAR(20) NOT NULL CHECK (interest_period IN ('DAILY', 'MONTHLY', 'YEARLY')),
129 minimum_balance DECIMAL(20, 2) DEFAULT 0 CHECK (minimum_balance >= 0),
130 capitalization_type VARCHAR(20) NOT NULL CHECK (capitalization_type IN ('SIMPLE', 'COMPOUND'))
131);
132
133CREATE TABLE Loan_installment
134(
135 installment_id SERIAL PRIMARY KEY,
136 installment_number INT NOT NULL,
137 due_date DATE NOT NULL,
138 amount DECIMAL(15, 2) NOT NULL CHECK (amount > 0),
139 status VARCHAR(20),
140 paid_date DATE,
141 loan_id INT NOT NULL,
142 CONSTRAINT chk_installment_status
143 CHECK (status IN ('PENDING', 'PAID', 'LATE')),
144 CONSTRAINT uq_installment_per_loan
145 UNIQUE (installment_number, loan_id),
146 FOREIGN KEY (loan_id) REFERENCES Loan (loan_id) ON DELETE CASCADE ON UPDATE CASCADE
147);
148
149CREATE TABLE Collateral
150(
151 collateral_id SERIAL PRIMARY KEY,
152 type VARCHAR(30) NOT NULL,
153 description VARCHAR(255),
154 value DECIMAL(15, 2) NOT NULL,
155 status VARCHAR(20),
156 date_added DATE DEFAULT CURRENT_DATE,
157 loan_id INT NOT NULL,
158 client_id INT NOT NULL,
159
160 CONSTRAINT chk_collateral_value
161 CHECK (value >= 0),
162
163 CONSTRAINT chk_collateral_status
164 CHECK (status IN ('ACTIVE', 'RELEASED', 'PLEDGED', 'DEFAULTED')),
165
166 FOREIGN KEY (loan_id) REFERENCES Loan (loan_id) ON DELETE CASCADE ON UPDATE CASCADE,
167 FOREIGN KEY (client_id) REFERENCES Client (client_id) ON DELETE RESTRICT ON UPDATE CASCADE
168);
169
170CREATE TABLE Transaction_type
171(
172 transaction_type_id SERIAL PRIMARY KEY,
173 type_name VARCHAR(30) NOT NULL,
174 description VARCHAR(225),
175 CHECK (type_name IN ('DEPOSIT', 'WITHDRAWAL', 'TRANSFER', 'PAYMENT', 'FEE', 'INTEREST', 'REFUND', 'LOAN_PAYMENT',
176 'CURRENCY_EXCHANGE'))
177);
178
179CREATE TABLE Payment_method
180(
181 method_id SERIAL PRIMARY KEY,
182 method_name VARCHAR(30) NOT NULL,
183 description VARCHAR(225),
184 CONSTRAINT chk_payment_method
185 CHECK (method_name IN ('CASH', 'DEBIT_CARD', 'CREDIT_CARD', 'BANK_TRANSFER', 'MOBILE_PAYMENT', 'CHECK'))
186);
187
188CREATE TABLE Counter
189(
190 counter_id SERIAL PRIMARY KEY,
191 counter_number INT NOT NULL,
192 status VARCHAR(20),
193 branch_id INT NOT NULL,
194 CONSTRAINT chk_counter_status
195 CHECK (status IN ('OPEN', 'CLOSED', 'MAINTENANCE')),
196 CONSTRAINT uq_counter_per_branch
197 UNIQUE (counter_number, branch_id),
198 FOREIGN KEY (branch_id) REFERENCES Branch (branch_id) ON DELETE RESTRICT ON UPDATE CASCADE
199);
200
201CREATE TABLE Branch_employee
202(
203 emp_branch_id SERIAL PRIMARY KEY,
204 branch_id INT NOT NULL,
205 employee_id INT NOT NULL,
206 start_date DATE DEFAULT CURRENT_DATE,
207 end_date DATE,
208 status VARCHAR(20) DEFAULT 'ACTIVE',
209 CONSTRAINT chk_branch_employee_dates
210 CHECK (end_date IS NULL OR end_date > start_date),
211 CONSTRAINT chk_branch_employee_status
212 CHECK (status IN ('ACTIVE', 'INACTIVE', 'SUSPENDED', 'TEMPORARY_ASSIGNMENT')),
213 FOREIGN KEY (branch_id) REFERENCES Branch (branch_id) ON DELETE RESTRICT ON UPDATE CASCADE,
214 FOREIGN KEY (employee_id) REFERENCES Employee (employee_id) ON DELETE CASCADE ON UPDATE CASCADE
215);
216
217CREATE TABLE Employee_counter
218(
219 emp_counter_id SERIAL PRIMARY KEY,
220 start_shift TIMESTAMP NOT NULL,
221 end_shift TIMESTAMP,
222 employee_id INT NOT NULL,
223 counter_id INT NOT NULL,
224 CONSTRAINT chk_shift_dates
225 CHECK (end_shift IS NULL OR end_shift > start_shift),
226 FOREIGN KEY (employee_id) REFERENCES Employee (employee_id) ON DELETE CASCADE ON UPDATE CASCADE,
227 FOREIGN KEY (counter_id) REFERENCES Counter (counter_id) ON DELETE RESTRICT ON UPDATE CASCADE
228);
229
230CREATE TABLE Transaction
231(
232 transaction_id SERIAL PRIMARY KEY,
233 amount DECIMAL(20, 2) NOT NULL CHECK (amount > 0),
234 transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
235 status VARCHAR(20) DEFAULT 'PENDING',
236 description VARCHAR(225),
237 reference_number VARCHAR(30) UNIQUE,
238 account_id INT NOT NULL,
239 account_id2 INT,
240 transaction_type_id INT,
241 payment_method_id INT,
242 currency_id INT,
243 currency_id2 INT,
244 installment_id INT,
245 emp_counter_id INT,
246 loan_id INT,
247 CONSTRAINT chk_transaction_status
248 CHECK (status IN ('PENDING', 'COMPLETED', 'FAILED', 'CANCELLED')),
249 CONSTRAINT chk_transfer_logic
250 CHECK (account_id2 IS NULL OR account_id <> account_id2),
251 FOREIGN KEY (account_id) REFERENCES Account (account_id) ON DELETE RESTRICT ON UPDATE CASCADE,
252 FOREIGN KEY (account_id2) REFERENCES Account (account_id) ON DELETE SET NULL ON UPDATE CASCADE,
253 FOREIGN KEY (transaction_type_id) REFERENCES Transaction_type (transaction_type_id) ON DELETE RESTRICT ON UPDATE CASCADE,
254 FOREIGN KEY (payment_method_id) REFERENCES Payment_method (method_id) ON DELETE RESTRICT ON UPDATE CASCADE,
255 FOREIGN KEY (currency_id) REFERENCES Currency (currency_id) ON DELETE SET NULL ON UPDATE CASCADE,
256 FOREIGN KEY (currency_id2) REFERENCES Currency (currency_id) ON DELETE SET NULL ON UPDATE CASCADE,
257 FOREIGN KEY (installment_id) REFERENCES Loan_installment (installment_id) ON DELETE SET NULL ON UPDATE CASCADE,
258 FOREIGN KEY (emp_counter_id) REFERENCES Employee_counter (emp_counter_id) ON DELETE RESTRICT ON UPDATE CASCADE,
259 FOREIGN KEY (loan_id) REFERENCES Loan (loan_id) ON DELETE RESTRICT ON UPDATE CASCADE
260);
261
262CREATE TABLE Receipt
263(
264 receipt_id SERIAL PRIMARY KEY,
265 print_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
266 receipt_number VARCHAR(30) NOT NULL UNIQUE,
267 transaction_id INT NOT NULL UNIQUE,
268 FOREIGN KEY (transaction_id) REFERENCES Transaction (transaction_id) ON DELETE CASCADE ON UPDATE CASCADE
269);
270
271CREATE TABLE Daily_report
272(
273 report_id SERIAL PRIMARY KEY,
274 report_date DATE NOT NULL,
275 total_transactions INT NOT NULL DEFAULT 0,
276 total_amount DECIMAL(20, 2) NOT NULL DEFAULT 0.00,
277 branch_id INT NOT NULL,
278 FOREIGN KEY (branch_id) REFERENCES Branch (branch_id) ON DELETE RESTRICT ON UPDATE CASCADE
279);
280
281CREATE TABLE Exchange_rate
282(
283 rate_id SERIAL PRIMARY KEY,
284 rate DECIMAL(15, 6) NOT NULL CHECK (rate > 0),
285 date_updated DATE DEFAULT CURRENT_DATE,
286 currency_id INT NOT NULL,
287 CONSTRAINT uq_currency_date
288 UNIQUE (currency_id, date_updated),
289 FOREIGN KEY (currency_id) REFERENCES Currency (currency_id) ON DELETE RESTRICT ON UPDATE CASCADE
290);
291
292CREATE TABLE Bank_user
293(
294 user_id SERIAL PRIMARY KEY,
295 username VARCHAR(50) NOT NULL UNIQUE,
296 password_hash VARCHAR(255) NOT NULL,
297 role_id INT NOT NULL,
298 employee_id INT NOT NULL,
299 CONSTRAINT chk_password_hash_length
300 CHECK (length(password_hash) >= 60),
301 CONSTRAINT chk_password
302 CHECK (
303 password_hash LIKE '$2a$%' OR
304 password_hash LIKE '$2b$%' OR
305 password_hash LIKE '$2y$%'
306 ),
307 FOREIGN KEY (role_id) REFERENCES Role (role_id) ON DELETE RESTRICT ON UPDATE CASCADE,
308 FOREIGN KEY (employee_id) REFERENCES Employee (employee_id) ON DELETE CASCADE ON UPDATE CASCADE
309);
310
311CREATE TABLE InterestPayment
312(
313 interest_payment_id SERIAL PRIMARY KEY,
314 amount DECIMAL(15, 2) NOT NULL CHECK (amount > 0),
315 payment_date DATE NOT NULL DEFAULT CURRENT_DATE,
316 period_start DATE NOT NULL,
317 period_end DATE NOT NULL,
318 account_id INT NOT NULL,
319 transaction_id INT NOT NULL,
320 FOREIGN KEY (transaction_id) REFERENCES Transaction (transaction_id) ON DELETE RESTRICT ON UPDATE CASCADE,
321 FOREIGN KEY (account_id) REFERENCES Account (account_id) ON DELETE CASCADE ON UPDATE CASCADE,
322 CONSTRAINT chk_period_dates CHECK (period_end >= period_start)
323);