Consider the following database which has the following relations: Movies ( titl
ID: 3873222 • Letter: C
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 unique
id numbers. Assume that names of people are unique, there is one producer of each
movie, and each studio has one president.
Express the following queries using Relational Algebra.
1. Find names of movie stars who starred in no movies produced by “Columbia
Pictures” in 2017
2. Find movie stars who only star in movies that are produced by producers that
have the same address as them.
3.Find names of movie stars who have starred in at least one movie for each major
studio (Using the Division operator)
Explanation / Answer
Query 1:
-----------
select
ME.name
FROM
MovieStar as MS,
Stars as S,
Movies as Mo,
MovieExec as ME
where
MS.name = S.starName AND
S.movieTitle = Mo.title AND
Mo.producer_id = ME.ID AND
ME.ID IN ( SELECT ID FROM MovieExec M ,Movies V where M.producer_id = v.ID and M.name != 'ColumbiaPictures' AND V.year != '2017')
Description:
-------------------
Query will fetch first all the movies which are not produced by producer "Columbia pictures" and not in year 2017. now this query output suppy to another queries ( main query ) and fetch the movie star name.
Query 2
------------
SELECT
MS.name
FROM
MovieStar as MS,
Stars as S,
Movies as Mo,
MovieExec as ME
where
MS.name = S.starName AND
S.movieTitle = Mo.title AND
Mo.producer_id = ME.ID AND
ME.ID IN ( SELECT ID FROM MovieStar as A,MovieExec as B WHERE A.address = B.address )
Desccription
------------------
Here nested queries are beign used . First inner query gives you the producer id list which has same address as star has and then those ids supply to main query and get the list of movies starts which are played star role in movies which produced by those producer ids.