\documentclass[11pt]{article}
\usepackage[margin=1in]{geometry}

\setlength{\parindent}{0pt}
\setlength{\parskip}{1ex}
\pagestyle{empty}

% question style
\usepackage[svgnames,table]{xcolor}
\newcommand{\Q}[1]{\textcolor{SlateBlue}{\textit{#1}}\medskip}

% compact bullet lists
\usepackage{enumitem}
\newcommand{\bull}{\\\hspace*{1.5em}$\bullet$\hspace*{1.2ex}}

% primary/foreign keys
\newcommand{\pk}[1]{\underline{\smash{#1}}}
\newcommand{\fk}[1]{\textit{#1}}

% quote indenting
\renewenvironment{quote}
{\list{}{
    \partopsep=0em
    \leftmargin=1em
    \rightmargin=1em
}\item\relax}{\endlist}

%------------------------------------------------------------------------------%
\begin{document}

\large
CS 374 \hfill Spring 2022
\begin{center}
\textbf{Normalization Example}
\end{center}
\hrule
\normalsize

%------------------------------------------------------------------------------%
\subsection*{Question 6.0 (not in the textbook)}
\Q{A movie database records the title, actors, and genre of each movie. Normalize the following relation, and indicate the primary and foreign key attribute types.}

\Q{Movie(movieId, title, year, actor(actorId, charId, realName, charName, creditOrder), genreId, genre)}

\Q{Note: A movie can have multiple actors but only one genre.}

\subsubsection*{First Normal Form}

1NF is violated because actor is a composite attribute type. It is also multivalued attribute type. The relation can be brought into 1NF as follows:

\begin{quote}
Movie(\pk{movieId}, title, year, genreId, genre)

Actor(\pk{\fk{movieId}}, \pk{actorId}, \pk{charId}, realName, charName, creditOrder)
\end{quote}

\subsubsection*{Second Normal Form}

Actor is not in 2NF because realName is partially dependent on (movieId, actorId, charId) -- it only depends on actorId. The same holds for charName and charId. The relation can be brought into 2NF as follows:

\begin{quote}
Movie(\pk{movieId}, title, year, genreId, genre)

Actor(\pk{actorId}, realName)

Char(\pk{charId}, charName)

Cast(\pk{\fk{movieId}}, \pk{\fk{actorId}}, \pk{\fk{charId}}, creditOrder)
\end{quote}

\subsubsection*{Third Normal Form}

The relation Movie is not in 3NF since we have a transitive functional dependency from movieId to genre via genreId. It can be brought into 3NF as follows:

\begin{quote}
Movie(\pk{movieId}, title, year, \fk{genreId})

Actor(\pk{actorId}, \pk{realName})

Char(\pk{charId}, \pk{charName})

Cast(\pk{\fk{movieId}}, \pk{\fk{actorId}}, \pk{\fk{charId}}, creditOrder)

Genre(\pk{genreId}, genre)
\end{quote}

%------------------------------------------------------------------------------%
\end{document}
