Згідно вищевказаної класифікації всі обмеження цілісності бази даних можна розділити на 4 групи:
1. Обмеження атрибута;
2. Обмеження домену;
3. Обмеження кортежу;
4. Обмеження відносини.
Далі розглянемо всі типи обмежень цілісності, що застосовуються в даному курсовому проекті (обмеження атрибута і відносини).
Обмеження атрибуту мають велике значення при організації бізнес-логіки системи. Одним з видів обмеження атрибутів є обмеження унікальності (UNIQUE constraints). Ще одна назва цього виду обмеження - альтернативний ключ (alternate key). У даному проекті цей вид обмежень широко використовувався для підтримки цілісності БД. Наприклад, при аналізі предметної області було виявлено, що назва ЛПУ повинна бути унікальною. Тому при створенні таблиці LPU був написаний наступний сценарій.
CREATE TABLE LPU
(IDLPU INT IDENTITY PRIMARY KEY,
NameLPU varchar (50) UNIQUE,
MestoLPU varchar (30))
Створивши дане відношення, ми встановили, що назва ЛПУ повинна бути унікальною. Таким чином, при спробі порушити це обмеження користувач отримає повідомлення про помилку.
Обмеження UNIQUE було встановлено у відносинах LPU, Vrach, Pacient, Type, Diagnos і інших для позначення потенційних ключів відносин.
Ще одним видом обмеження атрибутів є неприпустимість NULL-значень. Це означає, що даний атрибут не може мати значення NULL (невизначеність). Це обмеження автоматично встановлюється для первинних ключів (Primary key) відносини, тому що при значенні первинного ключа NULL він перестає однозначно ідентифікувати кортеж відносини. Можна також встановити обмеження неприпустимість NULL-значень на будь-який з інших атрибутів. У даному курсовому проекті цей вид обмеження використовувався дуже широко, наприклад:
CREATE TABLE Type
(IDType INT IDENTITY PRIMARY KEY,
NameType varchar (40) UNIQUE NOT NULL,
TarifType MONEY)
При створенні таблиці Type (спеціальність лікаря) ми встановили, що назва спеціальності не може бути NULL, тому що в противному випадку втрачається весь сенс цього відносини (назва спеціальності є атрибутом, який несе в собі найбільшу інформативність для користувача).
Установка обмеження NOT NULL була проведена у всіх первинних і потенційних ключах всіх відносин, у всіх зовнішніх ключах, а також полях, які несуть найбільшу інформативність у відношенні.
Також при розробці бази даних було використано обмеження перевірки атрибуту (CHECK). Треба зауважити, що цей тип обмеження відноситься до обмежень рівня відношення. Позитивна особливість даного виду обмежень полягає в тому, що їх застосування не обмежується окремими стовпцями. У принципі можна перевірити на відповідність певним критерієм будь-яку комбінацію полів цього запису. У даному курсовому проекті обмеження значення використовувалося в основному для атрибутів типу DateTime, щоб виключити можливість введення майбутніх дат. Для цього треба було написати такий сценарій.
ALTER TABLE Isveshenie
WITH CHECK
ADD CONSTRAINT ChekDateBegin
CHECK (Isveshenie.BeginPer <GETDATE ())
Обмеження CHECK було встановлено для перевірки правильності введення дат у відносинах Prikas, Isveshenie, Karta.
Ще одним видом обмежень, можливо, самим важливим, є обмеження первинного ключа (PRIMARY KEY). Ми можемо говорити про важливість цього типу обмежень, так як реляційні бази даних створювалися для реалізації можливостей завдання зв'язків між даними. Тому важливо мати унікальні ідентифікатори для кожного кортежу. Первинний ключ повинен містити унікальні значення (і тому не може містити NULL-значень). Наведемо приклад використання в створеній базі даних обмеження первинного ключа.
CREATE TABLE Diagnos
(IDDiagnos INT IDENTITY PRIMARY KEY,
NameDiagnos varchar (50),
ShifrDiagnos varchar (20),
Norma INT)
Тут можна додати, що первинним ключем відносини Diagnos є атрибут IDDiagnos, таким чином він ідентифікує будь-який кортеж відносини.
Природно, даний вид обмежень використовувався у всіх відносинах бази даних для позначення первинного ключа.
Наступним типом обмеження є обмеження зовнішнього ключа (FOREIGN KEY). Вони використовуються як для забезпечення цілісності даних, так і для завдання відносин між таблицями. При цьому після створення зовнішнього ключа будь-який запис, що додається у посилальне ставлення, повинна мати відповідний запис у таблиці, на яку існує посилання, або значення для стовпців зовнішнього ключа повинні бути встановлені в NULL. Останній випадок у даному курсовому проекті не використовується, тому що це може призвести до порушення узгодженого стану бази даних. Тому наведемо приклад для розглянутого типу обмежень.
CREATE TABLE Otdel
(IDOtdel INT IDENTITY PRIMARY KEY,
Name varchar (30),
IDLPU INT,
TarifOtdel MONEY,
CONSTRAINT OtdelLPUforeign FOREIGN KEY (IDLPU) REFERENCES LPU)
Після виконання цього скрипта ми встановили обмеження зовнішнього ключа для відносини Otdel. Тепер при спробі видалення ЛПУ, первинний ключ якого міститься в розглянутій таблиці в якості зовнішнього (IDLPU), буде видано повідомлення про помилку.
Обмеження зовнішнього ключа ми встановили у відносинах, які є посилаються (див. Додаток 1).
Підтримка цілісності бази даних також здійснюється за допомогою тригерів. Тригер (Trigger) представляє собою певний різновид збереженої процедури, яка виконується при настанні певних подій. Тригери дуже допомагають при реалізації бізнес-правил. Наприклад, у статистичному талоні може бути кілька діагнозів, але при цьому лише один з них може мати тип «основний», а решта - супутній. Для підтримки в базі даних цього правила можна написати наступний тригер.
CREATE TRIGGER OsnovDiagnTalon
ON DiagnTalon
FOR INSERT, UPDATE
AS
IF ((SELECT COUNT (D. Type) FROM DiagnTalon D
INNER JOIN INSERTED I
ON I. IDTalon = D. IDTalon
WHERE D. Type = 'основний' AND D. IDTalon = I. IDTalon
GROUP BY D. IDTalon) <> 1)
BEGIN
RAISERROR ('Не можна мати більше одного основного діагнозу в талоні!', 16,1)
ROLLBACK TRAN
END
Тепер ми при додаванні або редагуванні відносини DiagnTalon (Діагноз у талоні) SQL Server буде стежити за тим, щоб основний діагноз для певного талона був тільки один. Точно так само реалізовано правило, згідно з яким в карті вибулого хворого повинен бути тільки один основний діагноз.
При створенні бази даних ми зіткнулися також з наступною проблемою. Ставлення Talon (Статистичний талон) має атрибути IDLPU, IDVrach, IDPacient, IDType, які представляють собою зовнішні ключі на відносини LPU, Vrach, Type. Ми можемо внести в базу даних інформацію, згідно з якою, наприклад, лікар А лікував в ЛПЗ B в якості спеціаліста C, але при цьому лікар А може не бути лікарем ЛПУ B, і не бути мати спеціальністю C. Щоб виправити цей недолік, були написані відповідні тригери, наведемо приклад одного з них.
ALTER TRIGGER TalonVrachLPU
ON Talon
FOR INSERT, UPDATE
AS
IF (EXISTS (SELECT 'true' FROM INSERTED
WHERE INSERTED.IDVrach NOT IN (SELECT IDVrach FROM
Vrach
WHERE IDLPU = INSERTED.IDLPU)
))
BEGIN
RAISERROR ('Такого лікаря немає у вибраному ЛПУ!', 16,1)
ROLLBACK TRAN
END
Аналогічним чином написані тригери, що забороняють додавання або редагування відносин Talon і Karta таким чином, щоб інформація в базі даних стала суперечливою (лікар не належить даному ЛПЗ або не володіє даною спеціальністю).
У ході аналізу предметної області з'ясувалося, що не можна вводити документацію по пацієнту, який вже помер. Адже померлі не можуть відвідувати лікарів чи лежати в лікарні. Для запобігання введенню такої інформації був написаний тригер, що забороняє введення даних по померлому пацієнтові в ставлення Talon (Статистичний талон), його сценарій наведено нижче:
CREATE TRIGGER StopSmert
ON Talon
FOR INSERT
AS
IF (EXISTS (SELECT 'true' FROM INSERTED
WHERE INSERTED.IDPacient IN (SELECT DISTINCT IDPacient FROM
Talon T
INNER JOIN DiagnTalon DT
ON T. IDTalon = DT.IDTalon
WHERE DT.Ishod = 'Смерть'
UNION
SELECT DISTINCT IDPacient FROM
Karta K
INNER JOIN DiagnKarta DK
ON K. IDKarta = DK.IDKarta
WHERE DK.Ishod = 'Смерть')
))
BEGIN
RAISERROR ('Пацієнт помер!', 16,1)
ROLLBACK TRAN
END
Аналогічно написаний тригер, що забороняє введення інформації за померлим у відношення Karta (Карта вибулого хворого).
Як вже було сказано, фінансування ЛПУ здійснюється на підставі наказу про оплату. Але у фахівця не повинно бути можливості видалити вхідну документацію (талони і карти). Для цього був написаний тригер, заперщающій видалення талонів і карток, за якими було проведено фінансування:
CREATE TRIGGER StopDelTalon
ON Talon
FOR DELETE
AS
IF (EXISTS (SELECT 'true' FROM DELETED
WHERE DELETED.Date BETWEEN (SELECT MAX (BeginPer) FROM Prikas WHERE BeginPer <DELETED.Date AND IDLPU = DELETED.IDLPU)
AND (SELECT MAX (BeginPer) FROM Prikas
WHERE EndPer> DELETED.Date AND IDLPU = DELETED.IDLPU)))
BEGIN
RAISERROR ('Не можна видалити талон, оскільки оплата по ньому вже проводилася!', 16,1)
ROLLBACK TRAN
END
Аналогічно був написаний тригер, що забороняє видалення карт вибулих хворих, за якими вже проводилося фінансування.
Таким чином, за допомогою обмежень і тригерів ми отримали базу даних, що володіє властивістю цілісності.

