Database Queries and Normalization

The assignment content involves designing a relational database schema and performing various SQL queries on the database. The schema consists of nine tables: Patrons, Sales, Products, Stores, Employees, Departments, Inventory, Orders, and Invontry. The queries include listing patrons with specific conditions, retrieving product information, calculating total order amount per customer, and more.

Part A: Entity Relationship Diagram:
Part B: Normalisation:
We insert the data in undeclared sequence then we apply the normalization on table and
we got the result with sequence.

Part C: Relational schema and MySQL database:
Part D: SQL
1) List the full name of Patrons, phone number and address in the descending order of
customer number. The full name is comprised of first and last name joined with single space.
Use the alias Customer full name for the composed column heading.
Answer:
selec
tPatrons.Customer_Name,Patrons.Customer_Mobile,Patrons.Customer_addressfrom
Patrons
orderbyCustomer_numberdesc
Customer_Name Customer_Mobile Customer_address
Mili 789465423 5rd stage NY 54893
arhayn 987056456 2rd stage NY 54894
Shaym 456987987 3rd stage NY 54895

2) List all the products having price less than $100
Answer:select*from Sales
whereProduct_Price< 100
Product_number Profuct_name Product_brand Product_description
Product_Price Product_Store_number
9875 dress ramnond Good Quality dress 99 1
3) List the product number, product title, quantity ordered, unit price and total amount per
product (unit price * quantity ordered) for order having order number 1005.
Answer:
selec
tsales.Product_number,Sales.Profuct_name,orders.Quantity,Sales.Product_Price,S
ales.Product_Price*orders.Quantityas [Total amount per product] from orders
leftjoin Sales onorders.Product_number=Sales.Product_number
Product_number Profuct_name Quantity Product_Price Total amount per
product
9877 makeup 3 150 450
9875 dress 1 99 99
9876 Watch 5 199 995
9874 Iphone 6s 2 299 598

4) List name, full address and fax number of stores that has the word ‘George’ anywhere in the
street component of address. Your query should consider the case sensitivity of the street as
well i.e. the stores with words ‘George’, ‘GEORGE’, ‘george’, ‘GeorGe’ etc. in street should be
returned by your query too
Answer:
select*from stores
whereStore_locationlike'%Aven%'
Store_number Store_name Store_Phone Store_Email Store_fax
Store_locationStore_department_number
3 R Branch 645698 [e-mail] Rbranch15 5Th
Aven,NY,45646 102
5) List all the orders having at least two different products ordered:
Answer:
select*from Sales
Product_number Profuct_name Product_brand Product_description
Product_Price Product_Store_number
9874 Iphone 6s apple the Phone comes with a 1810 mAh battery etc 299
2

9875 dress ramnond Good Quality dress 99 1
9876 Watch MI the Watch comes with a Good mAh battery etc 199 2
9877 makeup reddmy all part is included 150 3
6) List customer number, name and address of all the customers who has not placed any order
Answer:
selectPatrons.*,Invontry.Product_statusfrom Patrons
leftjoin Sales onPatrons.Store_Number=Sales.Product_Store_number
leftjoinInvontryonSales.Product_number=Invontry.product_number
whereInvontry.Product_statuslike'cancel%'
Customer_number Customer_Name Customer_Mobile
Customer_address Store_Number Product_status
789458 Mili 789465423 5rd stage NY 54893 3 cancel
7) Retrieve first name, last name and employee number of all the employees along with
department name and store name where they work.
Answer:
select
Employee
s.Employee_name,Employees.Employees_Number,Deparement.Department_name,stores.S
tore_name from Employees
leftjoinDeparementonEmployees.department_number=Deparement.Department_number
leftjoin stores onDeparement.Department_number=stores.Store_department_number
Employee_name Employees_Number Department_name Store_name
Sam John 1001 Account Z Branch

Wena John 1002 Finance R Branch
tena stark 1003 Sales NULL
Raj Jon1004 HR X Branch
8) For each store, display store name and number of employees works in account department:
Answer:
selectstores.Store_name,stores.Store_number,Deparement.Department_name from
stores
leftjoi
nDeparementonstores.Store_department_number=Deparement.Department_number
whereDeparement.Department_namelike'Account%'
Store_name Store_numberDepartment_name
Z Branch 2 Account
9) List all the orders placed for financial year 2018 i.e. orders placed after 30/06/2017 and
before 01/07/2018.
Answer:
select*from orders
whereOrder_datelike'2018%'
Order_number Order_date Product_number Quantity
321456787 2018-12-26 00:00:00.0000000 9876 5
321456789 2018-12-26 00:00:00.0000000 9874 2

10) Retrieve customer number and the total number of orders placed by each customer.
Answer:
selectPatrons.Customer_number,sum(orders.Quantity)as [Total number of order]
from Patrons
leftjoin Sales onPatrons.Store_Number=Sales.Product_Store_number
leftjoin orders onSales.Product_number=orders.Product_number
groupbyPatrons.Customer_number
Customer_number Total number of order
789456 1
789457 7
789458 3
11) For each order, retrieve the order number, order date and number of products ordered in it.
Sort the output in descending order of order number.
Answer:
selectorders.Order_number,orders.Order_date,sum(orders.Quantity)as[number of
products ordered] from orders
groupbyOrder_number,Order_date

orderbyOrder_numberdesc
Order_number Order_date number of products ordered
321456789 2018-12-26 00:00:00.0000000 2
321456787 2018-12-26 00:00:00.0000000 5
321456780 2019-02-18 00:00:00.0000000 1
321456770 2019-05-26 00:00:00.0000000 3
Part E: Personal Report:
Experience building the database: Great experience with Building the database here we create a
primary keys and foreign key after that we denote the relationship on that. Create the 9 table and
insert the data on all tables. Use draw.io for ER diagram. All table and variable denoted there. Solve
every question and written here answer too with sql coding and screen shot. Here we have face the
problem on building the ER diagram but I solve all problem. We take here only 4 rows data only but
if we have more than this data this code also working if variable is same.