Homework 2
Contents
Instructions
This homework should be uploaded to Canvas by 11:59pm on Sunday, September 28. All submissions should be in PDF format (if you use Word, just 'PDF' in the 'Save as' dialog). Be sure to list who you've worked with, if anyone.
Questions
1. (10 pts.) Suppose you have the following relations:
| EmployeeID | LastName | FirstName |
|---|---|---|
| 1 | Jones | Janet |
| 2 | Smith | John |
| 3 | Jackson | George |
| 4 | Miller | Lou |
| EmployeeID | PositionTitle | PositionLevel |
|---|---|---|
| 1 | Engineer | II |
| 2 | Engineer | I |
| 3 | Manager | III |
| 4 | Manager | I |
| PositionTitle | PositionLevel | Base |
|---|---|---|
| Engineer | I | 50000 |
| Engineer | II | 55000 |
| Engineer | III | 60000 |
| Manager | I | 60000 |
| Manager | II | 65000 |
| Manager | III | 75000 |
| EmployeeID | Bonus |
|---|---|
| 2 | 2500 |
| 4 | 4500 |
Write the relational algebra expression to get the salary base for Janet Jones.
2. (10 pts.) Based on the relations from Question 1, write the relational algebra expression to find all employees receiving a bonus.
3. (10 pts.) Based on the relations from Question 1, write the relational algebra expression to find all position titles that have at least one level with a base salary greater than $60,000.
4. (10 pts.) Based on the relations from Question 1, what does the following expression return?
$$ {\large\pi}_{\textrm{EmployeeID}} \left( \textrm{Bonuses} \bowtie \left( {\large\sigma}_{\textrm{Base} < 60000} \left(\textrm{Positions} \bowtie \textrm{SalaryBases}\right) \right)\right)$$5. (10 pts.) Based on the relations from Question 1, what does the following expression return?
$$ {\large\pi}_{\textrm{PositionTitle}} \left( {\large\sigma}_{\textrm{Base} < 60000} \textrm{SalarayBases} \cap {\large\sigma}_{\textrm{Base} > 54000} \textrm{SalarayBases} \right)$$ (Back to top)