quiz9-relational-algebra-solution.pdf - Name USC ID Quiz 9 Relational Algebra(10 points 15 minutes Consider again the following tables that you have

quiz9-relational-algebra-solution.pdf - Name USC ID Quiz 9...

This preview shows page 1 out of 2 pages.

You've reached the end of your free preview.

Want to read both pages?

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) 1. 2. 3. 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 − Π (>1) Π ⋈ ⋈ ="" Π, ,() ℎ Π, ℎ See self joining solution 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

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

Stuck? We have tutors online 24/7 who can help you get unstuck.
A+ icon
Ask Expert Tutors You can ask You can ask You can ask (will expire )
Answers in as fast as 15 minutes