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

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

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

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

Принципиальная схема простейшей телефонной линии показана на рисунке. Используются два электронных телефонных аппарата - телефоны-трубки с кнопочными номеронабирателями, но можно взять и простые
дисковые аппараты. Они связаны между собой двумя проводами. На эти провода через сопротивление R1 поступает постоянное напряжение величиной 60-80В. Когда трубки обеих ТА повешены между этими проводами имеется напряжение 60-80В, поскольку в таком состоянии внутренние сопротивления ТА очень высоки.

Предположим, подняли трубку ТА2, при этом его рычажный переключатель подключает к линии его разговорный узел и сопротивление ТА2 резко уменьшается, на столько, что напряжение в линии падает до 8-10В (напряжение падает потому, что из резистора R1 и сопротивления разговорного узла ТА получается делитель напряжения, а линия оказывается подключенной как раз через этот делитель).

Если начать говорить перед микрофоном ТА2, сопротивление его разговорного узла начинает изменяться в такт с речью, а значит изменяется и общее сопротивление ТА2. При этом R1 остается неизменным и в линии напряжение изменяется в такт с речью. Телефонный аппарат ТА1 также подключен к этой линии и если поднять его трубку можно услышать то, что говорят перед микрофоном ТА2. Если абонент ТА1 ответит, его голос услышит абонент ТА2. Таким образом можно разговаривать друг с другом.

Для вызова абонента телефонный аппарат имеет звонок или зуммер, он подключен к линии всегда, когда трубка ТА повешена и реагирует на любой значительный перепад напряжения в линии. Если поднять трубку ТА2 то звонок ТА1 "звякнет" один раз, потому что в линии был один перепад напряжения, повесите трубку - ТА1 "звякнет" еще раз.

В составе ТА имеется номеронабиратель, кнопочный или дисковый, неважно, в любом случае он служит для создания в линии перепадов напряжения, эти перепады принимает телефонная станция, отсчитывает их и таким образом определяет номер абонента. В нашем случае телефонной станции нет, но номеронабиратель можно использовать для вызова абонента. Если вы набираете цифру "9" ТА создает в линии девять перепадов напряжения, а это значит что второй ТА "звякнет" уже не один раз а девять, если цифру "0" - десять раз.

Таким образом чтобы вам с телефона ТА2 позвонить своему другу на ТА1 вам нужно поднять трубку ТА2 и несколько раз подряд набрать "О" (или другую цифру). В результате ТА1 зазвонит, звонок услышит ваш друг, и подняв трубку сможет с вами разговаривать. Точно также можно вызвать ТА2 с аппарата ТА1.

Постоянное напряжение для питания линии получается из сетевого напряжения 220В при помощи трансформатора Т1 и выпрямителя на диодах VD1-VD4. Пульсации выпрямленного напряжения сглаживаются конденсатором С1. Трансформатор Т1 можно сделать из малогабаритного трансформатора от сетевого источника для питания игрушек или переносной аппаратуры. Нужно разобрать корпус источника и подключив трансформатор при помощи удлинителя к сети измерить напряжение на его вторичной обмотке. Предположим вольтметр показал 6В.

Нам нужно 50 В. Теперь отключаем трансформатор от сети, разбираем его пластинчатый сердечник и разматываем вторичную обмотку (не сетевую), аккуратно, отсчитывая её витки. Допустим насчитали 120 витков, этот при том, что выходное напряжение было 6В. Теперь рассчитываем сколько у данного трансформатора на 1В приходится витков: 120 / 6 = 20, итого 20 витков. Нам нужно 50 В, значит вторичная обмотка должна содержать 20x50 = 1000 витков. Берем тонкий намоточный провод, например ПЭВ 0,06 или ПЭВ 0,09 и осторожно наматываем этим проводом 1000 витков вместо старой вторичной намотки.

Затем собираем сердечник трансформатора. Можно взять готовый трансформатор, который вырабатывает как минимум 36В и как максимум 50В.
Диоды Д226 можно заменить на любые выпрямительные, например на Д7 или на современные КД102, КД103, КД105, КД226, КД209. Конденсатор С1 на напряжение не менее 100В и емкость не менее 160 мкФ. Резистор R1 сопротивлением от 1 кОм до 1,8 кОм на мощность не менее 2 Вт. Тумблер S1 любого типа.

Конструктивно источник питания может быть подключен к линии в любом месте, возле ТА2, возле TA1 или вообще посредине. Например если ТА2 находится в электрофицированном помещении, а ТА1 в садовой беседке, понятно, что источник подключается возле TA2.

При монтаже не перепутайте полярность подключения конденсатора С1 и диодов. У диодов Д226 на корпусе нанесено графическое изображение диода, вдоль корпуса, так чтобы можно было понять назначение выводов.

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

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

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

Способ 1: прямое связывание таблиц формулой

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

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

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

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

Способ 2: использование связки операторов ИНДЕКС — ПОИСКПОЗ

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

1. Выделяем первый элемент столбца «Ставка» . Переходим в Мастер функций , кликнув по пиктограмме «Вставить функцию» .



2. В Мастере функций в группе «Ссылки и массивы» находим и выделяем наименование «ИНДЕКС» .



3. Данный оператор имеет две формы: форму для работы с массивами и ссылочную. В нашем случае требуется первый вариант, поэтому в следующем окошке выбора формы, которое откроется, выбираем именно его и жмем на кнопку «OK» .



4. Выполнен запуск окошка аргументов оператора ИНДЕКС . Задача указанной функции — вывод значения, находящегося в выбранном диапазоне в строке с указанным номером. Общая формула оператора ИНДЕКС такова:

   ИНДЕКС(массив;номер_строки;[номер_столбца])

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

   «Номер строки» — аргумент, являющийся номером этой самой строчки. При этом важно знать, что номер строки следует указывать не относительно всего документа, а только относительно выделенного массива.

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

   Ставим курсор в поле «Массив» . После этого переходим на Лист 2 и, зажав левую кнопку мыши, выделяем все содержимое столбца «Ставка» .



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



6. Запускается стандартное окно Мастера функций . Переходим в нем в ту же самую группу «Ссылки и массивы» . На этот раз в перечне выбираем пункт «ПОИСКПОЗ» . Выполняем щелчок по кнопке «OK» .



7. Производится активация окошка аргументов оператора ПОИСКПОЗ . Указанная функция предназначена для того, чтобы выводить номер значения в определенном массиве по его наименованию. Именно благодаря данной возможности мы вычислим номер строки определенного значения для функции ИНДЕКС . Синтаксис ПОИСКПОЗ представлен так:

   ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

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

   «Просматриваемый массив» — аргумент, представляющий собой ссылку на массив, в котором выполняется поиск указанного значения для определения его позиции. У нас эту роль будет исполнять адрес столбца «Имя» на Листе 2 .

   «Тип сопоставления» — аргумент, являющийся необязательным, но, в отличие от предыдущего оператора, этот необязательный аргумент нам будет нужен. Он указывает на то, как будет сопоставлять оператор искомое значение с массивом. Этот аргумент может иметь одно из трех значений: -1 ; 0 ; 1 . Для неупорядоченных массивов следует выбрать вариант «0» . Именно данный вариант подойдет для нашего случая.

   Итак, приступим к заполнению полей окна аргументов. Ставим курсор в поле «Искомое значение» , кликаем по первой ячейке столбца «Имя» на Листе 1 .



8. После того, как координаты отобразились, устанавливаем курсор в поле «Просматриваемый массив» и переходим по ярлыку «Лист 2» , который размещен внизу окна Excel над строкой состояния. Зажимаем левую кнопку мыши и выделяем курсором все ячейки столбца «Имя» .



