ISY1002 Database Management Project: Design, SQL, and Normalization

This assignment presents a comprehensive database project developed for the Australian Manufacturing Company (AMC). It begins with an Entity Relationship Diagram (ERD) identifying key entities such as Departments, Products, Stores, Employees, Patrons, and Orders, along with their respective attributes. The solution then addresses database normalization, providing a normalized schema for efficient data management. A relational schema is provided, detailing attributes, data types, sizes, and primary/foreign key designations for each table. The core of the assignment involves SQL queries, demonstrating the ability to retrieve, filter, and manipulate data within the database. Ten SQL queries are provided, addressing various data retrieval and manipulation tasks, from simple sorting and filtering to more complex joins and aggregations. Finally, a personal report details the development process, challenges faced, and resources used, including references to relevant literature. The project was developed on the Xampp server, with the most challenging aspect being the creation of table references. This assignment provides a practical demonstration of database design, implementation, and querying using SQL.

Running head: DATABASE MANAGEMENT
Database Management
Name of the Student:
Name of the University:
Author Note

1
DATABASE MANAGEMENT
Part A: Entity Relationship Diagram
The main entities and attributes identified in the system are:
Department (DeptID, DeptName)
Products (ProductNumber, ProductTitle, UnitPrice)
Stores (StoreID, Name, Address, FaxNumber)
Employees (EmployeeID, LastName, FirstName StoreID, DeptID)
Patrons (CustomerID, LastName, FirstName, PhoneNumber, Address)
Orders (OrderNumber, OrderDate, NumberOfProducts, CustomerID, EmployeeID)
ProductsOrdered (ID, ProductNumber, OrderNumber, QunatityOrdered)

2
DATABASE MANAGEMENT
Part B: Normalization
The normalized schema for the database has been provided below:
Department (DeptID (pk), DeptName)
Products (ProductNumber (pk), ProductTitle, UnitPrice)
Stores (StoreID (pk), Name, Address, FaxNumber)
Employees (EmployeeID (pk), LastName, FirstName StoreID (fk), DeptID (fk))
Patrons (CustomerID (pk), LastName, FirstName, PhoneNumber, Address)
Orders (OrderNumber (pk), OrderDate, NumberOfProducts, CustomerID (fk), EmployeeID (fk))
ProductsOrdered (ID (pk), ProductNumber (fk), OrderNumber (fk), QunatityOrdered)

3
DATABASE MANAGEMENT
Part C: Relational schema and MySQL database
Department
Attribute Datatype Size Key
DeptID Int Primary
DeptName Varchar 30
Products
Attribute Datatype Size Key
ProductNumber Int Primary
ProductTitle Varchar 30
UnitPrice Int
Stores

4
DATABASE MANAGEMENT
Attribute Datatype Size Key
StoreID Int Primary
Name Varchar 30
Address Varchar 30
FaxNumber Int
Employees
Attribute Datatype Size Key
EmployeeID Int Primary
LastName Varchar 30
FirstName Varchar 30
StoreID Int Foreign
DeptID Int Foreign
Patrons
Attribute Datatype Size Key
CustomerID Int Primary
LastName Varchar 30
FirstName Varchar 30
PhoneNumber Int Foreign
Address Varchar 50 Foreign

5
DATABASE MANAGEMENT
Orders
Attribute Datatype Size Key
OrderNumber Int Primary
Orderdate Date
NumberOfProducts Int
CustomerID Int Foreign
EmployeeID Int Foreign
ProductsOrdered
Attribute Datatype Size Key
ID Int Primary
ProductNumber Int Foreign
OrderNumber int Foreign
QuantityOrdered Int
Part D: SQL
Question 1:
SQL Select * From Patron Order by CutomerID DESC;
Output +-----------+----------+-----------+-------------+----------------+
| CutomerID | LastName | FirstName | PhoneNumber | Address |
+-----------+----------+-----------+-------------+----------------+

6
DATABASE MANAGEMENT
| 3 | Perry | Wyane | 5646565 | 88 CD Rd |
| 2 | Harry | Miller | 4565258 | 90 Will Street |
| 1 | Jerry | Bean | 5646566 | 45 Derik side |
+-----------+----------+-----------+-------------+----------------+
3 rows in set (0.000 sec)
Question 2:
SQL Select * from product where UnitPrice < 100;
Output +---------------+------------------+-----------+
| ProductNumber | ProductTitle | UnitPrice |
+---------------+------------------+-----------+
| 1 | Avengers T-Shirt | 45 |
| 2 | Pokemon T-shirt | 50 |
| 3 | Cricket T-Shirt | 35 |
+---------------+------------------+-----------+
3 rows in set (0.000 sec)
Question 3:
SQL Select Product.ProductNumber, Product.ProductTitle, Product.UnitPrice,
(Product.UnitPrice*ProductOrder.QuantityOrdered) From Product Inner Join
ProductOrder On Product.ProductNumber = ProductOrder.Productnumber;
Output +---------------+------------------+-----------+--------------------------------------------------

