COIT20247 Database Design and Development, Assignment 2 Report

This report presents a detailed analysis of a database design and development assignment. It begins with the normalization of the database to the Third Normal Form (3NF), ensuring data integrity and minimizing redundancy. The report includes the mapping of an Entity-Relationship Diagram (ERD) to a set of relational tables. Functional dependencies within the tables are identified and demonstrated to meet 3NF criteria. The report also showcases the implementation of data integrity constraints, such as data type validation and range checks, within the database. Furthermore, it provides a series of SQL queries designed to extract specific information from the database, such as the status of parcels, the number of unclaimed parcels, and the customer with the highest charges. The report concludes with a discussion of interesting and difficult features encountered during the assignment, along with a list of references used.

COIT20247 Database Design and Development
Assignment 2
Student ID:
Student Name:
5/20/2019

Report
Normalization
Normalized Relations
TabbCustomer (FiildCustomerID, FiildCustomerName, FiildStreet, FiildCity, FiildPostCode,
FiildCustomerContactNum)
Primary Key FiildCustomerID
TabbEmployee (FiildEmployeeID, FiildEmployeeName, FiildEmployeeStartDate)
Primary Key FiildEmployeeID
TabbParcel (FiildParcelID, FiildParcelWeightInGrams, FiildFromStreet, FiildFromCity,
FiildFromPostCode, FiildToStreet, FiildToCity, FiildToPostCode, FiildDateCollected,
FiildParcelValue, FiildCharges, FiildStatus, FiildCustomerID, FiildEmployeeID)
Primary Key ParcelID
FiildCustomerID references TabbCustomer (FiildCustomerID)
FiildEmployeeID references TabbEmployee (FiildEmployeeID)
TabbDelivered (FiildParcelID, FiildDeliveredDate)
Primary Key FiildParcelID
FiildParcelID references TabbParcel (FiildParcelID)
TabbReturned (FiildParcelID, FiildReturnedDate)
Primary Key FiildParcelID
FiildParcelID references TabbParcel (FiildParcelID)
TabbLost (FiildParcelID, FiildLostEntryDate)
Primary Key FiildParcelID
FiildParcelID references TabbParcel (FiildParcelID)
TabbClaim (FiildClaimID, FiildClaimLodgeDate, FiildClaimSettledAmount,
FiildClaimPaidDate, FiildParcelID)
Primary Key FiildClaimID
FiildParcelID references TabbLost (FiildParcelID)
TabbInvoice (FiildInvoiceID, FiildInvoiceAmount, FiildInvoiceDate, FiildParcelID)

Primary Key FiildInvoiceID
FiildParcelID references TabbParcel (FiildParcelID)
Functional Dependency
Employee Table
FiildEmployeeName  FiildEmployeeID
FiildEmployeeStartDate  FiildEmployeeID
Customer Table
FiildCustomerName  FiildCustomerID
FiildStreet  FiildCustomerID
FiildCity  FiildCustomerID
FiildPostCode  FiildCustomerID
FiildCustomerContactNum  FiildCustomerID

Demonstration of Third Normal Form
Employee Table
1. In employee table, there is no repeated group. So, employee table is in first normal form.
2. In employee table, all fields are depending upon the primary key employeeID only which
is single. There is no chance of partial dependency. So, employee table is in second
normal form.
3. In employee table, all fields are depending upon the primary key employeeID only. No
field depends upon any other field. There is no transitive dependency. So, employee table
is in third normal form.
Customer Table
1. In customer table, there is no repeated group. So, customer table is in first normal form.
2. In customer table, all fields are depending upon the primary key customerID only which
is single. There is no chance of partial dependency. So, customer table is in second
normal form.
3. In customer table, all fields are depending upon the primary key customerID only. No
field depends upon any other field. There is no transitive dependency. So, customer table
is in third normal form.
(Studytonight 2019)
(Chapple M. 2019)

