Comp2400- Assignment: SQL Solved

Relational Databases

 Instructions:

This assignment should be done individually (no group work).
You need to check whether your computer can connect to your own database (i.e., your UID, such as u1234567) and to the database moviedb at the server partch following the instructions below:Log into your account on partch from the lab computer or from your own computer.
To connect to moviedb, enter “psql moviedb”.
You must submit one file: sql for all the questions on Wattle before the due date. You can download the template files from the folder “SQL Assignment for COMP2400” on Wattle. You must enter your queries into the template file, and more specifically,For the submitted file sql, it should be executable in the given database moviedb, i.e., “moviedb=> \i myqueries.sql”.
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/programadministration/assessments-exams/special-assessment-consideration).
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, productionyear, scene no]⊆Scene[title, production year, scene no]

[title, productionyear, 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 and save your queries into the template file myqueries.sql on the desktop.

1.1 Find all people who were born in 1945. List their ids, first names and last names.
 
1.2 How many movies were produced in the USA? List that number.
 
1.3 Which comedy movies (i.e., the major genre of the movie is comedy) were produced in 1993? List the titles of these movies.     (2 Mark)

1.4 How many directors have directed at least one action movie (i.e., the major genre of the movie is action)?

List that number.

1.5 Find all movies that won at least two movie awards. List their titles and production years along with the corresponding number of movie awards. Order your result in ascending order of the number of movie

awards.

1.6 How many directors have never won a director award? List that number.

1.7 Find all writers who have also played roles in at least one movie written by themselves. List their ids, and the titles and production years of the corresponding movies. (2 Mark)

1.8 What is the maximum number of crew members in a movie? List that number.

1.9 Who directed the movie(s) with the maximum number of scenes? List the id(s), first and last name(s).

1.10 A person has worked on a movie if this person is a director, a writer or a crew member of this movie. Who worked on at least three different movies in this database? List their ids, first and last names.

+++++