Unformatted text preview: Name: ___________________________ USC ID: ______________________ Quiz 9: Relational Algebra (10 points. 15 minutes)
Consider again the following tables that you have seen in the last quiz. Note that attributes in the
primary key of each table are underlined. Write a relational algebra expression (or expression tree) for
each question below. Assume that relational algebra does NOT support having condition for group-by.
Laptop(SerialNo, OperatingSystem, HardDrive)
Product(SerialNo, Brand, Model, Price)
Store(ID, Name, City)
Purchase(BuyerName, SellerName, StoreID, SerialNo, Date)
Person(Name, PhoneNumber, City)
4. [2 points] Find names of people who live in LA and have bought some Apple products.
[3 points] Find names of people who bought some Apple products but never bought IBM products.
[3 points] Find names of buyers who purchased at least two laptops.
[2 points] Find the number of laptops sold at each store in LA. (Use the space below wisely) 1 Π
⋈ ⋈= ⋈= ="" Π Π, 4 ,() ℎ Π ⋈ ="" ="" Π, Π ℎ Π
3 2 −
="" Π, ,() ℎ Π,
ℎ See self joining
next page ⋈
="" Π, ℎ Π Every two wrong or missing elements, e.g. wrong operators and missing attributes, resulted
in 0.5 points being deducted. When the number of mistakes divided by two, a floor was taken
if there’re same types of them and a ceiling was taken if not.
Different sequences of operations, i.e. whether select or join first, didn’t affect your scores.
The solution presented is a relatively efficient one by doing selection first then joining.
INF 551, Spring 2017 Name: ___________________________ USC ID: ______________________ Π1 3 ⋈1=2,1!=2
ρ1,1 ρ2,2 ⋈
ℎ INF 551, Spring 2017 Π ...
View Full Document
- Fall '14
- Algebra, Relational model