История развития SQL

SQL (Structured Query Language) — Структурированный Язык Запросов — стандартный язык запросов по работе с реляционными БД. Язык SQL появился после реляционной алгебры, и его прототип был разработан в конце 70-х годов в компании IBM Research. Он был реализован в первом прототипе реляционной СУБД фирмы IBM System R. В дальнейшем этот язык применялся во многих коммерческих СУБД и в силу своего широкого распространения постепенно стал стандартом «де-факто» для языков манипулирования данными в реляционных СУБД.

Первый международный стандарт языка SQL был принят в 1989 г. (далее мы будем называть его SQL/89 или SQL1). Иногда стандарт SQL1 также называют стандартом ANSI/ISO, и подавляющее большинство доступных на рынке СУБД поддерживают этот стандарт полностью. Однако развитие информационных технологий, связанных с базами данных, и необходимость реализации переносимых приложений потребовали в скором времени доработки и расширения первого стандарта SQL.

В конце 1992 г. был принят новый международный стандарт языка SQL, который в дальнейшим будем называть SQL/92 или SQL2. И он не лишен недостатков, но в то же время является существенно более точным и полным, чем SQL/89. В настоящий момент большинство производителей СУБД внесли изменения в свои продукты так, чтобы они в большей степени удовлетворяли стандарту SQL2.

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

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

В качестве действия могут выступать

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

А зачем вообще нужны эти стандарты? Зачем их изобретают и почему надо изучать их? Текст стандарта SQL2 занимает 600 станиц сухого формального текста, это очень много, и кажется, что это просто происки разработчиков стандартов, а не то, что необходимо рядовым разработчикам. Однако ни один серьезный разработчик, работающий с базами данных, не должен игнорировать стандарт, и для этого существуют весьма веские причины. Разработка любой информационной системы, ориентированной на технологию баз данных' (а других информационных систем на настоящий момент и не бывает), является трудоемким процессом, занимающим несколько десятков и даже сотен человеко-месяцев. Следует отдавать себе отчет, что нельзя разработать сколько-нибудь серьезную систему за несколько дней. Кроме того, развитие вычислительной техники, систем телекоммуникаций и программного обеспечения столь стремительно, что проект может устареть еще до момента внедрения. Но развивается не только вычислительная техника, изменяются и реальные объекты, поведение которых моделируется использованием как самой БД, так и процедур обработки информации в ней, то есть конкретных приложений, которые составляют реальное наполнение разрабатываемой информационной системы. Именно поэтому проект информационной системы должен быть рассчитан на расширяемость и переносимость на другие платформы. Большинство поставщиков аппаратуры и программного обеспечения следуют стратегии поддержки стандартов, в противном случае пользователи просто не будут их покупать.

Однако каждый поставщик cтремится улучшить

Однако каждый поставщик cтремится улучшить свой продут введением дополнительных возможностей, не входящих в стандарт. Выбор разработчиков, следовательно, таков: ориентироваться только на экзотические особенности данного продукта либо стараться в основном придерживаться стандарта. Во втором случае весь интеллектуальный труд, вкладываемый в разработку, становится более защищенным, так как система приобретает свойства переносимости. И в случае появления более перспективной платформы проект, ориентированный в большей степени на стандарты, может быть легче перенесен на нее, чем тот, который в основном ориентировался на особенности конкретной платформы. Кроме того, стандарты — это верный ориентир для разработчиков, так как все поставщики СУБД в своих перспективных разработках обязательно следуют стандарту, и можно быть уверенным, что в конце концов стандарт будет реализован практически во всех перспективных СУБД. Так произошло со стандартом SQL1, так происходит со стандартом SQL2 и так будет происходить со стандартом SQL3.

Для поставщиков СУБД стандарт — это путеводная звезда, которая гарантирует правильное направление работ. А вот эффективность реализации стандарта — это гарантия успеха.

SQL нельзя в полной мере отнести к традиционным языкам программирования, он не содержит традиционные операторы, управляющие ходом выполнения программы, операторы описания типов и многое другое, он содержит только набор стандартных операторов доступа к данным, хранящимся в базе данных. Операторы SQL встраиваются в базовый язык программирования, которым может быть любой стандартный язык типа C++, PL, COBOL и т. д. Кроме того, операторы SQL могут выполняться непосредственно в интерактивном режиме.

Оператор выбора SELECT

Язык запросов (Data Query Language) в SQL состоит из единственного оператора SELECT. Этот единственный оператор поиска реализует все операции реляционной алгебры. Как просто, всего один оператор. Однако писать запросы на языке SQL (грамотные запросы) сначала совсем не просто. Надо учиться, так же как надо учиться решать математические задачки или составлять алгоритмы для решения непростых комбинаторных задач. Один и тот же запрос может быть реализован несколькими способами, и, будучи все правильными, они, тем не менее, могут существенно отличаться по времени исполнения, и это особенно важно для больших баз данных.

Синтаксис оператора SELECT имеет следующий вид:

SELECT [ALL | DISTINCT] «писок полей>|*)

FROM <Список таблиц>

[WHERE <Предикат-условие выборки или соединения>]

[GROUP BY <Список полей результата>]

[HAVING <Предикат-условие для группы>]

[ORDER BY <Список полей, по которым упорядочить вывод>]

Здесь ключевое слово ALL означает, что в результирующий набор строк включаются все строки, удовлетворяющие условиям запроса. Значит, в результирующий набор могут попасть одинаковые строки. И это нарушение принципов теории отношений (в отличие от реляционной алгебры, где по умолчанию предполагается отсутствие дубликатов в каждом результирующем отношении). Ключевое слово DISTINCT означает, что в результирующий набор включаются только различные строки, то есть дубликаты строк результата не включаются в набор.

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

В разделе FROM задается перечень исходных отношений (таблиц) запроса.

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

В разделе GROUP BY задается список полей группировки.

В разделе HAVING задаются предикаты-условия, накладываемые на каждую группу.

В части ORDER BY задается список полей упорядочения результата, то есть список полей, который определяет порядок сортировки в результирующем отношении.

если первым полем списка будет

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

В выражении условий раздела WHERE могут быть использованы следующие предикаты:

Предикаты сравнения { =, <>, >,<, >=,<= }, которые имеют традиционный смысл.

Предикат Between A and В — принимает значения между А и В. Предикат истинен, когда сравниваемое значение попадает в заданный диапазон, включая границы диапазона. Одновременно в стандарте задан и противоположный предикат Not Between A and В, который истинен тогда, когда сравниваемое значение не попадает в заданный интервал, включая его границы.

Предикат вхождения в множество IN (множество) истинен тогда, когда сравниваемое значение входит в множество заданных значений. При этом множество значений может быть задано простым перечислением или встроенным подзапросом. Одновременно существует противоположный предикат NOT IN (множество), который истинен тогда, когда сравниваемое значение не входит в заданное множество.

Предикаты сравнения с образцом LIKE и NOT LIKE. Предикат LIKE требует задания шаблона, с которым сравнивается заданное значение, предикат истинен, если сравниваемое значение соответствует шаблону, и ложен в противном случае. Предикат NOT LIKE имеет противоположный смысл.

По стандарту в шаблон могут быть включены специальные символы:

символ подчеркивания (_) — для обозначения любого одиночного символа;

символ процента (%) — для обозначения любой произвольной последовательности символов;

остальные символы, заданные в шаблоне, обозначают самих себя.

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

При сравнении неопределенных значений не

При сравнении неопределенных значений не действуют стандартные правила сравнения: одно неопределенное значение никогда не считается равным другому неопределенному значению. Для выявления равенства значения некоторого атрибута неопределенному применяют специальные стандартные предикаты:

<имя атрибута>IS NULL и <имя атрибута> IS NOT NULL.

Если в данном кортеже (в данной строке) указанный атрибут имеет неопределенное значение, то предикат IS NULL принимает значение «Истина» (TRUE), а предикат IS NOT NULL — «Ложь» (FALSE), в противном случае предикат IS NULL принимает значение «Ложь», а предикат IS NOT NULL принимает значение «Истина».

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

	А	В	Not A	А ^ В	A B
	TRUE	TRUE	FALSE	TRUE	TRUE
	TRUE	FALSE	FALSE	FALSE	TRUE
	TRUE	Null	FALSE	Null	TRUE
	FALSE	TRUE	TRUE	FALSE	TRUE
	FALSE	FALSE	TRUE	FALSE	FALSE
	FALSE	Null	TRUE	FALSE	Null
	Null	TRUE	Null	Null	TRUE
	Null	FALSE	Null	FALSE	Null
	Null	Null	Null	Null	Null

Предикаты существования EXIST и несуществования NOT EXIST. Эти предикаты относятся к встроенным подзапросам, и подробнее мы рассмотрим их, когда коснемся вложенных подзапросов.

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

Отложив на время знакомство с группировкой, рассмотрим детально первые три строки оператора SELECT:

SELECT — ключевое слово, которое сообщает СУБД, что эта команда — запрос. Все запросы начинаются этим словом с последующим пробелом. За ним может следовать способ выборки — с удалением дубликатов (DISTINCT) или без удаления (ALL, подразумевается по умолчанию).

Затем следует список перечисленных через

Затем следует список перечисленных через запятую столбцов, которые выбираются запросом из таблиц, или символ '*' (звездочка) для выбора всей строки. Любые столбцы, не перечисленные здесь, не будут включены в результирующее отношение, соответствующее выполнению команды. Это, конечно, не значит, что они будут удалены или их информация будет стерта из таблиц, потому что запрос не воздействует на информацию в таблицах — он только показывает данные.

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

Все последующие разделы оператора SELECT являются необязательными.

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

SELECT *

FROM Rl. R2

соответствует декартову произведению таблиц R1 и R2. Выражение

SELECT Rl.A, R2.B

FROM Rl. R2

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

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

Рассмотрим базу данных, которая моделирует сдачу сессии в некотором учебном заведении. Пусть она состоит из трех отношений R1, R2, R3. Будем считать, что они представлены таблицами Rl, R2 и R3 соответственно.

Приведем несколько примеров использования оператора

R1 = (ФИО, Дисциплина, Оценка);

R2 = (ФИО, Группа);

R3 = (Группы, Дисциплина )

R1
ФИО	Дисциплина	Оценка
Петров Ф. И.	Базы данных	5
Сидоров К. А.	Базы данных	4
Миронов А. В.	Базы данных	2
Степанова К. Е.	Базы данных	2
Крылова Т. С.	Базы данных	5
Сидоров К. А.	Теория информации	4
Степанова К. Е.	Теория информации	2
Крылова Т. С.	Теория информации	5

R1
ФИО	Дисциплина	Оценка
Миронов А. В.	Теория информации	Null
Владимиров В. А.	Базы данных	5
Трофимов П. А.	Сети и телекоммуникации	4
Иванова Е. А.	Сети и телекоммуникации	5
Уткина Н. В.	Сети и телекоммуникации	5
Владимиров В. А.	Английский язык	4
Трофимов П. А.	Английский язык	5
Иванова Е. А.	Английский язык	3
Петров Ф. И.	Английский язык	5

	R2
	ФИО	Группа
	Петров Ф. И.	4906
	Сидоров К. А.	4906
	Миронов А. В.	4906
	Крылова Т. С.	4906
	Владимиров В. А.	4906
	Трофимов П. А.	4807
	Иванова Е. А.	4807
	Уткина Н. В.	4807

	R3
	Группа	Дисциплина
	4906	Базы данных
	4906	Теория информации
	4906	Английский язык
	4807	Английский язык
	4807	Сети и телекоммуникации

Приведем несколько примеров использования оператора SELECT.

Вывести список всех групп (без повторений), где должны пройти экзамены.

SELECT DISTINCT Группы

FROM R3

Результат:

Группа

4906

4807

Вывести список студентов, которые сдали экзамен по дисциплине «Базы данных» на «отлично».

SELECT ФИО

FROM R1

WHERE Дисциплина = "Базы данных" AND Оценка = 5

Результат:

ФИО

Петров Ф. И.

Крылова Т. С.

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

SELECT ФИО,Дисциплина

Здесь часть WHERE задает условия

FROM R2.R3

WHERE R2.Группа = R2.Группа;

Здесь часть WHERE задает условия соединения отношений R2 и R3, при отсутствии условий соединения в части WHERE результат будет эквивалентен расширенному декартову произведению, и в этом случае каждому студенту были бы приписаны все дисциплины из отношения R3, а не те, которые должна сдавать его группа.

Результат:

	ФИО	Дисциплина
	Петров Ф. И.	Базы данных
	Сидоров К. А.	Базы данных
	Миронов А. В.	Базы данных
	Степанова К. Е.	Базы данных
	Крылова Т. С.	Базы данных
	Владимиров В. А.	Базы данных
	Петров Ф. И.	Теория информации
	Сидоров К. А.	Теория информации
	Миронов А. В.	Теория информации
	Степанова К. Е.	Теория информации

	ФИО	Дисциплина
	Крылова Т. С.	Теория информации
	Владимиров В. А.	Теория информации
	Петров Ф. И.	Английский язык
	Сидоров К. А.	Английский язык
	Миронов А. В.	Английский язык
	Степанова К. Е.	Английский язык
	Крылова Т. С.	Английский язык
	Владимиров В. А.	Английский язык
	Трофимов П. А.	Сети и телекоммуникации
	Иванова Е. А.	Сети и телекоммуникации
	Уткина Н. В.	Сети и телекоммуникации
	Трофимов П. А.	Английский язык
	Иванова Е. А.	Английский язык
	Уткина Н. В.	Английский язык

Вывести список лентяев, имеющих несколько двоек.

SELECT DISTINCT R1.ФИО

FROM Rl a, R1 b

WHERE a.ФИО = b.ФИО AND

a.Дисциплина <>

b.Дисциплина AND

а.Оценка <= 2 AND b.Оценка <= 2:

Здесь мы использовали псевдонимы для именования отношения R, а и b, так как для записи условий поиска нам необходимо работать сразу с двумя экземплярами данного отношения.

Результат:

ФИО

Степанова К. Е.

Из этих примеров хорошо видно, что логика работы оператора выбора (декартово произведение—селекция—проекция) не совпадает с порядком описания в нем данных (сначала список полей для проекции, потом список таблиц для декартова произведения, потом условие соединения).

в том, что SQL изначально

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

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

SELECT ФИО. Дисциплина

FROM R1

WHERE Оценка IS NULL

Результат:

	ФИО	Дисциплина
	Миронов А. В.	Теория информации

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

В операции манипулирования данными входят три операции: операция удаления записей — ей соответствует оператор DELETE, операция добавления или ввода новых записей — ей соответствует оператор INSERT и операция изменения (обновления записей) — ей соответствует оператор UPDATE. Рассмотрим каждый из операторов подробнее.

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

Оператор ввода данных INSERT имеет следующий синтаксис:

INSERT INTO имя_таблицы [(<список столбцов>) ] VALUES (<список значений>)

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

INSERT INTO BOOKS ( ISBN.TITL.AUTOR.COAUTOR.YEARIZD.PAGES)

VALUES ("5-88782-290-2","Аппаратные средства IBM PC. Энциклопедия".

'Гук М. "."",2000.816)

В этой книге только один автор, нет соавторов, но мы в списке столбцов задали столбец COAUTOR, поэтому мы должны были ввести соответствующее значение в разделе VALUES. Мы ввели пустую строку, потому что мы знаем точно, что нет соавтора. Мы могли бы ввести неопределенное значение NULL.

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

INSERT INTO BOOKS VALUES ("5-88782-290-2",

"Аппаратные средства IBM PC. Энциклопедия"."Гук М.","".2000.816)

Результаты работы обоих операторов одинаковые.

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

INSERT INTO BOOKS ( ISBN,TITL.AUTOR.YEARIZD,PAGES)

VALUES ("5-88782-290-2"."Аппаратные средства IBM PC.

Какие столбцы должны быть заданы

Энциклопедия".

Гук М.".2000,816)

Столбцу COAUTOR будет присвоено в этом случае значение NULL.

Какие столбцы должны быть заданы при вводе данных? Это определяется тем, как описаны эти столбцы при описании соответствующей таблицы, и будет рассмотрено более подробно при описании языка DDL (Data Definition Language) в главе 8. Здесь мы пока отметим, что если столбец или атрибут имеет признак обязательный (NOT NULL) при описании таблицы, то оператор INSERT должен обязательно содержать данные для ввода в каждую строку данного столбца. Поэтому если в таблице все столбцы обязательные, то каждая вводимая строка должна содержать полный перечень вводимых значений, а указание имен столбцов в этом случае необязательно. В противном случае, если имеется хотя бы один необязательный столбец и вы не вводите в него значений, задание списка имен столбцов — обязательно.

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

INSERT INTO EXEMPLAR (INV.ISBN.YES_NO.NUM_READER.DATE_IN, DATE_OUT)

VALUES (1872. "5-88782-290-2" .NO,344.GetDate() .DateAdcKd.GetDate() ,14))

И это означает, что мы выдали экземпляр книги с инвентарным номером 1872 читателю с номером читательского билете 344, отметив, что этот экземпляр не присутствует с этого момента в библиотеке, и определили дату выдачи книги как текущую дату (функция GetDateO), а дату возврата задали двумя неделями позднее, использовав при этом функцию DateAdd О, которая позволяет к одной дате добавить заданное количество интервалов даты и тем самым получить новое значение типа «дата». Мы добавили 14 дней к текущей дате.

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

Тогда мы можем сделать всех

Тогда мы можем сделать всех студентов читателями нашей библиотеки одним оператором:

INSERT INTO READER (NAME_READER. ADRESS. HOOM_PHONE. BIRTH_DAY)

SELECT (NAMEJTUDENT. ADRESS, HOOM_PHONE, BIRTH_DAY)

FROM STUDENT

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

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

Синтаксис оператора DELETE следующий:

DELETE FROM имя_таблицы [WHERE условия_отбора]

Если условия отбора не задаются, то из таблицы удаляются все строки, однако это не означает, что удаляется вся таблица. Исходная таблица остается, но она остается пустой, незаполненной.

Например, если нам надо удалить результаты прошедшей сессии, то мы можем удалить все строки из отношения R1 командой

DELETE FROM R1

Условия отбора в части WHERE имеют тот же вид, что и условия фильтрации в операторе SELECT. Эти условия определяют, какие строки из исходного отношения будут удалены. Например, если мы исключим студента Миронова А. В., то мы должны написать следующую команду:

DELETE FROM R2 WHERE ФИО = 'Миронов А.В.'

В части WHERE может находиться встроенный запрос. Например, если нам надо исключить неуспевающих студентов, то по закону о высшем образовании неуспевающим считается студент, имеющий две и более задолженности по последней сессии. Тогда нам в условиях отбора надо найти студентов, имеющих либо две или более двоек, либо два и более несданных экзамена из числа тех, которые студент сдавал. Для поиска таких горе-студентов нам надо выбрать из отношения R1 все строки с оценкой 2 или с неопределенным значением, потом надо сгруппировать полученный результат по атрибуту ФИО и, подсчитав количество строк в каждой группе, которое соответствует количеству несданных экзаменов каждым студентом, отобрать те группы, у которых количество строк не менее двух.

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

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

DELETE FROM R2 WHERE R2.ФИО IN (SELECT Rl.ФИО FROM Rl

WHERE Оценка = 2 OR Оценка IS NULL GROOP BY Rl.ФИО HAVING COUNT(*) >= 2

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

DELETE FROM R2 WHERE R2.ФИО IN

(SELECT Rl.ФИО

FROM (R2 NATURAL INNER JOIN R3 )

LEFT JOIN Rl USING ( ФИО. Дисциплина)

WHERE Оценка = 2 OR Оценка IS NULL

GROOP BY Rl.ФИО HAVING COUNT(*) >= 2

Все операции манипулирования данными связаны с понятием целостности базы данных, которое будет рассматриваться далее в главе 9. В настоящий момент мне бы хотелось отметить только то, что операции манипулирования данными не всегда выполнимы, даже если синтаксически они написаны правильно. Действительно, если мы бы захотели удалить какую-нибудь группу из отношения R3, то СУБД не позволила бы нам это сделать, так как в отношениях R1 и R2 есть строки, связанные с удаляемой строкой в отношении R3. Почему так делается, мы узнаем позднее, а пока просто примем к сведению, что не все операторы манипулирования выполнимы.

Операция обновления данных UPDATE требуется тогда, когда происходят изменения во внешнем мире и их надо адекватно отразить в базе данных, так как надо всегда помнить, что база данных отражает некоторую предметную область. Да-примср, в нашем учебном заведении произошло счастливое событие, которое связано с тем, что госпожа Степанова К. Е. пересдала экзамен по дисциплине «Базы данных» с двойки сразу на четверку. В этом случае нам надо срочно выполнить соответствующую корректировку таблицы R1. Операция обновления имеет следующий формат:

UPDATE имя_таблицы

SET имя_столбца = новое_значение [WHERE условие_отбора]

Часть WHERE является необязательной, так

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

Для решения ранее поставленной задачи нам необходимо выполнить следующую операцию

UPDATE Rl

SET Rl.Оценка = 4

WHERE R1.ФИО = "Степанова К.Е." AND R1.Дисциплина = "Базы данных"

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

R4 = < Группа, Курс>

	R4
	Группа	Курс
	4906	3
	4807	4

В этом случае перевод на следующий курс можно выполнить следующей операцией обновления:

UPDATE R4

SET R4.Kypc = R4.Kypc + 1

И результат будет выглядеть следующим образом:

	Группа	Курс
	4906	4
	4807	5

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

	R5
	ФИО	Группа	Стипендия
	Петров Ф. И.	4906	<Null>
	Сидоров К. А.	4906	<Null>
	Миронов А. В.	4906	<Null>
	Крылова Т. С.	4906	<Null>
	Владимиров В. А.	4906	<Null>
	Трофимов П. А.	4807	<Null>
	Иванова Е. А.	4807	<Null>
	Уткина Н. В.	4807	<Null>

<

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

Будем считать наличие трех пятерок по сессии признаком повышенной стипендии, + 50% к основной, наличие двух пятерок из сданных экзаменов и, отсутствие двоек и троек на сданных экзаменах — признаком повышения стипендии на 25%, наличие хотя бы одной двойки среди сданных экзаменов — признаком снятия или отсутствия стипендии вообще, то есть -100% надбавки. При отсутствии троек на сданных экзаменах назначим обычную стипендию с надбавкой 0%. Однако все эти изменения мы должны будем сделать отдельными операциями обновления.

Назначение повышенной стипендии:

UPDATE R5

SET R5.Стипендия = 50% WHERE R5.ФИО IN

(SELECT Rl.ФИО

FROM Rl

WHERE Rl.Оценка =5

GROOP BY Rl.ФИО

HAVING COUNT(*) =3 )

Назначение стипендии с надбавкой 25%:

UPDATE R5 SET R5.Стипендия = 25%

WHERE R5.ФИО IN (SELECT Rl.ФИО FROM R1

WHERE Rl.ФИО NOT IN (SELECT А.ФИО FROM Rl A

WHERE А.Оценка <=3 OR А.Оценка IS NULL)

GROOP BY Rl.ФИО HAVING COUNT(*)>2 )

Назначение обычной стипендии:

UPDATE R5

SET R5.Стипендия = 0% WHERE R5.ФИО IN (SELECT Rl.ФИО FROM Rl

WHERE Rl.Оценка >=4 AND Р1.ФИО NOT IN (SELECT А.ФИО FROM Rl A

WHERE А.Оценка <= 3 OR А.Оценка IS NULL) )

Снятие стипендии:

UPDATE R5

SET R5.Стипендия =-100% WHERE R5.ФИО IN

(SELECT Rl.ФИО

FROM Rl

WHERE Rl.Оценка <= 2 OR Rl.Оценка IS NULL)

Почему мы в первом запросе на обновление не использовали дополнительную проверку на отсутствие двоек, троек и несданных экзаменов, как мы сделали это при назначении следующих видов стипендии? Просто мы учли особенности нашей предметной области: у нас в соответствии с исходными данными не только 3 экзамена. Но если мы можем предположить, что число экзаменов может быть произвольным и изменяться от семестра к семестру, то нам надо изменить наш запрос. Запрос — это некоторый алгоритм решения конкретной задачи, которую мы формулируем заранее на естественном языке. И оттого, что наша задача решается всего одним оператором языка SQL, она не становится примитивной. Мощность языка SQL и состоит в том, что он позволяет одним предложением сформулировать ответы на достаточно сложные запросы, для реализации которых на традиционных языках понадобилось бы писать большую программу.

как нам надо изменить текст

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

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

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

SELECT R3.Группа. Число_экзаменов = COUNT(*)

FROM R3 GROOP BY R3.Группа

Однако нам нужен не этот запрос, нам нужен запрос, в котором мы определяем для каждого студента количество экзаменов. Этот запрос мы должны строить по схеме встроенного запроса:

SELECT COUNT(*) FROM R3

WHERE R2.Группа = R3.Группа GROOP BY R3.Группа

А почему мы здесь в части FROM не написали имя второго отношения R2? Мы имя этого отношения укажем для связи с вышестоящим запросом, когда будем формировать запрос полностью. Теперь попробуем сформулировать полностью запрос. Нам надо объединить отношения R1 и R2 по атрибуту ФИО, нам надо знать группу, в которой учится каждый студент, далее надо выбрать все строки с оценкой 5 и сгруппировать их по фамилии студента, сосчитав количество строк в каждой группе, а выбирать мы будем те группы, в которых число строк в группе равно числу строк во встроенном запросе, рассмотренном ранее, при условии равенства количества строк в группе результату подзапроса, который выводит только одно число.

а теперь нам осталась последняя

SELECT Rl.ФИО FROM R1.R2

WHERE Rl. ФИО = R2ФИО AND Rl.Оценка = 5

GROOP BY Rl.ФИО HAVING COUNK*) = (SELECT COUNT(*)

FROM R3

WHERE R2.Группа = R3.Группа

GROOP BY R3.Группа)

Ну а теперь нам осталась последняя простейшая операция: надо заменить старый вложенный запрос, определявший отличников, получивших три пятерки на сессии, на новый универсальный запрос:

UPDATE R5

SET R5.Стипендия = 50%

WHERE R5.<WO IN (SELECT Rl.ФИО

FROM R1.R2

WHERE Rl. ФИО = Р2.ФИО AND Rl.Оценка = 5

GROOP BY Rl.ФИО

HAVING COUNT(*) = (SELECT COUNT(*)

FROM R3

WHERE R2.Группа = R3.Группа GROOP BY R3.Группа))

Вот какой сложный запрос мы построили. Это ведь практически один оператор, а какую сложную задачу он решает. Действительно, мощность языка SQL иногда удивляет даже профессионалов, кажется невозможно построить один запрос для решения конкретной задачи, но когда начинаешь поэтапно его конструировать — все получается. Самое сложное — это сделать переход от словесной формулировки задачи к представлению ее в терминах нашего SQL, но этот процесс сродни процессу алгоритмизации при решении задач традиционного программирования, а он всегда был самым трудным, творческим и неформализуемым процессом. Недаром на заре развития программирования известный американский специалист по программированию Дональд Е. Кнут озаглавил свой многотомный капитальный труд по теории и практике программирования «Искусство программирования для ЭВМ» («The art of computer programming»).

Применение агрегатных функций и вложенных запросов в операторе выбора

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

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

Это делается с помощью агрегатных функций. Агрегатные функции вычисляют одиночное значение для всей группы таблицы. Список этих функций представлен в таблице 5.7.

Таблица 5.7. Агрегатные функции

	Функция		Результат
	COUNT		Количество строк или непустых значений полей, которые выбрал запрос
	SUM		Сумма всех выбранных значений данного поля
	AVG		Среднеарифметическое значение всех выбранных значений данного поля
	M1N		Наименьшее из всех выбранных значений данного поля
	MAX		Наибольшее из всех выоранных значений данного поля

	R1
			ФИО		Дисциплина		Оценка
	Группа 1		Петров Ф. И.		Базы данных		5
			Сидоров К. А.		Базы данных		4
			Миронов А. В.		Базы данных		2
			Степанова К. Е.		Базы данных		2
			Крылова Т. С.		Базы данных		5
			Владимиров В. А.		Базы данных		5
	Группа 2		Сидоров К. А.		Теория информации		4
			Степанова К. Е.		Теория информации		2
			Крылова Т. С.		Теория информации		5
			Миронов А. В.		Теория информации		Null
	Группа 3		Трофимов П. А.		Сети и телекоммуникации		4
			Иванова Е. А.		Сети и телекоммуникации		5
			Уткина Н. В.		Сети и телекоммуникации		5
	Группа 4		Владимиров В. А.		Английский язык		4
			Трофимов П. А.		Английский язык		5
			Иванова Е. А.		Английский язык		3
			Петров Ф. И.		Английский язык		5 i

<

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

Агрегатные функции используются подобно именам полей в операторе SELECT, но с одним исключением: они берут имя поля как аргумент. С функциями SUM и AVG могут использоваться только числовые поля. С функциями COUNT, MAX и MIN могут использоваться как числовые, так и символьные поля. При использовании с символьными полями МАХ и MIN будут транслировать их в эквивалент ASCII кода и обрабатывать в алфавитном порядке. Некоторые СУБД позволяют использовать вложенные агрегаты, но это является отклонением от стандарта ANSI со всеми вытекающими отсюда последствиями.

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

SELECT R1.Дисциплина. СОUNТ(*)

FROM R1

GROUP BY R1 Дисциплина

Результат:

	Дисциплина	COUNT(*)
	Базы данных	6
	Теория информации	4
	Сети и телекоммуникации	3
	Английский язык	4

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

SELECT R1.Дисциплина. COUNT(*)

FROM R1 WHERE R1.

Оценка IS NOT NULL

GROUP BY Rl.Дисциплина

Получим результат:

	Дисциплина	COUNT(*)
	Базы данных	6
	Теория информации	3
	Сети и телекоммуникации	3
	Английский язык	4

В этом случае строка со студентом

	Миронов А, В.	Теория информации	Null

не попадет в набор кортежей перед группировкой, поэтому количество кортежей в группе для дисциплины «Теория информации» будет на 1 меньше.

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

отличается от выбора поля, поскольку

Обратившись снова к базе данных «Сессия» (таблицы Rl, R2, R3), найдем количество успешно сданных экзаменов:

SELECT COUNT(*)

FROM Rl

WHERE Оценка > 2:

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

SELECT Rl.Дисциплина.

COUNT(DISTINCT R1.Оценка)

FROM R1

WHERE R1.Оценка IS NOT NULL

GROUP BY Rl.Дисциплина

Результат:

	Дисциплина	COUNT(DISTINCT R1 .Оценка)
	Базы данных	3
	Теория информации	3
	Сети и телекоммуникации	2
	Английский язык	3

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

SELECT R2.Группа. R1.Дисциплина. COUNT(*), АVР(Оценка)

FROM R1.R2

WHERE Rl.ФИО = R2.ФИО AND

Rl.Оценка IS NOT NULL AND

Rl.Оценка > 2

GROUP BY R2.Группа. Rl.Дисциплина

Результат:

	Дисциплина	COUNT(*)	АVР(Оценка)
	Базы данных	6	3.83
	Теория информации	3	3.67
	Сети и телекоммуникации	3	4.66
	Английский язык	4	4.25

Мы не можем использовать агрегатные функции в предложении WHERE, потому что предикаты оцениваются в терминах одиночной строки, а агрегатные функции — в терминах групп строк.

Предложение GROUP BY позволяет определять подмножество значений в особом поле в терминах другого поля и применять функцию агрегата к подмножеству.

Это дает возможность объединять поля

Это дает возможность объединять поля и агрегатные функции в едином предложении SELECT. Агрегатные функции могут применяться как в выражении вывода результатов строки SELECT, так и в выражении условия обработки сформированных групп HAVING. В этом случае каждая агрегатная функция вычисляется для каждой выделенной группы. Значения, полученные при вычислении агрегатных функций, могут быть использованы для вывода соответствующих результатов или для условия отбора групп.

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

SELECT R2.Группа

FROM R1.R2

WHERE Rl.ФИО = R2.ФИО AND

Rl.Оценка = 2

GROUP BY R2.Группа . R1.Дисциплина

HAVING count(*)> 1

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

F = <N, ФИО, Филиал, ДатаОткрытия, ДатаЗакрытия, Остаток>;

Q = (Филиал, Город);

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

Например, предположим, что мы хотим найти суммарный остаток на счетах в филиалах. Можно сделать раздельный запрос для каждого из них, выбрав SUM(Остаток) из таблицы для каждого филиала. GROUP BY, однако, позволит поместить их все в одну команду:

SELECT Филиал, SUM

GROUP BY Филиал:

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

что мы хотели бы увидеть

Предположим, что мы хотели бы увидеть только те суммарные значения остатков на счетах, которые превышают $5000. Чтобы увидеть суммарные остатки свыше $5000, необходимо использовать предложение HAVING. Предложение HAVING определяет критерии, используемые, чтобы удалять определенные группы из вывода, точно так же как предложение WHERE делает это для индивидуальных строк.

Правильной командой будет следующая:

SELECT Филиал, SUM(Остаток)

FROM F

GROUP BY Филиал

HAVING SUM(Остаток) > 5000;

Аргументы в предложении HAVING подчиняются тем же самым правилам, что и в предложении SELECT, где используется GROUP BY. Они должны иметь одно значение на группу вывода.

Следующая команда будет запрещена:

SELECT Филиал.SUM(Остаток)

FROM F GROUP BY Филиал

HAVING ДатаОткрытия = 27/12/1999;

Поле ДатаОткрытия не может быть использовано в предложении HAVING, потому что оно может иметь больше чем одно значение на группу вывода. Чтобы избежать такой ситуации, предложение HAVING должно ссылаться только на агрегаты и поля, выбранные GROUP BY. Имеется правильный способ сделать вышеупомянутый запрос:

SELECT Филиал,SUM(Остаток)

FROM F

WHERE ДатаОткрытия = '27/12/1999'

GROUP BY Филиал;

Смысл данного запроса следующий: найти сумму остатков по каждому филиалу счетов, открытых 27 декабря 1999 года.

Как и говорилось ранее, HAVING может использовать только аргументы, которые имеют одно значение на группу вывода. Практически, ссылки на агрегатные функции — наиболее общие, но и поля, выбранные с помощью GROUP BY, также допустимы. Например, мы хотим увидеть суммарные остатки на счетах филиалов в Санкт-Петербурге, Пскове и Урюпинске:

SELECT Филиал.SUМ(Остаток)

FROM F.Q

WHERE F.Филиал = Q.Филиал

GROUP BY Филиал

HAVING Филиал IN ("Санкт-Петербург". "Псков". "Урюпинск");

Поэтому в арифметических выражениях предикатов, входящих в условие выборки раздела HAVING, прямо можно использовать только спецификации столбцов, указанных в качестве столбцов группирования в разделе GROUP BY.Остальные столбцы можно специфицировать только внутри спецификаций агрегатных функций COUNT, SUM, AVG, MIN и MAX, вычисляющих в данном случае некоторое агрегатное значение для всей группы строк. Аналогично обстоит дело с подзапросами, входящими в предикаты условия выборки раздела HAVING: если в подзапросе используется характеристика текущей группы, то она может задаваться только путем ссылки на столбцы группирования.

Результатом выполнения раздела HAVING является сгруппированная таблица, содержащая только те группы строк, для которых результат вычисления условия поиска есть TRUE. В частности, если раздел HAVING присутствует в табличном выражении, не содержащем GROUP BY, то результатом его выполнения будет либо пустая таблица, либо результат выполнения предыдущих разделов табличного выражения, рассматриваемый как одна группа без столбцов группирования.

Структура SQL

В отличие от реляционной алгебры, где были представлены только операции запросов к БД, SQL является полным языком, в нем присутствуют не только операции запросов, но и операторы, соответствующие DDL — Data Definition Language — языку описания данных. Кроме того, язык содержит операторы, предназначенные для управления (администрирования ) БД. SQL содержит разделы, представленные в таблице 5.1:

Таблица 5.1. Операторы определения данных DDL

	Оператор		Смысл		Действие
	CREATE TABLE		Создать таблицу		Создает новую таблицу в БД
	DROP TABLE		Удалить таблицу		Удаляет таблицу из БД
	ALTER TABLE		Изменить таблицу		Изменяет структуру существующей таблицы или ограничения целостности, задаваемые для данной таблицы
	CREATE VIEW		Создать представление		Создает виртуальную таблицу, соответствующую некоторому SQL-запросу
	ALTER VIEW		/Изменить представление		Изменяет ранее созданное представление
	DROP VIEW		Удалить представление		Удаляет ранее созданное представление
	CREATE INDEX		Создать индекс		Создает индекс для некоторой таблицы для обеспечения быстрого доступа по атрибутам, входящим в индекс
	DROP INDEX		Удалить индекс		Удаляет ранее созданный индекс

Таблица 5.2. Операторы манипулирования данными Data Manipulation Language (DMP)

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

<

Язык запросов Data Query Language

Таблица 5.3. Язык запросов Data Query Language (DQL)

	Оператор	Смысл	Действие
	SELECT	Выбрать строки	Оператор, заменяющий все операторы реляционной алгебры и позволяющий сформировать результирующее отношение, соответствующее запросу

Таблица 5.4. Средства управления транзакциями

	Оператор	Смысл	Действие
	COMMIT	Завершить транзакцию	Завершить комплексную взаимосвязанную обработку информации, объединенную в транзакцию
	ROLLBACK	Откатить транзакцию	Отменить изменения, проведенные в ходе выполнения транзакции
	'SAVEPOINT	Сохранить промежуточную точку выполнения транзакции	Сохранить промежуточное состояние БД, пометить его для того, чтобы можно было в дальнейшем к нему вернуться

Таблица 5.5. Средства администрирования данных

	Оператор	Смысл	Действие
	ALTER DATABASE	Изменить БД	Изменить набор основных объектов в базе данных, ограничений, касающихся всем базы данных
	ALTER -DBAREA	Изменить область хранения БД	Изменить ранее созданную область храпения

Таблица 5.5 (продолжение)

	Оператор	Смысл	Действие
	ALTER PASSWORD	Изменить пароль	Изменить пароль для всей базы данных
	CREATE DATABASE	Создать БД	Создать новую базу данных, определив основные параметры для нее
	CREATE DBAREA	Создать область хранения	Создать новую область хранения и сделать ее доступной для размещения данных
	DROP DATABASE	Удалить БД	Удалить существующую базу данных (только в том случае, когда вы имеете право выполнить это действие)
	DROP DBAREA	Удалить область хранения БД	Удалить существующую область хранения (если в ней на настоящий момент не располагаются активные данные)
	GRANT	Предоставить права	Предоставить нрава доступа на ряд действий над некоторым объектом БД
	REVOKE	Лишить прав	Лишить прав доступа к некоторому объекту или некоторым действиям над объектом

Таблица 5.6. Программный SQL

к БД, который соответствует виртуальному

	Оператор	Смысл	Действие
	DECLARE	Определяет курсор для запроса	Задает некоторое имя и определяет связанный с ним запрос к БД, который соответствует виртуальному набору данных
	OPEN	Открыть курсор	Формирует виртуальный набор данных, соответствующий описанию указанного курсора и текущему состоянию БД
	FETCH	Считать строку из множества строк, определенных курсором	Считывает очередную строку, заданную параметром команды из виртуального набора данных, соответствующего открытому курсору
	CLOSE	Закрыть курсор	Прекращает доступ к виртуальному набору данных, соответствующему указанному курсору
	PREPARE	Подготовить оператор SQL к динамическому выполнению	Сгенерировать план выполнения запроса, соответствующего заданному оператору SQL

	Оператор	Смысл	Действие
	EXECUTE	Выполнить оператор SQL, ранее подготовленный к динамическому выполнению	Выполняет ранее подготовленный план запроса

В коммерческих СУБД набор основных операторов расширен. В большинство СУБД включены операторы определения и запуска хранимых процедур и операторы определения триггеров.

Типы данных

В языке SQL/89 поддерживаются следующие типы данных:

CHARACTER(n) или CHAR(n) — символьные строки постоянной длины в п символов. При задании данного типа под каждое значение всегда отводится п символов, и если реальное значение занимает менее, чем п символов, то СУБД автоматически дополняет недостающие символы пробелами.

NUMERIC[(n,m)] — точные числа, здесь n — общее количество цифр в числе, m — количество цифр слева от десятичной точки.

DECIMAL[(n,m)] — точные числа, здесь n — общее количество цифр в числе, m — количество цифр слева от десятичной точки.

DEC[(n,m)] - то же, что и DECIMAL[(n,m)].

INTEGER или INT — целые числа.

SMALLINT — целые числа меньшего диапазона.

Несмотря на то, что в стандарте SQL1 не определяется точно, что подразумевается под типом INT и SMALLINT (это отдано на откуп реализации), указано только соотношение между этими типами данных, в большинстве реализаций тип данных INTEGER соответствует целым числам, хранимым в четырех байтах, a SMALLINT — соответствует целым числам, хранимым в двух байтах. Выбор одного из этих типов определяется размером числа.

FLOAT[(n)] — числа большой точности, хранимые в форме с плавающей точкой. Здесь n — число байтов, резервируемое под хранение одного числа. Диапазон чисел определяется конкретной реализацией.

REAL — вещественный тип чисел, который соответствует числам с плавающей точкой, меньшей точности, чем FLOAT.

DOUBLE PRECISION специфицирует тип данных с определенной в реализации точностью большей, чем определенная в реализации точность для REAL.

В стандарте SQL92 добавлены следующие типы данных:

VARCHAR(n) — строки символов переменной длины.

NCHAR(N) — строки локализованных символов постоянной длины.

NCHAR VARYING(n) — строки локализованных символов переменной длины.

BIT(n) — строка битов постоянной длины.

BIT VARYING(n) — строка битов переменной длины.

DATE — календарная дата.

ТIMESТАМР(точность) — дата и время.

INTERVAL — временной интервал.

Большинство коммерческих СУБД поддерживают еще дополнительные типы данных, которые не специфицированы в стандарте.

в том или ином виде

Так, например, практически все СУБД в том или ином виде поддерживают тип данных для представления неструктурированного текста большого объема. Этот тип аналогичен типу MEMO в настольных СУБД. Называются эти типы по-разному, например в ORACLE этот тип называется LONG, в DB2 - LONG VARCHAR, в SYBASE и MS SQL Server - TEXT.

Однако следует отметить, что специфика реализации отдельных типов данных серьезным образом влияет на результаты запросов к БД. Особенно это касается реализации типов данных DATE и TIMESTAMP. Поэтому при переносе приложений будьте внимательны, на разных платформах они могут работать по-разному, и одной из причин может быть различие в интерпретации типов данных.

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

213-314 612.716 + 551.702

Константы с плавающей запятой задаются, как и в большинстве языков программирования, путем задания мантиссы и порядка, разделенных символом Е, например:

2.9Е-4 -134.235Е7 0.54267Е18

Строковые константы должны быть заключены в одинарные кавычки:

'Крылов Ю.Д.' 'Санкт-Петербург'

В некоторых реализациях, например MS SQL Server и Informix, допустимы двойные кавычки в строковых константах:

"Москва" "New York"

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

Константы даты, времени и временного интервала в реляционных СУБД представляются в виде строковых констант. Форматы этих констант отличаются в различных СУБД. Кроме того, формат представления даты различен в разных странах. В большинстве СУБД реализованы способы настройки форматов представления дат или специальные функции преобразования форматов дат, как сделано, например, в СУБД ORACLE.

March 15, 1999 Маг 15

Приведем примеры констант в MS SQL Server:

March 15, 1999 Маг 15 1999 3/15/1999 3-15-99 1999 MAR 15

В СУБД ORACLE та же константа запишется как

15-MAR-99

Кроме пользовательских констант в СУБД могут существовать и специальные системные константы. Стандарт SQL1 определяет только одну системную константу USER, которая соответствует имени пользователя, под которым вы подключились к БД.

В операторах SQL могут использоваться выражения, которые строятся по стандартным правилам применения знаков арифметических операций сложения (+), вычитания (-), умножения (*) и деления (/). Однако в ряде СУБД операция деления (/) интерпретируется как деление нацело, поэтому при построении сложных выражений вы можете получить результат, не соответствующий традиционной интерпретации выражения. В стандарт SQL2 включена возможность выполнения операций сложения и вычитания над датами. В большинстве СУБД также определена операция конкатенации над строковыми данными, обозначается она, к сожалению, по-разному. Так, например, для DB2 операция конкатенации обозначается двойной вертикальной чертой, в MS SQL Server — знаком сложения (+), поэтому два выражения, созданные в разных СУБД, эквивалентны:

'Mr./Mrs. '| | NAME || '' LAST_NAME

'Mr./Mrs. ' + NAME + ' ' LAST_NAME

В стандарте SQL1 не были определены встроенные функции, однако в большинстве коммерческих СУБД такие функции были реализованы, и в стандарт SQL2 уже введен ряд стандартных встроенных функций:

BIT_LENGTH(cтpoкa) — количество битов в строке;

САSТ(значение AS тип данных) — значение, преобразованное в заданный тип данных;

CHAR_LENGTH(cтpoкa) — длина строки символов;

CONVERT(cтpoкa USING функция) — строка, преобразованная в соответствии с указанной функцией;

CURRENT_DATE - текущая дата;

CURRENT_TIME(точность) — текущее время с указанной точностью;

CURRENT_TIMESTAMP(точность) — текущие дата и время с указанной точностью;

LOWER(cтpокa) — строка, преобразованная к верхнему регистру;

OCTED_LENGTH(строка) — число байтов в строке символов;

POSITION( первая строка IN вторая строка) — позиция, с которой начинается вхождение первой строки во вторую;

SUBSTRING(cтpoкa FROM n FOR длина) — часть строки, начинающаяся с n-го символа и имеющая указанную длину;

TRANSLATE(строка USING функция) — строка, преобразованная с использованием указанной функции;

TRIM(BOTH символ FROM строка) — строка, у которой удалены все первые и последние символы;

TRIM(LEADING символ FROM строка ) — строка, в которой удалены все первые указанные символы;

TRIM(TRAILING символ FROM строка) — строка, в которой удалены последние указанные символы;

UPPER(строка) — строка, преобразованная к верхнему регистру.

Вложенные запросы

Теперь вернемся к БД «Сессия» и рассмотрим на ее примере использование вложенных запросов.

С помощью SQL можно вкладывать запросы внутрь друг друга. Обычно внутренний запрос генерирует значение, которое проверяется в предикате внешнего запроса (в предложении WHERE или HAVING), определяющего, верно оно или нет. Совместно с подзапросом можно использовать предикат EXISTS, который возвращает истину, если вывод подзапроса не пуст.

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

FROM Rl AS A, Rl AS В

ИЛИ

FROM Rl A. Rl В:

оба выражения эквивалентны и рассматриваются как применения оператора SELECT к двум экземплярам таблицы R1.

Например, покажем, как выглядят на SQL некоторые запросы к БД «Сессия»:

Список тех, кто сдал все положенные экзамены.

SELECT ФИО

FROM Rl as a

WHERE Оценка > 2

GROUP BY ФИО

HAVING COUNT(*) = (SELECT COUNT(*)

FROM R2.R3

WHERE R2.Группа=R3.Группа AND ФИОа.ФИО)

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

Список тех, кто должен был сдавать экзамен по БД, но пока еще не сдавал.

SЕLЕСТ ФИО

FROM R2 a, R3

WHERE R2.Fpynna=R3.Группа AND Дисциплина = "БД" AND NOT EXISTS

(SELECT ФИО FROM Rl WHERE ФИО=а.ФИО AND Дисциплина = "БД")

Предикат EXISTS ( SubQuery) истинен, когда подзапрос SubQuery не пуст, то есть содержит хотя бы один кортеж, в противном случае предикат EXISTS ложен.

истинен только тогда, когда подзапрос

Предикат NOT EXISTS обратно — истинен только тогда, когда подзапрос SubQuery пуст.

Обратите внимание, каким образом NOT EXISTS с вложенным запросом позволяет обойтись без операции разности отношений. Например, формулировка запроса со словом «все» может быть выполнена как бы с двойным отрицанием. Рассмотрим пример базы, которая моделирует поставку отдельных деталей отдельными поставщиками, она представлена одним отношением SP «Поставщики—детали» со схемой

SP (Номер_поставщика. номер_детали) Р (номер_детали. наименование)

Вот каким образом формулируется ответ на запрос: «Найти поставщиков, которые поставляют все детали».

SELECT DISTINCT НОМЕР_ПОСТАВЩИКА FROM SP SP1 WHERE NOT EXISTS

(SELECT номер_детали

FROM P WHERE NOT EXISTS

(SELECT * FROM SP SP2

WHERE SР2.номер_поставщика=SР1.номер_поставщика AND

sр2.номер_детали = Р.номер_детали)):

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

SELECT DISTINCT Номер_поставщика

FROM SP

GROUP BY Номер_поставщика

HAVING CounKDISTINCT номер_детали) =

(SELECT Count( номер_детали)

FROM P)

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

Ключевое слово ANY, поставленное в любом предикате сравнения, означает, что предикат будет истинен, если хотя бы для одного значения из подзапроса предикат сравнения истинен. Ключевое слово ALL требует, чтобы предикат сравнения был бы истинен при сравнении со всеми строками подзапроса.

Например, найдем студентов, которые сдали все экзамены на оценку не ниже чем «хорошо». Работаем с той же базой «Сессия», но добавим к ней еще одно отношение R4, которое характеризует сдачу лабораторных работ в течение семестра:

R1 = (ФИО, Дисциплина, Оценка);

R2 = (ФИО, Группа);

R3 = (Группы, Дисциплина )

R4 = (ФИО, Дисциплина, Номер_лаб_раб, Оценка);

Select R1.ФИО From R1 Where 4 > = All (Select Rl.Оценка

From Rl as R11

Where R1.Фио = R11.Фио)

Рассмотрим еще один пример:

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

Select R1.Фио

From R1 Where R1.Оценка >= ANY (Select R4.Оценка

From R4

Where Rl.Дисциплина = R4. Дисциплина AND R1.Фио = R4.Фио)

Внешние объединения

Стандарт SQL2 расширил понятие условного объединения. В стандарте SQL1 при объединении отношений использовались только условия, задаваемые в части WHERE оператора SELECT, и в этом случае в результирующее отношение попадали только сцепленные по заданным условиям кортежи исходных отношений, для которых эти условия были определены и истинны. Однако в действительности часто необходимо объединять таблицы таким образом, чтобы в результат попали все строки из первой таблицы, а вместо тех строк второй таблицы, для которых не выполнено условие соединения, в результат попадали бы неопределенные значения. Или наоборот, включаются все строки из правой (второй) таблицы, а отсутствующие части строк из первой таблицы дополняются неопределенными значениями. Такие объединения были названы внешними в противоположность объединениям, определенным стандартом SQL1, которые стали называться внутренними.

В общем случае синтаксис части FROM в стандарте SQL2 выглядит следующим образом:

FROM <список исходных таблиц> |

< выражение естественного объединения > |

< выражение объединения >

< выражение перекрестного объединения > |

< выражение запроса на объединение >

<список исходных таблиц>::= <имя_таблицы_1>

[ имя синонима таблицы_1] [ ...]

[,<имя_таблицы_п>[ <имя синонима таблицы_n> ] ]

<выражение естественного объединениям:: =

<имя_таблицы_1> NATURAL { INNER | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER]} JOIN <имя_таблицы_2>

