Гайдамакин Н.А. Автоматизированные информационные системы, базы и банки данных. Вводный курс Учебное пособие

Подождите немного. Документ загружается.

4.3. Обработка данных

2-й_таблицыОЛ'^имя_поля_соединения_1-й_таблицы=имя_по-

ля_соединения_2-й_таблицы». На рис.

4.15

приведен пример

реализации операций внутреннего, а также левого и правого

внешних соединений таблиц «Сотрудники» и «Исполнение» (до-

кументов) по полю «Фамилия».

На рис.

4.15

приведены также варианты построения

8^^-

инструкций, для реализации соответствующих запросов. Как

видно из рисунка, выбор пита соединения определяется целя-

ми дальнейшего использования результатов запроса.

Фаинлн я

Егорова

Ивэно в

Петсо в

Сидоров

Фетисов

Сотрудники

Должност ь

Секветао ь

Начальник

Инженер

Экономист

Контролер

Кабинет

101- а

101

110

105

Телефон

1 101

11 0

1 105

Псгюлнение

Рег.Н г

1123с

15/н с

15/с

22/3

23/2

И-13

Наниеновлнне

д-та

Заявка на

(финансировани е

Отчет за 1-й квартал

Справка по результатам проверки

Приказ

поощрении

Приказ

наказании

Приказ 0 назначении

Дата

15.02.98

30.03.98

10.02.93

01.01.98

02.01.9 3

01.01.98

Фамилия

Андреев

Сидоров

Седгее в

Иванов

Фактическое Исполнение

Я5'^,^?С7• Сотрудник

и.*.

Исполнсние.Рег_>Г5,

Исполнение.Наименован

ие_д-та.

Исполнение.

Дата

Я!ОМСотрудники

1Ш/ЕК

УО/?/Исполнение

О//Сотрудники.Фамилия

Исполнение.Фамилия;

Фамилия

Иванов

Сидоров

Должность

Начальник

Экономист

Кабинет

101

110

Телефон

1 101

1 110

Рег.П< 1

22/3

23/2

И-13

15/нс

Наименование д-та

Приказ

поошоени и

Приказ 0 наказании

Приказ 0 назначении

Отчет

за

1-й квартал

Дата

01, 9

02.01.9

01.01

30.03.9

Рис.

4.15,

а.

Левое

внешнее соединение

При внутреннем соединении целью является получение

новой таблицы с итоговыми данными по уже состоявшимся свя-

зям.

151

4. Ввод, обработка и вывод данных в

фактографических

АИС

Сотрудники и Исполнение

^5Б^^СТСотрудники.*,

атапнение.Рег_К»,

Исполнение.Наименование_д-та,Испспнение,Дата

Я7?ОМСотрудники1Й^Т^ОШИспопнение

ОГ/СсггрудннкиФамипил"

Испопнение.Фамипия ;

Фам

илня

Егорова

Иванов

Петров

Сидоров

Фетисов

Должность

Секретарь

Начальник

Инженер

Экономист

Контролер

Кабинет

101-а

101

110

105

Телефон

1 101

101

1 101

1 110

110

1 105

Рег.Н»

22/3

23/2

И-13

15/нс

Наименование

д-т а

Приказ 0 поощрении

Приказ 0 наказании

Приказ 0 назначении

Отчет за 1-й

1фартал

Дата

01.01.98

02.01.98

01.01.98

30.03.98

Рис.

4.15,

б. Левое

впешиее

соедииепие

Документы и Исполнение

^^Е1ЕСТНспаанаол1!.?ег_И1,

Иатапиение.Наименование_д-^а,

Испопнение.Дата,

Сотрудники.*

/^КОМ

Сотрудники

Я/СЯГ^ОЛ*/Испспнение

ОЯСотрудники.Фвмилия"

Испопнение.Фамипия;

РегЛ г

112ЭС

22/3

23/2

И-13

15/с

15/нс

Нлнменованне

документа

Заявка на

финансирование

Приказ 0 поощрении

Приказ 0 наказании

Приказ 0 назначении

Справка по результатам проверки

Отчет за 1-й квартал

Дата

15.02.98

01.01.98

02.01.98

01.01.98

10.02.98

30.03.98

Фамилия

Иванов

Сидоров

Должность

Начальник

Экономист

Каби-

нет

101

110

Теле-

фон

1 101

110

Рис. 4.15, в. Правое впешиее соедииепие

Внешнее соединение по смыслу направлено на создание

итоговой таблицы для просмотра и анализа состоявшихся и еще

несостоявшихся связей. При этом для левого внешнего объеди-

нения упор делается на анализ связей от первой таблицы (в

нашем случае от сотрудников, чтобы просмотреть и проанали-

зировать, кто и какие документы исполнил, а кто вообще не

исполнил ни одного документа). Иначе говоря, информация по

связям служит в качестве дополнительного аспекта, дополни-

тельной характеристики для записей левой таблицы. Для пра-

вого внешнего объединения упор делается на анализ связей от

второй таблицы (в нашем случае от «Исполнения», чтобы про-

смотреть и проанализировать, какие документы исполнены и

152

4.3. Обработка данных

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

«Сотрудники»).

В некотором смысле антиподом запросов на

соединение

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

запросом на поиск записей без подчиненных.

Поиск записей без подчиненных применяется для анализа

данных в связанных таблицах, когда связи в силу каких-либо

причин не состоялись. Реализуется данный вид запроса на ос-

нове запроса на левое (правое) внешнее соединение с дополни-

тельным условием отбора записей с пустыми значениями по

полю соединения в правой (левой) таблице. По сути, запрос на

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

внутреннее соединение. Примером запроса по поиску записей

без подчиненных, представленным на рис.

4.16,

является зап-

рос, строящий набор записей по таблице «Сотрудники», кото-

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

ненных записей в таблице

«Исполнение».

Запросы на соединение могут решать и более сложные ло-

гические информационные задачи по анализу связанных дан-

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

кого рода

запросов'-

можно привести следующий запрос по фор-

мированию набора записей сотрудников, командированных в

январе 1998 г. в организации г. Саратова со служебным задани-

ем «Сопровождение поставок», данные из которого выбирают-

ся из последовательно связанных отношением

«Од11Н-ко-мно-

гим» 4-х таблиц — «Сотрудники», «Командировки», «Пункт ко-

мандирования», «Задания»:

^^^^Е'СГСотрудники. *

/=7?ОМ

((Сотрудники

/Л^Л^^Л/О/Л'^Командировки

ОЛ'^Со-

трудники.

ФИО=Командировки.ФИО)

ШМЕК УО/ТУЗадания

Командировки.Служебное

задание =

Задания.Наименование)

ШМЕК /О/ТУ

Пункт командирования

ОТУ

Командировки. Пункт

комавдирования

Пункт командирования. Наименование

данном случае

д;и1еко

не самого сложного.

153

4. Ввод, обработка и вывод данных в

фактографических

АИС

Сотрудники

Фан

нлня

Егорова

Иванов

Иванова

Петров

Петрова

Сидоров

Сидорова

Фетисов

Должность

Секретарь

Начальник

Секретарь

Инженер

Секретарь

Экономист

Секретарь

Инженер

Кабинет

101- а

101

106

110

106

110

108

105

Телефо н

1 101

1 105

1 110

1 105

1 11 0

1 105

10 5

Исполнение

Рег.Кг

11/1

11/4

112/с

19/нс

21/1

21/нс

///-и

Наименование

д-т а

0 поощрении

0 наказании

0 работе с кадрами

0 сотрудничестве

0 состоянии НИР

0 состоянии труд

дисц .

0 поставках продукции

Об увольнении

Вид

документа

Приказ

Отчет

Письмо

Справка

Письмо

Приказ

Дата

01.01.9 8

02.01.9 8

01.01.3 8

18.03.9 8

17.03.98

01.01.9 8

19.03.98

10.01.3 8

Фамилия

Иванова

Петров а

Сидорова

Петрова

Иванова

Сидорова

Петрова

СЬтру^гаиШг

ие

исяалшишиа

^ок^лыито е

Фамилия

Иванов

Петров

Сидоров

Фетисов

Егорова

Должность

Начальник

Инженер

Экономист

Инженер

Секретарь

Кабинет

101

110

105

101-а

Телефо н

1 101

1 110

1 105

1 101

Поиск

записей

без подчиненных

ЗВ1ЖСТС а1рущааа1.Фа1лтия,

Сстрз'ДНИКи.Долхаюсть,

Сотрудники.Кабинет,

Сструдники.Тепефсн

^'Р0МСагрудникиX.ЕЯ7'/0ЖИспся^нение

О^/Сструтцопси.фаыкпия

•

Испшшенне.фамипия

ШИЕЯЕ

((Исишнение.Ф

амилих) /г

}кИ);

