УПР 13. Тип связи МНОГИЕ-КО-МНОГИМ

Введение в проблему

Созданная нами ИС «Кадры» работает правильно, как мы убедились по предыдущим упражнениям, но не позволяет решать ряд управленческих задач. Например, она не может отслеживать документы о движении сотрудников по службе, т.е. приказы о переводе из отдела в отдел, повышении (или понижении) в должности, увольнении.

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

Если мы хотим решить эту задачу, то между таблицами Должности и Сотрудники обнаруживаются связи типа «Многие-ко-многим». Иными словами одну и ту же должность могут занимать многие сотрудники, и один сотрудник теперь может занимать последовательно разные должности. Между таблицами Отделы и Сотрудники также существует связь типа «Многие-ко-многим»: в отделе работает много сотрудников и сотрудник банка может поработать последовательно во многих отделах. Проблема состоит в том, что реляционная СУБД не поддерживает обеспечения целостности по связи типа «Многие-ко-многим». Действительно, попробуйте в рамках существующей схемы данных попытаться сохранить все должности, которые занимал сотрудник в течение его работы в АКБ «Буревестник»

93

Можно попытаться ввести в таблицу Сотрудники поля [Код_должности_1] [Код_должности_2] …[Код_должности_N]. Однако, что делать, если вдруг сотрудник перемещался по службе более N раз? Более того, чтобы эти коды были внешними ключами таблицы Сотрудники, нужно установить много связей между таблицами Сотрудники и Должности. Но этого не позволит сделать реляционная СУБД, так как она может следить за обеспечением целостности только одной связи между двумя таблицами. Поэтому для решения данной проблемы необходимо создать новую таблицу, в которой будет храниться информация о назначениях и перемещениях сотрудников. То есть нужно перестроить базу данных, из старой создать новую, внеся в нее необходимые изменения.

Для конкретизации задач, решаемых в новой БД, предположим, что нам нужно:

1.Сделать в Журнале кадровых приказов записи о приеме на работу всех имеющихся сотрудников с 01.01 текущего года.

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

3.Переместить на должность Главного управляющего Птичкину.

4.Переместить Сидорова с должности Помощника управляющего Валютным отделом на должность Управляющего того же отдела c 01.02.

5.Изменить структуру нашей организации и добавить отделы Бухгалтерия и Кадры.

6.Изменить штатное расписание и добавить должности Главного бухгалтера и Инспектора отдела кадров.

7.Принять на работу в эти отделы Зайцева и Волкова.

94

дующей структуры:

3.Удалите все оставшиеся после копирования БД связи в старой схеме данных: между таб-

лицами Отделы, Сотрудники и Должности. Для этого откройте Схему данных, выде-

лите курсором каждую связь и в контекстном меню выберите команду Удалить.

4.Добавьте в Схему данных таблицу Журнал приказов.

5.Удалите ненужные, уже имеющиеся в Журнале приказов, поля из таблицы Сотрудники:

внешние ключи [Код Должности] и [Код Отдела], а также поле [Надбавка].

6.Создайте новые связи между таблицами Журнал приказов и Сотрудники, Журнал приказов и Отделы; Журнал приказов и Должности на основе первичных (П.Кл) и

внешних ключевых (Вн.Кл) полей. Установите свойство «Обеспечение целостности дан-

96

ВНИМАНИЕпризаполненииЖурналаприказовнеобходимоучитывать,чтодатыприказовсогласованыи идутповозрастанию. Подсогласованностьюподразумевается, чтоперваязаписьонекоторомсотруднике– это запись о его приеме на работу и последующие записи о перемещениях имеют даты позднее даты его

97

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

12. Создайте простой запрос «Движение сотрудников», в который соберите из всех таблиц

98

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

13.Создайте новый запрос «Движение сотрудников с зарплатами» содержащий следующие поля:

[Код_сотрудника], [Фамилия], [Должность], [Отдел], [Тип Приказа], [Дата Приказа], [Надбавка] и [Оклад] Зарплата:=Вычисляемое поле (Постройте выражение)

14.Для определения количества принятых, переведенных и уволенных сотрудников создайте

взапросе «Движение сотрудников с зарплатами» три дополнительные колонки Приняты, Переведены, Уволены так, чтобы в каждой из них стояли 1, если [Тип Приказа] соответствует наименованию колонки, и 0, если тип приказа другой. Для данной задачи используйте в построителе условный оператор

->Встроенные функции/Управление/ IIf(«expr»;«truepart»;«falsepart»).

•Вычисляемое выражение Приняты: =IIF([Тип Приказа]=1;1;0)

•Вычисляемое выражение Переведены: =IIF([Тип Приказа]=2;1;0)

•Вычисляемое выражение Уволены: =IIF([Тип Приказа]=3;1;0)

15.Для определения сотрудников, работающих в АКБ «Буревестник» на текущую дату, создайте запрос «Список работающих», в котором отберите последние записи о каждом сотруднике ( группировка по [Код_Сотрудника], в остальных колонках функция Last) и исключите уволенных. Рассчитайте зарплату каждого работающего.

16.Создайте отчет с итогами «О текучести кадров отделах» на основе запроса «Движение

99

сотрудников с зарплатами» с группировкой по отделам и сортировкой по [Фамилии]. Итоги подведите суммированием значений в полях: Приняты, Переведены, Уволены.

17.Создайте отчет «Фонд оплаты труда» на текущую дату, используя в качестве источника запрос «Список работающих». Группировка по должностям, итоги по полю Зарплата. В заголовке отчета должна автоматически проставляться дата его составления

ДОПОЛНИТЕЛЬНО К УПР 13

Общее правило подготовки отчета – сначала сконструировать запрос, отбирающий нужную информацию, сортирующий ее по выбранному полю, производящий необходимые вычисления в дополнительных вычисляемых колонках запроса, и только затем строить на его основе отчет. Хотя MS Access позволяет строить отчет, оперируя полями, взятыми из таблиц, это все рано приводит к построению запроса, который, правда, скрыт от пользователя.

1.Постройте отчет о текучести кадров в определенном году. Он похож на запрос п.16 Упр. 13, однако, нужно обеспечить выборку записей Журнала приказов, относящихся только к определенному году, вводимому пользователем. Этот год должен отображаться в заголовке отчета.

2.Создайте новый запрос «Льготы по Зарплате сотрудников», скопировав запрос «Список сотрудников». В конструкторе добавьте в запрос следующие поля: [Дети] и [Пол]. Вычислите поле Льготы: IFF(AND([Дети]>0, [Пол]=женский); 13%[Дети]*[МРОТ];0). Это структура вычислений, а не готовая формула.

100

3.Создайте новый запрос «Адреса сотрудников» на основе запроса «Движение сотрудников» со следующими условиями для полей:

[Фамилия], [Имя]- не выводятся на экран, а используются для выражения Сотруд-

ник:=[Фамилия] +” ”+Left([Имя];1)+”.”

Возраст:=Вычисляемое поле (Вычисляет возраст), [Адрес], [Телефон домашний]. Откройте запрос для просмотра. Информация выводится

правильно, но записи о сотрудниках повторяются столько раз, сколько у него было переводов. Для устранения повторений введите группировку по полю [Фамилия], а для всех остальных полей определите в строке группировка функцию Last, которая выводит последнюю записью.

4.Постройте отчет на основании запроса «Адреса сотрудников». О сотрудниках должна выводиться только информация о Фамилии, инициалы, возраст и адрес. В итогах отчета должно указываться общее количество сотрудников в списке отчета.

101