Question 1
The relational database moviedb has the following database schema: Movie(title, production year, country, run time, major genre)
primary key : {title, production year} Person(id, first name, last name, year born)
primary key : {id} Award(award name, institution, country)
primary key : {award name} Restriction Category(description, country)
primary key : {description, country}
Director(id, title, production year)
primary key : {title, production year}
foreign keys : [title, production year] ⊆ Movie[title, production year]
[id] ⊆ Person[id]
20 Marks
1
Writer(id, title, production year, credits)
primary key : {id, title, production year}
foreign keys : [title, production year] ⊆ Movie[title, production year]
[id] ⊆ Person[id] Crew(id, title, production year, contribution)
primary key : foreign keys :
{id, title, production year}
[title, production year] ⊆ Movie[title, production year] [id] ⊆ Person[id]
Scene(title, production year, scene no, description)
primary key : foreign keys :
{title, production year, scene no}
[title, production year] ⊆ Movie[title, production year]
Role(id, title, production year, description, credits)
primary key : {title, production year, description}
foreign keys : [title, production year] ⊆ Movie[title, production year]
[id] ⊆ Person[id]
Restriction(title, production year, description, country)
primary key : {title, production year, description, country}
foreign keys : [title, production year] ⊆ Movie[title, production year]
[description, country] ⊆ Restriction Category[description, country]
Appearance(title, production year, description, scene no)
primary key : {title, production year, description, scene no}
foreign keys : [title, production year, scene no]⊆Scene[title, production year, scene no]
[title, production year, description]⊆Role[title, production year, description] Movie Award(title, production year, award name, year of award,category, result)
primary key : foreign keys :
Crew Award(id, title, primary key : foreign keys :
{title, production year, award name, year of award, category} [title, production year] ⊆ Movie[title, production year] [award name] ⊆ Award[award name]
production year, award name, year of award, category, result)
{id, title, production year, award name, year of award, category} [id, title, production year] ⊆ Crew[id, title, production year] [award name] ⊆ Award[award name]
Director Award(title, production year, award name, year of award, category, result) primary key : {title, production year, award name, year of award, category} foreign keys : [title, production year] ⊆ Director[title, production year]
[award name] ⊆ Award[award name]
Writer Award(id, title, production year, award name, year of award, category, result) primary key : {id, title, production year, award name, year of award, category} foreign keys : [id, title, production year] ⊆ Writer[id, title, production year]
[award name] ⊆ Award[award name]
Actor Award(title, production year, description, award name, year of award,category,result) primary key : {title, production year, description, award name, year of award, category} foreign keys : [award name] ⊆ Award[award name]
[title,production year,description]⊆Role[title,production year,description] 2
There are five different categories of awards: movie awards, crew awards, director awards, writer awards and actor awards. A movie can only win an award after being nominated for the award.
Your task is to answer the following questions using SQL queries. For each question, your answer must be a single SQL query that may contain subqueries, and you must write and save your queries into the template file myqueries.sql on the desktop.
- 1.1 Find all people who were born in 1945. List their ids, first names and last names. (2 Mark)
- 1.2 How many movies were produced in the USA? List that number. (2 Mark)
- 1.3 Which comedy movies (i.e., the major genre of the movie is comedy) were produced in 1993? List the titles of these movies. (2 Mark)
- 1.4 How many directors have directed at least one action movie (i.e., the major genre of the movie is action)? List that number. (2 Mark)
- 1.5 Find all movies that won at least two movie awards. List their titles and production years along with the corresponding number of movie awards. Order your result in ascending order of the number of movie awards. (2 Mark)
- 1.6 How many directors have never won a director award? List that number. (2 Mark)
- 1.7 Find all writers who have also played roles in at least one movie written by themselves. List their ids, and the titles and production years of the corresponding movies. (2 Mark)
- 1.8 What is the maximum number of crew members in a movie? List that number. (2 Mark)
- 1.9 Who directed the movie(s) with the maximum number of scenes? List the id(s), first and last name(s). (2 Mark)
- 1.10 A person has worked on a movie if this person is a director, a writer or a crew member of this movie. Who worked on at least three different movies in this database? List their ids, first and last names. (2 Mark)+++++
3
