Assigment $4(45$pts$)$

$2024$S$-$BAM$-2004-$OTT$01$ SQL and Data Analysis

Jared Capaldi

May $28,2024$

Question $1($Normalization$)(18$pts$)$

The teacher at the local nursery school wants to create a database of the children and their pets.

Some children are siblings and share the same pets. For example, Heather and Lola Jones are sisters that share Thomas the cat and Peter the rabbit. Also, two pets can have the same name, such as Rex the dog and Rex the cat. The teacher has created the following list:

$\backslash$table$[[$Student ID$,$Name,Age,Pet Name,Breed,Type$],[102,$Heather Jones,$8,\backslash$table$[[$Thomas$],[$Peter$]],\backslash$table$[[$Siamese$],[$Jersey Wooly$]],\backslash$table$[[$Cat$],[$Rabbit$]]],[105,$Rachel Smith,$10,\backslash$table$[[\backslash$table$[[$Fluff$],[$Rex$]]]],\backslash$table$[[$Persian$],[$Collie$]],\backslash$table$[[$Cat$],[$Dog$]]],[101,$Jimmy Johnson,$11,\backslash$table$[[$Baxter$],[$Mandy$]],\backslash$table$[[$Hound$],[$Siamese$]],\backslash$table$[[$Dog$],[$Cat$]]],[104,$Lola Jones,$10,\backslash$table$[[$Thomas$],[$Peter$]],\backslash$table$[[$Siamese$],[$Jersey Wooly$]],\backslash$table$[[$Cat$],[$Rabbit$]]],[106,$Peter Allen,$9,\backslash$table$[[$Baxter$],[$Ralph$]],\backslash$table$[[$Beagle$],[$Beagle$]],\backslash$table$[[$Dog$],[$Dog$]]],[103,$Lilly Sinclair,$11,\backslash$table$[[\backslash$table$[[$Rex$],[$Max$]]]],\backslash$table$[[$Bengal$],[$Siamese$]],\backslash$table$[[$Cat$],[$Cat$]]]]$

Complete the following:

Convert table to $1NF$ and create relational schema $($tabular and text format$)$: $(6$pts$)$

a$)$ Each attribute $($cell$)$ contains a single value.

b$)$ Identify the primary key.

Convert to $2$NF: $(6$pts$)$

a$)$ Identify the partial key dependencies.

b$)$ Convert tables to $2$NF and create relational schema $($tabular and text format$).$

Convert to $3$NF: $(6$pts$)$

a$)$ Eliminate transitive dependencies where non$-$key attributes are dependent on non$-$key attributes.

b$)$ Convert tables to $3$NF and create relational schema $($tabular and text format$)$