Database Normalization and Implementation

This report discusses the concepts of database normalization and implementation. It covers topics such as 3NF relations, functional dependency, ER diagram mapping, data integrity, and database queries. The report also highlights interesting features and challenges in the implementation process.

[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