Assignment 2: Relational Algebra and SQL

Assignment 2: Relational Algebra and SQL
Mark = 70 marks. Weight=10%
This assignment will use two databases, ”Department-Store Database” and ”Drinker-Beer
Database”. Underlined fields are the attributes of primary keys. The records in the tables only
serve as examples. The tables may contain more records. For your own practices, more queries on
these databases can be found in the file ”sample-db-query” on Canvas.
Department Store Database
Employee relation:
eid name salary dept
111 Jane 8000 Household
222 Anderson 8000 Toy
333 Morgan 10000 Cosmetics
444 Lewis 12000 Stationery
555 Nelson 6000 Toy
666 Hoffman 16000 Cosmetics
Sales relation:
dept item
Stationery pen
Cosmetics lipstick
Toy puzzle
Stationery ink
Household disk
Sports skates
Toy lipstick
Types relation:
item color
pen red
lipstick red
pen black
puzzle black
ink red
ink blue
1
Drinker-beer Database
Frequents relation:
drinker bar
Ullman Manuel’s
Ullman Orchard Night
Ullman Faculty Clue
Ullman Dynasty
Graham Dynasty
Sam Manul’s
Sam Orchard Night
Smith Dynasty
Serves relation:
bar beer
Manuel’s Miller Lite
Manuel’s Tiger
Orchard Night Busch
Manuel’s Qindao
Faculty Club Tiger
Faculty Club Miller Lite
Dynasty Anchor
Likes relation:
drinker beer
Ullman Miller Lite
Ullman Tiger
Ullman Anchor
Jane Anchor
Sam Anchor
2
Question 1 (25 marks, 5 marks each). Express the following queries in Relational Algebra.
Both the correctness and the simplicity count.
1. List items available in both “red” and “blue”.
2. List the name of the employees making at least as much as “Jane”. If there are several
employees named ”Jane”, which Jane’s salary is used in this comparison in your answer?
3. Find the largest salary paid to any employees.
4. What departments sell every item with a red color.
5. What departments sell only items with a red color, in other words, what departments do not
sell any item with a non-red color.
Question 2 (25 marks, 5 marks each). Express the queries in Question 1 in SQL.
1. List items available in both “red” and “blue”.
2. List the name of the employees making at least as much as “Jane”. If there are several
employees named ”Jane”, which Jane’s salary is used in this comparison in your answer?
3. Find the largest salary paid to any employees.
4. What departments sell every item with a red color.
5. What departments sell only items with a red color, in other words, what departments do not
sell any item with a non-red color.
Question 3 (20 marks, 5 marks each) This question refers to the queries in Question 1.
(a) Express query 1 in SQL without using INTERSECT
(b) Express query 2 in SQL using nested query
(c) Express query 3 without using EXCEPT
(d) Express query 5 without using EXCEPT
3