Question

Exercise III (30 pts): Industrial Database design and querying. Restaurant ordering world is an interesting example of industrial application for database. This exercise is concerned with modeling of the database of Zomato application for online ordering food. Propose a logical E/R diagram for the data set of this application. Make sure to indicate all cardinality constraints. The E/R diagram should not contain redundant entity sets, relationships, or attributes. Also, use relationships whenever appropriate. b- Write the query of the search bar that the customer uses to search for restaurant nearby his location.

Answer 1

Below is the logical E/R diagram

Customer entity has attributes like Name,Contact number and email and among all these attributes contact number and email are primary keys which should be unique for each customers.

Pice Discount Contact numbo Email Prdex id Time uartity Customer Places N has Details Ordes 1 1 Special nstruction Restaursant Name Name has has Doeshe exists 1 1 - UPT y ment Pelivory Sign Up Net banking Cash On Delivery Cre dit/Debit Card Agent Contact No

If the customer doesn't exist he can sign up and the relation is given as 1:N because one customer can have N accounts with different Contact Number and Email.

The Customer entity and Order entity has a relation called "Places",which means customer places order and the cardinality will be 1:N because one customer can place N orders at a time and each order has attributes like order_id,time at which order has been placed and the restaurant name from where the order has been placed.Order entity and Payment entity has a relation with cardinality 1:1 which means one order has one mode of payment.Payment entity has attributes like UPI,Debit/Credit Card,Net Banking and Cash On Delivery.Order and Delivery linked by relation "Has" with cardinality 1:1 because one order will be delivered by one person at a time.Order entity is linked with details with a relation "has"and the Details have attributes like Quantity,Special Instructions,Discount and Price and the relation between order and details is N:N because one order may consist of many items .

(b)

SELECT * FROM 'Restaurants' WHERE 'location'= Diners Street