Database Normalization 3NF Relations, Queries, and Report Analysis

This report provides a comprehensive analysis of database normalization, focusing on the implementation of Third Normal Form (3NF) in relational database design. The assignment includes the creation of tables such as Customer, Employee, Parcel, Delivered, Returned, Lost, Claim, and Invoice, detailing their attributes and primary/foreign key relationships. Functional dependencies are identified and assessed for the Employee and Invoice tables to demonstrate adherence to 3NF. An ER diagram visually represents the database structure, and data integrity constraints are defined for the Parcel table, including data type specifications and error messages. The report also features five SQL queries designed to extract specific information from the database, such as parcel status counts, unclaimed parcel counts, the customer with the highest charges, employees who haven't lost parcels, and employee name search. The report concludes with a discussion of interesting and difficult points encountered during the assignment, along with the references used.

Student ID:
Student Name:

Report
Database Normalization
3NF Relations
TableCustomer (FieldCustomerID, FieldCustomerName, FieldStreet, FieldCity, FieldPostCode,
FieldCustomerContactNum)
Primary Key FieldCustomerID
TableEmployee (FieldEmployeeID, FieldEmployeeName, FieldEmployeeStartDate)
Primary Key FieldEmployeeID
TableParcel (FieldParcelID, FieldParcelWeightInGrams, FieldFromStreet, FieldFromCity,
FieldFromPostCode, FieldToStreet, FieldToCity, FieldToPostCode, FieldDateCollected,
FieldParcelValue, FieldCharges, FieldStatus, FieldCustomerID, FieldEmployeeID)
Primary Key ParcelID
FieldCustomerID references TableCustomer (FieldCustomerID)
FieldEmployeeID references TableEmployee (FieldEmployeeID)
TableDelivered (FieldParcelID, FieldDeliveredDate)
Primary Key FieldParcelID
FieldParcelID references TableParcel (FieldParcelID)
TableReturned (FieldParcelID, FieldReturnedDate)
Primary Key FieldParcelID
FieldParcelID references TableParcel (FieldParcelID)
TableLost (FieldParcelID, FieldLostEntryDate)
Primary Key FieldParcelID
FieldParcelID references TableParcel (FieldParcelID)
TableClaim (FieldClaimID, FieldClaimLodgeDate, FieldClaimSettledAmount,
FieldClaimPaidDate, FieldParcelID)
Primary Key FieldClaimID
FieldParcelID references TableLost (FieldParcelID)
TableInvoice (FieldInvoiceID, FieldInvoiceAmount, FieldInvoiceDate, FieldParcelID)

Primary Key FieldInvoiceID
FieldParcelID references TableParcel (FieldParcelID)
Functional Dependency
Employee Table
FieldEmployeeName  FieldEmployeeID
FieldEmployeeStartDate  FieldEmployeeID
There is no partial or transitive dependency in Employee table.
Invoice Table
FieldInvoiceAmount  FieldInvoiceID
FieldInvoiceDate  FieldInvoiceID
FieldParcelID  FieldInvoiceID
There is no partial or transitive dependency in Invoice table.
(Tutorialspoint.con n.d.)

3 NF Demonstration
TableEmployee (FieldEmployeeID, FieldEmployeeName, FieldEmployeeStartDate)
1 NF: If there is no repeated group in the table, then table is in first normal form. Therefore, the
Employee table is in 1 NF.
2 NF: If there is no partial dependency in the table, then table is in second normal form. The
employee id is the only single primary key in the table and all fields are depending upon it only.
There is no chance of partial dependency. Therefore, the Employee table is in 2 NF.
3 NF: if one attribute depend upon another attribute except primary or composite key, it is called
transitive dependency. If there is no transitive dependency, the table is in third normal form. All
fields are depending upon the primary key employee id only. Therefore, the Employee table is in
3 NF.
TableInvoice (FieldInvoiceID, FieldInvoiceAmount, FieldInvoiceDate, FieldParcelID)
1 NF: If there is no repeated group in the table, then table is in first normal form. Therefore, the
Invoice table is in 1 NF.
2 NF: If there is no partial dependency in the table, then table is in second normal form. The
invoice id is the only single primary key in the table and all fields are depending upon it only.
There is no chance of partial dependency. Therefore, the Invoice table is in 2 NF.
3 NF: if one attribute depend upon another attribute except primary or composite key, it is called
transitive dependency. If there is no transitive dependency, the table is in third normal form. All
fields are depending upon the primary key invoice id only. Therefore, the Invoice table is in 3
NF.

