The assignment is designed to be done on your own. The assignment must be your own work, not copied from others or any other

course, textbook, or website. If plagiarism is detected, appropriate consequences will follow. Please refer to Policy ST$2$: Student

academic integrity.

Objective:

$-$ Design databases to meet user needs

Problem Specifications

For this assignment, you will be developing a database to support an online food ordering service. The core tables will encompass

user management, restaurant data, menu items, order processing, and payment handling. The database will consist of six

fundamental tables: users, restaurants, menu items, orders, and payments.

$1-$ Users $($userID$,$ firstname, lastname, email, password, phone, address$)$

$2-$ Restaurant $($resturantID$,$ name, address, phone, email, rating$)$

$3-$ MenuItem $($itemID$,$ resturantID, name, description, price, category$)$

$4-$ Orders $($orderID$,$ userID, resturantID, totalPrice, orderDate, deliveryDate$)$

$5-$ OrderItem $($orderItemID$,$ orderID, itemID, quantity, price$)$

$6-$ Payment $($paymentID$,$ orederID, userID, amount, paymentmethod, paymentDate$)$

$1-$ Download the FoodDB.accdb from the Moodle course website. Change the name by starting with your first name and then the database

name. Modify the file name, to begin with your first name and then the database name. For instance, if your first name is John, the

database should be named John$\_$FoodDB.accdb. Then, Fulfill the requirements for each table, as indicated below. $(10$ points$)$

Users Table

Column Name Data Type Constraint NULL value

userID Auto$-$number Primary Key No

firstname Char$(35)$ No

lastname Char$(35)$ No

email Char$(100)$ Unique No

password Char$(255)$ No

phone Char$(12)$ No

address Char$(255)$

Restaurant Table

Column Name Data Type Constraint NULL value

resturantID Auto$-$number Primary Key No

name Char$(100)$ No

address Char$(255)$ No

phone Char$(12)$ No

email Char$(100)$ Unique No

rating Numeric Two decimal places

MenuItem Table

Column Name Data Type Constraint NULL value

itemID Auto$-$number Primary Key No

resturantID Number Foreign Key No

name Char$(100)$ No

description Long Text

price Currency Two decimal places No

category Char$(50)$

Orders Table

Column Name Data Type Constraint NULL value

orderID Auto$-$number Primary Key No

userID Number Foreign Key No

resturantID Number Foreign Key No

totalPrice Currency Two decimal places No

orderDate Date$/$Time Default Current Date

deliveryDate Date$/$Time

OrderItem Table

Column Name Data Type Constraint NULL value

orderItemID Auto$-$number Primary Key No

orderID Number Foreign Key No

itemID Number Foreign Key No

quantity Number integer No

price Currency Two decimal places No

Payment Table

Column Name Data Type Constraint NULL value

paymentID Auto$-$number Primary Key No

orederID Number Foreign Key No

userID Number Foreign Key No

paymentmethod Char$(25)$ No

paymentDate Date No

The data that has been inserted into the first five tables using INSERT SQL statements is shown below.

$*$ remember that MS ACCESS can insert one record at a time.

Users Table

Restaurant Table

MenuItem Table

Orders Table

OrederItem Table

Payment Table

$2-$ Create the following relationships based on Figure $1.$ Force update of the foreign key $(6$ points$)$

Figure $1$ tables relationships

$3-$ Create the following queries. Save each query in different file using the format the firsttwolettersofyour name$\_$ Query$\_$#

where # is the question letter. For example, if your name is John then the A query will be saved using Jo$\_$Query$\_$A$.(24$

points$)$

A$-$ Display all users' first and last names with phone numbers from the USER table. Sort the result by the user's first name in

descending order.

B$-$ Display all the restaurant IDs from the ORDERS table without duplication with a total price of less than $$20.$

C$-$ Retrieve the first and last names of users from the USERS table, displaying them in a single column along with their email.

Ensure there is a space between the first and last names.

D$-$ Display the order ID and the time in minutes needed to deliver each order from ORDERS table.

E$-$ List all the users$$ first and last names with phone number start with $604.$

F$-$ Show all the first and last names of the users who made an order from a restaurant name entered while running this

statement $($Use implicit join to get full credits$).$

G$-$ Show each order with a description of the order item and the quantity.

H$-$ Display restaurant name with the total number of items sold.

I$-$ Retrieve all the orders with their items names which paid by credit card $($user INNER JOIN to get full credits$)$

J$-$ Display all the orders, items in each order and category. Sort result based on category.

K$-$ Retrieve the total price paid by each user for all of their orders, along with their first and last names.

L$-$ Retrieve first name, last name and phone number from USER table for users with first name beginning with the letter J or

M and the phone number end by $0.$ Display the result ordered by user id$.$

$4-$ Using SQL state