ПЕК НАУ

Постановка задачі

Розробити базу даних (БД) за учбовою темою «Зарплата», передбачивши в ній декілька таблиць, що зв’язані між собою відношенням «один до багатьох» форми для введення даних, звіти для виведення даних та запит щодо зарплати жінок. Відкриття форм, звітів та запиту оформити через кнопкові форми. Розрахункові поля забезпечити макросами або процедурами. Звіт побудувати групуванням за одним з полів і сортуванням за іншим полем.

Структура бази даних

Серед довідкових таблиць зупинимось на двох: «Посади» та «Працівники». Передбачимо ще таблицю облікового характеру «Зарплата». Нехай ці таблиці містять поля та пов’язані між собою так, як це зображено на наступній схемі, де символом  позначено ключове поле:

1 1  П ОСАДИ Посада Назва ТарифЗаГодину		ПРАЦІВНИКИ ТабНомер Прізвище Ім’я По-батькові Стать ДатаНародження Посада
	ЗАРПЛАТА ТабНомер КількістьГодин СумаНарахована Податок ВнесокПФ ВнесокФССбр ВнесокФССл СумаНаРуки

Рисунок 1. Структура БД «Зарплата»

Нехай типи полів та їх розмір(максимальна кількість символів для даних поля) будуть такими:

Таблиця 1. Основні параметри структури таблиць

Таблиця	Поле	Тип	Розмір
ПОСАДА	Посада	текстовий	3
	Назва	текстовий	40
	ТарифЗаГодину	грошовий
ПРАЦІВНИКИ	ТабНомер	текстовий	4
	Прізвище	текстовий	16
	Ім’я	текстовий	12
	По-батькові	текстовий	16
	Стать	текстовий, «Ч» або «Ж»	1
	ДатаНародження	дата
	Посада	текстовий	3
ЗАРПЛАТА	ТабНомер	текстовий	4
	КількістьГодин	числовий, цілий
	СумаНарахована	грошовий
	Податок	грошовий
	ВнесокПФ	грошовий
	ВнесокФССбр	грошовий
	ВнесокФССл	грошовий
	СумаНаРуки	грошовий

В деяких рядках таблиці 1 не вказаний розмір, тому що у MS Access він визначається автоматично. Наприклад, розмір поля типу дата дорівнює 8.

Припустимо такі правила обчислення розрахункових полів, вказуючи перед ними назву відповідної таблиці:

ЗАРПЛАТА.СумаНарахована — загальна сума нарахованої зарплати. Обчислюється добутком кількості відпрацьованих годин та тарифу оплати праці за годину.

ЗАРПЛАТА.Податок — прибутковий податок. Обчислюється за підсумовуючою шкалою в залежності від суми нарахованої зарплати, далі позначимо її через S (грн.), а саме, спочатку відкидаємо дробову частину S (тобто копійки), позначаємо результат через S, далі податок накопичується обчисленням процентів з урахуванням інтервалу, до якого належить S:

Таблиця 2. Шкала нарахування прибуткового податку

Інтервал, до якого належить S	%
[0;17]	0
(17;85]	10
(85;170]	15
(170;1020]	20
(1020;1700]	30
(1700;)	40

ЗАРПЛАТА.ВнесокПФ — внесок в пенсійний фонд. Обчислюється за шкалою в залежності від суми нарахованої зарплати S (грн.):

Таблиця 3. Шкала нарахування внесків у пенсійний фонд

Інтервал, до якого належить S	Внесок, грн.
[0;150]	S*1%
(150;1600]	S*2%
(1600; )	32

ЗАРПЛАТА.ВнесокФССбр — внесок у фонд соціального страхування по безробіттю — обчислюється за шкалою в залежності від суми нарахованої зарплати S (грн.):

Таблиця 4. Шкала нарахування внесків у фонд соціального страхування по безробіттю

Інтервал, до якого належить S	Внесок, грн.
[0;1600]	S*0.5%
(1600; )	8

ЗАРПЛАТА.ВнесокФССл — внесок у фонд соціального страхування по непрацездатності — обчислюється за шкалою в залежності від суми нарахованої зарплати S (грн.):

Таблиця 5. Шкала нарахування внесків у фонд соціального страхування по непрацездатності

Інтервал, до якого належить S	Внесок, грн.
[0;150]	S*0.25%
(150;1600]	S*0.5%
(1600; )	8

ЗАРПЛАТА.СумаНаРуки — заробітна плата, яку працівник отримує через касу. Обчислюється як різниця між загальною нарахованою зарплатою S та сумою податків і внесків.

Побудова звіту здійснюється групуванням за полем Посада та сортуванням за полем Прізвище.

Розв’язання задачі

