% Created 2022-10-31 Mon 14:58
% Intended LaTeX compiler: pdflatex
\documentclass[11pt]{article}
\usepackage[utf8]{inputenc}
\usepackage[T1]{fontenc}
\usepackage{graphicx}
\usepackage{longtable}
\usepackage{wrapfig}
\usepackage{rotating}
\usepackage[normalem]{ulem}
\usepackage{amsmath}
\usepackage{amssymb}
\usepackage{capt-of}
\usepackage{hyperref}
\author{Logan Hunt}
\date{\today}
\title{Homework 4}
\hypersetup{
 pdfauthor={Logan Hunt},
 pdftitle={Homework 4},
 pdfkeywords={},
 pdfsubject={},
 pdfcreator={Emacs 28.2.50 (Org mode 9.5.5)}, 
 pdflang={English}}
\begin{document}

\maketitle
\tableofcontents


\section{Question One}
\label{sec:org7142899}
The number of super keys is the cardinality of the set of tuples of attributes that contain at least the clientId. In this case, that is:

\begin{enumerate}
\item \texttt{(clientId)}
\item \texttt{(clientId, name)}
\item \texttt{(clientId, phone)}
\item \texttt{(clientId, address)}
\item \texttt{(clientId, name, phone)}
\item \texttt{(clientId, name, address)}
\item \texttt{(clientId, phone, address)}
\item \texttt{(clientId, name, phone, address)}
\end{enumerate}

Therefore there are eight super keys.

\section{Question Two}
\label{sec:org808ee68}
There is only one primary key which is \texttt{(clientId, empId, packageId)}.

\section{Question Three}
\label{sec:orgdde212d}
Salesman has two candidate keys empId and name, if we assume that each salesperson has a unique name. If not, then empId is the only candidate key (therefore, the primary key!).

\section{Question Four}
\label{sec:org9bc5fbb}
\begin{verbatim}
select clientId, name, phone, address from Client where name="Peter Smith";
\end{verbatim}

\section{Question Five}
\label{sec:orgaaa81c5}
\begin{verbatim}
select videoCode from Video where videoLength < 2;
\end{verbatim}

\section{Question Six}
\label{sec:org55e6ae2}
Making the assumption that all addresses are formatted as "<street no> <street address>". For example "12345 John Blvd":

\begin{verbatim}
select siteCode, type, address, phone from Site where right(address, locate(' ', address)-1) = "University Dr";
\end{verbatim}

\section{Question Seven}
\label{sec:orgd1dc577}
\begin{verbatim}
select administrator.empId, administrator.name, administrator.gender from administers
       join administrator on administrator.empId=administers.empId
       where siteCode=111;
\end{verbatim}

\section{Question Eight}
\label{sec:orgbaea177}
\begin{verbatim}
select salesman.empId, client.clientId, client.name, client.phone from client
       join purchases on purchases.clientId = client.clientId
       join salesman on salesman.empId = purchases.empId
       where salesman.name="John";
\end{verbatim}

\section{Question Nine}
\label{sec:org67a8ee6}
\begin{verbatim}
select digital_display.serialNo from digital_display
       join locates on locates.serialNo=digital_display.serialNo
       join site on site.siteCode=locates.siteCode
       where site.siteCode = 112;
\end{verbatim}

\section{Question Ten}
\label{sec:org3aed2a8}
\begin{verbatim}
select distinct(digital_display.schedulerSystem) from digital_display
       join locates on locates.serialNo=digital_display.serialNo
       join site on site.siteCode=locates.siteCode
       where site.siteCode = 112;
\end{verbatim}

\section{Question Eleven}
\label{sec:org8771b4d}
\begin{verbatim}
select * from model where screenSize > 10 and weight < 3
\end{verbatim}

\section{Question Twelve}
\label{sec:org90164e0}
\begin{verbatim}
select digital_display.serialNo, digitial_display.modelNo, digitial_display.schedulerSystem from digitial_display
       join locates on locates.serialNo=digital_display.serialNo
       join site on site.siteCode=locates.siteCode
       where site.type="bar";
\end{verbatim}

\section{Question Thirteen}
\label{sec:org522e64a}
\#+BEGIN\textsubscript{SRC} sql
select empId, day from adm\textsubscript{work}\textsubscript{hours} where hours > 8;
\#+END\textsubscript{SRC} sql
\end{document}