Question

Write the following queries using relational algebra. SHOW ALL STEPS

CREATE TABLE MovieStar (msid char(3), msname char(10), title char(4), PRIMARY KEY(msid)); msid – movie star ID, msname – star’s name, title – denotes gender (“Mr.’, “Mrs.”, “Ms.”)

CREATE TABLE Stars_in (msid char(3), mid char(3), contract number(9,2), duration number(2), PRIMARY KEY (msid, mid), FOREIGN KEY(msid) REFERENCES MovieStar, FOREIGN KEY(mid) REFERENCES Movie); contract – the star’s salary for the movie, duration – contract duration in months.

CREATE TABLE Movie (mid char(3), mname char(10), year number(4), length number(4,2), PRIMARY KEY (mid)); mid – movie ID, mname – movie title, year – the year when the movie was created, length – movie duration in hours.

1. Find the names of male stars who had contracts for less than 3 months.

2. Find titles of movies with the stars’ contracts over $1,000,000.00.

3. Find stars’ names who starred in movies with duration over 2 hours.

4. Find stars’ names who starred in at least one movie.

5. Find stars’ names, who starred in at least two movies.

6. Find the stars’ names who starred in movies created in 2005 and 2015.

Please elaborate on the answer.

Answer 1

A sample DB for given schema:

1. SELECT msname FROM MovieStar WHERE title LIKE 'Mr.' AND msid in (SELECT msid FROM Stars_in WHERE duration < 3)

   Explanation: The inner query finds all stars ids which have starred in some movie with contract duration < 3, then in outer query, WHERE condition, we ensure that title is Mr. and msid belongs to the msids selected above.
   The output of the query for given db:

2. SELECT mname FROM Movie WHERE mid in (SELECT mid FROM Stars_in WHERE contract > 1000000.00)

   Explanation: The inner query finds all movie ids which have given a contract > 1000000, then in outer query, WHERE condition, we ensure that mid comes from this set of ids
   The output of the query for given db:

3. SELECT msname FROM MovieStar ms JOIN Stars_in si ON ms.msid = si.msid JOIN Movie mv ON mv.mid = si.mid WHERE length > 2

   Here, we join tables MoivieStar, Stars_in on basis of the star id, then join this combined table with Movie on basis of movie id. This gives us a table with info about movie, star, and movie_star combined so now just select star which have movie length > 2 hours
   Sample output:

4. SELECT msname FROM MovieStar WHERE msid in (SELECT msid FROM Stars_in)

   Here, we firstly find the stars id present in Stars_in , it gives us ids of all the stars which have starred in some movie. Now just select names of those stars from outer query.
   Sample output:

5. SELECT msname FROM MovieStar WHERE msid in (SELECT msid FROM Stars_in GROUP BY msid HAVING count(msid) >= 2)

   In the inner query, we find all stars' id which have starred in more than 2 movies, by combining all rows by movie star id (group by) and checking count. After that in outer query we get names from those ids.
   Sample output:

6. SELECT msname FROM MovieStar ms JOIN Stars_in si ON ms.msid = si.msid JOIN Movie mv ON mv.mid = si.mid WHERE mv.year BETWEEN 2005 AND 2015

   Similar to 3rd, we combine all three tables and pick up stars names where movie year was between 2005 and 2015
   Sample Output: