Проектирование реляционных баз данных 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).

имеет

Стационар

имеют
записывает

имеют

содержит

палаты

имеются

Процедуры

содержит

Рис. 1.1. Диаграмма сущность-связь БД больницы

R9 R11

R5R7

Стационар

R10

R14

R6

R17

палаты

R12

R16

Процедуры

R19

Рис. 1.2. Уточненная диаграмма сущность-связь БД больницы

В таблице 1.1 приведено описание связей

Таблица описания связей таблица 1.1

Отношения приведены в табл. 1.2 – 1.8. В столбце "Динамичность" бу-

дем помечать буквой D изменяемые атрибуты (динамические), S - неизменяемые (статические). "Количество повторений" означает, сколько раз повторяется множественный атрибут. В столбце "Область возможных значений" указывается тип (C - символы, D - дата, N - число) и, возможно, диапазон изменения атрибута. В столбце "Вывод значений" указываются номера атрибутов, из которых можно получить данный атрибут. Выводимый атрибут можно не хранить. В столбце "Ограничение доступа" указано, кто имеет право изменять сведения.

Таблица 1.2

Описание атрибутов объекта Пациенты

Таблица1.3

Описание атрибутов объекта Врачи

Таблица1.4

Описание атрибутов объекта Пац_Стационара

Таблица1.5

Описание атрибутов объекта Прием

Таблица 1.6

Описание атрибутов объекта Стационар

Таблица 1.7

Описание атрибутов объекта Диагноз

Таблица 1.8

Описание атрибутов объекта Лечение

Таблица 1.9

Описание атрибутов объекта Палаты

Таблица 1.10

Описание атрибутов объекта Процедуры

2. Определение требований к операционной обстановке

Для выполнения этого этапа необходимо знать (хотя бы ориентировоч-

но) объём работы издательства (т.е. количество книг, авторов и заказчиков), а

также иметь представление о характере и интенсивности запросов.

Объём внешней памяти, необходимый для функционирования системы,

складывается из двух составляющих: память, занимаемая модулями СУБД (ядро, утилиты, вспомогательные программы), и память, отводимая под данные( Д М ). Наиболее существенным обычно является Д М . Объём памяти Д М , требуемый для хранения данных, можно приблизительно оценить по формуле

М_д=2∑ⁿ_i=1l_i*(N_i+N_ai)

где li – длина записи в i-й таблице (в байтах), Ni – примерное (максимально

возможное) количество записей в i-й таблице, i Na – количество записей в архиве 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):

R9 R11

R7

R5 Стационар

R10

R14

R6

R17

палаты

R12

R16

Процедуры

R19

Рис. 1.3. Окончательная ER-модель БД больницы

В таблице 1.11 приведено уточненное описание связей.

Таблица 1.11

Таблица описания связей

Окончательные схемы отношений базы данных с указанием ключей и

других ограничений целостности приведены в табл. 1.12 – 1.20.

Описание атрибутов объекта Пациенты

Таблица 1.12

Таблица 1.13

Описание атрибутов объекта Врачи

Описание атрибутов объекта Пац_стационар

Таблица 1.14

Описание атрибутов объекта Прием

Таблица 1.15

Описание атрибутов объекта Стационар

Таблица 1.16

Описание атрибутов объекта Диагноз

Таблица 1.17

Описание атрибутов объекта Лечение

Таблица 1.18

Описание атрибутов объекта Палаты

Таблица 1.19

Описание атрибутов объекта Процедуры

Таблица 1.20

4.2. Определение дополнительных ограничений цело-

стности

Перечислим ограничения целостности, которые не указаны в табл. 1.12–1.20.

1. Значения всех числовых атрибутов – больше 0 (или null, если атрибут

необязателен).

2. Область значений атрибута Статус от ношения Палаты-символы м,ж.

А в отношении Лечение – платное,бесплатное.

3. В отношении Пациентыпорядковые номера пациентов должны идти подряд, начиная с 1.

Ограничения (3) нельзя реализовать в схеме отношения. В реальных

БД подобные ограничения целостности реализуются программно (через внешнее приложение или специальную процедуру контроля данных).

4.3. Описание групп пользователей и прав доступа

Опишем для каждой группы пользователей права доступа к каждой таб-

лице и к каждому полю (атрибуту).

1. Администратор БД: имеет доступ ко всем данным (по записи), может из-

менять структуру базы данных и связи между отношениями. Устанавли-

вает права доступа для всех остальных групп.

2. Представители администрации компании: имеют доступ по чтению ко