Relational Database Implementation
ERD Mapping
Data Integrity
Parcel Table
Attribute Data type Integrity Constraint
Implemented
Error Message
FiildStatus Text(20) Collected Or Returned
Or Lost Or Delivered
Enter any one value
from - Collected Or
Returned Or Lost Or
Delivered
FiildParcelValue Integer >0 Enter greater than 0.
FiildCharges Integer >0 Enter greater than 0.
FiildDateCollected Date/Time <=Date() Enter greater than or
equal to today’s date.

Invoice Table
Attribute Data type Integrity Constraint
Implemented
Error Message
FiildInvoiceAmount Integer >0 Enter greater than 0.
FiildInvoiceDate() Date/Time <=Date() Enter before or equal
to today’s date.
Employee Table
Attribute Data type Integrity Constraint
Implemented
Error Message
FiildEmployeeStartDate() Date/Time <=Date() Enter before or equal
to today’s date.
Delivered Table
Attribute Data type Integrity Constraint
Implemented
Error Message
FiildDeliveredDate() Date/Time <=Date() Enter before or equal
to today’s date.
Returned Table
Attribute Data type Integrity Constraint
Implemented
Error Message
FiildReturnedDate() Date/Time <=Date() Enter before or equal
to today’s date.
Lost Table
Attribute Data type Integrity Constraint
Implemented
Error Message
FiildLostEntryDate() Date/Time <=Date() Enter before or equal
to today’s date.

Database Queries
Query 1
SELECT FiildStatus, count(FiildParcelID) AS NumberOfParcels FROM TabbParcel GROUP
BY FiildStatus;
Query 2
SELECT count(FiildParcelID) AS ParcelsNotClaimed FROM TabbLost WHERE FiildParcelID
not in (Select FiildParcelID from TabbClaim);
Query 3
SELECT TOP 1 TabbCustomer.FiildCustomerID, TabbCustomer.FiildCustomerName,
TabbCustomer.FiildStreet, TabbCustomer.FiildCity, TabbCustomer.FiildPostCode,
TabbCustomer.FiildCustomerContactNum, sum(TabbParcel.FiildCharges) FROM
TabbCustomer INNER JOIN TabbParcel ON
TabbCustomer.FiildCustomerID=TabbParcel.FiildCustomerID GROUP BY
TabbCustomer.FiildCustomerID, TabbCustomer.FiildCustomerName,
TabbCustomer.FiildStreet, TabbCustomer.FiildCity, TabbCustomer.FiildPostCode,
TabbCustomer.FiildCustomerContactNum ORDER BY sum(TabbParcel.FiildCharges) DESC;

Query 4
SELECT TabbEmployee.FiildEmployeeID, TabbEmployee.FiildEmployeeName,
TabbEmployee.FiildEmployeeStartDate, count(TabbParcel.FiildParcelID) AS NumberOfParcels
FROM TabbEmployee INNER JOIN TabbParcel ON
TabbEmployee.FiildEmployeeID=TabbParcel.FiildEmployeeID
WHERE TabbEmployee.FiildEmployeeID not in (Select FiildEmployeeID from TabbParcel
where FiildStatus='Lost') GROUP BY TabbEmployee.FiildEmployeeID,
TabbEmployee.FiildEmployeeName, TabbEmployee.FiildEmployeeStartDate;
Query 5
SELECT * FROM TabbEmployee WHERE FiildEmployeeName Like '*Wheel*';

Database Report
Implementation Report
Fascinating Features
The assignment is containing number of interesting and fascinating features but I liked the most-
- Database report
- Database sub queries
Difficult Features
The assignment has some difficult topics also but I found the solutions and completed that. The
most difficult task I found is-
- Inner join queries

References
Studytonight (2019). Normalization of Database. Retrieved From:
https://www.studytonight.com/dbms/database-normalization.php
Chapple M. (2019). Database Normalization Basics. Retrieved From:
https://www.lifewire.com/database-normalization-basics-1019735