Информационная система для предметной области “Поликлиника”

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Заполнение таблицы «Вид приема»

 

INSERT Vid_priema(Number, Name_priema) VALUES (1, 'По записи')

INSERT Vid_priema(Number, Name_priema) VALUES (2, 'По направлению')

 

Заполнение таблицы «Специалист»

 

INSERT Specialist VALUES (1, 'Хирург')

INSERT Specialist VALUES (2, 'Окулист')

INSERT Specialist VALUES (3, 'Невропатолог')

INSERT Specialist VALUES (4, 'Отоларинголог')

INSERT Specialist VALUES (5, 'Терапевт')

INSERT Specialist VALUES (6, 'Дерматолог')

INSERT Specialist VALUES (7, 'Кардиолог')

INSERT Specialist VALUES (8, 'Нефролог')

INSERT Specialist VALUES (9, 'Эндокринолог')

INSERT Specialist VALUES (10, 'Стоматолог')

INSERT Specialist VALUES (11, 'Медсестра')

 

Заполнение таблицы «Должность»

 

INSERT Dolgnost VALUES (1, 'Заведующий',16500)

INSERT Dolgnost VALUES (2, 'Врач - специалист',13000)

INSERT Dolgnost VALUES (3, 'Медсестра',6000)

 

Заполнение таблицы «Корпус»

 

INSERT Korpus (Number_korpusa,Adress)VALUES(1, 'Красная 45')

INSERT Korpus (Number_korpusa,Adress)VALUES(2, 'Володарского 6')

INSERT Korpus (Number_korpusa,Adress)VALUES(3, 'Ладожская 12')

 

Заполнение таблицы «Пациент»

 

INSERT Pacient VALUES(123456, 'Копылова Мария Сергеевна', 5604123456, 'Маркина 12 - 27', '06.07.1988')

INSERT Pacient VALUES(456789, 'Долотов Роман Александрович', 5605174004, 'Краснова 45 - 44', '02.02.1988')

INSERT Pacient VALUES(778900, 'Трушин Иван Александрович', 5600349065, 'Богданова 39 - 100', '20.05.1975')

INSERT Pacient VALUES(897321, 'Иванова Ольга Валерьевна', 5602674809 ,'Коммунистическая 43 - 35', '18.09.1970')

INSERT Pacient VALUES(269056, 'Малютин Игорь Сергеевич',  5605905678, 'Лядова 112 - 67', '27.11.1963')

INSERT Pacient VALUES(652945, 'Жарикова Влада Олеговна',  5607348601, 'Славы 3 - 5','19.04.1982')

INSERT Pacient VALUES(660087, 'Кожевников Кирилл Алексеевич',  5608002576, 'Калинина 110 - 9', '02.03.1949')

INSERT Pacient VALUES(190276, 'Ульянова Вера Анатольевна', 5601778543, 'Кирова 48 - 7',  '23.09.1974')

INSERT Pacient VALUES(996411, 'Дарвина Елизавета Ивановна', 5603889904, 'Лобачевского 6 - 54', '01.08.1980')

INSERT Pacient VALUES(450967, 'Закревский Максим Андреевич', 5604530912, 'Одесская 18 - 90', '30.06.1952')

INSERT Pacient VALUES(269056, 'Малютин Игорь Сергеевич', 5605905678, 'Лядова 112 - 67', '27.11.1963')

 

 

 

Заполнение таблицы «Медицинский персонал»

 

INSERT Med_personal VALUES(1, 563858, 'Панов Александр Алексеевич', 'Краснова 39 - 45', '23.10.1955', 16500, 1, 1)

INSERT Med_personal VALUES(2, 223456, 'Арефьев Николай Иванович', 'Проспект Строителей 15 25', '15.05.1965', 13500, 2, 1)

INSERT Med_personal VALUES(3, 986542, 'Степанова Оксана Сергеевна', 'Глазунова 5 - 190', '05.12.1965', 12000, 2, 2)

INSERT Med_personal VALUES(4, 120987, 'Рязанова Вера Петровна', 'Мира 68 - 36',  '04.09.1960', 13800, 2, 2)

INSERT Med_personal VALUES(5, 554432, 'Петров Петр Павлович', 'Ленинградская 19 - 105', '23.10.1960', 12400, 2, 3)

INSERT Med_personal VALUES(6, 986523, 'Пичугин Алексей Николаевич', 'Ватутина 10 - 100', '30.01.1974', 13500, 2, 4)

