Проектирование базы данных

 

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

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

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

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

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

 

Дата	Наименование товара	Покупатель	Отпущено, шт.
10.12.2006	Паровой котел Booster	ООО «Водяной»	2
10.01.2007	Насосы Wilo	ООО «Водяной»	10
12.04.2007	Горелки Olimpia	ООО «Трионика»	5

Рис.8. Таблица товар

 

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

 

Покупатель	Адрес	Телефон
ООО «Водяной»	г. Екатеринбург, ул. Ленина 28	8 (343) 210-10-10
ООО «Трионика»	г. Екатеринбург, ул. Гагарина 10	8 (343) 220-20-20

Рис.9. Таблица покупатели

 

Базы данных, между отдельными таблицами, в которых существуют связи, называются реляционными (от relation – связь, отношение).

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

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

Первичные ключи облегчают установление связи между таблицами. В таблице 3 таким ключом может быть одноименное поле. Установив связь по первичному ключу, мы можем выяснить, что, например 10.12.2001 года со склада было отпущено 50 единиц «Паровых котлов BOOSTER CO LTD» покупателю ООО «Водяной», который расположен по адресу: г. Екатеринбург, ул. ул. Гагарина 10, контактный телефон 8 (343) 220-20-20, E-mail:. Vodyanoy @.ru.

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

 

№	Покупатель	Адрес	Телефон
1	ООО «Водяной»	г. Екатеринбург, ул. Лени на 28	8 (343) 210-10-10
2	ООО «Трионика»	г. Екатеринбург, ул. Гагарина 10	8 (343) 220-20-20

Рис.10. Модифицированная таблица покупателей

 

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

 

Дата	Наименование товара	Покупатель	Отпущено, шт.
10.12.2006	Паровой котел Booster	1	2
10.01.2007	Насосы Wilo	1	10
12.04.2007	Горелки Olimpia	1	5

Рис.11. Таблица отпуска товара

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

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

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

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

 

Таблица 2

Фирмы

Имя поля	Назначение
Firm ID	Первичный ключ
FName	ООО «Трионика»
FAdress	Ул. Ленина 28
FCity	Екатеринбург
FPhone	Тел: 8 (343) 210-10-10
FE-mail	Trionika.@.ru
FPerson	Иванов Иван Иванович
FFinDelta	Финальное сальдо
FCngDelta	Обменное сальдо
FCoeff	Скидка 10%
FRetDays	60 дней

 

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

 

Таблица 3

Товар

Имя поля	Назначение
TovarID	Первичный ключ
TName	Паровой котел
TMade	Booster Co LTD
TYear	2006
TNumber	5
TQuan	5
TPrise	70 000
TOpt	77 000
TRozn	85 000

 

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

 

Таблица 4

Накладные

Имя поля	Назначение
NaklID	Первичный ключ
NDate	10.01.2007
NRetDate	2 месяца
NType	Тип накладной: 0- покупка у поставщика; 1- продажа покупателю; 2- возврат поставщику; 3- возврат от покупателя; 4- товар, получаемый по обмену; 5- товар передается по обмену; 6- покупка с предоплатой; 7- продажа с предоплатой.
NFirm	Уникальный код партнера
NCoeff	Скидка/наценка (10%)
NSum	Сумма накладной (100 000)
NPayedSum	Оплачиваемая сумма (50 000)
NRetSum	Сумма возврата (0)

 

Возникает вопрос: каким образом система управления базами данных будет хранить сведенья о получаемого или передаваемого по накладной товара? Ведь их состав и количество могут быть какими угодно: если осуществляется покупка оборудования у поставщика, в накладной обычно указывается лишь одно – два наименования, которые покупаются крупными партиями; если оборудование покупает магазин, он может закупить до сотни наименований не большими партиями; не исключена и розничная продажа одного единственного наименования товара покупателю. Ясно, что в таблице NAKLS, как и в любой другой таблице базы данных, не может быть переменного количества полей, поэтому сведения о связанном с накладн6ой товаре будут храниться в отдельной таблице MOVETOVAR: в ее полях укажем ссылку на соответствующую накладную, наименование и количество переданного – полученного оборудования.

Таблица5

Передвижение товара

Имя поля	Назначение
MoveID	Первичный ключ
MNakl	Код накладной
MTovar	Код товара
MQuan	5
MPrise	77 000 т.р.

 

Наконец, в таблицу PAYMENTS поместим сведения о платежах: кто, кому и сколько платит.

 

Таблица 6

Сведения о платежах

Имя поля	Назначение
PayID	Первичный ключ
PFirm	Код партнера
POut	Направление платежа True – партнеру False – от партнера
PDate	10.01.2007
PSum	77 000 т.р.

 

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

Перед созданием базы данных, на компьютере должен быть развернут и запущен сервер InterBase версии 6.0 или 7.0.

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

Вместе с тем у сервера InterBase есть серьезный недостаток. Он не оптимизирует страницы дисковой памяти, выделяемые для размещения записей, а по завершении транзакций не удаляет ее, а лишь помечает как удаленную. С течением времени (по умолчанию – через каждые 10000 транзакций) сервер автоматически убирает накопившийся «мусор» и перестраивает индексы, но до этого времени «дырчатая» структура памяти и не перестроенные индексы могут сильно снижать производительность сервера при работе с «грязной» базой данных, в особенности, если в ней используются громоздкие таблицы, содержащие сотни тысяч и миллионы записей. Мне известен реальный промышленный проект, в котором этот недостаток после нескольких лет эксплуатации системы управления базой данных послужил причиной ее радикальной перестройки с заменой сервера.

В Delphi 6 и 7 включены компоненты, позволяющие осуществлять программное администрирование сервера InterBase версии 6.0 и выше. В том числе – осуществлять резервное копирование базы данных и ее восстановление из копии. Процедура архивации / восстановления самым благоприятным образом сказывается на производительности базы данных, так как удаляет «дырки» и перестраивает индексы. До недавнего времени эта операция, которую рекомендуется проводить хотя бы еженедельно (желательно каждый день), выполнялась опытным сотрудником (администратором сервера) с помощью специальных утилит. Если система управления баз данных сделана на заказ для небольших фирм, руководство таких фирм обычно не может позволить содержать в штате администратора сервера, так что периодичность процедуры «чистки» базы данных в этом случае растягивается на многие недели и месяцы, а производительность сервера при работе с ней постепенно становится удручающе низкой.

Программа IBConsole предназначена для управления сервером InterBase и является его консолью. Консоль устанавливается совместно с сервером InterBase, и находиться в его каталоге BIN, его главный файл называется IBConsole.exe. Программу так же можно запустить через меню Пуск Windows, выбрав команду программы \ InterBase\ IBConsole.

Для запуска и остановки сервера служит программа InterBase Server Manager, функциональность которой начинается с версии Delphi 6, значительно уменьшилась.

Программа IBConsole обеспечивает:

· Управление локальными и удаленными серверами;

· Управление базами данных;

· Интерактивное управление SQL запросов.

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

Управление сервером заключается:

· Регистрации сервера;

· Подключении сервера;

· Просмотре протокола работы;

· Управлении сертификатами;

· Определении пользователей.

Для управления сервером используются команды меню Server главного окна программы IBConsole, а также команды контекстного меню сервера и его элементов.

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

Подключение к серверу, выбранному в левой панели, выполняется командой Login. При этом появляется окно Server Login, в котором необходимо указать имя пользователя (User Name) и его пароль (Password). После указания имени SYSBDA системного администратора, его пароля master key и нажатие кнопки Login осуществляется подключение к серверу, имя (alias) которого отображается в надписи Server, а к значку сервера добавляется зеленая галочка.

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

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

Кнопка Test инициирует проверку, результаты которой выводятся в поле Results.

На страницах TCP/ IP, NetBEUI, SPX можно выполнить настройку соответствующего сетевого протокола.

Для регистрации в консоли нового сервера необходимо выполнить команду Register, после чего открывается окно регистрации и соединение с сервером Register Server and Connect.

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

В случае регистрации удаленного сервера устанавливается переключатель Remote Server, и заполняются поля, которые заблокированы при подключении локального сервера: в поле Server Name указывается сетевое имя сервера, а списке Network Protocol выбирается протокол связи, а поле Alias Name задается имя (псевдоним), под которым сервер регистрируется консоли.

Одновременно с регистрацией можно выполнить подключении сервера, для чего должны быть заполнены поля User Name и Password группы Login Information.

Отмена регистрации выбранного сервера выполняется командой Un-Register. При этом выдается запрос на продолжение операции, и в случае подтверждения сервер исключается из консоли. Перед отменой регистрации сервера его необходимо отключить. Для управлением базами данных используются команды меню Database главного окна программы IBConsole, а также команды контекстного меню баз данных и их элементов.

