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

Реляционная модель данных (РМД) некоторой предметной области представляет собой набор отношений, изменяющихся во времени.

При создании информационной системы совокупность отношений позволяет хранить данные об объектах предметной области и моделировать связи между ними. Элементы РМД и формы их представления приведены в табл.1.

Отношение является важнейшим понятием и представляет собой двумерную таблицу, содержащую некоторые данные.

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

Атрибуты представляют собой свойства, характеризующие сущность.

В структуре таблицы каждый атрибут именуется и ему соответствует заголовок некоторого столбца таблицы. Математически отношение можно описать следующим образом. Пусть даны n множеств %%D1, D2, D3,..., Dn%%, тогда отношение %%R%% есть множество упорядоченных кортежей %%%%, где %%dk \subset Dk%%, %%dk%% - атрибут, a %%Dk%% - домен отношения %%R%%.

На рис.1 приведен пример представления отношения СОТРУДНИК.

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

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

Отношение СОТРУДНИК включает 4 домена. Домен 1 содержит фамилии всех сотрудников, домен 2 - номера всех отделов фирмы, домен 3 - названия всех должностей, домен 4 - даты рождения всех сотрудников. Каждый домен образует значения одного типа данных, например, числовые или символьные.

Отношение СОТРУДНИК содержит 3 кортежа. Кортеж рассматриваемого отношения состоит из 4 элементов, каждый из которых выбирается из соответствующего домена. Каждому кортежу соответствует строка таблицы (рис.1).

Схема отношения (заголовок отношения) представляет собой список имен атрибутов. Например, для приведенного примера схема отношения имеет вид СОТРУДНИК (ФИО, Отдел, Должность, Дата рождения) . Множество собственно кортежей отношения часто называют содержимым (телом) отношения.

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

Например, в отношении СОТРУДНИК (ФИО, Отдел, Должность, Дата рождения) ключевым является атрибут «ФИО». Ключ может быть составным (сложным), то есть состоять из нескольких атрибутов.

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

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

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

Ключи обычно используют для достижения следующих целей:

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

Пусть в отношении R1 имеется не ключевой атрибут А, значения которого являются значениями ключевого атрибута В другого отношения R2. Тогда говорят, что атрибут А отношения R1 есть внешний ключ .

С помощью внешних ключей устанавливаются связи между отношениями.

Например, имеются два отношения Студент (ФИО, Группа, Специальность) и Предмет (Назв.Пр, Часы) , которые связаны отношением СТУДЕНТ_ПРЕДМЕТ(ФИО, Назв.Пр. Оценка) (рис.2). В связующем отношении атрибуты ФИО и Назв.Пр образуют составной ключ . Эти атрибуты представляют собой внешние ключи, являющиеся первичными ключами других отношений.

Рис.2. Связь отношений

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

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

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

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

Основной единицей обработки данных в реляционных БД является отношение , а не отдельные его кортежи (записи).

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

Термин «индекс» тесно связан с понятием «ключ», хотя между ними есть и некоторое отличие.

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

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

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

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

Общие сведения

Таблицы данных в 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 , а также предложить новую функцию или улучшение на веб-сайте

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

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

· определение цели создания базы данных;

· определение таблиц, которые должна содержать база данных;

· определение необходимых в таблице полей;

· определение полей с уникальными значениями в каждой записи;

· определение связей между таблицами.

Определение цели создания базы данных

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

Определение таблиц, которые должна содержать база данных

Определение необходимых в базе данных таблиц может оказаться самым непростым этапом процесса проектирования базы данных, поскольку результаты, которые должна выдавать база данных -- отчеты, формы и т.п. -- не всегда дают полное представление о структуре таблиц, по которым они создаются. Для проектирования таблиц вовсе не обязательно использовать Microsoft Access. Сначала лучше разработать структуру на бумаге. При разработке таблиц рекомендуется руководствоваться следующими основными принципами:1) Сведения не должны дублироваться в таблице или между таблицами. В этом отношении таблицы в реляционной базе данных отличаются от таблиц в приложениях, работающих с таблицами в текстовом формате, таких как редакторы электронных таблиц. Данные, хранящиеся только в одной таблице, обновляются только в этой таблице. Это более эффективно и, кроме того, исключает возможность дублирования записей, содержащих разные сведения. Например, адрес и номер телефона каждого клиента достаточно сохранить один раз, в одной таблице.2) Каждая таблица должна содержать информацию только на одну тему. Когда каждая таблица содержит сведения только по одной теме, со сведениями по каждой теме можно работать независимо от остальных тем. Например, адрес клиента хранится отдельно от заказов этого клиента, что позволяет удалить один запрос, сохранив сведения о клиенте. В данной базе данных содержится семь таблиц: жилищная организация, жилой дом, квартира, жильцы, информация о жильцах, услуга, информация о выполненной услуге.

Определение необходимых в таблице полей

Каждая таблица содержит сведения по конкретной теме, а каждое поле в таблице содержит конкретный факт по теме таблицы. Например, таблица «Жильцы» содержит поля фамилия, имя, отчество, а конкретный факт это конкретный человек, например, Иванов Иван Иванович.

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

· Каждое поле должно быть связано с темой таблицы;

· Таблица должна содержать все необходимые сведения;

· Данные следует разбить на наименьшие логические единицы.

Определение полей с уникальными значениями в каждой записи

Для связывания в Microsoft Access сведений, хранящихся в разных таблицах - например, для связывания паспортистки со всеми ее клиентами - каждая таблица базы данных должна содержать поля или набор полей, однозначно определяющих каждую запись. Такое поле или набор полей называют первичным ключом. Например, таблица “Жилищная организация” связана с таблицей “Жилой дом” связью “один ко многим”, следовательно, таблица “Жилой дом” имеет вторичный ключ “Номер организации”, а первичный ключ “Номер дома”.

Определение связей между таблицами

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