Занятие 5-3. определение связи «многие ко многим»lesson 5-3 — defining a many-to-many relationship

Элемент области навигации для основной сущности

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

Использование связей с кратностью «многие ко многим» вместо временного решенияUse a relationship with a many-many cardinality instead of the workaround

Начиная июльской версии 2018 г., в Power BI Desktop можно напрямую связывать таблицы, например те, которые мы упоминали выше, не прибегая к использованию аналогичных временных решений.With the July 2018 version of Power BI Desktop, you can directly relate tables, such as the ones we described earlier, without having to resort to similar workarounds. Теперь можно задать кратность связи многие ко многим.It’s now possible to set the relationship cardinality to many-to-many. Этот параметр указывает, что ни одна из таблиц не содержит уникальные значения.This setting indicates that neither table contains unique values. Такие связи позволяют контролировать, какая таблица фильтрует другую таблицу.For such relationships, you may still control which table filters the other table. Либо можно применить двунаправленную фильтрацию, где каждая таблица фильтрует другую.Or you can apply bidirectional filtering, where each table filters the other.

В Power BI Desktop по умолчанию задается кратность многие ко многим, когда программа определяет, что ни одна из таблиц не содержит уникальные значения столбцов связи.In Power BI Desktop, the cardinality defaults to many-to-many when it determines neither table contains unique values for the relationship columns. В таких случаях отображается предупреждение с сообщением о том, что вы хотите установить связь, и изменение не является случайным результатом проблемы с данными.In such cases, a warning message confirms you want to set a relationship, and the change isn’t the unintended effect of a data issue.

Например, при создании связи непосредственно между CityData и Sales—, где фильтры должны применяться от CityData к Sales, в —Power BI Desktop отображается диалоговое окно Изменение связи.For example, when you create a relationship directly between CityData and Sales—where filters should flow from CityData to Sales—Power BI Desktop displays the Edit relationship dialog box:

Итоговое представление связей будет содержать прямую связь «многие ко многим» между двумя таблицами.The resulting Relationship view would then display the direct, many-to-many relationship between the two tables. Внешний вид таблиц в списке Поля и их последующее поведение при создании визуальных элементов аналогично ситуации, в которой мы применили обходной путь.The tables’ appearance in the Fields list, and their later behavior when the visuals are created, are similar to when we applied the workaround. Там мы создали дополнительную скрытую таблицу уникальных штатов.In the workaround, the extra table that displays the distinct State data isn’t made visible. Как было сказано выше, отображается визуальный элемент со сведениями о штате, населении и продажах.As described earlier, a visual that shows State, Population, and Sales data would be displayed:

Основные различия между связями с кратностью «многие ко многим» и более распространенными связями многие к одному заключается в следующем.The major differences between relationships with a many-many cardinality and the more typical Many-1 relationships are as follows:

• Значения, показываемые в них, не содержат пустую строку, отвечающую за несовпадающие строки в другой таблице.The values shown don’t include a blank row that accounts for mismatched rows in the other table. Кроме того, значения не отвечают за строки, в которых столбец, используемый для связи в другой таблице, имеет значение NULL.Also, the values don’t account for rows where the column used in the relationship in the other table is null.

• Использовать функцию (так как связанными могут быть несколько строк) не удастся.You can’t use the function, because more than one row could be related.

• При использовании функции в таблице не удаляются фильтры, примененные к другим таблицам, у которых с ней установлена связь «многие ко многим».Using the function on a table doesn’t remove filters that are applied to other, related tables by a many-to-many relationship. В предыдущем примере мера, определенная согласно данному скрипту, не удалит фильтры по столбцам связанной таблицы CityData.In the preceding example, a measure that’s defined as shown here wouldn’t remove filters on columns in the related CityData table:

  Визуальный элемент с отображением штата, объема продаж и общего объема продаж будет таким, как на рисунке ниже.A visual showing State, Sales, and Sales total data would result in this graphic:

Учитывая перечисленные различия, убедитесь, что вычисления, использующие , такие как % от общей суммы, возвращают желаемые результаты.With the preceding differences in mind, make sure the calculations that use , such as % of grand total, are returning the intended results.

Добавление необходимых таблиц к представлению источника данныхAdding Required Tables to the Data Source View

Откройте конструктор представлений источника данных для представления источников данных Adventure Works DW 2012 .Open Data Source View Designer for the Adventure Works DW 2012 data source view.

Щелкните правой кнопкой мыши панель Организатор диаграмм , выберите команду Создать диаграмму и укажите Причина заказа через Интернет в качестве имени созданной диаграммы.Right-click anywhere in the Diagram Organizer pane, click New Diagram, and specify Internet Sales Order Reasons as the name for this new diagram.

Перетащите таблицу InternetSales с панели Таблицы на панель Диаграмма .Drag the InternetSales table to the Diagram pane from the Tables pane.

Щелкните правой кнопкой мыши панель Диаграмма и выберите команду Добавить или удалить таблицы.Right-click anywhere in the Diagram pane, and then click Add/Remove Tables.

