Множественные связи между файлами можно установить в следующей модели данных
Обновлено: 04.07.2024
Существуют 3 модели данных - реляционная, сетевая и иерархическая; у них разные множества допустимых информационных конструкций. Вообще можно говорить о наличии самостоятельной модели данных в каждой СУБД. Однако, при создании СУБД происходит модификация модели данных, исходя из удобства программной реализации системы.
Внутреннее описание определяет организацию данных в памяти ЭВМ и организацию доступа к ним. Оно соответствует наиболее детальному представлению о процессах обработки данных в системе.
Реляционная модель данных
Модель данных - указание множества допустимых информационных конструкций, операций над данными и множества ограничений для хранимых значений данных.
Концепция реляционной модели данных была предложена Е.Ф. Коддом в 1970 г. в связи с необходимостью обеспечить независимость представления и описания данных от прикладных программ.
Основа реляционной модели - отношение (relation). Оно удобно представляется двумерной таблицей при соблюдении определенных ограничивающих условий. Таблица понятна, обозрима и привычна для человека (см. рис.15).
Рис.15 Пример таблицы реляционной модели данных
Набор отношений (таблиц) может быть использован для хранения данных об объектах реального мира и моделирования связей между ними. Ниже приведенная схема представляет термины реляционной модели.
Схема отношения: СОТРУДНИКИ (Фамилия, Должность, Возраст). Число атрибутов - степень отношения, число кортежей - мощность отношения.
Реляционная база данных - набор взаимосвязанных отношений. Каждое отношение (таблица) представляется в памяти компьютера в виде файла.
Существуют следующие соответствия понятий:
| Сущность (класс) | Отношение | Таблица | Файл |
| Экземпляр (объект) Атрибут | Кортеж Атрибут | Строка Столбец | Запись Поле |
Оригинальность подхода Кодда состояла в том, что он предложил применять к отношениям стройную систему операций, позволяющих получать (выводить, вычислять подобно арифметическим операциям) одни отношения из других. Это дает возможность делить информацию на хранимую и нехранимую (вычисляемую) части, тем самым экономя память.
Основных операций над отношениями насчитывается 8:
- традиционные операции над множествами (объединение, пересечение, разность (вычитание), декартово произведение, деление);
- специальные реляционные операции: проекция, соединение и выбор (селекция, ограничение).
Языки для выполнения операций над отношениями делят на 2 класса:
1) языки реляционной алгебры, описывающие последовательность действий для получения желаемого результата; это процедурные языки.
2) языки реляционного исчисления, предоставляющие пользователю набор правил для записи “запросов” к базе данных, в которых содержится только информация о желаемом результате. Пример - языки запросов SQL (Structured Query Language).
Реляционная база данных в целом соответствующая 3НФ, обладает рядом свойств, знание которых облегчает и упорядочивает процедуры обработки хранящейся в ней информации. Типичные процедуры, выполняемые с базой данных: выборка, корректировка и арифметические вычисления.
Условия запросов второго типа могут комбинироваться с помощью логических операций И, ИЛИ, НЕ.
Существуют правила реализации запросов к базе данных с помощью операторов реляционной алгебры:
1. В словесной формулировке запроса выделяются имена атрибутов, составляющие оболочку, вход и выход запроса, а такие условия выборки.
2. Зафиксировать множество атрибутов оболочки. Если все необходимые атрибуты находятся в одном отношении, то последующие операции выборки и проекции проводятся только с ним. Если они распределены по нескольким отношениям, то эти отношения необходимо соединить. Каждая пара отношений соединяется по условию равенства атрибутов с совпадающими именами (или определенных на общем домене). После каждого соединения с помощью проекции можно отсечь ненужные для последующих операций атрибуты.
3. Полученное единственное отношение далее обрабатывается операциями выборки и проекции. Выборка по значениям атрибута должна предшествовать проекции, в которой этот атрибут выводится из отношения.
4. Если запрос можно разделить на части (подзапросы), то его реализация также делится на части, где результатом подзапроса является отдельное отношение.
5. Указанная последовательность действий является стандартной, но, возможно, создает промежуточные отношения слишком большого размера. Этот недостаток можно компенсировать, выполняя некоторые выборки и проекции над исходными отношениями (до проведения соединения) и меняя взаимный порядок требуемых соединений.
Иерархическая модель данных
Структура данных называется иерархической, если ее схема представлена в виде дерева. Узлами дерева-схемы являются записи, дугами - иерархические связи между записями. Иерархическая связь предполагает, что одной «верхней» записи соответствует несколько реализаций «нижней», т.е. структура использует связи вида «один-ко-многим».
Что представляет собой запись в иерархической структуре? Множество записей, относящихся к заданному узлу схемы, рассматриваемое вне связи с другими узлами, имеет сходство с нормализованным файлом и характеризуется уникальной совокупностью атрибутов.
Совокупность иерархически организованных записей называется иерархической базой данных; отдельный тип записи, соответствующий узлу схемы, называется сегментом.
Если два узла дерева соединены дугой, то узел более высокого уровня называется порождающим, а узел более низкого уровня - порожденным (или подчиненным).
Всякий узел иерархической структуры (кроме корня дерева) имеет один порождающий узел. Ниже (см. рис.16) изображен пример схемы иерархической структуры.
Рис. 16. Пример иерархической модели данных
По заданной иерархической схеме может быть построен файл в первой нормальной форме, иначе, иерархическая база данных может быть преобразована в один или несколько нормализованных файлов. Возможно обратное преобразование: построение иерархической базы данных по одному или совокупности нормализованных файлов.
Для нормализации иерархического файла достаточно каждый узел дерева заменить нормализованным файлом со схемой, представляющей собой объединение атрибутов данного узла с ключами всех узлов, которые мы будем проходить при движении от данного узла по дугам в направлении снизу вверх. Результат операции объединения не изменится, если вместо ключей узлов будем писать дополнения до ключей (ключи только порожденных узлов без ключей порождающих).
Сетевая модель данных
Рассмотрим табель учета рабочего времени
Исходный документ не нормализован. В клетках указано количество рабочих часов данного сотрудника в данный день.
Таблица позволяет получать ответы на запросы двух типов:
1. По заданной фамилии сотрудника сообщить его рабочие часы на различные даты.
2. По заданной дате сообщить рабочие часы на эту дату для различных сотрудников.
В нормализованном виде получим таблицу:
В составе нормализованного файла можно выделить СЕИ, которые располагаются то в ключе, то в результате запроса, и атрибут Q5, всегда относящийся к результату запроса. Запросы такого вида называются инверсными по отношению друг к другу, СЕИ S12 и S34 назовем инверсным условием поиска, атрибут Q5 - информацией связи. Этим запросам можно поставить в соответствие следующие иерархические структуры, изображенные на рис. 17.
В обоих случаях информация связи располагается в подчиненном сегменте. Структура, обеспечивающая ответы на запросы обоих типов, имеет вид, представленный на рис.18:
Рис.18 V-образная сетевая схема
Такая структура называется V-образной сетевой схемой. Она содержит два порождающих сегмента и один подчиненный. Тип связей, соответствующий схеме - “один-ко-многим”.
Подчиненный сегмент Q5 является информацией связи между S12 и S34.
Экземпляр рассматриваемой V-образной сетевой схемы называется сетевой БД. Его нормализованное представление - таблица ТАБЕЛЬ_1, а двухвходовое - ТАБЕЛЬ (двухвходовая таблица).
Я уже показал вам как данные из разных таблиц могут быть связаны при помощи связи по внешнему ключу. Вы видели как заказы связываются с клиентами путем помещения customer_id в качестве внешнего ключа в таблице заказов.
Другой пример связи один-ко-многим – это связь, которая существует между матерью и ее детьми. Мать может иметь множество детей, но каждый ребенок может иметь только одну мать.
(Технически лучше говорить о женщине и ее детях вместо матери и ее детях потому, что, в контексте связи один-ко-многим, мать может иметь 0, 1 или множество потомков, но мать с 0 детей не может считаться матерью. Но давайте закроем на это глаза, хорошо?)
Когда одна запись в таблице А может быть связана с 0, 1 или множеством записей в таблице B, вы имеете дело со связью один-ко-многим. В реляционной модели данных связь один-ко-многим использует две таблицы.
Схематическое представление связи один-ко-многим. Запись в таблице А имеет 0, 1 или множество ассоциированных ей записей в таблице B.
Как опознать связь один-ко-многим?
Если у вас есть две сущности спросите себя:
1) Сколько объектов и B могут относится к объекту A?
2) Сколько объектов из A могут относиться к объекту из B?
Если на первый вопрос ответ – множество, а на второй – один (или возможно, что ни одного), то вы имеете дело со связью один-ко-многим.
Примеры.
Некоторые примеры связи один-ко-многим:
- Машина и ее части. Каждая часть машины единовременно принадлежит только одной машине, но машина может иметь множество частей.
- Кинотеатры и экраны. В одном кинотеатре может быть множество экранов, но каждый экран принадлежит только одному кинотеатру.
- Диаграмма сущность-связь и ее таблицы. Диаграмма может иметь больше, чем одну таблицу, но каждая из этих таблиц принадлежит только одной диаграмме.
- Дома и улицы. На улице может быть несколько домов, но каждый дом принадлежит только одной улице.
В данном случае все настолько просто, что только поэтому может оказаться трудным понимание. Возьмем последний пример с домами. На улице ведь действительно может быть любое количество домов, но у каждого дома именно на этой улице может быть только одна улица (не берем дома, которые на практике принадлежат разным улицам, возьмем, к примеру, дом в центре улицы). Ведь не может конкретно этот дом быть одновременно в двух местах, на двух разных улицах, а мы говорим не про какой-то абстрактный дом вообще, а про конкретный.
8. Связь многие-ко-многим.
Связь многие-ко-многим – это связь, при которой множественным записям из одной таблицы (A) могут соответствовать множественные записи из другой (B). Примером такой связи может служить школа, где учителя обучают учащихся. В большинстве школ каждый учитель обучает многих учащихся, а каждый учащийся может обучаться несколькими учителями.
Связь между поставщиком пива и пивом, которое они поставляют – это тоже связь многие-ко-многим. Поставщик, во многих случаях, предоставляет более одного вида пива, а каждый вид пива может быть предоставлен множеством поставщиков.
Обратите внимание, что при проектировании базы данных вы должны спросить себя не о том, существуют ли определенные связи в данный момент, а о том, возможно ли существование связей вообще, в перспективе. Если в настоящий момент все поставщики предоставляют множество видов пива, но каждый вид пива предоставляется только одним поставщиком, то вы можете подумать, что это связь один-ко-многим, но… Не торопитесь реализовывать связь один-ко-многим в этой ситуации. Существует высокая вероятность того, что в будущем два или более поставщиков будут поставлять один и тот же вид пива и когда это случится ваша база данных — со связью один-ко-многим между поставщиками и видами пива – не будет подготовлена к этому.
Создание связи многие-ко-многим.
Связь многие-ко-многим создается с помощью трех таблиц. Две таблицы – “источника” и одна соединительная таблица. Первичный ключ соединительной таблицы A_B – составной. Она состоит из двух полей, двух внешних ключей, которые ссылаются на первичные ключи таблиц A и B.
Все первичные ключи должны быть уникальными. Это подразумевает и то, что комбинация полей A и B должна быть уникальной в таблице A_B.
Пример проект базы данных ниже демонстрирует вам таблицы, которые могли бы существовать в связи многие-ко-многим между бельгийскими брендами пива и их поставщиками в Нидерландах. Обратите внимание, что все комбинации beer_id и distributor_id уникальны в соединительной таблице.
Таблицы “о пиве”.
Таблицы выше связывают поставщиков и пиво связью многие-ко-многим, используя соединительную таблицу. Обратите внимание, что пиво 'Gentse Tripel' (157) поставляют Horeca Import NL (157, AC001) Jansen Horeca (157, AB899) и Petersen Drankenhandel (157, AC009). И vice versa, Petersen Drankenhandel является поставщиком 3 видов пива из таблицы, а именно: Gentse Tripel (157, AC009), Uilenspiegel (158, AC009) и Jupiler (163, AC009).
Еще обратите внимание, что в таблицах выше поля первичных ключей окрашены в синий цвет и имеют подчеркивание. В модели проекта базы данных первичные ключи обычно подчеркнуты. И снова обратите внимание, что соединительная таблица beer_distributor имеет первичный ключ, составленный из двух внешних ключей. Соединительная таблица всегда имеет составной первичный ключ.
Есть еще одна важная вещь на которую нужно знать. Связь многие-ко-многим состоит из двух связей один-ко-многим. Обе таблицы: поставщики пива и пиво – имеют связь один-ко-многим с соединительной таблицей.
Другой пример связи многие-ко-многим: заказ билетов в отеле.
В качестве последнего примера позвольте мне показать как бы могла быть смоделирована таблица заказов номеров гостиницы посетителями.
Соединительная таблица связи многие-ко-многим имеет дополнительные поля.
В этом примере вы видите, что между таблицами гостей и комнат существует связь многие-ко-многим. Одна комната может быть заказана многими гостями с течением времени и с течением времени гость может заказывать многие комнаты в отеле. Соединительная таблица в данном случае является не классической соединительной таблицей, которая состоит только из двух внешних ключей. Она является отдельной сущностью, которая имеет связи с двумя другими сущностями.
Вы часто будете сталкиваться с такими ситуациями, когда совокупность двух сущностей будет являться новой сущностью.
9. Связь один-к-одному.
В связи один-к-одному каждый блок сущности A может быть ассоциирован с 0, 1 блоком сущности B. Наемный работник, например, обычно связан с одним офисом. Или пивной бренд может иметь только одну страну происхождения.
В одной таблице.
Связь один-к-одному легко моделируется в одной таблице. Записи таблицы содержат данные, которые находятся в связи один-к-одному с первичным ключом или записью.
В отдельных таблицах.
В редких случаях связь один-к-одному моделируется используя две таблицы. Такой вариант иногда необходим, чтобы преодолеть ограничения РСУБД или с целью увеличения производительности (например, иногда — это вынесение поля с типом данных blob в отдельную таблицу для ускорения поиска по родительской таблице). Или порой вы можете решить, что вы хотите разделить две сущности в разные таблицы в то время, как они все еще имеют связь один-к-одному. Но обычно наличие двух таблиц в связи один-к-одному считается дурной практикой.
Примеры связи один-к-одному.
- Люди и их паспорта. Каждый человек в стране имеет только один действующий паспорт и каждый паспорт принадлежит только одному человеку.
Проект реляционной базы данных – это коллекция таблиц, которые перелинковываются (связываются) первичными и внешними ключами. Реляционная модель данных включает в себя ряд правил, которые помогают вам создать верные связи между таблицами. Эти правила называются “нормальными формами”. В следующих частях я покажу как нормализовать вашу базу данных.
Какой же вид связи вам нужен?
Примеры связей таблиц на практике. Когда какие-то данные являются уникальными для конкретного объекта, например, человек и номера его паспортов, то имеем дело со связью один-ко-многим. Т.е. в одной таблице мы имеем список неких людей, а в другой таблице у нас есть перечисление номеров паспортов этого человека (напр., паспорт страны проживания и загранпаспорт). И эта комбинация данных уникальная для каждого человека. Т.е. у каждого человека может быть несколько номеров паспортов, но у каждого паспорта может быть только один владелец. Итого: нужны две таблицы.
А если есть некие данные, которые могу быть присвоены любому человеку, то имеем дело со связью многие-ко-многим. Например, есть таблица со списком людей и мы хотим хранить информацию о том, какие страны посетил каждый человек. В данном случае имеется две сущности: люди и страны. Любой человек может посетить любое количество стран равно, как и любая страна может быть посещена любым человеком. Т.е., в данном случае, страна не является уникальными данными для конкретного человека и может использоваться повторно.
А когда у вас есть набор уникальных данных, которые имеют отношение только друг к другу, то храните все в одной таблице. Ваш выбор – связь один-к-одному. Например, у вас есть небольшая коллекция автомобилей и вы хотите хранить информацию о них (цвет, марка, год выпуска и пр.).
Чтобы сделать анализ данных более емким, создайте связи в разных таблицах. Связь — это соединение между двумя таблицами, которые содержат данные: один столбец в каждой таблице является основой для связи. Чтобы понять, чем полезны связи, представим, что отслеживаются данные для заказов клиентов в бизнесе. Вы можете отслеживать все данные в одной таблице с такой структурой:
DiscountRate
Однообъективный зеркальный фотоаппарат
Этот подход может быть эффективным, но он подразумевает хранение множества избыточных данных, таких, как адрес электронной почты клиента для каждого заказа. Хранение данных обходится дешево, но если адрес электронной почты изменился, необходимо убедиться, чтоб была обновлена каждая строка для этого клиента. Одним из решений этой проблемы является разбиение данных на несколько таблиц и задание связей между этими таблицами. Этот подход используется в реляционных базах данных таких, как SQL Server. Например, импортированная база данных может представлять данные заказа, используя три связанные таблицы.
CustomerDiscounts
DiscountRate
Однообъективный зеркальный фотоаппарат
Связи существуют в модели данных ( та, которую вы создали явным образом или которая Excel автоматически создается от вашего имени при одновременном импорте нескольких таблиц. Кроме того, вы можете воспользоваться надстройкой Power Pivot для создания модели и управления ею. Дополнительные сведения см. в статье Создание модели данных в Excel.
Во время импорта таблицы из одной базы данных с помощью надстройки Power PivotPower Pivot может обнаруживать связи между таблицами, основанными на столбцах, заключенных в [квадратные скобки], и воспроизводить эти связи в модели данных, создаваемой в фоновом режиме. Дополнительные сведения см. в разделе Автоматическое обнаружение и вывод связей этой статьи. Если таблицы импортируются из нескольких источников, можно вручную создать связи, как это описано в статье Создание связей между двумя таблицами.
Связи основываются на столбцах в каждой таблице, содержащих одинаковые данные. Например, можно связать таблицу "Клиенты" с таблицей "Заказы", если каждая из них содержит столбец с ИД клиента. В данном примере имена столбцов одинаковы, но это не является обязательным условием. Один столбец может называться CustomerID, а другой — CustomerNumber, при условии, что все строки в таблице Orders содержат идентификатор, который также хранится в таблице Customers.
В реляционной базе данных существует несколько типов ключей. Ключ обычно является столбцом со специальными свойствами. Знание назначения каждого ключа помогает в управлении моделью данных с несколькими таблицами, предоставляющей данные для сводной таблицы, сводной диаграммы или отчета Power View.
Хотя существует множество типов ключей, они являются самыми важными в нашем предназначении:
Первичный ключ: однозначно определяет строку в таблице, например CustomerID в таблице Customers.
Альтернативный ключ (или первичный ключ): уникальный столбец, который не является первичным ключом. Например, таблица Employees может хранить идентификатор работника и номер карточки социального страхования, при том что оба они являются уникальными.
Внешнее ключ: столбец, который ссылается на уникальный столбец другой таблицы, например CustomerID в таблице Orders, который ссылается на CustomerID в таблице Customers.
В модели данных первичный или резервный ключ называется связанным столбцом. Если таблица содержит первичный и резервный ключ, любой из них можно использовать как основу для связи между таблицами. Внешний ключ называется исходным столбцом или просто столбцом. В нашем примере связь между customerID в таблице Orders (столбцом) и CustomerID в таблице Customers (столбцом подытов) будет определена. Если данные импортируются из реляционной базы данных, по умолчанию Excel выбирает внешний ключ из одной таблицы и соответствующий первичный ключ из другой таблицы. Тем не менее для столбца подстановки можно использовать любой столбец, содержащий уникальные значения.
Связь между клиентом и заказом является связью "один-к-многим". Каждый клиент может иметь несколько заказов, однако ни один из заказов не может иметь несколько клиентов. Еще одна важная связь между таблицами — "один к одному". В нашем примере таблица CustomerDiscounts, которая определяет единую ставку дисконтирования для каждого клиента, имеет отношение "один-к-одному" с таблицей Customers.
В этой таблице показаны связи между тремя таблицами(Customers, CustomerDiscountsи Orders):
Столбец подстановки
Примечание: Связи «многие ко многим» не поддерживаются в модели данных. Примером связи «многие ко многим» является прямая связь между таблицами Products и Customers, в которой заказчик может купить много продуктов и одинаковый продукт может быть одновременно куплен несколькими заказчиками.
После создания связи необходимо Excel пересчет всех формул, которые используют столбцы из таблиц в созданной связи. Обработка может занять некоторое время в зависимости от объема данных и сложности связей. Дополнительные сведения см. в теме Пересчет формул.
Модель данных может содержать несколько связей между двумя таблицами. Для точного вычисления Excel требуется один путь от одной таблицы к другой. Поэтому одновременно активной может быть только одна связь между каждой парой таблиц. Хотя другие неактивны, вы можете указать неактивное отношение в формулах и запросах.
В представлении диаграммы активная связь является сплошной линией, а неактивные — пунктирными линиями. Например, в таблице AdventureWorksDW2012 таблица DimDate содержит столбец DateKey, связанный с тремя разными столбцами в таблице FactInternetSales:OrderDate, DueDateи ShipDate. Если есть активная связь между столбцами DateKey и OrderDate, эта связь и будет использоваться по умолчанию в формулах, если не указано иное.
Связь можно создать, если выполняются следующие требования.
Уникальный идентификатор для каждой таблицы
Каждая таблица должна иметь один столбец, однозначно определяющий каждую строку в этой таблице. Такой столбец часто именуется первичным ключом.
Уникальные столбцы подстановки
Значения данных в столбце подстановки должны быть уникальными. Другими словами, столбец не может содержать дубликаты. В модели данных нули и пустые строки эквивалентны пустому полю, которое является самостоятельным значением данных. Это значит, что столбец подстановки не может содержать несколько значений NULL.
Совместимые типы данных
Типы данных в исходном столбце и в столбце подстановки должны быть совместимыми. Дополнительные сведения о типах данных см. в теме Типы данных, поддерживаемые в моделях данных.
Неподтверченные функции базы данных в Excel данныхВ модели данных нельзя создать связь между таблицами, если ключ является составным. Также существует ограничение на создание связей «один к одному» и «один ко многим». Другие типы связей не поддерживаются.
Составные ключи и столбцы подстановки
Составной ключ состоит из нескольких столбцов. Модели данных не могут использовать составные ключи: таблица должна всегда иметь ровно один столбец, однозначно определяя каждую строку в таблице. При импорте таблиц, имеющих существующую связь на основе составного ключа, мастер импорта таблиц в Power Pivot не будет учитывать эту связь, так как ее нельзя создать в модели.
Для создания связи между двумя таблицами, имеющими несколько столбцов, в которых определены первичный и внешние ключи, сначала объедините значения для создания единого ключевого столбца. Это можно сделать перед импортом данных или путем создания вычисляемого столбца в модели данных с помощью надстройки Power Pivot.
Связи «многие ко многим»
Модель данных не может иметь связи «многие ко многим». В модель нельзя добавлять соединяющие таблицы . Тем не менее для моделирования связей «многие ко многим» можно использовать функции DAX.
Самосоединения и циклы
В модели данных не разрешается использование самосоединений. Самосоединение — это рекурсивная связь таблицы с самой собой. Самосоединения часто используются для определения иерархий типа «родители-потомки». Например, можно настроить самосоединение для таблицы Employees, чтобы создать иерархию, показывающую цепочку управления на предприятии.
Excel не позволяет создавать циклы среди связей в книге. Иными словами, следующий набор связей запрещается.
Таблица 1, столбец «а» к Таблице 2, столбец «f»
Tаблица 2, столбец «f» к Таблице 3, столбец «n».
Таблица 3, столбец «n» к Таблице 1, столбец «a».
При попытке создания связи, которая приведет к образованию цикла, выдается ошибка.
Автоматическое обнаружение и вывод связей в Power PivotОдно из преимуществ импорта данных с помощью надстройки Power Pivot заключается в том, что Power Pivot иногда может обнаруживать связи и создавать новые связи в модели данных, создаваемой в Excel.
При импорте нескольких таблиц Power Pivot автоматически определяет все существующие связи между ними. Кроме того, при создании сводной таблицы Power Pivot анализирует данные в таблицах. Он обнаруживает возможные связи, которые не были определены, и предлагает столбцы, которые можно включить в них.
Алгоритм обнаружения на основании статистических данных о значениях и метаданных столбцов формирует выводы о вероятности связей.
Типы данных во всех связанных столбцах должны быть совместимыми. Для автоматического обнаружения поддерживаются только целочисленные и текстовые типы данных. Дополнительные сведения о типах данных см. в разделе Типы данных, поддерживаемые вмоделях данных.
Для успешного обнаружения связи количество уникальных ключей в столбце подстановки должно превышать количество значений в таблице на стороне «многие». Другими словами, ключевой столбец на стороне «многие» связи не должен содержать значений, не содержащихся в ключевом столбце таблицы подстановки. Например, предположим, что имеется таблица, в которой перечислены продукты и их идентификаторы (таблица подстановки), а также таблица продаж, содержащая данные продаж всех продуктов (сторона «многие» связи). Если записи продаж содержат идентификатор продукта, не имеющего соответствующий идентификатор в таблице Products, связь нельзя создать автоматически, но можно создать вручную. Для обеспечения обнаружения связи с помощью Excel необходимо сначала обновить таблицу подстановки Product с использованием идентификаторов недостающих продуктов.
Убедитесь, что имя ключевого столбца на стороне «многие» совпадает с именем ключевого столбца в таблице подстановки. Имена не должны быть абсолютно идентичны. Например, в бизнес-параметрах часто имеются варианты имен столбцов, которые содержат фактически одинаковые данные: "ИД сотрудника", "ИД сотрудника", "ИД сотрудника", "EMP_ID"и так далее. Алгоритм выявляет похожие имена и задает более высокие значения вероятности столбцам, имена которых похожи или полностью совпадают. Поэтому, чтобы увеличить вероятность создания связи, можно попытаться переименовать столбцы в импортируемых данных, подобрав имена чем-то похожие на имена строк в существующих таблицах. Если Excel находит несколько возможных связей, связь не создается.
Эти сведения помогают понять, почему не удалось выявить все связи и какие изменения в метаданных (именах полей и типах данных) могут повысить эффективность автоматического обнаружения связей. Дополнительные сведения см. в разделе Устранение неполадок в связях.
Автоматическое обнаружение именованных наборов
Связи между именованными наборами и связанными полями в сводной таблице не обнаруживаются автоматически. Такие связи можно создать вручную. При необходимости использования автоматического обнаружения связей удалите каждый именованный набор и добавьте отдельные поля из именованного набора непосредственно в сводную таблицу.
Вывод связей
В некоторых случаях связи между таблицами автоматически объединяются в цепочки. Например, если создать связь между первыми двумя наборами таблиц, указанных ниже, то определяется наличие связи между другими двумя таблицами и эта связь устанавливается автоматически.
Products и Category — связь создается вручную
Category и SubCategory — связь создается вручную
Products и SubCategory — связь определяется автоматически
Для автоматического объединения связей в цепочки эти связи должны идти в одном направлении, как показано выше. Если исходные связи были установлены, например между таблицами Sales и Products, а также между Sales и Customers, то связь не выводится. Это вызвано тем, что связь между таблицами Products и Customers является связью «многие ко многим».
Примечание:
Во всех статьях текущей категории уроков по SQL используются примеры и задачи, основанные на учебной базе данных.
Приступая к изучению данного материала, рекомендуется ознакомиться с описанием учебной БД.
Практически всегда БД не ограничивается одной таблицей. Сложно представить себе какой-либо бизнес-процесс на предприятии, который мог бы сконцентрироваться только на одном предмете в плане информации.
Рассмотрим пример учебной базы данных. Имеется отдел, который занимается обработкой звонков, поступающих на различные линии. Линии обслуживаются конкретными операторами. Операторы состоят в разных группах под присмотром супервайзеров.
Только из данного краткого описания можно выделить несколько самостоятельных объектов:
Ознакомившись с диаграммой базы данных, можно обратить внимание на то, что некоторая информация из одних таблиц присутствует в других, т.е. между ними имеются связи.
В нашем конкретном случае, все таблицы можно соединить между собой. Чтобы понять, как это правильно сделать, необходимо рассмотреть типы связей.
Логику соединения таблиц в БД важно понять с самого начала изучения SQL, так как наверняка Вы не будете писать запросы только к одной таблице.
Всего существует 3 типа связей:
Примечание:
В данном материале обозначения связей приводятся на примере MS SQL Server. В иных СУБД они могут обозначаться по-разному, но у Вас не должно возникнуть проблем с определением их типа, т.к. они либо очень похожи, либо интуитивно понятны.
Связь «Один к одному»
Связь один к одному образуется, когда ключевой столбец (идентификатор) присутствует в другой таблице, в которой тоже является ключом либо свойствами столбца задана его уникальность (одно и тоже значение не может повторяться в разных строках).
На практике связь «один к одному» наблюдается не часто. Например, она может возникнуть, когда требуется разделить данных одной таблицы на несколько отдельных таблиц с целью безопасности.
В учебной безе данных нет подходящего примера, но гипотетически могла бы существовать необходимость разделения таблицы сотрудников.
Пример:
Представьте, что базой данных пользуются несколько менеджеров и аналитиков, а таблица «Сотрудники» содержит те же столбцы, что и учебная база. Следовательно, доступ к персональным данным может получить любой из упомянутых работников.
Чтобы устранить возможность утечки конфиденциальной информации, принимается решение о переносе информации паспортных данных в отдельную таблицу, доступ к которой предоставляется ограниченному кругу лиц.
Связь «Один ко многим»
В типе связей один ко многим одной записи первой таблицы соответствует несколько записей в другой таблице.
Рассмотрим связь учебной базы данных между должностями и сотрудниками, которая относится к рассматриваемому типу.
Записи должностей в таблице «Должность» уникальны, так как нет смысла повторно создавать имеющуюся запись. Записи в таблице «Сотрудники» также уникальны, но несколько различных сотрудников могут находиться на одинаковой должностной позиции.
Символ ключа на конце связи указывает, что таблица, к которой этой конец прилегает, находится на стороне «один» (связанный столбец является первичным ключом), а символ бесконечности находится на стороне «многие» (такой столбец является внешним ключом).
Связь «Многие ко многим»
Если нескольким записям из одной таблицы соответствует несколько записей из другой таблицы, то такая связь называется «многие ко многим» и организовывается посредством связывающей таблицы.
В нашей базе подобное наблюдается только между таблицами с сотрудниками и линиями.
Из диаграммы видно, что имеются две связи «один ко многим» (один сотрудник может обрабатывать несколько телефонных линий, и одну линию могут обрабатывать несколько сотрудников), но в совокупности они образуют связь «многие ко многим».
Для чего все это нужно?
Связи выполняют более важную роль, чем просто информация размещения данных по таблицам. Прежде всего они требуются разработчикам для поддержания целостности баз данных.
Правильно настроив связи, можно быть уверенным, что ничего не потеряется.
Представьте, что Вы решили удалить одну из групп в таблице учебной базы данных. Если бы связи не было, то для тех сотрудников, которые к ней были определены, остался идентификатор несуществующей группы. Связь не позволит удалить группу, пока она имеется во внешних ключах других таблиц. Для начала следовало определить сотрудников в другие имеющиеся или новые группы, а только затем удалить ненужную запись. Поэтому связи называют еще ограничениями.
Читайте также: