Microsoft Word – CMPT 354 Assignment 1.docx
1
CMPT 354 Assignment 1
Due: 11:59 pm, October 12, 2021
100 points in total
Please submit your assignment in Coursys. All answers should be typed in a PDF file.
Every student has to complete the assignment independently. While you are encouraged to learn
through discussion with the instructor, the TAs and the peer students, any plagiarisms are serious
violation of the university’s academic integrity policy. We have absolutely zero tolerance of such
behavior. Using any tutoring platforms for this course, such as Course Hero or 51zuoyejun is
strictly NOT ALLOWED. If detected, a student using this kind of paid services in this course or
uploading materials of this course to such platforms will be regarded as dishonest and will be
reported to the university as a plagiarism case.
This assignment covers the first 5 chapters of the textbook.
Question 1 (35 points)
This question is about relational algebra expressions.
Consider the following employee database.
employee (person_name, street, city)
works (person_name, company_name, salary)
company (company_name, city)
Write a relational algebra to express each of the following queries. You are NOT allowed to use
any user-defined functions, like max(), min(), or count().
1. Find the name of companies in the city where employee “John Doe” lives.
2. Find employee names who work in one city and live in a different city.
3. Find the names of companies that do not have any employees yet.
4. Find the names of companies that do NOT have any employees whose salary is $100,000
or more.
5. Find pairs of employee names (X, Y) such that X and Y work in the same company and live
in the same street and city.
6. Find the name of the employee who has the highest salary. You are NOT allowed to use
functions like max() or min(). If there are more than one such employees, output all of
them.
7. Find the name of the employee who has the second highest salary. You are NOT allowed
to use functions like max() or min(). If there are more than one such employees, output
all of them. The second highest salary is the second largest number in attribute salary of
relation works.
2
Question 2 (40 points)
This question is about writing SQL queries.
Again, consider the employee database in Question 1. Write a SQL query to express each of the
following queries.
1. Find the companies that have employees in all cities in table employee.
2. Find the name of the employee who has the second highest salary. If there are more than
one such employees, output all of them. The second highest salary is the second largest
number in attribute salary of relation works.
3. Find the companies that have the largest number of employees in a least 3 cities.
4. Find the company pairs (x, y) such that x and y have employees in the same cities and, in
each of such cities, x has more employees than y.
5. For each city, find the company such that the employees living in the city and working for
the company have the lowest average salary among all the companies that have
employees in the city. If a company y does not have any employee in a city, the company
should not be considered for that city.
6. Find the city pairs (x, y) such that for each company c that has employees in both x and y,
some employee in x working for c has a salary higher than all those in y working for c.
7. Find those companies whose average employee salary is higher than the overall average
employee salary in the city where the company is located.
8. Find the companies whose average salary is higher than the highest salary of the company
with the lowest average salary.
Question 3 (25 points)
This question is about views and advanced SQL features.
Again, consider the employee database in Question 1.
1. Define a view total_employees(comp, city, num_emp) that gives the number of
employees of company comp in a city.
2. SQLite does not support right outer join and full outer join. Can you implement full outer
join using left outer join? Please use one example to demonstrate your answer.
Specifically, suppose we have two tables, s (a, b) and t(b, c), can you rewrite the following
query using only left outer join?
select a, b, c
from s natural full outer join t
3. Question 4.16 in the textbook (page 179).
4. Question 4.17 in the textbook (page 179).
5. Question 4.19 in the textbook (page 179-180).