Please Suggest the Entities and Attributes and their data types for creating a database for the scenario below:
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 would like to determine and validate that their new business is profitable and business-savvy. Thus, they have hired you to design a database that supports the validation of the late-night pizza-delivery business. Along with any considerations mentioned above, they know they need to be able to track the following details in a database:
Pizza orders, including toppings added.
Customer who ordered pizza.
Address pizza was delivered to.
Name and phone number of employee who delivered pizza.
Name of employee who took pizza order.
Sales per hour, day, week, and month.
Delivery status details at various points in the night. Each order should have a status along with the order date and time and the delivery date and time. Note that if a customer picks up the pizza, the pickup time is the delivery time.
Some other considerations for the ordering process include the following:
● A customer can certainly order more than one pizza.
● Customers move often and may order from different locations (a friend’s house, for instance), so address details may also change often. However, each customer would have one preferred address.
● Most orders need to be completed and delivered within an hour.
● The Morgans envision opening other stores in other college towns if this venture is successful, so they would like this database to handle multiple stores.
The Morgans are hoping to implement the database very soon, most certainly within the next few months.