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

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

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

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

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

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

Специального обсуждения заслуживает процедура управления БД . Она наиболее проста в однопользовательском режиме. В многопользовательском режиме и в распределенных БД процедура сильно усложняется. При одновременном доступе нескольких пользователей без принятия специальных мер, возможно, нарушение целостности . Для устранения этого явления используют систему транзакций и режим блокировки таблиц или отдельных записей.

Транзакция - процесс изменения файла, записи или базы данных , вызванный передачей одного входного сообщения.

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

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

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

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

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

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

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

Основными причинами низкой эффективности проектируемых БД могут быть:

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

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

Основные этапы разработки БД

Этап 1. Уточнение задач

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

Этап 2. Последовательность выполнения задач

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

Этап 3. Анализ данных

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

Этап 4. Определение структуры данных

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

Этап 5. Разработка макета приложения и пользовательского интерфейса

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

Этап 6. Создание приложения

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

Этап 7. Тестирование и усовершенствование

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

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

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

Основные понятия о базах данных и СУБД

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

База данных – это ИС, которая хранится в электронном виде.

База данных (БД) – организованная совокупность данных, предназначенная для длительного хранения во внешней памяти ЭВМ, постоянного обновления и использования.

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

Классификация баз данных:

1. По характеру хранимой информации:

- Фактографические – содержат краткие сведения об описываемых объектах, представленных в строго определённом формате (картотеки, н-р: БД книжного фонда библиотеки, БД кадрового состава учреждения),

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

2. По способу хранения данных:

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

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

3. По структуре организации данных:

- Реляционные (табличные),

- Нереляционные.

Термин «реляционный» (от лат. relatio – отношение) указывает на то, что такая модель хранения данных построена на взаимоотношении составляющих её частей. Реляционная база данных, по сути, представляет собой двумерную таблицу . Каждая строка такой таблицы называется записью. Столбцы таблицы называются полями: каждое поле характеризуется своим именем и топом данных. Поле БД – это столбец таблицы, содержащий значения определенного свойства.

Свойства реляционной модели данных:

Каждый элемент таблицы – один элемент данных;

Все поля таблицы являются однородными, т.е. имеют один тип;

Одинаковые записи в таблице отсутствуют;

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

Иерархической называется БД, в которой информация упорядоченная следующим образом: один элемент считается главным, остальные – подчинёнными. В иерархической базе данных записи упорядочиваются в определенную последовательность, как ступеньки лестницы, и поиск данных может осуществляться последовательным «спуском» со ступени на ступень. Данная модель характеризуется такими параметрами, как уровни, узлы, связи. Принцип работы модели таков, что несколько узлов более низкого уровня соединяются при помощи связи с одним узлом более высокого уровня.

Узел – информационная модель элемента, находящегося на данном уровне иерархии.

Свойства иерархической модели данных:

Несколько узлов низшего уровня связано только с одним узлом высшего уровня;

Иерархическое дерево имеет только одну вершину (корень), не подчинено никакой другой вершине;

Каждый узел имеет своё имя (идентификатор);

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

Иерархической базой данных является Каталог папок Windows, с которым можно работать, запустив Проводник. Верхний уровень занимает папка Рабочий стол. На втором уровне находятся папки Мой компьютер, Мои документы, Сетевое окружение и Корзина, которые представляют собой потомков папки Рабочий стол, будучи между собой близнецами. В свою очередь, папка Мой компьютер – предок по отношению к папкам третьего уровня, папкам дисков (Диск 3,5(А:), С:, D:, E:, F:) и системным папкам (Принтеры, Панель управления и др.).

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

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

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

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

Основные действия, которые пользователь может выполнять с помощью СУБД:

Создание структуры БД;

Заполнение БД информацией;

Изменение (редактирование) структуры и содержания БД;

Поиск информации в БД;

Сортировка данных;

Защита БД;

Проверка целостности БД.

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

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

Популярные СУБД - FoxPro, Access for Windows, Paradox.

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

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

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

- Минимальные затраты. Низкая стоимость хранения и использования данных, минимизация затрат на внесение изменений.

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

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

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

- Целостность . Современные базы данных могут содержать данные, используемые многими пользователями. Очень важно, чтобы в процессе работы элементы данных и связи между ними не нарушались. Кроме того, аппаратные ошибки и различного рода случайные сбои не должны приводить к необратимым потерям данных. Значит, система управления данными должна содержать механизм восстановления данных.

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

Далее на примере одной из самых распространенных систем управления базами данных – Microsoft Access входит в состав популярного пакета Microsoft Office – мы познакомимся с основными типами данных, способами создания баз данных и с приемами работы с базами данных.

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

Как и любой программный продукт, база данных обладает собственным жизненным циклом (ЖЦБД). Главной составляющей в жизненном цикле БД является создание единой базы данных и программ, необходимых для ее работы.

ЖЦБД включает в себя следующие основные этапы:

1. Планирование разработки базы данных;

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

3. Сбор и анализ требований пользователей:

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

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

Логическое проектирование базы данных – создание логической модели данных; создание схемы базы данных на основе конкретной модели данных, например, реляционной модели данных. Для реляционной модели данных логическая модель - набор схем отношений, обычно с указанием первичных ключей, а также «связей» между отношениями, представляющих собой внешние ключи.

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

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

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

5. Разработка приложений:

Проектирование транзакций (группа инструкций SQL (набор команд), исполняемых как единое целое);

Проектирование пользовательского интерфейса;

6. Реализация;

8. Тестирование;

9. Эксплуатация и сопровождение:

Анализ функционирования и поддержка исходного варианта БД;

Адаптация, модернизация и поддержка переработанных вариантов.

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

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

Обеспечение хранения в БД всей необходимой информации.

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

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

Обеспечение целостности базы данных.

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

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

Надлежащим образом структурированная база данных:

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

Разработка БД включает в себя следующие этапы:

1. Анализ требований или определение цели базы данных;
2. Организация данных в таблицах;
3. Указание первичных ключей и анализ связей;
4. Нормализация таблиц.

Рассмотрим каждый этап проектирования баз данных подробнее. Обратите внимание, что в этом руководстве рассматривается реляционная модель базы данных Эдгара Кодда , написанная на языке SQL (а не иерархическая, сетевая или объектная модели ).

Анализ требований: определение цели базы данных

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

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

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

Начните со сбора существующих данных, которые будут включены в базу. Затем определите типы данных, которые нужно сохранить. А также объекты, которые описывают эти данные. Например:

Клиенты

• Адрес;
• Город, штат, почтовый индекс;
• Адрес электронной почты.

Товары

• Название;
• Цена;
• Количество в наличии;
• Количество под заказ.

Заказы

• Номер заказа;
• Торговый представитель;
• Дата;
• Товар;
• Количество;
• Цена;
• Стоимость.

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

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

Структура базы данных: построение блоков

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

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

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

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

• CHAR — конкретная длина текста;
• VARCHAR — текст различной длины;
• TEXT — большой объем текста;
• INT — положительное или отрицательное целое число;
• FLOAT , DOUBLE — числа с плавающей запятой;
• BLOB — двоичные данные.

Некоторые СУБД также предлагают тип данных Autonumber , который автоматически генерирует уникальный номер в каждой строке.

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

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

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

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

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

Создание связей между сущностями

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

Каждый объект может быть взаимосвязан с другим с помощью одного из трех типов связи:

Связь «один-к одному»

Когда существует только один экземпляр объекта A для каждого экземпляра объекта B, говорят, что между ними существует связь «один-к одному » (часто обозначается 1:1 ). Можно указать этот тип связи в ER-диаграмме линией с тире на каждом конце:

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

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

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

Связь «один-ко-многим»

Эта связи возникают, когда запись в одной таблице связана с несколькими записями в другой. Например, один клиент мог разместить много заказов, или у читателя может быть сразу несколько книг, взятых в библиотеке. Связи «один- ко-многим » (1:M ) обозначаются так называемой «меткой ноги вороны», как в этом примере:

Чтобы реализовать связь 1:M , добавьте первичный ключ из «одной » таблицы в качестве атрибута в другую таблицу. Если первичный ключ таким образом указан в другой таблице, он называется внешним ключом. Таблица со стороны связи «1 » представляет собой родительскую таблицу для дочерней таблицы на другой стороне.

Связь «многие-ко-многим»

