«java-проект от а до я»: разбираем базы данных и язык sql. часть 5

Как определить связи между таблицами

При создании связи между таблицами связанные поля не должны иметь одни и те же имена. Однако связанные поля должны иметь один и тот же тип данных, если только поле первичного ключа не является полем AutoNumber. Вы можете сопоставить поле AutoNumber с полем Number, только если свойство FieldSize обоих совпадающих полей совпадает. Например, можно сопоставить поле AutoNumber и поле Number, если свойство theFieldSizeproperty обоих полей имеет значение Long Integer. Даже если оба совпадающих поля являются числовыми полями, они должны иметь параметр sameFieldSizeproperty.

Как определить связи «один ко многим» или «один к одному»

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

1. Закройте все таблицы. Нельзя создавать или изменять связи между открытыми таблицами.

2. В Access 2002 и Access 2003 выполните следующие действия.

   1. Нажмите F11, чтобы переключиться в окно базы данных.
   2. В меню Инструменты выберите Связи.

   В Access 2007, Access 2010 или Access 2013 нажмите Связи в группе Показать/Скрыть на вкладке Инструменты базы данных.

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

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

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

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

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

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

7. Нажмите кнопку Создать, чтобы создать связь.

8. Повторите шаги с 4 по 7 для каждой пары таблиц, которые вы хотите связать.

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

   Примечание

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

Как определить связь «многие ко многим»

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

1. Создайте две таблицы, которые будут иметь связь «многие ко многим».

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

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

   Примечание

   Чтобы создать первичный ключ, выполните следующие действия:

   1. Откройте таблицу в Конструкторе.

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

   3. В Access 2002 или в Access 2003 нажмите на Первичный ключ на панели инструментов.

      В Access 2007 нажмите на Первичный ключ в группе Инструменты на вкладке Дизайн.

      Примечание

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

4. Определите связь один-ко-многим между каждой основной и связующей таблицами.

Связь 1-N — один ко многим

Помним:

у одного клиента — много заказов, нужен список заказов клиента

и тут нюанс — в заказе клиент уже указан:

• зачем создавать и вручную заполнять список заказов..? пахнет костылем
• решение — связь 1-N

Настройка в ELMA:

1. Перейдем в клиента
2. Добавим свойство заказы
3. Укажем тип свойства «Множественная (1-N)» и укажем «ключевую колонку» Клиент из объекта Заказ

дальше интереснее

Посмотрим в базу данных

На диаграмме ничего не поменялось и вот почему:

• база данных знала заранее и нарисовала связь 1-N
• в направлении Заказ -> Клиент соответствие 1-1 , в обратном N-1 ( это пытались сказать разработчики ELMA в начале)

Таблицы выглядят по прежнему:

На этом чудеса не заканчиваются:

1. Добавьте еще один заказ клиенту
2. Теперь откройте карточку клиента

Что тут произошло:

ELMA не создавала новое поле в таблице а только создала список в объекте клиент который определяется по свойству клиент в поле объекта заказ

Нюансы работы со связями 1-1 и 1-N (также работает из кода)

1. Если в заказе указать клиента — он отразится в списке заказов клиента (это известно)
2. Если в список заказов клиента добавить заказ — в добавленном заказе изменится клиент (поэтому будьте уверены что нужна связь 1-N а не N-N, читайте о ней дальше)

Извлечение данных для связи «многие ко многим» (SELECT)

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

Рассмотрим задачу извлечения участников, связанных с данной номинацией — или короче «номинации, и всех, кто подал в неё заявки» (алгоритм извлечения данных в обратную сторону — т.е. «участик и все его номинации» абсолютно аналогичен).
На практике приходится сталкиваться с двумя базовыми ситуациями:

1. Извлечение одной сущности номинации и связанных с ней участников
2. Извлечение списка сущностей номинаций и связанных с каждой из номинаций участников (т.е. фактически список участников для каждого элемента из списка номинаций).

