Movies Database

Movies Database

-- Create the 'directors' table
CREATE TABLE directors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    director_name VARCHAR(255) NOT NULL,
    notes TEXT
);

-- Create the 'genres' table
CREATE TABLE genres (
    id INT AUTO_INCREMENT PRIMARY KEY,
    genre_name VARCHAR(255) NOT NULL,
    notes TEXT
);

-- Create the 'categories' table
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(255) NOT NULL,
    notes TEXT
);

-- Create the 'movies' table
CREATE TABLE movies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    director_id INT,
    copyright_year DATETIME,
    length INT,
    genre_id INT,
    category_id INT,
    rating DECIMAL(3,1),
    notes TEXT,
    FOREIGN KEY (director_id) REFERENCES directors(id),
    FOREIGN KEY (genre_id) REFERENCES genres(id),
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- Populate the 'directors' table with 5 records
INSERT INTO directors (director_name, notes) VALUES
    ('Steven Spielberg', 'One of the most influential directors in Hollywood.'),
    ('Christopher Nolan', 'Known for his mind-bending films.'),
    ('Quentin Tarantino', 'Famous for his unique storytelling and style.'),
    ('Greta Gerwig', 'Acclaimed director and writer.'),
    ('Martin Scorsese', 'Renowned for his work in crime films.');

-- Populate the 'genres' table with 5 records
INSERT INTO genres (genre_name, notes) VALUES
    ('Action', 'Exciting and fast-paced films.'),
    ('Drama', 'Character-driven narratives.'),
    ('Comedy', 'Intended to make the audience laugh.'),
    ('Sci-Fi', 'Deals with futuristic or speculative concepts.'),
    ('Thriller', 'Keeps the audience on the edge of their seats.');

-- Populate the 'categories' table with 5 records
INSERT INTO categories (category_name, notes) VALUES
    ('Adventure', 'Movies that take the audience on a journey.'),
    ('Mystery', 'Involves solving a mysterious event or crime.'),
    ('Romance', 'Focuses on romantic relationships.'),
    ('Horror', 'Intended to scare or horrify the audience.'),
    ('Biography', 'Based on the life of real individuals.');

-- Populate the 'movies' table with 5 records
INSERT INTO movies (title, director_id, copyright_year, length, genre_id, category_id, rating, notes) VALUES
    ('Jurassic Park', 1, 1993, 127, 1, 1, 8.1, 'Classic Spielberg film with dinosaurs.'),
    ('Inception', 2, 2010, 148, 4, 2, 8.8, 'Mind-bending thriller directed by Christopher Nolan.'),
    ('Pulp Fiction', 3, 1994, 154, 1, 4, 8.9, 'Quentin Tarantino\'s iconic crime film.'),
    ('Lady Bird', 4, 2017, 94, 2, 3, 7.4, 'Greta Gerwig\'s coming-of-age comedy-drama.'),