Работа с базами данных в Microsoft Access

Практическое занятие 2.15 РАБОТА С БАЗАМИ ДАННЫХ В MICROSOFT ACCESS

1. Основные понятия и терминология баз данных.

2. Особенности MS Access как базы данных реляционного типа.

3. Основы работы в конкретной СУБД.

1. Основные понятия и терминология баз данных

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

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

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

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

Модели представления данных

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

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

Сетевая модель (смотрите рис. 2) является развитием иерархической. Модель дает возможность описать более сложные виды взаимоотношений между данными. В такой структуре любой объект может быть связан с любым другим объектом.

Рис. 1. Иерархическая модель Рис. 2. Сетевая модель

Реляционную модель данных предложил в 1970 году сотрудник фирмы IBM Эдгар Кодд. В статье «Реляционная модель данных для больших совместно используемых банков данных» он показал, что любое представление данных можно свести к совокупности особых двумерных таблиц, известных в математике как отношение (англ. relation - отношение). Каждая строка такой таблицы хранит информацию об одном конкретном объекте, а в столбцах содержатся его свойства.

Реляционные базы данных

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

Основной информационной единицей в реляционной базе данных является таблица, в которой хранятся сведения об однотипных объектах информационной системы. Строки таблицы называются записями, а столбцы – полями. Каждая запись хранит информацию о некотором объекте, а в полях размещаются, описывающие объект, свойства.

Реляционная таблица обладает следующими свойствами:

• любая таблица имеет уникальное имя и состоит из однотипных строк;

• записи имеют фиксированное число полей и значений (не допускается использование повторяющихся групп);

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

• полям присваиваются уникальные имена и в каждом поле хранятся элементы, имеющие один и тот же тип данных;

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

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

Рис. 3. Пример реляционной таблицы

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

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

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

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

Система управления базами данных

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

Система управления базами данных (СУБД) – комплекс программных и языковых средств, необходимых для создания, администрирования и использования баз данных.

Основные операции, выполняемые СУБД, по управлению данными:

• поиск информации в базе данных;

• манипулирование данными;

• выполнение несложных расчётов;

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

• восстановление данных после сбоев.

Как правило, взаимодействие пользователя с СУБД происходит через прикладную программу.

Виды СУБД:

• локальные СУБД;

• серверные СУБД;

• файл-серверные СУБД;

• клиент-серверные СУБД.

2. Особенности MS Access как базы данных реляционного типа

Интерфейс СУБД Access, ее основные элементы

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

В окне База данных расположены ее объекты: Таблицы, Запросы, Формы, Отчеты и др. Все эти объекты, если они создавались, хранятся в общем файле базы данных на диске с расширением .mdb (.accdb). При работе с каждым из объектов они имеют свое окно.

Рассмотрим кратко назначение каждого объекта.

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

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

Форма отображает данные из таблиц или запросов. С помощью форм удобно вводить данные в таблицы.

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

Создание базы данных и заполнение таблиц

Для создания новой базы данных выполните команды Пуск - Ms Office - MS Access. В правой части раскрывшегося окна в поле Имя файла вместо стандартного имени Database задайте имя новой базы данных. Справа от поля Имя файла раскройте папку и задайте путь для сохранения базы данных. Нажмите кнопку Создать.

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

Рис. 4. Схема данных

Наименование поля используется для ссылки на данные таблицы. Тип данных определяет характер хранимых в поле данных. В MS Access допустимыми являются данные следующих типов:

• текстовый;

• числовой;

• денежный;

• счетчик;

• даты/времени;

• логический;

• поле МЕМО;

• поле объекта OLE;

• гиперссылка;

• мастер подстановок.

Текстовые поля могут содержать буквы, цифры и специальные символы. Максимальная ширина поля составляет 255 символов. По умолчанию MS Access присваивает всем создаваемым полям с текстовым типом данных ширину поля, равную 50 символов.

Числовые поля используются при выполнении математических операций. Достаточно задать размер поля 4 знака. При этом в числовом поле можно хранить числа от -3.4 * 10 ³⁸ до 3.4 * 10³⁸ с дробной частью до 7 знаков.

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

Денежный. В поля этого типа можно вводить только денежные величины. Обозначение денежной единицы вводится автоматически. По умолчанию в Access используется знак денежной единицы (¥, £, $ и т. п.), указанный в региональных параметрах Windows.

