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