всем данным и доступ по записи к отношениям Врачи, Палаты и Стационар

3. Менеджеры: имеет доступ по чтению ко всем данным, кроме отношения

Диагноз. Имеют доступ по записи к отношениям Пациенты,Прием,Стационар,Врачи,Лечение,Процедуры,Палаты,Пац_стационар

4. Сотрудники: имеют доступ по чтению к отношениям Палаты, Стационар,.

6. Реализация проекта базы данных

Данный проект реализуется в СУБД FOXPRO. Для нормального функ-

ционирования базы данных создаются таблицы, запросы, отчеты и формы. Для

удобства пользователя – кнопочная форма. Также целесообразно определить

пользователей базы данных и разграничить права доступа.

Представим последовательность реализации в семь этапов.

1 Этап. Создание таблиц

На данном этапе в режиме Конструктора, Мастера или Путем ввода

данных задаются названия полей, типы данных, маски ввода, размеры и описа-

ния полей, выбираются первичные и вторичные ключи.

Рис. 1.5. Таблица Прием в режиме Конструктора

Аналогичным образом создаются все остальные таблицы базы данных

2 Этап. Схема данных

На данном этапе на Схему данных MS Access выносятся все созданные

таблицы и устанавливаются связи между ними. При установлении связей между таблицами необходимо установить режим Обеспечения целостности данных.

Рис. 1.6. Схема данных реализуемого проекта

VisualFoxProтакже позволяет просматривать сведения о зависимостях между объектами базы данных. Просмотр списка объектов, использующих указанный объект, помогает осуществлять поддержку базы данных и предотвращать ошибки, связанные с потерей источников записей. Реализована возможность просматривать объекты, зависящие от данного объекта, а также объекты, от которых зависит он. Также с помощью анализа зависимостей можно найти и локализовать возможные ошибки схемы данных.

Чтобы посмотреть зависимости объекта БД (таблицы, запроса, формы,

отчета) нужно выбрать из контекстного меню объекта пункт "Зависимости объектов" (рис. 1.7).

Рис. 1.7. Просмотр объектов зависящих от таблицы врачи

Теперь рассмотрим готовые запросы:

-вывод пациентов с летальным исходом;

-вывод количество мест в мужских палатах;

-вывод количество мест в женских палатах;

-вывод пациентов, которым делали операцию.

Вывод пациентов с летальным исходом:

SELECT Пац_стационар.id_pacien, Пац_стационар.rezultat;

FROM ;

data1!пац_стационар;

WHERE Пац_стационар.rezultat LIKE ( "л%" )

Количество мест в мужских палатах

SELECT Палаты.status, Палаты.kollichestvo_mest;

FROM ;

data1!стационар ;

INNER JOIN data1!палаты ;

ON Стационар.kod_otdel = Палаты.kod_otdel;

WHERE Палаты.status = ( "м" )

Количество мест в женских палатах

SELECT Палаты.kollichestvo_mest, Палаты.status;

FROM ;

data1!стационар ;

INNER JOIN data1!палаты ;

ON Стационар.kod_otdel = Палаты.kod_otdel;

WHERE Палаты.status = ( "ж" )

вывод пациентов, которым делали операцию

SELECT Пациенты.id_pacien, Прием.isxod;

FROM ;

data1!пациенты ;

INNER JOIN data1!прием ;

ON Пациенты.id_pacien = Прием.id_pacien;

WHERE Прием.isxod LIKE ( "операция" )

3 Этап. Создание отчетов

Отчет является эффективным средством представления данных в печатном формате. Имея возможность управлять размером и внешним видом всех

элементов отчета, пользователь может отобразить сведения желаемым образом.

Пример отчета основанного на одной таблице(в режиме Конструктора) представлен на рис. 1.10 и 1.11

Рис.1.10 отчет в режиме конструктора

Рис.1.11. отчет в режиме просмотра

Этап 4. Создание экранных форм

Для удобства ввода значений в таблицы базы данных в VisualFoxPro

предусмотрена возможность создания экранных форм.

Формы можно создавать с помощью мастера построения и конструктора. На формы можно выносить не только поля и их названия, но и дополни-

тельные кнопки (навигации, поиска, открытия других форм, отчетов, обработки записей), а также информацию, такую как дата и время.

В режиме конструктора

В режиме просмотра

Отчет основанный на одной таблице

В режиме конструктора

В режиме просмотра

Этап 5. Разграничение доступа

Учетные записи пользователей предоставляют отдельным пользователям определенные привилегии доступа к сведениям и ресурсам базы данных.

