은행

테이블 (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))