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

• Фильтрация записей

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

• Фильтрация по выражению

При использовании фильтрации по выражению  набор данных ограничивается записями, удовлетворяющими выражению фильтра, задающему условия отбора записей. Достоинством фильтрации по выражению  является то, что она применима  к любым полям, в том числе к неиндексированным. В связи с тем, что в процессе отбора просматриваются все записи таблицы, фильтрация по выражению эффективна при небольшом количестве записей. Для задания выражения фильтра используется свойство Filter: string. Если выражение фильтра не позволяет сформировать сложный критерий фильт-рации, то можно использовать обработчик события OnFilterRecord. Для активизации и деактивизации фильтра применяется свойство Filtered: boolean. Параметры фильтрации задаются с помощью свойства FilterOptions:TFilterOptions. Это свойство принадлежит к множественному типу и может принимать комбинации двух значений:

1. focaseinsensitive — регистр букв не учитывается.
2. foNoPartiaicompare — выполняется проверка на полное соответствие содержимого поля и значения, заданного для поиска.

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

• Фильтрация по диапазону

Для включения и выключения фильтрации по диапазону применяются методы ApplyRange - активизирует фильтр и CancelRange – деактивизирует. Методы SetRangeStart и SetRangeEnd устанавливают нижнюю и верхнюю границу диапазона, соответственно. Для изменения предварительно установленных границ диапазона предназначены методы EditRangeStart и EditRangeEnd. Когда одна из границ диапазона не задана, то диапазон открыт, т. е. нижняя граница становится равной минимально возможному, а верхняя граница — максимально возможному значению этого поля. Если фильтрация выполняется одновременно по нескольким полям, то после вызова методов SetRangeStart или SetRangeEnd должны стоять несколько операторов присваивания, каждый из которых определяет границу по одному полю. Предварительно в качестве текущего должен быть установлен индекс, построенный по этим полям.

Задание:

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

 

 

Лабораторная №12, №13

Работа с транзакциями. Кэширование изменений при работе с транзакциями.

Цель  работы: .научится создавать кэш и управлять им; грамотно использовать свойства, события и методы компонентов для работы с транзакциями и кэшем; научится создавать транзакции и управлять ими

• Pеализация механизма транзакций

Механизм транзакций используется для поддержания целостности  БД: транзакция переводит БД из одного целостного состояния в другое. Транзакция может быть явной и неявной. Неявная транзакция запускается и завершается автоматически, явной транзакцией управляет программист. Для модификации данных в удаленной базе может использоваться SQL-запрос, выполняемый с помощью метода ExecSQL компонента Query. Такой запрос называют PassThrough SQL, его выполнение приводит к запуску неявной транзакции. Способ взаимодействия с сервером на уровне такой транзакции определяет параметр sqlpassthru mode псевдонима БД или драйвера InterBase, который может принимать следующие значения:

1. shared autocommit — операторами модификации БД, например, update или insert, автоматически запускается неявная транзакция (по умолчанию); после внесения изменений эта транзакция автоматически подтверждается; разные транзакции могут использовать общее соединение с БД;
2. shared no autocommit — операторами модификации БД также автоматически запускается неявная транзакция, но автоматического ее подтверждения не происходит, и нужно самостоятельно выполнять оператор commit; разные транзакции могут использовать общее соединение с БД;
3. not shared — транзакции должны использовать различные соединения с БД и подтверждаться выполнением оператора commit. Возможность явного управления транзакциями предоставляет язык SQL сервера, который имеет в своем составе следующие операторы:

• set transaction — начать транзакцию;

• commit — подтвердить транзакцию;
• rollback — отменить транзакцию.

Оператор  запуска явной транзакции имеет формат:

SET TRANSACTION

[READ WRITE | READ ONLY]

[WAIT | NO WAIT]

[[ISOLATION LEVEL]

{SNAPSHOT [TABLE STABILITY] | READ COMMITED}]

[RESERVING <Список таблиц>

[FOR [{SHARED | PROTECTED}]

[{READ | WRITE}]];

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

read write — разрешены чтение и модификация записей (по умолчанию);

read only — разрешено только чтение записей.

Поведение в случае конфликта  транзакций при обновлении записей:

wait — ожидание завершения другой транзакции (по умолчанию);

no wait — прекращение данной транзакции.

Уровень изоляции от других транзакций (операнд isolation level):

snapshot — чтение данных в состоянии на момент начала транзакции (по умолчанию); изменения, сделанные другими транзакциями, в данной транзакции не видны;

snapshot table stability — предоставление транзакции исключительного доступа к таблицам; другие транзакции могут читать записи из таблиц;

read commited — чтение только подтвержденных изменений в записях; если изменения еще не подтверждены, то читается предыдущая версия записи.

Блокирование таблиц, указанных  в списке операнда reserving, для других транзакций:

protected read — разрешено только чтение записей;

protected write — для транзакций с уровнем изоляции snapshot или read committed разрешено только чтение записей;

shared read — разрешены чтение и модификация записей;

shared write — разрешено чтение записей;

Для транзакций с уровнем изоляции snapshot или read committed разрешена модификация записей. Действие операторов commit и rollback по утверждению и отмене транзакции аналогично действию одноименных методов компонента Database.

• Использование механизма кэшированных изменений

Механизм кэшированных изменений заключается в том, что на компьютере клиента в кэше (буфере) создается локальная копия  данных, и все изменения в данных выполняются в этой копии. Для  хранения локальной копии используется специальный буфер (кэш). Сделанные изменения можно утвердить, перенеся их в основную БД, хранящуюся на сервере, или отказаться от них. Этот механизм напоминает механизм транзакций, но, в отличие от него, снижает нагрузку на сеть, т. к. все изменения передаются в основную БД одним пакетом. Механизм кэшированных изменений реализуется в приложении, для чего компоненты, в первую очередь Database, Table и Query, имеют соответствующие средства. Кроме того, механизм кэшированных изменений поддерживается предназначенным для этого компонентом UpdateSQL. Основные достоинства рассматриваемого механизма проявляются для удаленных БД, но его можно использовать и при работе с локальными БД.

Контрольное задание:

1. Используя компонент TIBTransaction с панели инструментов Interbase создать приложение, позволяющее измененять информацию об объекте недвижимости.
2. Создать запросы на изменение информации в базе данных Exmpl.gdb, используя компонент UpdateSQL

 

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

Обеспечение достоверности данных и перехват исключительных ситуаций.

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

 

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

Исключение  создают оператором: CREATE EXCEPTION ИмяИсключения   "Сообщение"!;]

