Создание базы данных, таблиц и связей используя графическую оболочку
Cоздаем базу данных MOVIES
Создание и заполнение таблиц
movies –> Table –> New –> Table
Таблица – person
Таблица – picture
Таблица – quote
Таблица – position
Таблица – person_in_movie
Таблица – genre
Таблица – movie_genre
Таблица – movie
Таблица – production_company
Таблица – movie_by_production_company
Диаграмма базы данных movies
Koosta 3 protseduuri, mis on loodud seotud (JOIN) tabelite põhjal. Iseseisvalt määra mida protseduurid teevad. Käivita neid.
1. Процедура, которая показывает все фильмы (название, год выпуска и продолжительность), в которых играл конкретный актер
CREATE PROCEDURE otsingNaitlejaFilm
@nimi varchar(100),
@perekonnanimi varchar(100)
AS
BEGIN
SELECT
p.first_name + ' ' + p.last_name AS actor,
m.title,
m.production_year,
m.duration
FROM person p
INNER JOIN person_in_movie pm ON p.id=pm.person_id
INNER JOIN movie m ON pm.movie_id = m.id
WHERE p.first_name=@nimi AND p.last_name=@perekonnanimi;
END;
-- kutse
EXEC otsingNaitlejaFilm @nimi='Johnny', @perekonnanimi='Depp';
Результат:
2. Процедура показывает, какие жанры используются чаще всего и количество фильмов, где используются данные жанры.
CREATE PROCEDURE zanrideArv
AS
BEGIN
SELECT
g.genre_name AS Zanr,
COUNT(mg.movie_id) AS FilmideArv
FROM genre g
INNER JOIN movie_genre mg ON g.id=mg.genre_id
GROUP BY g.genre_name
ORDER BY FilmideArv DESC;
END;
-- kutse
EXEC zanrideArv;
Результат:
3. Процедура, которая обновляет жанр у конкретного фильма
CREATE PROCEDURE uuendaFilmiZanr
@filmNimi varchar(100),
@uusZanr varchar(50)
AS
BEGIN
UPDATE mg SET mg.genre_id=g.id
FROM movie_genre mg
INNER JOIN movie m ON mg.movie_id=m.id
INNER JOIN genre g ON g.genre_name=@uusZanr
WHERE m.title=@filmNimi;
SELECT m.title, g.genre_name
FROM movie_genre mg
INNER JOIN movie m ON mg.movie_id = m.id
INNER JOIN genre g ON mg.genre_id = g.id;
END;
-- kutse
EXEC uuendaFilmiZanr
@filmNimi='Harry Potter and the Philosophers Stone',
@uusZanr='Adventure';
Проверка:
Выбираем жанр из таблицы ‘genre’ и фильм из таблицы ‘movie’ и вводим нужные данные для обновления таблицы ‘movie_genre’.
При вызове процедуры у фильма Гарри Поттер поменялся жанр с фэнтэзи на приключение.
Loo 2 kasutajat (admin, kes saab teha kõike kõikidega tabelitega / kasutajaNimi, kes saab lisada ja kontrollida andmed tabelites: person, pictures.
Создаю пользователя admin (Security –> Login –> New Login)
Даю пользователю admin права (User Mapping –> Database role)
Создаю пользователя kasutajaNimi (Security –> Login –> New Login)
Даю пользователю kasutajaNimi права на INSER и SELECT в таблицах ‘picture’ и ‘person’
(Databases –> “DB Nimi” –> Security –> “Kasutaja Nimi”)
Проверка!
Проверяем, может ли пользователь admin делать всё со всеми таблицами.
Пользователь admin видит все таблицы
Проверка, добавляется ли в таблицу logi изменения внесенные пользователем admin
INSERT INTO quote(quote_text, person_in_movie_id)
VALUES ('I make a point of avoiding familiarity with pirates.', 7);
SELECT * FROM quote;
SELECT * FROM logi;
В таблице logi отслеживается команда INSERT пользователем admin в таблице ‘quote’
Проверяем, может ли пользователь kasutajaNimi видеть или изменять другие таблицы, кроме picture и person.
Пользователь может просматривать таблицы person и picture
У пользователя нет прав на просмотр других таблиц
Проверка на добавление в таблицу picture и person
Проверка на удаление в таблице picture и person
Loo 2-3 trigerid, mis jälgivad ainult põhitabelite täitmine/muutmine (näiteks, need tabelid kus on olemas FK).
Создаю таблицу logi, для отслеживания заполнения/изменения основных таблиц
Create table logi(
id int PRIMARY KEY IDENTITY (1,1),
toiming varchar(100),
aeg DATETIME,
kasutajanimi varchar(25),
andmed TEXT
)
1. Комбинированный триггер, который отслеживает добавление (INSERT) и удаление (DELETE) в таблице ‘quote’.
В таблицу ‘logi’ в данные выводится: цитата, роль (из таблицы person_in_movie) и имя актёра (из таблицы person).
Использовала триггер, который мы рассматривали на уроке (для комбинирования двух команд) и добавила к нему INNER JOIN для связи с таблицей по FK.
CREATE TRIGGER tsitaadiLisamineJaKustutamine
ON quote
AFTER INSERT, DELETE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO logi(toiming, aeg, kasutajanimi, andmed)
SELECT
'On tehtud INSERT käsk',
GETDATE(),
SUSER_NAME(),
CONCAT('Tabel: "quote" / Tsitaat: "', inserted.quote_text, '", Roll: ', pm.role, ', Näitleja: ', p.first_name ,' ', p.last_name)
FROM inserted
INNER JOIN person_in_movie pm ON pm.id=inserted.person_in_movie_id
INNER JOIN person p ON p.id=pm.person_id
UNION ALL
SELECT
'On tehtud DELETE käsk',
GETDATE(),
SUSER_NAME(),
CONCAT('Tabel: "quote" / Tsitaat: "', deleted.quote_text, '", Roll: ', pm.role, ', Näitleja: ', p.first_name ,' ', p.last_name)
FROM deleted
INNER JOIN person_in_movie pm ON pm.id=deleted.person_in_movie_id
INNER JOIN person p ON p.id=pm.person_id;
END
Проверка:
--Läbivaatus
INSERT INTO quote(quote_text, person_in_movie_id)
VALUES ('Not all treasure’s silver and gold, mate', 2);
SELECT * FROM quote;
SELECT * FROM logi;
--Läbivaatus
DELETE FROM
quote
WHERE
id=2;
SELECT * FROM quote;
SELECT * FROM logi;
2. Триггер, который отслеживает изменение (UPDATE) в таблице ‘picture’.
CREATE TRIGGER naitlejaPildiUuendamine
ON picture
FOR UPDATE
AS
INSERT INTO logi(toiming, aeg, kasutajanimi, andmed)
SELECT
'On tehtud UPDATE',
GETDATE(),
SUSER_NAME(),
CONCAT('Tabel: "picture" / Vanad andmed - Näitleja: ', p1.first_name, ' ', p1.last_name,
', Sünniaeg: ', p1.birth_date, ', Pildifaili nimi: ', deleted.picture_file_name,
' / Uued andmed - Näitleja: ', p2.first_name, ' ', p2.last_name,
', Sünniaeg: ', p2.birth_date, ', Pildifaili nimi: ', inserted.picture_file_name)
FROM deleted
INNER JOIN inserted ON deleted.id=inserted.id
INNER JOIN person p1 ON p1.id=deleted.person_id
INNER JOIN person p2 ON p2.id=inserted.person_id;
Проверка:
UPDATE picture SET picture_file_name='tom_hanks_profile.png'
WHERE id=4;
SELECT * FROM picture;
SELECT * from logi;
3. Триггер, который отслеживает добавление (INSERT) в таблице person_in_movie
CREATE TRIGGER naitlejaFilmisLisamine
ON person_in_movie
FOR INSERT
AS INSERT INTO logi(toiming, aeg, kasutajanimi, andmed)
SELECT
'On tehtud INSERT',
GETDATE(),
SUSER_NAME(),
CONCAT('Tabel: "person_in_movie" / Nimi: ', p.first_name, ' ', p.last_name, ', Film: ', m.title, ', Amet: ', pos.position_name, ', Roll: ', inserted.role)
FROM inserted
INNER JOIN person p ON p.id=inserted.person_id
INNER JOIN movie m ON m.id=inserted.movie_id
INNER JOIN position pos ON pos.id=inserted.position_id;
Проверка:
INSERT INTO person_in_movie(person_id, movie_id, position_id, role)
VALUES (6, 2, 1, 'Will Turner');
SELECT * FROM person_in_movie;
SELECT * FROM logi;
kasutajaNimi ei pea näha logi tabeli, kuhu triger salvestab oma andmed.
-- DENY logi tabeli lugemise õigus
DENY SELECT, INSERT, UPDATE, DELETE ON logi TO kasutajaNimi
Проверка:
