\documentclass[12pt]{article}

\usepackage{fullpage,amsmath,amsfonts}

%\usepackage{array}
%\usepackage{booktabs} % For better looking tables
\usepackage{tabularray}

\begin{document}

\title{Foundations of Information Systems}
\author{Winter Semester 2024--25, Exercise 12}
\date{For discussion on Tuesday, February 4, 2024}
\maketitle

\begin{enumerate}


\item You are given the following relational database schema:
\begin{quote}
\ttfamily
STUDENT(SNO, NAME) \\
ENROLL(CNO, SNO, GRADE) \\
COURSE(CNO, DEPT)
\end{quote}
\begin{enumerate}
\item Underline the primary keys and dashed-underline all foreign
keys.
\item 
Write the following queries in relational algebra and SQL.
\begin{enumerate}
\item Find the names of all students failing a course (\texttt{GRADE='F'}).
\item Find the names of all students taking a course
in the Math Department (\texttt{DEPT='MATH'}).
\end{enumerate}
\end{enumerate}

\item Consider the following bank customer database with relations
``Customers'', ``Accounts'', and ``Branch''.
\begin{center}
\begin{tblr}{|llll|}
    \hline
    \SetCell[c=4]{c} \textbf{Customers}\\
    \hline
    CustomerID & Name & Address & Phone \\
    \hline
    001 & John Doe & 123 Elm Street & 555-1234 \\
    002 & Jane Smith & 456 Pine Street & 555-5678 \\
    003 & Bob Brown & 789 Oak Street & 555-9012 \\
    \hline
\end{tblr}
\end{center}

\begin{center}
\begin{tblr}{|lllll|}
    \hline
    \SetCell[c=5]{c}{ \textbf{Accounts}} \\
    \hline
    AccountNumber & CustomerID & BranchID & Balance & Type \\
    \hline
    A0001 & 001 & B01 & \$5,000 & Checking \\
    A0002 & 002 & B02 & \$8,000 & Savings \\
    A0003 & 003 & B01 & \$2,500 & Savings \\
    A0004 & 003 & B01 & \$3,500 & Checking \\
    \hline
\end{tblr}
\end{center}

\begin{center}
\begin{tblr}{|llll|}
    \hline
    \SetCell[c=4]{c}  {\textbf{Branches}} \\
    \hline
    BranchID & BranchName & Manager & Location \\
    \hline
    B01 & Downtown & Alice Johnson & 123 City Square \\
    B02 & Uptown & Gary White & 456 Suburb Road \\
    \hline 
\end{tblr}
\end{center}

State the relations returned by the following relational algebra
expressions, as a table.
\begin{enumerate}
\item $\displaystyle \pi_{\text{Type}}(\text{Accounts})$
\item $\displaystyle \sigma_{\text{BranchID}=\text{`B01'}}(\text{Accounts})$
\item $\displaystyle \text{Customers} \bowtie \text{Accounts}$
\item $\displaystyle \pi_{(\text{Name},\text{BranchName})}
(\text{Customers} \bowtie \text{Accounts} \bowtie \text{Branches})$
\end{enumerate}

\item Prove the following:
\begin{enumerate}
\item If given relations $r(R)$, $s(S)$, and $t(T)$, then
\[
  r \bowtie (s \bowtie t) = (r \bowtie s) \bowtie t \,.
\]
\item Given relations $r(R)$ and $s(S)$, and attributes $N, M \in R$,
then
\[
  \sigma_{N=M}(r \bowtie s) = \sigma_{N=M}(r) \bowtie s \,.
\]
\end{enumerate}

\end{enumerate}



\end{document}
