Assessment Task 2

This document is Assessment Task 2 for the Diploma of Information Technology. It includes answers to questions related to database normalization and an updated entity relationship model. The document also includes a bibliography.

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.