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

Наконец, анализ сущностей Издания, Переплеты, Места, Читатели и Языки, показал, что единственной "подозрительной" сущностью является стержень Языки, имеющий два функционально связанных неключевых поля: Язык и Сокращение.

Поле Язык стало неключевым из-за ввода цифрового первичного ключа Код_языка, заменяющего текстовый возможный ключ Язык. Это позволило уменьшить объем хранимых данных в таблице Переводчики, затраты труда на ввод множества текстовых значений и возможной противоречивости, которая часто возникает из-за ввода в разные поля ошибочных дубликатов (например, "Английский", "Англиский", "Анлийский", "Англйский" и т.п.). Если мы вспомним рекомендации п. 4.5 о замене на время нормализации цифровыз заменителей первичных ключей (Код_языка) на исходный ключ (Язык) или воспользуемся формулировкой НФБК, то окажется, что таблица Языки - нормализована.

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

Глава 6. Программирование на Delphi - работа с базами данных (БД)

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

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

Рис. 6.1 Эволюция инструментов хранения и манипулирования информацией

Обычно такая информация накапливается в виде «твердых» бумажных копий. Хранение информации в таком виде обладает рядом крупных недостатков: невозможность редактирования, отсутствие защиты от утраты документов, неудобство поиска и манипулирования. По мере своего развития организация переходит в следующую стадию хранения информации - сохранение ее в виде электронных документов (текстовые документы, электронные таблицы и т. д.) в файловой системе операционной системы компьютера. Этот способ хранения существенно лучше предыдущего за счет возможности дублирования информации для защиты ее от потери, автоматизация поиска и т. д. Но данный способ обладает и рядом недостатков: отсутствие жестко заданной структурированности информации ведет к ее многократному дублированию, что в свою очередь создает основу для несогласованности данных (информация из разных источников об одном объекте может быть противоречивой); кроме того, затруднен поиск и манипулирование информацией. В случае накопления большого объема данных, например за 5 - 10 лет работы организации использование электронного хранения информации приводит только к ухудшению степени ее сохранности и полезности. Поэтому, на сегодняшний день, самым перспективным способом хранения информации является базы данных, где информация жестко структурирована, согласно бизнес - процессам организации, что позволяет однократное хранение данных об одном и том же объекте. Управляет базами данных специальное программное обеспечение, называемое системой управления базами данных (СУБД или DBMS по-английски). СУБД обычно имеет в своем арсенале мощные средства защиты информации от потери и несанкционированного доступа, обладает простым языком программирования, позволяющем строить сложные запросы на выборку данных и на их редактирование.

В настоящее время наиболее распространенными СУБД являются реляционные СУБД (РСУБД).

Реляционные БД

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

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

Идентификатор клиента (Id)	Наименование (Name)	Тип (Type)	Дата открытия (OpenDate)	Дата закрытия (CloseDate)	Контактный № телефона (PhoneNum)
1	ЕНУ им.Л.Н.Гумилева	бюджетные	01.03.1997		506558
5	ТОО «Алга»	хозрасчетные	15.12.1993
3	Ахметов И. И.	частные	05.02.1996	01.01.2000	111213
15	Жунусов К. Ф.	частные	03.03.1999		110511

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

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

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

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

1. Шумаков П. В. «Delphi 3 и создание приложений баз данных»

2. Дейт К. «Введение в базы данных»

3. www.citforum.ru

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

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

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

Еще раз вернемся к таблице Clients на рис. 3.2. Здесь в качестве первичного ключа можно использовать только идентификатор клиента - Id, т. к. значения других полей могут не быть уникальными. Например, для поля Name теоретически могут существовать два и более частных лиц с одинаковой фамилией и инициалами.

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

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

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

№ телефона и дата/время его установки клиенту

№ телефона и дата/время состоявшегося разговора

Id клиента, дата и сумма оплаты за переговоры

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

Отношения в БД

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

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

Отношение «один ко многим». Отношение такого типа имеет место, когда одной записи master - таблицы может соответствовать несколько записей detail - таблицы.

Идентификатор клиента (Id)	Наименование (Name)	Тип (Type)	Дата открытия (OpenDate)	Дата закрытия (CloseDate)	Контактный № телефона (PhoneNum)
1	ЕНУ им.Л.Н.Гумилева	бюджетные	01.03.1997		506558
5	ТОО «Алга»	хозрасчетные	15.12.1993
3	Ахметов И. И.	частные	05.02.1996	01.01.2000	111213
15	Жунусов К. Ф.	частные	03.03.1999		110511

Идентификатор клиента (ClientId)	№ телефона (PhoneNum)	Дата открытия (OpenDate)	Дата закрытия (CloseDate)
1	353806	01.03.200
1	353807	01.03.2000
1	353809	11.04.2000
5	333333	15.12.1997	01.01.2000
5	333334	15.12.1997	01.01.2000

Связь «один ко многим» между таблицами Clients и Phones

Как видно из одной записи родительской таблицы Clients может соответствовать несколько записей дочерней таблицы Phones. Обратите внимание, что могут существовать записи в master - таблице, для которых в данный момент нет записей в detail - таблице.

Связь «один ко многим» является самой распространенной для реляционных БД. Как можно заметить, она позволяет моделировать иерархии данных.

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

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

Идентификатор клиента (Id)	Наименование (Name)	Тип (Type)	Дата открытия (OpenDate)	Дата закрытия (CloseDate)	Контактный № телефона (PhoneNum)
1	ЕНУ им.Л.Н.Гумилева	бюджетные	01.03.1997		353806
5	ТОО «Алга»	хозрасчетные	15.12.1993
3	Ахметов И. И.	частные	05.02.1996	01.01.2000	111213
15	Жунусов К. Ф.	частные	03.03.1999		110511

Идентификатор клиента (ClientId)	РНН	ФИО первого руководителя
1	111111111111	Абдыманапов С.А.
3	232323232323
15	666666666666
5	333333	директор

Связь «один к одному» между таблицами Clients и OptionRecvs

Отношение «многие ко многим». Связь такого типа имеет место, когда:

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

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

Многие СУБД не поддерживают связи «многие ко многим» явным образом. Считается, что любая связь «многие ко многим» может быть преобразована к нескольким связям «один ко многим».

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

Рассмотрим наиболее часто встречающуюся в БД связь «один ко многим». Для этого вернемся к рис. 3.1.3. Как можно заметить, таблицы Clients и Phones связаны по общему полю идентификатора клиента - Id и ClientId соответственно. Назовем это поле - полем связи.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Существуют два метода доступа к записям таблицы: последовательный и индексно - последовательный.

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

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

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

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

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

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

Нормализация

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

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

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

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

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

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

На сегодняшний день такой формализацией процесса проектирования является понятие нормализации БД.

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

Существует несколько нормальных форм: 1НФ, 2НФ, 3НФ, нормальная форма Бойса - Кодда, 4НФ, 5НФ. Обычно, на практике, БД нормализуют только до 3НФ.

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

было атомарным (неделимым);

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

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

Повторяющимися являются поля, содержащие одинаковые по смыслу значения. Например, если необходимо получить статистику о количестве разговоров с каждого телефона за первые 3 месяца года, можно создать таблицу TraffStat, имеющую 4 поля: PhoneNum - № телефона (первичный ключ); Jan, Feb и Mar - поля с количеством разговоров за январь, февраль и март соответственно.

Однако, что делать, если потребуется статистика не за 3 месяца, а за 6 или 8? Конечно, можно определить столько полей, сколько месяцев. Но как быть, если количество месяцев заранее неизвестно или по одному телефону имеются разговоры только за два месяца в середине года, а по другому - за весь год. Реализовать структуру таблицы с переменным количеством полей в реляционных БД невозможно. Исходя из этого повторяющиеся группы полей необходимо устранить. В результате, мы получим таблицу с 3 полями: PhoneNum - № телефона; Month - № месяца в году; Calls - количество разговоров за месяц. Первичный ключ - комбинация полей PhoneNum и Month.

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

Имя поля	Описание
ClientName	Наименование звонившего клиента
ClientAddress	Адрес звонившего клиента
CallDate	Дата и время разговора
PhoneNum	№ телефона
DirectionId	Префикс и код страны/города, куда состоялся разговор
DirectionName	Наименование страны/города, куда состоялся разговор
Price	Цена минуты разговора по данному коду страны/города
Destination	Остаток номера, куда состоялся разговор
Duration	Продолжительность разговора в секундах
Cost	Стоимость разговора

Структура таблицы Traffic в 1НФ

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

Для приведения таблицы Traffic к 2НФ определим первичный ключ. Проведя смысловой анализ, можно утверждать, что первичным ключом для таблицы Traffic является комбинация полей PhoneNum и CallDate, т. к. не может быть двух разговоров с одного номера за одно и тоже время и дату. Но поля DirectionName и Price совершенно не зависят от данного ключа, а зависят от поля DirectionId. Поэтому выделим эти поля в отдельную таблицу Directions и создадим связь «один ко многим» от Directions к Traffic. Тоже самое можно сказать и о полях ClientNameи ClientAddress. Эти поля мы тоже выделим в отдельную таблицу Clients со связью по отношению к Traffic «один ко многим».

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

Traffic

PhoneNum
ClientId
CallDate
DirectionId
Destination
Duration
Cost

Directions

Id
Name
Price

Phones

PhoneNum
ClientId

Clients

Id
Name
Address

Структура БД, отвечающая требованиям 2НФ

Рассмотрим полученную структуру БД с точки зрения 3НФ.

Третья нормальная форма (3НФ) требует, чтобы в таблице не имелось транзистивных зависимостей между неключевыми полями, т. е. чтобы значение любого поля таблицы, не входящего в первичный ключ, не зависело от значения другого поля, также не входящего в первичный ключ. Исследуя структуру БД с этой позиции, можно заметить, что значение неключевого поля Cost в Traffic зависит от значения неключевого поля Duration. Кроме того, значения поля Cost можно всегда вычислить из значений полей Duration таблицы Traffic и Price таблицы Directions. Отсюда следует, что поле Cost может быть вообще убрано из таблицы Traffic, как избыточное.

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

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

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

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

Тогда, при добавлении нового клиента в БД необходимо, чтобы были последовательно выполнены 2 команды СУБД: первая добавляет запись о клиенте в Clients, вторая - запись о телефоне нового клиента в Phones. Если произойдет сбой при добавлении телефона, то БД окажется в рассогласованном состоянии, т. к. она не отвечает требованиям нашего бизнес - правила. Чтобы предотвратить появление таких ситуаций, мы должны выполнить обе команды добавления в рамках одной транзакции, чтобы гарантировать, что изменения БД этих команд будут либо зафиксированы СУБД, либо, в случае сбоя, отброшены.

Навигационный и декларативный подходы к операциям над данными

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

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

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

Лабораторная работа

Дано описание некоторой задачи. Необходимо разработать структуру БД для данной задачи и нормализовать ее до 3НФ.

1. Пункт обмена валют. Необходимо отслеживать курсы валют различных стран (максимально 99 видов). Курсы бывают 4-х видов: покупки , продажи, Национального Банка и биржевой. По некоторым видам валют курсы Нац. Банка и биржи могут отсутствовать. Курсы валют могут меняться несколько раз в день.

Реализовать приложение, позволяющее вести БД валют и получать следующие отчеты:

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

Курсы покупки на 03.07.2000

Код валюты (3 символа)	Наименование	Локальная валюта	Иностранная валюта
011	Доллар США	144.50	1.00
143	Итальянская лира	3.53	1000.00
…

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

011 Доллар США начальная дата 01.0702000

Дата	Нац. Банк	Биржа	Покупка	Продажа
02.07.2000	+0.50	-0.37	+0.00	+1.00
03.07.2000	+0.00	+0.30	+0.00	+0.00
…

2. Тарификация телефонных переговоров. Исходящий номер телефона (номер, куда звонили) состоит из 3 частей: префикса (1…5 символов), кода направления (1…8 символов) и остатка (1…20 символов). Префиксы могут содержать несколько кодов направлений. Существуют направления с одинаковыми кодами направлений, но разными префиксами, например, 8290 - сотовая связь «Алтел» и 810290 - остров «Святой Елены».

Для процесса тарификации комбинация префикс + код группируются в тарифные зоны. На каждую зону заводится тариф.

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

Id зоны	Наименование зоны	Префикс	Код	Наименование
1	Казахстан 1	8	3272	Алматы
2	Казахстан 2	8	300	Сотовая связь «K-Cell»
2	Казахстан 2	8	333	Сотовая связь «K-Mobile»
3	Международный 1	810	1	США

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

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

ЕНУ им. Л.Н. Гумилева

Зона	Цена за минуту
Казахстан 1	0.2
Междугородний 1	0.76
…

4. Библиотека. Имеются книги, авторы и тематики. Один автор может выпускать несколько книг; книга может принадлежать нескольким тематикам и тематика содержит несколько книг.

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

Тематика

Книга	Автор

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

Реализовать приложение, позволяющее вести БД сотрудников, отделов и заработных плат и получать следующие отчеты:

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

годовая заработная плата за выбранный год по выбранному сотруднику;

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

6. Складской учет. На складе имеется определенный перечень штучных товаров. Цены за 1 штуку товара могут со временем меняться. Товары со склада отпускаются по накладным, в которых указывается дата выписки и перечень товаров со стоимостью.

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

Накладная № …от 01.07.2000

Код товара	Наименование	Количество штук	Цена	Стоимость
…

6.2 Разработка БД

Постановка задачи

В данном разделе мы, использую структуру БД, спроектированную в предыдущих разделах, физически создадим данную БД, используя инструменты Delphi.

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

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

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

Создание псевдонима

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

Обращение к БД из утилит и приложений осуществляется по псевдониму (alias) БД. Такой псевдоним должен быть зарегистрирован в файле конфигурации конкретного компьютера при помощи утилиты BDE Administrator.

Пусть наша БД будет находится в каталоге C:\Program Files\Borland\Delphi5\Projects\Traffic\DB, а псевдоним создаваемой БД будет Traffic. Запустим утилиту BDE Administrator (меню кнопки «Пуск» «Программы»\ «Borland Delphi 5»\ «BDE Administrator»).

Для создания нового псевдонима выберем пункт меню «Object»\ «New». В появившемся диалоговом окне выберем тип драйвера «Standard», что соответствует локальной СУБД Paradox, с которой мы и будем в дальнейшем работать. Нажмем кнопку «ОК».

В левой части главного окна администратора БД изменим предлагаемое наименование псевдонима «STANDARD1» на «Traffic».

В правой части главного окна приведены параметры БД. Оставим их без изменения, изменив лишь PATH. Этот параметр указывает путь к каталогу, в котором будет расположена БД. Введем вручную или выберем с помощью кнопки «…» путь.

Для сохранения созданного псевдонима необходимо щелкнуть правой кнопкой мышки на созданном псевдониме в левой части главного окна и из появившегося меню выбрать пункт «Apply».

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

Создание структуры БД с помощью Database Desktop

Объявление полей. Для создание таблиц БД необходимо запустить утилиту Database Desktop (DBD). После запуска DBD установим рабочий псевдоним. Это псевдоним, с которым DBD работает по умолчанию. Это производится путем выбора пункта меню «File»\ «Working Directory», в открывшемся окне в списке Aliases выбрать псевдоним Traffic и нажать кнопку «ОК».

Для создания таблицы БД необходимо выбрать пункт меню «File»\ «New»\ «Table». В тип таблицы оставляем без изменения, нажимая кнопку «ОК». После этого появляется окно определения структуры таблицы. Каждая строка таблицы данного окна соотвествует одному полю создаваемой таблиы БД:

Fields Name - имя поля;

Type - тип поля;

Size - размер поля, используется только для спецификации длины строкового типа

Key - содержит символ «*», если поле входит в состав первичного ключа.

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

Тип поля	Обозначение	Допустимые значения
Alpha	A	Символьные значения длиной до 255 символов
Number	N	Числовые значения с плавающей точкой в диапазоне -10307…10308
Money	$	Аналогичен типу Number, но предназначен для хранения денежных сумм. Число знаков после запятой - 2. При отображении значения выводится знак валюты
Short	S	Целочисленные значения в диапазоне -32767…32767
LongInteger	I	Целочисленные значения в диапазоне -2147483648…2147483647
BCD	#	Числовые значения в двоично - десятичном формате
Date	D	Значения даты в диапазоне 01.01.9999 до н. э. …31.12.9999
Time	T	Значения времени
Timestamp	@	Значения даты и времени
Memo	M	Строковые значения длиной более 255 символов. Хранятся в отдельных MB - файлах
Formatted Memo	F	Аналогичен Memo, но позволяет хранить тексты в формате rtf
Graphic Fields	G	Графические изображения в форматах bmp, pcx, tif, eps. Хранятся в отдельных файлах в формате bmp
OLE	O	Хранятся OLE - объекты
Logical	L	Логические значения True и False
Autoincrement	±	Автоинкрементное поле. При добавлении записей в таблицу данное поле увеличивает автоматически значение на единицу для новой записи
Binary	B	Произвольные двоичные значения. Хранятся в отдельных MB - файлах
Bytes	Y	Произвольные двоичные значения длиной от 1 до 240 байт.

Описание типов СУБД Paradox

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

Required Field - в установленном состоянии означает , что поле обязательно к заполнению;

Minimum Value, Maximum Value - определяют минимальное/максимальное значение поля;

Default Value - определяет значение поля по умолчанию;

Picture - определяет шаблон отображения поля. Для формирования шаблона необходимо нажать кнопку «Assist».

Чтобы на диске был создан файл с пустой таблицей, необходимо нажать кнопку «Save As…» и указать имя таблицы - Traffic. При этом будет создан файл Traffic.db в папке, указанной в псевдониме Traffic.

Повторим описанный процесс для всех таблиц БД. На диске должно быть создано 4 файла: Traffic.db, Directions.db, Clients.db, Phones.db.

Определение дополнительных индексов. Создадим индекс по полю Name для таблицы Clients. Для этого необходимо ее открыть. Это производится путем выбора пункта меню «File»\ «Open»\ «Table». В открывшемся окне выбирается файл Clients.db. Будет показано содержимое таблицы. В нашем случае таблица не имеет ни одной записи. Для перехода в режим изменения структуры таблицы выберите пункт меню «Table»\ «Restructure».

Выберем в списке Table Properties элемент Secondary Indexes. Чтобы определить новый индекс, необходимо нажать кнопку «Define». В появившемся окне в качестве индекса определим поле Name, перенеся его в список Indexed Fields с помощью кнопки со стрелкой вправо, установим флажок «Unique», указывая, что индекс должен быть уникальным, и нажмем кнопку «ОК». В появившемся окне укажем имя индекса - IndName. После этого имя индекса появляется в списке.

При необходимости можно изменить определение индекса, выбрав его в списке и нажав кнопку «Modify». Для удаления индекса используется кнопка «Erase».

Определение ссылочной целостности в БД. На этапе проектирования структуры БД мы определили несколько связей «один ко многим», например между таблицами Directions и Traffic. Создадим эту связь, используя DBD. Ссылочная целостность в СУБД Paradox определяет, во-первых, связь между таблицами, а, во-вторых, вид каскадных воздействий.

Откроем таблицу Traffic, в списке Table Properties выберем элемент Referential Integrity и нажмем кнопку «Define». В появившемся окне в списке Fields показаны поля таблицы Traffic, а в списке Tables - таблицы БД.

Выберем в списке Fields поле DirectionId и перенесем его в поле Child Fields с помощью кнопки со стрелкой вправо. Выберем в списке Tables таблицу Directions и нажмем кнопку со стрелкой влево. При этом в поле Parent Key будет отображаться первичный ключ этой таблицы.

