Имеются данные о клиентах банка.

ЛАБОРАТОРНАЯ РАБОТА № 5.

ТЕМА: СИСТЕМА УПРАВЛЕНИЯ БАЗАМИ ДАННЫХ ACCESS.

Цель: изучить технологии работы в среде системы управления базами данных ACCESS, что позволит профессионально создавать и редактировать базы данных реляционного типа, осуществлять к ним запросы, формировать отчеты.

ЗАНЯТИЕ 1.

СОЗДАНИЕ ОДНОТАБЛИЧНОЙ БАЗЫ ДАННЫХ

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

Access является приложением Windows, а поскольку и Windows и Access разработаны одной фирмой (Microsoft), они очень хорошо взаимодействуют друг с другом. СУБД Access работает под управлением Windows; таким образом, все преимущества Windows доступны в Access, например, можно вырезать, копировать и вставлять данные из любого приложения Windows в приложение Access и наоборот. Access — это реляционная СУБД. Это означает, что с ее помощью можно работать одновременно с несколькими таблицами базы данных. Применение реляционной СУБД помогает упростить структуру данных и таким образом облегчить выполнение работы.

В СУБД Access предусмотрено много дополнительных сервисных возможностей. Мастера помогут создать таблицы, формы или отчеты из имеющихся заготовок, сделав основную черновую работу. Выражения используются в Access, например, для проверки допустимости введенного значения. Макросы позволяют автоматизировать многие процессы без программирования, тогда как встроенный в Access язык VBA (Visual Basic for Applications) — специально разработанный компанией Microsoft диалект языка Basic для использования в приложениях Microsoft Office — дает возможность опытному пользователю программировать сложные процедуры обработки данных. Просматривая свою форму или отчет, можно представить, как они будут выглядеть в распечатанном виде.

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

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

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

В Access существует четыре способа создания пустой таблицы:

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

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

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

• определение всех параметров макета таблицы в режиме конструктора.

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

Ввод данных в ячейки таблицы производится обычным образом — переводите курсор в нужную ячейку, т.е. делаете ее активной, и вводите в нее данные (вводить данные в ячейки для поля с типом данных Счетчик не требуется, так как значения в этом поле появляются автоматически при вводе данных в любую другую ячейку данной записи). Особенности ввода следующие: при нажатии клавиши <Del> ячейка очищается; если ввод данных в ячейку прервать, нажав клавишу <Esc>, то восстанавливается старое значение, а если нажать клавиши <Enter> или <ТаЬ>, то в ячейку заносится новое значение. Можно редактировать текущее значение, меняя в нем отдельные символы. Для этого надо в ячейке таблицы дважды щелкнуть мышью или нажать клавишу <F2>. Однако есть одна важная особенность при вводе данных — для некоторых типов данных (числовой, денежный, дата/время, логический) Access автоматически проверяет правильность их ввода. Например, если введете букву в ячейку с числовым типом, то Access выдаст сообщение о неправильно введенном значении и не позволит перейти к другой ячейке, пока не будут введены правильные данные.

Для всех типов полей (кроме типов Счетчик и поле объекта OLE) можно самостоятельно задавать ограничения для вводимых данных. Для этого в режиме конструктора надо выбрать вкладку Общие, перевести курсор в поле с именем «Условия на значение» и ввести ограничение на данные. Например, стаж работы вряд ли может быть более 70 лет, и ограничение будет следующее: <70. Ограничение можно вводить и на текстовое поле. Обычно в этом случае задаются слова, которые могут присутствовать в данном поле.

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

Можно использовать еще один удобный инструмент при вводе данных — параметр Значение по умолчанию (находится также на вкладке Общие). Здесь можно задать данные, которые Access будет вводить по умолчанию при заполнении таблицы. Это удобно использовать, когда большинство значений данного поля одинаковы и лишь некоторые отличаются. Например, должность большинства преподавателей — доцент. Если ввести это слово в строку параметра Значение по умолчанию, то все ячейки поля «Должность» примут значение Доцент и надо будет лишь изменить значения ячеек для преподавателей с другими должностями. Данные можно вводить и копированием их из одной ячейки в другую стандартными средствами Windows.

