Проектирование и разработка базы данных для муниципальной больницы - Курсовая работа

@surname varchar(15) = null, @name varchar(15) = null, @MIDDLENAME varchar(15) = null, @date date = null, @post varchar(60) = null as select distinct e.Surname as "Фамилия", e.Name as "Имя", e.MIDDLENAME as "Отчество", p.Name as "Специальность" from (p.Surname = @surname or @surname is null) and (p.Name = @name or @name is null) and (p.MIDDLENAME = @MIDDLENAME or @MIDDLENAME is null) and (@date = (select(CAST(rec.TIMERECEPTION as date))) or @date is null) @surname varchar(15) = null, @name varchar(15) = null, @MIDDLENAME varchar(15) = null, @date1 datetime = null, @date2 datetime = null, @nomer int = null as select r.TIMERECEPTION as "Время и дата приема", p.Surname as "Фамилия", p.Name as "Имя", p.MIDDLENAME as "Отчество", d.Name as "Диагноз", m.Name as "Выписанное лекарство", medproc.Name as "Назначенная процедура", r.Notice as "Заметка врача" from (p.Surname = @surname or @surname is null) and (p.Name = @name or @name is null) and (p.MIDDLENAME = @MIDDLENAME or @MIDDLENAME is null) and (r.TIMERECEPTION >= @date1 or @date1 is null) and (r.TIMERECEPTION <= @date2 or @date2 is null) and (p.AMBULANCENO = @nomer or @nomer is null) go @surname varchar(15) = null, @name varchar(15) = null, @MIDDLENAME varchar(15) = null, @post varchar(60) = null as select r.TIMERECEPTION as "Время и дата приема", p.Surname as "Фамилия", p.Name as "Имя", p.MIDDLENAME as "Отчество", p.AMBULANCENO as "Номер абул. карты", d.Name as "Диагноз", m.Name as "Выписанное лекарство", medproc.Name as "Назначенная процедура", r.

Основная цель выполнения индивидуального задания по дисциплине «Технологии баз данных» - углубить и закрепить теоретические знания, полученные в ходе освоения учебной программы, применить основные теоретические положения дисциплины на практике при создании базы данных.

Объектом проектирования в индивидуальном задании является медицинское учреждение - муниципальная больница. В процессе выполнения задания была создана база данных для данного учреждения. Используемая СУБД - Microsoft SQL Server 2008 R2 Developer.

1.

Основная часть

1.1 Описание объекта проектирования

Объектом проектирования в данной работе является муниципальная больница. Больница - вид гражданского стационарного медицинского учреждения, направленного на лечение больных и/или специализированную углубленную дифференциальную диагностику заболеваний в стационарных условиях. Задача данного учреждения заключается в том, чтобы в условиях модернизации здравоохранения организовать: · оказание качественной лечебной, диагностической и профилактической медицинской помощи прикрепленному и обратившемуся населению в амбулаторно-поликлиническом звене учреждения;

· оказание высококвалифицированной и специализированной лечебно-диагностической медицинской помощи лицам, находящимся на лечении в стационаре;

· удовлетворение материальных и иных потребностей медицинского и другого персонала учреждения.

Никакие платы за оказанные услуги в этой больнице не взимаются, т.к. все лечение в муниципальной больнице оплачивается государством.

Представленные в работе функции медицинского учреждения: · определение отделения, в котором будет проходить лечение поступивший больной

· назначение врача больному

· создание амбулаторной медицинской карты больного (в случае отсутствия таковой)

· оказание качественной медицинской помощи больному, которое заключается в приеме выписанных лечащим врачом лекарств

· ведение истории болезни

· выписка больного

Создание информационной системы позволит ускорить процесс обслуживания людей, обратившихся в данное учреждение, а так же предоставит возможность в кратчайшие сроки составить отчет о текущей ситуации с больными.

1.2 Описание бизнес процессов

