Создание базы данных

Предположим, что не все продавцы имеют телефоны (приведите данные в соответствие с этим предположением). Подсчитайте число продавцов, имеющих телефоны.

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

Подсчитайте среднюю зарплату продавцов.

Найдите зачение максимальной и минимальной зарплаты.

4.5 Требования к отчету

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

4.6 Контрольные вопросы

Какие операторы специального вида вы знаете? Назначение каждой операции?

Какие операции отношения используются в условиях отбора данных?

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

Что такое агрегатная функция?

Играет ли роль тип данных при использовании функции COUNT.

Чтобы группировать данные по столбцу, должен ли этот столбец быть указан в списке ключевого слова SELECT?

Для чего используются псевдонимы таблиц?



5. Лабораторная работа № 5. Создание и использование представлений и хранимых процедур

Цель работы: использование представлений для защиты данных; освоение хранимых процедур.

5.1 Использование представления для защиты данных

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

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

Представление создается с помощью команды CREATE VIEW:

а) создание представления для данных одной таблицы:

CREATE VIEW имя_представления

SELECT * | столбец1 [, столбец2]

FROM имя_таблицы

[WHERE выражение1 [, выражение2]]



б) создание представления для данных нескольких таблиц:

CREATE VIEW имя_представления

SELECT * | столбец1 [, столбец2]

FROM имя_таблицы1, имя_таблицы2 [, имя_таблицы3]

[WHERE выражение1 [, выражение2]]

в) создание представления на основе другого представления:

CREATE VIEW представление2

SELECT *

FROM представление1

5.2 Использование хранимых процедур

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

Некоторые из преимуществ использования сохраненных процедур:- операторы процедуры уже сохранены в базе данных;- операторы процедуры уже проверены и находятся в готовом для использования виде;- при использовании процедур результат получается быстрее;- возможность сохранения процедур позволяет использовать модульное программирование;

- сохраненные процедуры могут вызывать другие процедуры;- сохраненные процедуры могут вызываться другими программами.

В SQL Server процедуры создаются с помощью оператора следующего вида:

CREATE PROCEDURE имя_процедуры

