In the following, use only pure relational algebra no aggregates, grouping or di

Question

In the following, use only pure relational algebra no aggregates, grouping or division Only o, nr, X x p, U, n and are valid. Recall to give your join conditions unless you are sure you can use a natural join. Recall to use renaming where needed (note that SQL-style renaming is not allowed!) Please type your answers. If your text processors does not have symbols for the relational algebra oper- ators, simply use PROJECT', "SELECT", 'JOIN', PRODUCT', 'RENAME', 'UNION, INTERSECTION and DIFFERENCE instead 1. Let the relation FLIGHTOfid,airline,source, destination,cost) indicate when there are direct flights (no stops) with flight id fid (primary key) in a given airline from a source to a destination. and at what cost. That is, a tuple (d1522,Delta,SDF,ORD,$500 means that there is a direct flight with id d1522 on Delta Airlines from SDF (Louisville) to ORD (Chicago's O'Hare) for $500. You can assume that airport names in source and destination are unique (no two airports have the same name) Write Relational Algebra expressions for the following queries (a) Find the id of all the Delta airlines flights that cost more than $1,000 (b) Find all the airlines that fly to SDF and to ORD (c) Find all the airlines that fly to SDF or to ORD (d) Find the id of all the flights from JFK to SDF with one stop (e) Find all the airlines that have no direct flights to SDF 2. Consider the following database schema: Researcher (name, institution, city, country) Paper (title, journal, volume, number, year) Author(rname,ptitle) where name is the identifier (primary key) of a researcher, institution is where the researcher works and city and country the city and country where the institution is located; title is the paper identifier (primary key), journal is the journal where it was published (in volume volume and number number), and year is the year it appeared. Finally, rnam in Author is a foreign key to Researcher and ptitle in e Author is a foreign key to Paper. A researcher may write several papers, and papers may be jointly written by several researchers; this means that the key of Author is (rname,ptitle) Write the following queries in Relational Algebra. (a) Find the names of authors from Louisville who have published a paper in journal Nature in 2016 (b) Find the names of researchers who have published two or more papers

Explanation / Answer

Please use the relational algebra symbols in your answer

1a. PROJECT fid SELECT airline = 'Delta' and cost > 1000
Make sure to display only unique records

PROJECT eliminates columns while SELECT eliminates rows.

1b. PROJECT airline SELECT destination ='SDF'
INTERSECTION
PROJECT airline SELECT destination ='ORD'

Intersection gives AND (where both the conditions are met all those records)
1c. SELECT airline where destination ='SDF'
UNION
SELECT airline where destination ='ORD'

UNION gives OR (All the records where at least one condition is met)
1d. Take a join of Flight table with Flight table with same FID where destination of 1 table is same as source of another table
Now on the result table apply the condition source1='JFK' and destination2='SDF'

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

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