Database Normalization, ER Diagram, and SQL Queries Report - COIT20247

This report presents a comprehensive analysis of database design and development, focusing on the implementation of a relational database system using MS Access. The assignment begins by mapping an Entity-Relationship Diagram (ERD) into a set of relations, ensuring they adhere to the Third Normal Form (3NF). Functional dependencies within two selected relations are identified and demonstrated to meet 3NF criteria. The report details the database schema, including tables for customers, employees, parcels, deliveries, returns, lost items, claims, and invoices. Data integrity constraints, such as data types and validation rules, are specified for key attributes like parcel status, value, and charges. Furthermore, the assignment showcases the use of SQL queries to retrieve specific data, including counts of parcel statuses, unclaimed parcels, and customer billing information. Advanced queries demonstrate the use of subqueries, joins, and grouping to extract meaningful insights from the database. The report also highlights interesting and challenging features of the database design, such as subqueries, SQL joins, and database reports, along with references to relevant academic sources.

Student ID:
Student Name:

Report
Database Normalization
3NF Relations
TabCustomer (FildCustomerID, FildCustomerName, FildStreet, FildCity, FildPostCode,
FildCustomerContactNum)
Primary Key FildCustomerID
TabEmployee (FildEmployeeID, FildEmployeeName, FildEmployeeStartDate)
Primary Key FildEmployeeID
TabParcel (FildParcelID, FildParcelWeightInGrams, FildFromStreet, FildFromCity,
FildFromPostCode, FildToStreet, FildToCity, FildToPostCode, FildDateCollected,
FildParcelValue, FildCharges, FildStatus, FildCustomerID, FildEmployeeID)
Primary Key ParcelID
FildCustomerID references TabCustomer (FildCustomerID)
FildEmployeeID references TabEmployee (FildEmployeeID)
TabDelivered (FildParcelID, FildDeliveredDate)
Primary Key FildParcelID
FildParcelID references TabParcel (FildParcelID)
TabReturned (FildParcelID, FildReturnedDate)
Primary Key FildParcelID
FildParcelID references TabParcel (FildParcelID)
TabLost (FildParcelID, FildLostEntryDate)
Primary Key FildParcelID
FildParcelID references TabParcel (FildParcelID)
TabClaim (FildClaimID, FildClaimLodgeDate, FildClaimSettledAmount, FildClaimPaidDate,
FildParcelID)
Primary Key FildClaimID
FildParcelID references TabLost (FildParcelID)
TabInvoice (FildInvoiceID, FildInvoiceAmount, FildInvoiceDate, FildParcelID)

Primary Key FildInvoiceID
FildParcelID references TabParcel (FildParcelID)
Functional Dependency
Claim Table
FildClaimLodgeDate  FildClaimID
FildClaimSettledAmount  FildClaimID
FildClaimPaidDate  FildClaimID
FildParcelID  FildClaimID
Customer Table
FildCustomerName  FildCustomerID
FildStreet  FildCustomerID
FildCity  FildCustomerID
FildPostCode  FildCustomerID
FildCustomerContactNum  FildCustomerID

Demonstration of Normalization
Claim Table
First Normal Form
The table has no repeated group. Therefore the table claim is in 1 NF.
Second Normal Form
The claimID is the single primary key. All fields are depending upon it only. It is the single key;
there is no scope of partial dependency. Therefore the table claim is in 2 NF.
Third Normal Form
All fields are depending upon the claimID only. No other attribute depends upon any other
attribute. Therefore the table claim is in 3 NF.
Customer Table
First Normal Form
The table has no repeated group. Therefore the table customer is in 1 NF.
Second Normal Form
The customerID is the single primary key. All fields are depending upon it only. It is the single
key; there is no scope of partial dependency. Therefore the table customer is in 2 NF.
Third Normal Form
All fields are depending upon the customerID only. No other attribute depends upon any other
attribute. Therefore the table customer is in 3 NF.
(Ambysoft Inc. 2019)
(TechTarget 2019)

Relational Database Implementation
ER Diagram Mapping
(Javapoint.con 2018)
(BeginnersBook. 2019)
Data Integrity
Parcel Tab
Attribute Data type Integrity Constraint
Implemented
Error Message
FildStatus Varchar(20) Collected, "Returned",
"Lost", "Delivered"
Enter any one value -
Collected, "Returned",
"Lost", "Delivered"
FildParcelValue Number >0 Enter greater than 0
value.
FildCharges Number >0 Enter greater than 0
value.
FildDateCollected Date/Time <=Date() The data in the date
collecetd Fild should
be less than or equal
to the current date.

Database Queries
Query 1
SELECT TabParcel.FildStatus, Count(TabParcel.FildParcelID) AS NumberofParcels FROM
TabParcel GROUP BY TabParcel.FildStatus;
Query 2
SELECT count(FildParcelID) AS UnclaimedParcels FROM Tablost WHERE FildParcelID not
in (Select FildParcelID from TabClaim);
Query 3
SELECT TOP 1 TabCustomer.FildCustomerID, TabCustomer.FildCustomerName,
TabCustomer.FildStreet, TabCustomer.FildCity, TabCustomer.FildPostCode,
TabCustomer.FildCustomerContactNum, sum(TabParcel.FildCharges) FROM TabCustomer
INNER JOIN TabParcel ON TabCustomer.FildCustomerID=TabParcel.FildCustomerID
GROUP BY TabCustomer.FildCustomerID, TabCustomer.FildCustomerName,
TabCustomer.FildStreet, TabCustomer.FildCity, TabCustomer.FildPostCode,
TabCustomer.FildCustomerContactNum ORDER BY sum(TabParcel.FildCharges) DESC;

Query 4
SELECT TabEmployee.FildEmployeeID, TabEmployee.FildEmployeeName,
TabEmployee.FildEmployeeStartDate, count(TabParcel.FildParcelID) AS NumOfParcels
FROM TabEmployee INNER JOIN TabParcel ON
TabEmployee.FildEmployeeID=TabParcel.FildEmployeeID
WHERE TabEmployee.FildEmployeeID not in (Select FildEmployeeID from TabParcel where
FildStatus='Lost')
GROUP BY TabEmployee.FildEmployeeID, TabEmployee.FildEmployeeName,
TabEmployee.FildEmployeeStartDate;
Query 5
SELECT * FROM TabEmployee WHERE FildEmployeeName like '*Wheel*';

Database Report
Interesting Features
There are so many different features of database and RDBMS that are implemented in the
assignment like normalization, SQL joins etc. The most interesting features in all the features
are-
- Sub queries
- SQL Joins
- Group by query
Difficult Features
The assignment is very interesting but still I stuck in some points and resolved the issue and
completed the assignment by achieving great knowledge. The most difficult features is-
- Database Report
There are so many options in the MS Access database report. I learned and explored all and
achieve the complete knowledge about it.

References
BeginnersBook. (2019). Entity Relationship Diagram – ER Diagram in DBMS. [Online].
Available: https://beginnersbook.com/2015/04/e-r-model-in-dbms/. [Accessed: 20-May-2019]
Javapoint.con (2018). ER Model. [Online]. Available: https://www.javatpoint.com/dbms-er-
model-concept. [Accessed: 20-May-2019]
Ambysoft Inc. (2019). Introduction to Data Normalization: A Database "Best" Practice. [Online].
Available: http://agiledata.org/essays/dataNormalization.html. [Accessed: 20-May-2018]
TechTarget (2019). Database normalization in MySQL: Four quick and easy steps. [Online].
Available: https://www.computerweekly.com/tutorial/Database-normalization-in-MySQL-Four-
quick-and-easy-steps. [Accessed: 20-May-2018]