Когда несколько объектов таблицы могут быть связаны с несколькими объектами другой. Говорят, что они имеют связь «многие-ко-многим » (M:N ). Например, в случае студентов и курсов, поскольку студент может посещать много курсов, и каждый курс могут посещать много студентов.

На ER-диаграмме эти связи отображаются с помощью следующих строк:

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

Для этого нужно создать между этими двумя таблицами новую сущность. Если между продажами и продуктами существует связь M:N , можно назвать этот новый объект «sold_products », так как он будет содержать данные для каждой продажи. И таблица продаж, и таблица товаров будут иметь связь 1:M с sold_products . Этот вид промежуточного объекта в различных моделях называется таблицей ссылок, ассоциативным объектом или таблицей связей.

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

Обязательно или нет?

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

Два объекта могут быть взаимозависимыми (один не может существовать без другого ).

Рекурсивные связи

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

Лишние связи

Лишние связи — это те, которые выражены более одного раза. Как правило, можно удалить одну из таких связей без потери какой-либо важной информации. Например, если объект «ученики » имеет прямую связь с другим объектом, называемым «учителя », но также имеет косвенные отношения с учителями через «предметы », нужно удалить связь между «учениками » и «учителями ». Так как единственный способ, которым ученикам назначают учителей — это предметы.

Нормализация базы данных

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

В то же время не все базы данных необходимо нормализовать. В целом, базы с обработкой транзакций в реальном времени (OLTP ), должны быть нормализованы.

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

Первая форма нормализации

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

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

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

Вторая форма нормализации

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

Например, атрибут «возраст » зависит от «дня рождения », который, в свою очередь, зависит от «ID студента », имеет частичную функциональную зависимость. Таблица, содержащая эти атрибуты, не будет соответствовать второй форме нормализации.

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

Таким образом, таблица с этими полями не будет соответствовать второй форме нормализации, поскольку атрибут «название товара » зависит от идентификатора продукта, но не от номера заказа:

• Номер заказа (первичный ключ );
• ID товара (первичный ключ );
• Название товара.

Третья форма нормализации

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

В соответствии с 3NF , нельзя хранить в таблице любые производные данные, такие как столбец «Налог », который в приведенном ниже примере, напрямую зависит от общей стоимости заказа:

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

Многомерные данные

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

Правила целостности данных

Также с помощью средств проектирования баз данных необходимо настроить БД с учетом возможности проверки данных на соответствие определенным правилам. Многие СУБД , такие как Microsoft Access , автоматически применяют некоторые из этих правил.

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

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

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

Добавление индексов и представлений

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

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

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

Расширенные свойства

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

SQL и UML

Унифицированный язык моделирования (UML ) — это еще один визуальный способ выражения сложных систем, созданных на объектно-ориентированном языке. Некоторые из концепций, упомянутых в этом руководстве, известны в UML под разными названиями. Например, объект в UML известен, как класс.

Сейчас UML используется не так часто. В наши дни он применяется академически и в общении между разработчиками программного обеспечения и их клиентами.

Системы управления базами данных

Структура проектируемой базы данных зависит от того, какую СУБД вы используете. Некоторые из наиболее распространенных:

• Oracle DB ;
• MySQL ;
• Microsoft SQL Server ;
• PostgreSQL ;
• IBM DB2 .

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

Перевод статьи «Database Structure and Design Tutorial » дружной командой проекта .

Хорошо Плохо

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

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

1. Вступление.

SQL – большая тема для повествования и его рассмотрение выходит за рамки данного руководства. Данная статья строго сфокусирована на изложении процесса проектирования баз данных . Позднее, в отдельном руководстве, я расскажу об основах SQL.

Реляционная модель.

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

Примеры.

РСУБД.

РСУБД, которую я использовал для создания таблиц примеров – MySQL. MySQL – наиболее популярная РСУБД и она бесплатна.

Утилита для администрирования БД.

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

Визуальное моделирование.

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

Проектирование независимо от РСУБД.

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

2. История.

Так выглядели профессионалы в сфере информационных технологий в 70-е. (Слева внизу находится Билл Гейтс).

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

Выше приведен пример того, как такой файл мог бы выглядеть. Программа, производящая чтение данного файла, должна быть уведомлена о том, что данные разделены запятыми. Если программа хочет выбрать и вывести категорию, в которой находится урок "Database Design Tutorial" , то она должна строчка за строчкой производить чтение до тех пор, пока не будут найдены слова "Database Design Tutorial" и затем ей нужно будет прочитать следующее за запятой слово для того, чтобы вывести категорию Software .

Таблицы баз данных.

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

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

История реляционной модели.

Сегодня доступен широкий выбор систем управления базами данных: от небольших десктопных приложений до многофункциональных серверных систем с высокооптимизированными методами поиска. Вот некоторые из наиболее известных систем управления реляционными базами данных (РСУБД):

- Oracle – используется преимущественно для профессиональных, больших приложений.
- Microsoft SQL server – РСУБД компании Microsoft. Доступна только для операционной системы Windows.
- Mysql – очень популярная РСУБД с открытым исходным кодом. Широко используется как профессионалами, так и новичками. Что еще нужно?! Она бесплатна.
- IBM – имеет ряд РСУБД, наиболее известна DB2.
- Microsoft Access – РСУБД, которая используется в офисе и дома. На самом деле – это больше, чем просто база данных. MS Access позволяет создавать базы данных с пользовательским интерфейсом.
В следующей части я расскажу кое-что о характеристиках реляционных баз данных.

3. Характеристики реляционных баз данных.

Использование ключей.

Используя структурированный язык запросов (SQL), данные из разных таблиц, которые связаны ключом, могут быть выбраны за один раз. Для примера вы можете создать запрос, который выберет все заказы из таблицы заказов (orders), которые принадлежат пользователю с идентификатором (id) 3 (Mike) из таблицы пользователей (users). О ключах мы поговорим далее, в следующих частях.

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

Отсутствие избыточности данных.

Ограничение ввода.

Когда вы создаете таблицу базы данных вы предоставляете тип данных для каждого столбца. К примеру, varchar – это тип данных для небольших фрагментов текста с максимальным количеством знаков, равным 255, а int – это числа.

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

Эти ограничения дают вам контроль над целостностью ваших данных и предотвращают ситуации, подобные следующим:

Ввод адреса (текста) в поле, в котором вы ожидаете увидеть число
- ввод индекса региона с длинной этого самого индекса в сотню символов
- создание пользователей с одним и тем же именем
- создание пользователей с одним и тем же адресом электронной почты
- ввод веса (числа) в поле дня рождения (дата)

Поддержание целостности данных.

Назначение прав.

Структурированный язык запросов (SQL).

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

Переносимость.

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

В следующей части подробнее рассмотрим первичные ключи.

Процесс проектирования включает в себя следующие этапы.

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

Определение требований к операционной обстановке, в которой будет функционировать информационная система.

Выбор системы управления базой данных (СУБД) и других инструментальных программных средств.

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

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

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

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

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

В настоящее время применяют проектирование с использованием метода "Сущность-связь"(entity–relation, ER–method), который является комбинацией предметного и прикладного методов и обладает достоинствами обоих.

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

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

Сущность – это объект, о котором в системе будет накапливаться информация. Сущности бывают как физически существующие (например, СОТРУДНИК или АВТОМОБИЛЬ ), так и абстрактные (например, ЭКЗАМЕН или ДИАГНОЗ ).

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

Стержневая сущность (стержень ) – это независимая сущность, которая не является ни ассоциацией, ни обозначением, ни характеристикой. Такие сущности имеют независимое существование, хотя они и могут обозначать другие сущности.

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

Характеристическая сущность ( характеристика ) – это связь вида "многие-к-одной" или "одна-к-одной" между двумя сущностями (частный случай ассоциации). Единственная цель характеристики в рамках рассматриваемой предметной области состоит в описании или уточнении некоторой другой сущности. Необходимость в них возникает в связи с тем, что сущности реального мира имеют иногда многозначные свойства.

Например, муж может иметь несколько жен, книга – несколько характеристик переиздания (исправленное, дополненное, ...) и т.д.

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

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

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

Тип сущности характеризуется именем и списком свойств, а экземпляр – конкретными значениями свойств.

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

Например, читатель библиотеки – сильная сущность, а абонемент этого читателя – слабая, которая зависит от наличия соответствующего читателя.