Примечание Длина сообщения не должна превышать 78 символов.

Для генерации  исключения в процедуре или триггере используют команду: EXCEPTION ИмяИсключения

Когда исключение сгенерировано:

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

Ход работы:

1. Запустите утилиту Interactive SQL.
2. Исполните SQL-команду:

CREATE EXCEPTION  IMPOSSIBLE_Rn  "Арендная плата д.б. > О"

Совет :   Не пытайтесь просмотреть результат, чтобы убедиться, что исключение создано. Как показано на рис. 18.8, проблемы IBConsole с русской кодировкой вам этого не позволят.

Ошибка вывода русскоязычного сообщения IBConsole

3. Создайте процедуру update__rn. Ее код приведен ниже:

 

CREATE PROCEDURE UPDATE_RN(inpRn NUMERIC(6, 2)}

AS

BEGIN

IF(:inpRn < 0) THEN EXCEPTION IMPOSSIBLE_Rn;

WHEN EXCEPTION IMPOSSIBLE_Rn DO EXIT;

/* вместо WHEN…DO можно ELSE */

UPDATE Rent SET Rn = :inpRn;

SUSPEND;

END

 

Примечание: Как видите, данная процедура позволяет изменить значение поля Rn таблицы Rent. Если вводимое значение Rn отрицательное число, то процедура генерирует исключение impossible_rn.

4. Убедитесь в ее работоспособности, исполнив следующую SQL-команду: EXECUTE PROCEDURE UPDATE_RN (-33)

Сообщение о генерации исключения

Контрольное задание: Выполнить обработку события проверки существования таблиц базы данных EXMPL.gdb.

 

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

Работа с отчетами.

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

• Разработка меню
1. Начните новый Delphi-проект.
2. Измените СВОЙСТВО формы Name на fmMenu, a Caption на Аренда.
3. Поместите на форму компонент TMainMenu.
4. Откройте Menu Designer, для чего выберите компонент меню на форме, щелкните на нем дважды мышью и создайте меню.
5. Чтобы убедиться в работоспособности меню, откомпилируйте его модуль.
6. Сохраните проект как Exmpl.dpr, а модуль — Menu.pas.

Форма меню информационной системы "Аренда Minus"

• Печать договора

Пусть текст  договора будет таким, как показано на рисунке.

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

 

Текст договора

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

1. Используя скрипт GET_LEASE_DATS.sql создайте процедуру выбора.

Код скрипта GET_LEASE_DATS.sql

SET TERM ^;

CREATE PROCEDURE GET_LEASE_DATS (TN_Name CHAR(10))

RETURNS (L_Tn CHAR(10), 0_0w CHAR(10), L_LDate TIMESTAMP, Rl_Typ CHAR(20),

L_AdR CHAR(20), Rl_Rn NUMERIC(6, 2), L_AdT CHAR(20), 0_AdO CHAR(20))

AS

BEGIN

FOR SELECT DISTINCT  L.Tn, O.Ow, L.LDate, Rl.Тур, L.Adr, Rl.Rn, L.AdT, O.AdO

FROM Lease L, Owner 0, Realty R, Rent Rl

WHERE (L.Tn = :Tn_Name) AND (O.Non = L.Non) AND (R.AdR = L.AdR)

AND (Rl.Typ = R.Typ)

INTO :L_Tn, :0_0w, :L_LDate, :Rl_Typ, :L_Adr, :Rl__Rn, :L_AdT, :0_AdO

DO SUSPEND;

END ^

commit;

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

 

2. Начните новый Delphi-проект.
3. Поместите на форму компоненты TLabel, TEdit, TButton и TDBGrid,
4. Присвойте СВОЙСТВУ Name формы значение fmFindLease.

 

 

 

Форма поиска

 

5. Создайте модуль данных и назначьте свойства компонентов

 

Модуль  данных

 

Компонент	Свойство	Значение
TDatabase	DatabaseName	C:\dbApp\clSRV\Exmpl\ Exmpl.gdb
	Name	dbExmpl
	Params	User Name=SYSDBA PASSWORD=masterkey
	LoginPrompt	False
	Connected	True
TQuery	Name	quLsQwRRn
	DatabaseName	dbExmpl
	SQL	Select * from GET_Lease_Dats(:NameTn)
TDataSourse	DataSet	quLsQwRRn
	Name	dsLsQwRRn
TIBTransaction	Name	Tr_actExmpl
	DefaultDatabase	dbExmpl

6. Создайте папку Print и сохраните в ней (File/Save As) модуль данных dmPrn и форму fmFindLease под именами dmPrnU.pas и Find.pas, соответственно.
7. Добавьте имя модуля данных в секцию implementation модуля Find (File/Use Unit).
8. Назначьте  значение свойства  DataSource компонента  TDBGrid  равным dmPrn.dsLsOwRRn.
9. Определите значение свойства Name кнопки Поиск равным btFind и поставьте в соответствие ее событию OnClick процедуру: