Практическое занятие № 21. Формирование запросов и Отчёт ов для однотабличной базы данных.

1. Цель занятия:

- познакомиться с основными видами запросов, научиться создавать запросы различными способами.

2. Перечень необходимых средств обучения (оборудование, материалы)

– технические средства обучения: персональные компьютеры, локальная сеть, коммутатор для подключения в сети Internet.

Используемое программное обеспечение:

- Microsoft Windows XP /7

- MS Access 2003

- Антивирус Касперского 6.0

3. Основные теоретические положения

Формирование запросов на выборку.

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

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

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

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

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

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

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

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

ü подчиненные запросы)

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

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

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

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

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

Формирование Отчёт ов.

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

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

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

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

4. Содержание заданий

4.1 Задание1.

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

4.1.2 Данные запроса отсортируйте по должностям.

4.1.3 Сохраните запрос

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

5. Рекомендации по выполнению заданий

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Задание2.

На основе таблицы Преподаватели создайте Отчёт с группированием данных по должностям.

Технология работы.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

- просмотрите, а затем закройте Отчёт .

6. Содержание отчёта:

Отчёт принимается в электронном виде. Содержит все запросы, приведённые в задании, а также отчёт, созданный на основе таблицы БД.

7. Контрольные вопросы.

7.1. Какие виды запросов вы знаете?

7.2. Как создать простой запрос?

7.3. Как создать запрос на выборку с параметром?

7.4. Как создать Отчёт ?

8. Список рекомендуемой литературы

8.1 Гвоздева В. А. Информатика, автоматизированные информационные технологии и системы: учебник / В. А. Гвоздева. - М. : ИД "Форум": Инфра-М, 2012. - 544 с.

8.2 Угринович Н. Д. Информатика и ИКТ:10,11 кл.- М.:БИНОМ. Лаборатория знаний, 2008. - 512с.

8.3 Немцова Т. И. Базовая компьютерная подготовка. Операционная система, офисные приложения, Интернет. Практикум по информатике : учеб. пособие / Т. И.

8.4 Гвоздева В. А. Информатика, автоматизированные информационные технологии и системы: учебник / В. А. Гвоздева. - М. : ИД "Форум": Инфра-М, 2012. - 544 с.

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

1. Цель занятия:

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

2. Перечень необходимых средств обучения (оборудование, материалы)

– технические средства обучения: персональные компьютеры, локальная сеть, коммутатор для подключения в сети Internet, макет системного блока, мультимедиа-проектор, принтер, сканер.

Используемое программное обеспечение:

- Microsoft Windows XP /7

- MS Access 2003

- Антивирус Касперского 6.0

3. Основные теоретические положения.

Организация данных.

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

В Access можно выделить три типа ключевых полей: простой ключ, составной ключ и внешний ключ.

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

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

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

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

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

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

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

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

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

Целостность данных

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

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

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

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

4. Содержание заданий

Задание 1. Создание инфологической и логической моделей базы данных.

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

4.2. Разработайте логическую модель реляционной базы данных

Технология работы:

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

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

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

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

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

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

Студенты		Оценки		Дисциплины		Преподаватели
Код студента		Код студента		Код дисциплины		Код дисциплины
Фамилия		Код дисциплины	Название дисциплины		Код преподавателя
Имя		Оценки				Фамилия
Отчество						Имя
Номер группы						Отчество
Дата рождения						Дата рождения
Стипендия						Преподаваемая дисциплина
						Телефон

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