Andmebaas MOVIES

Sisukord show

Ülesanne

Kasuta SQL Server Managment Stuudio või mariaDB/XAMPP

Loo tabelid, primaarsed võtmed ja välisvõtmed. Graafilises keskkonnas! ja tee vastava konspekti.

Täida tabelid (vähemalt 5 rida igas tabelis)

——————- protseduurite, trigerite ja kasutajate loomiseks saab SQL laused ka kasutada (ei tohi Chat GPT kasutada!)

1. Koosta 3 protseduuri, mis on loodud seotud (JOIN) tabelite põhjal. Iseseisvalt määra mida protseduurid teevad. Käivita neid.

2. Loo 2 kasutajat (admin, kes saab teha kõike kõikidega tabelitega / kasutajaNimi, kes saab lisada ja kontrollida andmed tabelites: person, pictures.

3. Loo 2-3 trigerid, mis jälgivad ainult põhitabelite täitmine/muutmine (näiteks, need tabelid kus on olemas FK).

4. kasutajaNimi ei pea näha logi tabeli, kuhu triger salvestab oma andmed.

5. Kõik tegevused on vaja kontrollida nii moodi, et aru saada mis kasutaja mida teeb!

Täitmiskäik

Andmebaasi tabelite ja skeemi loomine

Loon andmebaasi skeem LucidChart-is

Ekspordime Lucichartist andmebaasi koodi.

Andmebaasi Movie loome SQL Server Management Stuudios. Tabelid lisatakse SQL-koodi abil käsitsi.

CREATE TABLE [genre] (
  [id] INT IDENTITY(1,1) PRIMARY KEY,  -- Lisatud IDENTITY
  [genre_name] VARCHAR(50),
  [description] VARCHAR(100)
);

CREATE INDEX [N] ON [genre] ([description]);

CREATE TABLE [movie] (
  [id] INT IDENTITY(1,1) PRIMARY KEY,  -- Lisatud IDENTITY
  [title] VARCHAR(100),
  [production_year] INT,
  [duration] INT,
  [description] VARCHAR(100)
);

CREATE INDEX [N] ON [movie] ([description]);

CREATE TABLE [movie_genre] (
  [id] INT IDENTITY(1,1) PRIMARY KEY,  -- Lisatud IDENTITY
  [movie_id] INT,
  [genre_id] INT,
  CONSTRAINT [FK_movie_genre.movie_id]
    FOREIGN KEY ([movie_id]) REFERENCES [movie]([id]),
  CONSTRAINT [FK_movie_genre.genre_id]
    FOREIGN KEY ([genre_id]) REFERENCES [genre]([id])
);

CREATE TABLE [person] (
  [id] INT IDENTITY(1,1) PRIMARY KEY,  -- Lisatud IDENTITY
  [first_name] VARCHAR(100),
  [last_name] VARCHAR(100),
  [birth_date] DATE
);

CREATE TABLE [position] (
  [id] INT IDENTITY(1,1) PRIMARY KEY,  -- Lisatud IDENTITY
  [position_name] VARCHAR(50),
  [description] VARCHAR(100)
);

CREATE INDEX [N] ON [position] ([description]);

CREATE TABLE [person_in_movie] (
  [id] INT IDENTITY(1,1) PRIMARY KEY,  -- Lisatud IDENTITY
  [person_id] INT,
  [movie_id] INT,
  [position_id] INT,
  [role] VARCHAR(50),
  CONSTRAINT [FK_person_in_movie.position_id]
    FOREIGN KEY ([position_id]) REFERENCES [position]([id]),
  CONSTRAINT [FK_person_in_movie.movie_id]
    FOREIGN KEY ([movie_id]) REFERENCES [movie]([id]),
  CONSTRAINT [FK_person_in_movie.person_id]
    FOREIGN KEY ([person_id]) REFERENCES [person]([id])
);

CREATE INDEX [N] ON [person_in_movie] ([role]);

CREATE TABLE [production_company] (
  [id] INT IDENTITY(1,1) PRIMARY KEY,  -- Lisatud IDENTITY
  [company_name] VARCHAR(100),
  [company_address] VARCHAR(100)
);

CREATE TABLE [movie_by_production_company] (
  [id] INT IDENTITY(1,1) PRIMARY KEY,  -- Lisatud IDENTITY
  [movie_id] INT,
  [production_company_id] INT,
  CONSTRAINT [FK_movie_by_production_company.movie_id]
    FOREIGN KEY ([movie_id]) REFERENCES [movie]([id]),
  CONSTRAINT [FK_movie_by_production_company.production_company_id]
    FOREIGN KEY ([production_company_id]) REFERENCES [production_company]([id])
);

CREATE TABLE [picture] (
  [id] INT IDENTITY(1,1) PRIMARY KEY,  -- Lisatud IDENTITY
  [picture_file_name] VARCHAR(100),
  [person_id] INT,
  CONSTRAINT [FK_picture.person_id]
    FOREIGN KEY ([person_id]) REFERENCES [person]([id])
);

CREATE TABLE [quote] (
  [id] INT IDENTITY(1,1) PRIMARY KEY,  -- Lisatud IDENTITY
  [quote_text] VARCHAR(100),
  [person_in_movie_id] INT,
  CONSTRAINT [FK_quote.person_in_movie_id]
    FOREIGN KEY ([person_in_movie_id]) REFERENCES [person_in_movie]([id])
);

Täidame tabelid (vähemalt 5 rida igas tabelis)

📥 1. genre

INSERT INTO genre VALUES ('Action', 'Fast-paced, exciting scenes');
INSERT INTO genre VALUES ('Drama', 'Emotional and narrative-driven');
INSERT INTO genre VALUES ('Comedy', 'Humorous content');
INSERT INTO genre VALUES ('Sci-Fi', 'Science fiction themes');
INSERT INTO genre VALUES ('Horror', 'Scary and suspenseful');

🎬 2. movie

INSERT INTO movie VALUES ('Skyfall', 2012, 143, 'James Bond film');
INSERT INTO movie VALUES ('Interstellar', 2014, 169, 'Space exploration');
INSERT INTO movie VALUES ('The Hangover', 2009, 100, 'Bachelor party comedy');
INSERT INTO movie VALUES ('The Godfather', 1972, 175, 'Mafia family story');
INSERT INTO movie VALUES ('Get Out', 2017, 104, 'Social horror thriller');

🎥 3. movie_genre

INSERT INTO movie_genre VALUES (1, 1);
INSERT INTO movie_genre VALUES (2, 4);
INSERT INTO movie_genre VALUES (3, 3);
INSERT INTO movie_genre VALUES (4, 2);
INSERT INTO movie_genre VALUES (5, 5);

👤 4. person

INSERT INTO person VALUES ('Daniel', 'Craig', '1968-03-02');
INSERT INTO person VALUES ('Matthew', 'McConaughey', '1969-11-04');
INSERT INTO person VALUES ('Bradley', 'Cooper', '1975-01-05');
INSERT INTO person VALUES ('Marlon', 'Brando', '1924-04-03');
INSERT INTO person VALUES ('Daniel', 'Kaluuya', '1989-02-24');

🧑‍💼🎬 5. position

INSERT INTO position VALUES ('Director', 'Directs the movie');
INSERT INTO position VALUES ('Actor', 'Performs roles');
INSERT INTO position VALUES ('Writer', 'Writes script');
INSERT INTO position VALUES ('Producer', 'Oversees production');
INSERT INTO position VALUES ('Editor', 'Edits final cut');

🧑‍🎬 6. person_in_movie

INSERT INTO person_in_movie VALUES (1, 1, 2, 'James Bond');
INSERT INTO person_in_movie VALUES (2, 2, 2, 'Cooper');
INSERT INTO person_in_movie VALUES (3, 3, 2, 'Phil');
INSERT INTO person_in_movie VALUES (4, 4, 2, 'Vito Corleone');
INSERT INTO person_in_movie VALUES (5, 5, 2, 'Chris Washington');

🏢 7. production_company

INSERT INTO production_company VALUES ('Eon Productions', 'London, UK');
INSERT INTO production_company VALUES ('Syncopy', 'Los Angeles, USA');
INSERT INTO production_company VALUES ('Legendary Pictures', 'Burbank, USA');
INSERT INTO production_company VALUES ('Paramount Pictures', 'Hollywood, USA');
INSERT INTO production_company VALUES ('Blumhouse', 'Los Angeles, USA');

📽️ 8. movie_by_production_company

INSERT INTO movie_by_production_company VALUES (1, 1);
INSERT INTO movie_by_production_company VALUES (2, 2);
INSERT INTO movie_by_production_company VALUES (3, 3);
INSERT INTO movie_by_production_company VALUES (4, 4);
INSERT INTO movie_by_production_company VALUES (5, 5);

🖼️ 9. picture

INSERT INTO picture VALUES ('craig.jpg', 1);
INSERT INTO picture VALUES ('mcconaughey.jpg', 2);
INSERT INTO picture VALUES ('cooper.jpg', 3);
INSERT INTO picture VALUES ('brando.jpg', 4);
INSERT INTO picture VALUES ('kaluuya.jpg', 5);

🗣️ 10. quote

INSERT INTO quote VALUES ('The name’s Bond, James Bond.', 1);
INSERT INTO quote VALUES ('Do not go gentle into that good night.', 2);
INSERT INTO quote VALUES ('What happens in Vegas, stays in Vegas.', 3);
INSERT INTO quote VALUES ('I’m gonna make him an offer he can’t refuse.', 4);
INSERT INTO quote VALUES ('Get out.', 5);

1. Protseduurid seotud tabelite põhjal: loomine ja käivitamine

1.1. Protseduur, mis lisab uue filmi ja seob selle valitud žanriga:

CREATE PROCEDURE AddMovieWithGenre
    @title VARCHAR(100),
    @production_year INT,
    @duration INT,
    @description VARCHAR(100),
    @genre_id INT
AS
BEGIN
    -- Lisame uue filmi tabelisse movie
    INSERT INTO movie (title, production_year, duration, description)
    VALUES (@title, @production_year, @duration, @description);

    -- Saame ID, mis lisati filmi tabelisse, kasutades SCOPE_IDENTITY
    DECLARE @movie_id INT = SCOPE_IDENTITY();

    -- Lisame filmi žanri
    INSERT INTO movie_genre (movie_id, genre_id)
    VALUES (@movie_id, @genre_id);

    -- Tagastame filmi info
    SELECT 
        m.id AS MovieID, 
        m.title AS MovieTitle, 
        m.production_year AS ProductionYear, 
        m.duration AS Duration, 
        m.description AS Description, 
        g.genre_name AS Genre
    FROM 
        movie m
    INNER JOIN 
        movie_genre mg ON m.id = mg.movie_id
    INNER JOIN 
        genre g ON mg.genre_id = g.id
    WHERE 
        m.id = @movie_id;
END;

EXEC AddMovieWithGenre 
    @title = 'The Hunger Games', 
    @production_year = 2012, 
    @duration = 142, 
    @description = 'Film "The Hunger Games" põhineb Suzanne Collinsi populaarsel romaanil ja kujutab düstoopilist ühiskonda, kus noored peavad ellujäämise nimel võitlema.', 
    @genre_id = 6;

1.2. Protseduur filmis osalevate näitlejate ja nende rollide leidmiseks:

CREATE PROCEDURE GetActorsInMovie
@movie_id INT
AS
BEGIN
    -- Saame kõik filmis osalevad näitlejad koos nende rollidega
    SELECT 
        p.first_name AS eesnimi,  -- Näitleja eesnimi
        p.last_name AS perenimi,  -- Näitleja perenimi
        pim.role AS roll,  -- Näitleja roll filmis
        m.title AS film_pealkiri  -- Filmi nimi
    FROM 
        person_in_movie pim
    JOIN 
        person p ON pim.person_id = p.id  -- Ühendame näitlejad filmidega
    JOIN 
        movie m ON pim.movie_id = m.id  -- Ühendame näitlejad filmidega
    WHERE 
        m.id = @movie_id  -- Filtreerime vastavalt valitud filmile
    ORDER BY 
        p.last_name;  -- Sorteerime näitlejad perenime järgi
END;

EXEC GetActorsInMovie @movie_id = 1;

1.3. Protseduur filmi lisamiseks:

CREATE PROCEDURE GetMoviesByProductionCompany
@company_name VARCHAR(100)  -- Sisestame tootmisfirma nime
AS
BEGIN
    -- Näitame kõik filmid, mida see firma on teinud
    SELECT 
        p.company_name AS tootmisfirma, -- Firma nimi
        m.title AS film_nimi, -- Filmi pealkiri
        m.production_year AS aasta, -- Millal film tehti
        m.duration AS kestus_minutites,  -- Kui pikk film on
        m.description AS kirjeldus  -- Lühikirjeldus filmist
    FROM 
        production_company p
        INNER JOIN movie_by_production_company mbpc ON p.id = mbpc.production_company_id
        INNER JOIN movie m ON mbpc.movie_id = m.id
    WHERE 
        p.company_name = company_name;  -- Otsime täpselt selle firma järgi
END;

EXEC GetMoviesByProductionCompany @company_name = 'Warner Bros';

Protseduur: Filmi lisamine koos täieliku infoga (žanr, näitleja, roll, tootmisfirma)

CREATE PROCEDURE AddMovieWithFullInfo
    @title VARCHAR(100),
    @production_year INT,
    @duration INT,
    @description VARCHAR(100),
    @genre_name VARCHAR(50),
    @person_first_name VARCHAR(100),
    @person_last_name VARCHAR(100),
    @person_role VARCHAR(50),
    @company_name VARCHAR(100)
AS
BEGIN
    DECLARE @movie_id INT;  -- Filmi ID
    DECLARE @genre_id INT;  -- Žanri ID
    DECLARE @person_id INT;  -- Näitleja ID
    DECLARE @company_id INT;  -- Tootmisfirma ID

    -- Lisame filmi 'movie' tabelisse
    INSERT INTO movie (title, production_year, duration, description)
    VALUES (@title, @production_year, @duration, @description);
    
    -- Saame uue filmi ID
    SET @movie_id = SCOPE_IDENTITY();

    -- Lisame žanri (kui seda pole veel) ja saame selle ID
    IF NOT EXISTS (SELECT 1 FROM genre WHERE genre_name = @genre_name)
    BEGIN
        INSERT INTO genre (genre_name, description)
        VALUES (@genre_name, '');  -- Kirjeldus on tühi, täiendage, kui vajalik
    END
    SET @genre_id = (SELECT id FROM genre WHERE genre_name = @genre_name);

    -- Seome filmi žanriga 'movie_genre' tabelis
    INSERT INTO movie_genre (movie_id, genre_id)
    VALUES (@movie_id, @genre_id);

    -- Lisame näitleja (kui teda pole veel) ja saame tema ID
    IF NOT EXISTS (SELECT 1 FROM person WHERE first_name = @person_first_name AND last_name = @person_last_name)
    BEGIN
        INSERT INTO person (first_name, last_name, birth_date)
        VALUES (@person_first_name, @person_last_name, '1900-01-01');  -- Sünnikuupäev on määratud, täiendage vajadusel
    END
    SET @person_id = (SELECT id FROM person WHERE first_name = @person_first_name AND last_name = @person_last_name);

    -- Lisame näitleja filmi 'person_in_movie' tabelis
    INSERT INTO person_in_movie (person_id, movie_id, position_id, role)
    VALUES (@person_id, @movie_id, 1, @person_role);  -- Positsioon määratud kui 1, täiendage vajadusel

    -- Lisame tootmisfirma (kui seda pole veel) ja saame firma ID
    IF NOT EXISTS (SELECT 1 FROM production_company WHERE company_name = @company_name)
    BEGIN
        INSERT INTO production_company (company_name, company_address)
        VALUES (@company_name, '');  -- Aadress on tühi, täiendage vajadusel
    END
    SET @company_id = (SELECT id FROM production_company WHERE company_name = @company_name);

    -- Seome filmi tootmisfirmaga 'movie_by_production_company' tabelis
    INSERT INTO movie_by_production_company (movie_id, production_company_id)
    VALUES (@movie_id, @company_id);
    
    -- Valime kogu filmi info INNER JOIN abil
    SELECT 
        m.title AS film_title,  -- Filmi pealkiri
        m.production_year AS year,  -- Filmi tootmisaasta
        m.duration AS duration_minutes,  -- Filmi kestus minutites
        m.description AS film_description,  -- Filmi kirjeldus
        g.genre_name AS genre,  -- Žanri nimi
        p.first_name AS actor_first_name,  -- Näitleja eesnimi
        p.last_name AS actor_last_name,  -- Näitleja perenimi
        pim.role AS actor_role,  -- Näitleja roll
        pc.company_name AS production_company  -- Tootmisfirma nimi
    FROM 
        movie m
    INNER JOIN movie_genre mg ON m.id = mg.movie_id  -- Ühendame filmi ja žanri
    INNER JOIN genre g ON mg.genre_id = g.id  -- Ühendame filmi ja žanri
    INNER JOIN person_in_movie pim ON m.id = pim.movie_id  -- Ühendame filmi ja näitlejad
    INNER JOIN person p ON pim.person_id = p.id  -- Ühendame näitlejad filmidega
    INNER JOIN movie_by_production_company mbpc ON m.id = mbpc.movie_id  -- Ühendame filmi ja tootmisfirma
    INNER JOIN production_company pc ON mbpc.production_company_id = pc.id  -- Ühendame filmi ja tootmisfirma
    WHERE 
        m.id = @movie_id;  -- Filtreerime järgi lisatud filmi ID
END;

EXEC AddMovieWithFullInfo 
    @title = 'A Minecraft Movie', 
    @production_year = 2025, 
    @duration = 110, 
    @description = 'Film "Minecraft" põhineb populaarsel videomängul Minecraft.', 
    @genre_name = 'Adventure', 
    @person_first_name = 'Jason', 
    @person_last_name = 'Momoa', 
    @person_role = 'Garrett "The Garbage Man" Garrison', 
    @company_name = 'Legendary Pictures';

2. Kahe SQL-kasutajate loomine – administraator (täielikud õigused) ja tavakasutaja (õigused tabelitele person ja picture).

Administraatori (täielikud õigused) loomine.

Tavakasutaja loomine (õigused kontrollida ja lisada andmeid tabelitesse person ja picture).

Loome 2-3 triggerit, mis jälgivad ainult põhitabelite täitmist ja muutmist (nt tabelid, kus on olemas välisvõtmed).

Loome tabel user_activity

CREATE TABLE user_activity (
    id INT IDENTITY(1,1) PRIMARY KEY,  -- Unikaalne ID
    user_name VARCHAR(100),            -- Kasutaja nimi, kes tegi tegevuse
    action VARCHAR(100),               -- Tegevuse tüüp (nt "AddMovie")
    table_name VARCHAR(50),            -- Tabel, kuhu tegevus toimus (nt "movie")
    record_id INT,                     -- Rekordi ID, millega tegevus on seotud
    action_time DATETIME               -- Aeg, millal tegevus toimus
);

Loome triggeri, mis jälgib uute kirjetuste lisamist tabelisse person_in_movie.

CREATE TRIGGER trg_after_insert_person_in_movie
ON person_in_movie
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO user_activity (user_name, action, table_name, record_id, action_time)
    SELECT 
        SYSTEM_USER,
        'InsertPersonInMovie: PersonID=' + CAST(i.person_id AS VARCHAR) +
        ', MovieID=' + CAST(i.movie_id AS VARCHAR) +
        ', PositionID=' + CAST(i.position_id AS VARCHAR) +
        ', Role=' + ISNULL(i.role, 'NULL'), -- на случай если роль пустая
        'person_in_movie',
        i.id,
        GETDATE()
    FROM inserted i;
END;

Loome triggeri, mis jälgib muudatusi tabelis movie.

CREATE TRIGGER trg_after_update_movie
ON movie
FOR UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO user_activity (user_name, action, table_name, record_id, action_time)
    SELECT 
        SYSTEM_USER,
        'Vanad andmed - Title: ' + ISNULL(d.title, 'NULL') +
        ', Year: ' + CAST(ISNULL(d.production_year, 0) AS VARCHAR) +
        ', Duration: ' + CAST(ISNULL(d.duration, 0) AS VARCHAR) +
        CHAR(13) + CHAR(10) +
        'Uued andmed - Title: ' + ISNULL(i.title, 'NULL') +
        ', Year: ' + CAST(ISNULL(i.production_year, 0) AS VARCHAR) +
        ', Duration: ' + CAST(ISNULL(i.duration, 0) AS VARCHAR),
        'movie',
        i.id,
        GETDATE()
    FROM deleted d
    INNER JOIN inserted i ON d.id = i.id;
END;

Loome triggeri, mis jälgib muudatusi tabelis person_in_movie

CREATE TRIGGER trg_after_update_person_in_movie
ON person_in_movie
FOR UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO user_activity (user_name, action, table_name, record_id, action_time)
    SELECT 
        SYSTEM_USER,
        'Vanad andmed - PersonID: ' + CAST(ISNULL(d.person_id, 0) AS VARCHAR) +
        ', MovieID: ' + CAST(ISNULL(d.movie_id, 0) AS VARCHAR) +
        ', PositionID: ' + CAST(ISNULL(d.position_id, 0) AS VARCHAR) +
        ', Role: ' + ISNULL(d.role, 'NULL') +
        CHAR(13) + CHAR(10) + -- Line break
        'Uued andmed - PersonID: ' + CAST(ISNULL(i.person_id, 0) AS VARCHAR) +
        ', MovieID: ' + CAST(ISNULL(i.movie_id, 0) AS VARCHAR) +
        ', PositionID: ' + CAST(ISNULL(i.position_id, 0) AS VARCHAR) +
        ', Role: ' + ISNULL(i.role, 'NULL'),
        'person_in_movie',
        i.id,
        GETDATE()
    FROM deleted d
    INNER JOIN inserted i ON d.id = i.id;
END;