ISY1002 Database Management System Project: Design & SQL

This document details a Database Management System project undertaken for the Australian Manufacturing Company (AMC), addressing their need for an effective data management system. The project encompasses the design and implementation of a relational database, including the creation of an Entity-Relationship Diagram (ERD), normalized schema, and database schema with foreign key constraints. SQL statements are provided for various queries, demonstrating data retrieval and manipulation. The report also outlines the challenges encountered during the database creation process, such as managing the data within the command prompt interface, and the solutions implemented, including the development of the ER diagram using MS Visio and normalization of the database tables. The project aims to improve AMC's data handling capabilities and business strategies by providing efficient data storage and retrieval options. The solution includes background information, requirements, mission statement, DBMS selection, SQL queries and outputs.

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)