Chapter $6$ Homework FIGURE $6\mathrm{.}3$ FIRST NORMAL FORM $(1$NF$)$ DEPENDENCY DIAGRAM

$1$ NF $($PROI NUM, EMP$\_$NUM, PROI$\_$NAME, EMP$\_$NAME, JOB$\_$CLASS, CHG$\_$HOURS, HOURS$)$

PARTIAL DEPENDENCIES:

$($PRO$)$ NUM $$ PROI$\_$NAME$)$

EMP$\_$NUM $$ EMP$\_$NAME, JOB$\_$CLASS, CHG$\_$HOUR$)$

TRANSITIVE DEPENDENCY:

$($JOB$\_$CLASS $$ CHG$\_$HOUR$)$ FIGURE $6\mathrm{.}5$ THIRD NORMAL FORM $(3$NF$)$ CONVERSION RESULTS

Table name: PROJECT

PROJECT $($PRO$)$ NUM, PROI$\_$NAME$)$

Table name: JOB

JOB $($JOB$\_$CLASS, CHG$\_$HOUR$)$

Table name: EMPLOYEE

EMPLOYEE $($EMP NUM, EMP$\_$NAME, JOB$\_$CLASS$)$

Table name: ASSIGNMENT

ASSIGNMENT $($PRO$)$ NUM, EMP$\_$NUM, ASSIGN$\_$HOURS$)$

Answer the question below.

Note:

In drawing the diagram, you can use any $($diagraming$)$ software, such as

Draw.io$,$ Microsoft Visio, or even

Microsoft Word$/$Excel$.$

Copy and paste the diagram to Microsoft Word or convert them to PDF and Submit the Word file or PDF

file

Include diagram ONLY, not the entire software screen

Your submission should be readable

Put your name on your document

$($Question$)$

The below table contains data about employees and the trainings they took.

In what normal form is this table?

Describe the insertion, update, and deletion anomalies of the table by providing an example $($that is$,$ the

problems that occurred when deleting, updating, and inserting record$($s$)).$

Refer to the update anomalies explained with the example on page $202.$ Your answer should be

similar to it$.$

Draw its dependency diagram and identify all dependencies.

Yours looks like the one in Figure $6\mathrm{.}3.$

Include both diagram $($the top portion in Figure $6\mathrm{.}3)$ and textual notation $($the bottom portion in

Figure $6\mathrm{.}3)$ for each identified dependency.

But you don't have to include relational schema, that is$,$ INF $($STORE$-$NO$,$ STORE$-$NAME, EMP$-$

NAME.......$).$

Normalize the table so that there are no more partial and$/$or transitive dependencies, and draw the new

dependency diagrams for the normal form.

Yours looks like the one in Figure $6\mathrm{.}5.$

Again include both diagram and textual notation.