1. Введение в базы данных

 

Принятые сокращения *

Введение *

Часть 1 *

Общие принципы разработки приложений баз данных в среде Delphi 3 *

1. Введение в базы данных *

1.1. Понятие баз данных. Степень детализации информации в базе данных *

1.2. Реляционные базы данных *

1.3. Понятие первичного ключа *

1.4. Реляционные отношения (связи) между таблицами базы данных *

1.4.1. Отношение "один-ко-многим" *

1.4.2. Отношение "один-к-одному" *

1.4.3. Отношение "многие-ко-многим" *

1.4.4. Связь меяаду записями одной таблицы *

1.5. Ссылочная целостность и каскадные воздействия *

1.6. Понятие внешнего ключа *

1.7. Индексы и методы доступа *

1.8. Нормализация таблиц при проектировании базы данных *

1.9. Нормализация - за и против *

1.10. Понятие транзакций *

1.11. Типы таблиц БД по виду их изменения -справочные, операционные и транзакционные *

1.12. Типы информационных систем по виду накапливания итоговой информации -операционные и накопительные *

1.13. Навигационный и SQL-ориентированный подходы к операциям над данными *

Принятые сокращения

БД - база данных

НД - набор данных

ТБД - таблица базы данных

СУБД - система управления базами данных

BDE - Borland Database Engine, машина баз данных фирмы Borland

DBD - Database Desktop, утилита в составе Delphi для создания таблиц БД, просмотра и изменения данных в таблицах БД

SQL - Structured Query Language, язык структурированных запросов к реляционным базам данных

Ro - при описании свойств компонентов Delphi означает read only, то есть свойство доступно только для чтения

Rt- при описании свойств компонентов Delphi означает run time, то есть свойство доступно только во время выполнения приложения и не доступно во время его разработки

Введение

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

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

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

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

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

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

Во второй части книги рассматриваются вопросы построения приложений в архитектуре "клиент-сервер". Изложение ведется на основе исследования возможностей SQL-сервера Borland InterBase (локальная версия которого поставляется вместе с Delphi) и касается средств и компонентов, присущих только серверным базам данных. Параллельно показывается, как те или иные средства серверной базы данных могут использоваться в приложениях Delphi.

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

Каждый раздел снабжен примерами программ на языке Object Pascal, или на SQL - языке серверных баз данных. Эти примеры брались из готовых приложений, то есть это работающие программы.

 

Часть 1

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

1. Введение в базы данных

1.1. Понятие баз данных. Степень детализации информации в базе данных

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

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

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

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

• быстрой выдачей информации о текущих остатках товара на складе;

• ежемесячной выдачей отчета об общих суммарных отпусках товара.

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

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

1.2. Реляционные базы данных

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

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

На рис. 1.1. приведен пример таблицы БД, в которой содержатся сведения об отпуске товара со склада. Столбцы представляют собой такие параметры, как дата отпуска товара, наименование отпущенного товара, наименование покупателя, количество единиц отпущенного товара. Каждая строка содержит введения о конкретном событии отпуска товара покупателю. в терминологии теории реляционных БД таблицам соответствуют отношения, столбцам - атрибуты, строкам - кортежи.

При практической разработке БД таблицы так и зовутся таблицами, строки -записями, столбцы - полями или столбцами ТБД.

Дата

Товар

Покупатель

Отпущено, ед.

10.02.97

Сахар

Геракл, ТОО

100

10.02.97

Сахар

Геракл, ТОО

100

12.02.97

Сахар

Пищеторг, ЗАО

2000

12.02.97

Макароны

Пищеторг, ЗАО

300

14.02.97

Сахар

Геракл, ТОО

200

15.02.97

Дрожжи

База № 28

100

Рис 1 1 Таблица базы данных "Отпуск товаров со склада "

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

Сетевая БД характерна внутренними ссылками между структурами данных. Если от элемента В имеется ссылка на элемент А, возможен выбор элемента данных А (рис. 1.3).

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

Однако иерархический и сетевой подходы продолжают жить, они находят свое воплощение в отдельных специализированных БД и являются

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