Флажки группы Update Rules определяют вид каскадных воздействий на таблицу Traffic при изменении значения поля связи таблицы Directions:

Cascade - каскадные изменения и удаления

Prohibit - запрет каскадных воздействий.

В нашем случае выберем Prohibit и нажмем кнопку «ОК». На запрос имени связи укажем RIDirectinId.

Подобным образом мы определяем все связи в нашей БД.

Лабораторная работа

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

6.3 Средства Delphi для разработки приложений БД

Обзор средств для работы с БД.

В состав Delphi Client/Server входят следующие средства для разработки и эксплуатации приложений БД:

Borland Database Engine (BDE) - представляет собой набор библиотек, выполняющих действия по доступу к данным и проверке их корректности. Должно устанавливаться на каждом компьютере, на который выполняются приложения, работающие с БД. Является центральным средством для работы с БД из приложений, созданных с помощью Delphi;

SQL Links - драйвера для работы с СУБД архитектуры «клиент - сервер» таких, как Oracle, Sybase и т. д. Доступ к локальным СУБД, типа Paradox и dBase, осуществляется BDE напрямую без использования SQL Links;

BDE Administrator - утилита для создания и управления псевдонимами БД. При работе приложения доступ к БД осуществляется по ее псевдониму. Параметры БД, определяемой псевдонимом, действуют только для этой БД. Параметры, установленные для драйвера СУБД, действуют для всех псевдонимов, использующих этот драйвер. Кроме того, в утилите можно произвести установку таких общих для всех драйверов параметров, как формат даты и времени, языковой драйвер и т. д. Вся информация сохраняется в файле idapi32.cfg;

Database Desktop (DBD) - средство создания и изменения структур БД локальных СУБД;

SQL Explorer - утилита конфигурирования псевдонимов БД, просмотра структуры БД, содержимого таблиц, формирования SQL - запросов к БД;

Data Module - невизуальный компонент TDataModule, предназначенный для централизованного хранения наборов данных в приложении, использующего БД;

невизуальные компоненты для работы с БД - располагаются на вкладке Data Access палитры компонентов Delphi. Служат для создания и поддержки во время исполнения приложения связи с БД;

визуальные компоненты для работы с БД - предназначены для визуализации записей и отдельных полей наборов данных. Располагаются на вкладке Data Controls палитры компонентов Delphi;

компоненты для построения отчетов - предназначены для создания, предосмотра и печати наборов данных приложения.



Рис. 6.2 Общая модель взаимодействия приложений и средств разработки и эксплуатации БД

Архитектуры БД

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

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

В архитектуре СУБД «файл - сервер» вся тяжесть обработки данных и поддержание согласованности данных в БД ложится на приложение. БД на сервере является только пассивным источником данных.

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

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

Многозвенная архитектура «клиент - сервер». Развитие идеи архитектуры «клиент - сервер» привело к появлению, так называемых, многозвенных архитектур.

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

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

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

6.4 Компоненты для работы с БД

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

Обзор невизуальных компонентов