Учетные записи групп содержат несколько учетных записей пользователей и

предоставляют средства контроля и управления разрешениями и доступом этих групп к объектам базы данных.

Заключение

Я научилась создавать и проектировать базы данных. В нашей жизни это очень важно и необходимо, так как современные информационные системы характеризуются огромными объёмами хранимых данных, их сложной организацией, необходимостью удовлетворять разнообразные требования пользователей.

Поэтому мы и рассматриваем понятие баз данных (БД), возможности систем управления базами данных (СУБД) и их использование.

База данных- это совокупность данных конкретной предметной области,при чем данные организованы по определенным правилам, предусматривающим общие принципы описания, хранения и манипулирования, и не зависят от программ обработки. В базе данных обеспечивается интеграция логически связанных данных при минимальном дублировании хранимых данных.

Одно из важнейших достоинств реляционных баз данных состоит в том, что вы можете хранить логически сгруппированные данные в разных таблицах и задавать связи между ними, объединяя их в единую базу.

VisualFoxPro - это система управления базами данных (СУБД). Под системой управления понимается комплекс программ, который позволяет не только хранить большие массивы данных в определенном формате, но и обрабатывать их, представляя в удобном для пользователей виде. VisualFoxPro дает возможность также автоматизировать часто выполняемые операции (например, расчет заработной платы, учет материальных ценностей и т.п.). С помощью VisualFoxPro можно не только разрабатывать удобные формы ввода и просмотра данных, но и составлять сложные отчеты

СУБД основывается на трех основных типов моделей данных и их комбинациях:

– Иерархическая,

– Сетевая;

– Реляционная.

СУБД позволяют вводить и корректировать данные двумя способами:

· с помощью стандартной формы в виде таблицы;

· с помощью экранных форм, специально созданных для этого пользователем.

Форма – это средство для ввода данных в таблицу.

В форме можно разместить элементы управления: счётчики, списки, переключатели, флажки и прочие элементы. Использование формы снимает утомление оператора и предотвращает появление печатных ошибок.

При работе с СУБД используются запросы.

Запрос – это инструкция на отбор записей.

Запросы служат для извлечения данных из таблиц и предоставления их в удобном виде пользователю.

С их помощью можно выполнить операции:

- отбора данных;

- сортировку и фильтрацию данных;

- преобразования данных по заданному алгоритму;

- создания новых таблиц;

- автоматического наполнения таблиц данными, импортированными из других источников;

- простейшего вычисления в таблицах.

Используются запросы следующих типов:

· запрос – выборка, предназначенный для отбора данных, хранящихся в таблицах, и не изменяющий эти данные (самый распространённый тип запроса);

· запрос – изменение, предназначенный для изменения или перемещения данных.

К ним относятся:

- запрос на добавление записей,

- запрос на удаление записей;

- запрос на создание таблицы;

- запрос на обновление.

· запрос с параметром, позволяющий определить одно или несколько условий отбора во время выполнения запроса.

Отчёты предназначены для вывода данных на печатающее устройство (принтер), а не на экран. В отчетах размещаются колонтитулы, номера страниц, время создания отчёта и другая служебная информация.

При создании отчётов предусмотрены дополнительные возможности вывода данных:

· включать в отчёт выборочную информацию из таблиц БД;

· добавлять информацию, не содержащуюся в БД;

· выдавать итоговые данные на основе информации БД;

· располагать вводимую в отчёте информацию в любом, удобном для пользователя виде;

· включать в отчёт информацию из разных связанных таблиц БД.

СПИСОК ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВ

1. Амелина Н.И., Мачулина Л.А. Методические указания по курсовому

проектированию по курсу "Базы данных". – Ростов-на-Дону.: Изд-во

Ростов. гос. Ун-та 1999. – 39 с.

2. Бекаревич Ю.Б., Пушкина Н.В., Смирнова Е.Ю. Управление базами

данных: Учеб. пособие. СПб.: Изд-во С.-Петер. ун-та 1999. – 172 с.

3. Боуман Джудит С., Эмерсон Сандра Л., Дарновски Марси Практиче-

ское руководство по SQL, 4-е издание.: Пер. с англ. – М.: Издатель-

ский дом "Вильямс", 2001. – 352 с.: ил.

4. Диязитдинова А.Р., Качков Д.А. Проектирование баз данных. Учебное

пособие. – Самара: ПГАТИ, 2003 г. – 110 с.

5. Карпова И.Н. Введение в базы данных: Методические указания к кур