\documentclass[12pt]{article}
\usepackage{amsfonts, amssymb, amsmath, comment}
\usepackage{times}
\usepackage{fancyhdr}
\usepackage{hyperref}
\usepackage{url}
\usepackage{listings}
\lstset{language=SQL}
%\usepackage{xypic}
%\usepackage{epsf}
%\usepackage{epsfig}
\usepackage{enumitem}
%\usepackage{comment}

\setlist[itemize]{nosep}
\setlist[enumerate]{nosep}

\setlength{\oddsidemargin}{0in}
\setlength{\evensidemargin}{0in}
\setlength{\textwidth}{6.5in}
\setlength{\textheight}{9.0in}
\setlength{\topmargin}{-0.5in}
\setlength{\headheight}{0in}
\parskip12pt
\parindent0pt
\pagestyle{fancy}
\lhead{CS 338 - Spring 2026}
\chead{CM A01}
\rhead{Due Friday, June 19, 11:59 PM EST}
\cfoot{\normalfont\medskip Copyright \copyright 2026}

\newcommand{\rmp}{\reversemarginpar\marginpar}

\providecommand{\SolutionOne}{}
\providecommand{\SolutionTwo}{}
\providecommand{\SolutionThree}{}
\providecommand{\SolutionFour}{}
\providecommand{\SolutionFive}{}
\providecommand{\SolutionSix}{}
\providecommand{\SolutionSeven}{}
\providecommand{\SolutionEight}{}
\providecommand{\SolutionNine}{}
\providecommand{\SolutionTen}{}

%\input xy
%\xyoption{all}

\begin{document}

\begin{itemize}
\item{
\textbf{Topic:} Relational Algebra
}
\item{
Assignments must be completed individually.
}
\item{
No late assignments will be accepted.
}
\item{
When writing Relational Algebra expressions, follow the formatting style in the lectures.
If you write your answers without proper formatting, it will be difficult to understand your answers.
This will increase the chances of your answers being misinterpreted, and not earning full points.
}
\item{
The questions here are based on the Enrollment database whose schema is listed on the last page.
Write relational algebra expressions to answer the following questions.
}
\item{
Submit your completed solutions to \textbf{Crowdmark}.
}
\item{
\textbf{Rubric:}
Each question is worth 3 points, and will be graded according to the following rubric:
\begin{itemize}
\item{
3 points: Good (no mistake or only a very small mistake)
}
\item{
2 points: OK (mostly correct)
}
\item{
1 point: Poor (mostly wrong but has some merit)
}
\item{
0 points: Wrong (totally off or no answer)
}
\end{itemize}
}
\end{itemize}

\begin{enumerate}

\item \label{QuestionOne}
Find \rmp{[3]}students who never took more than one course from the same instructor.
That is, every course that they took (including repeated courses if any) had a different instructor.
The result should list student name and ID.
\SolutionOne

\newpage

\item \label{QuestionTwo}
Find \rmp{[3]}course(s) offered in W26 for which all the students were from the same major, or the course used textbook(s) published by ``Pearson Publishing''.
The result should display course name and course number.
\SolutionTwo

\newpage

\item \label{QuestionThree}
In \rmp{[3]}the Math Faculty, a student can attempt the same course at most three times.
We need to give a friendly reminder to students who are attempting the same course for the third time this term (S26).
Write a query to find these students.
The result should display their name, SID and major.
Assume that the policy has now been adopted by all Faculties and applied to all students.
\SolutionThree

\newpage

\item \label{QuestionFour}
Find \rmp{[3]}the publisher(s) who has at least one book adopted by each department except the AMATH department (i.e. ZERO books have been adopted by courses offered by AMATH department).
\SolutionFour

\newpage

\item \label{QuestionFive}
Find \rmp{[3]}out which of the following majors has the most students: C\&O, AMATH or ITM.
The result should be displayed in the provided format below.
The first row contains the program names.
The second row contains the number of students in each program.
For example,
\\
\begin{tabular}{|l|l|l|}
\hline
C\&O & AMATH & ITM \\
\hline
35 & 67 & 31 \\
\hline
\end{tabular}

