Лекция № 25

Тема: «Обработка данных в отношении многие-ко-многим»

 

План

1. Отображения связанных таблиц в одном наборе данных.

2. Обновление одной записи в наборе данных.

3. Работа с таблицами в отношении «один-ко-многим».

 

1. Отображения связанных таблиц в одном наборе данных

Для оптимизации скорости обработки, данные в БД «нормализуют», что приводит к появлению нескольких таблиц, связанных друг с другом отношениями разных типов: один-к-одному, один-ко-многим, многие-ко-многим. Такое разбиение никоим образом не должно усложнять доступ к информации пользователю. Он должен видеть на экране информацию в обычном виде. Т.е. необходимо каким-то образом «собрать» информацию из нескольких таблиц в одном наборе данных и вывести для пользователя.

Для решения поставленной задачи будем использовать запросы по нескольким таблицам. Такой запрос в общем виде имеет вид:

 

SELECT список полей FROM таблица1

INNER JOIN таблица2

ON таблица1.поле = таблица2.поле

[WHERE условие]

 

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

 

таблица.имя_поля

 

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

 

Если нужно связать больше таблиц в одном запросе, то запрос записывается так:

 

SELECT список полей FROM таблица1

INNER JOIN (таблица2 INNER join таблица3

            ON таблица2.поле = таблица3.поле)

ON таблица1=таблица2

[WHERE условие]

 

Например. Пусть имеются 3 таблицы, показанные на схеме данных.

 

 

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

 

SELECT nazv_spec, nazv_grup, fam, imya, datar FROM spec

INNER JOIN (grup INNER JOIN stud

   ON grup.id_grup=stud.id_grup)

ON spec.id_spec=grup.id_spec

 

Если на форму нанести компонент ADOQuery и в свойстве SQL ввести данный запрос, то компонент отберет данные из 3 таблиц в один набор данных, который можно отобразить, например, в сетке DBGrid.

Для нашего примера пользователь увидит в сетке 5 колонок, при этом 1-я  принадлежит таблице spec, 2-я – таблице grup, остальные – таблице stud. Если пользователь начнет редактировать данные в колонке, то приложение отработает корректно, так как оно автоматически «знает» какие колонки в какие таблицы нужно записывать.

Однако, если пользователь попытается удалить строку в сетке, то появится сообщение об ошибке, которая вызвана тем, что программа не знает, из какой из 3-х таблиц, представленных в сетке, нужно удалить запись. Решением данной задачи является задание «главной таблицы» перед удалением. То есть, если нужно удалить запись о студенте из таблицы stud, данные о котором выведены в сетку через компонент ADOQuery (при этом в сетку также выведены название специальности и группа из 2-х других таблиц), то код удаления может иметь вид:

 

//задаем «главную» таблицу в запросе

ADOQuery1.Properties[‘Unique Table’].Value:=’stud’;

//удаляем запись из набора данных

ADOQuery1.Delete;

 

2. Обновление одной записи в наборе данных

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

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

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

 

//в качестве набора данных указана таблица ADOTable

//аналогично можно использовать компонент ADOQuery,

//если запрос в нем отбирает данные из одной таблицы

ADOTable1.UpdateCursorPos;

ADOTable1.Recordset.Resync(adAffectCurrent, adResyncAllValues);

ADOTable1.Resync([]);

 

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

 

//задаем дополнительные параметры перед обновлением

//задаем «главную» таблицу в наборе данных

ADOQuery1.Properties['Unique Table'].Value:='stud';

ADOQuery1.Properties['Update Resync'].Value:= adResyncAll;

//к тексту запроса добавляем условие в виде:

//ключевое поле «главной таблицы» = ?

ADOQuery1.Properties['Resync Command'].Value:= AdoQuery1.Sql.Text+' where id_stud=?';

 

//далее выполняем обновление записи как показано ранее

ADOQuery1.UpdateCursorPos;

ADOQuery1.Recordset.Resync(adAffectCurrent, adResyncAllValues);

ADOQuery1.Resync([]);

 

3. Работа с таблицами в отношении «многие-ко-многим»

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

Например, пусть имеются таблицы "Tovar" и "Client". Так как один товар могут купить несколько клиентов, а один клиент может купить несколько товаров, то такие таблицы необходимо связать соотношением "многие-ко-многим". Для этого создается вспомогательная таблица, например для учета продаж с указанием даты продажи и количества проданного товара под именем "Uchet". Схема БД показана на рисунке:

 

 

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

В этом случае таблицу tovar отображаем на форме с помощью компонента ADOTable. Данные о клиентах и продажах отображаем с помощью запроса по связанным таблицам вида:

 

SELECT client, data, kolvo

FROM client INNER JOIN uchet

ON client.id_client=uchet.id_client

AND uchet.id_tovar=:id_tovar

 

Форма просмотра может иметь вид:

 

 

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

В этом случае таблицу client отображаем на форме с помощью компонента ADOTable. Данные о товарах и продажах отображаем с помощью запроса по связанным таблицам вида:

 

SELECT data, kol, nazv_tovar

FROM tovar INNER JOIN uchet

ON tovar.id_tovar=uchet.id_tovar

AND uchet.id_client=:id_client

 

Форма просмотра может иметь вид:

 

 

Принцип работы с подчиненным запросом ничем не отличается от обычного подчиненного запроса:

- при переходе по главной таблице (событие OnAfterScroll компонента ADOTable) необходимо в подчиненный запрос в качестве значения параметра передавать ключевое поле из главной таблицы;

- перед удалением данных их подчиненного запроса необходимо указывать, какая из связанных таблиц в запросе считается «главной», см. выше ADOQuery1.Properties['Unique Table'].Value;

- при добавлении новой записи в промежуточную таблицу необходимо программно заполнять в новой записи связанные поля значениями ключевых полей из главных таблиц (в нашем примере поля id_tovar и id_client);

- после любого изменения данных (добавление, изменение, удаление) необходимо обновлять данные в подчиненном запросе методом Requery().