Database Normalization and SQL Queries Assignment COIT20247, May 2019

This document presents a comprehensive solution to a database design and development assignment, focusing on normalization, SQL queries, and report implementation. The solution begins by mapping an ERD to a set of relations in 3NF, providing functional dependencies and demonstrating 3NF compliance for two selected relations. It includes the creation of Customer, Employee, and Parcel tables. The solution details data integrity constraints for the Parcel table and provides SQL queries to retrieve specific data, such as the status of parcels, not-claimed parcels, customers with the highest charges, and employees with the number of parcels handled. Finally, it discusses the interesting and difficult tasks encountered during the assignment, particularly SQL subqueries and joins, and references relevant sources on database normalization.

COIT20247 Database Design and
Development
May 22
2019
Student ID:
Student Name:
Module Tutor:

Database Normalization
3NF Relations
Customer (CustomerID, CustomerName, Street, City, PostCode, CustomerContactNum)
Primary Key CustomerID
Employee (EmployeeID, EmployeeName, EmployeeStartDate)
Primary Key EmployeeID
Parcel (ParcelID, ParcelWeightInGrams, FromStreet, FromCity, FromPostCode, ToStreet,
ToCity, ToPostCode, DateCollected, ParcelValue, Charges, Status, CustomerID, EmployeeID)
Primary Key ParcelID
CustomerID references Customer (CustomerID)
EmployeeID references Employee (EmployeeID)
Delivered (ParcelID, DeliveredDate)
Primary Key ParcelID
ParcelID references Parcel (ParcelID)
Returned (ParcelID, ReturnedDate)
Primary Key ParcelID
ParcelID references Parcel (ParcelID)
Lost (ParcelID, LostEntryDate)
Primary Key ParcelID
ParcelID references Parcel (ParcelID)
Claim (ClaimID, ClaimLodgeDate, ClaimSettledAmount, ClaimPaidDate, ParcelID)
Primary Key ClaimID
ParcelID references Lost (ParcelID)
Invoice (InvoiceID, InvoiceAmount, InvoiceDate, ParcelID)
Primary Key InvoiceID
ParcelID references Parcel (ParcelID)

Functional Dependency
Customer Table
CustomerName  CustomerID
Street  CustomerID
City  CustomerID
PostCode  CustomerID
CustomerContactNum  CustomerID
Invoice Table
InvoiceAmount  InvoiceID
InvoiceDate  InvoiceID
ParcelID  InvoiceID

Demonstration of Data Normalization
Customer (CustomerID, CustomerName, Street, City, PostCode, CustomerContactNum)
1. The table Customer is not having any repeated group. So, it is satisfying the rule of 1 NF
and is in 1 NF.
2. The table Customer is having a primary key Customer ID and it is the only key that is
identifying all the fields. There is no partial dependency. So, it is satisfying the rule of 2
NF and is in 2 NF.
3. The table Customer is not having the partial dependency because the primary key
Customer ID is only identifying all the fields. So, it is satisfying the rule of 3 NF and is in
3 NF.
Invoice (InvoiceID, InvoiceAmount, InvoiceDate, ParcelID)
1. The table Invoice is not having any repeated group. So, it is satisfying the rule of 1 NF
and is in 1 NF.
2. The table Invoice is having a primary key Invoice ID and it is the only key that is
identifying all the fields. There is no partial dependency. So, it is satisfying the rule of 2
NF and is in 2 NF.
3. The table Invoice is not having the partial dependency because the primary key Invoice
ID is only identifying all the fields. So, it is satisfying the rule of 3 NF and is in 3 NF.

Relational Database Implementation
ER Diagram Mapping
Data Integrity
Parcel Table
Attribute Data type Integrity Constraint
Implemented
Error Message
Status Text "Collected" Or
"Returned" Or "Lost"
Or "Delivered"
The value should be
"Collected" Or
"Returned" Or "Lost"
Or "Delivered".
ParcelValue Number >0 The value should be
greater than 0.
Charges Number >0 The value should be
greater than 0.
DateCollected Date/Time <=Date() The value should be
less than or equal to
the current date.

Database Queries
Query 1
SELECT Parcel.Status, Count(Parcel.ParcelID) AS NumberOfParcels FROM Parcel GROUP
BY Parcel.Status;
Query 2
SELECT count(ParcelID) AS NotClaimedParcels FROM Lost WHERE ParcelID not in (Select
ParcelID from Claim);
Query 3
SELECT TOP 1 Customer.CustomerID, Customer.CustomerName, Customer.Street,
Customer.City, Customer.PostCode, Customer.CustomerContactNum, sum(Parcel.Charges) AS
ChargesTotal FROM Customer INNER JOIN Parcel ON
Customer.CustomerID=Parcel.CustomerID GROUP BY Customer.CustomerID,
Customer.CustomerName, Customer.Street, Customer.City, Customer.PostCode,
Customer.CustomerContactNum ORDER BY sum(Parcel.Charges) DESC;;

Query 4
SELECT Employee.EmployeeID, Employee.EmployeeName, Employee.EmployeeStartDate,
count(Parcel.ParcelID) AS NumberOfParcels
FROM Employee INNER JOIN Parcel ON Employee.EmployeeID=Parcel.EmployeeID
WHERE Employee.EmployeeID not in (Select EmployeeID from Parcel where Status='Lost')
GROUP BY Employee.EmployeeID, Employee.EmployeeName, Employee.EmployeeStartDate;
Query 5
SELECT *
FROM Employee
WHERE EmployeeName like '*Wheel*';

Database Report
Implementation Report
Interesting Tasks
I liked so many tasks in the assignment but the most interesting tasks are-
- SQL sub queries
- SQL report
Difficult Task
The most difficult task in the assignment is-
- SQL joins

References
SQL World (n.d.). Database Normalization with Examples. Retrieved From:
http://www.complexsql.com/database-normalization/
Datanamic Solutions (n.d.). Database Normalization. Retrieved From:
https://www.datanamic.com/support/database-normalization.html