Задание.

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

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

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

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

Выполнение работы.

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

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

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

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

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

• в

  Слово Фамилия перейдет в

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

• щелкните по кнопке окно «Выбранные поля»;

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

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

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

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

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

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

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

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

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

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

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

• создайте запрос на выборку для следующих полей таб­лицы Преподаватели:

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

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

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

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

М

или выполнив команду Вид, Конструктор;

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

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

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

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

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

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

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

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

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

Задание.

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

Выполнение работы.

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

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

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

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

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

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

из таблицы, поэтому щелкните по кнопке

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

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

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

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

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

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

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

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

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

• закройте отчет.

Работа 6. Разработка модели базы данных.

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

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

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

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

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

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

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

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

Т

■Ч,

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

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

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

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

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

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

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

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

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

Задание.

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

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

Выполнение работы.

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

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

М:Ы

Студенты

Дисциплины

» Преподаватели

1:М

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

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

1:М

Дисциплины |<-

—Ж

1:М

Студенты

1:М —»

Оценки

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

2. В реляционной базе данных в качестве объектов рассматриваются отношения, которые можно предста­вить в виде таблиц. Таблицы между собой связываются посредством общих полей, т.е. одинаковых по ^Лформатам

и, как правило, по названию, имеющихся в обеих табли­цах. Рассмотрим, какие общие поля надо ввести в табли­цы для обеспечения связности данных. В таблицах Сту-

33

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

Студенты Оценки Дисциплины Преподаватели

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

Работа 7. Создание реляционной базы данных.

1. Создайте базу данных Деканат.

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

3. Создайте структуру таблицы Дисциплины.

4. Измените структуру таблицы Преподаватели.

5. Создайте структуру таблицы Оценки.

6. Разработайте схему данных.

Выполнение работы.

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

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

• в окне <Файл новой базы данных> задайте имя файла и выберите папку, где ваша база будет находиться. По умолчанию Access предлагает имя базы dbl, а тип файла

• Базы данных Access. Имя задайте Деканат, а тип файла оставьте прежним;

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

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

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

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

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

Имя поля	Тип данных	Размер поля
Код студента	Числовой	Целое
Фамилия	Т екстовый	15
Имя	Текстовый	12
Отчество	Текстовый	15
Номер группы	Числовой	Целое
Телефон	Текстовый	9
Стипендия	Логический	Да/Нет

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

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

режиме формы.

2. Создайте структуру таблицы Дисциплины анало­гично п. 2 в соответствии с табл. 7.2 :

. Имя поля	Тип данных	Размер поля
Код дисциплины	Числовой	Целое
Название дисциплины	Текстовый	30

В качестве ключевого поля задайте «Код дисципли­ны». Заполняться эта таблица будет также в режиме фор­мы.

2. Структура таблицы Преподаватели уже создана в работе 1 и заполнена данными, поэтому для работы ис­пользуйте эту таблицу с одним лишь изменением —в соот­ветствии с рис. 6.3 в структуру таблицы надо добавить по­ле «Код дисциплины» и заполнить его в соответствии с данными табл. 7.2.

3. Создайте структуру таблицы Оценки аналогично п. 2 в соответствии с таблицей 7.3.,.

Имя поля	Тип данных	Размер поля
Код студента	Числовой	Целое
Код дисциплины	Числовой	Целое
Оценки	Числовой	Байт

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

2. Разработайте схему данных, т.е. создайте связи между таблицами. Для этого:

• щелкните по кнопке на панели инструментов или выполните команду Сервис, Схема данных. На экране поя­вится окно «Схема данных»;

• щелкните по кнопке на панели инструментов или выполните команду Связи, Добавить таблицу;

• в появившемся окне будет выделено название одной таблицы. Щелкните по кнопке <Добавить>;

• переведите выделение на имя следующей таблицы и щелкните по кнопке <Добавить>. Аналогично добавьте оставшиеся две таблицы;

