Consider a database containing information related to movies organized in tables corresponding to the following relations (Sample create table statements in the Appendix. Attribute genre in table Movie has as value one of {"comedy", drama", tragedy", "musical", "horror"}.). Movie(title, year, genre, budget, gross) Director(name, country, YofB) Actor(name, country, YofB) Producer(name, country, YofB) DirectorMovie(d_name, m_title, m_year) ActorMovie(a_name, m_title, m_year) ProducerMovie(p_name, m_title, m_year) 

Part 1.  Write the following queries in Relational Algebra. 

1. Find actors who played in, directed and produced the same movie. 

2. Find directors who were younger than all the actors they directed in a movie

 3. Find actors who only played in comedies. 

4. Find directors who never directed a drama 

Part 2. Write the following queries in SQL 5. List the movies with a young cast, i.e. all actors who worked on that movie are under the age of 25.

 6. List all producers in the order of their average profit (the profit for each movie is calculated as the difference between its gross earnings and its budget). 

7. List all directors who were very prolific in their first 10 years of activity, i.e., they directed at least one movie each year, in the 10 years after their first movie (i.e., the year of their first movie counts as the first of 10.) 

8. List actors who were cast in more comedies than in dramas and tragedies, combined. 

APPENDIX 

CREATE TABLE MOVIE( 

TITLE VARCHAR(100) NOT NULL, 

YEAR INTEGER NOT NULL, 

GENRE VARCHAR(20) NOT NULL,

 BUDGET INTEGER NOT NULL, 

GROSS INTEGER NOT NULL,

 PRIMARY KEY (TITLE,YEAR)); 

CREATE TABLE DIRECTOR( NAME VARCHAR(30) NOT NULL,

 COUNTRY VARCHAR(20) NOT NULL, 

YOFB INTEGER NOT NULL,

 PRIMARY KEY (NAME));

 CREATE TABLE ACTOR( NAME VARCHAR(30) NOT NULL,

 COUNTRY VARCHAR(20) NOT NULL, YOFB INTEGER NOT NULL, 

PRIMARY KEY (NAME)); 

CREATE TABLE PRODUCER( NAME VARCHAR(30) NOT NULL,

 COUNTRY VARCHAR(20) NOT NULL, YOFB INTEGER NOT NULL, 

PRIMARY KEY (NAME)); 

CREATE TABLE DIRECTORMOVIE( D_NAME VARCHAR(30) NOT NULL,

 M_TITLE VARCHAR(100) NOT NULL, M_YEAR INTEGER NOT NULL,

 PRIMARY KEY (D_NAME,M_TITLE,M_YEAR)); 

CREATE TABLE ACTORMOVIE( A_NAME VARCHAR(30) NOT NULL, 

M_TITLE VARCHAR(100) NOT NULL, 

M_YEAR INTEGER NOT NULL,

 PRIMARY KEY (A_NAME,M_TITLE,M_YEAR));

 CREATE TABLE PRODUCERMOVIE( P_NAME VARCHAR(30) NOT NULL,

 M_TITLE VARCHAR(100) NOT NULL, 

M_YEAR INTEGER NOT NULL,

 PRIMARY KEY (P_NAME,M_TITLE,M_YEAR));