Проектирование баз данных

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

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

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

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

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

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

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

Физическое проектирование БД

На следующем этапе физического проектирования БД логическая структура отображается в виде структуры хранения БД, то есть увязывается с такой физической средой хранения, где данные будут размещены максимально эффективно. Здесь детально расписывается схема данных с указанием всех типов, полей, размеров и ограничений. Помимо разработки индексов и таблиц, производится определение основных запросов.

Построение физической модели сопряжено с решением во многом противоречивых задач:

1. задачи минимизации места хранения данных,
2. задачи достижения целостности, безопасности и максимальной производительности.

Вторая задача вступает в конфликт с первой, поскольку, например:

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

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

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

Современная база данных

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

Таблицы Excel — ничем не отличаются от Oracle и MySQL в контексте прямоугольных (реляционных) конструкций: столбцы и строки = одна ячейка на пересечении имени столбца (поля) и индекса выборки (строка). Если не учитывать меру и объем ручного труда, то, благодаря развитым средствам объединения ячеек по вертикали и горизонтали, Excel опережает даже Oracle!

Excel, по его основной идее, никогда «не светит» динамика, функционал Oracle, и перенести что-то с одного листа на другой «по остаткам» он не может. Здесь Oracle перспективнее, но ее соображения по вопросам миграции больших объемов информации и объединению формализованных позиций из различных источников оставляют желать лучшего. Здесь MySQL перспективнее: она не ставит перед собой глобальных задач, но свою работу исполняет отменно.

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

Современная БД — это таблицы, строки, столбцы и индексы, окруженные полным функционалом, развитыми дополнительными средствами, учитывающими множественные операции, большие нагрузки и огромные объемы.

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

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

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

2.1. Этапы проектирования бд

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

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

Проектирование
базы данных состоит в построении
комплекса взаимосвязанных данных. На
рисунке 1 условно отображены этапы
процесса проектирования базы данных.

Рис.1 — Этапы процесса
проектирования базы данных

Процесс
проектирования Базы данных начинается
с постановки за­дачи и выявления
объектов, процессов или сущностей
предметной об­ласти. Например, объектами
могут быть Институт, Сотрудники. Для
каждого из объектов выбирается набор
характеризующих его свойств (полей,
реквизитов). Для института – наименование,
адрес, расчетный счет и пр., для сотрудника
– фамилия, имя, отчество, адрес, паспортные
данные, пр. Затем в про­цессе анализа
определяется информационная потреб­ность
каждой за­дачи, которую составляют
входные и результатные до­кументы, и
опре­деляется периодичность решения
задач.

Работа
проектировщиков Базы данных в значительной
степени зави­сит от качества
инфологической модели. Инфологическая
модель соз­дается для того, чтобы
на ее основе можно было построить
модель дан­ных, т. е. она должна
учитывать особенности реализации
выбранной СУБД. На основе инфологической
модели строятся концептуаль­ная,
ло­гическая и физическая модели.
Отсюда вытекают основные этапы, на
которые разбивается процесс проектирования
базы данных информаци­онной системы.

Концептуальное
проектирование
– сбор, анализ и редактирование требований
к данным. Для этого осуществляются
следующие мероприя­тия:

—
обследование предметной области,
изучение ее информационной структуры;

—
выявление всех фрагментов, каждый из
которых характеризуется пользовательским
представлением, информационными
объектами, свя­зями между ними и
процессами;

·-
моделирование и интеграция всех
представлений.

Результат
данного этапа – концептуальная модель,
ин­вариантная к структуре Базы данных,
часто представляется в виде модели
«сущ­ность-связь».

Логическое
проектирование– преобразование
требований к данным в структуры данных.
Результат – СУБД-ориентированная
структура Базы данных и спецификации
прикладных программ. На этом этапе часто
мо­делируют Базы данных применительно
к различным СУБД и проводят сравнительный
анализ моделей.

Физическое
проектирование
– определение особенностей хранения
данных, методов доступа и т. д. Физическая
база данных нужна разработчикам
информационной сис­темы для разработки
кода, а проектировщикам – для проверки
их идей. Проектировщики и разработчики
могут работать как с одной и той же
схемой, так и с разными схемами. В процессе
разработки проекта, как правило, создается
несколько версий схемы.

Построение
логической и физиче­ской моделей
данных является основной частью
проектирования Базы данных.

Инфологическое проектирование

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

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

Инфологическую модель можно создавать с помощью нескольких методов и подходов:

1. Функциональный подход отталкивается от поставленных задач. Функциональным он называется, потому что применяется, если известны функции и задачи лиц, которые с помощью проектируемой базы данных будут обслуживать свои информационные потребности.
2. Предметный подход во главу угла ставит сведения об информации, которая будет содержаться в базе данных, при том, что структура запросов может не быть определена. В этом случае в исследованиях предметной области ориентируются на её максимально адекватное отображение в базе данных в контексте полного спектра предполагаемых информационных запросов.
3. Комплексный подход по методу «сущность-связь» объединяет достоинства двух предыдущих. Метод сводится к разделению всей предметной области на локальные части, которые моделируются по отдельности, а затем вновь объединяются в цельную область.

Поскольку использование метода «сущность-связь» является комбинированным способом проектирования на данном этапе, он чаще других становится приоритетным.

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

Зависимость сущностей отражается в разделении их на сильные (базовые, родительские) и слабые (дочерние). Сильная сущность (например, читатель в библиотеке) может существовать в БД сама по себе, а слабая сущность (например, абонемент этого читателя) «привязывается» к сильной и отдельно не существует.

Для каждой отдельной сущности выбираются атрибуты (набор свойств), которые в зависимости от критерия могут быть:

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

После этого производится спецификация атрибута, спецификация связей в локальном представлении (с разделением на факультативные и обязательные) и объединение локальных представлений.При числе локальных областей до 4-5 их можно объединить за один шаг. В случае увеличения числа, бинарное объединение областей происходит в несколько этапов.

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

Логическое проектирование БД

Замечание 4

Вторая фаза проектирования БД заключается в создании логической модели данных для исследуемой части организации.

Определение 1

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

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

Замечание 5

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

Основные элементы реляционных баз данных.

Одним
из основных типов баз данных является
реляционная – т.е. представление данных
в виде таблицы (практически все типы
баз данных можно представить в виде
таблицы)

В
любой таблице можно выделить такие
элементы как записи и поля.

Свойства
полей базы данных.

Поля
базы данных не просто определяют
структуру базы — они еще определяют
групповые свойства данных, записываемых
в ячейки, принадлежащие каждому из
полей. Ниже перечислены основные свойства
полей таблиц баз данных на примере СУБД
Microsoft Access.

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

Тип
поля — определяет тип данных, которые
могут содержаться в данном поле.

• Размер
поля — определяет предельную длину (в
символах) данных, которые могут размещаться
в данном поле.

• Формат
поля — определяет способ форматирования
данных в ячейках, принадлежащих полю.

• Маска
ввода — определяет форму, в которой
вводятся данные в поле (средство
автоматизации ввода данных).

• Подпись
— определяет заголовок столбца таблицы
для данного поля (если подпись не указана,
то в качестве заголовка столбца
используется свой-ство Имя поля).

• Значение
по умолчанию — то значение, которое
вводится в ячейки поля автоматически
(средство автоматизации ввода данных).

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

• Сообщение
об ошибке — текстовое сообщение, которое
выдается автоматически при попытке
ввода в поле ошибочных данных (проверка
ошибочности выполняется автоматически,
если задано свойство Условие на значение).

• Обязательное
поле — свойство, определяющее
обязательность заполнения данного поля
при наполнении базы;

• Пустые
строки — свойство, разрешающее ввод
пустых строковых данных (от свойства
Обязательное поле отличается тем, что
относится не ко всем типам данных, а
лишь к некоторым, например к текстовым).

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

Связи и виды связей

На пятом этапе проектирования необходимо спроектировать связи между таблицами. Связи моделируют отношение между сущностями предметной области.

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

Проблема лишь в том, что при наличии двух таблиц можно перенести ключевые поля как минимум двумя способами: из таблицы «Товар» в таблицу «Поставщики» или наоборот — из таблицы «Поставщики» в таблицу «Товар». Эти варианты неравноценны! Для выбора правильного варианта нужно воспользоваться ограничениями предметной области, описанными на третьем этапе проектирования.

Попробуем добавить поле «Код товара» в таблицу «Поставщики» и показать, что поставщик «Ашан» поставляет оба вида молока и сыр.

Замечание 3

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

Второй способ тоже приведет к нарушению 1 н.ф.

Этот способ тоже называется «один-ко-многим», но он соответствует другой ситуации: один поставщик может поставлять много товаров, но один товар поставляется одним поставщиком. И этот вариант тоже не соответствует нашим ограничениям предметной области.

Остается еще один способ построения связи: вынести ключевые поля двух таблиц в третью таблицу. Такой вид связи называется «много-ко-многим». В результате появится вот такая таблица:

Здесь уже не нарушается 1 н.ф. и полностью выполняются ограничения предметной области. Если хорошо подумать, то становится видно, что полученная таблица соответствует сущности «документ о поставке». Результатом проектирования становится схема логической модели:

Литература

• Дейт К. Дж. Введение в системы баз данных = Introduction to Database Systems. — 8-е изд. — М.: «Вильямс», 2006. — 1328 с. — ISBN 0-321-19784-4.
• Когаловский М.Р. Перспективные технологии информационных систем. — М.: ДМК Пресс; Компания АйТи, 2003. — 288 с. — ISBN 5-279-02276-4.
• Когаловский М.Р. Энциклопедия технологий баз данных. — М.: Финансы и статистика, 2002. — 800 с. — ISBN 5-279-02276-4.
• Кузнецов С. Д. Основы баз данных. — 2-е изд. — М.: Интернет-Университет Информационных Технологий; БИНОМ. Лаборатория знаний, 2007. — 484 с. — ISBN 978-5-94774-736-2.
• Коннолли Т., Бегг К. Базы данных. Проектирование, реализация и сопровождение. Теория и практика = Database Systems: A Practical Approach to Design, Implementation, and Management. — 3-е изд. — М.: «Вильямс», 2003. — 1436 с. — ISBN 0-201-70857-4.
• Гарсиа-Молина Г., Ульман Дж., Уидом Дж. Системы баз данных. Полный курс. — М.: «Вильямс», 2003. — 1088 с. — ISBN 5-8459-0384-X.

4.1 Основные цели и этапы проектирования баз данных

Терминология
уровней описания данных и сути
проектирования БД приведена в п. 1.1.3.

Основные цели
проектирования баз данных (ПБД) следующие:

• обеспечение
  хранения в БД всех необходимых данных;

• обеспечение
  получения данных по всем необходимым
  запросам;

• сокращение
  избыточности и дублирования данных;

• обеспечение
  целостности данных: исключение потери
  данных, противоречий в содержании БД,
  нарушений смысла данных;

• сокращение
  времени доступа к данным и получения
  данных по запросам.

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

Концептуальное
(инфологическое) проектирование
– анализ предметной области и ее
описание. Этот этап осуществляется без
ориентации на какие-либо конкретные
программные или технические средства.
Результатом данного этапа является
формализованная модель предметной
области, построенная с использованием
специальных языковых средств (обычно
– графических). Инфологическая модель,
как правило, включает следующие основные
элементы:

• описание объектов
  предметной области;

• описание атрибутов
  (свойств) объектов;

• описание связей
  между объектами.

Пусть,
например, проектируется база данных о
промышленном предприятии. Объектами
предметной области в данном случае
могут являться работники предприятия,
его подразделения, другие предприятия
– поставщики сырья или потребители
продукции, заключенные договоры, виды
выпускаемой продукции, конкретные
выпущенные изделия и т.д. Эти объекты
имеют свойства (атрибуты) – фамилия,
адрес и профессия работника, стоимость
изделия, объем поставок по договору и
т.д. Наконец, объекты находятся в различных
связях друг с другом: работник работает
в некотором подразделении, подразделения
выпускают продукцию, продукция
поставляется по договорам другим
предприятиям и т.д.

Для
описания объектов предметной области,
их атрибутов и связей между ними обычно
применяются стандартизированные системы
графических обозначений. Чаще всего
применяются ER-модели
(ER-диаграммы),
подробно рассматриваемые в разделе 2,
и семантические объектные модели
(COM-модели),
рассматриваемые в .

Кроме того,
инфологическая модель может включать:

• описание основных
  запросов к проектируемой БД;

• описание
  документооборота, т.е. документов,
  используемых в качестве источников
  данных для БД или составляемых на основе
  БД;

• описание
  алгоритмических связей между данными
  (например, алгоритмы и формулы для
  вычисления каких-либо величин, хранящихся
  в БД или определяемых на основе БД);

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

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

• описание таблиц;

• описание связей
  между таблицами;

• описание атрибутов.

Физическое
проектирование
– описание физической структуры БД,
т.е. ее размещения на запоминающем
устройстве. Такое описание называется
физической моделью. Физическая модель
включает:

• тип носителя;

• способы
  организации данных;

• способы управления
  свободной памятью;

• способы сжатия
  данных и т.д.

Этот
этап, как правило, в основным скрыт от
проектировщика БД, так как реализуется
средствами СУБД. Элементы физического
проектирования рассматривались выше
(п. 2.9) и далее этот этап не рассматривается.

Примечание — При
использовании систем автоматизированного
проектирования БД этап инфологического
проектирования обычно называют логическим
проектированием, а этапы даталогического
и физического проектирования – физическим
проектированием.