Разработка запросов

Создание таблиц

Создать таблицы базы данных можно тремя разными способами:

· В режиме конструктора;

· С помощью мастера;

· Путем ввода данных.

Если выбран режим ввода данных, программа формирует таблицу с 10 столбцами и 20 строками. После того как в таблицу будут добавлены данные, ее можно сохранить под каким-либо именем. Проще всего закрыть окно щелчком на кнопке с крестиком в верхнем правом углу окна. При этом программа выведет сообщение о необходимости сохранения таблицы.

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

Если выбран режим мастера, появляется возможность быстро сформировать таблицы, содержащие наиболее типичные, с точки зрения разработчиков программы, данные. Так, на рисунке 3.3 представлено диалоговое окно Создание таблиц, которое появилось в результате запуска мастера.

Рисунок 3.3- Создание таблиц с помощью мастера

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

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

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

Рисунок 3.4 – Основная форма конструктора

Рассмотрим разработку таблиц для учебной базы данных «электронный журнал». В качестве первой таблицы базы данных выберем таблицу «кадры». Первым шагом при разработке таблицы в режиме конструктора является заполнение верхней части формы на рисунке 3.5. В первом столбце формы имена полей будущей таблицы. Во втором столбце для каждого создаваемого поля Вы должны определить тип данных из перечня допустимых типов. Перечень допустимых типов данных имеет следующий вид:

Рисунок 3.5 – Разработка таблицы «кадры»

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

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

Свойство «Обязательное поле» может принимать два значения ДА или НЕТ. Если установит значение ДА, то при вводе данных в таблицу это поле нельзя будет пропустить, т.е. обязательно должны быть введены в это поле данные.

Свойство «Индексированное поле» устанавливается в значение ДА в случае, когда по данному полю предполагается упорядочивание.

Рассмотрим таблицу «Журнал».

Для полей Студент, Дисциплина, ВидЗанятий, Оценка в качестве типа данных выбирается мастер подстановок. Источниками данных для поля Студент будет таблица Кадры, для поля Дисциплина Учебный план, для полей ВидЗанятий и Оценка наборы предопределенных значений.

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

Рисунок 3.6 – Схема данных

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

Рисунок 3.7 – Установка свойств связей

Здесь требуются пояснения относительно трех установленных флажков.

Транзакции и целостность баз данных.

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

С понятием целостности БД непосредственно связано понятие транзакции.

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

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

Рисунок 3.8 – Пример транзакции в базе данных

Механизм управления транзакциями должен быть построен таким образом, что либо результаты всех операторов, входящих в транзакцию, отображаются в БД, либо воздействие всех этих операторов полностью отсутствует. Лозунг транзакции - "Все или ничего": при завершении транзакции оператором COMMIT результаты гарантированно фиксируются во внешней памяти (смысл слова commit - "зафиксировать" результаты транзакции); при завершении транзакции оператором ROLLBACK результаты гарантированно отсутствуют во внешней памяти (смысл слова rollback - ликвидировать результаты транзакции).

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

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

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

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

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

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

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

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

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

Разработка запросов

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

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

Любой запрос можно сохранить для последующего использования. Это можно сделать несколькими способами, например, выбирая позиции меню «Файл» - «Закрыть», ответив «Да» на вопрос о сохранении файла и задав после этого имя запроса. Имя запроса не может совпадать не только с именем другого запроса, но и с именем какой-либо таблицы в данной базе данных. Сохраненный запрос можно впоследствии «открывать», что означает его выполнение. Сохраненный запрос может быть скорректирован, если открыть его в режиме конструктора.

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

Рисунок 3.9 – Форма конструктора запросов

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

Можно переносить в бланк запроса не по одному полю, а сразу требуемую совокупность полей. Выделение полей, подлежащих переносу, осуществляется стандартным для Windows-систем способом (нажать клавишу Shift для выделения полей, расположенных друг за другом, и, удерживая ее, выбрать первое и последнее поле набора; выбирать поля при нажатой клавише Ctrl для полей, располагающихся в произвольном порядке не подряд друг за другом).

Можно перенести в бланк запроса одновременно все поля. Для этого надо установить указатель на заголовок списка полей и дважды щелкнуть кнопкой мыши или установить указатель на символ звездочки (*) и нажать кнопку мыши.

Не все поля, которые используются при формировании запроса, должны обязательно отражаться в ответе. Так, поле может быть необходимо для задания условия отбора, но надобность в его появлении в ответе отсутствует.Поля, выводимые в ответ, указываются в строке конструктора запроса «Вывод на экран». В соответствующих колонках этой строки указывается знак вхождения поля в ответ («•» – «галочка»). Если же поле не должно включаться в ответ, то этот знак отсутствует (□).

Рассмотрим пример запроса к БД на студентов получившим неудовлетворительные оценки (рисунок 3.10).

Рисунок 3.10 – Пример построения запроса

Для того чтобы посмотреть ответ, можно щелкнуть мышью на кнопке «Запуск» («!») на панели инструментов, либо выбрать опцию «Открыть» из меню «Запросы», либо щелкнуть на название запроса в списке запросов.

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

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

Для ввода выражения в пустом поле щелкните правой клавишей мышки и выберите команду меню Построить (рисунок 3.11).

Рисунок 3.11 – Создание вычисляемого поля

Само выражение разрабатывается в построителе выражений (рисунок 3.12).

Рисунок 3.12 - работа с построителем выражений

Укажем порядок сортировки в запросе в позиции Фамилия. Сортировка может быть выполнена по нескольким полям. Например, по группам и фамилиям. При этом первой позицией должна быть группа. На рисунке 3.13 представлен результат выполнения запроса.

Рисунок 3.13 – результат выполнения запроса