Создание взаимосвязанных и подчиненных таблиц

ПРАКТИЧЕСКАЯ РАБОТА №2. УСТАНОВКА СВЯЗЕЙ МЕЖДУ ТАБЛИЦАМИ.

Типы связей

Связи объектов могут быть разного типа: один-к-одному (1:1), один-ко-многим (1:М) и многие-ко-многим (М:N).

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

Связь один-ко-многим (1:М)– связи один-ко-многим связывают одну строку какой-либо таблицы с двумя или несколькими строками другой таблицы. Связь при этом устанавливается между первичным ключом основной таблицы и соответствующим внешним ключом связанной таблицы. Связи один-ко-многим больше всего распространены между таблицами. Например, связь один-ко-многим ставит все записи таблицы Порты в соответствие одной единственной записи таблицы Суда. С помощью этой связи можно отобрать и отобразить на экране все порты, в которые будет заходить выбранный корабль, - в данном случае отображены все записи таблицы Порты, относящиеся к кораблю.

Связь многие – к-одному. –это зеркальное отражение связи один-ко-многим.

Связь многие-ко-многим (M:N)– эту связь нельзя установить между двумя таблицами непосредственно. Она устанавливается после создания третьей таблицы, связанной с двумя основными таблицами отношением многие-к-одному.

Создание взаимосвязанных и подчиненных таблиц.

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

Примером одной из таких таблиц является вложенная таблица «Приход». Почему данная таблица является вложенной. Разберемся с этим.

Главной нашей целью является ввод данных о покупке товаров в определенный день у конкретного поставщика. Поставщик один, а товаров, которых он продает нашему предприятию может быть множество. Например, поставщик «МЧП БЕЛИНДА» продает товары каша гречневая, каша с курицей, каша с грибами. При этом другой поставщик «ЧП МАКРОС» продает только кашу гречневую и с курицей. И так далее.

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

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

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

Для того чтобы создать взаимосвязанные таблицы, необходимо выполнить следующую последовательность действий:

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

2. Данные в главную таблицу не вводить.

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

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

5. Данные в подчиненную таблицу не вводить.

6. Установить связь один-ко-многим между главной и подчиненной таблицами.

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

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

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

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

Поэтому для завершения работы с вложенной таблицей необходимо также:

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

8. Для улучшения ввода данных создать подчиненные поля.

Создание таблицы Приход.

1. Создайте новую таблицу в режиме конструктора.

2. В конструкторе введите поля, согласно рис.10.

Примечание !!! В таблице будут использоваться данные о поставщиках, поэтому обязательным полем будет Код_поставщика и тип его Числовой.

2. Закройте конструктор и сохраните таблицу под именем Приход.

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

Для того чтобы было удобно просматривать, создавать, удалять и модифицировать связи между таблицами, в Microsoft Access используется схема данных (Relationships) (рис. 11).

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

· Щелкнуть правой кнопкой мыши на свободном пространстве схемы данных и из контекстного меню выбрать команду Добавить таблицу(Show table).

· В диалоговом окне Добавление таблицы(Show table) раскрыть вкладку Таблицы(Tables), выбрать из списка таблицу, и нажать кнопку Добавить(Add).

· Нажать кнопку Закрыть(Close), чтобы закрыть диалоговое окно Добавление таблицы(Show table).

5. Для создания связи между таблицами следует выбрать в главной таблице поле для связи, нажать левую кнопку мыши и перетащить поле во вторую таблицу. Отпустить левую кнопку мыши над тем полем подчиненной таблицы, с которым устанавливается связь. Выберите соответственно поле Код_поставщика из таблицы Поставщик и нажмите левую клавишу мыши и перетащите поле на соответвующее поле Код_поставщика таблицы Приход, отпустите мышь. После этого появится диалоговое окно Изменение связей(Edit Relationships) (рис. 12).

