Гринберг А.С., Иванюкович В.А., Новыш Б.В., Шешолко В.К. Ситуационный анализ и моделирование на основе информационных технологий баз данных

Подождите немного. Документ загружается.

EXTEND A ADD EXP AS Z;

Результатом этого выражения будет отношение с заголовком,

эквивалентным заголовку отношения А, расширенному новым атрибутом Z,

который рассчитывается скалярным выражением exp для кортежа отношения

А. При этом отношение А не должно иметь атрибута Z и выражение exp не

должно ссылаться на атрибут Z. Кардинальное число результата равно

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

единица.

Пример: Подсчитать количество поставок, сделанных каждым

поставщиком.

EXTEND Поставщики ADD COUNT ((Поставки RENAME П№ AS X)

WHERE X= П№) AS Кол_п;

Результат действия этого выражения показан на рис.j3.3.1. Для данного

поставщика в отношении ПОСТАВЩИКИ выражение

((Поставки RENAME П№ AS X) WHERE X= П№);

дает множество поставок, сделанных этим поставщиком. Затем итоговая

функция COUNT применяется для этого множества поставок и возвращает

соответствующее кардинальное

число, которое равно количеству

поставок.

Итоговая функция берет в

качестве аргумента множество

значений и возвращает одно

значение. Кроме функции COUNT (подсчет) итоговыми функциями являются SUM

(сумма), AVG (среднее значение), MAX (максимальное значение), MIN

(минимальное значение), StDev (среднеквадратичное отклонение от среднего

значения поля) и Var (дисперсия значений поля). Если аргумент такой функции

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

MAX и MIN возвращают максимальное и минимальное значения

соответствующего домена.

П№ Имя_П Статус Гор Кол_

П1 Волк 20 Брест 6

П2 Заяц 10 Минск 2

П3 Лев 30 Гродно 1

П4 Лиса 20 Минск 3

П5 Бык 30 Брест 0

Рис.4.3.1

Таким образом, операция расширения обеспечивает возможность

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

вертикальных вычислений выполняет операция подведения итогов SUMMARIZE:

SUMMARIZE A BY (A1,A2,…,AN) ADD EXP AS Z;

Здесь A1,A2,…,An – отдельные атрибуты отношения А. Результатом этого

выражения будет отношение с заголовком {A1,A2,…,An,Z} и с телом,

содержащим все такие кортежи t, которые являются кортежами проекции

отношения А по атрибутам A1,A2,…,An, расширенного значением для нового

атрибута Z. Такое новое значение Z подсчитывается вычислением итогового

значения exp по всем кортежам отношения А, которое имеет те же самые

значения для атрибутов A1,A2,…,An, что и кортеж t. Кардинальное число

результирующего отношения равно кардинальному числу проекции отношения

А по атрибутам A1,A2,…,An,, а степень равна степени такой проекции плюс

единица. Например, количество поставок, сделанных каждым поставщиком

может быть получено при помощи выражения

SUMMARIZE Поставки BY (П№) ADD COUNT AS Кол_П;

Результат операции (рис.4.3.2) не содержит сведений о

поставках, сделанных поставщиком П5 (сравните с

примером, показанным на рис.4.3.1, где поставщик сделал

ноль поставок). Причина состоит в том, что поставщика П5

нет в отношении ПОСТАВКИ.

Реляционная модель может включать также операции реляционного

присвоения, которые дают возможность «запоминать» значение некоторых

алгебраических выражений в базе данных и таким образом изменять состояние

базы данных или, иначе говоря, обновлять базу данных:

Поставки  Поставки MINUS (Поставки WHERE Кол = 0);

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

INSERT, DELETE и UPDATE:

INSERT (Поставщики WHERE Гор_П = Минск ) INTO Temp;

(здесь выбранные данные вставлены в отношение Temp, которое должно быть

совместимым по типу с отношением Поставщики).

П№ Кол_П

П1 6

П2 2

П3 1

П4 3

Рис.4.3.2.

UPDATE (Поставщики WHERE Гор_П = Брест) СТАТУС  40;

DELETE Поставщики WHERE Статус < 20;

Все операции обновления действуют на уровне множеств (например,

операция DELETE удаляет множество кортежей из целевого отношения) и

должны контролироваться с помощью предиката рассматриваемого отношения.

Примеры использования реляционной алгебры для выражения словесных

запросов в виде формул

Получить имена поставщиков, которые поставляют деталь Д2.

((Поставки JOIN Поставщики) WHERE Д№=’Д2’) [Имя_П];

Получить имена поставщиков, которые поставляют по крайней мере одну

черную деталь.

(((Детали WHERE Цв = ‘Черный’) JOIN Поставки) [П№] JOIN

Поставщики) [Имя_П]

или

(((Детали WHERE Цв = ‘Черный’) [Д№] JOIN Поставки) JOIN

Поставщики) [Имя_П]

Получить имена поставщиков, которые поставляют все детали.

((Поставки [П№,Д№] DIVIDEBY Детали [Д№] JOIN Поставщики)

[Имя_П]

Получить номера поставщиков, которые поставляют по крайней мере все

те детали, которые поставляет поставщик П2.

Поставки [П№,Д№] DIVIDEDBY (Поставки WHERE Имя_П=’П2’) [Д№]

Получить имена поставщиков, которые не поставляют деталь Д2.

((Поставщики [П№] MINUS (Поставки WHERE Д№=’Д2’) [П№])

JOIN (Поставщики) [Имя_П]

Покажем, в качестве примера, как выглядит запись команды на

выполнение последнего запроса, сделанная на языке SQL:

SELECT DISTINCT Поставщики.[Имя_П] FROM Поставщики

WHERE Поставщики.[Имя_П] NOT IN

(SELECT Поставки.[П№] FROM Поставки

WHERE Поставки.[Д№]);

5 . ОС Н О В Ы SQ L

Познакомимся с синтаксисом и конструкцией языка структурированных

запросов SQL (Structured Query Language). Это позволит лучше понимать

процесс обработки данных и создавать эффективные запросы.

SQL – наиболее распространенный язык управления базами данных

архитектуры клиент-сервер. Стандарт современной версии SQL был принят в

1992 году. Эта версия дает возможность использовать общий набор операторов

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

MSjACCESSj2000 использует версию Jetj4.0jSQL. СУБД SQLjServerj7.0,

работающая под управлением WindowsjNT (или 2000), использует

расширенную версию SQL – Transact-SQL. Компании, разрабатывающие SQL-

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

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

команд, описанный в ANSIjSQL-92. Мы будем уделять большее внимание

версии Jetj4.0jSQL.

Все основные термины языка программирования можно разделить на

различные категории в соответствии с их назначением. Для SQL можно ввести

следующие категории терминов:

1. Команды – слова, определяющие действие, которое необходимо выполнить

(например, SELECT, JOIN).

2. Условия – ограничивают диапазон значений элементов, входящих в запрос

(например, WHERE).

3. Модификаторы – изменяют выполнение команды или результата

выполнения команды (например, ORDER BY).

4. Операторы – сравнивают значения и применяются в условиях отбора

записей (например, =, >,<,<>), а также для создания связей между

таблицами.

5. Статистические (или агрегатные) функции – возвращают одно

результирующее значение, вычисленное на основании набора данных

(например, MIN, MAX).

6. Слова, употребляемые для управления курсором и выполняющие некоторые

дополнительные функции.

Термины SQL, относящиеся к перечисленным категориям, называются

ключевыми словами (они обычно записываются прописными буквами). Для

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

параметры (которые, как правило, записываются строчными буквами).

Синтаксис SQL предусматривает использование знаков пунктуации:

 запятые используются для разделения компонентов списка параметров;

 точки используются для отделения имен таблиц от имен полей;

 точка с запятой ставится в конце инструкции JetjSQL;

 квадратные скобки используются для выделения имен полей только тогда,

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

разрешенные в SQL;

 одинарная (предпочтительней) или двойная кавычка применяется для

описания строчных переменных;

 символы * и ? используются для маскирования окончания или одного

символа соответственно;

 символ # применяется для представления одной цифры в операторе LIKE.

Необходимо помнить, что в разных версиях SQL могут быть некоторые

различия в использовании знаков пунктуации.

5.1. Определение данных в SQL

Домены в SQL далеки от настоящих реляционных доменов. Единственное

назначение доменов в SQL – обеспечить возможность определить

элементарную спецификацию типа данных:

CREATE DOMAIN domain data-type

[default-definition]

[domain-constraint-definition-list]

SQL поддерживает следующие типы данных: CHARACTERj(n); BITj(n);

NUMERICj(p,q); DECIMALj(p,q); INTEGER; SMALLINT; FLOATj(p); DATE; TIME;

TIMESTAMP; INTERVAL. Если в СУБД ACCESS используется модификатор

текстового типа данных CHARACTER без указания размера, то приписывается

тип данных MEMO.

Необязательный элемент определения по умолчанию default-definition

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

определенному на домене.

Необязательный список определений ограничений доменов domain-constraint-

definition-list представляет набор ограничений целостности, применяемое к

каждому столбцу, определенному на домене.

Пример:

CREATE DOMAIN COLORS CHAR(6) DEFAULT ‘???’

CONSTRAINT VALID COLORS

CHECK ( VALUE IN (‘Red’,’Yellow’,’Blue’,’Green’,’???’ ));

Существующий домен можно изменить с помощью оператора ALTER

DOMAIN или уничтожить с помощью оператора DROP DOMAIN.

Для создания таблиц в SQL используются следующие операторы:

CREATE TABLE имя таблицы (имя_поля тип_данных [размер поля][, имя_поля тип_данных

[размер поля]…])

Описание свойств полей отделяется от их имен пробелом.

CONSTRAINTjимяjиндексаj{PRIMARYKEY/UNIQUE/REFERENCES

связаннаяjтаблицаj[(связанноеjполе)]}

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

(PRIMARYKEY) или уникальным (UNIQUE). Для создания связи между данным

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

REFERENCES связанная таблица [(связанное поле)]. Элемент [(связанное

поле)] используется лишь тогда, когда внешнее поле связанное поле не является

первичным ключом внешней таблицы.

CHECK (УСЛОВИЕ) – создает ограничивающее условие.

CREATEjUNIQUEjINDEXjимяjиндексаjONjимяjтаблицыj(имяjполя [ASC/DESC] [, имяjполя

[ASC/DESC],…]) [WITH {PRIMARY/DISALLOW NULL/IGNORE NULL})

– создает индекс для одного или нескольких полей таблицы. Параметр

DISALLOW NULL не допускает добавление записей, индексированное поле

которых имеет значение NULL. Параметр IGNORE NULL запрещает включение в

индекс записей, имеющих значения NULL в индексируемых полях.

ALTER TABLE имя таблицы … – добавляет в таблицу новое поле при помощи

параметра (ADD COLUMN имя поля…), или удаляет уже существующее (DROP

COLUMN имя поля…), или изменяет свойства поля (имя_поля тип_данных

[размер поля]).

DROP INDEX имя индекса ON имя таблицы – удаляет индекс из таблицы.

DROP TABLE имя таблицы – удаляет таблицу из базы данных.

5.2. Запрос на выборку

Для создания запроса на выборку используется команда SELECT. Она

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

поля из выбранных записей исходных таблиц:

SELECT [ALL/DISTINCT/DISTINCTROW] [TOP n [PERCENT]] список полей

FROM имена таблиц

[WHERE условие отбора]

[ORDER BY столбцы сортировки [ASC/DESC]];

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

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

(или в представлении). Имена полей разделяются запятыми. необязательные

параметры ALL, DISTINCT и DISTINCTROW определяют способ отбора строк:

 ALL – включает все строки соответствующие указанным далее условиям

отбора;

 DISTINCT (ключевое слово из ANSI SQL-92) – исключает строки с

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

набора записей;

 DISTINCTROW (ключевое слово из Jet SQL-92) – исключает повторяющиеся

строки на основе анализа полных строк исходной таблицы, независимо

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

различаются.

Например, пусть в исходной таблице присутствует фамилия и имя. Есть

повторяющиеся фамилии при различных именах. Параметр DISTINCT возвратит

повторяющуюся фамилию один раз, а DISTINCTROW – столько раз, сколько

было неповторяющихся имен.

Необязательный параметр TOPjnj[PERCENT] ограничивает количество

записей в результирующей таблице первыми n или n% набора.

Оператор FROM определяет имена таблиц, из которых должны выбираться

данные.

WHERE определяет условие для отбора записей. Условие задается

текстовым оператором типа LIKE для текстовых полей или числовыми

операторами типа >,<,=, <j>,>= для числовых полей. Если WHERE не

использован, то запрос возвратит все записи, удовлетворяющие критерию

SELECT.

Модификатор ORDERjBY определяет порядок сортировки записей в

созданной таблице. Ключевыми словами ASC и DESC можно определить

сортировку по возрастанию или убыванию соответственно.

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

SELECT [Код отдела], Бюджет

FROM Отделы

WHERE Бюджет>5000000

ORDER BY [Код отдела];

Результатом выполнения запроса будет новая таблица, содержащая два

столбца Кодjотдела и Бюджет и множество строк, удовлетворяющих условию

Бюджет>5000000, отсортированных по коду отдела в алфавитном порядке.

5.3. Статистические функции

Статистические (или агрегатные) функции используются тогда, когда

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

минимальное, максимальное и т.п.) группы записей с общим значением

атрибута. Для этого используется инструкция GROUP BY:

SELECT [ALL/DISTINCT/DISTINCTROW] статистическая функция (имя поля) AS заголовок

поля [, список полей]

FROM имена таблиц

[WHERE условие отбора]

GROUP BY условие группировки

[HAVING условие для результата]

[ORDER BY столбцы сортировки];

Поле, используемое как аргумент статистической функции, должно

содержать данные числового типа.

Ключевое слово AS определяет заголовок столбца результирующего

набора записей. GROUP BY определяет столбец, по значениям которого записи

объединяются в группы, к которым применяется статистическая функция.

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

результирующего столбца, полученного в результате группировки и

применения статистической функции.

Пример инструкции SQL, применяющей статистическую функцию:

SELECT DISTINCT Отделы.Наименование

AVG (Зарплата) AS [средняя зарплата]

FROM Сотрудники

GROUP BY Код_отдела

HAVING AVG (Зарплата)>100

ORDER BY [средняя зарплата] DESC;

Результирующая таблица будет содержать два столбца и записи для

каждого отдела, в котором средняя зарплата больше 100. Сортировка сделана в

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

5.4. Создание связей

Для организации связей между таблицами и их объединения SQL

предлагает функцию JOIN…ON, которая определяет подключаемую таблицу и

связь между полями:

SELECT [ALL/DISTINCT/DISTINCTROW] список полей

FROM имя таблицы {INNER/LEFT/RIGHT} JOIN связанная таблица [имя таблицы

{INNER/LEFT/RIGHT} JOIN связанная таблица

ON условие связи]

ON условие связи

[WHERE условие отбора]

[ORDER BY столбцы сортировки];

В приведенной инструкции показано, как оператор JOIN окружен именами двух

связываемых таблиц, причем вместо правого имени может использоваться

повторно конструкция JOINj…jON, называемая вложенной, это означает, что

первая таблица объединяется с объединением второй и третьей таблиц.

Инструкция SQL может содержать набор нескольких вложенных конструкций

JOINj…jON. Их число обычно равно общему количеству таблиц, включенных в

запрос, минус один. Перед оператором JOIN должен быть указан тип

объединения:

INNER – объединяет записи из двух таблиц, если связующие поля этих таблиц

содержат одинаковые значения;

LEFT (RIGHT) – объединяет записи исходных таблиц при использовании любом

предложении FROM. Левое внешнее объединение включает все записи из

первой (левой) таблицы, даже если нет совпадающих значений для записей из

второй таблицы. Правое внешнее объединение включает все записи из второй

(правой) таблицы, даже если нет совпадающих значений для записей из первой

таблицы.

ON условие связи позволяет описать два поля и связь между ними (одно поле в

таблице связанная таблица, второе – в таблице имя таблицы). В выражении

условие связи присутствует оператор сравнения значений полей, который

возвращает значения TRUE или FALSE. Если значение выражения True,

объединенная запись включается в результирующий набор.

Пример инструкции на объединение базы данных, изображенной на рис. 1.7.3:

SELECT DISTINCT Сотрудники.Фамилия, Отделы.Наименование, Сотрудники.Зарплата

FROM Отделы INNER JOIN Сотрудники

ON Отделы.Код_отдела = Сотрудники.Код_отдела;

(Более простую и гибкую инструкцию на объединение можно создать и при

помощи оператора WHERE: