Database Systems, Spring 2014
Prof. Dr. Michael B¨ohlen
DatabaseTechnology Group
Exercise 4
Date of issue: 18.3.2014
Deadline: 25.3.2014, 12:00
The exercise is based on the Mondial database, whose schema can be accessed
at http://www.dbis.informatik.uni-goettingen.de/Mondial/.
In order to have uniform solutions, please write the attributes in the same
order shown in Postgres.
1
SQL to RA and to DRC (1)
Consider the following SQL query:
SELECT country.name, geo_river.province, MAX(length)
FROM country, geo_river, river
WHERE geo_river.river = river.name
AND country.code = geo_river.country
GROUP BY country.name, geo_river.province
1) What does the query compute?
2) Convert the query to a RA expression and a DRC expression.
2
SQL to RA and to DRC (2)
Consider the following SQL query:
SELECT country
FROM religion
GROUP BY country
HAVING COUNT(name) > 2
1) What does the query compute?
2) Convert the query to a RA expression and a DRC expression.
3
DRC to RA and to SQL
Consider the following DRC expression:
{C | county( , C, , , , ) ∧
¬∃C2(borders(C2, C, ) ∨ borders(C, C2, ))}
1) What does the expression return?
2) Convert the DRC expression to a RA expression and a SQL expression.
1
Database Systems, Spring 2014
Prof. Dr. Michael B¨ohlen
4
DatabaseTechnology Group
RA to SQL and to DRC
Consider the following RA expression:
Ar ← πAREA (σN AM E=0 AF RICA0 (Continent))
T 0 ← ρCODE,AREACOU N T RY (πCODE,AREA (Country))
T 1 ← Encompasses ./COU N T RY =CODE T 0
T 2 ← Continent ./N AM E=CON T IN EN T T 1
T 3 ← σN AM E=0 AF RICA0 (T 2)
T 4 ← πCODE,AREACOU N T RY ∗100/AREA (T 3 × AR)
1) What does the expression return?
2) Considering that a term can also be an expression between variables and
constant (for instance population*2.3 /area), convert the RA expression to a
SQL query and a DRC expression.
2
© Copyright 2024 ExpyDoc
