Что необходимо для установления соответствия между таблицами базы данных

Тема: «Установление связей между таблицами в СУБД Microsoft Office Access 2003»

Практическая работа №37

Тема: «Установление связей между таблицами в СУБД Microsoft Office Access 2003»

Цель: научиться использовать средства СУБД Microsoft Access для создания связей между таблицами, входящими в БД.

Основные теоретические сведения:

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

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

1. Связанное поле главной таблицы является ключевым полем.

2. Связанные поля имеют один тип данных.

3. Обе таблицы принадлежат одной базе данных Microsoft Access.

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

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

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

Для установления связей между таблицами:

1. Задайте команду Сервис – Схема данных.

2. В диалоговом окне Добавление таблицы выделите названия таблиц, которые должны быть связаны (названия каждой из таблиц со списками полей появятся в соответствующем окне). Щёлкните по кнопке Добавить, а затем – Закрыть.

3. Установите курсор в любую из таблиц на поле, по которому будет установлена связь, и с помощью мыши отбуксируйте это поле на связующее поле другой таблицы.

4. Активизируйте контрольную метку Обеспечение целостности данных. Данное действие позволит предотвратить случайное удаление или изменение связанных данных.

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

В случае, если для какой-то из таблиц не было определено ключевое поле, в поле Тип отношения отображается текст: «Не определено». Для удаления связи в окне Схема данных выделите ненужную связь и нажмите клавишу Delete.

1. Прочитайте основные теоретические сведения.

2. Запустите Microsoft Access и откройте БД «Товародвижение» из созданной ранее подпапки с именем вашей фамилии в папке Мои документы.

3. Откройте окно Схема данных, задав команду Сервис – Схема данных или нажав на кнопку — Схема данных на панели инструментов База данных.

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

1). В диалоговом окне Добавление таблицы выделите с помощью мыши названия трёх таблиц.

2). Щёлкните по кнопке Добавить, а затем – Закрыть.

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

4). При необходимости расширьте размеры таблиц.

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

6. Аналогично создайте связь между таблицами «Справочник поставщиков» и «Поставки» по строке «Наименование поставщика».

7. Сохраните БД и закройте диалоговое окно Схема данных.

8. Введите исходные данные в таблицу «Поставки». Для этого:

1). Откройте таблицу, щёлкнув 2ЛКМ по её названию – Поставки.

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

Источник

Связи между таблицами базы данных

1. Введение

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

1.1. Для кого эта статья?

Эта статья будет полезна тем, кто хочет разобраться со связями между таблицами базы данных. В ней я постарался рассказать на понятном языке, что это такое. Для лучшего понимания темы, я чередую теоретический материал с практическими примерами, представленными в виде диаграммы и запроса, создающего нужные нам таблицы. Я использую СУБД Microsoft SQL Server и запросы пишу на T-SQL. Написанный мною код должен работать и на других СУБД, поскольку запросы являются универсальными и не используют специфических конструкций языка T-SQL.

1.2. Как вы можете применить эти знания?

2. Благодарности

Учтены были советы и критика авторов jobgemws, unfilled, firnind, Hamaruba.
Спасибо!

3.1. Как организовываются связи?

Связи создаются с помощью внешних ключей (foreign key).
Внешний ключ — это атрибут или набор атрибутов, которые ссылаются на primary key или unique другой таблицы. Другими словами, это что-то вроде указателя на строку другой таблицы.

3.2. Виды связей

4. Многие ко многим

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

4.1. Как построить такие таблицы?

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

На эту таблицу можно посмотреть с двух сторон:

4.2. Реализация

С помощью ограничения foreign key мы можем ссылаться на primary key или unique другой таблицы. В этом примере мы

4.3. Вывод

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

5. Один ко многим

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

Предположим, нам нужно реализовать некую БД, которая ведет учет данных о пользователях. У пользователя есть: имя, фамилия, возраст, номера телефонов. При этом у каждого пользователя может быть от одного и больше номеров телефонов (многие номера телефонов).

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

Другими словами, телефон принадлежит только одному пользователю. А пользователю могут принадлежать 1 и более телефонов (многие).

Как мы видим, это отношение один ко многим.

5.1. Как построить такие таблицы?

Данная таблица представляет три номера телефона. При этом номера телефона с id 1 и 2 принадлежат пользователю с id 5. А вот номер с id 3 принадлежит пользователю с id 17.
Заметка. Если бы у таблицы «Phones» было бы больше атрибутов, то мы смело бы их добавляли в эту таблицу.

