A new sandwich franchise called UWE Sarnies has chosen you to design a database system for them to manage their sandwich orders.

The company owns several stores, each has a unique store ID, a street address, and a postcode. Each store is managed by a store manager who is identified by a unique manager ID and a first and a last name. The date the manager took over the store is also stored. While each store is managed by a single manager, it is permitted for different stores to be managed by the same manager.

Each order (which is for one sandwich only, i.e., if a customer wishes to order n different sandwiches, he/she needs to make n different orders) has a unique (across all stores) order number, an order date & time indicating when the order was made, a completion date & time indicating when the order has been completed, and a cost in British pounds. Note that any order belongs to a single store, but any store can issue many orders.

Each menu item (sandwich) has a unique name, e.g., Tuna Heaven, and a unique menu item ID number. Each sandwich on the menu has a pre-fixed set of fillings, however, the number of fillings in different menu items vary. When making an order, the customer states the sandwich name (or the corresponding menu item ID) they would like to order, and since each sandwich has a pre-fixed set of fillings, the customer just needs to choose the type of bread they would like for their order. Each bread type has a unique name, e.g., Brown, White, Herbs & Cheese, and a price (pence per loaf). They also wish to store the total number of calories of each bread type. Note that all bread types have the same size so no need to store information about the loaf size.

Each filling has a unique ID, a unique name, e.g., Tomato, Tuna, Egg, etc., a price

(pence per gram) and calories (per gram). Each filling belongs to a category e.g., Vegetable, Poultry, etc. where each category is identified by a unique ID and has a distinct name.

Each sandwich is a combination of a set of fillings (each with a quantity in grams) and a type of bread. For instance, the menu item Tuna Heaven, consists of 200g of tuna, 150g of tomato, 100g of cucumber, 100g of lettuce, and 150g of gherkin, whereas the Big Egg menu item consists of 150g of egg, 120g of tomato and 100g of watercress. The cost of an order is computed as the sum of the product of the cost (per gram) of each filling used in the sandwich belonging to that order by the quantity of that ingredient used plus the cost for the chosen bread.

Among other things, your design should permit the following queries:

Listing orders by store(s), e.g., completed/pending orders by store X

Customers should be able to search for menu items and find out what fillings they contain and the prices for the different variants

Customers should be able to search for sandwiches by the category of their fillings,

e.g., sandwiches which contain vegetable and/or fish fillings but not red meat fillings etc.

Customers should be able to search for menu items by their calories, e.g., sandwiches which has 400 calories.

You are required to finish all 3 following tasks:

1) Design a conceptual schema (using UML notation diagram) from the above description

2) Give the logical relational schema for the database including all constraints for all involved relations. Apply the necessary normalization (if required) to eliminate any redundancy from the relations

3) Give the Oracle SQL DDL statements to create the tables corresponding to the relations in your logical schema including all the necessary constraints

3 Marks will be given if the design reads as one cohesive design, i.e.,the logical schema is a valid translation of the corresponding conceptual schema and the physical schema is a valid translation of the relational schema