Государственное бюджетное учреждение дополнительного профессионального педагогического образования центр повышения квалификации специалистов 
«Информационно-методический центр» Пушкинского района Санкт-Петербурга

А.С. Горбенко, Е. А. Юркова

Основы работы с базами данных

Конспект практических занятий по курсу

«Компьютерные технологии в прикладных областях»

Санкт-Петербург

§ 1. СПИСКИ EXCEL КАК БД

База данных (БД) – структурированная информация об однородных объектах, которая хранится в виде карточек (форм) или строк таблицы.

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

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

• простейшие БД могут вестись даже в текстовом редакторе. Здесь возможна только сортировка и поиск данных;
• для решения сложных задач управления БД предназначены профессиональные СУБД – Microsoft Access, Paradox, FoxPro и др;
• Microsoft Excel используется как средство управления БД малого и среднего размера, не содержащих графических элементов.

При работе в Excel с БД для обозначения таблицы БД используется термин Список. При создании списка необходимо выполнять следующие условия:

1. каждый столбец должен содержать информацию одного типа (например, текст);                
2. одна или две верхние строки списка должны содержать заголовки столбцов;                
3. в списке не должно быть пустых строк и столбцов;                        
4. список должен быть отделен от других данных рабочего листа Excel пустыми строками и столбцами.

Сортировка записей в списке:

• активизируйте ячейку внутри списка в нужном столбце;
• выполните сортировку: Вкладка  Данные/Группа Сортировка и фильтр;
• наличие/отсутствие строки заголовка в списке задается в диалоговом окне Сортировка. Здесь же можно задать более сложное условие сортировки.

Выбор нужных записей из списка:

• активизируйте ячейку внутри списка;
• включите фильтрацию: Вкладка  Данные/Группа Сортировка и фильтр/Кнопка Фильтр;
• если фильтрация включена, достаточно щелкнуть по стрелке в заголовке столбца, чтобы выбрать фильтр для этого столбца.

Практическое задание 1-1

1. Лист Нумизмат: создайте список на основании следующей таблицы.
2. Отсортируйте список по столбцу Название монеты.
3. Добавьте столбец для вычисления:      Масса, карат = Масса, г *5.
4. Изучите возможности фильтра при фильтрации текста и чисел.
5. Постройте линейчатую диаграмму (Название монеты; Масса, г)
6. Сохраните документ под именем Списки в своей папке.

Практическое задание 1-2

1. Откройте документ Списки.
2. Лист Планеты: создайте список на основании следующей таблицы.
3. Используя фильтр, найдите общую массу планет:

• имеющих экваториальный диаметр менее 50 тыс. км;
• имеющих период обращения по орбите более 10 земных лет.

4. Сохраните результаты фильтрации рядом с исходной таблицей.

§ 2. РАСШИРЕННЫЙ ФИЛЬТР

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

В Microsoft Excel для более сложных условий отбора применяется расширенный фильтр. Расширенный фильтр способен сформировать новую таблицу из отфильтрованных записей.

Для создания расширенного фильтра выполните следующие действия.

1.  Сформируйте ТАБЛИЦУ КРИТЕРИЕВ:

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

2. Выделите ячейку внутри исходного списка.
3. Выполните команду вкладка  Данные / Группа Сортировка и фильтр / Кнопка Дополнительно. В появившемся окне укажите:

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

Практическое задание 2

1. Откройте документ Списки. Создайте на листе Звери список  на основании следующей таблицы.

2. Используя расширенный фильтр и копирование данных через буфер обмена, создайте в текстовом редакторе Word документ Уникальные звери нашей планеты в соответствии с образцом.

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

Вот только некоторые, самые уникальные, из зверей.

Из отряда приматов обращают на себя внимание обезьяны весом свыше 50 килограмм. Их еще называют человекообразными.

Табл. 1. Человекообразные обезьяны (в порядке возрастания веса).

Интересны хищники, среди которых есть малютки, весящие не больше килограмма, и гиганты весом свыше центнера.

Табл. 2. Диапазон веса хищников (в порядке убывания веса).

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

Табл. 3.  Ластоногие (по алфавиту).

§ 3. ОСНОВНЫЕ ПОНЯТИЯ И МОДЕЛИ БАЗ ДАННЫХ

База данных (БД) – это структурированная информация об объектах, связанных общей темой или задачей.  Структурирование данных – объединение данных по определенным параметрам.

ОСНОВНЫЕ МОДЕЛИ БД

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

Свойства:

• узел низшего уровня связан только с одним узлом высшего уровня;
• иерархическое дерево имеет только одну вершину (корень);
• каждый узел имеет свое уникальное имя.

Примеры: файловая структура диска, сложное меню, БД Столицы:

2). Сетевая БД – имеет те же  элементы, что и иерархическая БД: узел, уровень, связь, но другой характер их отношений. В сетевой модели принята свободная связь между элементами разных уровней.

Пример: производство (выпуск изделий разными цехами):

3). Реляционная БД  (реляционная модель была предложена в 1969 году математиком, научным сотрудником фирмы IBM Э.Коддом) – использует представление данных в виде таблицы (лат. relation – отношение). В простейшем случае реляционная БД представляет собой двумерную таблицу, а при создании сложных информационных моделей – совокупность взаимосвязанных таблиц.

Каждая ячейка таблицы – один элемент данных.

Все столбцы в таблице однородны, т.е. имеют один тип (числовой, текстовый и т.д.). Каждый столбец имеет уникальное имя.

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

Запись  – строка таблицы.                        Поле  – столбец таблицы.

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

Примеры:

СУБД – системы управления базами данных. СУБД Microsoft Access 2007 позволяет хранить все объекты БД в одном файле с расширением accdb.

Создание БД начинается с создания таблицы. Таблица – основа БД: главное хранилище информации (набор однотипных записей).

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

Изменение формата данных можно сделать, используя вкладку Работа с таблицами / Режим Таблицы.

Практическое задание 3-1

Создайте БД  Учебная, содержащую несколько таблиц.

Таблица Записная книжка. Введите в таблицу 9 записей.

Таблица Мероприятия. Введите в таблицу 8 записей.

§ 4. СОЗДАНИЕ ТАБЛИЦЫ В РЕЖИМЕ «КОНСТРУКТОР»

Тип поля определяется данными, которые будут храниться  в этом поле. В зависимости от типа поля изменяется и набор его свойств:

текстовый – строка текста длиною до 255 символов;

числовой – числовые данные (например, 89, 0,5);

поле МЕМО – текст более 255 символов (до 65535);

дата/время – дата или время (например, 18.03.2008, 12:55:00);

денежный – денежный формат (например, 345,00р.);

логический – одно из значений ДА/НЕТ (ИСТИНА/ЛОЖЬ);

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

поле объектов OLE – внешние данные: изображения, звуки и т.д.;

гиперссылка – ссылка на любые внешние объекты;

мастер подстановок.

Основные свойства полей

Размер:

для текстового поля – по умолчанию - 50 символов.

для числового поля размер – по умолчанию длинное целое

Значение по умолчанию  – значение, автоматически задаваемое в поле для новой записи.

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

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

выберите режим Конструктор;

для каждого поля:

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

Практическое задание 4-1

Создайте БД Мосты.

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

Практическое задание 4-2

Создайте БД Страны.

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

§ 5. ФИЛЬТРАЦИЯ – СРЕДСТВО АНАЛИЗА ДАННЫХ НА УРОВНЕ ТАБЛИЦЫ

Фильтрация – отбор записей, удовлетворяющих заданному критерию. Если при работе с таблицей нужно ОПЕРАТИВНО сделать выборку записей в соответствии с определенными критериями, то следует использовать ФИЛЬТР.

Для работы с фильтрами  используем группу Сортировка и фильтр на вкладке Главная. Основные команды фильтрации: Выделение, Дополнительно–Изменить фильтр и Дополнительно–Расширенный фильтр.

На бланке Фильтр добавьте поля, для которых будут заданы условия поиска и сами условия. Если условия связаны операцией И (And), располагайте их в одной строке бланка. Если условия связаны операцией ИЛИ (Or), располагайте их на разных строках бланка.

При составлении условия поиска можно использовать:

операции сравнения: <, <=, >, >=, =, <>;

логические операции: And (И), Not (Нет), Or (Или);

шаблоны *, ?, [ ], #, !

Примеры использование шаблонов в фильтрах

Примечание: Access автоматически изменяет некоторые значения, которые вы вводите в строку Условие отбора бланка фильтра:

– символ # по обе стороны от даты означает, что это не математическое выражение;

– к текстовым значениям добавляется имя функции сравнения строк Like, а сам образец ставится в кавычки.

Практическое задание 5

На основании БД Мосты, создайте ТЕКСТОВЫЙ документ Отчет о результатах фильтрации, который содержит:

• название фильтра,
• результат фильтрации.

Для вставки отфильтрованного списка в документ  используйте буфер обмена.

Примените следующие фильтры:

1.  Арочные мосты, длиной более 500 м.
2.  Мосты США, постройки после 1970 года.
3.  Мосты постройки до 1918 года или после 1960 года (отсортируйте по году  постройки).
4.  Мосты длиной от 300 до 500 м.
5.  Арочные мосты с названием начинающимся на Д или заканчивающимся на й (отсортируйте по названию).
6.  Мосты США длиной более 1200 м.
7.  Мосты, в названии которых имеется буква ф.

