2. MS Access. Уровень 1. Связи с таблицами

Создание связи вручную и с помощью мастера. Мастер подстановок: формирование списка данных для поля таблиц и связывание полей таблиц. Связи между таблицами: ОДИН-К-ОДНОМУ, ОДИН-КО-МНОГИМ и МНОГИЕ-КО-МНОГИМ. Схема данных. Целостность данных. Первичный(главный) ключ, вторичный(внешний) ключ. Импорт данных: из базы данных MS Access, MS Excel и текстового файла. Импорт в одну таблицу с добавлением из разных. Импорт связанной таблицы. Анализ таблиц: разбиение таблиц на несколько с созданием связи.

Запустим программу MS Access 2010, откроем нашу базу данных Студенты и
откроем таблицу Студенты в режиме конструктора.

Для поля Пол, выбираем тип Мастер подстановок. Далее в диалоге выбираем флажок
Будет введён фиксированный набор значений, жмём Далее>, в столбец1 введём две строки:
Мужской и Женский, жмём Далее>, выбираем Ограничиться списком, и жмём Готово.

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

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

Самостоятельно и аналогично, полю Пол, создадим список для поля Деятельность.
В качестве набора значений введём: Библиотекарь, Сотрудник профкома, Сотрудник актового зала, Участник КВН, Сотрудник профилактория,
IT-специалист, Лаборант, Сотрудник студ. городка, Музыкант и Волонтёр. Затем в режиме таблицы для нескольких записей из раскрывающегося списка поля
Деятельность добавим несколько данных.

В режиме конструктора добавим поле Экскурсии, тип данных Мастер подстановок.
В диалоге выбираем первый флажок, жмём Далее>, выбираем источник таблиц – Экскурсии, жмём Далее>,
из доступных полей кнопкой > выбираем поля Название и ДатаЭкскурсии, жмём Далее>,
выберем сортировку по любому полю, жмём Далее>, активность флажка который скрывает ключевой столбец полезен когда
ключевое поле не представляет никакой визуальной ценности, но поскольку у нас ключевое поле это названия экскурсий,
поэтому флажок отключаем чтобы названия экскурсий были видны, жмём Далее>, выбираем поле по которому однозначно
можно определить строки таблицы, в нашем случае это названия экскурсий, жмём Далее>, выбираем флажок проверки
целостности данных. Жмём Готово.

В конструкторе таблицы Студенты в свойстве поля Экскурсии на вкладке Подстановка, убедитесь, что поле Источник строк содержит это:

SELECT [Экскурсии].[Название], [Экскурсии].[ДатаЭкскурсии] FROM Экскурсии;

Также убедимся, что: строка Присоединяемый столбец имеет значение 1, а Число столбцов – 2.

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

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

Таблицы Студенты и Экскурсии связаны между собой. Отобразим визуально связь: Работа с базами данных/Отношения/Схема данных:

На вкладке Схема данных, появится схема из двух таблиц, связанных между собой по полю Название и Экскурсии:

Цифра 1 и символ ∞, указывает на тип связи ОДИН-КО-МНОГИМ. Удалим связь: правый клик по линии и выбираем пункт Удалить или клавишей [Del]

Установим связь вручную(старый метод): в схеме данных, щёлкнем над полем Название таблицы Экскурсии и отпустим над полем Экскурсии таблицы Студенты. Появится диалог Изменение связи:

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

Отобразим значки: правый клик по связи далее Изменить связь, ставим флажок Обеспечение целостности данных. Целостность данных не позволит:

1. добавить студенту – экскурсию, которой нет в таблице Экскурсии,
2. удалить в таблице Экскурсии, ту экскурсию, которая уже есть у студента.

Ещё момент: тип поля Название и Экскурсии должны быть одинаковыми. У нас они текстовые.

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

Чтобы изменить в первичной таблице, значения которое связано с другой таблицей, мы должны установить обновление: закроем все таблицы, затем в схеме данных правый клик по связи/Изменить связь. В диалоге выбираем Каскадное обновление связанных полей и жмём ОК.

Теперь Откроем таблицу Экскурсии в режиме таблицы, изменим название Музей на Театр и откроем таблицу Студенты для проверки данных поля Экскурсии. Закроем файл базы командой Файл/Закрыть базу данных.

Продолжим работать с базой данных студентов. Откроем файл БД_Студенты_1. Выбираем Включить содержимое.

