Relational Algebra Exercises 1. Consider a database with the following schema: Person ( name, age, gender ) name, pi pizzeria ) Frequents ( na Eats ( name, pizza ) pizzer zeria ia,, piz pizza, za, pric pricee ) Serves ( piz
name is a key (name, pi pizzeria) is is a key (name, pizza) is a key (piz (pizze zeri ria, a, pizz pizza) a) is a key key
Write relational algebra expressions for the following nine queries (Warning: (Warning: some of the later queries are a bit challenging) If you know SQL, you can can try running SQL queries to match your relational relational algebra algebra exressions! "e#ve create$ a file a file for $ownloa$ $ownloa$ with with schema $eclarations an$ samle $ata! %See our quick gui$e for gui$e for SQL system instructions!& 'o 'o check your queries, the correct results are foun$ in the answers section below! a b c d e
!ind all pizzerias frequented by at least one person under the age of "# !ind the names of all females who eat either mushroom or pepperoni pizza (or both) b oth) !ind the names of all females who eat both mushroom and pepperoni pizza !ind all pizzerias that ser$e at least one pizza that %my eats for less than &"''' !ind all pizzerias that are frequented by b y only females or only males !or each person, find all pizzas the person eats that are not ser$ed by any pizzeria the person frequents
f g h i
eturn all such person (name) pizza pairs !ind the names of all people who frequent only pizzerias ser$ing at least one pizza they eat !ind the names of all people who frequent e$ery pizzeria ser$ing at least one pizza they eat !ind the pizzeria ser$ing the cheapest cheap est pepperoni pizza *n the case of ties, return all of the cheapest+pepper
2. Consider a schema with two relations, relations, ((%, ) and S (, (, C), where all $alues are integers -ake no assumptions about keys Consider the following three relational algebra expressions:
.wo of the three expressions are equi$alent (ie, produce the same answer on all databases), while one of them can produce a different answer Which query can produce a different answer/ 0i$e the simplest database instance you can think of where a different answer is produced
3. Consider a relation ((%, ) that contains r tuples, and a relation S (, C) that contains s tuples1 assume r 2 ' and s 2 ' -ake no assumptions about keys !or each of the following relational algebra expressions, state in terms of r and s the minimum and maximum number of tuples that could be in the result of the expression
4. .wo more exotic relational algebra operators we didn3t co$er are the semi)oin and anti)oin 4emi5oin is the same as natural 5oin, except only attributes of the first relation are returned in the result !or example, if we ha$e relations Stu$ent (*6, name) and Enrolle$ (*6, course), and not all students are enrolled in courses, then the query 7 Stu$ent ⋉ Enrolle$ 7 returns the *6 and name of all students who are enrolled in at least one course *n the general case, 8 " ⋉ 89 returns all tuples in the result of expression 8 " such that there is at least one tuple in the result of 89 with matching $alues for the shared attributes %nti5oin is the con$erse: 8" ▷ 89 retuns all tuples in the result of expression 8 " such that there are no tuples in the result of 8 9 with matching $alues for the shared attributes !or example, the query 7Stu$ent ▷ Enrolle$ 7 returns the *6 and name of all students who are not enrolled in any courses
ike some other relational operators (eg, intersection, natural 5oin), semi5oin and anti5oin are abbre$iations + they can be defined in terms of other relational operators
6efine 8" ⋉ 89 in terms of other relational operators .hat is, gi$e an equation 78" ⋉ 89 ; //7, where // on the right+hand side is a relational algebra expression that doesn3t use semi5oin 4imilarly, gi$e an equation 78 " ▷ 89 ; //7, where // on the right+ hand side is a relational algebra expression that doesn3t use anti5oin
5. Consider a relation 'em(region*6, name, high, low) that records historical high and low temperatures for $arious regions egions ha$e names, but they are identified by region*6, which is a key Consider the following query, which uses the linear notation introduced at the end of the relational algebra $ideos
4tate in 8nglish what is computed as the final esult .he answer can be articulated in a single phrase 1. 4ample solutions1 in general there are many correct expressions for each query
Query results for SQL $ata* a b c d e f g h i
4traw
ork ?izza, ?izza ork ?izza ittle Caesars, Chicago ?izza, =ew >ork ?izza %my: mushroom, 6an: mushroom, 0us: mushroom %my, en, 6an, 8li, !ay, 0us, ork ?izza
2. @uery (c) is different et ( ; A(B, )D and S ; A(", 9)D .hen query (a) and (b) produce an empty result while (c) produces A(B, 9)D
3. a -inimum ; max(r, s) (if one relation is a subset of the other) -aximum ; r E s (if the relations are dis5oint)
b -inimum ; ' (if there are no shared $alues) -aximum ; r x s (if all of the $alues are the same) c -inimum ; ' (if there are no shared $alues) -aximum ; min(r, s) (if one relation3s $alues are a subset of the other3s, and all $alues are distinct) d (equi$alent to ) -inimum ; r, -aximum ; r e -inimum ; ' (if % ; in all tuples of () -aximum ; r (if % F2 in all tuples of ()
4.
5. =ames of regions with the highest high temperature andor lowest low temperature
