Online Food Ordering Database

Online Food Ordering Database.Create a step-by-step user guide on how to run the above scripts to create the database from scratch using the “CreateDB.sql” and to populate some data into the generated tables using the “PopulateData.sql.” Title this document “User Guide.” Your user guide should be detailed enough to include your comments describing the SQL functionalities and overall tasks, along with screenshots of all steps and their outputs. Create a screencast video presentation using Screencast-O-Matic in which you walk the instructor through your Online Food Ordering Database. (Quick-start guides for Screencast-O-Matic (Links to an external site.) are provided for your convenience.) Be sure to describe the concepts related to the design and management of your database. Your screencast should be eight to 10 minutes in length. (It is highly recommended that you create a script and/or speaker’s notes to ensure that your screencast will meet the time requirements. This will also allow you to practice your presentation prior to recording it.) Once your screencast has been created, make sure to include the URL for your screencast at the end of your User Guide.

Online Food Ordering Database

The online food ordering database will enable customer registration, administrator registration, product display on the dashboard, selection of food products by customer and placing order. The database will allow increasing or decreasing of quantity ordered, addition of new food product, deletion of existing product from the selection list, and modifying the name and price of an existing product.

Get Your Custom Essay Written From Scratch
Are You Overwhelmed With Writing Assignments?
Give yourself a break and turn to our top writers. They’ll follow all the requirements to compose a premium-quality piece for you.
Order Now

The following SQL scripts represent the schema for the database and populate data respectively:

 

CREATE SCHEMA `Online_Food_Ordering_DB`;

 

CREATE TABLE `Online_Food_Ordering_DB`.`Restaurant`

(

`restaurant_name` varchar(50) NOT NULL,

`restaurant_id` int(10) NOT NULL,

`restaurant_address` varchar(50) NOT NULL,

`restaurant_rating` varchar(50) NOT NULL,

`restaurant_phone` varchar(50) NOT NULL,

`restaurant_BusinessHours` Time NOT NULL,

`restaurant_email` varchar(50) NOT NULL,

)

DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE `Restaurant` ADD PRIMARY KEY(`Restaurant_id`);

 

 

CREATE TABLE `Online_Food_Ordering_DB`.`Customers`

(

`C_id` int(10) NOT NULL,

`C_username` varchar(50) NOT NULL,

`C_firstName` varchar(50) NOT NULL,

`C_lastName` varchar(50) NOT NULL,

`C_password` varchar(50) NOT NULL,

`C_phone` varchar(50) NOT NULL,

`C_address` varchar(255) NOT NULL,

`C_paymentMode` varchar(50) NOT NULL,

)

ALTER TABLE `Customers` ADD PRIMARY KEY(`C_id`);

 

 

CREATE TABLE `Online_Food_Ordering_DB`.`Product`

(

`P_id` int(10) NOT NULL,

`P_Description` varchar(255) NOT NULL,

`P_weight` int(10) NOT NULL,

`P_price` decimal(10) NOT NULL,

)

ALTER TABLE `Product` ADD PRIMARY KEY(`P_id`);

 

 

CREATE TABLE `Online_Food_Ordering_DB`.`OrderDetails`

(

`O_id` int(10) NOT NULL,

`C_id` int(10) NOT NULL,

`O_Date` DateTime NOT NULL,

`O_Number` varchar(50) NOT NULL,

`O_Price` decimal(10) NOT NULL,

`O_Discount` decimal(10) NOT NULL,

)

ALTER TABLE `OrderDetails` ADD PRIMARY KEY(`O_id`);

ALTER TABLE `OrderDetails` ADD FOREIGN KEY(`C_id`);

 

 

CREATE TABLE `Online_Food_Ordering_DB`.`tbl_Employee`

(

EmployeeID int not null auto_increment primary key,

Employee_LastName varchar (50)

Employee_FirstName varchar (50)

EmployeeSalary int (20)

)

 

 

CREATE TABLE `Online_Food_Ordering_DB`.`tbl_Employee_audit`

(

Employee_auditID int not null auto_increment primary key,

audit_data, varachar (50)

)

 

 

 

create trigger on tbl_Employee after INSERT

INSERT on tbl_Employee for

(EmployeeID, Employee_LastName, Employee_FirstName, EmployeeSalary)

INSERT into tbl_Employee_audit values

(new EmployeeID)

 

 

Create trigger tbl_Employee after DELETE on tbl_Employee for

(EmployeeID, Employee_LastName, Employee_FirstName, EmployeeSalary)

DELETE into tbl_Employee_audit values

(new EmployeeID)

 

 

 

 

SQL Script for populate data:

Inserting Data into Restaurant Table

 

INSERT INTO Online_Food_Ordering_DB.Restaurant (

R_name, R_id, R_address, R_rating, R_phone, R_businessHours, R_email)

VALUES (‘Oyster R’,’001′,’9/10′,’+1 313-300-2700′,’8AM – 8PM’,’info@oysterr.org’);

 

 

INSERT INTO Online_Food_Ordering_DB.Restaurant (

R_name, R_id, R_address, R_rating, R_phone, R_businessHours, R_email)

VALUES (‘Melvins’,’002′,’8/10′,’+1 313-300-1700′,’9AM – 8PM’,’melvinsfoods@gamil.com’);

 

 

 

Inserting data into Customers table

 

INSERT INTO Online_Food_Ordering_DB.Customers (

C_id, C_username, C_firstName, C_lastName, C_password, C_phone, C_email, C_address, C_paymentMode)

VALUES (’01’,’Britanny1′,’Ali’,’Assad’,’xxxxxxxxx’,’+1 313-415-0100′,’alihasadi@mail.com’,’23rd Avenue Bronx’, ‘MasterCard’);

 

INSERT INTO Online_Food_Ordering_DB.Customers (

C_id, C_username, C_firstName, C_lastName, C_password, C_phone, C_email, C_address, C_paymentMode)

VALUES (’02’,’Ashley1′,’Ashley’,’Leyton’,’xxxxxxxxx’,’+1 313-433-0200′,’aleyton@mail.com’,’2075 West Virginia Avenue, Albany’,’CreditCard’);

 

 

 

Inserting data into Product table

 

INSERT INTO Online_Food_Ordering_DB.Product (

P_id, P_Description, P_weight, P_price)

VALUES (‘100′,’Rich in minerals and vitamins’,’3 lbs’,’$19.00′);

 

INSERT INTO Online_Food_Ordering_DB.Product (

P_id, P_Description, P_weight, P_price)

VALUES (‘101′,’Packed with crucial iron and phosphorus’,’1lbs’,’$10.00′);

 

 

Inserting data into OrderDetails table

INSERT INTO Online_Food_Ordering_DB.OrderDetails (

O_id, C_id, O_Date, O_Number, O_Quantity, O_Price, O_Discount)

VALUES (‘001′,’01’,’04/22/2022′,’160′,’2 tins’,’$38.00′,’0.00′);

 

INSERT INTO Online_Food_Ordering_DB.OrderDetails (

O_id, C_id, O_Date, O_Number, O_Quantity, O_Price, O_Discount)

VALUES (‘002′,’02’,’04/19/2022′,’170′,’1 packet’,’$20.00′,’0.00′);