SQL_DAY1

Tables (4)

customer

  • cust_id — BIGSERIAL — Primary key — NOT NULL
  • name — VARCHAR(40) — NOT NULL
  • gender — CHAR(1)
  • birth_ymd — DATE
  • region — VARCHAR(20)
  • grade — VARCHAR(10)
  • join_dt — DATE — NOT NULL

plan

  • plan_id — SERIAL — Primary key — NOT NULL
  • plan_name — VARCHAR(30) — NOT NULL
  • monthly_fee — INTEGER — NOT NULL
  • data_gb — INTEGER — NOT NULL
  • voice_min — INTEGER — NOT NULL

subscription

  • sub_id — BIGSERIAL — Primary key — NOT NULL
  • cust_id — BIGINT — Foreign key → customer.cust_id — NOT NULL
  • plan_id — INTEGER — Foreign key → plan.plan_id — NOT NULL
  • start_dt — DATE — NOT NULL
  • end_dt — DATE
  • status — VARCHAR(10)

usage_log

  • usage_id — BIGSERIAL — Primary key — NOT NULL
  • sub_id — BIGINT — Foreign key → subscription.sub_id — NOT NULL
  • use_dt — DATE — NOT NULL
  • call_sec — INTEGER
  • data_mb — INTEGER
  • sms_cnt — INTEGER