\textbf{Hint:} Use the cross-product.
\SolutionFive

\newpage

\item \label{QuestionSix}
Find \rmp{[3]}the publisher(s) who has at least 10 different books adopted by courses offered by the CS department, but NONE adopted by the STAT department.
\SolutionSix

\newpage

\item \label{QuestionSeven}
Find \rmp{[3]}students who have earned at least 5 credits (in 2A or beyond) but have yet to pass the first communication requirement.
Assume each course is 0.5 credit and the only courses in the first communication requirement are ENGL109 or SPCOM100.
Note that a student might have taken the same course twice and passed both times.
This counts as 0.5 credit, not 1 credit.
Say John took MATH135 twice and got 50 and 65. John earned 0.5 credit from MATH135.
Jane took MATH135 and got 55 and is currently taking it again in F24.
Jane has also earned 0.5 credit from MATH135. The result should display student name, SID and E-mail.
\SolutionSeven

\newpage

\item \label{QuestionEight}
For \rmp{[3]}pairs of students who are in the same major, find the pair(s) who took classes together (same course in the same term) the most frequently (across all majors).
Note that (A, B) and (B, A) is the same pair, and only one should be displayed.
The result should show the SID.
\SolutionEight

\newpage

\item \label{QuestionNine}
Find \rmp{[3]}the Math/BA student(s) with the second highest average.
The result should display their name, SID and major.
\SolutionNine

\newpage

\item \label{QuestionTen}
Find \rmp{[3]}the most difficult course(s) in W26.
The difficulty of a course is estimated by a weighted factoring formula:
\begin{displaymath}
(Number \ of \ failures * 0.6 + Number \ of \ WD * 0.2 - \ Class \ Average * 0.2).
\end{displaymath}
The higher this number, the more difficult the course is.

\noindent
\textbf{Hint:}
You can use arithmetic in the projection operator $(\pi)$ as follows:\\
Say we have $R(AB), \ R1(C) \leftarrow \pi_{A-B}(R)$.
This produces a new relation \lstinline!R1! having a column \lstinline!C!.
Each row in \lstinline!C! is the difference between \lstinline!A! and \lstinline!B! in \lstinline!R!.

\noindent
The result should display the Course\#.
\SolutionTen

\end{enumerate}

\newpage

\noindent
\textbf{The Enrollment Database}

\noindent
The following relations for a database keep track of student enrollment in courses and the books adopted for each course.

\noindent
\textbf{Assumptions:}

\begin{itemize}
\item{
Examples of Course\#: CS338, MATH135;
}
\item{
Examples of Dept: CS, ACTSC, AMATH, ENGL;
}
\item{
Examples of Major: CS, Math/BA, SE, STAT;
}
\item{
Examples of Term: F24, W24;
}
\item{
For students who are taking a course but have yet to receive a grade, the grade will be recorded as NA;
}
\item{
For students who enroll into a course but drop it midway through, the grade will be recorded as WD;
}
\item{
A grade below 50 is a failure;
}
\item{
Publisher is unique.
}
\end{itemize}

\noindent
\textbf{Relations:}

\begin{itemize}
\item{
Student(\underline{SID}, Name, Major, E-mail, Bdate)
}
\item{
Course(\underline{Course\#}, Cname, Dept)
}
\item{
Enroll(\underline{SID, Course\#, Term}, Grade, InstructorID)
}
\item{
Textbook(\underline{Course\#, Term, ISBN})
}
\item{
Book(\underline{ISBN}, Title, Publisher, Author)
}
\end{itemize}

\noindent
Note that in some question(s), you might consider using the \lstinline!Count-Distinct(a)! aggregation function instead of \lstinline!Count(a)!.
The former counts the number of \textbf{distinct} values in attribute \lstinline!a!.

\end{document}