Pizza Ordering System: ER Model, Relational Schema, and SQL Queries

This project focuses on designing and implementing a relational database for a pizza ordering system. It begins with an Entity-Relationship (ER) model, outlining entities such as customers, orders, pizzas, ingredients, employees, and the restaurant itself, along with their attributes and relationships...Read More

Relational Database management system

Contents
INTRODUCTION......................................................................................................................................3
TASKS..................................................................................................................................................... 3
1.Design and draw an ER model for the pizza delivery database...........................................................3
2.Implement the relational data schemas based on your ER model......................................................5
3. Solve Queries................................................................................................................................20

INTRODUCTION
Relational database management system is a technique for creating an effective and efficient
database. This database system useful in the organization for managing all the information in proper
manner. This assignment will describe the entity relationship diagram and implementation of database
management system.
TASKS
1.Design and draw an ER model for the pizza delivery database
Figure:1

Entity relationship is a graphical representation Database is absolutely an integral part of software
system. To fully utilize ER Diagram in database engineering guarantee you to produce high quality
database design to use in database creation, management and maintenance. An ER model also provides
a means for communication. According to figure: 1, there are important entities and attributes discuss in
given below:
Entity:
customer
Attributes:
Customer_id
Customer_name
Customer_Email
Customer_phone no
Customer_city
Entity:
order
Attributes:
Order_id
Order_date
Order_time
Order_status
Entity:
Pizza
Attributes:
Pizza_id
Pizza_name
Pizza_price
Pizza_amount
Entity:
Non_pizza
Attributes:

N_id
N_name
N_amount
N_price
Entity:
Ingredient
Attributes:
Ingredient_id
Ingredient_name
Ingredient_price
Entity:
Employee
Attributes:
Employee_id
Employee_name
Employee_contact no
Employee_city
Entity:
restaurant:
Attributes:
Street_no
Street_name
stuburb
This entity relationship diagram shows the entire entities and attributes that is performing different
specific role playing in database management system. Customer is relationship with their order. It also
shows relationship in between them because many customer order variety of pizza. There are important
relationship representing in the er diagram such as
Customer is relationship with order.
They also place order by using online and offline mode.

Employee relationship with order because they will finalize the order of particular person or client.
Pizza, non-pizza and order are representing the ISA relationship because they are connected to each
other. They perform specific task and operations in the online pizza ordering system.
2.Implement the relational data schemas based on your ER model
Figure: 2
This is Entity relationship model of pizza ordering system. They provide specific information regarding
the database management system. It is important for determining the need and requirement of
database. This management system will manage the entire business process in proper manner.

Database tables:

Insert value in tables:

SQL Queries:

3. Solve Queries
a. List all the employee information of employees who have the roles of both cook and driver in the
restaurant.

Create a view EmployeeSalary() to list all the employees and their total income.

List the top 10 most profitable ingredients in the restaurant in terms of the number of pizzas sold.