Аблязов В.И., Редько С.Г. Проектирование баз данных Microsoft Office Access 2003

Подождите немного. Документ загружается.

Внешний вид правильно определенной схемы данных приведен на ри-

сунке.

Содержание Лабораторной работы № 2

1. Создайте новую базу данных и в ней необходимые таблицы с соответст-

вующими полями, согласно предметной области.

2. Определите типы данных (счетчик, текстовый, числовой и т.д.), описа-

ние и другие необходимые свойства полей (размер поля, маску ввода,

значение по умолчанию и т.д.) созданных таблиц.

3. Определите первичные ключи в созданных таблицах.

4. Определите необходимые связи между таблицами и вид объединения.

5. В схеме данных проверьте правильность созданных таблиц и связей ме-

жду ними.

6. Заполните созданные таблицы данными (необходимо наличие в табли-

цах всех основных типов данных). При заполнении таблиц использовать

режимы импорта таблиц из других приложений, например, MS Excel.

7. Сохраните изменения в созданной базе данных, чтобы с ней в дальней-

шем можно было работать.

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

1. Какие есть способы создания и заполнения таблиц?

2. В чем отличие режимов Мастера таблиц и Конструктора?

3. Обязательно ли указывать тип и формат данных в создаваемых полях

таблицы?

4. Может ли Имя таблиц и полей начинаться с точки (.), восклицатель-

ного знака (!), пробела, строчной буквы, знака (#) или квадратных

скобок ([ ]).

5. Обязательно ли присутствие ключевого поля в каждой из создавае-

мых таблиц?

6. Могут ли в таблице присутствовать несколько ключевых полей?

7. Все ли таблицы должны охватываться связями в таблице данных?

3. ФОРМИРОВАНИЕ ЗАПРОСОВ

3.1. Особенности запросов и фильтров

Таблица Access сама не может инициировать процесс выдачи данных

пользователю. То есть пользователь сам должен указать, что необходимо по-

лучить из базы данных. Отбор (выборка) данных, хранящихся в таблицах,

осуществляется при помощи Запросов.

Запрос это требование на отбор (выборку) данных, хранящихся в таб-

лицах, или требование на выполнение определенных действий с данными. За-

прос на выборку содержит условия отбора данных и возвращает (отображает,

формирует) выборку, соответствующую указанным условиям, без изменения

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

находящихся в одной или в разных таблицах.

Простейший запрос по отбору записей по каким-то условиям из одной

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

применяются при работе в режиме Таблицы или Формы для просмотра или

изменения подмножества записей. Фильтр не является объектом БД, посколь-

ку он отображает данные только на экране.

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

том, что и в тех, и в других производится извлечение подмножества запи-

сей из базовой таблицы или запроса в виде таблицы.

Однако фильтры, в отличие от запросов, не позволяют выполнять

следующие действия:

1) объединять таблицы, т.е. отображать в одной строке данные из не-

скольких таблиц;

2) указывать поля, которые должны отображаться в результирующем

наборе записей, т.е. они отображают все поля базовой таблицы;

3) производить вычисления (суммы, средних значений и др.).

4) сохранять окно базы данных фильтра как отдельный объект , хотя и

могут его сохранить в виде запроса;

3.2. Работа с фильтрами

Таблица должна быть открыта. Команда меню Записи  Фильтр ис-

пользуется для создания фильтров.

Создать Простой фильтр

 по выделенному значению поля;

 по выделенным значениям нескольких полей (последовательно);

 выполнить сортировку (по возрастанию, по убыванию);

 изменить фильтр;

 удалить фильтр.

Создать Расширенный фильтр

 задать поля, их значения и порядки сортировки при фильтрации;

 задать условия отбора в нескольких полях;

 задать условия отбора в нескольких строках одного поля (все строки

объединяются по условию «ИЛИ»);

 на основе записей, отобранных с помощью фильтра, создать запрос.

Сохранить фильтр в виде запроса

 Для этого нажать кнопку «Сохранить как запрос» на панели инстру-

ментов при открытом окне Фильтр.

3.3. Типы запросов

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

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

позволяют осуществлять:

 группировку записей;

 вычисления сумм (Sum), средних значений (Avg), подсчета записей

(Count) и др.;

 поиск записей, которым не соответствует ни одна запись в подчинен-

ной таблице;

 поиск повторяющихся записей.

 Перекрестные запросы - для расчетов и представления данных в

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

сумму, среднее или другие расчеты, после чего результаты группируют-

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

заголовки столбцов, а другой заголовки строк.

 Запросы на изменение - для создания таблиц, удаления, обновления и

добавления записей.

 Запросы SQL (это запрос, создаваемый при помощи языка инструкций

SQL (Structured Query Language), например, команд SELECT,

UPDATE или DELETE и предложений WHERE или ORDER BY. Когда

пользователь создает запрос в режиме конструктора, Microsoft Access

автоматически создает эквивалентную инструкцию SQL).

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

3.4.1 Простой Запрос на выборку с помощью Мастера запросов

1) На панели объектов выбрать Запросы.

