Как сделать связь многие ко многим в access?

Pers.narod.ru. Обучение. Access VBA — редактируем связь многие-ко-многим и программно выполняем запрос с параметрами формы

Статья написана в учебных целях, хотя в ней есть и пара интересных неучебных нюансов.

Постановка проблемы: мы хотим динамически редактировать в Access связи между 2 таблицами, объединенными отношением «многие ко многим» (например, теги и слова, помеченные тегами, студенты и дисциплины, которые они посещают, или просто абстрактные «объекты» и «категории»). Думаю, понятно, что «многие ко многим» означает, что один объект может относиться к нескольким категориям и, наоборот, одной категории соответствует несколько объектов.

Сама по себе реализация такого редактора в Access несложна, вот весь процесс.

1. Создаем новую базу данных и сохраняем ее.

2. В окне базы данных на вкладке «Таблицы» создаем в режиме конструктора 3 таблицы:

• таблица «Категории» будет включать поле «Код категории», имеющее тип «Счетчик» и текстовое поле «Категория», служащее для описания; щелкнув правой кнопкой на поле «код», сделаем его ключевым:
• таблица «Объекты» будет устроена аналогично: она включает ключевое поле-счетчик с именем «Код объекта» и текстовое поле «Объект», предназначенное для данных;
• наконец, таблица «Связи» состоит из двух числовых полей, показанных ниже:

Обратите внимание, что оба поля я сделал ключевыми — это поможет избежать дублирования связей — например, объект 1 не должен иметь две одинаковых связи с категорией 1. Чтобы сделать оба поля ключевыми, нужно при нажатой клавише Ctrl выделить их, щелкая по области ключа, а затем вызвать правой кнопкой пункт меню.

4. Закрыв и сохранив схему, вносим по несколько записей в таблицы «Категории» и «Объекты».

5. Формы для работы с категориями и объектами по отдельности или в связке «главная и подчиненная таблица» сделать легко.

Для последнего, например, достаточно перейти на вкладку Формы, вызвать Мастер форм, добавить для формы все поля таблиц «Категории» и «Объекты», а на следующем шаге определить главную и починенную формы.

Но нас интересует сейчас не это. Главное, что мы хотим сделать — спроектировать форму «Связи» для редактирования наших данных.

6. Вызываем Конструктор форм, получаем новую пустую форму. Если окна «Раздел: область данных» (на самом деле это окно свойств) нет на экране, вызываем его, выбрав в окне формы правой кнопкой мыши пункт меню Свойства.

Если мастера для этого или других элементов не вызываются, причин может быть 2: не нажата кнопка «Мастера» на Панели элементов или не установлены соответствующие компоненты Access.

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

9. Аналогичным образом создаем список для отображения объектов из таблицы «Объекты», а называться он будет «СписокОбъекты».

Проблема состоит в том, что при повторном добавлении связи Access начинает ругаться стандартными сообщениями («Изменения не были внесены из-за повторяющихся значений в индексе…») и, более того, не дает сохранить последние внесенные изменения. Напишем небольшую процедуру на VBA для решения проблемы и лучшей обработки записей.

11. Вернувшись в режим конструктора, добавим на свободное место кнопку и с помощью мастера «Создание кнопок» назначим ей действие «добавить запись» из категории действий «Обработка записей». Все остальное можно настроить по вкусу.

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

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

Сгенерировать такой запрос на вкладке «Запросы» и потом просто вызвать его, к сожалению, не получится. Дело в том, что Access не видит взятых из формы параметров запроса, если запрос выполняется программно. Ошибка, как правило, возникает со следующим текстом «Too few parameters. Expected Число» («Слишком мало параметров. Ожидалось Число»).

Эта ошибка возникает, если команда или один из нижележащих запросов содержит обращения к формам или собственные параметры, — все эти обращения будут восприняты как параметры, которым не передано значение.

Почему так происходит? По этому поводу в справке Microsoft MSDN написано примерно следующее: NOTE: В DAO Вы должны явно присвоить значение параметру. При использовании DoCmd.OpenQuery Вы этого делать не должны, т.к. DAO использует операции низкого уровня, что даёт Вам большую свободу в использовании параметров (т.е.

Вы можете сами присвоить параметру значение переменной, а не использовать ссылку на форму), но Вы должны выполнить служебные действия, которые Access делает «за кулисами» при исполнении DoCmd. С другой стороны, DoCmd работает на более высоком уровне, чем DAO.

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

При выполении запроса из кода методом Execute или иным, запрос выполняется с помощью библиотеки DAO, которая, будучи внешней, ничего «не знает» о формах ACCESS, поэтому все недостающие значения считает неопределенными.

В Интернете можно встретить советы предварительно обработать все параметры процедурой вида

Dim q As DAO.QueryDef, p As DAO.Parameter
Set q = CurrentDb.QueryDefs(«ИмяЗапроса») 'как обычного запроса Select,
'так и INSERT/UPDATE; в запросах на удаление это не помогает
For Each p In q.Parameters
p.Value = Eval(p.Name)
Next
q.Execute
q.close: Set q=Nothing

Однако, для работы этого кода нужно, во-первых, иметь подключенную DAO (в окне редактора Visual Basic при остановленной программе вызвать Tools, References (или Сервис, Ссылки), найти и включить в списке библиотеку Microsoft DAO 3.6 Object Library), во-вторых, работа кода все-таки не гарантируется и в этом случае.

Мы хотим обойтись стандартным кодом

Dim rst As Recordset
CurrentDb.OpenRecordset («строка запроса»)

однако, едва избавившись от ошибок с недостающимим параметрами, получим сообщение о нессответствии типов (type mismatch, ошибка с кодом 13)!

Вся проблема состоит в том, что объект RecordSet есть и в библиотеке DAO, и в используемой Access по умолчанию библиотеке ADODb! Таким образом, наличие прямой ссылки на DAO, как в показанной выше процедуре, не гарантирует работоспособность кода — может возникать куча заморочек, связанных с тем, какая библиотека подключена в данный момент и у какой выше приоритет.

Dim rs

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

Private Sub КнопкаДобавить_Click()
If ([Forms]![Связи]![СписокКатегории] < 1) Or ([Forms]![Связи]![СписокОбъекты] < 1) Then MsgBox "Выберите категорию и объект", vbOKOnly, "Сообщение" Exit Sub Else Dim rs Dim l1, l2 As Integer l1 = (Forms![Связи]![СписокКатегории]) l2 = (Forms![Связи]![СписокОбъекты]) Dim s As String s = "SELECT Связи.[Код категории],Связи.[Код объекта] FROM Связи WHERE (([Код категории]=" & l1 & ") AND ([Код объекта]=" & l2 & "))" Set rs = CurrentDb.OpenRecordset(s) If rs.RecordCount = 0 Then DoCmd.GoToRecord , , acNewRec Else MsgBox "Уже есть такая связь", vbOKOnly, "Сообщение" Exit Sub End If End If End Sub

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

MsgBox Err.Description

на

MsgBox «Достигнуто начало базы данных», vbOKOnly, «Сообщение»

1. Это пример можно скачать и доделать, ведь область применения отношений «многие-ко-многим» так же широка, как сами эти отношения.
2. Скачать редактор связи «многие-ко-многим»: base_mn.zip, 32 Кб

гостевая; E-mail

Источник: http://pers.narod.ru/study/links_mn.html

Связь многие-ко-многим: пример в Access, в SQL. Как сделать связь многие-ко-многим?

Во всех СУБД (системах управления базами данных) имеется несколько типов отношений между таблицами. Среди них связь один-к-одному, один-к-многим, многие-к-одному (некоторые склонны отождествлять эти два типа в один) и связь многие-ко-многим. Пример последней, ее объяснение и применение в различных СУБД, таких как Access или SQL, будет рассмотрено в этой статье.

Определение

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

Когда может быть использована связь многие-ко-многим?

Как сделать связь многие-ко-многим?

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

Microsoft Access

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

Есть две таблицы.