[[(] @имя_параметра



ТИП_ДАННЫХ [(ДЛИНА) | (ТОЧНОСТЬ) [, МАСШТАБ])

[=DEFAULT][OUTPUT]]

[, @ИМЯ_ПАРАМЕТРА

ТИП_ДАННЫХ [(ДЛИНА) | (ТОЧНОСТЬ) [, МАСШТАБ])

[=DEFAULT][OUTPUT]]

[WITH RECOMPILE]

AS операторы SQL

Сохраненные процедуры используются следующим образом:

EXECUTE [@ =] имя_процедуры

[[@ имя_параметра =] значение |

[@ имя_параметра =] @ переменная [OUTPUT]]

[WITH RECOMPILE]

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

5.3 Задание на лабораторную работу

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

- представление, содержащее только описание товара и его цену;

- представление, содержащее название клиента и его адрес;

- представление, содержащее номер счета, название клиента, описание товара и дату счета.

Создайте хранимые процедуры:

- для добавления новых строк в таблицу PRODUCTS_TBL;

- для поиска товаров, цена которых превышает определенную сумму;

- для увеличения цены всех товаров на 12%;

- для поиска счетов клиентов с максимальной общей суммой;

- для формирования списка клиентов с их телефонами;

- для формирования списка товаров с их ценами;

- проверьте работу всех созданных процедур.

5.4 Требования к отчету

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

5.5 Контрольные вопросы

Что такое представление?

Что случится, если таблица, на основе которой строится представление, будет удалена?

Как представление можно использовать для защиты данных?

Как можно удалить представление?

Что такое хранимая процедура?

В чем преимущества использования процедур?

Может ли сохраненная процедура вызывать другую сохраненную процедуру?

Может ли сохраненная процедура выполнять вычисления?

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

Где хранится процедура?



6. Лабораторная работа № 6. Использование триггеров

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

6.1 Определение триггера

Триггерами (Triggers) называется специальный класс хранимых процедур, автоматически запускаемых при добавлении, изменении и удалении данных из таблицы. Триггер срабатывает при модификации данных и запускает хранимую процедуру, выполняющую определенные действия. В зависимости от выполняемых пользователем действий, приводящих к запуску триггера, они делятся на три категории: триггеры изменения (UPDATE TRIGGER), триггеры вставки (INSERT TRIGGER) и триггеры удаления (DELETE TRIGGER). В одном триггере могут сочетаться все три типа. Очевидно, что невозможность срабатывания триггера по операции SELECT объясняется отсутствием модификации данных в этой операции.

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

Синтаксис оператора на создание триггера:

/*Заголовок и название триггера*/

CREATE TRIGGER Trigger_name



/*Имя таблицы, для которой написан триггер*/

ON table_name

/*На какие события реагирует триггер*/

FOR INSERT, UPDATE, DELETE

/* Ключевое слово */

AS

/*Определение данных триггера*/

/*Начало тела триггера*/

BEGIN

DECLARE @var_name type

/*var_name - имя переменной,@ - обязательный символ, type - тип данных переменной*/

/*Присвоение переменной значения столбца таблицы*/

SELECT @var_name=Table_name.Column_name

/*Из какой таблицы будет выбираться столбец; T,Q - псевдонимы; inserted - условное обозначение вставляемых данных*/

FROM table_name T, inserted Q



/*Сравниваются ключевые поля исходной таблицы и вставляемых данных*/

WHERE T.key=Q.key

/*Условие выполнения триггера*/

IF условие

BEGIN

/*Отмена транзакции*/

ROLLBACK TRAN

/*Сообщение об ошибке*/

RAISERROR (`Сообщение')

END

END

/*Конец триггера*/

Пример создания триггера (создать триггер и проверить его работу рекомендуется после выполнения заданий 7.2.1 - 7.2.3).

Предположим необходимо отслеживать количество товара, которое мы можем продать. Это количество не должно превышать количество товара на складе; если менеджер по ошибке нарушает это условие, система должна выдать сообщение. Код триггера будет иметь вид:



CREATE TRIGGER CHECK_OSTATOK

ON ORDERS_TBL

for

INSERT

AS

declare @ost int, @ost1 int

select

/*OSTATOK =

= PRODUCTS_TBL.QTY_VSEGO - ORDERS_TBL.QTY*/

@ost= ORDERS_TBL.QTY,

@ost1= PRODUCTS_TBL. OSTATOK

from ORDERS_TBL, PRODUCTS_TBL

where PRODUCTS_TBL.PROD_ID= ORDERS_TBL. PROD_ID

if @ost>@ost1

BEGIN

rollback tran

raiserror ('недостаточно товара на складе',16,3)

END

6.2 Задание на лабораторную работу

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

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

Внесите следующие изменения в структуру базы данных TradeCompany:

- добавьте в таблицу PRODUCTS_TBL поле QTY_VSEGO;

- добавьте в таблицу PRODUCTS_TBL поле OSTATOK;

- добавьте в таблицу ORDERS_TBL поле SUMMA_К_OPLATE;

- добавьте в таблицу CUSTOMER_TBL поле CITY;

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

Используя команду UPDATE, заполните следующие поля:

PRODUCTS_TBL.QTY_VSEGO,

CUSTOMER_TBL.CITY,

ORDERS_TBL.OPLATA

данными (в последнем поле для некоторых клиентов установите значение NO). В поле QTY_VSEGO таблицы PRODUCTS_TBL хранятся данные о количестве товара на складе, в поле QTY в таблице ORDERS_TBL - количество проданного товара, в поле OSTATOK - разница между значениями двух предыдущих полей.

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

- в поле PRODUCTS_TBL.OSTATOK - количество товара, оставшегося на складе (PRODUCTS_TBL. QTY_VSEGO - ORDERS_TBL.QTY);

- в поле ORDERS_TBL.SUMMA_К_OPLATE - сумму, уплаченную за приобретенный товар; она рассчитывается как произведение цены товара на количество (таблица ORDERS_TBL).

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

Создайте триггеры, позволяющие контролировать следующие ситуации:

- не обслуживать клиента из определенного города;

- обслуживать клиентов только из определенного города;

- не удалять запись о продажах, если товар не оплачен;

- не закупать товары, цена которых превышает определенную сумму.



6.3 Требования к отчету

Отчет по работе выполняется на бумажном носителе и должен содержать:

- листинги текстов триггеров;

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

6.4 Контрольные вопросы

В чем преимущества использования процедур? 6.4.2 Когда выполняются триггеры - до или после выполнения команд INSERT, UPDATE и DELETE? 6.4.3 Можно ли изменить триггер?

Как можно ввести текст триггера в MS SQL 2005?

Как проверяется работа триггера?



7. Лабораторная работа № 7. Разработка интерфейса пользователя

Цель работы: освоение процедуры создания клиентского приложения к базе данных средствами Borland C++Builder.

7.1 Организация доступа к данным средствами технологии ADO

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

Технология ADO включает набор высокоуровневых интерфейсов OLE DB, которые входят в состав любой операционной системы Windows. Интерфейс OLE DB (Object Linking and Embedding Database - связывание и внедрение объектов баз данных) - это универсальная технология для доступа к источникам данных любого типа с использованием специализированных объектов COM (Component Object Model - модель компонентных объектов), в которых инкапсулированы стандартные функции обработки данных и передачи данных между объектами.

В лабораторной работе разрабатывается интерфейс к рассматриваемой базе данных TradeCompany.

Для облегчения задачи создайте упрощенный вариант структуры таблицы ORDERS_TBL: в этой таблице поле ORD_NUM будет являться первичным ключом (поле ORDERS_ID удаляется из таблицы). Заполните таблицу данными, поле TOTAL_COST не заполняйте.

С помощью этого интерфейса можно выполнять следующие задачи:

- просмотр данных в блицах базы данных;

- выполнение различных запросов к базе данных;

- формирование отчетов.

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

7.2 Настройка соединения ADO

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

- в приложении Borland Builder C++ поместите на форму компонент ADO Connection со страницы ADO палитры компонентов. Параметры соединения указываются в свойстве ConnectionString;

- в окне инспектора объектов в свойствах ADO Connection выберите Connection String. В появившемся окне шелкните на кнопке Build; в окне «Свойства связи с данными» на вкладке «Поставщик данных» выбрать SQL Native Client;

- на вкладке «Подключение»: в пункте «Для входа в сервер» выбрать Учетные сведения Windows NT; в следующем пункте выбрать начальный каталог из раскрывающегося списка. Обязательно проверить подключение;

- в окне Инспектора объектов - значение свойства Login Promt установить false, чтобы при каждом обращении к базе не происходил вызов окна ввода пароля.



7.3 Доступ к данным базы данных

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

- наборы данных (DataSet), непосредственно связывающиеся с базой данных; в лабораторной работе это такие компоненты, как Table, Query, StoredProc;

- источник данных (DataSource), осуществляющий обмен информацией между компонентами Dataset и компонентами визуализации и управления данными;

- компоненты визуализации и управления данными, такие, как DBGrid, DBEdit, DBText (см. рисунок 7.1).

Размещено на http://www.allbest.ru/

Рисунок 7.1 - Связь компонентов между собой и с базой данных

Свойство DataSet компонента DataSource идентифицирует имя компонента TDataSet. Значение свойству DataSet на этапе проектирования можно присвоить с помощью инспектора объектов, а на этапе выполнения - программно.

Для отображения данных базы данных можно использовать два варианта: АDO Table и ADOQuery.

Рассмотрим применение этих компонентов:

а) для локальных баз данных обычно используется компонент Table (ADOTable), представленный классом TTable. Он обеспечивает самый быстрый и простой доступ к таблице:

- на форму, описанную в п. 7.2, поместите компоненты ADOTable1, DataSource1;

- установите свойства компонентов (свойства устанавливаются в Инспекторе объектов):

Компонент	Свойство	Значение
DataSource1	DataSet	ADOTable1
ADOTable1	Connection	ADOConnection1
ADOTable1	TableName	Имя таблицы

- поместите на форму компоненты DBGrid1 и DBNavigator1; установите свойства компонентов:

Компонент	Свойство	Значение
DBGrid1	DataSource	DataSource1
DBNavigator1	DataSource	DataSource1
DBNavigator1	ShowHint	True

- для компонента ADOTable1 значение свойства Active установие true;

- предусмотрите возможность выхода из приложения;

- запустите приложение; просмотрите результат;

- измените внешний вид таблицы (например, надписи в заголовках столбцов) с помощью редактора свойств Columns Editor. Для вызова Columns Editor нужно либо выбрать соответствующую опцию в контекстном меню компонента DBGrid или щелкнуть мышью в колонке значений напротив свойства Columns в Инспекторе объектов. Щелкнув правой кнопкой мыши и выбрав строку Add All Fields, откроем свойство Title и для каждого поля в свойстве Caption можно записать русские названия полей.

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

б) в подавляющем большинстве случаев оправданно использование объектов TQuery (ADOQuery), чем применение наборов данных TTable. Связано это с большой гибкостью и эффективностью языка запросов SQL, полагаемых в основу TQuery. Преимущества использования компонента TQuery связаны с такими факторами, как:

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

- возможность в одном наборе данных сочетать информацию из разных таблиц одной базы данных;

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

- возможность использовать параметры.

Использование ADOQuery: удалите компонент ADOTable1 с формы и поместите вместо него ADOQuery1; для этого компонента установить дополнительные свойства:

Компонент	Свойство	Значение
ADOQuery1	Connection	ADOConnection1
ADOQuery1	SQL	select * from PRODUCTS_TBL
ADOQuery1	Active	true

Основное свойство компонента Query - SQL, имеющее тип TStrings. Это список строк, содержащих запросы SQL. Здесь значение свойства SQL приведено в качестве примера. Последнее свойство компонента можно установить программно:

//Подключение к базе данных

{ADOquery1->Open();

//К активной базе данных нельзя подключиться

ADOquery1->Enabled=False

}

Во время выполнения приложения свойство SQL может формироваться программно методами, обычными для класса TStrings: Clear() - очистка, Add ()- добавление строки, Open() - активизация запроса и т.д.

Здесь используется один и тот же компонент Query, который можно применить для разных запросов: текст запроса вписывается в текст кода (предварительно идет очистка от старого запроса, затем текущий запрос активируется). В результате выполнения запроса компонент DBGrid представил таблицу PRODUCTS_TBL в табличном виде.

Поместите кнопку Button1 на форму: при нажатии на эту кнопку выполняется запрос свойства SQL; код обработчика события будет иметь следующий вид:

void __fastcall TForm1::Button1Click(TObject *Sender)

{

//Очищаем от старого запроса

ADOQuery1->SQL->Clear();

//Запрос

ADOQuery1->SQL->Add("select * from PRODUCTS_TBL ");

//Активизирует компонент

ADOQuery1->Open();}

7.4 Выполнение поиска по некоторому параметру

7.4.1 Использование фильтра

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

Для выполнения поиска в компоненте TADOTable есть свойство Filter. В нем можно указывать условие, по которому будут отображаться данные. Чтобы фильтр заработал, нужно установить значения свойств TADOTable:

Компонент	Свойство	Значение
ADOTable	Filter	текст условия в виде: Поле [оператор сравнения] 'значение'
ADOTable	Filtered	true

Например, текст условия может быть следующим:

PROD_DECS ='ватман';

Для возможности изменения фильтра можно использовать строку ввода TЕdit: поместите на форму дополнительно компонент - поле TЕdit1, в которое будет вводиться название товара, и кнопку - «Фильтрация по названию товара». В этом случае поиск задается программно и будет иметь вид:

void __fastcall TForm2::Button1Click(TObject *Sender)

{

ADOTable1->Filtered=False;

ADOTable1->Filter="PROD_DECS='"+Edit1->Text+"'";

ADOTable1->Active=True;

ADOTable1->Filtered=True;

}

Значение нужно указывать в одинарных кавычках (апострофы), а строка ввода заключается в кавычки, поэтому условие имеет вид '"+Edit1->Text+"'. Это конструкция будет использоваться и дальше.

7.4.2 Использование языка запросов SQL для поиска

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

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

Пусть необходимо по названию товара получить всю информацию о нем.

Откройте новую форму. Поместите на нее компоненты: Label1 - с текстом подписи, Edit1 - для ввода названия товара; Label2, Label3 - для вывода количества записей со следующими свойствами:

Компонент	Свойство	Значение
Label1	Caption	Введите название товара
Label2	Caption	Видов данного товара

Код для поиска указанного товара имеет вид:

void __fastcall TForm2::Button1Click(TObject *Sender)

{

ADOQuery1->SQL->Clear();

ADOQuery1->SQL->Add("select PROD_DECS, COST from PRODUCTS_TBL where PROD_DECS='"+Edit1->Text+"'");

ADOQuery1->Open();

Label3->Caption=IntToStr(ADOQuery1->RecordCount);

}



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

ADOQuery1->SQL->

Add ("select PROD_DECS, COST from PRODUCTS_TBL");

ADOQuery1->SQL->Add("where PROD_DECS='"+Edit1->Text+"'");

Компоненты TADOTable и TADOQuery имеют свойство RecordCount - количество строк в таблице

Усложним задачу поиска - необходимо вывести данные по указанному товару, проданному на указанную дату: название товара, цену товара, указанное в счете количество, дату.

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

void __fastcall TForm1::Button1Click(TObject *Sender)

{

ADOQuery1->SQL->Clear();

ADOQuery1->SQL->Add("Select PRODUCTS_TBL.PROD_DECS,");

ADOQuery1->SQL->

Add("PRODUCTS_TBL.COST, ORDERS_TBL.QTY,");

ADOQuery1->SQL->Add("ORDERS_TBL.ORD_DATE,");

ADOQuery1->SQL->Add(" from PRODUCTS_TBL,ORDERS_TBL");

ADOQuery1->SQL->Add("where PRODUCTS_TBL.PROD_DECS=");

ADOQuery1->SQL->Add("'"+Edit1->Text+"' and");

ADOQuery1->SQL->



Add("ORDERS_TBL.ORD_DATE='"+Edit2->Text+"' and");

ADOQuery1->SQL->

Add("ORDERS_TBL.PROD_ID=PRODUCTS_TBL.PROD_ID");

ADOQuery1->Open();

}

Если в запросе используется оператор select, то используется метод Open().

Если в запросе удаляются строки или изменяется структура столбца (в запросе есть такие операторы, как INSERT, UPDATE, DELETE или/и CREATE TABLE), необходимо вызвать метод ExecSQL компонента ADOQuery. То есть, если запрос возвращает данные, то достаточно активировать компонент - Open(), а если изменяет, то запрос нужно выполнить с помощью ExecSQL. Например, чтобы заменить данные, удалить строку в таблице, вставить новую запись, можно записать следующие коды обработчика:

void __fastcall TForm1::Button1Click(TObject *Sender)

{

ADOQuery1->SQL->Clear();

ADOQuery1->SQL->Add("Update PRODUCTS_TBL set PROD_DECS='ручка1' where PROD_DECS='ручка'");

ADOQuery1->ExecSQL();

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button2Click(TObject *Sender)

{

ADOQuery1->SQL->Clear();



ADOQuery1->SQL->Text="Delete PRODUCTS_TBL where PROD_ID='"+Edit1->Text+"'";

ADOQuery1->ExecSQL();

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button3Click(TObject *Sender)

{

ADOQuery1->SQL->Clear();

ADOQuery1->SQL->Text="Insert into PRODUCTS_TBL

values(24,'коврик для мыши',3000)";

ADOQuery1->ExecSQL();

}

7.5 Заполнение таблиц

Рассмотрим задачу создания формы для ввода и редактирования записей таблицы. Вначале рассмотрим заполнение таблицы PRODUCTS_TBL. Откройте новую форму, поместите на нее компонент доступа к данным - TDBEdit1, TDBEdit2, TDBEdit, ADO Table1, DataSource1, DBNavigator1. Свойства компонентов приведены ниже в таблице 7.1.

Таблица 7.1 - Свойства компонентов

Компонент	Свойство	Значение
TDBEdit	DataSource	DataSource1
TDBEdit	DataField	Выбор соответствующего поля
DataSource	DataSet	ADO Table1
ADO Table1	Connection	ADO Connection1
ADO Table1	TableName	Выбор из списка
ADO Table1	Active	True
DBNavigator1	DataSource	DataSource1



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

Запустите приложение. На навигаторе щелкните на кнопке Insert record. Введите данные в DBEdit, щелкните на кнопке Post edit. Данные будут введены в таблицу PRODUCTS_TBL.

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

Создадим новую форму для заполнения полей: ORD_NUM, CUST_ID, PROD_ID, ORD_DATE, QTY (соответственно номер счета, код клиента, код товара, дата счета и количество товара) и вычисляемого поля TOTAL_COST. Для заполнения используются компоненты DBEdit (вкладка DataControl).

При работе с формой при нажатии на кнопках (которые будут размещены на форме) «Описание товара», «Цена товара», «Стоимость товара» на форме появляются значения соответствующих полей на компоненте DBText. Эти значения определяются в зависимости от введенного кода товара в таблицу ORDERS_TBL. В связи с этим необходимо формировать запрос (по введенному коду товара) для определения цены и описания товара.

Итак, поместим на форму необходимые компоненты, список которых с их свойствами приведен ниже в таблице 7.2.

Таблица 7.2 - Свойства компонентов

Компонент	Свойство	Значение
TDBEdit	DataSource	DataSource1
TDBEdit	DataField	Выбор соответствующего поля
DataSource1	DataSet	ADO Table1
DataSource2	DataSet	ADO Query1
DataSource3	DataSet	ADO Query2
ADO Table1	Connection	ADO Connection1
ADO Table1	TableName	Выбор из списка
ADO Table1	Active	True
DBNavigator1	DataSource	DataSource1
ADO Query1	Connection	ADO Connection1
ADO Query2	Connection	ADO Connection1
DBText1	DataSource	DataSource2
DBText2	DataSource	DataSource3

Компоненты DBText1, DBText2 связываются с полем PROD_DECS и COST программно, например: DBText1->DataField="PROD_DECS";.

Ниже приводятся фрагменты кодов для различных ситуаций:

- определение названия товара

void __fastcall TForm1::Button1Click(TObject *Sender)

{

AnsiString t;

ADOQuery1->SQL->Clear();

t=DBEdit3->Text;

ADOQuery1->SQL->Add("select PROD_DECS from PRODUCTS_TBL where

PROD_ID='"+t+"'");

DBText1->DataField="PROD_DECS";

ADOQuery1->Open();

}

- определение цены товара

//---------------------------------------------------------------------------

void __fastcall TForm1::Button2Click(TObject *Sender)

{AnsiString t;

ADOQuery2->SQL->Clear();

t=DBEdit3->Text;

ADOQuery2->SQL->



Add("select COST from PRODUCTS_TBL where PROD_ID='"+t+"'");

DBText2->DataField="COST";

ADOQuery2->Open();

}

- вычисление стоимости товара и заполнение поля TOTAL_COST

//---------------------------------------------------------------------------

void __fastcall TForm1::Button3Click(TObject *Sender)

{

DBText3->Caption=

IntToStr(StrToInt(DBText2->Caption)*StrToInt(DBEdit5->Text));

DBEdit6->Text= DBText3->Caption;

//DBEdit6->Text=

IntToStr(StrToInt(DBText2->Caption)*StrToInt(DBEdit5->Text));

}

В некоторых случаях приложению требуется получить список имен полей таблицы, связанной с ADOQuery. Это может быть сделано методом GetFieldNames, который загружает список имен полей в любую переменную типа TStrings, передаваемую в него в качестве аргумента. Например, оператор ADOQuery1->GetFieldNames(ComboBox1l->Items);

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

7.6 Использование модуля данных

При разработке приложений с большим количеством таблиц или использующих большое количество запросов, интерфейс форм может быть переполнен невизуальными компонентами, соответствующими наборам и источникам данных. Borland С++ Builder (а также и Delphi) позволяют создать специальное окно Data Module (модуль данных), которое удобно для хранения компонентов доступа к базам данных.

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

Чтобы добавить модуль данных в проект, следует выполнить следующие команды: File| New| Data Module. Далее на форме расположить невизуальные компоненты, для работы с базой данных: ADOConnection, DataSource, ADOTable, ADOQuery.

Для того чтобы форма имела связь с модулем данных, необходимо в соответствующий заголовочный файл формы включить директиву #include “Unit.h”.

Обратиться к объекту, расположенному в модуле данных, можно только по составному имени <название модуля данных>-><название объекта>. Например, следующим образом:

DataModule1->ADOTable1->Open();

DataModule1->ADOQuery->SQL-> Text="Delete tovar

where PROD_ID='"+Edit1->Text+"'";

DataModule1->ADOQuery1->ExecSQL();

7.7 Создание отчетности

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

В Borland Builder C++ отчеты QuickReport не устанавливаются, но это можно сделать самостоятельно. Для этого надо выбрать меню Project | Options и на вкладке Packages нажать кнопку Add. Найти файл dcltqr60. bpl, который должен находиться в папке Bin, где установлен Builder, и открыть его.

Все компоненты Quick Reports находятся на вкладке QReport палитры компонентов. Головной компонент TQuickRep. Этот компонент - основа любого отчета. Он представляет собой холст листа будущего отчета. Свойство Bands этого компонента (полосы) содержит несколько пунктов. В этих пунктах можно указать, что должен иметь будущий документ:

- HasTitle - в этом разделе задается заголовок отчета;

- HasColumnHeader - заголовки колонок. Если отчет содержит таблицу, то шапка, где будут определены названия колонок, создают в этой части документа. Если нужна будет таблица, то этому свойству присваивается значение true;

- HasDetail - если в отчете есть таблицы, то вид строк формируется в этом разделе.

- HasPageFooter - в этом разделе создается нижний колонтитул;

- DataSet - здесь указывается таблица, из которой отчет будет брать данные.

На вкладке QReport палитры компонентов доступен ряд компонентов, которые можно располагать в этих разделах: QRLabel - надпись (этот компонент просто отражает нужные данные); QRDBText - данные (предназначен только для отображения значения какого-либо поля из базы данных; QRSysData - системная информация (аналогичен TLabel, только отображает системную информацию: дату, время, номер страницы и т.д.); QRMemo - набор строк (аналогичен TMemo, отображает Memo-данные из базы данных).

Для предварительного просмотра созданного отчета используется метод QuickRep1->Preview().

Рассмотрим формирование отчетов:

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

Поместим на форму компоненты DataSource1, ADOTable1, QuickRep1. Настроим компоненты DataSource, ADOTable.

У компонента QuickRep1 для свойства DataSet установим значение ADOTable1. Откроем пункты свойства Bands. В свойстве HasTitle установить значение true. В появившуюся строку поместить компонент QRLabel со страницы QReport и в свойстве Caption ввести «Список товаров», также поместить компонент QRSysData1 для отображения текущей даты и времени. В свойстве HasColumnHeader установить значение true. Это означает, что в отчете будут отображаться названия полей таблицы. Вставить в появившуюся строку для полей три компонента QRLabel. В заголовках написать: Код товара, Описание товара, Цена товара.

В свойстве HasDetail установить значение true. Вставить в появившуюся строку три компонента QRDBText со следующими свойствами:

Компонент	Свойство	Значение
QRDBText	DataSet	ADOTable1
QRDBText	DataField	указать соответствующие поля
QRSysData1	Data	qrsDateTime

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

/---------------------------------------------------------------------------

void __fastcall TForm1::Button1Click(TObject *Sender)

{



QuickRep1->Preview();

}

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

- данные по наименованию [название товара], купленному [дата счета].

Используем форму по поиску товара, купленного на определенную дату. Поместим компонент QuickRep, со значением ADOQuery1 свойства DateSet. Настроить пункты свойства Bands:

- поместите компоненты QRLabel в строку заголовка отчета; учесть, что название товара и дата - изменяемые параметры, которые определяются в Edit1 и Edit2;

- в строке HasColumnHeader отобразите названия полей;

- в строке HasDetail поместите компоненты QRDBText; свойство DateSet имеет значение ADOQuery1..

В этом случае значение свойства DataField определяется программно:

QRDBText1->DataField="+имя поля+";

Обработчик события для создания отчета для указанного запроса имеет вид:

void __fastcall TForm1::Button1Click(TObject *Sender)

{

QRLabel2->Caption=Edit1->Text;

QRLabel4->Caption=Edit2->Text;

ADOQuery1->SQL->Clear();

ADOQuery1->SQL->Text="Select PRODUCTS_TBL.PROD_DECS,



PRODUCTS_TBL.COST, ORDERS_TBL.QTY,

ORDERS_TBL.ORD_DATE, ORDERS_TBL.TOTAL_COST

from PRODUCTS_TBL,ORDERS_TBL where

PRODUCTS_TBL.PROD_DECS='"+Edit1->Text+"' and

ORDERS_TBL.ORD_DATE='"+Edit2->Text+"' and

ORDERS_TBL.PROD_ID=PRODUCTS_TBL.PROD_ID";

ADOQuery1->Open();

QRDBText1->DataField="COST";

QRDBText2->DataField="QTY";

QRDBText3->DataField="TOTAL_COST";

QuickRep1->Preview();

}

7.8 Задание на лабораторную работу

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

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

7.9 Требования к отчету

Отчет по работе должен содержать:

- разработанное приложение:

- листинги модулей с комментариями.

Выполнить демонстрацию работы разработанного приложения.



7.10 Контрольные вопросы

На что ориентирована технология ADO?

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

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

Объяснить предназначение свойства и методы компонентов Datasource, ADOTable, ADOQuery.

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

На что указывает свойство RecordCount и свойством каких компонентов оно может быть?

В каких случаях используются методы ExecSQL(), Open()?

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

Объяснить использование модуля данных.

Как обратиться к объекту, расположенному в модуле данных?

Перечислить основные пункты и объяснить назначение компонента Bands.

На что указывает компонент QRSysData?

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



8. Лабораторная работа № 8. Шифрование данных в SQL Server 2005

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

8.1 Общие сведения

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

В сравнении с предыдущими версиями, MS SQL Server 2005 обеспечивает защиту данных не только внутри своих серверов, но и во вне. Это достигается системой шифрования, которая позволяет защищать данные, передаваемые по незащищенному каналу. Алгоритмы шифрования разнятся от самых простых, с применением простого шифрования паролем, до многоступенчатых и комплексных, где даже сами ключи для шифрования на каждом шаге генерируются отдельно (алгоритмы DES, TRIPPLE DES, RSA). В случае физического проникновения злоумышленника, то есть в случае такой ситуации, когда рубежи защиты разделением пользователей пали, в сервере MS SQL Server вся зашифрованная информация будет для него нечитаемой.

Для шифрования данных в SQL Server 2005 предусмотрено четыре способа:

- шифрование при помощи сертификатов. Сертификат при этом должен присутствовать в виде объекта в базе данных (в SQL Server Management Studio можно просмотреть имеющиеся сертификаты в контейнере Databases/ Имя_базы_данных/Security/Certificates);

- шифрование при помощи асимметричных ключей. Алгоритм здесь такой же, как и при использовании сертификатов. От сертификата асимметричный ключ отличается тем, что в нем не предусмотрено дополнительных полей с информацией о том, кому он выдан, для каких целей, до какого времени действителен и т. п. Имеющиеся асимметричные ключи можно просмотреть в контейнере Asymmetric Keys, который находится там же, где и контейнер Certificates;

- шифрование при помощи симметричных ключей. Используются намного более быстрые алгоритмы, по сравнению с асимметричными ключами. Сами симметричные ключи также создаются в виде объектов базы данных и могут быть защищены сертификатом, другим симметричным ключом, ассиметричным ключом или просто паролем. Просмотреть их можно при помощи контейнера Symmetric Keys;

- простое шифрование при помощи паролей.

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



8.2 Процедуры шифрования данных в MS SQL Server 2005

8.2.1 Шифрование с помощью сертификатов для защиты ключей

Создание сертификата выполняется при помощи команды CREATE CERTIFICATE. Самый простой вариант этой команды выглядит так:

CREATE CERTIFICATE Cert1

ENCRYPTION BY PASSWORD = '11'

WITH SUBJECT = 'Проверка шифрования',

START_DATE = '02/06/2009'

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

Параметр ENCRYPTION BY PASSWORD определяет пароль, который потребуется для расшифровки данных, защищенных сертификатом (для шифрования данных он не нужен). Если этот параметр пропустить, то создаваемый сертификат будет автоматически защищен главным ключом базы данных (Database Master Key). Автоматически этот ключ не создается. Чтобы получить возможность работать с ним, нужно предварительно его создать:

CREATE MASTER KEY ENCRYPTION BY

PASSWORD = 'P@ssw0rd';

Кроме пароля, главный ключ базы данных автоматически защищается также главным ключом службы (Service Master Key). Этот ключ автоматически генерируется SQL Server 2005 в процессе установки. Надо быть очень внимательным при использовании главного ключа базы данных: если мы переустановим сервер (а, следовательно, изменится главный ключ службы), зашифрованные данные могут быть потеряны. Чтобы этого не случилось, нужно производить регулярное копирование базы данных master или экспортировать главный ключ службы в файл при помощи команды BACKUP SERVICE MASTER KEY. Обязательный параметр SUBJECT команды CREATE CERTIFICATE определяет цель выдачи сертификата.

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

insert into Num_cards

values(1, EncryptByCert(Cert_ID('Cert1'), N'111001') )

insert into Num_cards

values(2, EncryptByCert(Cert_ID('Cert1'), N'111002') )

insert into Num_cards

values(3, EncryptByCert(Cert_ID('Cert1'), N'111003') )

Для расшифрования используется команда DecryptByCert. Чтобы расшифровать данные, нужно использовать обыкновенный запрос SELECT:

select convert(nvarchar(50),

DecryptByCert(Cert_ID('Cert1'),Cred_ID, N'11') )

from Num_cards

8.2.2 Шифрование ассиметричным ключом

Вначале нужно создать ассиметричный ключ.

CREATE ASYMMETRIC KEY ASymKey1

WITH ALGORITHM = RSA_512

ENCRYPTION BY PASSWORD = '11'



Обратите внимание, что, кроме пароля, здесь требуется указать длину создаваемого ключа. В нашем распоряжении три варианта: 512, 1024 и 2048 бит.

После этого при помощи созданного ключа можно производить шифрование данных:

insert into Num_cards

values

(1, EncryptByAsymKey(AsymKey_ID('ASymKey1'), N'111001'))

insert into Num_cards

values

(2, EncryptByAsymKey(AsymKey_ID('ASymKey1'), N'111002'))

insert into Num_cards

values

(3, EncryptByAsymKey(AsymKey_ID('ASymKey1'), N'111003'))

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

Для расшифрования воспользуемся функцией DecryptByAsymKey

SELECT Convert(nvarchar(50), DecryptByAsymKey(AsymKey_ID('ASymKey1'),Cred_ID, N'11'))

FROM Num_cards

8.2.3 Шифрование симметричным ключом

При использовании симметричных ключей шифрование производится быстрее, чем при применении асимметричных алгоритмов, поэтому при работе с большими объемами данных рекомендуется использовать именно их. Применение симметричных ключей выглядит очень похоже. Правда, есть и небольшие отличия. Во-первых, при создании симметричного ключа его можно защищать не только паролем, но и другим симметричным ключом, асимметричным ключом или сертификатом. Во-вторых, при создании симметричного ключа вы можете указать один из восьми алгоритмов шифрования, поддерживаемых SQL Server 2005 (DES, TRIPLE_DES, RC2, RC4, DESX, AES_128, AES_192, AES_256). Само создание симметричного ключа может выглядеть так:

CREATE SYMMETRIC KEY SymKey1

WITH ALGORITHM = DES

ENCRYPTION BY PASSWORD = '11'

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

OPEN SYMMETRIC KEY SymKey1 DECRYPTION BY

PASSWORD = '11'

Используем созданный ключ для шифрования данных:

insert into Num_cards

values(1, EncryptByKey(Key_GUID('SymKey1'), convert(nvarchar(50),'111001') ))

insert into Num_cards

values(2, EncryptByKey(Key_GUID('SymKey1'), convert(nvarchar(50),'111002') ))

insert into Num_cards

values(3, EncryptByKey(Key_GUID('SymKey1'),

convert(nvarchar(50),'111003') ))



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

select convert(nvarchar(50), DecryptByKey(Cred_ID))

from Num_cards

Потому как зашифрованные данные нельзя хранить в столбцах типа int, char, применяетcя команда convert.

8.2.4 Шифрование паролем

SQL Server 2005 позволяет производить шифрование данных также просто при помощи пароля. Для этого используется функция EncryptByPassPhrase.

В самом простом варианте она принимает только пароль и данные, которые необходимо зашифровать:

insert into Num_cards

values(4, EncryptByPassPhrase('Password', N'111004'))

Расшифровка производится при помощи функции DecryptByPassphrase:

select

convert(nvarchar(50), DecryptByPassPhrase('Password', Cred_Id))

from Num_cards



8.3 Задание на выполнение лабораторной работы

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

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

Выясните недостатки и достоинства каждого вида шифрования для определенного типа поля.

8.4 Требования к отчету

Отчет по лабораторной работе должен содержать:

- структуру таблицы используемой базы данных;

- тексты команд шифрования различными способами и листинги

результатов доступа к зашифрованной информации;

- сравнительный анализ способов шифрования полей различного типа.

8.5 Варианты заданий

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

8.6 Контрольные вопросы

Для чего нужна процедура шифрования в базе данных?

Какие способы шифрования данных базы данных вы знаете?

Что такое сертификат?

Как отображаются данные таблицы, к которой применено шифрование?

Различается ли применение шифрования от типа поля таблицы?

Список литературы

1. Дейт К.Дж. Введение в системы баз данных. - М.: Издательский дом «Вильямс», 2008.

2. Коннолли Т., Бегг К. Базы данных. Проектирование, реализация и сопровождение. Теория и практика. - М.: Издательский дом «Вильямс», 2003.

3. Кузнецов С.Д. Основы баз данных. - 1-е изд. - М.: «Интернет-университет информационных технологий - ИНТУИТ.ру», 2005.

4. Харрингтон Дж. Разработка баз данных. - М.: ДМК Пресс, 2005.

5. Хомоненко А.Д., Цыганков В.М., Мальцев М.Г. - Базы данных. Учебник для вузов. - М.: Корона-Принт, 2004.

6. SQL Server 2005. Реализация и обслуживание. //Серия: Учебный курс Microsoft SQL. - СПб.: Питер, Русская редакция, 2007.

7. Хансен Г., Хансен Д. Базы данных: разработка и управление. - М.: ЗАО «Издательство БИНОМ», 1999.

8. Плю Р., Стефенс Р., Райан К. Освой самостоятельно SQL за 24 часа. - М.: Издательский дом «Вильямс», 2000.

9. Кандзюба С.П., Громов В.Н. Delphi 6/7. Базы данных и приложения. - СПб: ООО «ДиаСофтЮП», 2002.

10. Григорьев Ю.А., Ревунов Г. И. Банки данных. - М.: Изд. МГТУ им. Н. Э. Баумана, 2002.

11. Харрингтон Д. Проектирование реляционных баз данных просто и доступно. - М.: Изд. «Лори», 2000.

Размещено на Allbest.ru