Поступление больного - непосредственное обращение лица в медицинское учреждение с какими-либо жалобами на свое здоровье.

Регистрация больного - документальное подтверждение обращения больного в больницу и назначение ему лечащего врача в соответствии с расписанием работы этого врача.

Ведение истории болезни - документальное подтверждение ухудшения и/или улучшения состояния здоровья обратившегося больного.

Лечение больного - оказание квалифицированной медицинской помощи в виде назначения лекарств или определенных процедур больному, направленных на улучшения его состояния здоровья.

Выписка больного - закрытие текущей истории болезни в связи с частичным или полным выздоровлением больного.

1.3 Описание пользователей базы данных

Пользователями разрабатываемой базы данных будут являться: · Заведующий больницей

· Заведующий отделением

· Врач

· Медицинский регистратор

1.4

Описание цели и задач базы данных

Целью разрабатываемой базы данных является учет и хранение информации о поступивших больных и проведенных врачебных приемах.

Задачи по вводу данных

1. Ввод (редактирование, удаление) информации о больнице

[заведующий больницей]

2. Ввод (редактирование, удаление) информации об отделениях

[заведующий отделением]

3. Ввод (редактирование, удаление) информации о специальностях

[заведующий больницей]

4. Ввод (редактирование, удаление) информации о расписании

[мед. регистратор]

5. Ввод (редактирование, удаление) информации о приемах

[врач]

6. Ввод (редактирование, удаление) информации о пациентах

[мед. регистратор]

7. Ввод (редактирование, удаление) информации о сотрудниках

[заведующий больницей, заведующий отделением]

8. Ввод (редактирование, удаление) информации о мед. процедурах

[врач]

9. Ввод (редактирование, удаление) информации о медикаментах

[врач]

Задачи по обработке данных

1. Проверка корректности номера амбулаторной карты пациента

Задачи по выводу данных

1. Формирование отчета о расписании (на определенный период)

[мед. регистратор]

2. Формирование отчета о врачах проводивших прием у указанного больного [мед. регистратор, врач]

3. Формирование отчета об истории болезни пациента

[врач]

4. Формирование отчета о количестве врачей определенной специальности [заведующий больницей, заведующий отделением]

5. Формирование отчета обо всех приемах, проведенных указанным врачом [заведующий больницей, заведующий отделением]

6. Формирование информации об указанном медикаменте

[врач]

7. Формирование отчета обо всех сотрудника в отделениях (указанном отделении) [заведующий больницей, заведующий отделением]

8. Формирование отчета обо всех пациентах

[мед. регистратор]

9. Формирование отчета обо всех медикаментах

[врач]

10. Формирование отчета обо всех мед. процедурах

[врач]

1.5 Концептуальная модель

1.6 Реляционная модель

1. Пол (ПОЛИД, Название)

2. Диагноз (ДИАГНОЗИД,Название, Описание)

3. Специальность (СПЕЦИАЛЬНОСТЬИД,Название)

4. Отделение (ОТДЕЛЕНИЕИД, Название)

5. Медикамент (МЕДИКАМЕНТИД,Название,Противопоказания,Показания к применению)

6. МЕДПРОЦЕДУРА (МЕДПРОЦЕДУРАИД, Название, Описание)

7. Больница (БОЛЬНИЦАИД, Название, СОТРУДНИКВОТДЕЛЕНИИИД)

Внешний ключ СОТРУДНИКВОТДЕЛЕНИИИД ссылается на СОТРУДНИКВОТДЕЛЕНИИ (СОТРУДНИКВОТДЕЛЕНИИИД)

8. СОТРУДНИКВОТДЕЛЕНИИ (СОТРУДНИКВОТДЕЛЕНИИИД, СОТРУДНИКИД, ОТДЕЛЕНИЕИД)

Внешний ключ СОТРУДНИКИД ссылается на Сотрудник (СОТРУДНИКИД)

