数据库代写 | TEC 3220 Winter 2021 Assignment 3

本次加拿大CS代写主要是使用database完成电影数据的SQL查询

Assignment 3

ITEC 3220 Winter 2021
Due Date: last day of classes in the Winter2021 term at 11:59pm

General instructions: You can work in teams of up to 4 members. Only one member of the team should submit the assignment. The cover page of each assignment should include the last name, first name, student number of all the team members. The assignment must be typed. Your submission should consist of one file in PDF format. A query needs to be syntactically correct for full marks.

Submission: upload your submission files on e-Class. You can work in teams of up to four students. Consider a database containing information related to movies organized in tables corresponding to the

following relations:
Movie(title, year, genre, budget, gross)
Director(name, YofB, country,)
Actor(name, YofB, country,)
Producer(name, YofB, country)
DirectorMovie(d_name, d_YofB, m_title, m_year) ActorMovie(a_name, a_YofB, m_title, m_year) ProducerMovie(p_name, p_YofB, m_title, m_year) DirectorMovie[d_name, d_YofB] Í Director[name, YofB] DirectorMovie[m_title, m_year] Í Movie[name, year] ActorMovie[a_name, a_YofB] Í Actor[name, YofB] ActorMovie[m_title, m_year] Í Movie[name, year] ProducerMovie[p_name, p_YofB] Í Producer[name, YofB] ProducerMovie[m_title, m_year] Í Movie[name, year]

Attribute genre in table Movie has as value one of {“comedy”, “drama”, “tragedy”, “musical”, “horror”}. Attributes YofB and year have integer values. Atributes budget and gross have integer values. Attribute country has values such as “Canada”, “India”, “UK”, “USA”, “Mexico”, etc.

Part 1. [20 points] Write the following queries in Relational Algebra.

  1. Find producers who directed and produced the same movie.
  2. Find actors who were younger than all the directors they were directed by in a movie
  3. Find directors who only directed dramas
  4. Find actors who never acted in a drama.

Part 2. [5 points] Write CREATE TABLE statements for the relations described above.

Sample statement:

CREATE TABLE MOVIE(
TITLE VARCHAR(100) NOT NULL, YEAR INTEGER NOT NULL, GENRE VARCHAR(20) NOT NULL, BUDGET INTEGER NOT NULL, GROSS INTEGER NOT NULL, PRIMARY KEY (TITLE,YEAR));

Part 3. [20 points] Write the following queries in SQL

  1. List the movies with a young cast, i.e. all actors who worked on that movie are under the age of 30.
  2. List all directors in the order of their average profit across all the movies they directed (the profit for

    each movie is calculated as the difference between its gross earnings and its budget).

  3. List all actors who were very prolific in their first 5 years of activity, i.e., they acted in at least one

    movie each year, in the 5 years after their first movie (i.e., the year of their first movie counts as the

    first of 5 years.)

  4. List producers who produced more dramas than comedies and tragedies, combined.