Instructions:
COMP2400 – Relational Databases Assignment 1 (SQL)
Due date: 23:59, September 3, 2021
• 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
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]
20 Marks
[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 : foreign keys :
Scene(title, production primary key : foreign keys :
{id, title, production year}
[title, production year] ⊆ Movie[title, production year] [id] ⊆ Person[id]
year, scene no, description)
{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) 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