9. После того, как их координаты отобразились в поле «Просматриваемый массив» , переходим к полю «Тип сопоставления» и с клавиатуры устанавливаем там число «0» . После этого опять возвращаемся к полю «Просматриваемый массив» . Дело в том, что мы будем выполнять копирование формулы, как мы это делали в предыдущем способе. Будет происходить смещение адресов, но вот координаты просматриваемого массива нам нужно закрепить. Он не должен смещаться. Выделяем координаты курсором и жмем на функциональную клавишу F4 . Как видим, перед координатами появился знак доллара, что означает то, что ссылка из относительной превратилась в абсолютную. Затем жмем на кнопку «OK» .



10. Результат выведен на экран в первую ячейку столбца «Ставка» . Но перед тем, как производить копирование, нам нужно закрепить ещё одну область, а именно первый аргумент функции ИНДЕКС . Для этого выделяем элемент колонки, который содержит формулу, и перемещаемся в строку формул. Выделяем первый аргумент оператора ИНДЕКС (B2:B7 ) и щелкаем по кнопке F4 . Как видим, знак доллара появился около выбранных координат. Щелкаем по клавише Enter . В целом формула приняла следующий вид:

    ИНДЕКС(Лист2!$B$2:$B$7;ПОИСКПОЗ(Лист1!A4;Лист2!$A$2:$A$7;0))



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



12. Как видим, несмотря на то, что порядок строк у двух связанных таблиц не совпадает, тем не менее, все значения подтягиваются соответственно фамилиям работников. Этого удалось достичь благодаря применению сочетания операторов ИНДЕКС —ПОИСКПОЗ .

Создание связи (отношения) типа «один ко многим» между таблицами базы данных Microsoft SQL Server

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

Условие задачи

Дана база данных Microsoft SQL Server . База данных размещается в файлах «MyDatabase.mdf» и «MyDatabase.ldf» . Загрузить архив с готовыми для работы файлами базы данных можно .

В базе данных заданы две таблицы с именами Source и Emission. Таблица Source определяет источник загрязненных выбросов. Таблица Emission определяет время выбросов и число загрязненных выбросов, которое было сформировано источником.

Структура таблиц следующая.

Таблица Source.

Таблица Emission

Используя средства системы Microsoft Visual Studio необходимо реализовать связь (отношение) «один ко многим» между таблицами Source и Emission по полю ID_Source.

Выполнение

1. Запуск Microsoft Visual Studio

Запустить систему визуальной разработки приложений Microsoft Visual Studio .

2. Создание/подключение базы данных

На этом шаге нужно подключить (или создать) готовую базу данных «MyDataBase.mdf» , которая состоит из двух файлов:

• файл «MyDataBase.mdf» ;
• файл «MyDataBase.ldf» .

Архив с файлами можно скачать .

После подключения окно Server Explorer будет иметь вид, как показано на рисунке 1.

Рис. 1. Окно Server Explorer после подключения базы данных «MyDataBase.mdf»

3. Поля ID_Source и ID_Emission

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

Поле ID_Source таблицы Source есть первичным ключом.

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

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

4.1. Снятие опции

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

Для этого нужно выполнить следующую последовательность шагов:

• перейти в меню Tools главного меню MS Visual Studio ;
• в меню Tools выбрать команду «Options…» . В результате откроется окно «Options» (рисунок 2);
• в левой части окна «Options» последовательно раскрыть вкладки «Database Tools» ->«Table and Database Designers» (рисунок 2);
• выбрать элемент «Table and Diagram Options» . В результате в правой части окна активируется группа элементов «Table Options» ;
• в группе «Table Options» снять пометку из опции «Prevent saving changes that require table re-creation» (рисунок 2) и подтвердить выбор (кнопка OK ).

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

Рис. 2. Опция «Prevent saving changes that require table re-creation»

4.2. Установление первичного ключа (Primary Key ) в таблице Source

Как видно из структуры таблиц (рисунок 1) общим для таблиц есть поле ID_Source. Связь между таблицами будет осуществляться по этому полю.

В таблице Source нужно установить поле ID_Source как «Первичный ключ» (Primary Key ).

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

