Worksheet 3

This worksheet contains questions relating to relational algebra.

Some of the answers will require you to type relational algebra expressions such as
$$\pi_{\text{id}}(\sigma_{(a > 10)} \left( (R \bowtie S\right) \times T)).$$

To write these in text, do the following

• Use the words rho, pi and sigma for $\rho$, $\pi$ and $\sigma$
• Use an underscore for subscripts
• Use the word bowtie for $\bowtie$ and the letter x for $\times$

So you will type

pi_id(sigma_(a>10) ((R bowtie S) x T))

to represent the sample relational algebra expression above.

Questions on RA

The first few questions will use a student / unit / grade database similar to the one from lectures, but where the tables are called S (for student), U (for unit) and G (for grade) and have the following schema:

S(sid, name, gender)
U(uid, name)
G(sid, uid, grade)

Students have a unique id, a name and a gender (either M or F) , Units have an unique id (like CITS1402) and a name (like Databases), while Grades indicate what grade the student with id sid achieved when they took the unit with id uid.

Sample Question: What relational algebra expression will produce a list of all the unit ids?
Answer: The expression is $\pi_\text{uid} (U)$
What you type: pi_uid(U)

1. What relational algebra expression will produce a relation containing just the ids of all of the students?
2. What relational algebra expression will produce a relation containing just the names of the male students?
3. What relational algebra expression will produce a relation containing the name and grade (for Databases) of every student in the Databases unit?
4. What relational algebra query corresponds to the following SQL query?

SELECT S.sid 
FROM Student S, Grade G
WHERE S.sid = G.sid
AND grade < 50;

5. What SQL query will produce a table with the same columns and contents as the following relational algebra expression?
   $$\sigma_{\text{uid}='\text{CITS1402}'} S \bowtie G$$

Questions on aggregate functions

None of these questions involve a GROUP BY condition and so all will produce a single row from the entire table that is specified in the FROM condition. (However this table may still be constructed as theJOIN of one or more tables.)

1. What is the average population of all the countries in the world database? (Hint: Use only the Country table)
2. What SQL query will produce a one-row table with one column called worldPopulation with the total population of all the countries listed in the database?
3. What are the minimum, maximum and average populations of English-speaking countries (where English is an official language)? One query should produce the three numbers, which you should specify in the given order, on one line of your solution file.
4. What is the lowest life expectancy among all Asian countries? [Hint: You do not need to find the country, just the life expectancy]
5. How many aggregate functions does MySQL have? (Use the MySQL documentation to determine this.)
   

Written with StackEdit.