Внешний ключ ОТДЕЛЕНИЕИД ссылается на Отделение (ОТДЕЛЕНИЕИД)

9. Сотрудник (СОТРУДНИКИД,Имя,Фамилия,Отчество, СПЕЦИАЛЬНОСТЬИД)

Внешний ключ СПЕЦИАЛЬНОСТЬИД ссылается на Специальность (СПЕЦИАЛЬНОСТЬИД)

10. Пациент (ПАЦИЕНТИД, Имя, Фамилия, Отчество, ПОЛИД, ДАТАРОЖДЕНИЯ, НОМЕРАМБУЛАТОРНОЙКАРТЫ)

Внешний ключ ПОЛИД ссылается на Пол(ПОЛИД)

11. Прием (ПРИЕМИД, ВРЕМЯПРИЕМА, ЗАМЕТКАВРАЧА, ПАЦИЕНТИД, ДИАГНОЗИД, МЕДИКАМЕНТИД, МЕДПРОЦЕДУРАИД)

Внешний ключ ПАЦИЕНТИД ссылается на Пациент (ПАЦИЕНТИД)

Внешний ключ ДИАГНОЗИД ссылается на Диагноз (ДИАГНОЗИД)

Внешний ключ МЕДИКАМЕНТИД ссылается на Медикамент (МЕДИКАМЕНТИД)

Внешний ключ МЕДПРОЦЕДУРАИД ссылается на МЕДПРОЦЕДУРА (МЕДПРОЦЕДУРАИД)

12. Расписание (РАСПИСАНИЕИД, Кабинет, ПРИЕМИД, СОТРУДНИКИД)

Внешний ключ ПРИЕМИД ссылается на Прием (ПРИЕМИД)

Внешний ключ СОТРУДНИКИД ссылается на Сотрудник (СОТРУДНИКИД)

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 as select rec.TIMERECEPTION as "Время приема", sc.Cabinet as "Кабинет", (e.Surname " " (select(LEFT(e.Name,1))) ". " (select(LEFT(e.MIDDLENAME,1))) ".") as "Врач", p.Name as "Специальность", (pac.Surname " " (select(LEFT(pac.Name,1))) ". " (select(LEFT(pac.MIDDLENAME,1))) ".") as "Пациент" from

Schedule sc inner join Reception rec on sc.RECEPTIONID = rec.ID inner join Employee e on sc.DOCTORID = e.ID, Post p, Pacient pac where e.POSTID = p.ID and rec.PACIENTID = pac.ID and (rec.TIMERECEPTION >= @date1 or @date1 is null) and (rec.TIMERECEPTION <= @date2 or @date2 is null) order by rec.TIMERECEPTION go

Вызов процедуры: exec dbo.GETALLSCHEDULE

@date1 = "2012-01-01", @date2 = "2012-01-05" go

Результат выполнения процедуры:

Вызов процедуры: exec GETALLSCHEDULE

@date1 = "2012-01-04" -начальная дата go

Результат выполнения процедуры:

2. Процедура, формирующая отчет о врачах проводивших прием у указанного больного

Листинг программного кода: create proc GETDOCTORBYPACIENTORPOST

@surname varchar(15) = null, @name varchar(15) = null, @MIDDLENAME varchar(15) = null, @date date = null, @post varchar(60) = null as select distinct e.Surname as "Фамилия", e.Name as "Имя", e.MIDDLENAME as "Отчество", p.Name as "Специальность" from

Employee e inner join Post p on e.POSTID = p.ID, Schedule sc, Reception rep, Pacient pac where

(sc.DOCTORID = e.ID) and (p.Name = @post or @post is null) and (sc.RECEPTIONID in

( select

rec.ID from

Reception rec inner join Pacient p on rec.PACIENTID = p.ID where

(p.Surname = @surname or @surname is null) and (p.Name = @name or @name is null) and (p.MIDDLENAME = @MIDDLENAME or @MIDDLENAME is null) and (@date = (select(CAST(rec.TIMERECEPTION as date))) or @date is null)

)

) go