Relational Database Implementation
ER Diagram Mapping
Data Integrity
Parcel Table
Attribute Data type Integrity Constraint
Implemented
Error Message
FieldStatus Varchar(20) "Collected" Or
"Returned" Or "Lost"
Or "Delivered"
The data in the status
field should be
"Collected" Or
"Returned" Or "Lost"
Or "Delivered".
FieldParcelValue Number >0 The data in the
ParcelValue field
should be greater than
0.
FieldCharges Number >0 The data in the
Charges field should
be greater than 0.
FieldDateCollected Date/Time <=Date() The data in the date
collecetd field should
be less than or equal
to the current date.
(Refsnes Data. 2019)

Database Queries
Query 1
SELECT TableParcel.FieldStatus, Count(TableParcel.FieldParcelID) AS NumberofParcels
FROM TableParcel GROUP BY TableParcel.FieldStatus;
Query 2
SELECT count(FieldParcelID) AS 'Unclaimed Parcels' FROM Tablelost WHERE FieldParcelID
not in (Select FieldParcelID from TableClaim);
Query 3
SELECT TOP 1 TableCustomer.FieldCustomerID, TableCustomer.FieldCustomerName,
TableCustomer.FieldStreet, TableCustomer.FieldCity, TableCustomer.FieldPostCode,
TableCustomer.FieldCustomerContactNum, sum(TableParcel.FieldCharges) FROM
TableCustomer INNER JOIN TableParcel ON
TableCustomer.FieldCustomerID=TableParcel.FieldCustomerID GROUP BY
TableCustomer.FieldCustomerID, TableCustomer.FieldCustomerName,
TableCustomer.FieldStreet, TableCustomer.FieldCity, TableCustomer.FieldPostCode,
TableCustomer.FieldCustomerContactNum ORDER BY sum(TableParcel.FieldCharges) DESC;

Query 4
SELECT TableEmployee.FieldEmployeeID, TableEmployee.FieldEmployeeName,
TableEmployee.FieldEmployeeStartDate, count(TableParcel.FieldParcelID) AS NumOfParcels
FROM TableEmployee INNER JOIN TableParcel ON
TableEmployee.FieldEmployeeID=TableParcel.FieldEmployeeID
WHERE TableEmployee.FieldEmployeeID not in (Select FieldEmployeeID from TableParcel
where FieldStatus='Lost')
GROUP BY TableEmployee.FieldEmployeeID, TableEmployee.FieldEmployeeName,
TableEmployee.FieldEmployeeStartDate;
Query 5
SELECT * FROM TableEmployee WHERE FieldEmployeeName like '*Wheel*';

Database Report
Implementation Report
Interesting Points
There are different interesting points in the assignment but the most interesting points I found in
the assignment are-
- SQL Queries
- Database Report
Difficult Points
The assignment is very interesting and full of knowledgeable. It is giving detail knowledge abour
RDBMS. One of the most difficult tasks I found is joins. Making queries using joins was
difficult but I resolved and learned a lot of things.

References
Refsnes Data. (2019). SQL Data Types for MySQL, SQL Server, and MS Access. [Online].
Available: https://www.w3schools.com/sql/sql_datatypes.asp. [Accessed: 15-May-2019]
Tutorialspoint.con (n.d.). Functional Dependency. [Online]. Available:
https://www.tutorialspoint.com/dbms/database_normalization.htm. [Accessed: 15-May-2019]