<выражение перекрестного объединениям: = <имя_таблицы_1> CROSS JOIN <имя_таблицы_2>

<выражение запроса на объединением:=

<имя_таблицы_1> UNION JOIN <имя_таблицы_2>

<выражение объединениям := <имя_таблицы_1> { INNER |

FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER]} JOIN {ON условие [USING (список столбцов)]} <имя_таблицы_2>

В этих определениях INNER — означает внутреннее объединение, LEFT — левое объединение, то есть в результат входят все строки таблицы 1, а части результирующих кортежей, для которых не было соответствующих значений в таблице 2, дополняются значениями NULL (неопределено).

Ключевое слово RIGHT означает правое

Ключевое слово RIGHT означает правое внешнее объединение, и в отличие от левого объединения в этом случае в результирующее отношение включаются все строки таблицы 2, а недостающие части из таблицы 1 дополняются неопределенными значениями, Ключевое слово FULL определяет полное внешнее объединение: и левое и правое. При полном внешнем объединении выполняются и правое и левое внешние объединения и в результирующее отношение включаются все строки из таблицы 1, дополненные неопределенными значениями, и все строки из таблицы 2, также дополненные неопределёнными значениями.

Ключевое слово OUTER означает внешнее, но если заданы ключевые слова FULL, LEFT, RIGHT, то объединение всегда считается внешним.