Рис.

4.16.

Пример запроса по поиску записей без подчиненных

УУНЕКЕ ((Пункт командирования. Город) - «Саратов»)

ЛМ)

((Задания.Наименование)

= «Сопровождение поставок») АЫО

((Командировки. Дата убытия) Ве(ш'еп П1/1/98ПАпсШ1/31/98#);

Запросы на объединение данных реализуют операцию

объединения реляционных таблиц и решают задачи создания

наборов данных, объединяющих однотипные по смыслу запи-

си (по группам однотипных полей) из нескольких таблиц. Стро-

ятся запросы на объединение через 5^^-инструкцию ЗЕЬЕСТ—

иЫЮМ 8ЕЬЕСТ. При этом запрос состоит из первой инструк-

ции 8ЕЬЕСТ, в которой перечисляются отбираемые поля и ус-

154

4.3. Обработка данных

ловия отбора записей из первой таблицы, и последующих ин-

струкций UNION SELECT, в которых

укс1зываются

отбираемые

поля и условия отбора записей из других таблиц. Обязатель-

ным условием является одинаковое количество отбираемых

полей в первой инструкции SELECT» последующих инструк-

циях UNION SELECT. При этом типы и длина полей в первой

инструкции и последующих инструкциях могут не совпадать.

При необходимости в итоговом наборе данных наименова-

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

после соответствующего поля в первой инструкции SELECT.

По умолчанию повторяющиеся записи не возвращаются,

но через использование предиката ALL после ключевого

слова

UNION

можио

обеспечить режим отбора всех, в том числе и

повторяющихся записей.

На рис.

4.17

приведен пример отбора и объединения дан-

ных из таблиц «Исходящие» и «Входящие» базы «Документо-

оборот» с целью формирования общего списка документов, по-

ступивщих после 1 декабря 1998 г., и документов, отправлен-

ных после 20 декабря 1998 г. В запросе первые и последние

поля переименованы, чтобы объединить смысл этих полей в

исходных таблицах.

4.3.2.1.3. Вычисления и групповые операции в запросах

Во многих случаях при формировании набора данных по

запросам на выборку требуется производить

определенные

вы-

числения или определенные операции по непосредственной об-

работке отбираемых данных. В реляционных

СУ

БД такие воз-

можности предоставляются через вычисляемые поля и группо-

вые операции в запросах над отбираемыми данными.

Вычисляемые

поля. В инструкции SELECT

списке отби-

раемых полей добавляется выражение, по которому вычисля-

ется новое поле, и посредством ключевого слова

-45

определя-

ется его имя в формируемом наборе данных. На рис.

4.18

при-

веден запрос, формирующий ведомость начислений

сотрудникам с вычисляемым полем «ИТОГО».

155

4. Ввод, обработка

вывод данных в

фактографических

АИС

Исходящие

Рег_1|!>

вход

223/ис

117/2 3

нн-534 3

Дата

10.11.98

15.12,98

20.12.98

Заголовок

конф

еренинн

по

эемноводны м

06 изменениях в номенклатуре

поставок

Рекламация по продукции

Отправитель

НИИ природы г,

Чеономорс к

ЗАО

ТлобалКомплекг"

г.

Уоюпинс к

000

"ТяжМаш"

п. Штауповщиков

Бхо»\ящил

Рег_11«

исход

2334

3435-12

Дата

25.12.9

18.12.9 8

Заголовок

06 оказании методической

помощи

По номенклатуре

поставок

АО

Глобал Комплект"

Адресат

Министерство "Науки и

помощи", г. Москва

АО

ТлобалКомплект"

г.

Урюпинс к

Запрос на

об-ьедгшение

данных.

ЗЕ1ЕСТРь7

Кэ

вхсдЛУРег

К'вход^иася,

Дата,

Загоповок.Отправитепь

Л50тправитель/Адресат

;ЯВОМВх

едящие

:»'ЯгЯЕ(Вхся1!щне.Дат|1>-«12Л1/58#)

УУ!0У1М.1

Ж1ВСГРег_№_исхсд,

Дета.Зм-оп1!е(ж,

Адресат

:^^^?0М Исходящие

^»'ЯгЯВ(1кхсдящие,Дата>-

ЙОЛ2/58#);

Локаченты.

полеченные

после

01.12.98

отпоавленные

после

20.12.98

Рег_11 «

>Х0Д,^|1СХ0 Д

Д.тта

Заголово к

Отправитель/Адресат

117/23

15.12.9 Об изменениях в номенклатуре

поставок

ЗАО

ТлобалКомплект",

г.Урюпинск

нн-5343 20.12.98 Рекламация по продукции

ООО'ТяжМаш",

п.Штамповщико в

2334 25.12.98 06 оказании методической

помош и

Министерство "Науки и помощи",

г. Мос>!в а

Рис.

4.17.

Пример запроса на объединение

Групповые операции. В процессе отбора и обработки дан-

ных важное значение имеют группирование данных по значе-

ниям какого-либо поля и осуществление тех или иных опера-

ций над сгруппированными записями. Групповые операции осу-

ществляются на основе

8^^-предложения

СКОПР

БУ в

сочетании со

статистическилш

функциями

8^^.

В большин-

стве диалектов языка

ЗР^

в состав инструкции

ЗЕЬЕСТдрщо-

кается включение статистических функций

8^^,

которые осу-

ществляют те или иные групповые вычислительные операции

над отбираемыми записями.

К числу статистических функций

8р^

относятся;

5'С/М(выражение)

— вычисляет сумму набора значений;

156

4.3. Обработка данных

Сотрудники

Таб

_Н«

Фамилия

Иванов

Петров

Сидоров

Егоров

Кузьмина

Имя

Иван

Петр

Сидоо

Егор

Ольга

Отчество

Иванович

Петрович

СиЛ0Р0ВН^

Егорович

Игоревна

Дол)кмость

Начальник

Инженер

Начальник

Секретарь

Оклад

ЮОо-

80р.

40р.

80р,

30р.

Персональная

Надбавка

ЮОо.

50р.

Ор.

30р.

150р.

Н<чцоавка

за

уч.степень

50о.

Ор.

20р.

40р.

Ор.

Ведомость начислений

Ж1^СГС

огрудгаоси

Согр5йгаши.Ога1а;г^"Согрудник11Персональна.ч_НадбаЕка

+С

ОГРУДНИКН.Н

адб

авка_з

а_уч_степень Л?

НТО

ГО

РЛОМ

отр^вики;

Таб

Фамилия

Имя

Отчество

Дол)кность

Оклад

Персональная

Надбавка

за уч.

ИТОГО

Иванов

Иван

Иванович

Начальник

100р

!00р

50р

250

Петров

Петр Петрович

Начальник

80р

50р

Ор

130р

Сидоров

Сидор

Сидорович

Инженер

40р

Ор

20р

60р.

Егоров

Егор

Егорович

Начальник

ЗОР

40о

150р

Кузьмина

Ольга

Игоревна Секретарь

ЗОР

150р

ОР

Рис.

4.18.

Приме/} запроса па выборку с

