db-hw3

Tables (13)

Coupon

  • coupon_id — INT — Primary key — NOT NULL
  • code — VARCHAR(20) — NOT NULL
  • discount_type — VARCHAR(10) — NOT NULL
  • discount_value — INT — NOT NULL
  • min_order_amount — INT
  • valid_from — DATE
  • valid_until — DATE

Coupon_Usage

  • usage_id — INT — Primary key — NOT NULL
  • coupon_id — INT — Foreign key → Coupon.coupon_id — NOT NULL
  • customer_id — INT — Foreign key → Customer.customer_id — NOT NULL
  • order_id — INT — Foreign key → Orders.order_id
  • issued_date — DATE — NOT NULL
  • used_date — DATE
  • discount_applied — INT

Customer

  • customer_id — INT — Primary key — NOT NULL
  • region_id — INT — Foreign key → Region.region_id
  • name — VARCHAR(30) — NOT NULL
  • phone — VARCHAR(20)
  • email — VARCHAR(50)
  • address — VARCHAR(100)
  • join_date — DATE — NOT NULL
  • membership_type — VARCHAR(20) — NOT NULL

Menu

  • menu_id — INT — Primary key — NOT NULL
  • restaurant_id — INT — Foreign key → Restaurant.restaurant_id — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • price — INT — NOT NULL
  • description — VARCHAR(200)
  • category — VARCHAR(20)
  • is_available — CHAR(1) — NOT NULL

Order_Item

  • order_id — INT — Foreign key → Orders.order_id — NOT NULL
  • menu_id — INT — Foreign key → Menu.menu_id — NOT NULL
  • quantity — INT — NOT NULL
  • unit_price — INT — NOT NULL
  • subtotal — INT — NOT NULL

Orders

  • order_id — INT — Primary key — NOT NULL
  • customer_id — INT — Foreign key → Customer.customer_id — NOT NULL
  • restaurant_id — INT — Foreign key → Restaurant.restaurant_id — NOT NULL
  • rider_id — INT — Foreign key → Rider.rider_id
  • order_date — DATE — NOT NULL
  • order_status — VARCHAR(20) — NOT NULL
  • total_amount — INT — NOT NULL

Owner

  • owner_id — INT — Primary key — NOT NULL
  • owner_name — VARCHAR(30) — NOT NULL
  • business_reg_no — VARCHAR(20) — NOT NULL
  • phone — VARCHAR(20)
  • bank_account — VARCHAR(30)
  • join_date — DATE

Payment

  • payment_id — INT — Primary key — NOT NULL
  • order_id — INT — Foreign key → Orders.order_id — NOT NULL
  • method — VARCHAR(20) — NOT NULL
  • amount — INT — NOT NULL
  • payment_date — DATE — NOT NULL
  • status — VARCHAR(20) — NOT NULL

Region

  • region_id — INT — Primary key — NOT NULL
  • sido — VARCHAR(20) — NOT NULL
  • sigungu — VARCHAR(20) — NOT NULL
  • dong — VARCHAR(30)
  • zip_code — VARCHAR(10)

Restaurant

  • restaurant_id — INT — Primary key — NOT NULL
  • owner_id — INT — Foreign key → Owner.owner_id — NOT NULL
  • region_id — INT — Foreign key → Region.region_id — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • category — VARCHAR(20)
  • phone — VARCHAR(20)
  • address — VARCHAR(100)
  • min_order_amount — INT
  • commission_rate — DECIMAL(4,2) — NOT NULL
  • registered_date — DATE

Review

  • review_id — INT — Primary key — NOT NULL
  • order_id — INT — Foreign key → Orders.order_id — NOT NULL
  • customer_id — INT — Foreign key → Customer.customer_id — NOT NULL
  • restaurant_id — INT — Foreign key → Restaurant.restaurant_id — NOT NULL
  • rating — INT — NOT NULL
  • comment — VARCHAR(300) — ARCHAR(300
  • review_date — DATE — NOT NULL

Rider

  • rider_id — INT — Primary key — NOT NULL
  • region_id — INT — Foreign key → Region.region_id
  • name — VARCHAR(30) — NOT NULL
  • phone — VARCHAR(20)
  • vehicle_type — VARCHAR(20)
  • status — VARCHAR(20) — NOT NULL

Settlement

  • settlement_id — INT — Primary key — NOT NULL
  • order_id — INT — Foreign key → Orders.order_id — NOT NULL
  • restaurant_id — INT — Foreign key → Restaurant.restaurant_id — NOT NULL
  • order_amount — INT — NOT NULL
  • commission_rate — DECIMAL(4,2) — NOT NULL
  • commission_fee — INT — NOT NULL
  • settlement_amount — INT — NOT NULL
  • settled_date — DATE
  • status — VARCHAR(20) — NOT NULL