Database Design and Normalization: A Comprehensive Report

This report focuses on database design, normalization, and SQL queries. It includes an overview of the ER diagram, database structure, and the process of normalization, specifically detailing the transition to the third normal form. The report also demonstrates the use of SQL queries to extract data, including queries to filter data, aggregate data, and join tables. The report presents the code and the results of these queries. The report also contains the references used in the assignment. This document serves as a valuable resource for students studying database design and implementation.

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]