аычисляелшм

полем

КС(выражение)

— вычисляет

среднее

арифметическое на-

бора чисел;

Мш(выражение)

— вычисляет минимальное значение из на-

бора значений;

Мах(выражение) — вычисляет максимальное

значение

из

набора значений;

5'/^<^у(выражение)

— вычисляет

среднеквадратичное

от-

клонение набора значений;

Со№1/(выражение)

— вычисляет количество записей, содер-

жащихся в наборе;

К|Г/г(выражение)

— вычисляет дисперсию по набору значе-

ний.

К числу функций, используемых в групповых операциях,

относятся также функции

Р1г51(выражение)

^а5^(выраженис),

вычисляющие (возвращающие), соответственно, первое и пос-

леднее значения поля в наборе данных. В выражениях в

каче-

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

157

4, Ввод, обработка и вывод данных в фактографических АИС

Собственно сами групповые вычисления задаются посред-

ством включения в

Зр^-инструкцию

5^^^СГ

вычисляемого

поля на основе выражения со статистическими функциями, вы-

полняемыми над наборами данных, формируемыми предложе-

нием

СЛ

гТР

Г.

Для примера на рис.

4.19

приведен запрос, формирующий

итоговые данные по общей сумме премиальных каждого из пре-

мированных сотрудников. Группирование данных производит-

