Kent Institute Australia: Database Fundamentals Report, Assessment 3

This report presents a design and implementation of a relational database system for International Parcel Service (IPS). The report includes an Entity-Relationship (ER) diagram, relational tables in Third Normal Form (3NF), and functional dependencies. The report also includes MS Access database queries to retrieve and manipulate data. The database design encompasses tables for customers, items, retail centers, employees, receipts, and transportation, ensuring data integrity and efficient retrieval. The report also explains the normalization process, ensuring that the data is structured efficiently, and includes the SQL queries to extract specific data. The report concludes with the design and implementation of a relational database, including the ER diagram, table structure, and SQL queries.

Database Fundamentals B01DBFN212/ BIT 208
Assessment 3
Student ID:
Student name:
5/21/2019

Report
ER Diagram
(Cinergix Pty Ltd. 2011)

Relational Tables in Third Normal Form
TbCustomer (FldcustomerID, FldCustomerName, Fldaddress, Fldphone, Fldemail)
Primary Key FldCustomerID
TbItem (FlditemNumber, Fldweight, Flddimensions, FldinsuranceAmount, Flddestination,
FldfinalDeliveryDate, Fldinstructions)
Primary Key FlditemNumber
TbRetail_Centre (FldretailCentreID, Fldtype, Fldaddress)
Primary Key FldretailCentreID
TbEmployee (FldemployeeID, FldemployeeName, Fldaddress, Fldphone, Fldemail,
FldretailCentreID)
Primary Key FldemployeeID
FldretailCentreID references Tbretail_Centre (FldretailCentreID)
TbReceipt (FlditemNumber, FldreceivedDate, Fldcharges, FldretailCentreID, FldemployeeID)
Primary Key FlditemNumber
FldretailCentreID references Tbretail_Centre (FldretailCentreID)
FldemployeeID references TbEmployee (FldemployeeID)
FlditemNumber references TbItem (FlditemNumber)
TbTransportation (FldscheduleNumber, FldcustomerID, Fldtype, FlddeliveryRoute,
FlditemNumber)
Primary Key FldscheduleNumber
FldcustomerID references TbCustomer (FldcustomerID)
FlditemNumber references TbItem (FlditemNumber)
(B. Peter n.d.)

Functional Dependency in Customer Table
FldEmployeeName  FldEmployeeID
FldAddress  FldEmployeeID
FldPhone  FldEmployeeID
FldEmail  FldEmployeeID
FldEmail  FldEmployeeID
FldRetailCentreID  FldEmployeeID
1. There is no repeated group in the table. Therefore; the table is in first normal form.
2. All fields are depending on only primary key EmployeeID. There is no partial
dependency. Therefore; the table is in second normal form.
3. There is no transitive dependency in the table. Therefore; the table is in third normal
form.

MS Access Database
Item Form

Queries
Query 1
SELECT * FROM TbCustomer WHERE CustomerName like "*Walker*";
Query 2
SELECT TbItem.itemNumber, TbItem.weight, TbItem.dimensions, TbItem.insuranceAmount,
TbItem.destination, TbItem.finalDeliveryDate, TbReceipt.receivedDate FROM TbItem INNER
JOIN TbReceipt ON TbItem.itemNumber=TbReceipt.itemNumber;
Query 3
SELECT TbTransportation.customerID, TbCustomer.customerName,
count(TbTransportation.scheduleNumber) AS NumSchedules FROM TbCustomer INNER JOIN
TbTransportation ON TbCustomer.customerID = TbTransportation.customerID GROUP BY
TbTransportation.customerID, TbCustomer.customerName;

Report

References
B. Peter n.d., SQL SERVER Database Normalization Basics for Developers, [Online].
Available: http://www.nullskull.com/a/1629/sql-server-database-normalization-basics-for-
developers.aspx. [Accessed: 21-May-2019]
Cinergix Pty Ltd. 2011, Ultimate Guide to ER Diagrams, [Online].
Available:http://creately.com/blog/diagrams/er-diagrams-tutorial/. [Accessed: 21-May-2019]