You will be writing relational algebra (not SQL) queries to select various sets of data from an auto dealership database

About the database:

The entities in the database are: ? 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 on 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 (eg. Taffeta white). The code is the hex representation of that color (e.g. #FFFAFA)

SCHEMA

Write in Relational algebra notation for questions 1-4

Questions

1. Select the make_name and model_name of all vehicles, regardless of whether they are on the lot or not, which have a first production year of 1986.

2. Select the make_name and model_name of all vehicles with the color name Sky Blue and which are on the lot.

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

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

Model Vehicle PK vehicle_id FK1 fk make id FK2 fk modelid Vehicle Incenive PK,FK1 fk vehicle id PK,FK2 fk incentive id PK model_id model_name year valid till year Make PK make id Incentive make name PK incentive id Inventory PK nventory_id FK1 fk vehicle id FK2 fk color_id type amount conditions country Color PK color id price name code