Реляционные БД имеют мощный теоретический фундамент, основанный на математической теории отношений. Он был разработан доктором Эдгаром Коддом. Для построения запросов к реляционным БД был также разработан язык SQL (Structured Query Language, язык структуризированных запросов). Он приобрел характер промышленного стандарта в реляционных системах управления базами данных (СУБД). Поэтому, переходя с одной реляционной базы на другую, пользователь и разработчик имеют дело с одним и тем же языком. Другим важным плюсом SQL является то, что этот язык ориентирован на высокоуровневые операции с данными. Выдавая запрос, можно не беспокоиться о низкоуровневых проблемах доступа к данным, специфичных для каждой БД, поскольку интерпретация запросов в команды низкого уровня лежит в ведении конкретной СУБД.

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

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

1.3. Понятие первичного ключа

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

На рис. 1.4 показана таблица "Сотрудники". В качестве первичного ключа не могут использоваться поля:

• ФИО - поскольку практически известно, что даже в одном отделе могут работать однофамильцы с одинаковыми инициалами или даже полные тезки;

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

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

№ пропуска

ФИО

Должность

Отдел

Год рожд.

111222

Иванов И.И.

нач. отдела

122

1940

333444

Петров П.П.

диспетчер

122

1942

234567

Сидоров С.С.

наладчик

118

1940

101010

Петраков А.И.

кладовщик

118

1967

202020

Мамукин М.М.

инженер

196

1966

Рис. 1.4. Таблица "Сотрудники"

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

№ продукта

ФИО

Должность

Отдел

Год рожд.

 

 

111222

Иванов И.И.

нач. отдела

122

1940

2

333444

Петров П.П.

диспетчер

122

1942

i4

234567

Сидоров С.С.

наладчик

118

1940

>2

101010

Петраков А.И.

кладовщик

118

1967

21

202020

Мамукин М.М.

инженер

196

1966

Рис. 1.5. Таблица "Сотрудники" после введения уникального поля

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

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

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

Приведем другие примеры первичных ключей:

• номер и серия паспорта;

• номер двигателя;

• фамилия и инициалы автора, название книги, год издания, издательство. В случае, если принять во внимание, что в течение одного года книга может издаваться тем же издательством повторно, в первичный ключ следует добавить номер издания (2-е, 3-е и т.д.);

• название факультета, кафедры, года приема - для именования учебных групп в вузах;

• номер лицевого счета в бухгалтерском балансе;

• дата - если событие может случиться в течение даты только единожды, например, поставка продуктов в заводскую столовую;

• дата и время отказа сетевого оборудования, а при наличии нескольких потенциально сбойных узлов - адрес (номер) узла (если сбой узла характеризуется несколькими типами ошибок - еще и номер ошибки);

• почтовый адрес организации - с указанием номеров комнат, в случае, если в одном здании располагается несколько организаций (например, офисное здание);

• банковские реквизиты;

• регистрационный номер банка, организации;

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

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

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

1.4. Реляционные отношения (связи) между таблицами базы данных

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

Существует три разновидности связей между таблицами базы данных: "один-ко-многим", "один-к-одному", "многие-ко-многим".

1.4.1. Отношение "один-ко-многим"

Отношение "один-ко-многим" имеет место, когда одной записи родительской таблицы может соответствовать несколько записей в дочерней таблице.

Как видно из рис. 1.6, одной записи из родительской таблицы "Товары" может соответствовать несколько записей в дочерней таблице "Отпуск товаров". Обратите внимание на глагол может: он означает, что такая возможность - потенциальная и что в родительской таблице могут найтись записи, для которых в данный момент нет записей в дочерней таблице (например, товар "Куры").

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

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

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

Связь "один-ко-многим" является самой распространенной для реляционных баз данных. Как можно заметить, она позволяет моделировать иерархии данных.

В широко распространенной нотации структуры баз данных IDEFIX отношение "один-ко-многим" изображается путем соединения таблиц линией, которая на стороне дочерней таблицы оканчивается кружком или иным символом. Поля, входящие в первичный ключ для данной ТБД, всегда расположены вверху и отчеркнуты от прочих полей линией (рис. 1.7).

1.4.2. Отношение "один-к-одному"

Отношение "один-к-одному" имеет место, когда одной записи в родительской таблице соответствует одна запись в дочерней таблице (рис. 1.8.).

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

Связь "один-к-одному" может быть жесткой и нежесткой. В первом случае для каждой записи в родительской таблице должна существовать запись в дочерней таблице. Во втором случае - запись в дочерней таблице может как существовать, так и отсутствовать.

1.4.3. Отношение "многие-ко-многим"

Отношение "многие-ко-многим" имеет место, когда:

а) записи в родительской таблице может соответствовать больше одной записи в дочерней таблице;

б) записи в дочерней таблице может соответствовать больше одной записи в родительской таблице.

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

Таблица "Учебные группы и Таблица "Преподаватели" дисциплины"

Группа

Предмет

№ преподавателя

 

 

№ преподавателя

ФИО преподавателя

Кафедра

ПС-1

Программирование

10

->

10

Красноов Ю.Б.

ТИ-1

ТИ-1

Программирование

12

 

 

12

Володин В.Н.

ТИ-1

ПС-1

Теория систем

10

62

Булгаков В.М.

РИО

РТ-2

Философия

62

78

Гноенский Л.С.

ТИ-1

ПС-1

Социология

62

85

Подушкин М.А.

ЭИ-1

...

...

...

 

 

...

...

...

Рис 1.9 Связь "многие-ко-многим"

Многие СУБД не поддерживают связи "многие-ко-многим" на уровне индексов и ссылочной целостности (см. следующий подраздел), хотя и позволяют реализовывать ее в таблицах неявным образом. Аналогично, мнногие CASE-средства (программы для разработки структуры базы данных в виде диаграмм и генерации на их основе физической базы данных) также нe позволяют определять эту связь между таблицами проектируемой базы даннь1Х. Считается, что всякая связь "многие-ко-многим" может быть заменена на одну или более связь "один-ко-многим". Хотя это так, по мнению автора, целесообразность применения такой связи должна рассматриваться прежде всего в контексте разрабатываемой базы данных и приложения для работы с ней, и там, где это удобно, такая связь должна реализовываться.

1.4.4. Связь между записями одной таблицы

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

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

Нужно заметить, что автоматическое обеспечение связен записей внутри одной таблицы реляционными СУБД не поддерживается и эти связи нужно реализовывать программно. Несложно заметить, что удаление записи, на которую имеются ссылки (у нас это записи со значением поля "№ подразделения", кроме 4,5,6,8,9,11), должно блокироваться, поскольку в противном случае в таблице будут иметь место ссылки на несуществующие номера подразделений. Также нельзя изменять номера подразделений, на которые имеются ссылки - это разрушит достоверность данных. Такие действия необходимо реализовывать программно. Рассмотренный пример является частным случаем более общей проблемы - обеспечение ссылочной целостности между таблицами базы данных. Речь об этой проблеме пойдет вследующем разделе.

1.5. Ссылочная целостность и каскадные воздействия

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

Таблица "Товары" Таблица "Отпуск товаров"

Товар

Ед.изм.

Цена ед.

 

 

Товар

Дата

Кол-во, ед.

Сахар

кг

5000

-|->

Сахар

10.01.97

100

Макароны

кг

7000

|->

Сахар

12.01.97

200

Куры

кг

10000

|->

Сахар

14.01.97

50

Фанта

бут. 1 л

6000

 

 

Макароны

10.01.97

1000

 

 

 

 

 

 

 

 

Макароны

11.01.97

500

 

 

 

 

 

 

 

 

Фанта

10.01.97

2000

 

 

 

 

 

 

 

 

Фанта

12.01.97

3000

Рис. 1.12. Связанные таблицы базы данных

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

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

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

Разберем первый случай. На рис. 1.13 показано изменение значения поля "Товар" с "Сахар" на "Рафинад" в таблице "Товары". В таблице "Отпуск товаров" значение поля связи "Сахар" осталось прежним. В результате:

в дочерней таблице "Отпуск товаров" для товара "Рафинад" (таблица "Товары") нет сведений о его отпуске со склада;

• некоторые записи таблицы "Отпуск товаров" содержат сведения об отпуске товара ("Сахар"), о котором нет информации в таблице "Товары".

Товар

Ед.изм.

Цена eд

 

 

Товар

Дата

Кол-во, ед.

Рафинад

кг

5000 ---

-|->

Сахар

10.01.97

100

Макароны

кг

7000

|->

Сахар

12.01.97

200

Куры

кг

10000

|->

Сахар

14.01.97

50

Фанта

бут.1 л

6000

 

 

Макароны

10.01.97

1000

 

 

 

 

 

 

 

 

Макароны

11.01.97

500

 

 

 

 

 

 

 

 

Фанта

