Format: individual (without the assistance of a third party or AI)
Estimated Time to Complete: 90 min
Read the complete assignment before you start.
In this assignment, learners will have the opportunity to express queries against a relational datastore in an abstract and language or database independent manner using both relational algebra and tuple relational calculus. In addition, learners will explore how relational algebra contrasts with SQL.
Completing this assignment, will allow you to
Prior to working on this assignment, review these lessons and refer to them during the assignment:
You must write the expressions using LaTeX. Latex is a key skill that scientists must master as it is the most common way to write scientific articles. LaTeX expressions can be embedded in R Notebooks and even in Google Docs. It is often best to build the LaTeX equation in an editor and then paste into your answer document. Find an editor through a Google search; learn LaTeX (or at least how to write equations) through the many online tutorials.
For example, the LaTeX expression
\sigma\_{(salary \geq 250000) \lor (salary \leq 100000)}(Employee)
produces:
\(\sigma_{(salary \geq 250000) \lor (salary \leq 100000)}(Employee)\)
Or, this LaTeX expression
{}\_{<cid>}\\mathfrak{F\_{\<AVG,MAX\>}(Employee)
produces:
\({}_{<cid>}\mathfrak{F}_{<AVG,MAX>}(Employee)\)
Consider downloading the sources for the lessons above and inspecting the .Rmd files to see how the various expressions were written in LaTeX.
Read through all of the questions first and then work through them in any sequence. Primary keys are underlined. Foreign keys are italicized. Assume all joins are natural joins.
Consider the relational schema below:
Write a relational algebra expression that satisfies this query:
Find the distinct names of all courses that meet during the “B” block in the “Spring 2023” term.
Consider the relational schema below:
Write a relational algebra expression that satisfies this query:
Find the distinct names of all students that took at least nine credits during the “Spring 2023” term.
Consider the relational schema below:
Write a relational algebra expression that satisfies this query:
Find the distinct names of all students who major in either “Accounting” or “Business” and who scored less than 80% in either course 91.274 or in course 14.102 (using cid).
Consider the relational schema below:
Write a relational algebra expression that satisfies this query:
Find the number of students in each course during the “Spring 2023” term assuming that a course can have several sections (if a course has several sections then the number of students enrolled in the course is the sum of the students in each section of the course).
Consider the relational schema below:
Write a relational algebra expression that satisfies this query:
How many courses are offered during the “Spring 2023” term that have more than one section?
Consider the relational schema below:
Write a relational algebra expression that satisfies this query:
List the names and majors of all students in the college “Khoury” who have a GPA below 3.0 and are not on coop.
Consider the relational schema below:
Write a tuple relational calculus expression that satisfies this query:
Find the distinct names of all courses that have at least 2 but no more than 5 credit hours .
Consider the relational schema below:
Write the equivalent tuple relational calculus expression for the SQL statement below:
SELECT sname, gpa
FROM Students
WHERE plusOne = T AND gpa < 2.5;Consider the relational schema below:
Write the equivalent relational algebra expression for the SQL statement below:
SELECT sname, gpa
FROM Students
WHERE plusOne = T
AND (gpa BETWEEN 2.99 AND 4.0);Consider the relational schema below:
Write a single equivalent SQL statement for the two relational algebra expressions below. You do not have to implement the SQL in an actual database:
\(\rho_{KhourySections}(\sigma_{college='Khoury'}(Courses \bowtie Sections))\)
\(\pi_{term,cname,room}(\sigma_{block='G' \lor block='H'}(KhourySections))\)
Submit a single PDF containing your name, term, course, instructor’s name, and all of your answer in a readable and professional form. You expressions can either be embedded images or (ideally) LaTeX expression. We recommend that you use markdown with embedded LaTeX. Clearly identify the question you are answering and note any assumptions you are making.
None yet.