Microsoft Access - функционально полная реляционная СУБД

     При попытке пользователя нарушить эти условия в операциях добавления и удаления записей или обновления ключевых данных в связанных таблицах Access выводит соответствующее сообщение и не допускает выполнения операции.

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

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

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

     Каскадное обновление и удаление связанных записей

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

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

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

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

     Язык  SQL (аббревиатура Structured Query Language) – это язык структурированных запросов, стандартный язык, предназначенный для создания баз данных, добавления новых и поддержки имеющихся данных, а также извлечения требуемой информации. Язык SQL с самого начала был создан, чтобы работать с данными из тех баз, которые следуют реляционной модели.

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

     При создании каждого запроса MS Access автоматически  составляет эквивалентную ему инструкцию SQL. Изменения, внесенные в инструкцию SQL, автоматически отражаются в бланке конструктора.

     Просмотрим или изменим инструкцию SQL:

     1. Выполнив анализ предметной области,  создадим групповой запрос «Список  студентов группы ДФД-31» на  основании таблиц «Студент» и  «Группа» (рис. 21), в который включим  список следующих полей таблиц:

     Группа.[Обозначение  группы], Студент.[Номер зачетной книжки], Студент.Фамилия, Студент.Имя, Студент.Отчество, Студент.Год рождения, Студент.Адрес, Студент.[Домашний телефон], Студент.[Балл при поступлении].

     В поле Группа.[Обозначение группы] используем условие отбора по коду группы: «ДФД-31».

     Групповой запрос «Список студентов группы ДФД-31» в MS Access в режиме конструктора

     2. Выберем Режим SQL в меню Вид или через кнопку «Режим SQL» на панели инструментов. На экране появится текущий запрос в режиме SQL эквивалентный созданному в режиме конструктора (рис. 22).

     Запрос  «Список студентов группы ДФД-31»  в MS Access в режиме SQL

     3. В этот запрос можно внести  изменения, и эти изменения  будут отражены в бланке конструктора. Например, попробуем удалить в  текстовом окне режима SQL записи Студент.Адрес, Студент.[Домашний телефон], Студент.[Балл при поступлении].

     Результат выполнения измененного запроса  «Список студентов группы ДФД-31»

     Основной  инструкцией языка SQL, всегда содержащейся в запросе, является команда SELECT [2].

     В простейшей форме эта команда занимается поиском информации в таблице. Она имеет следующий формат:

     SELECT field1, field 2, …

     FROM Table;

     Здесь field1, field 2,… – список столбцов таблицы Table, которые должны быть представлены в результате запроса.

     Для получения всей таблицы вместо списка столбцов необходимо поставить символ «*» (звездочка).

     Команда SELECT имеет следующие параметры:

     A. DISTINCT (получить список без повторений)

     Формат: SELECT DISTINCT field1, field2, …

     FROM Table;

     B. ALL (получить список со всеми повторениями)

     Формат: SELECT ALL field1, field2, …

     FROM Table;

     C. WHERE (извлечь нужные строки)

     Формат: SELECT field1, field2, …

     FROM Table WHERE predicate;

     Здесь predicate – логическое выражение, которое  может быть истинно или ложно  для каждой записи таблицы.

     D. ORDER BY (рассортировать выходные данные)

     Формат: SELECT field1, field2, …

     FROM Table

     ORDER BY field1 DESC;

     Это означает, что выходные данные будут  рассортированы по столбцу field1 в порядке убывания (порядок возрастания задается по умолчанию или с помощью слова ASC).

     E. GROUP BY (группировать выходные данные)

     Формат: SELECT field1, field2, …

     FROM Table

     GROUP BY [field1, field2, …]

     ORDER BY field1 DESC;

     Группировка – это объединение записей  в соответствии со значениями некоторого заданного поля.

     Агрегатные функции

     Существуют  следующие основные агрегатные функции:

