Проектирование базы данных ортопедического отделения больницы
КУРСОВАЯ РАБОТА
по дисциплине: «Базы данных»
на тему: «Проектирование базы данных
ортопедического отделения больницы»
Оглавление
ВВЕДЕНИЕ
1 НОРМАЛИЗАЦИЯ БД
2 ПРАКТИЧЕСКАЯ
ЧАСТЬ
.1 Структура
системы, перечень подсистем
.2 Создание
таблиц
.3 Триггер
.4 Процедура
ЗАКЛЮЧЕНИЕ
СПИСОК ЛИТЕРАТУРЫ
ВВЕДЕНИЕ
Необходимо создать базу данных, которая включает в себя информацию об
ортопедическом отделении больницы. В базу должны входить несколько таблиц:
список операционных больных, в котором отображается вся информация о пациентах;
список медперсонала, в котором отображается вся информация о сотрудниках
ортопедического отделения; список операций, который отображает дату операции,
больного, вид операции и номер работающей бригады; график работы каждой бригады;
список назначений пациентам; второстепенные необходимые таблицы.
1 НОРМАЛИЗАЦИЯ БД
НФ (Первая нормальная форма)
Первая нормальная форма (1НФ) - это обычное отношение. Согласно нашему
определению отношений, любое отношение автоматически уже находится в 1НФ.
Напомним кратко свойства отношений (это и будут свойства 1НФ):
1
В отношении нет
одинаковых кортежей.
2
Кортежи не
упорядочены.
3
Атрибуты не
упорядочены и различаются по наименованию.
4
Все значения
атрибутов атомарны.
В ходе логического моделирования на первом шаге предложено хранить данные
в одном отношении, имеющем следующие атрибуты:
ОРТОПЕДИЧЕСКОЕ ОТДЕЛЕНИЕ (ID_Pacienta, Diagnoz, Fio, №palati FIO rodstv, DATA operacii, ID_rabotnika, №brigadi , Doljnost rabotnika, FIO rabotnika, Naznachenie
где
ID_Pacienta - идентификационный номер пациента
Fio -
фамилия, имя, отчество пациента
Diagnoz
- диагноз пациента
№palati - номер палаты
FIO rodstv - номер ФИО
ближайшего родственника пациента
DATA operacii - дата
операции
ID_rabotnika - номер идентификационный номер
работника отделения
№brigadi - номер бригады, в которой работает
сотрудник
Doljnost rabotnika -
должность работника
FIO rabotnika - ФИО
работника отделения
Naznachenie - назначенные пациенту лекарства
Таблица 1 - Ортопедическое отделение (1НФ)
ID_Pacienta
|
Fio
|
Diagnoz
|
№palati
|
FIO rodstv
|
DATA operacii
|
ID_rabotnika
|
№brigadi
|
FIO rabotnika
|
Doljnost rabotnika
|
Naznachenie
|
1
|
Иванов И.И.
|
Миниск
|
1
|
Иванов И.И.
|
10.10.10
|
1
|
1
|
Мазур В.В.
|
хирург
|
аспирин
|
2
|
Петров П.П.
|
Миниск
|
1
|
Петров П.П
|
12.10.10
|
2
|
1
|
Мазур В.В.
|
хирург
|
Аспирин
|
Сидоров В.В
|
Миниск
|
2
|
Сидоров В.В.
|
14.10.10.
|
3
|
2
|
Сахренко И.И.
|
хирург
|
Аспирин
|
4
|
Савинов М.Г.
|
Миниск
|
3
|
Савинова Г.В.
|
14.10.10
|
4
|
3
|
Демин М.В.
|
Хирург-ортопед
|
Аспирин
|
5
|
Петрова С.С.
|
Миниск
|
3
|
Петрова С.С.
|
15.10.10
|
5
|
3
|
Демин М.В.
|
Хирург-ортопед
|
Аспирин
|
НФ (Вторая Нормальная Форма)
Отношение находится во второй нормальной форме (2НФ) тогда и только
тогда, когда отношение находится в 1НФ и нет неключевых атрибутов, зависящих от
части сложного ключа. (Неключевой атрибут - это атрибут, не входящий в состав
никакого потенциального ключа).
Замечание. Если потенциальный ключ отношения является простым, то
отношение автоматически находится в 2НФ.
В выше представленной таблице были добавлены второстепенные ключи(foreign key), а так же созданы соответствующие дополнительные
таблицы, перечисляющие относящиеся к нужным полям данные:
Таблица 2 - Ортопедическое отделение (2НФ)
ID_Pacienta
|
Fio
|
Diagnoz
|
№palati
|
FIO rodstv
|
1
|
Иванов И.И.
|
Миниск
|
1
|
Иванов
|
2
|
Петров П.П.
|
Миниск
|
1
|
Петров
|
3
|
Сидоров В.В
|
Разрыв АКС
|
2
|
Сидоров
|
4
|
Савинов М.Г.
|
Разрыв АКС
|
3
|
Савинова
|
5
|
Петрова С.С.
|
Разрыв АКС
|
3
|
Петрова
|
Таблица 3 - Ортопедическое отделения(2)
ID_Operacii(PK)
|
ID_Pacienta
|
Vid operacii
|
Data operacii
|
1
|
1
|
1
|
Удаление жидкости
|
10.10.12
|
2
|
2
|
1
|
Удаление жидкости
|
11.10.12
|
3
|
3
|
2
|
Удаление пластины
|
11.10.12
|
4
|
4
|
3
|
Удаление пластины
|
12.10.12
|
5
|
5
|
2
|
Синтез пластины
|
15.10.12
|
Таблица 4 - Отношение «диагнозы»
IDДиагноз
|
|
1
|
Разрыв связки
|
2
|
Вывих
|
3
|
Остеомиелит
|
4
|
Артрогрипоз
|
5
|
Миниск
|
6
|
Анкилоз
|
7
|
Перелом
|
8
|
Повреждение АКС
|
9
|
Анкилоз
|
10
|
Бурсит
|
3НФ (Третья Нормальная Форма)
Атрибуты называются взаимно независимыми, если ни один из них не является
функционально зависимым от другого.
Отношение находится в третьей нормальной форме (3НФ) тогда и только
тогда, когда отношение находится в 2НФ и все не ключевые атрибуты взаимно
независимы.
Для достижения 3НФ необходимо избавиться от полей, не имеющих
второстепенную связь. Следовательно, необходимо создать дополнительные таблицы,
отображающие список с нужной информацией
Таблица 5 - Отношение «Бригады»
ID_brigadi
|
№brigadi
|
1
|
1
|
2
|
2
|
3
|
3
|
Таблица 6 - Отношение «Вид операции»
ID
|
Vid operacii
|
1
|
Чистка
|
2
|
Синтез/удаление
|
3
|
Таблица 7 - «Медперсонал»
ID_rabotnika
|
Doljnost
|
FIO
|
1
|
Хирург
|
Мазур В.В
|
2
|
Хирург
|
Сахренко И.И.
|
3
|
Хирург-ортопед
|
Демин М.В.
|
4
|
Кардиолог
|
Петренко З.К.
|
5
|
Медсестра
|
Зуратова М.М.
|
6
|
Медсестра
|
Евгеньева С.В.
|
7
|
Медсестра
|
Шевченко С.С.
|
8
|
Санитар
|
Иванченко К.С.
|
9
|
Санитар
|
Коробова Е.Е.
|
10
|
Анестезиолог
|
Деулина А.А.
|
Таблица 8 - Отношение «Должность»
ID
|
Doljnost
|
1
|
Хирург
|
2
|
Хирург-ортопед
|
3
|
Медсестра
|
4
|
Санитар
|
5
|
Кардиолог
|
6
|
Анестезиолог
|
В итоге все обнаруженные аномалии устранены. Реляционная модель,
состоящая из 5 отношений, находящихся в 3 нормальной форме, является адекватной
описанной модели предметной области.
2 ПРАКТИЧЕСКАЯ ЧАСТЬ
2.1 Структура
системы, перечень подсистем
Для данной работы выделим 10 таблиц, которые будут содержать всю
информацию. Свяжем таблицы друг с другом внешними ключами. Схема базы данных
выполнена в программе Erwin
и представлена на рисунке 1.
Рисунок 1 - Схема баз данных
1. Spisok_operacionnih -
таблица, содержащая в себе информацию о пациентах, находящихся в отделении.
2. Operacionii_spisok - таблица,
содержащаяв себе информацию об будущих операциях
3. Naznachenie - таблица, содержащая в себе информацию о назначениях
различных лекарств пациентам
4. Lekarstva - список всех лекарств в отделении
5. Brigada - список с номерами бригад отделения
6. Brigadi rabotnikov - таблица,
содержащая в себе информацию о том, к какой бригаде относится каждый сотрудник
отделения
7. Medpersonal - таблица, содержащая в себе информацию о каждом сотруднике
хирургического отделения
8. Grafik - таблица, содержащая в себе информацию о графике работы
каждой бригады отделения
9. Doljnost - список всех должностей
10. Diagnoz - список диагнозов
Таблица 9- Поля и типы
Таблица
|
Поле
|
Тип
|
Constraint
|
Spisok_operacionnih
|
ID_pacienta
|
Primary key
|
|
FIO
|
Varchar2
|
-
|
|
№ palati
|
Number
|
-
|
|
adress
|
Varchar2
|
-
|
|
Diagnoz
|
Varchar2
|
Foreign Key
|
|
FIO rodstvennika
|
Varchar2
|
-
|
|
Data rojdenia
|
Date
|
-
|
Operacionii
Spisok
|
ID_Operacii
|
Number
|
Primary key
|
|
ID_pacienta
|
Number
|
Foreign Key
|
|
ID_Brigadi
|
Number
|
Foreign Key
|
|
Vid Operacii
|
Varchar2
|
-
|
|
Date
|
Date
|
-
|
Naznachenie
|
ID_Pacienta
|
Number
|
Foreign key
|
|
Id_rabotnika
|
Number
|
Foreign key
|
|
ID_lekarstva
|
Number
|
Foreign key
|
|
Primechanie
|
Varchar2
|
-
|
Diagnoz
|
ID
|
Number
|
Primary key
|
|
Diagnoz
|
Varchar2
|
-
|
Doljnost
|
ID
|
Number
|
Primary key
|
|
Doljnost
|
Varchar2
|
-
|
Lekarstva
|
ID_lekarstva
|
Number
|
Primary key
|
|
Naimenovanie
|
Varchar2
|
-
|
Grafik
|
Number
|
Primary key
|
|
№ brigadi
|
Number
|
Foreign Key
|
|
Data
|
Data
|
-
|
Brigada
|
ID_brigadi
|
Number
|
Primary key
|
|
№ brigadi
|
Number
|
-
|
Brigadi_
rabotnikov
|
ID_brigadi
|
Number
|
Foreign Key
|
|
ID_rabotnika
|
Number
|
Foreign Key
|
Medpersonal
|
ID_rabotnika
|
Number
|
-
|
|
Doljnost
|
Varchar2
|
Foreign Key
|
|
FIO
|
Varchar2
|
-
|
|
Otpusk
|
Date
|
-
|
2.2 Создание
таблиц
Далее с помощью языка PL/SQL создадим и заполним таблицы.
Скрипты таблиц:table spisok_operacionnih
(ID_Pacienta Number Not Null Primary key,Varchar2(400),
№palati Number,Varchar2 (100),Varchar2(200),_rodstvennika
Varchar2(400),_rojdenia Date),FK_SO Foreign Key (Diagnoz) references Diagnoz
(Diagnoz);into spisok_operacionnih (ID_Pacienta, FIO, №palati, address ,
Diagnoz , FIO_rodstvennika, Data_rojdenia) VALUES (‘1’, ‘ Алексеев Алексей
Алексеевич’, ‘1’, ‘ Алексено 3-1’, ‘-‘, ‘Алексеев Алексей
Иванович’, ’10.10.69’);into
spisok_operacionnih (ID_Pacienta, FIO, №palati, address , Diagnoz ,
FIO_rodstvennika, Data_rojdenia) VALUES (‘2’, ‘ Андреев Андрей Алексеевич’, ‘1’, ‘ Петровка
31-12’, ‘-‘, ‘Андреев Алексей
Иванович’, ’15.09.77’);into
spisok_operacionnih (ID_Pacienta, FIO, №palati, address , Diagnoz ,
FIO_rodstvennika, Data_rojdenia) VALUES (‘3’, ‘ Бондарев Виктор Сергеевич, ‘2’, ‘ Сталеваров 41-2, ‘-‘, ‘Бондарев Сергей
Генадьевич’, ’02.01.59’);into
spisok_operacionnih (ID_Pacienta, FIO, №palati, address , Diagnoz ,
FIO_rodstvennika, Data_rojdenia) VALUES (‘4’, ‘ Воложин Виктор Андреевич, ‘2’, ‘ Маркса
116-23, ‘-‘, ‘Воложина Тамара
Сергеевна’, ’05.06.91’);into
spisok_operacionnih (ID_Pacienta, FIO, №palati, address , Diagnoz ,
FIO_rodstvennika, Data_rojdenia) VALUES (‘5’, ‘ Вахитов Рустам Авраамович, ‘2’, ‘ Ворошилова 16-35, ‘-‘, ‘Вахитов Авраам
Маратович ’, ’16.10.47’);into
spisok_operacionnih (ID_Pacienta, FIO, №palati, address , Diagnoz ,
FIO_rodstvennika, Data_rojdenia) VALUES (‘6’, ‘Гайсин Марат Алексеевич, ‘3’, ‘ Ворошилова 88-15, ‘-‘, ‘Гайсина Светлана Евгеньевна ’, 19.12.85’);into
spisok_operacionnih (ID_Pacienta, FIO, №palati, address , Diagnoz ,
FIO_rodstvennika, Data_rojdenia) VALUES (‘7’, ‘ Дубов Сергей Сергеевич, ‘4’, ‘ Вокзальная 112-31, ‘-‘, ‘Дубова Наталья
Леонидовна ’, ’16.05.49’);into
spisok_operacionnih (ID_Pacienta, FIO, №palati, address , Diagnoz ,
FIO_rodstvennika, Data_rojdenia) VALUES (‘8’, ‘ ДемьяновА Анна Ивановна, ‘5’, ‘ Завенягина 1-3, ‘-‘, ‘-‘, ’16.10.47’);into spisok_operacionnih (ID_Pacienta, FIO,
№palati, address , Diagnoz , FIO_rodstvennika, Data_rojdenia) VALUES (‘9’, ‘ Давыдова Анна Сергеевна, ‘5’, ‘ Завенягина 12-32, ‘-‘, ‘-‘, ’17.01.57);into
spisok_operacionnih (ID_Pacienta, FIO, №palati, address , Diagnoz ,
FIO_rodstvennika, Data_rojdenia) VALUES (‘10’, ‘ Петрова Наталья Ивановна, ‘5’, ‘ Мичурина
15-49, ‘-‘,‘-‘, ‘17.02.83’);table Operacionii_Spisok
(ID_Operacii Number Not Null Primary key,_Pacienta
Number,_brigadi Number,_operacii Varchar2 (100),Varchar2(200));FK_OS Foreign
Key (ID_Pacienta) references spisok_operacionnih (ID_Pacienta),FK_OS1 Foreign
Key (ID_brigadi) references Brigada (ID_brigadi);table Naznachenie
(ID_Pacienta Number,_Rabotnika Number,_Lekarstva
Number,Varchar2 (100),Date),FK_Nazn Foreign Key (ID_Pacienta) references
spisok_operacionnih (ID_Pacienta),FK_Nazn1 Foreign Key (ID_rabotnika)
references Medpersonal (ID_rabotnika),FK_Nazn2 Foreign Key (ID_Lekarstva)
references Lekarstva (ID_Lekarstva);table Diagnoz
(ID Number Not Null Primary key,Varchar2(400));into Diagnoz
(ID, Diagnoz) VALUES (’1’,’ Разрыв связки’);into Diagnoz (ID, Diagnoz) VALUES
(’2’,’ Бурсит’);into Diagnoz (ID,
Diagnoz) VALUES (’3’,’ Вывих’);into Diagnoz (ID,
Diagnoz) VALUES (’4’,’ Остеомиелит’);into Diagnoz (ID,
Diagnoz) VALUES (’5’,’ Артрогрипоз’);into Diagnoz (ID,
Diagnoz) VALUES (’6’,’ Миниск’);into Diagnoz (ID,
Diagnoz) VALUES (’7’,’ Анкилоз’);into Diagnoz (ID,
Diagnoz) VALUES (’8’,’ Перелом’);into Diagnoz (ID,
Diagnoz) VALUES (’9’,’ Повреждение АКС’);into Diagnoz (ID, Diagnoz) VALUES (’10’,’ Анкилоз’);table Doljnost
(ID Number Not Null Primary key,Varchar2(400));into Doljnost
(ID, Doljnost) VALUES (’1’,’ Хирург’);into
Doljnost (ID, Doljnost) VALUES (’2’,’ Санитар’);into Doljnost (ID, Doljnost) VALUES (’3’,’ Кардиолог’);into Doljnost (ID, Doljnost)
VALUES (’4’,’ Анестезиолог’);into Doljnost (ID,
Doljnost) VALUES (’5’,’ Медсестра’);into Doljnost (ID,
Doljnost) VALUES (’6’,’ Главный хирург’);table Lekarstva
(ID_Lekarstva Number Not Null Primary key,Varchar2(400));into
Lekarstva (ID_Lekarstva, Naimenovanie) VALUES (’1’,
’ Пеницилин’);into Lekarstva
(ID_Lekarstva, Naimenovanie) VALUES (’2’,
’Абактал’);into Lekarstva
(ID_Lekarstva, Naimenovanie) VALUES (’3’,
‘Метронидазол’);into Lekarstva
(ID_Lekarstva, Naimenovanie) VALUES (’4’,
‘Дравмадол’);into Lekarstva
(ID_Lekarstva, Naimenovanie) VALUES (’5’,
‘Найс’);into Lekarstva
(ID_Lekarstva, Naimenovanie) VALUES (’6’,
‘Анальгин’);into Lekarstva
(ID_Lekarstva, Naimenovanie) VALUES (’7’,
‘Парацетамол’);into Lekarstva
(ID_Lekarstva, Naimenovanie) VALUES (’8’,
‘Цефазолин’);into Lekarstva
(ID_Lekarstva, Naimenovanie) VALUES (’9’,
‘Цефтриаксон’);into Lekarstva (ID_Lekarstva,
Naimenovanie) VALUES (’10’,
‘Аспирин’);table Grafik
(ID Number Not Null Primary key,
№brigadi Number,Date),FK_Graf Foreign Key (№brigadi)
references Brigada (№brigadi);table Brigada
(ID_brigadi Number Not Null Primary key,
№brigadi Number);into Brigada (ID_brigadi, №brigadi) VALUES
(’1’,
’1’);into Brigada (ID_brigadi, №brigadi) VALUES (’2’,
’2’);into Brigada (ID_brigadi, №brigadi) VALUES (’3’,
’3’);into Brigada (ID_brigadi, №brigadi) VALUES (’4’,
’4’);table Brigadi_rabotnikov
(ID_brigadi Number,_rabotnika Number),FK_Br Foreign Key
(ID_brigadi) references Brigada (ID_brigadi),FK_Br2 Foreign Key (ID_rabotnika)
references Medpersonal (ID_rabotnika);table Medpersonal
(ID_rabotnika Number Not Null Primary
key,Varchar2(400),Varchar2(400),Date),FK_Med Foreign Key (Doljnost) references
Doljnost (Doljnost);into Medpersonal (ID_rabotnika, Doljnost, FIO, Otpusk)
VALUES (’1’,’-’, ’Иванов Иван Иванович’, ’Январь’);into Medpersonal (ID_rabotnika, Doljnost, FIO, Otpusk)
VALUES (’2’,’-’, ‘Мазур Леонид
Исаакович’, ‘Февраль’);into Medpersonal (ID_rabotnika,
Doljnost, FIO, Otpusk) VALUES (’3’,’-’, ‘ Петров
Петр Антонович ’, ‘Февраль’);into Medpersonal
(ID_rabotnika, Doljnost, FIO, Otpusk) VALUES (’4’,’-’, ‘ Сергеева Анна Ивановна ’, ‘ Май ’);into Medpersonal (ID_rabotnika, Doljnost, FIO, Otpusk)
VALUES (’5’,’-’, ‘ Шиванчук Евгения
Васильевна ’, ‘ Март ’);into Medpersonal (ID_rabotnika,
Doljnost, FIO, Otpusk) VALUES (’6’,’-’, ‘ Борисова Майя Петровна ’, ‘ Октябрь ’);into Medpersonal
(ID_rabotnika, Doljnost, FIO, Otpusk) VALUES (’7’,’-’, ‘ Страховая Инна Матвеевна ’, ‘ Декабрь ’);into Medpersonal (ID_rabotnika, Doljnost, FIO, Otpusk)
VALUES (’8’,’-’, ‘ Деулин Максим
Алексеевич ’, ‘ Июнь ’);into Medpersonal (ID_rabotnika,
Doljnost, FIO, Otpusk) VALUES (’9’,’-’, ‘ Иванова
Марья Петровна ’, ‘ Август ’);into Medpersonal
(ID_rabotnika, Doljnost, FIO, Otpusk) VALUES (’10’,’-’, ‘ Стаканова Елена Алексеевна ’, ‘ Май ’);
2.3 Триггер
база данные оracle
Ниже описано создание триггера, который выполняет определенную проверку:
в один рабочий день может работать только одна бригада в ортопедическом
отделении. График работы бригад находится в таблице «Grafik». Операции может проводить в конкретный день, только
та бригада, которая работает по графику в этот день. Если в операционном списке
записана дата операции и номер бригады, которая не работает в этот день, то
«всплывает» ошибка.
Скрипт триггера:
Сreate
or replace trigger "AVB_OPERATING_LIST_T1"or update or delete on
"AVB_OPERATING_LIST"each rowTRIGGER AVBinsert on
AVB_OPERATING_LISTNUMBER;NUMBER;NUMBER;NUMBER;MIN(id) into MIN from
AVB_OPERATING_LIST;MAX(id) into MAX from AVB_OPERATING_LIST;count(*) into X
from AVB_GRAFIK where AVB_OPERATING_LIST. DATE_OPERATING=AVB_GRAFIK.DATE and
AVB_OPERATING_LIST.№BRIGADI=AVB_GRAFIK.№Brigadi and
AVB_OPERATING_LIST.ID_OPERATING=i;i in MIN..MAX loop(X=0 then NULLX=1 then
DELETE FROM AVB_OPERATING_LIST where AVB_OPERATING_LIST.id=i;if;if;loop;
COMMIT;.
2.4 Процедура
Ниже описано создание процедуры, которая проверяет и заносит в переменную
количество операций, запланированных на сегодняшний день (дата сегодняшнего дня
берется из SYSDATE(системного времени)).
END;
Комментарий: при создании процедуры была использована вспомогательная
таблица AVB_vspomog, которая не описана в системе и не отображена на
рисунке 1.
Заключение
Благодаря изученному материалу была создана БД, которая имеет
дополнительный триггер и процедуру для выполнения условия задачи. Был изучен
программный продукт Oracle Application Express и был закреплен материал по изученному ранее языку MySql.
Список литературы
1. «Первые шаги - PL/SQL в
Oracle» [<http://www.firststeps.ru/sql/oracle/>]
2. «Oracle
Application Express»[ <http://apex.oracle.com/i/index.html>]
3. «Форум программистов»
[<http://www.sql.ru>]
. Санжей Мишра «Секреты
Oracle SQL». Издательство «Символ-плюс» 2010г.
. Коннор МакДональд «Oracle PL/SQL». Издательство «ДиаСофтЮП» 2009г.
. Скотт Урман
«Программирование на языке PL/SQL. Руководство для программистов». Издательство «Лори»
2009г.