Computer sciences and Information technology
Data base
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.