• перейти в режим редактирования таблицы Source выбором команды «Open Table Definition» (рисунок 3). Откроется окно редактирования таблицы;
• сделать клик правой кнопкой «мышки» на строке ID_Source и в контекстном меню выбрать команду «Set Primary Key» . В результате поле ID_Source будет обозначено как поле, которое есть первичным ключом (рисунок 5);
• сохранить и закрыть таблицу Source .

Рис. 3. Команда «Open Table Definition»

Рис. 4. Установление первичного ключа в таблице Source

Рис. 5. Поле ID_Source в таблице Source после установления первичного ключа

В таблице Emission не обязательно устанавливать первичный ключ.

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

Для создания связей между таблицами используется элемент “Database Diagrams» базы данных «MyDataBase.mdf» . Чтобы создать связь между таблицами нужно выполнить следующие действия:

• с помощью клика правой кнопкой «мышки» вызвать контекстное меню (рисунок 6). В меню выбрать команду «Add New Diagram» . В результате, база данных создаст пустую диаграмму. Будет выведено окно «Add Table» добавления таблиц в диаграмму (рисунок 7);
• поочередно выбрать нужные таблицы (Source, Emission) и подтвердить выбор нажатием на кнопке «Add Table» ;
• закрыть окно «Add Table» .

Рис. 6. Команда добавления новой диаграммы

Рис. 7. Окно «Add Table» добавления таблиц в диаграмму

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

Рис. 8. Таблицы Source и Emission

Чтобы начать устанавливать отношение между таблицами, нужно сделать клик на поле ID_Source таблицы Source, а потом (не отпуская кнопку мышки) перетянуть его на поле Source таблицы Emission .

В результате последовательно откроются два окна: Tables and Columns (рисунок 9) и Foreign Key Relationship (рисунок 10), в которых нужно оставить все как есть и подтвердить свой выбор на кнопке OK .

В окне «Tables and Columns» есть такие поля (рисунок 9):

• поле «Relationship name» . В этом поле задается имя объекта, который символизирует соединение (отношение) между таблицами. С помощью этого объекта (имени) можно управлять некоторыми свойствами связи (отношения). В нашем случае соединение (отношение) имеет название FK_Emission_Source ;
• поле «Primary key table:» . Это поле задает таблицу, которая есть первичной по отношению к другой таблице. В нашем случае первичной есть таблица Source. Поле, которое служит первичным ключом таблицы имеет название ID_Source ;
• поле «Foreign key table:» указывает название таблицы (Emission ) и поля в этой таблице (ID_Source ), которое есть внешним ключом.

Рис. 9. Окно настройки параметров связи (отношения) между таблицами

В окне «Foreign Key Relationship» настраиваются свойства соединения. Можно оставить все как есть.

Рис. 10. Настройка свойств соединения FK_Emission_Source

4.4. Диаграмма связи

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

Рис. 11. Вид диаграммы после установки отношения (связи) между таблицами Source и Emission

Как видно из рисунка, конец соединения (отношения), что примыкает к таблице Source имеет вид ключа. А конец соединения, которое примыкает к таблице Emission имеет вид знака бесконечность ∞ .

Это означает, что в таблице Source числовое значение в поле ID_Source может встречаться только один раз. А в таблице Emission числовое значение ID_Source может повторяться (бесконечное количество раз). Таким образом можно представить любое множество уникальных объектов, которые имеют свойство повторяться в некоторой предметной области.

Рис. 12. Задание имени для диаграммы

Также, система может вывести окно сохранения таблиц в базе данных (рисунок 13), поскольку между таблицами уже существует отношение (связь). В этом окне нужно указать «Yes» .

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

После выполненных действий, диаграмма Diagram1 отобразится в окне Server Explorer (рис. 14). С помощью команд контекстного меню есть возможность управлять диаграммой. Так, например, команда «Design Database Diagram» переводит диаграмму в режим редактирования, в котором можно изменять связи между таблицами базы данных.