• закройте окно, щелкнув по кнопке <3акрыть>;

• создайте связь между таблицами Дисциплины и Оценки. Для этого подведите курсор мыши к полю «Код дисципли­ны» в таблице Дисциплины, щелкните левой кнопкой мы­ши и, не отпуская ее, перетащите курсор на поле «Код дис­циплины» в таблицу Оценки, а затем отпустите кнопку мыши. На экране откроется окно «Связи»;

• установите флажок ("галочку") в свойстве Обеспечение целостности данных, щелкнув по нему;

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

• щелкните по кнопке <Создать>. Связь будет создана;

• аналогично создайте связи между полем «Код дисцип­лины» в таблице Дисциплины и полем «Код дисциплины» в таблице Преподаватели, а также между полем «Код сту­дента» в таблице Студенты и полем «Код студента» в таб­лице Оценки.

• закройте окно схемы данных, ответив ДА на во­прос о сохранении макета.

Работа 8. Создание форм для ввода данных в таб­лицы.

1. Создайте форму Студенты.

2. Заполните данными таблицу Студенты посредством формы Студенты.

3. Создайте форму Дисциплины.,

4. Заполните данными таблицу Дисциплины посредством формы Дисциплины.

5. Создайте форму Оценки.

6. Заполните данными таблицу Оценки посредством фор­мы Оценки.

Выполнение работы.

1. Для создания формы Студенты:

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

• в открывающемся списке выберите таблицу Студенты;

• выберите пункт Автоформа: ленточная;

• щелкните по кнопке <ОК>.

2. Заполните данными, приведенными в табл. 8.1, таб­лицу Студенты посредством формы.

Таблица 8.1

Код студент»	Ф1МИЛНЯ	Имя	Отчество	Номер группы	Телефон	Стипендия
1	Арбузов	Николай	Николаевич	151	260-15-63	Да
2	Киршин	Петр	Валерьевич	151	110-67-82	Да
3	Кривннский	Сергей	Николаевич	151	172-97-21	Нет
4	Крылова	Елена	Петровна	151	130-31-87	Да
5	Кульчий	Григорий	Викторович	151	269-53-75	Да
6	Патрикеев	Олег	Борисович	152	234-11-63	Нет
7	Перлов	Кирилл	Николаевич	152	312-21-33	Нет
8	Соколова	Натали	Петровна	152	166-87-24	Нет
9	Степанская	Ольга	Витальевна	152	293-43-77	Да
10	Тимофеев	Сергей	Трофимович	152	260-11-57	Да

Переход между ячейками лучше выполнять клавишей <ТАВ> либо мышью. Закройте форму, задав ей имя Сту­денты.

3. Создайте форму Дисциплины аналогично п.1.

4. Заполните данными, приведенными в табл. 8.2, таб­лицу Дисциплины посредством формы и закройте форму, задав ей имя Дисциплины.

5. Создайте форму Дисциплины аналогично п. 1.

6. Заполните данными, приведенными в табл. 8.3, табли­цу Оценки. Посредством формы закройте форму, задав ей имя Оценки.

Таблица 8.2.

Код дисциплины	Название дисциплины
1	Информатика
2	Математика
3	Физика
4	Экономика

Таблица8.3.

Код	Код	Оценки	Код	Код	Оценки
студент»	дисциплины		студент»	дисциплины
1	1	4	6	1
I	2	5	6	2	4
1	3	4	6	3	5
1	4	4	6	4	4
2	1	5	7		4
2	2	5	7
2	3	4	7	3	4
2	4	4	7	4	3
3	1	3	8		3
3	2	5	8		5
3	3	4	8	3	5
3	' 4	3	8	4	4
4	1	4	9		4
4	2	4	9		4
4	3	3	9	3	4
4	4	4	9	4	4
5	I	5	10		5
5	2	5	10
5	3	5	10	3	5
5	4	5	10	4	5

Работа 9. Формирование сложных запросов

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

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

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

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

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

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