Veronika Portfoolio

Menu

Andmebaas MOVIES

1 Создание базы данных

CREATE DATABASE MoviesDB;
USE MoviesDB;

2 Создание всех таблиц

-- 1. Таблица person
CREATE TABLE person (
    id INT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    birth_date DATE
);

-- 2. Таблица movie
CREATE TABLE movie (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    release_year INT,
    rating DECIMAL(3,1)
);

-- 3. Таблица picture
CREATE TABLE picture (
    id INT PRIMARY KEY,
    picture_url VARCHAR(255),
    person_id INT,
    FOREIGN KEY (person_id) REFERENCES person(id)
);

-- 4. Таблица position (режиссёр, актёр и т.д.)
CREATE TABLE position (
    id INT PRIMARY KEY,
    position_name VARCHAR(100)
);

-- 5. Таблица person_in_movie (многие-ко-многим + позиция)
CREATE TABLE person_in_movie (
    id INT PRIMARY KEY,
    person_id INT,
    movie_id INT,
    position_id INT,
    FOREIGN KEY (person_id) REFERENCES person(id),
    FOREIGN KEY (movie_id) REFERENCES movie(id),
    FOREIGN KEY (position_id) REFERENCES position(id)
);

-- 6. Таблица quote (цитаты персонажей)
CREATE TABLE quote (
    id INT PRIMARY KEY,
    quote_text TEXT,
    person_id INT,
    FOREIGN KEY (person_id) REFERENCES person(id)
);

-- 7. Таблица genre
CREATE TABLE genre (
    id INT PRIMARY KEY,
    genre_name VARCHAR(100)
);

-- 8. Таблица movie_genre (многие-ко-многим фильмы–жанры)
CREATE TABLE movie_genre (
    id INT PRIMARY KEY,
    movie_id INT,
    genre_id INT,
    FOREIGN KEY (movie_id) REFERENCES movie(id),
    FOREIGN KEY (genre_id) REFERENCES genre(id)
);

-- 9. Таблица production_company
CREATE TABLE production_company (
    id INT PRIMARY KEY,
    company_name VARCHAR(150)
);

-- 10. Таблица movie_by_production_company (многие-ко-многим)
CREATE TABLE movie_by_production_company (
    id INT PRIMARY KEY,
    movie_id INT,
    company_id INT,
    FOREIGN KEY (movie_id) REFERENCES movie(id),
    FOREIGN KEY (company_id) REFERENCES production_company(id)
);

2 Проверяю, что все таблицы созданы и отображаются в БД MoviesDB ➜ Tables:

3 Заполняю все таблицы данными

MoviesDB ➜ выбираю таблицу, которую хочу заполнить➜ Edit Top 200 Rows➜ Вписываю данные

4 Делаю связи между таблицами

Создаём New Database Diagram для построения связей и присвоение первичных PK и вторичных ключей FK.

Делаем связи для всех таблиц нажимая на таблицу, где вторичный ключ FK и выбирая Relationships:

Связь person_in_movie(person_id) c person(id):

Связь person_in_movie(movie_id) c movie(id):

Связь picture(person_id) c person(id):

Связь quote(person_in_movie_id) c person_in_movie(id):

Связь person_in_movie(position_id) c position(id):

Связь movie_genre(genre_id) c genre(id):

Связь movie_by_production_company(movie_id) c movie(id):

Связь movie_by_production_company(production_company_id) c 

company(id):

Связь movie_genre(movie_id) с movie(id):

Готовые связи:

3 процедуры

1. Процедура Get_Movies_With_Genres

Извлекает список фильмов с их соответствующими жанрами.
Задействованные таблицы: movie, movie_genre, genre

CREATE PROCEDURE Get_Movies_With_Genres
AS
BEGIN
    SELECT 
        movie.title AS MovieTitle,
        genre.genre_name AS Genre
    FROM movie
    INNER JOIN movie_genre ON movie.id = movie_genre.movie_id
    INNER JOIN genre ON movie_genre.genre_id = genre.id;
END;

2. Процедура Get_Persons_In_Movies

Показывает, кто работал над каким фильмом, с указанием должности (актёр, режиссёр и т.д.).
Задействованные таблицы: person_in_movie, person, movie, position

CREATE PROCEDURE Get_Actors_In_Movies
AS
BEGIN
    SELECT 
        person.first_name,
        person.last_name,
        movie.title AS MovieTitle,
        person_in_movie.role
    FROM person
    INNER JOIN person_in_movie ON person.id = person_in_movie.person_id
    INNER JOIN movie ON person_in_movie.movie_id = movie.id;
END;

3. Процедура Get_Quotes_With_Person

Показывает цитаты из фильмов и имена персонажей, которые их произнесли.
Задействованные таблицы: quote, person_in_movie, person, movie

CREATE PROCEDURE Get_Quotes_With_Person
AS
BEGIN
    SELECT 
        quote.quote_text,
        person.first_name,
        person.last_name,
        movie.title AS MovieTitle
    FROM quote
    INNER JOIN person_in_movie ON quote.person_in_movie_id = person_in_movie.id
    INNER JOIN person ON person_in_movie.person_id = person.id
    INNER JOIN movie ON person_in_movie.movie_id = movie.id;
END;