ISY1002 Database Project: Australian Manufacturing Company

This document presents a comprehensive database project developed for the Australian Manufacturing Company (AMC). It begins with a background on the project's requirements, mission statement, and the selection of phpMyAdmin MySQL on an XAMPP server for database development. The project includes an Entity-Relationship Diagram (ERD) illustrating the database structure, followed by a normalized schema for efficient data organization. The database schema and foreign key constraints are detailed, ensuring data integrity. A series of SQL statements are provided, demonstrating the querying of the database to retrieve specific information, such as customer details, product information, and order data. The report contains 11 SQL queries with their outputs. The project also includes a group report section discussing the challenges faced during development and the solutions implemented. Finally, the document concludes with a bibliography of the resources used.

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 required for the implementation of the
database are described below:
 The details of the patrons are to be stored in the system with
the customer number being unique attribute which is to be used
for the reference of the customer data.
 The details of the departments are to be stored in the
database.
 The names of the stores and their addresses and fax are to be
stored in the system identified uniquely by their store
number.
 The details of the products and their prices are to be stored
in the system.
 The details of the orders made by the customers in the system
and employees handling the orders are required to be stored in
the system.
Mission statement
The system would be helpful for the organization in
maintaining their data also provide the organization with efficient
data storing options that would be increasing the business
efficiencies of the organization.
DBMS Selection
The Organization has chosen phpmyadmin mysql available on the
xampp server to develop their database that would be supporting
their backend process for the development of the system.

3
DATABASE MANAGEMENT SYSTEM
ERD
Normalised Schema
Department (DepartmentNumber (pk), DepartmentName)
Store (StoreNumber (pk), StoreName, Address, Fax)
Employee (EmployeeNumber (pk), LastName, FirstName, StoreNumber
(fk), DepartmentNumber (fk))
Products (ProductNumber (pk), ProductTitle, UnitPrice)
Orders (OrderNumber (pk), OrderQuantity, ProductNumber, OrderDate,
CustomerNumber (fk), EmployeeNumber (fk))
Payroll (PayrollID (pk), EmployeeID (fk), Amount, PaymentDate)

4
DATABASE MANAGEMENT SYSTEM
Database schema
Foreign Key constraints
Foreign keys for payroll table
Foreign keys for Employee Table

5
DATABASE MANAGEMENT SYSTEM
Foreign key for the Orders table
SQL Statements
Query 1
SQL
Query:
Select CONCAT(LastName, " " ,FirstName) As 'Customer
Fullname', PhoneNumber, Address
From Patron
Order by CustomerNumber DESC;

6
DATABASE MANAGEMENT SYSTEM
Output: +-------------------+-------------+--------------------
+
| Customer Fullname | PhoneNumber | Address
|
+-------------------+-------------+--------------------
+
| Peterson Adam | 1278917 | 66 Henry Street
|
| Williams Farrel | 1278122 | 54 Jhonston street
|
| Torres Lanny | 1236252 | 45 Derreck Avenue
|
| Lane Sammy | 1243398 | 45 Denever Lane
|
+-------------------+-------------+--------------------
+
Query 2
SQL
Query:
SELECT * FROM product where unitprice < 100;
Output: +---------------+----------------+-----------+
| ProductNumber | ProductTitle | UnitPrice |
+---------------+----------------+-----------+
| 1 | T-Shirt | 20 |
| 2 | Mobile Charger | 23 |
| 3 | Hair Dryer | 21 |
+---------------+----------------+-----------+

7
DATABASE MANAGEMENT SYSTEM
Query 3
SQL
Query:
SELECT product.ProductNumber, product.ProductTitle,
orders.QuantityOrdered, product.UnitPrice,
(orders.QuantityOrdered*product.UnitPrice) As 'Total
Amount' FROM product
INNER JOIN orders On product.ProductNumber =
orders.ProductNumber
WHERE orders.OrderNumber = 1005;
Output:
Query 4
SQL
Query:
SELECT * FROM store
WHERE address LIKE "%george%"
Output: +---------------+----------------+-----------------
+-----------+--------------+
| ProductNumber | ProductTitle | QuantityOrdered |
UnitPrice | Total Amount |
+---------------+----------------+-----------------
+-----------+--------------+
| 2 | Mobile Charger | 2 |
23 | 46 |
+---------------+----------------+-----------------
+-----------+--------------+
1 row in set (0.05 sec)

8
DATABASE MANAGEMENT SYSTEM
Query 5
SQL
Query:
SELECT * FROM orders where orders.QuantityOrdered > 2;
Output: +-------------+-----------------+---------------
+------------+----------------+----------------+
| OrderNumber | QuantityOrdered | ProductNumber |
OrderDate | CustomerNumber | EmployeeNumber |
+-------------+-----------------+---------------
+------------+----------------+----------------+
| 1002 | 3 | 2 | 2017-
11-17 | 202 | 4006 |
| 1003 | 5 | 1 | 2017-
10-19 | 204 | 4006 |
+-------------+-----------------+---------------
+------------+----------------+----------------+
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 |
+----------------+----------+-----------+-------------