ся по полю «ФИО», т. е. все записи с одинаковыми значениями

поля «ФИО», объединяются в одну и дополнительно формиру-

ется вычисляемое поле «ИТОГО», рассчитываемое как сумма

сгруппированных в одну записей по произведению полей «Ок-

лад» и «Премия».

ФИО

ГРОЗНЫЙ

И.И

Грозный

И.И.

Грозный

И.И.

Гуманный АА.

Иванов

И.И,

Петров

П.П.

Почтенная

О.П.

Сидоров

С,

С.

Тишайшая

Т.Т.

Почтенная О.П.

Оклад

1ШР .

100р.

100Р.

99р.

70р.

60р.

90р.

80р.

28Р.

90Р.

|>«мня

200%

300%

100%

50%

100%

90%

100%

Премирование

Дата

01.01.93

01.02.93

01.03.93

10.01.98

10.01.93

10.01.98

01.03.93

10.01.98

01.02.98

01.01.93

Формулировка

За сложность и напряженность

За гуманизм в работе с кадрами

За творческий подход к делу

За служебное рвение

За сложность и напряженность

За проявленную принципиальность

За исполнительность'

За сложность и напряженность

Общий итог по прениям

5'г1.гСГПремии.ФИ0,Дт(Премш.0ы1ад*Преими.Прем1и)Л5 ИТОГО