Откроем любые таблицы и увидим, что вкладок нет, т.к. файл был создан в старой версии программы
MS Access 2010. Исправим это командой: Файл/Параметры/Текущая база данных/Параметры приложений
активируем флажок Вкладки.

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

Отобразим схему данных: вкладка Работа с базами данных/Отношения/Схема данных:

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

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

В режиме конструктора таблицы Студенты, добавим поле КодЭкскурсии, тип данных Мастер подстановок. В качестве источника выбираем другую таблицу, жмём Далее>, выбираем таблицу Экскурсии жмём Далее>, кнопкой > выбираем поля: КодЭкскурсии (мы его потом выберем ключевым), Название и ДатаЭкскурсии, жмём Далее>, выберем сортировку поля Название по возрастанию и поля ДатаЭкскурсии по убыванию, жмём Далее>, отображаем ключевой столбец (т.е. галку снимаем), Далее>, на этом шаге выбираем ключевой столбец который мы отразили на предыдущем шаге, жмём Далее>, включаем проверку целостности, жмём Готово и соглашаемся с сохранением.

Убедимся, что тип данных нашего поля изменился на Числовой, это получилось, потому что мы связываем поле нашей таблицы Студенты с полем счётчик первичной таблицы Экскурсии. Обратим внимание на свойства вкладки Подстановка. Поле Источник строк содержит путь к нашим данным. Поле Ширина столбцов содержит три цифры. 2,54см;2,54см;2,54см это размер трёх полей

Откроем таблицу Студенты и введём в поле КодЭкскурсии несколько данных. В ячейках будут выводиться только цифры. Если нужно чтобы отобразились названия, то в режиме конструктора для поля КодЭкскурсии на вкладке Подстановка для свойства Ширина столбцов, первую цифру обнуляем — 0см;2,54см;2,54см, проверим ввод данных в режиме таблица.

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

Удалим новую связь правым кликом по ней пункт Удалить, а в конструкторе таблицы Студенты, удалим поле КодЭкскурсии правым кликом по нему и выбираем Удалить строки

Мы удалили это поле, поскольку Студенты не хотят участвовать только в одной экскурсии, т.е. связь ОДИН-КО-МНОГИМ не подходит, нужна связь МНОГИЕ-КО-МНОГИМ.

В этой связи один студент может пойти на несколько экскурсий. Однако для связи МНОГИЕ-КО-МНОГИМ, двух таблиц будет недостаточно, между таблицами Студенты и Экскурсии нужна третья таблица “посредник”, которая содержит как минимум два поля для соединения первых двух таблиц. Эти два поля по отдельности являются внешними ключами для первичных таблиц, но в совокупности для своей таблицы, они образуют первичный ключ.

В нашей базе данных создадим новую таблицу командой Создание/Таблицы/Конструктор таблиц с двумя полями КодСтудента и КодЭкскурсии, оба поля одновременно делаем первичными: [Shift]+hold, выделяем оба поля и нажимаем на кнопку Ключевое поле:

Правый клик по вкладке, сохраним таблицу как Экскурсанты

Тип данных для поля КодСтудента — Мастер подстановок, в качестве источника выбираем другую таблицу, жмём Далее>, выбираем таблицу Студенты жмём Далее>, кнопкой > выбираем поля: НомерСтудБилета (мы его потом выберем ключевым), Фамилия, Имя и Отчество, жмём Далее>, выберем сортировку полей Фамилия, Имя и Отчество по возрастанию, жмём Далее>, отображаем ключевой столбец (т.е. галку снимаем), Далее>, на этом шаге выбираем ключевой столбец который мы отразили на предыдущем шаге, жмём Далее>, включаем проверку целостности, жмём Готово и соглашаемся с сохранением.

Убедимся, что тип данных нашего поля изменился на Числовой.

Аналогично свяжем поле КодЭкскурсии с таблицей Экскурсии: тип данных Мастер подстановок. В качестве источника выбираем другую таблицу, жмём Далее>, выбираем таблицу Экскурсии жмём Далее>, кнопкой > выбираем поля: КодЭкскурсии (мы его потом выберем ключевым), Название и ДатаЭкскурсии жмём Далее>, сортировку пропускаем, жмём Далее>, отображаем ключевой столбец (т.е. галку снимаем), Далее>, на этом шаге выбираем ключевой столбец который мы отразили на предыдущем шаге, жмём Далее>, включаем проверку целостности, жмём Готово и соглашаемся с сохранением.

