Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

May I ask the SQL code as follows? The relational database moviedb has the following database schema: Movie(title, production year, country, run time, major genre)

May I ask the SQL code as follows?

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.5 Which year was the youngest director (i.e., the youngest person in the database who has ever directed a movie) born in? List that year. (1 Mark)

1.6 How many crew members have won at least one crew award? List that number. (1 Mark)

1.7 What is the maximum number of scenes in any movie? List that number. (1.5 Mark)

1.8 Give the list of directors who have directed at least two American movies (i.e., produced in USA). Provide their ids and the number of American movies that they each have directed. (1.5 Mark)

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Concepts of Database Management

Authors: Philip J. Pratt, Mary Z. Last

8th edition

1285427106, 978-1285427102

More Books

Students also viewed these Databases questions

Question

Define sprawl.

Answered: 1 week ago

Question

How do modern Dashboards differ from earlier implementations?

Answered: 1 week ago