- Assigned: 10/4
- Due: 10/16 at 10:00AM via Gradescope
- worth 3.75% of your grade
- 'You can answer relational algebra with sub steps.' is added to the description of 1. Relational Algebra.
- 'Make sure your SQL can be executed.' is added to the description of 3. SQL.
- 2.5 T1 ⨝T1.A<T2.B (σB<=2(T2))
- 'Simply answer NULL if you believe the resulting table is empty or there are some errors with the expressions' is added to the description of 2. More Relational Algebra.
1. Relational Algebra
(2 points each, 4 points total)
Consider the simplified schema below (primary keys are in bold):
liquors(lid, name, price, manufacturer)
sales(month, seller, liquors, county, quantity)
Note: sales(liquors) references liquors(lid), and month is a text field (e.g. January, February).
Construct relational algebra for the following queries, you can answer relational algebra with sub steps:
Q1: Find the names of liquors that had at least one sale in "Clarke" or "Marshall" counties for the month of November.
Q2: Find the names of manufacturers that sold at least three different liquors during the month of January in "Marshall" county.
Note: Please use only operators covered in class. For Q2, it might help to think about the "at least two" case and then expand it to "at least three".
2. More Relational Algebra
(1 point each, 6 points total)
Simply answer NULL if you believe the resulting table is empty or there are some errors with the expressions.
Given the following tables
Write the result table for the relational algebra expressions:
- T1 × πA(T1)
- T1 ⨝T1.B=T2.C T2
- T1 − (T1 ∩ T2)
- T1 ⨝T1.A<T2.B (σB<=2(T2))
- T1 ⨝ (σB=c(T2))
Write the following relational algebra expressions in SQL. Make sure your SQL can be executed.
Note: You don't need to know the full schema to translate these statements.
- Student × πage(Student)
- Student ⨝Student.name=Teacher.name Teacher
- Student − (Student ∩ Teacher)
- (σyear=2018(Student) )⨝Student.age<Teacher.age (σname=engene(Teacher))
- Student ⨝ (σage<=50(Teacher))
Submit a PDF file containing your answers to Gradescope.
Consider using a text editor. Scanned documents are fine, but make sure your answers are readable.