Регистрация базы данных начинается командой Register, которая открывает окно Register Database and Connect. В этом окне необходимо указать (выбрать) главный файл базы данных (File), а также псевдоним (имя) базы данных (Alias), под которым она будет зарегистрирована на сервере, обозначенным надписью Server. По умолчанию в качестве псевдонима базе данных предлагается имя ее главного файла с расширением. Остальные данные не являются обязательными. Однако если задать имя и пароль пользователя, то после регистрации выполняется подключение указанной базы данных.

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

Подключение базы данных к серверу и отключение от него выполняется командами Connect и Disconnect соответственно. Команда Connect As позволяет подключиться к базе данных с новыми параметрами, указываемыми в окне Database Connect.

Консоль позволяет достаточно удобно и быстро создать базу данных, в том числе многофайловые. Создание базы данных, а также ее удаление, для которых предназначены команды Create Database и Drop Database.

Метаданные представляют собой элементы структуры базы данных. Для выбранной базы данных их можно просмотреть с помощью команды View Metadata, которая открывает окно Database Metadata. Метаданные представляют собой сценарий (скрипт), написанный на языке SQL, который можно распечатать или сохранить в текстовом файле. Сохраненный сценарий впоследствии можно выполнить, создав базу данных со всей ее структурой и данными.

В процессе интенсивной многопользовательской работы в базе данных накапливается так называемый «мусор», под которым понимают старые версии записей, которые могут образовываться при одновременном доступе к записям нескольких транзакций. Наличие мусора увеличивает размер и фрагментацию базы данных, поэтому базу данных надо периодически чистить – «удалять мусор» (удаление мусора также называют «сбором мусора»). Удаление мусора можно выполнять в ручном или автоматическом режимах. В ручном режиме удаление мусора начинается командой Maintenance\ Sweep (Обслуживание \ Чистка). Во втором режиме удаление мусора начинается автоматически, когда общее число примененных к базам данных транзакций достигает предельного значения. По умолчанию это значение установлено равным 20 000, его можно изменить в поле Sweep Interval (интервал чистки) окна свойств баз данных (Database Properties).

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

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

База данных должна находиться в целостном и непротиворечивом состоянии, т.е. содержать правильные данные. Для проверки состояния базы данных нужно выбрать команду Maintenance\Validation, которая открывает диалоговое окно проверки базы данных Database Validation.

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

· Validate Record Fragments (проверка структуры базы данных и структуры страниц);

· Read Only Validation (в процессе проверки допускается только читать, но не изменять записи);

· Ignore Checksum Errors (ошибки контрольных сумм игнорируются).

По умолчанию все параметры имеют значения False, т.е. включены. После нажатия кнопки ОК выполняется проверка, о результатах которой выдается соответствующий отчет в окне Validation Report.

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

Для вывода статистики нужно выбрать команду Maintenance\ Database Statistics, которая открывает диалоговое окно Database Statistics.

Выводимые в окне сведения о базе данных сгруппированы по секциям:

· Database (имя базы данных)

· Database header page information (страница заголовка базы данных):

· Flags (флаги);

· Checksum (контрольная сумма);

· Generation (счетчик обновлений заголовка);

· Page size (размер страницы);

· ODS version (версия формата файла базы данных);

· Oldest transaction (номер самой старой незавершенной (активной, отмененной или сбойной) транзакции);

· Oldest active (номер самой старой активной транзакции);

· Next transaction (номер, который будет назначен следующей транзакции);

· Sequence number (номер первой станицы);

· Next attachment ID (номер, который будет назначен следующему соединению);

· Implementation ID (идентификатор операционной системы, в которой создана база данных);

· Shadow count (число теневых файлов, определенных для базы данных);

· Page buffers (номер страницы в КЭШе базы данных);

· Next header page (номер, который будет назначен следующей страницы заголовка);

· Creation date (дата создания базы данных);

· Attributes (атрибуты базы данных);

· Database file sequence (список файлов базы данных):

· File (имя файла);

· Database log page information (страница журнала базы данных).

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

Для создания резервной копии базы данных нужно выполнить команду Maintenance\ Backup-Restore\ Backup.

В списке Alias группы Database выбирается имя сохраняемой базы данных, а элементы группы Backup File(s) определяют файл результата – сохраненной копии базы данных. В комбинированных списках Server и Alias выбирается или вводится соответственно имя сервера и базы данных для сохраняемой базы данных, а в поле Filename(s) вводится полное имя файла копии базы данных.