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

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

2-е издание, исправленное

Туманов В.Е.

Национальный Открытый Университет “ИНТУИТ”
2016

2

УДК 004.652.4(075.8)
ББК 17
Т83
Основы проектирования реляционных баз данных. / Туманов В.Е. - M.: Национальный Открытый
Университет “ИНТУИТ”, 2016 (Основы информационных технологий)
ISBN 978-5-94774-713-3

Проектирование реляционных баз данных является составной частью проектов создания
информационных систем с базами данных. В курсе изучается базовый набор профессиональных
задач проектировщика реляционных баз данных. Приведены примеры решения таких задач.
Курс нацелен на формирование навыков проектирования реляционных баз данных для OLTP-систем.
Курс разработан в соответствии с модульной методикой профессионального обучения. В отличие от
традиционного предметного подхода к формированию программ обучения, ориентированного, в
основном, на передачу знаний, модульный метод основан на деятельностном подходе к построению
курсов и нацелен на достижение обучаемым в ходе обучения определенной профессиональной
компетентности. В рамках курса проанализирован базовый набор профессиональных задач
проектировщика реляционных баз данных. Каждая задача представлена в виде набора связанных
бизнес - процедур, которые должен выполнить проектировщик реляционной базы данных.
Предложена базовая бизнес-модель процесса проектирования реляционных баз данных, которая
может быть взята за основу при реализации проектов создания информационных систем с базами
данных. Приведены примеры решения базовых профессиональных задач проектировщика
реляционных баз данных на основе диалекта SQL СУБД Oracle9i и SQLBase 7.0.

(c) ООО “ИНТУИТ.РУ”, 2007-2016
(c) Туманов В.Е., 2007-2016

3

Предисловие

Выборочные аналитические исследования ИТ-проектов, периодически проводимые
международными компаниями Gartner Groups и IDC, показывают, что далеко не все
они заканчиваются успешно. По оценкам этих компаний, 50% проектов с базами
данных нельзя признать успешными. Существует множество причин, по которым это
происходит: от плохо поставленной задачи до неудовлетворительного исполнения в
установленные сроки. Одним из направлений работы, способствующих успешности
ИТ-проектов, является формирование компетенции членов команды проекта в
процессе целенаправленного обучения.

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

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

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

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

Процесс проектирования базы данных охватывает несколько основных сфер:

4

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

OLAP, OLTP и т. д.) системы.

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

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

В чем состоит задача проектирования базы данных? База данных:

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

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

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

5

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

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

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

Во второй лекции - “Предметная область базы данных и ее модели” - вводится понятие
предметной области базы данных, описываются основные приемы построения моделей
предметной области. Рассматриваемые модели являются входными данными для
процесса проектирования базы данных.

В третьей лекции - “Что такое проектирование баз данных” - определяется процесс
проектирования базы данных и рассматривается базовая бизнес-модель процесса
проектирования реляционной базы данных, основанная на понятии жизненного цикла.

В четвертой лекции “Реляционная модель данных” вводятся основные понятия
реляционной модели данных. Эти понятия используются при решении задачи
проектирования реляционной базы данных - создания логической модели реляционной
базы данных.

В пятой лекции - “Функциональные зависимости и реляционные базы данных” -
вводится понятие функциональной зависимости. Это понятие является основой
математической теории реляционных баз данных.

В шестой лекции - “Нормальные формы отношений. Создание логической модели
реляционной базы данных” - вводится понятие нормальных форм отношений и
понятие логической модели реляционной базы данных. Эти понятия составляют
теоретическую основу для процедур проектирования реляционных баз данных.

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

В восьмой лекции - “Введение в структурированный язык запросов - SQL ” - вводятся
основные понятия языка манипулирования данными в реляционных базах данных ( SQL

6

) и описываются объекты физической базы данных и операторы языка.

В девятой лекции - “Создание объектов для хранения данных. Работа с
ограничениями” - изучается создание объектов физической реляционной базы данных
на основе разработанной логической модели с помощью команд SQL.

В десятой лекции - “Создание физической модели базы данных: учет влияния
транзакций” - рассматриваются вопросы учета влияния транзакций при
проектировании физической структуры базы данных и принципы денормализации на
уровне расширения логической модели реляционной базы данных.

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

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

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

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

В пятнадцатой лекции - “Введение в оптимизацию запросов” - рассматриваются
основы оптимизации обработки запросов в реляционных базах данных. Здесь мы
кратко рассмотрим эволюцию языков обработки запросов и различные подходы к
оптимизации запросов в реляционных СУБД.

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

Моим детям Наталии, Евгению и Ольге посвящается

7

Информационные системы с базами данных

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

Информация, данные, информационные системы

Информация как социальный ресурс

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

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

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

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

Информация и данные

Прежде чем перейти к обсуждению понятия информационной системы (ИС),
попытаемся выяснить, что же понимается под словом информация. Ответить на этот
вопрос и просто, и сложно: слово “информация” связано с широким кругом понятий, в
том числе и определенных строго математически (информация по Шеннону,

8

например).

Содержательная же сторона понятия “информация” очень многогранна и не имеет
четких семантических границ. Однако всегда можно сказать, что можно с ней делать.
Именно ответ на этот вопрос чаще всего и интересует как системных аналитиков и
разработчиков (ИС), так и пользователей информации (ее основных потребителей).

С точки зрения как пользователей, так и разработчиков ИС, у информации есть одно
важное свойство - она является единицей данных, подлежащих обработке. Обычно
информация поступает потребителю именно в виде данных: таблиц, графиков,
рисунков, фильмов, устных сообщений, которые фиксируют в себе информацию
определенной структуры и типа. Таким образом, данные выступают как способ
представления информации в определенной, фиксированной форме, пригодной для
обработки, хранения и передачи. Хотя очень часто термины “информация” и “данные”
выступают как синонимы (особенно в среде разработчиков ИС), следует помнить об
этом их существенном отличии. Именно в данных информация обретает
интерпретацию в конкретной ИС.

При упоминании о “форме” представления информации следует сказать и еще об
одном, “человеческом” свойстве информации - ее восприятии различными
категориями людей. Данные могут быть сгруппированы совместно в документ.
Документ может иметь или не иметь определенную внутреннюю структуру. Данные
могут быть отображены на экране дисплея компьютера. Документы могут иметь аудио-
или видеоформу. Разрабатывая ИС, никогда не следует забывать, для кого они
(системы) создаются и кто будет их использовать (воспринимать информацию в них).
Форма представления информации в ИС определяет не только ее “дружелюбие”, но
также и категории пользователей. ИС создаются для конкретных групп пользователей,
т.е. они, как правило, проблемно-ориентированны.

Определение понятия информации

Теперь мы готовы дать следующее определение информации в применении к ИС.

Определение 1. Информация есть данные, которым придается некоторый смысл
(интерпретация) в конкретной ситуации в рамках некоторой системы понятий.
Информация представляется посредством кодирования данных и извлекается путем их
декодирования и интерпретации.

В этом определении фиксируется три основных преобразования информации и данных
в процессе их обработки в ИС: информация-данные, данные-данные, данные-
информация.

Рассмотрим пример с классической ИС - библиотекой. Книга поступает в библиотеку.
На нее заводятся библиографические карточки (выполняется преобразование
информация-данные). Библиографические карточки размещаются в каталогах в
соответствии с внутренними библиотечными процедурами систематизации
(выполняется преобразование “данные-данные”). Читатели работают с каталогами

9

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

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

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

Рис. 1.1.  Содержание термина “информация”

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

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

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

10