· В диалоговом окне Изменение связей(Edit Relationships) можно выбрать из списков названия связанных таблиц и полей для связывания. Если отношение между таблицами "один-ко-многим", то слева из списка Таблица/запрос(Table/ Query) выбирается главная таблица и поле в этой таблице, а справа из списка Связанная таблица/запрос— подчиненная и соответственно поле в ней. Если отношение "один-к-одному", то порядок таблиц значения не имеет. Если вы устанавливали связь графически, то все поля в списках уже выбраны, и нужно только определить правила ссылочной целостности.

· Для этого устанавливают флажок Обеспечение целостности данных(Enforce Referential Integrity) и один или оба флажка: каскадное обновление связанных полей(Cascade Update Related Fields) и каскадное удаление связанных записей(Cascade Delete Related Records) (см. следующий раздел).

6. Установите свойства связи: обеспечение целостности данных, каскадное обновление и удаление данных.

· При необходимости можно изменить параметры объединения, для чего требуется нажать кнопку Объединение(Join Type) и выбрать один из переключателей (рис. 13):

o 1. Объединение только тех записей, в которых связанные поля обеих таблиц совпадают;

o 2. Объединение ВСЕХ записей из <имя первой таблицы> и только тех записей из <имя второй таблицы>, в которых связанные поля совпадают;

o 3. Объединение ВСЕХ записей из <имя второй таблицы> и только тех записей из <имя первой таблицы >, в которых связанные поля совпадают.

По умолчанию устанавливается первый переключатель. Нажать кнопку ОК.

· После установки всех параметров связи необходимо нажать кнопку ОК в окне Параметры объединения(Edit Relationship).

Изменять параметры объединения не нужно.

7. Закройте схему данных и сохраните ее.

8. Откройте таблицу Приход в режиме конструктора.

9. Щелкните на поле Код_поставщика, преобразуем его в постановочное поле. Для этого:

· В свойствах поля Код_поставщика, перейдите на вкладку Подстановка.

· Выберите – Поле со списком.

· Нажмите на троеточие в свойстве Источник строк - …

· У Вас откроется окно – MS ACCESS – Инструкция SQL: построитесь запросов. Это окно, с которым мы познакомимся более подробно на следующих занятиях. Его назначение – создание запросов. В нашем случае мы хотим создать раскрывающийся список с данными о поставщике, в котором будет вместо простого его номера отображаться информация о его названии, идентификационном коде, фамилии директора.

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

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

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

· Чтобы добить поле Код_поставщика в запрос выделите его в таблице Поставщики и два раза щелкните левой клавишей мыши. При этом в нижней области конструктора отобразится поле Код_поставщика. Аналогично добавьте 3 поля – те поля, которые мы хотим увидеть в списке – название_поставщика, идентф_код, ФИО_директора. У вас должен получится запрос приведенный на рис.15.

· Закройте конструктор запроса, подтвердите сохранение.

· Вернувшись обратно в свойства Код_поставщика у вас в поле Источник строк будет отображен созданный нами sql-запрос, см. 16.

· Также дополнительно измените свойства подстановки:

· Число полей - 4

· Заглавия столбцов – Да.

· Ширина полей – 0;3;0;0. Почему мы устанавливаем нулевую ширину столбцов? Установив нулевую ширину первого столбца Код_поставщика мы скрываем ненужный для обычных пользователей сгенерированный код Поставщика. Обычному пользователю более информативно увидеть название поставщика. А почему мы скрываем информацию о коде и фамилии директора? Дело в том, что сейчас при создании таблицы Приход в подчиненном поле достаточно увидеть информацию о названии поставщика, а вот при создании в дальнейшем соответствующей формы мы воспользуемся скрытыми полями.

Таким образом, у поля Код_поставщика свойства подстановки будут иметь вид, см. рис.16.

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

· Перейдите навкладку Общиеи измените свойства поля: Подпись – Поставщик, и уберите Значение по умолчанию равное 0.

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

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

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