Реляционная модель данных (РМД) некоторой предметной области представляет собой набор отношений, изменяющихся во времени.
При создании информационной системы совокупность отношений позволяет хранить данные об объектах предметной области и моделировать связи между ними. Элементы РМД и формы их представления приведены в табл.1.
Отношение является важнейшим понятием и представляет собой двумерную таблицу, содержащую некоторые данные.
Сущность есть объект любой природы, данные о котором хранятся в базе данных. Данные о сущности хранятся в отношении.
Атрибуты представляют собой свойства, характеризующие сущность.
В структуре таблицы каждый атрибут именуется и ему соответствует заголовок некоторого столбца таблицы.
Математически отношение можно описать следующим образом. Пусть даны n множеств %%D1, D2, D3,..., Dn%%, тогда отношение %%R%% есть множество упорядоченных кортежей %%
На рис.1 приведен пример представления отношения СОТРУДНИК.
В общем случае порядок кортежей в отношении, как и в любом множестве, не определен. Однако, в реляционных СУБД для удобства кортежи все же упорядочивают. Чаще всего для этого выбирают некоторый атрибут, по которому система автоматически сортирует кортежи по возрастанию или убыванию. Если пользователь не назначает атрибута упорядочения, система автоматически присваивает номер кортежам в порядке их ввода.
Домен представляет собой множество всех возможных значений определенного атрибута отношения.
Отношение СОТРУДНИК включает 4 домена. Домен 1 содержит фамилии всех сотрудников, домен 2 - номера всех отделов фирмы, домен 3 - названия всех должностей, домен 4 - даты рождения всех сотрудников. Каждый домен образует значения одного типа данных, например, числовые или символьные.
Отношение СОТРУДНИК содержит 3 кортежа. Кортеж рассматриваемого отношения состоит из 4 элементов, каждый из которых выбирается из соответствующего домена. Каждому кортежу соответствует строка таблицы (рис.1).
Схема отношения (заголовок отношения) представляет собой список имен атрибутов. Например, для приведенного примера схема отношения имеет вид СОТРУДНИК (ФИО, Отдел, Должность, Дата рождения) . Множество собственно кортежей отношения часто называют содержимым (телом) отношения.
Первичным ключом (ключом отношения, ключевым атрибутом) называется атрибут отношения, однозначно идентифицирующий каждый из его кортежей.
Например, в отношении СОТРУДНИК (ФИО, Отдел, Должность, Дата рождения) ключевым является атрибут «ФИО». Ключ может быть составным (сложным), то есть состоять из нескольких атрибутов.
Каждое отношение обязательно имеет комбинацию атрибутов, которая может служить ключом. Ее существование гарантируется тем, что отношение - это множество, которое не содержит одинаковых элементов - кортежей. То есть в отношении нет повторяющихся кортежей, а это значит, что по крайней мере вся совокупность атрибутов обладает свойством однозначной идентификации кортежей отношения. Во многих СУБД допускается создавать отношения, не определяя ключи.
Возможны случаи, когда отношение имеет несколько комбинаций атрибутов, каждая из которых однозначно определяет все кортежи отношения. Все эти комбинации атрибутов являются возможными ключами отношения. Любой из возможных ключей может быть выбран как первичный.
Если выбранный первичный ключ состоит из минимально необходимого набора атрибутов, говорят, что он является не избыточным .
Ключи обычно используют для достижения следующих целей:
Пусть в отношении R1 имеется не ключевой атрибут А, значения которого являются значениями ключевого атрибута В другого отношения R2. Тогда говорят, что атрибут А отношения R1 есть внешний ключ .
С помощью внешних ключей устанавливаются связи между отношениями.
Например, имеются два отношения Студент (ФИО, Группа, Специальность) и Предмет (Назв.Пр, Часы) , которые связаны отношением СТУДЕНТ_ПРЕДМЕТ(ФИО, Назв.Пр. Оценка) (рис.2). В связующем отношении атрибуты ФИО и Назв.Пр образуют составной ключ . Эти атрибуты представляют собой внешние ключи, являющиеся первичными ключами других отношений.
Рис.2. Связь отношений
Реляционная модель накладывает на внешние ключи ограничение для обеспечения целостности данных, называемое ссылочной целостностью . Это означает, что каждому значению внешнего ключа должны соответствовать строки в связываемых отношениях.
Поскольку не всякой таблице можно поставить в соответствие отношение, приведем условия, выполнение которых позволяет таблицу считать отношением.
Если задаваемое таблицей отношение имеет ключ, то считается, что таблица тоже имеет ключ, и ее называют ключевой или таблицей с ключевыми полями . К отношениям можно применять систему операций, позволяющую получать одни отношения из других. Например, результатом запроса к реляционной БД может быть новое отношение, вычисленное на основе имеющихся отношений. Поэтому можно разделить обрабатываемые данные на хранимую и вычисляемую части.
Основной единицей обработки данных в реляционных БД является отношение , а не отдельные его кортежи (записи).
Как отмечалось выше, определение ключа для таблицы означает автоматическую сортировку записей, контроль отсутствия повторений значений в ключевых полях записей и повышение скорости выполнения операций поиска в таблице. Для реализации этих функций в СУБД применяют индексирование .
Термин «индекс» тесно связан с понятием «ключ», хотя между ними есть и некоторое отличие.
Под индексом понимают средство ускорения операции поиска записей в таблице, а следовательно, и других операций, использующих поиск: извлечение, модификация, сортировка и т. д. Таблицу, для которой используется индекс, называют индексированной.
Индекс выполняет роль оглавления таблицы, просмотр которого предшествует обращению к записям таблицы. На практике для создания индекса для некоторой таблицы БД пользователь указывает поле таблицы, которое требует индексации. Ключевые поля таблицы во многих СУБД как правило индексируются автоматически. Индексные файлы, создаваемые по ключевым полям таблицы, часто называются файлами первичных индексов.
Индексы, создаваемые пользователем для не ключевых полей, иногда называют вторичными (пользовательскими) индексами. Введение таких индексов не изменяет физического расположения записей таблицы, но влияет на последовательность просмотра записей.
Эта статья содержит инструкции по проектированию базы данных. Пользователь научится решать, какие сведения являются необходимыми, и распределять данные по таблицам и столбцам, а также изучит связи таблиц. Прежде чем создавать базу данных или приступать к работе с серьезными базами данных, необходимо ознакомиться с этой статьей.
В этой статье
В Microsoft Office Access 2007 данные организуются в таблицы — совокупности строк и столбцов, аналогичные бумагам бухгалтера или книге Microsoft Office Excel 2007. Простая база данных может состоять всего из одной таблицы. Большинство баз данных включают несколько таблиц. Например, в одной таблице могут храниться сведения о продуктах, во второй — сведения о заказах, а в третьей — сведения о клиентах.
Каждая строка называется также записью , а каждый столбец, или тип элемента, называется также полем . Записи позволяют комбинировать данные. Поля представляют собой отдельные элементы данных — типы элементов для каждой строки. Например, в таблице «Продукты» каждая строка или запись содержит сведения об одном продукте. В каждом столбце или поле хранятся определенные данные о продукте, например наименование или цена.
В основе процесса создания базы данных лежат определенные принципы. Первый принцип заключается в необходимости исключать повторяющиеся (или лишние) данные, т. к. они занимают место и повышают вероятность возникновения ошибок и неполадок. Второй принцип касается важной роли правильных и полных данных. Если база данных содержит неправильные данные, все отчеты, в которых данные объединяются, будут также содержать неверные сведения. Это может привести к принятию неправильных решений на основе отчетов.
Правильная структура базы данных подразумевает:
Процесс разработки базы данных включает следующие шаги.
Это позволяет подготовиться к выполнению следующих шагов.
Следует собирать все данные, которые необходимо сохранить в базе данных, например, наименования продуктов и номера заказов.
Распределите элементы данных по группам или темам, например «Продукты» или «Заказы». Для каждой темы будет создана таблица.
Определите, какие данные требуется хранить в каждой таблице. Каждый элемент данных будет введен в отдельное поле и станет столбцом таблицы. Например, таблица «Сотрудники» может содержать такие поля, как «Фамилия» и «Дата найма».
Выберите первичные ключи таблиц. Первичный ключ — столбец для однозначного определения каждой записи, например код продукта или заказа.
Проанализируйте все таблицы и определите, как данные одной таблицы связаны с данными других таблиц. Добавьте в таблицы поля или создайте новые таблицы для создания необходимых связей.
Проверьте структуру базы данных на наличие ошибок. Создайте таблицы и добавьте несколько записей с образцами данных. Проанализируйте полученные результаты. Внесите в структуру необходимые изменения.
Примените правила нормализации, чтобы проверить правильность структуры таблиц. Внесите в таблицы необходимые изменения.
Целесообразно записать цель создания базы данных на бумаге: задачи, способы использования и список пользователей. Для базы данных небольшого объема для работы на дому можно определить следующую простую цель: «База данных клиентов содержит сведения о клиентах и используется для рассылки сообщений электронной почты и отчетов». При создании более сложной базы данных для большого количества пользователей, например в организации, описание цели может состоять из нескольких параграфов; необходимо указать время и способы использования базы данных различными пользователями. Основная задача — составить подробное описание цели создания базы данных, чтобы иметь возможность обращаться к нему в процессе проектирования. Наличие описания позволяет следовать поставленным целям в процессе принятия решений.
Процесс поиска и организации необходимых данных следует начать с записи имеющихся сведений. Например, можно записать заказы из главной книги или сохранить сведения о клиентах из картотеки. Соберите необходимые документы и составьте список типов данных (например, список полей в бланке). Если бланки не используются, представьте, что необходимо создать бланк для записи сведений о клиентах. Какие данные требуется записывать? Какие поля для заполнения необходимо создать? Определите нужные элементы и составьте их список. Предположим, что сведения о клиентах хранятся в картотеке. Анализ карточек позволяет определить, что в них содержатся такие сведения, как имя клиента, адрес, город, область, почтовый индекс и номер телефона. Каждый из этих элементов может стать столбцом таблицы.
При подготовке списка необязательно стремиться придать ему законченный вид с первого раза. Следует записывать все возможные варианты. Если база данных предназначена для нескольких пользователей, попросите их предложить свои варианты. Список можно исправить позднее.
Затем следует определить типы отчетов или рассылок, которые требуется выполнять из базы данных. Например, может потребоваться выполнять отчет о продажах продуктов по регионам или сводный отчет по запасам продуктов. Может также потребоваться создавать письма, адресованные клиентам и содержащие сведения о продажах или специальных предложениях. Продумайте структуру отчета, определите его внешний вид. Какие сведения требуется включить в отчет? Составьте список нужных сведений. Выполните те же действия для писем и других отчетов.
Определение структуры отчетов и рассылок позволяет выявить элементы, которые требуется включить в базу данных. Предположим, что требуется предоставить клиентам возможность подписываться или отменять подписку на регулярную рассылку новостей, а также иметь возможность выводить на печать список подписчиков. Для записи этих сведений в таблицу клиентов следует добавить столбец для отправки сообщений электронной почты. В этом поле можно выбрать вариант «Да» или «Нет» для каждого клиента.
Для отправки клиентам сообщений электронной почты необходимо записать еще один элемент данных. Если клиент согласится получать сообщения электронной почты, потребуется записать его адрес электронной почты. Следовательно, для каждого клиента потребуется сохранить адрес электронной почты.
Удобно создать прототип каждого отчета или списка клиентов для печати и определить, какие элементы данных потребуются для их создания. Например, анализ письма позволяет выявить несколько элементов. Если требуется включить в письмо приветствие — например строку «г.» или «г-жа», — необходимо создать соответствующий элемент. Кроме того, письмо, как правило, начинается со стандартной фразы «Уважаемый г. Егоров», а не с «Уважаемый г. Владимир Егоров». Поэтому фамилию требуется сохранять отдельно от имени.
Важно помнить, что данные необходимо разбивать на минимальные элементы. Имя следует разделять на две части: имя и фамилию, чтобы фамилия была доступна для использования. Например, чтобы выполнить сортировку отчета по фамилиям, необходимо сохранить их в отдельных полях. В целом, если требуется выполнять сортировку, поиск, вычисления или отчеты для отдельных элементов данных, эти элементы следует сохранять в отдельных полях.
Составьте список вопросов, ответы на которые требуется получать с помощью базы данных. Например, каков объем продаж отдельного продукта за последний месяц? Где находятся самые перспективные клиенты? Кто поставляет самый продаваемый продукт? Список возможных вопросов позволяет определить дополнительные элементы данных для записи.
После сбора данных можно переходить к следующему шагу.
Чтобы распределить данные по таблицам, выделите основные группы или темы. Например, после поиска и организации данных для базы данных продаж продуктов может быть составлен следующий список:
Основные группы — продукты, поставщики, клиенты и заказы. Поэтому целесообразно создать четыре таблицы: по одной для сведений о продуктах, поставщиках, клиентах и заказах. Хотя составленный список не является полным, можно начать с этих основных таблиц. Список можно дополнять до тех пор, пока не будет получена нужная структура базы данных.
При первом просмотре предварительного списка элементов может показаться, что целесообразнее поместить все элементы в единую таблицу вместо того чтобы создавать четыре таблицы, как показано в предыдущем примере. В этом разделе объясняется, почему такой подход неэффективен. Ознакомьтесь с таблицей на иллюстрации.
В данном случае каждая строка содержит данные о продуктах и поставщиках. Поскольку один поставщик поставляет различные продукты, его имя и адрес приходится вводить повторно. При этом место на диске используется неэффективно. Более рациональным решением является сохранение сведений о поставщике в отдельной таблице поставщиков и связывание этой таблицы с таблицей продуктов.
Вторая проблема при такой структуре базы данных возникает при необходимости изменить данные о поставщике. Предположим, что требуется изменить адрес поставщика. Поскольку данные о поставщике содержатся в большом количестве строк, пользователь может пропустить некоторые из них. Эту проблему можно также решить с помощью сохранения адреса поставщика в одном расположении.
При разработке базы данных следует стремиться к однократному сохранению каждого элемента данных. Если данные повторяются (например, адрес отдельного поставщика), поместите их в отдельную таблицу.
Наконец, предположим, что компания Coho Winery поставляет только один продукт и его требуется удалить, но при этом сохранить сведения об имени и адресе поставщика. Как удалить запись о продукте, сохранив при этом данные поставщика? Это невозможно. Поскольку каждая строка содержит данные о продукте, а также данные о поставщике, невозможно удалить эти данные отдельно друг от друга. Чтобы разделить эти данные, необходимо создать две таблицы: одну для сведений о продуктах, а вторую — для сведений о поставщиках. В этом случае удаление записи о продукте не будет приводить к удалению данных о поставщике.
После выбора темы для таблицы необходимо отслеживать, чтобы столбцы этой таблицы содержали данные только по этой теме. Например, таблица продуктов должна содержать только сведения о продуктах. Поскольку адрес поставщика относится к данным о поставщиках, а не о продуктах, он должен быть сохранен в таблице поставщиков.
Чтобы определить столбцы таблицы, следует решить, какие сведения требуется отслеживать для таблицы. Например, в таблицу клиентов можно включить столбцы «Имя», «Адрес», «Город/область/почтовый индекс», «Отправка электронной почты», «Обращение» и «Адрес электронной почты». Все строки содержат одинаковый набор столбцов, поэтому для каждой строки можно сохранять соответствующие сведения. Например, столбец «Адрес» содержит адреса клиентов. Каждая запись содержит сведения только об одном клиенте, и поле адреса содержит адрес этого клиента.
Первоначальный набор столбцов для каждой таблицы можно дополнять новыми столбцами. Например, удобно хранить имя и фамилию клиента в разных столбцах, чтобы облегчить сортировку, поиск и создание индекса. Адрес также состоит из нескольких компонентов: адреса, города, области, почтового индекса и страны, которые лучше хранить в отдельных столбцах. Например, если требуется выполнить поиск, фильтрацию или сортировку по области, необходимо хранить сведения об области в отдельном столбце.
Следует также определить, какого рода данные будут храниться в базе данных: национального или международного уровня. Например, если планируется хранить в базе данных международные адреса, лучше использовать столбец «Регион» вместо «Страна», т. к. в таком столбце можно указывать области внутри своей страны и регионы других стран. Таким же образом в поле Почтовый индекс можно будет хранить почтовые индексы разных стран.
В приведенном ниже списке содержатся несколько советов по созданию столбцов.
Не следует хранить в таблицах результаты вычислений. Вместо этого можно выполнять вычисления в Access при каждой необходимости получить результат. Предположим, что в отчете о заказанных продуктах отображаются промежуточные итоги для заказанных продуктов каждой категории. Однако таблица не содержит столбец для промежуточных итогов. Вместо этого таблица включает столбец для заказанных продуктов, в котором хранится количество каждого продукта. С помощью этих данных Access высчитывает промежуточные итоги при каждой печати отчета. Сами промежуточные итоги не хранятся в таблице.
Может показаться, что лучше создавать единые поля для сохранения полных имен или названий продуктов вместе с их описаниями. Однако объединение различных данных в одном поле затрудняет их дальнейшее извлечение. Следует разбивать информацию на логические компоненты. Например, можно создать отдельные поля для имени и фамилии или для названия, категории и описания продукта.
После создания столбцов в таблицах можно выбрать первичный ключ для каждой таблицы.
Каждая таблица должна содержать столбец или набор столбцов для однозначного определения каждой строки таблицы. Как правило, в этих целях используется уникальный идентификационный номер, например код сотрудника или серийный номер. В базе данных такие сведения носят название первичного ключа таблицы. В Access первичные ключи служат для быстрого связывания данных из нескольких таблиц и их отображения для пользователя.
Если в таблице имеется уникальный идентификатор, например номер продукта, который однозначно определяет каждый продукт в каталоге, его можно использовать в качестве первичного ключа таблицы, но значения этого столбца должны быть различными для всех записей. Первичный ключ не должен содержать повторяющихся значений. Например, не следует использовать в качестве первичного ключа имена людей, т. к. они не являются уникальными. В одной таблице могут существовать две записи с одинаковыми именами.
Первичный ключ должен всегда иметь значение. Если столбец допускает неназначенное или отсутствующее значение, его не следует использовать в качестве компонента первичного ключа.
Значение первичного ключа не должно меняться. В базе данных с несколькими таблицами первичный ключ одной таблицы может использоваться в качестве ссылки в других таблицах. Если первичный ключ изменяется, это изменение необходимо применить ко всем ссылкам на этот ключ. Благодаря использованию первичного ключа с постоянным значением снижается вероятность нарушения синхронизации с другими таблицами.
Часто в качестве первичного ключа используется произвольное уникальное числовое значение. Например, каждому заказу можно присвоить уникальный номер для однозначного определения заказа. Этот номер не подлежит изменению.
Если не удается выбрать столбец или набор столбцов для использования в качестве первичного ключа, можно использовать столбец с типом данных «Счетчик». При использовании такого столбца в Access автоматически назначаются значения. Такой код не содержит сведений; в нем нет описания строки, которую он представляет. Бессодержательные коды идеально подходят для использования в качестве первичного ключа, т. к. они не изменяются. Первичный ключ, содержащий фактические данные о строке, например номер телефона или имя клиента, более подвержен изменениям, т. к. фактические сведения могут измениться.
Столбец с типом данных «Счетчик» — удобный первичный ключ. Коды продуктов никогда не совпадают.
В некоторых случаях в качестве первичного ключа таблицы требуется использовать два и более полей. Например, для таблицы сведений о заказах, в которой хранятся элементы строк для заказов, в первичном ключе можно использовать два столбца: код заказа и код продукта. Первичный ключ из нескольких столбцов называется составным.
В базе данных продаж можно создать столбец счетчика для первичного ключа всех таблиц: «КодТовара» для таблицы товаров, «КодЗаказа» для таблицы заказов, «КодКлиента» для таблицы клиентов и «КодПоставщика» для таблицы поставщиков.
После распределения данных по таблицам необходимо получить возможность объединять их. Например, изображенная ниже форма включает сведения из нескольких таблиц.
Эта форма содержит данные из таблицы клиентов,
Сотрудников,
Заказов,
Продуктов
И сведений о заказах.
Access — это система управления реляционными базами данных. В реляционной базе данных сведения распределяются по отдельным, тематически организованным таблицам. Для объединения данных используются связи между таблицами.
Рассмотрим следующий пример: база данных заказов продуктов содержит таблицы поставщиков и продуктов. Поставщик может поставлять любое количество продуктов. Следовательно, для каждого поставщика из таблицы поставщиков в таблице продуктов может содержаться большое количество продуктов. Поэтому связь между таблицей поставщиков и таблицей продуктов представляет собой отношение «один-ко-многим».
Чтобы создать отношение «один-ко-многим» в структуре базы данных, добавьте первичный ключ на стороне «один» в таблицу на стороне «многие» в виде дополнительного столбца. В данном примере необходимо добавить столбец кода поставщика из таблицы поставщиков в таблицу продуктов. После этого Access сможет использовать код поставщика из таблицы продуктов для поиска поставщика каждого продукта.
Столбец кода поставщика в таблице продуктов называется внешним ключом. Внешний ключ — это первичный ключ другой таблицы. Столбец кода поставщика в таблице продуктов является внешним ключом, т. к. он является также первичным ключом таблицы поставщиков.
Основой для связывания таблиц является объединение первичных и внешних ключей в пары. Если не удается определить таблицы с общим столбцом, создание отношения «один-ко-многим» обеспечивает необходимость общего столбца для двух таблиц.
Рассмотрим связь между таблицами продуктов и заказов.
Один заказ может включать несколько продуктов. С другой стороны, отдельный продукт может содержаться в нескольких заказах. Следовательно, для каждой записи таблицы заказов может существовать несколько записей в таблице продуктов, и наоборот. Такой тип связи называется отношением «многие-ко-многим». Обратите внимание, что для обнаружения отношения «многие-ко-многим» между таблицами важно рассмотреть обе стороны.
Темы двух таблиц — заказы и продукты — связаны отношением «многие-ко-многим». Это проблема. Представьте, что произойдет, если создать связь между двумя таблицами путем добавления поля с кодом продукта в таблицу заказов. Чтобы заказ мог содержать несколько продуктов, каждый заказ в таблице должен включать несколько записей. В этом случае сведения о заказе должны повторяться в каждой строке заказа, что может привести к неэффективности структуры таблицы и неточности данных. Такая же трудность возникает при создании поля с кодом заказа в таблице продуктов — для каждого продукта в таблице существует несколько записей. Как решить эту проблему?
Решением является создание третьей, связующей, таблицы, в которой отношение «многие-ко-многим» разбивается на два отношения «один-ко-многим». Первичные ключи двух таблиц вставляются в третью таблицу. В результате в третьей таблице сохраняются все экземпляры отношения.
Каждая запись в таблице сведений о заказах представляет собой отдельный элемент строки заказа. Первичный ключ таблицы состоит из двух полей — внешних ключей таблиц заказов и продуктов. Невозможно использовать для первичного ключа этой таблицы только поле с кодом заказа, т. к. один заказ может содержать много элементов строк. Код заказа повторяется для каждого элемента строки, поэтому это поле не содержит уникальных значений. Невозможно также использовать только поле с кодом продукта, т. к. один продукт может входить в несколько заказов. Однако использование двух полей позволяет получить уникальное значение для каждой записи.
В базе данных продаж продуктов таблицы заказов и продуктов не имеют непосредственной связи. Они связаны опосредованно через таблицу сведений о заказах. Отношение «многие-ко-многим» между заказами и продуктами представлено в базе данных двумя отношениями «один-ко-многим»:
В таблице сведений о заказах можно определить продукты для отдельного заказа. Можно также определить заказы для отдельного продукта.
После создания таблицы сведений о заказах список таблиц и полей может выглядеть следующим образом:
Следующий тип отношений — отношение «один-к-одному». Предположим, что требуется сохранить дополнительные сведения о продукте, которые редко используются или применимы к небольшому числу продуктов. Поскольку эта информация используется редко и ее сохранение в таблице продуктов требует создания дополнительного пустого поля для всех продуктов, к которым она не применима, лучше поместить эту информацию в отдельную таблицу. Как и в таблице продуктов, в качестве первичного ключа используется код продукта. Дополнительная таблица и таблица продуктов связаны отношением «один-к-одному». Для каждой записи таблицы продуктов в дополнительной таблице существует одна запись. При определении отношения такого типа обе таблицы должны содержать одно поле.
Если возникает необходимость в создании в базе данных отношения «один-к-одному», рассмотрите возможность объединения данных в одну таблицу. Если этот вариант неприемлем, например по причине возникновения пустых полей, используйте приведенный ниже список для определения отношения в структуре базы данных.
Определение связей между таблицами позволяет обеспечить правильность таблиц и столбцов. При наличии отношения «один-к-одному» или «один-ко-многим» таблицы должны содержать общие столбцы. При наличии отношения «многие-ко-многим» требуется третья таблица.
После создания необходимых таблиц, полей и связей следует заполнить таблицы образцами данных и выполнить с ними различные операции: создание запросов, добавление новых записей и т. д. Это позволит выявить возможные проблемы — например, может потребоваться добавить дополнительный столбец или разбить одну таблицу на две для исключения повторяющихся данных.
Проверьте, можно ли использовать базу данных для получения ответов на нужные вопросы. Создайте черновые формы и отчеты и убедитесь, что в них отображаются нужные данные. Проверьте базу данных на наличие повторяющихся данных и при необходимости удалите эти данные.
В начале работы с базой данных можно выявить области для усовершенствования ее структуры. Можно обратить внимание на следующие пункты:
Предположим, что все продукты в базе данных можно разделить на несколько общих категорий, например напитки, приправы или рыбные продукты. Таблица продуктов может содержать поле, в котором указана категория продукта.
Предположим, что после анализа и усовершенствования структуры базы данных было решено сохранять не только имя категории продуктов, но и ее описание. Если добавить поле с описанием категории в таблицу продуктов, описание придется повторять для каждого продукта в отдельной категории, а это решение не является рациональным.
Более рациональный подход — выделить категорию как отдельную тему для отслеживания в базе данных, создать отдельную таблицу с собственным первичным ключом. Затем первичный ключ таблицы категорий можно добавить в таблицу продуктов в качестве внешнего ключа.
В процессе анализа структуры таблиц следует выявлять повторяющиеся группы. Предположим, что таблица содержит следующие столбцы:
В данном случае продукты — повторяющиеся группы столбцов, отличные друг от друга только номером в имени столбца. Если столбцы пронумерованы таким образом, следует пересмотреть структуры таблицы.
Такая структура имеет несколько недостатков. Прежде всего, существует верхний предел для числа продуктов. После превышения этого предела в таблицу необходимо добавить новую группу столбцов, а эта задача сложна для выполнения.
Следующий недостаток заключается в том, что если число продуктов для отдельного поставщика меньше максимального, некоторые столбцы будут оставаться пустыми. Но самый серьезный недостаток такой структуры — усложнение многих задач, таких как сортировка или создание индекса таблицы по коду или наименованию продукта.
При наличии повторяющихся групп следует рассмотреть возможность разбиения одной таблицы на две. В приведенном выше примере лучше создать две таблицы: одну — для поставщиков, а вторую — для продуктов, и связать их с помощью кода поставщика.
Следующим этапом создания базы данных может стать применение правил нормализации данных (или просто правил нормализации). Эти правила позволяют проверить правильность структуры таблиц. Процесс применения этих правил к структуре базы данных называется нормализацией базы данных или просто нормализацией.
Нормализацию рекомендуется выполнять после внесения в базу данных всех элементов данных и создания предварительной структуры. Цель этого процесса — проверить правильность распределения элементов данных по таблицам. Однако нормализация не позволяет проверить правильность самих элементов данных.
Применение правил нормализации выполняется последовательно; на каждом этапе проверяется соответствие базы данных одной из т. н. нормальных форм. Существует пять нормальных форм. В этой статье описываются первые три формы, поскольку их применения достаточно для большинства баз данных.
Первая нормальная форма содержит правило о том, что каждая ячейка на пересечении строки и столбца в таблице должна содержать отдельное значение, а не список значений. Например, поле «Цена» должно содержать лишь одно значение цены. Если на пересечении строки и столбца находится ячейка, она должна содержать лишь одно значение.
Вторая нормальная форма содержит требование о том, что каждый столбец, не входящий в ключ, должен находиться в зависимости от всего ключевого столбца, а не от его части. Это правило применимо в том случае, если первичный ключ состоит из нескольких столбцов. Предположим, что таблица, в которой первичный ключ состоит из столбцов с кодом заказа и кодом продукта, содержит следующие столбцы:
Такая структура не соответствует второй обычной форме, т. к. имя продукта зависит от кода продукта, но не зависит от кода заказа; следовательно, этот столбец зависит лишь от части первичного ключа. Столбец с именем продукта следует удалить из таблицы. Он должен быть включен в другую таблицу (таблицу продуктов).
Третья нормальная форма содержит требование о том, что столбцы, не являющиеся ключевыми, должны не только зависеть от всего первичного ключа, но и быть независимыми друг от друга.
Другими словами, каждый столбец, не являющийся ключевым, должен зависеть только от первичного ключа. Предположим, что таблица содержит следующие столбцы:
Предположим, что скидка зависит от рекомендуемой розничной цены. В этом случае таблица не соответствует требованию третьей обычной формы, т. к. столбец скидки, не являющийся ключевым, зависит от другого столбца, не являющегося ключевым (столбца рекомендуемой розничной цены). Требование о независимости столбцов друг от друга означает, что изменение любого неключевого столбца не должно влиять на другие столбцы. Но при изменении значения в столбце розничной цены значение скидки изменялось бы соответствующим образом, нарушая правило. Поэтому в данном случае столбец скидки следует перенести в другую таблицу, в которой столбец розничной цены является ключевым.
Дополнительные сведения о проектировании баз данных см. в следующих книгах.
Применимо к Access 2007 и выше
Каждая таблица содержит информацию на отдельную тему, а каждое поле в таблице содержит отдельные сведения по теме таблицы. Например, в таблице с данными о клиенте могут содержаться поля с названием компании, адресом, городом, страной и номером телефона. При разработке полей для каждой таблицы необходимо помнить:
Каждое поле должно быть связано с темой таблицы.
В таблице должна присутствовать вся необходимая информация.
Информацию следует разбивать на наименьшие логические единицы (Например, поля «Имя» и «Фамилия», а не общее поле «Имя»).
С тем чтобы Microsoft Access мог связать данные из разных таблиц, например, данные о клиенте и его заказы, каждая таблица должна содержать поле или набор полей, которые будут задавать индивидуальное значение каждой записи в таблице. Такое поле или набор полей называют основным ключом .
После распределения данных по таблицам и определения ключевых полей необходимо выбрать схему для связи данных в разных таблицах. Для этого нужно определить связи между таблицами.
После проектирования таблиц, полей и связей необходимо еще раз просмотреть структуру базы данных и выявить возможные недочеты. Желательно это сделать на данном этапе, пока таблицы не заполнены данными.
Для проверки необходимо создать несколько таблиц, определить связи между ними и ввести несколько записей в каждую таблицу, затем посмотреть, отвечает ли база данных поставленным требованиям. Рекомендуется также создать черновые выходные формы и отчеты и проверить, выдают ли они требуемую информацию. Кроме того, необходимо исключить из таблиц все возможные повторения данных.
Если структуры таблиц отвечают поставленным требованиям, то можно вводить все данные. Затем можно создавать любые запросы, формы, отчеты, макросы и модули.
В Microsoft Access существует два инструмента для усовершенствования структуры баз данных. Мастер анализа таблиц исследует таблицу, в случае необходимости предлагает новую ее структуру и связи, а также переделывает ее.
Анализатор быстродействия исследует всю базу данных, дает рекомендации по ее улучшению, а также осуществляет их.
По мере разработки автономных разделов приложения желательно передать их заказчику для проверки их функционирования и получения мнения о необходимости внесения тех или иных изменений. После того как заказчик ознакомится с работой приложения, у него практически всегда возникают дополнительные предложения по усовершенствованию, какой бы тщательной не была предварительная проработка проекта. Пользователи часто обнаруживают, что некоторые моменты, о которых в процессе постановки задач они говорили как об очень важных и необходимых, на самом деле не играют существенной роли при практическом использовании приложения. Выявление необходимых изменений на ранних стадиях разработки приложения позволяет существенно сократить время на последующие переделки.
Таблица данных является диапазон ячеек, в которых можно изменить значения в некоторых в некоторых ячейках, задаваемые по-разному проблемы. Хороший пример таблицы данных использует функции ПЛТ с разных кредита сумм и процентной ставки для вычисления комиссионных по цене по займу ипотеки. Эксперименты с разными значениями, чтобы просмотреть соответствующие вариантов в результатах является общие задачи в Анализ данных .
Таблицы данных в Microsoft Excel являются частью набора команд известные как инструменты анализа "что если". После создания и анализа данных таблиц, вы выполняете анализ "что если".
Анализ "что если" является процесс об изменении значений в ячейках, чтобы увидеть, как эти изменения повлияют на результат формулы на листе. Например, с помощью таблицы данных варьироваться процентная ставка и продолжительность срока действия подписки по займу в соответствии - для оценки потенциальных суммы ежемесячных платежей.
Примечание: Вы можете выполнять быстрее выполнять вычисления с помощью таблицы данных и Visual Basic для приложений (VBA). Дополнительные сведения можно найти таблицы данных Excel "что если": быстрее расчета с использованием VBA .
Типы анализ "что если"
Существует три типа инструменты анализа "что если" в Excel: сценарии таблицы данных и средство подбора . Сценарии и таблицы данных с помощью наборов ввода значений для вычисления возможных результатов. Средство подбора совершенно различные, Active Directory использует один результат и вычисляет возможные входные значения, которые могут привести к результату.
Как и сценарии, таблицы данных позволяют изучить набор возможных результатов. В отличие от сценариев, в таблицах данных все результаты отображаются в одной таблице на одном листе. Применение таблиц данных облегчает и ускоряет изучение возможных вариантов. Поскольку внимание сосредоточено только на одной или двух переменных, результаты легко воспринимаются; ими также просто обмениваться в табличной форме.
Таблица данных не может принимать более двух переменных. Если необходимо проанализировать больше переменных, вместо таблиц данных лучше использовать сценарии. Несмотря на то что работа с таблицей данных ограничена только одной или двумя переменными (одна для подстановки значений по столбцам, а другая - по строкам), при этом можно использовать любое количество различных значений переменных. Сценарий поддерживает только 32 различных значения, но количество сценариев может быть любым.
Дополнительные сведения в статье Введение в анализ "что если" .
Базовые сведения о таблицах данных
Создайте таблицу данных с одной или двумя переменными либо в зависимости от количества переменных и формул, которые необходимо проверить.
Таблицы данных с одной переменной
Если нужно отображать как различные значения одной переменной в одну или несколько формул будут изменяться результаты этих формул с помощью таблицы данных с одной переменной. Например, можно использовать таблицу данных с одной переменной для просмотра различных процентной ставки влияют на ежемесячный платеж по ипотеке с помощью функции ПЛТ . Ввод значений переменных в один столбец или строку и результаты отображаются в смежных столбцах или строках.
На иллюстрации ниже ячейка D2 содержит формулу платежа (=ПЛТ(B3/12;B4;-B5) ), которая ссылается на ячейку ввода B3.
Таблицы данных с двумя переменными
Таблицы данных с двумя переменными используются в том случае, если требуется проследить, как изменение значений двух переменных в одной формуле повлияет на результаты этой формулы. Например, таблицу данных с двумя переменными можно использовать, чтобы узнать, как разные комбинации процентных ставок и сроков ссуды повлияют на размер ежемесячного платежа.
На иллюстрации ниже ячейка C2 содержит формулу определения размера платежа (=ПЛТ(B3/12;B4;-B5) ), в которой используются две ячейки ввода: B3 и B4.
Расчет таблицы данных
Создание таблицы данных с одной переменной
Таблица данных с одной переменной содержать его входных значений в одном столбце (столбец программой) или в строке (ориентированные по строке). Любую формулу в таблице данных с одной переменной должны ссылаться только один ячейка ввода.
Выполните следующие действия.
Добавление формулы в таблицу данных с одной переменной
Формулы, которые используются в таблице данных с одной переменной, должны ссылаться только на одну ячейку ввода.
Инструкции
Если таблица данных ориентирована по столбцу, введите новую формулу в пустую ячейку справа от существующую формулу в первой строке таблицы данных.
Если таблица данных ориентирована по строке, введите новую формулу в пустую ячейку под существующую формулу в первом столбце таблицы данных.
Выделите диапазон ячеек, которые содержат таблицу данных и новую формулу.
На вкладке данные нажмите кнопку Анализ "что если" > Таблицы данных (в группе Работа с данными или прогноза Excel 2016 группы).
Выполните одно из следующих действий:
Если таблица данных ориентирована по столбцу, введите ссылку на ячейку для ячейки ввода в поле .
Если таблица данных ориентирована по строке, введите ссылку на ячейку для ячейки ввода в поле .
Создание таблицы данных с двумя переменными
В таблице данных с двумя переменными используется формула, содержащая два списка входных значений. Формула должна ссылаться на две разные ячейки ввода.
Выполните следующие действия.
В ячейку листа введите формулу, которая ссылается на две ячейки ввода.
В приведенном ниже примере, в котором начальные значения формулы введены в ячейки B3, B4 и B5, введите формулу =PMT(B3/12,B4,-B5) в ячейке C2.
В том же столбце ниже формулы введите значения подстановки для первой переменной.
В этом случае введите разные процентные ставки в ячейки C3, C4 и C5.
Введите второй список в той же строке с формулой, справа от нее.
Введите условия займа (в месяцах) в ячейки D2 и E2.
Выделите диапазон ячеек, содержащих формулу (C2), строку и столбец значений (C3:C5 и D2:E2), а также ячейки, в которые нужно поместить вычисленные значения (D3:E5).
В этом случае выделите диапазон C2: E5.
На вкладке " данные " в группе Работа с данными или прогноза (в Excel 2016), нажмите кнопку анализа "что если" > Таблицы данных (в группе Работа с данными или прогноза Excel 2016 группы).
В поле Подставлять значения по столбцам
введите ссылку на ячейку ввода для входных значений в строке.
Ячейки B4
введите в поле Подставлять значения по столбцам
.
В поле Подставлять значения по строкам
введите ссылку на ячейку ввода для входных значений в столбце.
В поле Подставлять значения по строкам
, введите B3
.
Нажмите кнопку ОК .
Пример таблицы данных с двумя переменными
Таблица данных с двумя переменными можно отобразить как различные сочетания процентная ставка и ссуды повлияет ежемесячный платеж по ипотеке. На рисунке ячейка C2 содержит формулу вычисления платежа, =PMT(B3/12,B4,-B5) , который использует две ячейки ввода B3 и B4.
Ускорение вычислений для листов, содержащих таблицы данных
Используйте средство подбора для поиска путем изменения входного значения требуемого результата .Поиск решения Excel
Надстройка "Поиск решения" Excel можно использовать для поиска оптимального значения для ввода переменных. "Поиск решения" работает с группы ячеек (называемые переменные решения или просто переменной ячейки), которые используются при вычислении формулы в ячейках цель и ограничения. Подбираются значения в ячейках переменной решения для удовлетворения ограничения на ограничение ячеек и получения нужного результата для целевой ячейки. Дополнительные сведения в этой статье: определить и решение задачи с помощью поиска решения .
Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте
