Database Normalization and 3NF Relations

This report discusses the concept of database normalization and 3NF relations. It explains the tables and their relationships in a relational database, along with functional dependencies. The report also provides a demonstration of 3NF and discusses the implementation of a relational database using an ER diagram. It covers data integrity and error messages, as well as various database queries. The report concludes with interesting and difficult points encountered during the assignment.

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]