Слабые сущности называют подчинёнными (дочерними) , а сильные – базовыми (основными, родительскими) .

Для каждой сущности выбираются свойства (атрибуты).

Различают:

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

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

Однозначные и многозначные атрибуты (могут иметь соответственно одно или много значений для каждого экземпляра сущности).

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

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

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

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

По типу различают множественные связи "один к одному" (1:1), "один ко многим" (1:n) и "многие ко многим" (m:n). ER–диаграмма, содержащая различные типы связей, приведена на рис. 1. Обратите внимание, что обязательные связи на рис. 1 выделены двойной линией.

Степень связи определяется количеством сущностей, которые охвачены данной связью. Пример бинарной связи – связь между отделом и сотрудниками, которые в нём работают. Примером тернарной связи является связь типа экзамен между сущностями ДИСЦИПЛИНА , СТУДЕНТ , ПРЕПОДАВАТЕЛЬ . Из последнего примера видно, что связь также может иметь атрибуты (в данном случае это Дата проведения и Оценка ). Пример ER–диаграммы с указанием сущностей, их атрибутов и связей приведен на рис. 2.

Принимаемые проектные решения можно описать языком инфологического моделирования (ЯИМ), основанном на языке SQL, который позволяет дать удобное и полное описание любой сущности и, следовательно, всей базы данных. Например:

СОЗДАТЬ ТАБЛИЦУ Блюда *(Стержневая сущность)

ПЕРВИЧНЫЙ КЛЮЧ (БЛ)

ПОЛЯ (БЛ Целое, Блюдо Текст 60, Вид Текст 7)

ОГРАНИЧЕНИЯ (1. Значения поля Блюдо должны быть

уникальными; при нарушении вывод

сообщения "Такое блюдо уже есть".

2. Значения поля Вид должны принадлежать

набору: Закуска, Суп, Горячее, Десерт,

Напиток; при нарушении вывод сообщения

"Можно лишь Закуска, Суп, Горячее,

Десерт, Напиток");

СОЗДАТЬ ТАБЛИЦУ Состав *(Связывает Блюда и Продукты)

ПЕРВИЧНЫЙ КЛЮЧ (БЛ, ПР)

ВНЕШНИЙ КЛЮЧ (БЛ ИЗ Блюда

NULL-значения НЕ ДОПУСТИМЫ

УДАЛЕНИЕ ИЗ Блюда КАСКАДИРУЕТСЯ

ОБНОВЛЕНИЕ Блюда.БЛ КАСКАДИРУЕТСЯ)

ВНЕШНИЙ КЛЮЧ (ПР ИЗ Продукты

NULL-значения НЕ ДОПУСТИМЫ

УДАЛЕНИЕ ИЗ Продукты ОГРАНИЧИВАЕТСЯ

ОБНОВЛЕНИЕ Продукты.ПР КАСКАДИРУЕТСЯ)

ПОЛЯ (БЛ Целое, ПР Целое, Вес Целое)

ОГРАНИЧЕНИЯ (1. Значения полей БЛ и ПР должны принадлежать

набору значений из соответствующих полей таблиц

Блюда и Продукты; при нарушении вывод сообщения

"Такого блюда нет" или "Такого продукта нет".

2. Значение поля Вес должно лежать в пределах от 0.1 до 500 г.);

Однако такое описание не отличается наглядностью. Для достижения большей иллюстративности целесообразно дополнять проект используя языки инфологического моделирования "Сущность-связь" или "Таблица-связь

В ER диаграммах "Сущность-связь" сущности изображаются (рис.2) помеченными прямоугольниками , ассоциации – помеченными ромбами или шестиугольниками , атрибуты – помеченными овалами , а связи между ними – ненаправленными ребрами (линиями, соединяющими геометрические фигуры), над которыми может проставляться степень связи (1 или буква, заменяющая слово "много") и необходимое пояснение.

В языке инфологического моделирования "Таблица-связь" (рис.3) все сущности изображаются одностолбцовыми таблицами с заголовками , состоящими из имени и типа сущности . Строки таблицы – это перечень атрибутов сущности, а те из них, которые составляют первичный ключ, располагаются рядом и обводятся рамкой. Связи между сущностями указываются стрелками, направленными от первичных ключей или их составляющих.

(стержень)