Для каждого типа данных (кроме типа данных Счетчик) предусмотрено пустое (нулевое) значение. Различают два типа пустых значений: пустые (Null) значения и пустые строки. Если пустое поле имеет пустое (Null) значение, то это означает, что данные для него существуют, но пока неизвестны. Если же введена пустая строка (два знака прямых кавычек ("")), то это означает, что данные не существуют вовсе. Access позволяет обрабатывать такие пустые значения.

Редактирование данных производится очень просто — курсор переводится в нужную ячейку, старые данные удаляются (клавишами <Del> или <Backspase>) и вводятся новые данные. Удалять можно не только данные в ячейках, но и целиком строки, предварительно их выделив. Но если таблица большая, то редактируемые данные надо сначала найти. Это делается следующим образом: переводят курсор в любую ячейку поля, начиная с которой будет вестись поиск, затем открывают пункт менюПравка и выполняют командуНайти. В появившемся окне вводят образец искомых данных и щелкают по кнопке <Найти>. Если значение найдено, курсор перейдет в эту ячейку. Иногда требуется большое количество одинаковых данных заменить на другое значение. Для этого надо открыть пункт меню Правка и выполнить командуЗаменить. В появившемся окне ввести образцы того, что надо найти и на что заменить. Затем надо решить, будете ли просматривать каждое заменяемое значение или нет. Если будете, то надо щелкнуть по кнопке <3аменить>, а если нет — один раз щелкнуть по кнопке <3аменить все>.

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

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

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

Фильтр по выделенному фрагменту — это способ быстрого отбора записей по выделенному образцу. Например, нужно просмотреть в таблице записи только о доцентах. Выделите слово Доцент в любой из записей. Щелкните по кнопке <Фильтр по выделенному> (или выполните командуЗаписи, Фильтр, Фильтр по выделенному), и Access выберет только те записи, для которых значение в столбце Должность равно Доцент. Обратите внимание, что в строке состояния окна таблицы присутствует слово Фильтр. В дополнение к этому кнопка <Применить фильтр> (третья кнопка фильтрования, на которой изображена воронка) затенена, а это означает, что используется фильтр. При отключении этой кнопки все фильтры будут сняты. Установки фильтра не пропадут: он просто будет отключен. Фильтр по выделенному может собирать вместе критерии выбора при каждом использовании кнопки <Фильтр по выделенному>. Например, поместили курсор в столбец Должность в записи, в которой значение поля равно Доцент, и щелкнули по кнопке <Фильтр по выделенному>. Увидите только записи о всех доцентах. Если затем поместите курсор в столбец <Должность> и выделите слово Информатика (конечно, если такая дисциплина присутствует в вашей таблице), а затем щелкните по кнопке <Фильтр по выделенному> появятся только записи о доцентах, которые преподают информатику. Если хотите просмотреть значения, которые не удовлетворяют этому критерию, например просмотреть всех преподавателей, кроме доцентов, читающих информатику, щелкните правой кнопкой (курсор мыши должен находиться внутри таблицы) и выберите команду Исключить выделенное. Будут выбраны все записи, кроме недавно выбранных (инверсный выбор).

Фильтрование данных в Access производится с помощью кнопок <Фильтр по выделенному> или <Изменить фильтр> (команда менюЗаписи, Фильтр, Изменить фильтр).После нажатия второй кнопки от таблицы остается одна запись. Каждое поле становится полем со списком (когда в нем находится курсор), в котором можно выбрать из списка все значения для данного поля. После щелчка по кнопке <Применить фильтр> будут выбраны записи, соответствующие измененному фильтру. Еще более сложные условия фильтрации можно задать командой менюЗаписи, Фильтр, Расширенный фильтр...

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

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

Создать форму можно несколькими способами. Если в режиме базы данных откроете вкладку Формы и щелкните по кнопке <Создать>, то откроется окно, в котором указаны способы создания формы.

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

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

