SQL代写数据库 | COMP2400 – Relational Databases Assignment: SQL

本次SQL代写为根据背景完成10个question的SQL

Instructions:
COMP2400 – Relational Databases Assignment: SQL
Due date: 11:59pm, Aug 27, 2019
• This assignment should be done individually (no group work).
• This assignment will count for 20% of the final grade.
• 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: myqueries.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 myqueries.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/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]
[id] ⊆ Person[id]
20 Marks
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 :
{id, title, production year}
[title, production year] ⊆ Movie[title, production year] [id] ⊆ Person[id]
Scene(title, production year, scene no, description)
primary key : foreign keys :
{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) 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 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. (2 Mark)
1.2 How many movies were produced in the USA? List that number. (2 Mark)
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. (2 Mark)
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. (2 Mark)
1.6 How many directors have never won a director award? List that number. (2 Mark)
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. (2 Mark)
1.9 Who directed the movie(s) with the maximum number of scenes? List the id(s), first and last name(s). (2 Mark)
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. (2 Mark)
+++++
3


程序代写代做C/C++/JAVA/安卓/PYTHON/留学生/PHP/APP开发/MATLAB


本网站支持淘宝 支付宝 微信支付  paypal等等交易。如果不放心可以用淘宝交易!

E-mail: [email protected]  微信:dmxyzl003


如果您使用手机请先保存二维码,微信识别。如果用电脑,直接掏出手机果断扫描。

发表评论