Чтобы не придумывать ничего нового, возьмем уже указанный для того, чтобы разъяснить связь многие-ко-многим, пример про студенчество. Необходимо создать таблицу «Студенты» и таблицу «Преподаватели». Как в первой, так и во второй из них имеются первичные ключи. Для объединения экземпляров этих двух сущностей требуется также еще одна таблица, поля которой — ключи первой и второй таблиц.

Если рассмотреть иной пример: допустим, футболисты и команды ( с учетом того, что хотя бы один из футболистов играл за разные сборные, и каждая сборная имеет в своем составе одиннадцать игроков), суть построения связи не поменяется. Также будут нужны три таблицы. Из них «Футболисты» и «Команды» в качестве основных, и одна промежуточная.

Схема данных

На иллюстрации выше показано, как выглядит вкладка «Схема данных» (Relathionships). Количество добавляемых на панель таблиц неограниченно. Расположение полностью регулируется пользователем.

SQL

Проектирование баз данных на SQL — задача сложнее, чем на «Аксес».

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

Известное ПО для данной задачи: Oracle MySQL и DB2 (популярное, но не единственное в своем роде). Несмотря на то, что у каждого из них есть свои тонкости и нюансы, язык SQL их «объединяет». Научившись работать хотя бы с одним из них, разобраться с другим будет гораздо проще.

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

Принцип создания связи многие-ко-многим

Реализация связи

Для реализации связи многие-ко-многим в скриптах SQL используются внешние ключи (FOREIGN KEY) аналогичные исходным ключам в основных таблицах. Они записываются вместе со всеми полями при их создании и/или редактировании.

Роль связи многие-ко-многим

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

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

Обычно, если база содержит данные о целой организации, компании или фирме, в ней содержится множество сущностей с различными экземплярами.

А это значит, что при составлении схемы данных (в «Аксесе») или написании скриптов (в «Оракл» или «ДиБиТу») будет присутствовать как минимум одна связь многие-ко-многим. Пример SQl, часто использующийся при обучении курса «Организации баз данных» — БД Кинга.

База данных Кинга

Эта учебная база данных представляет собой сведения о корпорации Кинга. Среди таблиц:

• сотрудники фирмы — содержит в себе код сотрудника, его фамилию, имя и средний инициал (ориентированность на зарубежные имена), также код начальника и занимаемой сотрудником должности, дату его поступления в фирму, получаемый им оклад и предусмотренные комиссионные, код отдела;
• отделы корпорации — среди полей таблицы есть код и название отдела, а также код его размещения;
• места размещения отделов, которая предполагает внесение информации по коду места размещения и названия города;
• должности в фирме — небольшая таблица с двумя полями кода должности и ее официального названия;
• фирмы-покупатели — поля: код и название покупателя, адрес, город и штат, почтовый код и код региона, телефон, код обслуживающего покупателя менеджера, кредит для покупателя и комментарии (примечания и заметки);
• договоры о продаже, содержащая в себе код и дату договора, код покупателя, дату поставки и общую сумму договора;
• акты продаж — код акта и код договора, в который входит акт, код продукта, его цена, количество приобретенного и общая стоимость покупки;
• товары — код и название продукта;
• цены — код продукта, объявленная на него цена, минимально возможная цена,дата установления и дата отмены цены.

Масштабные же таблицы, такие как «сотрудники фирмы», «фирмы-покупатели», «договоры о продаже» и «акты продаж» связаны сразу с несколькими сущностями, причем с некоторыми — при помощи «посредников» отношением многие-ко-многим.

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

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

Важные нюансы

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

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

Источник: https://autogear.ru/article/228/964/svyaz-mnogie-ko-mnogim-primer-v-access-v-sql-kak-sdelat-svyaz-mnogie-ko-mnogim/

Руководство по проектированию реляционных баз данных (7-9 часть из 15) [перевод]

Продолжение. Предыдущие части: 1-3, 4-6

7. Связь один-ко-многим.

Я уже показал вам как данные из разных таблиц могут быть связаны при помощи связи по внешнему ключу. Вы видели как заказы связываются с клиентами путем помещения 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 в отдельную таблицу для ускорения поиска по родительской таблице).

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