Извлечение связанных (многие-ко-многим) данных для одной сущности

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

1. Сначала просто получим кортеж этой номинации:

   mysql> SELECT * FROM Nominations WHERE nominationID=4;
   +--------------+-----------------------------+
   | nominationID | title                       |
   +--------------+-----------------------------+
   |            4 | Лучшее пособие              |
   +--------------+-----------------------------+
   
   

2. После, опять же зная id номинации (используем в WHERE), достаточно просто сделать LEFT JOIN между таблицей связи и таблицей участников:

   SELECT * FROM  Tickets_Nominations LEFT JOIN Tickets 
   	ON ticket_id = ticketID 
   	WHERE Tickets_Nominations.nomination_id = 4;

   Получим:

   +-----------+---------------+----------+-------------------------- +----------------------------------------------+
   | ticket_id | nomination_id | ticketID | name                      | info                                         |
   +-----------+---------------+----------+---------------------------+----------------------------------------------+
   |         3 |             4 |        3 | Программирование для всех | Некоммерческая образовательная организация  
   |         4 |             4 |        4 | Юный программист          | Кружок для детей в д. Простоквашино        
   |         5 |             4 |        5 | IT FOR FREE               | Русскоязычное IT-сообщество с уклоном в web  
   |         6 |             4 |        6 | Саша Петров               | Студент 2 курса, автор пособия по SQL   
   

   — как видим, тут мы получили вообще все колонки (т.к. в запросе указали звездочку *) двух соединённых таблиц (связи и заявок).
   Также видим что на номинации с id=4 номинировалось 4-ре участника, кроме их имен видны также и описания.
   Все эти данные можно использовать в приложении, после выполнения запроса к БД — например записать, то что нужно в поле, хранящее массив объекта конкретной номинации.

Если вам требуется от массива связанных сущностей только одно поле (напр. имена участников), то решить задачу можно вообще одним sql запросом, используя группировку (GROUP BY) и применимую к группируемым значения колонки функцию конкатенации GROUP_CONCAT():

SELECT 
    Nominations.*, 
	 GROUP_CONCAT(Tickets.name SEPARATOR ', ') as participants_names
 FROM  
   Nominations LEFT JOIN Tickets_Nominations 
	   ON Nominations.nominationID = Tickets_Nominations.nomination_id 
	LEFT JOIN Tickets  
	   ON Tickets.ticketID = Tickets_Nominations.ticket_id
 
WHERE Tickets_Nominations.nomination_id = 4
 	GROUP BY Nominations.nominationID;

Получим единственный кортеж:

+--------------+-----------------------------+-----------------------------------------------------------------------------------------------------------------------+
| nominationID | title                       | participants_names                                                                                                    |
+--------------+-----------------------------+-----------------------------------------------------------------------------------------------------------------------+
|            4 | Лучшее пособие              | Программирование для всех, Юный программист, IT FOR FREE, Саша Петров                                                 |
+--------------+-----------------------------+-----------------------------------------------------------------------------------------------------------------------+

— здесь мы:

• провели сразу тройной JOIN, как бы поставив таблицу связи между таблицами номинаций и заявок.
• нас интересовали имена участников для 4 номинации — поэтому использовали WHERE Tickets_Nominations.nomination_id = 4
• Группировка (чтобы в итоге получить только одну строку-кортеж) проходила по id номинации (Nominations.nominationID)
• Сконкатенированному полю мы назначили псевдоним (participants_names)

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

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

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

Таблица 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 с вашего собственного сайта.

Установка связеймежду таблицами БД Access 2007

Логические связи устанавливаются между одноименными полями таблиц базы данных Access 2007. Связь данных в одной таблице с данными в других таблицах осуществляется через уникальные идентификаторы (ключи) или ключевые поля. В нашем случае мы должны установить логические связи между таблицами: Группы студентов, Студенты, Дисциплины и Успеваемость.