§ 6. ОТБОР ЗАПИСЕЙ С ПОМОЩЬЮ ЗАПРОСА НА ВЫБОРКУ

Запросы – специальные объекты, предназначенные для отбора, изменения, добавления и удаления данных.  Запрос сохраняется как обычная таблица. Источник данных для запроса – исходная таблица БД или таблицы других запросов.

При создании запросов можно не только выбирать информацию, но и обрабатывать ее: сортировать, объединять, разделять, выполнять вычисления. При этом никаких изменений в исходных базовых таблицах БД не происходит.

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

Простой запрос на выборку

В результате работы запроса формируется результирующая таблица, содержащая записи и поля, соответствующие условию отбора.

Для создания запроса:

• откройте вкладку Создание группу Другие;
• выберите команду Конструктор запросов;
• добавьте таблицы (и/или запрос), по которым будет создаваться запрос. Таблицы добавляются в верхнюю часть окна Запрос на выборку;

в нижней части окна заполните бланк запроса:

• перетащите названия необходимых полей в строку Поле. Строка Имя таблицы заполняется автоматически;
• при необходимости выберите вид сортировки по полям;
• если не нужно, чтобы поле было видно в запросе, уберите флажки из строки Вывод на экран,
• укажите Условие отбора:

• условия связанные операцией И (And) располагают в одной строке бланка; условия связанные операцией ИЛИ (Or) –  на разных;
• при формировании условий можно использовать шаблоны * ? #.

Для выполнения запроса – щелкните по кнопке Выполнить . Чтобы вернуться к созданию запроса – щелкните по кнопке Конструктор  на вкладке Главная.

Примеры условных выражений для формирования условий отбора

Запрос на выборку с параметром

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

Практическое задание 6-1

Создайте следующие запросы для БД Учебная.

1. Запрос Телефон (поля – фамилия, имя, Домашний телефон).
2. Запрос Спорт (все поля; условие: записи, для которых увлечение – спорт).
3. Запрос с параметром По имени (все поля; параметр: имя).

Практическое задание 6-2

Создайте следующие запросы для БД Страны.

1. Запрос Население (поля – Название, Население, Площадь).
2. Запрос Английский язык (все поля, условие: страны с английским языком и населением более 10 000 тыс. человек).
3. Запрос с параметром Религия (все поля; параметр: Религия).
4. Запрос Площадь (поля – Название, Площадь; условие: страны с английским языком и площадью менее 100 000 кв.км).

§ 7. САМОСТОЯТЕЛЬНАЯ РАБОТА

1. Скопируйте к себе в папку базу данных

Р:\Базы данных\ Самостоятельная работа №1

2. Создайте для таблицы Заказы следующие запросы:

1.  Запрос Получатель:

• поля – Дата размещения, Дата отгрузки, Получатель, Город получателя,  Адрес получателя;
• условие: Дата размещения –1 квартал 2006 года;
• параметр – Город получателя.

Примечание: 1 квартал 2006 года – это январь, февраль, март.

2.  Запрос Важные:

• поля – Код, Дата размещения, Получатель;
• условие: только важные.

Примечание: для логического типа  Условие отбора:  

если поле имеет вид  ☑ – Да, иначе (□) – Нет.

3. Создайте для таблицы Товары следующие запросы:

1.  Запрос Категория:

• поля – Наименование, Стандартная стоимость, Цена по прейскуранту, Желаемый запас, Категория;
• параметр – Категория;
• условие: только те, для которых поставки прекращены;
• сортировка: Желаемый запас – по возрастанию.

2.  Запрос Стоимость:

• поля – Наименование, Цена по прейскуранту, Категория;
• параметр: те товары, для которых Цена по прейскуранту меньше …. ;
• сортировка: Наименование – по убыванию.

§ 8. КОНТРОЛЬ ВВОДА ДАННЫХ

1. Мастер подстановок.

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

• откройте таблицу в Режиме Конструктор;
• выберите Тип данных – Мастер подстановок.

Корректировка данных, введенных с использованием Мастера подстановок, возможна на вкладке Подстановка свойств поля: строки Источник строк и Ограничиться списком.

2. Значение по умолчанию. 

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

3. Условие на значение.

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

Например, для поля Месяц: >0 And <13

4. Маска ввода. 

Установить ограничения на формат вводимых данных можно с использованием Маски ввода, которая обеспечивает соответствие данных определенному формату (например: 678-76-765) и автоматически вводит постоянные символы, такие как дефис, косая черта и т.д.

Чтобы задать для поля маску ввода:

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

непосредственно введите Маску ввода на вкладке Общие или щелкните по кнопке Построения (…) и постройте маску с помощью Мастера масок ввода.

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

Примечание: Мастер масок ввода предназначен только для полей типа Текстовый и Дата/время.

Маска ввода может иметь до трех частей, отделяемых друг от друга точкой с запятой (;):

сама маска;

0 или 1 (0 – постоянные символы-разделители сохраняются вместе с маской, 1 или отсутствие значения – не сохраняются);

символ-заполнитель (стандартный символ-заполнитель – символ подчеркивание (_); при желании его можно заменить на любой другой).

Спецсимволы масок ввода

Примеры масок ввода

Практическое задание 8

Создайте БД Квартиры, состоящую из 30 записей.

Поля БД:

• улица – текст (маска: ул. );
• дом – текст (маска: №);
• количество комнат – число целое (1-4);
• общая площадь – число дробное (20-200) м2;
• площадь кухни – число дробное (5-20) м2;
• этаж – число целое (1-10);
• телефон – логическое (да/нет);
• балкон – текст (Б,Л,НЕТ; других вариантов быть не может);
• год постройки – число целое (1900-2006);
• цена – денежный (20 000 – 100 000) евро.

§ 9. ИТОГОВЫЕ ЗАПРОСЫ. ГРУППОВЫЕ ОПЕРАЦИИ В ЗАПРОСАХ

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

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

• Sum – вычисляет сумму значений, содержащихся в заданном поле запроса;
• Avg – вычисляет среднее арифметическое набора значений, содержащихся в поле запроса;
• Min – вычисляет минимальное из значений, содержащихся в заданном поле запроса;
• Max – вычисляет максимальное из значений, содержащихся в заданном поле запроса;
• Count – вычисляет количество записей (не учитываются при подсчете записи, имеющие значения Null).

В запрос включают два поля:

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

Дополнительно можно включить еще поля для определения условий запроса (сняв с них флажок отображения поля в запросе).

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

Создание итогового запроса с помощью Конструктора

• введите в бланк запроса необходимые поля;
• щелкните по кнопке Итоги  в группе Показать или скрыть на вкладке Работа с запросами/Конструктор. 
  В бланке запроса появляется строка Групповая операция;
• выберите вид групповой операции для нужного поля;
• измените имя полученного поля: в режиме Конструктор, в контекстном меню выполните команду Свойства. В окне Свойства поля введите новое имя в строке Подпись.

Практическое задание 9-1

Создайте следующие запросы для БД Страны:

1. Население+религия – количество населения, исповедующего одну религию (Sum).
2. Страны+религия– количество стран, исповедующих одну религию (Count).
3. Площадь+язык – общая площадь, занимаемая народами, говорящими на одном языке (Sum).

Практическое задание 9-2

Создайте следующие запросы для БД Квартиры:

1. Кухни – средняя площадь кухни для квартир с различным числом комнат.
2. Средняя цена – средняя цена квартир с различным числом комнат.
3. Минимальная цена – минимальная цена квартир с различным числом комнат.
4. Количество – количество квартир с различным числом комнат.

Практическое задание 9-3

Создайте следующие запросы для БД Мосты:

1. Средняя длина – средняя длина мостов различных типов (тип моста – параметр).
2. Количество мостов – количество мостов различных типов (тип моста – параметр).

§ 10. ЗАПРОСЫ С ВЫЧИСЛЕНИЯМИ

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

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

Создание вычисляемого поля:

• введите в свободный столбец бланка запроса имя нового поля и ДВОЕТОЧИЕ;
• после двоеточия напишите формулу для расчета вручную или воспользуйтесь Построителем выражений  (кнопка Построитель в группе Настройка запроса).

В формулах:

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

Пример:

Итого: [Заказано]![Цена]* [Заказано]![Количество]

Примечания: 

1. При работе с Построителем выражений автоматически появляется текст «Выражение». Этот  текст нужно удалить!

2. Комбинация клавиш [Shift]+[F2] позволят увидеть вычисляемое поле целиком в отдельном окне.

Построитель выражений

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

Левая область содержит список всех источников данных для запроса. Ими могут быть объекты БД: таблицы, запросы, формы и т.д. Кроме того, в левый список включены папки, содержащие функции, константы и операторы, которые могут быть использованы в выражении.

Средняя область служит для отображения элементов, входящих в выбранный объект из левой области.

Правая область служит для выбора самых примитивных объектов.

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

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

Дополнительно выполнить во всех заданиях:

В вычисляемых полях установите в свойствах поля (контекстное меню) фиксированный формат данных с 2-мя десятичными знаками.

Практическое задание 10-1

Для БД Мосты создайте запрос Ярды (поля: название, длина; добавьте поле Длина пролета в ярдах (1 ярд = 0,9144 м)).

Практическое задание 10-2

Для БД Страны создайте запрос Плотность (все поля; добавьте поле Плотность населения на квадратный километр).

Практическое задание 10-3

Для БД Квартиры создайте запросы:

1. Стоимость в рублях (все поля, добавьте поле Стоимость квартиры в рублях (1 евро = 34,87 руб.));
2. Коэффициенты (поля: улица, дом; добавьте поля К1, К2).

Коэффициент К2 определяется с использованием математической функции округления Round («number»; «precision»), 

        где         «number» – выражение, которое округляется;

                «precision» – точность (количество знаков после запятой).

§ 11. Самостоятельная работа

Практическое задание 11

Создайте базу данных «Туристическое агентство».

Таблица «Туры»

Поля:

Страна (одна из четырех).

Название отеля.

Количество дней.

Стоимость 1 дня.

Стоимость перелета.

Наличие (да-нет).

Запросы:

№1        Количество туров по странам, которые есть в наличии.

№2        Цена туров (поля: страна, отель, количество дней, цена). Цена определяется как стоимость перелета + стоимость проживания (зависит от числа дней) + 15% от полученной суммы перечисление агентству.  Сортировка по странам.

§ 12. ФОРМЫ

Форма – это объект базы данных, который можно использовать для ввода, изменения или отображения данных из таблицы или запроса.

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

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

Создание формы

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

На вкладке Создание в группе Формы выберите нужную команду- кнопку:

• форма – ввод данных по одной записи за раз; создается по одной таблице или запросу и содержит все поля данного объекта;
• разделенная форма – в верхней части формы отображается таблица, в нижней – форма для ввода данных;
• несколько элементов – форма, в которой записи отображаются в виде таблицы;
• пустая форма; поля нужно  добавлять из списка;
• другие формы ➔ мастер форм; предоставляется больше возможностей для управления процессом создания формы: можно выбирать таблицы и включаемые поля, можно выбирать последовательность следования полей;
• конструктор форм: создание формы в режиме Конструктор возможно, но очень трудоемко; лучше в этом режиме форму дорабатывать: редактировать и форматировать.

Режимы создания формы

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

Режим конструктора. В этом режиме форма не выполняется. Однако этот режим позволяет более подробно просмотреть структуру формы.

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

Практическое задание 12-1

Для БД Страны: 

• создайте форму Ввод данных и измените ее дизайн;
• используя форму, добавьте в таблицу записи еще о двух странах:

Практическое задание 12-2

Используя Мастер форм, создайте форму для БД Квартиры в соответствии с образцом.

Практическое задание 12-3

Для БД Туристическое агентство: 

• создайте форму Новый тур и измените ее дизайн;
• разместите на форме рисунки (Р:\Базы данных\Туристическое агенство);
• используя форму, добавьте в таблицу записей еще о пяти турах.

§ 13. САМОСТОЯТЕЛЬНАЯ РАБОТА. ЧАСТЬ 1

1. Создайте БД Винчестеры со следующими полями:

2. Создайте форму для ввода информации:

3. Заполните таблицу данными:

4. Создайте запрос с параметром о винчестерах заданного объема (в запросе отобразите все поля). Название запроса – Винчестеры заданного объема.

5. Создайте итоговый запрос о количестве имеющихся винчестеров различных моделей. Название запроса – Количество винчестеров.

6. Создайте запрос с вычисляемым полем К (в запросе отобразите поля: объем, производитель и модель и только винчестеры, имеющие гарантию 12 месяцев):

   К= Объем* (Кэш-4)/ Цена  

Установите для коэффициента К фиксированный формат с 2-мя десятичными знаками. Название запроса – Параметрический коэффициент.

§ 14. САМОСТОЯТЕЛЬНАЯ РАБОТА. ЧАСТЬ 2

1. Создайте БД Сервизы со следующими полями:

2. Создайте форму (в столбец) для ввода данных.

Рисунок можно взять из файла Сервиз.jpg

3. Заполните таблицу данными:

4. Создайте запрос с параметром о сервизах заданной комплектации (в запросе отобразите все поля). Название запроса – Сервизы заданной комплектации.

5. Создайте итоговый запрос о средней стоимости сервизов различного типа. Название запроса – Средняя стоимость.

6. Создайте запрос с параметром о сервизах, имеющихся в наличии (в запросе отобразите поля: Название, Тип и Стоимость). Проведите сортировку по полю Стоимость. Параметр – материал. Название запроса – Сервизы в наличии.

7. Создайте запрос с вычисляемым полем К (в запросе отобразите поля: Тип, Производитель и Материал и только сервизы, имеющиеся в наличии):

К = Стоимость  / Комплектация 

Установите для коэффициента К фиксированный формат с 3-мя десятичными знаками. Название запроса – Параметрический коэффициент.

§ 15. Отчеты

Отчеты – объекты для вывода информации на печать. Отчет позволяет наглядно отобразить данные таблиц и запросов.

Для работы с отчетом имеются режимы: Представление отчета, Предварительный просмотр, Режим макета и Конструктор.

