은행
테이블 (6)
Bank
- bank_id — INT
- name — VARCHAR(100) — NOT NULL
- address — VARCHAR(255)
- contact_number — VARCHAR(15)
- daily_loan_limit — VARCHAR(20)
Customer
- customer_id — INT
- name — VARCHAR(50) — NOT NULL
- date_of_birth — DATE
- address — VARCHAR(255)
- phone — VARCHAR(15)
- email — VARCHAR(100)
Loan
- loan_id — INT
- customer_id — INT
- bank_id — INT
- loan_type_id — INT
- principal_amount — VARCHAR(20) — NOT NULL
- interest_rate — DECIMAL(5, 2) — NOT NULL
- start_date — DATE — NOT NULL
- end_date — DATE
- remaining_balance — VARCHAR(20) — NOT NULL
- status — ENUM('Active', 'Closed', 'Defaulted')
LoanType
- loan_type_id — INT
- name — ENUM('Mortgage', 'Auto', 'Personal') — NOT NULL
- max_amount — VARCHAR(20) — NOT NULL
PaymentHistory
- payment_id — INT
- loan_id — INT
- payment_date — DATE — NOT NULL
- payment_amount — VARCHAR(20) — NOT NULL
- principal_paid — VARCHAR(20) — NOT NULL
- interest_paid — VARCHAR(20) — NOT NULL
- remaining_balance — VARCHAR(20) — NOT NULL
PaymentSchedule
- schedule_id — INT
- loan_id — INT
- payment_due_date — DATE — NOT NULL
- payment_amount — VARCHAR(20) — NOT NULL
- principal_amount — VARCHAR(20) — NOT NULL
- interest_amount — VARCHAR(20) — NOT NULL
- payment_status — ENUM('Pending', 'Completed', 'Missed')
- CHECK — (DATEDIFF(payment_due_date, (SELECT start_date FROM Loan WHERE loan_id = PaymentSchedule.loan_id))