Database Fundamentals B01DBFN212/ BIT 208.

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]