This robust database system powers a full-featured e-commerce platform. It handles everything from user accounts and product listings to order processing, payments, reviews, and inventory. The system is built with ten interconnected tables and uses stored procedures and views for efficient data management and reporting.
- Purpose: Stores user account details.
- Primary Key:
UserID - Related Tables: Orders, Reviews, Addresses
- Purpose: Manages product categories.
- Primary Key:
CategoryID - Related Tables: Products
- Purpose: Stores supplier information.
- Primary Key:
SupplierID - Related Tables: Products
- Purpose: Contains detailed product information.
- Primary Key:
ProductID - Related Tables: OrderDetails, Reviews, Inventory
- Foreign Keys:
CategoryID,SupplierID
- Purpose: Records order details.
- Primary Key:
OrderID - Related Tables: OrderDetails, Payments
- Foreign Key:
UserID
- Purpose: Stores specifics of each product within an order.
- Primary Key:
OrderDetailID - Related Tables: Orders, Products
- Foreign Keys:
OrderID,ProductID
- Purpose: Manages payment transactions.
- Primary Key:
PaymentID - Related Tables: Orders
- Foreign Key:
OrderID
- Purpose: Stores customer reviews on products.
- Primary Key:
ReviewID - Related Tables: Products, Users
- Foreign Keys:
ProductID,UserID
- Purpose: Records user addresses.
- Primary Key:
AddressID - Related Tables: Users
- Foreign Key:
UserID
- Purpose: Tracks product inventory levels.
- Primary Key:
InventoryID - Related Tables: Products
- Foreign Key:
ProductID
- Purpose: Retrieves orders for a specific user.
- Parameters:
@userId INT
- Purpose: Fetches the inventory quantity for a specific product.
- Parameters:
@productId INT
- Purpose: Adds a new review for a product.
- Parameters:
@productId INT,@userId INT,@rating INT,@comment TEXT
- Purpose: Updates the status of an order.
- Parameters:
@orderId INT,@status VARCHAR(50)
- Purpose: Adds a new product to the catalog.
- Parameters:
@Name VARCHAR(100),@Description TEXT,@Price DECIMAL(10, 2),@CategoryID INT,@SupplierID INT
- Purpose: Updates details of an existing product.
- Parameters:
@ProductID INT,@Name VARCHAR(100),@Description TEXT,@Price DECIMAL(10, 2),@CategoryID INT,@SupplierID INT
- Purpose: Removes a product from the catalog.
- Parameters:
@ProductID INT
- Purpose: Creates a new order.
- Parameters:
@UserID INT,@TotalAmount DECIMAL(10, 2)
- Purpose: Adds a detail to an existing order.
- Parameters:
@OrderID INT,@ProductID INT,@Quantity INT,@Price DECIMAL(10, 2)
- Purpose: Provides a summary of user orders, displaying username, order ID, order date, and total amount.
- Base Tables: Users, Orders
- Purpose: Lists product reviews, showing product name, rating, and comments.
- Base Tables: Products, Reviews
- Purpose: Lists products supplied by each supplier, showing supplier name, product ID, product name, and price.
- Base Tables: Suppliers, Products
- Purpose: Provides a detailed summary of order details including order ID, order date, username, product name, quantity, and price.
- Base Tables: Orders, OrderDetails, Products, Users
- User Management: Facilitates user registration, credential storage, and contact details.
- Product Management: Organizes products into categories, manages suppliers, tracks product details, and monitors inventory.
- Order Processing: Records orders, manages order details, tracks payment transactions, and updates order statuses.
- Customer Feedback: Captures and stores product reviews provided by customers.
- Address Management: Maintains multiple addresses per user for accurate order deliveries.
- Reporting: Utilizes views to generate reports summarizing orders, product reviews, supplier products, and detailed order information.
This database system ensures robust data integrity through primary and foreign key relationships, supporting efficient data retrieval and manipulation via stored procedures and views. It provides a structured approach to managing data, ensuring reliability and facilitating seamless operations across various database tasks.