Expressing relational calculus & algebra queries in plain English re passengers, flights & trips in economy

Question

I have this statement:

And this one:

How do I go about converting these to plain English?

Here is the extent of my understanding:

For the first one, I think it's selecting p_id where there exists f_no1, f_date and f_no2 from the Flight and Trip tables joined.

The second one is confusing; I know kind of what it's doing but I dont know how to convert it to plain English. It's natural joining the trip, flight and passenger tables, then it's selecting the rows from that resulting table where the class is business. From the rows where the class is business, it is then selecting only the rows where the final destination is Los Angeles, and then from those rows, it is selecting the passenger id and name. So I guess the English translation will be something along the lines of "Get the name and id of passengers going to Los Angeles in business class" but I'm not sure.

Answer 1

Relational Calculus

You're on the right track.

• Free variable: p_id (determines your output structure)
• Bounded variables: f_no1, f_no2, f_date

You can see that there are two lines that look very similar, but differ significantly. Each line is pairing information across two relationships with the intention to find values that satisfy the conditions.

Notice that the f_date and p_id variables are the same on both lines, whereas f_no differs. This indicates that there are two separate flights which occur on the same day with the same passenger on both. The first line indicates a journey from Rapanui to Papeete and the second line indicates a journey from Papeete to Auckland. Both of these journeys must also satisfy the requirement of being traveled via Economy class.

Bring this information together, this query is asking for the p_id's where that p_id travels from Rapnui to Auckland via Papeete on the same day, with both being in Economy class.

Relational Algebra

You pretty much have it there. The query selects p_id and p_name of all passengers who have flown to Los Angeles in Business class.