Логическое проектирование баз данных

УДК 004.6 
М79 

Р е ц е н з е н т 
кандидат технических наук, доцент J. С. Кожаринов 

Морозов Е.А. 
М79 
Логическое проектирование баз данных: Практикум. 
М.:МИСиС,2003.-67с. 

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

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

Практикум соответствует учебной программе по дисциплине «Базы данных». 
Для студентов, обучающихся по специальностям 2202 «Автоматизированные системы обработки информации и управления» и 3514э «Прикладная 
информатика». 

© Московский государственный институт 
стали и сплавов (Технологический 
университет) (МИСиС), 2003 

ОГЛАВЛЕНИЕ 

Введение 
4 

1. Сущность логического проектирования БД 
5 

2. Отображение сущностей в таблицы 
8 

3. Изменение структуры БД с целью повышения 
производительности системы 
11 

3.1. Денормализация таблиц БД 
11 

3.2. Изменение структуры таблиц БД с целью улучшения 
гибкости системы 
16 

3.3. Изменение структуры таблиц БД с целью уменьшения 
времени реакции системы на основные запросы 
24 

4. Описание таблиц 
32 

5. Задание ограничений ссылочной целостности 
37 

6. Задание ограничений целостности: общих, доменов и 

семантики 
46 

7. Обеспечение целостности данных с помощью процедур 
49 

8. Использование триггеров для обеспечения целостности данных 
54 

9. Выбор и описание вторичных индексов 
58 

10. Создание представлений 
61 

Заключение 
65 

Библиографический список 
66 

ВВЕДЕНИЕ 

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

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

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

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

1. СУЩНОСТЬ ЛОГИЧЕСКОГО 
ПРОЕКТИРОВАНИЯ БД 

Основной целью логического проектирования БД является 
разработка логической структуры БД с учетом модельных ограничений выбранной СУБД и ее описание на языке описания данных 
(ЯОД - подмножество операторов языка SQL). 

Исходными данными для проектирования являются: 
- 
концептуальная схема БД; 

- 
описание приложений; 

- 
ограничения модели данных выбранной СУБД. 

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

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

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

• 
Отображение сущностей в таблицы (определение таблиц). 

• 
Описание таблиц на ЯОД. 

• 
Задание ограничений целостности данных. 

• 
Выбор и описание вторичных индексов. 

• 
Описание представлений. 

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

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

Результатом логического проектирования является логическая схема БД, описанная операторами SQL. Это описание используется в дальнейшем администратором БД при физическом создании 
БД (в памяти компьютера) в качестве метаданных, хранимых в системном каталоге. На рис. 1.1 представлена роль схемы БД в плане 
трехуровневой архитектуры ANSI/SPARC описания данных в системном каталоге. 

Внешний 
уровень 

Подсхема 
пользователя 1 

Подсхема 
пользователя N 

Концептуальный 
уровень 

Схема БД 

Внутренний 
уровень 

Внутренняя схема 

Описание 
размещения, 
методы доступа 

База данных 

Рис. 1.1. Трехуровневая архитектура ANSI/SPARC описания данных 
в системном каталоге 

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

В логической схеме представляются все основные объекты 
БД (прежде всего это таблицы со связями между ними индексы и 
представления). Однако в зависимости от особенностей конкретной 
СУБД могут использоваться и редко встречающиеся объекты, как 
например, последовательности (средства генерации последовательности целых чисел) для СУБД ORACLE. В качестве иллюстрации 
понятия схемы на рис. 1.2 представлен возможный вариант схемы 
для ORACLE [2]. 

Представление 
CUST 

Табл. 
CUSTOMERS 
Табл. ORDERS 

Табл. ITEMS 

Customer Table 
id 
company 
last name 
address 
city 
state 
phone 

ORDERS TABLE 
id 
cost id 
order, date 
status 
1 

Items, table 
order, id 
id 
part, date 
quantity 

PARTS TABLE 

Представление 
ORDER ITEM 

order id 
order cost. 
items 
items 

id 

id 

quantity 

Рис. 1.2. Пример схемы БД для ORACLE 

2. ОТОБРАЖЕНИЕ СУЩНОСТЕЙ В ТАБЛИЦЫ 

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

- 
сущность отображается в две или более таблиц; 

- 
несколько сущностей отображаются в одну таблицу. 

Первый случай возможен, например, при использовании ранних версий СУБД ORACLE, если сущность имеет более одного атрибута с типом данных Long. 

Пример. 
Сущность Публикация: 