Примеры связи один-к-одному

• Люди и их паспорта. Каждый человек в стране имеет только один действующий паспорт и каждый паспорт принадлежит только одному человеку.

Проект реляционной базы данных – это коллекция таблиц, которые перелинковываются (связываются) первичными и внешними ключами. Реляционная модель данных включает в себя ряд правил, которые помогают вам создать верные связи между таблицами. Эти правила называются “нормальными формами”. В следующих частях я покажу как нормализовать вашу базу данных.

Какой же вид связи вам нужен?

Примеры связей таблиц на практике. Когда какие-то данные являются уникальными для конкретного объекта, например, человек и номера его паспортов, то имеем дело со связью один-ко-многим. Т.е. в одной таблице мы имеем список неких людей, а в другой таблице у нас есть перечисление номеров паспортов этого человека (напр.

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

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

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

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

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

хранится она только в одном месте (таблице), в остальных таблицах имеются лишь ссылки на нее.

А когда у вас есть набор уникальных данных, которые имеют отношение только друг к другу, то храните все в одной таблице. Ваш выбор – связь один-к-одному. Например, у вас есть небольшая коллекция автомобилей и вы хотите хранить информацию о них (цвет, марка, год выпуска и пр.).

Источник: https://habr.com/post/193380/

Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих

Здравствуйте, уважаемые посетители сайта ZametkiNaPolyah.ru. Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3.

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

Данная тема является базовой и ее понимание необходимо для работы с базами данных и для их проектирования.

Виды связей между таблицами в базе данных. Связи в реляционных базах данных. Отношения, кортежи, атрибуты.

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

Термины кортеж, атрибут и  отношение в реляционных базах данных

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

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

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

Таблица с данными из базы данных World

У нас есть простая таблица City из базы данных World, в которой есть строки и столбцы. Но термины: таблица, строка, столбец – это термины стандарта SQL.

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

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

Если рассматривать таблицу, как объект (например книга), то столбец – это характеристики объекта, а строки содержат информацию об объекте.

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

Давайте теперь рассмотрим то, как могут быть связаны таблицы в реляционных базах данных. Сразу скажу, что всего существует три вида связей между таблицами баз данных:
• связь один к одному;
• связь один ко многим;
• связь многие ко многим.

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

Реализация связи один ко многим в теории баз данных

Связь один ко многим в реляционных базах данных реализуется тогда, когда объекту А может принадлежать или же соответствовать несколько объектов Б, но объекту Б может соответствовать только один объект А. Не совсем понятно, поэтому смотрим пример ниже.

Реализация связи один ко многим в реляционных базах данных

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

Связь многие ко многим

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

Пример связи многие ко многим

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

Связь один к одному

Связь один к одному – самая редко встречаемая связь между таблицами. В 97 случаях из 100, если вы видите такую связь, вам необходимо объединить две таблицы в одну.

Пример связи один к одному

Таблицы будут связаны один к одному тогда, когда одному объекту таблицы А соответствует один объект таблицы Б, и одному объекту таблицы Б соответствует один объект таблицы А. Как я уже говорил: если вы видите, что связь один к одному – смело объединяйте таблицы в одну, за исключением тех случаев, когда происходит модернизация базы данных.

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

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

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

Возможно, эти записи вам покажутся интересными

Источник: https://zametkinapolyah.ru/zametki-o-mysql/chast-3-2-vidy-svyazej-mezhdu-tablicami-v-baze-dannyx-svyazi-v-relyacionnyx-bazax-dannyx-otnosheniya-kortezhi-atributy.html

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

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

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

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

Рис. 1.

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

Рис. 2.

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

Рис. 3.

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

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

Рис. 4.

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

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

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

Источник: https://www.lessons-tva.info/edu/inf-access/access_3.html

jtest.ru

21.03.2011

Представляю Вашему вниманию вольный перевод статьи SQL for Beginners Part 3

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

Предыдущие статьи

• SQL для начинающих. Часть 1
• SQL для начинающих. Часть 2

Вступление

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

Например, клиент имеет заказы, а у заказа есть позиции (товары). Эти взаимосвязи должны быть отражены в базе данных.

А также, когда мы получаем данные с помощью SQL, мы должны использовать определенные типы запросов JOIN, чтобы получить нужный результат.

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

• Отношения один к одному
• Один ко многим и многие к одному
• Многие ко многим
• Связь с самим собой

Когда данные выбираются из нескольких связанных таблиц, мы будем использовать запрос JOIN. Есть несколько типов присоединения, мы познакомимся с этими:

• Cross Joins (Перекрестное соединение)
• Natural Joins (Естественное соединений)
• Inner Joins (Внутреннее соединений)
• Left (Outer) Joins (Левое (внешнее) соединение)
• Right (Outer) Joins (Правое (внешнее) соединение)

Также мы изучим предложения ON и USING.

Связь один к одному

Допустим есть таблица покупателей (customers):

Мы можем расположить информацию о адресе покупателя в другой таблице:

Теперь у нас есть связь между таблицами покупателей (Customers) и адресами (Addresses). Если каждый адрес может принадлежать только одному покупателю, то такая связь называется «Один к одному». Имейте ввиду, что такой тип отношений не очень распространен. Наша первоначальная таблица, в которой информация о покупателе и его адресе хранилась вместе, в большинстве случаев работает нормально.

Обратите внимание, что теперь поле с названием «address_id», в таблице покупателей, ссылается на соответствующую запись в таблице адресов. Оно называется внешним ключом (Foreign Key) и используется во всех видах связей в базе. Мы рассмотрим этот вопрос позже в этой статье.

Вот так можно отобразить отношения между покупателями и адресами:

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

Связь один ко многим и многие к одному

Этот тип отношений наиболее часто встречающийся. Рассмотрим такой сайт интернет магазина:

• У покупателей может быть несколько заказов.
• Заказ может содержать несколько товаров.
• Товары могут иметь описание на нескольких языках.

В этих случаях нам потребуется создать связь «Один ко многим». Пример:

Каждый покупатель может иметь 0 или более заказов. Но каждый заказ может принадлежать только одному покупателю.

Связь многие ко многим

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

Для такой связи нам потребуется создать дополнительную таблицу:

Назначение таблицы «Items_Orders» только одно — создать связь «Многие ко многим» между товарами и заказами.

Так можно представить этот тип отношений:

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

Связь с собой

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

Покупатели 102 и 103 ссылаются на покупателя 101.

Этот тип похож на связь «Один ко многим», поскольку один покупатель может ссылаться на несколько покупателей. Это можно представить как древовидную структуру:

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

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

Внешние ключи

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

• В отношениях, обсуждаемых выше, у нас всегда было поле вида «****_id», которое ссылалось столбец в другой таблице. В нашем примере столбец customer_id, в таблице Orders, является внешним ключом:
• В таких базах как MySQL есть два способа создания внешних ключей:

Задать внешний ключ явно

Создадим простую таблицу с покупателями:

CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
customer_name VARCHAR(100)
);

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

CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
amount DOUBLE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Оба столбца (customers.customer_id и orders.customer_id) должны быть одного типа. Если у первого тип INT, то второй не должен быть типа BIGINT, например.

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

Без явного объявления

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

CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
amount DOUBLE,
INDEX (customer_id)
);

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

SELECT * FROM orders
JOIN customers USING(customer_id)

Мы подошли к изучению запросов JOIN, которые обсудим далее в статье.

Отображение связей

В данный момент, моей любимой программой для проектирования баз данных и отображения связей является MySQL Workbench.

После того как Вы спроектировали базу данных, ее можно экспортировать в SQL и выполнить на сервере. Это очень удобно при создании больших и сложных баз данных.

Запросы JOIN

Чтобы получить связанные данные из базы данных следует использовать запросы JOIN.

Прежде чем мы начнем, давайте создадим для работы тестовые таблицы и данные.

CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
customer_name VARCHAR(100)
);

CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
amount DOUBLE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO `customers` (`customer_id`, `customer_name`) VALUES
(1, 'Adam'),
(2, 'Andy'),
(3, 'Joe'),
(4, 'Sandy');

INSERT INTO `orders` (`order_id`, `customer_id`, `amount`) VALUES
(1, 1, 19.99),
(2, 1, 35.15),
(3, 3, 17.56),
(4, 4, 12.34);

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

