Prompt: In Milestone One, you read the final project scenario and then gathered the business requirements based on the goals, objectives, and user and business needs.

For this milestone, you will frame the entity relationship model (ERM) and define the entities and attributes for the business. Your next steps will be to normalize your entities, define primary and foreign keys, and finally, draft an initial entity-relationship diagram (ERD).

*Specifically, the following critical elements must be addressed:*

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. Hint: In this section, define the process you have used, and note several considerations you have made in your choice of specific entities and attributes. Be sure to list the various entities and attributes that are of interest based on the scenario. Some entities and attributes may not ultimately be included in your initial or final design and ERD.

B. Determine the appropriate ERM and explain your choice.

Hint: Describe the appropriateness, reason, or basis for why certain entities and attributes would be included in your design and others would be left out.

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.

Hint: For each entity and table, complete dependency diagrams proving the table is in 3NF. If a table is in 3NF, only one diagram is needed; if it is not in 3NF, progress through the normalization process, breaking up tables as necessary. Additionally, revise your ERD based on the results of your normalization process.

Final Project 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 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 friends 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.