Проектирование и разработка базы данных для муниципальной больницы
Введение
Основная цель выполнения индивидуального задания по дисциплине
«Технологии баз данных» - углубить и закрепить теоретические знания, полученные
в ходе освоения учебной программы, применить основные теоретические положения
дисциплины на практике при создании базы данных.
Объектом проектирования в индивидуальном задании является медицинское
учреждение - муниципальная больница. В процессе выполнения задания была создана
база данных для данного учреждения. Используемая СУБД - Microsoft SQL Server 2008 R2 Developer.
1.
Основная часть
1.1 Описание объекта проектирования
Объектом проектирования в данной работе является муниципальная больница.
Больница - вид гражданского стационарного медицинского учреждения,
направленного на лечение больных и/или специализированную углубленную
дифференциальную диагностику заболеваний в стационарных условиях. Задача
данного учреждения заключается в том, чтобы в условиях модернизации
здравоохранения организовать:
· оказание качественной лечебной, диагностической и профилактической
медицинской помощи прикрепленному и обратившемуся населению в
амбулаторно-поликлиническом звене учреждения;
· оказание высококвалифицированной и специализированной
лечебно-диагностической медицинской помощи лицам, находящимся на лечении в
стационаре;
· удовлетворение материальных и иных потребностей медицинского
и другого персонала учреждения.
Никакие платы за оказанные услуги в этой больнице не взимаются, т.к. все
лечение в муниципальной больнице оплачивается государством.
Представленные в работе функции медицинского учреждения:
· определение отделения, в котором будет проходить лечение
поступивший больной
· назначение врача больному
· создание амбулаторной медицинской карты больного (в случае
отсутствия таковой)
· оказание качественной медицинской помощи больному, которое
заключается в приеме выписанных лечащим врачом лекарств
· ведение истории болезни
· выписка больного
Создание информационной системы позволит ускорить процесс обслуживания
людей, обратившихся в данное учреждение, а так же предоставит возможность в
кратчайшие сроки составить отчет о текущей ситуации с больными.
1.2 Описание бизнес процессов
Поступление больного - непосредственное обращение лица в медицинское
учреждение с какими-либо жалобами на свое здоровье.
Регистрация больного - документальное подтверждение обращения больного в
больницу и назначение ему лечащего врача в соответствии с расписанием работы
этого врача.
Ведение истории болезни - документальное подтверждение ухудшения и/или
улучшения состояния здоровья обратившегося больного.
Лечение больного - оказание квалифицированной медицинской помощи в виде
назначения лекарств или определенных процедур больному, направленных на
улучшения его состояния здоровья.
Выписка больного - закрытие текущей истории болезни в связи с частичным
или полным выздоровлением больного.
1.3 Описание
пользователей базы данных
Пользователями разрабатываемой базы данных будут являться:
· Заведующий больницей
· Заведующий отделением
· Врач
· Медицинский регистратор
1.4
Описание цели и задач базы данных
Целью разрабатываемой базы данных является учет и хранение информации о
поступивших больных и проведенных врачебных приемах.
Задачи по вводу данных
. Ввод (редактирование, удаление) информации о больнице
[заведующий больницей]
. Ввод (редактирование, удаление) информации об отделениях
[заведующий отделением]
. Ввод (редактирование, удаление) информации о специальностях
[заведующий больницей]
. Ввод (редактирование, удаление) информации о расписании
[мед. регистратор]
. Ввод (редактирование, удаление) информации о приемах
[врач]
. Ввод (редактирование, удаление) информации о пациентах
[мед. регистратор]
. Ввод (редактирование, удаление) информации о сотрудниках
[заведующий больницей, заведующий отделением]
8. Ввод (редактирование, удаление) информации о мед. процедурах
[врач]
. Ввод (редактирование, удаление) информации о медикаментах
[врач]
Задачи по обработке данных
. Проверка корректности номера амбулаторной карты пациента
Задачи по выводу данных
. Формирование отчета о расписании (на определенный период)
[мед. регистратор]
. Формирование отчета о врачах проводивших прием у указанного
больного [мед. регистратор, врач]
. Формирование отчета об истории болезни пациента
[врач]
. Формирование отчета о количестве врачей определенной
специальности [заведующий больницей, заведующий отделением]
. Формирование отчета обо всех приемах, проведенных указанным
врачом [заведующий больницей, заведующий отделением]
. Формирование информации об указанном медикаменте
[врач]
. Формирование отчета обо всех сотрудника в отделениях (указанном
отделении) [заведующий больницей, заведующий отделением]
. Формирование отчета обо всех пациентах
[мед. регистратор]
. Формирование отчета обо всех медикаментах
[врач]
. Формирование отчета обо всех мед. процедурах
[врач]
1.5 Концептуальная
модель
1.6 Реляционная
модель
1. Пол (ПолИД, Название)
. Диагноз (ДиагнозИД,Название, Описание)
. Специальность (СпециальностьИД,Название)
. Медикамент (МедикаментИД,Название,Противопоказания,Показания к
применению)
. МедПроцедура (МедПроцедураИД, Название, Описание)
. Больница (БольницаИД, Название, СотрудникВОтделенииИД)
Внешний ключ СотрудникВОтделенииИД ссылается на СотрудникВОтделении
(СотрудникВОтделенииИД)
. СотрудникВОтделении (СотрудникВОтделенииИД, СотрудникИД,
ОтделениеИД)
Внешний ключ СотрудникИД ссылается на Сотрудник (СотрудникИД)
Внешний ключ ОтделениеИД ссылается на Отделение (ОтделениеИД)
. Сотрудник (СотрудникИД,Имя,Фамилия,Отчество, СпециальностьИД)
Внешний ключ СпециальностьИД ссылается на Специальность (СпециальностьИД)
. Пациент (ПациентИД, Имя, Фамилия, Отчество, ПолИД, ДатаРождения,
НомерАмбулаторнойКарты)
Внешний ключ ПолИД ссылается на Пол(ПолИД)
. Прием (ПриемИД, ВремяПриема, ЗаметкаВрача, ПациентИД, ДиагнозИД,
МедикаментИД, МедПроцедураИД)
Внешний ключ ПациентИД ссылается на Пациент (ПациентИД)
Внешний ключ ДиагнозИД ссылается на Диагноз (ДиагнозИД)
Внешний ключ МедикаментИД ссылается на Медикамент (МедикаментИД)
Внешний ключ МедПроцедураИД ссылается на МедПроцедура (МедПроцедураИД)
. Расписание (РасписаниеИД, Кабинет, ПриемИД, СотрудникИД)
Внешний ключ ПриемИД ссылается на Прием (ПриемИД)
Внешний ключ СотрудникИД ссылается на Сотрудник (СотрудникИД)
1.7 Структура
таблиц
Рисунок
1. Таблицы базы данных
Таблица «Отделение» («Departament»)
Таблица «Диагноз» («Diagnosis»)
Таблица «Персонал» («Employee»)
Таблица «Персонал в отделениях» («EmployeeInDepartaments»)
Таблица «Пол» («Gender»)
Таблица «Больница» («Hospital»)
Таблица «Медикаменты» («Medicine»)
Таблица «Мед. процедуры» («MedProcedure»)
Таблица «Пациент» («Pacient»)
Таблица «Специальность» («Post»)
Таблица «Прием» («Reception»)
Таблица «Расписание» («Schedule»)
1.8 Объекты
базы данных, решающие поставленные задачи
база
данные пользователь
1.8.1 Процедуры
1. Процедура, формирующая расписание на заданный период времени:
Листинг программного кода:
create proc GetAllSchedule
@date1 datetime = null,
@date2 datetime = null.TimeReception as 'Время приема',.Cabinet as
'Кабинет',
(e.Surname + ' ' + (select(LEFT(e.Name,1))) + '. ' +
(select(LEFT(e.MiddleName,1))) + '.') as 'Врач',.Name as 'Специальность',
(pac.Surname + ' ' + (select(LEFT(pac.Name,1))) + '. ' +
(select(LEFT(pac.MiddleName,1))) + '.') as 'Пациент'sc inner join Reception
recsc.ReceptionID = rec.IDjoin Employee esc.DoctorID = e.ID,p,pac.PostID = p.ID
and rec.PacientID = pac.ID
(rec.TimeReception >= @date1 or @date1 is null)
(rec.TimeReception <= @date2 or @date2 is null)by.TimeReception
Вызов процедуры:
exec dbo.GetAllSchedule
@date1 = '2012-01-01',
@date2 = '2012-01-05'
Вызов процедуры:
exec GetAllSchedule
@date1 = '2012-01-04' --начальная дата
Результат выполнения процедуры:
. Процедура, формирующая отчет о врачах проводивших прием у указанного
больного
Листинг программного кода:
create proc GetDoctorByPacientOrPost
@surname varchar(15) = null,
@name varchar(15) = null,
@middleName varchar(15) = null,
@date date = null,
@post varchar(60) = nulldistinct.Surname as 'Фамилия',.Name
as 'Имя',.MiddleName as 'Отчество',.Name as 'Специальность'e inner join Post p
on e.PostID = p.ID,sc,rep,pac
(sc.DoctorID = e.ID)
(p.Name = @post or @post is null)
(sc.ReceptionID in
(.IDrec inner join Pacient prec.PacientID = p.ID
(p.Surname = @surname or @surname is null)
(p.Name = @name or @name is null)
(p.MiddleName = @middleName or @middleName is null)
(@date = (select(CAST(rec.TimeReception as date))) or @date
is null)
)
)
Вызов процедуры:
exec GetDoctorByPacientOrPost
@surname = 'Мельничук'
Результат выполнения процедуры:
Вызов процедуры:
exec GetDoctorByPacientOrPost
@surname = 'Филипинко',
@name = 'Алексей',
@date = '2011-12-15'
Результат выполнения процедуры:
Вызов процедуры:
exec GetDoctorByPacientOrPost
@ambulanceNo = 153568,
@post = 'Эндокринолог'
Результат выполнения процедуры:
. Процедура, формирующая историю болезни пациента
Листинг программного кода:
create proc GetPacientHistory
@surname varchar(15) = null,
@name varchar(15) = null,
@middleName varchar(15) = null,
@date1 datetime = null,
@date2 datetime = null,
@nomer int =
null.TimeReception as 'Время и дата приема',.Surname as 'Фамилия',.Name as
'Имя',.MiddleName as 'Отчество',.Name as 'Диагноз',.Name as 'Выписанное
лекарство',.Name as 'Назначенная процедура',.Notice as 'Заметка врача'rjoin
Pacient pr.PacientID = p.IDjoin Diagnosis dr.DiagnosisID = d.IDjoin Medicine mr.MedicineID
= m.IDjoin MedProcedure medprocr.MedProcedureID = medproc.ID
(p.Surname = @surname or
@surname is null)
(p.Name = @name or @name is
null)
(p.MiddleName = @middleName
or @middleName is null)
(r.TimeReception >= @date1
or @date1 is null)
(r.TimeReception <= @date2
or @date2 is null)
(p.AmbulanceNo = @nomer or
@nomer is null)
Вызов процедуры:
exec GetPacientHistory
@middleName = 'Владимировна',
@name = 'Ирина'
Результат выполнения
процедуры:
Вызов процедуры:
exec GetPacientHistory
@nomer = 154987,
@date1 = '2012-01-02'
Результат выполнения
процедуры:
Вызов процедуры:
@surname = 'Мельничук,
@date1 = '2011-05-04',
@date2 = '2012-01-11'
Результат выполнения
процедуры:
. Процедура, отображающая
количество врачей указанной специальности
Листинг программного кода:
create proc
GetDoctorsCountWithPost
@post varchar(60) = null,
@count int outdistinct(*) as
'Количество'ejoin Post pe.PostID = p.ID.Name = @post
Вызов процедуры:
exec GetDoctorsCountWithPost
@post = 'Терапевт'
Результат выполнения
процедуры:
. Процедура, формирующая
отчет обо всех приемах, проведенных указанным врачом
Листинг программного кода:
create proc
GetAllReceptionsAtDoctor
@surname
varchar(15) = null,
@name varchar(15)
= null,
@middleName
varchar(15) = null,
@post varchar(60)
= null.TimeReception as 'Время и дата приема',.Surname as 'Фамилия',.Name as
'Имя',.MiddleName as 'Отчество',.AmbulanceNo as 'Номер абул. карты',.Name as
'Диагноз',.Name as 'Выписанное лекарство',.Name as 'Назначенная
процедура',.Notice as 'Заметка врача'rjoin Pacient pr.PacientID = p.IDjoin
Diagnosis dr.DiagnosisID = d.IDjoin Medicine mr.MedicineID = m.IDjoin
MedProcedure medprocr.MedProcedureID = medproc.ID.ID in
(.IDscjoin Employee
esc.DoctorID = e.ID,post
(e.PostID =
post.ID)
(e.Surname =
@surname or @surname is null)
(e.Name = @name or
@name is null)
(e.MiddleName =
@middleName or @middleName is null)
(@post = post.Name
or @post is null)
)
Вызов процедуры:
exec GetAllReceptionsAtDoctor
@surname = 'Сидоров'
Результат выполнения
процедуры:
exec GetAllReceptionsAtDoctor
@middleName = 'Дмитриевич',
@post = 'хирург'
Результат выполнения
процедуры:
. Процедура, отображающая
информацию об указанном медикаменте
Листинг программного кода:
create proc
GetInfoAboutMedicine
@name
varchar(20).Name as 'Название',.IndicationFor as 'Показания к
применению',.Contraindication as 'Противопоказания'm.Name = @name
Вызов процедуры:
exec
GetInfoAboutMedicine
@name =
'Омепразол'
Результат выполнения
процедуры:
. Процедура, формирующая
отчет обо всех сотрудниках во всех (или выбранном) отделениях
Листинг программного кода:
create proc
GetEmployeeInDepartaments
@departament
varchar(60) = null
(e.Surname + ' ' +
(select(LEFT(e.Name,1))) + '. ' + (select(LEFT(e.MiddleName,1))) + '.') as
'Врач',.Name as 'Отделение'eid inner join Employee eeid.EmployeeID = e.IDjoin
Departament depeid.DepartamentID = dep.ID.Name = @departament or @departament
is nullby.Name
Вызов процедуры:
exec
GetEmployeeInDepartaments
Результат выполнения
процедуры:
. Процедура, формирующая
список всех пациентов
Листинг программного кода:
create proc
GetAllPacient.Surname as 'Фамилия',.Name as 'Имя',.MiddleName as
'Отчество',.Name as Пол',.DateBirth as 'Дата рождения',.AmbulanceNo as 'Номер
абмул. карты'pacjoin Gender gendpac.GenderID = gend.ID
Вызов процедуры:
exec GetAllPacient
Результат выполнения
процедуры:
. Процедура, формирующая
список всех врачей в указанном отделении и количество приемов, проведенных
каждым из них.
Листинг программного кода:
create procedure
GetCountOfReceptionsInChoosenDep
@name
varchar(60).Surname,.Name,.MiddleName,.Name,(sc.ReceptionID) as 'Количество
приемов'e,p,sc.DoctorID = e.ID.PostID =
p.IDby.ID,e.Name,e.MiddleName,e.Surname,p.Name.ID in
(select
EmployeeInDepartaments.EmployeeID from EmployeeInDepartamentsEmployeeInDepartaments.DepartamentID
= (select Departament.ID from Departament where Departament.Name = @name))
Вызов процедуры:
@name = 'Хирургическое'
Результат выполнения
процедуры:
. Процедура, отображающая
доктора(ов), которые провели наибольшее количество приемов. В качестве
входящего параметра выступает специальность доктора.
Листинг программного кода:
create procedure
GetMaxReceptionsAtPost
@post varchar(20).Surname as
'Фамилия',.Name as 'Имя',.MiddleName as 'Отчество',.receptions as 'Количество
приемов'
((q1.receptions) as 'count'
(.Surname,e.Name,e.MiddleName,(sc.ReceptionID)
as 'receptions'e,p,sc.DoctorID = e.ID.PostID = p.ID.Name = @postby.ID,e.Surname,e.Name,e.MiddleName
) q1
) qmax,
(.Surname,e.Name,e.MiddleName,(sc.ReceptionID)
as 'receptions'e,p,sc.DoctorID = e.ID.PostID = p.ID.Name =
@postby.ID,e.Surname,e.Name,e.MiddleName
) q2.receptions = qmax.count
Вызов
процедуры:GetMaxReceptionsAtPost
@post = 'хирург'
Результат работы процедуры:
1.8.2 Триггеры
1. Триггер CheckReceptionDateTime
В таблице dbo.Reception сопоставляется дата добавляемого приема и дата рождения указанного
пациента. В случае, если дата приема меньше даты рождения пациента происходит
откат.
Листинг программного кода:
create trigger
CheckReceptionDateTimedbo.Receptioninsert(select i.TimeReception from inserted
i) < (select p.DateBirth from Pacient p,inserted i where p.ID =
i.PacientID)'Дата приема не может быть меньше даты рождения пациента'
Некорректная попытка добавления приема:AddReception
@pacientID = 1,
@notice = 'значительное улучшение',
@diagnosisID = 1,
@medicineID = 1,
@timeReception = '1919-01-01',
@medProcedureID = 1
Результат срабатывания триггера:
. Триггер DontUpdateGender
Данный триггер запрещает добавление и удаление строк в таблицу dbo.Gender,
после того, как уже были введены 2 пола.
Листинг программного кода:
create trigger DontUpdateGenderdbo.Genderupdate,insert,delete(select
COUNT(*) from Gender) > 1'Запрещено добавление или удаление строк в таблице'
Пример кода для срабатывания триггера:* from Genderinto
Gender(Name) values('M')
Результат срабатывания триггера:
. Триггер
IncorrectHospName
Проверяет корректность введеного названия больницы при изменении или
добавлении строк (при условии что в таблице уже есть хотя бы 1 строка).
Листинг программного кода:
create trigger IncorrectHospNamedbo.Hospitalupdate,
insert(select COUNT(*) from Hospital) > 0((select i.Name from inserted i)
<> (select top 1 Name from dbo.Hospital))'Введеное имя больницы
отличается от имеющегося. Изменение таблицы отменено'
Пример кода для срабатывания триггера:* from HospitalHospitalName
= 'Больница №3'ID = 1
. Триггер, осуществляющий проверку уникальности номера амбулаторной карты
пациента. В случае, если номер карты добавляемого/изменяемого пациента не
уникален, то он изменяется на уникальный, полученный с помощью функции Rand().
Листинг программного кода:
create trigger CheckAbulanceNodbo.Pacientupdate,insert(select
count(*) from Pacient p,inserted i where p.AmbulanceNo = i.AmbulanceNo) >
1@num int = (select inserted.AmbulanceNo from inserted)
---(select COUNT(*) from Pacient where Pacient.AmbulanceNo =
@num) > 0@num = Floor(RAND() * 100000)
---PacientAmbulanceNo = @numinserted iPacient.ID = i.ID'Номер
карты изменен на случайный'
Пример кода для срабатывания триггера:PacientAmbulanceNo =
(select Pacient.AmbulanceNo from Pacient where Pacient.ID =
0)ID = 1
Результат срабатывания триггера:
Список
литературы
Виейра Р.
«Программирование баз данных Microsoft SQL Server 2005»