Problem 2 Suppose you're designing a database for a restaurant. The restaurant manager says they want to keep track of customers who are currently seated, dishes (e.g. "hamburger", "fried chicken", etc). Customers are identified by their table number and their seat number (at the table), e.g. Table 5, Seat 2. Each dish has a unique name, a description, a category (e.g. appetizer, main dish, dessert) and a price. The database will keep track of which dishes were ordered by which customer, along with the status (e.g. "in preparation", "ready","served, finished", etc). 1. Draw an ER diagram modeling this information. It should have an entity set representing customers, an entity set representing dishes, and one relationship set. 2. While reviewing this ER diagram with you, the restaurant manager realizes that some of the dishes have different sizes with different prices (e.g., "small tomato soup for $3.00 and large tomato soup for $5.00 ). Draw a new ER diagram to deal with this. Hint: use a weak entity set. Think about which entity sets participate in the relationship set representing orders. 3. You did such a good job on the database for "eat-in" orders, that the restaurant has hired you to design a database for their online orders. In this case, customers do not have table numbers and seat numbers, but each customer has a unique phone number and an address, composed of a building number, street name, and apartment number. In this scenario, a customer may order multiple servings of the same dish (e.g. three hamburgers). The database will keep track of how many of which dish was ordered by which customer, along with its status (e.g. "in preparation", "ready", "out for delivery", etc). The database only keeps track of current orders and you may assume that a customer can't change an order once it's in the system or place simultaneous orders for the same food. Draw an ER diagram for the online orders. (You may include different sizes of dishes or not, whichever youprefer.) 4. Now the manager decides that the restaurant would also like to keep track of the date and time on which each order was made and keep historical data, so that they'll know which customers have ordered which dishes in the past. Note that adding date and time attributes to the ordered relationship set is not sufficient, as this still will not allow the a customer to order the same dish at different date/times. (Why not?). Instead, you can take one of the following approaches: - use a ternary relationship set, involving an additional entity set representing dates/time; - or, change the ordered relationship set into an entity set representing orders, with relationship sets indicating who the order is by and what item is ordered). You may represent the orders with a strong entity set, adding an OrderNumber or with a weak entity set that has two identifying strong entity sets. Draw an ER diagram for the online orders with historical data
