ERD Assignment: Let’s Consider The Following Scenario: Papa
ERD Assignment Let’s consider the following scenario: Papa Georgeo (AKA PG) is opening a Pizzeria and he needs a database to capture and maintain the data for the entities Sales, Customers, Products, Purchase, Suppliers, and Employees
Papa Georgeo (PG) is opening a pizzeria and requires a comprehensive database system to manage various aspects of the business. This database must encompass data related to sales transactions, customer information, products offered, purchases from suppliers, and employee details. The core focus is to create an efficient, normalized database schema suitable for an online transaction processing (OLTP) environment.
In terms of sales, PG wants to track every order with detailed information. An order may include multiple items—for example, a customer could order three large pizzas, breadsticks, and beverages under a single order ID. The sales data should include order date, customer ID, order time, subtotal, tax amount, total due, individual order items, quantity, price charged, and discount amount per item. Recognizing customer loyalty, PG plans to introduce a club card system (PG Card), which awards points (PG points) for each purchase. Customers accumulate points to redeem free items, and the database should be capable of tracking points, purchase histories, and enabling targeted promotions based on purchasing behavior.
The business also accepts various payment methods, including cash, checks, and major credit cards. Employee management is another critical aspect; PG plans to hire shift managers, pizza bakers, and delivery drivers, necessitating data on their roles, wages, work shifts, physical addresses, and other relevant employment data.
Additionally, the menu includes multiple product categories: pizzas (with different types and sizes), pasta, garlic bread, chicken wings, beverages, and desserts. Each pizza can have various sizes (small, medium, large), and other items come in standardized serving sizes. The schema must record detailed information about each product, inventory levels, and customer reviews posted on the website. Suppliers are an essential part of the supply chain, providing ingredients and supplies such as cleaning products, with the database capturing supplier details and the specific products purchased from them.
The system should support complex queries to analyze sales and customer data, such as identifying top customers, their accumulated points, best-selling products, preferences based on product size, purchase histories, supplier details, product categorizations, and customer reviews. All data models must be normalized to the third normal form (3NF) to ensure data integrity and efficiency.
Paper For Above instruction
The design of a comprehensive database schema for a pizzeria business requires careful consideration of the various entities involved and their interrelationships to ensure data integrity, operational efficiency, and support for complex analytical queries. The primary goal is to Capture all necessary business data within a normalized structure, adhering to third normal form principles, and ensuring the schema supports the specified queries.
Entities and Their Attributes
Key entities include Customers, Employees, Products, Suppliers, Purchases, and Sales. Each entity's attributes must be selected to encapsulate all necessary information while avoiding redundancy. For example, the Customer entity includes customer ID, name, telephone, address, and email. The Employee entity records employee ID, name, role (shift manager, baker, driver), wage, shift timing, and address.
Products are categorized into Pizzas, Pasta, Wings, Beverages, and Desserts, each with their specific attributes. Pizzas, for instance, include pizza ID, name, type, size, and price. Product reviews link to customer and product IDs, capturing review text and ratings. Supplies and Purchases involve data about supply ID, supplier ID, product ID, quantity, purchase date, and cost.
Relationships and Normalization
The schema design must accurately depict relationships such as:
- Customers Sales (one-to-many): A customer can place multiple orders.
- Sales Sale Items (one-to-many): Each sale involves multiple sale items.
- Sale Items Products (many-to-one): Each sale item corresponds to a specific product.
- Products Categories (many-to-one): Products belong to categories like Pizza or Beverages.
- Suppliers Supplies (one-to-many): Suppliers provide multiple supplies.
- Purchases Suppliers (many-to-one): Purchases are made from specific suppliers.
- Employees Shifts (one-to-many): Employees work in shifts.
- Customers Reviews (one-to-many): Customers can post multiple reviews.
Implementing these relationships requires foreign keys and ensuring they preserve referential integrity. Each table's primary key should be designated explicitly, and foreign keys should map appropriate relationships.
Supporting Complex Queries
The schema, once designed in conformity with normalization standards, supports queries such as:
- Identifying top 10 customers by sales amount: requires joining Customers, Sales, and Sale Items, aggregating total sales contingent on customer ID.
- Tracking airline points: summing points earned per customer based on purchase history.
- Top-selling products and sizes: aggregating sales per product and size categories.
- Order-specific quantities of product categories over time: joining Sales, Sale Items, and Products for detailed trends.
- Supplier details and purchased products: joining Suppliers and Purchases to display supplier-product relationships.
- Customer reviews: linking Customers and Review entities to display reviews with customer contact information.
Conclusion
Designing a robust, normalized ERD for the pizzeria business ensures data consistency, supports complex analytical queries, and scales as the business grows. Each entity encapsulates relevant attributes, and relationships are designed to reflect real-world interactions between customers, products, sales, employees, and suppliers. Confirming the schema's compliance with 3NF guarantees elimination of redundant data and dependency anomalies, leading to a reliable, efficient database system aligned with business needs.