7
DATABASE MANAGEMENT
+
| ProductNumber | ProductTitle | UnitPrice |
(Product.UnitPrice*ProductOrder.QuantityOrdered) |
+---------------+------------------+-----------+--------------------------------------------------
+
| 3 | Cricket T-Shirt | 35 | 105 |
| 3 | Cricket T-Shirt | 35 | 70 |
| 1 | Avengers T-Shirt | 45 | 135 |
| 3 | Cricket T-Shirt | 35 | 70 |
+---------------+------------------+-----------+--------------------------------------------------
+
4 rows in set (0.000 sec)
Question 4.
SQL Select * From Store where Address LIKE '%george%';
Output +---------+-------------+------------------+-----------+
| StoreID | Name | Address | FaxNumber |
+---------+-------------+------------------+-----------+
| 1 | Car Digital | 65 George Street | 55656 |
| 2 | Fire Cars | 94 George Avenue | 55626 |
+---------+-------------+------------------+-----------+
2 rows in set (0.000 sec)

8
DATABASE MANAGEMENT
Question 5.
SQL Select * From Orders Where NumberOfProducts > 1;
Output +---------+------------+------------------+------------+------------+
| orderID | OrderDate | NumberOfProducts | CustomerID | EmployeeID |
+---------+------------+------------------+------------+------------+
| 1 | 2020-01-08 | 4 | 1 | 1 |
| 2 | 2020-01-14 | 3 | 1 | 3 |
| 3 | 2020-01-07 | 2 | 2 | 3 |
| 4 | 2020-01-08 | 2 | 1 | 4 |
| 5 | 2020-01-15 | 3 | 2 | 4 |
+---------+------------+------------------+------------+------------+
5 rows in set (0.000 sec)
Question 6.
SQL Select * From Patron Where CutomerID NOT IN (Select CustomerID From
Orders);
Output +-----------+----------+-----------+-------------+----------+
| CutomerID | LastName | FirstName | PhoneNumber | Address |
+-----------+----------+-----------+-------------+----------+
| 3 | Perry | Wyane | 5646565 | 88 CD Rd |
+-----------+----------+-----------+-------------+----------+
1 row in set (0.012 sec)

9
DATABASE MANAGEMENT
Question 7.
SQL Select Employees.*,Store.Name, Department.DeptName From Employees Inner
Join Store On Employees.StoreID = Store.StoreID Inner Join Department On
Department.DeptID = Employees.DeptID;
Output +------------+----------+-----------+---------+--------+---------------+-----------+
| EmployeeID | LastName | FirstName | StoreID | DeptID | Name | DeptName |
+------------+----------+-----------+---------+--------+---------------+-----------+
| 1 | Davis | Ben | 1 | 2 | Car Digital | Account |
| 2 | Kevin | Spears | 3 | 2 | Cars Republic | Account |
| 3 | Davey | James | 2 | 1 | Fire Cars | Marketing |
| 4 | Harry | Williams | 3 | 2 | Cars Republic | Account |
+------------+----------+-----------+---------+--------+---------------+-----------+
4 rows in set (0.000 sec)
Question 8.
SQL Select Store.Name, COUNT(Employees.EmployeeID) From Employees Inner Join
Store On Store.StoreID = Employees.StoreID Order by Store.Name;
Output +-------------+-----------------------------+
| Name | COUNT(Employees.EmployeeID) |
+-------------+-----------------------------+
| Car Digital | 4 |
+-------------+-----------------------------+

10
DATABASE MANAGEMENT
1 row in set (0.000 sec)
Question 9.
SQL Select * From Orders Where OrderDate > '2017-12-31' and OrderDate < '2018-12-
31';
Output +---------+------------+------------------+------------+------------+
| orderID | OrderDate | NumberOfProducts | CustomerID | EmployeeID |
+---------+------------+------------------+------------+------------+
| 1 | 2018-01-08 | 4 | 1 | 1 |
| 2 | 2018-01-14 | 3 | 1 | 3 |
| 3 | 2018-01-07 | 2 | 2 | 3 |
| 4 | 2018-01-08 | 2 | 1 | 4 |
| 5 | 2018-01-15 | 3 | 2 | 4 |
+---------+------------+------------------+------------+------------+
5 rows in set (0.008 sec)
Question 10.
SQL Select CONCAT(Patron.LastName, " ", Patron.FirstName),
COUNT(Orders.OrderID) From Patron Inner join Orders On Patron.CutomerID =
Orders.CustomerID Group By Patron.LastName, Patron.FirstName;
Output +------------------------------------------------+-----------------------+
| CONCAT(Patron.LastName, " ", Patron.FirstName) | COUNT(Orders.OrderID) |

11
DATABASE MANAGEMENT
+------------------------------------------------+-----------------------+
| Harry Miller | 2 |
| Jerry Bean | 3 |
+------------------------------------------------+-----------------------+
2 rows in set (0.001 sec)
Question 11.
SQL Select * From Orders Order By OrderID DESC;
Output +---------+------------+------------------+------------+------------+
| orderID | OrderDate | NumberOfProducts | CustomerID | EmployeeID |
+---------+------------+------------------+------------+------------+
| 5 | 2018-01-15 | 3 | 2 | 4 |
| 4 | 2018-01-08 | 2 | 1 | 4 |
| 3 | 2018-01-07 | 2 | 2 | 3 |
| 2 | 2018-01-14 | 3 | 1 | 3 |
| 1 | 2018-01-08 | 4 | 1 | 1 |
+---------+------------+------------------+------------+------------+
5 rows in set (0.000 sec)