Student ID: Student Name: Date:.

Student ID:
Student Name:
Date:

Report
ER Diagram

Relational Tables in Third Normal Form
Customers (FlcustomerID, FlCustomerName, Fladdress, Flphone, Flemail)
Primary Key FlCustomerID
Items (FlitemNumber, Flweight, Fldimensions, FlinsuranceAmount, Fldestination,
FlfinalDeliveryDate, Flinstructions)
Primary Key FlitemNumber
RetailCentres (FlretailCentreID, Fltype, Fladdress)
Primary Key FlretailCentreID
Employees (FlemployeeID, FlemployeeName, Fladdress, Flphone, Flemail, FlretailCentreID)
Primary Key FlemployeeID
FlretailCentreID references retailCentres (FlretailCentreID)
Receipts (FlitemNumber, FlreceivedDate, Flcharges, FlretailCentreID, FlemployeeID)
Primary Key FlitemNumber
FlretailCentreID references retail_Centre (FlretailCentreID)
FlemployeeID references Employees (FlemployeeID)
FlitemNumber references Items (FlitemNumber)
Transportation (FlscheduleNumber, FlcustomerID, Fltype, FldeliveryRoute, FlitemNumber)
Primary Key FlscheduleNumber
FlcustomerID references Customers (FlcustomerID)
FlitemNumber references Items (FlitemNumber)

Item’s Table Functional Dependency
FlWeight  FlItemNumber
FlDimension  FlItemNumber
FlInsuranceAmount  FlItemNumber
FlDestination  FlItemNumber
FlFinalDEliveryDate  FlItemNumber
FlInstructions  FlItemNumber
First Normal Form
The primary key in the table is ItemNumber and there is no repeated group. Therefore, the Item
table is in First Normal Form.
Second Normal Form
All fields depend upon the primary key ItemNumber and the ItemNumber is a single primary
key. Therefore, there is no partial dependency in the table. Therefore, the Item table is in Second
Normal Form.
Third Normal Form
All fields depend upon the primary key ItemNumber only. Therefore, there is no transitive
dependency in the table. Therefore, the Item table is in Third Normal Form.
(Thakur D. n.d.)
(Poolet A. M. 2019)

MS Access Database
Item Form

Queries
Query 1
SELECT * FROM Receipts WHERE month(receivedDate)=4;
Query 2
SELECT monthname(month(receivedDate)) AS [Month], sum(Receipts.charges) AS
TotalCharges FROM Receipts GROUP BY month(receivedDate);
Query 3
SELECT Receipts.itemNumber, Receipts.receivedDate, Receipts.charges,
Receipts.retailCentreID, Employees.employeeName FROM Employees INNER JOIN Receipts
ON Employees.employeeID=Receipts.employeeID;

Report

References
Thakur D. (n.d.). Database Normalization. [Online]. Available:
http://ecomputernotes.com/fundamental/what-is-a-database/what-is-a-database-normalization.
[Accessed: 23-May-2019]
Poolet A. M. (2019). SQL by Design: Why You Need Database Normalization. [Online].
Available: https://www.itprotoday.com/sql-server/failover-detection-utility-simplifies-
availability-group-failover-analysis. [Accessed: 23-May-2019]