2) Выбрать: Создание запроса с помощью мастера или нажать на

кнопку «Создать» и в диалоговом окне Новый запрос выбрать Простой за-

прос.

3) В окне Создание простых запросов в поле со списком Таблицы и

запросы выбрать одну или несколько таблиц или запросов, которые будут

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

4) С помощью стрелок вправо и влево переместить из списка Доступ-

ные поля в список Выбранные поля те поля, которые необходимы в конст-

руируемом запросе. При этом порядок полей в запросе будет соответствовать

порядку полей в списке Выбранные поля. Если нужно включить в запрос все

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

6) Выбрать «Подробный» или «Итоговый».

Для Итогового запроса указать способ подсчета итогов (Avg - среднее,

Sum - сумма, Max - максимальный, Min – минимальный, Count - количест-

во).

Итоговый запрос фактически является вариантом Группового (стати-

стического) запроса.

Чтобы посмотреть результаты запроса, достаточно открыть его в режиме

Таблицы. Для этого необходимо:

 В окне базы данных на панели объектов выбрать Запросы.

 Выделить нужный запрос в списке запросов и нажать на кнопку «От-

крыть» или дважды щелкнуть левой кнопкой мыши на нужном запросе.

После этого на экране появляется таблица, которая содержит только те

записи, которые удовлетворяют критериям отбора, указанным в запросе, и те

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

тировки записей, они выводятся на экран в соответствующем порядке.

Внешне отображение результатов запроса не отличается от отображения

таблицы. Все режимы работы с таблицей применимы и к результирующей

таблице запроса. С ней можно работать так же, как и с обычной табли-

цей, в частности, не только просматривать, но и изменять данные. Изме-

нения сохраняются в записях базовой таблицы, на основе которой по-

строен запрос. Если запрос создан на основе двух и более связанных таблиц,

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

Особенности работы в окне Запроса:

 Порядок полей соответствует их выводу на экран. Для изменения –

выделить столбец и перетащить.

 Конструктор выделяет связи между таблицами автоматически, уда-

лить их можно вручную.

 Добавить или удалить Таблицу из запроса – через контекстное меню.

 Добавить или удалить Поле из запроса  Выделить столбец и DEL

 Изменение ширины столбцов – простое перетаскивания как в EX-

CEL

 Поле «Сортировка» - указывает порядок сортировки записей в За-

просе. Если несколько полей – то слева направо. Освоить выполнение различ-

ных запросов:

а) Сделать несколько вариантов подробного запроса

б) Сделать вар. итоговых запросов.

сумм. стипедию по группам,

среднюю стип. по группам

максимальную стип., число стип. и сумму стип. по группам,

сумму оценок по студентам

средний балл студентов

средний балл по группам

средняя оценка по городам

число студентов по городам

средняя стипендия по городам

средняя оценка у всех девочек и всех мальчиков

средняя оценка по всем студентам по группам

число стипендиатов по городам

сумма стипендий по городам

7) Задать имя запроса в поле «Задайте имя запроса» и выбрать даль-

нейшие действия: Открыть запрос для просмотра данных или «Изменить ма-

кет запроса».

8) Нажать на кнопку Готово.

Полученную в результате запроса таблицу можно использовать не толь-

ко для чтения данных, но и для изменения записей или внесения новых запи-

сей.

3.4.2 Запросы в режиме Конструктора

Использование Конструктора дает гораздо больше возможностей, чем

использование Мастера. Он позволяет строить сложные структуры запросов с

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

Начальная фаза:

1) На панели объектов выбрать Запросы;

2) Выбрать: Создание запроса с помощью Конструктора или нажать

на кнопку Создать (New) и в диалоговом окне Новый запрос выбрать Конст-

руктор и нажать OK.

3) В Окне «Добавление таблицы» - выбрать необходимые таблицы и

запросы, которые потребуются для формирования запроса. Закрыть окно До-

бавление таблицы.

В появившемся окне Запрос верхняя часть окна содержит список Таб-

лиц (и/или Запросов). Нижняя часть окна представляет собой макет таблицы

формируемой в результате Запроса. Количество полей (столбцов) таблицы оп-

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

ство строк фиксировано.

Назначение строк в окне Запроса

1 строка таблицы называется Поле. В ней содержатся имена полей. На-

звания полей переносятся мышью из соответствующей таблицы в верхней по-

ловине окна. Поле может быть вычисляемым. Тогда в соответствующей ячей-

ке содержится выражение для вычисления значения этого поля.

2 строка - Имя таблицы. Формируется автоматически.

3 строка - Сортировка. Она определяет способ сортировки по соответ-

ствующему полю (по возрастанию, по убыванию или без сортировки). В слу-

чае назначения Групповых операций в этой строке задаётся режим Группи-

ровка. В этом случае строка Сортировка и последующие строки опускаются

на строчку ниже.

4 строка управляет Выводом на экран значения соответствующего по-

ля.

5 строка называется Условием отбора. Содержит критерии, по которым

производится отбор записей в динамический набор данных.

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

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

