Розробка механізмів управління даними в базі за допомогою тригерів

Тригер — це збережена процедура особливого типу, яку користувач не викликає особисто, а використання якої обумовлено настанням визначеної події у реляційній базі даних:

1) додаванням ‘INSERT’;

2) вилученням рядка в заданій таблиці ‘DELETE’;

3) зміною даних у певному стовпці заданої таблиці ‘UPDATE’.

Тригери застосовуються для забезпечення цілісності даних і реалізації складної бізнес-логіки. Тригер запускається сервером автоматично при спробі зміни даних у таблиці, з якою він пов'язаний. Всі здійснені ним модифікації даних розглядаються як виконані в транзакції, в якій виконано дію, що викликала спрацьовування тригера. Відповідно, у разі виявлення помилки або порушення цілісності даних може відбутися відкат цієї транзакції.

Момент запуску тригера визначається за допомогою ключових слів ‘BEFORE’ (тригер запускається до виконання пов'язаної з ним події; наприклад, до додавання запису) або ‘AFTER’ (після події). У випадку, якщо тригер викликається до події, він може внести зміни у модифікований подією запис (звичайно, за умови, що подія — не вилучення запису).

Крім того, тригери можуть бути прив'язані не до таблиці, а до уявлення (VIEW). В цьому випадку за їхньої допомоги реалізується механізм «оновлюваного виду». В цьому випадку ключові слова ‘BEFORE’ і ‘AFTER’ впливають лише на послідовність виклику тригерів, бо власне подія (вилучення, вставка або оновлення) не відбувається. Тригери можуть викликатися не для кожної модифікації запису, а один раз на зміну таблиці. Такі тригери називаються табличними.

У базі даних «Адресна книга» було створено тригер «savetoMTS», який зберігає до таблиці «operatorMTS» усі дані контакту, якщо той використовує оператор мобільного зв’язку МТС.

При додаванні нового такого контакту до таблиці «Person» він має автоматично скопіюватися і у таблицю «operatorMTS». Скрипт створеного тригера викладений нижче.

 

CREATE DEFINER = 'root'@'localhost' TRIGGER `savetoMTS` AFTER INSERT ON `person`

FOR EACH ROW

BEGIN

INSERT INTO operatormts (`Person_ID`, `Name`, `Surname`, `MiddleName`, `Email`, `Address`,`Mobile`, `Type_ID`)

SELECT Person_ID, Name, Surname, MiddleName,

Email, Address, Mobile, Type_ID

FROM person

WHERE (Mobile LIKE '050%' OR Mobile LIKE '066%'

OR Mobile LIKE '095%' OR Mobile LIKE '099%')

AND Person_ID

NOT IN (SELECT Person_ID FROM operatormts);

END;

 

У таблиці з контактами «Person» створено 5 осіб з мобільним оператором МТС, отже таблиця «operatorMTS» має такий вигляд, як показано на рисунку 3.13.

 

 

Рисунок 3.13 – Таблиця «operatorMTS» до створення нового контакта

 

Після додавання до таблиці «Person» нового контакта з оператором МТС таблиця «operatorMTS» має такий вигляд, як показано на рисунку 3.14.

 

 

Рисунок 3.14 – Таблиця «operatorMTS» після створення контакта

 

Також був створений тригер «updateOperatorMTS», наведений нижче.

 

CREATE DEFINER = 'root'@'localhost' TRIGGER `updateOperatorMTS` AFTER UPDATE ON `person`

FOR EACH ROW

BEGIN

UPDATE `operatorMTS`

JOIN `person` ON `operatorMTS`.`Person_ID` = `person`.`Person_ID`

SET `operatormts`.`Name` = `person`.`Name`,

`operatormts`.`Surname` = `person`.`Surname`,

`operatormts`.`MiddleName` = `person`.`MiddleName`,

`operatormts`.`Email` = `person`.`Email`,

`operatormts`.`Mobile` = `person`.`Mobile`,

`operatormts`.`Address` = `person`.`Address`

WHERE (`operatormts`.`Name` <> `person`.`Name`

OR `operatormts`.`Surname` <> `person`.`Surname`

OR `operatormts`.`MiddleName` <> `person`.`MiddleName`

OR `operatormts`.`Email` <> `person`.`Email`

OR `operatormts`.`Mobile` <> `person`.`Mobile`

OR `operatormts`.`Address` <> `person`.`Address`);

END;

При зміні якого-небудь поля у таблиці «Person» тригер «updateOperatorMTS» виконує відповідну зміну цих даних і в таблиці «operatorMTS». Таким чином, дані в таблиці «operatorMTS» завжди є актуальними. Наприклад, змінимо адресу проживання Віталія Петраша з ідентифікатором «18» на «Pushkinska str, 140, 53» у таблиці «Person». Тоді таблиця «operatorMTS» міститиме такі дані, які показані на рисунку 3.15.

 

 

Рисунок 3.15 – Таблиця «operatorMTS»

ВИСНОВКИ

 

У процесі виконання курсового проекту була визначена предметна область, основні сутності та зв’язки між ними в межах цієї предметної області. Була визначена система бізнес-правил, відповідно до вимог якої дані і будуть міститися в таблицях БД. Також побудована концептуальна та логічна модель даних. Чимало уваги було приділено вибору СУБД, серед яких для поставленого завдання найбільше підійшла СУБД MySQL.

Далі на основі концептуальної та логічної моделі була створена безпосередньо база даних та наповнена записами для роботи з нею. Дана БД забезпечує надійне зберігання інформації, а також істотну економію часу, що витрачається на пошук, редагування та видалення існуючих даних. Наступним етапом була розробка запитів до бази даних, використовуючи такі SQL-оператори, як ‘INSERT’, ‘UPDATE’, SELECT’, ‘DELETE’ та ‘JOIN’. Також були створені уявлення, збережені процедури та тригери. У процесі виконання курсового проекту було виділено три розділи.

Розділ 1. Була описана актуальність розробки та використання баз даних у наш час, аналіз предметної області, система бізнес-правил, глосарій проекту та основні цілі, які повинні бути досягнуті в результаті.

Розділ 2. Була виконана безпосередня розробка концептуальної моделі даних, її перетворення у логічну модель та визначені основні типи запитів у системі.

Розділ 3. Був описаний вибір системи управління базами даних для її реалізації, безпосередня реалізація бази даних та результати, одержувані користувачем при використанні прикладного програмного забезпечення.

Результатом роботи над курсовим проектом є створена та цілком працездатна база даних, перевагами якої є зручність та швидкість обробки інформації.

СПИСОК ІНФОРМАЦІЙНИХ ДЖЕРЕЛ

 

1 Базы данных – Понятие базы данных // [Електронний ресурс]// Режим доступу: http://www.site-do.ru/db/db1.php

2 База даних // [Електронний ресурс]// Режим доступу: https://ru.wikipedia.org/wiki/База_даних

3 Основні відомості про бази даних // [Електроний ресурс]// Режим доступу: http://office.microsoft.com/uk-ua/access-help/HA010064450.aspx

4 SQL SELECT Statement // [Електронний ресурс]// Режим доступу:

http://www.w3schools.com/sql/sql_select.asp

5 SQL – SELECT Query // [Електронний ресурс]// Режим доступу: http://www.tutorialspoint.com/sql/sql-select-query.htm

6 ELSE (IF…ELSE) (Transact-SQL) // [Електронний ресурс]// Режим доступу: https://msdn.microsoft.com/ru-ru/library/ms182587.aspx

7 Полезные запросы в MySQL // [Електронний ресурс]// Режим доступу: http://plutov.by/post/mysql_useful_queries

8 Запросы MySQL: UPDATE // [Електронний ресурс]// Режим доступу: http://oftob.com/mysql-запросы/629-mysql-update-1

9 MySQL Примеры использования команды JOIN // [Електронний ресурс]// Режим доступу: http://eddnet.org/?p=1580

10 Выборка данных – оператор SELECT // [Електронний ресурс]// Режим доступу: http://www.site-do.ru/db/sql4.php

11 JOIN (SQL) // [Електронний ресурс]// Режим доступу: https://ru.wikipedia.org/wiki/Join_(SQL)

12 SQL и оператор JOIN // [Електронний ресурс]// Режим доступу: http://www.k-press.ru/cs/2009/3/join/join.asp

13 Outer joins // [Електронний ресурс]// Режим доступу: https://msdn.microsoft.com/en-us/library/ms714641(v=vs.85).aspx

14 SELECT Syntax // [Електронний ресурс]// Режим доступу: https://dev.mysql.com/doc/refman/5.0/en/select.html

15 SQL – Вложенные запросы // [Електронний ресурс]// Режим доступу: http://www.site-do.ru/db/sql5.php

16 User – Defined Variables // [Електронний ресурс]// Режим доступу: https://dev.mysql.com/doc/refman/5.0/en/user-variables.html

17 PRINT (Transact – SQL) // [Електронний ресурс]// Режим доступу: https://msdn.microsoft.com/ru-ru/library/ms176047.aspx

18 Вложенные запросы SQL // [Електронний ресурс]// Режим доступу: http://bd-sql-xml.org.ua/index.php/zaprosy-sql/18-slozhnye-zaprosy-sql/60-vlozhennye-zaprosy-sql

19 Создание и уничтожение представлений // [Електронний ресурс]// Режим доступу: http://wm-help.net/books-online/book/83971/83971-44.html

20 Представления (VIEW) в MySQL // [Електронний ресурс]// Режим доступу: http://habrahabr.ru/post/47031/

21 ВВЕДЕНИЕ: ПРЕДСТАВЛЕНИЯ // [Електронний ресурс]// Режим доступу: http://www.mysql.ru/docs/gruber/mg20.html

22 Введение в хранимые процедуры MySQL 5 // [Електронний ресурс]// Режим доступу: http://ruseller.com/lessons.php?rub=28&id=1189

23 Хранимая процедура // [Електронний ресурс]// Режим доступу: https://ru.wikipedia.org/wiki/Хранимая_процедура

24 Хранимые процедуры и триггеры // [Електронний ресурс]// Режим доступу: http://www.zoonman.ru/library/mysql_sr_and_t.htm

25 Триггеры // [Електронний ресурс]// Режим доступу: http://www.rldp.ru/mysql/mysqlpro/triggers.htm

26 Триггеры в MySQL // [Електронний ресурс]// Режим доступу: http://habrahabr.ru/post/37693/

27 RAISERROR (Transact – SQL) // [Електронний ресурс]// Режим доступу: https://msdn.microsoft.com/ru-ru/library/ms178592.aspx

 

ДОДАТОК А

 

SQL-скрипт бази даних «Мобільний оператор»

 

# SQL Manager for MySQL 5.5.3.46984

# ---------------------------------------

# Host: localhost

# Port: 3306

# Database: project

 

 

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

 

SET FOREIGN_KEY_CHECKS=0;

 

CREATE DATABASE `project`

CHARACTER SET 'utf8'

COLLATE 'utf8_general_ci';

 

USE `project`;

 

#

# Структура для таблицы `abonent`:

#

 

CREATE TABLE `abonent` (

`id` INTEGER(11) NOT NULL AUTO_INCREMENT,

`balance` FLOAT(11,2) NOT NULL DEFAULT 0.00,

`nomer` INTEGER(13) NOT NULL,

`activation_time_tariff` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

`tariff_id` INTEGER(11) NOT NULL DEFAULT -1,

PRIMARY KEY (`id`) USING BTREE

) ENGINE=InnoDB

AUTO_INCREMENT=12 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'

;

 

#

# Структура для таблицы `history`:

#

 

CREATE TABLE `history` (

`id` INTEGER(11) NOT NULL AUTO_INCREMENT,

`abonent_id` INTEGER(11) NOT NULL,

`time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`abonent_to` INTEGER(11) NOT NULL,

`duration` INTEGER(11) NOT NULL,

PRIMARY KEY (`id`) USING BTREE,

KEY `abonent_id` (`abonent_id`) USING BTREE

) ENGINE=InnoDB

AUTO_INCREMENT=18 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'

;

 

#

# Структура для таблицы `service`:

#

 

CREATE TABLE `service` (

`id` INTEGER(11) NOT NULL AUTO_INCREMENT,

`name` VARCHAR(100) COLLATE utf8_general_ci NOT NULL,

PRIMARY KEY (`id`) USING BTREE

) ENGINE=InnoDB

AUTO_INCREMENT=9 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'

;

 

#

# Структура для таблицы `sms`:

#

 

CREATE TABLE `sms` (

`id` INTEGER(11) NOT NULL AUTO_INCREMENT,

`abonent_id` INTEGER(11) NOT NULL,

`text` TEXT COLLATE utf8_general_ci NOT NULL,

`abonent_to` INTEGER(11) NOT NULL,

`time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`id`) USING BTREE

) ENGINE=InnoDB