Название 
Вид публикации (статья, книга) 
Аннотация - Long 
ФИО автора 
Текст публикации - Long 
Наличие двух длинных полей вынуждает отобразить эту 
сущность в двух таблицах: 
Таблица PublMain 

Publld - Integer (идентификатор публикации) 
PublType-Char(l) 
Announce - Long 
Family - Varchar (60) 
Таблица PublText 

Piblld 
PublText - Long 
Другие причины разбивки сущности могут быть связаны с 
особенностями обработки данных в приложении. Например, в распределенной БД нужно только часть данных отсылать в другую БД. Тогда 
неотсылаемые атрибуты можно выделить в отдельную таблицу. 
Пример. 
Сущность Кандидат мажоритарного округа: 

ФИО кандидата 
Место работы 
Должность 
Дата регистрации 
Фото кандидата 

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

Второй случай - отображение двух сущностей в одну таблицу. 
Сущность Кандидат в депутаты: 

ИД кандидата 
Фамилия 
Имя 
Отчество 
Дата рождения 
Адрес 
Работа 
Дата выдвижения 

Сущность Кандидат с отказом в регистрации: 

ИД кандидата 
Орган, принявший решение 
Номер решения 
Дата решения 
Причина решения 
Эти сущности выступают в роли класса и подкласса. У них 
совпадают ключевые атрибуты. 

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

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

С учетом этих замечаний две исходные сущности можно отобразить в одну таблицу. 
Таблица Candidat 

Candid - Integer 
Name - Varchar (20) 
Firstname - Varchar (20) 

Lastname - Varchar (20) 
BirthDate - Date 
Address - Varchar (60) 
Work -Varchar (60) 
DateStart - Date 
UnitDes - Varchar (50) 
NvmDes-Dec(3,0) 
DateDes - Date 
PrichDes-Varchar (100) 
Приведенные примеры преобразования сущностей в таблицы 
являются простыми и, самое главное, они не приводят к серьезным 
изменениям концептуальной схемы БД. Ситуация может оказаться 
значительно сложнее, когда требуется преобразование концептуальной схемы. Последнее чаще всего обусловлено необходимостью повышения производительности системы или обеспечения ее надежности. На первый взгляд, необходимость изменения концептуальной 
схемы может выглядеть как недоработка концептуального проектирования. Однако следует принять во внимание, что именно на стадии 
логического проектирования разработчик учитывает ограничения и 
особенности конкретной СУБД. Поэтому именно на этой стадии он 
может понять, обеспечивает ли концептуальная схема необходимую 
производительность информационной системы или надежность хранения и обработки данных. 

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

10 

3. ИЗМЕНЕНИЕ СТРУКТУРЫ БД С ЦЕЛЬЮ 
ПОВЫШЕНИЯ ПРОИЗВОДИТЕЛЬНОСТИ 

СИСТЕМЫ 

3.1. Денормализация таблиц БД 

На этапе логического проектирования иногда возникает необходимость денормализации таблиц БД. Под денормализацией понимают процесс внесения изменений в структуру нормализованных 
таблиц, другими словами, введение избыточности в таблицы [3, 4]. 
Цель денормализации - повышение производительности системы за 
счет упрощения доступа к некоторому подмножеству данных. Денормализация имеет следующие недостатки: 

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

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

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

Рассмотрим возможные случаи денормализации. 

1. Использование производных (вычисляемых) данных 
Очень часто выходными данными запросов являются вычисляемые данные. При этом возникает дилемма - либо их где-то хранить, либо вычислять по мере исполнения запроса. 

Пример. 

Таблица 3.1 

Товар 

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

А 
С 

Количество 

10 
5 

Цена 

2 
10 

Стоимость партии 

20 
50 

11 

в данном примере столбец «Стоимость партии» является избыточным, так как может быть вычислен по формуле 

Стоимость = Цена х Количество 

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

2. Объединение сущностей со связями типа 1:1 

Пример. 

Таблица 3.2 

Житель (района) 

ФИО 

Иванов 
Петров 
Сидоров 
Кошкин 

Адрес 

А 
В 
К 
с 

Таблица 3.3 

Интервью 

ФИО 

Петров 
Кошкин 

Дата 

10.11.01 
11.11.01 

Комментарий 

Доволен 

Не доволен 

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

Таблица 3.4 

Житель (которого интервьюировали) 

ФИО 

Иванов 
Петров 
Сидоров 
Кошкин 

Адрес 

А 
В 
К 
с 

Дата 

NULL 
10.11.01 
NULL 
11.11.01 

Комментарий 

NULL 
Доволен 
NULL 

Пе доволен 

12