описания запроса несколько раз. При записи условия в запросе можно объе-

динять критерии при помощи операций логического сложения (OR) или логи-

ческого умножения (AND).

3.4.3 Групповые операции

Групповые (статистические) запросы могут возвращать только суммар-

ную информацию. Они возвращают расчеты значений полей с использовани-

ем функций SUM, AVG, MIN, MAX, COUNT, StDEV (среднеквадратичное от-

клонение), Var (дисперсия значения поля). В групповом (статистическом) за-

просе невозможно отобразить отдельные строки; допускается только отобра-

жение статистических данных. Как следствие, все столбцы, помеченные для

вывода, должны быть назначены либо статистической функции, либо предло-

жению GROUP BY. Группировка – определяет поле группировки.

Для статистических запросов можно использовать Числа, дата, время,

счетчик.

Для функций (MIN, MAX, COUNT) – можно использовать и текстовый

тип данных. Освоить выполнение запросов различного типа:

а) выбрать две таблицы Студенты и Ведомость

 перенести поля Фамилия и Успеваемость 267 записей

 в 3 строке ввести Групповая операция (Группировка) 44 записи , т.к. у каждого только раз-

ные оценки

 в Успеваемости заменить Группировку на Count - останется 24 записи по числу студентов

и будет показывать число оценок студента,

 в Успеваемости заменить Группировку на Avg - станет 24 записи по числу студентов и бу-

дет показывать средний бал студента,

 в условии отбора поставим >4 , останется 13 записей, в т.ч. Балагуров и Худяков

 в условии отбора поставим >=4,7, останется 9 записей .Не будет Худякова. Почему?

Внутри отдельного поля можно использовать сложные конструкции ви-

да («like “А*” AND like “*ит*”) или (>4 And <4,7)

 добавим вторично поле Успеваемость Группировку на Avg в условии отбора поставим

<4,7 и уберем Вывод на экран. Останется 4 записи. (>4 And <4,7)

Если в строке несколько условий, то каждая строка – это отдельное ус-

ловие по «И» (AND) для нескольких выбранных полей. Само поле можно на

экране не отражать.

 - добавим поле Д_рождения гупповой операции Группировк.В условии отбора поставим

Between #01.01.2007# And #31.12.2007#. Останется 2 записи.

Все строки объединяются по условию «ИЛИ» (Or).

 - Выбрать студентов из Тихвина и Колпино через разные строки (можно задавать и без ка-

вычек).

3.4.4 Запросы с параметрами

Отличие запросов с параметрами заключается в способе управления ус-

ловиями: вместо ввода фактических значений настраивается приглашение

пользователю ввести значения условий при запуске запроса. При выполнении

такого запроса выдается диалоговое окно «Введите значение параметра»

(Enter Parameter Value), в котором пользователь должен ввести конкретное

значение и затем получить нужный результат.

Чтобы создать подобный запрос нужно определить параметр запроса

(поле) и ввести в строку Условие отбора (Criteria) текст, который появится

при запуске запроса, заключив его в квадратные скобки [ ].

Например:

[Поставщик:] Задается наименование поставщика

Between [Начальная дата] And [Конеч-

ная дата]

Задается конкретный диапазон вы-

борки

 - Сформировать запрос с выбором задаваемого диапазона Д._рождения студентов

 - вывести список студентов задаваемого курса (ФИО, пол, курс);

 - вывести успеваемость (ФИО, группа, предметы, оценка) конкретного студента;

 - то же самое, но вводя идентификатор студента (Test_5)

3.4.5 Запись условий

Операторы сравнения с образцом: Between, Like «Ме*»

В операторе Like «Ме*»:

o символ "*" замещает любое число знаков, а символ шаблона "?"

замещает только один знак, поэтому Like "Ив*" вернет True для

значений "Иванов", "Иващенко", "Иволгин". Для "dbl" или "dbl00"

значение оператора Like "db??" будет False, но тот же оператор

вернет для "dbl0" и "dbXX" значение True. Символы шаблона "*"

и "?" могут стоять в любом месте шаблонной строки, . Например:

o Like "*ms* . ?*" — возвращает True только для значений, содер-

жащих в своем теле подстроку "ms", после которой (может быть

сразу, а может быть через несколько символов) следует не менее

двух символов, первый из которых точка (.);

o Like "?1???*" — возвращает True только для значений, состоящих

не менее чем из пяти символов, вторым из которых является "1".

Примеры записи условий:

Between #01.01.2007# And #31.12.2007# Выборка за 2007 г.

Between [Начальная дата] And [Конечная дата] В заданном промежутке

<>Да Любое значение, кроме «Да»

«английский» Отобрать данные со значением «англий-

ский»

«like “А*” AND like “*ит*” Отобрать данные, начинающиеся с буквы

«А» и имеющие буквосочетание «ит»

3.4.6 Перекрестный запрос

Для создания перекрестного запроса готовится обычно предваритель-

ный запрос, включающий в себя все необходимые поля.

Далее через меню окна Создать выбирается опция Перекрестный за-

прос.