Design A Database For High-Volume Late-Night Pizza Delivery
Design a database for a high-volume, late-night pizza delivery business
Cougar Pizza Pies is a new concept in pizza delivery. Dylan and Samantha Morgan have been serving high-class pizzas for over 20 years but have decided to take a new direction with their next pizza concept.
Instead of focusing on an ever-increasing variety of specialty pizzas, the Morgans have decided to turn the value concept toward the high-volume, late-night pizza-delivery business. Certainly, being close to Atlanta United University, home of the Cougars, and its 38,000 students provides a deep and growing market for their business. As such, the Morgans are creating Cougar Pizza Pies to sell and deliver pizzas only between midnight and 4:00 a.m. A view of the current competitive landscape shows that no other business is currently targeting that same market. The Morgans believe that the simplest business model will bring them the best success in their desired market.
As such, the new restaurant will not have any seating (as most orders will be delivered), but it will allow for pickup of pizzas if needed. It will also have a limited menu of pizza types (thin, regular, or pan) and a limited topping list (pepperoni, sausage, ham, and extra cheese) in order to facilitate the high sales volume expected. Also, to coordinate special instructions from customers (delivery access, cooking preferences, etc.), the Morgans would like to have an area where they can add customer notes to orders. Following their simplicity model, the Morgans have fixed their prices as follows:
- Thin and Regular Crust: $13.00
- Pan Pizza: $15.00
- Toppings: $2.00 each
Furthermore, for marketing outreach and research purposes, they would like to track orders by phone number, street address, city, state, and zip code.
The Morgans aim to determine and validate the profitability of their new business. Therefore, they need a database that supports tracking details such as:
- Pizza orders, including toppings added
- Customer who ordered pizza
- Address to which pizza was delivered
- Name and phone number of employee who delivered pizza
- Name of employee who took the pizza order
- Sales per hour, day, week, and month
- Delivery status details at various points in the night
Each order should include a status, order date and time, and delivery date and time. If a customer opts for pickup, the pickup time is considered the delivery time. Additional considerations include:
- A customer can order multiple pizzas
- Customers may change addresses often; each customer has a preferred address
- Most orders must be completed and delivered within an hour
- The database should handle multiple stores in different college towns, as the business expands
The Morgans seek to implement this database within the next few months to support their business operations effectively.
Paper For Above instruction
The development of a comprehensive database for Cougar Pizza Pies necessitates a systematic approach encompassing requirements gathering, conceptual design, normalization, and final implementation. This paper delineates each stage, emphasizing how the design aligns with business needs, facilitates operational efficiency, and supports future scalability.
Requirement Gathering
The core purpose of the database is to support Cougar Pizza Pies’ unique business model, which operates exclusively during late-night hours targeting college students near Atlanta United University. The primary objectives include tracking customer orders, managing addresses, monitoring sales, and analyzing delivery performance. User requirements are derived from stakeholder input, emphasizing real-time data entry, ease of access for employees, and detailed reporting capabilities. The system must handle multiple stores, accommodate address changes, and associate each customer with a preferred delivery location. Additionally, the database must capture special instructions and delivery statuses to ensure service quality.
Business processes addressed include order placement, preparation, delivery, and sales analysis. Restrictions involve timing constraints (orders between midnight and 4 AM), inventory limitations (limited menu), and delivery timings (most within an hour). The end goal is a reliable, scalable system that provides operational insights and supports marketing efforts.
Conceptual Design
During initial analysis, assessing various Entity-Relationship Models (ERMs) was essential to identify the most suitable structure. Among possible ERMs, a model centered on entities such as Customer, Address, Order, Pizza, Topping, Employee, Store, and Delivery Status proved most applicable. After comparison, the chosen ERM effectively captures the relationships between customers and their orders, addresses, and employees with clear foreign key associations.
Data sets identified include entities like Customer (with attributes such as CustomerID, Name, Phone, PreferredAddressID), Address (AddressID, Street, City, State, Zip), Order (OrderID, CustomerID, OrderDateTime, StatusID, EmployeeID for order taker, EmployeeID for delivery, special instructions), Pizza (PizzaID, Type, Price), Topping (ToppingID, Description, Price), and Delivery Status (StatusID, Description). These entities facilitate detailed tracking and analysis.
Design Revision
ERD Construction
The ERD was constructed to visually represent entity relationships. Customers are linked to Orders via CustomerID, with each Order associated with a specific Address. Orders are connected to Pizza entries, which include type and pricing. Each Pizza may include multiple Toppings, captured through a junction table ToppingOrderDetail, establishing a many-to-many relationship.
Primary keys, such as CustomerID, OrderID, PizzaID, ToppingID, and AddressID, were determined to uniquely identify each record. Foreign keys specify relationships, e.g., CustomerID in Orders references Customers, and AddressID in Orders refers to Addresses. Delivery Statuses are linked via StatusID, enabling real-time order tracking.
Data Types and Sizes
Given data sensitivity and performance considerations, appropriate data types were assigned. CustomerID, AddressID, OrderID, EmployeeID, PizzaID, ToppingID, and StatusID are integers. Names and descriptions utilize VARCHAR with specified lengths (e.g., VARCHAR(50)). Prices are DECIMAL(18,2). Timestamps use DATETIME. Address components are broken into specific fields (e.g., StreetLine1, City, State, Zip) to facilitate data normalization and querying.
Normalization
Normalization to Third Normal Form (3NF) was achieved through dependency diagrams, ensuring elimination of transitive dependencies. The Address entity was separated from Customer to accommodate address changes. The ToppingOrderDetail junction table handles many-to-many relationships between Pizzas and Toppings, avoiding redundancy. This normalization enhances data integrity and reduces update anomalies.
Finalization and Process Summary
The final ERD encapsulates all entities and relationships, reflecting adjustments from normalization. The process involved iterative refinement, balancing normalization with practical query requirements. Utilizing dependency diagrams clarified functional dependencies, ensuring compliance with 3NF standards.
The resulting database design empowers Cougar Pizza Pies to accurately track orders, handle customer preferences, monitor sales performance, and analyze delivery statuses. Its scalability supports future expansion to additional stores. Moreover, normalization ensures data consistency, facilitates efficient querying, and provides a solid foundation for business decision-making and growth.