Карпова Т.С. Базы данных: модели, разработка, реализация

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

Операции

над

отношениями.

Реляционная алгебра

Кю[Шифр

детали,

Наименование

детали;

Шифр детали,

Наименование

детали]R?

Операция деления достаточно сложна

для

абстрактного

представления.

Она

мо-

жет

быть заменена

последовательностью

других

операций.

Действительно,

вы-

полним

тот же

запрос

использованием

других

операций.

Для

этого

определим

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

приведет

нас к

конечно-

му

результату;

Построим

отношение,

которое моделирует

ситуацию,

когда

каждом цеху

изготавливается

вся

номенклатура,

это уже

построенное

памп

ранее

расши-

ренное

декартово

произведение

отношений

Rg.

Это

отношение

Rg;

Ra«

©R

Теперь

найдем перечень того,

что

из

обязательной номенклатуры

не

выпус-

каегся

некоторых

цехах

RII=

Re\Rio

найдем

те

цеха,

которых

не все

детали

выпускаются,

Д;Гя

этого

нам

надо отношение

спроектировать

на

столбец

«Цех»:

Rie-Rii[Uex]

теперь

из

перечня

всех

цехои

вычтем

те,

кто

выпускает

не

все

детали,

и по-

лучим

огвст

на

запрос,

это

будет

тот же

результат,

что

отношении

RI?.

Посмотрим,

как

работают

операции

реляционной

алгебры

для

другого

примера.

Возьмем

набор

отношений,

которые

моделируют

сдачу

сессии

студентами

неко-

торого

учебного

заведения.

Тема

весьма

попятная

привычная,

«=

<ФИО,

Дисциплина,

Оценка>;

ет

<ФИО,

Группа>;

Кз

Группы,

Дисцпшпша>,

где

информация

попытках (как

успешных,

так

неуспешных)

сдачи

эк-

заменов

сгудснтамп;

состав

групп;

—

список

дисциплин,

которые

надо

сдавать каждой

группе.

Домены

для

атрибутов

формально

задавать

не

будем,

но,

ориентируясь

на

здравый смысл,

будем

считать,

что

доменом

для

атрпбуга

Дисциплина

будег

множество

всех

дисциплин,

преподающихся

ВУЗе,

доменом

для

атрибута

Группа

будет

множество

всех

групп

ВУЗа

п т. д.

Цех

Цех!

R18

Цех

ЦсхЗ

Глава

Реляционная

модель данных

Покажем,

каким образом

можно

получить

этих

таблиц

интересующие

нас

сведения

помощью

реляционной

а.чгеоры.

каждом

из

приведенных

приме-

ров

путем

операций

над

исходными

отношениями

формпрую!ся

про-

межуточные

опюшепня

результирующее

(пношение

содержащее

требуе-

мую

информацию.

Список

студен

юн,

которые

сдали

икпамен

по

БД

на

«отлично».

Результат

можег

быть получен

применением

операции

фильтрации

по

сложному

усло-

вию

отношению

последующим

проектированием

на

атрибут

«ФИО»

(лам

ведь

требуется

только

список

фамилии)*

(RiiОценка

5 л

Дисциплина

«БД»])[ФИО];

Список

тех,

кто

должен

был

сдавать

экзамен

по

БД,

но

пока

еще

не

сдавал.

Сначала

найдем

всех,

кто

должен

был

сдавать экзамен

по БД. В

отношении

R;i

находится

список

псих

дисциплин,

по

которым каждая группа

должна

была

сдавать

экзамены,

ограничим

перечень

дисциплин

только

«БД».

"Для

того

чтобы

получит

список

студентов,

нам

надо

соединить*

отношение

Кз

отношением

котором

определен

список

студентов

каждой

группы.

(R2[Ra.HoAieprpynnbi

*•

К2.НомсрГруппы

Кз-Дпсцпплина

«БД»]

)[ФИО];

Теперь

получим

список всех,

кто

сдавал

экзамен

по

«БД»

(пас

пока

не

инте-

ресует

результат сдачи,

интересует

сам

факт

попытки

сдачи,

то

есть

прн-

сугстаие

отношении

RI):

R,-

