You have been hired to design a database that meets an outlined set of requirements in order to solve an information management problem. After reading through the provided scenario, you will first determine the overall purpose of the database and the user requirements.
You will then work through a conceptual design process, design revision, and design finalization. Your final submission should be in the form of a final entity-relationship diagram (ERD) in third normal form (3NF) and an explanatory report that summarizes your process and recommendations for the database solution.
Along with your final ERD and new work for the assignment, the document should incorporate the text and diagrams from your milestones, including revisions. Specifically, the following critical elements must be addressed:
I. Requirement Gathering: Read the provided scenario in order to determine the overall purpose of the database and the user requirements, specifically the following:
A. Define the purpose, goals, and objectives of the database as determined by the scenario.
B. Assess the user requirements associated with the database according to its owners and stakeholders.
C. Explain the business processes and restrictions that the database addresses in support of the user requirements.
D. Explain what the end result of the database should be as determined by the scenario.
II. Conceptual Design: Using your analysis of the provided scenario, address the following in order to determine an appropriate entity-relationship model (ERM) that will inform your final design
A. Assess various ERMs for their design applicability to your database design.
B. Determine the appropriate ERM and explain your choice.
C. Identify the data sets for the database, including all entities and attributes.
III. Design Revision: During this stage, you will draft your entity-relationship diagram (ERD), addressing the following elements:
A. Construct your ERD, utilizing your identified entities and attributes.
B. Determine appropriate primary and foreign keys for each entity and note them on your ERD.
C. Determine table relationships and note them on your ERD.
D. Determine appropriate data types and sizes for each attribute and note them on your ERD.
E. Complete the normalization process utilizing dependency diagrams in order to prove tables are in third normal form (3NF), and adjust your ERD if necessary.
IV. Design Finalization: Finalize your ERD after normalization, and conclude your report package, addressing the following elements:
A. Draft your recommended final ERD, which accounts for any adjustments made to its elements as determined through the normalization process.
B. Summarize your process for developing your database design, including an explanation for the determinations you made while filling in your ERD and making adjustments after normalization.
C. Explain how your design will allow the database to achieve your desired end result, including consideration of the user requirements, business processes, and restrictions.
Scenario
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 businesssavvy. 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.