extracted_text:
КОНЦЕПЦИЯ БАЗ ДАННЫХ В ACCESS

Модель данных Access

Access представляет собой систему
управления базой данных (СУБД) офисного типа (т. н. xBase СУБД) и может
представлять интерес для начинающего пользователя благодаря сравнительной
простоте теоретических концепций и практической работы по проектированию баз
данных. Кроме того, Access является разработкой фирмы Microsoft и представляет
собой развивающуюся систему. Все это обусловливает широкое распространение
этого популярного Windows-приложения.
Начинающий пользователь должен знать, что
работа с Access выполняется на этапе реализации (рабочего проектирования)
системы базы данных. В таком контексте Access представляет собой
инструментальную среду, в которой реализуются результаты предшествующих этапов
проектирования. Иными словами, не с Access начинается разработка базы данных, и
начинающий пользователь должен знать, в какой момент потребуются
соответствующие знания и навыки.
Основным интегрированным объектом Access
является база данных.
База данных (БД) – это организованная на машинном носителе
совокупность взаимосвязанных данных, которая представляет сведения об объектах
определенной предметной области (ПО), их свойствах и связях между ними.
Используемым типом структуры данных
является реляционная модель данных (РМД). Термин «реляционная»
происходит из теории множеств теоретической основы баз данных, где обозначает
специфический тип структуры данных, называемый отношением (отношение,
по-английски relation, определяет название модели). Как тип структуры
данных отношение допускает наглядное представление данных в виде двумерной
таблицы.
Основным элементом структуры БД в Access
является таблица, с помощью которой моделируются объекты и связи между
ними. Следовательно, БД в Access представляет собой совокупность взаимосвязанных
двумерных таблиц.
Структура реляционной таблицы задается её заголовком и определяется составом и
последовательностью полей-атрибутов, моделирующих свойства объектов и
связей.
Содержание таблицы заключено в её строках.
Каждая строка содержит данные об одном экземпляре объекта или связи и
называется записью.
Для однозначного определения каждой записи
(экземпляра объекта) таблица должна иметь уникальный ключ (первичный ключ).
Ключ может состоять из одного или нескольких полей.
Таблицы в БД взаимосвязаны. Связь каждой
пары таблиц, в которой одна является «отцом», а другая – «сыном»,
обеспечивается ключом связи (внешним ключом). Внешний ключ – это
первичный ключ таблицы-отца, мигрировавший в таблицу-сына. Связи между
таблицами могут быть двух типов: «один к одному» (1:1) или «один ко многим»
(1:N).
Структура реляционной БД задается схемой
данных. Схема данных включает в себя определение структуры каждой таблицы и
определение связей между таблицами. Схема данных в Access создается специально
для использования связей между таблицами при обработке данных из разных таблиц,
а также для обеспечения целостности БД.

Лабораторная работа №1. СОЗДАНИЕ БАЗЫ ДАННЫХ

Основы технологии создания базы данных

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

Задание 1. В своей
рабочей папке создайте файл базы данных с именем «УЧЕБА».
Файл БД может содержать спецификации до 7
типов объектов-компонентов.
1. Таблицы, которые содержат хранимые
данные.
2. Запросы, предназначенные для выборки
или изменения данных одной или нескольких связанных таблиц.
3. Формы – экранные формы, предназначенные
для ввода и просмотра взаимосвязанных данных в удобном для пользователя БД
виде.
4. Отчеты – шаблоны печатных документов
для вывода данных в удобном для пользователя БД виде.
5. Страницы – средства доступа к данным
через Internet.
6. Макросы – средства автоматизации работы
с БД.
7. Модули – компоненты прикладной программы
автоматизации работы с БД, составленные на языке Visual Basic.

Объекты базы данных

Таблицы и запросы представляют собой сами
данные в виде реальных таблиц, т.е. хранимых данных, или в виде виртуальных
таблиц, которые представляются пользователю БД как реально существующие.
Формы и отчеты представляют собой средства
разработки интерфейса конечного пользователя БД.
Страницы – средства доступа к данным через
Internet.
Макросы и модули – это средства
автоматизации работы с БД в эргономической среде пользователя.
Работа с БД может быть прервана в любой
момент путем закрытия окна
БД или файла БД (по команде Файл | Закрыть)
и продолжена путем открытия файла БД (по команде Файл | Открыть).
Продолжение работы с БД всегда начинается
с окна БД (рис. 1). Для работы с тем или иным объектом БД нужно выбрать
соответствующую закладку и нажать экранную кнопку:
– для просмотра результата проектирования
объекта;
– для изменения структуры объекта;
 – для создания объекта «с нуля».

Рис. 1. Окно базы данных Access

ПРОЕКТИРОВАНИЕ ТАБЛИЦ

Разработка структуры таблицы

Основы технологии формирования
структуры таблицы
Таблицы БД могут быть созданы одним из
следующих способов:
1) в среде Access в режиме конструирования
«с нуля»;
2) как результат запроса того или иного типа
к уже созданным таблицам;
3) в результате импорта таблиц в форматах
других сред.

Рассмотрим более подробно основной первый
способ.
Создание таблицы «с нуля» выполняется в
два этапа. Сначала разрабатывается её структура. Затем выполняется наполнение таблицы
необходимой информацией.
При нажатии кнопки открывается окно «Новая
таблица» с предложением выбрать способ создания новой таблицы. Наиболее
актуальными являются:
- Мастер таблицы – для формирования
структуры таблицы из полей заданных тематических таблиц;
- Конструктор – для создания
таблицы «с нуля» (основной способ).
При работе под управлением Конструктора
формирование структуры новой таблицы выполняется в диалоговом окне (рис. 2).
Для каждого поля в соответствующем столбце предлагается указать:
1) имя поля (набирается вручную в
столбце «Поле», можно по-русски);
2) тип данных (выбирается из
списка);
3) описание-комментарий (не
обязательно);
4) свойства (в зависимости от типа
данных).

Рис. 2 Окно формирования
структуры таблицы

Многообразие типов данных,
предопределенных в Access (см. таблицу 1), определяет разнообразие информации,
которую можно хранить в БД.

Таблица 1
Типы данных

Типы данных

Диапазон значений

Размер
в байтах

1

2

3

Текстовый

0 ÷ 255

1

Числовой
  (байт)

0 ÷ 255

1

Числовой
  (целое)

-32768 ÷ 32767

2

Числовой
  (длинное целое)

-2147483468 ÷ 2147483467

4

Числовой
  (с плав. точкой)

-3.402823 1038 ÷ 3.402823 1038

4

Числовой
  (двойной точности)

-1.797693134862232 10308 ÷
1.797693134862232 10308

8

Логический

Да / Нет

Дата/Время
  100 ÷ 9999 гг.

8

Продолжение таблицы 1

1

2

3

Денежный

15,4

8

Счетчик

4

Поле
  MEMO

64000

OLE-объект

109

Гиперссылка

64000

Большинство типов данных в
комментариях не нуждаются. Поясним некоторые из них.
Тип «Счетчик» может быть присвоен не более
чем одному полю.
Данные этого типа являются уникальными
номерами записей и не могут быть изменены или удалены.
Тип «Денежный» используется для описания
данных, над которыми выполняются финансовые операции с высокой точностью
вычисления. Точность представления данных этого типа составляет до 15 знаков в
целой части и 4 – в дробной.
Тип «поле MEMO» используется для полей,
содержащих пространную текстовую информацию.
Тип «поле объекта OLE» присваивается
полям-объектам других приложений Windows (рисунок, фотография, звуковая запись,
анимация и т. д.).
Тип «Гиперссылка» используется для полей,
содержащих адрес удаленного источника данных.
В нижней части окна на закладке «Общие»
могут быть заданы свойства полей, которые зависят от типа данных:
1) размер поля (для числовых полей
возможна детализация);
2) формат поля (внешняя форма
представления данных) при помощи специальных символов форматирования;
3) маска ввода в виде символов,
управляющих вводом данных;
4) подпись – псевдоним поля;
5) значение по умолчанию;
6) условие на значение в виде
ограничений на допустимые значения поля;
7) сообщение об ошибке,
сопровождающее нарушение условия на значение;
8) обязательное поле – поле,
которое не может принимать не определенное значение (т.н. Null-значение);
9) индексированное поле – поле, по
которому предполагается упорядочение строк таблицы.
Несколько слов об управлении вводом и
отображением данных таблиц. В Access существует два свойства полей,
использование которых приводит к  похожим результатам: свойство формат поля и
свойство маска ввода.
Свойство формат поля используется
для отображения данных и влияет только на отображение значения, но никак не
влияет на значение, хранимое в таблице. Формат отображения применяется только
после сохранения введенных данных. Если вводом данных необходимо управлять, в
дополнение к формату отображения данных или вместо него используется маска
ввода. Если требуется, чтобы данные отображались так, как они были введены,
свойство  формат поля можно не устанавливать.
Маска ввода обеспечивает соответствие
данных определенному формату, а также заданному типу значений, вводимых в
каждую позицию. Маска ввода является удобным средством контроля вводимых
значений.
Если для поля определены как формат
отображения, так и маска ввода, то при добавлении и редактировании данных
используется маска ввода, а формат поля определяет отображение данных после
сохранения записи. При этом необходимо следить за тем, чтобы маска и формат не
противоречили друг  другу. Управление вводом осуществляется при помощи символов
маскирования, которые выполняют специфические функции (см. таблицу 2).
Таблица 2
Управляющие символы маски ввода

Теоретические предпосылки создания
таблиц базы данных
Реализации проекта базы данных
предшествует большая аналитическая работа, которая выполняется на этапах
инфологического и даталогического проектирования. На основании анализа
семантики данных предметной области разрабатывается ее информационная модель,
которая затем отображается в компьютерную среду с учетом структурных и
процедурных возможностей СУБД реляционного типа. Разработка структуры БД, в том
числе разработка структуры каждой таблицы, установление зависимостей между
атрибутами, назначение ключевых атрибутов, установление связей между таблицами,
является результатом продолжительной аналитической работы. Не нужно забывать,
что Access – это только инструментальная среда для реализации информационной
модели ПО и что обращаться к ней следует лишь тогда, когда проработаны
особенности структуры данных ПО.
В качестве примера рассмотрим упрощенный
вариант ПО, называемой «УЧЕБА». Допустим, что для спецификации этой ПО в
упрощенном варианте достаточно следующих атрибутов-свойств:
номер студента - учётный номер студента, который однозначно
идентифицирует его персону;
фамилия - фамилия и инициалы студента;
специальность - специальность, по которой обучается студент;
факультет - факультет, на котором учится студент;
предмет - учебная дисциплина, по которой студент в семестре
сдает экзамен и получает оценку;
семестр - учебный семестр, по итогам которого студент сдает
экзамен и получает оценку;
оценка - оценка за экзамен.
В реляционном представлении такой ПО можно
поставить в соответствие «универсальное» отношение со схемой:
Учёба (номер студента, фамилия,
специальность, факультет, предмет, семестр, оценка)
здесь подчеркнуты ключевые атрибуты.
Допустим также, что по условиям
функционирования ПО УЧЕБА справедливы следующие семантические условия и
соответствующие им функциональные (однозначные) зависимости (ФЗ) между
атрибутами:
1. Учетный номер студента, а не его
фамилия, является уникальной характеристикой студента (т.е. допускаются
однофамильцы):
номер студента -> фамилия
2. Каждый студент учится только по одной
специальности:
номер студента -> специальность
3. Каждый студент учится только на одном
факультете:
номер студента -> факультет
4. По каждой специальности готовят
специалистов только на одном факультете:
специальность -> факультет
5. Каждый студент (номер студента) по
учебному предмету в семестр может получить лишь одну итоговую оценку:
(номер студента, предмет, семестр) -> оценка
Схема ФЗ ПО УЧЕБА приведена на рис. 4.

