123 - Курсовая работа на тему \"база данных аптеки\" PDF

Preview

Summary

Курсовая работа на тему "база данных аптеки"...

Description

СОДЕРЖАНИЕ ОПРЕДЕЛЕНИЯ, ОБОЗНАЧЕНИЯ И СОКРАЩЕНИЯ......................................4 ВВЕДЕНИЕ..............................................................................................................5 1 АНАЛИЗ ПРЕДМЕТНОЙ ОБЛАСТИ................................................................6 2 ПОСТРОЕНИЕ ИНФОЛОГИЧЕСКОЙ МОДЕЛИ............................................8 3 СОСТАВЛЕНИЕ СПИСКА НЕОБХОДИМЫХ АТРИБУТОВ.........................9 4 ПОСТРОЕНИЕ НАБОРА НЕОБХОДИМЫХ ОТНОШЕНИЙ И ИХ НОРМАЛИЗАЦИЯ................................................................................................10 4.1 Набор необходимых отношений базы данных..........................................11 4.2 Нормализация...............................................................................................13 5 ПОСТРОЕНИЕ СХЕМЫ ДАННЫХ.................................................................15 6 ПРОЕКТИРОВАНИЕ И РЕАЛИЗАЦИЯ ЗАПРОСОВ К БД..........................16 ЗАКЛЮЧЕНИЕ.....................................................................................................28 СПИСОК ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВ.............................................29

ОПРЕДЕЛЕНИЯ, ОБОЗНАЧЕНИЯ И СОКРАЩЕНИЯ База данных (БД) – некая именованная совокупность данных, отражающая состояние объектов или явлений в некоторой выбранной предметной области и связи между ними. Она создаётся для хранения данных и доступа к ним. Предметная область (ПО) – любая область деятельности человека или часть реального мира, подлежащую изучению для организации управления ею и автоматизации. Система управления базами данных (СУБД) – совокупность программных и лингвистических средств общего или специального назначения, обеспечивающих управление созданием и использованием баз данных. Сущность – это собирательное понятие (некоторая абстракция реально существующего объекта, процесса или явления о котором необходимо хранить информацию в системе) [1]. Реляционная БД – множество взаимосвязанных двумерных таблиц – реляционных таблиц, называемых также отношениями, в каждой из которых содержатся сведения об одной сущности автоматизируемой предметной области [2]. ER-модель (англ. entity-relationship model) – модель данных, позволяющая описывать концептуальные схемы предметной области. С её помощью можно выделить ключевые сущности и обозначить связи, которые могут устанавливаться между этими сущностями [3]. Атрибут – поименованная характеристика сущности. Ключ – это минимальный набор атрибутов, по значениям которых можно однозначно найти требуемый экземпляр сущности. Нормализация – процесс преобразования отношений базы данных к виду, отвечающему нормальным формам. Декомпозиция – замена отношения на совокупность отношений такую, что каждое из них есть проекция исходного, и каждый атрибут входит хотя бы в одну из проекций декомпозиции.

2

ВВЕДЕНИЕ В данной работе будет рассмотрено построение базы данных на основе реляционной модели данных. Она основана на принципах теории множеств: объекты представляют собой элементы некоторых множеств и подмножеств, а сама БД есть совокупность двумерных связанных между собой таблиц. Основные требования, которые нужно учитывать при проектировании базы данных: минимальная избыточность, непротиворечивость, целостность и независимость данных, возможность ведения (добавления и удаления) и актуализации (корректировки, модификации) данных, безопасность и высокая производительность. По сравнению с другими способами хранения информации БД обладает следующими преимуществами:  сокращение избыточности в хранении данных;  обеспечение безопасности данных;  устранение возможных противоречий в данных;  поддержание целостности и корректности данных;  обеспечение независимости организации данных.

приложений

от

физической

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

3

