Question $1(30$ marks$)$

A food establishment has many outlets in various parts of Singapore, offering both set meals

as well as ala carte items. A set meal consists of two or more food items that are bundled $($e$.$g$.,$

soup, main and dessert$)$ and is attractively priced compared to when food items are purchased

individually. In addition, set meals and ala carte food items enjoy discounts of varying amount

periodically. To expand its customer base, the food establishment also makes use of several

delivery platforms to allow customers to make online orders.

You are to construct a conceptual $($ER$)$ model to support the daily operations of the food

establishment and to subsequently perform data analysis on data from its daily operations.

Daily operations include:

Introducing new set meals and ala carte food items, indicating name, price, description,

and availability, and identified with an item code.

Marking set meals and ala carte food items as available not available when they are no

longer in the menu.

Setting up discount amount and discount period $($start of period and end of period$)$ for

set meals and ala carte food items.

Updating the current undiscounted prices of set meals and ala carte items.

Establishing new outlets, each with an address, a seating capacity, area of location

$($north$,$ south, east, west, and central$),$ phone numbers, and a unique outlet name. Having

a central kitchen allows all outlets to offer the same set meals and ala carte items at

standard prices.

Accepting orders both online and in$-$person.

Each order is identified by an order number and date and time of order. Online order is

made through delivery platform to the outlet selected on the platform. An online order

also includes the delivery charges levied on the customer, the time the order was

collected. For every delivery platform, record the name of the delivery partner, an email

and a contact to call in cases of delivery issues. For in$-$person order, record the size of

the dining group.

Allowing for customer feedback

Customers can provide feedback on either the food items, service and or delivery. Each

feedback is identified by an auto$-$generated identification number, the date and time of

the feedback is made, a description and a rating. Upon receipt, a staff may categorise

the feedback as positive or negative and then provide a response with a datetime of

response. In some cases, there may be several follow$-$up responses to a feedback after

some investigation.

Some analysis includes the following queries, therefore, do ensure that your design can

support these queries:

The size of order in terms of number of food items purchased where food items in set

meal are individually counted. Include the total cost of the order, total discount applied,

the number of discounted food items and the number of food items without a discount.

The average spending per pax for in$-$person orders.

The number of online and in$-$person orders respectively in hourly slots, e$.$g$.,11\mathrm{.}00$ am

to $11\mathrm{.}59am,$ showing the most popular hour slots first.

The number of orders by outlet area of location $($north$,$ south, east, west, central$)$ and

the average number of phone lines $($or phone numbers$)$ for outlets in an area of location.

The number of negative and number of positive feedback received for orders made

online and orders made in$-$person for each outlet.

Construct a conceptual $($ER$)$ model from the statements of requirements to represent the data

model, showing

entities, with entities name, relevant attributes and identifier,

$(13$ marks$)$

relationships with maximum and minimum cardinalities and relationship name

$(17$ marks$)$

State your assumption$($s$)$ for only data requirements that are not specified. Show your model

as ONE diagram.