COMP2400 – Relational Databases
Assignment 1 (SQL)
Due date: 23:59, September 3, 2021
Instructions:
• This assignment should be done individually (no group work). Do not post any idea/interpretation/par-
tial solution/result related to this assignment on the Wattle Discussion forum. Join the special drop-in sessions
in Week 5 and Week 6 if you need any clarifications.
• This assignment will count for 20% of the final grade.
• A copy of the moviedb database is available on both Option 1 (LinuxVDI) and Option 2 (VirtualBox). You
should first open a command shell and then connect to the moviedb database by entering
psql moviedb
• You must submit one file: myqueries.sql for all the questions on Wattle before the due date. You can download
the template files from the folder Assignment 1 (SQL) for COMP2400 on Wattle. You are welcome to check
your query against the moviedb database one by one following the instruction at the end of the Lab 3 document.
You must enter your queries into the template file, and more specifically, for the submitted file myqueries.sql,
it should be executable in the given database moviedb
moviedb=> \i myqueries.sql
• Sample SQL questions and solutions on moviedb are available on Wattle, which will be helpful for you to work
on your assignment.
• Late submission is not granted under any circumstance. You will be marked on whatever you have submitted at
the time of the deadline. Please take careful note of deadlines and adhere to them. Of course, if you find yourself
in a situation beyond your control that you believe significantly affects an assessment, you should follow the
ANU’s special consideration process (http://www.anu.edu.au/students/program-administration/assessments-
exams/special-assessment-consideration).
• Plagiarism will attract academic penalties in accordance with the ANU guidelines. A student in
this course is expected to be able to explain and defend any submitted assessment item. The
course convener can conduct or initiate an additional interview about any submitted assessment
item for any student. If there is a significant discrepancy between the two forms of assessment, it
will be automatically treated as a case of suspected academic misconduct.
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]
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 : {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, 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 : {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) max
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.
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 your answers into the template file myqueries.sql.
2
1.1 How many comedy movies (i.e., the major genre of the movie is comedy) are produced in Australia? List that
number. (2 Marks)
1.2 Which movies are classified as “S” in Finland? List their titles and production years, and order them in the
ascending order of their production years. (2 Marks)
1.3 Which countries have at least three restriction categories? List these countries along with the corresponding
number of restriction categories. (2 Marks)
1.4 Which directors have directed both drama and romance movies? List their ids. (2 Marks)
1.5 Which movies have won at least one director or writer award? List their titles and production years. (2 Marks)
1.6 For all movies produced in Germany, list their title, production year and the age of the director when the movie
was produced. (Hint: if a movie is produced in 1994 and its director was born in 1940, then the age of the
director when this movie was produced is 1994 – 1940 = 54) (2 Marks)
1.7 What is the largest number of crew members working in a single movie? List that number. (2 Marks)
1.8 Which directors have collaborated with at least two different writers? List the ids, first and last names of these
directors, and order them in the ascending order of their last names. (Hint: a director is considered to collaborate
with a writer if the director directed a movie written by the writer). (2 Marks)
1.9 Among those directors who have never won any director award, who directed the largest number of movies? List
their ids. (2 Marks)
1.10 How many writers have never co-written movies with other writers? List that number. (Hint: a movie may have
multiple writers but every movie written by such a writer has only one writer) (2 Marks)
3