Методичні рекомендації до лабораторних робіт для студентів спеціальності 186 «Видавництво та поліграфія» сторінка 2

15
Бланк конструктора таблиць по горизонталі розділений на дві части- ни. У верхній частині знаходяться три стовпці: «Ім'я поля», «Тип даних»,
«Опис». Стовпець «Опис» заповнювати не обов'язково.
Нижня частина вікна Конструктора таблиць призначена для визна- чення властивостей поточного поля (зокрема, на рис. 7 показані власти- вості поля «Ціна», тому що воно є поточним). Загальні властивості поля залежать від обраного типу даних.
Після введення імені поля в сусідньому стовпці Тип даних за замов- чуванням відображається тип даних Текстовий. Якщо в поле будуть збе- рігатися дані іншого типу, його потрібно вибрати зі списку (рис. 8).
Рис. 8. Вибір необхідного типу даних
У нижній частині бланка Конструктора таблиць встановлюють необ- хідні загальні властивості поля (зверніть увагу, що список властивостей залежить від типу даних).
Потім призначають первинний ключ. Для цього виділяють рядок з ім'ям поля, яке повинно бути ключовим, і натискають відповідну кнопку на панелі інструментів.
Відповідність записів визначається збігом значень пов'язаних полів.
Для кожної з пари пов'язаних таблиць розрізняють головну та під- порядковану таблиці. Головними в зв'язках роблять, як правило, довідкові таблиці, інформація яких змінюється порівняно рідко. Головні таблиці

16 бажано заповнювати першими. Підлеглі таблиці зазвичай зберігають часто оновлювані оперативні дані та використовують для заповнення даних у головних таблицях.
Найбільш поширеним типом зв'язку між таблицями є відношення
«один-до-багатьох». Це таке відношення, за якого одному запису головної таблиці відповідає до декількох записів підпорядкованої таблиці (причому ніякому запису підпорядкованої таблиці не повинно відповідати більше одного запису головної таблиці).
Установлення зв'язку між таблицями здійснюється у вікні «Схема даних» команди «Робота з базами даних» шляхом перетягування ключо- вого поля головної таблиці на відповідне поле підпорядкованої таблиці.
Приклад вікна схеми даних для БД «Борей» наведено на рис. 9.
Рис. 9. Схема даних БД «Борей»

17
Вікно «Схема даних» призначене для перегляду, створення та ре- дагування зв'язків між полями таблиць, а також для визначення власти- востей зв'язків між таблицями (рис. 10).
Рис. 10. Вікно «Змінення зв'язків» таблиць «Клієнти»
та «Замовлення» БД «Борей»
Наприклад, між таблицями «Клієнти» та «Замовлення» не встанов- лена властивість каскадного видалення пов'язаних записів. Тому видален- ня запису про певного клієнта з таблиці «Клієнти» не призводить до ви- далення відомостей про його замовлення з таблиці «Замовлення».
У табл. 1. наведено перелік можливих предметних областей для ін- дивідуальних БД студентів.
Таблиця 1
Предметні області створення індивідуальних БД
№ варіанта
Назва БД
Загальна постановка задачі
1 2
3 1
Автомайстерня
Облік виконаних ремонтних робіт в авто- майстерні для поточного та підсумкового аналізу роботи майстерні. Ведеться облік клієнтів і автомобілів

18
Продовження табл. 1 1
2 3
2
Мережа АЗС
Облік операцій з продажу різних видів палива в мережі АЗС. Ведеться облік поставок і поставників
3
Аптека
Облік операцій з реалізації медичних препаратів у роздрібній торгівлі в окремій аптеці міста. Ведеться облік поставок препаратів і поставників
4
Депозитні вклади клієнтів філії банку
Облік операцій обслуговування клієнтів.
Різні види вкладів. Списки боржників
5
Бібліотека
Облік операцій обслуговування читачів.
Ведеться облік читачів, літератури, а також роботи персоналу бібліотеки
6
Товарна біржа
Облік біржових операцій. У базі зберігаються дані про фірми-продавці, фірми-покупці, а також дані про брокерів, які обслуговують складені угоди
7
Сировинна біржа
Облік біржових операцій. В базі зберігаються дані про фірми-продавці, фірми-покупці, а також дані про брокерів, які обслуговують складені угоди
8
Відеотека
Оперативний облік роботи пункту прокату фільмів на різних носіях
9
Клієнти готелю
Оперативний облік обслуговування клієнтів готелю. Ведеться облік клієнтів, персоналу, номерів
10
Деканат. Успішність студентів одного курсу
Оперативний облік успішності студентів за дисциплінами одного курсу
11
Кадрова агенція
Ведеться облік резюме здобувачів
і заявок роботодавців, а також облік з виконаного підбору персоналу (укладені контракти)
12
Магазин «Книжковий світ»
Ведеться облік роздрібного продажу книжок у книжковому магазині. Необхідно мати інформацію про поставників
і поставки літератури різних жанрів
13
Меблі на замовлення
Види меблів, матеріалів, виробників, замовлення

19
Закінчення табл. 1 1
2 3
14
Заходи в організації
Заходи, співробітники, план проведення заходів
15
Обмін валют
Ведеться облік роздрібного продажу та купівлі валют у мережі пунктів обміну.
У базі фіксується кожна проведена операція з обміну валюти, а також поточний курс купівлі та продажу
16
Продаж авіаквитків
Рейси, пасажири, продаж білетів
17
Продаж залізничних квитків Ж/д маршрути, зони, продаж білетів
18
«Продсервіс» – продаж продуктів харчування за дисконтними картками
Робочі місця продавців (номер касового апарату), товари, клієнти, які мають дисконтну картку, фіскальні чеки (облік продажу)
19
Розклад занять у ВНЗ
Аудиторії, дисципліни, викладачі, групи, розклад
20
Склад – Облік матеріалу
Постачальники, споживачі, матеріали, облік прийому/відпуску матеріалів
21
Митниця – Ввезення автомобілів
Мита на марки автомобілів, громадяни, проходження митного контролю
22
Телеательє
Вид техніки, клієнти, облік ремонтних операцій
23
Туристична фірма
Опис турів, клієнти, продаж путівок
24
Облік робочого часу персоналу організації
Співробітники, таблиця реєстрації часу початку та завершення робочого процесу
25
Фірма з продажу комп'ютерів
Монітори, процесори, відеокарти, ПЗУ,
ОЗУ, формування конфігурації
26
Вибори
Виборчі дільниці, кандидати, результати голосування
27
Видавництво
Штат, заплановані видання, етапи підготовки до видання, виконання видавничого плану
28
Телестудія
Співробітники, опис проектів, виходи в ефір
29
Пошта. Підписка клієнтів на періодичні видання
Періодичні видання, передплатники, оформлення підписки
30
Поліклініка
Оперативний облік звернень пацієнтів до різних лікарів

20
Хід роботи
1. Виберіть тематику своєї БД за погодженням з викладачем – само- стійно або зі запропонованої табл. 1 з варіантами завдань.
2. Продумайте перелік вихідних документів і звітів.
3. Складіть перелік завдань, що вирішуються за допомогою даної БД.
4. З'ясуйте, які вихідні дані необхідні для вирішення поставлених завдань, які розрахунки необхідно виконати та за якими формулами.
5. Складіть перелік таблиць, що містять вихідні дані (не менше п'яти таблиць).
6. Продумайте структуру таблиць – склад полів, тип даних, діапазон можливих значень, ключові поля.
7. Накресліть на папері в звіті схему даних БД – таблиці зі складом полів і зв'язок між ними, визначте тип зв'язків.
8. Виконайте нормалізацію таблиць з метою уникнення дублювання даних.
9
. Узгодьте з викладачем склад таблиць та їх полів, ключові поля, зв'язки між таблицями.
10.
Створіть базу даних за своїм варіантом, необхідні таблиці, згідно з предметною областю, встановіть типи даних (лічильник, текстовий, число- вий і т. д.), опис та інші необхідні властивості полів (розмір поля, маску введення, підпис, значення за замовчуванням тощо) створених таблиць.
Визначте первинні ключі в створених таблицях.
11.
Визначте необхідні зв'язки між таблицями, задайте необхідні параметри забезпечення цілісності даних і вид об'єднання. Проаналі- зуйте й обґрунтуйте у звіті, які параметри потрібні для кожного зв'язку між таблицями.
12
. Заповніть створені таблиці даними (мінімум десять записів у кожній таблиці).
Контрольні запитання
1. Як ви розумієте термін «відношення» в реляційній БД?
2. Яким вимогам повинен задовільнювати первинний ключ таблиці?
3. Чи обов'язково повинні збігатися імена пов'язаних полів? Які властивості повинні бути однаковими у пов'язаних полів (у первинного
і у зовнішнього ключів)?

21 4. У чому полягає сенс відносини типу «один-до-багатьох»? Наведіть свої приклади.
5. У чому полягає сенс відносини типу «багато-до-багатьох»? Наведіть свої приклади.
6. Як ви розумієте цілісність даних? Чим вона забезпечується?
7. Чи є обов'язковими властивості зв'язку «каскадне оновлення по- в'язаних полів» і «каскадне видалення зв'язаних записів»? Обґрунтуйте свою відповідь. Наведіть приклади.
Лабораторна робота 9
.
Робота з запитами на вибірку. Запити з групуванням даних.
Перехресні запити. Запити з параметрами.
Модифікуючі запити
Мета – ознайомитися з основними можливостями та різновидами запитів на вибірку; освоїти правила побудови умов фільтрації даних; ознайомитися з призначенням, синтаксисом та особливостями викорис- тання модифікуючих запитів; отримати навички створення запитів на ство- рення, додавання, поновлення та видалення даних у БД.
Лабораторна робота сприяє напрацюванню таких компетентностей відповідно до Національної рамки кваліфікації:
знання:
синтаксису запитів на вибірку, модифікуючих і групових запитів та за- питів з параметрами; правил побудови виразів і параметрів основних вбудованих функ- цій MS Access; особливостей застосування різних типів запитів;
уміння:
здійснювати вибір інструментарію для вирішення конкретного при- кладного завдання з ведення БД; створювати запити з використанням складних умов фільтрації; застосовувати основні вбудовані функції MS Access; обчислювати підсумки за групами записів;

22 зберігати потрібні дані у вигляді нових таблиць; редагувати значення полів таблиць для записів, що відповідають певним умовам; видаляти записи з однієї або кількох пов'язаних таблиць; додавати записи в кінець існуючих таблиць;
комунікації:
презентувати результати розв'язання прикладних завдань з фільтра- ції даних, створення нових таблиць, видалення або додавання нових за- писів, редагування значень полів таблиць; обчислення виразів і підсумків за групами значень; аргументована взаємодія з командою учасників проекту щодо за- стосованого інструментарію для побудови запитів; рекомендації співробітникам підприємства щодо реакції системи у ви- падку застосування некоректних запитів;
автономність і відповідальність:
самостійно приймати рішення та відповідати за коректність і аде- кватність використаного інструментарію; прийняття рішення щодо доцільного вибору типу запитів і матема- тичного апарату.
Основні положення
Запити є основним інструментом СУБД для аналізу й управління даними: це інструкції (вираження), записані спеціальною мовою SQL
(Structured Query Language).
Запит на вибірку даних – найпоширеніший вид запитів у MS Access.
За допомогою запитів на вибірку можна виконувати такі дії з даними: об'єднувати поля з різних таблиць в одній тимчасовій (динамічній) таблиці; обмежувати набір даних, що відображаються за допомогою логічних виразів – умов відбору даних; будувати обчислювані поля.
Для створення запитів на вибірку в MS Access передбачені різні способи: за допомогою майстрів зі створення запитів; у режимі конструк- тора; в режимі SQL.

23
Режим конструктора надає користувачам простий, інтуїтивно зрозу- мілий інтерфейс для створення запитів будь-якої складності.
Результати виконання запиту подаються в табличній формі.
Доступ користувачів до таблиць зазвичай закривають, оскільки для однієї і тієї ж таблиці можна створити безліч різних запитів, що надають користувачам тільки необхідну їм інформацію. Наприклад, бухгалтер за- воду матиме можливість використати запит, хто зі співробітників скільки днів прохворів, проте доступ до запиту, що дозволяє дізнатися, чим співро- бітник хворів і де лікувався, може бути тільки у лікаря медичної частини заводу.
Запити можуть бути побудовані на основі однієї (однотабличні) або кількох таблиць (багатотабличні). Результат виконання багатотабличних запитів формується на основі збігу значень пов'язаних полів базових таблиць. Якщо такого збігу немає, результатом запиту буде порожня таблиця.
Вікно конструктора запитів складається з двох частин (рис. 11).
Рис. 11. Вікно бланка запиту на вибірку