INSERT Med_personal VALUES(7, 239801, 'Панов Александр Алексеевич', 'Краснова 39 - 45', '23.10.1955', 13000, 2, 4)

INSERT Med_personal VALUES(8, 457890, 'Рыбина Наталья Валерьевна', 'Циалковского 39 - 31', '23.12.1964', 12000, 2, 5)

INSERT Med_personal VALUES(9, 109865, 'Букреев Олег Иванович', 'Тамбовская 18 - 35', '26.02.1969', 13300, 2, 5)

INSERT Med_personal VALUES(10, 181122, 'Сидорова Екатерина Сергеевна', 'Титова 139 - 145', '13.12.1950', 13400, 2, 6)

INSERT Med_personal VALUES(11, 785522, 'Орлова Евгения Павловна' , 'Володарского 26 - 11',  '20.07.19549', 14000, 2, 7)

INSERT Med_personal VALUES(12, 097865, 'Алексеев Роман Дмитриевич', 'Пушкина 18 - 99', '18.08.1972', 14100, 2, 8)

INSERT Med_personal VALUES(13, 348700, 'Мишутин Илья Геннадьевич', 'Суворова 29 - 199',  '24.04.1963', 12900, 2, 9)

INSERT Med_personal VALUES(14, 572366, 'Анашкина Анастасия Кирилловна', 'Красная 23 - 18',  '21.05.1970', 13000, 2, 10)

INSERT Med_personal VALUES(15, 234567, 'Краснова Ольга Владимировна', 'Лермонтова 23 - 56',  '15.12.1984', 6200, 3, 11)

INSERT Med_personal VALUES(16, 674509, 'Захарова Татьяна Юрьевна', 'Чехова 20 - 13', '07.07.1980', 6500, 3, 11)

INSERT Med_personal VALUES(17, 129576, 'Куликова Надежда Ивановна', 'Проспект Строителей 126 - 95', '31.08.1982', 5900, 3, 11)

INSERT Med_personal VALUES(18, 785634, 'Юсупова Светлана Витальевна', 'Суворова 139 - 75', '08.03.1985', 6000, 3, 11)

INSERT Med_personal VALUES(19, 981234, 'Аркадева Александра Владимировна', 'Калинина 139 - 40', '11.10.1985', 5900, 3, 11)

 

Заполнение таблицы «Запись»

 

INSERT Zapis (Number_zapisi,Vremja_priema,Number_specialista,Number_polisa,Number_personala,Number,Number_korpusa)VALUES (1, '18.05.08 14:20', 3, 123456, 4, 1, 1)

INSERT Zapis (Number_zapisi,Vremja_priema,Number_specialista,Number_polisa,Number_personala,Number,Number_korpusa)VALUES (2, '01.06.08 09:20', 10, 450967, 14, 1, 2)

INSERT Zapis (Number_zapisi,Vremja_priema,Number_specialista,Number_polisa,Number_personala,Number,Number_korpusa)VALUES (3, '15.05.08 12:00', 1, 456789, 10, 2, 1)

INSERT Zapis (Number_zapisi,Vremja_priema,Number_specialista,Number_polisa,Number_personala,Number,Number_korpusa)VALUES (4, '15.05.08 13:30', 1, 190276, 2, 1, 1)

INSERT Zapis (Number_zapisi,Vremja_priema,Number_specialista,Number_polisa,Number_personala,Number,Number_korpusa)VALUES (5, '30.04.08 10:30', 5, 652945, 13, 1, 1)

INSERT Zapis (Number_zapisi,Vremja_priema,Number_specialista,Number_polisa,Number_personala,Number,Number_korpusa)VALUES (6, '15.07.08 16:25', 9, 996411, 6, 2, 3)

INSERT Zapis (Number_zapisi,Vremja_priema,Number_specialista,Number_polisa,Number_personala,Number,Number_korpusa)VALUES (7, '10.05.08 11:40', 6, 897321, 9, 2, 2)

INSERT Zapis (Number_zapisi,Vremja_priema,Number_specialista,Number_polisa,Number_personala,Number,Number_korpusa)VALUES (8, '18.04.08 15:20', 2, 778900, 7, 2, 2)

INSERT Zapis (Number_zapisi,Vremja_priema,Number_specialista,Number_polisa,Number_personala,Number,Number_korpusa)VALUES (9, '08.06.08 11:00', 4, 190276, 11, 1, 1)

INSERT Zapis (Number_zapisi,Vremja_priema,Number_specialista,Number_polisa,Number_personala,Number,Number_korpusa)VALUES (10, '25.04.08 08:20', 4, 660087, 6, 1, 3)