9
DATABASE MANAGEMENT SYSTEM
+-----------------+
| 201 | Lane | Sammy | 1243398 |
45 Denever Lane |
+----------------+----------+-----------+-------------
+-----------------+
1 row in set (0.00 sec)
Query 7
SQL
Query:
SELECT employee.lastname, employee.FirstName,
employee.EmployeeNumber, store.Storename,
department.DepartmentName
From employee
INNER JOIN store on employee.StoreNumber =
store.StoreNumber
INNER JOIN department on department.DepartmentNumber =
employee.DepartmentNumber;
Output: +----------+-----------+----------------
+----------------+----------------+
| lastname | FirstName | EmployeeNumber | Storename
| DepartmentName |
+----------+-----------+----------------
+----------------+----------------+
| Fedricks | Rayn | 4001 | Derrek Stores
| Accounts |
| Storm | Hailey | 4002 | Derrek Stores
| Human Resource |
| Knowles | Darren | 4003 | General

10
DATABASE MANAGEMENT SYSTEM
Stores | Accounts |
| Owens | Betty | 4004 | David Stores
| Accounts |
| Phillips | Lewis | 4005 | General
Stores | Sales |
| Steelers | Harvey | 4006 | David Stores
| Sales |
+----------+-----------+----------------
+----------------+----------------+
6 rows in set (0.05 sec)
Query 8
SQL
Query:
SELECT store.storename, COUNT(employee.EmployeeNumber)
FROM store INNER JOIN employee on store.StoreNumber =
employee.StoreNumber INNER JOIN department on
department.DepartmentNumber = employee.DepartmentNumber
Where department.DepartmentName = 'Accounts'
GROUP BY store.StoreNumber;
Output: +----------------+--------------------------------+
| storename | COUNT(employee.EmployeeNumber) |
+----------------+--------------------------------+
| General Stores | 1 |
| David Stores | 1 |
| Derrek Stores | 1 |
+----------------+--------------------------------+
Query 9

11
DATABASE MANAGEMENT SYSTEM
SQL
Query:
SELECT * FROM orders where orders.OrderDate > '2017-06-
30' And orders.OrderDate > '2017-06-30';
Output: +-------------+-----------------+---------------
+------------+----------------+----------------+
| OrderNumber | QuantityOrdered | ProductNumber |
OrderDate | CustomerNumber | EmployeeNumber |
+-------------+-----------------+---------------
+------------+----------------+----------------+
| 1002 | 3 | 2 | 2017-
11-17 | 202 | 4006 |
| 1003 | 5 | 1 | 2017-
10-19 | 204 | 4006 |
| 1004 | 1 | 3 | 2017-
10-19 | 203 | 4005 |
| 1005 | 2 | 2 | 2017-
11-16 | 202 | 4005 |
| 1006 | 1 | 2 | 2018-
03-15 | 204 | 4006 |
+-------------+-----------------+---------------
+------------+----------------+----------------+
Query 10
SQL
Query:
SELECT `CustomerNumber`, COUNT(`OrderNumber`) FROM
`orders`
GROUP BY `CustomerNumber`;
Output: +----------------+----------------------+
| CustomerNumber | COUNT(`OrderNumber`) |

12
DATABASE MANAGEMENT SYSTEM
+----------------+----------------------+
| 202 | 2 |
| 203 | 1 |
| 204 | 2 |
+----------------+----------------------+
3 rows in set (0.00 sec)
Query 11
SQL
Query:
SELECT `OrderNumber`,`OrderDate`,`QuantityOrdered` FROM
`orders`
ORDER BY OrderNumber DESC;
Output: +-------------+------------+-----------------+
| OrderNumber | OrderDate | QuantityOrdered |
+-------------+------------+-----------------+
| 1006 | 2018-03-15 | 1 |
| 1005 | 2017-11-16 | 2 |
| 1004 | 2017-10-19 | 1 |
| 1003 | 2017-10-19 | 5 |
| 1002 | 2017-11-17 | 3 |
+-------------+------------+-----------------+
5 rows in set (0.00 sec)
Group report
From the personal point of view, it can be said that the AMC
organization is in an urgent need to resolve the issues that they
are facing with the current business procedures. The organization
has various branches across of Australia and it becomes very
difficult for the organization to maintain their business process.

13
DATABASE MANAGEMENT SYSTEM
Therefore, the development of the database would be very useful for
the organization in improving their business strategy.
Challenges and solution
The major challenges faced was during the development of the
data and working in the command prompt interface. The main solution
obtained to this problem is that the ER diagram was developed after
reading the case study thorough. The Development of the ER diagram
was then done with the help of the MS Visio Tool. In addition to
this, the normalization of the tables to be included in the database
were performed. Hence, a clear view of the database that was to be
developed was obtained. Hence, the solution was obtained.
In addition to this, the YouTube videos were referred for
working on the MySql command prompt interface. The videos helped a
great deal in developing the queries on the command prompt
interface. Hence, the problem was solved.

14
DATABASE MANAGEMENT SYSTEM
Bibliography
Coronel, C. and Morris, S., 2016. Database systems: design,
implementation, & management. Cengage Learning.
Hilles, M.M. and Naser, S.S.A., 2017. Knowledge-based Intelligent
Tutoring System for Teaching Mongo Database.
Nascimento, L.M.E.D., Ferreira, A.C.D.M. and Gonzalez, S.D.A., 2018,
March. Design and development of a geo-referenced database to
radionuclides in food. In Journal of Physics: Conference Series
(Vol. 975, No. 1, p. 012045). IOP Publishing.