Рис. 4. Схема функциональных зависимостей ПО УЧЕБА

Основы технологии работы с таблицами
Работа с таблицей в Access может
выполняться в двух режимах:
1. Режим Конструктор используется
при создании новой таблицы или при изменении структуры уже созданной таблицы.
Начало работы в окне БД – по экранным кнопкам или  (рис. 1) на закладке Таблицы.
2. Режим таблицы используется для
добавления, удаления, изменения и просмотра данных в таблице. Начало работы в
окне БД – по экранной кнопке  (рис. 1) на закладке Таблицы.
Переход из одного режима в другой может
выполняться:
1) по командам Вид | Конструктор
и Вид | Режим таблицы главного меню;
2) нажатием экранных кнопок на панели
инструментов конструктора таблиц:
  –  режим конструктора;
  –  режим таблицы.

Задание 2. После анализа ПО можно приступить к реализации проекта
в среде Access:
1. В БД УЧЕБА создайте в режиме Конструктора
таблицу с именем «Учёба». При разработке  её  структуры  воспользуйтесь  спецификацией
 полей атрибутов (см. таблицу 3).
2. По завершении спецификации структуры
новой таблицы выполните команду Файл | Сохранить как… и присвойте
таблице имя «Учеба».
Таблица 3
Спецификация полей-атрибутов отношения «Учёба»

3. Задайте в окне разработки структуры
таблицы ключевые поля по следующему алгоритму:
1) удерживая клавишу Ctrl в нажатом
состоянии, отметьте мышью в колонке слева от названия полей все ключевые поля
(они должны выделиться цветом);
2) выполните команду Правка | Ключевое
поле главного меню (в колонке слева от имени поля должно появиться
изображение ключа).
По окончании формирования спецификация
структуры таблицы «Учёба» должна выглядеть так, как показано на рис. 3.

НАПОЛНЕНИЕ И РЕДАКТИРОВАНИЕ ТАБЛИЦЫ

Пока таблица не заполнена данными, можно смело
редактировать  её структуру в режиме Конструктора:
1. Добавить новое поле:
1) выделить мышью в колонке слева от имени
поля строку таблицы структуры;
2) выполнить команду Вставка | Строки.
2. Удалить поле:
1) выделить мышью в колонке слева от имени
поля строку таблицы структуры;
2) выполнить команду Правка | Удалить
строки.
3. Переименовать поле.
4. Переопределить тип поля.
5. Определить ключ.
Если таблица уже заполнена данными,
редактирование её структуры может повлечь за собой потерю данных. Например,
если при изменении типа данных конкретное значение невозможно преобразовать к
новому типу данных, то оно удаляется и заменяется во всех содержащих это
значение полях на пустое значение. Например, если поле преобразуется из типа «Текстовый»
в тип «Числовой», то будет удалено любое значение, содержащее одну или
несколько букв. При этом выводится предупреждение о числе удаляемых значений, и
пользователь имеет возможность отменить преобразование.
Наиболее часто встречающиеся преобразования
типов данных можно представить следующими четырьмя категориями:
1)
преобразование в текст других
типов данных;
2)
преобразование текстовых данных в
числовые, денежные, логические форматы или в формат дата/время;
3)
преобразование денежного типа
данных в числовой, текстового в поле MЕМО и наоборот;
4)
изменение настройки свойства Размер
поля для числовых полей.

Задание 3. Наполните таблицу «Учёба» содержательной информацией
(табл. 4):

Таблица 4
Информация для ввода в таблицу «Учёба»

1) перейдите в Режим таблицы.
Обратите внимание на контекстное изменение главного меню. В нем появились
пункты «Формат» (для изменения размеров полей таблицы) и «Записи» (для работы с
записями таблицы);
2) заполните шаблон таблицы «Учёба»
данными (табл. 4). Обратите внимание, что расположение записей в таблице при
вводе определяется не хронологией ввода, а условиями упорядочения «по ключу» (в
порядке возрастания ключа);
3) по завершении ввода сохраните
результаты по команде Файл | Сохранить главного меню.
В Режиме таблицы можно выполнить
простейшие интерактивные (без запоминания условий) запросы-выборки:
1. Поиск записи по заданному значению поля
(выполняется по команде Правка | Найти).
2. Сортировка записей по значению поля
(выполняется по команде Записи | Сортировка).
3. Выборка по условию (выполняется по
команде Записи | Фильтр).

Лабораторная работа №2. ФОРМИРОВАНИЕ ЗАПРОСОВ

Спецификация запросов

База данных представляет собой
информационную модель ПО и предназначена для удовлетворения информационных
запросов пользователей, работающих в этой ПО. После того как БД будет наполнена
содержательной информацией, к ней можно обращаться с запросами. Запрос представляет
собой спецификацию условий манипулирования данными, в результате которого
создается то или иное представление о хранимой в БД информации. БД является
объектом коллективного пользования, и каждый запрос выражает индивидуальное,
локальное представление пользователей о структуре информации моделируемой ПО.
Запросы к БД можно разделить на две
категории.
1. Запросы-выборки осуществляют
только извлечение информации из одной или нескольких связанных таблиц и
представление её в виде виртуальной (не существующей на самом деле) таблицы.
Структура такого рода таблицы определяется локальным представлением о ПО
конечного пользователя БД в контексте запроса.
2. Корректирующие запросы
или запросы на добавление, удаление и изменение данных в БД. Это весьма
значительные по своим последствиям запросы, так как их реализация приводит к
изменению хранимой в БД информации. Такого рода запросы соответствуют функциям
администрирования БД.
БД одной и той же ПО может быть
представлена в виде одной таблицы («универсального отношения») или в виде
совокупности связанных таблиц.
Для спецификации запросов необходимы
соответствующие языковые средства – языки запросов. Языки запросов – это
языки высокого уровня, не процедурные (как Паскаль, Си, Бейсик и др.), а
декларативные. Это значит, что в запросе достаточно указать, что нужно сделать,
а реализация запроса скрыта от пользователя.
В Access (как и во многих других СУБД)
используются языки запросов двух типов:
1. QBE (Query By
Example) – запрос по образцу. Запрос
формируется в режиме диалога путем заполнения таблицы специального вида. QBE-
технология дает пользователю визуальное представление достаточно сложного
запроса.
2. SQL (Structured Queries
Language) – язык структурированных запросов. Запрос формируется в терминах
команд этого языка (т. е. нужно знать язык SQL). В настоящее время
SQL-технология запросов получила широкое распространение в системах БД на
основе распределенных систем, в компьютерных сетях.

ПРОЕКТИРОВАНИЕ СТРУКТУРЫ
ЗАПРОСА

Основы технологии проектирования
запросов

Основной технологией формирования запросов
в Access является QBE- технология с возможностью отображения запроса в стиле SQL.
Работа с запросами в Access может
выполняться в трех режимах:
1. Режим Конструктора (основной)
используется при создании нового запроса или при изменении структуры уже
созданного запроса в стиле QBE.
Начало работы в окне БД – по экранным
кнопкам или  (рис. 1) на закладке Запросы.
2. Режим SQL используется для
просмотра уже созданного QBE- запроса, а также для создания нового запроса или
при изменении структуры уже созданного в стиле QBE-запроса.
3. Режим таблицы используется для
просмотра результатов запроса. Начало работы в окне БД – по экранной кнопке  (рис. 1) на закладке Запросы.
Переход из одного режима в другой может
выполняться:
1) по командам Вид | Конструктор,
Вид | Режим SQL и Вид | Режим таблицы
главного меню;
2) нажатием экранных кнопок панели
инструментов конструктора запросов:
– режим конструктора;
– режим таблицы;
– режим SQL.

Основной алгоритм построения
запросов
Формирование запроса выполняется примерно
по следующему алгоритму:
1. В окне БД выбрать закладку Запрос и
нажать экранную кнопку.
2. В окне «Новый запрос» выбрать вариант Конструктор.
3. В окне «Добавление таблицы» выбрать по
закладке источник данных к запросу: Таблицы | Запросы | Таблицы
и Запросы.
4. Выбрать в списке конкретную таблицу или
запрос и нажать экранную кнопку и в конце . Появится основное окно конструктора
запросов в стиле QBE (рис. 5).
5. В окне конструктора оформить условия
спецификации конкретного запроса (как это делается, рассмотрим позднее на
конкретных примерах типов запросов). Обратите внимание, что в контекстном меню
проявился новый пункт меню «Запрос».
6. Посмотреть результат запроса по команде
Запрос–>Запуск. На экране появится окно просмотра
данных в Режиме таблицы.
7. Вернуться в режим Конструктора и
принять решение, удовлетворяют ли вас результаты запроса или нет. При
отрицательном исходе можно продолжить формирование запроса.

Рис. 5. Окно конструктора QBE-запросов

8. Если результаты запроса вас удовлетворяют, запрос
можно сохранить по команде Файл | Закрыть или Файл | Сохранить
как, указав в диалоговом окне имя запроса, под которым он будет представлен
в списке на закладке Запросы текущей БД.
9. Для воспроизведения результата запроса необходимо
выбрать запрос по имени в списке в окне БД на закладке Запросы и нажать
экранную кнопку.
10. Для изменения структуры запроса необходимо выбрать
запрос по имени в списке в окне БД на закладке Запросы и нажать экранную
кнопку.

ЗАПРОСЫ ВЫБОРКИ

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

Задание 4. Сформируйте запрос-выборку всех полей таблицы «Учёба»
БД УЧЕБА:
1) перенесите мышью в строку «Поле»
таблицы запроса имена всех полей из списка в верхней части таблицы;
2) включите щелчком мыши по квадратикам в
строке «Вывод на экран» режим просмотра соответствующих полей – спецификация
запроса примет вид таблицы, приведенной на рис. 6;
3) завершите результаты формирования
запроса так. Результаты запроса представлены на рис. 7;
4) перейдите в SQL-режим по команде Вид
| Режим SQL, чтобы получить представление о структуре
SQL-запроса;
5) сохраните запрос под именем «Все поля».

