--PAGE_BREAK--Согласно вышеуказанной классификации все ограничения целостности базы данных можно разделить на 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
Ограничение 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
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
продолжение
--PAGE_BREAK--