Cross Join (Перекрестное объединение)

Это вид JOIN запроса по-умолчанию, если не определено условие.

Результатом будет, так называемое, «Декартово объединение» таблиц. Это означает, что каждая строка из первой таблицы сопоставляется с каждой строкой второй таблицы. Т.к. в каждой таблице по 4 строки, мы получили в результате 16 строк.

Ключевое слово JOIN можно заменить на запятую, в этом случае.

Конечно такой результат почти бесполезен. Давайте взглянем на другие виды объединений.

Natural Join (Естественное объединение)

При таком виде запроса JOIN таблицы должны иметь совпадающие, по имени, столбцы. В нашем случае в обеих таблицах должен присутствовать столбец customer_id. MySQL объединит записи только в случае совпадения значений в этих столбцах.

Как Вы можете видеть, в этот раз столбец customer_id отображаются только один раз, потому что движок базы рассматривает этот столбец как общий. Мы видим два заказа Adam'а, и другие два заказа Joe и Sandy. Наконец мы получили некоторую полезную информацию.

Inner Join (Внутреннее объединение)

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

Результат почти такой же. Столбец customer_id повторяется два раза, по разу для каждой таблицы. Объясняется это тем, что мы попросили базу сравнить значение по двум столбцам. При этом не знаю, что возвращают одну и туже информацию.

1. Добавим побольше условий к запросу.
2. На этот раз возвращается только те заказы, сумма которых превышает $15.

Предложение ON

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

Теперь мы можем различать условия, относящиеся к JOIN и условия в части WHERE. Но еще есть небольшая разница в функционировании. Мы увидим это, когда перейдем к примерам с LEFT JOIN.

Предложение USING

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

На самом деле это очень похоже на NATURAL JOIN, т.е. объединяющий столбец (customer_id) не повторяется дважды.

Left (Outer) Join (Левое внешнее соединение)

LEFT JOIN это вид внешнего соединения. В следующем запросе, если не найдены совпадения во второй таблице, записи из первой таблице все равно отобразятся.

Хотя у Andy и нет заказов, эта запись все равно отображается. Значение из второй таблицы равно NULL.

Это полезно, когда нужно найти записи, у которых нет связей. Например, мы можем найти всех покупателей, которые ничего не заказывали.

Отметим, что ключевое слово OUTER не обязательно. Вы можете использовать просто LEFT JOIN вместо LEFT OUTER JOIN.

Условия

Теперь давайте посмотрим на запросы с условиями.

Так, что случилось с Andy и Sandy? LEFT JOIN подразумевает, что мы должны получить покупателей, у которых нет заказов. Проблема в том, что условие WHERE скрывает эти результаты. Чтобы получить их, мы можем попытаться включить условие с NULL.

Появился Andy, но нет Sandy. Выглядит неправильно. Для того чтобы получить то, что мы хотим, нужно использовать предложение ON.

Теперь мы получили всех, и все заказы более $15. Как я говорил ранее, предложение ON иногда работает не так как WHERE. В таких внешних объединениях как это, столбцы включаются всегда, даже если нет совпадений в условии предложения ON.

Right (Outer) Join (Правое внешнее соединение)

Объединение RIGHT OUTER JOIN работает также, только порядок таблиц меняется на обратный.

На этот раз мы не получили результатов с NULL, потому что каждый заказ имеет сопоставление с записью покупателя. Мы можем поменять порядок таблиц и получим тот же результат, что и с LEFT OUTER JOIN.

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

Заключение

Спасибо за чтение статьи. Надеюсь Вам понравилось!

Источник: http://jtest.ru/bazyi-dannyix/sql-dlya-nachinayushhix-chast-3.html