Task $2$ Normalization

$($Individual work, no groups$)$

Due Date:

Please refer to the Instructional Plan for all assessment deadlines.

Description:

This assessment has multiple objectives for student learning and skill$($s$)$ development:

$1.$ This scenario will provide students with an academic$-$based case which is meant to:

a$.$ Establish an understanding of the relationship between data sets.

b$.$ Identify the structure of data and how record sets are formed.

$2.$ This deliverable will a$$ord course faculty a chance to gauge student preparedness and ability to:

a$.$ Understand the structure and relationship of data.

b$.$ To work within a business setting to elicit clarification on data, and data structure design, concerns.

Tasks:

Consider the following dataset.

ProgramName Department DepartmentHead Duration DegreeType Enrollment

Computer

Science Computer

Science Dr$.$ Smith $4$ years Bachelor$$s $500$

Electrical

Engineering Engineering Dr$.$ Johnson $5$ years Bachelor$$s $400$

Psychology Psychology Dr$.$ Williams $4$ years Bachelor$$s $600$

Biology, Math Science,

Engineering Dr$.$ Lee, Dr$.$ Johnson $4$ years, $3$

years Bachelor$$s $450$

Biotechnology Science Dr$.$ Lee $2$ years Master$$s $10$

System

Applications Computer

Science Dr$.$ Smith $2$ years Master$$s $20$

Computer

Science Computer

Science Dr$.$ Smith $2$ years Master$$s $30$

Section A

A$\mathrm{.}1)$ Identify all candidate keys.

A$\mathrm{.}2)$ Check $1$NF

Is the table in $1$NF$?$

If not, make the necessary adjustments to change it into $1$NF$.$ Include the table starting point, and the final table$($s$)$ result $($after any modifications you make to it$).$

Add comments to explain why you made the changes you did or why changes were not necessary.

A$\mathrm{.}3)$ Check $2$NF

Is your current table $($the one you ended A$\mathrm{.}2$ with$)$ in $2$NF$?$

If not, make the necessary adjustments to change it into $2$NF$.$ Include the final table$($s$)$ result $($after any modifications you make to it$).$ You can skip the starting table here.

Add comments to explain why you made the changes you did or why changes were not necessary.

A$\mathrm{.}4)$ Check $3$NF

Is your current table $($the one you ended A$\mathrm{.}3$ with$)$ in $3$NF$?$

If not, make the necessary adjustments to change it into $3$NF$.$ Include the final table$($s$)$ result $($after any modifications you make to it$).$ You can skip the starting table here.

Add comments to explain why you made the changes you did or why changes were not necessary.

Section B

Create an unnormalized table for any topic and columns with names and types of your own choice. Add at least $6$ columns and $5$ rows $($records$).$ In one of the rows include your student number as data.

B$\mathrm{.}1)$ Make sure your example has a surrogate key that make sense. Justify its use.

B$\mathrm{.}2)$ What are the possible candidate keys in your example? Justify it$.$

B$\mathrm{.}3)$ Explain what makes your table an unnormalized table. Show the steps and changes you need to take to transform it into $1$NF$.$ Document these steps with screenshots and explanations in your report.