AUTO_INCREMENT=2 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'

;

 

#

# Структура для таблицы `tariff`:

#

 

CREATE TABLE `tariff` (

`id` INTEGER(11) NOT NULL AUTO_INCREMENT,

`price` FLOAT(11,2) NOT NULL,

`delay` INTEGER(11) NOT NULL,

`name` VARCHAR(100) COLLATE utf8_general_ci NOT NULL,

PRIMARY KEY (`id`) USING BTREE

) ENGINE=InnoDB

AUTO_INCREMENT=5 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'

;

 

#

# Структура для таблицы `tariff_service`:

#

 

CREATE TABLE `tariff_service` (

`id` INTEGER(11) NOT NULL AUTO_INCREMENT,

`tariff_id` INTEGER(11) NOT NULL,

`service_id` INTEGER(11) NOT NULL,

`price` FLOAT(11,2) NOT NULL,

PRIMARY KEY (`id`) USING BTREE,

KEY `service_id` (`service_id`) USING BTREE

) ENGINE=InnoDB

AUTO_INCREMENT=10 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'

;

 

#

# Определение для представления `abonent_last`:

#

 

CREATE ALGORITHM=UNDEFINED DEFINER='root'@'localhost' SQL SECURITY DEFINER VIEW `abonent_last`

AS

