Руководство по разработке структуры и проектированию базы данных

Содержание проектирования баз данных и этапность

Замысел проектирования основывается на какой-либо сформулированной общественной потребности. У этой потребности есть среда её возникновения и целевая аудитория потребителей, которые будут пользоваться результатом проектирования. Следовательно, процесс проектирования баз данных начинается с изучения данной потребности с точки зрения потребителей и функциональной среды её предполагаемого размещения. То есть, первым этапом становится сбор информации и определение модели предметной области системы, а также – взгляда на неё с точки зрения целевой аудитории. В целом, для определения требований к системе производится определение диапазона действий, а также границ приложений БД.

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

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

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

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

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

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

Таким образом, основные этапы проектирования в детализированном виде представлены этапами:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Нормальные формы

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

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

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

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

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

• Значение поля не пусто для любой записи.
• Значение поля уникально для любой записи так, что по этому значению можно однозначно определить запись.

Такое поле называется ключевым полем таблицы или первичным ключом таблицы.

Замечание 2

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

Для рассматриваемого примера о товарах и поставщиках нужно спроектировать две таблицы:

Таблица «Товары»

Таблица «Поставщики»

2.4. Microsoft Access 2007

2.4.5. Создание запросов и поиск информации в базе данных

В СУБД Access 2007 можно создавать queries для отображения требуемых полей из записей одной или нескольких таблиц.

В СУБД Access 2007 применяются различные типы запросов: на выборку, на обновление, на добавление, на удаление, перекрестный query, выполнение вычислений, создание таблиц. Наиболее распространенным является query на выборку. Применяются два типа запросов: query по образцу (QBE) и query на основе структурированного языка запросов (SQL).

Запросы на выборку используются для отбора требуемой пользователю информации, содержащейся в нескольких таблицах. Они создаются только для связанных таблиц. Queries могут основываться как на нескольких таблицах, так и существующих запросах. СУБД Access 2007 включает такие средства создания запросов, как Мастер и Конструктор.

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

На скриншоте (рисунок 1) средства сортировки и фильтрации выделены скругленным прямоугольником красного цвета.

Рис. 1.

Рассмотрим создание запроса на выборку с помощью Конструктора

Для создания нового пустого запроса в режиме конструктора надо щелкнуть на пиктограмме Конструктор запросов (рисунок 2).

Рис. 2.

Откроется активное окно диалога Добавление таблицы (рисунок 3) на фоне неактивного окна «Запрос1». В этом окне можно выбрать таблицы и queries для создания новых запросов.

Рис. 3.

В окне Добавление таблицы следует выбрать несколько таблиц из представленного списка таблиц, на основе которых будет проводиться выбор данных, и щелкнуть на кнопке Добавить. После этого закрыть окно Добавление таблицы, а окно «Запрос1» станет активным (рисунок 4).

Рис. 4.

Окно Конструктора состоит из двух частей – верхней и нижней. В верхней части окна размещается схема данных запроса, которая содержит список связанных таблиц. В нижней части окна находится Бланк построения запроса QBE, в котором каждая строка выполняет определенную функцию.

Переместим имена полей с таблиц-источников в Бланк. Из таблицы Группы студентов переместим поле Название в первое поле Бланка, из таблицы Студенты переместим поле Фамилии во второе поле, а из таблицы Успеваемость переместим поле Оценка в третье поле и из таблицы Дисциплины переместим поле Название в четвертое поле Бланка запросов.

При необходимости можно задать принцип сортировки (по возрастанию или по убыванию) результатов запроса. В строке «Вывод на экран» автоматически устанавливается флажок просмотра информации.

Условия ограниченного поиска или критерий поиска информации вводится в строке «Условия» отбора и строке «Или». Например, введем критерий поиска — «5/A» в строке «Условия» для поля Оценка. В этом случае в результате выполнения запроса на экране будут отображаться все фамилии студентов, которые получили оценку 5/A (рисунок. 5).

Рис. 5.

Далее надо закрыть окно запроса Запрос1, появится окно диалога Сохранить, ответить — Да и ввести имя запроса, например «Успеваемость студентов». Для запуска запроса дважды щелкнем на query «Успеваемость студентов», откроется таблица с результатами выполненного запроса (рис. 6).

Рис. 6.

Далее создаем параметрический query или query с параметрами. Создаем этот query также как и предыдущий, в режиме конструктора, но только в строке Условия отбора для поля Фамилия введем условие отбора в виде приглашения в квадратных скобках, например . В этом случае в результате выполнения запроса на экране будет отображаться фамилия студента и все дисциплины, по которым он получил оценку.

Закрыть окно запроса на выборку. На вопрос о сохранении изменения ответить — Да и ввести имя запроса, например «Параметрический query». Запустим Параметрический query, дважды щелкнув на нем. В открывшемся на экране окне диалога «Введите значение параметра» надо ввести фамилию студента, информацию об успеваемости которого необходимо получить (рис. 8).

Рис. 7.

Затем надо щелкнуть на кнопке ОК, откроется таблица с результатами выполненного запроса (рис. 8).

Рис. 8.

В некоторых случаях для создания запросов можно использовать Мастер запросов. После создания запросов на выборку информации из БД Access 2007 можно приступать к формированию форм.

Далее >>> Раздел: 2.4.6. Создание форм для ввода данных в таблицы базы данных Access 2007

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

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

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

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

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

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

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

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

Краткие сведения о субд Access

База
данных в Access
представляет собой единый большой
объект, который объединяет такие
составляющие, как таблицы, отчеты,
запросы, формы и т.д. и позволяет хранить
их в одном файле.

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

Каждый объект и
элемент управления имеет свои свойства,
определяя которые можно настраивать
объекта и элементы управления.

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

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

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

Отчеты
предназначены для формирования выходного
документа, предназначенного для вывода
на печать.

Варианты создания баз данных в Microsoft Access

Пустую базу данных можно создать в проводнике Windows, щелкнув правой кнопкой на пустом пространстве и выбрав «Создать… Базу данных Microsoft Access». Появится пустой файл (ему сразу же можно присвоить подходящее имя), который следует открыть двойным щелчком. Однако в современных версиях программы предусмотрены более эффективные способы выполнения этого действия.

Работа с шаблонами

Можно создать новую БД:

• из стандартного шаблона: несколько готовых шаблонов поставляются вместе с программой;
• из шаблона с сайта Office.com: там имеется обширная коллекция заготовок для баз данных различного назначения; их можно загружать по сети с помощью меню (вкладка «Создать»).

Рисунок 1. Варианты создания новой базы данных в MS Access. Автор24 — интернет-биржа студенческих работ

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

Шаблоны, как и пустые базы данных, можно редактировать: добавлять и изменять таблицы, формы и другие объекты.

Замечание 2

С сайта Office.com можно загружать не только целые шаблоны, но и так называемые «части приложения» — связанные между собой компоненты, например, таблица и построенная на ее основе форма.

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

Создание пустой базы данных

Для создания пустой БД следует на вкладке «Файл» выбрать пункт «Создать» и вариант «Пустая база данных», затем указать имя файла. Access создаст БД с незаполненной таблицей «Таблица1». Она сразу же будет открыта в режиме таблицы. Для добавления данных нужно просто вводить их, отложив оптимизацию структуры колонок. Процесс ввода данных похож на работу с листом Excel.

Замечание 3

Файл Blank.accdb в папке :\Program Files\Microsoft Office\Templates\1049\Access. используется как шаблон для всех новых локальных пустых баз данных. Все новые базы данных наследуют содержимое этого файла. Это отличный способ распространения содержимого по умолчанию.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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