 |
Предикаты, использующие выборку. Вложенные подзапросы.
|
|
|
|
Предикаты – условия на запись, используемые в опции WHERE – как в запросах, так и командах модификации, в свою очередь, могут ссылаться на результаты запросов. Ниже следует перечисления таких предикатов.
1. r Знак_сравнения ALL (команда SELECT)
(здесь и далее r – некоторое выражение скалярного типа над полями записи некоторой таблицы; comp – знак сравнения)
Предикат истинен, если сравнение выполняется для стоящего слева значения поля и всех значений [SELECT], выбранных командой SELECT; при этом предполагается, что последняя выдает некоторый список значений List - формально, таблицу с единственным полем, совместимым по типу со значением выражения, стоящим слева. Условие считается истинным также, если подвыборка пуста.
r comp ALL (команда SELECT) = " vÎ List (r comp v)
2. r comp ANY | SOME (команда SELECT)
истинно, если сравнение выполняется для стоящего слева значения поля и хотя бы одного из значений, выбранных командой SELECT - снова предполагается, что последняя выдает некоторый список значений, совместимых по типу со значением поля. Если подвыборка пуста, условие считается ложным.
r comp ANY | SOME (команда SELECT) = $ vÎ List (r comp v)
3. r [NOT] IN (команда_SELECT)
имеет тот же смысл, что и одноименный предикат IN, рассмотренный выше, с той лишь разницей, что список значений не задается явно, но является результатом подвыборки; истинен, если значение поля совпадает хотя бы с одним значением из (единственного поля) результата выборки.
R IN [SELECT] = r Î [SELECT]
3. [NOT] EXISTS (команда_SELECT)
истинно, если подвыборка (не) пуста, т.е. (не) содержит по крайней мере одну строку.
EXISTS (SELECT) = Card([SELECT])>0 =$ r (rÎ [SELECT])
Пример.
Выдать список имен богатых покупателей (с кредитом выше среднего)
|
|
|
Синтаксис SQL разрешает использовать во вложенном запросе ссылки на имена полей внешнего запроса. Такие вложенные запросы называют соотнесенными.
Пример. Выдать список имен богатых в своем городе покупателей (с кредитом выше среднего по городу, в котором они живут)
В какой степени к результатам выборки можно относиться как к реальным таблицам? Многие версии предлагают опцию
INTO приемник выборки
Позволяя сохранить результаты выборки в реальном файле-таблице – постоянной или временной (курсоре), либо в оперативной памяти (массиве и пр.), а также переслать их а экран монитора, принтер и т.п.
Представления дают способ сослаться на результаты сравнения, не прибегая к его сохранению.
СОЗДАНИЕ ПРЕДСТАВЛЕНИЯ
CREATE VIEW Имя_представления AS команда SELECT
Замечание.
В FoxPro допустимы
1) параметризованные представления, ссылающиеся на переменные FoxPro - при использовании параметров нужно поставить знак? перед именем переменной.
2) модифицируемые представления; при этом cуть определения модифицируемости состоит в следующем
- каждая базовая таблица, в которой будет происходить реальная физическая модификация данных, обязана иметь первичный ключ;
- этот первичный ключ обязан содержаться в модифицируемом представлении.
Более точное определение модифицируемости представлений можно найти в документации.
СОЗДАНИЕ КУРСОРА
Курсор – это временная таблица, с которой можно обращаться точно также, как с обычной таблицей БД, за одним, но важным исключением - в отличие от таблиц и представлений, курсор не считается частью БД; его содержимое и само определение действительно только в текущем сеансе работы и "теряется" немедленно по его закрытии.
CREATE CURSOR имя_курсора
(список_описания_полей_курсора)
где описание поля курсора имеет вид
|
|
|
(имя_поля тип_поля [(ширина_поля [, точностьe])
[NULL | NOT NULL]
[CHECK Правило_корректности_поля
[ERROR Текст_сообщения_о_нарушении_правила]]
[DEFAULT значение_по_умолчанию]
[UNIQUE]
См. описание опций в команде CREATE TABLE.
Поскольку курсоры не считаются неотъемлемой частью SQL, мы снова отошлем к документации любознательного читателя, интересующегося деталями обработки курсоров в FoxPro.
ТРАНЗАКЦИИ
Введенное ранее понятие целостности БД - важнейший случай более общего понятия безопасности данных, подразумевающего включение в рассмотрение не только логической схемы самой БД, но и некоторую схему всех аспектов ее использования - от физического размещения и сохранности данных до определения прав пользователей - относимым к вопросам ведения или администрирования БД. Не касаясь здесь сколь-нибудь подробно этих вопросов, отметим лишь как существенно бóльшую безопасность данных, так и бóльшую сложность задач администрирования при использовании технологии "клиент-сервер"[7]. Одним из наиболее важных программных средств обеспечения безопасности данных является механизм поддержки транзакций.
Транзакция - логически единая операция по модификации базовых таблиц, состоящая из одной или нескольких команд СУБД, переводящая БД из одного целостного, корректного, с точки зрения предметной области, состояния в другое, также логически корректное.
Это понятие полностью совпадало бы с понятием процедуры, если бы ни одно обстоятельство, которое в данном случае нельзя игнорировать – недетерминизм, действия по модификации БД могут не выполниться, по не зависящим от программиста и программы внешним воздействиям. Состояние s команды/программы не полностью определяет результат ее выполнения F(s).
F(s,X)
X – скрытый фактор внешних воздействий, который может привести БД в некорректное состояние - F(s)¹F(s,X), для некоторых X.
В качестве такого скрытого фактора могут выступать самые разнообразные случайные факторы вроде сбоя электропитания, но даже при идеализации сетевой вычислительной среды нельзя игнорировать участия других пользователей, т.е. параллелизма действий различных модификаций.
Пример. Один пользователей удаляет запись, другой ее редактирует. Пользователь читает данные в момент выполнения другой команды.
|
|
|
Transaction(F,s,X)=if F(s)¹F(s,X) then F(s) [ else E ], " X
Поддержка транзакций гарантирует, что входящие в транзакцию команды либо выполнятся полностью, либо – если последнее невозможно по какой-либо причине (сбой энергоснабжения, конфликт действий пользователей в многопользовательской/ сетевой БД и пр.) – полное восстановление состояния БД на момент начала транзакции (или иной выбранной программистом контрольной точки соответствующей процедуры).
Утром деньги, вечером – стулья. Трансакция происходит от соотвествующего финансового термина – банковская сделка по переводу денег. Элементарная сделка такого рода состоит из операция снятия денег с одного счета и записи соответствующей суммы на другой. Понятно, что без выполнения той или иной операции трансакцию нельзя считать успешной. Более сложный пример такого рода колеективный обмен – например, квартир.
Определяющие свойства трансакции определяеют как ACID – Atomicity, Consistency, Isolation, and Durability - атомность, непротиворечивость, изолированность и стойкость.
Под атомностью – «все или ничего», все вовлеченные в трансакцию изменения либо происходят целиком, либо не происходят вообще.
Вместо – все или ничего, я бы сказал – не навреди.
Непротиворечивость обеспечивает, что изменения приводят к корректному состоянию данных.
Изоляция – трансакции могут вкладываться, но не могут пересекаться (промежуточные результаты одной трансакции доступны другой, лишь если та полностью вложена в первую, т.е. составляет ее часть)
Стойкость означает, что подтвержденные результаты трансакции должны выдерживать все внешние воздействия (скажем, падение сервера)
Простые примеры трансакции – сами команды SQL. Это – автоматические, не объявленные явно трансакции.
Примером транзакции может служить триггер каскадного удаления записи родительской таблицы, с последующим удалением записей дочерних таблиц. Все удаления должны быть либо осуществлены полностью, либо не осуществлены вообще – в противном случае в БД могут появиться записи-сироты.
|
|
|
 |
 |
Подобно традиционному понятию процедуры, транзакция – логическая операция, реализуемая с помощью команд языка.
Отличие - в явном допущении возможности лишь частичного выполнения.
Основной принцип транзакции - "все или ничего". Сбой при выполнении любой команды ведет к откату – невыполнению всей транзакции.
Понятие трансакции отражает существо, и скрывает сложную реализацию двух основных механизмов поддержки многопользовательских БД – буферизации и блокировки.
Внутри транзакции модификация содержимого таблиц БД производиться над их копиями в буфере, затем производиться блокировка – запрет на действия по модификации оригиналов таблиц другими пользователями. Если блокировка удачна, т.е. соответствующие таблицы не заблокированы другим пользователем, содержимое буфера (результат трансакции) записывается в собственно таблицы БД, если нет – оригиналы остаются неизменными.
Начало транзакции оформляется командой SQL
BEGIN TRANSACTION
закачивается либо командой корректного завершения транзакции
END /COMMIT [TRANSACTION]
либо командой "отката", т.е. возврата к состоянию БД к моменту начала транзакции
ROLLBACK
FoxPro поддерживает до 5 уровней вложенности транзакций. Однако, поскольку другие пользователи сети не имеют доступа к записи таблиц БД, модифицируемым во время исполнения транзакции, необходимо минимизировать время ее выполнения.
Примерные типы заданий.
Пример простой БД «Учет заказов».
Некоторая торговая фирма просит вас автоматизировать учет заказов, поступающих от покупателя к обслуживающему его постоянно продавцу. При этом предполагается хранить следующую информацию.
Сведения о покупателях:
- УЧЕТный №;
- ФАМИЛИЯ;
- КРЕДИТ (сумма, на которую покупатель имеет право покупать товары у данной фирмы)
- ДАТА рождения
- ГОРОД проживания
- АДРЕС
- ТЕЛЕФОН
- ТАБЕЛЬный № обслуживающего продавца;
Сведения о продавцах:
- ТАБЕЛЬный №;
- ФАМИЛИЯ;
- КОМИССИЯ - число из интервала [0,1] - доля от суммы заказа, составляющая выручку продавца;
- ДАТА рождения
- ГОРОД проживания
Сведения о заказах:
- ПРОДАВЕЦ - табельный № продавца;
- ПОКУПАТЕЛЬ - учетный № покупателя;
- ТОВАР - артикул (идентификатор) товара
- ЗАКАЗАН (дата заказа);
- ПОСТАВЛЕН (дата поставки)
- СУММА заказа, в рублях;
- КОЛИЧЕСТВО поставляемого товара (в штуках или других подходящих единицах)
Упражнения.
|
|
|
1) Определите правила проверки корректности для полей и записей таблиц, делая разумные допущения о том, что такое:
- реальная дата заказа, если фирма основана 1.01.1990;
- реальная сумма заказа, если фирма продает автомобили ценой от 10000 до 100000 рублей, в текущих ценах;
- корректные сведения о заказе, если учитывать произошедшую 1.01.1998 деноминацию - смену масштаба цен.
2) Определите правила целостности в БД, предполагая, в частности, что:
- при увольнении продавца информация о его заказах сохраняется;
- при разрыве отношений с покупателем информация о его заказах далее не хранится
3) Определите структуру БД "Учет заказов" в вашей СУБД
4) Измените структуру БД, для того, чтобы отразить старшинство членов фирмы, полагая, что у каждого продавца, кроме главы фирмы, есть непосредственный начальник.
5) Дополните модель сведениями о продаваемых фирмой товарах – а именно, их
- АРТИКУЛ,
- НАИМЕНОВАНИЕ,
- код ТИПа товара (пример кодировки - 0101 - 'Автомобили', 0903 - 'Канцелярские товары' и пр.),
- коде предприятия-ИЗГОТОВИТЕЛЯ,
- коде ЕДИНИЦЫ измерения (метры, кв. метры, килограммы, штуки),
- ЦЕНЕ за единицу
- и имеющемся на складе КОЛИЧЕСТВЕ.
Как изменится структура БД, если допустить, что
- Каждый покупатель всегда покупает только один товар?
- Каждый продавец продает постоянно лишь один товар?
- В общем (и подразумеваемом далее) случае неверно ни то, ни другое?
ЗАПРОСЫ К БАЗЕ ДАННЫХ.
Введем несколько определений, полезных для компактной формулировки задач. Каждая строка таблицы БД описывает некоторый объект предметной области (или отношение между объектами, которое снова удобно считать объектом). Атрибутами объекта назовем значения одного или нескольких полей соответствующей ему таблицы. Свойством или категорией объектов назовем некоторое естественно определяемое, в терминах предметной области, множество объектов. Категории объектов разделим на простые и сложные, в зависимости от того, содержится ли необходимая для определения категории информация в одной или нескольких таблицах БД. Примеры атрибутов и категорий для объектов БД «Учет заказов» приведены ниже.
Фильтрация таблиц. Определите атрибуты объектов по их свойствам, делая разумные допущения о предметной области и возможных значениях переменных.
Определите
(атрибуты покупателей:)
- учетные номера
- фамилии, телефоны и адреса
- даты рождения, по возрастанию
- возраст, по убыванию
- города проживания, в алфавитном порядке
- табельные номера обслуживающих продавцов
покупателей,
(простые категории покупателей:)
- живущих в данном городе (например, Казани)
- живущих в данных городах (например - Казани, Самаре или Москве)
- (не) совершеннолетних, старых, молодых
- (не) кредитоспособных
Определите
(атрибуты продавцов:)
- табельные номера
- фамилии и комиссионные, в процентах
- даты рождения, по убыванию
- возраст, по возрастанию
- города проживания, в алфавитном порядке
продавцов,
(простые категории продавцов:)
- живущих в данном городе (например, Казани)
- живущих в данных городах (например - Казани, Самаре или Москве)
- низко-, средне- высокооплачиваемых
- старых, молодых
Определите
(атрибуты заказов:)
- номера
- суммы
- артикулы товаров
- табельные номера продавцов
- учетные номера покупателей
заказов
(простые категории заказов:)
- на трехзначную сумму, по возрастанию суммы
- сделанных сегодня (вчера, на прошлой неделе, этим летом, в прошлом году), по возрастанию суммы заказа
- (не) выгодных для фирмы
- просроченных, по возрастанию даты заказа
- мелких, средних, крупных
Определите
(атрибуты товаров:)
- артикулы
- цену и названия, по возрастанию цены
товаров
(простые категории товаров:)
- дорогих (дешевых)
- имеющихся на складе в (не)достаточном количестве, отсутствующих на складе
|
|
|
