Лабораторная работа 1, 2, 3: Госпиталь (инфологическая и логическая модель, простые и сложные запросы, триггер, процедура)

Вариант 5. Госпиталь.
Итоговый отчет по всем лабораторным работам.
Принята преподавателем на максимальный балл.
Условие:
Госпиталь состоит из отделений. Известно сколько палат в каждом отделении, этаж, где оно расположено, фамилия заведующего.
В каждом отделении работают врачи. При этом каждый врач по основной специальности работает только в одном отделении.
Каждому врачу присвоен уникальный номер, известны его фамилия, паспортные данные, адрес, год рождения, специализация, дата поступления на работу в госпиталь. При увольнении врача проставляется также дата его увольнения.
В каждом отделении находится несколько палат. Каждая палата имеет номер, тип (обычная, реанимационная, VIP) и характеризуется количеством мест.
При поступлении пациента в госпиталь его направляют в конкретное отделение и определенную палату. О каждом пациенте известны его паспортные данные, адрес, дата рождения. Каждому пациенту при поступлении назначается один лечащий врач из числа врачей отделения. Но каждый лечащий врач может вести нескольких пациентов.
На каждого больного ведется история болезни. История болезни открывается при поступлении пациента в госпиталь и закрывается при выписке пациента, т.е. известны даты поступления и выписки пациента. Если пациент поступает в госпиталь повторно, то на него открывается новая история болезни.
Каждая история болезни имеет уникальный номер, в нее при поступлении заносится основный диагноз, дата поступления пациента и в дальнейшем дата выписки из госпиталя.
Для консультаций пациентов в случае необходимости могут приглашаться врачи и других отделений.
Каждый осмотр пациента лечащим врачом или приглашенным консультантом заканчивается записью в истории болезни. Каждая запись содержит сведения о враче, сделавшим запись, дату, основные наблюдения и назначения, сделанные в результате осмотра.
Простые запросы.
Показать все сведенья о врачах, принятых на работу в апреле 2013 года.
Показать все сведенья о пациентах, выписавшихся из госпиталя в за последние 20 дней.
Показать, сколькими палатами каждой категории располагает каждое отделение госпиталя.
Показать средний возраст пациентов каждого отделения.
Показать, сколько пациентов из Москвы находится в настоящий момент в госпитале.
Показать, сколько записей в истории болезней сделал каждый приглашенный консультант в 2020 году.
Сложные запросы.
Создать отчет о лечащих врачах отделения ХХХ за 2020 год по форме:
Уникальный номер врача, Фамилия врача, количество пациентов, у которых врач был лечащим.
Составьте отчет об отделениях госпиталя по форме: Номер отделения, название отделения, Ф.И.О. заведующего, количество мест в отделении.
Покажите все сведенья о действующем враче, проработавшем в госпитале дольше всех.
Показать все сведенья о врачах, которые ни разу не назначались лечащими врачами у пациентов (с помощью левостороннего соединения)
Показать фамилии врачей, которые не назначались лечащими врачами пациентов в марте 2020 года.
Показать все сведенья о враче, у который был назначен лечащим у наибольшего числа пациентов (с использованием view).
Триггеры и процедуры.
Разработайте процедуру, которая сформирует и сохранит в отдельной таблице БД отчет по форме:
Год
Месяц
Nº врача
Фамилия врача
количество пациентов врача за этот период.
В отчет выбираются все врачи, которые были назначены лечащими для пациентов, поступивших в госпиталь в указанный период.
Отчет составляется для периода, год и месяц которого передаются в процедуру в качестве входных параметров.
Разработайте триггер, который будет срабатывать при добавлении новой записи о пациенте. В триггере реализовать увеличение количества пациентов для соответствующего врача за текущий период.Показать/скрыть дополнительное описание