• Count – определение численности;
• Sum – определение суммы;
• First/Last – определение первого/последнего значения;
• Min/Max – определение минимума/максимума;
• Avg – определение среднего значения.

     Для обозначения связи двух таблиц дополнительно к команде FROM используются атрибуты INNER JOIN и ON.

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

     С помощью атрибута INNER JOIN мы указали, что таблица «Студент» связана с таблицей «Группа». А с помощью атрибута ON мы указали, как именно связаны между собой две таблицы «Студент» и «Группа»: по полю Код группы (FROM Группа INNER JOIN Студент ON Группа.[Код группы] = Студент.[Код группы]).

     С помощью атрибута WHERE мы указали, что  нужно извлечь только строки, которые  содержат запись в поле Группа.[Обозначение  группы] «ДФД-31»: WHERE (((Группа.[Обозначение  группы])="ДФД-31")).

     ПРИМЕЧАНИЕ. Обратите внимание на то, что в качестве имени поля всегда используется то имя, которое было присвоено полю в процессе создания таблицы в режиме конструктора, а не надпись, которую мы видим на экране в таблице в режиме заполнения.

     4. Запрос может быть создан также  только исключительно через Режим SQL в конструкторе.

     Выберем в окне базы данных «Запрос», «Создать»  через опцию «Конструктор». В  диалоговом окне «Добавление таблицы» выберем опцию «Закрыть». В меню Вид выберите Режим SQL. Появится окно «Запрос на выборку». Наберем следующую инструкцию SQL:

     Эта инструкция предназначена для получения  списков всех предметов и их кодов. Данные для этого запроса берутся  из таблицы «Предмет». Результатом  выполнения данного запроса будет  таблица, состоящая из двух полей (Наименование предмета и Код предмета) и из всех записей таблицы «Предмет».

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

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

     Рассмотрим  запрос «План проведения занятий  в группе», созданный на основании  анализа предметной области:

     SELECT Группа.[Обозначение группы], Предмет.[Наименование  предмета], Преподаватель.Фамилия, Преподаватель.Имя,  Преподаватель.Отчество, Преподаватель.[Табельный номер], [Учебный план].Часы, [Учебный план].[Вид занятия], [Учебный план].Семестр

     FROM (Группа INNER JOIN Студент ON Группа.[Код  группы] = Студент.[Код группы]) INNER JOIN (Преподаватель INNER JOIN (Предмет INNER JOIN ([Учебный план] INNER JOIN Успеваемость ON [Учебный план].[Код учебного плана] = Успеваемость.[Код учебного плана]) ON Предмет.[Код предмета] = [Учебный план].[Код предмета]) ON Преподаватель.[Код преподавателя] = [Учебный план].[Код преподавателя]) ON Студент.[Код студента] = Успеваемость.[Код студента]

     GROUP BY Группа.[Обозначение группы], Предмет.[Наименование  предмета], Преподаватель.Фамилия, Преподаватель.Имя,  Преподаватель.Отчество, Преподаватель.[Табельный  номер], [Учебный план].Часы, [Учебный  план].[Вид занятия], [Учебный план].Семестр

     ORDER BY Группа.[Обозначение группы], Предмет.[Наименование  предмета], Преподаватель.Фамилия;

     Результатом будет следующий запрос на выборку

     Аналогично, следует подготовить запрос «Экзаменационная ведомость», созданный на основании анализа предметной области (см. рис. 5):

     SELECT Предмет.[Наименование предмета], Группа.[Обозначение  группы], Студент.[Номер зачетной  книжки], Студент.Фамилия, Студент.Имя,  Студент.Отчество, Успеваемость.Оценка, Успеваемость.[Дата сдачи], [Учебный план].Семестр, [Учебный план].[Вид сдачи]

     FROM (Группа INNER JOIN Студент ON Группа.[Код  группы] = Студент.[Код группы]) INNER JOIN (Преподаватель INNER JOIN (Предмет INNER JOIN ([Учебный план] INNER JOIN Успеваемость ON [Учебный план].[Код учебного плана] = Успеваемость.[Код учебного плана]) ON Предмет.[Код предмета] = [Учебный план].[Код предмета]) ON Преподаватель.[Код преподавателя] = [Учебный план].[Код преподавателя]) ON Студент.[Код студента] = Успеваемость.[Код студента]

     WHERE ((([Учебный план].[Вид сдачи])="экзамен" Or ([Учебный план].[Вид сдачи])="Зачет"))

     ORDER BY Предмет.[Наименование предмета], Группа.[Обозначение группы], Студент.Фамилия;

     Рассмотрим  для примера запрос «Успеваемость  студентов в группах по предметам  у преподавателей». Запишем текст запроса на языке SQL в окно Режим SQL:

     SELECT Группа.[Обозначение группы], Студент.[Номер  зачетной книжки], Студент.Фамилия,  Студент.Имя, Студент.Отчество, Преподаватель.Фамилия,  Преподаватель.Имя, Преподаватель.Отчество, Предмет.[Наименование предмета], Avg(Успеваемость.Оценка) AS [Avg-Оценка]

     FROM (Группа INNER JOIN Студент ON Группа.[Код  группы] = Студент.[Код группы]) INNER JOIN (Преподаватель INNER JOIN (Предмет INNER JOIN ([Учебный план] INNER JOIN Успеваемость ON [Учебный план].[Код учебного плана] = Успеваемость.[Код учебного плана]) ON Предмет.[Код предмета] = [Учебный план].[Код предмета]) ON Преподаватель.[Код преподавателя] = [Учебный план].[Код преподавателя]) ON Студент.[Код студента] = Успеваемость.[Код студента]

     GROUP BY Группа.[Обозначение группы], Студент.[Номер зачетной книжки], Студент.Фамилия, Студент.Имя, Студент.Отчество, Преподаватель.Фамилия, Преподаватель.Имя, Преподаватель.Отчество, Предмет.[Наименование предмета]