Assignment Title: Database Normalization Question $2.$

Unnormalized Relation:

Employee$\_$Project$\_$Assignment

Instructions:

$1.$ Normalization Tasks:

o Analyze the Given Relation: Start with the provided unnormalized relation.

Identify any redundancy, partial dependencies, and transitive dependencies.

o Apply Normalization: Transform the unnormalized relation through $1$NF$,$

$2$NF$,$ and $3$NF$.$

$2.$ Deliverables:

o A document containing:

$$ The original unnormalized relation.

$$ The relational schema in $1$NF$,$ including a brief explanation.

$$ The relational schema in $2$NF$,$ including a brief explanation.

$$ The relational schema in $3$NF$,$ including a brief explanation.

$$ A summary of the normalization process and any observations about

the improved database design.

Steps for Normalization:

o First Normal Form $(1$NF$)$: Ensure that the relation has a primary key and

that all attributes contain only atomic $($indivisible$)$ values. Remove any

repeating groups if they exist.

o Second Normal Form $(2$NF$)$: Remove partial dependencies; that is$,$ ensure

that all non$-$key attributes are fully functionally dependent on the entire

primary key.

o Third Normal Form $(3$NF$)$: Remove transitive dependencies; ensure that

non$-$key attributes are not dependent on other non$-$key attributes.

Additional Guidelines:

o Primary Key Identification: Identify the primary key for each normalized

relation.

o Functional Dependency Analysis: Clearly list and analyze the functional

dependencies at each normalization stage.

o Schema Representation: Use proper notation to represent the relations and

their attributes.

Grading Criteria:

$$ Correctness: Accuracy in the application of normalization principles.

$$ Clarity: Clear explanations and logical reasoning for each normalization step.

$$ Completeness: Comprehensive documentation of the normalization process.

$$ Presentation: Neat and organized presentation of the normalized relations.

Question $1.$

Unnormalized Relation:

Student$\_$Course$\_$Enrollment

$\backslash$table$[[\backslash$table$[[$Student$],[$ID$]],\backslash$table$[[$Student$\_$Na$],[$me$]],\backslash$table$[[$ Course$\_$Co $\_($ de $)]],\backslash$table$[[\backslash$table$[[$Course$\_$Ti$],[$tle$]]]],\backslash$table$[[\backslash$table$[[$Instructor$\_$Na$],[$me$]]]],\backslash$table$[[$Instructor$\_$Off$],[$ice$]],\backslash$table$[[$Grad$],[$e$]]],[1001,$Alice Smith,CS$101,$Intro to CS$,$Dr$.$ Jones,Room $210,$A$],[1002,$Bob Brown,CS$101,$Intro to CS$,$Dr$.$ Jones,Room $210,$B$],[1001,$Alice Smith,MATH$201,$Calculus I,Dr$.$ Lee,Room $305,$A$-],[1003,$Carol White,CS$102,\backslash$table$[[$Data$],[$Structures$]],$Dr$.$ Martin,Room $220,$B$+],[1002,$Bob Brown,MATH$201,$Calculus I,Dr$.$ Lee,Room $305,$B$]]$