Основные разделы отчета

Заголовок отчета – элементы, размещенные в этом разделе, видны только в начале отчета.

Верхний колонтитул –  элементы, размещенные в этом разделе, видны в начале каждой страницы.

Область данных – размещены данные отчета.

Нижний колонтитул –  элементы, размещенные в этом разделе, видны в конце каждой страницы.

Примечание отчета – элементы, размещенные в этом разделе, оказываются в конце отчета, но перед элементами нижнего колонтитула.

Создание отчета

1. Команда Отчет  - самый быстрый способ создания отчета: отчет формируется на основе текущей таблицы или запроса сразу же, без дополнительных вопросов. В сформированном отчете представлены все записи базовой таблицы или запроса. Готовый отчет можно изменить в Режиме макета или Конструктора в соответствии с поставленной задачей.

• Откройте таблицу (или запрос), на основе которой нужно создать отчет.
• На вкладке Создание в группе Отчеты выберите кнопку Отчет . Созданный отчет отобразится в Режиме макета.

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

• На вкладке Создание в группе Отчеты выберите Мастер отчетов.
• Следуйте указаниям на страницах Мастера отчетов. На последней странице нажмите кнопку Готово.

Доработка отчета в Режиме макета

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

При работе с отчетом в режиме макета доступны кнопки вкладок Работа с макетами отчетов \Формат, Упорядочить и Параметры страницы.

Доработка отчета в режиме Конструктора

Доработать отчет можно также в режиме Конструктора. Новые элементы управления и поля добавляются в отчет путем их размещения их в сетку Конструктора.

При работе с отчетом в режиме Конструктора доступны кнопки вкладок Инструменты конструктора отчетов \Конструктор, Упорядочить и Параметры страницы.

Практическое задание 15-1

Создайте отчеты для БД Винчестеры на основании таблицы данных. Измените стандартный дизайн.

1. Отчет Винчестеры; все сведения о винчестерах.
2. Отчет Винчестеры-мастер: мастер отчетов; поля – объем, производитель, цена.

Практическое задание 15-2

Создайте отчеты для БД Винчестеры на основании запросов. Измените стандартный дизайн.

1. Отчет Винчестеры заданного объема с определением суммарного количества винчестеров (Итоги). Используйте одноименный запрос с параметром.
2. Отчет Параметрический коэффициент. Используйте одноименный запрос.

Практическое задание 15-3

Создайте отчеты для БД Сервизы  на основании таблицы данных. Измените стандартный дизайн.

1. Отчет Сервизы; все сведения о сервизах.
2. Отчет Сервизы-мастер: мастер отчетов; поля – название, тип, материал, стоимость.

Практическое задание 15-4

Создайте отчеты для БД Сервизы на основании запросов. Измените стандартный дизайн.

1. Отчет Сервизы заданной комплектации с определением средней цены сервиза (Итоги). Используйте одноименный запрос с параметром.
2. Отчет Сервизы в наличии. Используйте одноименный запрос с параметром.
3. Отчет Параметрический коэффициент. Используйте одноименный запрос.

§ 16. СВЯЗЫВАНИЕ ТАБЛИЦ

Напомним:

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

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

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

• один-ко-многим: каждой записи в одной таблице (главной) соответствует несколько записей в другой таблице (подчиненной). Ключевое поле главной таблицы называется первичным ключом, а ключевое поле в подчиненной таблице – внешним ключом;

• один-к-одному: каждой записи в одной таблице соответствует только одна запись в другой таблице. Ключевые поля обеих таблиц – первичные ключи;
• многие-ко-многим: каждой записи в одной таблице соответствует несколько записей в другой таблице и наоборот. Таблицы связываются между собой через третью таблицу, используя связь типа один-ко-многим.

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

Установка ключевого поля (первичного)

в режиме Конструктор установите курсор на поле будущего первичного ключа;

щелкните по кнопке Ключевое поле  на Панели инструментов Конструктор таблиц или воспользуйтесь контекстным меню.

Установка связи между таблицами

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

• задайте первичный ключ в главной таблице;
• откройте подчиненную таблицу в режиме Конструктор;
• выберите в подчиненной таблице для вторичного ключа тип – Мастер подстановок:

• выберите вариант Объект «столбец подстановки» будет использовать значение из таблицы или запроса;
• выберите таблицу, содержащую подставляемые значения;
• выберите в столбец подстановки нужные поля;
• при необходимости можно задать порядок сортировки списка.

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

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

Практическое задание 13

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

Таблица Авторы:

1. КодАвтора (ключевое поле) – счетчик.
2. Фамилия – текстовый.
3. Имя – текстовый.
4. Отчество – текстовый.
5. ГодРождения – числовой, ЦЕЛОЕ, в интервале 1700-2000.
6. Фото – поле объекта Вложение.

