Программирование в Excel — удалённая работа в Москве

Анализ и работа с базами данных, автоматизация формирования отчётов. Техническое задание есть. Используя в качестве СУБД MySQL и в качестве клиента DBeaver/Workbench (или другой SQL-клиент). Требуется создать БД корпоративной клиники компании ЗдравМедТех для хранения информации о врачах, услугах, пациентах, которым было выдано направление на обследование в предыдущем задании. Для проверки работоспособности нужно будет сделать несколько запросов в виде отчётов для руководства. Также вы создадите небольшую базу знаний, протестируете БД на безопасность, создадите несколько ролей и пользователей. Общие требования 1. В заданиях встречается фраза [Скриншот №] – в этом случае необходимо сделать снимок экрана, на котором должно быть видно то, что требует задание, и соответствующий кусок кода для реализации данной части. Название скриншота делаете таким же «Скриншот №», указывая номер, как в задании. 2. Все операции по добавлению, удалению, выборке данных должны выполняться с помощью написания SQL-скриптов. 3. Каждый блок кода имеет логичный комментарий, описывающий за что отвечает эта часть. Проектирование структуры Клиника переходит на цифровую систему. Вам необходимо спроектировать электронную регистратуру. Данные должны быть нормализованы (разделены по логическим таблицам) и связаны между собой внешними ключами. 1. Создайте базу данных с именем zdravmedteh и спроектируйте 4 таблицы, самостоятельно подобрав подходящие типы данных для полей: 1. Справочник услуг (имя таблицы: reference_services): • id - уникальный идентификатор (первичный ключ, автозаполнение), • service_name - название услуги (до 100 символов, не может быть пустым), • description - описание (вмещает большие объёмы текста) • preparation - подготовка к процедуре (вмещает большие объёмы текста). 2. Врачи (имя таблицы: doctors): • id - уникальный идентификатор, • fio - ФИО врача (до 100 символов, не может быть пустым), • specialty – специальность (до 50 символов, не может быть пустым). 3. Пациенты (имя таблицы: patients): • id - уникальный идентификатор, • fio - ФИО пациента (до 100 символов, не может быть пустым), • department - отдел (до 50 символов), • age - возраст (целочисленное значение), • smoking - курение (до 3 символов, по умолчанию «Нет»), • ИМТ (числовое значение - 2 цифры до запятой и 1 после), • status - статус (до 50 символов). 4. Журнал приемов (имя таблицы: appointments): • id - уникальный идентификатор, • patient_id - идентификатор пациента, • doctor_id - идентификатор врача, • service_id - идентификатор услуги, • appointment_date - дата приёма. Настройте внешние ключи от таблицы журнала приёмов к трём остальным таблицам. Наполнение таблиц данными Мы переносим в базу картотеку сотрудников из Excel. После этого нужно указать врачей, открыть услуги и массово записать группы пациентов на первичные осмотры. 1. Выполните импорт данных о пациентах с направлениями, список которых был сформирован в конце задания 3, в таблицу patients. [Скриншот №1]: Сделайте запрос и выведите на экран содержимое всей таблицы пациентов, чтобы показать успешный импорт. 2. ДобавьтеТерапевт Иванов И.И., • Кардиолог Петров П.П., • Диетолог Сидоров С.С., • Невролог Смирнов А.А. [Скриншот №2]: Выведите на экран весь сформированный список врачей. 3. Добавьте 3 услуги в таблицу reference_services: • УЗИ брюшной полости: o Описание: Обследование внутренних органов. o Подготовка: Строго натощак. • ЭКГ: o Описание: Электрокардиография сердца. o Подготовка: Исключить кофе. • Анализ крови: o Описание: Биохимия и общий анализ. o Подготовка: Утром натощак. [Скриншот №3]: Выведите на экран весь справочник услуг. 4. Напишите запросы, которые запишут пациентов на приём: • Запишите Новикову П.Р., Волкова И.В. и Веселову О.П. к терапевту на анализ крови на сегодняшнюю дату (используйте системную функцию получения текущей даты). • Запишите весь отдел «IT» к кардиологу на ЭКГ на завтрашнюю дату (используйте функцию текущей даты с прибавлением 1 дня). • Запишите весь отдел «Логистика» к неврологу на УЗИ на послезавтра (прибавление 2 дней к функции текущей даты). • Запишите всех курящих пациентов к диетологу на анализ крови через 3 дня (прибавление к функции текущей даты). • Запишите сотрудников сразу двух отделов «Продажи» и «HR» к терапевту на ЭКГ на сего[Скриншот №4]: Выведите на экран весь журнал приёмов.дняшнюю дату (функция текущей даты). Терапевт оказался перегружен, часть его пациентов нужно передать другому специалисту. Кроме того, выяснилось, что два человека не явились на прием сегодня, их талоны аннулируются. Внесите эти изменения: 1. Освобождаем график терапевта: [Скриншот №5]: Выведите все текущие записи к неврологу до изменений. [Скриншот №6]: Выведите все текущие записи к терапевту до изменений. • Переведите 3 любые записи от терапевта к неврологу. [Скриншот №7]: Выведите все текущие записи к неврологу после изменений. [Скриншот №8]: Выведите все текущие записи к терапевту после изменений. 2. Отмена несостоявшихся приёмов: [Скриншот №9]: Выведите все записи на приём, которые должны состояться сегодня, до изменений. • Удалите 2 любые сегодняшние записи. [Скриншот №10]: Выведите все записи на приём, которые должны состояться сегодня, после изменений. Отчёты Руководству требуются управленческие отчеты для оценки здоровья коллектива и нагрузки на врачей. Напишите 11 запросов на выборку данных по нарастающей сложности: 1. [Скриншот №11]: Выведите ФИО, отдел и возраст тех пациентов, которые не курят. 2. [Скриншот №12]: Выведите ФИО, отдел и ИМТ 5-и пациентов с самыми высокими показателями ИМТ, отсортировав список по убыванию ИМТ. 3. [Скриншот №13]: Сгруппируйте данные по отделам, подсчитайте и выведите, сколько сотрудников из каждого отдела числится в базе. Вывод: отдел, количество человек. 4. [Скриншот №14]: Сгруппируйте пациентов и выведите только те отделы, в которых числится 3 и более курящих сотрудников. Вывод: отдел, количество курящих [Скриншот №15]: Выведите ФИО и отдел тех людей из картотеки, которые ни разу не были записаны ни к одному врачу (отсутствуют в журнале приёмов). 6. [Скриншот №16]: Выведите ФИО пациента и даты приема, отфильтровав список строго по сегодняшнему дню. 7. [Скриншот №17]: Выведите ФИО пациента, ФИО назначенного врача и дату приёма для всех записей. 8. [Скриншот №18]: Сгруппируйте результат по пациентам и выведите ФИО и количество талонов только для тех людей, у которых на руках больше 1 записи ко врачу. 9. [Скриншот №19]: Выведите ФИО каждого врача и суммарное количество назначенных к нему приёмов, предварительно отсортируйте результаты от самых загруженных специалистов к более свободным. 10.[Скриншот №20]: Подсчитайте общее количество визитов ко врачам от сотрудников каждого отдела и выведите название отдела и количество визитов, отсортировав по убыванию. 11.[Скриншот №21]: Выведите список: Пациент, Врач, Процедура, Инструкция, Дата – при этом отчёт должен быть отсортирован сначала по дате приема (от будущих дат к текущим), затем (внутри одного дня) по ФИО врача по алфавиту от а до я, и наконец по ФИО пациента по алфавиту от а до я. Оптимизация и база знаний 1. Напишите команду для создания структуры (индекса), ускоряющей поиск по колонке ФИО в таблице пациентов, чтобы БД работала быстрее. [Скриншот №22]: Выведите системную информацию о структуре таблицы пациентов (команда SHOW INDEX FROM patients), подтверждающую успешное создание этого индекса. 2. Создайте новую таблицу knowledge_rules для системы помощи врачам. Со следующими полями: • id - уникальный идентификатор, • condition_if - условие (до 255 символов, не может быть пустым), • conclusion_then - заключение (до 255 символов, не может быть пустым). Добавьте в неё 2 правила:Условие: «ИМТ > 30.0», Заключение: «Высокий риск сердечнососудистых заболеваний». 2. Условие: «Курение = Да И Возраст > 50», Заключение: «Обязательное регулярное ЭКГ». [Скриншот №23]: Выведите содержимое таблицы базы знаний на экран. Безопасность и права доступа Проверим, как механизмы транзакций спасают от критических ошибок персонала, а затем настроим безопасную ролевую модель (создадим профили пользователей клиники). 1. Демонстрация работы транзакций: [Скриншот №24]: Выведите все приёмы к кардиологу. • Запустите механизм транзакции и удалите все приёмы к кардиологу. [Скриншот №25]: Выведите все приёмы к кардиологу. • Совершите программную отмену транзакции. [Скриншот №26]: Выведите все приёмы к кардиологу. 2. Напишите запросы на создание 3 системных ролей и распределите привилегии: • Admin_Role (все привилегии на БД); • Doctor_Role (только просмотр таблицы пациентов и полное управление (выборка, добавление, обновление) в таблице приёмов); • Registrar_Role (выборка, добавление и обновление данных в таблицах пациентов и приёмов, а также право удаления записей только из таблицы приёмов). [Скриншот №27]: Выведите созданные роли с помощью команды SELECT User, Host FROM mysql.user; 3. Создайте трёх системных пользователей и назначьте им роли, которые сделали в предыдущем пункте: • Пользователь: admin_boss. Роль: Admin_Role. • Пользователь: doc_ivanov. Роль: Doctor_Role. • Пользователь: reg_anna. Роль: Registrar_Role. [Скриншот №28]: Выведите созданных пользователей через SHOW GRANTS. Сохранение файлов 1. Создайте полную резервную копию БД в виде исполняемого SQL-скрипта с названием БД. При настройке параметров экспорта обязательно отключите экспорт глобальных идентификаторов транзакций (GTID). Для этого в дополнительных аргументах укажите параметр --set-gtid-purged=OFF. 2. Поместите файл с резервной копией и все скриншоты в zip архив с названием Фамилия_Практика_Задание4.zip (подставляете свою фамилию).