DB FOUNDATION – ASSIGNMENT 3

Use Case: Online Food Ordering System

Objective:

In this assignment, you will practice views, joins, subqueries,

transaction control, indexes, triggers, stored procedures, and functions.

Recommended Tool:

 Oracle LiveSQL
 Mysql Workbench

Part A – Advanced Query Writing

Create a view named CustomerOrdersView that shows

customer name , order_id, order_date, and total_amount.

To create a SQL view named CustomerOrdersView that displays

customer name, order_id, order_date, and total_amount, you need the
following information from your database:

- The table containing customer names (usually a Customers or Users

table).

- The table(s) containing orders, including order_id, order_date,

total_amount, and a way to relate orders to customers (e.g.,
customer_id).

Assuming the typical table structure:

- customers: customer_id, customer_name, ...

- orders: order_id, customer_id, order_date, total_amount, ...

Here is the SQL statement to create the desired view:

sql

CREATE VIEW CustomerOrdersView AS

SELECT

c.customer_name,

o.order_id,

o.order_date,

o.total_amount

FROM

customers c

JOIN orders o ON c.customer_id = o.customer_id;

If your table or column names differ, replace them accordingly. This

view will give you a simple way to query customer names alongside
their order details.

Display the top 3 Customers who spent the most on orders

To display the top 3 customers who spent the most on orders, you’ll
want to sum each customer’s total amount spent and then pick the top
three. Assuming the same table structure as before (customers and
orders tables), you can use the following SQL query:
sql

SELECT

c.customer_name,

SUM(o.total_amount) AS total_spent

FROM

customers c

JOIN orders o ON c.customer_id = o.customer_id

GROUP BY

c.customer_name

ORDER BY

total_spent DESC

LIMIT 3;

This query joins customers to their orders, sums up the amount each
customer has spent, sorts them by that sum in descending order, and
returns only the top three customers. If your tables or columns have
different names, adjust accordingly.

Find all restaurants that have not received any views.

Find all restaurants that have not received any reviews

To find all restaurants that have not received any reviews, you typically
need two tables:

- restaurants: restaurant_id, restaurant_name, etc.

- reviews: review_id, restaurant_id, review_text, etc.

You can use a LEFT JOIN to identify restaurants without matching

reviews, or a NOT EXISTS subquery. Here are two common SQL
approaches:

Using LEFT JOIN

sql

SELECT r.restaurant_id, r.restaurant_name

FROM restaurants r

LEFT JOIN reviews rv ON r.restaurant_id = rv.restaurant_id

WHERE rv.review_id IS NULL;

Using NOT EXISTS

sql

SELECT r.restaurant_id, r.restaurant_name

FROM restaurants r

WHERE NOT EXISTS (

SELECT 1 FROM reviews rv WHERE rv.restaurant_id = r.restaurant_id

);

Both queries will return all restaurants for which there are no
corresponding reviews in the reviews table. Adjust table or column
names as needed for your schema.
To find all restaurants that have not received any reviews, you typically
need two tables:

- restaurants: restaurant_id, restaurant_name, etc.

- reviews: review_id, restaurant_id, review_text, etc.

You can use a LEFT JOIN to identify restaurants without matching

reviews, or a NOT EXISTS subquery. Here are two common SQL
approaches:

Using LEFT JOIN

sql

SELECT r.restaurant_id, r.restaurant_name

FROM restaurants r

LEFT JOIN reviews rv ON r.restaurant_id = rv.restaurant_id

WHERE rv.review_id IS NULL;

Using NOT EXISTS

sql

SELECT r.restaurant_id, r.restaurant_name

FROM restaurants r

WHERE NOT EXISTS (

SELECT 1 FROM reviews rv WHERE rv.restaurant_id = r.restaurant_id

);
Both queries will return all restaurants for which there are no
corresponding reviews in the reviews table. Adjust table or column
names as needed for your schema.

Write a query to display the order_id, item_name, quantity, and

subtotal (quantity*price) for each order.

Write a query to display the order_id, item_name, quantity, and

subtotal

(quantity × price) for each order.

Assuming your database has the following common structure:

- orders: order_id, ...

- order_items: order_id, item_id, quantity, price

- items: item_id, item_name, ...

Here is a SQL query that displays order_id, item_name, quantity, and

the subtotal (quantity × price) for each order:

sql

SELECT

oi.order_id,

i.item_name,

oi.quantity,

(oi.quantity * oi.price) AS subtotal

FROM
 order_items oi

JOIN items i ON oi.item_id = i.item_id;

If your table or column names are different, adjust them accordingly.

This query pulls each order's details, including the calculated subtotal
for each item in the order.

Use a nested query to find customers who ordered the most

expensive food item

Use a nested query to find customers who ordered the most expensive
food

