Describe a set of recommended indexes for this database. Which columns and tables should be included? Assume that read operations happen far more frequently than write operations, and that database space is unlimited. 

Movie Database Details: 

This database will support an application that tracks movies, as well as the performers and production crew for those movies (think IMDB). For each movie, the title, year of release, genre, and parental advisory rating should be stored. For each performer/crew member, first and last name should be stored, as well as date of birth and hometown.

Each performer/crew member can be related to any number of movies. The database should also track the role the individual played in the production of the movie—producer, director, screenwriter, actor, and so forth. For acting roles, the relationship should also track the name of the character portrayed. The database must allow for the same individual to perform multiple roles in the same movie. 

CREATE TABLE movies (

    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    title VARCHAR(255) NOT NULL,

    year INT NOT NULL,

    genre VARCHAR(255) NOT NULL,

    rating VARCHAR(255) NOT NULL

);

 

CREATE TABLE people (

    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    first_name VARCHAR(255),

    last_name VARCHAR(255),

    dob DATE,

    hometown VARCHAR(255)

);

 

CREATE TABLE roles (

    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    movie_id INT NOT NULL,

    person_id INT NOT NULL,

    role VARCHAR(255),

    character_name VARCHAR(255)

);

 

CREATE TABLE reviews (

    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    reviewer_id INT NOT NULL,

    movie_id INT NOT NULL,

    rating INT

);

Answer rating: 100% (QA)

To optimize the performance of this database the following set of recommended indexes can be impleme... View the full answer