Таблица Книги:

1. КодАвтора (внешний ключ) – применить Мастер подстановок.
2. Название – текстовый.
3. ТипОбложки (мягкая, твердая, подарочная – Мастер подстановок).
4. Аннотация – поле Мемо.
5. Цена – денежный.
6. ГодИздания – числовой, ЦЕЛОЕ, в интервале 1700-2007.

Заполните таблицы: 5 любых авторов и не менее трех книг для каждого автора. Фотографии можно взять в папке Портреты.

Примечание: Для размещения фотографии в поле типа Вложение:

• установите курсор в ячейку и вызовите контекстное меню и выберите команду Управление вложениями.
• найдите нужный объект, используя кнопку Добавить.

Фотография будет видна только в объектах Форма и Отчет.

§ 17. СОЗДАНИЕ ФОРМ, ЗАПРОСОВ И ОТЧЕТОВ НА БАЗЕ НЕСКОЛЬКИХ ТАБЛИЦ

Запросы

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

Формы

• используйте режим Мастер;
• в окне Создание форм ПОСЛЕДОВАТЕЛЬНО выберите таблицы или запросы и необходимые поля;
• если поля выбираются из разных таблиц, то возникают подчиненные или связанные формы.

Отчеты

• используйте режим Мастер;
• в окне Создание отчетов ПОСЛЕДОВАТЕЛЬНО выберите таблицы или запросы и необходимые поля.

Практическое задание 17-1

Создайте ФОРМЫ для БД Библиотека. 

1. Каталог авторов (все сведения из таблицы Авторы).
2. Каталог книг (все сведения из таблицы Книги).
3. Библиотека (все сведения об авторах и их книгах).
   ! Выберите вариант – подчиненная форма.

Практическое задание 17-2

Создайте ОТЧЕТЫ для БД Библиотека.  

1. Книги библиотеки (на основании таблиц). Поля: фамилия автора, имя автора, название книг. Сгруппируйте отчет по полю Фамилия.
2. Все об авторе (на основании запроса с параметром) – сведения о конкретном авторе. Поля: фамилия, имя, отчество, год рождения, фото.
3. Книги в оформлении (на основании запроса с параметром) – сведения о книгах и их авторах, в указанном оформлении (переплете). Поля: фамилия, имя, название, тип обложки, год издания, цена.
4. Книги автора (на основании запроса с параметром) –сведения о книгах конкретного автора с итоговой стоимостью книг. Поля: фамилия, имя, название, год издания, цена.
5. Стоимостью выше (на основании запроса с параметром) – сведения о книгах и их авторах, стоимостью свыше заданной. Поля: фамилия, имя, отчество, название, год издания, тип обложки, цена.
6. Изданы в период (на основании запроса с параметром) – сведения о книгах и их авторах, изданных в указанный период времени. Поля: фамилия, имя, название, год издания.

§ 18. САМОСТОЯТЕЛЬНАЯ РАБОТА. БД АВТОПРЕДПРИЯТИЕ

1. Создайте структуру таблиц БД (таблицы не заполняйте!).
2. Свяжите таблицы с помощью Мастера подстановок по полю КодАвто, обеспечив целостность данных.
3. Создайте удобную форму для заполнения данных об автомобилях (без поля Код авто). Заполните таблицу Автомобили данными.
4. Создайте подчиненную форму для заполнения данных о поездках. Форму создавайте на основе запроса (марка авто, пункт назначения, расстояние, дата поездки, категория поездки). Заполните таблицу Поездки данными (20 записей).

Таблица Автомобили

Таблица Поездки

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

5. Создайте отчеты для БД Автопредприятие.

1. Все рейсы: рейсы, выполненные всеми автомобилями автопредприятия (поля: дата, пункт назначения, расстояние). Подсчитайте суммарный пробег для каждого автомобиля.
2. Рейсы автомобиля: рейсы, выполненные конкретным автомобилем автопредприятия (поля: дата, пункт назначения, расстояние). Подсчитайте суммарный пробег автомобиля.

Рекомендация: предварительно создайте запрос с параметром.

3. Расход топлива: расход топлива на рейсы, выполненные конкретным автомобилем автопредприятия (поля: дата, пункт назначения, расстояние, затраты топлива). Подсчитайте суммарный пробег автомобиля и общий расход топлива.

Затраты определите по формуле C= S/100*P*20,5*2.

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

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

Стоимость поездки определите по формуле C= S/100*P*20,5*2*К.

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

5. Поездки за период: поездки в заданный период времени совершенные всеми автомобилями (поля: автомобиль, дата, пункт назначения).

Рекомендация: предварительно создайте запрос с параметром.

§ 16. СОЗДАНИЕ МЕЖТАБЛИЧНЫХ СВЯЗЕЙ МНОГИЕ-КО-МНОГИМ

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

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

