Geum.ru

Практикум к главе 1 «Информационные системы» Раздел Системология Работа Модели систем (к 2)

Вид материалаПрактикум
Работа 2.2. Создание базы данных «Классный журнал»
Таблицы выбирается команда Создание таблицы в режиме конструктора
Длина (Формат)
Длина (Формат)
Длина (Формат)
Сервис - Схема данных
Обеспечить целостность данных
Далее  включить радиокнопку «В один столбец»  Далее
Окно конструктора запросов
Бланк запроса
Длина (формат)
УРОВЕНЬ 2: задания на самостоятельную разработку
5) Используя конструктор запросов, реализовать указанные запросы к базе данных
Шифр, Наименование, Номер_рецепта) Изготовление (Номер
Подобный материал:
1   2   3

Работа 2.2. Создание базы данных «Классный журнал»


Цель работы:
  • освоение приемов работы с MS Access в процессе создания спроектированной базы данных;

Используемое программное средство: MS Access.


1. Открыть файл для новой базы данных:

ФайлСоздать БДНовая БД

 в файловом окне указать путь и имя файла : «Классный журнал»


На экране откроется окно:



2. Создать таблицу УЧЕНИКИ:

 в режиме Таблицы выбирается команда Создание таблицы в режиме конструктора

 в открывшемся окне конструктора таблиц для всех полей таблицы УЧЕНИКИ указать имена, типы и форматы полей:

УЧЕНИКИ

Имя поля
Тип поля
Длина (Формат)

НОМЕР УЧ
числовой
целый

ФАМИЛИЯ
текстовый
30

ИМЯ
текстовый
15

ПОЛ
текстовый
1

АДРЕС
текстовый
40

ТЕЛЕФОН
текстовый
15




  1. Назначить главный ключ таблицы:

 установить указатель мыши на поле НОМЕР_УЧ,

 выполнить команду Правка – Ключевое поле или нажать на кнопку, изображающую ключ на панели инструментов
  1. Сохранить таблицу с именем УЧЕНИКИ. Окно конструктора таблиц показано на рисунке


5. Создать, назначить главный ключ и сохранить таблицу ПРЕДМЕТЫ следующей структуры:


ПРЕДМЕТЫ

Имя поля
Тип поля
Длина (Формат)

НАЗВ_ПРЕДМЕТА
текстовый
30

УЧИТЕЛЬ
текстовый
40


6. Создать, назначить главный ключ и сохранить таблицу ОЦЕНКИ:


ОЦЕНКИ

Имя поля
Тип поля
Длина (Формат)

НОМЕР_УЧ
числовой
целый

НАЗВ_ПРЕДМЕТА
текстовый

30

ДАТА
дата



ОЦЕНКА
числовой
целый


При назначении составного ключа одновременно выделяются поля НОМЕР_УЧ, НАЗВ_ПРЕДМЕТА и ДАТА. Выделение производится с помощью мыши при нажатой клавише Shift. Затем выполняется команда Правка – Ключевое поле.


7. Связать таблицы УЧЕНИКИ и ОЦЕНКИ. Для этого:

 выполнить команду Сервис - Схема данных ;

Откроется окно «Добавление таблицы»;

 выделить название таблицы «Ученики» и выполнить команду Добавить

 выделить название таблицы «Оценки» и выполнить команду Добавить

 выполнить команду Закрыть.

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

 с помощью мыши, перетащить ключевое поле НОМЕР_УЧ из образа таблицы «Ученики» на это же поле в образе таблицы «Оценки»

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




8. Связать таблицы ПРЕДМЕТЫ и ОЦЕНКИ, действуя аналогично предыдущему этапу.

9. Выполнить команду  Создать. Схема готова! В окне «Схема данных» будет иметь следующий вид:



10. Ввести данные в таблицу УЧЕНИКИ. Для этого:


 в режиме «Таблицы» выделить название таблицы УЧЕНИКИ;

 отдать команду Открыть;

На экране появится бланк таблицы, содержащей заголовки полей и пустую строку

 ввести данные:




11. Аналогично ввести данные в таблицу ПРЕДМЕТЫ:




12. Ввести данные в таблицу ОЦЕНКИ





13. Создать форму для просмотра и ввода в таблице УЧЕНИКИ. Для этого:


 перейти на вкладку «Формы»  Создать

 выбрать способ создания формы: «Мастер форм»

 выбрать таблицу УЧЕНИКИ

 переместить все поля таблицы из окна «Доступные поля» в окно «Выбранные поля»  Далее

 включить радиокнопку «В один столбец»  Далее

 выбрать стиль формы «Стандартный»  Далее

 задать имя формы: оставить имя УЧЕНИКИ; включить радиокнопку «Открытие формы для просмотра и ввода данных»  Готово.





14. Сохранить базу данных по команде Файл – Сохранить.


Работа 2.3. Реализация запросов с помощью конструктора


Цель работы: освоение приемов реализации запросов на выборку с помощью конструктора запросов MS Access.

Используемые программные средства: MS Access.


Основные понятия

Конструктор запросов - высокоуровневое средство формирования запросов в СУБД MS Access, который можно рассматривать как пользовательскую оболочку к языку запросов SQL. Для формирования запроса в конструкторе используется табличная форма.

Окно конструктора запросов представлено на следующем рисунке:





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

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


ЗАДАНИЕ 1

Получить список учеников класса, содержащий номер в журнале, фамилию и имя каждого ученика.

Команда выборки на учебном языке запросов:

.выбрать УЧЕНИКИ.НОМЕР_УЧ, УЧЕНИКИ.ФАМИЛИЯ, УЧЕНИКИ.ИМЯ сортировать УЧЕНИКИ.НОМЕР_УЧ по возрастанию


1. Перейти к работе с конструктором запросов:

 выбрать в списке объектов «Запросы»

 выбрать режим «Создание запроса в режиме конструктора»

 отдать команду Создать

2. В поле схемы запроса поместить таблицу «Ученики»:

 в окне «Добавление таблицы», вкладке «Таблицы» выбрать название таблицы «Ученики»

 выполнить команду Добавить - Закрыть

3. Заполнить бланк запроса

 внести в бланк данные, показанные на предыдущем рисунке.

4. Исполнить запрос:

выполнить команду Запрос - Запуск. На экране появится таблица следующего вида:





  1. Сохранить запрос с именем «Список учеников»


ЗАДАНИЕ 2

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


Команда на учебном языке запросов:

.выбрать ОЦЕНКИ.НАЗВ_ПРЕДМЕТА, ОЦЕНКИ.ДАТА, ОЦЕНКИ.ОЦЕНКА где ОЦЕНКИ.НОМЕР_УЧ=5 сортировать ОЦЕНКИ.НАЗВ_ПРЕДМЕТА по возрастанию, ОЦЕНКИ.ДАТА по возрастанию


1. Построить запрос в конструкторе запросов в виде, показанном на рисунке:





2. Исполнить запрос. Результат будет следующим:




3. Сохранить запрос с именем «Оценки Волегова»


ЗАДАНИЕ 3

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


Команда на учебном языке запросов:

.выбрать УЧЕНИКИ.ФАМИЛИЯ, ОЦЕНКИ.ДАТА, ОЦЕНКИ.ОЦЕНКА где ОЦЕНКИ.НАЗВ_ПРЕДМЕТА=”Алгебра” сортировать УЧЕНИКИ.ФАМИЛИЯ по возрастанию, ОЦЕНКИ.ДАТА по возрастанию

  1. Построить запрос в конструкторе запросов в виде, показанном на рисунке:




  1. Исполнить запрос. Результатом будет следующая таблица:




3. Сохранить запрос с именем «Оценки по алгебре»


ЗАДАНИЕ 4


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


Команда на учебном языке запросов:

.выбрать УЧЕНИКИ.ФАМИЛИЯ, ОЦЕНКИ.ДАТА, ОЦЕНКИ.НАЗВ_ПРЕДМЕТА, ПРЕДМЕТЫ_УЧИТЕЛЬ где ОЦЕНКИ.ОЦЕНКА=2 сортировать УЧЕНИКИ.ФАМИЛИЯ по возрастанию

Для реализации этого запроса используются все три связанные таблицы.

  1. Построить запрос в конструкторе запросов в виде, показанном на рисунке:





  1. Исполнить запрос. Результатом будет следующая таблица:





3. Сохранить запрос с именем «Двоечники»


ЗАДАНИЕ 5

Вывести список всех учеников класса получивших пятерки по алгебре и по истории, указав фамилию, имя, предмет и дату получения оценки


Команда на учебном языке запросов:

.выбрать УЧЕНИКИ.ФАМИЛИЯ, УЧЕНИКИ.ИМЯ, ОЦЕНКИ.НАЗВ_ПРЕДМЕТА, ОЦЕНКИ.ДАТА где (ОЦЕНКИ.НАЗВ_ПРЕДМЕТА=”Алгебра” или ОЦЕНКИ.НАЗВ_ПРЕДМЕТА=”История”) и ОЦЕНКИ.ОЦЕНКА=5 сортировать УЧЕНИКИ.ФАМИЛИЯ по возрастанию


1. Построить запрос в конструкторе запросов в виде, показанном на рисунке:





Обратите внимание на то, как реализовано сложное логическое выражение в условии отбора. Условия отбора, записанные в одной строке, объединяются операцией «и». Условия во второй строке объединяются с условием первой строки операцией «или». Такая реализация условия отбора в этом запросе равносильна раскрытию скобок в логическом выражении в команде на учебном языке:

(НАЗВ_ПРЕДМЕТА=”Алгебра” или НАЗВ_ПРЕДМЕТА=”История”) и ОЦЕНКА = 5 тождественно выражению:

НАЗВ_ПРЕДМЕТА=”Алгебра” и ОЦЕНКА = 5 или НАЗВ_ПРЕДМЕТА=”История” и ОЦЕНКА = 5


2. Исполнить запрос. Результатом будет следующая таблица:




3. Сохранить запрос с именем «Пятерки по алгебре и истории»


ЗАДАНИЕ 6

Вычислить средние оценки для всех учеников по всем предметам. Вывести фамилию ученика, название предмета, среднюю оценку, ФИО учителя по этому предмету.


Команда на учебном языке запросов:

.выбрать УЧЕНИКИ.ФАМ, ОЦЕНКИ.НАЗВ_ПРЕДМЕТА, Avg(ОЦЕНКИ.ОЦЕНКА), ПРЕДМЕТЫ.УЧИТЕЛЬ группировать УЧЕНИКИ.ФАМ, ОЦЕНКИ.НАЗВ_ПРЕДМЕТА, ПРЕДМЕТЫ.УЧИТЕЛЬ сортировать УЧЕНИКИ.ФАМ по возрастанию, ОЦЕНКИ.НАЗВ_ПРЕДМЕТА по возрастанию


1. Построить запрос в конструкторе запросов в виде, показанном на рисунке:




Для того чтобы в конструкторе запросов появилась строка «Групповая операция», нужно через главное меню окна Access отдать команду Вид - Групповые операции. Во всех полях новой строки установится слово «Группировка». Функция вычисления среднего Avg выбирается из списка, который открывается после щелчка по кнопке в этой строке в ячейке для поля ОЦЕНКА.


2. Исполнить запрос. Результатом будет следующая таблица:




3. Сохранить запрос с именем «Средние оценки»


ЗАДАНИЕ 7

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


Команда на учебном языке запросов:


.выбрать УЧЕНИКИ.ФАМ, ОЦЕНКИ.НАЗВ_ПРЕДМЕТА, Avg(ОЦЕНКИ.ОЦЕНКА) группировать УЧЕНИКИ.ФАМ, ОЦЕНКИ.НАЗВ_ПРЕДМЕТА где ОЦЕНКИ.НАЗВ_ПРЕДМЕТА = “Алгебра” и Avg(ОЦЕНКИ.ОЦЕНКА) >4 сортировать УЧЕНИКИ.ФАМ по возрастанию


1. Построить запрос в конструкторе запросов в виде, показанном на рисунке:





2. Исполнить запрос. Результатом будет следующая таблица:





3. Сохранить запрос с именем «Средние оценки больше 4»


Работа 2.4. Расширение базы данных


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

Используемые программные средства: MS Access.


ЗАДАНИЕ 1

Дополнить БД данными с четвертными оценками учеников.

  1. Создать в БД новую таблицу ИТОГИ следующей структуры:




Имя поля
Тип поля
Длина (формат)

НОМЕР_УЧ
Числовой
Целое

НАЗВ_ПРЕДМЕТА
Текстовый
30

1_ЧЕТВ
Числовой
Целое

2_ЧЕТВ
Числовой
Целое

3_ЧЕТВ
Числовой
Целое

4_ЧЕТВ
Числовой
Целое

ГОД
Числовой
Целое



  1. Связать эту таблицу с таблицами УЧЕНИКИ и ПРЕДМЕТЫ через общие поля. В результате получится следующая схема:




  1. Заполнить таблицу ИТОГИ следующими данными:





ЗАДАНИЕ 2

Получить список отличников по отдельным предметам. Указать фамилию, имя ученика и предмет, по которому он имеет все четвертные пятерки.


Команда на учебном языке:

.выбрать УЧЕНИКИ.ФАМИЛИЯ, УЧЕНИКИ.ИМЯ, ПРЕДМЕТЫ.НАЗВ_ПРЕДМЕТА где ИТОГИ.1_ЧЕТВ=5 и ИТОГИ.2_ЧЕТВ=5 и ИТОГИ.3_ЧЕТВ=5 и ИТОГИ.4_ЧЕТВ=5 сортировать УЧЕНИКИ.ФАМИЛИЯ по возрастанию





В результате выполнения запроса получим таблицу:





ЗАДАНИЕ 3

Вычислить суммы оценок по информатике у всех учеников и вывести список в прядке убывания суммы.


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


.выбрать УЧЕНИКИ.ФАМИЛИЯ, СУММА: [ИТОГИ].[1_ЧЕТВ]+ [ИТОГИ].[ 2_ЧЕТВ]+ [ИТОГИ].[3_ЧЕТВ]+ [ИТОГИ].[4_ЧЕТВ] где ПРЕДМЕТЫ.НАЗВ_ПРЕДМЕТА= ”Информатика” сортировать СУММА по убыванию





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





УРОВЕНЬ 2: задания на самостоятельную разработку


Описаны отношения реляционной базы данных для информационной системы в определенной предметной области.
  1. выбрать типы для атрибутов
  2. Используя конструктор таблиц Access, создать соответствующие таблицы, организовать связи и построить схему
  3. Создать формы для просмотра и заполнения таблиц
  4. Заполнить таблицы конкретными данными

5) Используя конструктор запросов, реализовать указанные запросы к базе данных


Вариант 1. Кухня.

Повар (Номер, ФИО, Место_работы, Разряд)

Продукты (Шифр, Наименование, Цена, Количество)

Блюдо ( Шифр, Наименование, Номер_рецепта)

Изготовление (Номер_повара, Шифр_продукта, Шифр_блюда, Расход_продукта)

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


Реализовать следующие запросы к БД:
  1. – выведите наименование самого дорогого блюда;
  2. – выведите названия блюд, в которых используются все продукты;
  3. – выведите ФИО всех поваров, которые изготовляют все блюда;
  4. – выведите наименования продуктов, которые не используются для приготовления указанного блюда;
  5. – выведите ФИО поваров, которые готовят те же блюда, что и указанный повар;
  6. – выведите наименования продуктов, которых не хватит для приготовления указанного блюда указанным поваром.