10.01.97

2000

 

 

 

 

 

 

 

 

Фанта

12.01.97

3000

Таблица "Товары" Таблица "Отпуск товаров"

Рис. 1.13. Нарушение целостности базы данных - записи с товаром "Сахар" (таблица "Отпуск товаров") не имеют родительской записи

 

Разберем второй случай. Пусть в одной из записей таблицы "Отпуск товаров" значение поля связи "Сахар" изменилось на "Рафинад" (рис. 1.14). В результате:

• в дочерней таблице "Отпуск товаров" недостоверны сведения об отпуске со склада товара "Сахар" (таблица "Товары");

• одна из записей таблицы "Отпуск товаров" содержит данные об отпуске товара ("Рафинад"), сведения о котором (такие, как единица измерения и цена за единицу) отсутствуют в таблице "Товары". И в первом, и втором случаях мы наблюдаем нарушение целостности базы данных, поскольку информация в ней становится недостоверной. Следовательно, нужно блокировать действия, которые нарушают целостность связей между таблицами, которую называют ссылочной целостностью. Когда говорят о ссылочной целостности, имеют в виду совокупность связей между отдельными таблицами во всей БД. Нарушение хотя бы одной такой связи делает информацию в БД недостоверной.

Таблица "Товары" Таблица "Отпуск товаров"

Товар

ед.изм.

Цена ед.

 

 

Товар

Дата

Кол-во,ед.

Сахар

кг

5000 ---

-|->

Рафинад

10.01.97

100

Макароны

кг

7000

|->

Сахар

12.01.97

200

Куры

кг

10000

|->

Сахар

14.01.97

50

Фанта

бут.1 л

6000

 

 

Макароны

10.01.97

1000

 

 

 

 

 

 

 

 

Макароны

11.01.97

500

 

 

 

 

 

 

 

 

Фанта

10.01.97

2000

 

 

 

 

 

 

 

 

Фанта

12.01.97

3000

Рис. 1.14. Нарушение целостности базы данных - запись с товаром "Рафинад" (таблица "Отпуск товаров ") не имеет родительской записи

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

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

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

• при удалении записи в родительской таблице, следует удалить соответствующие записи в дочерней таблице.

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

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

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

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

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

1.6. Понятие внешнего ключа

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

1.7. Индексы и методы доступа

Порядковый № записи

Дата прихода товара

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

Количество,

1

10.01.1997

Сахар

10

2

12.01.1997

Картофель

50

3

12.01.1997

Свекла

20

4

14.01.1997

Сахар

50

5

14.01.1997

Свекла

10

6

16.01.1997

Сливы

4

Рис. 1.15. Физическая структура таблицы

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

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

По дате прихода товара

По наименованию товара

По количеству

Дата прихода

№ записи

Товар

№ записи

Количество.

№ записи

10.01.1997

1

Картофель

2

4

6

12.01.1997

2

Сахар

1

10

1

12.01.1997

3

Сахар

3

10

5

14.01.1997

4

Свекла

3

20

3

14.01.1997

5

Свекла

5

50

2

16.01.1997

6

Сливы

6

50

4

Рис. 1 16 Логическая структура индексов

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

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

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

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

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

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

При индексно-последовательном доступе просматривается только часть индекса, а из таблицы читаются только записи, удовлетворяющие условию поиска. Метод назван индексно-последовательным потому, что:

• поиск ведется по индексу, а не по самой таблице;

• поиск в индексе начинается только с первой строки, удовлетворяющей условию запроса или его части (так называемый прямой доступ);

• строки в индексе, начиная с такой записи, просматриваются все-таки последовательно.

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

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

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

Для "локальных" ("персональных") СУБД типа Paradox, dBase индексы хранятся отдельно от основной таблицы БД - в виде отдельного файла. В случае их определения в "промышленных" ("серверных") СУБД - таких как Oracle, Sybase, InterBase, SQL Server - индексы хранятся вместе с БД.

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

• обеспечения нужного порядка сортировки данных;

• оптимизации доступа к базе данных.

1.8. Нормализация таблиц при проектировании базы данных

При проектировании структуры новой БД определяют сущности (объекты, явления) предметной области, которые должны найти свое отражение в базе данных. Анализ предметной области обычно осуществляется:

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

• исходя из целей проектирования программной системы;

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

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

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

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

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

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

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

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