Рассмотрим примеры выполнения внешних объединений. Снова вернемся к БД «Сессия». Создадим отношение, в котором будут стоять все оценки, полученные всеми студентами по всем экзаменам, которые они должны были сдавать. Если студент не сдавал данного экзамена, то вместо оценки у него будет стоять неопределенное значение. Для этого выполним последовательно естественное внутреннее объединение таблиц R2 и R3 по атрибуту Группа, а полученное отношение соединим левым внешним естественным объединением с таблицей R1, используя столбцы ФИО и Дисциплина. При этом в стандарте разрешено использовать скобочную структуру, так как результат объединения может быть одним из аргументов в части FROM оператора SELECT.

SELECT Rl.ФИО, R1.Дисциплина. Rl.Оценка

FROM (R2 NATURAL INNER JOIN R3 ) LEFT JOIN Rl USING ( ФИО. Дисциплина)

Результат:

	ФИО	Дисциплина	Оценка
	Петров Ф. И.	Базы данных	5
	Сидоров К. А.	Базы данных	4
	Миронов Л. В.	Базы данных	2
	Степанова К. Е.	Базы данных	2
	Крылова Т. С.	Базы данных	5
	Владимиров В. А.	Базы данных	5
	Петров Ф. И.	Теория информации	Null
	Сидоров К. А.	Теория информации	4
	Миронов А. В.	Теория информации	Null
	Степанова К. Е.	Теория информации	2
	Крылова Т. С.	Теория информации	5
	Владимиров В. А.	Теория информации	Null
	Петров Ф. И.	Английский язык	5
	Сидоров К. А.	Английский язык	Null
	Миронов А. В.	Английский язык	Null
	Степанова К. Е.	Английский язык	Null
	Крылова Т. С.	Английский язык	Null
	Владимиров В. А.	Английский язык	4
	Трофимов П. А.	Сети и телекоммуникации	4
	Иванова Е. А.	Сети и телекоммуникации	5

<

Рассмотрим еще один пример, для

	ФИО	Дисциплина	Оценка
	Уткина Н. В.	Сети и телекоммуникации	5
	Трофимов П. А.	Английский язык	5
	Иванова Е. А.	Английский язык	3
	Уткина Н. В.	Английский язык	Null

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

BOOKS(ISBN, TITL. AUTOR. COAUTOR. YEARJZD, PAGES)

READER(NUM_READER. NAME_READER, ADRESS. HOOM_PHONE. WORK_PHONE. BIRTH_DAY)

EXEMPLARE (INV, ISBN, YES_NO. NUM_READER. DATE_IN. DATE_DUT)

Здесь таблица BOOKS описывает все книги, присутствующие в библиотеке, она имеет следующие атрибуты:

ISBN — уникальный шифр книги;

TITL — название книги;

AUTOR — фамилия автора;

COAUTOR — фамилия соавтора;

YEARIZD — год издания;

PAGES — число страниц.

Таблица READER хранит сведения обо всех читателях библиотеки, и она содержит следующие атрибуты:

NUM_READER — уникальный номер читательского билета;

NAME_READER — фамилию и инициалы читателя;

ADRESS — адрес читателя;

HOOM_PHONE — номер домашнего телефона;

WORK_PHONE — номер рабочего телефона;

BIRTH_DAY — дату рождения читателя.

Таблица EXEMPLARE содержит сведения о текущем состоянии всех экземпляров всех книг. Она включает в себя следующие столбцы:

INV — уникальный инвентарный номер экземпляра книги;

ISBN — шифр книги, который определяет, какая это книга, и ссылается на сведения из первой таблицы;

YES_NO — признак наличия или отсутствия в библиотеке данного экземпляра в текущий момент;

NUM_READER — номер читательского билета, если книга выдана читателю, и Null в противном случае;

DATE_IN — если книга у читателя, то это дата, когда она выдана читателю; a DATE_OUT — дата, когда читатель должен вернуть книгу в библиотеку.

Определим перечень книг у каждого читателя; если у читателя нет книг, то номер экземпляра книги равен NULL. Для выполнения этого поиска нам надо использовать левое внешнее объединение, то есть мы берем все строки из таблицы READER и соединяем со строками из таблицы EXEMPLARE, если во второй таблице нет строки с соответствующим номером читательского билета, то в строке результирующего отношения атрибут EXEMPLARE.INV будет иметь неопределенное значение NULL:

FROM READER RIGHT JOIN EXEMPLARE

SELECT READER.NAME_READER, EXEMPLARE.INV

FROM READER RIGHT JOIN EXEMPLARE ON READER.NUM_READER=EXEMPLARE.NUM_READER

Операция внешнего объединения, как мы уже упоминали, может использоваться для формирования источников в предложении FROM, поэтому допустимым будет, например, следующий текст запроса:

SELECT *

FROM ( BOOKS LEFT JOIN EXEMPLARE)

LEFT JOIN (READER NATURAL JOIN EXEMPLARE)

USING (ISBN)

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

Перекрестное объединение в трактовке стандарта SQL2 соответствует операции расширенного декартова произведения, то есть операции соединения двух таблиц, при которой каждая строка первой таблицы соединяется с каждой строкой второй таблицы.

Операция запроса па объединение эквивалентна операции теоретико-множественного объединения в алгебре. При этом требование эквивалентности схем исходных отношений сохраняется. Запрос на объединение выполняется по следующей схеме:

SELECT - запрос

UNION SELECT - запрос

UNION SELECT - запрос

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

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

SELECT READER. NAME_READER

FROM READER, EXEMPLARE.BOOKS

WHERE EXEMPLARE.NUM_READER= READER.NUM_READER AND

EXEMPLRE.ISBN = BOOKS.ISBN AND

BOOKS.TITLE = "Идиот"

UNION

SELECT READER.NAME_READER

FROM READER, EXEMPLARE,BOOKS

WHERE EXEMPLARE.NUM_READER= READER.NUM_READER AND

EXEMPLRE.ISBN = BOOKS.ISBN AND

BOOKS.TITLE = "Преступление и наказание"

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

Запрос на объединение может объединять

Запрос на объединение может объединять любое число исходных запросов.

Так, к предыдущему запросу можно добавить еще читателей, которые держат на руках книгу «Замок»:

UNION

SELECT READER. NAME_READER

FROM READER. EXEMPLARE,BOOKS

WHERE EXEMPLARE.NUM_READER= READER.NUM_READER AND .

EXEMPLRE.ISBN = BOOKS.ISBN AND

BOOKS.TITLE = "Замок"

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

SELECT - запрос

UNION ALL

SELECT - запрос

UNION ALL

SELECT - запрос

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

SELECT DISTINCT READER.NAME_READER

FROM READER. EXEMPLARE.BOOKS

WHERE EXEMPLARE.NUM_READER= READER.NUM_READER AND

EXEMPLRE.ISBN = BOOKS.ISBN AND

BOOKS.TITLE = "Идиот" OR

BOOKS.TITLE = "Преступление и наказание" OR

BOOKS.TITLE = "Замок"

Ни один из исходных запросов в операции UNION не должен содержать предложения упорядочения результата ORDER BY, однако результат объединения может быть упорядочен, для этого предложение ORDER BY с указанием списка столбцов упорядочения записывается после текста последнего исходного SELECT-запроса.

В отношении R5 отметить студентов

Задание 1. В отношении R5 отметить студентов — претендентов на отчисление. Считаем, что в отношении R1 находятся окончательные результаты сессии, и поэтому отчислению подлежат все студенты, которые не сдали или не сдавали два и более из положенных экзаменов в сессию. Для того чтобы зафиксировать этот факт, нам потребуется добавить еще один столбец в отношение R5, назовем его результат_сессии, и там могут быть два допустимых значения: переведен на следующий курс или отчислен. Запрос писать по универсальному алгоритму.
Задание 2. В отношении R5 отметить студентов, переведенных на следующий курс.

Задание 3. Провести отчисление студентов по результатам текущей сессии. Обратите внимание, что это уже другая операция по сравнению с заданиями 1 и 2.

Даталогическое проектирование

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

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

Описание концептуальной схемы БД в терминах выбранной СУБД.

Описание внешних моделей в терминах выбранной СУБД.

Описание декларативных правил поддержки целостности базы данных.

Разработка процедур поддержки семантической целостности базы данных.

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

Мы должны построить корректную схему БД, ориентируясь на реляционную модель данных.

Корректной назовем схему БД, в которой отсутствуют нежелательные зависимости между атрибутами отношении.

Процесс разработки корректной схемы реляционной БД называется логическим проектированием БД.

Проектирование схемы БД может быть выполнено двумя путями:

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

путем синтеза, то есть путем компоновки из заданных исходных элементарных зависимостей между объектами предметной области схемы БД.

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

Понятие функциональной зависимости является фундаментальным

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

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

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

В теории реляционных БД обычно выделяется следующая последовательность нормальных форм:

первая нормальная форма (1NF);

вторая нормальная форма (2NF);

третья нормальная форма (3NF);

нормальная форма Бойса— Кодда (BCNF);

четвертая нормальная форма (4NF);

пятая нормальная форма, или форма проекции-соединения (5NF или PJNF).

Основные свойства нормальных форм:

каждая следующая нормальная форма в некотором смысле улучшает свойства предыдущей;

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

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

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

При выполнении эквивалентных преобразований сохраняется

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

Функциональные зависимости определяют не текущее состояние БД, а все возможные ее состояния, то есть они отражают те связи между атрибутами, которые присущи реальному объекту, который моделируется с помощью БД.

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

Приведем ряд основных определений.

Функциональной зависимостью набора атрибутов В отношения R от набора атрибутов А того же отношения, обозначаемой как

R.A -> R.B или А -> В

называется такое соотношение проекций R[A] и R[B], при котором в каждый момент времени любому элементу проекции R[A] соответствует только один элемент проекции R[B] , входящий вместе с ним в какой-либо кортеж отношения R.

Функциональная зависимость R.A -> R.B называется полной, если набор атрибутов В функционально зависит от А и не зависит функционально от любого подмножества А, то есть R.A -> R.B называется полной, если:

любое А1 с А=> R.A -/-> R.B, что читается следующим образом:

для любого А1, являющегося подмножеством A, R.B функционально не зависит от R.A, в противном случае зависимость R.A -> R.B называется неполной.

Функциональная зависимость R.A -> R.B называется транзитивной, если существует набор атрибутов С такой, что:

С не является подмножеством А.

С не включает в себя В.

Существует функциональная зависимость R.A -> R.C.

Не существует функциональной зависимости R.C -> R.A.

Существует функциональная зависимость R.C -> R.B.

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

А может ли быть ситуация,

А может ли быть ситуация, когда отношение не имеет возможного ключа? Давайте вспомним определение отношения: отношение — это подмножество декартова произведения множества доменов. И в полном декартовом произведении все наборы значений различны, тем более в его подмножестве. Значит, обязательно для каждого отношения всегда существует набор атрибутов, по которому можно однозначно определить кортеж отношения. В .вырожденном случае это просто полный набор атрибутов отношения, потому что если мы зададим для всех атрибутов конкретные значения, то, по определению отношения, мы получим только один кортеж.

В общем случае в отношении может быть несколько возможных ключей.

Среди всех возможных ключей отношения обычно выбирают один, который считается главным и который называют первичным ключом отношения.

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

Взаимно-независимые атрибуты — это такие атрибуты, которые не зависят функционально один от другого.

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

Для функциональных зависимостей как фундаментальной основы проекта БД

были проведены исследования, позволяющие избежать избыточного их представления. Ряд зависимостей могут быть выведены из других путем применения правил, названных аксиомами Армстронга, по имени исследователя, впервые сформулировавшего их. Это три основных аксиомы:

Рефлексивность: если В является подмножеством А, то А->В

Дополнение: если. А->В , то АС->ВС

Транзитивность: если А->В и В->С , то А->С.

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

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

в первой нормальной форме тогда

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

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

	Препода- ватель	День недели	Номер пары	Название дисциплины	Тип занятий	Группа
	Петров В. И.	Поиед.	1	Теор. выч. проц.	Лекция	4906
		Вторник	1	Коми, графика	Лаб. раб.	4907
		Вторник	2	Комн. графика	Лаб. раб.	4906
	Киров В. А.	Понед.	2	Теор. ииформ.	Лекция	4906
		Вторник	3	Пр-е па C++	Лаб. раб.	4907
		Вторник	4	Пр-е на C++	Ллб. раб.	4906
	Ссргш А. А.	Понед.	3	Защита ииф.	Лекция	4944
		Среда	3	Пр-е на VB	Лаб. раб.	4942
		Четверг	4	Пр-е на VB	Лаб. раб.	4922

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

Для приведения отношения «Расписание» к первой нормальной форме необходимо дополнить каждую строку фамилией преподавателя.

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

	Препода- ватель	День недели	Номер пары	Название дисциплины	Тип занятий	Группа
	Петров В. И	Понед.	1	Теор. выч. проц.	Лекция	4906
	Петров В. И	Вторник	1	Комм, графика	Лаб. раб.	4907
	Петров В. И	Вторник	2	Коми, графика	Лаб. раб.	4906
	Киров В. А.	Понед.	2	Теор. информ.	Лекция	4906
	Киров В. А.	Вторник	3	Пр-е на C++	Лаб. раб.	4907
	Киров В. А.	Вторник	4	Пр-е на C++	Лаб. раб.	4906
	Серов А. А.	Поиед.	3	Защита инф.	Лекция	4944
	Серов А. А.	Среда	3	Пр-е на VB	Лаб. раб.	4942
	Серов А. А.	Четверг	4	Пр-е на VB	Лаб. раб.	4922

<

Рассмотрим отношение, моделирующее сдачу студентами

Рассмотрим отношение, моделирующее сдачу студентами текущей сессии. Структура этого отношения определяется следующим набором атрибутов:

(ФИО. Номер зач.кн.. Группа. Дисциплина. Оценка)

Так как каждый студент сдает целый набор дисциплин в процессе сессии, то первичным ключом отношения может быть (Номер, зач.кн.. Дисциплина), который однозначно определяет каждую стоку, отношения. С другой стороны, атрибуты ФИО и Группа зависят только от части первичного ключа — от значения атрибута Номер зач, кн., поэтому мы должны констатировать наличие неполных функциональных зависимостей в данном отношении. Для приведения данного отношения ко второй нормальной форме следует разбить его на проекции, при этом должно быть соблюдено условие восстановления исходного отношения без потерь. Такими проекциями могут быть два отношения:

(ФИО, Номер.зач.кн.. Группа) (Номер зач.кн.. Дисциплина. Оценка)

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

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

Если же мы перешли ко

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

Отношение находится в третьей нормальной форме тогда и только тогда, когда оно находится во второй нормальной форме и не содержит транзитивных зависимостей.

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

(ФИО. Номер зач.кн.. Группа. Факультет, Специальность, Выпускающая кафедра)

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

Номер зач .кн. -> ФИО

Номер зач.кн. -> Группа

Номер зач.кн. -> Факультет

Номер зач.кн. -> Специальность

Номер зач.кн. -> Выпускающая кафедра

Группа -> Факультет

Группа -> Специальность

Группа -> Выпускающая кафедра

Выпускающая кафедра -> Факультет

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

(Номер. зач. кн., ФИО. Специальность. Группа) (Группа. Выпускающая кафедра) (Выпускащая кафедра, Факультет)

Теперь необходимо удостовериться, что при

Первичные ключи отношений выделены.

Теперь необходимо удостовериться, что при естественном соединении мы не потеряем ни одной строки и не получим лишних кортежей. И это упражнение я предлагаю выполнить вам самостоятельно.

Полученный набор отношений находится в третьей нормальной форме.

Отношение находится в нормальной форме Болса—Кодла, если оно находится в третьей нормальной форме и каждый детерминант отношения является возможным ключом отношения.

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

(Номер зач.кн.. Идентификатор_студента. Дисциплина. Дата. Оценка)

Возможными ключами отношения являются Нонер_зач.кн, Дисциплина, Дата и Идеитификатор_студента, Дисциплина, Дата.

Какие функциональные зависимости у нас имеются?

Номер_зач.кн, Дисциплина. Дата -> Оценка;

Идентификатор_студента, Дисциплина. Дата -> Оценка;

Номер зач.кн. -> Идентификатор_студента;

Идентификатор_студента -> Номер зач.кн.

Откуда взялись две последние функциональные зависимости? Но ведь мы предварительно описали, что каждому студенту ставится в соответствие одни номер зачетной книжки и один Идентификатор_студента, поэтому по значению Номер зач.кн. можно однозначно определить Идентификатор_студента (это третья зависимость) и обратно (и это четвертая зависимость). Оценим это отношение,

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

и четвертая зависимости, разве они

А как же третья и четвертая зависимости, разве они не являются неполными? Нет, потому что зависимым не является непервичный атрибут, то есть атрибут, не входящий ни в один возможный ключ. Поэтому придраться к этому мы не можем. Но вот под четвертую нормальную форму наше отношение не подходит, потому что у нас есть два детерминанта Номер зач.кн. и Идентификатор_студента, которые не являются возможными ключами отношения. Для приведения отношения к нормальной форме Бойса—Кодда надо разделить отношение, например, на два со следующими схемами:

(Идентификатор_студента. Дисциплина. Дата. Оценка)

(Номер зач.кн.. Идентификатор_студента)

или наоборот:

(Номер зач.кн., Дисциплина. Дата, Оценка)

(Номер зач.кн.. Идентификатор_студента)

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

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

В отношении R (А, В, С) существует многозначная зависимость (multi valid dependence, MVD) R.A -» R.B в том и только в том случае, если множество значений В, соответствующее паре значений А и С, зависит только от А к не зависит от С.

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

Когда это может быть? Рассмотрим

Когда это может быть? Рассмотрим конкретную ситуацию, понятную всем студентам. Пусть дано отношение, которое моделирует предстоящую сдачу экзаменов на сессии. Допустим, оно имеет вид:

(Номер зач.кн.. Группа. Дисциплина)

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

В данном отношении существуют следующие две многозначные зависимости: Группа -» Дисциплина Группа -» Номер зач.кн.

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

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

В теории реляционных баз данных доказывается, что в общем случае в отношении R (А, В, С) существует многозначная зависимость R.A -» R.B в том и только в том случае, когда существует многозначная зависимость R.A -» R.C.

Дальнейшая нормализация отношений, подобных нашему, основывается на теореме Фейджина.

ТЕОРЕМА ФЕЙДЖИНА

Отношение R (А, В, С) можно спроецировать без потерь в отношения R1 (А, В) и R2 (А, С) в том и только в том случае, когда существует MVD А -» В С ( что равнозначно наличию двух зависимостей А -» В и А -» С).

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

Практически теорема доказывает наличие эквивалентной

Практически теорема доказывает наличие эквивалентной схемы для отношения, в котором существует несколько многозначных зависимостей.

Отношение R находится в четвертой нормальной форме (4NF) is том и только в том случае, если в случае существования многозначной зависимости А -» В все остальные атрибуты R функционально зависят от А.

В нашем примере можно произвести декомпозицию исходного отношения в два отношения:

(Номер зач.кн.. Группа)

(Группа, Дисциплина)

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

Последней нормальной формой является пятая нормальная форма 5NF, которая связана с анализом нового вида зависимостей, зависимостей «проекции соединения» (project-join зависимости, обозначаемые как PJ-зависимости). Этот вид

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

Отношение R (X, Y, ..., Z) удовлетворяет зависимости соединения (X, Y, ..., Z) в том и только в том случае, когда R восстанавливается без потерь путем соединения своих проекций на X, Y, ..., Z. Здесь X, Y, ..., Z — наборы атрибутов отношения R.

Наличие PJ-зависимости в отношении делает его в некотором роде избыточным и затрудняет операции модификации. . .

Отношение R находится в пятой нормальной форме (нормальной форме проекции-соединения — PJ/NF) в том и только в том случае, когда любая зависимость соединения в R следует из существования некоторого возможного ключа в R.

Рассмотрим отношение R1:

R1(Преподаватель. Кафедра, Дисциплина)

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

В этом случае ключом отношения

В этом случае ключом отношения является полный набор из трех атрибутов. В отношении отсутствуют многозначные зависимости, и поэтому отношение находится в 4NF.

Введем следующие обозначения наборов атрибутов:

ПК (Преподаватель, Кафедра)

ПД (Преподаватель, Дисциплина)

КД (Кафедра, Дисциплина)

Допустим, что отношение R1 удовлетворяет зависимости проекции соединения (ПК, ПД, КД). Тогда отношение R1 не находится в NF/PJ, потому что единственным ключом его является полный набор атрибутов, а наличие зависимости PJ связано с наборами атрибутов, которые не составляют возможные ключи отношения R1. Для того чтобы привести это отношение к NF/PJ, его надо представить в виде трех отношений:

R2 (Преподаватель, Кафедра)

R3 (Преподаватель, Дисциплина)

R4 (Кафедра, Дисциплина)

Пятая нормальная форма редко используется на практике. В большей степени она является теоретическим исследованием. Очень тяжело определить само наличие зависимостей «проекции—соединения», потому что утверждение о наличии такой зависимости делается для всех возможных состояний БД, а не только для текущего экземпляра отношения R1. Однако знание о возможном наличии подобных зависимостей, даже теоретическое, нам все же необходимо.

