Flummoxed: From Questions to Queries

In this activity, we will practice translating our human questions into queries our robot counterparts can understand. As usual, you will not be required to turn in anything. For your convenience, we have included a short example of how to typeset relational algebra notation in a Jupyter notebook.

LaTeX-Style Typesetting in Jupyter Notebooks

You can use TeX-style commands to include mathematical expressions in Jupyter notebook. In a markdown cell, just include the commands you want wrapped in $ to have an inline expression and $$ if you want centered equation typesetting.

$$basic-cust-accts \leftarrow \Pi_{(name, customer.sin, account-number)} (\sigma_{customer.sin = account.sin}(customer \times account))$$

$$basic-cust-accts \leftarrow \Pi_{(name, customer.sin, account-number)} (\sigma_{customer.sin = account.sin}(customer \times account))$$

Operation	Symbol	LaTeX Name
Assign	$\rightarrow$	rightarrow
Select	$\sigma$	sigma
Project	$\Pi$	Pi
Inner product	$\bowtie$	bowtie
Cross product	$\times$	times
Rename	$\rho$	rho
Groupby	$\gamma$	gamma
And	$\wedge$	wedge
Or	$\vee$	vee
Negation	$\neg$	neg

Inquiries

Consider the following schema below. The key fields are underlined, and the domain is listed after the field names. The Catalog table contains prices for Balloons sold by different Clowns standing at certain booths in a fair.

Clowns(cid integer, cname text, booth text)
Balloons(bid integer, bshape text, bcolor text)
Catalog(cid integer, bid integer, cost float)

Inquiry: Funny Algebra

In this section, you are asked to translate the following queries into relational algebra semantics.

Problem 1

What are the costs of all whale-shaped balloons?

SOLUTION: $\Pi_{cost}(\sigma_{bshape='whale'}Balloons \bowtie_{bid=bid} Catalog)$

Problem 2

What are the names of the clowns that sell periwinkle (color) balloons in at least one shape?

SOLUTION: $\Pi_{cname}((\Pi_{pid}\sigma_{bcolor='periwinkle'}Balloons)\bowtie Catalog)\bowtie_{cid=cid} Clowns)$

Problem 3 (Hard -- Opinions may be divisive)

What are the cids of the clowns that sell periwinkle balloons in every shape?

Hint: Consider the following strategy

1. Compute all possible attribute pairings
2. Remove the existing pariings
3. Remove the non-answers from the possible answers

SOLUTION: $\Pi_{cid}Catalog - \Pi_{cid}((\Pi_{cid}Catalog \times \Pi_{bid}\sigma_{bcolor='periwinkle'}Balloons)-Catalog)$

Inquiry: The SQL

In this section, you are asked to translate the following questions into their equivalent SQL queries. Do not delete the backticks if you want to keep a nice fix-width formatting.

Problem 4

What are the top 10 most expensive shapes sold by Whompers LeFou?

SOLUTION:

SELECT bshape, cost
FROM Clowns, Balloons, Catalog 
WHERE Clowns.cid=Catalog.cid
    AND Balloons.bid=Catalog.bid
    AND cname='Whompers LeFou'
ORDER BY cost DESC
LIMIT 10;

Problem 5

How many different colors are available at each booth?

SOLUTION:

SELECT booth, COUNT(DISTINCT bcolor)
FROM Clowns, Balloons, Catalog
WHERE Clowns.cid=Catalog.cid
    AND Balloons.bid=Catalog.bid
GROUP BY booth

Problem 6

What is the average cost of a balloon at booths that offer more than 3 red shapes per clown? Each clown at the booth does not necessarily have to be selling more than 3 shapes.

SOLUTION:

SELECT booth, avg(cost)
FROM Clowns, Balloons, Catalog
WHERE Clowns.cid=Catalog.cid
    AND Balloons.cid=Catalog.cid
    AND bcolor='red'
GROUP BY booth
HAVING COUNT(DISTINCT bshape)/COUNT(DISTINCT Clowns.cid) > 3

Bonus

Recall the schema for the representation of a matrix in SQL: matrix(x, y, value). In lecture, someone asked how the matrix transpose would be defined in SQL. Let's think about the implementation. First, we should think about the definition. Let $M$ denote a matrix and $[M]_{ij}$ be the element in the $i^{th}$ row and $j^{th}$ column. The matrix transpose is defined as $$[M^T]_{ij}=[M]_{ji}$$ This suggests that all we naively have to do is make a SELECT statement that swaps x and y. Hint: $\rho(Mt, (1\rightarrow y, 2 \rightarrow x), M)$

SOLUTION:

SELECT M.y AS x, M.x AS y, value  
FROM M;

SQL query goes here...