Write a report which documents the logical design of a database to support the Information System requirements described in the Appendix A. Your logical design should be carried out using the top-down and bottom-up techniques that you have been taught in the module.
1) Identification of the entities.
For each entity found in the attached case study, provide a suitable name and a description. Your description should indicate why it is necessary in the database.
2) Definition of tables.
For each table, list the table name, the entity that the table implements (if appropriate), the primary key of the table (name and data type) and other identified attributes (names, data types and all other constraints).
3) Proof of normalization.
Demonstrate that you have normalized the data for the tables to 3rd normal form (removing repeating groups, resolving functional dependencies).
4) Produce an Entity-Relationship Diagram to complete the Logical Design Produce an Entity-Relationship diagram for the database system, showing all entities (with attributes and primary keys), relationships (with relationship name and cardinality),.
5) Evaluation of your design
Write a short (250 word) evaluation of your database design, discussing the features of the requirements that you have included and any problematic issues that were difficult to resolve.
CASE STUDY:
Pronto Pizzas (PP) is a medium scale restaurant chain. The restaurants are open daily from 6pm till 12 midnight. They have two main types of pizzas, Traditional Italiano and Thick Crust. A pizza can have different types of toppings (Mushrooms, Olives, Extra cheese, Pepperoni, Chicken, Pineapple, etc.) and each pizza will have at most 3 different toppings. There are 3 different sizes of pizzas offered (Personal, Regular, and Large). Apart from pizzas PP offers its customers starters, pasta dishes, desserts and beverages. The new Information System needs to track daily and monthly sales as well as which items are more popular with the customers.
The restaurant has outlets in all of Southern England. Each outlet will have 1 manager, 1 chef, 1 assistant, 2 order takers and 2 delivery persons. There are currently 12 outlets in total, 4 in East Sussex, and the rest in other counties.
A customer may eat-in, take away or have the pizza delivered to their door. Customers can register with PP. These customers are classified as Loyal. Loyal Customers personal details are stored in system, so PPs special promotions and offers are notified to its customers. Also loyal customers are delivered a pizza of their choice for their birthdays as a perk, if requested.
When a customer eats-in or takes away, the order is taken by an order taker and that order taker is responsible for completing the order until the final payment is made and receipt is handed over to the customer. If it is a delivery, an order taker will take the order and once order is ready a delivery person will deliver the order to the customer along with the receipt.
You need to study the above business case, and produce a database design to support the new Information System. You should state any reasonable additional assumptions within the context of the problem. Your design including ER model, tables and SQL queries should be guided by the following queries/scenarios.