(RifДисциплина

«БД»])[ФИО|;

и,

наконец,

результат

—

псе,

кто

есть

первом множестве,

но

не

но

втором;

;

Список

несчастных,

имеющих

несколько двоек:

S -

(Ri[R^I>MO

R'l.OHO

К,.Днсциплппа

^.Дисциплина

Ri.OuciiKa

2 л

RVOueiiKa

Я',)[ФИО]

Этот

пример

весьма

интересен;

для

поиска

строк,

удовлетворяющих

сово-

купности

условию

больше

одного,

применяется

операция

соединения

ноше-

ния

самим

собой.

Поэтому

мы как бы

взяли

копию

отношения

назва-

ли

се

R'/.

Список

круглых

отличников.

Строим

список

всех

пар

«ггудент—дпсшшлн-

на>,

которые

принципе

должны

быть

сданы:

R-i

(К2[Ка.Групна

Кз.Грунпа]

^)[ФИО,

Дисциплина];

Строим

список

нар

<студеит-дпсцигшша>,

где

получена

оценка

«отлично»:

(К,[0цспка

5])[ФИО,

Дисциплина!;

Строим

синеок

студентов,

что-либо

не

сдавших

на

«огличпо»:

Кв-ОгЛМФИО].

Задания

для

самостоятельной работы

Наконец,

исключив

последнее отношение

из

общего списка

студентов,

полу-

чаем

результат:

[ФИО]

R«

Обратите

внимание,

что для

получения

множества студентов, что-либо

не

сдав-

ших

на

«отлично»

(R(j),

мы

осуществили

«инверсию»

множества

всех

отлично

сданных

пар

<студснт—дпсццплина>

)

путем вычитания

его из

предвари-

тельного

построенного

универсального

множества

(R^).

Рекомендуем очень

внимательно

разобрать этот пример

вникнуть

смысл каждого

действия

—

это

очень

пригодится

для

понимания

реляционно!

алгебры.

Задания

для

самостоятельной

работы

Задание

Даны

отношения,

моделирующие

работу

байка

и его

филиалов.

Клиент

может

иметь несколько

счетов,

при

этом

они

могут быть размещены

как в

одном,

так

разных

филиалах

банка.

отношении

содержится

информация

обо

всех

клиентах

их

счетах

филиалах

нашего

банка.

Каждый

клиент,

соответствии

со

своим счетом, может

рассчитывать

на

некоторый кредит

от

нашего

байка,

сумма допустимого

кредита

также зафиксирована.

ФИО

клиента

№

филиала

Pailon

№

счета Остаток Кредит

использованием

языка

реляционной

алгебры составить

запросы,

позволяю-

щие

выбрать:

Филиалы,

клиенты

которых имеют счета

остатком,

превышающим

$1000,

Клиентов,

которые имеют счета

во

всех

филиалах

данного

банка.

Клиентов,

которые

имеют

только

по

одному

счету

разных

филиалах

банка.

То

есть

общем

этих

клиентов

может быть

несколько

счетов,

но в

одном

филиале

не

более одного счета.

Клиенты,

которые имеют счета

нескольких

филиалах

банка,

расположен-

ных

только

одном

районе.

Филиалы,

которые

не

имеют

ни

одного

клиента.

Филиалы,

которые

имеют

клиентов

остатком

на

счету

(ноль).

Глава

Реляционная модель данных

Филиалы,

которых есть клиенты

кредитом,

превышающим

остаток

на

счету

в 2

раза.

Задание

Даны

отношения,

моделирующие работу международной фирмы, имеющей

не-

сколько

филиалов. Филиалы фирмы могут

быть

расположены

разных стра-

нах,

это

отражено

отношении

(

Клиенты фирмы также

могут

быть

из

разных

стран,

и это

отражено

отношении

По

каждому

конкретному

заказу клиент

мог

заказать несколько разных

товаров.

Филиал

Страна

Филиал

Заказчик

заказа

Товар

Заказчик

Страна

Количество

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

составить

запросы, позволяющие

вы-

брать:

Заказчиков,

которые работают

со

всеми филиалами фирмы,

но

покупают

только один

товар.

Филиалы фирмы, которые торгуют всеми товарами,

Товары, которые фирма продает только

одной

стране.

Заказчиков,

которые

работают

филиалами

фирмы,

которые

расположены

только

одной

стране,

Филиалы,

которыми

не

работает

ни

один заказчик.

Заказчиков, которые работают только

филиалами,

расположенными

той

же

стране,

что и

заказчик.

Заказчиков, которые покупают

все

товары,

представ

ленные

отношении

Кз-

Задания

для

самостоятельной

работы

Задание

Даны отношения, моделирующие работу

фирмы,

занимающейся разработкой

программных систем. Каждый

сотрудник

административно

закреплен

только

за

одним

отделом.

Файлы

хранятся

на

разных

серверах.

На

разных

серверах

файлы могут иметь одинаковые имена. Создатель файла является

его

владель-

цем, поэтому

каждого файла только

один

владелец,

но

владелец файла может

разрешить пользоваться файлом другим сотрудникам. Существует множество

системного программного обеспечения, каждая программа может работать

с од-

ним

или

несколькими файлами, расположенными

на

одном

или

нескольких

серверах:

Название

файла

Имя

владельца файла

Сотрудник

Отдел

Название

программы

Название

файла

Сервер

Кз

Назиапие

файла

Название

сервера

использованием

реляционной алгебры

языка составить запросы,

позволяю-

щие

выбрать:

Файлы, которые имеют

нескольких

пользователей

из

разных,

отделов.

Программы,

которые

работают

только

одним файлом,

Файлы,

которые имеют

одно

тоже имя,

но

расположены

на

различных

сер-

верах

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

Файлы,

которыми

работают

сотрудники

всех отделов.

Файлы,

пользователями

которых являются

сотрудники

только

одного

отдела.

Программы, которые работают

со

всеми

серверами.

Отделы,

сотрудники которых

не

работают

ни

одним

файлом.

То

есть

отде-

лы,

которых

нет

нп

одного сотрудника, работающего

каким-нибудь

фай-

лом.

Отделы, сотрудники которых работают

со

всеми серверами,

Серверы,

которыми работают

сотрудники

только одного отдела.

ГЛАВА

Язык

SQL-

Формирование

запросов

базе

данных

История

развития

SQL

SQ£

(Structured Query Language)

—

Структурированный

Язык Запросов

стан-

дартный

язык запросов

по

работе

реляционными

БД.

Язык

SQL

появился

по-

сле

реляционной

алгебры,

и его

прототип

был

разработан

конце 70-х годов

компании

IBM

Research.

Он был

реализован

первом

прототипе реляционной

СУБД фирмы

IBM

System

R. В

дальнейшем этот язык

применялся

во

многих

коммерческих

СУБД

и в

силу своего широкого

распространения

постепенно

стал

стандартом

«де-факто»

для

языков манипулирования данными

реляционных

СУБД.

Первый международный стандарт языка

SQL был

принят

1989

г.

(далее

мы

будем называть

его

SQL/89

или

SQL1).

Иногда стандарт

SQU

также называют

стандартом

ANSI/ISO,

подавляющее большинстро доступных

на

рынке

СУБД

поддерживают

этот стандарт полностью. Однако развитие

информационных

тех-

нологий,

связанных

базами данных,

необходимость реализации переноси-

мых

приложений потребовали

скором времени доработки

расширения

пер-

вого стандарта SQL.

конце

1992

г. был

принят новый

международный

стандарт языка SQL, кото-

рый

дальнейшим будем называть

SQL/92

или

SQL2.

И он

не

лишен

недостат-

ков,

но в то же

время является

существенно

более точным

полным,

чем

SQL/89.

настоящий момент

большинство

производителей СУБД внесли

изменения

свои продукты так,

чтобы

они

большей

степени

удовлетворяли

стандарту

SQL2,

1999 году появился новый стандарт, названный

SQL3.

Если отличия между

стандартами

SQL1

SQL2

во

многом были

количественными,

то

стандарт

SQL3

соответствует

качественным

серьезным

преобразованиям*

SQL3

введены

но-

вые

типы

данных,

при

этом предполагается

возможность

задания сложных

История

развития

SQL - 67

структурированных типов данных, которые

большей степени

соответствуют

объектной ориентации. Наконец, добавлен

раздел,

который вводит стандарты

на

события

триггеры,

которые

ранее

не

затрагивались

стандартах,

хотя

давно

уже

широко использовались

коммерческих

СУБД.

стандарте

определены

возможности

четкой спецификации триггеров

как

совокупности события

дей-

ствия.

качестве

действия

могут выступать

не

только последовательность опе-

раторов

SQL,

но и

операторы управления ходом

выполнения

программы,

рам-

ках

управления

транзакциями

произошел

возврат

старой

модели

транзакций,

допускающей точки

сохранения

(savepoints),

возможность указания

операто-

ре

отката

RQQLBACK

точек возврата позволит откатывать транзакцию

не в

на-

чало,

а в

промежуточную ранее сохраненную

точку.

Такое

решение

повышает

гибкость

реализации-сложных

алгоритмов обработки информации.

зачем вообще нужны

эти

стандарты? Зачем

их

изобретают

почему надо

изу-

чать

их?

Текст

стандарта

SQL2

занимает

600

станиц сухого

формального

текста,

это

очень много,

кажется,

что это

просто происки разработчиков стандартов,

не

то, что

необходимо

рядовым разработчикам,

Однако

ни

один

серьезный

разработчик,

работающий

базами данных,

не

должен игнорировать стандарт,

для

этого существуют весьма веские причины. Разработка любой информа-

ционной

системы,

ориентированной

на

технологию

баз

данных

(а

других

ин-

формационных систем

на

настоящий

момент,

и не

бывает), является трудоем-

ким

процессом,

занимающим несколько

десятков

даже

сотен

человеко-месяцев.

Следует

отдавать

себе отчет,

что

нельзя

разработать сколько-нибудь серьезную

систему

за

несколько

дней.

Кроме того, развитие

вычислительной

техники,

сис-

тем

телекоммуникаций

программного

обеспечения

столь стремительно,

что

проект может устареть

еще до

момента

внедрения.

Но

развивается

не

только

вычислительная

техника,

изменяются

реальные

объекты,

поведение

которых

моделируется

использованием

как

самой

БД,

так и

процедур обработки инфор-

мации

ней,

то

есть

конкретных

приложений,

которые составляют реальное

на-

полнение

разрабатываемой информационной

системы,

Именно поэтому

проект

информационной

системы

должен

быть рассчитан

на

расширяемость

перено-

симость

на

другие

платформы,

Большинство

поставщиков

аппаратуры

про-

граммного

обеспечения

следуют

стратегии

поддержки

стандартов,

в'противном

случае

пользователи

просто

не

будут

их

покупать. Однако каждый поставщик

стремится улучшить свой продут введением

дополнительных

возможностей,

не

входящих

стандарт.

Выбор разработчиков, следовательно, таков; ориентиро-

ваться

только

на

экзотические

особенности

данного

продукта

либо

стараться

основном

придерживаться

стандарта.

Во

втором случае весь

-интеллектуаль-

ный

труд, вкладываемый

разработку,

становится более защищенным,

так как

система

приобретает

свойства

переносимости,

И в

случае

появления

более

пер-

спективной

платформы проект,

ориентированный

большей

степени

на

стан-

дарты,

может

быть легче

перенесен

на

нее,

чем

тот,

который

основном ориен-

тировался

на

особенности

конкретной платформы. Кроме того,

стандарты

— это

верный

ориентир

для

разработчиков,

так как все

поставщики

СУБД

своих

перспективных

разработках обязательно следуют стандарту,

можно

быть

уве-

ренным,

что в

конце

концов

стандарт будет реализован практически

во

всех

перспективных

СУБД.

Так

произошло

со

стандартом

SQ.L1,

так

происходит

со

стандартом

SQL2

так

будет

происходить

со

стандартом

SQL3.

Глава

Язык SQL. Формирование запросов

базе

данных

Для

поставщиков СУБД

стандарт

— это

путеводная звезда, которая гарантирует

правильное

направление работ.

вот

эффективность реализации стандарта

'—

это

гарантия

успеха.

SQL

нельзя

полной мере

отнести

традиционным

языкам

программирования,

он

не

содержит

традиционные

операторы, управляющие ходом

выполнения

про-

граммы,

операторы

описания

типов

многое

другой,

он

содержит только набор

стандартных операторов доступа

данным,

хранящимся

базе

данных.

Опера-

торы

SQL

встраиваются

базовый язык программирования, которым может быть

любой стандартный язык

типа

C++,

PL,

COBOL

и т. д.

Кроме

того,

операторы

SQL

могут выполняться непосредственно

интерактивном режиме.

Структура

SQL

отличие

от

реляционной алгебры,

где

были

представлены

только операции

запросов

к БД, SQL

является полным языком,

в нем

присутствуют

не

только

операции

запросов,

но и

операторы,

соответствующие

DDL —

Data

Definition

Language

—

языку

описания

данных.

Кроме того, язык

содержит

операторы,

пред-

назначенные

для

управления

(администрирования

) БД.

SQL

содержит

разделы, представленные

таблице 5.1:

Таблица

5.1.

Операторы

определения

данных

DDL

Оператор

CREATE

TABLE

DROP TABLE

ALTER

TABLE

CREATE

VIEW

ALTER

VIEW

DROP VIEW

CREATE INDEX

DROP INDEX

Смысл

Создать

таблицу

Удалить

таблицу

Изменить

таблицу

Создать

представление

Изменить

представление

Удалить

представ

те

Создать

индекс

Удалить

индекс

Действие

Создаст

новую

таблицу

в БД

Удаляет таблицу

из БД

Изменяет

структуру

существующей

таблицы

или

ограничения

целостности,

задаваемые

для

данной

таблицы

Создает

виртуальную

таблицу,

соответствующую

некоторому

SQI--

запросу

Изменяет

ранее

созданное

представление

Удаляет

ранее

созданное

преде

гавлсние

Создаст

индекс

для

некоторой

таблицы

для

обеспечения

быстрого

доступа

по

атрибутам,

«ходящим

индекс

Удаляет

ранее

созданный

индекс

Структура

SQL

Таблица

5.2.

Операторы

манипулирования

данными

Data

Manipulation

Language

(DMP)

Оператор

DELETE

INSERT

UPDATE

Смысл

Удалить

строки

Вставить строку

Обновить строку

Действие

Удаляет

одну

или

несколько

строк,,

соответствующих

условиям

фильтрации,

из

базовой

таблицы.

Применение

оператора

согласуется

принципами

поддержки

целостности,

поэтому

этот

оператор

не

всегда

может быть

выполнен

корректно,

даже

если

синтаксически

он

записан

правильно

Вставляет

одну

строку

базовую

таблицу.

Допустимы

модификации

оператора,

при

которых

сразу

несколько

строк могут быть

перенесены

из

о;июй

таблицы

или

запроса

базовую

таблицу

Обновляет

значения

одного

или

нескольких

столбцов

одной

или

нескольких

строках,

соответствующих

условиям

фильтрации

Таблица

5.3.

Язык

запросов

Data

Query

Language

(DQL)

Оператор

SELECT

Смысл

Выбрать строки

Действие

Оператор,

заменяющий

все

операторы

реляционной

алгебры

позволяющий

сформировать

результирующее

отношение,

соответствующее

запросу

Таблица

5.4.

Средства

управления

транзакциями'

Оператор

COMMIT

ROLLBACK

SAVEPOINT

Смысл

Завершить

транзакцию

Откатить

транзакцию

Сохранить

промежуточную

точку

выполнения

транзакции

Действие

Завершить

комплексную

взаимосвязанную

обработку

информации,

объединенную

транзакцию

Отменить

изменения,

проведенные

ходе

выполнения

транзакции

Сохранить

промежуточное

состояние

БД,

пометить

его

для

того,

чтобы

можно

было

дальнейшем

нему

вернуться

Таблица

5.5.

Средства

администрирования

данных

Оператор

ALTER

DATABASE

ALTER DBAREA

Смысл

Изменить

БД

Изменить

область

храпения

БД

Действие

Изменить

набор

бсиовпых

объектов

базе

данных,

ограничений,

касающихся

веси

базы

данных

Изменить

ранее

созданную область

хранения

Глава

Язык

SQL

Формирование

запросов

базе

данных

Таблица

5.5

(продолжение)

Оператор

ALTER

PASSWORD

CREATE DATABASE

CREATE

DBAREA

DROP

DATABASE

DROP DBAREA

GRANT

REVOKE

Смысл

Изменить

пароль

Создать

БД

Создать

область

хранения

Удалить

БД

Удалить

область

хранения

БД

Предоставить

права

Лишить

прав

Действие

Изменить пароль

для

всей базы данных

Создать

новую

базу дачных, определив

основные

параметры

для нес

Создать новую

область

хранения

сделать

се

доступной

для

размещения

данных

Удалить

существующую

базу

данных

(только

в том

случае, когда

вы

имеете

право выполнить

это

действие)

Удалить

существующую

область

хранения (если

в ней на

настоящий

момент

не

располагаются

активные

данные)

Предоставить

права

доступа

на ряд

действий

нал

некоторым

объектом

БД

Лишить

прав

доступа

некоторому

объекту

или

некоторым

действиям

над

объектом

Таблица 5*6.

Программный

SQL

Оператор

DECLARE

OPEN

FETCH

PREPARE

Смысл

Определяет

курсор

для

запроса

Открыть курсор

Считать

строку

из

множества

строк,

определенных

курсором

Закрыть курсор

Подготовить

оператор

SQL

динамическому

выполнению

Действие

Задает

некоторое

имя и

определяет

связанный

ним

запрос

к БД,

который

соответствует

виртуальному

набору

данных

Формирует

виртуальный

набор

данных,

соответствующий

описанию

указанного

курсора

текущему

состоянию

БД

Считывает

очередную

строку,

заданную

параметром

команды

из

виртуального

набора

данных,

соответствующего

открытому

курсору

Прекращает

доступ

виртуальному

набору

данных,

соответствующему

указанному

курсору

Сгенерировать

план

выполнения

запроса,

соответствующего

заданному

оператору

SQL