Part 1: A restaurant owner has approached your team. He has one branch and plans to open another two branches. Also, he wants to enable the restaurant customers to order from an application and save their orders. The owner currently has a system for managing the restaurant data. However, the current system does not support some features such as adding branches. Your task is to produce an ER diagram for a new database that can support the improvements. Here is the gathered information:

The restaurant has many branches. Each branch has a unique number, a detailed address, an email, and a phone number. The branches provide the same menu which has many dishes. Each dish has a unique name, price, a recipe with ingredients, food allergy (if any), and serving time. The serving time is only breakfast, lunch, or dinner.

The restaurant has employees, chefs, and customers. Some information needs to be kept for all of them including their ID number, full name, phone number,email, and address. Therefore, some specialized data need to be stored. For instance, a customer has a password for his account, an employee has a position and salary, and a chef has a salary and speciality (i.e.: pastrychef, fish chef, etc.). The employees and chefs can be customers of the restaurant branches.

People can place multiple orders on the restaurant application from any branch and the order must have dishes. The order is identified by a unique number, date, time, comments (if any), and a total price which is derived from the number of dishes and their prices. Each chef must cook a dish and to a maximum of 10 dishes, and dishes must be cooked by one to many chefs. Also, each chef works on one and only one branch.

There is a tutoring program, arranged by the restaurantowner, only for the chefs. Here, one chef may tutor many other chefs and each chef may be tutored by one another chef. The system needs to store this.

Identify entities, relationship, attributes and theirtypes, keys, and relationship constraints: Cardinality and Participation. Then, draw the ER diagram that captures the above information using an application program like Visio, Edraw, . etc.

If the scenario did not clarify some relationships or requirements. Make clear assumptions and include them in your diagram.

The answer is:

Part 2: Map the ER Diagram that you drew in Part 1 into a relational schema and specify all primary keys and foreign keys to each relation.

Note: you can use a different font colour for FKs.