Consider the following database which has the following relations: Movies ( titl

Question

Consider the following database which has the following relations:
Movies ( title, year, length, genre, studioName, producer_id )
Stars ( movieTitle, movieYear, starName )
MovieStar ( name, address, gender, birthdate )
MovieExec ( name, id, address, networth )
Studio ( name, address, president_id )
Where movie executives can be producers or studio presidents, and they have uniq
id numbers. Assume that names of people are unique, there is one producer of eac
movie, and each studio has one president.
Express the following queries using Relational Algebra.
1. Find the names of presidents of studios that released a movie in 2017.
2. Find movies which star more than 10 movie stars.
3. Find movie stars who only star in movies produced by “Guillermo del Toro”.

Explanation / Answer

Hi,

ANS 1-
SELECT ME.NAME FROM MOVIEEXEC ME
JOIN STUDIO S
ON ME.ID=S.PRESIDENT_ID
JOIN MOVIES M
ON S.NAME=M.STUDIONAME
AND M.YEAR=2017;

ANS 2-
SELECT MOVIETITLE,COUNT(MS.NAME) FROM STARS S
JOIN MOVIESTAR MS
ON S.STARNAME=MS.NAME
GROUP BY MOVIETITLE
HAVING COUNT(MS.NAME)>10;

ANS 3-
SELECT S.STARNAME FROM STARS S
JOIN MOVIES M
ON S.MOVIETITLE=M.TITLE
JOIN MOVIEEXEC ME
ON M.PRODUCER_ID=ME.ID
AND ME.NAME='Guillermo del Toro'

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects