CI1 : Relational Algebra
Practice Relational Algebra.
Database Schema (∼20mn – medium)
In this exercice, we will practice the notions seen during the lecture. To answer the question, you can use paper. For drawing relational algebra diagrams, we can also use pen and paper (like it will be the case during the exam), or use Powerpoint/LibreOffice/Google slides with the symbols you can find here.
During this lab, we will work on a database from a wine seller. Here are rows examples for all the tables in the database:
This is the database of a wine seller. It contains six tables. The first one (Wines) contains the collection of wines, with information about its vineyard, year of harvest and degree of alcool. The second one (Harvests) links the wines with the producers and adds the weight of the harvest. The third one (Producers) describes the producers, with their name and city of origin. Next, Clients contains the list of clients of the wine seller, with their names and cities. Orders contains all the orders of the seller, with information about who bought what, when and in which quantity. Finally, Deliveries contains the current status of the orders.
During this lab, we will work on a database from a wine seller. Here are rows examples for all the tables in the database:
| WineID | vineyard | year | degree |
|---|---|---|---|
| W0129 | Muscadet | 2021 | 13.5 |
| W0130 | Chinon | 2022 | 12.5 |
| W0131 | Madiran | 2021 | 13.5 |
| WineID | ProducerID | weight |
|---|---|---|
| W0129 | P2230 | 12.5 |
| W0129 | P2241 | 20.1 |
| W0131 | P2230 | 1.4 |
| ProducerID | name | city |
|---|---|---|
| P2230 | Dupond | Brive-La-Gaillarde |
| P2241 | Dupont | Bordeaux |
| P2231 | Marcel | Meaux |
| ClientID | name | city |
|---|---|---|
| C0001 | Jean Duchamps | Paris |
| C0002 | John Doe | Évry |
| C0003 | Gérard Dixit | Palaiseau |
| OrderID | date | ClientID | WineID | quantity |
|---|---|---|---|---|
| O8976 | 12/12/2022 | C0001 | W0129 | 10.5 |
| O8977 | 20/12/2022 | C0002 | W0130 | 20.3 |
| O8978 | 01/02/2023 | C0002 | W0130 | 1.7 |
| OrderID | status |
|---|---|
| O8976 | Order Received |
| O8977 | Delivering |
| O8978 | Delivered |
For each table, identify what the primary key is.
- Wines: WineID
- Harvests: WineID, ProducerID
- Producers: ProducerID
- Clients: ClientID
- Orders: OrderID
- Deliveries: OrderID
For each table, identify the foreign keys.
- Wines: None
- Harvests: WineID, ProducerID
- Producers: None
- Clients: None
- Orders: (OrderID), ClientID, WineID
- Deliveries: (OrderID)
For each attribute, suggest a domain.
- WineID: String
- vineyard: String
- year: Integer
- degree: Float
- ProducerID: String
- name: String
- city: String
- date: Date
- quantity: Float
- status: String
Using the answers above, give the final database schema.
- Wines(WineID: String, vineyard: String, year: Integer, degree: Float)
- Harvests(WineID: String, ProducerID: String, weight: Float)
- Producers(ProducerID: String, name: String, city: String)
- Clients(ClientID: String, name: String, city: String)
- Orders(OrderID: String, date: Date, ClientID: String, WineID: String, quantity: Float)
- Deliveries(OrderID: String, status: String)
Relational Algebra (∼60mn – medium)
Get the name of all the clients.
Give the OrderID of all orders currently on delivery (status="on delivery").
Get the name of the WineIDs of wines produced before 1980.
Get the wines IDs that were never ordered
Methodology:
- Identify the tables you need:
- Tables explicitly mentioned in the query: Wines, Orders
- Tables to connect tables: None
- Identify what you need to output: WineIDs from Wines
- Filter (with SELECT) and keep only the columns you need (with PROJECT)
- We need WineID from Orders and Wines
- Identify the links you need to create and the conditions/operations:
- The WineID attributes connect Orders and Wines
- We need to remove WinesID in Orders from WinesID in Wines (DIFFERENCE)
- Filter and keep only the columns you need as you progress
Get the names of producers living in Paris who are also clients (they have the same name)
Get the names of all the cities involved in the wine business (i.e. where a producer or a client lives)
Get the names of the cities where a producer harvested more that 1 ton of grapes in one time.
Get the names of the producers of Muscadet (type of vineyard)
Methodology
- Identify the tables you need
- Tables explicitly mentioned in the query: Producers and Wines (for the vineyard)
- Tables to connect tables: Harvest connect Producers and Wines
- Identify what you need to output: We want Producers.name
- Filter (with SELECT) and keep only the columns you need (with PROJECT)
- In Wines, we need the WineID (to link with Harvest) and vineyard (to pick Muscadet)
- In Harvest, we need WineID and ProducerID to connect Producers and Wines
- In Producer, we need ProducerID (to link with Harvest) and name (the output)
- Identify the links you need to create and the conditions/operations
- We link Wines and Harvest with the commun column WineID
- We link Producers and Harvest with the commun ProducerID
- Filter and keep only the columns you need as you progress
- We need to filter the final result to get only the name in Producer
Get the names of the wines that were never ordered
Get the names of clients who live in Paris and order before 2000 at least a bottle of Macon (vineyard) from 1995
Methodology
- Identify the tables you need
- Tables explicitly mentioned in the query: Clients, Wines and Orders
- Tables to connect tables: None
- Identify what you need to output: We want Client.name
- Filter (with SELECT) and keep only the columns you need (with PROJECT)
- In Wines, we need the WineID (to link with Orders), vineyard (to pick Macon) and year (to pick 1995)
- In Orders, we need WineID and ClientID to connect Clients and Wines
- In Clients, we need ClientID (to link with Orders), name (the output) and city (to pick Paris)
- Identify the links you need to create and the conditions/operations
- We link Wines and Orders with the commun column WineID
- We link Clients and Orders with the commun ClientID
- Filter and keep only the columns you need as you progress
- We need to filter the final result to get only the name in Client
Get the name of the clients who are currently being delivered (status = “delivering”)
Get the names of the clients who bought a wine produced in Paris