Рис. 6. Структура QBE - запроса «Все поля»

Рис. 7. Результаты запроса «Все поля»

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

Задание 5. Сформируйте запрос-выборку некоторых полей таблицы
«Учёба» БД УЧЕБА.
1) сформируйте запрос, в котором
присутствовали бы следующие поля: «номер студента», «фамилия», «специальность»,
«факультет» (технология построения запроса такая же, как в примере «Все поля»,
отличается только количеством полей);
2) просмотрите результаты запроса.
Обратите внимание на присутствие в итоговой таблице дублирующих записей;
3) назовите запрос «Дубли».

Выборка полей без дублей записей
(проекция)

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

Задание 6. Создайте запрос на выполнение проекции исходной
таблицы:
1) сформируйте запрос, в котором
присутствовали бы следующие поля: «номер студента», «фамилия», «специальность»,
«факультет» (технология построения запроса такая же, как в предыдущем примере);
2) в режиме Конструктора выполните
команду Вид | Свойства;
3) в диалоговом окне «Свойства запроса»
переключите мышью свойство
«Уникальные значения» в состояние «Да»
(рис. 8);

Рис. 8. Спецификация свойств запроса «без дублей
записей»

4) завершите результаты формирования
запроса;
5) назовите запрос «Проекция».

Запросы на основе операции селекции

Очень распространенным типом запросов
являются запросы выборки записей-строк по условию (горизонтальная выборка).
Итоговая таблица в таком случае представляет собой результат реляционной
операции селекция.
Условия отбора записываются в строке
«Условие отбора» структуры запроса и могут состоять из литералов, констант и
идентификаторов, соединенных операторами. Условия отбора можно записывать «по
памяти» или при помощи специального инструмента, так называемого Построителя
выражений.
Доступ к этому средству осуществляется в
контексте построения условия при нажатии правой клавиши мыши и выборе пункта Построить
контекстного меню (рис. 9).

Рис. 9. Окно построителя выражений условий

Выборка по значению поля
Задание 7. Для выбора всех данных о студентах, обучающихся на
факультете «экономический»:
1) выберите все поля в структуру запроса,
как в примере «Все поля»;
2) в поле «факультет» в строке «Условие
отбора» наберите вручную значение «экономический» (рис. 10);
3) завершите результаты формирования
запроса;
4) назовите запрос «Селекция».

Рис. 10. Структура запроса-выборки по значению поля

Параметрический запрос
Значение поля в условие запроса можно
вводить не только непосредственно в шаблон структуры запроса, как в предыдущем
примере, но и динамически в диалоге в процессе выполнения запроса. Для этой
цели используется параметрический запрос.
Задание 8. Сформируйте параметрический запрос всех данных о
результатах сдачи экзаменов по предмету, заданному его названием:
1) выберите все поля в структуру запроса,
как в примере «Все поля»;
2) в поле «предмет» в строке «Условие
отбора» наберите в квадратных скобках текст подсказки к вводу
конкретного значения (рис. 11);

Рис. 11. Структура параметрического запроса

3) завершите результаты формирования
запроса;
4) в поле диалогового окна, которое
появится при выполнении запроса, введите конкретное значение, например
«математика» (рис. 12);
5) сохраните запрос под именем
«Параметрический».

Рис. 12. Диалоговое окно параметрического запроса

Выборка по условию с операторами
сравнения
В спецификации условий выборки можно
использовать операторы сравнения (=, <>, >, <, >=,
>=). Для этого в шаблоне структуры запроса в строке «Условие запроса» в
соответствующем поле указывается оператор сравнения.

Задание 9. Сформируйте запрос на выборку всех данных о студентах-младшекурсниках
(1–3 курсы) может быть формализован условием «<7» по полю «семестр»:
1) выберите все поля в структуру запроса,
как в примере «Все поля»;
2) в поле «семестр» в строке «Условие
отбора» наберите «<7»;
3) завершите результаты формирования
запроса;
4) назовите запрос «Меньше».

Выборка по условию с логическими
операторами

В спецификации условий выборки можно
использовать логические операторы (Not, And, Or и т. д.). Речь идет о реализации
сложных условий запроса. Для спецификации условия с And операнды
логического выражения должны располагаться в строке «Условие запроса» на одном
уровне в шаблоне структуры запроса. Для спецификации условия с Or операнды
логического выражения должны располагаться в строке «Условие запроса» и
последующих строках «или» на разных уровнях в шаблоне структуры запроса.

Задание 10. Выполните пример на тему And. Сформируйте запрос на
выборку всех данных о результатах сдачи экзаменов студентами факультета «Технологический»
на оценки 4 и 5 (т.е. более 3).
1) перенесите все поля в структуру запроса,
как в примере «Все поля»;
2) в поле «факультет» в строке «Условие
отбора» наберите «Технологический», а в поле «оценка» в той же строке
наберите «>3» (рис. 13);
3) завершите результаты формирования
запроса;
4) назовите запрос «And».

Рис. 13. Структура запроса с And

Задание 11. Выполните пример на тему Or. Сформируйте запрос на
выборку всех данных о результатах сдачи экзаменов студентами, которые или
учатся на факультете «Экономический», или получили оценки 4 и 5:
1) перенесите все поля в структуру запроса,
как в примере «Все поля»;
2) в поле «факультет» в строке «Условие
отбора» наберите «Экономический», а в поле «оценка» в строке или наберите
«>3»;
3) завершите результаты формирования
запроса;

4) назовите запрос «Or».

Выборка по условию с оператором
Between...And

Оператор Between определяет
принадлежность значения поля таблицы указанному диапазону значений и имеет
следующий синтаксис применения:
Between «значение1» And «значение2», здесь «значение1» и «значение2» – выражения,
задающие границы диапазона. Порядок следования операндов «значение1» и
«значение2» значения не имеет.
Если значение поля попадает в диапазон,
задаваемый выражениями «значение1» и «значение2» (включительно), оператор
Between...And возвращает значение «истина»; в противном случае возвращается
значение «ложь». Допускается проверка с помощью логического оператора Not
обратного условия (т.е. того, что выражение не принадлежит диапазону значение1
– значение2).

Задание 12. Сформируйте запрос на выборку всех данных о результатах
сдачи экзаменов студентами, которые сдали экзамены на 4 или 5 (Between 4 And
5):
1) перенесите все поля в структуру
запроса, как в примере «Все поля»;
2) в поле «оценка» в строке «Условие
отбора» наберите Between 4 And 5 (рис. 14);
3) завершите результаты формирования
запроса так, как это обсуждалось в алгоритме формирования запроса на стр. 18;
4) назовите запрос «Between».

Рис. 14. Структура запроса с Between

Границы диапазона можно задать параметрически
по образцу Between [от] And [до].

Выборка по условию с оператором Like

Оператор Like имеет синтаксис Like
«шаблон» и используется для поиска в поле текстового типа значений,
совпадающих с указанным образцом. Допускается как точное указание аргумента
«шаблон» (например, Like «Иванов И.И.»), так и использование поддерживаемых
операционной системой символов шаблона, определяющих диапазон значений
(например, Like «И∗»).
При подстановке в выражение оператор Like
позволяет сравнивать значение поля со строковым значением. Например, при вводе
в строку «Условие отбора» таблицы запроса выражения Like «И∗» в запросе будут выведены все поля, содержимое
которых начинается с буквы «И».
Контрольное задание 13. Сформируйте запрос на выборку всех данных о результатах
сдачи экзаменов студентами, чьи фамилии заканчиваются на «ов» (Петров, Иванов и
т.д.):
1) перенесите все поля в структуру
запроса, как в примере «Все поля»;
2) в поле «фамилия» в строке «Условие
отбора» наберите Like «∗ов
*» (после «ов» пробел);
3) завершите результаты формирования
запроса;
4) назовите запрос «Like».
Запрос с Like, как любой запрос, может
быть задан параметрически.
В запросе с параметром допускается вывод
для пользователя приглашения ввести образец поиска.

Задание 14. Сформируйте параметрический запрос на выборку всех
данных о студентах, чьи фамилии начинаются с первых букв (одной, двух, сколько
нужно по контексту поиска), задаваемых в диалоге:
1) перенесите все поля в структуру
запроса, как в примере «Все поля»;
2) в поле «фамилия» в строке «Условие
отбора» наберите Like [Введите первые буквы фамилии:]&«∗» (рис. 15);
3) завершите результаты формирования
запроса.
При выполнении запроса будет открыто окно
диалога с надписью над полем ввода «Введите первые буквы фамилии». Если
пользователь введет в поле буквы «Ив», то в запросе будет выполняться
поиск по шаблону «Ив∗» (т.е. будут найдены все фамилии, начинающиеся с букв
«Ив»);
5)    назовите запрос «Like параметрический».

Рис. 15. Структура запроса с Like
Выборка по условию с оператором In
Оператор In имеет синтаксис In(«значение1»;«значение2»;
...) и проверяет значение поля таблицы на совпадение с любым из элементов
указанного списка значений. Если содержимое поля совпадает со значением одного
из элементов списка, оператор In возвращает значение «истина»; в противном
случае возвращается «ложь». Допускается проверка с помощью логического
оператора Not обратного условия (т.е. того, что выражение не принадлежит
списку значений).

Задание 15. Сформируйте запрос на выборку всех данных о результатах
сдачи экзаменов по базовым дисциплинам (физика, математика и др.):
1) перенесите все поля в структуру
запроса, как в примере «Все поля»;
2) в поле «специальность» в строке
«Условие отбора» наберите In («физика»;«математика») (рис. 16);
3) завершите результаты формирования
запроса;
4) сохраните запрос под именем «In».

Рис. 16. Структура запроса с In

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

Задание 16. Сформируйте запрос-выборку с вычисляемыми данными из
БД УЧЕБА, представленной одной таблицей «Учёба». Допустим, что по условиям
функционирования нашей модельной ПО «Учёба» две первые цифры номера студента
указывают на год его зачисления в вуз. Допустим также, что датой зачисления
студента является 1 сентября года приема. Добавим в структуру запроса на
выборку всех данных после поля «номер студента» вычисляемое поле «дата
зачисления», построенное по следующей формуле:

DateValue(«20»+Mid([Учеба]![номер студента];1;2)+«.01.09»)

