Andmebaas MOVIES (2 variant MS SQL Server)

Kasutan SQL Server Management Stuudio

1. Loome andmebaas MOVIES:

2. Tabelite nummerdus:

person — Inimesed (näitlejad, režissöörid jne)

picture — Inimeste fotod

position — Ametid (näitleja, režissöör jne)

person_in_movie — Inimeste ja filmide seosed ning nende rollid

quote — Tsitaadid, mida tegelased on öelnud

movie — Filmid

genre — Žanrid

movie_genre — Filmide ja žanrite seos (mitu-mitmele)

production_company — Filmitootmisettevõtted

movie_by_production_company — Filmide ja tootmisettevõtete seos (mitu-mitmele)

3. Ühenduste loomine:

person on seotud tabelitega picture ja person_in_movie

person_in_movie on seotud tabelitega movie, position ja quote

movie on seotud tabelitega movie_genre ja movie_by_production_company

movie_by_production_company on seotud tabeliga production_company

movie_genre on seotud tabeliga genre

4. Protseduuride loomine:

Protseduur 1:

Kõigi filmide ja nende žanrite saamine

CREATE PROCEDURE usp_GetMoviesWithGenres
AS
BEGIN
    SELECT
        m.title AS MovieTitle,
        g.genre_name AS Genre
    FROM movie m
    INNER JOIN movie_genre mg ON m.movie_ID = mg.movie_ID
    INNER JOIN genre g ON mg.genre_ID = g.genre_ID;
END;

Protseduur 1 kontroll:

EXEC usp_GetMoviesWithGenres;

Protseduur 2:

Näitlejate kuvamine konkreetse filmi kohta

CREATE PROCEDURE usp_GetActorsByMovie
    @movieTitle NVARCHAR(100)
AS
BEGIN
    SELECT
        p.first_name + ' ' + p.last_name AS ActorName,
        pim.role
    FROM person p
    INNER JOIN person_in_movie pim ON p.person_ID = pim.person_ID
    INNER JOIN movie m ON pim.movie_ID = m.movie_ID
    INNER JOIN position pos ON pim.position_ID = pos.position_ID
    WHERE m.title = @movieTitle AND pos.position_name = 'Actor';
END;

Protseduur 2 kontroll:

EXEC usp_GetActorsByMovie @movieTitle = 'Inception';
EXEC usp_GetActorsByMovie @movieTitle = 'The Godfather';
EXEC usp_GetActorsByMovie @movieTitle = 'Interstellar';
EXEC usp_GetActorsByMovie @movieTitle = 'The Dark Knight';
EXEC usp_GetActorsByMovie @movieTitle = 'Forrest Gump';

Protseduur 3:

Tsiteeringute kuvamine koos näitlejanime ja filmi pealkirjaga

CREATE PROCEDURE usp_GetQuotesWithContext
AS
BEGIN
    SELECT
        q.quote_text,
        p.first_name + ' ' + p.last_name AS PersonName,
        m.title AS MovieTitle
    FROM quote q
    INNER JOIN person_in_movie pim ON q.person_in_movie_ID = pim.person_in_movie_ID
    INNER JOIN person p ON pim.person_ID = p.person_ID
    INNER JOIN movie m ON pim.movie_ID = m.movie_ID;
END;

Protseduur 3 kontroll:

	
EXEC usp_GetQuotesWithContext;

5. Loome 2 triggeri

Lisame uus inimene person tabellisse

CREATE TRIGGER Person_Insert
ON person
AFTER INSERT
AS
BEGIN
    INSERT INTO log_table (username, action, table_name, action_time)
    SELECT
        SUSER_SNAME(),
        CONCAT(
            'INSERT person: ',
            'Name: ', i.first_name, ' ', i.last_name,
            ', Birth: ', CONVERT(varchar, i.birth_date, 23)
        ),
        'person',
        GETDATE()
    FROM inserted i;
END;

2. Uuendame pildinimi picture tabellis

CREATE TRIGGER Picture_Update
ON picture
AFTER UPDATE
AS
BEGIN
    INSERT INTO log_table (username, action, table_name, action_time)
    SELECT
        SUSER_SNAME(),
        CONCAT(
            'UPDATE picture: ',
            'Old file - ', d.picture_file_name, ' (person_ID ', d.person_ID, ') > ',
            'New file - ', i.picture_file_name, ' (person_ID ', i.person_ID, ')'
        ),
        'picture',
        GETDATE()
    FROM deleted d
    INNER JOIN inserted i ON d.picture_ID = i.picture_ID;
END;

6. Loome 2 kasutajat

admin — saab teha kõike kõikidega tabelitega

CREATE LOGIN admin WITH PASSWORD = 'StrongAdminPass123!';
CREATE USER admin FOR LOGIN admin;
 
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: dbo TO admin;

admin õiguste kontroll

SELECT * FROM genre;
SELECT * FROM movie;
SELECT * FROM movie_by_production_company;
SELECT * FROM movie_genre;
SELECT * FROM person_in_movie;
SELECT * FROM position;
SELECT * FROM production_company;
SELECT * FROM quote;
SELECT * FROM log_table;
 
SELECT * FROM person;
SELECT * FROM picture;

kastujaMOVIES — saab lisada ja kontrollida andmed tabelites: person, pictures

CREATE LOGIN kasutajaMOVIES WITH PASSWORD = 'KasutajaPass456!';
CREATE USER kasutajaMOVIES FOR LOGIN kasutajaMOVIES;
 
GRANT SELECT, INSERT, UPDATE ON person TO kasutajaMOVIES;
GRANT SELECT, INSERT, UPDATE ON picture TO kasutajaMOVIES;

kasutajaMOVIES õiguste kontroll

SELECT * FROM genre;
SELECT * FROM movie;
SELECT * FROM movie_by_production_company;
SELECT * FROM movie_genre;
SELECT * FROM person_in_movie;
SELECT * FROM position;
SELECT * FROM production_company;
SELECT * FROM quote;
SELECT * FROM log_table;
 
SELECT * FROM person;
SELECT * FROM picture;