БИКОВ І. Ю., ЖИРНОВ М. В., ХУДЯКОВА І. М.
MICROSOFT OFFICE В ЗАДАЧАХ ЕКОНОМІКИ ТА УПРАВЛІННЯ
3.3.Зв’язування таблиць
Перед тим, як зв’язувати таблиці, слід перевірити їх властивості в режиміконструктора. Пристворенніінформаційно-логічноїмоделівизна- чають, якими відношеннями буде зв’язано кожну пару таблиць та за якими саме полями здійснюється зв’язок. Всі поля, які будуть брати участь у зв’язках у відношенні «один», мають бути ключовими, або для властивості «Индексированное поле» цього поля має бути задане значення «Да (Совпадения не допускаются)». Всі поля, які будуть брати участь у зв’язках у відношенні «багато», не повинні бути ні ключовими, ні індексованими.
Після коригування таблиць, відповідно до цих вимог треба відкрити схему даних для зв’язування таблиць: КН «Схема данных» або М «Правка» → «Схема данных». Якщо зв’язки в БД застосовується вперше, спочаткувідкриєтьсяДВ«Добавитьтаблицу». Вспискутаблицьслід по черзі виділити кожну з таблиць, які мають брати участь в зв’язуванні
ідодати її до схеми даних, натиснувши кнопку «Добавить». Після того, як всі таблиці буде додано до схеми даних, діалогове вікно закривається кнопкою «Закрыть».
Увікні схеми даних з’являться всі додані до неї таблиці. За бажанням, за допомогою миші їх можна розташувати більш зручно. Якщо Ви помилково додали одну й ту саму таблицю до схеми даних кілька разів, зробіть щиглик на зайвій копії цієї таблиці і вилучіть її за допомогою клавіші Delete. В двох таблицях, що будуть об’єднуватися, знайдіть поля, за якими буде встановлено зв’язок (наприклад, поле «Назва» таблиці «Клієнти» і поле «Покупець» таблиці «Продаж»). Клацніть на одному з цих полів і «перетягніть» його на назву іншого з полів, що зв’я- зуються між собою. Будьте уважні: якщо таблиці мають знаходитися у відношенні «один до багатьох», слід перетягувати те з полів, яке входить у зв’язок у відношенні «один».
Відкриється ДВ «Связи». Перевірте, чи було зв’язано належні поля
іклацніть на прапорці «Обеспечение целостности данных». Забезпечення цілісності даних означає, що до функцій програми буде входити слідкування за тим, щоб користувач вводив ідентичні дані до полів, які зв’я- зуються між собою. Якщо до цього все було зроблено правильно, Access
112
Частина IIІ СИСТЕМА УПРАВЛІННЯ БАЗАМИ ДАННИХ MICROSOFT ACCESS
установлює необхідне значення перемикача «Тип отношения»: «один к одному» або «один ко многим». За бажанням можна встановити прапорці «Каскадное обновление связанных полей» і «Каскадное удаление связанных полей». Перший з них дасть змогу автоматично оновлювати значення зв’язаних полів у підлеглих таблицях (тобто тих, які входять у зв’язок у відношенні «багато») при заміні значення ключового поля
вбатьківській таблиці, яка входить у зв’язок у відношенні «один» (наприклад, при зміні назви клієнта в таблиці «Клієнти» цю назву буде такожавтоматичнозаміненовусіхзаписахтаблиці«Продаж», девонаприсутня). Другий прапорець дасть змогу автоматично вилучати всі записи
впідлеглій таблиці, які містять значення ключового поля, вилученого з батьківської таблиці. Нарешті, клацніть кнопку «Создать». Якщо зв’язок установлено правильно, Access намалює лінію між таблицями і зобразить на кінці лінії біля таблиці, яка входить у відношення з боку «один» цифру 1, а на кінці лінії біля таблиці, яка входить у відношення з боку «багато» — символ нескінченності.
Цю процедуру слід повторити для кожної пари таблиць, що зв’я- зуються між собою.
Щоб вилучити який-небудь зв’язок, клацніть на його лінії і натисніть клавішу Delete. Щоб скоригувати зв’язок, двічі клацніть на його лінії і тоді відкриється ДВ «Связи».
Для того, щоб зв’язати новоутворену таблицю з існуючою БД, треба відкрити схему даних, натиснути кнопку «Добавить таблицу» (або виконати команду «Добавить таблицу» контекстного меню), а далі додати нову таблицю і зв’язати її з іншими таблицями в спосіб, який розглядався вище.
Помилки, які виникають призв’язуванні таблиць, в переважній більшості зводяться до таких типів:
1.Дані, які вводяться у підлеглу таблицю, суперечать даним, які містяться в головній таблиці. Це стосується полів, що зв’язуються між собою. Не можна вводити до підлеглої таблиці дані, якщо у відповідному полі головної таблиці немає ідентичних даних.
2.Помилково визначено ключові та індексовані поля. Поля, які беруть участь у зв’язку у відношенні «один», обов’язково мають бути
113
БИКОВ І. Ю., ЖИРНОВ М. В., ХУДЯКОВА І. М.
MICROSOFT OFFICE В ЗАДАЧАХ ЕКОНОМІКИ ТА УПРАВЛІННЯ
ключовими або індексованими зі значенням «Да (совпадения не допускаються)» властивості «Индексированное поле». Поля, які беруть участь у зв’язку у відношенні «багато», не можуть бути ключовими, і для них установлюється значення «Нет» властивості «Индексированное поле».
3.Неправильно визначені типи даних полів, які беруть участь у зв’язку. Типи даних мають бути однаковими. Виняток становить поле типу «Счетчик». Його слід зв’язувати з полем типу «Числовое», для якого вибрано формат «Длинное целое».
4.При проектуванні схеми даних всі таблиці мають бути закритими.
Приклад
Розробити схему даних для БД «Торговельне підприємство».
Зв’язок між таблицями «Клієнти» та «Продаж» встановлюється за допомогоюполів«Назва» і«Покупець». Поле«Назва» входитьдотаблиці«Клієнти» увідношенні «один» (кожний клієнт реєструється вбазі даних один раз і тому кожному клієнту в цій таблиці відповідає єдиний запис), аполе«Покупець» входитьдотаблиці«Продаж» увідношенні«багато» (один і той самий клієнт може обслуговуватись безліч разів, тому кожному клієнту в цій таблиці може відповідати кілька записів).
Зв’язок між таблицями «Товари» та «Продаж» встановлюється за допомогою полів «Код_товару» і «Товар». Поле «Код_товару» входить до таблиці «Товари» у відношенні «один» (кожний товар записується до
Рис. 3.6
114
Частина IIІ СИСТЕМА УПРАВЛІННЯ БАЗАМИ ДАННИХ MICROSOFT ACCESS
базиданихлишеодинразітомукожномутоварувційтаблицівідповідає єдиний запис), а поле «Товар» входить до таблиці «Продаж» у відношенні«багато» (одинітойсамийтоварможебутипроданобезліч разів, тому кожному товару в цій таблиці може відповідати кілька записів).
Таблиці «Клієнти» і «Товари» безпосередньо між собою не пов’язані. Схема даних для БД «Торговельне підприємство» зображена на рис. 3.6.
3.4.Запити
ВAccess існують 2 типи запитів:
•запити-вибірки, які відбирають певну інформацію з таблиць, але не змінюють дані в цих таблицях;
•запити-дії, при виконанні яких додаються, оновлюються або вилучаються дані з таблиць БД.
Список запитів можна побачити на вкладці «Запрос» вікна БД.
Створення запиту-вибірки: вкладка «Запрос» вікна БД → КН
«Создать» → Конструкторабокоманда«Созданиезапросаврежимеконструктора». Запит буде відкрито в режимі конструктора. Запити, як і таблиці, існують в двох режимах: в режимі конструктора і в режимі таблиці (тобто перегляду результатів виконання запиту)1.
На екрані з’явиться бланк конструктора запитів і ДВ «Доба-
вить таблицу». В діалоговому вікні виділяємо таблиці (одну чи кілька), дані з яких будуть брати участь в складанні запиту, і по черзі додаємоїхдозапитузадопомогоюКН«Добавить». Післядодаваннявсіх таблиць закриваємо ДВ. Зазначимо, що запити можна створювати також на базі інших запитів.
Якщо таблиці, на яких будується запит, безпосередньо не зв’язані між собою, слід додати до бланка запиту також всі таблиці, які забезпечують «ланцюг» такого зв’язку. Всупереч результати виконання запиту утворять так званий декартів добуток даних: до кожного запису першої таблиці буде по черзі додано кожний запис іншої таблиці.
1 Взагалі, для запитів існує ще й третій режим – режим створення інструкцій на структурованій мові запитів SQL, який в цьому посібнику розглядатися не буде.
115
БИКОВ І. Ю., ЖИРНОВ М. В., ХУДЯКОВА І. М.
MICROSOFT OFFICE В ЗАДАЧАХ ЕКОНОМІКИ ТА УПРАВЛІННЯ
Бланк конструктора запитів складається з двох частин. У верхній частині знаходяться списки таблиць, які вибрано для створення запиту, а також зображуються зв’язки між таблицями. В будь-який момент можна додати до бланка таблицю (КН «Добавить таблицу») або вилучити непотрібну таблицю (клацнути на ній і натиснути клавішу Delete).
Оскільки результатом виконання запиту по суті є нова таблиця, слід оголосити, які поля будуть розміщені в запиті. Для цього треба в верхній частині бланка виділити необхідне поле і перетягнути його мишею до першої вільної комірки графи «Поле», яка розташована в нижній частині бланка або двічі клацнути на назві поля, або вибрати назву поля зі списку, розташованому в графі «Поле». Крім полів, які входитимуть до таблицізрезультатомзапиту, требаобов’язковододатидобланкатіполя, що визначають умови відбору даних. Якщо в запиті треба використати всі без винятку поля таблиці, можна додати до бланка рядок із зірочкою *, який є першим у списку полів таблиці.
Назви всіх вибраних полів з’являться в графі «Поле» нижньої частини бланка, а назви таблиць, з яких ці поля взято — в графі «Имя таблицы». Графа «Сортировка» містить список, завдяки якому можна відсортувати дані в таблиці-результаті запиту. Графа «Вывод на экран» міститьпрапорці, знявшиякіможнаскасуватививеденнянаекрантого чи іншого поля. Графи «Условие отбора» і «или» задають умови відбору записів. Кожна умова записується в стовпці відповідного поля в графі «Условие отбора»; графа «или» використовується при конструюванні більш складних логічних умов відбору (див. способи утворення умов відбору даних).
Способи утворення умов відбору даних:
1.Якщо дані, що відбираються, мають дорівнювати якому-небудь конкретному значенню, в графі умови відбору записується це значення. Текстові дані беруться в лапки, дати оточуються символами #. Наприклад: 2000 для поля «Кількість_на_складі»; «Зошит» для поля «Тип_товару», #01.01.05# для поля «Дата_продажу».
2.Якщо дані, що відбираються, повинні задовольняти умові нерівності, в графі умови відбору записується знак нерівності, а потім — значення, з яким порівнюються дані. Наприклад, умова >=0,03,
116
Частина IIІ СИСТЕМА УПРАВЛІННЯ БАЗАМИ ДАННИХ MICROSOFT ACCESS
записана для поля «Знижка», відбирає всі дані про продаж товарів зі знижкою не меншою ніж 3 %.
3.Якщо дані, що відбираються, належать до інтервалу значень, можна використати функцію BETWEEN <менше значення> AND <більше значення>. Наприклад, умова BETWEEN #1.01.05# AND #31.01.05#
вполі «Дата_продажу» відбере всі дані за січень 2005 р.
4.Якщо текстові дані відбираються шляхом порівняння зі значеннями, які належать деякому спискові, можна використати функцію IN
(<список>). Наприклад, умова IN(«Іванов», «Петров», «Сидоров»)
вполі «Покупець» дозволить вибрати дані про всі покупки зазначених в списку клієнтів.
5.Якщо утворюється більш складна умова, яка складається з кількох простих умов, поєднаних логічним сполучником «И» (тобто ці прості умови мають бути правильними одночасно), вони записуються до однієї графи бланка. Наприклад, якщо треба вибрати дані про дефіцитні штучні товари, тобто про всі штучні товари, які є в наявності на складі в кількості, меншій ніж 100 одиниць, треба записати умову «шт.» для поля «Од_виміру» і умову <100 для поля «Кількість_на_складі» в графу «Условие отбора».
6.Якщо складна умова утворюється з кількох простих умов, поєднаних логічним сполучником «ИЛИ» (тобто повинна бути правильною хоча б одна з простих умов), їх слід записувати в різних графах бланка: першу — в графі «Условие отбора», другу і подальші — в графах «или». Наприклад, якщо треба відібрати всі дані про продаж олівців, а також всіх товарів вартістю до 1 грн. за одиницю, то слід
вграфі «Условие отбора» записати умову «Олівець» в полі «Товар»,
ав першій з граф «или» — умову <1 в полі «Вартість».
Приклад
Користуючись БД «Торговельне підприємство» створити запит, який відбирає всі дані про продаж олівців, а також про всі товари, продані після 31 грудня 2004 року і за які не сплачено кошти.
Запит створюємо на базі таблиць «Товари» і «Продаж». До запиту включаємо поля «Тип_товару», «Вид_товару» таблиці «Товари» і поля
117
БИКОВ І. Ю., ЖИРНОВ М. В., ХУДЯКОВА І. М.
MICROSOFT OFFICE В ЗАДАЧАХ ЕКОНОМІКИ ТА УПРАВЛІННЯ
Рис. 3.7
«Кількість», «Дата_продажу», «Сплата» таблиці «Продаж». Дані розподіляємо за датою продажу. Дві умови — «олівці...» і «несплачений товар, проданийпісля31 грудня...» поєднанісполучником«ИЛИ». Умови«товар, проданий після 31 грудня...» і «... несплачений товар» поєднані сполучником «И». Тому умови відбору записуємо так, як це показано на рис. 3.7.
Перегляд результатів запиту: КН «Вид» → «Режим таблицы» або М «Вид» → «Режим таблицы». Повернення до режиму конструктора запитів: КН «Вид» → «Конструктор» або М «Вид» → «Конструктор».
Поля, щообчислюються— цетимчасовіполя, якістворюютьсяна часвиконаннязапитуівякізаносятьсярезультатиобчисленьнадданими з полів таблиць. Для їх створення до пустої комірки графи «Поле» бланка конструктора запиту треба ввести вираз, в якому можна використати оператори + (додавання), — (віднімання), * (множення), / (ділення), \ і MOD (округлює два вирази до цілого, ділить один на інший і повертає відповідно результат, знов округлений до цілого та остачу), & (приєднує другий рядок тексту до кінця першого).
СУБД Access оснащено зручним засобом для створення виразів — будівельником виразів (рис. 3.8). Він викликається натисканням кнопки «Построить», на якій зображено три крапки. В верхній частині вікна будівельника виразів розташоване поле, в яке виводиться вираз, що створюється. Під цим полем знаходиться рядок з кнопками операторів. В нижній частині вікна містяться три списки, які
118
Частина IIІ СИСТЕМА УПРАВЛІННЯ БАЗАМИ ДАННИХ MICROSOFT ACCESS
Рис. 3.8
дозволяють додати до виразу допоміжні об’єкти: поля таблиць або запитів, різноманітні функції таінші. Так, для звернення дополятаблиці слідвлівомусписку клацнутинарядку«Таблица» івибратинеобхідну таблицю, а в середньому списку виділити необхідне поле. Щоб додати назву цього поля до виразу, натисніть КН «Вставить». Наприклад, якщо вартість товару в таблиці «Продаж» надано з урахуванням ПДВ, то для обчислення вартості без урахування ПДВ можна створити та-
кий вираз: =[Продаж]![Вартість]*5/6.
Для використання функції необхідно в лівому списку вибрати рядок «Функция» → «Встроенная функция», в середньому списку — необхідну категорію функцій, а в правому списку — власне функцію, яка потрібна. Після натискання КН «Вставить» в полі виразу з’явиться назва функції, а в дужках — надпис «expr», який треба замінити на вираз, що визначає аргумент функції.
Після натискання КН «ОК» будівельника виразів в графі «Поле» бланка конструктора запитів з’явиться надпис «Выражение... <вираз>». Щоб дати назву цьому полю, відкрийте список властивостей цього поля (КН «Свойства» або М «Вид» → «Свойства») і введіть цю назву у графі властивості «Подпись поля».
Підсумкові запити. Інколи користувач цікавиться не всіма записами таблиці, а деякими підсумковими значеннями за групами даних (наприклад, середнім віком працівників кожного підрозділу
119
БИКОВ І. Ю., ЖИРНОВ М. В., ХУДЯКОВА І. М.
MICROSOFT OFFICE В ЗАДАЧАХ ЕКОНОМІКИ ТА УПРАВЛІННЯ
підприємства). Після натискання кнопки «Групповые операции» (або М «Вид» → «Групповые операции») в бланку конструктора запиту з’я- виться графа «Групповая операция». В цій графі треба визначити, які дії слід виконати з даним полем — групування або застосування групової операції. Групування за полем — це об’єднання до однієї групи записів з однаковим значенням в даному полі. Дані групуються при виборі значення «Группировка» в графі «Групповая операция». Групові операції виконуються для всіх значень даного поля в кожній групі. Існують такі групові операції: Sum (сума), Avg (середнє арифметичне значення), Min (найменше значення), Max (найбільше значення), Count (кількість записів, що входять до групи), StDev (статистичне стандартне відхилення), Var (статистична дисперсія), First (перше значення поля, яке знайдено в групі), Last (останнє значення поля, яке знайдено в групі). Крім того, є команда «Выражение», яку можна вживати у випадку, коли треба створити арифметичний вираз з використанням однієї або кількох групових операцій.
До підсумкового запиту додаються тільки ті поля, за якими виконуються групування та групові операції. Якщо групування виконується для кількох полів, то дані спочатку розподіляються на групи за першим полем, потім в кожній з груп розбиваються на дрібніші групи за другим полем тощо.
Якщо поле використовується лише для створення умови відбору, то в графі «Групповая операция» для цього поля вибирається команда «Условие». Власне умова відбору вводиться в графі «Условие отбора».
Приклад
Визначити сумарну кількість проданих товарів кожного виду.
Запитстворюємонабазітаблиць«Товари» і«Продаж». Дозапиту додаємо поле «Вид_товару» з таблиці «Товари» і поле «Кількість» з таблиці «Продаж». Умови відбору в даному випадку не потрібні. Дані групуються за видом товару, тому в графі «Групповая операция» вибираємо значення «Группировка» для поля «Вид_товару» і значення Sum для поля «Кількість». Запит в режимі конструктора набуває такого вигляду (рис. 3.9):
120
Частина IIІ СИСТЕМА УПРАВЛІННЯ БАЗАМИ ДАННИХ MICROSOFT ACCESS
Рис. 3.9
Перехресний запит призначений для групування даних та подання їх в компактному вигляді, який нагадує зведену електронну таблицю і є більш зручним для сприйняття. Для створення перехресного запиту до бланка конструктора запиту треба додати необхідні поля, а потім натиснути кнопку «Перекрестный» (або М «Запрос» → «Перекрестный»). Для кожного поля в графі «Перекрестная таблица» треба вибрати одну з установок: «Заголовки строк», «Заголовки столбцов», «Значение», «Не выводить». Обов’язково слід визначити щонайменше одне поле як заголовок рядка, одне поле як заголовок стовпця і одне поле значень. Кожне поле, яке є заголовком рядка чи стовпця, повинне мати установку «Группировка» в графі «Групповая операция». Для поля значень треба вибрати одну з групових операцій в графі «Групповая операция». Значення «Не выводить» в графі «Перекрестная таблица», як правило, установлюється для допоміжних полів, за якими створюються умови відбору даних.
Приклад
Визначити, на яку суму придбано товари кожного виду кожним із клієнтів.
Длярозробкизапитукористуємосятаблицями«Товари» і«Продаж» і додаємодобланкаконструкторазапитівполе«Покупець» зтаблиці«Продаж», поле «Код_товару» з таблиці «Товари» і поле, яке обчислюється.
121
БИКОВ І. Ю., ЖИРНОВ М. В., ХУДЯКОВА І. М.
MICROSOFT OFFICE В ЗАДАЧАХ ЕКОНОМІКИ ТА УПРАВЛІННЯ
Рис. 3.10
Доостанньогополявводимовираз[Продаж]![Вартість]*[Продаж]![Кількість], який обчислює загальну суму кожного замовлення клієнта. За першими двома полями будемо групувати дані значення поля, яке обчислюється, треба підсумувати. Тому в графі «Групповая операция» задаємо значення «Группировка» для полів «Покупець» і «Код_товару», а також значення Sum для поля, яке обчислюється. В графі «Перекрестная таблица» вибираємо значення «Заголовки строк» для поля «Покупець», значення «Заголовки столбцов» для поля «Код_товару» і значення «Значение» для поля, яке обчислюється. Результат виконання запиту наведено на рис. 3.10.
Запит з параметром. Якщо на етапі створення запиту користувач не може вирішити, які саме значення треба відшукати, можна занести до запиту параметр. В цьому разі перед виконанням запиту на екрані з’я- виться діалогове вікно з ім’ям цього параметра у вигляді надпису, а також з текстовим полем. До цього текстового поля користувач має внести значення поля таблиці, яке треба відшукати. Щоб створити параметр, требавграфі«Условиеотбора» бланкаконструкторазапитузаписатидовільну фразу в квадратних дужках, яка й буде ім’ям параметра.
Приклад
Одержати телефонні номери всіх клієнтів, які не сплатили за придбаний товар. Прізвище клієнта визначає користувач під час виконання запиту.
Вигляд запиту в режимі конструктора і діалогового вікна з параметром подано відповідно на рис. 3.11. та 3.12.
122
Частина IIІ СИСТЕМА УПРАВЛІННЯ БАЗАМИ ДАННИХ MICROSOFT ACCESS
Рис. 3.11
Рис. 3.12
Запити-дії. Існують 4 типи запитів-дій: запити на оновлення, додавання, вилучення даних і запит на створення нової таблиці. Спочатку створюється запит-вибірка, який має визначити з якими саме даними буде виконуватися відповідна дія. Потім запит-вибірка перетворюється на за- пит-діюшляхомнатисканняоднієїзкнопок«Обновление», «Добавление», «Удаление», «Создание таблицы» або однойменної команди пункту меню «Запрос». Режим таблиці тут використовується для перегляду результатів відповідного запиту-вибірки. Для виконання ж власне запиту-дії треба натиснути кнопку «Выполнение» (або М «Запрос» → «Выполнение»).
Слід звернути увагу на те, що запити-дії безповоротно змінюють вміст таблиць. Тому, щобзастерегтисявідвтративажливихданих, доцільнозробити копії таблиць, які використовуються в запиту-дії, перед його виконанням.
Нижче наведено подальші операції щодо створення запитів-дій. Запит на оновлення даних дозволяє виконати однакові зміни в ба-
гатьох записах таблиці. В графу «Обновление» вводимо вираз, який визначає як саме буде змінено дані в цьому полі. Вираз зручно створювати за допомогою будівельника виразів.
123