Проектирование реляционных БД на основе принципов нормализации

Что такое проект? Это схема — эскиз некоторого устройства, который в дальнейшем будет воплощен в реальность. Что такое проект реляционной базы данных? Это набор взаимосвязанных отношений, в которых определены все атрибуты, заданы первичные ключи отношений и заданы еще некоторые дополнительные свойства отношений, которые относятся к принципам поддержки целостности и будут более подробно рассмотрены в главе 9. Почему именно взаимосвязанных отношений? Потому что при выполнении запросов мы производим объединение отношений и одни и те же значения должны в разных отношениях-таблицах обозначаться одинаково. Действительно, если мы в одной таблице оценки будем обозначать цифрами, а в другой словами «отлично», «хорошо» и т. д., то мы не сможем объединить эти таблицы по столбцу Оценка, хотя по смыслу это для нас одно и то же, но то, что интуитивно понятно человеку, совсем не понятно «умному» компьютеру. Это проблема систем с искусственным интеллектом, которые могут решать весьма сложные интеллектуальные задачи, трудные для рядового инженера, но иногда пасуют перед простейшими интуитивными ассоциациями, понятными любому школьнику. И это необходимо учитывать. Поэтому проект базы данных должен быть очень точен и выверен. Фактически проект базы данных — это фундамент будущего программного комплекса, который будет использоваться достаточно долго и многими пользователями. И как в любом здании, можно достраивать мансарды, переделывать крышу, можно даже менять окна, но заменить фундамент, не разрушив всего здания, невозможно. Этапы жизненного цикла базы данных изображены на рис. 6.1. Они аналогичны, в основном, развитию любой программной системы, однако в них есть определенная специфика, касающаяся только баз данных. Более подробно мы будем рассматривать этапы жизненного цикла БД в следующих разделах учебного пособия, потому что термины, которые мы вынуждены применять при этом описании, пока еще неизвестны нашим читателям.

Рис. 6.1. Этапы жизненного цикла БД

Процесс проектирования БД представляет собой

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

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

Проектирование инфологической модели предметной области — частично формализованное описание объектов предметной области в терминах некоторой семантической модели, например, в терминах Е-модели.

Даталогическое или логическое проектирование БД, то есть описание БД в терминах принятой диалогической модели данных.

Физическое проектирование БД, то есть выбор эффективного размещения БД на внешних носителях для обеспечения наиболее эффективной работы приложения.

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


Рис. 6.2. Этапы проектирования БД

Пример описания предметной области

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

уникальный шифр;

название;

фамилии авторов (могут отсутствовать);

место издания (город);

издательство;

год издания;

количество страниц;

стоимость книги;

количество экземпляров книги в библиотеке.

Книги могут иметь одинаковые названия, но они различаются по своему уникальному шифру (ISBN).

В библиотеке ведется картотека читателей.

На каждого читателя в картотеку заносятся следующие сведения:

фамилия, имя, отчество;

домашний адрес;

телефон (будем считать, что у нас два телефона — рабочий и домашний);

дата рождения.

Каждому читателю присваивается уникальный номер читательского билета. Каждый читатель может одновременно держать на руках не более 5 книг. Читатель не должен одновременно держать более одного экземпляра книги одного названия.

Каждая книга в библиотеке может присутствовать в нескольких экземплярах. Каждый экземпляр имеет следующие характеристики:

уникальный инвентарный номер;

шифр книги, который совпадает с уникальным шифром из описания книг;

место размещения в библиотеке.

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

номер билета читателя, который взял книгу;

дата выдачи книги;

дата возврата.

Предусмотреть следующие ограничения на информацию в системе:

Книга может не иметь ни одного автора.

В библиотеке должны быть записаны читатели не моложе 17 лет.

В библиотеке присутствуют книги, изданные

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

Каждый читатель может держать на руках не более 5 книг.

Каждый читатель при регистрации в библиотеке должен дать телефон для связи: он может быть рабочим или домашним.

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

С данной информационной системой должны работать следующие группы пользователей:

библиотекари;

читатели;

администрация библиотеки,

При работе с системой библиотекарь должен иметь возможность решать следующие задачи:

Принимать новые книги и регистрировать их в библиотеке.

Относить книги к одной или к нескольким областям знаний.

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

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

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

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

При приеме книги, возвращаемой читателем,

При приеме книги, возвращаемой читателем, проверяется соответствие возвращаемого инвентарного номера книги выданному инвентарному номеру, и она ставится на свое старое место на полку библиотеки.

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

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

Читатель должен иметь возможность решать следующие задачи:

Просматривать системный каталог, то есть перечень всех областей знаний, книги по которым есть в библиотеке.

По выбранной области знаний получить полный перечень книг, которые числятся в библиотеке.

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

Для выбранного автора получить список книг, которые числятся в библиотеке.

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

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

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

И это правильно, ведь когда

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

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

Системный анализ предметной области

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

В общем случае существуют два подхода к выбору состава и структуры предметной области:

Функциональный подход — он реализует принцип движения «от задач» и применяется тогда, когда заранее известны функции некоторой группы лиц и комплексов задач, для обслуживания информационных потребностей которых создается рассматриваемая БД. В этом случае мы можем четко выделить минимальный необходимый набор объектов предметной области, которые должны быть описаны.

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

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

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

Инфологическое моделирование

Мифологическая модель применяется на втором этапе проектирования БД, то есть после словесного описания предметной области. Зачем нужна инфологическая модель и какую пользу она дает проектировщикам? Еще раз хотим напомнить, что процесс проектирования длительный, он требует обсуждений с заказчиком, со специалистами в предметной области. Наконец, при разработке серьезных корпоративных информационных систем проект базы данных является тем фундаментом, на котором строится вся система в целом, и вопрос о возможном кредитовании часто решается экспертами банка на основании именно грамотно сделанного инфологического проекта БД. Следовательно, инфоло-гическая модель должна включать такое формализованное описание предметной области, которое легко будет «читаться» не только специалистами по базам данных. И это описание должно быть настолько емким, чтобы можно было оценить глубину и корректность проработки проекта БД, и конечно, как говорилось раньше, оно не должно быть привязано к конкретной СУБД. Выбор СУБД — это отдельная задача, для корректного ее решения необходимо иметь проект, который не привязан ни к какой конкретной СУБД.

Инфологическое проектирование прежде всего связано с попыткой представления семантики предметной области в модели БД. Реляционная модель данных в силу своей простоты и лаконичности не позволяет отобразить семантику, то есть смысл предметной области. Ранние теоретико-графовые модели в большей степени отображали семантику предметной области. Они в явном виде определяли иерархические связи между объектами предметной области.

Проблема представления семантики давно интересовала разработчиков, и в семидесятых годах было предложено несколько моделей данных, названных семантическими моделями. К ним можно отнести семантическую модель данных, предложенную Хаммером (Hammer) и Мак-Леоном (McLeon) в 1981 году, функциональную модель данных Шипмана (Shipman), также созданную в 1981 году, модель «сущность—связь», предложенную Ченом (Chen) в 1976 году, и ряд других моделей.

У всех моделей были свои

У всех моделей были свои положительные и отрицательные стороны, но испытание временем выдержала только последняя. И в настоящий момент именно модель Чена «сущность—связь», или «Entity Relationship», стала фактическим стандартом при инфологическом моделировании баз данных. Общепринятым стало сокращенное название ER-модель, большинство современных CASE-средств содержат инструментальные средства для описания данных в формализме этой модели. Кроме того, разработаны методы автоматического преобразования проекта БД из ER-модели в реляционную, при этом преобразование выполняется в даталогическую модель, соответствующую конкретной СУБД. Все CASE-системы имеют развитые средства документирования процесса разработки БД, автоматические генераторы отчетов позволяют подготовить отчет о текущем состоянии проекта БД с подробным описанием объектов БД и их отношений как в графическом виде, так и в виде готовых стандартных печатных отчетов, что существенно облегчает ведение проекта.

В настоящий момент не существует единой общепринятой системы обозначений для ER-модели и разные CASE-системы используют разные графические нотации, но разобравшись в одной, можно легко понять и другие нотации.

Модель «сущность—связь»

Как любая модель, модель «сущность—связь» имеет несколько базовых понятий, которые образуют исходные кирпичики, из которых строятся уже более сложные объекты по заранее определенным правилам.

Эта модель в наибольшей степени согласуется с концепцией объектно-ориентированного проектирования, которая в настоящий момент несомненно является базовой для разработки сложных программных систем, поэтому многие понятия вам могут показаться знакомыми, и если это действительно так, то тем проще вам будет освоить технологию проектирования баз данных, основанную на ER-модели.

В основе ER-модели лежат следующие базовые понятия:

Сущность, с помощью которой моделируется класс однотипных объектов. Сущность имеет имя, уникальное в пределах моделируемой системы. Так как сущность соответствует некоторому классу однотипных объектов, то предполагается, что в системе существует множество экземпляров данной сущности. Объект, которому соответствует понятие сущности, имеет свой набор атрибутов — характеристик, определяющих свойства данного представителя класса. При этом набор атрибутов должен быть таким, чтобы можно было различать конкретные экземпляры сущности. Например, у сущности Сотрудник может быть следующий набор атрибутов: Табельный номер, Фамилия, Имя, Отчество, Дата рождения, Количество детей, Наличие родственников за границей. Набор атрибутов, однозначно идентифицирующий конкретный экземпляр сущности, называют ключевым. Для сущности Сотрудник ключевым будет атрибут Табельный номер, поскольку для всех сотрудников данного предприятия табельные номера будут различны. Экземпляром сущности Сотрудник будет описание конкретного сотрудника предприятия. Одно из общепринятых графических обозначений сущности — прямоугольник, в верхней части которого записано имя сущности, а ниже перечисляются атрибуты, причем ключевые атрибуты помечаются, например, подчеркиванием или специальным шрифтом (рис. 7.1):

Рис. 7.1. Пример определения сущности в модели ER

Между сущностями могут быть установлены связи — бинарные ассоциации, показывающие, каким образом сущности соотносятся или взаимодействуют между собой.

Связь может существовать между двумя

Связь может существовать между двумя разными сущностями или между сущностью и ей же самой (рекурсивная связь). Она показывает, как связаны экземпляры сущностей между собой. Если связь устанавливается между двумя сущностями, то она определяет взаимосвязь между экземплярами одной и другой сущности. Например, если у нас есть связь между сущностью «Студент» и сущностью «Преподаватель» и эта связь — руководство дипломными проектами, то каждый студент имеет только одного руководителя, но один и тот же преподаватель может руководить множеством .студентов-дипломников. Поэтому это будет связь «один-ко-многим» (1:М), один со стороны «Преподаватель» и многие со стороны «Студент» (см. рис. 7.2).



Рис. 7.2. Пример отношения «один-ко-многим» при связывании сущностей «Студент» и «Преподаватель»

В разных нотациях мощность связи изображается по-разному. В нашем примере мы используем нотацию CASE системы POWER DESIGNER, здесь .множественность изображается путем разделения линии связи на 3. Связь имеет общее имя «Дипломное проектирование» и имеет имена ролей со стороны обеих сущностей. Со стороны студента эта роль называется «Пишет диплом под руководством», со стороны преподавателя эта связь называется «Руководит». Графическая интерпретация связи позволяет сразу прочитать смысл взаимосвязи между сущностями, она наглядна и легко интерпретируема. Связи делятся на три типа по множественности: один-к-одному (1:1), од и и-ко-многим (1:М), многие-ко-многим (М:М). Связь один-к-одному означает, что экземпляр одной сущности связан только с одним экземпляром другой сущности. Связь 1: М означает, что один экземпляр сущности, расположенный слева по связи, может быть связан с несколькими экземплярами сущности, расположенными справа по связи. Связь «один-к-одному» (1:1) означает, что один экземпляр одной сущности связан только с одним экземпляром другой сущности, а связь «многие-ко-мно-гим» (М:М) означает, что один экземпляр первой сущности может быть связан с несколькими экземплярами второй сущности, и наоборот, один экземпляр второй сущности может быть связан с несколькими экземплярами первой сущности.

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

Например, если мы рассмотрим связь типа «Изучает» между сущностями «Студент» и «Дисциплина», то это связь типа «многие-ко-многим» (М:М), потому что каждый студент может изучать несколько дисциплин, но и каждая дисциплина изучается множеством студентов.ч Такая связь изображена на рис. 7.3.

