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

• Запуск процедур манипуляции данными из приложения клиента

Хранимые  процедуры позволяют не только возвращать данные, но и манипулировать ими. Для этого используют DML-команды языка SQL. Такие процедуры удобны, например, для реализации каскадного механизма сохранения ссылочной целостности. Покажем, как, используя компоненты TStoredProc и TQuery, можно инициировать процедуры манипуляции данными.

• Использование TstoredProc

Ход работы:

1. Начните новый Delphi-проект.
2. Поместите на форму компонент TDatabase
3. Поместите компонент TStoredProc на форму

Свойства  компонента TDatabase      Свойства компонента TStoredProc

Свойство	Значение		Свойство	Значение
AliasName	clsrvExmpl		DatabaseName	Exmpl
DatabaseName	Exmpl		StoredProcName	Update_Lease
Params	User Name=SYSDBA PASSWORD=masterkey		Params	Ldat=12.08.2005
LoginPrompt	False
Connected	True

 

 

 

 

 

 

 

 

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

CREATE PROCEDURE UPDATE_LEASE(LDat TIMESTAMP, NumLease SMALLINT)

AS

BEGIN

UPDATE Lease SET LDate = :LDat

WHERE NLease = :NumLease;

SUSPEND;

END

 

5. Для исполнения хранимой процедуры поместите на форму кнопку и ассоциируйте с ней процедуру:

procedure TForml.ButtonlClick(Sender:   TObject); begin

with StoredProcl do begin

ParamByName('NumLease').Aslnteger   :=  StrToInt(Editl.Text); Prepare; ExecProc;

Editl.Text   :=   'Данные изменены' end; end;

6. Поместите на форму компонент TEdit.
7. Откомпилируйте и запустите приложение.
9. Введите в поле редактора, например, цифру 2 и щелкните мышью на кнопке.
10. Используя IBConsole  можете убедиться,  что дата заключения договора изменилась.
 

• Использование TQuery

Ход работы:

1. Начните новый Delphi-проект.
2. Поместите на форму компонент TDatabase и назначьте его свойства.
3. Поместите компонент TQuery на форму и назначьте его свойства.

Свойство	Значение
DatabaseName	Exmpl
SQL	EXECUTE PROCEDURE UPDATE_LEASE(’14.05.2001’, 3)

 

 

 

4. Для исполнения хранимой процедуры поместите на форму кнопку и ассоциируйте с ней процедуру:

procedure TForml.ButtonlClick(Sender: TObject);

begin

with Queryl do begin

Prepare; ExecSQL;

Editl.Text := 'Данные изменены';

end;

end;

5. Поместите на форму компонент TEdit.
6. Откомпилируйте и запустите приложение и щелкните мышью на кнопке.

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

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

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

Ход работы:

1. Откройте проект RetResStorProc.dpr.
2. Добавьте к процедуре ButtonlClick вслед за строкой

with StoredProcl do begin

следующий код:

//Ввести значение параметра

paramByName('Tenant').AsString := Editl.Text;

3. Откомпилируйте приложение и введите фамилию арендатора,

Ввод  значения параметра хранимой процедуры

Разработаем приложение типа "главный-подчиненный", т. е. когда свойство Params компонента TQuery не задано ни во время разработки приложения, ни в процессе его исполнения, и оно определяется по данным свойства DataSource. Это свойство используют для связи с другим набором данных. Таким набором может быть TTаblе, TQuery или другой потомок TDataSet. По значениям свойств SQL компонентов TQuery заданных наборов данных определяют одинаковые поля. Если такие есть, то связь наборов данных устанавливают по этим полям.

Ход работы:

1. Создайте процедуру выбора Get_whoThatRent, которая отвечает на вопрос: "Кто что арендует?"

Код скрипта WhoThatRentsql

CREATE PROCEDURE Get_WhoThatRent(Tenant CHAR(10))

RETURNS (Lease ID SMALLINT, Rent Тур CHAR(20), Rent_Rn NUMERIC(6, 2))

AS

BEGIN

FOR SELECT Lease.NLease, Rent.Тур, Rent.Rn

FROM Tenant, Lease, Realty, Rent WHERE (Tenant.Tn = :Tenant)

AND (Lease.NTn = Tenant.NTn)

AND (Realty.AdR = Lease.AdR)

AND (Rent.Тур = Realty.Тур) INTO :Lease_ID, :Rent_Typ, :Rent_Rn DO

SUSPEND;

END

2. Поместим на форме компонент TTаblе, ассоциировав его с таблицей Tenant для того, чтобы обратиться к данной процедуре из клиентского Delphi-приложения.

 

3. Для передачи текущего значения поля тп в хранимую процедуру как входного параметра активизируйте вкладку Events в Object Inspector и определите обработчик события Afterscroii, как показано ниже:

Queryl.Close;

Queryl.ParamByNameCPrm').Value:= Tablel.FieldByName('Tn').Value;

Queryl.Open;

4. Поместите компонент TDataSource и установите свойство DataSet равным Tablel.
5. Расположите компонент TDBGrid и установите свойство DataSource равным DataSourcel.
6. Для визуализации набора данных, который возвращает хранимая процедура Get_WhoThatRent, поместите компонент TQuery.
7. Активизируйте Object Inspector и:

 

• установите свойство DatabaseName равным clsrvRealt, a DataSource - DataSourcel;
• определите свойство sql, как обращение к хранимой процедуре:

SELECT  *   FROM Get_WhoThatRent(:Prm)

8. Поместите   компонент   TDataSource   и   назначьте   значение   свойства DataSet равным Queryl.
9. Добавьте компонент TDBGrid. Его свойство DataSource установите равным DataSource2.
10. Установите значение свойства Active компонента TTаblе равным True.
11. Откомпилируйте приложение и убедитесь, что подчиненный набор данных TQuery соответствует данным таблиц Tenant, Lease, Realty, Rent.

Возвращение набора данных

 

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

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

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

• Сортировка набора данных

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

Сортировка наборов  данных Table выполняется автоматически по текущему индексу. При смене индекса происходит автоматическое переупорядочивание записей. Таким образом, сортировка возможна по полям, для которых создан индекс. Для сортировки по нескольким полям нужно создать индекс, включающий эти поля. Направление сортировки определяет параметр ixDescending текущего индекса.

Список полей, по которым  выполняется сортировка наборов  данных Query, указывается в операнде ORDER BY. Поля в списке обозначаются именами или номерами, которые соответствуют номерам в списке полей после слова SELECT. По умолчанию сортировка происходит в порядке возрастания значений полей. Для указания обратного порядка сортировки по какому-либо полю нужно указать после имени этого поля описатель DESC.

Задание: Выполнить сортировки в таблицах базы данных Exmpl.gdb:

Таблица	Поле	Порядок сортировки
Owner	Ow	По убыванию
	NOn	По убыванию
Lease	Ldate	По возрастанию
Realty	Тур	По алфавиту
Rent	Rn	По возрастанию

• Поиск записей

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

При организации поиска записей важное значение имеет наличие  индекса для полей, по которым  ведется поиск. Индексирование значительно  повышает скорость обработки данных, кроме того, ряд методов может  работать только с индексированными полями. К средствам поиска можно отнести методы Locate, Lookup, FindFirst, FindLast, FindNext и FindPrior, осуществляющие переход на записи, удовлетворяющие условиям фильтра, а также просмотры и курсоры.

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

Для создания просмотра применяют команду:

CREATE VIEW имяПросмотра [(CTon6eu_view [,столбец_у!ew ...])]

AS <onepaTop_select> [WITH CHECK OPTION];

• (cтол6ец_view [,столбец_view...]) — необязательный список столбцов;
• <оператор_select> — критерий выбора строк, которые будут включены в просмотр
• with check option — предотвращение ввода записей, не удовлетворяющих критерию выбора для обновляемых просмотров.

Создадим  просмотр rent_cost, который позволит просмотреть данные таблицы Rent.

1. Запустите утилиту Interactive SQL командой Tools/Interactive S QL главного меню IBConsole.
2. Используя меню Query/Execute создайте просмотр rent_cost командой:

CREATE VIEW  RENT_COST

AS SELECT  Тур, Rn FROM Rent

3. Закройте утилиту.
4. Инициировать просмотр из клиентской части системы:
1. Начните новый Delphi-проект.
2. Используя Form Wizard создайте клиент-серверное приложение на основе компонента TTable, алиаса clsrvExmpl и "таблицы" rent.

 

 

Выбор просмотра RENT_COST

 

Поскольку просмотр — это  виртуальная таблица, то можно модифицировать ее данные. Однако это возможно, если запрос соответствует трем условиям:

• просмотр должен формироваться из записей только одной таблицы;
• в просмотр должен быть включен каждый столбец таблицы, имеющий атрибут not null;
• SELECT-оператор просмотра не содержит подзапросы, агрегатные функции, зарезервированное слово distinct, предложение having, хранимые процедуры и функции, определенные пользователем.

Такой запрос называют модифицируемый (updatable). Если в определении просмотра нарушено хотя бы одно из выше перечисленных условий, то он — не модифицируемый (read-only).

 

rent_cost —. модифицируемый запро. Чтобы убедиться, можете переоценить стоимость аренды однокомнатных квартир.

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

UPDATE RENT_COST SET Rn = 70 WHERE Rn = 60

2. Чтобы изменения вступили в силу, выберите команду Transactions/Commit.

• Пример не модифицируемого просмотра.

1. Создайте не модифицируемый просмотр realty_cost, используя следующую команду:

CREATE VIEW REALTY_COST AS

SELECT ADR, RN

FROM REALTY, RENT

WHERE RENT.TYP = REALTY.TYP

2. Чтобы убедиться, что просмотр realty_cost не модифицируемый, исполните следующую SQL-команду:

UPDATE REALTY_COST SET RN = 60 WHERE RN = 70

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

SELECT  *  FROM  REALTY_COST

• Поиск в наборах данных

Для поиска записей по полям служат методы Locate и Lookup, причем поля могут быть неиндексированными. Функция Locate (const KeyFields: String; const KeyValues: Variant; Options: TLocateOptions): Boolean ищет запись с заданными значениями полей. Если удовлетворяющие условиям поиска записи существуют, то указатель текущей записи устанавливается на первую из них. Если запись найдена, функция возвращает значение True, в противном случае — значение False. Для поиска в наборе данных также используется функция Lookup(const KeyFields: String; const KeyValues: Variant; const ResultFields: String): Variant осуществляет поиск записи, удовлетворяющей определенным условиям, но, в отличие от метода Locate, не перемещает указатель текущей записи на найденную запись, а считывает информацию из полей записи.

• Поиск по индексным полям

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

1. FindKey, SetKey, EditKey и GotoKey - поиска на точное соответствие;
2. FindNearest, SetNearest, EditNearest и GotoNearest, - частичное совпадение заданных для поиска значений и значений полей записей.

Задание: Выполнить поиск информации об объекте и владельцах недвижимости в таблицах базы данных Exmpl.gdb. Проверить информацию об арендованных объектах недвижимости на введенную дату.