Побудова алгоритмів

Загальна сума нарахованої зарплати обчислюється добутком, тобто:

ЗАРПЛАТА.СумаНарахована=ЗАРПЛАТА.КількістьГодин*ПОСАДИ.ТарифЗаГодину

Якщо позначити функцію, що відокремлює цілу частину, через int, функцію, що знаходить максимум серед двох чисел, через max, функцію, що знаходить мінімум серед двох чисел, через min, то алгоритм знаходження податку P буде таким:

S = int(S)	(1)
P = min(85-17,max(S-17,0))*10/100+min(170-85,max(S-85,0))*15/100+min(1020-170,max(S-170,0))*20/100+min(1700-1020,max(S-1020,0))*30/100+max(S-1700,0)*40/100	(2)

Алгоритм знаходження внесків у пенсійний фонд і фонд соціального страхування — відповідно Впф , Вфссбр , Вфссл — буде таким:

Якщо S<=150

то Впф=S*1/100

інакше Впф=min(S*2/100,32)

КінецьЯкщо

Вфссбр=min(S*0.5/100,8)

Якщо S<= 150

то Вфссл =S*0.25/100

інакше Вфссл=min(S*0.5/100,8)

КінецьЯкщо

Якщо позначити через if функцію, яка знаходить значення в залежності від значень умови: істина або хибність, то алгоритми для знаходження внесків Впф, Вфссл перепишуться більш компактно:

Впф = iif( S <= 150 , S*1/100 , min(S*2/100,32))	(3)
Вфссл = iif( S <= 150 , S*0.25/100 , min(S*0.5/100,8))	(4)

Що стосується реалізації алгоритму в середовищі MS Access, то необхідно відмітити відсутність там функцій знаходження максимуму та мінімуму декількох чисел. Це означає, що дані функції необхідно самостійно побудувати. Їх вигляд у мові VBA такий:

Private Function max(a, b) max=if(a >= b, a, b) End Function	(5)
Private Function min(a, b) min = iif(a >= b, b, a) End Function	(6)

Зауваження: визначені вище функції max та min за своєю назвою, але не за своїм змістом!, є в середовищі MS Access — це функції знаходження максимального або мінімального значення виразу, який обчислюється на множині даних записів таблиць.

Побудова нової бази даних

Запуск MS Access здійснюється або через меню персонального комп’ютера, або, якщо це можливо, через піктограму на робочому столі Windows. Нижче рисунок 2 показаний вигляд першого вікна MS Access:

Рисунок 2. Перше діалогове вікно MS Access

У цьому вікні необхідно визначитись: будувати нову базу даних або відкрити вже існуючу. У свою чергу побудова нової бази даних може здійснюватись безпосередньо користувачем (радіокнопка «Новая база даных» або за допомогою майстра: радіокнопка «Запуск мастера».

Якщо база даних вже існує, то необхідно вибрати її із списку останніх БД, що були в роботі, або вибрати рядок «Другие файлы…» та знайти базу даних, використовуючи стандартне вікно відкриття файлу Windows.

В останньому випадку або у випадку побудови нової бази даних необхідно вказати пристрій, папку та назву файлу бази даних. За угодою файл бази даних отримує назву db1, db2, db3, … і т. п., а розширення — mdb. Нехай назва нашого файлу бази даних буде ПРИКЛАД.mdb.

Наступне вікно рисунок 3 містить декілька закладок для опрацювання об’єктів бази даних Таблицы, Запросы, Формы, Отчеты, Макросы і Модули. Для побудови таблиць необхідно розпочати з закладки Таблицы.

Рисунок 3. Вікно бази даних MS Access

Побудова таблиць

Перш за все, необхідно побудувати таблиці, поля яких не містять підстановки значень полів з інших таблиць. Такою в нашому прикладі є таблиця ПОСАДИ. Натиснувши кнопку Создать, отримуємо вікно нової таблиці, де можна вибрати один з декількох режимів побудови таблиці. Виберемо режим Конструктор.

Рисунок 4. Вікно вибору режиму створення таблиць

Наступне вікно рисунок 5 містить три стовпчика для введення назви поля, типу даних поля та приміток.

Рисунок 5. Вікно визначення структури таблиці в режимі конструктора

В нього необхідно ввести розроблені вище поля та їх типи. Для ключових полів треба відмітити їх, увімкнувши піктограму з ключем, що знаходиться на панелі конструктора таблиць (повторне вмикання піктограми знімає ознаку ключового поля).

Для таблиці ПОСАДИ ключовим є поле Посада. В нижній частині вікна побудови таблиці в закладці Общие необхідно ввести розмір поля. Так, для поля Посада розміром поля буде 3. Після введення всіх полів та їх розмірів необхідно зберегти структуру таблиці ПОСАДИ.

Система пропонує за угодою назву таблица 1, а користувач дає свою назву: в даному випадку — посади. Нарешті, закривши вікно, бачимо, що у списку об’єктів, який спочатку був порожнім, зявилось ім’я посади.

Якщо необхідно повернутися до вікна побудови структури таблиці (щоб виправити помилки або переглянути структуру таблиці), треба вибрати у вікні бази даних об’єкт посади та натиснути кнопку Конструктор.

Дещо більше роботи з наступною таблицею ПРАЦІВНИКИ. Крім дій, що були в попередній таблиці, тут ще необхідно забезпечити підстановку значень у поле Посада. Це легко зробити за допомогою майстра підстановок, виклик якого здійснюється в розділі типів даних вікна визначення структури таблиці.

Майстер підстановок зробить свою справу за декілька кроків діалогу з користувачем:

Перший крок

Рисунок 6. Вибір способу створення стовпчика підстановки

В цьому вікні на рисунку 6, якщо опрацьовується підстановка поля Стать, необхідно вибрати радіокнопку буде введено фіксований набор значений для подальшого забезпечення підстановки значень «Ч» та «Ж».

Другий крок

Рисунок 7. Вибір значень для стовпчиків підстановки

Вибравши кількість стовпчиків — 1 рисунок 7, введемо значення «Ч» та «Ж» в стовпчику №1 відповідно в першому та другому рядках.

Третій крок

Рисунок 8. Вибір назви для стовпчика підстановки

На цьому кроці рисунок 8 необхідно погодитись з назвою підпису, або змінити цю назву, та завершити весь процес створення підстановки.

Якщо опрацьовується підстановка поля Посада, то на першому кроці рисунок 6 необхідно вибрати радіокнопку столбец подстановки использует значения из таблицы или запроса. В цьому випадку другий крок і всі наступні будуть такими:

Другий крок

Вибираємо таблицю, поля якої дають дані для підстановки рисунок 9. В нашому прикладі такою таблицею є таблиця ПОСАДИ.

Третій крок

На цьому кроці рисунок 10 з вікна Доступные поля у вікно Выбранные поля необхідно перемістити ті поля, які в майбутньому допоможуть правильно вибрати той чи інший запис. Так, у прикладі можна обмежитись полями Посада та Назва.

Четвертий крок

У цьому вікні рисунок 11 за угодою вимикач Скрыть ключевой столбец (рекомендуется) увімкнуто, але це далеко не завжди зручно. Досить часто є необхідність у візуалізації значення ключового поля.

Рисунок 9. Вибір таблиці з даними для підстановки

Рисунок 10. Вибір полів із значеннями

П’ятий крок

Вибір падає переважно на ключове поле, назва якого може зявитись в процедурах рисунок 12. Таким чином, вибравши у прикладі поле Посада, можна зробити останній крок, який за змістом такий самий, як і у випадку підстановки фіксованих значень, але з іншим значенням підпису.

Рисунок 11. Завдання ширини стовпчиків

Рисунок 12. Вибір поля для підстановки

Побудова схеми даних

Для забезпечення цілісності бази даних будуємо схему даних. Для цього, залишаючись в закладці Таблица, можна скористатися піктограмою схеми даних . Відкриється вікно схеми даних рисунок 13.

Якщо таблиць у вікні немає, їх можна вставити, використавши меню MS Access. У випадку, коли таблиця зайва необхідно виділити її, а потім видалити клавішею «Delete».

Рисунок 13. Вікно схеми даних

Якщо у схемі даних немає сполучень, то можна їх додати, протягнувши мишу від першого поля сполучення до другого. Наприклад, стати на поле посада таблиці ПОСАДИ і, утримуючи кнопку миші натиснутою, тягнути її до відповідного поля посада таблиці ПРАЦІВНИКИ.

Наслідком такої дії буде відкриття вікна сполучень. Якщо сполучення є, але лінії сполучень закінчуються крапками, то через властивості цих ліній також можна увійти у вікно сполучень рисунок 14:

Рисунок 14. Вікно визначення параметрів сполучень

В цьому вікні необхідно увімкнути Обеспечение целостности данных, що активізує ще два вимикача відносно каскадного оновлення та видалення даних. Їх, як правило, залишають вимкнутими. Вийти з вікна Связи можна через кнопку Создать.

Наслідком буде побудована схема даних. Для прикладу схема даних має наступний вигляд рисунок 15:

Рисунок 15. Схема даних БД «Зарплата»

На рисунку зображено зрозумілі позначення відношень «один-до-багатьох», які пов’язують таблиці в єдину цілісну базу даних.