test

테이블 (12)

base_station

  • 기지국ID (station_id) — INT — 기본 키 — NOT NULL
  • name — VARCHAR(50)
  • region — VARCHAR(50)
  • latitude — DECIMAL(9,6)
  • longitude — DECIMAL(9,6)

bill

  • 청구_id (bill_id) — INT — 기본 키 — NOT NULL
  • issue_date — DATE — NOT NULL
  • due_date — DATE — NOT NULL
  • total_amount — INT — NOT NULL
  • status — ENUM('UNPAID','PAID','OVERDUE')
  • 회선_id (line_id) — INT — 외래 키 → line.회선_id — NOT NULL

data_session

  • 세션ID (session_id) — INT — 기본 키 — NOT NULL
  • start_time — DATETIME — NOT NULL
  • end_time — DATETIME — NOT NULL
  • data_mb — INT — NOT NULL
  • network_type — ENUM('5G','4G','3G') — NOT NULL
  • 평균 전송속도 (avg_speed_mbps) — INT
  • 회선_id (line_id) — INT — 외래 키 → line.회선_id — NOT NULL
  • 기지국ID (station_id) — INT — 외래 키 → base_station.기지국ID — NOT NULL

line

  • 회선_id (line_id) — INT — 기본 키 — NOT NULL
  • phone_number — VARCHAR(20) — NOT NULL
  • usim_number — VARCHAR(30)
  • activated_at — DATETIME — NOT NULL
  • suspended_at — DATETIME
  • terminated_at — DATETIME
  • status — ENUM('ACTIVE','SUSPENDED','TERMINATED') — NOT NULL
  • 회원ID (member_id) — INT — 외래 키 → member.회원ID — NOT NULL
  • 요금제id (plan_id) — INT — 외래 키 → plan.요금제id — NOT NULL

member

  • 회원ID (member_id) — INT — 기본 키 — NOT NULL
  • login_id — VARCHAR(50) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • birth_date — DATE — NOT NULL
  • phone — VARCHAR(20) — NOT NULL
  • email — VARCHAR(100) — NOT NULL
  • grade — ENUM('BASIC','VIP','VVIP')
  • status — ENUM('ACTIVE','SUSPENDED','WITHDRAWN','DORMANT')
  • join_date — DATETIME — NOT NULL

plan

  • 요금제id (plan_id) — INT — 기본 키 — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • monthly_fee — INT — NOT NULL
  • data_limit (data_limit_mb) — INT
  • network_type — ENUM('5G','4G','3G') — NOT NULL

usage_summary

  • 사용량_id (usage_id) — INT — 기본 키 — NOT NULL
  • data_used_mb — INT
  • 날짜 (date) — DATE
  • 회선_id (line_id) — INT — 외래 키 → line.회선_id — NOT NULL

결제수단 (payment_method)

  • 결제수단_id (method_id) — INT — 기본 키 — NOT NULL
  • method_type — ENUM('CARD','ACCOUNT','TRANSFER') — NOT NULL
  • is_auto_pay — TINYINT(1)
  • valid_from — DATE
  • valid_to — DATE
  • 회원ID (member_id) — INT — 외래 키 → member.회원ID — NOT NULL
  • 등록일 (created_at) — DATE

납부내역 (payment)

  • 결제내역_id (payment_id) — INT — 기본 키 — NOT NULL
  • pay_amount — INT — NOT NULL
  • pay_date — DATETIME — NOT NULL
  • 결제수단_id (method_id) — INT — 외래 키 → 결제수단.결제수단_id — NOT NULL
  • 청구_id (bill_id) — INT — 외래 키 → bill.청구_id — NOT NULL

월별 회원수 (monthly_member)

  • 월별 회원수 id (monthly_member_id) — INT — 기본 키 — NOT NULL
  • 날짜 (date) — DATE
  • 회원수 (number_of_member) — INT
  • 회원등급 (grade) — ENUM('BASIC','VIP','VVIP')
  • Field

할인정책 (discount_policy)

  • 할인정책_id (discount_policy) — INT — 기본 키 — NOT NULL
  • 정책이름 (name) — VARCHAR(30)
  • 할인율 (discount_percent) — DOUBLE
  • 유효기간 (valid_from) — DATETIME

회원별 적용할인 (member_discount)

  • id (member_discount_id) — INT — 기본 키 — NOT NULL
  • 회원ID (member_id) — INT — 외래 키 → member.회원ID — NOT NULL
  • 할인정책_id (discount_policy) — INT — 외래 키 → 할인정책.할인정책_id — NOT NULL