3.2. Підтримка бізнес-логіки

Підтримка бізнес-логіки є ще одним завданням при розробці бази даних. Бізнес-логіка - логіка виконання бізнес-процесу за певними правилами, званим ще бізнес-правилами. Так, при аналізі предметної області з головного процесу «Врахувати витрати на надані медичні послуги» було виділено 5 підпроцесів:
Ø Фіксувати отриману документацію;
Ø Отримати документацію;
Ø Сформувати заявку;
Ø Оформити платіжне доручення;
Ø Отримати повідомлення про оплату.
База даних будувалася таким чином, щоб зберігати всю вхідну і формувати вихідну документацію, отриману в результаті виконання основного бізнес-процесу. У цьому розділі ми розглянемо, які об'єкти бази даних було створено для забезпечення цієї вимоги.
При розгляді першого підпроцесу (Фіксувати отриману документацію) з'явилася необхідність створення відносин, відповідних вхідним потокам даних. Так з'явилися відносини Talon і Karta з атрибутами, які входять до словника даних (Курсовий проект з дисципліни ІТ). Створення відносини Talon здійснювалося наступним чином:
CREATE TABLE Talon
(Number INT PRIMARY KEY NOT NULL,
Date DATETIME,
Type varchar (30),
IDLPU INT,
IDVrach INT,
IDPacient INT,
IDType INT,
CONSTRAINT TalonLPUforeign FOREIGN KEY (IDLPU) REFERENCES LPU,
CONSTRAINT TalonVrachforeign FOREIGN KEY (IDVrach) REFERENCES Vrach,
CONSTRAINT TalonTypeforeign FOREIGN KEY (IDType) REFERENCES Type,
CONSTRAINT TalonPacientforeign FOREIGN KEY (IDPacient) REFERENCES Pacient)
Зесь слід зазначити, що кожен статистичний талон може містити кілька діагнозів, таким чином з'явилося ставлення DiagnTalon (Діагноз у талоні):
CREATE TABLE DiagnTalon
(IDTalon INT,
IDDiagnos INT,
Ishod varchar (30),
Type varchar (30),
CONSTRAINT PK_Foreign PRIMARY KEY (IDTalon, IDDiagnos),
CONSTRAINT TalonDiagnforeign FOREIGN KEY (IDTalon) REFERENCES Talon ON DELETE CASCADE,
CONSTRAINT DiagnTalonforeign FOREIGN KEY (IDDiagnos) REFERENCES Diagnos)
Особливістю цього відношення є опція ON DELETE CASCADE при обмеженні зовнішнього ключа. Це дозволяє автоматично видалити всі діагнози при видаленні будь-якого статистичного талона. Таке видалення подібне реальному видаленню документа, при цьому всі дані, пов'язані з ним, також видаляються. Звичайно ж, використання цієї опції необхідно не особливо часто, краще застосування вона знаходить саме в слабких сутності, як і в цьому випадку.
Аналогічним чином були створені відносини Karta, Prikas і Isveshenie, які призначені для зберігання інформації з вхідної наступній документації - карта вибулого хворого, наказ і повідомлення про оплату. Вхідний потік інформації Норми на лікування матеріалізувався у вигляді відношення Diagnos, де стала зберігатися інформація про діагнози та строки їх лікування. Створення відносини Diagnos:
CREATE TABLE Diagnos
(IDDiagnos INT IDENTITY PRIMARY KEY,
NameDiagnos varchar (50),
ShifrDiagnos varchar (20),
Norma INT)
Документ Прейскурант цін на медичні послуги знайшов своє вираження у відносинах Type (Спеціальність лікаря) та Otdel (Відділення), в які в якості атрибутів увійшли тарифи з певної спеціальності та відділенню.
CREATE TABLE Otdel
(IDOtdel INT IDENTITY PRIMARY KEY,
Name varchar (30),
IDLPU INT,
TarifOtdel MONEY,
CONSTRAINT OtdelLPUforeign FOREIGN KEY (IDLPU) REFERENCES OtdelLPU)
Вихідний документ Платіжне доручення формується за допомогою збереженої процедури на підставі певного наказу про оплату. Вхідними параметрами для процедури є NameLPU (Найменування ЛПУ) і Date (Дата наказу про оплату).
CREATE PROC Poruchenie
@ NameLPU varchar (50),
@ Date DateTime
AS
SELECT NameLPU, MestoLPU, Date, BeginPer, EndPer, Summa
FROM Prikas
INNER JOIN LPU
ON Prikas.IDLPU = LPU.IDLPU
WHERE LPU.NameLPU = @ NameLPU AND Prikas.Date = @ Date
Ще одним вихідним документів автоматизується процесу є З аявка на фінансування, до якої входять суми для фінансування окремих ЛПУ за період. Вхідними параметрами для процедури є дати початку та кінця періоду. Сама ж процедура реалізована таким чином: вважаються суми окремо для стаціонару (в підзапитах) і для поліклініки, потім дві отримані суми складаються і дають таким чином остаточний результат. Для формування заявки був написаний наступний сценарій, який створює збережену процедуру:
CREATE PROC GetZayavka
@ Begin DateTime,
@ End DateTime
AS
SELECT LPU.NameLPU, LPU.MestoLPU, SUM (TarifType) + Stacionar.Summa [Summa]
FROM Talon
INNER JOIN Type
ON Talon.IDType = Type.IDType
INNER JOIN LPU
ON Talon.IDLPU = LPU.IDLPU
INNER JOIN (SELECT LPU.IDLPU, SUM (TarifOtdel * (Norma * 1.15)) [Summa]
FROM Karta
INNER JOIN LPU
ON Karta.IDLPU = LPU.IDLPU
INNER JOIN OtdelLPU
ON OtdelLPU.IDLPU = LPU.IDLPU
INNER JOIN Otdel
ON Otdel.IDOtdel = OtdelLPU.IDOtdel
INNER JOIN DiagnKarta
ON Karta.IDKarta = DiagnKarta.IDKarta
INNER JOIN Diagnos
ON Diagnos.IDDiagnos = DiagnKarta.IDDiagnos
WHERE Karta.DateEnd BETWEEN @ Begin AND @ End AND DiagnKarta.Type = 'основний'
GROUP BY LPU.IDLPU) Stacionar
ON LPU.IDLPU = Stacionar.IDLPU
WHERE Talon.Date BETWEEN @ Begin AND @ End
GROUP BY LPU.NameLPU, LPU.MestoLPU, Stacionar.Summa
У ході написання курсового проекту з дисципліни «Управління даними» були написані запити до бази даних. Частина з них знайшла своє відображення при створенні бази даних у вигляді збережених процедур. Всі запити перераховувати не має сенсу, тому пояснимо одну з них. Наведена нижче процедура повертає прізвища, імена та по батькові всіх пацієнтів конкретного ЛПУ за період. Таким чином, вхідними параметрами є найменування ЛПУ і дати початку та кінця періоду. У процедурі відбувається об'єднання 3 вибірок - вибірки за датою початку з карти вибулого хворого, за датою кінця з карти і за датою статистичного талона. Зрозуміло, що крім цього в речення WHERE включено умова відсіву пацієнтів тільки по конкретному ЛПУ.
CREATE PROC PacientLPU
@ NameLPU varchar (50),
@ Begin DateTime,
@ End DateTime
AS
SELECT Fam, Im, Otch
FROM Pacient
INNER JOIN Karta
ON Pacient.IDPacient = Karta.IDPacient
INNER JOIN LPU
ON Karta.IDLPU = LPU.IDLPU
WHERE LPU.NameLPU = @ NameLPU AND Karta.DateEnd BETWEEN @ Begin AND @ End