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

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

Операторы

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

данными

101

(SELECT

Rl.OMO

FROM

WHERE

Rl.Оценка

2 OR

Rl.Оценка

NULL)

Почему

мы в

первом запросе

на

обновление

не

использовали

дополнительную

проверку

па

отсутствие двоек, троек

несданных

экзаменов,

как мы

сделали

это

при

назначении следующих видов

стипендии?

Просто

мы

учли особенности

на-

шей

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

у нас в

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

исходными

данными

не

только

экзамена.

Но

если

мы

можем

предположить,

что

число экзаменов может быть

произвольным

изменяться

от

семестра

семестру,

то нам

надо

изменить

наш

запрос. Запрос

— это

некоторый

алгоритм

решения

конкретной

задачи,

которую

мы

формулируем

заранее

па

естественном

языке.

оттого,

что

наша задача

ре-

шается

всего

одним

оператором

языка

SQL,

она не

становится

примитивной.

Мощность языка

SQL и

состоит

том,

что он

позволяет одним

предложени-

ем

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

отпеты

на

достаточно

сложные

запросы,

для

реализации

которых

на

традиционных языках понадобилось

бы

писать большую программу.

Итак,

подумаем,

как нам

надо

изменить

текст

нашего запроса

на

обновление

для

назначения

повышенной

стипендии

при

любом

количестве

сданных экзаменов.

Прежде

всего, каждая группа может

иметь

свое

число

экзаменов

сессию,

это

зависит

от

специальности

учебного

плана,

по

которому

учится

данная группа.

Поэтому

для

каждого студента

нам

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

он

должен

был

сдавать

сколько экзаменов

он

сдал

на

пять,

и в том

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

эти

два

числа

равны,

мы

можем назначить

ему

повышенную

стипендию.

Будем решать нашу задачу

по

шагам.

конечном

счете

нам все

равно

надо

знать,

сколько

экзаменов

должен

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

конкретный

студент,

поэтому снача-

ла

сосчитаем

количество

экзаменов,

которые

должна

сдавать

группа,

которой

учится

этот

студент,

Это мы

делать

умеем,

для

этого

надо

сделать запрос

SELECT

над

отношением

R3,

сгруппировав

его по

атрибуту

Группа,

вывести

для

каждой группы

количество

дисциплин,

по

которым должны сдаваться

экзамены.

Если

мы

учтем,

что

од-

ной

сессии

по

одной

дисциплине

не

бывает

более одного

экзамена,

то

можно

просто подсчитывать

количество

строк

каждой группе.

SELECT

R3,Группа,

Число_экзаненов

COUNTf*)

FROM

GROOP

R3.Группа

Однако

нам

нужен

не

этот запрос,

нам

нужен

запрос,

котором

мы

определяем

для

каждого

студента

количество

экзаменов.

Этот

запрос

мы

должны

строить

по

схеме

встроенного запроса;

SELECT

COUNT(*)

FROM

WHERE

R2.Группа

R3,Группа

GROOP

R3.Группа

102

Глава

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

базе

данных.

почему

мы

здесь

части

FROM

не

написали

имя

второго отношения

R2? Мы

имя

этого

отношения укажем

для

связи

вышестоящим запросом, когда будем

формировать запрос полностью.

Теперь

попробуем сформулировать полностью

запрос.

Нам

надо объединить

отношения

R1 и R2 по

атрибуту

ФИО,

нам

надо

знать

группу,

которой учится каждый

студент,

надо

выбрать

все

строки

оцет

кой

5 и

сгруппировать

их по

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

каж-

дой

группе,

выбирать

мы

будем

те

группы,

которых

число

строк

группе

равно

числу

строк

во

встроенном

запросе,

рассмотренном ранее,

при

условии

равенства

количества

строк

группе

результату подзапроса, который

выводит

только одно

число.

SELECT

R1.ФИО

FROM

R1.R2

WHERE

R1.

ФИО

Р2.ФИО

AND

R1.Оценка

GROOP

Rl-ФИО

HAVING

COUNT(*)

(SELECT

COUNTC*)

FROM

WHERE

R2.Группа

R3.Группа

GROOP

R3.Группа)

Ну

теперь

нам

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

операция:

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

ста-

рый

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

три

пятерки

на

сессии,

на

новый

универсальный

запрос:

UPDATE

SET

R5.Стипендия

50£

WHERE

КБ.ФИО

(SELECT

Rl-ФИО

FROM

R1.R2

WHERE

Rl.

ФИО

Р2.ФИО

AND

Rl,Оценка

GROOP

Rl-ФИО

HAVING

COUNTS)