INSERT Zapis (Number_zapisi,Vremja_priema,Number_specialista,Number_polisa,Number_personala,Number,Number_korpusa)VALUES (11, '01.07.08 18:00', 8, 269056, 8, 1,3)

 

 

 

 

 

 

 

 

 

 

 

 

Демонстрация заполненных таблиц:

Таблица «Должность»

 

Таблица «Корпус»

 

Таблица «Медицинский персонал»

 

Таблица «Пациент»

Таблица «Специалист»

Таблица «Вид приема»

 

 

 

Таблица «Запись»

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

ПРОГРАММНАЯ РЕАЛИЗАЦИЯ ТРИГГЕРОВ

Приложение Г

(обязательное)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1. Триггер, осуществляющий проверку зарплаты:

 

create trigger Proverka_ZP

on Med_personal for insert

as

if @@rowcount=1

begin

declare @ZP int, @Dolg int, @d int

select @ZP=ZP, @Dolg=Number_dolgnosti from inserted

select @d=YEAR(Right(Data_rogdenia,4)) from inserted

if @ZP<(select Sred_ZP from Dolgnost where Number_dolgnosti=@Dolg)

begin

print('Зарплата  сотрудника не может быть ниже  зарплаты, предусмотренной тарифной  сеткой'+char(13)+'Сотрудник не принят')

rollback

end

else  if ((YEAR(GETDATE())-@d)>18) OR ((YEAR(GETDATE())-@d)<60)

begin

print('Новый  сотрудник принят на работу.'+CHAR(13)+'Если  все будет хорошо, он проработает  '+CAST((@d+60-YEAR(GETDATE())) as varchar(8))+' лет')

end

end

 

Выполнение:

 

а) добавление сотрудника, зарплата которого ниже средней:

INSERT Med_personal VALUES(19, 981234, 'Аркадева Александра Владимировна', 'Калинина 139 - 40', '11.10.1985', 2900, 3, 11)

 

 

б) добавление сотрудника, зарплата которого выше средней. При этом выводится количество лет, оставшихся до пенсии:

 

INSERT Med_personal VALUES(19, 981234, 'Аркадева Александра Владимировна', 'Калинина 139 - 40', '11.10.1985', 6900, 3, 11)

 

2) Триггер,  осуществляющий проверку наличия  записи к какому-либо специалисту  на определенное время:

 

create trigger Priem

on Zapis for insert

as

if @@rowcount=1

begin

declare @V varchar(18), @Sp int

select @V=Vremja_priema, @Sp=Number_personala from inserted

if @Sp in (select Number_specialista from Zapis where Vremja_priema=@V)

begin

print('У  данного специалиста уже есть  прием в это время!')

rollback

end

end

 

Выполнение: введем время, на которое уже есть запись:

 

INSERT Zapis VALUES (12, '01.07.08 18:00', 8, 269056, 8, 1,3)

 

 

3) Триггер,  срабатывающий при добавлении  сотрудника в базу:

 

create trigger Proverka_vozrasta_sotrudnika

on Med_personal for insert

as

if @@rowcount=1

begin

declare @d int

select @d=YEAR(Right(Data_rogdenia,4)) from inserted

if ((YEAR(GETDATE())-@d)<18) OR ((YEAR(GETDATE())-@d)>60)

begin

print('К  сожалению, возраст данного сотрудника  не позволяет ему работать  в нашей поликлинике. Извините.')

rollback

end

end

 

Выполнение:

 

  добавление  сотрудника, который младше 18 лет: 

INSERT Med_personal VALUES(19, 981234, 'Аркадева Александра Владимировна', 'Калинина 139 - 40', '11.10.1995', 6900, 3, 11)

 

 

4) Удаление  сотрудника из базы:

 

Выполнение:

 

а)  после  добавления сотрудника:

 

 

б) после удаления сотрудника:

 

delete from MEd_personal where Number_personala=19

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

ПРОГРАММНАЯ РЕАЛИЗАЦИЯ ЗАПРОСОВ

Приложение Д

(обязательное)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1) Вывести весь мед. персонал

 

Select FIO_personala as ФИО_персонала, Dom_Adress as дом_адрес, Data_rogdenia as дата_рождения From Med_personal

 

 

2) Вывести всех пациентов, старше 30 лет

Select FIO as ФИО, Dom_adress as дом_адрес, God_rogdenia as дата_рождения from Pacient