5.2. Почему мы не делаем тут таблицу-посредника?

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

5.3. Реализация

6. Один к одному

Представим, что на работе вам дали задание написать БД для учета всех работников для HR. Начальник уверял, что компании нужно знать только об имени, возрасте и телефоне работника. Вы разработали такую БД и поместили в нее всю 1000 работников компании. И тут начальник говорит, что им зачем-то нужно знать о том, является ли работник инвалидом или нет. Наиболее простое, что приходит в голову — это добавить новый столбец типа bool в вашу таблицу. Но это слишком долго вписывать 1000 значений и ведь true вы будете вписывать намного реже, чем false (2% будут true, например).

Более простым решением будет создать новую таблицу, назовем ее «DisabledEmployee». Она будет выглядеть так:

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

Выполнив это мы получили связь один к одному.

Заметка. Обратите внимание на то, что мы могли также наложить на атрибут EmloyeeId ограничение primary key. Оно отличается от ограничения unique лишь тем, что не может принимать значения null.

6.1. Вывод

Можно сказать, что отношение один к одному — это разделение одной и той же таблицы на две.

6.2. Реализация

7. Обязательные и необязательные связи

Связи можно поделить на обязательные и необязательные.

7.1. Один ко многим

У одной биологической матери может быть много детей. У ребенка есть только одна биологическая мать.

А) У женщины необязательно есть свои дети. Соответственно, связь необязательна.
Б) У ребенка обязательно есть только одна биологическая мать – в таком случае, связь обязательна.

7.2. Один к одному

У одного человека может быть только один загранпаспорт. У одного загранпаспорта есть только один владелец.

А) Наличие загранпаспорта необязательно – его может и не быть у гражданина. Это необязательная связь.
Б) У загранпаспорта обязательно есть только один владелец. В этом случае, это уже обязательная связь.

7.3. Многие ко многим

Человек может инвестировать в акции разных компаний (многих). Инвесторами какой-то компании являются определенные люди (многие).

А) Человек может вообще не инвестировать свои деньги в акции.
Б) Акции компании мог никто не купить.

8. Как читать диаграммы?

Выше я приводил диаграммы созданных нами таблиц. Но для того, чтобы их понимать, нужно знать, как их «читать». Разберемся в этом на примере диаграммы из пункта 5.3.

Мы видим отношение один ко многим. Одной персоне принадлежит много телефонов.

9. Итоги

10. Задачи

Для лучшего усвоения материала предлагаю вам решить следующие задачи:

Источник

Начало работы со связями между таблицами

Проверьте, как это работает!

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

Чтобы увидеть все связи в базе данных, откройте шаблон Access, а затем на вкладке Работа с базами данных нажмите кнопку Схема данных.

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

Описание области «Схема данных»

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

По линиям и символам можно определить параметры связи.

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

На приведенном изображении цифра 1 означает, что в таблице слева может быть только одна связанная запись. В таблице «Заказы» каждому заказу может соответствовать только одна запись.

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

Виды связей между таблицами

Между таблицами могут быть установлены связи трех видов:

Один-к-одному. Каждый элемент используется в каждой таблице только один раз. Например, каждый сотрудник может использовать только один служебный автомобиль. Дополнительные сведения см. в статье Создание связей типа «один-к-одному».

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

Многие-ко-многим. Для одного или нескольких элементов из первой таблицы можно создать связь с одним или несколькими элементами из второй таблицы. Например, в каждый заказ может входить несколько продуктов, и каждый продукт может быть указан в нескольких заказах. Дополнительные сведения см. в статье Создание связей типа «многие-ко-многим».

Связи типа «один ко многим»

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

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

В показанной ниже связи у каждого человека из таблицы «Контакты» есть идентификатор, представляющий собой первичный ключ (он отмечен значком ключа). Этот идентификатор также используется в поле «Владелец» в таблице «Активы». Чтобы написать электронное письмо человеку, связанному с активом, следует использовать значение поля «Адрес электронной почты». Для этого необходимо узнать значение поля «Владелец» из таблицы «Активы», а затем найти этот идентификатор в таблице «Контакты». Число 1 на одном конце соединительной линии и знак «∞» на другом означают, что это связь типа «один-ко-многим», поэтому один контакт может быть связан с несколькими активами.

Изменение связи

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

