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 1 – Kuva filmi info

CREATE PROCEDURE GetMovieInfo
@movie_id int -- Filmi ID, mille kohta infot soovitakse
AS
BEGIN
-- Tagastab filmi põhiinfo koos žanriga
SELECT 
m.id,
m.title,
m.duration,
m.production_year,
g.genre_name
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;
Kontroll
EXEC GetMovieInfo @movie_id = 1;

1.2. Protseduur 2 – Lisa inimene koos pildiga ja kuva lisatud andmed

CREATE PROCEDURE AddPersonWithPicture
@first_name VARCHAR(100),          -- Inimese eesnimi
@last_name VARCHAR(100),           -- Inimese perekonnanimi
@birth_date DATE,                  -- Inimese sünnikuupäev
@picture_file_name VARCHAR(100)    -- Pildi failinimi
AS
BEGIN
-- Lisa uus inimene
INSERT INTO person (first_name, last_name, birth_date)
VALUES (@first_name, @last_name, @birth_date);

-- Võta isiku ID
DECLARE @person_id INT = SCOPE_IDENTITY();

-- Lisa pilt
INSERT INTO picture (picture_file_name, person_id)
VALUES (@picture_file_name, @person_id);

-- Tagasta lisatud andmed
SELECT 
p.id AS person_id,
p.first_name,
p.last_name,
p.birth_date,
pic.picture_file_name
FROM 
person p
INNER JOIN 
picture pic ON p.id = pic.person_id
WHERE 
p.id = @person_id;
END;
Kontroll
EXEC AddPersonWithPicture 
@first_name = 'Josh',
@last_name = 'Hutcherson',
@birth_date = '1992-10-12',
@picture_file_name = 'josh_hutcherson.jpg';

1.3. Protseduur 3 – Uuenda filmi kirjeldus ja kuva uuendatud kirjeldus

CREATE PROCEDURE UpdateMovieDescription
@movie_id int,                 -- Muudetava filmi ID
@new_description varchar(100) -- Uus kirjeldus
AS
BEGIN
 -- Uuendab filmi kirjelduse väärtuse
UPDATE movie
SET description = @new_description
WHERE id = @movie_id;
-- Tagastab uuendatud filmi andmed
SELECT 
id AS movie_id,
title,
production_year,
duration,
description
FROM 
movie
WHERE 
id = @movie_id;
END;
Kontroll
exec UpdateMovieDescription 
@movie_id = 9, 
@new_description = 'Katniss must fight for survival in a brutal televised event known as the Hunger Games.';

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

Administraatori (täielikud õigused) loomine.

Administraatori õiguste kontroll

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

Tavakasutaja õiguste kontroll

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

Loome tabel action_log

create table action_log (
id int identity(1,1) primary key,
table_name varchar(50),
action_info varchar(200),
action_time datetime default getdate()
);

Trigger 1 – Loome triggeri, mis jälgib isikute lisamist, uuendamist ja kustutamist tabelis person.

CREATE TRIGGER trg_log_person_changes
ON person
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
-- Uuendamine (UPDATE): kirje olemas nii inserted kui deleted tabelis
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
BEGIN
INSERT INTO action_log (table_name, action_info)
SELECT 
'person',
'update by ' + SUSER_NAME() +
' Before -> name: ' + d.first_name + ' ' + d.last_name +
' After  -> ' + i.first_name + ' ' + i.last_name
FROM inserted i
JOIN deleted d ON i.id = d.id;
END

 -- Lisamine (INSERT): kirje olemas ainult inserted tabelis
IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
BEGIN
INSERT INTO action_log (table_name, action_info)
SELECT 
'person',
'insert by ' + SUSER_NAME() + 
' -> name: ' + first_name + ' ' + last_name
FROM inserted;
END

-- Kustutamine (DELETE): kirje olemas ainult deleted tabelis
IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS (SELECT * FROM inserted)
BEGIN
INSERT INTO action_log (table_name, action_info)
SELECT 
'person',
'delete by ' + SUSER_NAME() + 
' -> name: ' + first_name + ' ' + last_name
FROM deleted;
END
END;
Kontroll
insert into person (first_name, last_name, birth_date)
values ('Donald', 'Sutherland', '1935-07-17');  -- President Snow

update person
set first_name = 'Don'
where first_name = 'Donald' and last_name = 'Sutherland';

delete from person
where first_name = 'Don' and last_name = 'Sutherland';

insert into person (first_name, last_name, birth_date)
values ('Stanley', 'Tucci', '1960-11-11');  -- Caesar Flickerman

Trigger 2 – Loome triggeri, mis jälgib filmide kirjelduse muutmist tabelis movie.

CREATE TRIGGER trg_movie_description_change
ON movie
AFTER UPDATE
AS
BEGIN
-- Kui description on muutunud, logime muudatuse
IF EXISTS (
SELECT * 
FROM inserted i
JOIN deleted d ON i.id = d.id
WHERE ISNULL(i.description, '') <> ISNULL(d.description, '')
)
BEGIN
INSERT INTO action_log (table_name, action_info, action_time)
SELECT 
'movie',
'update by ' + SUSER_NAME() +
' Before -> description: ' + d.description +
' After  -> ' + i.description,
GETDATE()
FROM inserted i
JOIN deleted d ON i.id = d.id
WHERE ISNULL(i.description, '') <> ISNULL(d.description, '');
END
END;
Kontroll
UPDATE movie
SET description = 'A thrilling dystopian story about survival and rebellion'
WHERE id = 9;
SELECT * FROM action_log;

Trigger 3 – Loome triggeri, mis jälgib uute piltide lisamist tabelisse picture.

CREATE TRIGGER trg_picture_insert
ON picture
AFTER INSERT
AS
BEGIN
-- Uue pildi lisamine
IF EXISTS (SELECT * FROM inserted)
BEGIN
INSERT INTO action_log (table_name, action_info, action_time)
SELECT 
'picture',
'insert by ' + SUSER_NAME() +
' -> picture_file_name: ' + picture_file_name,
GETDATE()
FROM inserted;
END
END;
Kontroll
INSERT INTO picture (picture_file_name, person_id)
VALUES ('woody_harrelson.jpg', 14);

INSERT INTO picture (picture_file_name, person_id)
VALUES ('elizabeth_banks.jpg', 15);

select * from action_log;