Для установления связей используем ключевые поля: КодГруппы, КодСтудентов и КодДисциплины. Например, между первичным ключом (КодГруппы) tables Группы студентов и вторичным ключом (КодГруппы) tables Студенты устанавливаем связь один — ко — многим.

Прежде чем приступить к созданию логических связей надо в Окне редактирования закрыть все tables и перейти на вкладку Работа с базами данных. Затем щелкнуть на пиктограмме Схема данных, в окне редактирования появится активное диалоговое окно «Добавление таблицы» на фоне неактивного окна Схема данных (рис. 1).

Рис. 1.

В окне Добавление таблиц необходимо выделить имена таблиц и нажать кнопку Добавить, при этом в окне «Схема данных» появятся все tables (рис. 2). После этого необходимо закрыть окно диалога.

Рис. 2.

Далее необходимо установить связи между табл. в окне Схема данных. Для этого в окне Схема данных необходимо отбуксировать (переместить) поле КодГруппы из таблицы Группы студентов на соответствующее поле tables Студенты, в результате этой операции появится окно «Изменение связей» (рис. 3) .

Рис. 3.

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

В окне Схема данных появится связь один-ко-многим между таблицами Группы студентов и Студенты. Аналогичным образом надо связать поля КодСтудента в таблицах Студенты и Успеваемость, а затем поля КодДисциплины в таблицах Успеваемость и Дисциплины. В итоге получим Схему данных, представленную на рисунке 4.

Рис. 4.

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

Затем установить связи между табл. «Студенты» и «Успеваемость», «Дисциплины» и «Успеваемость», так как поля КодСтуденты и КодДисциплины табл. Успеваемость используется в качестве столбца подстановки для заполнения соответствующих полей таблицы Успеваемость.

Далее >>> Раздел: 2.4.4. Заполнение таблиц базы данных Access 2007

Как в access

В разделе Программное обеспечение на вопрос Как в Access установить связи между таблицами, с поддержкой целостности данных заданный автором Алексей Ковалёв лучший ответ это оздание связей «один ко многим» или «один к одному»Чтобы создать связь вида «один ко многим» или «один к одному», воспользуйтесь приведенной ниже последовательностью действий:1. Закройте все открытые таблицы. Создавать или изменять связи между открытыми таблицами нельзя.2. В Access версий 2002 или 2003 выполните указанные ниже действия.a. Нажмите клавишу F11, чтобы перейти в окно базы данных.b. В меню Сервис выберите команду Связи.В Access 2007 нажмите кнопку Связи в группе Показать или скрыть вкладки Инструменты для баз данных.3. Если в базе данных отсутствуют связи, то автоматически появится диалоговое окно Добавление таблицы. Если окно Добавление таблицы не появилось, но при этом нужно добавить таблицы в список связываемых, выберите команду Добавить таблицу в меню Связи.4. Дважды щелкните названия таблиц, которые необходимо связать, после чего закройте диалоговое окно Добавление таблицы. Чтобы связать таблицу с самой собой, добавьте ее два раза.5. Перетащите связываемое поле из одной таблицы на связываемое поле в другой. Чтобы перетащить несколько полей, нажмите клавишу CTRL, щелкните каждое поле, а затем перетащите их.В большинстве случаев понадобится перетащить поле первичного ключа (выделенное полужирным текстом) из одной таблицы на аналогичное поле (часто с тем же самым названием) , называемое внешним ключом, другой таблицы.6. Появится окно Изменение связей. Убедитесь, что в каждом из столбцов отображаются названия нужных полей. Если нужно, их можно изменить.При необходимости задайте параметры связи. Если требуются сведения о конкретном элементе окна Изменение связей, нажмите кнопку со знаком вопроса, а затем щелкните соответствующий элемент. Эти параметры будут подробно описаны ниже.7. Чтобы установить связь, нажмите кнопку Создать.8. Повторите действия с 5 по 8 для каждой пары связываемых таблиц.При закрытии диалогового окна Изменение связей Microsoft Access спросит, нужно ли сохранить макет. Вне зависимости от ответа на этот вопрос создаваемые связи сохраняются в базе данных.Примечание. Создавать связи можно не только в таблицах, но и в запросах. При этом, однако, не обеспечивается целостность данных.Создание связей «многие ко многим»Чтобы создать связь вида «многие ко многим», выполните указанные ниже действия.1. Создайте две таблицы, которые необходимо связать отношением «многие ко многим».2. Создайте третью таблицу, называемую соединительной, и добавьте в нее поля с теми же определениями, что и поля первичных ключей в каждой из двух других таблиц. Поля первичных ключей соединительной таблицы служат внешними ключами. В соединительную таблицу, как и в любую другую, можно добавить и другие поля.3. Задайте первичный ключ этой таблицы таким образом, чтобы он включал в себя поля первичных ключей обеих основных таблиц. Например, первичный ключ соединительной таблицы «АвторыКниг» будет состоять из полей «ИД_заказа» и «ИД_продукта».Примечание. Чтобы создать первичный ключ, выполните указанные ниже действия.a. Откройте таблицу в режиме конструктора.b. Выберите одно или несколько полей, которые необходимо определить в качестве первичного ключа. Чтобы выбрать одно поле, щелкните знак выбора строки для нужного поля.Чтобы выбрать несколько полей, удерживайте нажатой клавишу CTRL и щелкните знак выбора строки для каждого из полей.c. В Access версий 2002 или 2003 нажмите кнопку Первичный ключ на панели инструментов.В Access 2007 нажмите кнопку Первичный ключ в группе Сервис вкладки Структура.Примечание. Чтобы порядок следования полей в первичном ключе, состоящем из нескольких полей, отличался от такового в таблице, нажмите кнопку Индексы на панели инструментов, в результате чего появится диалоговое окно Индексы, в котором можно изменить порядок следования полей индекса под названием КлючевоеПоле.