Поля в форме в один столбец упорядочены и представлены в виде столбцов. Такая форма может занимать одну или несколько страниц. Поля можно упорядочить как угодно. Access дает возможность использовать большинство стандартных элементов управления Windows, которые создают привычный интерфейс при вводе данных. Линии, рамки, цвета и даже специальные эффекты (например, тени) позволяют создать удобные формы с привлекательным внешним видом. Ленточная форма и табличная форма похожи друг на друга, но табличная форма фактически повторяет вид уже знакомой таблицы и в ней ограничены возможности редактирования. А в ленточной форме доступны все виды инструментов и способы оформления фона и ячеек с данными. Выровненный вид формы похож на ленточную форму, но отличается тем, что на экран выводятся все поля с одной записью, и если полей много, то они располагаются рядами — один под другим.

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

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

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

ЗАДАНИЕ 1 – СОЗДАНИЕ БАЗЫ ДАННЫХ

1. Для создания новой базы данных:

• загрузите Access, в появившемся окне выберите пунктНовая база данных;

• в окне «Файл новой базы данных» задайте имя базы (пунктИмя файла)и выберите папку (пунктПапка), где база данных будет находиться. По умолчанию Access предлагает имя базы dbl, а тип файла — Базы данных Access. Имя задайте Преподаватели, а тип файла оставьте прежним, так как другие типы файлов нужны в специальных случаях;

• щелкните по кнопке <Создать>.

Таблица 1. Таблица данных Преподаватели

ЗАНЯТИЕ 2.

ФОРМИРОВАНИЕ ЗАПРОСОВ И ОТЧЕТОВ ДЛЯ ОДНОТАБЛИЧНОЙ БАЗЫ ДАННЫХ

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

Запросы можно создавать самостоятельно и с помощью мастеров. Мастера запросов автоматически выполняют основные действия в зависимости от ответов пользователя на поставленные вопросы. Самостоятельно разработать запросы можно в режимеконструктора.

В Access можно создавать следующие типы запросов:

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

• запрос с параметрами;

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

• запрос на изменение (запрос на удаление, обновление и добавление записей на создание таблицы);

• запросы SQL (запросы на объединение, запросы, к серверу, управляющие запросы, подчиненные запросы)

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

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

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

Для создания нового запроса надо в окне базы данных выбрать вкладку Запросы и щелкнуть по кнопке <Создать>. Откроется окно «Новый запрос». В окне надо выбрать один из пяти пунктов: Конструктор, Простой запрос, Перекрестный запрос, Повторяющиеся записи, Записи без подчиненных. Конструктор позволит пользователю самостоятельно создать любой тип запроса, но этот режим рекомендуется пользователям, уже имеющим некоторый опыт создания запросов. Простой запрос позволит создать с помощью Мастера запрос на выборку из определенных полей таблиц или других запросов. (Это наилучший способ создания запроса для начинающих пользователей.) Что такое перекрестный запрос, рассматривалось выше. При выборе пункта Повторяющиеся записи будет создан запрос на поиск повторяющихся записей в простой таблице или в запросе, а при выборе пункта Записи без подчиненных — запрос на поиск записей, которым не соответствует ни одна запись в подчиненной таблице. Такой запрос используется для многотабличных баз данных.

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

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

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

• на физическом носителе информации (обычно это жесткий диск) требуется меньший объем пространства;

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

При каждом выполнении запрос обращается к базовым таблицам и снова создает результирующий набор данных. Поскольку сам по себе результирующий набор данных не сохраняется, запрос автоматически отображает любые изменения, происшедшие в базовых таблицах с момента последнего запуска этого запроса (даже в реальном времени в многопользовательской среде). Для сохранения запроса следует выполнить следующие действия. Выполните командуФайл, Сохранить или щелкните по кнопке <Сохранить> на панели инструментов. Если впервые сохраняете запрос, введите новое имя запроса в диалоговом окне «Сохранение».

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

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

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

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

Для создания отчета надо открыть вкладку Отчеты и щелкнуть по кнопке <Создать>. Откроется окно «Новый отчет», в котором приведены шесть пунктов меню, т.е. шесть способов создания отчета: Конструктор, Мастер отчетов, Автоотчет в столбец, Автоотчет ленточный, Мастер диаграмм и Почтовые наклейки. Конструктор позволит пользователю самостоятельно создать отчет, но это непросто даже для опытного пользователя. Мастер отчетов автоматически создаст отчет на основе выбранных полей таблиц (запросов) и макетов отчетов. Этот способ создания отчетов является наиболее удобным как для начинающих, так и для опытных пользователей. Автоотчет в столбец и Автоотчет ленточный — самые простейшие способы создания отчетов: достаточно указать только имя таблицы (запроса), на основе которого будет создан отчет, а остальное сделает мастер отчетов. Мастер диаграмм поможет создать отчет в виде диаграммы, а Почтовые наклейки создадут отчет, отформатированный для печати почтовых наклеек.

ЗАДАНИЕ 1 – ФОРМИРОВАНИЕ ЗАПРОСОВ НА ВЫБОРКУ

1. Для создания простого запроса:

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

• в открывшемся окне щелкните по кнопке <Создать>;

• из появившихся пунктов окна «Новый запрос» выберитеПростой запрос и щелкните по кнопке <ОК>;

• в появившемся окне в строке Таблицы/запросы выберите таблицу Преподаватели (если других таблиц или запросов не было создано, она будет одна в открывающемся списке);

• в окне «Доступные поля» переведите выделение на параметр Фамилия;

• щелкните по кнопке  . Слово Фамилия перейдет в окно «Выбранные поля»;

• аналогично в окно «Выбранные поля» переведите поля«Имя», «Отчество», «Должность» (порядок важен — в таком порядке данные и будут выводиться);

• щелкните по кнопке <Далее>;

• в строке параметра Задайте имя запроса введите новое имя Должности преподавателей;

• щелкните по кнопке <Готово>. На экране появится таблица с результатами запроса.

2. Для сортировки данных:

• щелкните в любой строке поля «Должность»;

• отсортируйте данные по убыванию. Для этого щелкните по кнопке  на панели инструментов или выполните командуЗаписи, Сортировка, Сортировка по убыванию.

3. Для сохранения запроса:

• щелкните по кнопке  или выполните командуФайл, Сохранить;

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

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

• создайте запрос на выборку для следующих полей таблицы Преподаватели'. «Фамилия», «Имя», «Отчество», «Преподаваемая дисциплина». Запрос создавайте аналогично тому, как это делалось в п. 1;

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

• щелкните по кнопке <Готово>. На экране появится таблица с результатами запроса;

• перейдите в режиме конструктора, щелкнув по кнопке  или выполнив командуВид, Конструктор;

• в строке параметра Условия отбора для поля «Фамилия» введите фразу (скобки тоже вводить): [Введите фамилию преподавателя];

• выполните запрос, щелкнув по кнопке  на панели инструментов или выполнив командуЗапрос, Запуск;

Вышеописанным способом запрос выполняется только в режиме конструктора. Для того чтобы выполнить запросиз другого режима, надо открыть вкладку Запросы, выделить требуемый запрос и щелкнуть по кнопке <0ткрыть>.

• в появившемся окне введите фамилию Гришин и щелкните по кнопке <ОК>. На экране появится таблица с данными о преподавателе Гришине — его имя, отчество и преподаваемая им дисциплина;

• сохраните запрос;

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

ЗАДАНИЕ 2 – НА ОСНОВЕ ТАБЛИЦЫ ПРЕПОДАВАТЕЛИ СОЗДАЙТЕ ОТЧЕТ С ГРУППИРОВАНИЕМ ДАННЫХ ПО ДОЛЖНОСТЯМ

Для создания отчета:

• откройте вкладку Отчеты и щелкните по кнопке <Создать>;

• в открывшемся окне выберите пунктМастер отчетов;

• щелкните по значку раскрывающегося списка в нижней части окна;

• выберите из появившегося списка таблицу Преподаватели;

• щелкните по кнопке <ОК>. В появившемся окне выберите поля, которые будут присутствовать в форме. В данном примере присутствовать будут все поля из таблицы, поэтому щелкните по кнопке ;

• щелкните по кнопке <Далее>;

