Description

• This assignment should be done individually (no group work).
• This assignment will count for 20% of the final grade.
• You need to check whether your computer can connect to your own database (i.e., your UID, such as u1234567) and to the database moviedb at the server partch following the instructions below:
– Log into your account on partch from the lab computer or from your own computer.
– To connect to moviedb, enter “psql moviedb”.
– For the submitted file myqueries.sql, it should be executable in the given database moviedb, i.e., “moviedb=> i myqueries.sql”.
Question 1 20 Marks
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]
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 : {id, title, production year}
foreign keys : [title, production year] ⊆ Movie[title, production year]
[id] ⊆ Person[id]
Scene(title, production year, scene no, description) primary key : {title, production year, scene no}
foreign keys : [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, productionyear, 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 : {title, production year, award name, year of award, category} foreign keys : [title, production year] ⊆ Movie[title, production year]
[award name] ⊆ Award[award name]
Crew 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] ⊆ 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]
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.
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)
+++++