Общие нюансы для связей

1. В контексте процесса доступно только два варианта связей 1-1 и 1-N (особенность контекста процесса, потому что доступно редактирование контекста без перезагрузки ELMA, но это уже другая история)
2. Будьте аккуратны с копированием свойств — списков:
   • 1-N — при копировании проверяйте ключевую колонку
     • если запустить «как есть» будет общая связь для разных свойств — что в последствии нарушит целостность базы данных (будут битые связи) и при перестроении базы данных — система не запустится — бомба замедленного действия (починить можно — как расскажу в следующий раз)
     • если открыть свойство на редактирование — ключевая колонка будет пустой и потребуется ее заполнить — тогда все будет хорошо
   • N-N — при копировании проверяйте название развязочной таблицы
     • если запустить «как есть» будет общая развязочная таблица для разных свойств — будут путаться списки и в последствии нарушится целостность базы данных и при перестроении базы данных ELMA не запустится — снова бомба замедленного действия
     • если открыть свойство на редактирование — система не даст сохранить развязочную таблицу с наименованием, которое уже есть в системе — тогда все будет хорошо
   • N-N (инверсия) — при копировании проверяйте ключевую колонку, как с 1-N — последствия те же
3. Не приравнивайте в коде один список к другому
   • нельзя писать Клиент1.Заказы = Клиент2.Заказы, при сохранении будет ошибка — найдены общие связи коллекций
   • нужно писать Клиент1.Заказы.AddAll( Клиент2.Заказы)
4. В объектах ELMA BPM разработчики решили даже в незаполненных объектах создавать пустые коллекции
5. Для создания списков — старайтесь не использовать блоки, тем более если элемент списка — независимый объект.(иногда для создания списка новички создают блок с одним свойством (получается развязочная таблица) — знайте это костыль

Бонус

Списки можно вывести таблицей — выведите на форму «Список связанных сущностей»

• со связями 1-N и N-N(инверсия) можно указать ключевую колонку и получится удобная таблица:
• со связью N-N придется заморочиться и придумать EQL вот из статья базы знаний