\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{EER Mapping Example}
\end{center}
\hrule
\normalsize

%------------------------------------------------------------------------------%
\subsection*{Question 6.3, Part A}

\Q{Given the EER model for an electricity market (on Page 144): \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*{Producer, Reseller}

Producer(\pk{ProdName}, Location)

Reseller(\pk{BrandName}, NumEmployees, Manager)

Sells(\pk{\fk{ProdName}}, \pk{\fk{BrandName}})
\bull ProdName references Producer
\bull BrandName references Reseller

\subsubsection*{Customer (all types)}

Customer(\pk{CustID}, ContractType, \fk{ContractWith})
\bull ContractWith references BrandName in Reseller

Residential(\pk{\fk{CustID}}, Household-size)
\bull CustID references Customer

Business(\pk{\fk{CustID}}, VAT-number)
\bull CustID references Customer

\subsubsection*{Power Plant, Regulator}

Regulator(\pk{Province}, Manager)

PowerPlant(\pk{PlantID}, MWH, Location, \fk{Province})
\bull Province references Regulator
    
Owns(\pk{\fk{ProdName}}, \pk{\fk{PlantID}})
\bull ProdName references Producer
\bull PlantID references PowerPlant

\subsubsection*{Gas, Wind, Nuclear}

Gas(\pk{\fk{PlantID}}, Pipelines)
\hspace{2.5em} PlantID references PowerPlant

Wind(\pk{\fk{PlantID}}, Mills)
\hspace{3.5em} PlantID references PowerPlant

Nuclear(\pk{\fk{PlantID}}, Reactors)
\hspace{1em} PlantID references PowerPlant

%------------------------------------------------------------------------------%
\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 A contract between a customer and an electricity reseller could be limited in time.
\item The size of a household could change from year to year.
\item The number of windmills of a power plant could change.
\end{itemize}

\medskip

Consistency across multiple relationship types:
\begin{itemize}[nosep]
\item The model cannot enforce that the power plant of an electricity producer that is located in a certain province is regulated by the local regulator of that same province.
\item The model cannot enforce that one person is not both the manager of an electricity reseller and of a local regulator at the same time.
\end{itemize}

\medskip

Integrity rules:
\begin{itemize}[nosep]
\item Household size should not be larger than 15.
\item A VAT number should have a fixed format of ten digits.
\item The province of the local regulator should be a valid province of the country we are creating this model for.
\end{itemize}

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

\begin{itemize}[nosep]
\item The disjointness of the specialization of Power Plant into Gas, Wind, and Nuclear cannot be enforced.
\item The disjointness of the specialization of Customer into Residential and Business cannot be enforced.
\item The minimum cardinality of 1 in the relationship type Owns between Electricity Producer and Power Plant cannot be enforced.
\item The minimum cardinality of 1 in the relationship type Sells between Electricity Producer and Electricity Reseller cannot be enforced.
\end{itemize}

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