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/partial 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}

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]

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 restriction 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