Практическое задание 16.

База данных Подписка 

1. Создайте структуру трех таблиц.

Таблица Издания

Таблица Подписчики

Связывающая таблица Связь данных

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

3. Заполните таблицу Издания (5 изданий).

4. Заполните таблицу Подписчики (20), одновременно заполняя данные об изданиях для подписчика в таблице Связь данных.

§ 17. Практикум

База данных Подписка 

1. Создайте отчет Список рассылки (поля: название издания, фамилия, адрес и телефон подписчика).

Сгруппируйте отчет по названию издания.

Рекомендация. Предварительно создайте запрос.

2. Создайте отчет К оплате (поля: фамилия подписчика, льгота, название издания, срок подписки, стоимость подписки с учетом льготы -50%).

Подсчитайте итоговую сумму к оплате для каждого подписчика.

Рекомендации:

предварительно создайте запрос с вычисляемым полем

К оплате = (Стоимость + Льгота*0,5*Стоимость)*Срок подписки

Обратите внимание, что значения логического поля соответствуют следующим значениям: ДА (-1) и НЕТ (0).

при создании отчета задайте группировку по полям Фамилия и Льгота.

§ 18. КНОПОЧНАЯ ФОРМА  

Для исключения возможность несанкционированного доступа к объектам БД и создания удобной среды для пользователя применяется кнопочная форма.

С помощью  кнопочной формы возможна работа только с формами, отчетами, макросами и пр. Доступ непосредственно к таблицам и запросам БД из Кнопочной формы невозможен. БД может содержать несколько кнопочных форм.

Создание кнопочной формы

Кнопочную форму проще всего создать с использованием Диспетчера кнопочных форм:

выполните команду Сервис / Служебные программы – Диспетчер кнопочных форм. Если кнопочная форма создается впервые, появится сообщение об этом;

в окне Диспетчер кнопочных форм щелкните по кнопке Изменить; в появившемся окне Изменение страницы кнопочной формы:

•  измените имя кнопочной формы (если необходимо);
• щелкните по кнопке Создать и в появившемся окне Изменение элемента кнопочной формы последовательно создайте элементы кнопочной формы (кнопки): задайте названия кнопок, определите действия, выполняемые при нажатии кнопок. Количество кнопок на одной форме не может быть более 8-ми.

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

Настройка запуска

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

• выполните команду Сервис / Параметры запуска;
• в поле Ввод формы/страницы выберите Кнопочная форма;
• для более полной защиты БД следует СНЯТЬ флажки   Строка состояния и Окно базы данных. В этом случае работа с объектами БД возможна ТОЛЬКО через Кнопочную форму.

Практическое задание 18

Создайте кнопочную форму для отчетов в своей курсовой работе.

Оглавление

§ 1. СПИСКИ EXCEL КАК БД        

§ 2. ФИЛЬТРАЦИЯ        

§ 3. ОСНОВНЫЕ ПОНЯТИЯ И МОДЕЛИ БАЗ ДАННЫХ        

§ 4. ТАБЛИЦЫ – ОСНОВА СУБД        

§ 5. ФИЛЬТРАЦИЯ – СРЕДСТВО АНАЛИЗА ДАННЫХ НА УРОВНЕ ТАБЛИЦЫ        

§ 6. ОТБОР ЗАПИСЕЙ С ПОМОЩЬЮ ЗАПРОСА НА ВЫБОРКУ        

§ 7. КОНТРОЛЬ ВВОДА ДАННЫХ        

§ 8. ИТОГОВЫЕ ЗАПРОСЫ. ГРУППОВЫЕ ОПЕРАЦИИ В ЗАПРОСАХ        

§ 9. ЗАПРОСЫ С ВЫЧИСЛЕНИЯМИ        

§ 10. ФОРМЫ        

§ 11. САМОСТОЯТЕЛЬНАЯ РАБОТА. ЧАСТЬ 1        

§ 11. САМОСТОЯТЕЛЬНАЯ РАБОТА. ЧАСТЬ 2        

§ 12. Отчеты        

§ 13. СВЯЗЫВАНИЕ ТАБЛИЦ        

§ 14. СОЗДАНИЕ ФОРМ, ЗАПРОСОВ И ОТЧЕТОВ НА БАЗЕ НЕСКОЛЬКИХ ТАБЛИЦ        

§ 15. САМОСТОЯТЕЛЬНАЯ РАБОТА. БД АВТОПРЕДПРИЯТИЕ        

§ 16. СОЗДАНИЕ МЕЖТАБЛИЧНЫХ СВЯЗЕЙ МНОГИЕ-КО-МНОГИМ        

§ 17. Практикум        

§ 18. КНОПОЧНАЯ ФОРМА