COIT20247 Database Normalization and ERD Mapping

This report explains the concept of database normalization and ERD mapping. It also includes implementation of database, data integrity, and database queries.

Report
Database Normalization
Third Normal Form Relations
TlCustomer (FlCustomerID, FlCustomerName, FlStreet, FlCity, FlPostCode,
FlCustomerContactNum)
Primary Key FlCustomerID
TlEmployee (FlEmployeeID, FlEmployeeName, FlEmployeeStartDate)
Primary Key FlEmployeeID
TlParcel (FlParcelID, FlParcelWeightInGrams, FlFromStreet, FlFromCity, FlFromPostCode,
FlToStreet, FlToCity, FlToPostCode, FlDateCollected, FlParcelValue, FlCharges, FlStatus,
FlCustomerID, FlEmployeeID)
Primary Key ParcelID
FlCustomerID references TlCustomer (FlCustomerID)
FlEmployeeID references TlEmployee (FlEmployeeID)
TlDelivered (FlParcelID, FlDeliveredDate)
Primary Key FlParcelID
FlParcelID references TlParcel (FlParcelID)
TlReturned (FlParcelID, FlReturnedDate)
Primary Key FlParcelID
FlParcelID references TlParcel (FlParcelID)
TlLost (FlParcelID, FlLostEntryDate)
Primary Key FlParcelID
FlParcelID references TlParcel (FlParcelID)
TlClaim (FlClaimID, FlClaimLodgeDate, FlClaimSettledAmount, FlClaimPaidDate,
FlParcelID)
Primary Key FlClaimID
FlParcelID references TlLost (FlParcelID)
TlInvoice (FlInvoiceID, FlInvoiceAmount, FlInvoiceDate, FlParcelID)

Primary Key FlInvoiceID
FlParcelID references TlParcel (FlParcelID)
Functional Dependency
Claim Table
FlClaimLodgeDate  FlClaimID
FlClaimSettledAmount  FlClaimID
FlClaimPaidDate  FlClaimID
FlParcelID  FlClaimID
Employee Table
FlEmployeeName  FlEmployeeID
FlEmployeeStartDate  FlEmployeeID
(Poolet A. M. 2019)

Normalization Demonstration
TlClaim (FlClaimID, FlClaimLodgeDate, FlClaimSettledAmount, FlClaimPaidDate,
FlParcelID)
1. The rule of first normal form is that the table should not have any repeated group and the
table Claim is not containing any repeated group. So, the table Claim is in 1 NF.
2. The rule of second normal form is that there should be no partial dependency in the table.
In Claim table there is only one primary key which is not composite. All fields depend
upon it only. So, there is no partial dependency in the Claim table and that is why the
Claim table is in 2 NF.
3. The rule of third normal form is that there should be no transitive dependency in the
table. In Claim table all fields depend upon the primary key only. No field depends upon
any other field. So, there is no transitive dependency in the Claim table and that is why
the Claim table is in 3 NF.
TlEmployee (FlEmployeeID, FlEmployeeName, FlEmployeeStartDate)
1. The rule of first normal form is that the table should not have any repeated group and the
table Employee is not containing any repeated group. So, the table Employee is in 1 NF.
2. The rule of second normal form is that there should be no partial dependency in the table.
In Employee table there is only one primary key which is not composite. All fields
depend upon it only. So, there is no partial dependency in the Employee table and that is
why the Employee table is in 2 NF.
3. The rule of third normal form is that there should be no transitive dependency in the
table. In Employee table all fields depend upon the primary key only. No field depends
upon any other field. So, there is no transitive dependency in the Employee table and that
is why the Employee table is in 3 NF.
(Thakur D. n.d.)

Database Implementation
ERD Mapping
Data Integrity
Parcel Table
Attribute Data type Integrity Constraint Error Message
FlStatus Varchar(20) Collected, Returned,
Lost, Delivered
Enter Collected,
Returned, Lost or
Delivered
FlParcelValue Number >0 Enter greater than 0
FlCharges Number >0 Enter greater than 0
FlDateCollected Date/Time <=Date() Enter date prior or
equal to the today’s
date

Database Queries
Query 1
SELECT FlStatus, count(FlParcelID) AS NumberOfParcels FROM TlParcel GROUP BY
FlStatus;
Query 2
SELECT Count(FlParcelID) AS ParcelsNotClaimed FROM TlLost WHERE FlParcelID Not In
(Select FlParcelID from TlClaim);
Query 3
SELECT TOP 1 TlCustomer.FlCustomerID, TlCustomer.FlCustomerName,
TlCustomer.FlStreet, TlCustomer.FlCity, TlCustomer.FlPostCode,
TlCustomer.FlCustomerContactNum, sum(TlParcel.FlCharges) as TotalCharges FROM
TlCustomer INNER JOIN TlParcel ON TlCustomer.FlCustomerID=TlParcel.FlCustomerID
GROUP BY TlCustomer.FlCustomerID, TlCustomer.FlCustomerName, TlCustomer.FlStreet,
TlCustomer.FlCity, TlCustomer.FlPostCode, TlCustomer.FlCustomerContactNum ORDER BY
sum(TlParcel.FlCharges) DESC;

Query 4
SELECT TlEmployee.FlEmployeeID, TlEmployee.FlEmployeeName,
TlEmployee.FlEmployeeStartDate, count(TlParcel.FlParcelID) AS NumberOfParcels FROM
TlEmployee INNER JOIN TlParcel ON TlEmployee.FlEmployeeID=TlParcel.FlEmployeeID
WHERE TlEmployee.FlEmployeeID not in (Select FlEmployeeID from TlParcel where
FlStatus='Lost') GROUP BY TlEmployee.FlEmployeeID, TlEmployee.FlEmployeeName,
TlEmployee.FlEmployeeStartDate;
Query 5
SELECT * FROM TlEmployee WHERE FlEmployeeName like '*Wheel*';

Database Report
Implementation Report
Attractive Features
The assignment is containing so many attractive features. The most attractive features are-
Join Queries
I used inner join queries in the assignment hat are fetching information from more than one table.
Inner join is used to fetch records which satisfy the specified condition.
Sub Queries
Sub queries are used to show information from tables. It is made up with the combination of
more than one query in a systematic way.
Difficult Feature

The most attractive feature in the assignment is database report.
Database Report
The database report is used to show the data summary. It is used to easily show the summarized
data in a very presentable view.
References
Thakur D. (n.d.). Database Normalization. Retrieved From:
http://ecomputernotes.com/fundamental/what-is-a-database/what-is-a-database-
normalization
Poolet A. M. (2019). SQL by Design: Why You Need Database Normalization. Retrieved From:
https://www.itprotoday.com/sql-server/failover-detection-utility-simplifies-availability-
group-failover-analysis