1 АНАЛИЗ ПРЕДМЕТНОЙ ОБЛАСТИ Создание базы данных начинается с изучения и анализа предметной области. В нашем случае необходимо создать базу данных для нужд аптеки: Аптека продает медикаменты и изготавливает их по рецептам. Лекарства могут быть разных типов:  Готовые лекарства: таблетки, мази, настойки...  Изготовляемые аптекой: микстуры, мази, растворы, настойки, порошки. Различие в типах лекарств отражается в различном наборе атрибутов, их характеризующих. Микстуры и порошки изготавливаются только для внутреннего применения, растворы для наружного, внутреннего применения и для смешивания с другими лекарствами и мази только для наружного применения. Лекарства различны также по способу приготовления и по времени приготовления. Порошки и мази изготавливаются смешиванием различных компонент. При изготовлении растворов и микстур ингредиенты не только смешивают, но и отстаивают с последующей фильтрацией лекарства, что увеличивает время изготовления. В аптеке существует справочник технологий приготовления различных лекарств. В нем указываются: идентификационный номер технологии, название лекарства и сам способ приготовления. На складе на все медикаменты устанавливается критическая норма, т.е. когда какого-либо вещества на складе меньше критической нормы, то составляются заявки на данные вещества и их в срочном порядке привозят с оптовых складов медикаментов. Для изготовления аптекой лекарства, больной должен принести рецепт от лечащего врача. В рецепте должно быть указано: ФИО, подпись и печать врача, ФИО, возраст и диагноз пациента, также количество лекарства и способ применения. При этом, надо учитывать и категории пациентов: не льготники, инвалиды, участники войны, пенсионеры по болезни, пенсионеры по старости и т.д. Больной отдает рецепт регистратору, он принимает заказ и смотрит, есть ли компоненты заказываемого лекарства. Если не все компоненты имеются в наличии, то делает заявки на оптовые склады лекарств и фиксирует ФИО, категорию, телефон и адрес необслуженного покупателя, чтобы сообщить ему, когда 4

доставят нужные компоненты. Такой больной пополняет справочник заказов - это те заказы, которые находятся в процессе приготовления, с пометкой, что не все компоненты есть для заказа. Если все компоненты имеются, то они резервируются для лекарства больного. Покупатель выплачивает цену лекарства, ему возвращается рецепт с пометкой о времени изготовления. Больной также пополняет справочник заказов в производстве. В назначенное время больной приходит и по тому же рецепту получает готовое лекарство. Такой больной пополняет список отданных заказов. Ведется статистика по объемам используемых медикаментов. Через определенный промежуток времени производится инвентаризация склада. Это делается для того, чтобы определить, есть ли лекарства с критической нормой, или вышел срок хранения или недостача. В приведённом выше описании ПО были выделены следующие сущности: готовые лекарства, изготовляемые лекарства, вещества (компоненты), справочник технологий, клиенты, заказы, рецепты и статистика. К этим сущностям также можно добавить состав лекарств, назначения (медикаменты, указанные в рецепте и их количество) и склад, на котором будут храниться медикаменты. Врач выписывает больному рецепт, тот приходит в аптеку, приобретает готовые лекарства и делает заказ на изготовляемые лекарства. Для их изготовления по определённой технологии необходимы медикаменты.

5

2 ПОСТРОЕНИЕ ИНФОЛОГИЧЕСКОЙ МОДЕЛИ Проектирование инфологической модели предметной области – частично формализованное описание объектов ПО в терминах некоторой семантической модели, например, в терминах ER-модели. По правилам построения ER-схемы в нотации Мартина сущность изображается в виде прямоугольника. Связь изображается линией, которая связывает две сущности, участвующие в отношении. Степень конца связи указывается графически, множественность связи изображается в виде «вилки» на конце связи. Модальность связи так же изображается графически — необязательность связи помечается кружком на конце связи [3]. На основе проведенного анализа ПО, составим ER-схему проектируемой базы данных в нотации Мартина. Схема представлена на рисунке 2.1.

Рисунок 2.1 – ER-схема проектируемой базы данных в нотации Мартина

6

3 СОСТАВЛЕНИЕ СПИСКА НЕОБХОДИМЫХ АТРИБУТОВ Атрибутом сущности является любая деталь, которая служит для уточнения, идентификации, классификации, числовой характеристики или выражения состояния сущности. Его наименование должно быть уникальным для конкретного типа сущности, но может быть одинаковым для различного типа сущностей [4]. Выявленные сущности будут иметь следующие наборы атрибутов:  Готовые лекарства стоимость);

