Проектирование базы данных ортопедического отделения больницы
Необходимо создать базу данных, которая включает в себя информацию об ортопедическом отделении больницы. В базу должны входить несколько таблиц: список операционных больных, в котором отображается вся информация о пациентах; список медперсонала, в котором отображается вся информация о сотрудниках ортопедического отделения; список операций, который отображает дату операции, больного, вид операции… Читать ещё >
Проектирование базы данных ортопедического отделения больницы (реферат, курсовая, диплом, контрольная)
КУРСОВАЯ РАБОТА
по дисциплине: «Базы данных»
на тему: «Проектирование базы данных ортопедического отделения больницы»
- ВВЕДЕНИЕ
- 1 НОРМАЛИЗАЦИЯ БД
- 2 ПРАКТИЧЕСКАЯ ЧАСТЬ
- 2.1 Структура системы, перечень подсистем
- 2.2 Создание таблиц
- 2.3 Триггер
- 2.4 Процедура
- ЗАКЛЮЧЕНИЕ
- СПИСОК ЛИТЕРАТУРЫ
ВВЕДЕНИЕ
Необходимо создать базу данных, которая включает в себя информацию об ортопедическом отделении больницы. В базу должны входить несколько таблиц: список операционных больных, в котором отображается вся информация о пациентах; список медперсонала, в котором отображается вся информация о сотрудниках ортопедического отделения; список операций, который отображает дату операции, больного, вид операции и номер работающей бригады; график работы каждой бригады; список назначений пациентам; второстепенные необходимые таблицы.
1 НОРМАЛИЗАЦИЯ БД
1НФ (Первая нормальная форма) Первая нормальная форма (1НФ) — это обычное отношение. Согласно нашему определению отношений, любое отношение автоматически уже находится в 1НФ. Напомним кратко свойства отношений (это и будут свойства 1НФ):
В отношении нет одинаковых кортежей.
Кортежи не упорядочены.
Атрибуты не упорядочены и различаются по наименованию.
Все значения атрибутов атомарны.
В ходе логического моделирования на первом шаге предложено хранить данные в одном отношении, имеющем следующие атрибуты:
ОРТОПЕДИЧЕСКОЕ ОТДЕЛЕНИЕ (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 | |
Иванов И.И. | Миниск | Иванов И.И. | 10.10.10 | Мазур В.В. | хирург | аспирин | |||||
Петров П.П. | Миниск | Петров П. П | 12.10.10 | Мазур В.В. | хирург | Аспирин | |||||
Сидоров В. В | Миниск | Сидоров В.В. | 14.10.10. | Сахренко И.И. | хирург | Аспирин | |||||
Савинов М.Г. | Миниск | Савинова Г. В. | 14.10.10 | Демин М.В. | Хирург-ортопед | Аспирин | |||||
Петрова С.С. | Миниск | Петрова С.С. | 15.10.10 | Демин М.В. | Хирург-ортопед | Аспирин | |||||
2НФ (Вторая Нормальная Форма)
Отношение находится во второй нормальной форме (2НФ) тогда и только тогда, когда отношение находится в 1НФ и нет неключевых атрибутов, зависящих от части сложного ключа. (Неключевой атрибут — это атрибут, не входящий в состав никакого потенциального ключа).
Замечание. Если потенциальный ключ отношения является простым, то отношение автоматически находится в 2НФ.
В выше представленной таблице были добавлены второстепенные ключи (foreign key), а так же созданы соответствующие дополнительные таблицы, перечисляющие относящиеся к нужным полям данные:
Таблица 2 — Ортопедическое отделение (2НФ)
ID_Pacienta | Fio | Diagnoz | №palati | FIO rodstv | |
Иванов И.И. | Миниск | Иванов | |||
Петров П.П. | Миниск | Петров | |||
Сидоров В. В | Разрыв АКС | Сидоров | |||
Савинов М.Г. | Разрыв АКС | Савинова | |||
Петрова С.С. | Разрыв АКС | Петрова | |||
Таблица 3 — Ортопедическое отделения (2)
ID_Operacii (PK) | ID_Pacienta | ID_brigadi | Vid operacii | Data operacii | |
Удаление жидкости | 10.10.12 | ||||
Удаление жидкости | 11.10.12 | ||||
Удаление пластины | 11.10.12 | ||||
Удаление пластины | 12.10.12 | ||||
Синтез пластины | 15.10.12 | ||||
Таблица 4 — Отношение «диагнозы»
ID | Диагноз | |
Разрыв связки | ||
Вывих | ||
Остеомиелит | ||
Артрогрипоз | ||
Миниск | ||
Анкилоз | ||
Перелом | ||
Повреждение АКС | ||
Анкилоз | ||
Бурсит | ||
3НФ (Третья Нормальная Форма)
Атрибуты называются взаимно независимыми, если ни один из них не является функционально зависимым от другого.
Отношение находится в третьей нормальной форме (3НФ) тогда и только тогда, когда отношение находится в 2НФ и все не ключевые атрибуты взаимно независимы.
Для достижения 3НФ необходимо избавиться от полей, не имеющих второстепенную связь. Следовательно, необходимо создать дополнительные таблицы, отображающие список с нужной информацией Таблица 5 — Отношение «Бригады»
ID_brigadi | №brigadi | |
Таблица 6 — Отношение «Вид операции»
ID | Vid operacii | |
Чистка | ||
Синтез/удаление | ||
Фиксирование | ||
Таблица 7 — «Медперсонал»
ID_rabotnika | Doljnost | FIO | |
Хирург | Мазур В. В | ||
Хирург | Сахренко И.И. | ||
Хирург-ортопед | Демин М.В. | ||
Кардиолог | Петренко З.К. | ||
Медсестра | Зуратова М.М. | ||
Медсестра | Евгеньева С.В. | ||
Медсестра | Шевченко С.С. | ||
Санитар | Иванченко К.С. | ||
Санитар | Коробова Е.Е. | ||
Анестезиолог | Деулина А.А. | ||
Таблица 8 — Отношение «Должность»
ID | Doljnost | |
Хирург | ||
Хирург-ортопед | ||
Медсестра | ||
Санитар | ||
Кардиолог | ||
Анестезиолог | ||
В итоге все обнаруженные аномалии устранены. Реляционная модель, состоящая из 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 | Number | 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 | ID | 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 создадим и заполним таблицы.
Скрипты таблиц:
Create table spisok_operacionnih
(ID_Pacienta Number Not Null Primary key,
FIO Varchar2(400),
№palati Number,
address Varchar2 (100),
Diagnoz Varchar2(200),
FIO_rodstvennika Varchar2(400),
Data_rojdenia Date),
Constraint FK_SO Foreign Key (Diagnoz) references Diagnoz (Diagnoz);
Insert into spisok_operacionnih (ID_Pacienta, FIO, №palati, address, Diagnoz, FIO_rodstvennika, Data_rojdenia) VALUES (`1', ` Алексеев Алексей Алексеевич', `1', ` Алексено 3−1', `-`, `Алексеев Алексей Иванович', '10.10.69');
Insert into spisok_operacionnih (ID_Pacienta, FIO, №palati, address, Diagnoz, FIO_rodstvennika, Data_rojdenia) VALUES (`2', ` Андреев Андрей Алексеевич', `1', ` Петровка 31−12', `-`, `Андреев Алексей Иванович', '15.09.77');
Insert into spisok_operacionnih (ID_Pacienta, FIO, №palati, address, Diagnoz, FIO_rodstvennika, Data_rojdenia) VALUES (`3', ` Бондарев Виктор Сергеевич, `2', ` Сталеваров 41−2, `-`, `Бондарев Сергей Генадьевич', '02.01.59');
Insert into spisok_operacionnih (ID_Pacienta, FIO, №palati, address, Diagnoz, FIO_rodstvennika, Data_rojdenia) VALUES (`4', ` Воложин Виктор Андреевич, `2', ` Маркса 116−23, `-`, `Воложина Тамара Сергеевна', '05.06.91');
Insert into spisok_operacionnih (ID_Pacienta, FIO, №palati, address, Diagnoz, FIO_rodstvennika, Data_rojdenia) VALUES (`5', ` Вахитов Рустам Авраамович, `2', ` Ворошилова 16−35, `-`, `Вахитов Авраам Маратович ', '16.10.47');
Insert into spisok_operacionnih (ID_Pacienta, FIO, №palati, address, Diagnoz, FIO_rodstvennika, Data_rojdenia) VALUES (`6', `Гайсин Марат Алексеевич, `3', ` Ворошилова 88−15, `-`, `Гайсина Светлана Евгеньевна ', 19.12.85');
Insert into spisok_operacionnih (ID_Pacienta, FIO, №palati, address, Diagnoz, FIO_rodstvennika, Data_rojdenia) VALUES (`7', ` Дубов Сергей Сергеевич, `4', ` Вокзальная 112−31, `-`, `Дубова Наталья Леонидовна ', '16.05.49');
Insert into spisok_operacionnih (ID_Pacienta, FIO, №palati, address, Diagnoz, FIO_rodstvennika, Data_rojdenia) VALUES (`8', ` ДемьяновА Анна Ивановна, `5', ` Завенягина 1−3, `-`, `-`, '16.10.47');
Insert into spisok_operacionnih (ID_Pacienta, FIO, №palati, address, Diagnoz, FIO_rodstvennika, Data_rojdenia) VALUES (`9', ` Давыдова Анна Сергеевна, `5', ` Завенягина 12−32, `-`, `-`, '17.01.57);
Insert into spisok_operacionnih (ID_Pacienta, FIO, №palati, address, Diagnoz, FIO_rodstvennika, Data_rojdenia) VALUES (`10', ` Петрова Наталья Ивановна, `5', ` Мичурина 15−49, `-`,`-`, `17.02.83');
Create table Operacionii_Spisok
(ID_Operacii Number Not Null Primary key,
ID_Pacienta Number,
ID_brigadi Number,
Vid_operacii Varchar2 (100),
Date Varchar2(200));
Constraint FK_OS Foreign Key (ID_Pacienta) references spisok_operacionnih (ID_Pacienta),
Constraint FK_OS1 Foreign Key (ID_brigadi) references Brigada (ID_brigadi);
Create table Naznachenie
(ID_Pacienta Number,
ID_Rabotnika Number,
ID_Lekarstva Number,
Primechanie Varchar2 (100),
Data Date),
Constraint FK_Nazn Foreign Key (ID_Pacienta) references spisok_operacionnih (ID_Pacienta),
Constraint FK_Nazn1 Foreign Key (ID_rabotnika) references Medpersonal (ID_rabotnika),
Constraint FK_Nazn2 Foreign Key (ID_Lekarstva) references Lekarstva (ID_Lekarstva);
Create table Diagnoz
(ID Number Not Null Primary key,
Diagnoz Varchar2(400));
Insert into Diagnoz (ID, Diagnoz) VALUES ('1',' Разрыв связки');
Insert into Diagnoz (ID, Diagnoz) VALUES ('2',' Бурсит');
Insert into Diagnoz (ID, Diagnoz) VALUES ('3',' Вывих');
Insert into Diagnoz (ID, Diagnoz) VALUES ('4',' Остеомиелит');
Insert into Diagnoz (ID, Diagnoz) VALUES ('5',' Артрогрипоз');
Insert into Diagnoz (ID, Diagnoz) VALUES ('6',' Миниск');
Insert into Diagnoz (ID, Diagnoz) VALUES ('7',' Анкилоз');
Insert into Diagnoz (ID, Diagnoz) VALUES ('8',' Перелом');
Insert into Diagnoz (ID, Diagnoz) VALUES ('9',' Повреждение АКС');
Insert into Diagnoz (ID, Diagnoz) VALUES ('10',' Анкилоз');
Create table Doljnost
(ID Number Not Null Primary key,
Doljnost Varchar2(400));
Insert into Doljnost (ID, Doljnost) VALUES ('1',' Хирург');
Insert into Doljnost (ID, Doljnost) VALUES ('2',' Санитар');
Insert into Doljnost (ID, Doljnost) VALUES ('3',' Кардиолог');
Insert into Doljnost (ID, Doljnost) VALUES ('4',' Анестезиолог');
Insert into Doljnost (ID, Doljnost) VALUES ('5',' Медсестра');
Insert into Doljnost (ID, Doljnost) VALUES ('6',' Главный хирург');
Create table Lekarstva
(ID_Lekarstva Number Not Null Primary key,
Naimenovanie Varchar2(400));
Insert into Lekarstva (ID_Lekarstva, Naimenovanie) VALUES ('1',
' Пеницилин');
Insert into Lekarstva (ID_Lekarstva, Naimenovanie) VALUES ('2',
'Абактал');
Insert into Lekarstva (ID_Lekarstva, Naimenovanie) VALUES ('3',
`Метронидазол');
Insert into Lekarstva (ID_Lekarstva, Naimenovanie) VALUES ('4',
`Дравмадол');
Insert into Lekarstva (ID_Lekarstva, Naimenovanie) VALUES ('5',
`Найс');
Insert into Lekarstva (ID_Lekarstva, Naimenovanie) VALUES ('6',
`Анальгин');
Insert into Lekarstva (ID_Lekarstva, Naimenovanie) VALUES ('7',
`Парацетамол');
Insert into Lekarstva (ID_Lekarstva, Naimenovanie) VALUES ('8',
`Цефазолин');
Insert into Lekarstva (ID_Lekarstva, Naimenovanie) VALUES ('9',
`Цефтриаксон');
Insert into Lekarstva (ID_Lekarstva, Naimenovanie) VALUES ('10',
`Аспирин');
Create table Grafik
(ID Number Not Null Primary key,
№brigadi Number,
Data Date),
Constraint FK_Graf Foreign Key (№brigadi) references Brigada (№brigadi);
Create table Brigada
(ID_brigadi Number Not Null Primary key,
№brigadi Number);
Insert into Brigada (ID_brigadi, №brigadi) VALUES ('1',
'1');
Insert into Brigada (ID_brigadi, №brigadi) VALUES ('2',
'2');
Insert into Brigada (ID_brigadi, №brigadi) VALUES ('3',
'3');
Insert into Brigada (ID_brigadi, №brigadi) VALUES ('4',
'4');
Create table Brigadi_rabotnikov
(ID_brigadi Number,
ID_rabotnika Number),
Constraint FK_Br Foreign Key (ID_brigadi) references Brigada (ID_brigadi),
Constraint FK_Br2 Foreign Key (ID_rabotnika) references Medpersonal (ID_rabotnika);
Create table Medpersonal
(ID_rabotnika Number Not Null Primary key,
Doljnost Varchar2(400),
FIO Varchar2(400),
Otpusk Date),
Constraint FK_Med Foreign Key (Doljnost) references Doljnost (Doljnost);
Insert into Medpersonal (ID_rabotnika, Doljnost, FIO, Otpusk) VALUES ('1','-', 'Иванов Иван Иванович', 'Январь');
Insert into Medpersonal (ID_rabotnika, Doljnost, FIO, Otpusk) VALUES ('2','-', `Мазур Леонид Исаакович', `Февраль');
Insert into Medpersonal (ID_rabotnika, Doljnost, FIO, Otpusk) VALUES ('3','-', ` Петров Петр Антонович ', `Февраль');
Insert into Medpersonal (ID_rabotnika, Doljnost, FIO, Otpusk) VALUES ('4','-', ` Сергеева Анна Ивановна ', ` Май ');
Insert into Medpersonal (ID_rabotnika, Doljnost, FIO, Otpusk) VALUES ('5','-', ` Шиванчук Евгения Васильевна ', ` Март ');
Insert into Medpersonal (ID_rabotnika, Doljnost, FIO, Otpusk) VALUES ('6','-', ` Борисова Майя Петровна ', ` Октябрь ');
Insert into Medpersonal (ID_rabotnika, Doljnost, FIO, Otpusk) VALUES ('7','-', ` Страховая Инна Матвеевна ', ` Декабрь ');
Insert into Medpersonal (ID_rabotnika, Doljnost, FIO, Otpusk) VALUES ('8','-', ` Деулин Максим Алексеевич ', ` Июнь ');
Insert into Medpersonal (ID_rabotnika, Doljnost, FIO, Otpusk) VALUES ('9','-', ` Иванова Марья Петровна ', ` Август ');
Insert into Medpersonal (ID_rabotnika, Doljnost, FIO, Otpusk) VALUES ('10','-', ` Стаканова Елена Алексеевна ', ` Май ');
2.3 Триггер
база данные оracle
Ниже описано создание триггера, который выполняет определенную проверку: в один рабочий день может работать только одна бригада в ортопедическом отделении. График работы бригад находится в таблице «Grafik». Операции может проводить в конкретный день, только та бригада, которая работает по графику в этот день. Если в операционном списке записана дата операции и номер бригады, которая не работает в этот день, то «всплывает» ошибка.
Скрипт триггера:
Сreate or replace trigger «AVB_OPERATING_LIST_T1»
BEFORE
insert or update or delete on «AVB_OPERATING_LIST»
for each row
begin
CREATE TRIGGER AVB
AFTER insert on AVB_OPERATING_LIST
declare
MIN NUMBER;
MAX NUMBER;
i NUMBER;
X NUMBER;
BEGIN
SELECT MIN (id) into MIN from AVB_OPERATING_LIST;
SELECT MAX (id) into MAX from AVB_OPERATING_LIST;
SELECT 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;
for i in MIN. MAX loop
if (X=0 then NULL
else
if X=1 then DELETE FROM AVB_OPERATING_LIST where AVB_OPERATING_LIST.id=i;
end if;
end if;
end loop;
COMMIT;
END.
2.4 Процедура
Ниже описано создание процедуры, которая проверяет и заносит в переменную количество операций, запланированных на сегодняшний день (дата сегодняшнего дня берется из SYSDATE (системного времени)).
Скрипт процедуры:
CREATE PROCEDURE XXX (DATE_OPERATING date) as
declare
X NUMBER;
v_dt DATE :=sysdate;
v_tx DATE;
begin
v_tx:=TRUNC (v_dt,'dd/mm/yy');
select count (*) into X from AVB_OPERATING_LIST where AVB_OPERATING_LIST.DATE_OPERATING=v_tx;
insert into AVB_vspomog (vspomog) values (X);
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]
4. Санжей Мишра «Секреты Oracle SQL». Издательство «Символ-плюс» 2010 г.
5. Коннор МакДональд «Oracle PL/SQL». Издательство «ДиаСофтЮП» 2009 г.
6. Скотт Урман «Программирование на языке PL/SQL. Руководство для программистов». Издательство «Лори» 2009 г.