This project designs a database for a retail store called "MR.Krid Mart" that needs to manage sales transactions and product stock.
The design started by identifying six main entities: EMPLOYEE, CUSTOMER, PURCHASE, PRODUCT, PRODUCT_CATEGORY, and ORDER_LIST (a junction table that links PURCHASE and PRODUCT, since one purchase can include many products).
Two versions of the ER diagram were created:
Finally, the diagram was translated into SQL Server DDL code, including primary keys, foreign keys, and check constraints (for example, preventing negative prices or stock, and stopping duplicate items in the same order), plus indexes on foreign key columns to support fast queries.
MR.Krid Mart is a retail store that wants to replace its paper receipts and spreadsheets with a proper database.
The store has employees (name, gender, province, phone) who work as cashiers, and customers (name, gender, province, phone) who shop there regularly.
Every time a customer checks out, it creates a purchase — recorded with a date, which employee rang it up, and which customer made it. In one purchase, a customer can buy multiple products at once, each in a different quantity (e.g., 2 shampoos + 1 bag of rice in the same transaction), so each item bought needs to be tracked individually along with how many units.
The store sells many products, each with a name, price, and stock quantity. Every product belongs to a product category (e.g., Beverages, Snacks, Household Supplies), which has its own name and description, so the store can report sales by category and manage restocking.
Design a database that lets the store: