Проектирование реляционных баз данных 2 Поволжский государственный университет телекоммуникаций и информатики Кафедра экономических и информационных систем Проектирование реляционных баз данных Рецензия Содержание Введение…………………………………………………………………………5 1. Инфологическое проектирование…………………………………………...6 1.1. Анализ предметной области……………………………………………….6 1.2. Анализ информационных задач и круга пользователей системы……….6 1.3. Составление реляционных отношений……………………………………7 2. Определение требований к операционной обстановке…………………….16 3. Выбор СУБД и других инструментальных программных средств………..16 4. Логическое проектирование БД……………………………………………...17 4.1. Нормализация полученных отношений…………………………………...17 4.2. Определение дополнительных ограничений целостности……………….26 4.3. Описание групп пользователей и прав доступа…………………………..26 5. Физическое проектирование БД……………………………………………..27 6. Реализация проекта БД……………………………………………………….28 Заключение……………………………………………………………………….37 Список использованных источников…………………………………………...39 Цели и задачи. Цель курсового проектирования – применение на практике знаний, полученных в процессе изучения курса "Базы данных", и приобретение практических навыков при проектировании и создания информационных систем (ИС),основанных на базах данных. Номер варианта Вариант 6 – Больница Задача – информационная поддержка деятельности регистратуры больницы. БД должна осуществлять: − учёт поступления пациентов (по отделениям); − учёт проведённого лечения; − учёт платных услуг с выдачей счетов на оплату; − ведение архива выписанных пациентов. Необходимо предусмотреть определение (по отделениям): − пропускной способности больницы; − среднего времени пребывания больных в стационаре; − наличия свободных мест в палатах (отдельно для мужчин и для женщин); − количества прооперированных пациентов (из них – с осложнениями и умерших); − смертности. Введение Проектирование баз данных - одна из наиболее сложных и ответственных задач, связанных с созданием информационной системы. База данных- это совокупность данных конкретной предметной области,при чем данные организованы по определенным правилам, предусматривающим общие принципы описания, хранения и манипулирования, и не зависят от программ обработки. В базе данных обеспечивается интеграция логически связанных данных при минимальном дублировании хранимых данных. Одно из важнейших достоинств реляционных баз данных состоит в том, что вы можете хранить логически сгруппированные данные в разных таблицах и задавать связи между ними, объединяя их в единую базу. VisualFoxPro - это система управления базами данных (СУБД). Под системой управления понимается комплекс программ, который позволяет не только хранить большие массивы данных в определенном формате, но и обрабатывать их, представляя в удобном для пользователей виде. VisualFoxPro дает возможность также автоматизировать часто выполняемые операции (например, расчет заработной платы, учет материальных ценностей и т.п.). С помощью VisualFoxPro можно не только разрабатывать удобные формы ввода и просмотра данных, но и составлять сложные отчеты. Основная цель проектирования баз данных состоит в получении такого проекта, который удовлетворяет следующим требованиям: 1)Корректность схемы БД, то есть база должна быть гомоморфным образом моделируемой предметной области, где каждому объекту предметной области соответствует данные в памяти ЭВМ, а каждому процессу – адекватные процедуры обработки данных. 2)Обеспечение ограничений 3) Эффективность функционирования 4)Защита данных 5)Простота и удобство эксплуатации 6)Гибкость, т.е. возможность развития БД. 1. Инфологическое проектирование 1.1. Анализ предметной области База данных создаётся для поддержки деятельности регистратуры больницы. БД должна содержать данные о пациентах, проведенном лечении, платных услугах, количестве мест в палатах и смертности. В соответствии с предметной областью система строится с учетом следующих особенностей: -пациента могут лечить сразу несколько врачей, при чем один из них главный врач; -диагноз выписывается врачом; -врач может лечить сразу несколько пациентов; -в одной палате могут жить сразу несколько пациентов; -в каждом отделении больницы много палат. Рассмотрение такой структуры базы данных начинается с построения простой модели взаимосвязи объектов. В самых общих чертах такое моделирование(оно называется моделированием сущностей) подразумевает определение сле- дующих элементов: объектов (сущностей), информация о которых будет содержаться в БД; свойств этих объектов(атрибутов); взаимосвязей между ними. Выделим базовые сущности этой предметной области. Без учета финансовой информации список сущностей будет следующим: -ВРАЧИ . Атрибуты-ФИО, номер телефона. -ПАЦИЕНТЫ . Атрибуты-ФИО, телефон, возраст -СТАЦИОНАР ПАЦИЕНТОВ . Атрибуты - дата начала лечения, номер палаты, дата окончания лечения, результат Каждый пункт этого списка описывает отдельное свойство или атрибут рассматриваемой сущности и является потенциальным столбцом в БД. Названия столбцов должны быть предельно ясными (назначение столбца должно быть понятно из его названия) и краткими (чтобы упростить ввод и названий и уменьшить их ширину). 1.2. Анализ информационных задач и круга пользователей системы Система создается для обслуживания следующих групп пользователей: -врачей; -медсестер; -сотрудников, которые регистрируют больных. 1) Функциональные возможности: − ведение БД (запись, чтение, модификация, удаление в архив); − обеспечение логической непротиворечивости БД; − обеспечение защиты данных от несанкционированного или случайного доступа (определение прав доступа); − реализация наиболее часто встречающихся запросов в готовом виде; − предоставление возможности сформировать произвольный запрос на языке манипулирования данными. 2) Готовые запросы: -вывод пациентов с летальным исходом; -вывод количество мест в мужских палатах; -вывод количество мест в женских палатах; -вывод количество пациентов, которым делали операцию 1.3. Составление реляционных отношений Для того, чтобы сделать работы регистратуры эффективнее необходимо учитывать всех больных, поступавших в больницу. А также время и дату поступления, к какому врачу, то есть кабинет и результат обследования или лечения. После выделения сущностей, необходимо определить первичные ключи. Рассмотрим таблицу Пациенты . Среди ее столбцов очевидным кандида- том на первичный ключ является ID-пациента. Первичные ключи выделяют подчеркиванием. Примечание : суррогатный первичный ключ также может вводиться в тех случаях, когда потенциальный ключ имеет большой размер (например, длинная символьная строка) или является составным (не менее трёх атрибутов). Сурагатныйключ в нашем случаи будет ID-пац_стационар. В таблице Врачи первичным ключом будет ID-врача. В таблице Прием- ID-приема, таблицу Диагноз можно идентифицировать ключом ID-диагноза. После определения ключей необходимо определить связи между сущностями. В моей базе данных практически все связи один ко многим. Рассмотрим одну из них: в одном стационаре может находится много врачей. Единственная связь один к одному между процедурами и пац_стационаром. Тип связи M:N реализуется путем ввода ассоциативного объекта, кото- рый является соединением первичных ключей соответствующих отношений (рис. 1.1), а связь M:N разбивается на две связи типа 1:N (рис. 1.2). ID-приема ID-пациента ID-врача ID-диагноза Дата Время Кабинет Исход ID-пациента ФИО Номер телефона Возраст Пациенты Прием Врачи ID-врача Код отделениия ФИО Номер телефона записывает имеет имеет Стационар Код отделения Кол-во палат Этаж имеют записывает имеют ID-диагноза ID-лечения Название ID-пац_стационара ID-пациента Код отделения Дата начала лечения Номер палаты Дата окончания лечения Результат Пац_стационар Диагноз Лечение ID-лечения Название Стоимость Статус содержит содержит палаты имеются ID -лечения ID -пац_стационара Процедуры содержит Рис. 1.1. Диаграмма сущность-связь БД больницы ID-приема ID-пациента ID-врача ID-диагноза Дата Время Кабинет Исход ID-пациента ФИО Номер телефона Возраст Пациенты Прием R2 Врачи ID-врача Код отделениия ФИО Номер телефона R 3 R 4 R 1 R9 R11 R5R7 Стационар Код отделения Кол-во палат Этаж R10 ID-диагноза ID-лечения Название ID-пац_стационара ID-пациента Код отделения Дата начала лечения Номер палаты Дата окончания лечения Результат Пац_стационар R8 Диагноз Лечение ID-лечения Название Стоимость Статус R15R13 R20 R14 R6 R17 палаты Номер палаты Статус Количество мест Код отделения R18 R12 R16 ID -лечения ID -пац_стационара Процедуры R19 Рис. 1.2. Уточненная диаграмма сущность-связь БД больницы В таблице 1.1 приведено описание связей Таблица описания связей таблица 1.1 Название связи Обозначение связи Главный объект Связанный объект Вид связи Условие связи Способ реализации Примечание имеет R1 Прием Врачи М:1 По коду врача имеет R2 Врачи Прием 1:М По коду врача записывает R3 Пациенты Прием 1:М По коду пациента записываются R4 Прием Пациенты М:1 По коду пациента имеются R5 Пациенты Пац_стационар 1:М По коду пациента имеют R6 Пац_стационар Пациенты М:1 По коду пациента записывает R7 Прием Диагноз М:1 По коду диагноза записывается R8 Диагноз Прием 1:М По коду диагноза имеет R9 Врачи Стационар М:1 По коду отделения имеются R10 Стационар Врачи 1:М По коду отделения имеют R11 Врачи Палаты 1:М По коду отделения имеются R12 Палаты врачи М:1 По коду отделения содержит R13 Диагноз Лечение М:1 По коду лечения содержится R14 Лечение Диагноз 1:М По коду лечения имеются R15 Пац_стационар Процедуры M:1 По коду пац_стационара имеются R16 Процедуры Пац_стационар 1:M По коду пац_стационара содержит R17 Пац_стационар Палаты М:1 По коду номера палаты содержатся R18 палаты Пац_стационар 1:М По коду номера палаты содержит R19 Процедуры Лечение М:1 По коду лечения содержится R20 лечение процедуры 1:М По коду лечения Отношения приведены в табл. 1.2 – 1.8. В столбце "Динамичность" бу- дем помечать буквой D изменяемые атрибуты (динамические), S - неизменяемые (статические). "Количество повторений" означает, сколько раз повторяется множественный атрибут. В столбце "Область возможных значений" указывается тип (C - символы, D - дата, N - число) и, возможно, диапазон изменения атрибута. В столбце "Вывод значений" указываются номера атрибутов, из которых можно получить данный атрибут. Выводимый атрибут можно не хранить. В столбце "Ограничение доступа" указано, кто имеет право изменять сведения. Таблица 1.2 Описание атрибутов объекта Пациенты Название атрибута Обозначение атрибута Динамичность Количество повторений Область возможных значений Вывод значений Ограничение доступа Примечание ID-пациента ID_pacien S - N(4) см. п.4.3 первичный ключ ФИО FIO D 1 C(50) см. п.4.3 Обязательное поле Номер телефона Nomer_telefona D 1 C(15) см. п.4.3 Многозначное поле Возраст Vozrast D 1 N(10) см. п.4.3 Обязательное поле Таблица1.3 Описание атрибутов объекта Врачи Название атрибута Обозначение атрибута Динамичность Количество повторений Область возможных значений Вывод значений Ограничение доступа Примечание ID-врача ID_pacien S - N(4) см. п.4.3 первичный ключ ФИО FIO D 1 C(50) см. п.4.3 Обязательное поле Номер телефона Nomer_telefona D 1 C(15) см. п.4.3 Многозначное поле Таблица1.4 Описание атрибутов объекта Пац_Стационара Название атрибута Обозначение атрибута Динамичность Количество повторений Область возможных значений Вывод значений Ограничение доступа Примечание ID-пац_стационара id_pac_sta S - N(4) см. п.4.3 Сурагатный первичный ключ ID-пациента ID_pacien S - N(5) см. п.4.3 Внешний ключ(к Пациенты) Код отделения kod_otdel S - N(4) см. п.4.3 Внешний ключ(к Стационар) Дата начала лечения data_nachala_lecheniya D 1 D(10) см. п.4.3 Обязательное поле Номер палаты nomer_pal D 1 N(10) см. п.4.3 Обязательное поле Дата окончания лечения data_okonchaniya_lecheniya D 1 D(10) см. п.4.3 Обязательное поле Результат rezultat D 1 C(10) см. п.4.3 Обязательное поле Таблица1.5 Описание атрибутов объекта Прием Название атрибута Обозначение атрибута Динамичность Количество повторений Область возможных значений Вывод значений Ограничение доступа Примечание ID-приема id_priema S - N(10) см. п.4.3 первичный ключ ID-пациента id_pacien S - N(4) см. п.4.3 внешний ключ(к Пациенты) ID-врача id_vracha S - N(10) см. п.4.3 Внешний ключ(к Врачи) ID-диагноза id_diagnoz S - N(10) см. п.4.3 Внешний ключ(к Диагноз) Дата data D 1 D(10) см. п.4.3 Обязательное поле Время vremya D 1 C(15) см. п.4.3 Обязательное поле Кабинет kabinet D 1 C(20) см. п.4.3 Обязательное поле Исход isxod D 1 C(20) см. п.4.3 Многозначительное поле Таблица 1.6 Описание атрибутов объекта Стационар Название атрибута Обозначение атрибута Динамичность Количество повторений Область возможных значений Вывод значений Ограничение доступа Примечание Код отделения kod_otdel S - N(4) см. п.4.3 первичный ключ Количество палат kollichestvo_palat D 1 C(10) см. п.4.3 Обязательное поле этаж etag D 1 C(10) см. п.4.3 Обязательное поле Таблица 1.7 Описание атрибутов объекта Диагноз Название атрибута Обозначение атрибута Динамичность Количество повторений Область возможных значений Вывод значений Ограничение доступа Примечание ID-диагноза id_diagnoz S - N(4) см. п.4.3 первичный ключ Название nazvanie D 1 C(27) см. п.4.3 Обязательное поле ID-лечения id_lechen S - N(10) см. п.4.3 Внешний ключ(к Лечение) Таблица 1.8 Описание атрибутов объекта Лечение Название атрибута Обозначение атрибута Динамичность Количество повторений Область возможных значений Вывод значений Ограничение доступа Примечание ID-лечения id_lechen S - N(4) см. п.4.3 первичный ключ Название nazvanie D 1 C(22) см. п.4.3 Обязательное поле стоимость stoimost D 1 Cur(10) см. п.4.3 Обязательное поле Статус statys D 1 C(10) см. п.4.3 Многозначное поле Таблица 1.9 Описание атрибутов объекта Палаты Название атрибута Обозначение атрибута Динамичность Количество повторений Область возможных значений Вывод значений Ограничение доступа Примечание Номер палаты nomer_pal S - N(4) см. п.4.3 первичный ключ статус status D 1 C(10) см. п.4.3 Многозначное поле Количество мест kollichestvo_mest D 1 C (10) см. п.4.3 Обязательное поле Код отделения kod_otdel S - N(10) см. п.4.3 Внешний ключ(к Стационар) Таблица 1.10 Описание атрибутов объекта Процедуры Название атрибута Обозначение атрибута Динамичность Количество повторений Область возможных значений Вывод значений Ограничение доступа Примечание ID-лечения id_lechen S - N(4) см. п.4.3 первичный ключ ID-пац_стационара nazvanie S - C(22) см. п.4.3 Обязательное поле 2. Определение требований к операционной обстановке Для выполнения этого этапа необходимо знать (хотя бы ориентировоч- но) объём работы издательства (т.е. количество книг, авторов и заказчиков), а также иметь представление о характере и интенсивности запросов. Объём внешней памяти, необходимый для функционирования системы, складывается из двух составляющих: память, занимаемая модулями СУБД (ядро, утилиты, вспомогательные программы), и память, отводимая под данные( Д М ). Наиболее существенным обычно является Д М . Объём памяти Д М , требуемый для хранения данных, можно приблизительно оценить по формуле Мд =2∑n i=1 li *(Ni +Nai ) где l i – длина записи в i -й таблице (в байтах), N i – примерное (максимально возможное) количество записей в i -й таблице, i N a – количество записей в архиве i -й таблицы. Коэффициент 2 перед суммой нужен для того, чтобы выделить память для хранения индексов, промежуточных данных, для выполнения объёмных операций (например, сортировки) и т.п. Посчитаем приблизительно, какой объём внешней памяти потребуется для хранения данных. Примем ориентировочно, что: − одновременно осуществляется около пятидесяти приемов, работа над ними продолжается в среднем два месяца (по 0,3К); − в компании работает 100 сотрудников (по 0,2К на каждого сотрудни- ка); − больница сотрудничает с тридцатью врачами (по 0,2К); − в день приема порядка двадцати заявок (по 0,1К); − устаревшие данные переводятся в архив. Тогда объём памяти для хранения данных за первый год примерно со- ставит: M Д = 2(100 ⋅0,2 + 6(50 ⋅0,3) + 30 ⋅0,2 + 250(20 ⋅0,1)) = 1232К ≈ 1,2М , где 250 – количество рабочих дней в году, а 12 мес./2 мес. = 6. Объём памяти будет увеличиваться ежегодно на столько же при сохранении объёма работы. Объём памяти, занимаемый программными модулями пользователя, обычно невелик по сравнению с объёмом самих данных, поэтому может не учитываться. Требуемый объём оперативной памяти определяется на основа- нии анализа интенсивности запросов и объёма результирующих данных. 3. Выбор СУБД и других программных средств Анализ информационных задач показывает, что для реализации требуе- мых функций подходят почти все СУБД для ПЭВМ (FoxPro, Clipper, MS Access и др.). Все они поддерживают реляционную модель данных и предоставляют разнообразные возможности для работы с данными. 14 Объём внешней и оперативной памяти, требующийся для функциониро- вания СУБД, обычно указывается в сопроводительной документации. Я выбрала СУБД FOXPRO. 4. Логическое проектирование реляционной БД 4.1. Нормализация полученных отношений (до 4НФ) 1НФ . Для приведения таблиц к 1НФ необходимо, чтобы все атрибуты были атомарны. Для этого необходимо разбить сложные атрибуты на простые,а многозначные атрибуты вынести в отдельные отношения. Примечание : В реальных БД сложные атрибуты разбиваются на простые, если: а) этого требует внешнее представление данных; б) в запросах поиск может осуществляться по отдельной части атрибута. Разделим атрибуты Фамилия Имя Отчество на три атрибута Фамилия, Имя, Отчество. 2НФ . В нашем случае составные первичные ключи имеют отношения Процедуры, Палаты, Лечение. Неключевые атрибуты этих отношений функционально полно зависят от первичных ключей. 3НФ. В отношении Диагноз атрибут код лечения зависит от кода диагноза,поэтому код лечение следует вынести в отдельное отношениет Лечение. 4НФ . Отношения данного примера не нарушают 4НФ, т.к. не содержат нетривиальных многозначных зависимостей. После проведённых преобразований схема БД выглядит так (рис. 1.3): ID-пациента Фамилия Имя Отчество Номер телефона Возраст ID-приема ID-пациента ID-врача ID-диагноза Дата Время Кабинет Исход Пациенты Прием R2 Врачи ID-врача Код отделениия ФИО Номер телефона R 3 R 4 R 1 R9 R11 R7 R5 Стационар Код отделения Кол-во палат Этаж R10 ID-диагноза ID-лечения Название ID-пац_стационара ID-пациента Код отделения Дата начала лечения Номер палаты Дата окончания лечения Результат Пац_стационар R8 Диагноз Лечение ID-лечения Название Стоимость Статус R15 R13 R20 R14 R6 R17 палаты Номер палаты Статус Количество мест Код отделения R18 R12 R16 ID -лечения ID -пац_стационара Процедуры R19 Рис. 1.3. Окончательная ER-модель БД больницы Название объекта Обозначе- ние объекта Количе- ство экземп- ляров Про- цент изме- нений Ограни- чение доступа Связанные объекты Примечания Пациенты Пациенты 100 20% больница Пац_стационар,Прием Прием Прием 200 20% больница Пациенты,диагноз,врачи Стационар Стационар 400 30% больница Пац_стационар,врачи,палаты Диагноз Диагноз 100 10% больница Прием,лечение Врачи Врачи 300 20% больница Прием,стационар Пац_стационар Пац_стационар 100 30% больница Процедуры,палаты,пациенты,стационар Лечение Лечение 100 20% больница Диагноз,процедуры Палаты Палаты 400 20% больница Стационар,пац_стационар Процедуры Процедуры 100 10% больница Пац_стационар,лечение В таблице 1.11 приведено уточненное описание связей. Таблица 1.11 Таблица описания связей Название связи Обозначение связи Главный объект Связанный объект Вид связи Условие связи Способ реализации Примечание имеет R1 Прием Врачи М:1 По коду врача имеет R2 Врачи Прием 1:М По коду врача записывает R3 Пациенты Прием 1:М По коду пациента записываются R4 Прием Пациенты М:1 По коду пациента имеются R5 Пациенты Пац_стационар 1:М По коду пациента имеют R6 Пац_стационар Пациенты М:1 По коду пациента записывает R7 Прием Диагноз М:1 По коду диагноза записывается R8 Диагноз Прием 1:М По коду диагноза имеет R9 Врачи Стационар М:1 По коду отделения имеются R10 Стационар Врачи 1:М По коду отделения имеют R11 Врачи Палаты 1:М По коду отделения имеются R12 Палаты врачи М:1 По коду отделения содержит R13 Диагноз Лечение М:1 По коду лечения содержится R14 Лечение Диагноз 1:М По коду лечения имеются R15 Пац_стационар Процедуры M:1 По коду пац_стационара имеются R16 Процедуры Пац_стационар 1:M По коду пац_стационара содержит R17 Пац_стационар Палаты М:1 По коду номера палаты содержатся R18 палаты Пац_стационар 1:М По коду номера палаты содержит R19 Процедуры Лечение М:1 По коду лечения содержится R20 лечение процедуры 1:М По коду лечения Окончательные схемы отношений базы данных с указанием ключей и других ограничений целостности приведены в табл. 1.12 – 1.20. Описание атрибутов объекта Пациенты Таблица 1.12 Название атрибута Обозначение атрибута Динамичность Количество повторений Область возможных значений Вывод значений Ограничение доступа Примечание ID-пациента ID_pacien S - N(4) см. п. 2.4.3 первичный ключ Фамилия familiya D 1 C(50) см. п. 2.4.3 Обязательное поле Имя imya D 1 C(20) см. п. 2.4.3 Обязательное поле Отчество otchestvo D 1 C(20) см. п. 2.4.3 Обязательное поле Номер телефона Nomer_telefona D 1 C(15) см. п. 2.4.3 Многозначное поле Возраст Vozrast D 1 N(10) см. п. 2.4.3 Обязательное поле Таблица 1.13 Описание атрибутов объекта Врачи Название атрибута Обозначение атрибута Динамичность Количество повторений Область возможных значений