В диалоговом окне Добавление или удаление таблиц добавьте в список Включенные объекты таблицы DimSalesReason и FactInternetSalesReason , а затем нажмите кнопку ОК.In the Add/Remove Tables dialog box, add the DimSalesReason table and the FactInternetSalesReason table to the Included objects list, and then click OK.
Обратите внимание, что связи «первичный-внешний ключ» между задействованными таблицами устанавливаются автоматически, поскольку эти связи определяются в базовой реляционной базе данных.Notice that the primary key-foreign key relationships between the tables that are involved are established automatically because those relationships are defined in the underlying relational database. Если эти связи не определены в базовой реляционной базе данных, их следует определить в представлении источника данных.If these relationships were not defined in the underlying relational database, you would have to define them in the data source view.

В меню Формат выберите команду Автоматический макет и щелкните значок Диаграмма.On the Format menu, point to Auto Layout, and then click Diagram.

В окне свойств измените свойство FriendlyName таблицы DimSalesReason на SalesReason, затем измените свойство FriendlyName таблицы FactInternetSalesReason на InternetSalesReason.In the Properties window, change the FriendlyName property of the DimSalesReason table to SalesReason, and then change the FriendlyName property of the FactInternetSalesReason table to InternetSalesReason.

На панели Таблицы разверните узел InternetSalesReason (dbo.FactInternetSalesReason), щелкните столбец SalesOrderNumber и просмотрите в окне свойств свойство DataType для этого столбца данных.In the Tables pane, expand InternetSalesReason (dbo.FactInternetSalesReason), click SalesOrderNumber, and then review the DataType property for this data column in the Properties window.
Обратите внимание, что в качестве типа данных для столбца SalesOrderNumber указан тип данных string.Notice that the data type for the SalesOrderNumber column is a string data type.

Просмотрите типы данных для других столбцов таблицы FactInternetSalesReason .Review the data types for the other columns in the InternetSalesReason table.
Обратите внимание, что для остальных двух столбцов этой таблицы указаны числовые типы данных.Notice that the data types for the other two columns in this table are numeric data types.

На панели Таблицы щелкните правой кнопкой мыши таблицу InternetSalesReason (dbo.FactInternetSalesReason) и выберите пункт Просмотр данных.In the Tables pane, right-click InternetSalesReason (dbo.FactInternetSalesReason), and then click Explore Data.
Обратите внимание, что для каждого номера строки каждого заказа значение ключа указывает причину покупки данной позиции линии, как показано на следующем рисунке.Notice that, for each line number within each order, a key value identifies the sales reason for the purchase of that line item, as shown in the following image.

Создание и изменение отношений 1:N между сущностями

Откройте обозреватель решений.

В разделе Компоненты раскройте узел Сущности, затем раскройте сущность, с которой требуется работать.

Выберите Отношения 1:N.

Чтобы изменить отношение или просмотреть сведения для отношения, выберите отношение и нажмите на панели инструментов «Действия» кнопку Другие действия, затем выберите Изменить.
— ИЛИ —
Чтобы добавить новое отношение, выберите Создать отношение «один ко многим».

Важно!
Если кнопка Создать отношение «один ко многим» не отображается на панели инструментов «Действия», то создать отношение 1:N для этой сущности невозможно.

Для нового отношения в разделе Определение отношения выберите в списке Связанная сущность сущность для связывания.

Примечание
При указании связанной сущности задается значение по умолчанию в поле Имя. Если изменить связанную сущность перед ее сохранением, соответственно изменится и значение поля Имя.

Выберите, будет ли это поле доступно для поиска или нет.

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

Важно!
При указании значения Отображаемое имя задается значение по умолчанию в поле Имя

Если изменить Отображаемое имя поля поиска перед сохранением данных, значение в поле Имя не изменится. Поэтому необходимо ввести в поле Имя информативное значение перед сохранением данных.

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

В разделе Элемент области переходов для основной сущности в списке Параметры отображения выберите вариант отображения связанных представлений для пользовательской метки.

В разделе Поведение отношений выберите в списке Тип отношений один из следующих вариантов.

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

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

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

Настраиваемое каскадное. В настраиваемом каскадном отношении между двумя сущностями выбирается поведение, связанное с каждым из наборов возможных действий.

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

Дополнительные сведения:

1. Выберите Сохранить и закрыть, чтобы закрыть форму Отношение.

2. Выполнив настройки, опубликуйте их:

   • Чтобы опубликовать настройки только для компонента, изменяемого в данный момент, на панели инструментов «Действия» выберите Опубликовать.

   • Чтобы опубликовать настройки для всех неопубликованных компонентов одновременно, на панели навигации или в области переходов выберите Сущности, затем на панели инструментов «Действия» выберите Опубликовать все настройки.

Примечание

