COIT20247 Database Design and Development: Normalization Report 2019

This report presents a comprehensive analysis of database normalization, focusing on Third Normal Form (3NF) relations within the context of a COIT20247 Database Design and Development assignment. The report begins by mapping an ERD into a set of 3NF relations, ensuring that the database design adheres to normalization principles. It then delves into functional dependencies within the TblInvoice and TblCustomer tables, demonstrating how each relation meets the criteria for 3NF. The implementation utilizes MS Access and includes a discussion of data integrity constraints for parcel attributes. Furthermore, the report provides a series of database queries designed to extract specific information from the database, such as the status of parcels and the number of unclaimed lost parcels. The report also implements database reports and discusses its interesting features and difficult tasks. The report concludes by referencing the sources used and provides a detailed overview of the database design and development process, which is crucial for students seeking to understand database normalization and its practical application.

[coit20247 database design and development (t12019)]
Student ID:
Student Name:
2019

Report
Database Normalization
3NF Relations
TblCustomer (FldCustomerID, FldCustomerName, FldStreet, FldCity, FldPostCode,
FldCustomerContactNum)
Primary Key FldCustomerID
TblEmployee (FldEmployeeID, FldEmployeeName, FldEmployeeStartDate)
Primary Key FldEmployeeID
TblParcel (FldParcelID, FldParcelWeightInGrams, FldFromStreet, FldFromCity,
FldFromPostCode, FldToStreet, FldToCity, FldToPostCode, FldDateCollected, FldParcelValue,
FldCharges, FldStatus, FldCustomerID, FldEmployeeID)
Primary Key ParcelID
FldCustomerID references TblCustomer (FldCustomerID)
FldEmployeeID references TblEmployee (FldEmployeeID)
TblDelivered (FldParcelID, FldDeliveredDate)
Primary Key FldParcelID
FldParcelID references TblParcel (FldParcelID)
TblReturned (FldParcelID, FldReturnedDate)
Primary Key FldParcelID
FldParcelID references TblParcel (FldParcelID)
TblLost (FldParcelID, FldLostEntryDate)
Primary Key FldParcelID
FldParcelID references TblParcel (FldParcelID)
TblClaim (FldClaimID, FldClaimLodgeDate, FldClaimSettledAmount, FldClaimPaidDate,
FldParcelID)
Primary Key FldClaimID
FldParcelID references TblLost (FldParcelID)
TblInvoice (FldInvoiceID, FldInvoiceAmount, FldInvoiceDate, FldParcelID)

Primary Key FldInvoiceID (Guru99 2016)
FldParcelID references TblParcel (FldParcelID)
Functional Dependency
Invoice Table
FldInvoiceAmount  FldInvoiceID
FldInvoiceDate  FldInvoiceID
FldParcelID  FldInvoiceID
Customer Table
FldCustomerName  FldCustomerID
FldStreet  FldCustomerID
FldCity  FldCustomerID
FldPostCode  FldCustomerID
FldCustomerContactNum  FldCustomerID

3 NF Demonstration
TblInvoice (FldInvoiceID, FldInvoiceAmount, FldInvoiceDate, FldParcelID)
First Normal Form
There is no repeated group in the table as there is only amount, one invoice date, one parcel id
for one invoice. Therefore, invoice table is in first normal form.
Second Normal Form
There is no partial dependency in the table because InvoiceID is the only primary key that is
determining other fields. It is single primary key. Therefore, there is no scope of partial
dependency. Therefore, invoice table is in second normal form.
Third Normal Form
No other attribute except Invoice ID (primary key) can determine another attributes.Therefore,
invoice table is in third normal form.
TblCustomer (FldCustomerID, FldCustomerName, FldStreet, FldCity, FldPostCode,
FldCustomerContactNum)
First Normal Form
There is no repeated group in the table as there is only amount, one invoice date, one parcel id
for one invoice. Therefore, invoice table is in first normal form.
Second Normal Form
There is no partial dependency in the table because InvoiceID is the only primary key that is
determining other fields. It is single primary key. Therefore, there is no scope of partial
dependency. Therefore, invoice table is in second normal form.
Third Normal Form
No other attribute except Invoice ID (primary key) can determine another attributes.Therefore,
invoice table is in third normal form.

Relational Database Implementation
ER Diagram Mapping
(Access Programmers n.d.)
Data Integrity
Parcel Table
Attribute Data type Integrity Constraint
Implemented
Error Message
FldStatus Text "Collected" Or
"Returned" Or "Lost"
Or "Delivered"
The value should be
"Collected" Or
"Returned" Or "Lost"
Or "Delivered".
FldParcelValue Number >0 The value should be
greater than 0.
FldCharges Number >0 The value should be
greater than 0.
FldDateCollected Date/Time <=Date() The value should be
less than or equal to
the current date.

Database Queries
Query 1
SELECT TblParcel.Status, Count(TblParcel.ParcelID) AS 'Number of Parcels' FROM TblParcel
GROUP BY TblParcel.Status;
Query 2
SELECT count(ParcelID) AS 'Unclaimed Lost Parcels' FROM Tbllost WHERE ParcelID not in
(Select ParcelID from TblClaim);
Query 3
SELECT TOP 1 TblCustomer.CustomerID, TblCustomer.CustomerName, TblCustomer.Street,
TblCustomer.City, TblCustomer.PostCode, TblCustomer.CustomerContactNum,
sum(TblParcel.Charges) FROM TblCustomer INNER JOIN TblParcel ON
TblCustomer.CustomerID=TblParcel.CustomerID GROUP BY TblCustomer.CustomerID,
TblCustomer.CustomerName, TblCustomer.Street, TblCustomer.City, TblCustomer.PostCode,
TblCustomer.CustomerContactNum ORDER BY sum(TblParcel.Charges) DESC;

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

Database Report
Implementation Report
Interesting Features
The assignment has so many interesting features like database queries, reports, normalization etc.
The most interesting and informative features are-
1. Database reports
2. Database normalization
Difficult Task
The assignment has some difficult tasks also which needs extreme knowledge and giving great
information to the developer. I found some difficulties in database query using inner joins but
later I resolved the issue.

References
Guru99 (2016). Learn Database Normalization with the help of a case study. Retrieved From:
http://www.guru99.com/database-normalization.html
Access Programmers (n.d.). Normalizing the Table Design. Retrieved From:
https://www.access-programmers.com/normalizing-the-table-design.aspx