(номер

лекарства,

наименование,

форма,

 Изготовляемые лекарства (номер лекарства, наименование, форма, стоимость);  Компоненты (номер компонента, наименование, форма, стоимость);  Справочник технологий (номер технологии, номер лекарства, способ приготовления, время приготовления);  Клиенты (номер клиента, фамилия, имя, отчество, дата рождения, адрес, телефон, категория);  Рецепты (номер рецепта, номер клиента, ФИО врача, диагноз);  Заказы (номер заказа, номер рецепта, дата изготовления, дата выдачи, стоимость, статус);

приёма,

 Назначения (номер рецепта, номер лекарства, количество);  Состав (номер лекарства, номер ингредиента, количество);  Склад (номер товара, количество, критическая норма);  Статистика (номер товара, продано, дата).

7

дата



8

4 ПОСТРОЕНИЕ НАБОРА НЕОБХОДИМЫХ ОТНОШЕНИЙ И ИХ НОРМАЛИЗАЦИЯ Для построения схемы реляционной базы данных необходимо определить совокупность отношений, составляющих базу данных. Эта совокупность отношений будет содержать всю информацию, которая должна храниться в базе данных. На основе полученной инфологической модели можно определить набор необходимых отношений базы данных. В реляционной базе данных каждому объекту и сущности реального мира соответствуют кортежи отношений. Каждое отношение должно обладать хотя бы одним ключом. Существуют ключи двух типов: первичные и внешние. Первичный ключ – это одно или несколько полей (столбцов), комбинация значений которых однозначно определяет каждую запись в таблице. Первичный ключ не допускает значений Null и всегда должен иметь уникальный индекс. Первичный ключ используется для связывания таблицы с внешними ключами в других таблицах. Внешний ключ – это одно или несколько полей (столбцов) в таблице, содержащих ссылку на поле или поля первичного ключа в другой таблице. Внешний ключ определяет способ объединения таблиц. Из двух логически связанных таблиц одну называют таблицей первичного ключа или главной таблицей, а другую таблицей внешнего ключа или подчиненной таблицей. Существует три типа первичных ключей: ключевые поля счетчика (счетчик), простой ключ и составной ключ. Поле счетчика. Тип данных поля в базе данных, в котором для каждой добавляемой в таблицу записи в поле автоматически заносится уникальное числовое значение. Простой ключ. Если поле содержит уникальные значения, такие как коды или инвентарные номера, то это поле можно определить как первичный ключ. В качестве ключа можно определить любое поле, содержащее данные, если это поле не содержит повторяющиеся значения или значения Null. Составной ключ. В случаях, когда невозможно гарантировать уникальность значений каждого поля, существует возможность создать ключ, состоящий из нескольких полей. Чаще всего такая ситуация возникает для таблицы, используемой для связывания двух таблиц многие-ко-многим [1]. 9

4.1 Набор необходимых отношений базы данных Разработанная база данных состоит из 11 отношений. Ниже в таблицах 4.1–4.11 приведены составы этих отношений. Таблица 4.1 – Готовые лекарства № 1 2 3 4 5

Имя поля Номер лекарства Номер на складе Наименование Форма Стоимость

Тип данных INT INT VARCHAR(50) VARCHAR(20) INT

Тип ключа Первичный Внешний

Таблица 4.2 – Изготовляемые лекарства № 1 2 3 4 5

Имя поля Номер лекарства Номер на складе Наименование Форма Стоимость

Тип данных INT INT VARCHAR(50) VARCHAR(20) INT

Тип ключа Первичный Внешний

Тип данных INT INT VARCHAR(50) VARCHAR(20) INT

Тип ключа Первичный Внешний

Таблица 4.3 – Компоненты № 1 2 3 4 5

Имя поля Номер лекарства Номер на складе Наименование Форма Стоимость

Таблица 4.4 – Справочник технологий № 1 2 3 4

Имя поля Номер технологии Номер лекарства Способ приготовления Время приготовления, часов

Тип данных INT INT VARCHAR(MAX) INT

Тип ключа Первичный Внешний

Тип данных INT

Тип ключа Первичный

Таблица 4.5 – Склад № 1

Имя поля Номер товара

10

2 3

Количество Критическая норма

INT INT

Таблица 4.6 – Статистика № 1 2 3

Имя поля Номер товара Дата Продано

Тип данных INT DATE INT

Тип ключа Первичный Первичный

Тип данных INT VARCHAR(20) VARCHAR(20) VARCHAR(20) DATE VARCHAR(50) VARCHAR(25) VARCHAR(20)

Тип ключа Первичный

Тип данных INT INT VARCHAR(60) VARCHAR(20)

Тип ключа Первичный Внешний

Тип данных INT INT DATE DATE

Тип ключа Первичный Внешний

Таблица 4.7 – Клиенты № 1 2 3 4 5 6 7 8

Имя поля Номер клиента Фамилия Имя Отчество Дата рождения Адрес Телефон Категория

Таблица 4.8 – Рецепты № 1 2 3 4

Имя поля Номер рецепта Номер клиента ФИО врача Диагноз

Таблица 4.9 – Заказы № 1 2 3 4

Имя поля Номер заказа Номер рецепта Дата приёма Дата изготовления

Продолжение таблицы 4.9 5 6 7

Дата выдачи Стоимость Статус

DATE INT VARCHAR(15)

Таблица 4.10 – Назначения 11

№ 1 2 3

Имя поля Номер рецепта Номер лекарства Количество

Тип данных INT INT INT

Тип ключа Первичный Первичный

Тип данных INT INT INT

Тип ключа Первичный Первичный

Таблица 4.11 – Состав № 1 2 3

Имя поля Номер лекарства Номер ингредиента Количество, мг

4.2 Нормализация Нормализация предназначена для приведения структуры базы данных к виду, обеспечивающему минимальную избыточность, то есть нормализация не имеет целью уменьшение или увеличение производительности работы или же уменьшение или увеличение объёма БД. Конечной целью нормализации является уменьшение потенциальной противоречивости хранимой в БД информации [5]. Процесс нормализации основан на концепции нормальных форм. Отношение находится в некоторой нормальной форме, если оно удовлетворяет всем её ограничениям. Существует множество нормальных форм, но для наших целей достаточно рассмотреть три из них: первую, вторую и третью. Отношение входит в первую нормальную форму, если его атрибуты содержат только скалярные значения (если оно представимо в виде плоской двумерной таблицы). Любое отношение входит в первую нормальную форму по умолчанию, поскольку реляционная модель данных требует, чтобы значения всех атрибутов были только скалярными. Отношение входит во вторую нормальную форму, если оно находится в первой нормальной форме, и нет неключевых атрибутов, функционально зависящих от части сложного первичного ключа. Если первичный ключ простой, то отношение автоматически входит во вторую нормальную форму. В случае выявления каких-либо аномалий производится декомпозиция. В построенной выше логической модели все отношения либо имеют простой ключ, либо не имеют функционально зависимых от части ключа атрибутов. Следовательно, наша БД удовлетворяет требованиям второй 12

нормальной формы. И, наконец, отношение входит в третью нормальную форму, если оно находится во второй нормальной форме, и все его неключевые атрибуты взаимно независимы, то есть, не связаны функциональной зависимостью. Если такая зависимость между неключевыми атрибутами есть, то снова производится декомпозиция: все зависимые атрибуты выносятся в отдельное отношение. В отношениях рассматриваемой нами БД нет функциональных зависимостей между неключевыми атрибутами, следовательно, она попадает в третью нормальную форму.

13

5 ПОСТРОЕНИЕ СХЕМЫ ДАННЫХ Для построения базы данных используется система управления базами данных Microsoft Access 2003. Выбор СУБД Access объясняется тем, что она входит в состав пакета прикладных программ Microsoft Office, установленного практически на всех персональных компьютерах. Сначала необходимо создать новую базу данных, затем в режиме конструктора создать все таблицы, соответствующие отношениям, определенным ранее в пункте 4.1. После создания всех таблиц необходимо установить между ними связи. В Microsoft Access 2003 это делается предельно просто: создаётся новая схема данных, на неё добавляются все таблицы БД, после чего связь устанавливается путём перетаскивания поля-источника данных из родительской таблицы на поле в дочерней. Тип данных обоих полей должен совпадать, одно из полей (поле-источник) должно быть ключевым. После установления всех связей в соответствии с логической моделью БД, мы получим схему данных, представленную на рисунке 5.1.

Рисунок 5.1 – Схема данных Теперь созданные таблицы необходимо заполнить, причём начинать заполнение данными нужно с тех таблиц, которые являются источниками данных для других. После заполнения всех таблиц данными можно приступить к последней части данной работы - реализации информационных запросов к БД. 14

6 ПРОЕКТИРОВАНИЕ И РЕАЛИЗАЦИЯ ЗАПРОСОВ К БД 1. Получить сведения о покупателях, которые не пришли забрать свой заказ в назначенное им время. а) Запрос на получение сведений о клиентах (результаты представлены на рисунке 6.1): SELECT Клиенты.[Номер клиента], Клиенты.Фамилия, Клиенты.Имя, Клиенты.Отчество, Клиенты.Телефон, Заказы.[Номер заказа], Заказы.[Дата изготовления] FROM (Клиенты INNER JOIN Рецепты ON Клиенты.[Номер клиента] = Рецепты.[Номер клиента]) INNER JOIN Заказы ON Рецепты.[Номер рецепта] = Заказы.[Номер рецепта] WHERE (((Заказы.[Дата изготовления]) < Date()) AND ((Заказы.Статус)="В процессе"));