В поле дата/время может содержаться любая дата и время от 1 января 100 г. до 31 декабря 9999 г . MS Access позволяет выводить и обрабатывать даты в нескольких форматах. Разработчик базы данных может задать для поля формат отображения. В этом случае (если маска ввода не задана) можно вводить данные почти в любом формате, но Access будет отображать данные в заданном формате. Например, можно ввести значение 11.10.2009, но оно будет отображаться как 11-окт-2009, если задан такой формат отображения.

Создание таблиц базы данных Школа начнем с таблицы Ученики. Выполним команды Создание | Конструктор таблиц. В столбце Имя поля зададим в каждой строке имена полей таблицы: Код ученика, Фамилия, Имя, Дата рождения, Класс. При этом по умолчанию в столбце Тип данных будет задан текстовый тип данных.

Для изменения типа данных щелкните ЛКМ на соответствующем поле, раскройте список типов данных и выберите необходимый тип из списка. В нижней части окна в свойствах поля задайте размер поля для текстовых полей (не более 25 символов) и формат для полей числовых и дата/время.

Задайте ключевое поле для связи с другими таблицами. Для этого щелкните ПКМ на поле Код ученика и в раскрывшемся окне выберите элемент Ключевое поле.

Редактирование таблицы. Если вы описали всю структуру таблицы, не сделав при этом ни одной ошибки, вам необходимо сохранить созданную таблицу. Однако вполне вероятно, что при задании структуры вы могли допустить ошибки. MS Access представляет средства для исправления ошибок. К их числу относятся:

• изменения наименования поля и/или его типа;

• вставка пропущенного поля;

• удаление ошибочно введенного поля;

• изменения порядка следования полей в таблице.

Изменение наименования поля и/или его типа. Предположим, что при задании имени или его типа была допущена ошибка. При этом ошибка была обнаружена только после окончания ввода поля. Установите указатель на наименование поля, которое требуется изменить. Для удаления неправильных символов в имени поля, используйте клавишу Backspace и Delete. После этого введите правильное имя поля.

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

а) изменить размер поля с 50 символов, заданных по умолчанию, до 20;

б) задать краткий формат поля Дата/время в виде ДД. ММ.ГГ.

Для этого откройте таблицу в режиме Конструктора (на вкладке Таблицы выберите нужную таблицу и нажмите кнопку Конструктор).

Подведите указатель к полю и нажмите левую кнопку мыши. Подведите указатель к цифре 50 в строке Размер поля в нижней половине окна и замените эту цифру на 20.

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

Добавление нового поля. Если вы забыли создать поле, выполните команду Вставить строки. Новая строка будет вставлена над текущей строкой, то есть той строкой, в которой вы находитесь в настоящий момент (она отмечена стрелкой в области выбора поля). Введите в этой строке имя поля и тип данных.

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

Аналогичные действия выполните для создания остальных таблиц базы данных Школа.

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

При связывании таблиц базы данных различают три типа связей:

• «один – к – одному;

• «один - ко – многим»;

• «многие - ко - многим».

В многотабличной базе данных связывание осуществляется на вкладке Работа с базами данных – Схема данных.

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

Чтобы создать связь «один – к - одному», надо при создании связываемой таблицы вторичный ключ сделать индексированным полем, при этом выбрать параметр: значения поля не должны повторяться.

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

3. Основы работы с конкретной СУБД

Рассмотрим, каким образом осуществляется выбор данных на примере базы данных «Школа».

Выбрать данные можно с помощью:

• упорядочивания данных (сортировки);

• фильтрации данных;

• запросов.

Сортировка и фильтрация выполняется аналогично подобным операциям в MS Excel.

Виды запросов

В СУБД ACCESS можно создать запросы следующих видов:

• запрос на выборку данных;

• запрос на создание таблицы;

• запрос на добавление данных в таблицу по условию;

• запрос на удаление данных из таблицы по условию;

• запрос на обновление данных в таблице;

• перекрестный запрос.

Создание запросов

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

Перейдем на вкладку Создание запросов – Конструктор запросов – Создать. В бланк запросов добавим необходимые таблицы. В нижней части бланка укажем условия отбора по правилу:

• условия, соединенные логической операцией «И» располагаем в одной строке;

