Database Management System

The assignment content is about Database Management System, which includes Entity Relationship Diagram, primary keys and foreign key, creation of 9 tables, insertion of data into all tables, use of draw.io for ER diagram, solving of every question and providing SQL coding with screenshots. The report highlights the experience and knowledge gained while building the database, denoting relationships between tables and using draw.io for ER diagram.

Database Management System

Table of Contents
Part A: Entity Relationship Diagram:......................................................................................................1
Part B: Normalisation:...........................................................................................................................2
Part C: Relational schema and MySQL database:..................................................................................3
Part D: SQL.............................................................................................................................................3
Part E: Personal Report:.........................................................................................................................6

Part A: Entity Relationship Diagram:
We use Draw.io and create this ER Diagram. Here we have 9 tables.
ER Diagram in SQL:

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: select Patrons.Customer_Name,
Patrons.Customer_Mobile,Patrons.Customer_address from Patrons
order by Customer_number desc
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
where Product_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:

select
sales.Product_number,Sales.Profuct_name,orders.Quantity,Sales.Product_Price,
Sales.Product_Price*orders.Quantity as [Total amount per product] from orders
left join Sales on orders.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
where Store_location like '%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:
select Patrons.*,Invontry.Product_status from Patrons
left join Sales on Patrons.Store_Number=Sales.Product_Store_number
left join Invontry on Sales.Product_number=Invontry.product_number
where Invontry.Product_status like '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
left join Deparement on
Employees.department_number=Deparement.Department_number
left join stores on
Deparement.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:
select stores.Store_name,stores.Store_number,Deparement.Department_name from
stores
left join Deparement on
stores.Store_department_number=Deparement.Department_number
where Deparement.Department_name like '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
where Order_date like '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:
select Patrons.Customer_number,sum(orders.Quantity)as [Total number of order]
from Patrons
left join Sales on Patrons.Store_Number=Sales.Product_Store_number
left join orders on Sales.Product_number=orders.Product_number
group by Patrons.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:
select orders.Order_number,orders.Order_date,sum(orders.Quantity) as[number of
products ordered] from orders
group by Order_number,Order_date
order by Order_number desc
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:
Good experience and Knowledge with Building the database here we create a primary keys and
foreign key. 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.