Убедимся, что тип данных нашего поля изменился на Числовой. Закрываем все открытые таблицы, убедимся, что отображена схема данных:

Правый клик по фону, выбираем Все связи.

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

В режиме конструктора создадим ещё одну таблицу с участниками футбольной команды учебного заведения:

Имя поля	Тип данных
КодСтудента	Текстовый
Номер	Числовой
Основной	Логический

Для поля Номер на вкладке Общие, настроим следующие свойства:

• Размер поля: Байт
• Число десятичных знаков: Авто
• Обязательное поле: Да
• Индексированное поле: Да (Совпадения не допускаются)

Для поля КодСтудента, создадим первичный ключ, тип данных — Мастер подстановок. Свяжем это поле с таблицей Студенты по полю НомерСтудБилета аналогично тем действиям, что мы уже делали. Таблицу назовём Спортсмены. Закроем таблицу Спортсмены. Откроем схему данных:

Правый клик по свободному месту рабочей области, выбираем Все связи. Отобразилась связь с новой таблицей. Однако между первичными простыми ключевыми полями двух таблиц может существовать одна связь ОДИН-К-ОДНОМУ, а у нас ОДИН-КО-МНОГИМ:

Исправим это: правый клик по связи/Изменить связь в окне убеждаемся что тип отношения ОДИН-К-ОДНОМУ теперь снимем галку Обеспечение целостности данных, ОК, снова изменим связь, заново ставим галку Обеспечение целостности данных и ОК. Теперь между таблицами значки две единицы:

Теперь в режиме таблицы заполним таблицу Спортсмены на своё усмотрение.

Чтобы лучше разбираться в том, какую связь использовать, в базе данных Студенты, нужно решить, если студент посещает одну экскурсию, то это связь ОДИН-КО-МНОГИМ ( 1 : ∞ ). Здесь связывается поле вторичной таблицы Студенты(которое является внешним ключом) с полем первичной таблицы Экскурсии(которое является первичным ключом). При этом одно или несколько экскурсий может быть в таблице Студенты, НО, только одну экскурсию может посетить студент. Отсюда название 1 : ∞.

Если же в нашей базе студент посещает несколько экскурсий, то это связь МНОГИЕ-КО-МНОГИМ ( ∞ : ∞ ). Здесь таблица Экскурсии и Студенты являются первичными и между ними нужно создать вторичную таблицу посредник с минимум двумя полями, каждое для связи со своей первичной таблицей. Эти две таблицы, связываются с посредником отношением ОДИН-КО-МНОГИМ. И как уже упоминалось, два поля в таблице посредник, по отдельности являются внешними ключами для первичных таблиц, но в совокупности, они образуют первичный ключ для самой таблицы посредник.

Связь 1 : 1 используется редко, понять её можно на примере таблицы Студенты, где НЕ КАЖДЫЙ студент может быть спортсменом и иметь собственный номер в спортивной игре. Поэтому такого студента можно связать с отдельной таблицей, например Спортсмены, где содержится спортивный номер студента. Причём в этом типе связи, таблицы соединяются по первичному ключу обоих таблиц. Если ВСЕ студенты будут спортсменами, то необходимость в отдельной таблице Спортсмены отпадает и соответственно связь 1 : 1, не нужна.

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

Закрываем базу БД_Студенты_1 командой Файл/Закрыть базу данных.

Откроем файл базы данных БД_Студенты_1, Выбираем Включить содержимое.

Реализовать хранение данных, о прохождении каждым студентом множества курсов ДОУ с указанием данных
Дата сдачи (с маской ввода через точку), Кол-во баллов (цело число до 100), Наличие сертификата (Да/Нет).

Подсказка 1. Поскольку один студент может пройти несколько курсов ДОУ, то здесь нужна отдельная таблица для реализации связи МНОГИЕ-КО-МНОГИМ через две связи ОДИН-КО-МНОГИМ.

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

Закрываем базу БД Студенты1 командой Файл/Закрыть базу данных.

Откроем файл базы данных БД_Студенты_2. Выбираем Включить содержимое. Ни один объект этой базы не должен быть открыт. Активируем вкладку Внешние данные и в группе Импорт и связи выберем Access:

Откроется диалог для работы с источником наших внешних данных. Нажимаем Обзор выберем файл БД_Борей, оставим флажок Импорт таблиц…, жмём ОК. В диалоге на вкладке Таблицы, выбираем пункт Поставщики, жмём два раза ОК.

В нашей базе БД_Студенты_2.accdb, появилась новая таблица Поставщики, которая базе благодаря флажку Импорт, есть физически в нашей.

Аналогично импортируем текстовой файл Переведённые_студенты. Выполним команду импорта текстового файла:

в диалоге нажимаем Обзор, выберем скопированный файл Переведённые_студенты, (откроется файл, правый клик, сохранить как, выбираем тип Text Document) активируем флажок Импортировать данные, жмём ОК. В диалоге выбираем флажок с разделителями, жмём Далее>. На предварительном просмотре таблицы видно, что первая строка не относится к данным, потому что это заголовок полей таблицы, значит ставим флажок Первая строка содержит имена полей. Если появится сообщение, соглашаемся. Здесь же ставим флажок Табуляция, жмём Далее>. Здесь можно выделить таблицу и настроить некоторые свойства полей, жмём Далее>. Выберем флажок автоматически создавать ключ, жмём Далее> Соглашаемся с именем таблицы по умолчанию, жмём Готово и Закрыть.

Откроем эту таблицу и убедимся что добавлено ключевое поле Код. Закроем таблицу.

Теперь импортируем три таблицы MS Excel, в одну таблицу MS Access, т.е. одну таблицу Excel импортируем как новую таблицу Access, а остальные добавим к новой:

Скопируем на свой диск файл БД_Студенты. Выполним команду Внешние данные/Импорт/Excel, жмём Обзор, выбираем скопированный файл БД_Студенты,
флажок Импортировать данные и ОК, выберем данные с листа Студенты_Iкурс, жмём Далее, соглашаемся, что первая строка содержит заголовки, далее, выберем для поля КодФакультета тип данных Байт:

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

Откроем таблицу Студенты импорт и убедимся что строк 16. Закрываем таблицу и аналогично добавим в неё данные из листа Студенты_IIкурс файла MS Excel:

Снова выполним команду Внешние данные/Импорт/Excel, жмём Обзор выбираем скопированный файл БД_Студенты, но теперь нам нужно импортировать не в новую таблицу, а добавить в таблицу Студенты импорт, значит активируем флажок Добавить копию… и в списке выбрать пункт Студенты импорт, ОК, выберем с какого листа будем брать данные – Студенты_IIкурс, далее, Готово и Закрыть.

Откроем таблицу Студенты импорт и убедимся что строк уже 32. Закрываем таблицу и аналогично добавим в неё данные из листа Студенты_IIIкурс. После добавления таблицы, строк будет уже 48.

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

Выполним команду Внешние данные/Импорт/Excel, жмём Обзор выбираем файл БД_Студенты.xls, флажок Создать связанную таблицу, ОК, выберем любой лист, два раза Далее, Готово и ОК.

Обратим внимание на значок новой таблицы:

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

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

Продолжаем работать с базой данных БД Студенты2. Закроем все таблицы, выполним: Работа с базами данных/Анализ/Анализ таблиц:

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

где поля Город, Страна и Деятельность, вынесены в отдельные таблицы. Все они связаны с таблицей описывающие студента. Что мастер сделал? Он нашёл в исходной таблице повторяющиеся города, вынес их в отдельную таблицу как уникальные данные, и связал её с исходной, т.е. города в отдельной таблице, теперь будут только в единственном экземпляре. Аналогично мастер вынес из таблицы с городами, страны, которые поместил в отдельную таблицу затем связал их.

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

И наоборот, если таки нужна отдельная таблица с деятельностью, тянем поле Деятельность обратно в пустую область диалога:

Нажимаем Далее, если нас не устроят имена таблиц, мы их можем в любое время переименовать. Теперь мы можем добавить ключевые поля, например поле Деятельность можно сделать ключевым: выделим строку Деятельность и нажмём на ключик:

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

Открываем таблицу 1 и видим поля Подстановка Таблица2 и Подстановка Таблица3 являются связанными с таблицами 2 и 3 соответственно.

Закроем файл БД_Студенты_2 командой Файл/Закрыть базу данных.