- 45 -

Кафедра `Персональные компьютеры и сети'

Базы данных

Учебно-методическое пособие по выполнению лабораторных работ

В.М. Баканов, А.С. Вахрушев

Москва 2005

Введение

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

Ниже рассматривается практика создания баз данных с использованием систем быстрой разработки приложений (RAD - Rapid Application Design) Delphi и C++Builder фирмы Borland Int. Системы отличаются в основном базовым языком программирования, элементарные `кирпичики' (компоненты) обоих систем практически одинаковы. Обычно более поздние версии этих систем обладают расширенным набором компонентов при сохранении традиционных, при этом обеспечивается совместимость сверху вниз. В начале описания каждой работы приводится информация о соответствующих теоретических предпосылках, в конце - вопросы для самопроверки.

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

Необходимое для проведения работ оборудование - IBM PC-совместимая ЭВМ, предустановленная ОС Windows версий не ниже 2000, пакеты Delphi или C++Builder версий не ниже 4.0; инсталляция пакета BDE (Borland Database Engine) обязательна.

Лабораторная работа №1. Создание и модификация таблиц базы данных c помощью утилиты Database Desktop

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

Теоретическая часть. В состав пакетов Delphi и C++Builder входит утилита DataBase Desktop, назначением которой является создание и модификация таблиц БД большинства поддерживаемых форматов [1,4]. Заметим, что при использовании RunTime-режима работы таблицы создаются, модифицируются и уничтожаются с помощью (интерактивно вводимых администратором или динамически генерируемых системой) SQL-предписаний [3].

DataBaseDesktop может быть инициализирована из главного меню (последовательность Tools|DatabaseDesktop) или выбором соответствующего варианта стартового меню Windows или прямым стартом исполняемого файла (типичный путь./Database Desktop/dbd32.exe); в последних версиях Delphi и C++Builder используется Database Desktop ver.7.0.

Внешний вид главного окна утилиты показан на рис. 1.

Рисунок 1. - Главное окно утилиты Database Desktop

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

Вариант File|New позволяет создавать, а File|Open - открывать существующие объекты БД - таблицы, файлы с текстами SQL-запросов (*.SQL - файлы) или файлы `запросов по образцу' QBE (*.QBE - файлы). По умолчанию место расположения этих файлов - каталог с (предопределенным) алиасом (псевдонимом) WORK (изменяется File|Working Directory). При открытии файлов таблиц БД поиск по умолчанию ведется в каталоге WORK (возможен выбор иных алиасов из выпадающего списка Alias в окне Open Table выбора файлов).

Для перевода выбранной таблицы в режим редактирования используются кнопка EditData (рис. 1) или клавиша F9.

Информацию о структуре выбранной таблицы можно получить в окне Structure Information (активизируется Table|Info Structure для выбранной таблицы или Tools|Utilities|Info Structure… для выбора файла таблицы), см. рис. 2. В строке Field Name указывается имя данного поля, в строке Type - тип поля, Size - размер поля (байты), Dec - число десятичных знаков (для числовых полей). В правой части окна помещается информация о (необязательной) индексации таблицы и имя (имена) индексных полей. Вариант Table Language (выбор из выпадающего списка Table properties) с последующим нажатием кнопки Detail Indexes… дает возможность уточнить набор используемых при кодировке таблицы символов. В этом режиме изменить структуру таблицы БД нельзя, для сохранения файла таблицы в иное место (с иным именем) служит клавиша Save As…



Рисунок 2. - Окно выдачи информации о структуре таблицы

Рисунок 3. - Окно изменения структуры таблицы

Для изменения структуры таблицы следует использовать окно Restructure (Table|Restructure для выбранной таблицы или Tools|Utilities|Restructure… для выбора файла таблицы), см. рис. 3.

В отличие от окна Info Structure здесь возможно (прямым редактированием) изменять названия, тип и размер каждого поля, а также добавлять (клавиша Ins) и / или уничтожать (клавиша Del) существующие поля. Кнопка Define инициализирует процедуру изменения индексирования таблицы (изменение входящих в индекс полей, требование уникальности индекса, сортировку по возрастанию или убыванию). Вариант Table Language (выбор из выпадающего списка Table properties) с последующим нажатием кнопки Modify… дает возможность выбрать набор используемых при кодировке таблицы символов. Установка флажка Pack Table дает возможность упаковать (удалить ранее помеченные для уничтожения записи) таблицу при сохранении. Установка Record lock позволяет реализовывать (при дальнейшей работе) режим блокировки записей dBASE-таблицы (длина дополнительного поля - 824 байт).

Важным этапом работы с БД является назначение соответствий алиасов (псевдонимов) имен таблиц и фактических путей в файловой системе. Для выполнения этой функции служит Alias Manаger (рис. 4), активизируемый посредством Tools|Alias Manager… С помощью Alias Manager возможно назначить новые соответствия алиасов и путей в файловой системе, добавить или удалить существующие алиасы (кнопки New и Remove соответственно).

Рисунок 4. - Окно настройки алиасов

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

Рисунок 5. - Окно работы с функциями изменения БД

Создание таблиц является этапом, следующим за логическим конструированием структуры БД и собственно таблиц [2]. С помощью Database Desktop определяется лишь логическая структура таблицы (параметры полей таблицы и информация об индексировании), записи же в таблицу необходимо вносить позднее. Для определения таблицы используется последовательность действий File|New|Table…, после чего необходимо выбрать формат файла таблицы (поддерживается большинство распространенных форматов, см. рис. 6).



Рисунок 6. - Выбор формата создаваемой таблицы

В дальнейшем в (подобном используемом в режимах Info Structure и Restructure) окне (рис. 7) предлагается вводить имена полей (столбец Field Name), их тип (возможен выбор из инициализируемого нажатием клавиши Space или двойным щелчком правой клавиши мыши выпадающего списка), размер поля и число цифр (для числовых полей).

Рисунок 7. - Выбор типа поля при создании структуры таблицы

Практически не рекомендуется использовать в названиях полей отличных от латиницы кодировок и ключевых слов из словаря клауз языка SQL. Варианты выпадающего меню Table Properties позволяют определить индексацию таблицы (допустимы составные индексы) и кодовую страницу содержания записей данной таблицы. Имеется возможность `заимствования' списков полей из других таблиц - для этого служит диалоговое окно Borrow Table Structure, инициализируемое кнопкой Borrow в диалоге Create Table (рис. 7, перед этим текущий список полей должен быть пуст).

В случае работы с защищенными паролем таблицами следует использовать вариант Tools|Passwords… для дополнения (или уничтожения) паролей. Paradox-таблицы поддерживают пароли и режимы доступа отдельно для каждого поля.

Для таблиц формата Paradox возможно задать дополнительные характеристики. Установка режима проверки правильности вводимых данных позволяет установить проверку корректности данных на уровне поля; для этого в окне Create Paradox Table после фиксации нужного поля выбирается вариант Validaty Check из выпадающего списка Table Properties (аналог см. на рис. 7). Флажок Required Field устанавливается в случае необходимости обязательного заполнения поля, допустимый диапазон вводимых в текущее поле данных устанавливается в окнах Minimize и Maximize, значение по умолчанию - Default, шаблон вводимых данных - в окне Picture (помощь в определении шаблонов выдается по кнопке Assist…).

Paradox-таблицы также поддерживают возможность определения списка допустимых значений поля (список содержится в другой таблице); для реализации этой возможности следует использовать окно Table Lookup (инициализируется выбором варианта Table Lookup из выпадающего списка Table Properties и нажатием кнопки Define); нужное для заполнения поле выбирается из расположенного в левой части окна Table Lookup списка Fields, список Lookup Table содержащих нужные значения таблиц содержится в правой части окна (из Lookup-таблицы выбирается первое поле, которое должно иметь соответствующее выбранному в Fields тип).

Задать вторичные индексы (используются для ускорения поисков в таблице и изменения порядка просмотра записей) Paradox-таблицы возможно посредством выбора варианта Secondary Index из выпадающего списка Table Properties; нажатием кнопки Define определяется новый индекс, Modify - изменяется существующий. В активизируемой таблице Define Secondary Index выбранные для индексации поле (поля) выбираются из слева расположенного списка Fields в список Indexed Fields (справа, возможно изменение порядка расположения полей). C целью (реального) обновления индекса следует включить флажок Mantained, включение Case Sensitive приводит к учету регистра при сортировке по индексу. Одиночный (включающий единственное поле) вторичный индекс будет иметь имя, совпадающее с именем таблицы; в противном случае имя индекса придется задать явно.

Таблицы Paradox поддерживают (усиливающее связи между таблицами) свойство ссылочной целостности, гарантирующее невозможность удаления информации в одной из связанных таблиц, если эти данные связаны с (существующей) записью (записями) в другой таблицы. Для реализации ссылочной целостности следует выбрать Referential Integrity из выпадающего списка Table Properties; нажатие кнопки Define определяют новые настройки, Modify - изменяет существующие. В правой части открывающегося окна Referential Integrity содержится список Table родительских таблиц, в Parent's Key при этом отображаются составляющие первичный ключ поля выбранной родительской таблицы; в левой части содержится список Fields полей текущей таблицы, с одним из которых следует связать (однотипное) поле родительской таблицы (не следует задавать связанное поле для каждого поля в родительской таблице). Следует выбрать Cascade в разделе Update Rule при желании, чтобы изменения в связанных полях родительской таблицы вызывали автоматическое изменение связанных полей в дочерней таблице; выбрать Prohibit при нежелании разрешать редакцию связанных полей в родительской таблице при наличии относящихся к ним полей в дочерней таблице и выбрать Strict Referential Integrity, чтобы предотвратить разрушение правил ссылочной целостности ранними версиями Paradox. При нажатии ОК будет запрошено название для установок ссылочной целостности. Имеющиеся связи ссылочного типа между родительской и дочерними таблицами могут быть просмотрены в правой части окна Restructure Paradox Tables, для этого следует выбрать вариант Dependent Tables из выпадающего списка Table Properties (аналог рис. 3).

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

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

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

Лабораторная работа №2. Создание простейшей реляционной базы данных

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

Теоретическая часть. При создании баз данных с помощью пакетов Delphi или C++Builder используются стандартные компоненты VCL (визуальной библиотеки компонентов, рис. 8).

Здесь Data - устройство хранения данных (обычно жесткий диск), BDE (Borland Database Engine, альтернативное наименование IDAPI - Integrated Database Application Program Interface) - технология доступа к БД фирмы Borland (фактически набор функций обслуживания БД), Data Access Components - (невизуализируемые) компоненты (закладка DataAccess палитры компонентов Delphi / C++Builder) доступа к БД, Data Control Components - (визуализируемые) пользовательские компоненты (закладка DataControl палитры компонентов) отображения и управления информацией БД (User Interface).

Рисунок 8. - Компоненты VCL для взаимодействия с базами данных в Delphi / C++Builder

Помещенный перед названием компонента символ `T' указывает на тип (шаблон) компонента, при визуальном проектировании приложения в среде Delphi / C++Builder компоненты получают имена вида `Название_компонентаN' (где N=1,2,3…).

Компонент типа TDataSource является связующим звеном между представленными TTable и TQuery и визуальными компонентами отображения и управления данными. Компонент TTable предназначен для (двустороннего) соединения таблиц БД через BDE c компонентом TDataSource (с одним TTable может быть связано несколько TDataSource, обратное неверно). TQuery используется для связи с SQL-серверами через BDE, открывая доступ к использованию языка SQL для создания запроса с целью получения данных от SQL-сервера (использование TQuery обязательно при построении приложений класса `клиент/сервер').

Компонент TDBGrid отображает содержимое таблицы в развернутом виде (данные структурированы в табличном виде с полосами вертикальной и горизонтальной прокрутки, для навигации по записям удобно использовать компонент TDBNavigator), TDBEdit позволяет отображать и редактировать содержимое заданного поля текущей записи, TDBImage предназначен для просмотра содержащих изображения полей (поля типа BLOB). Компонент TBDLookupCombo используется в случаях, когда необходимо при перемещении по записям некоторой таблицы одновременно видеть (в выпадающем списке) связанную с текущей записью дополнительную информацию из другой таблицы, TDBListBox позволяет редактировать связанное поле текущей записи таблицы данных путем выбора возможного значения из списка. В последующих версиях Delphi / C++Builder число компонентов работы с БД расширено.

Настройка свойств компонентов при создании действующего приложения приведена в табл. 1 (компонент TQuery нужен лишь при необходимости использования языка SQL).

Таблица 1. - Настройки свойств компонентов VCL при создании приложений баз данных

Тип компонента	Настраиваемое свойство (Property)	Значение настраиваемого свойства
TDBGrid	DataSource	имя компонента DataSource (страница палитры компонентов DataControls)
TDataSource	DataSet	имя компонента Table или Query (*), страница палитры компонентов DataAccess
TTable	DatabaseName	имя алиаса БД (*)
(если	TableName	имя файла таблицы (*)
SQL не	IndexFieldName	имя индексируемого поля (*)
требуется)	IndexName	имя вторичного индекса (*)
TQuery	DatabaseName	имя алиаса БД (*)
(если	DataSource	пусто (в простейшем случае)
требуется	SQL	select * from имя_таблицы
SQL)	RequestLive	true
TDBNavigator (необязательно)	DataSource	имя компонента DataSource (*), страница палитры компонентов DataControls

(*) - возможность выбора из ниспадающего списка

Для просмотра содержимого таблицы в DesignTime (проектирование приложения без создания исполняемого кода) необходимо установить в TTable или TQuery свойство Active=true.

Внешний вид экранной формы (будущего окна) при проектировании простейшего приложения БД приведен на рис. 9. Указанные компоненты помещаются на форму стандартным образом (путем перетаскивания мышью c палитры компонентов), из них только Table и DataSource видимы в период DesignTime и будут невидимы при RunTime (этап выполнения программы после компиляции).



Рисунок 9. - Форма простейшего приложения класса баз данных на этапе проектирования (DesignTime)

На рис. 9 открыта (таблица визуализируется в компоненте DBGrid, отдельные поля таблицы - в компонентах DBEdit1DBEdit4 и DBImage) таблица ANIMALS из файла ANIMALS.DBF формата dBase (алиас BCDEMOS в случае C++Builder). Компоненты TDBNavigator, TDBEdit и TDImage настраиваются путем установки свойства DataSource в реальное название компонента TDataSource (при единственном на форме компоненте такого типа это обычно TDataSource1); для TDBEdit и TDImage дополнительно в свойстве DataField указывается имя поля, которое необходимо отобразить в компоненте. Редактирование изображения в TDImage возможно посредством буфера обмена (Ctrl+C, Ctrl+V, Ctrl+X для копирования в буфер, вставки из буфера и очистки соответственно).

Переключение между визуализацией формы и текстом программы в DesignTime осуществляется стандартным образом (клавиша F12), вызов окна Object Inspector'а (где на вкладке Properties и устанавливаются необходимые значения свойств) - клавишей F11. С целью позиционирования компонента DBGrid в нижней части окна его свойство Align установлено в значение alBottom, свойство Active компонента Table - в True.

Заметим, что возможно (а иногда и предпочтительно) явное присвоение значений свойств компонентов (например, строкой DBNavigator1-> DataSource=DataSource1; в стиле C).

Фильтрация данных при использовании TTable может быть осуществлена настройкой строкового свойства Filter этого компонента (возможно использование любого количества объединенных логическими связками условий). При необходимости использования языка SQL запросов к базам данных следует вместо TTable применить TQuery (в этом случае свойства TableName, IndexFieldName и IndexName отсутствуют, а имя таблицы задается в строковом свойстве SQL - см. образец в табл. 1; свойство DataSource в этом случае следует оставить пустым).

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

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

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

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

Лабораторная работа №3. Разработка базы данных с отношением 'Master-Detail' (главный-подчиненный) между таблицами

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

Теоретическая часть. При отношении 'Master-Detail' (главный-подчиненный) между таблицами одна из них объявляется главной (Master), вторая - подчиненной (зависимой, Detail); связь таблиц осуществляется по полю с одинаковым именем, входящему в состав обоих таблиц.

Типичным примером является связь между списком заказчиком (таблица CUSTOMER.DB) и заказов (ORDERS.DB). Каждая из таблиц имеет (числовое) поле CustNo, однозначно идентифицирующее заказчика (для таблицы заказчиков - уникальное). Конечная цель проекта - получение информации о заказах (из таблицы ORDERS.DB), сделанных конкретным заказчиком (соответствующим выбранной записи в таблице CUSTOMER.DB). Естественно, у некоторых заказчиков может не быть заказов вообще, у некоторых - много заказов; при попытке удаления заказчика с ненулевым количеством заказов возникает ситуация нарушения ссылочной целостности. Добавление заказа должно предваряться дополнением списка заказчиков (если информация о данном заказчике отсутствует).

Копия окна будущего приложения приведена на рис. 10. На форму помещены по два компонента типа TDBGrid, TTable и TDataSource, для навигации по (главной) таблице служит TDBNavigator. Настройки компонентов приведены в табл. 2.

Дополнительно в свойстве MasterSource компонента Table2 (Detail-таблица) установить значение DataSource1 (т.е. фактически дать ссылку на соответствующий компонент, связанный с Master-таблицей).

Путем щелчка мыши на кнопке свойства Master Fields компонента Table2 вызвать окно Field Link Designer (окно инструмента для установления связи таблиц по одноименным полям), в выпадающем списке Available Indexes выбрать CustNo (имя поля, по которому будет осуществлено связывание). Выбрать (щелчком левой кнопки мыши) в левом списке Detail Fields и в правом списке Master Fields имен связываемого поля CustNo, нажатием кнопки Add подтвердить выбор этого поля и проконтролировать внесение строки CustNoCustNo в список Joined Fields (рис. 11). Напомним, что возможен просмотр данных из таблиц в DesignTime при установке значений свойства Active компонентов TTable в True.

Таблица 2. - Настройки свойств компонентов VCL при создании приложения `Master-Detail'

Тип компонента	Настраиваемое свойство (Property)	Значение настраиваемого свойства
DBGrid1	DataSource	DataSource1
DBGrid2	DataSource	DataSource2
DataSource1	DataSet	Table1
DataSource2	DataSet	Table2
Table1	DatabaseName	BCDEMOS (*)
	TableName	CUSTOMER.DB
	IndexFieldName	CustNo (*)
Table2	DatabaseName	BCDEMOS (*)
	TableName	ORDERS.DB
	IndexFieldName	CustNo (*) или пусто

(*) - возможность выбора из ниспадающего списка



Рисунок 10. - Форма приложения `Master-Detail' на этапе проектирования (DesignTime)

Рисунок 11. - Настройка (с помощью Field Link Designer) связи между таблицами

CUSTOMER.DB и ORDERS.DB по полю CustNo на этапе проектирования

(DesignTime)

Внешний вид окна скомпилированного приложения приведен на рис. 12. Функциональность приложения достигается тем, что каждой (соответствующей определенному значению поля CustNo) записи о заказчиках таблицы CUSTOMER (верхняя таблица на рис. 12) соответствует запись (записи) о заказах (таблица ORDERS) с тем же значением CustNo (нижняя таблица на рис. 12). Дополнительная ссылочная целостность БД (каскадное изменение или удаление записей главной таблицы и др.) должна быть реализована дополнительно (программными средствами).

Рисунок 12. - Копия окна скомпилированного приложения `Master-Detail'

Порядок проведения работы - студент получает задание на создание программы (конкретные таблицы для связывания), самостоятельно определяет возможность связывания в отношении `Master-Detail', названия полей для связывания и необходимые для обеспечения функциональности приложения компоненты, конструирует окно приложения (пользовательский интерфейс), настраивает свойства компонентов, проверяет функциональность в DesignTime и компилирует приложение для конечной отладки. Дополнительным (усложненным) заданием может быть создание такой же функциональности исключительно языковыми средствами Delphi / С++Builder.

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

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

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

Лабораторная работа №4. Разработка баз данных с использованием технологии динамического SQL

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

Теоретическая часть. Одной из особенностей языка запросов к БД является его представление в виде текста; формирование текста SQL-запроса может быть произведено любыми средствами (важно лишь соблюдение правил синтаксиса запроса). Существуют три формы SQL: интерактивный (Interactive), статический (Static) и динамический (Dynamic).

Интерактивный SQL применяется для непосредственной работы с БД - пользователь вводит SQL-оператор, он сразу же выполняется и пользователь видит результат выполнения (или код ошибки).

Статический SQL содержит SQL-операторы, жестко закодированные в теле исполняемого приложения. Наиболее распространен встроенный SQL (Embedded SQL), где SQL-код включается в исходный текст (базовой) программы, написанной на другом языке (например, С или Pascal); при использовании встроенного SQL результаты выполнения операторов SQL перенаправляются в переменные, которыми оперирует базовая программа. К настоящему времени SQL встроен в языки Ada, Cobol, Fortran, C, Pascal, PL/1, Java, Mumps (теперь M).

Динамический SQL также является частью приложения, но конкретный SQL-код генерируется во время выполнения (RunTime), а не вводится заранее.

Наибольшей гибкостью обладает последний вариант формирования текста SQL-запроса, однако он требует навыков программирования (в первую очередь при работе с данными в виде последовательностей символов - строк). При проектировании SQL-запросов полезно воспользоваться работой [3]; для несложных случаев достаточна публикация [6], свободно выгружаемая в виде файла http://pilger.mgapi.edu/metods/sql.zip.

В целом проектирование приложения соответствует вышеприведенному в работе 1, но вместо TTable используется компонент TQuery. Компонент TQuery не содержит свойства TableName, т. к. имя таблицы (таблиц) указывается текстом SQL-строки (задается в свойстве SQL). Выполнение этой SQL-строки инициируется в DesignTime установкой Active=True; при RunTime следует выполнять Query1->Open(); в случае SQL-предписаний, не возвращающих набора данных (таких как INSERT, UPDATE, DELETE, CREATE TABLE) или Query1->ExecSQL(); в противном случае (содержащие SELECT предписания языка SQL).

Окно проектируемого приложения показано на рис. 13 (приведены имена компонентов, в скобках - тип компонента). Строка ввода Edit_ManualInput служит для ручного ввода SQL-предписаний с целью отладки, сгенерированный текст SQL-команды перед исполнением индицируется в LabelBottom (в отлаженной версии продукта подобное недопустимо). В табл. 3 приведены имена функций, вызываемых по нажатию расположенных на форме кнопок.

Рисунок 13. - Форма приложения с использованием динамического SQL на этапе проектирования (DesignTime)

Таблица 3. - Имена функций, вызываемых по нажатию кнопок на форме рис. 13.

Название компонента-кнопки	Имя вызываемой процедуры
RunManuallyInput	RunSqlFromEdit
ClearAllInGrid	ShowAllTable
SelectByLength	SelectByMinMax
SelectByFirstChar	SelectByFirstCharName
SelectByAnytChar	SelectByAnyCharName

Текст содержащего эти функции файла Unit1.cpp приведен ниже (заголовок процедуры RunSQL создан вручную, строка

void __fastcall RunSQL (char* stringSQL);

добавлена в секцию

__published: // IDE-managed Components

файла Unit1.h таким же образом.

// -

#include <vcl.h>

#include «stdio.h» // необходимо для использования функций семейства str***

#pragma hdrstop

#include «Unit1.h»

// -

#pragma package (smart_init)

#pragma resource «*.dfm»

TForm1 *Form1;

// -

char s_const[]= «SELECT * FROM biolife WHERE»; // постоянная часть SQL

// -

__fastcall TForm1:TForm1 (TComponent* Owner): TForm(Owner)

{

} // конец функции -

void __fastcall TForm1: RunSqlFromEdit (TObject *Sender)

{

RunSQL (Edit_ManualInput->Text.c_str());

} // конец функции -

void __fastcall TForm1: ShowAllTable (TObject *Sender)

{

RunSQL («select * from biolife»);

} // конец функции ShowAllTable -

void __fastcall TForm1: RunSQL (char* stringSQL)

{

char s1 [250]; // рабочая строка

LabelBottom->Caption=stringSQL; // вывод SQL-строки для проверки

Query1->Close(); // закрыть набор данных

Query1->SQL->Clear(); // очистить буфер SQL-строк

Query1->SQL->Add(stringSQL); // добавление строки в буфер

// определяем, содержится ли в строке клауза SELECT (независимо от регистра)

strcpy (s1, StrUpper(stringSQL)); // копируем SQL-строку (в верхнем регистре) в s1

if (strstr (s1, «SELECT»)!=NULL) // SELECT содержится в тексте запроса…

Query1->Open(); // выполнение SQL-предписания, содержащего SELECT

else // SELECT НЕ содержится в тексте запроса…

Query1->ExecSQL(); // выполнить SQL-предписание, не содержащего SELECT

// функция RunSQL упрощена в части исключения анализа корректности

// выполнения Open() и ExecSQL() методом анализа исключений

} // конец функции RunSQL -

void __fastcall TForm1: SelectByMinMax (TObject *Sender)

{ // динамически сгенерировать текст SQL для выбора рыб по длине (от…до)

// выбор осуществляется по полю Length_In (длина в дюймах)

char s1 [250], s2 [250]; // рабочие строки для генерации SQL-предписания

strcpy (s1, s_const); // добавили постоянную часть строки

sprintf (s2, «Length_In >%s AND Length_In <%s»,

Edit_LengthMin->Text, Edit_LengthMax->Text);

strcat (s1, s2); // добавили переменную часть строки

RunSQL(s1); // выполнить s1

} // конец функции SelectByMinMax -

void __fastcall TForm1: SelectByFirstCharName (TObject *Sender)

{ // динамически сгенерировать текст SQL для выбора рыб по начальным

// символам названия (выбор осуществляется по полю Common_Name0

char s1 [250], s2 [250]; // рабочие строки для генерации SQL-предписания

strcpy (s1, s_const); // добавили постоянную часть строки

sprintf (s2, «Common_Name LIKE ' % s%%'», Edit_FirstChar->Text);

strcat (s1, s2); // добавили переменную часть строки

RunSQL(s1); // выполнить s1

} // конец функции SelectByFirstCharName -

void __fastcall TForm1: SelectByAnyCharName (TObject *Sender)

{ // динамически сгенерировать текст SQL для выбора рыб по произвольным

// символам названия (выбор осуществляется по полю Common_Name)

char s1 [250], s2 [250]; // рабочие строки для генерации SQL-предписания

strcpy (s1, s_const); // добавили постоянную часть строки

sprintf (s2, «Common_Name LIKE '%%%s%%'», Edit_AnyChar->Text);

strcat (s1, s2); // добавили переменную часть строки

RunSQL(s1); // выполнить s1

} // конец функции SelectByAnyCharName -

Как видно из вышеприведенного листинга, SQL-предписания конкатенируются из неизменяемой части s_const (описана глобальной) и зависящей от требуемой функциональности (внутри функций SelectByMinMax, SelectByFirstCharName и SelectByAnyCharName) составляющей; готовая строка передается функции RunSQL для исполнения. Вид окна cкомпилированного приложения приведен на рис. 14.



Рисунок 14. - Копия окна скомпилированного приложения, использующего динамический SQL

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

· на основе анализа требуемой функциональности программы рассмотреть применимость различных клауз SQL и составить шаблоны SQL-команд

· разделить (логически) строки-шаблоны на неизменяемые и изменяемые при генерации SQL части

· перейти к созданию и отладке исходного кода программы для генерации синтаксически корректных SQL-предписаний.

При создании полноценного приложения `клиент/сервер' (Oracle, InterBase, MS SQL Server, Informix, Sybase и др.) необходимо применение компонента типа TStoredProc (для использования хранимых процедур сервера БД и передачи параметров хранимой процедуре) и TDatabase (установление постоянной связи с БД, задание параметров сервера, управление транзакциями); последние версии Delphi / C++Builder штатно содержать дополнительные компоненты работы с БД. При этом необходимо дополнить драйвера SQL Links или ODBC, расширяющие возможности Borland Database Engine [4].

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

Технология хранимых процедур поддерживает параметризацию - например, имя полей необязательно конкретизировать заранее, а возможно уточнять методом замены формальных значений фактическими. Например, в следующем SQL-предписании на дополнение записей в таблице CUSTOMER значения полей COMPANY, CITY, STATE заранее не конкретизируются, а объявляются формальными параметрами: Company,:City,:State (где символ `двоеточие' как раз и указывает на принадлежность к формальным параметрам)

INSERT INTO CUSTOMER (COMPANY, CITY, STATE) VALUES (:Company,:City,:State)

Перед выполнением запроса на сервере происходит замена формальных параметров фактическими, например, с использованием свойств Params или метода ParamByName компонента Query:

……….

Query1->Params[0]->AsString=» Moon Light»;

Query1->Params[1]->AsString=» Los Angeles»;

Query1->Params[2]->AsString=» California»;

Query1->ExecSQL();

или

……….

Query1->ParamByName («City»)->AsString=» Los Angeles»;

Query1->ParamByName («Company»)->AsString=» Moon Light»;

Query1->ParamByName («State»)->AsString=» California»;

Query1->ExecSQL();

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

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

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

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

Лабораторная работа №5. Применение пакета SQL_TEST при изучении возможностей языка SQL

Цель работы - приобретение практических знаний в разработке текстов SQL-запросов и их оптимизации.

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

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

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

Тренажер SQL-запросов к одной таблице. Для освоения (локального) SQL разработан простой тренажер в применении SQL, исполняемый файл которого называется SQL_1. ЕХЕ (пакет SQL_TEST может быть свободно выгружен из Сети в виде файла http://pilger.mgapi.edu/bin/sql_test.zip и инсталлирован стандартным образом).

Тренажер позволяет исполнять SQL-предложения при работе с локальной БД, имя БД-файла - ЕМР.DBF (алиас ЕМР). Текст SQL-запроса вводится пользователем и исполняется, результат исполнения визуализируется тут же в таблице, ошибка индицируется. Само SQL-выражение запоминается в дисковом файле с расширением SQL_1.SQL (список - в файле SQL_1.SEQ) и восстанавливается при повторном старте тренажера.

При работе с тренажером имеется возможность пользоваться системой контекстного HELP'a (используется файл LOCALSQL.HLP из поставки Delphi); для вызова системы помощи следует пользоваться клавишей F1 (или Ctrl+Fl) или щелкнуть правой кнопкой мыши.

Для перемещения по записям БД служит компонент TDBNavigator, представленный в виде линейки с кнопками в левой нижней части окна; он же используется для удаления записей, их изменения / добавления (в случае, если в результате последнего выполненного SQL-предписания БД не была открыта в режиме ReadOnly).

Риcунок 15. - Копия окна программы при функционировании модуля SQL_1

При работе тренажера ведется и выдается на экран статистика выполнения SQL-предписаний, протоколирование в файл не предусмотрено. Общий вид экрана при работе с тренажером SQL_1 приведен на рис. 15.

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

Учащийся вводит текст SQL-директивы в соответствующем окне и нажимает кнопку ВЫПОЛНИТЬ, после чего в таблице индицируется результат SQL-запроса или выдается предупреждение о невозможности выполнения. Кнопка ПОКАЗАТЬ ВСЮ ТАБЛИЦУ служит для вывода всех записей таблицы ЕМР, при этом (неявно) выполняется SQL-предписание SELECT * FROM 'emp'. Заметим, что в данной реализации SQL имена таблиц полезно заключать в кавычки, для наименований полей (вместе с конкретизирующим данное поле именем таблицы) кавычки не требуются.

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

Пользоваться данными о времени выполнения запросов целесообразно только в случае достаточно большой таблицы (сотни / тысячи записей). При оптимизации текста SQL-запросов в первую очередь следует обратить внимание на последовательность условий клаузы WHERE (исходя из того, что условия проверяются в основном последовательно), ORDER BY (целесообразно максимально раньше исключить с помощью WHERE записи, априори не требующие упорядочивания) и на рациональное индексирование таблиц (SQL при возможности максимально использует индексацию для ускорения доступа к данным).

Тренажер SQL-запросов к двум таблицам. Исполняемый файл данного тренажера имеет название SQL_2.EXE, при работе используются таблицы ЕМР.DBF и DEPT.DBF (алиасы EMP и DEPT соответственно).

Общий вид экрана при работе с тренажером SQL_2 приведен на рис. 16. Условно экран разбит на 2 области - в верхней вводятся SQL-директивы и просматривается результат их выполнения (в окне РЕЗУЛЬТИРУЮЩАЯ ТАБЛИЦА), в нижней показано содержание исходных таблиц DEPT и ЕМР (допускается их редактирование с помощью линеек управления).

Учащийся вводит текст SQL-директивы в соответствующем окне и нажимает кнопку ВЫПОЛНИТЬ, после чего в окне РЕЗУЛЬТИРУЮЩАЯ ТАБЛИЦА индицируется результат SQL-запроса или выдается предупреждение о невозможности выполнения. SQL-выражение запоминается в дисковом файле с расширением SQL_2.SQL (список - в файле SQL_2.SEQ) и восстанавливается при повторном старте тренажера.

Заметим, что при использовании поля HIREDATE следует применять встроенные функции DAY(), MONTH(), YEAR(); подробнее см. контекстный HELP. Точка с запятой в конце SQL-строки необязательна. Локальный SQL поддерживает псевдонимы внутри SQL-выражений (см. ниже задаваемые в клаузе FROM псевдонимы D и Е для таблиц DEPT и ЕМР соответственно).

SELECT D.deptno, D.dname, D.loc, E.ename, E.job, E.hiredate, E.sal, E.comm

FROM 'dept' D, 'emp' E

WHERE D.deptno = E.deptno

ORDER BY E.sal

Учащийся может и должен самостоятельно составить SQL-запросы для уточнения возможностей локального SQL.



Рисунок 16. - Копия окна программы при функционировании модуля SQL_2

Утилита dbEXPLORER разработки АО ROSNO. В комплект программного обеспечения для исследования БД входит разработанная и бесплатно распространяемая АО ROSNO утилита dbEXPLORER ('исследователь' БД, рис. 17). Утилита позволяет подключаться к таблице (файлу) БД по известному алиасу, просматривать содержимое БД (и выполнять допустимые SQL-запросы), получать информацию о полях и их типах в выбранной БД.

Утилита dbEXPLORER по функциональности приближается к (штатной) утилите SQL Explorer и приводится в качестве иллюстрации возможностей создания системного ПО с помощью интегрированных сред Delphi / C++Builder. Принцип взаимодействия пользователя с dbEXPLORER является стандартным для Windows, ввод и выполнение SQL-запросов практически не отличается от вышеописанного.

Автор данной работы заочно благодарен разработчикам dbEXPLORER'a и обязан сообщить о внесенных им несущественных изменениях в исходные тексты dbEXPLORER'а (в основном оформительского характера), не затрагивающих сущности утилиты.

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

Преподавателем проверяется корректность выполнения запроса и предложенные студентом пути ускорения его выполнения.

Рисунок 17. - Копия окна программы при функционировании утилиты dbEXPLORER (русификация FREEWARE-продукта АО ROSNO)

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

Лабораторная работа №6. Проектирование баз данных c использованием cистем автоматизированного проектирования (применение CASE-пакета ERwin)

Цель работы - приобретение практических знаний в проектировании баз данных с использованием CASE-систем.

Теоретическая часть. Проектирование лишь простейших БД возможно `на лету', при создании более сложных систем (а для технологии `клиент/сервер' практически всегда) полезно применение специальных инструментов - CASE (Computer-Aided System Engineering) систем. Такие системы позволяют описывать правила функционирования приложения и реализующие их объекты БД (индексы, триггеры), в буквальном смысле рисуя мышью связи между таблицами без программирования. При этом сильно упрощается процесс разработки БД и ее изменения (обычно с целью расширения возможностей) на основе имеющейся разработки (реинженерия), существенно снижается вероятность ошибок разработки.

Система ERwin (фирма Logic Works) использует являющуюся промышленным стандартом модель `сущность-связь' (ER - Entity-Relationship model). ER-модель является обобщенной моделью данных и обеспечивает основанный на способе мышления людей подход к представлению и организации данных [3]. При этом переход от ER-модели достаточно прост.

Этапами проектирования являются определение сущностей, атрибутов, связей и нормализация таблиц. Сущность - множество однотипных объектов (экземпляров), каждый объект индивидуален и уникален. Атрибут - характеристика сущности, выражающая одно законченное и определенное свойство сущности (напр., дату рождения). Связь - логическое отношение между сущностями, выражающее ограничение или правило действия; типичными связями являются `один ко многим' и `многие ко многим'. При переходе от ER-модели к реляционной БД сущности становятся таблицами, атрибуты - полями таблиц, связи реализуются с использованием ключей.

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

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

При создании связей между сущностями (например, `один ко многим') в дочернюю сущность передаются атрибуты, составляющие первичный ключ в родительской сущности; эти атрибуты образуют в дочерней сущности внешний ключ.

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

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

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

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

Ниже описывается процесс проектирования простой (аналогичной описанной в [4]) БД с применением классического CASE-пакета ERwin [2]. Проектируется БД посещаемости пациентами медицинского учреждения. На рис. 18 приведен исходный список данных, являющихся существенными для хранения информации о посещениях (на первом этапе проектирования именно эти данные представляются центральной сущностью информационной системы).



Рисунок 18. - Начальный этап проектирования БД - примерный список данных о посещении пациентами поликлиники

Т.к. в посещении участвуют врач и пациент, представляется целесообразным разделение данных на (дополнительные) сущности `врач' и `пациент' (рис. 19). В верхней части каждого бланка приводится имя сущности (`Врач', `Посещение', `Пациент'), в первой строке указывается имя первичного ключа (Primary Key), ниже - список атрибутов (редактируются в окне Entity-Attribute Editor, активизируемом нажатием правой клавиши мыши в области бланка данной сущности). Сам (пустой) бланк новой сущности создается при нажатии копки Independent entity или (Dependent entity) на плавающей панели ERwin Toolbox (рис. 19); для дальнейшего редактирования следует выбрать вариант Select (`стрелка') плавающей панели. Показанные штриховыми линиями связи создаются нажатием кнопок Identifying relation и подобных на плавающей панели.