Здесь функция DateValue(«строка
символов») преобразует строковое представление даты в данное типа дата.
Функция Mid(«строка символов»;
«начало»; «длина») вырезает из «строки символов», начиная с позиции
«начало» (целое число), подстроку символов длиной «длина» (целое число)
символов.
Оператор + выполняет сцепление
(конкатенацию) строк символов.
Построение формулы для вычисления
виртуального поля удобно производить под управлением Построителя выражений.
Сформируйте запрос по следующему алгоритму:
1) перенесите все поля в структуру
запроса, как в примере «Все поля»;
2) для добавления нового поля выделите
поле, слева от которого будет выполнена вставка. В нашем случае это поле
«фамилия». (Для выбора столбца следует поместить курсор в любую его ячейку или
нажать кнопку в области маркировки полей в верхней части таблицы запроса);
3) выполните команду Вставка | Столбцы.
(Для удаления поля следует выделить соответствующий столбец и выполнить команду
Правка | Удалить);
4) в строке «Поле» наберите формулу для
вычисляемого поля. Формулу можно вводить вручную или конструировать при помощи Построителя
выражений (рис. 17) по принципу «снаружи внутрь»;
5) в списке «Функции» («Встроенные
функции») в категории «Дата/время» найдите функцию DateValue («stringexpr»);
6) аргумент функции «stringexpr» замените
на сцепление трех строк символов: «20»+ Mid («stringexpr»; «start»; «length»)+ «01.09»;
7) функцию Mid найдете в списке «Функции»
(«Встроенные функции») вкатегории «Текстовые»: Mid («stringexpr»; «start»; «length»);
8) аргумент «start» замените на «1» (с
первой позиции), аргумент «length»– на «2» (подстрока длиной 2 символа);
9) аргумент «stringexpr» найдите в списке
«Таблицы» («Учеба», поле «номер студента») [Учеба]![номер студента];

Рис. 17. Построение выражения для вычисляемого поля

10) в таблице результата запроса новое
поле будет иметь имя «Выражение1». Чтобы присвоить ему содержательное имя,
установите курсор в ячейку «Поле» и нажмите правую клавишу мыши;
11) в контекстном меню выполните команду Свойства
и введите в диалоговом окне «Свойства поля» в строку «Подпись» имя поля
«дата зачисления»;
12) завершите результаты формирования
запроса;
13) результат запроса будет иметь вид, как
на рис. 18;
14) сохраните запрос под именем
«Вычисляемое поле».

Рис. 18. Результат запроса с вычисляемым полем

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

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

Задание 17. Постройте запрос на сортировку результатов сдачи
экзаменов в таблице «Учеба» по факультетам (по полю «факультет» по возрастанию),
а в пределах факультета по специальностям (по полю «специальность» по
убыванию):
1) перенесите все поля в структуру
запроса, как в предыдущем примере;
2) в структуре запроса поменяйте местами
поля «факультет» (первое поле) и «специальность» (второе поле);
3) в поле «факультет» в строке
«Сортировка» мышью включите список способов сортировки и выберите «По
возрастанию» (по алфавиту);
4) в поле «специальность» в строке
«Сортировка» мышью включите список способов сортировки и выберите «По
убыванию»;
5) завершите результаты формирования
запроса;
6) сохраните запрос под именем
«Сортировка».

Выборка с группированием данных и
вычислением функций агрегации

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

Задание 18. Допустим, что для каждого студента из БД УЧЕБА, представленной
одной таблицей «Учёба», требуется подсчитать количество сданных экзаменов и
средний балл по результатам сдачи всех экзаменов. Основанием для группирования
является поле «номер студента» и «фамилия» (если мы хотим увидеть это поле в
структуре запроса). Группировка по полю «фамилия» будет фиктивной, поскольку с
каждым номером студента связана только одна фамилия. Вычисление групповых
характеристик (количество и среднее значение) будет выполняться по полю
«оценка».

Сформируйте запрос на выборку с
группированием данных и вычислением групповых характеристик:
1) перенесите в структуру запроса поля
«номер студента», «фамилия», «оценка», «оценка»;
2) выполните команду Вид | Групповые
операции – в структуре запроса появится строка «Групповая операция».
Группирование данных будет выполняться по полю «номер студента»; поле «фамилия»
не создаст вложенной группы, так как уникальной характеристикой студента является
его учетный номер.
Поэтому значением групповой операции для
этих полей будет значение по умолчанию «Группировка»;
3) в строке «Групповая операция» для
первого поля «оценка» щелкните мышью и выберите из списка функцию подсчета
количества Count, для второго поля – функцию вычисления среднего значения Avg
(рис. 19);
4) в таблице результата запроса полям, по
которым вычисляются групповые характеристики, присвойте содержательные имена;
5) установите курсор в ячейку «Поле» и
нажмите правую клавишу мыши;
6) в контекстном меню выполните команду Свойства
и введите в диалоговом окне «Свойства поля» в строку «Подпись» имена
вычисляемых полей «количество экзаменов» и «средний балл». Результаты запроса
будут иметь вид, как на рис. 20;
7) завершите результаты формирования
запроса;
8) сохраните запрос под именем
«Группировка».

Рис. 19. Структура запроса на группирование данных

Рис. 20. Результаты запроса на группирование данных

Лабораторная
работа №3

Тема:
Закрепление изученного материала

Задания
для самостоятельной работы
В
предлагаемых ниже вариантах задач выполнить следующие задания:
1.
Создать базу данных в соответствии с вариантом.
2.
Определить ключевые поля.
2.
Заполнить таблицу данными. Количество записей должно быть не менее 20.
3.
Продумать и выполнить следующие типы запросов-выборок:
1) проекция
на подмножество однородных атрибутов;
2) параметрический
запрос на примере селекции по условию на основе операций сравнения (равно,
не  равно,  больше,  меньше,  больше  или  равно, меньше или равно);
3) запрос-выборка,
селекция по сложному условию на основе оператора конъюнкции And;
4) запрос-выборка,
селекция по сложному условию на основе оператора дизъюнкции Or;
5) запрос-выборка,
селекция  по  условию  на  основе  оператора  Between…And;
6) запрос-выборка,
селекция по условию на основе оператора Like;
7) запрос-выборка,
селекция по условию на основе оператора In;
8) запрос-выборка,
содержащий вычисляемое поле;
9) запрос-выборка
с сортировкой по двум-трем полям;
10) запрос-выборка
с группировкой данных и вычислением статистических функций Count, Sum, Avg, Min, Max и др.

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

Вариант
I. База данных Поставки
(номер поставщика, фамилия поставщика, город, код детали, город, код детали,
количество за месяц).

Вариант
II. База данных Больница
(номер пациента, фамилия пациента, адрес, номер хирурга, фамилия хирурга, дата
операции, тип операции).

Вариант
III. База данных Библиотека
(номер абонента, фамилия абонента, номер книги, автор, название, год издания,
дата выдачи, дата возврата).

Вариант
IV. База данных Аренда
(номер клиента, фамилия клиента, номер объекта, плата, фамилия собственника,
дата начала, дата конца).

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

Лабораторная работа №4. ПРОБЛЕМЫ МАНИПУЛИРОВАНИЯ ДАННЫМИ И
ЗАПРОСЫ НА СОЗДАНИЕ ТАБЛИЦ

Проблемы
выполнения корректирующих запросов

Корректирующие
запросы на добавление, обновление или удаление данных приводят к изменению
содержания базы данных. Если они выполняются некорректно, то в БД будет
храниться противоречивая информация.
При
выполнении корректирующих запросов могут встретиться проблемы, которые являются
следствием недостатков структуры данных «универсального» отношения. Это так
называемые аномалии добавления, обновления и удаления. Такими дефектами
обладает и таблица «Учеба».
Например, нельзя
ввести в таблицу данные о зачисленном в вуз студенте до тех пор, пока он не
сдаст первый экзамен и не будут определены ключевые атрибуты «предмет» и
«семестр». Это пример аномалии
добавления.
Другой пример: при изменении фамилии студента необходимо обновить поле
«фамилия» не один, а много раз во всех строках-записях, содержащих информацию о
результатах сдачи экзаменов этим студентом. При этом есть вероятность
ошибочного ввода в разные строки разных данных. Это пример аномалии
обновления.
А при удалении единственной строки, содержащей информацию о сдаче экзаменов
студентом, приведет к потере информации и о самом студенте. Это пример аномалии удаления.
Аномалии
манипулирования данными являются следствием избыточного дублирования данных,
присущего «универсальному» отношению. Дело в том, что в «универсальном»
отношении присутствуют семантически разнородные данные и попытки
манипулирования данными одного рода влекут за собой проблемы в отношении данных
другого рода.
Решением
проблемы аномалии манипулирования данными является корректная декомпозиция
«универсального» отношения на его проекции (подмножества, содержащие
семантически однородные данные).
В
реляционной алгебре операция построения из одной таблицы- отношения другой,
являющейся её подмножеством, называется проекцией исходной таблицы на подмножество
её столбцов-атрибутов.

Запрос
на создание таблицы

Запрос на
создание новой таблицы как проекции исходной рассмотрим в контексте проблемы
декомпозиции «универсального» отношения. Выполним декомпозицию «универсального»
отношения со схемой:
Учёба
(номер студента, фамилия, специальность, факультет, предмет, семестр, оценка)
на ее
проекции со схемами:
Кафедра
(специальность, факультет)
Экзамен
(номер студента, фамилия, факультет, предмет, семестр, оценка)
Рассмотрим,
как в Access реализуется операция проекция–создание одной таблицы как проекции
другой. Алгоритм выполнения этой процедуры состоит из трёх этапов:
1.
Построение запроса-выборки столбцов-атрибутов без дублей строк (Задание 6 на
стр. 21]. Тем самым создается проекция – подмножество данных исходной таблицы,
которое определит структуру и наполнение новой таблицы.
2.
Выполнение запроса на создание новой таблицы.
3.
Определение ключа вновь созданной таблицы .

Задание
1. На
основе исходной таблицы «Учёба» со схемой отношения:
Учёба
(номер студента, фамилия, специальность, факультет, предмет, семестр, оценка)
создайте две
новые таблицы со схемами:
Кафедра
(специальность, факультет)
Экзамен
(номер студента, фамилия, факультет, предмет, семестр, оценка)
Алгоритм
построения и выполнения запроса на создание новой таблицы рассмотрим на примере
отношения «Кафедра»:
1) в режиме Конструктора запросов в качестве
таблицы-источника данных к запросу выберите таблицу «Учёба»;
2)
сформируйте запрос-выборку, в котором присутствовали бы следующие поля таблицы
«Кафедра»: специальность,
факультет;
3) в режиме Конструктора выполните команду Вид | Свойства;
4) в
диалоговом окне «Свойства запроса» переключите мышью свойство «Уникальные
значения» в состояние «Да»;
5) завершите
результаты формирования запроса (на этом первая часть алгоритма заканчивается);
6) в режиме Конструктора выполните команду Запрос | Создание таблицы;
7) в
диалоговом окне «Свойства запроса» введите в поле «Таблица» имя новой таблицы «Кафедра» (рис. 21);
8) в режиме Конструктора выполните команду Запрос | Запуск. В новую таблицу будет
скопировано соответствующее количество записей
9) перейдите
в режим Конструктора
и
завершите работу с запросом (можно без сохранения структуры запроса, так как
результатом запроса является созданная таблица). На этом заканчивается вторая
часть алгоритма;

Рис.
21. Окно наименования
новой таблицы

10) в окне
БД выберите закладку Таблицы
и
убедитесь, что новая таблица «Кафедра» представлена в списке таблиц БД; это
является признаком успешного выполнения запроса на создание таблицы;
11) выберите
новую таблицу «Кафедра» в списке и нажмите экранную кнопку;
12) в режиме
Конструктора
таблиц
в окне структуры таблицы определите ключевые атрибуты (в таблице «Кафедра» это
поле «специальность»);
13)
сохраните результаты спецификации новой таблицы «Кафедра» по команде Файл | Сохранить. На этом заканчивается
третья часть алгоритма;
14)
просмотрите в Режиме
таблицы наполнение
новой таблицы «Кафедра» (рис. 22);
15) по
рассмотренному алгоритму создайте вторую таблицу «Экзамен».

Рис.
22 Наполнение таблицы «Кафедра»

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

В результате
декомпозиции «универсального» отношения «Учеба» мы получили представление той
же самой предметной области в виде совокупности двух отношений – таблиц
«Кафедра» и «Экзамен». Теперь установим связь между новыми таблицами.
Правила
установления связей между таблицами в Access таковы, что разрешены только бинарные (между двумя таблицами)
связи. Связи могут быть двух типов «один к одному» и «один ко многим». Причём
одна таблица выступает в роли старшей (отцовской), например
таблица «Кафедра» в отношении «Кафедра» : «Экзамен», а другая – в роли младшей
(сыновьей), например таблица «Экзамен» в отношении «Кафедра» : «Экзамен».
Однако одна и та же таблица в разных отношениях может выступать в роли старшей
и младшей одновременно. Связь между таблицами устанавливается по общим
полям-атрибутам (поле «факультет» в нашем случае). Связь между таблицами Access
устанавливается при помощи инструмента, называемого «Схемой данных».

Задание
2. Для
установления связи между таблицами «Кафедра» и «Экзамен»:
1) выполните
команду Сервис
| Схема данных – появится
окно макета схемы данных БД;
2) в
контекстном меню выполните команду Связи | Добавить
таблицу;
3) в окне
«Добавление таблицы» выберите в списке таблицу «Кафедра» и нажмите экранную
кнопку ;
4) в окне
«Добавление таблицы» выберите в списке таблицу «Экзамен» и нажмите экранную
кнопку ;
5) для
установления связи между таблицами «Кафедра» и «Экзамен» захватите мышью и
перенесите поле «факультет» таблицы «Кафедра» на поле «факультет» таблицы
«Экзамен» – раскроется окно «Изменение связей» (рис. 23);
6) не
настраивая свойства связи, нажмите экранную кнопку.
Схема базы
данных примет вид, аналогичный представленному на рис. 24.

Рис. 23. Окно спецификации связей в схеме данных

Рис. 24. Схема данных двух
таблиц

ДЕКОМПОЗИЦИЯ
ОТНОШЕНИЙ И ЗАПРОСЫ НА СОЕДИНЕНИЕ ТАБЛИЦ

Корректность
декомпозиции отношений

Итак,
структура информации в БД одной и той же предметной области (ПО) УЧЕБА может
быть представлена двумя способами:
1) в виде
схемы одного так называемого «универсального» отношения (в нашем примере в виде
таблицы «Учёба»);
2) в виде
комбинации схем нескольких отношений (в нашем примере в виде таблиц «Кафедра» и
«Экзамен»).
Эквивалентность
того и другого представления обеспечивается правилами корректной декомпозиции,
при которой отсутствуют искажения хранимых данных и сохраняются все
неизбыточные функциональные зависимости между атрибутами. Корректность
декомпозиции можно проверить, выполнив так называемую операцию естественного
соединения компонентов
декомпозиции. Декомпозиция считается корректной с точки зрения сохранения
данных, если при помощи естественного соединения компонентов декомпозиции можно
восстановить исходное отношение.

Запрос
на соединение таблиц

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

Задание
3. Создайте
запрос на соединение двух независимых таблиц «Кафедра» и «Экзамен»:
1) в окне БД
выберите закладку Запрос
и
нажмите экранную кнопку  ;
2) в окне
«Новый запрос» выберите вариант Конструктор;
3) в окне
«Добавление таблицы» выберите по закладке источник данных к запросу: Таблицы | Запросы | Таблицы и Запросы; в данном случае это Таблицы;
4) выберите
в списке таблицы «Кафедра» и «Экзамен» и нажмите экранную кнопку ;
5) нажмите
экранную кнопку  –
появится основное окно конструктора запросов в стиле QBE (рис. 25). Обратите внимание
на связи между одноименными атрибутами разных таблиц в верхней части бланка
запроса;
6)
перенесите в структуру запроса в нижней части из разных таблиц разноименные поля (рис. 25);
7) выполните
запрос по команде Запрос | Запуск – результат запроса будет выглядеть, как на
рис. 26;
8) завершите
результаты формирования запроса так, как это обсуждалось ранее;
9) сохраните
результат под именем «Естественное соединение».
Обратите
внимание на важный результат: итоговая таблица (рис. 26) по запросу на соединение
двух таблиц не совпадает с исходной таблицей «Учеба» по числу строк. В ней
присутствуют лишние строки, которые содержат семантически некорректные данные,
например о том, что студенты учатся по двум специальностям. Из полученного
результата следует важный вывод: декомпозиция таблицы-отношения «Учёба»
выполнена некорректно.

Рис.
25. Структура запроса на
соединение таблиц «Кафедра» и «Экзамен»

Рис. 6. Результат
запроса на соединение таблиц «Кафедра» и «Экзамен»

Корректная
декомпозиция

Декомпозиция
исходного отношения (таблицы) на его проекции считается корректной, если
выполняются два условия:
1.
Сохранение исходных данных в результате естественного соединения компонентов
декомпозиции.
2.
Сохранение неизбыточных функциональных зависимостей между атрибутами исходного
отношения.
Из теории
БД, занимающейся проблемой оптимизации структуры данных, следует, что для
реализации корректирующих запросов на добавление, удаление и изменение данных
предпочтительнее представление БД в виде не одной таблицы («универсального»
отношения), а виде совокупности так называемых нормализованных
таблиц-отношений. Проблемы
нормализации отношений здесь не обсуждаются. Достаточно понять, что в основе
нормализации таблиц-отношений лежит процедура декомпозиции ненормализованного
«универсального» отношения на её нормализованные компоненты, которые объединяют
семантически однородные данные.
Задание
4. Выполните
декомпозицию «универсального» отношения со схемой:
Учёба
( номер студента, фамилия, специальность, факультет,предмет, семестр, оценка)
на ее
проекции со схемами:
Кафедра
(специальность, факультет)
Студент
(номер студента, фамилия, специальность)
Сессия
(номер студента, предмет, семестр, оценка)
Таблица
«Кафедра» уже создана, поэтому:
1) создайте
остальные таблицы по алгоритму, описанному на стр. 26;
2)
установите связи между таблицами так, как обсуждалось на стр. 28;
3) проверьте
корректность декомпозиции по новой схеме как результат операции естественного
соединения трех таблиц (см. стр. 29).
Итоговые
таблицы «Студент» и «Сессия» должны выглядеть так, как показано на рис. 27 и 28.
Схема данных
нового варианта БД на основе трех таблиц должна выглядеть, как на рис. 29.
Структура
запроса на естественное соединение таблиц «Кафедра», «Студент» и «Сессия»
представлена на рис. 30.
Результат
запроса на естественное соединение таблиц «Кафедра», «Студент» и «Сессия»
приведен на рис. 31.

Рис. 27. Наполнение
таблицы «Студент»

Рис. 28. Наполнение
таблицы «Сессия»

Рис. 29. Схема данных
трех таблиц

Рис. 30. Структура
запроса на соединение трех таблиц

Рис. 31. Результат
запроса на естественное соединение трех таблиц

Обратите
внимание на важнейший результат: результирующая таблица (рис. 11) по запросу на
соединение трех таблиц с точностью до порядка следования строк и столбцов (что не
существенно) совпадает с исходной таблицей «Учёба» в [6, стр. 18], из которой
новые таблицы были получены как её проекции.
Из
полученного результата следуют важные выводы:
1.
Декомпозиция таблицы-отношения «Учёба» на её компоненты- проекции «Кафедра», «Студент»
и «Сессия» выполнена корректно, т. е. Без потери данных.
2.
Представления предметной области (ПО) «Учёба» в виде одноименного «универсального»
отношения и в виде комбинации нормализованных отношений- таблиц «Кафедра»,
«Студент» и «Сессия» эквивалентны. Отныне наша база данных УЧЕБА будет
представлена не одной таблицей «Учеба», а комбинацией нормализованных связанных
таблиц «Кафедра», «Студент» и «Сессия». Все простые запросы-выборки, которые
были выполнены ранее на примере одной таблицы «Учеба», теперь с равным успехом могут
быть воспроизведены на основе связанных таблиц. Рассмотрим в качестве примера
запрос на группировку данных.

Задание
5. Допустим,
что для каждого студента из БД УЧЕБА, представленной теперь тремя таблицами,
требуется подсчитать количество сданных экзаменов и средний балл по результатам
сдачи всех экзаменов.
Сформируйте
запрос на выборку с группированием данных из связанных таблиц «Студент» и
«Сессия» и вычислением групповых характеристик:
1) в окне БД
на закладке Запрос
приступите
к созданию нового запроса (нажмите экранную кнопку );
2) в окне
«Новый запрос» выберите вариант Конструктор;
3) в окне
«Добавление таблицы» по закладке Таблицы выделите в списке таблицы «Студент» и
«Сессия» и нажмите экранные кнопки и;
4) в окне
конструктора запросов выберите в структуру запроса поля
«номер
студента», «фамилия» (из таблицы «Студент»), «оценка», «оценка» (из таблицы
«Сессия»);
5) выполните
команду Вид
| Групповые операции;
в структуре запроса в строке «Групповая операция» для полей «номер студента» и
«фамилия» включите настройку «Группировка»;
6) для
первого поля «оценка» в строке «Групповая операция» выберите из списка функцию
подсчета количества Count, для второго поля –функцию вычисления среднего
значения Avg (рис. 32);
7) в таблице
результата запроса полям, по которым вычисляются групповые характеристики,
присвойте содержательные имена «количество экзаменов» и «средний балл»;
8)
результаты запроса будут иметь вид, как на рис. 33;
9) сохраните
запрос под именем «Группировка2»

Рис. 32. Структура запроса на
группирование данных из связанных таблиц

         Рис. 33. Результаты запроса на группирование данных из
связанных таблиц