(SELECT

COUNT(*>

FROM

WHERE

R2.Группа

R3.Группа

GROOP

R3.Группа»

Вот

какой

сложный

запрос

мы

построили.

Это

ведь

практически

одни

оператор,

какую сложную задачу

он

решает.

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

мощность

языка

SQL

ино-

гда

удивляет даже профессионалов, кажется

невозможно

построить один запрос

для

решения

конкретной задачи,

но

когда начинаешь

поэтапно

его

конструиро-

вать

— все

получается. Самое

сложное

- это

сделать

Переход

от

словесной

фор-

мулировки

задачи

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

се в

терминах

нашего

SQL,

по

этот процесс

сродни процессу

алгоритмизации

при

решении задач

традиционного

програм-

мирования,

он

всегда

был

самым

трудным,

творческим

лсформализуемым

процессом. Недаром

на

заре развития

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

известный американ-

ский

специалист

по

программированию

Дональд

Е,

Кнут озаглавил свой

много-

Задания

для

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

работы

103

томный

капитальный

труд

по

теории

практике

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

«Искусство

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

для

ЭВМ»

(«The

art of

computer

programming»).

Задания

для

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

работы

Задание

отношении

отметить студентов

—

претендентов

на

отчисление.

Считаем,

что

отношении

находятся

окончательные

результаты

сессии,

по-

этому

отчислению подлежат

все

студенты, которые

не

сдали

пли не

сдавали

два

более

из

положенных экзаменов

сессию.

Для

того чтобы зафиксировать

этот

факт,

нам

потребуется добавить

еще

один столбец

отношение

R5,

назовем

его

результат_сессии,

и там

могут

бь'пъ

два

допустимых значения:

переведен

на

сле-

дующий

курс

или

отчислен.

Запрос писать

по

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

Задание

2. В

отношении

отметить

студентов,

переведенных

на

курс.

Задание

Провести отчисление студентов

по

результатам текущей сессии.

Об-

ратите внимание,

что это уже

другая операция

по

сравнению

заданиями

1 и 2.

ГЛАВА

Проектирование

реляционных

БД

на

основе

принципов

нормализации

Что

такое проект?

Это

схема

—

эскиз некоторого устройства, который

дальней-

шем

будет

воплощен

реальность.

Что

такое проект

реляционной

базы

данных?

Это

набор

взаимосвязанных

отношений,

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

все

атрибуты,

заданы

первичные

ключи

отношений

заданы

еще

некоторые

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

свойства

отношений,

которые относятся

принципам

поддержки

целостности

будут более подробно

рассмотрены

главе

Почему

именно

взаимосвязанных

отношений?

Потому

что при

выполнении

запросов

мы

производим

объединение

отношений

одни

и те же

значения должны

разных

отношениях-таблицах

обозначаться

одинаково. Действительно, если

мы в

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

обозначать цифрами,

а в

другой

словами

«отлично»,

«хорошо»

и т.

д.,

то мы

не

сможем

объединить

эти

таблицы

по

столбцу

Оценка,

хотя

по

смыслу

это для

нас

одно

и то

же,

но то, что

интуитивно понятно человеку, совсем

не

понятно

«умно-

му»

компьютеру.

Это

проблема

систем

искусственным

интеллектом,

которые

могут

решать весьма

сложные

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

задачи,

трудные

для

рядового

инженера,

но

иногда пасуют перед

простейшими

интуитивными

ассоциациями,

понятными

любому

школьнику.

И это

необходимо учитывать, Поэтому проект

базы

данных

должен быть очень точен

выверен.

Фактически

проект базы

дан-

ных

— это

фундамент

будущего

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

комплекса, который

будет

ис-

пользоваться достаточно

долго

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

И как в

любом

зда-

нии,

можно

достраивать мансарды, переделывать

крышу,

можно

даже менять

окна,

но

заменить

фундамент,

не

разрушив всего

здания,

невозможно,

Этапы

жизненного

цикла базы данных

изображены

на

рис. 6.1.

Они

аналогичны,

в ос-

новном,

развитию любой

программной

системы,

однако

них

есть определен*

лая

специфика,

касающаяся только

баз

данных.

Более

подробно

мы

будем

рас-

сматривать этапы

жизненного

цикла

БД в

следующих разделах

учебного

пособия, потому

что

термины,

которые

мы

вынуждены

применять

при

этом

описании,

пока

еще

неизвестны

нашим

читателям.

105

Процесс

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

БД

представляет

собой

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

переходов

от

неформального

словесного

описания

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

структуры

предметной

области

формализованному

описанию объектов

предметной

области

терми-

нах

некоторой

модели.

общем случае можно выделить

следующие

этапы

про-

ектирования;

Системный

анализ

словесное

описание

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

объектов

пред-

метной

области.

Проектирование

пи

фол

отческой

модели предметной

области

частично

фор-

мализованное

описание

объектов

предмешой

области

терминах

некоторой

семантической

модели,

например,

терминах

Е-модели.

Даталогичсскос

или

логическое

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

БД, то

есть

описание

БД

терминах

принятой

датал9гпческой

модели

данных,

.Физическое

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

БД, то

есть выбор

эффективного

размещения

БД па

внешних

носителях

для

обеспечения

наиболее

эффективной работы

приложения.

Если

мы

учтем,

что

между вторым

третьим

этапами

необходимо

принять

ре-

шение,

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

какой

стандартной

СУБД

будет

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

наш

проект,

то

условно

процесс

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

БД

можно

представить

последова-

тельностью

выполнения

пяти

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

этапов

(см.

рис,

6.2).

Рассмот-

рим

более подробно этапы

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

БД.

106

Глава

Проектирование

реляционных

БД на

основе

принципов

нормализации

Системный

анализ

предметной

области

точки

зрения

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

БД в

рамках системного

анализа,

необходи-

мо

осуществить первый этап,

то

есть провести подробное

словесное

описание

объектов

предметной

области

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

описываемыми

объектами»

Желательно,

чтобы

данное

описание

позволяло

кор-

ректно

определить

все

взаимосвязи

между объектами предметной области.

общем случае

существуют

два

подхода

выбору состава

структуры

пред-

метной

области;

Функциональный

подход

- он

реализует

принцип

движения

«от

задач»

при-

Меняется

тогда, когда заранее известны функции некоторой

группы

лиц и

комплексов

задач,

для

обслуживания

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

потребностей

кото-

рых

создается

рассматриваемая

БД. В

этом

случае

мы

можем

четко

выделить

минимальный

необходимый

набор

объектов

предметной

области,

которые

должны

быть описаны,

Предметный

подход

—

когда

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

потребности

будущих

пользо-

вателей

БД

жестко

не

фиксируются.

Они

могут быть

многоаспектными

весьма

динамичными.

Мы не

можем

точно

выделить

минимальный

набор

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

описывать.

описание

предметной

области

этом случае включаются

такие

объекты

взаимосвязи,

которые

наиболее

характерны

наиболее существенны

для

нее.

БД,

кон-

струируемая

при

этом, называется предметной,

то

есть

она

может быть

ис-

Системный

анализ

предметной

области

107

пользована

при

решении

множества

разнообразных,

заранее

пе

определен-

ных

задач.

Конструирование

предметной

БД в

некотором

смысле кажется

го-

раздо более заманчивым, однако трудность всеобщего

охвата

предметной

об-

ласти

невозможностью

конкретизации

потребностей пользователей может

привести

избыточно

сложной

схеме

БД,

которая

для

конкретных

задач

бу-

дет

неэффективной.

Чаще всего

па

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

компромисс-

нцй

вариант,

.который,

одной

стороны,

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

на

конкретные

задачи

или

функциональные

потребности

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

а с

другой

стороны,

учитывает

возможность

наращивания

новых

приложений.

Системный

анализ

должен

заканчиваться

подробным

описанием

информации

об

объектах

предметной

области, которая требуется

для

решения

конкретных

задач

которая должна храниться

БД,

формулировкой конкретных задач,

которые

будут

решаться

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

данной

БД с

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

ал-

горитмов

их

решения,

описанием выходных

документов,

которые должны гене-

рироваться

системе,

описанием

входных

документов,

которые

служат

основа-

нием

для

заполнения

данными

БД.

Пример

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

Пусть

требуется

разработать

информационную

систему

для

автоматизации

уче-

та

получения

выдачи

книг

библиотеке. Система

должна

предусматривать

режимы

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

знаний,

по

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

книги

библиотеке. Внутри библиотеки области

знаний

систематическом

каталоге могут

иметь

уникальный

внутренний

номер

пол-

ное

наименование.

Каждая

книга

может

содержать сведения

из

нескольких

об-

ластей

знаний.

Каждая

книга

библиотеке может присутствовать

нескольких

экземплярах.

Каждая

книга,

хранящаяся

библиотеке,

характеризуется

следую-

щими

параметрами;

уникальный

шифр;

название)

фамилии

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

место

издания

(город);

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

Q год

издания;

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

стоимость

книги;

количество экземпляров

книги

библиотеке.

Книги

Могут

иметь одинаковые

названия,

по

они

различаются

по

своему

уни-

кальному

шифру

(ISBN).

библиотеке

ведется

картотека

читателей.

На

каждого читателя

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

сведения:

108

Глава

Проектирование реляционных

БД на

основе принципов

нормализации

фамилия,

имя,

отчество;

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

телефон

(будем

считать,

что у нас

дна

телефона

—

рабочий

домашний);

дата

рождения.

Каждому

читателю присваивается

уникальный

номер читательского

билета.

Каждый

читатель может

одновременно

держать

на

руках

не

более

книг. Чита-

тель

не

должен

одновременно

держать

более

одного

экземпляра

книги

одного

названия.

Каждая

книга

библиотеке может

присутствовать

нескольких

экземплярах.

Каждый

экземпляр

имеет

следующие

характеристики:

уникальный

инвентарный

номер;

шифр книги, который совпадает

уникальным

шифром

из

описания

книг;

место размещения

библиотеке.

случае выдачи экземпляра

книги

читателю

библиотеке

хранится

специаль-

ный

вкладыш,

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

номер билета читателя, который

взял

книгу;

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

дата

возврата.

Предусмотреть

следующие

ограничения

на

информацию

системе:

Книга может

не

иметь

ни

одного

автора.

2. В

библиотеке

должны

быть

записаны

читатели

не

моложе

лет,

3. В

библиотеке присутствуют книги,

изданные

начиная

1960

по

текущий

год.

Каждый

читатель

может

держать

на

руках

не

более

книг.

Каждый читатель

при

регистрации

библиотеке

должен

дать

телефон

для

связи;

он

может быть рабочим

или

домашним.

Каждая

область

знаний

может

содержать

ссылки

на

множество

книг,

но

каж-

дая

книга может

относиться

различным

областям

знаний.

данной

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

системой

должны

работать

следующие

группы

поль-

зователей:

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

читатели;

администрация

библиотеки!

При

работе

системой

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

должен иметь возможность

решать

сле-

дующие задачи:

Принимать

новые

книги

регистрировать

их в

библиотеке.

Относить

книги

одной

или

нескольким

областям

знаний.

Системный

анализ

предметной

области

109

Проводить

каталогизацию

книг,

то

есть

назначение

новых

инвентарных

но-

меров

вновь

принятым

книгам,

и,

помещая

их

на

полки

библиотеки,

запоми-

нать

место

размещения каждого экземпляра,

Проводить

дополнительную

каталогизацию,

если

поступило

несколько

экзем-

пляров

книги,

которая

уже

есть

библиотеке,

при

этом

информация

книге

предметный

каталог

не

вносится,

каждому

новому

экземпляру

присваи-

вается

новый

инвентарный

номер

для

него

определяется

место

на

полке

библиотеки.

Проводить

списание

старых

и не

пользующихся

спросом

книг.

Списывать

можно

только

книги,

ни

один

экземпляр которых

не

находится

читателей.

Списание

проводится

по

специальному

акту

списания,

который

утверждает-

ся

администрацией

библиотеки.

Вести

учет

выданных

книг

читателям,

при

этом

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

два

режима

работы:

выдача книг читателю

прием

от

него возвращаемых

им

книг обрат-

но

библиотеку.

При

выдаче книг

фиксируется,

когда

какой экземпляр

книги

был

выдан

данному

читателю

п к

какому

сроку

читатель

должен

вер-

нуть

этот

экземпляр

книги.

При

выдаче

книг

наличие

свободного экземпля-

ра и его

конкретный

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

по

заданному

уникальному

шифру

книги

или

инвентарный номер может быть

известен

заранее.

Но

тре-

буется вести

«историю?-

чтения

книг,

то

есть

требуется

отражать только теку-

щее

состояние

библиотеки.

При

приеме

книги,

возвращаемой

читателем,

про-

веряется соответствие возвращаемого инвентарного номера

книги

выданному

инвентарному

номеру,

она

ставится

на

свое старое место

на

полку

библиотеки.

Проводить списание утерянных

читателем

книг

по

специальному

акту списа-

ния

или

замены,

подписанному

администрацией

библиотеки.

Проводить

закрытие

абонемента

читателя,

то

есть

уничтожение данных

нем,

если

читатель

хочет выписаться

из

библиотеки

не

является

ее

должником,

то

есть

за

ним

не

числится

ни

одной

библиотечной

книги.

Читатель

должен

иметь

возможность

решать

следующие

задачи:

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

системный

каталог,

то

есть

перечень

всех областей

знаний,

книги

по

которым есть

библиотеке.

2. По

выбранной

области

знаний

получить полный

перечень

книг,

которые чис-

лятся

библиотеке.

3. Для

выбранной

книги

получить

инвентарный

номер свободного

экземпляра

книги

или

сообщение

том,

что

свободных

экземпляров

книги

нет.

случае

отсутствия

свободных экземпляров

книги

читатель должен

иметь

возмож-

ность

узнать дату

ближайшего

предполагаемого возврата экземпляра

данной

книги.

Читатель

не

может узнать данные

том,

кого

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

экземпляры

данной книги находятся

на

руках

(в

целях

обеспечения

личной

безопасности

держателей

требуемой

книги).

4. Для

выбранного

автора

получить

список

книг,

которые

числятся

библиотеке.

Администраций

библиотеки

должна

иметь

возможность

получать

сведения

долж-

никах—читателях

библиотеки,

которые

не

вернули

вовремя взятые

книги;

све-

дения

книгах,

которые

не

являются

популярными,

т.

е.

пи

один экземпляр

110

Глава

Проектирование реляционных

БД на

основе принципов нормализации

которых

ие

находится

на

руках

читателей; сведения

CTOHIVTOCTH

конкретной

книги,

для

того чтобы установить

возможность

возмещения стоимости утерян-

ной

книги

или

возможность замены

се

другой

книгой; сведения

наиболее

по-

пулярных книгах,

то

есть

таких,

все

экземпляры

которых находятся

на

руках

читателей.

Этот совсем небольшой пример показывает,

что

перед началом

разработки

не-

обходимо

иметь точное представление

том,

что же

должно выполняться

на-

шей

системе, какие пользователи

в ней

будут работать, какие

задачи

будет

ре-

шать

каждый пользователь.

И это

правильно,

ведь

когда

мы

строим

здаппснмы

тоже

заранее

предполагаем:

для

каких

целей

оно

предназначено,

каком клима-

те оно

будет

стоять,

на

какой

почве,

и в

зависимости

от

этого проектировщики

могут предложить

нам тот или

иной проект.

Но, к

сожалению,

очень часто

по

отношению

базам данных считается,

что все

можно

определить потом, когда

проект

системы

уже

создан.

Отсутствие

четких

целей

создания

БД

может све-

сти

на нет все

усилия

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

проект

БД

получится «плохим»,

не-

удобным,

не

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

ни

реально моделируемому объекту,

ни

задачам,

которые

должны

решаться

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

данной

БД.

Отложим

на

время рассмотрение этапа

инфологаческого

моделирования

предмет-

ной

области

—

этому серьезному

вопросу

будет

посвящена

следующая, седьмая

глава,

а мы

пойдем классическим путем

рассмотрим

сначала

этап

даталоги-

ческого

проектирования.

Напомним,

что

этап

даталогического

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

происходит

уже

после выбора

конкретной

модели

данных.

И мы

рассматриваем

даталогическое

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

для

реляционной

модели

данных.

Даталогическое

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

реляционных

БД

даталогическое

или

логическое

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

приводит

разработке

схемы

БД, то

есть

совокупности

схем

отношений,

которые

адекват-

но

моделируют абстрактные

объекты

предметной

области

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

свя-

зи

между этими

объектами.

Основой

анализа

корректности

схемы являются

так

называемые

функциональные

зависимости

между

атрибутами

БД.

Некоторые

зависимости

между атрибутами

отношений

являются

нежелательными

из-за

по-

бочных

эффектов

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

они

вызывают

при

модификации

БД.

При

этом

под

процессом

модификации

БД мы

понимаем

внесение

новых

дан-

ных

в БД или

удаление

некоторых

данных

из

БД,

также

обновление

значений

некоторых

атрибутов.

Однако

этап логического

или

даталогического

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

не

закапчива-

ется проектированием схемы отношений.

общем случае

результате выпол-

нения

этого этапа должны быть получены

следующие

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

доку-

менты;

Описание

концептуальной

схемы

БД в

терминах

выбранной

СУБД.

Описание

внешних

моделей

терминах

выбранной

СУБД.

Описание декларативных

правил

поддержки

целостности

базы

данных.

ГЛ

Разработка процедур поддержки

семантической

целостности базы

данных.