task 16

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', '[e-mail]'),
('user2', '[e-mail]'),
('user3', '[e-mail]');

-- 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');