Вызов процедуры: exec GETDOCTORBYPACIENTORPOST

@surname = "Мельничук" go

Результат выполнения процедуры:

Вызов процедуры: exec GETDOCTORBYPACIENTORPOST

@surname = "Филипинко", @name = "Алексей", @date = "2011-12-15" go

Результат выполнения процедуры:

Вызов процедуры: exec GETDOCTORBYPACIENTORPOST

@AMBULANCENO = 153568, @post = "Эндокринолог" go

Результат выполнения процедуры:

3. Процедура, формирующая историю болезни пациента

Листинг программного кода: create proc GETPACIENTHISTORY

@surname varchar(15) = null, @name varchar(15) = null, @MIDDLENAME varchar(15) = null, @date1 datetime = null, @date2 datetime = null, @nomer int = null as select r.TIMERECEPTION as "Время и дата приема", p.Surname as "Фамилия", p.Name as "Имя", p.MIDDLENAME as "Отчество", d.Name as "Диагноз", m.Name as "Выписанное лекарство", medproc.Name as "Назначенная процедура", r.Notice as "Заметка врача" from

Reception r inner join Pacient p on r.PACIENTID = p.ID inner join Diagnosis d on r.DIAGNOSISID = d.ID inner join Medicine m on r.MEDICINEID = m.ID inner join MEDPROCEDURE medproc on r.MEDPROCEDUREID = medproc.ID where

(p.Surname = @surname or @surname is null) and (p.Name = @name or @name is null) and (p.MIDDLENAME = @MIDDLENAME or @MIDDLENAME is null) and (r.TIMERECEPTION >= @date1 or @date1 is null) and (r.TIMERECEPTION <= @date2 or @date2 is null) and (p.AMBULANCENO = @nomer or @nomer is null) go

Вызов процедуры: exec GETPACIENTHISTORY

@MIDDLENAME = "Владимировна", @name = "Ирина" go

Результат выполнения процедуры:

Вызов процедуры: exec GETPACIENTHISTORY

@nomer = 154987, @date1 = "2012-01-02" go

Результат выполнения процедуры:

Вызов процедуры: exec GETPACIENTHISTORY

@surname = "Мельничук, @date1 = "2011-05-04", @date2 = "2012-01-11" go

Результат выполнения процедуры:

4. Процедура, отображающая количество врачей указанной специальности

Листинг программного кода: create proc GETDOCTORSCOUNTWITHPOST

@post varchar(60) = null, @count int out as select distinct

COUNT(*) as "Количество" from

Employee e inner join Post p on e.POSTID = p.ID where p.Name = @post go

Вызов процедуры: exec GETDOCTORSCOUNTWITHPOST

@post = "Терапевт" go

Результат выполнения процедуры:

5. Процедура, формирующая отчет обо всех приемах, проведенных указанным врачом

Листинг программного кода: create proc GETALLRECEPTIONSATDOCTOR

@surname varchar(15) = null, @name varchar(15) = null, @MIDDLENAME varchar(15) = null, @post varchar(60) = null as select r.TIMERECEPTION as "Время и дата приема", p.Surname as "Фамилия", p.Name as "Имя", p.MIDDLENAME as "Отчество", p.AMBULANCENO as "Номер абул. карты", d.Name as "Диагноз", m.Name as "Выписанное лекарство", medproc.Name as "Назначенная процедура", r.Notice as "Заметка врача" from

Reception r inner join Pacient p on r.PACIENTID = p.ID inner join Diagnosis d on r.DIAGNOSISID = d.ID inner join Medicine m on r.MEDICINEID = m.ID inner join MEDPROCEDURE medproc on r.MEDPROCEDUREID = medproc.ID where r.ID in