select

`abonent`.`id` AS `id`,

`abonent`.`balance` AS `balance`,

`history`.`abonent_to` AS `abonent_to`,

max(`history`.`time`) AS `last`

from

(`abonent`

join `history` on ((`history`.`abonent_id` = `abonent`.`id`)))

group by

`history`.`time`;

 

#

# Data for the `abonent` table (LIMIT 0,500)

#

 

INSERT INTO `abonent` (`id`, `balance`, `nomer`, `activation_time_tariff`, `tariff_id`) VALUES

(2,24.00,634455999,'2016-03-31 02:21:58',2),

(3,60.00,997744334,'2016-03-31 02:24:31',-1),

(4,-1.00,997746364,'2016-03-31 02:24:57',4),

(5,100.00,777777777,'2016-03-31 02:26:00',-1),

(6,1.00,2123213,'2016-04-11 19:21:26',1),

(10,0.00,213123,'2016-04-11 20:45:45',-1),

(11,0.00,0,'2016-04-21 20:56:22',-1);

COMMIT;

 

#

# Data for the `history` table (LIMIT 0,500)

#

 

INSERT INTO `history` (`id`, `abonent_id`, `time`, `abonent_to`, `duration`) VALUES

(3,2,'2016-03-31 02:26:29',3,60),

