x3RsUmea

1DROP DATABASE IF EXISTS FP_SBD_KEL_5;
2CREATE DATABASE FP_SBD_KEL_5;
3USE FP_SBD_KEL_5;
4
5CREATE TABLE ACCOUNT_TYPE (
6    AT_ID CHAR(5) NOT NULL,
7    AT_LIMIT DECIMAL(10,2) NOT NULL,
8    PRIMARY KEY (AT_ID)
9);
10
11CREATE TABLE ACCOUNT (
12    ACC_KTP_NUM CHAR(16) NOT NULL,
13    ACC_AT_ID CHAR(5) NOT NULL,
14    ACC_NAME VARCHAR(60) NOT NULL,
15    ACC_EMAIL VARCHAR(256) NOT NULL,
16    ACC_PHONE_NUM VARCHAR(15) NOT NULL,
17    ACC_ADDRESS VARCHAR(256) NOT NULL,
18    ACC_GENDER CHAR(1) NOT NULL,
19    ACC_DATE_OF_BIRTH DATE NOT NULL,
20    ACC_BANK_NUM VARCHAR(16) NOT NULL,
21    ACC_JOB VARCHAR(50) NOT NULL,
22    ACC_SALARY DECIMAL(10,2) NOT NULL,
23    ACC_REG_DATE DATE NOT NULL,
24    ACC_EMERGENCY_PHONE_NUM VARCHAR(15) NOT NULL,
25    ACC_EMERGENCY_CONTACT_RELATIONSHIP VARCHAR(10) NOT NULL,
26    ACC_KTP_SCAN BLOB NOT NULL,
27    ACC_NPWP_SCAN BLOB NULL,
28    ACC_SELFIE BLOB NOT NULL,
29    ACC_BANK_NUM_SCAN BLOB NOT NULL,
30    ACC_RATING DECIMAL(2,1) NOT NULL,
31    PRIMARY KEY (ACC_KTP_NUM),
32    FOREIGN KEY (ACC_AT_ID) REFERENCES ACCOUNT_TYPE (AT_ID)
33);
34
35CREATE TABLE EMPLOYEE (
36    E_KTP_NUM VARCHAR(16) NOT NULL,
37    E_NAME VARCHAR(60) NOT NULL,
38    E_EMAIL VARCHAR(256) NOT NULL,
39    E_PHONE_NUM VARCHAR(15) NOT NULL,
40    E_ADDRESS VARCHAR(256) NOT NULL,
41    E_GENDER CHAR(1) NOT NULL,
42    PRIMARY KEY (E_KTP_NUM)
43);
44
45CREATE TABLE LOAN_TYPE (
46    LT_ID CHAR(5) NOT NULL,
47    LT_NAME VARCHAR(16) NOT NULL,
48    LT_INTEREST DECIMAL(5,4) NOT NULL,
49    LT_PERIOD INT NOT NULL,
50    LT_PENALTY DECIMAL(10,2) NOT NULL,
51    PRIMARY KEY (LT_ID)
52);
53
54CREATE TABLE TRANSACTION (
55    T_ID CHAR(5) NOT NULL,
56    T_ACC_KTP_NUM CHAR(16) NOT NULL,
57    T_E_KTP_NUM VARCHAR(16) NOT NULL,
58    T_LT_ID CHAR(5) NOT NULL,
59    T_DATE DATE NOT NULL,
60    T_DUE_DATE DATE NOT NULL,
61    T_STATUS VARCHAR(11) NOT NULL,
62    T_LOAN DECIMAL(10,2) NOT NULL,
63    T_MONTHLY_PAYMENT DECIMAL(10,2) NOT NULL,
64    T_PENALTY_COUNT INT NULL,
65    PRIMARY KEY (T_ID),
66    FOREIGN KEY (T_ACC_KTP_NUM) REFERENCES ACCOUNT (ACC_KTP_NUM),
67    FOREIGN KEY (T_E_KTP_NUM) REFERENCES EMPLOYEE (E_KTP_NUM),
68    FOREIGN KEY (T_LT_ID) REFERENCES LOAN_TYPE (LT_ID)
69);
70
71CREATE TABLE LOAN_PAYMENT (
72    LP_ID CHAR(6) NOT NULL,
73    LP_T_ID CHAR(5) NOT NULL,
74    LP_DATE DATE NOT NULL,
75    LP_PAYMENT DECIMAL(10,2) NOT NULL,
76    PRIMARY KEY (LP_ID),
77    FOREIGN KEY (LP_T_ID) REFERENCES TRANSACTION (T_ID)
78);
79