Relational Models, Selection + Projection

Question 1

What is the equivalent SQL for 𝜋firstname, birthday(Person)?

Answer 1

SELECT DISTINCT firstname, birthday FROM Person

Question 2

Which SQL condition would fulfill the following:
- matches “bat”
- matches “chat”
- does NOT match “bank”

Answer 2

LIKE “%a_”

% = match everything
_ = match one thing

Question 3

What is projection? Given a relation R, how to select columns A and B?

Answer 3

• step which select columns
  𝜋A,B(R)

Question 4

What does this mean:
𝜋Title (𝜎genre=”suspense”^”audience”=”Teenagers”(Books))

Answer 4

SELECT DISTINCT Title FORM Books WHERE genre = “Suspense” AND audience = “Teenagers”

Question 5

What is the cartesian product? How to represent in SQL? in RA?

Answer 5

Contains every combination of rows in the 2 tables:
SQL: SELECT * FROM R1, R2;
RA: R1 x R2

Question 6

In SQL and RA, show to how to use cartesian product to find all stores based in Winnipeg

Answer 6

SELECT storeName FROM Store, City
WHERE Store.CID = City.CID
AND cityName = “Winnipeg”

𝜋storeName(𝜎Store.CID=City.CID^CityName=”Winnipeg”(Store x City))

Question 7

Why doesn’t this work in cartesian product?
S𝐸𝐿𝐸𝐶𝑇 𝐷𝐼𝑆𝑇𝐼𝑁𝐶𝑇 𝑡𝑖𝑡𝑙𝑒 𝐹𝑅𝑂𝑀 𝐵𝑜𝑜𝑘𝑠, 𝑅𝑒𝑎𝑑, 𝑃𝑒𝑜𝑝𝑙𝑒
𝑊𝐻𝐸𝑅𝐸 𝑓𝑖𝑟𝑠𝑡 = “𝐴𝑖𝑑𝑒𝑛” 𝐴𝑁𝐷 𝑙𝑎𝑠𝑡 = “𝐵𝑎𝑘𝑒𝑟“

Answer 7

You need to tell SQL how the tables are connected. To do so, do joins on People, Read, and Books.

Question 8

What is a natural join?

Answer 8

Join which matches on all columns that share a name, and doesn’t duplicate columns

Question 9

How to represent this in RA?
𝑆𝐸𝐿𝐸𝐶𝑇 𝑓𝑖𝑟𝑠𝑡, 𝑙𝑎𝑠𝑡, 𝐴𝐼𝐷 𝐹𝑅𝑂𝑀 𝑃𝑒𝑜𝑝𝑙𝑒 𝑁𝐴𝑇𝑈𝑅𝐴𝐿 𝐽𝑂𝐼𝑁 𝐴𝑢𝑡ℎ𝑜𝑟𝑠

Answer 9

𝜋first,last,AID(People⋈Authors)

Question 10

When to use a JOIN ON?

Answer 10

Tables don’t share same column name, or want to be more explicit about what’s happening.

SQL:
𝑆𝐸𝐿𝐸𝐶𝑇 𝑓𝑖𝑟𝑠𝑡, 𝑙𝑎𝑠𝑡, 𝐴𝐼𝐷
𝐹𝑅𝑂𝑀 𝑃𝑒𝑜𝑝𝑙𝑒 𝐽𝑂𝐼𝑁 𝐴𝑢𝑡ℎ𝑜𝑟𝑠 𝑂𝑁 𝑃𝑒𝑜𝑝𝑙𝑒. 𝑆𝐼𝑁 = 𝐴𝑢𝑡ℎ𝑜𝑟𝑠. 𝑆𝐼𝑁

RA:
𝜋first,last,AID(People⋈People.SIN=Authors.SIN Authors)

Question 11

In RA and SQL, Find the names of all the people who live in Winnipeg and have read at least
one book

Answer 11

S𝐸𝐿𝐸𝐶𝑇 𝐷𝐼𝑆𝑇𝐼𝑁𝐶𝑇 𝑓𝑖𝑟𝑠𝑡, 𝑙𝑎𝑠𝑡
𝐹𝑅𝑂𝑀 𝑃𝑒𝑜𝑝𝑙𝑒 𝑁𝐴𝑇𝑈𝑅𝐴𝐿 𝐽𝑂𝐼𝑁 𝐶𝑖𝑡𝑦 𝑁𝐴𝑇𝑈𝑅𝐴𝐿 𝐽𝑂𝐼𝑁 𝑅𝑒𝑎𝑑
𝑊𝐻𝐸𝑅𝐸 𝑐𝑖𝑡𝑦𝑁𝑎𝑚𝑒 = “𝑊𝑖𝑛𝑛𝑖𝑝𝑒𝑔“

𝜋first,last(𝜎cityName=”Winnipeg”(People⋈City⋈Read))

Question 12

Describe the different set operations and their corresponding SQL/RA translations

Answer 12

UNION (U in RA):
result set contains tuples in 1 or other, or both

INTERSECT (∩ in RA):
result set contains only tuples in both

EXCEPT (- in RA) (aka subtraction):
result set contains tuples in first relation but not the second