Give equivalent queries using the relational algebra select distinct

Task1:

Consider the following scenario of a pharmaceutical database:

For each instrument, the year of manufacturing and the creator are stored. Musicians may play multiple

instruments, and each instrument may be played by several musicians. Each recorded album has a title

producer. Musicians can act as producers, but producers are not necessarily musicians. Each producer

has produced one or more albums.

(d) Skill question: Describe examples for each situation in which a foreign key can be part of a primary key of a relation.

(e) Skill question: Describe under which circumstances relationships can have key attributes in an ER diagram?

FilmReview(Title, Reviewer, Points)

1. Express the following queries using relational algebra:

5. Query for the actors that directed the movies in which they played.

6. Query for a list of movies made in 1980 in which all actors that are in the database appeared.

select distinct FD.Director, FR.Title

from FilmDirector FD, FilmReview FR

group by FC.Actor

having count(Title)>=2;

and FR.Title=FD.Title

and FD.Title=FC.Title

Train (tid : integer, ttype : String, rank : integer)

Certified (eid : integer, tid : integer)

2. Find the names of all train drivers are certified to drive trains of type ”diesel”.

3. Find the names and id of all train drivers that are allowed to drive at least one of the trains leaving from Wellington