Существует несколько нормальных форм - 1НФ, 2НФ, ЗНФ, 4НФ, ЗНФ, нормальная форма Бойса-Кодда (БКНФ). При практической разработке баз данных важны первые три - 1НФ, 2 НФ, ЗНФ.

Первая нормальная форма (1НФ) требует, чтобы каждое поле таблицы БД:

• было неделимым;

• не содержало повторяющихся групп.

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

Накладная №123

Дата

Покупатель

Адрес

10.01.97

ТОО "Геракл"

г. Москва , ул. Стромынка , 20

Отпущен товар

Количество

ед.изм.

Цена ед.изм.

Общая стоимость

Тушенка

10000

банки

7000

70 000 000

Сахар

200

кг

5000

1 000 000

Макароны

1000

кг

3000

3 000 000

Итого 74 000 000

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

Замечание. Дефис в заголовке таблицы не является обязательным требованием именования таблиц БД; просто таблицы именуются именно такимобразом в использованной автором программе формирования IDEF1X-диаграмм базы данных (Design/IDEF).

Однако, что делать, если товаров не 4, а 104? Конечно, можно определить столько полей, сколько товаров. Но как быть, если число товаров заранее не известно и по одной накладной может быть отпущено 2, а по другой- 772 товара? Реализовать запись с переменным числом полей в реляционных базах данных невозможно, поскольку запись таблицы реляционной БД должна иметь четкую структуру. Исходя из вышесказанного, повторяющиеся группы следует устранить. В результате получим запись, содержащую информацию о статистике продаж по одному товару (рис. 1.18). Для 4 товаров будем иметь 4 записи, для 104 товаров - 104 записи и для n товаров - n записей для каждого месяца.

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

В начале проектирования, приводя данные к первой нормальной форме, сведем имеющиеся данные в одну таблицу. Известно, что впоследствии будет необходимо производить анализ продаж по городам. Поэтому из поля "Адрес" (допускающего толкование как делимого поля) выделим в отдельное поле "Город". Известно, что каждый покупатель может закупить в один день различное количество товаров. Поэтому переборем искушение назначить каждому товару отдельное поле и выделим факт отпуска товара в отдельную запись (рис. 1.19). Для того, чтобы продолжить нормализацию данных, приведем данные ко второй нормальной форме (2НФ).

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

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

Дата

Покупатель

Адрес

 

 

10.01.97

ТОО "Геракл" г. Москва , ул. Стромынка, 20

Отпущен товар

Количество ед. изм.

Цена ед.изм

. Общая стоимость

Тушенка

6000 банки

7000

42 000 000

Тушенка

4000 банки

7000

28 000 000

Сахар

200 кг

5000

1 000 000

Макароны

1000 кг

3000

3 000 000

 

 

 

 

 

 

Итого 74 000 000

Покажем на рис. 1.20. структуру таблицы "Отпуск товаров со склада" после выделения полей в составе первичного ключа (эти поля отчеркнуты от прочих полей линией и располагаются в верхней части структуры таблицы).

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

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

Первое требование 2НФ выполнено. Чего не скажешь о втором требовании, гласящем, что значения всех полей записи должны однозначно зависеть от совокупного значения первичного ключа и не должна иметь место ситуация, когда некоторые поля зависят от части первичного ключа. Действительно, при дальнейшем анализе можно увидеть, что поля "Единица измерения", "Цена за единицу измерения" зависят только от значения поля "Товар". В самом деле, стоимость единицы измерения товара и название самой единицы измерения не зависят от конкретной накладной и будут одинаковыми для всех накладных, в которые входит данный товар. Поэтому выделяем данные поля в отдельную таблицу "Товары" и определяем связь: поскольку один товар может присутствовать во многих накладных, таблицы "Товары" и "Отпуск товаров со склада" находятся в связи "один-ко-многим" (рис. 1.22.).

После анализа структуры таблицы "Отпуск товаров со склада" можно заметить, что значение поля "Покупатель" никоим образом не зависит от пары значении "Номер накладной", "Товар", а зависит только от значения поля "Номер накладной". Поэтому данное поле и зависящие от его значения поля "Город", "Адрес" выделяются в отдельную таблицу "Покупатели" (рис. 1 23.)

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