• условия, соединенные логической операцией «ИЛИ» располагаем в разных строках.

Рис. 5. Бланк запросов

Например, необходимо выбрать учащихся, обучающихся в 11Б классе, но только тех, которым 1 января 2019 года уже исполнится 17 лет.

Условия:

Таблица 1.

Таблица «Ученики»

Класс	Дата рождения
11Б	01.01.2002

Использование вычислительных полей при создании запросов

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

В MS Access для группировки записей сортировать таблицу не нужно. Группировка и вычисление итоговых операций осуществляются с помощью конструктора запросов. В окне конструктора необходимо кликнуть ПКМ в поле, по значению которого будут выделяться группы, и выбрать в контекстном меню команду Итоги. После этого в бланк запросов будет добавлено поле Групповая операция (рис. 6).

Рис. 6. Группировка по полю Класс и вычисление даты рождения самого младшего ученика в классе

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

1. В чем заключается смысл понятия «реляционная база данных»?

2. Какие основные объекты используются в СУБД Access?

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

4. Какие виды запросов используются в СУБД Access?

Вопросы для самостоятельного изучения

1. Создание форм в СУБД Access в режиме конструктора.

1. Слияние и разделение баз данных в СУБД Access.

2. Импорт данных из внешних источников в СУБД Access.

Основная литература

1. Информатика. Учебник для 9 класса. И. Г. Семакин, Л. А. Залогова, С. В. Русаков, Л. В. Шестакова. – М.: БИНОМ. Лаборатория знаний, 2014. – 125 с.

2. Информатика. Углубленный уровень: учебник для 11 класса: в 2 ч. Ч.1/ И.Г. Семакин, Т.Ю. Шеина, Л.В. Шестакова. – М.: БИНОМ. Лаборатория знаний, 2014. – 184 с.

3. Информатика. Углубленный уровень: учебник для 11 класса: в 2 ч. Ч.2/ И.Г. Семакин, Т.Ю. Шеина, Л.В. Шестакова. – М.: БИНОМ. Лаборатория знаний, 2014. – 232 с.

Дополнительная литература

1. Базы даных: Учеб. для вузов / Под ред. А. Д. Хомоненко. СПб.: КОРОНА принт, 2010. – 416 с.

1. Информатика: Базовый курс / под редакцией С. В. Симоновича – СПб.: Питер, 2003. – 640 с.: ил. – Режим доступа:

http://www.ipa.nw.ru/PAGE/aspirantura/literatura/bukvar2.pdf

1. Лабораторный практикум по информатике: Учебное пособие для вузов/ Под ред. В.А. Острейковского. – М.: Высшая школа, 2003. – 376 с.: ил. – Режим доступа: http://msk.edu.ua/ivk/OKM/Uchebniki/ostreykovskiy_v_a_laboratornyy

_praktikum_po_informatike.pdf.

1. Проектирование баз данных. СУБД Microsoft Access: Учебное пособие для вузов / Н. Н. Гринченко, Е. В. Гусев, Н. П. Макаров, А. Н. Пылькин, Н. И. Цуканова. - М.: Горячая линия-Телеком, 2004. – 240с.: ил.

2. Хомоненко А. Д., Цыганков В. М., Мальцев М. Г. Базы данных: Учеб. для вузов. 2-е изд. Спб.: КОРОНА принт, – 2002. – 665 с.

Электронные образовательные ресурсы

1. http://fipi.ru/ – сайт Федерального института педагогических измерений.

1. http://inf.reshuege.ru; http://infoegehelp.ru – разбор заданий, тренировочные online-тесты.

2. http://chebgym5.ru/uaccess/p1aa1.html – сайт «Технология обработки информации в системе управления базами данных»

3. http://informatics-lesson.ru/bd/index.php – сайт дистанционной подготовки по информатике «Урок информатики».

4. http://lbz.ru/metodist/authors/informatika/2/ – ЭУМК автора учебников по информатике Семакина И.Г.

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

1. Создайте таблицы: Ученики, Учителя, Преподавание, Классы, Предметы.

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

2. Заполните таблицы данными.

3. Создайте запросы:

• Отобразить всех учителей, преподающих в 11А классе.

• Отобразить всех мальчиков старше 16 лет.

• Добавить поле успеваемость в таблицу Ученики.

• Вычислите среднюю успеваемость по каждому классу.

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

9