-- ============================================================
-- N5 Computing Science — Databases Unit
-- Films Database
-- James Gillespie's High School
-- Run in DataGrip against your MySQL (Laragon) connection
-- ============================================================


CREATE DATABASE IF NOT EXISTS films;
USE films;



-- ============================================================
-- TABLE: director
-- ============================================================
CREATE TABLE director (
    director_id  INT          PRIMARY KEY,
    first_name   VARCHAR(100) NOT NULL,
    last_name    VARCHAR(100) NOT NULL,
    nationality  VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLE: film
-- ============================================================
CREATE TABLE film (
    film_id       INT           PRIMARY KEY,
    title         VARCHAR(255)  NOT NULL,
    year          INT           NOT NULL,
    certificate   VARCHAR(5)    NOT NULL,
    runtime_mins  INT,
    rating        DECIMAL(3,1)  NOT NULL,
    genre         VARCHAR(50)   NOT NULL,
    director_id   INT           NOT NULL,
    FOREIGN KEY (director_id) REFERENCES director(director_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- INSERT: director (15 directors)
-- ============================================================
INSERT INTO director VALUES (1,  'Steven',      'Spielberg',  'American');
INSERT INTO director VALUES (2,  'Christopher', 'Nolan',      'British');
INSERT INTO director VALUES (3,  'Ridley',      'Scott',      'British');
INSERT INTO director VALUES (4,  'James',       'Cameron',    'Canadian');
INSERT INTO director VALUES (5,  'Tim',         'Burton',     'American');
INSERT INTO director VALUES (6,  'Quentin',     'Tarantino',  'American');
INSERT INTO director VALUES (7,  'Peter',       'Jackson',    'New Zealander');
INSERT INTO director VALUES (8,  'Sofia',       'Coppola',    'American');
INSERT INTO director VALUES (9,  'Wes',         'Anderson',   'American');
INSERT INTO director VALUES (10, 'Greta',       'Gerwig',     'American');
INSERT INTO director VALUES (11, 'Denis',       'Villeneuve', 'Canadian');
INSERT INTO director VALUES (12, 'Bong',        'Joon-ho',    'South Korean');
INSERT INTO director VALUES (13, 'Alfonso',     'Cuarón',     'Mexican');
INSERT INTO director VALUES (14, 'Guillermo',   'del Toro',   'Mexican');
INSERT INTO director VALUES (15, 'Jordan',      'Peele',      'American');

-- ============================================================
-- INSERT: film (52 films, 4 deliberate NULLs in runtime_mins)
-- ============================================================

-- Steven Spielberg
INSERT INTO film VALUES (1,  'Jaws',                                              1975, 'PG',  124, 8.0, 'Thriller',  1);
INSERT INTO film VALUES (2,  'Raiders of the Lost Ark',                           1981, 'PG',  115, 8.4, 'Adventure', 1);
INSERT INTO film VALUES (3,  'E.T. the Extra-Terrestrial',                        1982, 'U',   115, 7.9, 'Drama',     1);
INSERT INTO film VALUES (4,  'Schindler\'s List',                                 1993, '15',  195, 8.9, 'Drama',     1);
INSERT INTO film VALUES (5,  'Jurassic Park',                                     1993, 'PG',  127, 8.1, 'Adventure', 1);
INSERT INTO film VALUES (6,  'Saving Private Ryan',                               1998, '15',  169, 8.6, 'War',       1);

-- Christopher Nolan
INSERT INTO film VALUES (7,  'Memento',                                           2000, '15',  113, 8.4, 'Thriller',  2);
INSERT INTO film VALUES (8,  'Batman Begins',                                     2005, '12A', 140, 8.2, 'Action',    2);
INSERT INTO film VALUES (9,  'The Dark Knight',                                   2008, '12A', 152, 9.0, 'Action',    2);
INSERT INTO film VALUES (10, 'Inception',                                         2010, '12A', 148, 8.8, 'Sci-Fi',    2);
INSERT INTO film VALUES (11, 'Interstellar',                                      2014, 'PG',  169, 8.6, 'Sci-Fi',    2);
INSERT INTO film VALUES (12, 'Dunkirk',                                           2017, 'PG',  106, 7.9, 'War',       2);

-- Ridley Scott
INSERT INTO film VALUES (13, 'Alien',                                             1979, '18',  117, 8.4, 'Sci-Fi',    3);
INSERT INTO film VALUES (14, 'Blade Runner',                                      1982, '15',  117, 8.1, 'Sci-Fi',    3);
INSERT INTO film VALUES (15, 'Gladiator',                                         2000, '15',  155, 8.5, 'Action',    3);
INSERT INTO film VALUES (16, 'The Martian',                                       2015, '12A', 144, 8.0, 'Sci-Fi',    3);

-- James Cameron
INSERT INTO film VALUES (17, 'Terminator 2: Judgment Day',                        1991, '15',  137, 8.5, 'Action',    4);
INSERT INTO film VALUES (18, 'Titanic',                                           1997, '12',  195, 7.9, 'Romance',   4);
INSERT INTO film VALUES (19, 'Avatar',                                            2009, 'PG',  162, 7.8, 'Sci-Fi',    4);

-- Tim Burton
INSERT INTO film VALUES (20, 'Batman',                                            1989, 'PG',  126, 7.5, 'Action',    5);
INSERT INTO film VALUES (21, 'Edward Scissorhands',                               1990, 'PG',  105, 7.9, 'Fantasy',   5);
INSERT INTO film VALUES (22, 'Big Fish',                                          2003, 'PG',  125, 8.0, 'Fantasy',   5);
INSERT INTO film VALUES (23, 'Corpse Bride',                                      2005, 'PG',  NULL, 7.3, 'Animation', 5);

-- Quentin Tarantino
INSERT INTO film VALUES (24, 'Pulp Fiction',                                      1994, '18',  154, 8.9, 'Crime',     6);
INSERT INTO film VALUES (25, 'Kill Bill: Vol. 1',                                 2003, '18',  111, 8.1, 'Action',    6);
INSERT INTO film VALUES (26, 'Django Unchained',                                  2012, '18',  165, 8.4, 'Western',   6);
INSERT INTO film VALUES (27, 'The Hateful Eight',                                 2015, '18',  NULL, 7.8, 'Western',   6);

-- Peter Jackson
INSERT INTO film VALUES (28, 'The Lord of the Rings: The Fellowship of the Ring', 2001, 'PG',  178, 8.8, 'Fantasy',   7);
INSERT INTO film VALUES (29, 'The Lord of the Rings: The Two Towers',             2002, 'PG',  179, 8.7, 'Fantasy',   7);
INSERT INTO film VALUES (30, 'The Lord of the Rings: The Return of the King',     2003, 'PG',  201, 8.9, 'Fantasy',   7);
INSERT INTO film VALUES (31, 'King Kong',                                         2005, '12A', 187, 7.2, 'Adventure', 7);

-- Sofia Coppola
INSERT INTO film VALUES (32, 'The Virgin Suicides',                               1999, '15',   97, 7.2, 'Drama',     8);
INSERT INTO film VALUES (33, 'Lost in Translation',                               2003, '15',  102, 7.7, 'Drama',     8);

-- Wes Anderson
INSERT INTO film VALUES (34, 'Moonrise Kingdom',                                  2012, 'PG',   94, 7.8, 'Comedy',    9);
INSERT INTO film VALUES (35, 'The Grand Budapest Hotel',                          2014, '15',   99, 8.1, 'Comedy',    9);
INSERT INTO film VALUES (36, 'Isle of Dogs',                                      2018, 'PG',  101, 7.8, 'Animation', 9);

-- Greta Gerwig
INSERT INTO film VALUES (37, 'Lady Bird',                                         2017, '15',   94, 7.4, 'Drama',     10);
INSERT INTO film VALUES (38, 'Little Women',                                      2019, 'U',   135, 7.8, 'Drama',     10);
INSERT INTO film VALUES (39, 'Barbie',                                            2023, '12A', 114, 6.9, 'Comedy',    10);

-- Denis Villeneuve
INSERT INTO film VALUES (40, 'Arrival',                                           2016, '12A', 116, 7.9, 'Sci-Fi',    11);
INSERT INTO film VALUES (41, 'Blade Runner 2049',                                 2017, '15',  163, 8.0, 'Sci-Fi',    11);
INSERT INTO film VALUES (42, 'Dune',                                              2021, '12A', 155, 8.0, 'Sci-Fi',    11);

-- Bong Joon-ho
INSERT INTO film VALUES (43, 'Snowpiercer',                                       2013, '15',  126, 7.1, 'Sci-Fi',    12);
INSERT INTO film VALUES (44, 'Parasite',                                          2019, '15',  132, 8.5, 'Drama',     12);

-- Alfonso Cuarón
INSERT INTO film VALUES (45, 'Harry Potter and the Prisoner of Azkaban',          2004, 'PG',  141, 7.9, 'Fantasy',   13);
INSERT INTO film VALUES (46, 'Children of Men',                                   2006, '15',  109, 7.9, 'Sci-Fi',    13);
INSERT INTO film VALUES (47, 'Roma',                                              2018, '15',  NULL, 7.7, 'Drama',    13);

-- Guillermo del Toro
INSERT INTO film VALUES (48, 'Pan\'s Labyrinth',                                  2006, '15',  118, 8.2, 'Fantasy',   14);
INSERT INTO film VALUES (49, 'The Shape of Water',                                2017, '15',  123, 7.3, 'Fantasy',   14);

-- Jordan Peele
INSERT INTO film VALUES (50, 'Get Out',                                           2017, '15',  104, 7.7, 'Horror',    15);
INSERT INTO film VALUES (51, 'Us',                                                2019, '15',  116, 6.8, 'Horror',    15);
INSERT INTO film VALUES (52, 'Nope',                                              2022, '15',  NULL, 6.8, 'Horror',   15);

-- ============================================================
-- END OF FILE
-- Database: films
-- Tables: director (15 rows), film (52 rows, 4 NULL runtimes)
-- NULL runtimes: film_id 23, 27, 47, 52
-- ============================================================