Между двумя сущностями может быть задано сколько угодно связей с разными смысловыми нагрузками. Например, между двумя сущностями «Студент» и «Преподаватель» можно установить две смысловые связи, одна — рассмотренная уже ранее «Дипломное проектирование», а вторая может быть условно названа «Лекции», и она определяет, лекции каких преподавателей слушает данный студент и каким студентам данный преподаватель читает лекции. Ясно, что это связь типа многие-ко-многим. Пример этих связей приведен на рис. 7.3.



Рис. 7.3. Пример моделирования связи «многие-ко-многим»

Связь любого из этих типов может быть обязательной, если в данной связи должен участвовать каждый экземпляр сущности, необязательной — если не каждый экземпляр сущности должен участвовать в данной связи. При этом связь может быть обязательной с одной стороны и необязательной с другой стороны. Обязательность связи тоже по-разному обозначается в разных нотациях. Мы снова используем нотацию POWER DESIGNER. Здесь необязательность связи обозначается пустым кружочком на конце связи, а обязательность перпендикулярной линией, перечеркивающей связь. И эта нотация имеет простую интерпретацию. Кружочек означает, что ни один экземпляр не может участвовать в этой связи. А перпендикуляр интерпретируется как то, что по крайней мере один экземпляр сущности участвует в этой связи.

Рассмотрим для этого ранее приведенный пример связи «Дипломное проектирование». На нашем рисунке эта связь интерпретируется как необязательная с двух сторон. Но ведь на самом деле каждый студент, который пишет диплом, должен иметь своего руководителя дипломного проектирования, но, с другой стороны, не каждый преподаватель должен вести дипломное проектирование.

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

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



Рис. 7.4. Пример обязательной и необязательной связи между сущностями

Кроме того, в ER-модели допускается принцип категоризации сущностей. Это значит, что, как и в объектно-ориентированных языках программирования, вводится понятие подтипа сущности, то«есть сущность может быть представлена в виде двух или более своих подтипов — сущностей, каждая из которых может иметь общие атрибуты и отношения и/или атрибуты и отношения, которые определяются однажды на верхнем уровне и наследуются на нижнем уровне. Все подтипы одной сущности рассматриваются как взаимоисключающие, и при разделении сущности па подтипы она должна быть представлена в виде полного набора взаимоисключающих подтипов. Если на уровне анализа не удается выявить полный Перечень подтипов, то вводится специальный подтип, называемый условно ПРОЧИЕ, который в дальнейшем может быть уточнен. В реальных системах бывает достаточно ввести подтипизацпю на двух-трех уровнях.

Сущность, на основе которой строятся подтипы, называется супертипом. Любой экземпляр супертипа должен относиться к конкретному подтипу. Для графического изображения принципа категоризации или типизации сущности вводится специальный графический элемент, называемый узел-дискриминатор, в нотации POWER DESIGNER он изображается в виде полукруга, выпуклой стороной обращенного к суперсущности. Эта сторона соединяется направленной стрелкой с суперсущностью, а к диаметру этого круга стрелками подсоединяются подтипы данной сущности (см. рис. 7.5).



Рис. 7.5. Диаграмма подтипов сущности ТЕСТ

Эту диаграмму можно расшифровать следующим образом. Каждый тест в некоторой системе тестирования является либо тестом проверки знаний языка SQL, либо некоторой аналитической задачей, которая выполняется с использованием заранее написанных Java-апплетов, либо тестом по некоторой области знаний, состоящим из набора вопросов и набора ответов, предлагаемых к каждому вопросу.

В результате построения модели предметной

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

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

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

Между сущностями «Книги» и «Экземпляры» существует связь «один-ко-многим» (1:М), обязательная с двух сторон. Чем определяется данный тип связи? Мы можем предположить, что каждая книга может присутствовать в библиотеке в нескольких экземплярах, поэтому связь «один-ко-многим». При этом если в библиотеке нет ни одного экземпляра дайной книги, то мы не будем хранить ее описание, поэтому если книга описана в сущности «Книги», то по крайней мере один экземпляр этой книги присутствует в библиотеке.

Это означает, что со стороны

Это означает, что со стороны книги связь обязательная. Что касается сущности «Экземпляры», то не может существовать в библиотеке ни одного экземпляра, который бы не относился к конкретной книге, поэтому и со стороны «Экземпляры» связь тоже обязательная.

Теперь нам необходимо определить, как в нашей системе будет представлен читатель. Естественно предложить ввести для этого сущность «Читатели», каждый экземпляр которой будет соответствовать конкретному читателю. В библиотеке каждому читателю присваивается уникальный номер читательского билета, который будет однозначно идентифицировать нашего читателя. Номер читательского билета будет ключевым атрибутом сущности «Читатели». Кроме того, в сущности «Читатели» должны присутствовать дополнительные атрибуты, которые требуются для решения поставленных задач, этими атрибутами будут: «Фамилия Имя Отчество», «Адрес читателя», «Телефон домашний» и «Телефон рабочий». Почему мы ввели два отдельных атрибута под телефоны? Потому что надо в разное время звонить по этим телефонам, чтобы застать читателя, поэтому администрации библиотеки будет важно знать, к какому типу относится данный телефон. В описании нашей предметной области существует ограничение на возраст наших читателей, поэтому в сущности «Читатели» надо ввести обязательный атрибут «Дата рождения», который позволит нам контролировать возраст наших читателей.

Из описания предметной области мы знаем, что каждый читатель может держать на руках несколько экземпляров книг. Для отражения этой ситуации нам надо провести связь между сущностями «Читатели» и «Экземпляры». А почему не между сущностями «Читатели» и «Книги»? Потому что читатель берет из библиотеки конкретный экземпляр конкретной книги, а не просто книгу. А как же узнать, какая книга у данного читателя? А это можно будет узнать по дополнительной связи между сущностями «Экземпляры» и «Книги», и эта связь каждому экземпляру ставит в соответствие одну книгу, поэтому мы в любой момент можем однозначно определить, какие книги находятся на руках у читателя, хотя связываем с читателем только инвентарные номера взятых книг.

и при этом она не

Между сущностями «Читатели» и «Экземпляры» установлена связь «один-ко-многим», и при этом она не обязательная с двух сторон. Читатель в данный момент может не держать ни одной книги на руках, а с другой стороны, данный экземпляр книги может не находиться ни у одного читателя, а просто стоять на полке в библиотеке.

Теперь нам надо отразить последнюю сущность, которая связана с системным каталогом. Системный каталог содержит перечень всех областей знаний, сведения по которым содержатся в библиотечных книгах. Мы можем вспомнить системный каталог в библиотеке, с которого мы обычно начинаем поиск нужных нам книг, если мы не знаем их авторов и названий. Название области знаний может быть длинным и состоять из нескольких слов, поэтому для моделирования системного каталога мы введем сущность «Системный каталог» с двумя атрибутами: «Код области знаний» и «Название области знаний». Атрибут «Код области знаний» будет ключевым атрибутом сущности.

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

Мифологическая модель предметной области «Библиотека» представлена на рис. 7.6.



Рис. 7.6. Мифологическая модель «Библиотека»

Мифологическая модель «Библиотека» разработана нами под те задачи, которые были перечислены ранее.В этих задачах мы не ставили условие хранения истории чтения книги, например, с целью поиска того, кто раньше держал книгу и мог нанести ей вред или забыть в ней случайно большую сумму денег. Если бы мы ставили перед собой задачу хранения и этой информации, то наша инфо-логическая модель была бы другой. Я оставлю эту задачу для вашего самостоятельного творчества.

Переход к реляционной модели данных

Инфологическая модель используется на ранних стадиях разработки проекта. Если понимать язык условных обозначений, которые соответствуют категориям ER-модели, то ее можно легко «читать», следовательно, она доступна для анализа программистам-разработчикам, которые будут разрабатывать отдельные приложения. Она имеет однозначную интерпретацию, в отличие от некоторых предложений естественного языка, и поэтому здесь не может быть никакого недопонимания со стороны разработчиков.

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

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

в соответствие отношение реляционной модели

Рассмотрим правила преобразования ER-модели в реляционную.

Каждой сущности ставится в соответствие отношение реляционной модели данных. При этом имена сущности и отношения могут быть различными, потому что на имена сущностей могут не накладываться дополнительные синтаксические ограничения, кроме уникальности имени в рамках модели. Имена отношений могут быть ограничены требованиями конкретной СУБД, чаще всего эти имена являются идентификаторами в некотором базовом языке, они ограничены по длине и не должны содержать пробелов и некоторых специальных символов. Например, сущность может быть названа «Книжный каталог», а соответствующее ей отношение желательно назвать, например, BOOKS (без пробелов и латинскими буквами).

Каждый атрибут сущности становится атрибутом соответствующего отношения. Переименование атрибутов должно происходить в соответствии с теми же правилами, что и переименование отношений в п.1. Для каждого атрибута задается конкретный допустимый в СУБД тип данных и обязательность или необязательность данного атрибута (то есть допустимость или недопустимость MULL значений для него).



Рис. 7.7. Преобразование сущности СОТРУДНИК к отношению EMPLOYEE

Первичный ключ сущности становится PRIMARY KEY соответствующего отношения. Атрибуты, входящие в первичный ключ отношения, автоматически получают свойство обязательности (NOT NULL).



Рис. 7.8. Свойства атрибутов отношения EMPLOYEE

В каждое отношение, соответствующее подчиненной сущности, добавляется набор атрибутов основной сущности, являющейся первичным ключом основной сущности. В отношении, соответствующем подчиненной сущности, этот набор атрибутов становится внешним ключом (FOREING KEY).

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

Для отражения категоризации сущностей при

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



Рис. 7.9. Преобразование взаимосвязанных сущностей СТУДЕНТ и ПРЕПОДАВАТЕЛЬ к взаимосвязанным отношениям STUDENT и PROFESSOR

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

Дополнительно при описании отношения между типом и подтипами необходимо указать тип дискриминатора. Дискриминатор может быть взаимоисключающим (М/Е, mutually exclusive ) или нет. Если установлен данный тип дискриминатора, то это значит, что один экземпляр сущности супертипа связан только с одним экземпляром сущности подтипа и для каждого экземпляра сущности супертипа существует потомок. Кроме того, необходимо указать для второго способа, наследуется ли только идентификатор супертипа в подтипы или наследуются все атрибуты супертипа.

Если мы зададим наследование только идентификатора, то мы получим следующее преобразование (см. рис. 7.10 и 7.11).



Рис. 7.10. Исходная модель взаимосвязи супертипа и подтипов



Рис.7.11. Результирующая модель с наследованием только идентификатора суперсущности

Разрешение связей типа «многие-ко-многим». Так как в реляционной модели данных поддерживаются между отношениями только связи типа «один-ко-мно-гим», а в ER-модели допустимы связи «многие-ко-многим», то необходим специальный механизм преобразования, который позволит отразить множественные связи, неспецифические для реляционной модели, с помощью допустимых для нее категорий.

Это делается введением специального дополнительного

Это делается введением специального дополнительного связующего отношения, которое связано с каждым исходным связью «один-ко-мно-гим», атрибутами этого отношения являются первичные ключи связываемых отношений. Так, например, в схеме «Библиотека» присутствует связь такого типа между сущностью «Книги» и «Системный каталог». Для разрешения этой неспецифической связи при переходе к реляционной модели должно быть введено специальное дополнительное отношение, которое имеет всего два атрибута: ISBN (шифр книги) и KOD (код области знаний). При этом каждый из атрибутов нового отношения является внешним ключом (FORKING KEY), а вместе они образуют первичный ключ (PRIMARY KEY) новой связующей сущности. На рис. 7.12 представлена реляционная модель, соответствующая представленной ранее на рис. 7.6 инфологической модели «Библиотека».

Теория нормализации, которую мы рассматривали ранее применительно к реляционной модели, применима и к модели «сущность—связь». Поэтому нормализацию можно проводить и на уровне мифологической (семантической) модели и смысл ее аналогичен нормализации реляционной модели. Алгоритм приведения семантической модели к 5-й нормальной форме может быть следующим:



Рис. 7.12. Результирующая модель с наследованием всех атрибутов суперсущности

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

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

Полученная схема будет находиться во

Полученная схема будет находиться во второй нормальной форме. Перейти к шагу 3.

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

Шаг 4. Проанализировать все сущности на наличие детерминантов, которые не являются возможными ключами. При обнаружении подобных расщепить сущность на две, установив между ними соответствующие связи. Полученная схема соответствует нормальной форме Бойса—Кодда. Перейти к шагу 5.

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



Рис. 7.13. Реляционная схема «Библиотека»

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

---

---