(4,3,'2016-03-31 02:27:40',5,120),

(5,5,'2016-03-31 02:27:48',2,10),

(6,4,'2016-03-31 02:27:55',2,30),

(7,3,'2016-04-11 17:41:14',2,123),

(8,1,'2016-04-11 17:41:27',4,456),

(9,1,'2016-04-11 17:42:18',3,456),

(10,2,'2016-04-11 17:42:18',4,456),

(11,4,'2016-04-11 17:42:18',1,456),

(12,3,'2016-04-11 17:42:18',3,456),

(13,2,'2016-04-11 17:42:18',2,456),

(14,3,'2016-04-11 17:42:18',1,456),

(15,1,'2016-04-11 17:42:18',3,456),

(16,2,'2016-04-11 17:42:18',2,456),

(17,4,'2016-04-11 17:42:18',3,456);

COMMIT;

 

#

# Data for the `service` table (LIMIT 0,500)

#

 

INSERT INTO `service` (`id`, `name`) VALUES

(4,'internet'),

(5,'sms'),

(6,'mms'),

(7,'звонки'),

(8,'internet 3G');

COMMIT;

 

#

# Data for the `sms` table (LIMIT 0,500)

#

 

INSERT INTO `sms` (`id`, `abonent_id`, `text`, `abonent_to`, `time`) VALUES

(1,0,'Благодарим вас за покупку сим-карты! Пополните себе счет: #123',2,'2016-04-11 19:47:58');

COMMIT;

 

#

# Data for the `tariff` table (LIMIT 0,500)

#

 

INSERT INTO `tariff` (`id`, `price`, `delay`, `name`) VALUES

(2,20.00,30,'Зашибись'),

(3,40.00,30,'Все, что нужно'),

(4,100.00,30,'Все включено');

COMMIT;

 

#

# Data for the `tariff_service` table (LIMIT 0,500)

#

 

INSERT INTO `tariff_service` (`id`, `tariff_id`, `service_id`, `price`) VALUES

(1,4,3,1.00),

(2,4,5,2.00),

(3,4,5,1.00),

(4,4,7,3.00),

(5,4,7,3.00),

(6,4,8,1.00),

(7,3,7,2.00),

(8,3,8,4.00),

(9,2,8,3.25);

COMMIT;

 

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

ДОДАТОК Б

 

Візуальна схема бази даних «Мобільний оператор»

 

 

Рисунок Б.1 – Візуальна схема бази даних