Рис. 14. Команда редактирования связей (отношений) между таблицами

5. Программное управление данными

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

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

Определение

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

Представленная схема наглядно отображает суть данного отношения.

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

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

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

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

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

Microsoft Access

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

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

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

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

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

Схема данных

Связи между таблицами в СУБД "Майкрософт Аксес" реализуются при помощи вкладки "Схема данных". На появившуюся панель добавляются все необходимые сущности (в нашем случае все три таблицы). Создание связи многие-ко-многим будет происходить при помощи двух отношений один-к-многих между главными ("Студенты" и "Преподаватели") и промежуточной таблицы. Для этого необходимо соединить между собой соответствующие первичные ключи.

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

SQL

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Отношение в Access позволяет объединить данные из двух разных таблиц. Каждое отношение состоит из двух полей (по одному в каждой таблице) со связанными данными. Например, таблицы "Товары" и "Сведения о заказах" могут содержать поле "КодТовара". Каждая запись в таблице "Сведения о заказах" содержит значение поля "КодТовара", которому соответствует запись в таблице "Товары" с тем же значением в данном поле.

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

Перед началом работы с отношениями изучите основные понятия. Дополнительные сведения о см. в статьях Руководство по связям между таблицами и Начало работы со связями между таблицами .

В этой статье

Обзор

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

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

Перетащите поле в таблицу из области Список полей .

При создании отношения между таблицами общие поля могут называться по-разному, однако часто требуется, чтобы эти имена совпадали. Очевидно, что общие поля должны иметь одинаковый тип данных. Однако если поле первичного ключа имеет тип "Счетчик", поле внешнего ключа также может быть числовым, если свойство Размер поля (FieldSize) обоих полей совпадает. Например, можно сопоставить поля с типами "Счетчик" и "Числовой", если свойство Размер поля обоих полей имеет значение "Длинное целое". Если оба общих поля являются числовыми, у них должно совпадать значение свойства Размер поля .

Создание отношения между таблицами с помощью окна отношений

Создание отношения между таблицами с помощью панели списка полей

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

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

Открытие таблицы в режиме таблицы

Дважды щелкните таблицу в области навигации.

Открытие области "Список полей"

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

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

Добавление поля и создание связи из области "Список полей"

Открыв таблицу в режиме таблицы, нажмите клавиши ALT+F8. Будет отображена область Список полей .

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

Перетащите нужное поле из области Список полей в таблицу, открытую в режиме таблицы.

Когда появится линия вставки, поместите поле в выбранное место.

Появится окно мастера подстановок .

Следуйте инструкциям мастера подстановок .

Поле будет отображено в таблице в режиме таблицы.

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

Изменение отношения

Чтобы изменить отношение, выберите его в окне "Схема данных" и внесите нужные изменения.

Установите указатель на линию связи и щелкните линию, чтобы выделить ее.

При выделении линия связи становится толще.

Дважды щелкните выделенную линию связи

на вкладке Конструктор в группе Сервис выберите команду Изменить связи .

Откроется диалоговое окно Изменение связей .

Открытие диалогового окна "Изменение связей"

Указание типа соединения

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

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

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

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

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

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

Указание типа соединения

В диалоговом окне Изменение связей нажмите кнопку Тип соединения .

Откроется диалоговое окно Параметры соединения .

Выберите нужные параметры и нажмите кнопку ОК .

В таблице ниже, составленной на основании таблиц "Клиенты" и "Заказы", приведены три варианта, отображаемые в окне Параметры объединения , указан используемый в них тип объединения, а также то, какие именно строки (все или только сопоставленные) возвращаются для каждой из таблиц.

При выборе варианта 2 или 3 на линии связи будет отображена стрелка, указывающая на ту сторону связи, в которой отображаются только сопоставленные строки.

Внесение изменений в окне "Параметры объединения"

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

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

Включение и отключение обеспечения целостности данных

На вкладке Работа с базами данных в группе Отношения нажмите кнопку Схема данных .

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