( select sc.ID from

Schedule sc inner join Employee e on sc.DOCTORID = e.ID, Post post where

(e.POSTID = post.ID) and (e.Surname = @surname or @surname is null) and (e.Name = @name or @name is null) and (e.MIDDLENAME = @MIDDLENAME or @MIDDLENAME is null) and (@post = post.Name or @post is null)

) go

Вызов процедуры: exec GETALLRECEPTIONSATDOCTOR

@surname = "Сидоров" go

Результат выполнения процедуры:

exec GETALLRECEPTIONSATDOCTOR

@MIDDLENAME = "Дмитриевич", @post = "хирург" go

Результат выполнения процедуры:

6. Процедура, отображающая информацию об указанном медикаменте

Листинг программного кода: create proc GETINFOABOUTMEDICINE

@name varchar(20) as select m.Name as "Название", m.INDICATIONFOR as "Показания к применению", m.Contraindication as "Противопоказания" from

Medicine m where m.Name = @name go

Вызов процедуры: exec GETINFOABOUTMEDICINE

@name = "Омепразол"

Go

Результат выполнения процедуры:

7. Процедура, формирующая отчет обо всех сотрудниках во всех (или выбранном) отделениях

Листинг программного кода: create proc GETEMPLOYEEINDEPARTAMENTS

@departament varchar(60) = null as select

(e.Surname " " (select(LEFT(e.Name,1))) ". " (select(LEFT(e.MIDDLENAME,1))) ".") as "Врач", dep.Name as "Отделение" from

EMPLOYEEINDEPARTAMENTS eid inner join Employee e on eid.EMPLOYEEID = e.ID inner join Departament dep on eid.DEPARTAMENTID = dep.ID where dep.Name = @departament or @departament is null order by dep.Name go

Вызов процедуры: exec GETEMPLOYEEINDEPARTAMENTS

Результат выполнения процедуры:

8. Процедура, формирующая список всех пациентов

Листинг программного кода: create proc GETALLPACIENT as select pac.Surname as "Фамилия", pac.Name as "Имя", pac.MIDDLENAME as "Отчество", gend.Name as Пол", pac.DATEBIRTH as "Дата рождения", pac.AMBULANCENO as "Номер абмул. карты" from

Pacient pac inner join Gender gend on pac.GENDERID = gend.ID go

Вызов процедуры: exec GETALLPACIENT

Результат выполнения процедуры:

9. Процедура, формирующая список всех врачей в указанном отделении и количество приемов, проведенных каждым из них.

Листинг программного кода: create procedure GETCOUNTOFRECEPTIONSINCHOOSENDEP

@name varchar(60) as select e.Surname, e.Name, e.MIDDLENAME, p.Name, count(sc.RECEPTIONID) as "Количество приемов" from

Employee e, Post p, Schedule sc where sc.DOCTORID = e.ID and e.POSTID = p.ID group by e.ID,e.Name,e.MIDDLENAME,e.Surname,p.Name having e.ID in

(select EMPLOYEEINDEPARTAMENTS.EMPLOYEEID from EMPLOYEEINDEPARTAMENTS where EMPLOYEEINDEPARTAMENTS.DEPARTAMENTID = (select Departament.ID from Departament where Departament.Name = @name)) go

Вызов процедуры: exec GETCOUNTOFRECEPTIONSINCHOOSENDEP

@name = "Хирургическое"

Результат выполнения процедуры:

10. Процедура, отображающая доктора(ов), которые провели наибольшее количество приемов. В качестве входящего параметра выступает специальность доктора.

Листинг программного кода: create procedure GETMAXRECEPTIONSATPOST

@post varchar(20) as select q2.Surname as "Фамилия", q2.Name as "Имя", q2.MIDDLENAME as "Отчество", q2.receptions as "Количество приемов" from