• в появившемся окне присутствует перечень полей. Переведите выделение на поле «Должность»;

• щелкните по кнопке  . Таким образом, задаете группировку данных по должности;

• щелкните по кнопке <Далее>;

• параметры появившегося окна оставим без изменений, поэтому щелкните по кнопке <Далее>;

• в появившемся окне выберите стиль оформления отчета;

• щелкните по кнопке <Далее>;

• в появившемся окне введите название отчета Преподаватели;

• щелкните по кнопке <Готово>. На экране появится сформированный отчет;

• просмотрите, а затем закройте отчет.

ЗАНЯТИЕ 3.

РАЗРАБОТКА ИНФОРМАЦИОННО-ЛОГИЧЕСКОЙ МОДЕЛИ И СОЗДАНИЕ СТРУКТУРЫ РЕЛЯЦИОННОЙ БАЗЫ ДАННЫХ

Слово "реляционная" происходит от английского relation — отношение. Отношение — математическое понятие, но в терминологии моделей данных отношения удобно изображать в виде таблицы. При этом строки таблицы соответствуют кортежам отношения, а столбцы — атрибутам. Ключом называют любую функцию от атрибутов кортежа, которая может быть использована для идентификации кортежа. Такая функция может быть значением одного из атрибутов (простой ключ), задаваться алгебраическим выражением, включающим значения нескольких атрибутов (составной ключ). Это означает, что данные в строках каждого из столбцов составного ключа могут повторяться, но комбинация данных каждой строки этих столбцов является уникальной. Например, в таблице Студенты есть столбцы Фамилия и Год рождения. В каждом из столбцов есть некоторые повторяющиеся данные, т.е. одинаковые фамилии и одинаковые года рождения. Но если студенты, имеющие одинаковые фамилии, имеют разные года рождения, то эти столбцы можно использовать в качестве составного ключа. Как правило, ключ является уникальным, т.е. каждый кортеж определяется значением ключа однозначно, но иногда используют и неуникальные ключи (ключи с повторениями). В локализованной (русифицированной) версии Access вводится термин ключевое поле, которое можно трактовать как первичный ключ. В Access можно выделить три типа ключевых полей: простой ключ, составной ключ и внешний ключ.

Одно из важнейших достоинств реляционных баз данных состоит в том, что можно хранить логически сгруппированные данные в разных таблицах и задавать связи между ними, объединяя их в единую базу. Для задания связи, таблицы должны иметь поля с одинаковыми именами или хотя бы с одинаковыми форматами данных. Связь между таблицами устанавливает отношения между совпадающими значениями в этих полях. Такая организация данных позволяет уменьшить избыточность хранимых данных, упрощает их ввод и организацию запросов и отчетов. Поясним это на примере. Допустим, в базе надо хранить данные о студентах (фамилия, изучаемая дисциплина) и преподавателях (фамилия, номер кафедры, ученая степень, преподаваемая дисциплина). Если хранить данные в одной таблице, то в строке с фамилией студента, изучающего конкретную дисциплину, будут храниться все атрибуты преподавателя, читающего эту дисциплину. Это же огромная избыточность данных. А если хранить данные о студенте в одной таблице, о преподавателе — в другой и установить связь между полями «Читаемая дисциплина» — «Изучаемая дисциплина» (фактически это одинаковые поля), то избыточность хранимых данных многократно уменьшится без ущерба для логической организации информации.

В Access можно задать три вида связей между таблицами: Один-ко-многим, Многие-ко-многим и Один-к-одному.

Связь Один-ко-многим — наиболее часто используемый тип связи между таблицами. В такой связи каждой записи в таблице А может соответствовать несколько записей в таблице В (поля с этими записями называют внешними ключами), а запись в таблице В не может иметь более одной соответствующей ей записи в таблице А.

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

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

Тип создаваемой связи зависит от полей, для которых определяется связь:

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

• связь Один-к-одному создается в том случае, когда оба связываемых поля являются ключевыми или имеют уникальные индексы;

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

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

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

• связанные поля имеют один тип данных. Здесь существует исключение. Поле счетчика может быть связано с числовым полем, если оно имеет тип Длинное целое;

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

