Database Management System

This document provides an overview of database management system, including its requirements, mission statement, DBMS selection, ERD, normalized schema, database schema, foreign key constraints, and SQL statements. It also includes a group report on the challenges faced and their solutions. The document is relevant for students studying database management systems.

Running Head: DATABASE MANAGEMENT SYSTEM
Title:
Database Management System
Student ID:
Student Name:

1
DATABASE MANAGEMENT SYSTEM
Table of Contents
Background.........................................................2
Requirements.....................................................2
Mission statement................................................2
DBMS Selection...................................................2
ERD................................................................3
Normalised Schema..................................................3
Database schema....................................................4
Foreign Key constraints............................................4
SQL Statements.....................................................5
Group report......................................................12
Challenges and solution...........................................12
Bibliography......................................................14

2
DATABASE MANAGEMENT SYSTEM
Background
Requirements
The business rules that are required in order to implement the
database are as follows:
 The details which concern the patrons need to be recorded in
the system along with the number of the customer that is a
unique attribute to be used in case of the reference regarding
the customer data.
 The details concerning the departments need to be recorded in
the system’s database.
 The name of various stores and their respective addresses and
fax need to be recorded in the system that can be identified
by their unique store numbers.
 The product details and their respective prices need to be
recorded in the system’s database.
 The order details that have been made by the consumers in the
system and the employees those who are handling the orders are
needed to be kept a record of in the database of the system.
Mission statement
The system would effectively aid the organization in the process of
maintaining data and at the same time provide the firm with data
storing options that would be effective enough to increase the
efficiencies of the firm in conducting the business.

3
DATABASE MANAGEMENT SYSTEM
DBMS Selection
ERD
Normalised Schema
Department (DepartmentNumber (pk), DepartmentName, Address,
PhoneNumber, Supervisor (fk))

