Базы данных
и язык SQL

Основы проектирования базы данных - пример

Навигация по уроку Связанные темы
Назад<<<СодержаниеВперёд>>>

Этапы проектирования базы данных

Этап начальной разработки

  • анализ деятельности компании;
  • анализ структуры компании;
  • спецификация требований;
  • определение целей;
  • сферы применения;
  • границы возможностей.

Концептуальное проектирование базы данных

  • анализ требований к базе данных, выявление представлений конечных пользователей и требований к обработке транзакций;
  • определение сущностей, атрибутов и связей;
  • разработка ER-диаграмм (от ER - Entity-Relationship - Сущность-Связь);
  • нормализация;
  • проверка модели данных, выявление основных процессов (правила ввода, обновления и удаления данных);
  • проверка отчётов, запросов, представлений, целостности, совместного использования и безопасности.

Анализ предметной области

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

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

В каждой из аптек регистрируется покупка каждой единицы препарата и все покупки, совершённые одним клиентом в одной аптеки в один момент времени, прикрепляются к корзине покупок.

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

Требуемыми функциями системы, использующей базу данных, являются:

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

Разработка модели базы данных

В процессе проектирования базы данных выделены следующие сущности:

  • Аптека (Pharmacy);
  • Группа препаратов(Group);
  • Наличие (Availability);
  • Дефицит (Deficit);
  • Сотрудник (Employee);
  • Клиент (Client);
  • Корзина (Basket);
  • Покупка (Buying).

На рисунке ниже приведено представление модели нашей базы данных с атрибутами сущностей (таблиц) и связями между таблицами. Для увеличения рисунка можно нажать на него левой кнопкой мыши. О том, какие атрибуты имеются у сущностей - в статье Создание базы данных, в которой приведены и команды языка SQL для создания БД и таблиц в ней. На рисунке атрибуты прописаны внутри прямоугольников, отображающих сущности. А уйти ещё глубже в теорию реляционных баз данных можно на уроке Реляционная модель данных.

Опишем отношения между сущностями.

Сущность «Аптека» связана отношением «Имеет» «один ко многим» с сущностью «Наличие», отношением «Имеет» «один ко многим» с сущностью «Дефицит», отношением «Имеет» «один ко многим» с сущностью «Покупка», отношением «Работает» «один ко многим» с сущностью «Сотрудник».

Сущности «Наличие» и «Дефицит» связаны отношениями «Включает» «многое к одному» с сущностью «Препарат».

Сущность «Препарат» кроме упомянутых связей связано отношением «Включает» «многое к одному» с сущностью «Группа» и отношением «Включает» «один ко многим» с сущностью «Покупка».

Сущность «Покупка» кроме упомянутых связей связана отношением «Включает» «многое к одному» с сущностью «Корзина».

Сущность «Сотрудник» кроме упомянутой связи с сущностью «Аптека» связан отношением «Регистрирует» «один ко многим» с сущностью «Корзина».

Сущность «Клиент» связан отношением «Имеет» «один ко многим» с сущностью «Корзина».

Анализ нормальных форм

В базе данных создаваемой информационной системы все таблицы должны находиться в третьей нормальной форме.

Все 9 таблиц базы данных находятся по меньшей мере в первой нормальной форме, так как:

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

Чтобы избежать невыполнения требований второй нормальной формы в таблице AVAILABILITY, создан первичный ключ этой таблицы, а ключи, связывающую эту таблицу с таблицами PHARMACY и PREPARATION, сделаны внешними ключами, сочетание значений которых должно быть уникальным. Таким образом, невозможна функциональная зависимость неключевого атрибута от части первичного ключа – идентификатора аптеки или идентификатора препарата.

Следовательно, все таблицы находятся и во второй нормальной форме.

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

Запросы к базе данных и отчёты

Запросы:

  • доступность выбираемого в селекторе препарата в выбираемой в селекторе аптеке на данный момент времени;
  • группы препаратов, которых нет в выбираемой в селекторе аптеке на данный момент времени;
  • аптеки, в которых есть дефицит любого препарата по введённой дате;
  • покупки препаратов, которые есть на витринах аптек по введённой дате с указанием препаратов и аптек;
  • клиенты, совершившие покупки в выбранной в селекторе аптеке по введённой дате.

Отчёты:

  • о количестве покупок со списком аптек по введённой дате с указанием сумм;
  • о продажах выбранного препарата во всех аптеках по введённой дате;
  • о проданных препаратах стоимостью более 150 рублей по введённой дате;
  • о препаратах, которые есть на витрине во всех аптеках;
  • о клиентах, зарегистрированных в определённый интервал времени.

Поделиться с друзьями

Назад<<<СодержаниеВперёд>>>