Online Food Delivery System Database Project Assignment

This assignment details the design and implementation of a database system for an online food delivery service. The project begins with an introduction to database systems and their functionalities, followed by the creation of an Entity Relationship (ER) Diagram to visually represent the database's structure, including entities like food, restaurants, orders, and customers, along with their attributes and relationships. A comprehensive data dictionary is provided, outlining each entity and its associated attributes. The core of the project involves constructing database tables using MS SQL Server, detailing the structure and data types for each table. SQL statements are then utilized to query and manipulate the data, including examples of DDL and DML commands to retrieve, update, and modify information. Finally, the project concludes with the creation of an Oracle Application Express (APEX) form to handle data entry, ensuring user-friendly interaction with the database. References to relevant literature are also included.

INTERMEDIATE DATABASE
1

Contents
INTRODUCTION...........................................................................................................................3
TASKS.............................................................................................................................................3
Entity Relationship Diagram.......................................................................................................3
Data dictionary:...........................................................................................................................4
Construct the database tables.......................................................................................................8
SQL Statement and provide the results based on the records....................................................12
Create one oracle form to handle entry that must display and enter data..................................18
CONCLUSION..............................................................................................................................20
REFERENCES..............................................................................................................................21
2

INTRODUCTION
Database system is defined as a collection of information that can store in the form of single
data set. It should be maintained, update, retrieve and modify the data in the database system.
This assignment is based on the online food delivery system that provide the services to the
customer. It useful for consumer to select the different categories of food item by using online
system. This documentation will discuss about the database table that created by MS SQL server
tool to store record in proper manner. It will construct the form by using Oracle Application
Express (APEX).
TASKS
Entity Relationship Diagram
Figure 1 ER Model
Entity relationship diagram is a type of graphical representation that can be shown all entities
and its specific attributes (Abad iand et.al., 2018). Each one has established a relationship
between one and more tables. It should be containing values in each column of database system.
It has some specific primary key and foreign key in the database system. It is an essential part of
DBMS that help to establish a relationship between one or more tables. In this diagram, it shown
3

all the necessary attributes of each table in proper manner.
Data dictionary:
Entities Attributes
Food table  Food_no (PK)
 Food_name
 Food_type
 Food_menu
 Food_expiry_hour
 Food price
 Restaurtant_id (FK)
Restaurant table  Restaurant_ID (PK)
 Restaurant_Name
 Restaurant_Address
 Restaurant_Phone
 Restaurant_Email
 Restaurant_PIC
Order table  Order_ID (PK)
 Order_Date
 Order_Qty
 Order_Amount
 Food_No (FK)
 Cust_ID (FK)
Customer table  Cust_ID (PK)
4

 Cust_Name
 Cust_Address
 Cust_Phone
 Cust_Email
Table:1
Database Name: - FoodDB
5

6

7

Construct the database tables
Customer table
Figure 2
According to Figure-2, It is based on the customer database table that contain the information
about of each consumers in the form of tables. It is the simplest way to represent the detail of
customer. It should be setting the particular size of index so that it can store or collect values in
it.
8

Food table
Figure 3
This figure is representing the Food database table that consits of various type of information
about the food items sucha as Food_no, Food_name, Food_expiry_hour and Food_menu etc.
These are the information contain in the table format where all data should be store in proper
ways (Dennis, Wixom and Roth, 2018). It should be setting the particular size of index so that it
can store or collect values in it.
9

Order table
Figure 4
According to figure 4, it can be representing the order table where it has to be stored in
the full details about the particular order in it. It useful for customer to check the order status
through database system (Vasarhelyi and Halper, 2018). It can easy to access the information for
finding the particular details about the order. It is stored in the table format where all data should
be store in proper ways. It esnure to fix the size of data type so that it can enter the value at
limited ways
10

Restaurant table
Figure 5
According to figure 5, it can represent the restaurtant table that can store the details about the
particular hotel such as id, address, email, name and pls etc. there are different restaurtants
access the online database system to check the details about order of customers. In this way,
Many restaurants are active memmbers to monitor the entire activities of people those who
wanted to place order by using online medium. It is stored in the table format where all data
should be store in proper ways. It esnure to fix the size of data type so that it can enter the value
in it.
11

SQL Statement and provide the results based on the records.
SQL stands for the structure query language that help for designing and developing the
relational database system (MaWei and Jian, 2019). It is the most important programming
language for performing different task such as modify, delete, creation and update etc. It is also
known as domain specific language that easily managing the entire management system and its
processing. A SQL statement can be categorising into different ways: DDL and DML
DML:
It refers to data manipulation language that can use for store, delete, modify and insert
data in the database system (Sayar and Er,2018). For Example- Select, insert and update
commands. This language must be created the data schema and classified in proper manner. It
can perform the operations after creating a DBMS tables that help to fill the records
DDL:
It stands for data definition language that can use to modify and create the overall
structure of database system (Rajaraman, 2018). For Example- it uses the different commands
such as Alter, Drop and create etc. This language always manipulates and populate data in the
database system. Designer is understanding the properties of data as a specific domain and its
attributes. It provides the facility to easily specify the necessary constraints that can control the
data consistency.
12

Show the number of food based on menu type.
13

Show all food sold by restaurants with food menu type “western”.
List the customer details who lives in “Subang”.
14

Show the total purchase information by getting the overall total purchased for food menu type
“Thai”.
Update the expiry hour for all food sold by restaurants name “ ABC” with additional 2 Hours. It
shows the after and before update records.
Before update
15

Display the customer_name, food_type, food_menu type, order_date, food_name and
order_price for last 30 days.
Display the details of customer, total number of order and total order amount made by customer.
16

Modify the order table by adding new column entry for the request made by consumer.
17

Create one oracle form to handle entry that must display and enter data.
18

19

CONCLUSION
As per discussion, it concluded that design an effective database system of online food
delivery through different restaurants. It helps the customer to select particular food item
according to requirement. It is the most efficient concept for increasing the demand in global
marketplace because many people are used the online technology so that it easy for succeed the
food delivery system.
20

REFERENCES
Book and Journals
Abadi, S. and et.al., 2018. Design of online transaction model on traditional industry in order to
increase turnover and benefits. International Journal of Engineering and
Technology. 7(2.27). pp.231-237.
Dennis, A., Wixom, B.H. and Roth, R.M., 2018. Systems analysis and design. John wiley &
sons.
Ma, P., Wei, K. and Jiang, C., 2019, January. Evaluating Distributed Transactional Database
System. In Proceedings of the 11th International Conference on Computer Modeling and
Simulation (pp. 203-207). ACM.
Rajaraman, V., 2018. Analysis and design of information systems. PHI Learning Pvt. Ltd..
Sayar, D. and Er, Ö., 2018. The antecedents of successful IoT service and system design: Cases
from the manufacturing industry. International Journal of Design. 12(1). pp.67-78.
Vasarhelyi, M.A. and Halper, F.B., 2018. The continuous audit of online systems. In Continuous
Auditing: Theory and Application (pp. 87-104). Emerald Publishing Limited.
21

22