COMP6240 - Relational Databases
Assignment 1 (SQL)
Due date: 23:59, 30 August, 2022
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
if you need any clarifications or need any technical support for accessing the moviedb database.
This assignment will count for 20% of the final grade. Each question is worth 2 marks for a total of 20 marks.
A copy of the moviedb database is available on both Option 1 (Docker) and Option 2 (Ubuntu Desktop). You
should connect to the moviedb database by entering the following in your terminal
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 COMP6240” on Wattle. You are welcome to run your
query against the moviedb database one by one following previous lab instructions. 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
The correctness of queries should not depend on any database state, and the current content in moviedb is
available for you to get familiar with the moviedb database. A tailored database will be designed to reveal
common issues of incorrect queries during marking and made available to you as part of the feedback for your
submission. Note that partial marks may be awarded if the query only has minor issues.
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 send
an Email to Yu Lin with the title “Special Consideration for Assignment 1 (SQL)” along
with the supporting documents.
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}
1
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, 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)
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 your answers into the template file myqueries.sql.
1.1 List the ids of persons whose first name starts with ‘Z’.
1.2 How many movies were categorised in the ‘K-14’ restriction in Finland? List that number.
1.3 How many writers were born after 1930 (inclusive)? List that number.
1.4 How many restriction categories each country has? List the countries and the corresponding numbers of restric-
tion categories. Order your result in the descending order of the numbers of restriction categories.
1.5 How many directors have never directed any romance movies (i.e., the major genre of the movie is romance)?
List that number.
1.6 What is the percentage of Australian movies (i.e., movies produced in Australia) among all movies in this
database? List the percentage as a decimal (round to two decimal places). Hint: in PostgreSQL, the function
ROUND(x, n) can round x to n decimal places, e.g., if x=0.1129, then ROUND(x, 2) = 0.11.
1.7 Which movie(s) won the largest number of crew awards in a single year? List their title(s) and production
year(s).
1.8 How many movies have never won any award (including movie awards, crew awards, director awards, writer
awards and actor awards)? List that number.
1.9 Which director(s) directed the largest variety of movies (i.e., the largest number of distinct major genres)? List
their id(s).
1.10 Which writers always wrote a movie with other writer(s), i.e., every movie written by such a writer has at least
two writers? List their ids, first and last names. Order your result in the ascending order of their last names.