Database Schema from EER Diagram
Category (category_id PK, name, description, parent_category FK)
Product (product_id PK, name, price, status, description, condition, category_id FK, date_added)
Customer (customer_id PK, F_name, L_name, name, email, phone, password)
Employee (Employee_id PK, F_name, L_name, name, email, phone, role, salary, hire_date, store_id FK)
Stores (store_id PK, name, phone, location_id FK)
Location (location_id PK, country, city, street)
Cart (cart_id PK, status, created_date, customer_id FK)
Buyer (customer_id PK FK, total_purchases, loyalty_points)
Donor (customer_id PK FK, total_donations)
Order (order_id PK, total_amount, status, created_date, location_id FK, cart_id FK)
Transaction (tr_id PK, payment_method, total, status, date, order_id FK)
Relationships:
- Category.parent_category references Category.category_id (self-referencing)
- Product.category_id references Category.category_id
- Employee.store_id references Stores.store_id
- Stores.location_id references Location.location_id
- Cart.customer_id references Customer.customer_id
- Buyer.customer_id references Customer.customer_id
- Donor.customer_id references Customer.customer_id
- Order.location_id references Location.location_id
- Order.cart_id references Cart.cart_id
- Transaction.order_id references Order.order_id
