setup_movies_db.sql•4.79 kB
-- Setup script for the Movies database example
-- Run this script to create the database and tables for the example
-- Create database
CREATE DATABASE IF NOT EXISTS movies;
USE movies;
-- Drop tables if they exist
DROP TABLE IF EXISTS movie_cast;
DROP TABLE IF EXISTS movie_genres;
DROP TABLE IF EXISTS movies;
DROP TABLE IF EXISTS actors;
DROP TABLE IF EXISTS genres;
-- Create tables
CREATE TABLE genres (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE
);
CREATE TABLE actors (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
birth_date DATE,
bio TEXT
);
CREATE TABLE movies (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
release_year INT,
rating DECIMAL(3,1),
director VARCHAR(100),
plot_summary TEXT,
runtime_minutes INT
);
CREATE TABLE movie_genres (
movie_id INT,
genre_id INT,
PRIMARY KEY (movie_id, genre_id),
FOREIGN KEY (movie_id) REFERENCES movies(id) ON DELETE CASCADE,
FOREIGN KEY (genre_id) REFERENCES genres(id) ON DELETE CASCADE
);
CREATE TABLE movie_cast (
movie_id INT,
actor_id INT,
character_name VARCHAR(100),
role ENUM('lead', 'supporting', 'cameo') DEFAULT 'supporting',
PRIMARY KEY (movie_id, actor_id, character_name),
FOREIGN KEY (movie_id) REFERENCES movies(id) ON DELETE CASCADE,
FOREIGN KEY (actor_id) REFERENCES actors(id) ON DELETE CASCADE
);
-- Insert sample data
-- Genres
INSERT INTO genres (name) VALUES
('Action'),
('Adventure'),
('Animation'),
('Comedy'),
('Crime'),
('Documentary'),
('Drama'),
('Fantasy'),
('Horror'),
('Mystery'),
('Romance'),
('Sci-Fi'),
('Thriller');
-- Actors
INSERT INTO actors (name, birth_date, bio) VALUES
('Tom Hanks', '1956-07-09', 'American actor and filmmaker'),
('Meryl Streep', '1949-06-22', 'American actress'),
('Leonardo DiCaprio', '1974-11-11', 'American actor and film producer'),
('Jennifer Lawrence', '1990-08-15', 'American actress'),
('Denzel Washington', '1954-12-28', 'American actor, director, and producer'),
('Emma Stone', '1988-11-06', 'American actress'),
('Brad Pitt', '1963-12-18', 'American actor and film producer'),
('Viola Davis', '1965-08-11', 'American actress and producer'),
('Robert Downey Jr.', '1965-04-04', 'American actor'),
('Scarlett Johansson', '1984-11-22', 'American actress');
-- Movies
INSERT INTO movies (title, release_year, rating, director, plot_summary, runtime_minutes) VALUES
('The Shawshank Redemption', 1994, 9.3, 'Frank Darabont', 'Two imprisoned men bond over several years', 142),
('The Godfather', 1972, 9.2, 'Francis Ford Coppola', 'The aging patriarch of an organized crime dynasty transfers control to his son', 175),
('Inception', 2010, 8.8, 'Christopher Nolan', 'A thief who steals corporate secrets through dream-sharing technology', 148),
('Forrest Gump', 1994, 8.8, 'Robert Zemeckis', 'The life journey of a man with low intelligence but good intentions', 142),
('The Matrix', 1999, 8.7, 'Lana and Lilly Wachowski', 'A computer hacker learns about the true nature of reality', 136),
('La La Land', 2016, 8.0, 'Damien Chazelle', 'A pianist and an actress fall in love while pursuing their dreams', 128),
('Parasite', 2019, 8.6, 'Bong Joon Ho', 'A poor family schemes to become employed by a wealthy family', 132),
('The Dark Knight', 2008, 9.0, 'Christopher Nolan', 'Batman faces his greatest challenge against the Joker', 152),
('Pulp Fiction', 1994, 8.9, 'Quentin Tarantino', 'The lives of two mob hitmen, a boxer, and a gangster\'s wife intertwine', 154),
('Toy Story', 1995, 8.3, 'John Lasseter', 'A cowboy doll is threatened by the arrival of a new space toy', 81);
-- Movie genres
INSERT INTO movie_genres (movie_id, genre_id) VALUES
(1, 7), -- Shawshank: Drama
(2, 5), -- Godfather: Crime
(2, 7), -- Godfather: Drama
(3, 1), -- Inception: Action
(3, 12), -- Inception: Sci-Fi
(3, 10), -- Inception: Mystery
(4, 7), -- Forrest Gump: Drama
(4, 11), -- Forrest Gump: Romance
(5, 1), -- Matrix: Action
(5, 12), -- Matrix: Sci-Fi
(6, 4), -- La La Land: Comedy
(6, 7), -- La La Land: Drama
(6, 11), -- La La Land: Romance
(7, 4), -- Parasite: Comedy
(7, 7), -- Parasite: Drama
(7, 13), -- Parasite: Thriller
(8, 1), -- Dark Knight: Action
(8, 5), -- Dark Knight: Crime
(8, 7), -- Dark Knight: Drama
(9, 5), -- Pulp Fiction: Crime
(9, 7), -- Pulp Fiction: Drama
(10, 3), -- Toy Story: Animation
(10, 2), -- Toy Story: Adventure
(10, 4); -- Toy Story: Comedy
-- Movie cast
INSERT INTO movie_cast (movie_id, actor_id, character_name, role) VALUES
(4, 1, 'Forrest Gump', 'lead'),
(6, 6, 'Mia', 'lead'),
(3, 3, 'Cobb', 'lead'),
(8, 5, 'Detective', 'supporting'),
(9, 7, 'Vincent Vega', 'lead'),
(5, 9, 'Neo', 'supporting'),
(7, 2, 'Housekeeper', 'cameo'),
(10, 1, 'Woody', 'lead'),
(5, 10, 'Trinity', 'supporting'),
(3, 6, 'Ariadne', 'supporting');