Diploma of IT: Data Modeling Assessment Task 2, Strathfield College

This project presents a solution for Assessment Task 2 of the ICTSAD501 unit, focusing on data modeling. The assignment addresses database normalization, transforming tables into first, second, and third normal forms to eliminate data redundancy and improve data integrity. The solution includes detailed explanations and examples of how to apply normalization rules. Furthermore, the project involves creating and modifying an Entity Relationship (ER) model to represent relationships between entities in a property sales office scenario. The author provides a visual representation of the ER model, showing the relationships between entities such as Sales Office, Employee, Property, and Owner. The project demonstrates a practical application of data modeling principles, including the design and implementation of relational databases. The solution also includes a bibliography of relevant academic sources. The project is designed to help students understand the process of designing and implementing relational databases. The assignment also covers the concept of creating relationships between tables and using forms to enter data.

Running head: ASSESSMENT TASK 2
Diploma of Information Technology: Assessment Task 2
Name of the Student
Name of the University
Author’s note:

1Assessment Task 2
Table of Contents
Part A:..............................................................................................................................................2
Part B:..............................................................................................................................................5
Bibliography:...................................................................................................................................9

2Assessment Task 2
Part A:
Answer to Question 1: The given table is as following.
JobNbr PartID QtyUsed PartDescription
JB001 P001 1 LCD
JB002 P002 2 fan
As per the above table, the table is already in first normal form as there is no multivalued
attributes. The QtyUsed key is dependent on composite primary key. However, the
PartDescription is dependent on the PartID only. Therefore, there is one partial dependency in
the table. In order to make the table in second normal form a new table called parts is created.
Therefore, the tables are as following.
JobNbr PartID QtyUsed
JB001 P001 1
JB002 P002 2
PartID PartDescription
P001 LCD
P002 fan

3Assessment Task 2
There is no transitive dependency among the entities of these two tables. Therefore, the
database tables are in third normal form.
Answer to Question 2: The given table is as following.
EmployeeNumber EmployeeName Job Details
EM001 Mike JB001, TV has broken LCD
EM002 Sam JB003, Phone speaker not working
Above table is not in third normal form as the Job Tables column is multivalued attribute.
In order to make the table in first normal form JobNbr and JobDesc is added and Job Details is
removed. The following table is as dollowing.
EmployeeNumber EmployeeName JobNbr JobDescription
EM001 Mike JB001 TV has broken LCD
EM002 Sam JB002 Phone speaker not
working
The JobDescription is dependent on the JobNbr. Therefore, there is a partial dependency
in the table. A new Job table is created to make the database in second normal form.
EmployeeNumber EmployeeName
EM001 Mike

4Assessment Task 2
EM002 Sam
JobNbr EmployeeNumber JobDescription
JB001 EM001 TV has broken LCD
JB002 EM002 Phone speaker not
working
There is no transitive dependency among the entities of these two tables. Therefore, the
database tables are in third normal form.
Answer to Question 3: The given table is as following.
DriverNumber DriverName VehicleNumber VehicleDestination
DR001 Mike VH001 Destination 1
DR002 Sam VH002 Destination 2
The table is already in first normal form. The VehicleDestination is dependent on the
DriverNumber which generate a partial dependency. In order to make the table in second normal
form, the following adjustments are done in the database.
DriverNumber DriverName
DR001 Mike

5Assessment Task 2
DR002 Sam
VehicleNumber DriverNumber VehicleDestination
VH001 DR001 Destination 1
VH002 DR002 Destination 2
There is no transitive dependency among the entities of these two tables. Therefore, the
database tables are in third normal form.
Part B:
Figure 1: Changed Entity Relationship Model of Property Sales Office
(Source: Created by Author)
Sales Office:

6Assessment Task 2
SalesOffice
ID Location
1 Main Street
2 Camac Street
3 Downtown
Employee:
Employees
EmployeeID ID EmployeeName
1 1 James Anderson
2 1 Emilia Clerk
3 2 John Wick
4 2 Jackie Chan
Property:

7Assessment Task 2
Property
PropertyID ID Location
1 2 Camac Street
2 3 Downtown
3 1 Main Street
Owner:
Owner
OwnerID OwnerName
1 Brad Traversy
2 Shakal Muaha
3 Alita Jhonson
Owner Property:
OwnerProperty
OwnerID PropertyID
1 2

8Assessment Task 2
OwnerProperty
OwnerID PropertyID
2 3
3 1

9Assessment Task 2
Bibliography:
Mathelier, A., Fornes, O., Arenillas, D. J., Chen, C. Y., Denay, G., Lee, J., ... & Zhang, A. W.
(2015). JASPAR 2016: a major expansion and update of the open-access database of
transcription factor binding profiles. Nucleic acids research, 44(D1), D110-D115.
Khan, A., Fornes, O., Stigliani, A., Gheorghe, M., Castro-Mondragon, J. A., van der Lee, R., ...
& Baranasic, D. (2017). JASPAR 2018: update of the open-access database of
transcription factor binding profiles and its web framework. Nucleic acids
research, 46(D1), D260-D266.
Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management.
Cengage Learning.