Сравните
полученные результаты с результатами аналогичного запроса к одной таблице. Совпадение очевидно.
Однако следует
заметить, что выборка данных из связанных таблиц, особенно если их много и они
содержат большое количество строк, требует для своего выполнения больше
компьютерного времени, чем выборка из одной таблицы.
Таким
образом, для запросов-выборок желательно иметь все данные в одном месте, в
одной таблице. Что же касается корректирующих запросов, то там ситуация прямо
противоположная. Эффективность корректирующих запросов и качество структуры
базы данных требуют представление БД в виде совокупности связанных таблиц.
Решение проблемы требует взвешенного подхода с учетом всех «за» и «против».

КОРРЕКТИРУЮЩИЕ ЗАПРОСЫ

Выполнение
корректирующих запросов
В
результате корректной декомпозиции отношения «Учеба» мы развели семантически
однородные данные по разным таблицам. Тем самым мы решили проблему  аномалий
манипулирования  данными  и  создали  предпосылки  для корректного выполнения
корректирующих запросов. Корректирующие запросы бывают трех типов:
1)
запросы на добавление данных;
2)
запросы на удаление данных;
3)
запросы на обновление данных.
Алгоритм
выполнения этих запросов состоит из двух этапов [7–10]:
1.
Построение запроса-выборки строк-записей, подлежащих добавлению, удалению или
обновлению. Тем самым создается подмножество корректируемых данных.
2.
Выполнение корректирующего запроса.
Продемонстрируем
эти типы запросов на примере таблицы «Студент».

Запрос
на удаление

Задание
6.  Создайте  запрос  на  удаление  из  таблицы «Студент» данных о студенте,
заданном его фамилией.
1)
в  качестве  таблицы-источника  данных  к  запросу  выберите  таблицу
«Студент»;
2)
сформируйте  запрос-выборку  всех  полей  по  условию,  задаваемому
параметрически;  в  поле «фамилия»  в  строке «Условие  отбора»  наберите  в
квадратных скобках текст подсказки к вводу конкретного значения [Фамилия
студента];
3)
выполните запрос  по  команде  Запрос | Запуск;  в  диалоговом  окне, которое
появится  при  реализации  запроса,  введите  в  кавычках  конкретное значение,
например «Андреев А.А.»;
4)
просмотрите  результаты  запроса  в  Режиме  таблицы  и  примите
решение, удовлетворяют ли вас результаты выборки или нет;
5)
вернитесь в режим Конструктора. На этом первая часть алгоритма заканчивается;
выборка
данных на удаление подготовлена;
6) в  режиме  Конструктора
выполните  команду  Запрос | Удаление – в бланке структуры запроса
появится строка «Удаление» (рис. 34);

Рис. 34. Структура запроса на удаление данных из
таблицы
7)
выполните запрос по команде Запрос | Запуск;
8)
завершите результаты формирования запроса. На этом заканчивается вторая часть
алгоритма;
9)
в  окне  БД  на  закладке  Таблицы  выберите  таблицу «Студент»  и
посмотрите  её  наполнение.  Убедитесь,  что  удаление  записи  действительно
произошло.

Запрос
на добавление

Задание
7. Речь идет о добавлении данных в
одну таблицу из другой. Создайте запрос на добавление данных о студенте,
заданном его фамилией из таблицы «Учёба» в таблицу «Студент»:
1)
в качестве таблицы-источника данных выберите таблицу «Учёба»;
2)
сформируйте запрос-выборку полей, которые должны быть добавлены
(«номер
студента», «фамилия», «специальность»)  по  условию,  задаваемому   параметрически;
в поле «фамилия»  в  строке «Условие  отбора»  наберите  в квадратных скобках
текст подсказки к вводу конкретного значения [Фамилия студента];
3)
чтобы избавиться от дублей строк в выборке для добавления в таблицу «Студент»
дополните условие выборки требованием «без дублей записей». Для этого в режиме Конструктора
выполните команду Вид | Свойства;
4)
в диалоговом окне «Свойства запроса» переключите мышью свойство
«Уникальные
значения» в состояние «Да»;
выполните
запрос  по  команде  Запрос | Запуск;  в  диалоговом  окне,  которое
появится при реализации запроса, введите в  кавычках  конкретное значение,
например «Андреев А.А.»;
просмотрите
результаты  запроса  в  Режиме  таблицы  и  примите  решение, удовлетворяют
ли вас результаты  выборки или нет;

Рис. 15. Структура запроса на добавление данных в
таблицу
5) вернитесь в режим Конструктора.
На этом первая часть алгоритма заканчивается; выборка данных на добавление
подготовлена.
6) в режиме Конструктора
выполните команду Запрос | Добавление;
7) в диалоговом окне «Добавление»
в поле «имя таблицы» выберите из списка таблицу – приёмник данных «Студент»;

8)  в бланке структуры
запроса появится строка «Добавление» (рис. 15);
9)  выполните  запрос по
команде Запрос | Запуск;
10)  завершите результаты
формирования запроса. На этом заканчивается вторая часть алгоритма;
11)  в  окне  БД  на
закладке  Таблицы  выберите  таблицу «Студент»  и убедитесь, что
добавление записи действительно произошло.

Запрос на обновление

Задание
8. Создайте запрос на замену в
таблице «Студент», например, фамилии «Николаева Н.Н.» на «Сергеева Н.Н.»:
1)
в качестве таблицы-источника данных выберите таблицу «Студент»;
2)
сформируйте параметрический запрос-выборку всех полей по условию «фамилия» =
[Старая фамилия]: в поле «фамилия» в строке «Условие отбора» наберите
 [Старая
фамилия];
3)
выполните запрос по команде Запрос | Запуск;
4)
в  диалоге  задайте  в  кавычках  старую  фамилию,  например «Николаева Н.Н.»

5)
просмотрите  результаты  запроса  в  Режиме  таблицы  и  примите решение,
удовлетворяют ли вас результаты выборки или нет;
6)
вернитесь в режим Конструктора. На этом первая часть алгоритма заканчивается;
выборка данных на обновление подготовлена.
7)
в  режиме  Конструктора  выполните  команду  Запрос | Обновление;
в бланке структуры запроса появится строка «Обновление» (рис. 36);
8)
в строку «Обновление» в поле «фамилия» введите подсказку для ввода
[Новая фамилия];
9)
выполните запрос по команде Запрос | Запуск;
10)
в диалоге задайте новую фамилию, например «Сергеева Н.Н.»;
11)
завершите  результаты  формирования  запроса. На этом заканчивается вторая
часть алгоритма;
12)
в  окне  БД  на  закладке  Таблицы  выберите  таблицу «Студент»  и
убедитесь,
что обновление записи действительно произошло.

Рис. 36. Структура запроса на обновление данных
ОБЕСПЕЧЕНИЕ ЦЕЛОСТНОСТИ
ДАННЫХ
Ограничения целостности
данных

В отношении базы данных важно хорошо уяснить себе
следующее. База данных – это не просто архив, хранилище полезной информации.
База данных – главным  образом  информационная  модель  ПО,  для  которой  она
создана.
Поэтому  важнейшим  условием  ее  успешной
эксплуатации  является адекватность хранимых в ней данных актуальному состоянию
ПО.
Целостность  БД –  это  синоним  её  системности.  Для
успешного исследования ПО должна рассматриваться как система. Точно так же БД –
её информационная модель – должна проектироваться как системный объект. Это
значит,  что  структура  БД  определяется  не  только  составом  образующих  её
информационных  элементов,  но  и  характером  связей  между  ними.  Связи
соответствуют  зависимостям  между  компонентами ПО.  Всякие  зависимости представляют
собой ограничения на  возможные отношения элементов системы. В теории БД эти
ограничения называются ограничениями целостности данных.
При  проектировании  БД  особое  значение  имеют  так
называемые семантические  ограничения  целостности (или  семантические
условия).
Семантические ограничения присущи самой ПО и их учёт
основан на анализе смыслового содержания данных. Семантические ограничения
целостности не выводятся. Это свойства данных, которые выполняются или не
выполняются  для рассматриваемого отношения элементов данных.
Семантические ограничения целостности различают:
1) ограничения целостности атрибутов (доменов);
2) ограничения целостности отношений (таблиц);
3) ограничения целостности ссылок между отношениями
(ограничения целостности базы данных).
Ограничением целостности атрибута является определение
множества элементов данных – домена, на котором определен атрибут. Ограничения
такого рода задаются при определении структуры таблицы в виде свойств
образующих ее  полей,  таких  как  тип  данных,  размер  поля,  маска  ввода,
значение  по умолчанию,  условие  на  значение  и  др.  Например,  понятие
«оценка» предполагает,  что  это  данное «числового»  и  никакого  другого
типа  со значениями в диапазоне от 2 до 5 или что «фамилия» – это строка
символов определенной длины, содержащая фамилию и инициалы и т. д.
Ограничение целостности атрибутов проверяется СУБД
всегда и притом немедленно. Любая попытка введения некорректного значения
атрибута будет отвергнута.
Ограничения  целостности  отношения  базируются  на
понятиях функциональных  зависимостей  атрибутов,  потенциальных  ключей,  неопределенных
так называемых Null-значений атрибутов и др.
Зависимости  одних  атрибутов  от  других  являются
очень распространенным типом ограничений. Особое значение имеют зависимости от
так  называемых  потенциальных  ключей.  Строки-кортежи  отношения соответствуют
реальным  экземплярам  объектов,  а  реальные  объекты различимы,
идентифицируемы.  Это  значит,  что  среди  атрибутов  отношения всегда
найдется такое их подмножество, которое однозначно определяет любой другой
атрибут  отношения,  а  значит,  и  всё  отношение  в  целом.  Например,
понятие «номер  студента»  в  роли  потенциального,  первичного  ключа  как
ограничение означает, что не может быть двух и более персон с одинаковыми
учётными номерами.
Значение  Null  используется  в  реляционной  модели
при  решении проблемы отсутствующей информации. Null – это не одно и то же, что
пробелы или  числовые  нули.  Значения Null  могут  использоваться  как
значения атрибутов,  для  которых  данные  не  известны. Null-значение  для
заданного атрибута  может  быть  разрешено  или  запрещено.  Значение Not Null
и означает, что (обязательное поле) является ограничением для ключевых полей ни
при  каких  условиях  ключевые  атрибуты  не  могут  принимать неопределенные
значения.
Ограничения целостности отношений также задаются при
спецификации структуры  каждой  таблицы  при  определении  ее  ключевых  или
индексированных полей. Ограничения целостности отношений также являются
безотлагательными,  они  проверяются  всегда  и  притом  немедленно.  Любая
попытка пропуска значений или ввода дублирующих значений ключевых полей пресекается
СУБД немедленно.
В случае, когда база данных моделируется не одним, а
несколькими (хотя бы и двумя) связанными отношениями, возникает проблема
ограничения целостности базы  данных.  Ограничение  целостности  базы  данных
в  реляционном представлении  определяется  как  ограничение  ссылочной
целостности,  или ограничение, основанное на связях между
отношениями-объектами. Связи между отношениями могут быть типа «один к одному»
(1:1) или «один ко многим» (1:N).
Связь  каждой  пары  отношений-объектов,  в  которой
одно  отношение  является «отцом», а другое – «сыном», обеспечивается ключом
связи, или внешним ключом.
Внешний  ключ –  это  первичный  ключ
отношения-отца,  переданный «по наследству» отношению-сыну.
Ограничения ссылочной целостности моделируют в
структуре данных два рода  зависимостей:  неидентифицирующие  и
идентифицирующие.  Если внешний ключ используется только для моделирования
связи и не входит в состав потенциального ключа отношения-сына, мы имеем пример
«слабой», или неидентифицирующей зависимости. Если внешний ключ используется не
только  для  моделирования  связи,  но  и  для  однозначной  идентификации отношения-сына
(входит в состав потенциального ключа отношения -сына), мы имеем случай
«сильной», или идентифицирующей зависимости.
Роль  связей  как  ограничений  целостности  проявляется
в  том,  что  они налагают ограничения на выполнение корректирующих операций
(добавление, удаление  и  обновление),  выполняемых  над  строками-кортежами
связанных таблиц. Корректирующие операции должны выполняться таким образом,
чтобы связи между таблицами всегда оставались согласованными и соответствовали
семантике связей между объектами ПО.
Ограничения  целостности  базы  данных,  как
правило,  не  проверяются немедленно. Проверка откладывается до завершения
транзакции –логического элемента работы с базой данных. Это делается для того,
чтобы были выполнены обновления  различных  связанных  отношений  при
переходе  из  одного целостного состояния в другое.
Примерами ограничений целостности ссылок в нашем
случае являются, например такие:
1.  Отношение между таблицами «Кафедра» и «Студент»
должно быть таково, что студент не может быть принят на несуществующую
специальность.
2.  Отношение  между  таблицами «Студент»  и «Сессия»
должно  быть таково, что персона, не зачисленная как студент, не может быть
допущена к сдаче экзамена.

