task 16
Published: 2023-12-03 07:13:55

1) -- Create the IMDB database
CREATE DATABASE IF NOT EXISTS IMDB;
USE IMDB;

-- Create the Movie table
CREATE TABLE IF NOT EXISTS Movie (
    movie_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    release_date DATE,
    genre VARCHAR(100),
    director VARCHAR(100),
    CONSTRAINT unique_title UNIQUE (title)
);

-- Create the Image table
CREATE TABLE IF NOT EXISTS Image (
    image_id INT PRIMARY KEY AUTO_INCREMENT,
    movie_id INT,
    image_url VARCHAR(255) NOT NULL,
    CONSTRAINT fk_movie_id FOREIGN KEY (movie_id) REFERENCES Movie(movie_id) ON DELETE CASCADE
);

-- Insert sample data into the Movie table
INSERT INTO Movie (title, release_date, genre, director) VALUES
    ('Movie1', '2023-01-01', 'Action', 'Director1'),
    ('Movie2', '2023-02-01', 'Drama', 'Director2'),
    ('Movie3', '2023-03-01', 'Comedy', 'Director3');

-- Insert sample data into the Image table
INSERT INTO Image (movie_id, image_url) VALUES
    (1, 'image1.jpg'),
    (1, 'image2.jpg'),
    (2, 'image3.jpg'),
    (3, 'image4.jpg');
3 ) -- Create the IMDB database
CREATE DATABASE IF NOT EXISTS IMDB;
USE IMDB;

-- Create the User table
CREATE TABLE IF NOT EXISTS User (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    CONSTRAINT unique_username UNIQUE (username),
    CONSTRAINT unique_email UNIQUE (email)
);

-- Create the Movie table
CREATE TABLE IF NOT EXISTS Movie (
    movie_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    release_date DATE,
    genre VARCHAR(100),
    director VARCHAR(100)
);

-- Create the Review table
CREATE TABLE IF NOT EXISTS Review (
    review_id INT PRIMARY KEY AUTO_INCREMENT,
    movie_id INT,
    user_id INT,
    rating DECIMAL(2, 1) NOT NULL,
    comment TEXT,
    CONSTRAINT fk_movie_id FOREIGN KEY (movie_id) REFERENCES Movie(movie_id) ON DELETE CASCADE,
    CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES User(user_id) ON DELETE CASCADE
);

-- Insert sample data into the User table
INSERT INTO User (username, email) VALUES
    ('user1', 'user1@example.com'),
    ('user2', 'user2@example.com'),
    ('user3', 'user3@example.com');

-- Insert sample data into the Movie table
INSERT INTO Movie (title, release_date, genre, director) VALUES
    ('Movie1', '2023-01-01', 'Action', 'Director1'),
    ('Movie2', '2023-02-01', 'Drama', 'Director2'),
    ('Movie3', '2023-03-01', 'Comedy', 'Director3');

-- Insert sample data into the Review table
INSERT INTO Review (movie_id, user_id, rating, comment) VALUES
    (1, 1, 4.5, 'Great movie!'),
    (1, 2, 3.8, 'Enjoyed it.'),
    (2, 3, 4.2, 'Interesting plot.'),
    (3, 1, 3.5, 'Funny moments.'),
    (3, 2, 4.0, 'Loved the cast.');
4)-- Create the IMDB database
CREATE DATABASE IF NOT EXISTS IMDB;
USE IMDB;

-- Create the Artist table
CREATE TABLE IF NOT EXISTS Artist (
    artist_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    birthdate DATE,
    nationality VARCHAR(50),
    bio TEXT
);

-- Create the Skill table
CREATE TABLE IF NOT EXISTS Skill (
    skill_id INT PRIMARY KEY AUTO_INCREMENT,
    artist_id INT,
    skill_name VARCHAR(50) NOT NULL,
    CONSTRAINT fk_artist_id FOREIGN KEY (artist_id) REFERENCES Artist(artist_id) ON DELETE CASCADE
);

-- Insert sample data into the Artist table
INSERT INTO Artist (name, birthdate, nationality, bio) VALUES
    ('Artist1', '1990-01-01', 'Nationality1', 'Bio1'),
    ('Artist2', '1985-02-15', 'Nationality2', 'Bio2'),
    ('Artist3', '1995-03-30', 'Nationality3', 'Bio3');

-- Insert sample data into the Skill table
INSERT INTO Skill (artist_id, skill_name) VALUES
    (1, 'Painting'),
    (1, 'Sculpting'),
    (2, 'Drawing'),
    (3, 'Photography'),
    (3, 'Digital Art');
5 ) -- Create the IMDB database
CREATE DATABASE IF NOT EXISTS IMDB;
USE IMDB;

-- Create the Artist table
CREATE TABLE IF NOT EXISTS Artist (
    artist_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    birthdate DATE,
    nationality VARCHAR(50),
    bio TEXT
);

-- Create the Film table
CREATE TABLE IF NOT EXISTS Film (
    film_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    release_date DATE,
    genre VARCHAR(100),
    director VARCHAR(100)
);

-- Create the Role table (Join table)
CREATE TABLE IF NOT EXISTS Role (
    role_id INT PRIMARY KEY AUTO_INCREMENT,
    artist_id INT,
    film_id INT,
    role_name VARCHAR(50) NOT NULL,
    CONSTRAINT fk_artist_id FOREIGN KEY (artist_id) REFERENCES Artist(artist_id) ON DELETE CASCADE,
    CONSTRAINT fk_film_id FOREIGN KEY (film_id) REFERENCES Film(film_id) ON DELETE CASCADE
);

-- Insert sample data into the Artist table
INSERT INTO Artist (name, birthdate, nationality, bio) VALUES
    ('Actor1', '1990-01-01', 'Nationality1', 'Bio1'),
    ('Actor2', '1985-02-15', 'Nationality2', 'Bio2'),
    ('Director1', '1970-05-20', 'Nationality3', 'Bio3');

-- Insert sample data into the Film table
INSERT INTO Film (title, release_date, genre, director) VALUES
    ('Film1', '2023-01-01', 'Action', 'Director1'),
    ('Film2', '2023-02-01', 'Drama', 'Director1'),
    ('Film3', '2023-03-01', 'Comedy', 'Director1');

-- Insert sample data into the Role table
INSERT INTO Role (artist_id, film_id, role_name) VALUES
    (1, 1, 'Lead Actor'),
    (2, 1, 'Supporting Actor'),
    (1, 2, 'Lead Actor'),
    (2, 2, 'Cameo'),
    (3, 1, 'Director'),
    (3, 2, 'Director'),
    (3, 3, 'Director');