Рисунок 6.1 – Результат выполнения запроса 1а б) Количество клиентов можно получить, выполнив следующий запрос: SELECT COUNT(*) AS Количество FROM (Клиенты INNER JOIN Рецепты ON Клиенты.[Номер клиента] = Рецепты.[Номер клиента]) INNER JOIN Заказы ON Рецепты.[Номер рецепта] = Заказы.[Номер рецепта] WHERE (((Заказы.[Дата изготовления]) < Date()) AND ((Заказы.Статус) = "В процессе"));

2. Получить перечень покупателей, которые ждут прибытия на склад нужных им медикаментов в целом и по указанной категории медикаментов. а) Запрос по всем медикаментам (результаты представлены на рисунке 6.2): SELECT Клиенты.[Номер клиента], Клиенты.Фамилия, Клиенты.Имя, Клиенты.Отчество, Заказы.[Номер заказа], [Изготовляемые лекарства].[Номер лекарства], [Изготовляемые лекарства].Наименование, [Изготовляемые лекарства].Форма, Склад.Количество, Назначения.Количество AS Нужно FROM Склад INNER JOIN (((Клиенты INNER JOIN Рецепты ON Клиенты. [Номер клиента] = Рецепты.[Номер клиента]) INNER JOIN Заказы ON Рецепты. [Номер рецепта] = Заказы.[Номер рецепта]) INNER JOIN ([Изготовляемые лекарства] INNER JOIN Назначения ON [Изготовляемые лекарства].[Номер 15

лекарства] = Назначения.[Номер лекарства]) ON Рецепты.[Номер рецепта] = Назначения.[Номер рецепта]) ON Склад.[Номер товара] = [Изготовляемые лекарства].[Номер на складе] WHERE (((Заказы.Статус)="Ожидание"));

Рисунок 6.2 – Результат выполнения запроса 2а б) Параметрический запрос по медикаментам указанной категории: SELECT Клиенты.[Номер клиента], Клиенты.Фамилия, Клиенты.Имя, Клиенты.Отчество, Заказы.[Номер заказа], [Изготовляемые лекарства].[Номер лекарства], [Изготовляемые лекарства].Наименование, [Изготовляемые лекарства].Форма, Склад.Количество, Назначения.Количество AS Нужно FROM Склад INNER JOIN (((Клиенты INNER JOIN Рецепты ON Клиенты. [Номер клиента] = Рецепты.[Номер клиента]) INNER JOIN Заказы ON Рецепты. [Номер рецепта] = Заказы.[Номер рецепта]) INNER JOIN ([Изгото...