Контроль целостности данных

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

Задание 9.  Для  проверки  корректности  процедур манипулирования
данными связанных таблиц попытайтесь добавить вручную в  таблицу «Студент»
бесконтрольную  запись  такого  содержания: «000000», «Одинцов  О.О.», «c0»
(речь  идет  о  приёме  студента  на  несуществующую специальность):
1) в окне БД на закладке
Таблица выберите из списка таблицу «Студент» и нажмите экранную кнопку  ;
2) введите вручную новую
запись: «000000», «Одинцов О.О.», «с0»;
3) сохраните результат по
команде Файл | Сохранить и Файл | Закрыть.
Вы выполнили некорректную с
точки зрения семантики ПО операцию:
зачислили студента на
несуществующую специальность. Система БД никак на это  не  отреагировала.  Это
плохо.  Наша  БД  не  защищена  от  некорректных действий  пользователя.
Причина  в  том,  что  нет  корректных  связей  между таблицами и ничто не
мешает вводить запрещенные данные (например, номер специальности, не
зарегистрированной в БД, в таблице «Кафедра», как в нашем примере).

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

В нашем примере представлены связи обоих типов. Между
таблицами «Кафедра» и «Студент» имеется неидентифицирующая связь. Роль внешнего
ключа  таблицы «Студент»  играет  атрибут «специальность».  Семантика этой  связи
такова «каждый студент обучается по определенной специальности, но персона  студента
однозначно  определяется  его  собственным  учетным номером».  Между  таблицами
«Студент»  и «Сессия»  имеется идентифицирующая  связь.  Внешним  ключом
таблицы «Сессия»  является  атрибут «номер студента». Семантика этой связи
такова «результаты сдачи экзамена по данному предмету в данный семестр
однозначно определяются учетным номером студента».
СУБД Access замечательна тем, что установка средств
контроля целостности данных  в  связанных  таблицах  в  значительной  степени
автоматизирована.
Познакомимся с этими возможностями подробнее.
Во-первых,  корректность  ввода  значений  внешнего
ключа «специальность»  в  таблице «Студент»  может  быть  обеспечена
настройкой свойства  подстановка  этого  поля.  Свойство  подстановка
позволяет  связать поле  с  элементом  управления  «поле  со  списком»
и  вводить  данные  под управлением  списка «разрешенных»  значений.
Источником «разрешенных» значений  для  поля «специальность»  таблицы
«Студент»  является  отцовская таблица «Кафедра».

Задание 10.  Для  обеспечения  контроля  ввода  корректных
значений внешнего ключа «специальность» в таблице «Студент» настройте его
свойство подстановка (создайте на основе таблицы «Кафедра» поле со
списком разрешенных значений):
1) в окне БД на закладке
Таблицы откройте таблицу «Студент» в режиме Конструктор;
2) выделите поле «специальность»;

3) в  нижней  части
диалогового  окна,  на  закладке «Подстановка» настройте свойства
элемента управления «Поле со списком» в соответствии с образцом, приведенным на
рис. 37;
4) по завершении модификации
структуры таблицы «Студент» сохраните результаты по команде Файл | Сохранить
и закройте диалоговое окно;
5) откройте таблицу «Студент»
в Режиме таблицы, для чего нажмите экранную кнопку ;
6) в  любой  строке  таблицы
щелчком  мыши  в  поле «специальность» вызовите список «разрешенных»
значений (рис. 38);
7) попробуйте ввести данные в
обход элемента  управления, убедитесь в тщетности этой попытки.

Рис. 37. Настройка свойств элемента управления для
поля специальность

Рис. 38. Ввод данных под управлением поля со списком

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

Задание
11. Для обеспечения целостности и
корректности данных  в  БД  установите  связи  между  таблицами,  т.е.
создайте  корректную схему данных БД:
1)
выполните  команду  Сервис | Схема  данных – появится  макет  схемы
данных БД (рис. 39);
2)
для  установления  контроля  целостности  связи  между  таблицами «Кафедра»  и
«Студент»  щелкните  правой  клавишей  мыши  по  изображению связи и выберите в
контекстном меню «Изменить связь» – раскроется окно «Изменение связей»
(рис. 39);
3)
в  окне «Изменение  связей»  мышью «включите»  настройку «Обеспечение
целостности данных» и нажмите экранную кнопку OK. Попытка установления
связи увенчается неудачей  (рис. 40). Причина в том, что в таблице  «Студент»
нарушено условие целостности данных – введены данные о студенте с
незарегистрированной в БД специальностью;

Рис. 39. Окно спецификации связей в схеме данных

Рис. 40. Сообщение о неудачной попытке установления
связи

4)
для  установления  контроля  целостности  связи  между  таблицами «Студент» и
«Сессия» щелкните правой клавишей мыши по изображению связи и  выберите  в
контекстном  меню «Изменить  связь» –  раскроется  окно «Изменение
связей»;
5)
в  окне «Изменение  связей»  мышью «включите»  состояние «Обеспечение
целостности данных» и нажмите экранную кнопку OK – связь будет
установлена.  Корректная связь  в  схеме  данных  выделяется  жирной линией и
метками кардинальности связи 1 и   (рис. 41);
6)
сохраните  результат  создания  связей  между  таблицами  по  команде Файл |
Сохранить.  Ничего,  что  попытка  установления  связи  с  обеспечением
целостности  данных  между  таблицами «Кафедра»  и «Студент»  оказалась
неудачной.  Схему  данных (макет),  как  и  любой  объект Access,  можно
отредактировать в новых условиях.
Из
полученных результатов важно уяснить себе следующее:
1)
методы создания связи с обеспечением целостности данных;
2)
причины неудачи создания связей;
3)
невозможность  установления  контроля  целостности  некорректных данных.

Задание
12.  Причина  неудачи  создания
связи  между таблицами «Кафедра» и «Студент» в предыдущем задании состоит в
том, что в таблице «Студент» нарушено условие целостности данных: введены данные
о студенте с незарегистрированной в БД специальностью. Исправьте этот дефект:
1)
в окне БД на закладке Таблица выберите из списка таблицу «Студент» и
нажмите экранную кнопку ;
2)
удалите вручную запись: «000000 Одинцов О.О. с0»;
3)
сохраните результат по команде Файл | Сохранить и Файл | Закрыть;
4)
вернитесь  к  настройке  свойств  связей  по  команде  Сервис | Схема данных
– появится макет схемы данных;
5)
щелкните мышью по связи «Кафедра»:«Студент» – раскроется окно «Изменение
связей» (рис. 39);
6)
в  окне «Изменение  связей»  мышью «включите»  состояние «Обеспечение
целостности данных» и нажмите экранную кнопку OK. Макет  схемы данных
примет вид, как на рис. 41;
7)
сохраните  результат  создания  связей  между  таблицами  по  команде Файл |
Сохранить.

Рис. 41. Корректная схема базы данных УЧЕБА
Внимание!
Важный  результат:  наша  демонстрационная  ПО  УЧЕБА представлена  БД  в
виде  совокупности  нормализованных  таблиц «Кафедра», «Студент»  и «Сессия»,
связанных  корректными  связями,  которые обеспечивают контроль целостности
данных.

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

Поскольку  в  нашей  БД  теперь  предусмотрен
контроль  целостности данных,  произвол  в  манипулировании  данными  не
возможен.  Правила ссылочной  целостности  регламентируют  выполнение
операций  добавления, удаления  и  обновления  над  данными  в  связанных
таблицах.  Основными правилами  являются «запрет»  и «каскадирование».  При
отсутствии специальных настроек свойств связей действуют правила, устанавливаемые
по умолчанию (правила  запрета).  Для  неидентифицирующих  и идентифицирующих
связей действуют правила запрета. Запрещаются удаление и обновление записи из
таблицы-отца, если в таблице-сыне есть связанные с ней записи. Запрещаются
добавление и обновление записи в таблице-сыне, если в таблице-отце
соответствующие данные отсутствуют.
Для  того  чтобы  при  манипулировании  данными
одной  из  связанных таблиц  выполнялись  адекватные  изменения  других
таблиц,  могут  быть «включены» каскадные обновление и удаление данных
[7–10].
Какие  правила  обеспечения  целостности  данных
выбрать,  зависит  от семантики  данных  моделируемой  ПО.  Рассмотрим  эти
проблемы  на  нашем примере БД УЧЕБА.
Конкретный  студент (строка  в  таблице «Студент»)  не
может  быть   зачислен  без  отнесения  его  к  определенной специальности
(внешний  ключ «специальность»  не  может  принимать Null-значение),
следовательно, добавление в таблицу «Студент» новой строки, не имеющей
соответствующей строки  в  таблице «Кафедра»,  должно  быть  запрещено
(нельзя  принимать студентов на несуществующую специальность). По этой же
причине удаление соответствующей строки в таблице «Кафедра» должно быть запрещено,
пока по этой специальности обучается хотя бы один студент. Альтернативное
правило каскадирования означало бы, что при закрытии (удалении) специальности
все ее студенты должны быть отчислены. Вряд ли такая семантика может быть
оправдана.
В случае обновления строки в таблице «Кафедра»
(например, изменении номера  специальности)  разумно  допустить
каскадирование,  т.е.  адекватное изменение этих же данных в таблице «Студент».

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

