Asking questions with sets of rows

Computing · Data & Databases · ages 18-19

Name ______________________ Date ____________

In π_name(σ_age>10(Students)), which operation acts on the table first?
- The project, which is written first
- Both run at the same moment
- The select, which sits closest to the table
Before you are allowed to write R ∪ S, what must be true about R and S?
- They must have the same attributes, with matching types
- They must have the same number of tuples
- They must share at least one primary key value
- Nothing, union works on any two tables
Someone moves the bottom row of a table to the top. Nothing else changes. Is it still the same relation?
- No, the first row is always special
- No, row order is part of the data
- Yes, a relation is a set, so row order does not matter
- Only if the table has a primary key
Which sentence describes a relation?
- A set of tuples over named attributes
- A list of numbers in a fixed order
- A single row with no column names
R holds 4 tuples. S shares 2 of them. How many tuples are in R - S?
Answer: ______________
Table Students(name, age). You want just the names of the students older than 10. Which expression gives that result?
- π_name(σ_age>10(Students))
- σ_age>10(π_name(Students))
- π_age(σ_name(Students))
- Students − π_name(Students)
Relation R has 4 tuples and relation S has 3 tuples. They share 2 identical tuples. How many tuples are in R ∪ S?
Answer: ______________
In pure relational algebra, a friend claims a query result contains the exact same tuple twice. What do you know?
- That cannot happen, because query results are sets and sets have no duplicates
- The query is invalid and must be rewritten
- The second copy is kept because the tuples arrived in a different order
- It means the table has no primary key
Relations R and S both have attribute a. Which pair of expressions always gives the same relation?
- σ_a>5(R ∩ S) and σ_a>5(R) ∩ σ_a>5(S)
- π_a(R ∩ S) and π_a(R) ∩ π_a(S)
- R − S and S − R
- σ_a>5(R − S) and σ_a>5(S − R)
Kim projects R and S onto a first, intersects, and claims it always equals intersecting first and projecting after. What is wrong?
- Intersection is only defined on single columns
- Projecting first can create matches between rows that were never equal
- Nothing, the two writings always agree

Answer key

For grown-ups. Fold this page away before handing over the rest.

Asking questions with sets of rows W1-mt_unDBd_Ug8T-s1

The select, which sits closest to the table · Expressions compose inside out, so the inner select filters rows before the project keeps names.
They must have the same attributes, with matching types · Union pours the tuples of both relations into one set. That only makes sense if every tuple has the same shape, meaning the same attribute names and types.
Yes, a relation is a set, so row order does not matter · A relation is a set of tuples. Sets care about membership, not arrangement, so shuffling rows leaves the relation unchanged.
A set of tuples over named attributes · A relation names its columns and stores each distinct row once, with no order.
2 · Difference drops the 2 shared tuples, so 4 - 2 = 2.
π_name(σ_age>10(Students)) · Select first, while the age column still exists to test, then project away the age column. Projecting first deletes the column the condition needs.
5 · A union is a set, so the 2 shared tuples are stored once, not twice. 4 + 3 counts them twice, so subtract the 2 duplicates.
That cannot happen, because query results are sets and sets have no duplicates · Every operation of relational algebra takes sets and returns sets, so duplicates can never appear in a final result. Real SQL allows duplicates only because SQL tables are bags, a deliberate break from the algebra.
σ_a>5(R ∩ S) and σ_a>5(R) ∩ σ_a>5(S) · Selection distributes over intersection: a tuple survives exactly when it is in both R and S and has a above 5. Projection does not distribute, because projecting first can create matches between tuples that were never equal.
Projecting first can create matches between rows that were never equal · Two rows can differ on dropped columns yet agree on a, so early projection can invent shared rows.