24
У верхню область вікна бланка запиту додаються таблиці, на під- ставі яких будується запит, тобто ця область відображає частину схеми даних.
У нижній області бланку запиту вказують:
імена полів, що відбираються запитом (можна з різних таблиць);
ім'я таблиці, з якої вибирається дане поле; можливість задати сортування на даному полі; чи потрібно виведення поля на екрані в результаті виконання запиту; умови фільтрації у вигляді логічних виразів, відповідно до яких вибираються тільки ті записи, які задовільнюють умовам. Рядок АБО та всі рядки, що розташовані нижче, дозволяють задавати додаткові умови до поля. Умови будуть виконуватися незалежно одна від іншої, тобто реалізують логічну функцію АБО.
Увага! Слід пам'ятати, що запит – це послідовність інструкцій
з фільтрації записів. З виконанням запиту щоразу створюється так
звана динамічна таблиця для виведення записів, із закриттям запиту
вона не зберігається.
У табл. 2 наведено спеціальні символи, які можна використовувати для побудови умов фільтрації.
Таблиця 2
Спеціальні символи, використовувані для побудови
умов фільтрації
Символи
Опис
*
Будь-яка кількість символів
?
Один будь-який символ
#
Будь-яка цифра від 0 до 9
[А-Z]
Будь-який символ, що входить у зазначений діапазон
[!А-Z]
Будь-який символ, який не входить у зазначений діапазон. Також використовується в поєднанні з шаблонами «*» або «?»
[А, D, H]
Будь-який символ із зазначеного списку

25
У табл. 3 подані приклади з використання спеціальних символів, ви- користовуваних для побудови умов фільтрації.
Таблиця 3
Приклади використання оператора Like
Послідовність символів
Приклад умови фільтрації
Результат
“aBBBa”
Like “a*a”
Істина
“Форма”
Like [А-Я]*
Істина
“а2а”
Like “a#a”
Істина
“BAT123 khg”
Like “B?T*”
Істина
“Форма”
Like [А, М, Ф]*
Істина
1.01.19
– 31.01.09
Like ##.01.19
Істина
Запит з параметром дозволяє користувачеві самому змінювати умови відбору записів або використовувати змінні величини в обчислюваних виразах за багаторазового використання запиту. Його формат:
[Текст звернення до користувача].
Текст звернення до користувача, з одного боку, є ім'ям параметра, з іншого – містить підказку, яку саме інформацію необхідно ввести.
У бланк запиту (зазвичай у рядок «Умова відбору») поміщається текст запиту з параметром у тому полі, за яким задається критерій відбору.
Можна сформувати запит з кількома параметрами. Вони будуть з'яв- лятися на екрані в тому порядку, в якому розташовані в бланку запиту
(зліва направо).
Із запуском такого запиту спочатку з'являється діалогове вікно для вве- дення значення, і тільки після введення значення параметра запит буде виконаний.
Параметри можна включати до складу логічних виразів.

26
Наприклад, за запитом користувач може відібрати відомості про за- мовлення з ціною не менше заданого значення, причому граничне зна- чення визначає сам користувач у ході виконання запиту:
> = [Введіть мінімальну ціну].
Вікно запиту з параметром наведено на рис. 12.
Можна задати виконання кількох умов одночасно, наприклад, обрати товари з ціною у заданих межах:
> = [Введіть мінімальну ціну] And <= [Введіть максимальну ціну].
Рис. 12. Приклад виконання запиту з параметром
Підсумкові запити створюються на основі запитів на вибірку, але міс- тять угруповання даних, що дозволяє використовувати статистичні функції
(категорія «Статистичні»). Підсумкові запити аналогічні проміжним під- сумкам у MS Excel.

27
Список статистичних функцій наведено в табл. 4.
Таблиця 4
Перелік вбудованих функцій категорії «Статистичні»
Функції
Призначення функції
Avg
Обчислює середнє арифметичне набору значень, що містяться в певному полі запиту
Count
Обчислює кількість записів, повернутих запитом
Min, Max
Повертають мінімальне або максимальне зі зна- чень, що містяться в заданому полі запиту
Sum
Повертає суму значень, що містяться в заданому полі запиту
StDev
Повертає значення стандартного відхилення ви- бірки даних, що містяться в заданому полі за- питу
Var
Повертають оцінку дисперсії вибірки значень, що містяться в заданому полі запиту
First, Last
Повертає значення з першої або останньої за- писи набору. Якщо записи не впорядковані, то по- вертаються значення, що виявляються випадко- вими
На відміну від MS Excel, у MS Access кількість рівнів угруповання необмежена. Зі створенням підсумкового запиту є можливість задавати умови відбору записів і створювати обчислювані поля, використовуючи будь-які функції MS Access.
Створення підсумкових запитів у MS Access можливе за допомогою майстра та в режимі конструктора.
Груповий запит у режимі конструктора створюють за допомогою кнопки «Підсумки» на панелі інструментів. У результаті в бланк за- питу додається рядок «Групова операція».
На рис. 13. наведено приклад групового запиту, що обчислює кіль- кість товарів за кожною категорією.