Задание
13.  Настройте  контроль
целостности  в  схеме данных на каскадные обновление и удаление данных:
1)
раскройте макет схемы данных по команде Сервис | Схема данных;
2)
для редактирования связи «Кафедра»:«Студент» щелкните мышью по связи –
раскроется окно «Изменение связей» (рис. 42);
3)
в  окне «Изменение  связей»  мышью «включите»  настройку «Каскадное
обновление связанных полей» и нажмите кнопку ОК (рис. 42);
4)
для редактирования связи «Студент»:«Сессия» щелкните мышью по связи. Раскроется
окно «Изменение связей» (рис. 43);
5)
в  окне «Изменение  связей»  мышью «включите»  состояния «Каскадное
обновление  связанных  полей»  и «Каскадное  удаление связанных полей»;
нажмите экранную кнопку ОК (рис. 43);

Рис. 41. Настройка свойств связи «Кафедра» : «Студент»
6)
сохраните  результаты  редактирования  связей  между  таблицами  по команде Файл
| Сохранить;

Рис. 42. Настройка свойств связи «Студент» : «Сессия»

Задание
14.  Выполните  простейшие
манипуляции с данными в условиях контроля целостности данных:
1)
попробуйте  добавить  в  сыновью  таблицу «Студент»  запрещенные данные. В окне
БД на закладке Таблица выберите из списка таблицу «Студент» и нажмите
экранную кнопку ;
2)
добавьте вручную запись: «000000 Одинцов О.О. с0». При попытке
сохранить  некорректные  данные  появится  диагностическое  сообщение  о
невозможности ввода (рис. 44);

Рис. 43. Диагностическое сообщение о невозможности
добавления записей

3)
попробуйте теперь удалить из отцовской таблицы «Кафедра» запись, имеющую
потомков в сыновьей таблице «Студент». В окне БД на закладке Таблица
выберите из списка таблицу «Кафедра» и нажмите экранную кнопку ;
4)
удалите  запись «с1  ф1».  При  попытке  удаления  появится
диагностическое сообщение о невозможности этой операции (рис. 45);

Рис. 44. Диагностическое сообщение о невозможности
удаления связанных записей

5)
попробуйте теперь изменить в отцовской таблице «Кафедра» запись, имеющую
потомков в сыновней таблице «Студент»;
6)
измените в таблице «Кафедра» номер специальности «c2» на «c4» в
соответствующей записи;
7)
посмотрите,  как  эта  операция  скажется  на  подчиненной  таблице «Студент».
В  окне  БД  на  закладке  Таблица  выберите  из  списка  таблицу  «Студент» и
нажмите экранную кнопку . Должно произойти каскадное обновление
данных в подчиненной таблице;
8)
попробуйте изменить в таблице «Студент» её независимые данные, например, «номер
студента»;
9)
измените  в  соответствующей  строке «номер  студента» (это Сергеева Н.Н.) «031512»
на «040000»  и  сохраните  результаты  по  команде Файл | Сохранить;

10)
посмотрите,  как  эта  операция  скажется  на  подчиненной  таблице «Сессия».
В  окне  БД  на  закладке  Таблица  выберите  из  списка  таблицу
«Сессия» и нажмите экранную кнопку . Должно произойти каскадное обновление
данных в подчиненной таблице;
11)
попробуйте удалить в таблице «Студент» хотя бы одну её запись.
В
окне  БД  на  закладке  Таблица  выберите  из  списка  таблицу
«Студент»  и нажмите экранную кнопку ;
12)
удалите  запись  о  студенте  Андрееве  А.А.  в  таблице «Студент»  в
соответствующей строке и сохраните результаты по команде Файл | Сохранить;

13)
посмотрите,  как  эта  операция  скажется  на  подчиненной  таблице
«Сессия».
В окне БД на закладке Таблица выберите из списка соответствующую таблицу
и нажмите экранную кнопку . Зависимые записи (такая была одна о
студенте  Андрееве  А.А.  под  номером 028101)  должны  исчезнуть.  Должно
произойти каскадное удаление данных в подчиненной таблице;
14)
попробуйте добавить в таблицу «Сессия» некорректные данные. В окне  БД  на
закладке «Таблица»  выберите  из  списка  таблицу «Сессия»  и нажмите экранную
кнопку ;
15)
добавьте  вручную запись: «000000  физика  2  3». При попытке сохранить
некорректные  данные  появится  диагностическое  сообщение  о невозможности
ввода;
16)
попробуйте удалить запись в таблице «Сессия» «031512  математика  1 5»;
17)
убедитесь, что эта операция прошла без проблем.

ЗАКЛЮЧЕНИЕ

Собственно
разработка структуры БД включает в себя:
1.
Разработку структуры каждой таблицы, в том числе:
1)
определение имен всех ее полей;
2)
определение типов данных полей;
3)
определение свойств полей;
4)
определение ключевых полей.
2.
Построение схемы базы данных, в том числе:
1)
установление корректных связей между таблицами;
2)
обеспечение контроля целостности связей;
3)
установление правил целостности данных (запрет или разрешение каскадных
преобразований).
Разработка
структуры  БД  должна  быть  выполнена  до  наполнения  ее
содержательной
информацией.

file_name: 011. П_КОНЦЕПЦИЯ БАЗ ДАННЫХ В ACCESS.doc

alt: КОНЦЕПЦИЯ БАЗ ДАННЫХ В ACCESS

alt: Лабораторная работа №1. СОЗДАНИЕ

alt: Рис. 1. Окно базы данных Access

alt: Поле MEMO 64000

alt: В реляционном представлении такой

alt: Основы технологии работы с таблицами

alt: Задание 8. Сформируйте параметрический запрос всех данных о результатах сдачи экзаменов по предмету, заданному его названием: 1) выберите все поля в структуру запроса, как в…

alt: Between «значение1» And «значение2» , здесь «значение1» и «значение2» – выражения, задающие границы диапазона

alt: Задание 15 . Сформируйте запрос на выборку всех данных о результатах сдачи экзаменов по базовым дисциплинам (физика, математика и др

alt: Продумать и выполнить следующие типы запросов-выборок: 1) проекция на подмножество однородных атрибутов; 2) параметрический запрос на примере селекции по условию на основе операций сравнения (равно,…

alt: Вариант II . База данных Больница (номер пациента, фамилия пациента, адрес, номер хирурга, фамилия хирурга, дата операции, тип операции)

alt: Это пример аномалии добавления

alt: Конструктора выполните команду

alt: Рис. 2 4. Схема данных двух таблиц

alt: Запрос | Запуск ; в диалоговом окне, которое появится при реализации запроса, введите в кавычках конкретное значение, например «

alt: Конструктора . На этом первая часть алгоритма заканчивается; выборка данных на обновление подготовлена

alt: Задание 12. Причина неудачи создания связи между таблицами «Кафедра» и «Студент» в предыдущем задании состоит в том, что в таблице «Студент» нарушено условие целостности данных:…

title: П_КОНЦЕПЦИЯ БАЗ ДАННЫХ В ACCESS


extracted_text:
ОПРЕДЕЛЕНИЕ ПСИХОЛОГИЧЕСКОГО КЛИМАТА ГРУППЫ
Психологический климат на эмоциональном уровне отражает
сложившиеся в коллективе взаимоотношения, характер делового сотрудничества,
отношение к значимым явлениям жизни. Складывается же психологический климат за
счет "психологической атмосферы – также группового эмоционального
состояния, которая, однако, имеет место в относительно небольшие отрезки
времени и которая в свою очередь создается ситуативными эмоциональными
состояниями коллектива.
Для общей оценки некоторых основных проявлений
психологического климата коллектива можно воспользоваться картой-схемой Л.Н.
Лутошкина. Здесь в левой стороне листа описаны те качества коллектива, которые
характеризуют благоприятный психологический климат, в правой – качества коллектива
с явно неблагоприятным климатом. Степень выраженности тех или иных качеств
можно определить с помощью семибалльной шкалы, помещенной в центре листа (от +3
до -3).
Используя схему, следует прочесть сначала
предложение слева, затем справа и после этого знаком "+" отметить в
средней части листа ту оценку, которая наиболее соответствует истине.
Надо иметь в виду, что оценки означают:
+3 – свойство, указанное слева, проявляется в данном
коллективе всегда;
+2 – свойство проявляется в большинстве случаев;
+1 – свойство проявляется достаточно часто;
0 – ни это, ни противоположное (указанное справа) свойства
не проявляются достаточно ясно, или то и другое проявляются в одинаковой
степени;
-1 –достаточно часто проявляется противоположное свойство
(указанное справа);
- 2 – свойство проявляется в большинстве случаев;
- 3 – свойство проявляется всегда.
Чтобы представить общую картину психологического климата
коллектива, надо сложить все положительные и отрицательные баллы. Полученный
результат может служить условной характеристикой психологического климата
большей или меньшей степени благоприятности.

ОПРЕДЕЛЕНИЕ ПСИХОЛОГИЧЕСКОГО КЛИМАТА ГРУППЫ
Бланк тестируемого______________________

Положительные особенности

+3

+2

+1

0

-1

-2

-3

Отрицательные особенности

Преобладает бодрое и жизнерадостное
  настроение

Преобладает подавленное настрое-ние, пессимистический тон

Преобладают доброжелательность во
  взаимоотношениях, взаимные симпатии

Преобладают конфликтность в
  отношениях, агрессивность, антипатии

В отношениях между группировками
  внутри коллектива существует взаимное расположение и понимание

Группировки конфликтуют между собой

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

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

Успехи или неудачи отдельных членов
  коллектива вызывают сопереживание, участие всех членов коллектива

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

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

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

Члены коллектива с ува-жением относятся к мнению друг друга

В коллективе каждый считает свое
  мнение главным и нетерпим к мнениям товарищей

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

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

Достижения или неудачи коллектива
  переживаются всеми как свои собственные

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

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

Новички чувствуют себя лишними,
  чужими, к ним нередко проявляется враждебность

Коллектив активен, полон энергии

Коллектив пассивен, инертен

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

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

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

Коллектив подразделяется на
  "при-вилегированных" и
  "пренебрегае-мых", здесь
  презрительно относятся к слабым, высмеивают их

У членов коллектива проявляется
  чувство гордости за свой коллектив, если его отмечают руководители

К похвалам и поощрениям коллектива
  здесь относятся равнодушно

alt: ОПРЕДЕЛЕНИЕ ПСИХОЛОГИЧЕСКОГО КЛИМАТА

alt: ОПРЕДЕЛЕНИЕ ПСИХОЛОГИЧЕСКОГО КЛИМАТА

title: Тест (для психологов)