Consider the following database which has the following relations: Movies ( titl
ID: 3873221 • 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 the titles of movies that have been used for two or more movies.
2. Find names of movie stars who only star in movies produced by producers who
have a networth of less than $5,000,000.
3. Find years where more than 3 horror movies were released.
4. Find names of movie execs who produce movies longer than 120 minutes or star
“Kit Harrington”
Explanation / Answer
(3) Find years where more than 3 horror movies were released
Answer:
select year, count(genre) > 3 from Movies where genre = 'horror';
(4) Find names of movie execs who produce movies longer than 120 minutes or star “Kit Harrington”
Answer:
select MovieExec.name from MovieExec
inner join Movie
inner join Star
where Movie.length > 120 or Star.starName = 'Kit Harrington';