Restaurant Database

Restaurant Database [ 40 points]The student administrator of the Mini University wants to design a database for profiling students’ preferences for the nearby restaurants and the dishes. For example, the student Jack Smith likes the “rib eye steak” at the “TGI Fridays”, not the “rib eye steak” at the “Olive Garden”, while the student Nancy Graham likes the Italian spaghetti at “Zeppoli’s”.• Each student has his/her SSN, the name, and the department.• Each restaurant has its name, and the mailing address.• The name of a restaurant may not be unique, but the mailing address is unique.• Each dish has the name and the price.• The price of a dish might differ on different restaurants. For example, “rib eye steak” is $15 at “TGI Fridays”, but $20 at the “Olive Garden”.• The name of a dish is unique within a restaurant. Two restaurants may have the same dish name.• A dish is offered by at least one restaurant, and a restaurant offers at least a dish.Please answer the following questions:Q2.1. (10 points) Draw an ER diagram for this database. Make sure to indicate primary keys, cardinality constraints, weak entities (if any), and participation constraints. List any assumptions you make in the process.Q2.2. (10 points) Translate the ER diagram in Q2.1 into relational database tables (i.e. give the SQL DDL statements). Make sure that the translation captures key constraints (primary keys and foreign keys if applicable) and participation constraints in the ER diagram. Identify constraints, if any, that you are not able to capture.Q2.3. (20 points) Identify the functional dependencies that you expect should hold for the Restaurant Database domain. Use the dependencies to find keys and BCNF normal forms.For more information on Restaurant Database check on:https://en.wikipedia.org/wiki/Restaurant