4
DATABASE MANAGEMENT SYSTEM
Store (StoreNumber (pk), StoreName, Address, Fax, Suburb, State,
postcode, StoreManager (fk))
Employee (EmployeeNumber (pk), LastName, FirstName, StoreNumber
(fk), DepartmentNumber (fk), Email, TFN, JoiningDate, Salary)
Products (ProductNumber (pk), ProductTitle, UnitPrice, Brand
Description)
Orders (OrderNumber (pk), OrderQuantity, ProductNumber (fk),
OrderDate, CustomerNumber (fk), EmployeeNumber (fk)
Payslip (PayslipNumber(pk), EmployeeID (fk), NoOfHoursWorked,
GrossPay)
Database schema
Foreign Key constraints
Foreign keys for Employee Table

5
DATABASE MANAGEMENT SYSTEM
Foreign key for the Orders table
Foreign key for the payslip table

6
DATABASE MANAGEMENT SYSTEM
SQL Statements
Query 1
SQL
Query:
Select CONCAT(LastName, " " ,FirstName) As 'Customer
Fullname', PhoneNumber, Address
From Patron
Order by CustomerNumber DESC;
Output: +-------------------+-------------+--------------------
+
| Customer Fullname | PhoneNumber | Address
|
+-------------------+-------------+--------------------
+
| Stevens Gary | 25681532 | 56 Bentinct Street
|
| Levy Richard | 25681022 | 16 leeway street
|
| Prince Kevin | 25685483 | 56 Dennis avenue

7
DATABASE MANAGEMENT SYSTEM
|
+-------------------+-------------+--------------------
+
3 rows in set (0.03 sec)
Query 2
SQL
Query:
SELECT * FROM product where unitprice < 100;
Output: +---------------+---------------+-----------+----------
+-------------------------+
| ProductNumber | ProductTitle | UnitPrice | Brand
| Description |
+---------------+---------------+-----------+----------
+-------------------------+
| 1 | gents t-shirt | 34 | Levis
| black t-shirt |
| 2 | Gents denims | 42 | Wrangler
| blue denims |
| 3 | Sunglasses | 56 | Rayban
| Black Rayban sunglasses |
+---------------+---------------+-----------+----------
+-------------------------+
3 rows in set (0.00 sec)
Query 3
SQL
Query:
SELECT product.ProductNumber, product.ProductTitle,
orders.QuantityOrdered, product.UnitPrice,

8
DATABASE MANAGEMENT SYSTEM
(orders.QuantityOrdered*product.UnitPrice) As 'Total
Amount' FROM product
INNER JOIN orders On product.ProductNumber =
orders.ProductNumber
WHERE orders.OrderNumber = 1005;
Output: +---------------+--------------+-----------------
+-----------+--------------+
| ProductNumber | ProductTitle | QuantityOrdered |
UnitPrice | Total Amount |
+---------------+--------------+-----------------
+-----------+--------------+
| 3 | Sunglasses | 2 |
56 | 112 |
+---------------+--------------+-----------------
+-----------+--------------+
1 row in set (0.06 sec)
Query 4
SQL
Query:
SELECT * FROM store
WHERE address LIKE "%george%"
Output: +-------------+---------------+----------------------
+--------+-------------+--------+-------+----------
+--------------+
| StoreNumber | Storename | Address |
Fax | PhoneNumber | Suburb | State | PostCode |
StoreManager |
+-------------+---------------+----------------------

9
DATABASE MANAGEMENT SYSTEM
+--------+-------------+--------+-------+----------
+--------------+
| 101 | Peter Stores | 53 Georgewell Street |
23-001 | 25683130 | Sydney | NSW | 1230 |
Casey Hemmie |
| 102 | Benson Stores | 67 Georgepool Road |
67-890 | 25315689 | Sydney | NSW | 0221 |
Stuart Law |
| 103 | Kevin Stores | 66 George Street |
63-897 | 25681473 | Sydney | NSW | 7865 | Rory
Wilson |
+-------------+---------------+----------------------
+--------+-------------+--------+-------+----------
+--------------+
3 rows in set (0.00 sec)
Query 5
SQL
Query:
SELECT * FROM orders where orders.QuantityOrdered > 2;
Output: +-------------+-----------------+---------------
+------------+----------------+----------------+
| OrderNumber | QuantityOrdered | ProductNumber |
OrderDate | CustomerNumber | EmployeeNumber |
+-------------+-----------------+---------------
+------------+----------------+----------------+
| 1006 | 3 | 3 | 2019-
03-12 | 3 | 3 |

10
DATABASE MANAGEMENT SYSTEM
+-------------+-----------------+---------------
+------------+----------------+----------------+
1 row in set (0.00 sec)
Query 6
SQL
Query:
SELECT patron.* from patron
LEFT OUTER JOIN orders On orders.CustomerNumber =
patron.customerNumber
Where orders.OrderNumber IS NULL;
Output: +----------------+----------+-----------+-------------
+------------------+
| CustomerNumber | LastName | FirstName | PhoneNumber |
Address |
+----------------+----------+-----------+-------------
+------------------+
| 2 | Levy | Richard | 25681022 |
16 leeway street |
+----------------+----------+-----------+-------------
+------------------+
1 row in set (0.00 sec)
Query 7
SQL
Query:
SELECT employee.lastname, employee.FirstName,
employee.EmployeeNumber, store.Storename,
department.DepartmentTitle
From employee
INNER JOIN store on employee.StoreNumber =

11
DATABASE MANAGEMENT SYSTEM
store.StoreNumber
INNER JOIN department on department.DepartmentNumber =
employee.DepartmentNumber;
Output: +----------+-----------+----------------+--------------
+-----------------+
| lastname | FirstName | EmployeeNumber | Storename
| DepartmentTitle |
+----------+-----------+----------------+--------------
+-----------------+
| Stuarts | David | 1 | Kevin Stores
| Accounts |
| Stuarts | Andy | 2 | Peter Stores
| HR |
| Parker | Mary | 3 | Kevin Stores
| Sales |
| Hemmie | Casey | 4 | Peter Stores
| HR |
| Law | Stuart | 5 | Kevin Stores
| Sales |
| Wilson | Rory | 6 | Kevin Stores
| Accounts |
+----------+-----------+----------------+--------------
+-----------------+
6 rows in set (0.04 sec)
Query 8
SQL SELECT store.storename, COUNT(employee.EmployeeNumber)

12
DATABASE MANAGEMENT SYSTEM
Query: FROM store INNER JOIN employee on store.StoreNumber =
employee.StoreNumber INNER JOIN department on
department.DepartmentNumber = employee.DepartmentNumber
Where department.DepartmentTitle = 'Accounts'
GROUP BY store.StoreNumber;
Output: +--------------+--------------------------------+
| storename | COUNT(employee.EmployeeNumber) |
+--------------+--------------------------------+
| Kevin Stores | 2 |
+--------------+--------------------------------+
1 row in set (0.00 sec)
Query 9
SQL
Query:
SELECT * FROM orders where orders.OrderDate > '2017-06-
30' And orders.OrderDate > '2017-06-30';
Output: +-------------+-----------------+---------------
+------------+----------------+----------------+
| OrderNumber | QuantityOrdered | ProductNumber |
OrderDate | CustomerNumber | EmployeeNumber |
+-------------+-----------------+---------------
+------------+----------------+----------------+
| 1003 | 1 | 1 | 2019-
03-11 | 1 | 5 |
| 1004 | 1 | 2 | 2019-
03-13 | 3 | 2 |
| 1005 | 2 | 3 | 2019-
02-12 | 1 | 6 |

13
DATABASE MANAGEMENT SYSTEM
| 1006 | 3 | 3 | 2019-
03-12 | 3 | 3 |
+-------------+-----------------+---------------
+------------+----------------+----------------+
4 rows in set (0.00 sec)
Query 10
SQL
Query:
SELECT `CustomerNumber`, COUNT(`OrderNumber`) FROM
`orders`
GROUP BY `CustomerNumber`;
Output: +----------------+----------------------+
| CustomerNumber | COUNT(`OrderNumber`) |
+----------------+----------------------+
| 1 | 2 |
| 3 | 2 |
+----------------+----------------------+
2 rows in set (0.06 sec)
Query 11
SQL
Query:
SELECT `OrderNumber`,`OrderDate`,`QuantityOrdered` FROM
`orders`
ORDER BY OrderNumber DESC;
Output: +-------------+------------+-----------------+
| OrderNumber | OrderDate | QuantityOrdered |
+-------------+------------+-----------------+
| 1006 | 2019-03-12 | 3 |

14
DATABASE MANAGEMENT SYSTEM
| 1005 | 2019-02-12 | 2 |
| 1004 | 2019-03-13 | 1 |
| 1003 | 2019-03-11 | 1 |
+-------------+------------+-----------------+
4 rows in set (0.00 sec)
Group report
On the basis of the personal viewpoint, it can be stated that AMC
organization urgently requires solving the various issues being
faced by them at present regarding the procedures of the business.
As the firm has numerous branches in and around Australia thus it
poses certain difficulties to the firm in the process of carrying
out and maintaining the process involved in their business. Hence,
the creation of database will be beneficial for the organization in
the process of improving the various strategies that are related to
their business.
Challenges and solution
The major challenges that were faced during the process of creation
of database are the data involved and the related working in the
command prompt interface. The primary solution obtained with
reference to the problem above is the development of the ER diagram
after analyzing the case study properly. The ER diagram was
developed by MS Visio Tool. Added to this, the tables that were to
be a part of the database were normalized thereby providing a lucid
view of the database that was getting developed. Thus in this way
the required solution was developed. The YouTube videos served as a
reference to work on MySql command prompt interface. These videos in

15
DATABASE MANAGEMENT SYSTEM
turn wee of great help in the process of developing the queries
related to the command prompt interface thereby solving the problem.

16
DATABASE MANAGEMENT SYSTEM
Bibliography
Hilles, M.M. and Naser, S.S.A., 2017. Knowledge-based Intelligent
Tutoring System for Teaching Mongo Database.