\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}

% Python colors based on IDLE
\definecolor{bgcolor}{HTML}{FAFAFA}
\definecolor{builtin}{HTML}{900090}
\definecolor{comment}{HTML}{DD0000}
\definecolor{keyword}{HTML}{FF7700}
\definecolor{numbers}{HTML}{A9A9A9}
\definecolor{strings}{HTML}{00AA00}

% syntax highlighting
\usepackage{textcomp}
\usepackage{listings}
\lstset{
    language=python,
    basicstyle=\ttfamily,
    backgroundcolor=\color{bgcolor},
    commentstyle=\color{comment},
    keywordstyle=\color{keyword},
    numberstyle=\scriptsize\color{numbers},
    stringstyle=\color{strings},
    columns=fullflexible,
    keepspaces=true,
    numbers=left,
    showlines=true,
    showstringspaces=false,
    tabsize=4,
    upquote=true,
    deletekeywords={print},
    morekeywords=[2]{print},
    keywordstyle=[2]{\color{builtin}}
}

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

\large
CS 374, HW3 \hfill Due: 02/13/2022
\begin{center}
\textbf{Type Your Name Here}
\end{center}
\hrule
\normalsize

%------------------------------------------------------------------------------%
\newpage
\subsection*{Question 6.4, Part A}

\Q{Given the EER model for the airline business (on Page 145): \smallskip
\bull Map the EER model to a relational model representation.
\bull Clearly indicate the primary and foreign key relationships.
(Use the commands \textbackslash pk and \textbackslash fk.)}

\subsubsection*{Airport, Plane, Flight}

Type your answer here.

\subsubsection*{Employee, \ldots, Assignment}

Type your answer here.

\subsubsection*{Customer, Ticket, Valid}

Type your answer here.

%------------------------------------------------------------------------------%
\newpage
\subsection*{Question 6.4, Part B}

\Q{Given the EER model for the airline business (on Page 145): \smallskip
\bull Discuss semantics that cannot be enforced by the EER model.
\bull Discuss the possible loss of semantics in the relational model.}

\subsubsection*{The following semantics cannot be enforced by the EER model:}

Temporal aspects:
\begin{itemize}[nosep]
\item Type your answer here.
\end{itemize}

\medskip

Consistency across multiple relationship types:
\begin{itemize}[nosep]
\item Type your answer here.
\end{itemize}

\medskip

Integrity rules:
\begin{itemize}[nosep]
\item Type your answer here.
\end{itemize}

\subsubsection*{The following semantics were lost in the relational mapping:}

\begin{itemize}[nosep]
\item Type your answer here.
\end{itemize}

%------------------------------------------------------------------------------%
\newpage
\subsection*{Question 6.1, Part A}
\Q{Normalize the following relation into 3NF. Show all your work, including intermediate steps through 1NF and 2NF. Indicate the primary and foreign key attributes. Justify your answer.}

\Q{R(ISBN, title, author(name, date\_of\_birth), publisher(name, address(streetnr, streetname, zipcode, city)), pages, price)}

\Q{See the textbook (Page 144) for the assumptions about $R$.}

\subsubsection*{First Normal Form}

Type your answer here.

\subsubsection*{Second Normal Form}

Type your answer here.

\subsubsection*{Third Normal Form}

Type your answer here.

%------------------------------------------------------------------------------%
\newpage
\subsection*{Question 6.1, Part B}

\Q{Suppose that a book can have multiple publishers. How would your answer to the previous question change? Where would you put the attribute type ``number\_of\_copies''?  Justify your answer.}

\subsubsection*{Third Normal Form}

Type your answer here.

%------------------------------------------------------------------------------%
\newpage
\subsection*{Question 6.2}
\Q{Normalize the following relation into BCNF. Show all your work, including intermediate steps through 1NF and 2NF. Indicate the primary and foreign key attributes. Justify your answer.}

\Q{R(flightnumber, flighttime, airline(airlinename), passenger(passengername, gender, date\_of\_birth), pilot(pilotname, gender, date\_of\_birth), departure\_city, arrival\_city, airplane(planeID, type, seats))}

\Q{See the textbook (Page 144) for the the assumptions about $R$.}

\subsubsection*{First Normal Form}

Type your answer here.

\subsubsection*{Second Normal Form}

Type your answer here.

\subsubsection*{Boyce--Codd Normal Form}

Type your answer here.

%------------------------------------------------------------------------------%
\newpage
\subsection*{Python Exercise (courses.py)}
\Q{Write a Python script that extracts course information from MyMadison and prints it to the screen in CSV format. See HW3 on the course website for more instructions.}

\lstinputlisting{classes.py}

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