item

Here’s how you can write a SQL query, using a nested (subquery), to
find all customers who have ordered the *most expensive food item*.
Assume the following tables:

- customers: customer_id, customer_name

- orders: order_id, customer_id

- order_items: order_id, item_id, quantity, price

- items: item_id, item_name

Query

sql

SELECT DISTINCT c.customer_id, c.customer_name

FROM customers c

JOIN orders o ON c.customer_id = o.customer_id

JOIN order_items oi ON o.order_id = oi.order_id

WHERE oi.item_id = (

SELECT item_id

FROM items

ORDER BY price DESC

LIMIT 1

);

*Explanation:*

- The subquery finds the item_id of the food item with the highest
price.

- The main query selects all distinct customers who have an order_item
with this item_id.

If the highest price occurs for multiple items, use IN instead of = and
adapt the subquery:

sql

WHERE oi.item_id IN (

SELECT item_id

FROM items

WHERE price = (SELECT MAX(price) FROM items)

Change table and column names as necessary for your schema.

 Show the restaurant and food item names where the discount
(offer) is greater than 20%

Show the restaurant and food item names where the discount (offer) is

greater than 20%.

Assuming you have these tables:

- restaurants: restaurant_id, restaurant_name

- items (or food_items): item_id, restaurant_id, item_name, discount

(or offer) — where discount is a percentage value

Here is a SQL query to show the restaurant name and food item name
where the discount is greater than 20%:

sql

SELECT

r.restaurant_name,

i.item_name

FROM

restaurants r

JOIN items i ON r.restaurant_id = i.restaurant_id

WHERE

i.discount > 20;

Modify the table and column names if necessary for your database
schema. This query filters for items with a discount greater than 20%
and lists the corresponding restaurant and item names.

Part B –Transaction Control(TCL)

Insert a new order into the Orders and OrderDetails tables

Insert a new order into the Orders and OrderDetails tables.

To insert a new order, you typically need to insert data into two tables:

- Orders: stores order-level details (e.g., order_id, customer_id,

order_date, total_amount)

- OrderDetails (or Order_Items): stores item-level details for each order

(e.g., order_id, item_id, quantity, price)

Assuming the typical columns in each table:

- Orders(order_id, customer_id, order_date, total_amount)

- OrderDetails(order_detail_id, order_id, item_id, quantity, price)

Here’s an example SQL to insert a new order and its details. Note that
order_id and order_detail_id might be auto-generated (e.g., auto-
increment).

Insert into Orders

sql

INSERT INTO Orders (customer_id, order_date, total_amount)

VALUES (123, CURRENT_DATE, 250.00);

Assuming order_id is auto-generated, you need to retrieve it before
inserting into OrderDetails. In many databases, you can do this with
LAST_INSERT_ID() or returning clauses.

Insert into OrderDetails (example 2 items)

sql

-- Assume the last generated order_id can be retrieved as @order_id

INSERT INTO OrderDetails (order_id, item_id, quantity, price)

VALUES

(@order_id, 101, 2, 50.00),

(@order_id, 102, 3, 50.00);

If your database doesn’t support variables like @order_id, you need to

retrieve the last inserted order ID in your application code or use
appropriate SQL syntax for your DBMS.

Replace the values with actual data for your use case. Let me know if
you want the syntax for a specific database system.

Use SAVEPOINT after inserting the order but before inserting

the details.

Use SAVEPOINT after inserting the order but before inserting the
details.
Using a SAVEPOINT allows you to set a point within a transaction to
which you can later roll back without affecting the entire transaction.
This is useful if you want to insert the order first, set a savepoint, and
then attempt to insert the order details, and if something goes wrong,
you can roll back to the savepoint instead of rolling back the entire
transaction.

Here’s an example SQL workflow with SAVEPOINT for inserting an order

and order details:

sql

BEGIN TRANSACTION;

-- Insert into Orders

INSERT INTO Orders (customer_id, order_date, total_amount)

VALUES (123, CURRENT_DATE, 250.00);

-- Set savepoint after inserting the order

SAVEPOINT order_inserted;

-- Insert into OrderDetails (example for 2 items)

INSERT INTO OrderDetails (order_id, item_id, quantity, price)

VALUES

(LAST_INSERT_ID(), 101, 2, 50.00),

(LAST_INSERT_ID(), 102, 3, 50.00);

-- If all good, commit

COMMIT;
If an error occurs while inserting into OrderDetails, you can roll back to
the savepoint and decide how to handle it (e.g., retry or rollback the
entire transaction):

sql

ROLLBACK TO SAVEPOINT order_inserted;

-- Then either COMMIT or ROLLBACK the entire transaction depending

on the situation

Note: The exact SQL syntax and function to get the last inserted
order_id (e.g., LAST_INSERT_ID()) depend on your database system
(MySQL, PostgreSQL, SQL Server, etc.). Adjust accordingly.

Rolllback to the savepoint and check if the order details still exist.

Rollback to the savepoint and check if the order details still exist.

When you rollback to a savepoint, all changes made after that

savepoint are undone, but changes made before it remain. In this case,
rolling back to the savepoint after inserting the order but before
inserting the order details will remove the inserted order details but
keep the order itself.

Here’s the conceptual SQL flow:

sql

-- Rollback to savepoint to undo order details insert

ROLLBACK TO SAVEPOINT order_inserted;

After rolling back, to check if the order details still exist (they should
not), run a query like:

sql

SELECT *

FROM OrderDetails

WHERE order_id = (SELECT LAST_INSERT_ID());

This query should return no rows because the rollback to savepoint

removes all inserts to OrderDetails that happened after the savepoint.

To check the order still exists (it should), run:

sql

SELECT *

FROM Orders

WHERE order_id = (SELECT LAST_INSERT_ID());

You will find the order record remains after rollback to the savepoint.

Note: Depending on your database system, the method to get the

relevant order_id might be different; if you stored the order_id in a
variable at insert time, use that variable for these checks.

Finally, COMMIT the transaction.

Finally, COMMIT the transaction.

To finalize all changes and make them permanent after working with
savepoints and rollback, you execute the COMMIT statement:
sql

COMMIT;

This will commit the transaction, saving the inserted order and any
other changes made up to this point to the database permanently.

Part C – Indexes & Optimization

Create an index on the email column of the Customer table

Create an index on the email column of the Customer table.

To create an index on the email column of the Customer table, you can
use the following SQL statement:

sql

CREATE INDEX idx_customer_email ON Customer(email);

This index will improve the performance of queries that filter or search
by the email column. Adjust table or column names if needed.

Create an index on the restaurant_id column of the FoodItem

table.

Create an index on the restaurant_id column of the FoodItem table.

To create an index on the restaurant_id column of the FoodItem table,

use this SQL statement:

sql

CREATE INDEX idx_fooditem_restaurant_id ON

FoodItem(restaurant_id);
This index will enhance query performance involving the restaurant_id
column in the FoodItem table. Adjust names if your schema uses
different identifiers.

Add a short SQL comment explaining how indexes help improve

query performance.

Add a short SQL comment explaining how indexes help improve query

performance.

sql

-- Indexes improve query performance by allowing the database to

quickly locate and access the rows matching a search condition without
scanning the entire table.

Citations:

[1] Comments in SQL (with examples) - CodeChef

https://www.codechef.com/blogs/comments-in-sql

[2] SQL Comments - W3Schools

https://www.w3schools.com/sql/sql_comments.asp

[3] Comments Within SQL Statements

https://docs.oracle.com/cd/B13789_01/server.101/b10759/sql_eleme
nts006.htm

[4] SQL Comment Essentials: Mastering Code Annotation in Databases

https://www.dbvis.com/thetable/sql-comment-a-comprehensive-
guide/
[5] SQL Comments - GeeksforGeeks
https://www.geeksforgeeks.org/sql/sql-comments/

[6] MySQL Comments - W3Schools

https://www.w3schools.com/mysql/mysql_comments.asp

[7] SQL Comments - Syntax, Use Cases, and Examples | Hightouch

https://hightouch.com/sql-dictionary/sql-comments

[8] SQL Comments (With Examples) - Programiz

https://www.programiz.com/sql/comments

[9] -- (Comment) (Transact-SQL) - SQL Server | Microsoft Learn

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/comme
nt-transact-sql?view=sql-server-ver17

Part D – Triggers

Create a trigger named OrderStatusTrigger that

automaticallysets the status column in the Orders table to
‘pending’ when a new order is inserted.

1. Create a trigger named OrderStatusTrigger that automatically sets

the status

column in the Orders table to 'Pending' when a new order is inserted.

Here is an example SQL statement to create a trigger named

OrderStatusTrigger that sets the status column to 'Pending'
automatically when a new order is inserted into the Orders table. The
syntax varies slightly between database systems; here is a general
example for MySQL:
sql

DELIMITER //

CREATE TRIGGER OrderStatusTrigger

BEFORE INSERT ON Orders

FOR EACH ROW

BEGIN

SET NEW.status = 'Pending';

END;

//

DELIMITER ;

This trigger fires before a new row is inserted into the Orders table and
sets the status field to 'Pending'. Adjust syntax if you are using a
different database system.

Verify the trigger by inserting a new order and checking its

status.

Verify the trigger by inserting a new order and checking its status.

To verify the OrderStatusTrigger, first insert a new order without

specifying the status, then query the inserted order to check if the
status was automatically set to 'Pending'.

1. Insert a new order (without specifying status):

sql
INSERT INTO Orders (customer_id, order_date, total_amount)

VALUES (123, CURRENT_DATE, 100.00);

2. Check the status of the newly inserted order:

If order_id is auto-incrementing, you can find the latest order for the
customer:

sql

SELECT order_id, status

FROM Orders

WHERE customer_id = 123

ORDER BY order_date DESC, order_id DESC

LIMIT 1;

This should show the inserted order with the status as 'Pending',
confirming the trigger works correctly.

Part E – Stored Procedures & Functions

Create a stored procedure AddNewCustomer that inserts a new

customer into the Customer table(accept parameters like name,
email, phone, address).

Create a stored procedure AddNewCustomer that inserts a new

customer

into the Customer table (accept parameters like name, email, phone,

address).
Here is an example of a stored procedure named AddNewCustomer
that inserts a new customer into the Customer table. It accepts
parameters for name, email, phone, and address. This example is for
MySQL:

sql

DELIMITER //

CREATE PROCEDURE AddNewCustomer(

IN p_name VARCHAR(100),

IN p_email VARCHAR(100),

IN p_phone VARCHAR(20),

IN p_address VARCHAR(255)

BEGIN

INSERT INTO Customer (name, email, phone, address)

VALUES (p_name, p_email, p_phone, p_address);

END;

//

DELIMITER ;
You can call this procedure by passing the customer details as
arguments. Adjust data types and lengths to match your table schema if
different.sql

CREATE VIEW CustomerOrdersView AS

SELECT

c.customer_name,

o.order_id,

o.order_date,

o.total_amount

FROM

customers c

JOIN orders o ON c.customer_id = o.customer_id;

Create a function CalculateOrderTotal(ordered) that returns the

total amount for a given order by swimming up (quantity* price)
from OrderDetails and FoodItem.

Create a function CalculateOrderTotal(orderId) that returns the total

amount

for a given order by summing up (quantity × price) from OrderDetails

and

FoodItem.
Here is an example of a SQL function CalculateOrderTotal(orderId) that
calculates the total amount for a given order by summing up (quantity ×
price) from OrderDetails joined with FoodItem (assuming the price is in
FoodItem). This example is for MySQL:

sql

DELIMITER //

CREATE FUNCTION CalculateOrderTotal(p_orderId INT)

RETURNS DECIMAL(10, 2)

DETERMINISTIC

BEGIN

DECLARE total DECIMAL(10, 2);

SELECT SUM(od.quantity * fi.price) INTO total

FROM OrderDetails od

JOIN FoodItem fi ON od.item_id = fi.item_id

WHERE od.order_id = p_orderId;

RETURN IFNULL(total, 0);

END;

//

DELIMITER ;
This function takes an orderId as input and returns the total amount by
multiplying quantity and price for all items in that order. Adjust table
and column names if different in your schema.

Demonstrate both by:

o Calling the procedure to insert a new customer.
o Calling the function to calculate the total for a specific order.

Demonstrate both by:

○ Calling the procedure to insert a new customer.

○ Calling the function to calculate the total for a specific order.

Here’s how you can demonstrate both the stored procedure

AddNewCustomer for inserting a new customer and the function
CalculateOrderTotal to calculate the total amount for a specific order.

1. Calling the stored procedure to insert a new customer:

sql

CALL AddNewCustomer('John Doe', '[email protected]', '123-

456-7890', '123 Elm Street');

This will insert a new customer with the given name, email, phone, and
address into the Customer table.

2. Calling the function to calculate the total for a specific order:

Assuming you want to calculate the total for order ID 101:

sql

SELECT CalculateOrderTotal(101) AS total_amount;

This will return the total amount for the order with ID 101 by summing
quantity × price from OrderDetails joined with FoodItem.

Submission
Conclusion

The Food Ordering System database was successfully designed and

implemented using DBMS concepts. Key entities such as Customer,
Restaurant, FoodItem, Orders, OrderDetails, Review, and Offer were
created with appropriate constraints like PRIMARY KEY, FOREIGN
KEY, NOT NULL, UNIQUE, and CHECK to maintain data integrity and
consistency.

With this system:

 • Customers can be stored and managed efficiently.

• Restaurants and their food items are properly linked.

• Orders can be tracked for each customer along with order

details.

• Reviews and offers add more features for customer

engagement.

By applying SQL queries (SELECT, JOIN, WHERE, etc.), users can

retrieve meaningful insights like:

• All orders placed by a specific customer.

• All food items with their respective restaurants.

• Customer reviews and restaurant offers.

This structured relational database ensures accuracy, reliability, and

scalability, making it suitable for real-world online food
ordering platforms.