Question

Write the following questions as queries in Relational Algebra. Use only the operators discussed in class (select, project, Cartesian product, join, union, intersection, set difference and renaming —in particular, no outer joins or aggregations). Type your answers. If you can’t find Greek letters in your word processor, you can simply write out the operator, all uppercase (i.e. ’SELECT’). Please use renaming consistently, as indicated in the handout. Before starting, make sure you understand the schema of the database. If you are in doubt about it, please ask the instructor.

Assume a database with schema:

ACTOR(name,age,address,nationality)

MOVIE(title,year,genre,budget,director-name,studio)

APPEARS(name,title,salary)

Write the following questions in Relational Algebra:

1. List the titles and budgets of movies where actor Keanu Reeves appeared in 1999.

2. List the names and ages of actors who have appeared in a comedy produced by Studio ’MGM’.

3. List the names of directors who have directed a drama or a comedy.

4. List the names of directors who have directed a drama and a comedy.

5. List pairs of actors who have appeared together in a movie (note: the schema of your answer should be (name1,name2), where both name1 and name2 are names of actors.

Answer 1

1. _title,budget(_{name='Keanu Reeves' AND year='1999'}  ( MOVIE * ( ACTOR * APPEARS) )   )

2 .  _name,age(_{genre='comedy' AND studio='MGM'}( MOVIE * (   ACTOR * APPEARS)    )   )

3. _{director-name}( _{genre='comedy' OR genre=''drama'}  ( MOVIE   )    )

4. _{director-name}( _{genre='comedy' AND genre=''drama'}  ( MOVIE   )    )

5.  _{name.e1,name.e2} ( MOVIE JOIN _{title=e1.title}  (  _{name.e1,name.e2,e1.title} ( e1.ACTOR JOIN   _{e1.name=e2.name} e2.ACTOR )    ) )