Our Movie Database

Overview
The Tables
Capturing Relationships

Overview

Our database contains:

the winners of the Academy Awards for Best Picture, Best Director, Best Actress, Best Actor, Best Supporting Actress, and Best Supporting Actor from the Oscars’ inception in 1929 to the present, as well as the movies for which those awards were won
the 200 top-grossing movies of all time (as of September 17, 2024)—i.e., the ones that have made the most money—as well as some former top-grossers that have since been demoted
the director(s) and the five top-billed cast members of each movie included in the database.

The source of our data is a great website called imdb.com, the Internet Movie Database.

The Tables

Below are the schema of the five tables/relations in our movie database, along with additional information about each attribute.

In the schema, the names of the primary-key attributes are underlined.
If an attribute is a foreign key, its description specifies this fact, and it also includes the referenced primary key.

Movie table

Schema:
Movie(id, name, year, rating, runtime, genre, earning_rank)

attribute name	data type	description
`id`	`CHAR(7)`	a unique id assigned to a movie
`name`	`VARCHAR(64)`	the name of the movie
`year`	`INTEGER`	the year the movie was released
`rating`	`VARCHAR(5)`	the MPAA rating of the movie; if the movie is unrated or has a non-standard rating, this value is `NULL`
`runtime`	`INTEGER`	the length of the movie in minutes
`genre`	`VARCHAR(16)`	the genre(s) of the film, formed by concatenating one-letter genre codes (e.g., A for action and D for drama)
`earnings_rank`	`INTEGER`	the earnings rank of the film. The film that has made the most money has an earnings rank of 1; the film that has made the second largest amount of money has an earnings rank of 2, etc. This attribute only has a non-`NULL` value for the 200 top-grossing movies. All other movies have a `NULL` value for their `earnings_rank` value.

Person table

This relation stores information about actors and directors.

Schema:
Person(id, name, dob, pob)

attribute name	data type	description
`id`	`CHAR(7)`	a unique id assigned to a person
`name`	`VARCHAR(128)`	the person’s full name (first name first)
`dob`	`DATE`	the person’s date of birth, if known; `NULL` otherwise
`pob`	`VARCHAR(128)`	the person’s place of birth, if known; `NULL` otherwise

Actor table

This relation captures relationships between actors and the movies in which they have acted.

Schema:
Actor(actor_id, movie_id)

attribute name	data type	description
`actor_id`	`CHAR(7)`	the `id` attribute of an actor; a foreign key that references `Person(id)`
`movie_id`	`CHAR(7)`	the `id` attribute of a movie in which the actor appeared; a foreign key that references `Movie(id)`

Director table

This relation captures relationships between directors and the movies they have directed.

Schema:
Director(director_id, movie_id)

attribute name	data type	description
`director_id`	`CHAR(7)`	the `id` attribute of a director; a foreign key that references `Person(id)`
`movie_id`	`CHAR(7)`	the `id` attribute of a movie that he or she directed; a foreign key that references `Movie(id)`

Oscar table

This relation stores information about Academy Awards.

Schema:
Oscar(movie_id, person_id, type, year)
(See below for a note explaining why this table does not have a primary key.)

attribute name	data type	description
`movie_id`	`CHAR(7)`	the `id` attribute of a movie that was awarded an Oscar; a foreign key that references `Movie(id)`
`person_id`	`CHAR(7)`	the id attribute of the actor, actress, or director who won the award; if type is `'BEST-PICTURE'` (see below), this attribute is `NULL`; a foreign key that references `Person(id)`
`type`	`VARCHAR(23)`	the type of Oscar won, which will be one of the following strings: `'BEST-PICTURE'`, `'BEST-DIRECTOR'`, `'BEST-ACTRESS'`, `'BEST-ACTOR'`, `'BEST-SUPPORTING-ACTRESS'`, `'BEST-SUPPORTING-ACTOR'`
`year`	`INTEGER`	the year in which the Oscar was won

(Note: We chose not to specify a primary key for the Oscar relation. One reason for our decision is that you need the combination of all four attributes in order to guarantee uniqueness. In addition, one of those four attributes (person_id) has NULL values in some tuples, and NULL values are not allowed in primary-key attributes. Instead of specifying a primary key, we use a UNIQUE clause to specify that the combination of the four attributes must be unique.)

Capturing Relationships

As noted in the descriptions of the attributes, the database uses foreign keys to capture relationships.

To see how this works, consider the fact that Tom Hanks won the Best Actor Oscar in 1994 for his performance in the movie Philadelphia, which was directed by Jonathan Demme.

The database includes a tuple for Philadelphia in the Movie table, along with tuples for Hanks and Demme in the Person table.

To capture the necessary relationships, the following tuples are also included:

('0000158', '0107818') appears in the Actor table to capture the fact that Tom Hanks acted in the movie. 0000158 is the id of Hanks in the Person table, and 0107818 is the id of Philadelphia in the Movie table.
('0001129', '0107818') appears in the Director table to capture the fact that Jonathan Demme directed the movie. 0001129 is Demme’s id in the Person table, and we again use the id of the movie.
('0107818', '0000158', 'BEST-ACTOR', 1994) appears in the Oscar table to capture the Oscar that Hanks won for his performance in the movie. The first two columns of this table are both foreign keys. The first attribute records the id of the movie, and the second attribute records the id of Tom Hanks.

Last updated on September 18, 2024.