• Настраиваемая сущность не может быть основной в каскадном отношении со связанной системной сущностью. Это означает, что между основной настраиваемой сущностью и связанной системной сущностью не может быть отношений с каким-либо из действий, установленным в «Передавать всем», «Передавать активным» или «Передавать владельцу».
• У новых отношений действие не может иметь значение Передавать всем, Передавать активным или Передавать владельцу, если связанная сущность в этом отношении уже является связанной сущностью в любом другом отношении, действие которого имеет значение Передавать всем, Передавать активным или Передавать владельцу. Это позволяет избежать создания отношений с несколькими родительскими сущностями.
• После каждого изменения элементов пользовательского интерфейса или внедрения скриптов формы для сущности необходима публикация изменений. Все изменения в схеме данных приложения, таких как настраиваемые сущности, связи или поля, применяются сразу.
• Если отношение является частью управляемого решения, разработчик решения может ограничить настройку отношения пользователями.
• Установка решения или публикация настроек может помешать нормальной работе системы. Рекомендуется запланировать импорт решения в оптимальный для пользователей период.

Запросы к отношениям

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

Например, у нас есть блог, в котором модель связана с моделью :

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

Методы отношений и Динамические свойства

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

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

Запрос на существование отношений

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

Вы также можете использовать операторы сравнения:

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

Вы также можете использовать методы и :

Жадная загрузка (eager loading)

Данные об отношениях подгружаются только в момент обращения к динамическому свойству. Жадная загрузка (eager loading) призвана устранить проблему N+1. Например, представьте, что у нас есть модель , которая связана с моделью . Отношение определено как:

Теперь загрузим все книги и их авторов:

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

Теперь давайте заранее получим всех авторов при помощи методов :

Тогда будут выполнены всего два запроса:

Жадная загрузка нескольких отношений

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

Вложенная жадная загрузка

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

Ограничения жадной загрузки

Иногда вам может быть нужно не только загрузить отношение, но также указать условие для его загрузки:

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

Ленивая жадная загрузка

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

Вы также можете дополнить запрос при помощи функции-замыкания:

Для чего все это нужно?

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

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

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

• < Назад
• Вперёд >

Новые статьи:

• Объединение таблиц – UNION

• Соединение таблиц – операция JOIN и ее виды

• Тест на знание основ SQL

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.

Microsoft Access

Офисный программный продукт от «Майкрософт» известен на рынке ПО уже довольно продолжительное время. Он поставляется вместе с текстовым редактором Worfd, табличным процессором Excel и прочими, входящими в линейку «офиса». Можно Access (читается как «аксес», дословный перевод — «доступ») приобрести и отдельно от его «коллег». Рекомендуется купить, разумеется, лицензионный софт, но ни для кого не секрет, сколько пиратских репаков можно найти на просторах Сети, в виде обычных файлов или раздач торрентов. «Майкрософт Аксес» доступен даже в портативной сборке. Она, не требующая инсталляции и особых навыков работы с ПК, лучше всего подходит для выбора, если ПО не будет использоваться продолжительно и часто.

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

Отношение «один-к-одному»

Отношение или связь «один-к-одному» связывает одну запись таблицы с одной или не связывает ни с одной записью другой таблицы. Иногда этот тип отношения применяется для разбиения таблицы с большим количеством полей на две или несколько меньших таблиц.

Таблица Products (изделия) может содержать подробную информацию, описывающую изделие и его цену, и дополнительные сведения об особенностях его производства.

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

В другой ситуации можно разбить таблицу на две, просто потому что она слишком велика. (Программа Access не разрешает таблице иметь более 255 полей.)

Рис. 5.15. Когда связываются два поля, в которых не допускаются дублирующиеся данные (и флажок Обеспечение целостности данных установлен), Access считает, что создается связь «один-к-одному».

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

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

создается так же, как отношение «один-ко-многим» — перетаскиванием с помощью мыши полей на вкладке Схема данных (рис. 5.15). Единственная

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

Примечание

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

Применяйте связи «один-к-одному» с осторожностью

Отношения «один-к-одному» крайне редко применяются в программе Access. Обычно гораздо удобнее использовать скрытие столбцов (см. разд. «Скрытие столбцов» главы 3) и запросы (см. главу 6), если вы хотите видеть только отдельные поля таблицы.

•    Две части таблицы необходимо поместить в отдельные БД (см. разд. «Что такое разделенная БД» главы 18) для того, чтобы разные люди могли копировать их на разные компьютеры и редактировать независимо.

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

•    У вас есть таблица с огромным объемом данных, таких как поля типа Вложение (см. разд. «Вложение» главы 2) с большими документами. В этом случае можно повысить производительность, если разделить таблицу. Вы даже можете решить, что лучше поместить половину таблицы в отдельную БД (см, разд. «Что такое разделенная БД» главы 18).

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

Если у вас нет таких ситуаций, вы больше выиграете от создания одной большой таблицы.

Отношение «многие-ко-многим»

Отношение или связь «многие-ко-многим»связывает одну или несколько записей одной таблицы с одной или несколькими записями в другой таблице. Рассмотрим БД, в которой в отдельных таблицах хранятся данные об авторах и книгах.

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

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

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

Связи «многие-ко-многим» довольно распространены, и программа Access предоставляет два способа их обработки.

Вы можете следить за любыми ответами на эту запись через RSS 2.0 ленту. Вы можете оставить ответ, или trackback с вашего собственного сайта.