Static_ERD

Tables (16)

Cart

  • Cart_ID — INT — Primary key
  • Customer_ID — INT — Foreign key → Customer.Customer_ID
  • Product_ID — INT — Foreign key → Product.Product_ID
  • Added_Date — DATE
  • Quantity — INT
  • Removed_Flag — BOOLEAN
  • Is_Ordered (Is_ordered) — BOOLEAN

Cluster

  • Cluster_ID — INT — Primary key — NOT NULL
  • Description — VARCHAR(100)
  • Centroid_Latitude — DECIMAL(10,6)
  • Centroid_Longitude — DECIMAL(10,6)
  • Priority_information (Field) — VARCHAR(100)

Collect_Data (Recommendation)

  • CD_ID (Rec_ID) — INT — Primary key
  • Customer_ID — INT — Foreign key → Customer.Customer_ID
  • Order_Detail_ID — INT — Foreign key → Order_Detail.Order_Detail_ID — NOT NULL
  • Recommended_Date — DATE
  • Time_Context (Field) — VARCHAR(100)
  • Situation_Context (Field2) — VARCHAR(100)
  • Preferred_Delivery_Time (Field3) — DATETIME

Customer

  • Customer_ID — INT — Primary key
  • Name — VARCHAR(100)
  • Phone — VARCHAR(20)
  • Address — TEXT
  • Preferred_Category — VARCHAR(50)
  • Age — INT
  • Gender — INT

Delivery

  • Delivery_ID — INT — Primary key
  • Warehouse_ID — INT — Foreign key → Warehouse.Warehouse_ID
  • Order_Detail_ID — INT — Foreign key → Order_Detail.Order_Detail_ID — NOT NULL
  • Cluster_ID (Cluster_ID2) — INT — Foreign key → Cluster.Cluster_ID — NOT NULL
  • Delivery_Manager_ID — VARCHAR(100)
  • Start_Time — DATETIME
  • End_Time — DATETIME
  • Delivery_Success_Rate (Was_Delayed) — INT

Homeplus

  • Homeplus_ID — INT — Primary key
  • Name — VARCHAR(100)
  • Latitude — DECIMAL(10,6)
  • Longitude — DECIMAL(10,6)

Marketing_Log (Untitled3)

  • Marketing_Log_ID (Key) — INT — Primary key — NOT NULL
  • Rec_Result_ID (Key2) — INT — Foreign key → Recommendation_Result.Rec_Result_ID — NOT NULL
  • Marketing_Type (Field) — VARCHAR(50)
  • Marketing_Sent (Field2) — BOOLEAN
  • Marketing_Time (Field3) — DATETIME
  • Coupon_Code (Field4) — VARCHAR(50)
  • Coupon_Discount (Field5) — FLOAT
  • Followup_Clicked (Field6) — BOOLEAN
  • Followup_Ordered (Field7) — BOOLEAN

Order

  • Order_ID — INT — Primary key
  • Customer_ID — INT — Foreign key → Customer.Customer_ID
  • Order_Date — DATE
  • Customer_Wait_Time — INT

Order_Detail

  • Order_Detail_ID — INT — Primary key — NOT NULL
  • Order_ID — INT — Foreign key → Order.Order_ID
  • Product_ID — INT — Foreign key → Product.Product_ID
  • Quantity — INT

Product

  • Product_ID — INT — Primary key
  • Name — VARCHAR(100)
  • Category — VARCHAR(50)
  • Price — DECIMAL(10,2)
  • Storage_Type — VARCHAR(10)
  • Expiration_Date — DATE

Recommendation_AI (Untitled)

  • Rec_AI_ID (Key) — INT — Primary key — NOT NULL
  • Customer_ID — INT — Foreign key → Customer.Customer_ID
  • CD_ID (Rec_ID) — INT — Foreign key → Collect_Data.CD_ID
  • Collaborative_Filtering (Field) — TEXT
  • Time_aware_Recommendation (Field2) — TEXT
  • Hybrid_Model (Field3) — TEXT

Recommendation_Result (Untitled2)

  • Rec_Result_ID (Key) — INT — Primary key — NOT NULL
  • Rec_AI_ID (Key2) — INT — Foreign key → Recommendation_AI.Rec_AI_ID — NOT NULL
  • Product_ID — INT — Foreign key → Product.Product_ID
  • Was_Clicked (Field) — BOOLEAN
  • Recommendation_Click_Through_Rate (Field4) — INT
  • Was_Ordered (Field2) — BOOLEAN
  • Was_Carted (Field3) — BOOLEAN

Review

  • Review_ID — INT — Primary key
  • Customer_ID — INT — Foreign key → Customer.Customer_ID
  • Order_Detail_ID — INT — Foreign key → Order_Detail.Order_Detail_ID — NOT NULL
  • Rating — INT
  • Comment — TEXT — EX
  • Review_Date — DATE

Warehouse

  • Warehouse_ID — INT — Primary key
  • Latitude — DECIMAL(10,6)
  • Longitude — DECIMAL(10,6)
  • Operating_Hours — INT

Warehouse_Inventory

  • Warehouse_Inventory_ID — INT — Primary key
  • Order_Detail_ID — INT — Foreign key → Order_Detail.Order_Detail_ID — NOT NULL
  • Warehouse_ID — INT — Foreign key → Warehouse.Warehouse_ID
  • Delivery_ID — INT — Foreign key → Delivery.Delivery_ID
  • Homeplus_ID — INT — Foreign key → Homeplus.Homeplus_ID
  • Arrival_Date — DATE
  • Packing_type — VARCHAR(20)
  • Is_Packed (Is_Completed) — BOOLEAN
  • Packing_start_time (Field) — TIME
  • Packing_end_time (Field2) — TIME

homeplus_Inventory

  • homeplus_Inventory_ID — INT — Primary key
  • Order_Detail_ID — INT — Foreign key → Order_Detail.Order_Detail_ID — NOT NULL
  • Homeplus_ID — INT — Foreign key → Homeplus.Homeplus_ID
  • Arrival_Date — DATE
  • Is_Picked — BOOLEAN
  • Picking_Start_Time (Field) — TIME
  • Picking_End_Time (Field3) — TIME
  • Is_Sorted — BOOLEAN
  • Sorting_Start_Time (Field2) — TIME
  • Sorting_End_Time (Field4) — TIME