Компонент	Назначение
Tsession (сессия соединения)	Содержит информацию о текущем сеансе работы с БД. Позволяет определить список доступных БД и список активных БД, открывать и закрывать БД, управлять параметрами.
TdataBase (база данных)	Активно используется при работе в архитектуре «клиент - сервер». Позволяет осуществлять соединение с сервером БД и управлять параметрами соединения, получать информацию о БД, получать информацию об открытых наборах данных (НД) и о доступных таблицах БД.
TdataSource (источник данных)	Служит промежуточным звеном в цепочке «НД - источник данных - визуальные компоненты для работы с данными». Позволяет устанавливать некоторые параметры НД, устанавливать состояние НД, отслеживать изменения в НД
TdataSet TBDEDataSet TDBDataSet	Явно в приложения не используются, однако являются предками активно используемых в приложениях компонентов типа НД (TTable, TQuery, TStoredProc). Определяют ряд свойств и методов, наследуемых и частично переопределяемых в НД.
TTable (таблица)	Реализует НД, источником данных для которого является одна таблица БД. Содержит множество методов, свойств и событий, посредством которых можно выполнять над НД широкий спектр операций. Наследуется от компонента TDataSet.
TQuery (SQL - команда)	Реализует НД, источником данных для которого является одна или несколько таблиц БД. Структура записи НД определяется SQL -запросом (select). Кроме того, служит для выполнения любых SQL - операторов. Наследуется от компонента TDataSet.
TStoredProc (сохраняемая процедура)	Используется в архитектуре «клиент - сервер» для доступа к хранимым процедурам, расположенным на сервере БД. Данный компонент является также НД, т. к. может возвращать множество записей.
TField (поле таблицы)	Реализует поле таблицы БД. Помимо полей, физически определенных в таблицах БД, может создаваться для вычисляемых полей. Предоставляет набор свойств и методов, позволяющих управлять поведением поля. Является родительским классом для компонентов, реализующих поля конкретных типов: TStringField, TIntegerField и т. д.
TBatchMove	Позволяет осуществлять копирование или перенос записей из одного НД в другой.

Обзор визуальных компонентов

Компонент	Назначение
TDBText	Показывает в режиме «только для чтения» (read only) значение поля текущей записи НД.
TDBEdit	Обеспечивает просмотр и изменение значения поля текущей записи НД. Поля - любого типа, кроме TMemoField и TBLOBField.
TDBCheckBox	Обеспечивает просмотр и изменение значения для поля типа Boolean текущей записи НД.
TDBRadioButton	Обеспечивает возможность выбора значения для поля, которое может содержать фиксированное число вариантов значений.
TDBMemo	Позволяет просматривать и корректировать содержимое мемо - поля в режиме текстового редактора.
TDBImage	Позволяет просматривать графические поля.
TDBListBox	Подобен компоненту TListBox, только элементами списка служат значения поля записей НД.
TDBComboBox	Подобен компоненту TComboBox, только элементами списка служат значения поля записей НД.
TDBGrid	Отображает НД в табличном виде, когда записям соответствуют строки TDBGrid, а полям - столбцы.
TColumn	Столбец компонента TDBGrid, позволяющий управлять его свойствами и поведением.
TQuickReport	Компонент TQuickReport и связанная с ним группа компонентов позволяет разрабатывать формы отчетов.

6.5 Создание приложения «Учет трафика»

Описание приложения (техническое задание)

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

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

Приложение «Учет трафика» реализуется для автоматизации работ по учету трафика переговоров телефонной компании и создается с использованием мультидокументного интерфейса (MDI - приложение). Главное окно приложения включает в себя систему меню и панель инструментов для ускорения доступа к основным операциям приложения.

Работа Действия Отчеты

Клиенты и телефоны Добавить Отчет по трафику

Направления Изменить

Трафик Печать

Выход Удалить

Система меню приложения

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

Приложение будет состоять из одной главной формы приложения и 3 дочерних. Дочерние окна служат для отображения и манипулирования данными таблиц: Clients и Phones; Directions; Traffic.

Подготовка каталога и создание проекта

Для разработки нового проекта необходимо определить рабочий каталог, в котором будут располагаться все элементы проекта. Мы уже создали такой каталог, когда создавали нашу БД. Путь к нему - ..\Projects\Traffic. Скопируем в сюда каталог с изображениями для кнопок панели инструментов. После этого коллекция изображений будет находиться в ..\Projects\Traffic\Images.

В Delphi создадим новый проект с именем файла Traffic.dpr и файлом главной формы приложения uMain.pas.

В свойствах проекта присвоим заголовку приложения строку «Учет трафика».

Создание главного окна приложения

Установим цвет рабочей области формы. Для этого свойству формы Color присвоим значение clAppWorkSpace. Кроме того, т. к. главная форма является также главной формой MDI - приложения, установим свойство FormStyle в значение fsMDIForm. Изменим также заголовок формы.

Как отмечалось в ТЗ, приложение должно иметь систему ме...