Установим связи между таблицами Один покупатель может встречаться во многих накладных. Поэтому между таблицами "Покупатели" и "Накладные" имеется связь "один-ко-многим" по полю "Покупатель". Одной накладной может соответствовать несколько товаров Поэтому между таблицами "Накладные" и "Отпуск товаров со склада" имеется связь "один-ко-многим" по полю "Номер накладной" (рис 1 25).

Для того чтобы уяснить, до конца нормализованы таблицы в составе разрабатываемой нами БД или нет, проанализируем ее структуру с позиций третьей нормальной формы (ЗНФ)

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

Продолжим рассмотрение примера. Можно увидеть, что в таблице "Отпуск товаров со склада" имеется зависимость значения поля "Общая стоимость" от значения поля "Количество". Значение поля "Общая стоимость" может вычисляться как значение поля "Количество", умноженное на значение поля "Цена за единицу измерения" из таблицы "Товары" (из записи с таким же значением поля "Товар"). Поэтому поле "Общая стоимость" из таблицы "Отпуск товаров со склада" удаляем В результате получаем нормализованную базу данных, структура которой приводится на рис. 1 26.

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

1.9. Нормализация - за и против

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

Однако у нормализованной БД есть и недостатки, прежде всего практического характера.

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

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

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

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

1.10. Понятие транзакций

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

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

Разберем пример. Рассмотренную выше нормализованную БД, содержащую сведения об отпуске товаров со склада, дополним двумя таблицами (рис. 1.27):

"Статистика по товару" - содержит сведения о суммарном отпуске каждого товара со склада, начиная с начала года;

• "Статистика по покупателю" - содержит сведения о суммарном отпуске

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

• добавление записи в таблицу "Отпуск товаров";

• отыскание записи по данному товару в таблице "Статистика по товару" и увеличение значения поля "Всего отпущено товара" на значение "Отпущено ед."; если запись по такому товару в таблице "Статистика по товару" отсутствует, она должна быть добавлена;

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

Рассмотрим случай отгрузки товара "Макароны" в количестве 100 кг по цене 3000 за кг покупателю "Продбаза № 4". Если в рамках транзакции произошел сбой по одной из операций, необходимо отменить результаты выполнения всех других операций, иначе информация в БД будет недостоверной.

Если произошел сбой на добавлении записи в таблицу "Отпуск товаров", выполнение других операций приведет к увеличению статистики в соответствующих таблицах по товару "Макароны" на 3000 кг и по покупателю "Продбаза № 4" на 300 000 руб, хотя в действительности сведения о такой отгрузке в таблице "Отпуск товаров" будут отсутствовать.

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

Если произошел сбой при записи в таблицу "Статистика по покупателю", а другие операции завершились успешно, данная таблица будет содержать недостоверные сведения о сумме отпуска товаров покупателю "Продбаза № 4"

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

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

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

1.11. Типы таблиц БД по виду их изменения -справочные, операционные и транзакционные

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

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

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

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

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

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

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

В рассматриваемом нами примере в качестве операционных выступают таблицы "Накладные" и "Отпуск товаров со склада". В них ежедневно добавляется информация о отпуске товаров со склада. Занесение данных в эти таблицы вызывает одновременные или периодические изменения в транзакционных таблицах "Статистика по товару" и "Статистика по покупателю".

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

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

В нашем примере в качестве транзакционных выступают таблицы "Статистика по товару'.' и "Статистика по покупателю". Информация в них формируется на основании данных в операционных таблицах "Накладные" и "Отпуск товаров со склада".

1.12. Типы информационных систем по виду накапливания итоговой информации -операционные и накопительные

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

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

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

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

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

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

- более понимаемой. Однако эти достоинства влекут за собой главный недостаток

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

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

варианту.

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

1.13. Навигационный и SQL-ориентированный подходы к операциям над данными

Существует два основных подхода к операциям над данными в ТБД.

Навигационный подход ориентирован на обработку каждой записи таблицы в отдельности. Этот подход используется в так называемых локальных (персональных, настольных) базах данных типа Paradox и dBase.

При SQL-ориентированном подходе происходит обработка групп записей (этот подход часто называют ориентированным на множества записей или на наборы данных). При этом могут обрабатываться записи нескольких таблиц БД. Такой подход используют так называемые "серверные" (промышленные, удаленные) базы данных - такие как Oracle, Sybase, Informix, InterBase и др. О различиях локальных и удаленных БД будет рассказано в следующих разделах.