( select max(q1.receptions) as "count" from

( select e.Surname,e.Name,e.MIDDLENAME, count(sc.RECEPTIONID) as "receptions" from

Employee e, Post p, Schedule sc where sc.DOCTORID = e.ID and e.POSTID = p.ID and p.Name = @post group by e.ID,e.Surname,e.Name,e.MIDDLENAME

) q1

) qmax, ( select e.Surname,e.Name,e.MIDDLENAME, count(sc.RECEPTIONID) as "receptions" from

Employee e, Post p, Schedule sc where sc.DOCTORID = e.ID and e.POSTID = p.ID and p.Name = @post group by e.ID,e.Surname,e.Name,e.MIDDLENAME

) q2 where q2.receptions = qmax.count go

Вызов процедуры: exec GETMAXRECEPTIONSATPOST

@post = "хирург"

Результат работы процедуры:

1.8.2 Триггеры

1. Триггер CHECKRECEPTIONDATETIME

В таблице dbo.Reception сопоставляется дата добавляемого приема и дата рождения указанного пациента. В случае, если дата приема меньше даты рождения пациента происходит откат.

Листинг программного кода: create trigger CHECKRECEPTIONDATETIME on dbo.Reception for insert as if (select i.TIMERECEPTION from inserted i) < (select p.DATEBIRTH from Pacient p,inserted i where p.ID = i.PACIENTID) begin print "Дата приема не может быть меньше даты рождения пациента" rollback end go

Некорректная попытка добавления приема: exec ADDRECEPTION

@PACIENTID = 1, @notice = "значительное улучшение", @DIAGNOSISID = 1, @MEDICINEID = 1, @TIMERECEPTION = "1919-01-01", @MEDPROCEDUREID = 1 go

Результат срабатывания триггера:

2. Триггер DONTUPDATEGENDER

Данный триггер запрещает добавление и удаление строк в таблицу dbo.Gender, после того, как уже были введены 2 пола.

Листинг программного кода: create trigger DONTUPDATEGENDER on dbo.Gender for update,insert,delete as if (select COUNT(*) from Gender) > 1 begin print "Запрещено добавление или удаление строк в таблице" rollback end go

Пример кода для срабатывания триггера: select * from Gender insert into Gender(Name) values("M")

Результат срабатывания триггера:

3. Триггер INCORRECTHOSPNAME

Проверяет корректность введеного названия больницы при изменении или добавлении строк (при условии что в таблице уже есть хотя бы 1 строка).

Листинг программного кода: create trigger INCORRECTHOSPNAME on dbo.Hospital for update, insert as if(select COUNT(*) from Hospital) > 0 begin if ((select i.Name from inserted i) (select top 1 Name from dbo.Hospital)) begin print "Введеное имя больницы отличается от имеющегося. Изменение таблицы отменено" rollback end end go

Пример кода для срабатывания триггера: select * from Hospital update Hospital set Name = "Больница №3" where ID = 1

4. Триггер, осуществляющий проверку уникальности номера амбулаторной карты пациента. В случае, если номер карты добавляемого/изменяемого пациента не уникален, то он изменяется на уникальный, полученный с помощью функции Rand().

Листинг программного кода: create trigger CHECKABULANCENO on dbo.Pacient after update,insert as if (select count(*) from Pacient p,inserted i where p.AMBULANCENO = i.AMBULANCENO) > 1 begin declare @num int = (select inserted.AMBULANCENO from inserted)

---- while (select COUNT(*) from Pacient where Pacient.AMBULANCENO = @num) > 0 set @num = Floor(RAND() * 100000)

---- update Pacient set AMBULANCENO = @num from inserted i where Pacient.ID = i.ID print "Номер карты изменен на случайный" end go

Пример кода для срабатывания триггера: update Pacient set AMBULANCENO =

(select Pacient.AMBULANCENO from Pacient where Pacient.ID = 0) where ID = 1

Результат срабатывания триггера:

Виейра Р. «Программирование баз данных Microsoft SQL Server 2005»

Размещено на