Consider a database with the following schema (when more than one attribute is u

Question

Consider a database with the following schema (when more than one attribute is underlined, the comp-bination of them is the primary key): Customer (cname, address) Dealership (dname, address) Car (model, manufacturer) Visits (cname, dname, times_a_year) Likes (cname, model) Serves (dname, model, price) Write the following queries in relational algebra: Find all customers who has visited "Provo Buick GMC". Find all dealerships that serve both Buick and Ford. Find all dealerships that serve at least one of the car models John likes for no more than $40000. For each dealership, find all car models served at this dealership that are liked by none of the customers who visit that dealership. Find all customers who visit only those dealerships that serve some car models they like. Find all customers who have visited every dealership that serves some car models they like. Find those customers who enjoy exactly the same set of cars as Mike does. For each car model, find the dealership(s) that serves it at the lowest price. Find the car models that are served by all dealerships. Find the dealerships that serve all cal models that Lin likes.

Explanation / Answer

Here are queries....

1)
Select Customer.cname from Customer INNER JOIN Visits where Customer.cname == Visits.cname AND Customer.address == " Provo Buick GMC";

-------------------------------------------------------------------------------------------
2) Select Dealership.dname from Dealership INNER JOIN Serves where Dealership.dname == Serves.dname AND (Serves.model == "Buick" AND Serves.model == "Ford");

-------------------------------------------------------------------------------------------------

3) Select * from Dealership INNER JOIN Serves where count(Serves.model)>=1 AND Serves.price <=40000;
-----------------------------------------------------------------------------------------------------------
4)
Select Car.model INNER JON Dealership INNER JOIN Serves where Dealership.dname == Serves.dname AND Serves.model == Car.model;
--------------------------------------------------------------------------------------------------------------------------------------
5) Select Customer.cname INNER JOIN Serves INNER JOIN Visits where Customer.cname == Visits.cname AND Visits.dname == Serves.dname;
-------------------------------------------------------------------------------------------------------------------------------------------
6) Select Customer.cname INNER JOIN Serves INNER JOIN Visits INNER JOIN Dealership where Customer.cname == Visits.cname AND Visits.dname == Serves.dname AND Visits.dname == Dealership.dname;
-----------------------------------------------------------------------------------------------------------------------------------------------------

7) Select Customer.cname INNER JOIN Likes INNER JOIN Car where Customer.cname == Likes.cname AND Likes.model == Car.model;
---------------------------------------------------------------------------------------------------------------------------
8)
Select Dealership.dname INNER JOIN Car INNER JOIN Serves where Car.model == Serves.model and MIN(Serves.price);
------------------------------------------------------------------------------------------------------------------------
9)
Select Dealership.dname INNER JOIN Car INNER JOIN Serves where Car.model == Serves.model;
------------------------------------------------------------------------------------------------------------------------
10)
Select Dealership.dname INNER JOIN Car, Likes, Serves where Dealership.dname == Serves.dname AND Serves.model == Likes.model;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.