ЗАДАНИЕ 1 – СОЗДАНИЕ ИНФОРМАЦИОННО-ЛОГИЧЕСКОЙ И ЛОГИЧЕСКОЙ МОДЕЛЕЙ БАЗЫ ДАННЫХ

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

Рассмотрим связь между объектами Студенты и Дисциплины. Студент изучает несколько дисциплин, что соответствует многозначной связи и отражено на рис.1 двойной стрелкой. Понятно, что каждая дисциплина изучается множеством студентов. Это тоже многозначная связь, обозначаемая двойной стрелкой (связь "один" обозначена одинарной стрелкой). Таким образом, связь между объектами Студенты и Дисциплины — Многие-ко-многим (М : N).

Рис. 1. Типы связей между объектами Студенты, Дисциплины и Преподаватели

Множественные связи усложняют управление базой данных, например в СУБД Access при множественных связях нельзя использовать механизм каскадного обновления. Поэтому использовать такие связи нежелательно и нужно строить реляционную модель, не содержащую связей типа Многие-ко-многим. В Access для контроля целостности данных с возможностью каскадного обновления и удаления данных необходимо создать вспомогательный объект связи, который состоит из ключевых реквизитов связываемых объектов и который может быть дополнен описательными реквизитами. Таким новым объектом для связи служит объект Оценки, реквизитами которого являются код студента, код дисциплины и оценки. Каждый студент имеет оценки по нескольким дисциплинам, поэтому связь между объектами Студенты и Оценки будет Один-ко-многим (1 : М). Каждую дисциплину сдает множество студентов, поэтому связь между объектами Дисциплины и Оценки также будет Один-ко-многим (1 : М). В результате получаем информационно-логическую модель базы данных, приведенную на рис.2.

Рис. 2. Информационно-логическая модель реляционной базы данных

2. В реляционной базе данных в качестве объектов рассматриваются отношения, которые можно представить в виде таблиц. Таблицы между собой связываются посредством общих полей, т.е. одинаковых по форматам и, как правило, по названию, имеющихся в обеих таблицах. Рассмотрим, какие общие поля надо ввести в таблицы для обеспечения связности данных. В таблицах Студенты и Оценки таким полем будет «Код студента», в таблицах Дисциплины и Оценки — «Код дисциплины», в таблицах Преподаватели и Дисциплины — «Код дисциплины». Выбор цифровых кодов вместо фамилий или названий дисциплин обусловлен меньшим объемом информации в таких полях: например, число "2" по количеству символов значительно меньше слова "математика". В соответствии с этим логическая модель базы данных представлена на рис.3, где жирными буквами выделены ключевые поля.

Рис. 3. Логическая модель базы данных

ЗАДАНИЕ 2 – СОЗДАНИЕ РЕЛЯЦИОННОЙ БАЗЫ ДАННЫХ

1. Создайте базу данных Деканат, выполнив следующие действия:

• загрузите Access, в появившемся окне выберите пункт Новая база данных, затем щелкните по кнопке <ОК>;

• в окне <Файл новой базы данных> задайте имя (пункт Имя файла) и выберите папку (пункт Папка), где база будет находиться. По умолчанию Access предлагает имя базы dbl, а тип файла — Базы данных Access. Имя задайте Деканат, а тип файла оставьте прежним, так как другие типы файлов нужны в специальных случаях;

• щелкните по кнопке <Создать>.

2. Создайте структуру таблицы Студенты. Для этого:

• в окне базы данных выберите вкладку Таблицы, а затем щелкните по кнопке <Создать>;

• в окне «Новая таблица» выберите пункт Конструктор и щелкните по кнопке <ОК>. В результате проделанных операций открывается окно таблицы в режиме конструктора, в котором следует определить поля таблицы;

• определите поля таблицы в соответствии с табл. 3;

• в качестве ключевого поля задайте «Код студента». Для этого щелкните по полю «Код студента» и по кнопке  на панели инструментов или выполните командуПравка, Ключевое поле;

• закройте таблицу, задав ей имя Студенты.

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

Таблица 3