Примечание: Если необходимые таблицы открыты, сначала следует закрыть их, а также любые открытые объекты, которые их используют.

Выберите Работа с базами данных > Схема данных.

Выберите линию, соединяющую две связанные таблицы.

Совет: Если необходимая связь отсутствует, на вкладке Конструктор в группе Связи нажмите кнопку Все связи.

На вкладке Конструктор нажмите кнопку Изменить связи.

Таблица/запрос — это родительская таблица, указанная слева (в рассмотренном примере — «Клиенты»).

Связанная таблица/запрос — это дочерняя таблица (в рассмотренном примере — «Заказы»).

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

Чтобы изменить соединенные поля, выберите другое поле под каждой отображаемой таблицей. В рассмотренном примере поле «Код» из таблицы «Клиенты» соединяется с полем «Код клиента» из таблицы «Заказы».

Настройте синхронизацию данных между таблицами.

Обеспечение целостности данных

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

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

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

Каскадное обновление связанных полей

Установите этот флажок, чтобы данные в связанных полях обновлялись во всех связанных таблицах.

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

Каскадное удаление связанных записей

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

Предположим, вы удалили грузоотправителя. Если выбран этот параметр, Access удаляет все записи во всех таблицах, ссылаясь на этот ИД грузоотправителя, включая все заказы (в таблице «Заказы»), отправленные этим грузоотправии. Этот параметр можно выбрать только в том случае, если вы уверены, что хотите удалить историю заказов.

Чтобы изменить связь между таблицами с внутреннего соединения на внешнее, нажмите кнопку Объединение. Дополнительные сведения см. в статье Создание запросов с внешними соединениями.

Удаление отношения между таблицами

Примечание: Если необходимые таблицы открыты, сначала следует закрыть их, а также любые открытые объекты, которые их используют.

Удаление связи между таблицами

Выберите Работа с базами данных > Схема данных.

Выберите линию, соединяющую две связанные таблицы.

Совет: Если необходимая связь отсутствует, на вкладке Конструктор в группе Связи нажмите кнопку Все связи.

Нажмите клавишу DELETE. Если потребуется подтвердить удаление, нажмите кнопку Да.

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

Источник

Что необходимо для установления соответствия между таблицами базы данных

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

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

Связи между таблицами устанавливаются двумя способами:

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

— создание вложенных таблиц данных.

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

§ Поле Счетчик разрешается связывать с числовым полем, если в числовом поле в свойстве Размер поля задано значение Длинное целое;

§ Поле Счетчик разрешается связывать с числовым полем. Если для обоих полей в свойстве Размер поля задано значение Код репликации.

Рекомендации разработчику базы данных:

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

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

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

5.2. Установление ключей в таблицах

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

На рис. 5.1 показано окно с описанием таблицы Сотрудники в режиме конструктор после установления ключевого поля.

5.3. Создание непосредственных связей

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

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

3. На вкладке «Таблицы» (рис. 5.4) перечислены все, ранее созданные, таблицы, которые следует перенести на поле «Схема данных», представленное на рис. 5.5.

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

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

5.4. Создание вложенных таблиц

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

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

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

1. Открыть таблицу (в нашем примере – Должности на фирме) в режиме просмотра таблицы.

2. В строке меню базы данных выбрать закладку вставка и выполнить команду « Подтаблица».

3. В открывшемся окне «Вставка подтаблицы» выбрать имя подтаблицы, например, Сотрудники. В раскрывающемся списке «Подчиненные поля» выбрать имя поля «Должность», а в окне «Основные поля» выбрать – «Должность на фирме», как это показано на рис. 5.11.

4. Нажать на кнопку ОК. Система выведет транспарант, который представлен на рис. 5.12, конечно следует ответить « Да», после чего будут созданы вложенные таблицы в таблицу Должность на фирме.

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

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

Вопросы для самоконтроля

1. Какие существуют способы установления связей между таблицами?

2. Для чего в таблицах устанавливают ключевое поле?

3. Какие типы данных можно хранить в ключевом поле?

4. Какое существует основное правило для решения вопроса о связывании полей разных таблиц?

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

6. В чем заключается последовательность установки связей между таблицами в окне «Схема данных»?

7. Как осуществлять операцию изменения связей?

Задания для самостоятельной работы

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

· Добавьте таблицу «Льготы на фирме» в окно «Схема данных» и выберите таблицу, с которой можно осуществить связь.

Источник