COMP6240 - Relational Databases - Assignment 1 (SQL)
Instructions:
COMP6240 - Relational Databases Assignment 1 (SQL)
Due date: 23:59, 30 August, 2022
- 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 <yu.lin@anu.edu.au> 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
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 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.
