Lab: Relational Algebra and Calculus Practice
This lab is designed to help you practice writing queries using the relational algebra and tuple calculus. You will also practice typesetting using LaTeX.
Introduction
I will start lab with a short introduction to LaTeX.
Instructions
You are welcome to work with a partner. If you have questions, please ask the instructors for help!
Try to write as many of the following queries as you can during lab time. Halfway through class, switch to the tuple relational calculus queries, so you have time to try both.
Symbol Reference
Each of these symbols is embedded using MathJax. To view the LaTeX for each, simply right-click then choose “Show Math As,” “TeX Commands.”
Relational Algebra Symbols
| Symbol | Meaning |
|---|---|
| \(\sigma\) | Select |
| \(\pi\) | Project |
| \(\rho\) | Rename |
| \(\cup\) | Union |
| \(\cap\) | Intersect |
| \(\times\) | Cross product |
| \(\div\) | Division |
| \(\bowtie\) | Join |
| \(\mathfrak{I}\) | Aggregate function |
Relational Calculus Symbols
| Symbol | LaTeX |
|---|---|
| \(\forall\) | Universal quantifier |
| \(\exists\) | Existential quantifier |
Relational Algebra Practice (20 minutes)
Specify the following queries on the COMPANY relational database schema shown below using relational algebra.
- List the names of all employees who have a dependent with the same first name as themselves.
- Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ProductX project.
- Find the names of all employees who are directly supervised by ‘Franklin Wong’.
- For each project, list the project name and the total hours per week (by all employees) spent on that project.
- Retrieve the names of all employees who work on every project.
- Retrieve the names of all employees who do not work on any project.
- For each department, retrieve the department name and the average salary of all employees working in that department.
- Retrieve the average salary of all female employees.
Tuple Relational Calculus Practice (20 minutes)
Using the same COMPANY schema, specify the following queries using the tuple relational calculus.
- List the names of all employees who have a dependent with the same first name as themselves.
- Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ProductX project.
- Find the names of all employees who are directly supervised by ‘Franklin Wong’.
- Retrieve the names of all employees who work on every project.
- Retrieve the names of all employees who do not work on any project.
Typeset with LaTeX (20 minutes)
Use LaTeX to typeset at least one of your relational algebra queries and one of your relational calculus queries.
If you don’t have LaTeX installed locally, I recommend using either Overleaf or the equation editor in Microsoft Word or Apple Pages.
Note: When working with large documents, I recommend using a local LaTeX installation, since local builds are faster than Overleaf. Overleaf is fast for small documents (e.g., this assignment). Typically, I host my projects on Overleaf, and I clone them locally using Git. This gives me fast local builds, and the convenience of Overleaf’s collaboration features.
Submit
Upload a PDF containing:
- Your names
- Your relational algebra queries
- At least one relational algebra query typeset in LaTeX
- Your relational calculus queries
- At least one relational calculus query typeset in LaTeX
This will only be graded for completion, as part of your attendance and participation grade.