Relational Algebra

In this assignment you will be writing relational algebra (not SQL) queries to select various sets of data. Attached is a schema of a auto dealership database.

Vehicle - The base class for types of vehicles to be sold. Make - The brand of vehicle. (e.g. BMW, Ford etc)

Model - The specific model (2 Series, Focus etc). First production year is the first year that model was ever made

Vehicle_Incentive - A relationship table between Vehicles and Incentives. Keeps track of when the incentive for that vehicle expires.

Incentive - Discounts and other deals. Type includes things like Factory or Dealer depending who is offering the incentive.

Inventory - The actual stock of vehicles in the lot. The price is the MSRP for that specific vehicle.

Color - The potential colors cars can come in. The name is the name given by the factory (Taffeta white). The code is the hex representation of that color (e.g. #FFFAFA)

 

 

 

 

Questions

1. Select the make_name and model_name of all vehicles which have a first production year of 1976 2. Select the make_name and model_name of all vehicles with the color name Blue

3. Select the make_name, model_name and incentive amount for all vehicles with a dealer type incentive

4. Convert the following query to relational algebra

SELECT Player.id, Team.name, City.name FROM Player INNER JOIN Team ON Player.team_id = Team.id INNER JOIN City ON Team.city_id = City.id
WHERE Player.score = 100;

5. For problem 3 above, convert your relational algebra query into a SQL query.

Attachments:

• 1493441474-Auto Dealer Schema-3.pdf