where (God_rogdenia like '__.__.__[1-7]_')

 

 

3) Вывести  всех врачей – окулистов, старше 40 лет и работающих в поликлиники  № 2

 

Select FIO_personala as ФИО_персонала, Dom_Adress as дом_адрес, Data_rogdenia as дата_рождения,  Number_korpusa as номер_поликлиники,

Specialist.Name_specialista as название_специалиста

from Med_personal, Specialist, Korpus

where (Data_rogdenia like '__.__.__[1-6][0-8]') and (Med_personal.Number_specialista = 2)and (Specialist.Number_specialista = 2)

and (Number_korpusa = 2)

 

4) Вывести  пациентов, которые записаны на 15.05.2008 на прием к хирургу

 

Select FIO as ФИО, Vremja_priema as время_приема, Name_specialista as название_специалиста ,Zapis.number_polisa as номер_полиса

from Pacient, Specialist, Zapis

where (Zapis.Vremja_priema like '15.05.08%')and(Zapis.Number_specialista=1)and (Specialist.Number_specialista=1)

and (Zapis.number_polisa=Pacient.number_polisa)

 

 

5) Вывести  мед.персонал с соответствующими  должностями

 

Select FIO_personala as ФИО_персонала, Name_specialista as название_специалиста from Med_personal, Specialist

where Med_personal.Number_specialista = Specialist.Number_specialista

 

 

 

 

 

6) Вывести  мед.персонал с зарплатой выше  средней

 

Select FIO_personala as ФИО_персонала, Name_specialista as название_специалиста,zp as зарплата

from Med_personal, Specialist,dolgnost

where Med_personal.Number_specialista = Specialist.Number_specialista

and Med_personal.number_dolgnosti=dolgnost.number_dolgnosti and Med_personal.zp>=dolgnost.sred_zp

 

 

7) Вывести  пациентов, которые записаны на  прием к врачу Петрову П.П.

 

Select FIO as ФИО_пациента,Vremja_priema as время_приема,Zapis.number_polisa as номер_полиса,

Name_specialista as название_специалиста, FIO_personala as ФИО_персонала

from Pacient,Zapis,Med_personal, specialist

where (Med_personal.FIO_personala like 'Петров%')

and (Zapis.number_polisa=Pacient.number_polisa)and (Zapis.number_specialista=3)

and (specialist.number_specialista=3)

 

 

8) Вывести  количество мед. персонала, которые  получают зарплату больше выше  средней и среднее значение  зарплаты

 

Select count (*) as количество

from Med_personal, Specialist,dolgnost

where Med_personal.Number_specialista = Specialist.Number_specialista

and Med_personal.number_dolgnosti=dolgnost.number_dolgnosti and Med_personal.zp>=dolgnost.sred_zp

 

 

Select FIO_personala as ФИО_персонала, Name_specialista as название_специалиста,

zp as зарплата,sred_zp as средняя_зарплата

from Med_personal, Specialist,dolgnost

where Med_personal.Number_specialista = Specialist.Number_specialista

and Med_personal.number_dolgnosti=dolgnost.number_dolgnosti and Med_personal.zp>=dolgnost.sred_zp

 

 

9) Вывести  все записи приема по направлению

 

Select FIO as ФИО_пациента,Vremja_priema as время_приема,Zapis.number_polisa as номер_полиса,

Name_specialista as название_специалиста,FIO_personala as ФИО_персонала,name_priema as название_приема

from Pacient,Zapis,Med_personal, specialist,vid_priema

where (Zapis.number=2)and (Zapis.number_polisa=Pacient.number_polisa)and (vid_priema.number=2)

and (Zapis.Number_specialista=specialist.Number_specialista)and

(Zapis.number_personala=Med_personal.number_personala)

 

 

10) По  фамилии врача определить фамилии  его пациентов

 

Select FIO_personala as ФИО_персонала,Name_specialista as название_специалиста,

FIO as ФИО_пациента,Zapis.number_polisa as номер_полиса

from Pacient,Zapis,Med_personal,specialist

where (Zapis.number_polisa=Pacient.number_polisa)and

(Zapis.number_specialista=specialist.number_specialista)

and (Zapis.number_personala=Med_personal.number_personala)

group by FIO_personala,Name_specialista,FIO,Zapis.number_polisa

 

 

ПРОГРАММНАЯ РЕАЛИЗАЦИЯ ПРЕДСТАВЛЕНИЙ  И ЗАПРОСОВ К НИМ

Приложение Е

(обязательное)