скрытый в диалоговом окне Свойства Параметры переходов


Дважды щелкните линию связи. Откроется диалоговое окно Изменение связей .

Выберите или отмените параметр .

ОК .

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

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

Не допускается удаление записи из главной таблицы, если в связанной таблице существуют связанные с ней записи. Например, невозможно удалить запись из таблицы "Сотрудники", если в таблице "Заказы" имеются заказы, относящиеся к данному сотруднику. Однако можно удалить главную запись и все связанные записи одним действием, установив флажок .

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

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

• Общее поле главной таблицы должно быть первичным ключом или иметь уникальный индекс.

  Общие поля должны иметь одинаковый тип данных. Единственное исключение - поле типа "Счетчик" можно связать с полем типа "Числовой", если его свойство Размер поля имеет значение Длинное целое .

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

Задание каскадных параметров

Иногда возникает ситуация, в которой требуется изменить значение только на стороне "один" отношения. В этом случае необходимо, чтобы Access автоматически обновил все затронутые строки в ходе выполнения одной операции. Тогда обновление будет полностью завершено, а база данных не будет находиться в несогласованном состоянии, когда некоторые строки обновлены, а другие - нет. Этой проблемы можно избежать с помощью параметра Access "Каскадное обновление связанных полей". Если при включении обеспечения целостности данных был включен параметр "Каскадное обновление связанных полей", то при последующем обновлении первичного ключа автоматически будут обновлены все связанные с ним поля.

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

Включение и отключение каскадного обновления и каскадного удаления

На вкладке Работа с базами данных в группе Отношения нажмите кнопку Схема данных .

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

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


Щелкните линию отношения, которое вы хотите изменить. При выделении линия связи становится толще.

Дважды щелкните линию связи.

Откроется диалоговое окно Изменение связей .


Установите флажок Обеспечение целостности данных .

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

Внесите в отношение необходимые изменения и нажмите кнопку ОК .

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

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

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

Чтобы удалить отношение между таблицами, нужно удалить линию связи в окне "Схема данных". Установите указатель мыши на линию связи и щелкните ее. При выделении линия связи становится толще. Выделив линию связи, нажмите клавишу DEL.

На вкладке Работа с базами данных в группе Отношения нажмите кнопку Схема данных .

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

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


Щелкните линию связи, которую требуется удалить. При выделении линия связи становится толще.

Нажмите клавишу DEL

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

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

Создание, изменение и удаление отношения в веб-приложении Access

Существуют важные отличия при работе со связями в веб-приложении Access.

Создание отношения

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

Примечание: Поле, используемое в качестве источника для подстановки, должно существовать перед созданием поля подстановки.

Вот как можно создать поле подстановки в веб-приложении Access:

Изменение отношения

В веб-приложении Access отсутствует окно "Схема данных". Поле в одной таблице служит источником (полем подстановки) значений для связанного поля в другой таблице.

Удаление отношения

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

Создание или изменение отношения в веб-базе данных Access 2010

Чтобы создать отношение в веб-базе данных Access 2010, необходимо использовать мастер подстановок. Окно "Схема данных" отсутствует в веб-базе данных. Поле в одной таблице используется в качестве источника для значения в связанном поле другой таблицы.

Примечание: Воспользоваться мастером подстановок можно только при наличии поля, используемого в качестве источника значений.

Создание в веб-базе данных Access 2010 отношения с помощью мастера подстановок

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

Справа от последнего поля нажмите кнопку Щелкните для добавления , а затем выберите пункт Подстановка и отношение .

Примечание: Для отображения кнопки Щелкните для добавления , может понадобиться прокрутить страницу по горизонтали.

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

Выберите таблицу-источник и нажмите кнопку Далее .

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

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

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

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

• Каскадное удаление позволяет при удалении записи в одной таблице удалять соответствующую запись в другой.

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

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

Изменение отношения в веб-базе данных Access 2010

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

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

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

Внесите нужные изменения в соответствии с инструкциями мастера. Можно изменить следующие элементы.