ЯКОМПрении

СДОУР

ВГПралнн.ФИО.

ФИО

ГРОЗНЫЙ

И.

Гуманный АА

Иванов

И.И.

Петров

П.П.

Почтенная О.П.

Сидоров

С.С.

Тишайшая Т.Т.

ИТОГО

700о.

99р.

35р.

ЗОР.

1еОр.

72р.

25р.

Рис. 4.19.

Прюмер

запроса с групповой функцией

8ит

В некоторых СУБД в отдельный вид выделяются запросы

по поиску повторов, а также вводится специальная разновид-

158

4.3. Обработка данных

ность запросов на выборку в виде так называемых перекрест-

пых запросов.

Запросы по поиску повторов применяются для анализа на-

личия повторяющихся групп значений по определенному полю

и их количественных (статистических) данных. В качестве при-

мера на рис. 4.20 приведен запрос по поиску повторов в табли-

це сотрудники по полю «Должность», формирующий в итоге

штатную расстановку по заполненным должностям.

Фамилия

Егорова

Иванов

Иванова

Петров

Петрова

Сидоров

Сидорова

Фетисов

Сотрудники

Должность

Секретарь

Начальник

Секретарь

Инженер

Секретарь

Экономист

Секретарь

Инженер

К«|б||нет

101-а

101

106

110

106

110

106

110

Телефон

101

105

110

105

1 110

1 105

110

Поиск повторов

Зг1ЕСТОГЗГШСТЯОЯ'ЯпКДаажискть)

ЛУДОЕЖНОСТЬ,

СЪШ<ДОЛЖНОСТЬ )

АУКспичество

ЯКОМ Сотрудники

ОЯОиР

ВГДспжность

НА У1Ка Соиг^ф.

опжностъ)

Заполненные

долзкиоспш

подразделения

Должность

Начальник

Инженер

Экономист

Секретарь

Количество

Рис. 4.20. Пример запроса по поиску повторяющихся

зпачеиий

Более сложные статистические задачи решают

перекре-

стные запросы. Название «перекрестный» отражает принцип

формирования и представления результатов таких запросов.

На рис.

4.21

иллюстрируется принцип построения итого-

вой (сводной) таблицы перекрестного запроса. В исходной (ба-

зовой) таблице для перекрестного запроса выбираются два поля.

По повторяющимся значениям одного поля формируются на-

звания заголовков строк итоговой (сводной) таблицы — «боко-

159

4. Ввод, обработка и вывод данных в фактографических АИС

Ксошчество

и названия

строк

формируются

по\

повторяющимсяЗ

группам

одинаковых

значении в

перБсм

выдытенном

В ячейках

огражаютсярезупьтаты

статистических функции по

оотБ

ет сгвумщигл группам 3

наченин

Количество

инзовання

СТСпбцОБ

формируются

по повторяющимся

группам

одинаковых

значений во

втором

Бьп&ленном

попе

Рис.

4.2].

Принцип

(рорлшроваиш1

результатов перекрестного

запроси

вик» сводной таблицы. По повторяющимся значениям другого

поля образуются названия столбцов итоговой таблицы—«шап-

ка» сводной таблицы. В ячейках сводной таблицы отражаются

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

ких-либо полях исходной таблицы.

Для примера на рис. 4.22 представлен запрос по формиро-

ванию статистических данных о

количестве

исполненных раз-

личными сотрудниками документов с разнесением по видам

документов. Полем для формирования боковика определено

поле «Фамилия», полем для формирования шапки определено

поле «Вид документа». На рисунке представлен также вариант

8^^-инструкции,

реализующей данный

запрос" .

4.3.2.2. Запросы на изменение данных

Важное значение для решения различных техиологичес-

ких информационных задач по ведению базы данных имеют зап-

росы на изменение данных. В отличие от непосредственного

ввода данных в режимах открытой таблицы или формы они вно-

сят изменения сразу в группу записей за одну операцию. Таким

образом,/7сз>'льд?/й/77ож запросов на из.менение является не на-

диалекте

Зр/,

М5

Ассехя.

160