ЭИ_ПОСОБИЕ_ЧАСТЬ_3

Причому, таблиця, у якій дане поле є ключовим (Дисципліни) називається

головною, а друга таблиця (Екран успішності) зв'язаною або підлеглою.

Узв'язку два основних призначення:

–забезпечення цілісності даних;

–автоматизація задач обслуговування бази даних. Цілісність даних дозволяє:

–заборонити корегування в зв'язаних таблицях;

–при зміні значення поля в головній таблиці, автоматично обновляти ці значення в зв'язаній таблиці (каскадне відновлення зв'язаних полів);

–при видаленні в головній таблиці запису з заданим значенням поля, автоматично видаляти в зв'язаній таблиці всі записи, що містять таке ж значення цього поля (каскадне видалення зв'язаних записів).

Для створення багатотабличної бази даних необхідно виконати наступну послідовність дій:

1. Створіть структуру таблиць і визначте ключові поля.

2. Закрийте усі відкриті таблиці. Створювати або змінювати зв'язки між відкритими таблицями не можна.

3. Для переходу у вікно бази даних натисніть клавішу F11.

4.Натисніть кнопку Схема данных на панелі інструментів.

5.Якщо в базі даних ще не визначені зв'язки, автоматично відкривається діалогове вікно Добавление таблицы. Якщо потрібно додати таблиці для створення зв'язків, а діалогового вікна Добавление таблицы на екрані немає, натисніть кнопку Отобразить таблицу на панелі інструментів.

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

7.Буде відкрите діалогове вікно Изменение связей. Перевірте правильність імен полів, що присутні у двох стовпцях. При необхідності їх можна змінити.

20

8.Якщо необхідно, установіть параметри зв'язку – прапорці Обеспечение целостности данных, каскадное обновление связанных полей, каскадное удаление связанных записей. Якщо буде встановлений тільки прапорець Обеспечение целостности данных, вносити зміни в головну таблицю буде неможливо.

9.Для створення зв'язку натисніть кнопку Создать.

10.Для зміни або видалення створеного зв'язку потрібно у вікні Схема данных виділити потрібний зв'язок, клацнути на ній правою кнопкою миші й в контекстному меню зв'язку вибрати потрібний пункт.

Заповнення таблиць багатотабличної бази даних починається з головних таблиць. Після цього вводяться дані в зв'язані або підлеглі таблиці, причому зручно дані зв'язаних полів не вводити, а заносити з головних таблиць

(властивість Подстановка або Тип данных/Мастер подстановок у режимі Конструктора для даного поля зв'язаної таблиці).

Створення запитів на вибірку, використання параметрів Запити (Запросы) служать для витягу даних з таблиць і надання їх

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

Конструктора запросов – об'єкт Запросы, опція меню Создать/Конструктор.

При виборі цієї команди відкриється вікно "Добавление таблицы", у якому можна вибрати таблиці або запити, інформація яких буде використана в запиті, що створюється.

Бланк конструктора запитів складається з двох частин. У верхній частині знаходяться таблиці, і/або запити, що містять потрібну інформацію, а у нижній потрібно вказати:

перелік полів, що ввійдуть у результуючу таблицю;

ім'я таблиці або запиту, з яких береться дане поле;

якщо необхідно, вид сортування, (по зростанню, по убуванню);

режим виводу на екран (прапорець означає, що значення поля виводиться на екран);

21

умова добору – у цьому рядку вводяться критерії добору для значень полів, для того, щоб у результуючу таблицю були обрані записи, значення полів яких відповідають заданим критеріям;

для введення альтернативних умов для одного поля може бути

використаний рядок или.

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

Построитель выражений.

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

Month([Студенти]![Дата народження]). Після натискання клавіші ENTER

або переходу в іншу комірку Microsoft Access задасть ім'я поля за замовчуванням у форматі «ВыражениеN:», де N являє собою ціле число. Замість цього імені можна ввести те, яке потрібно, наприклад "Місяць:". Значенням даного поля, що обчислюється, буде номер місяця дати народження для кожного студента. В рядку условия отбора можна задати необхідний номер місяця або діапазон, для одержання списку студентів, що народилися в цьому діапазоні місяців. Поля, що обчислюються, можуть містити і будь-які інші вирази.

Запит, при виконанні якого у власному діалоговому вікні відображається запрошення ввести дані, наприклад значення умови добору, називається

запитом з параметрами або параметричним запитом (параметрическим запросом). Наприклад, у рядку Условия отбора для поля Група, вводиться в

квадратних дужках текст [Уведіть номер групи]. При виконанні запиту відкриється відповідне вікно (рис. 1.2.), у яке потрібно ввести значення параметра. Це значення буде використовуватися як умова добору.

Рис. 1.2. Вікно введення значення параметра

22

У бланк конструктора запитів, при зміні типу запиту, можуть додаватися додаткові рядки.

Перехресні запити (Перекрестные запросы) дозволяють створювати більш компактні і зручні для аналізу результуючі таблиці. Дані групуються по вертикалі і по горизонталі. Наприклад, за допомогою перехресного запиту можна видати таблицю, де по вертикалі виведено прізвища студентів, по горизонталі – найменування дисциплін, а на перетинанні – оцінки кожного студента по цих дисциплінах.

Для створення перехресного запиту, якщо всі необхідні дані знаходяться в одній таблиці, можна використовувати Мастер (об'єкт Запросы Создать/Перекрестный запрос). Якщо ж потрібно використовувати дані з декількох таблиць, варто створити спочатку запит на вибірку, щоб вибрати всі необхідні поля, а потім змінити тип запиту – опція меню Запрос/Перекрестный або кнопка Тип запроса на панелі інструментів. При цьому в бланку запиту з'явиться додатковий рядок Перекрестная таблица, у якій потрібно вказати,

які поля є заголовками строк, заголовками столбцов, і яке поле містить значение.

Підсумкові запити (Итоговые запросы) дозволяють використовувати статистичні функції і групові операції для значень полів. Для створення підсумкового запиту потрібно в конструкторі запитів вибрати пункт Вид/Групповые операции або, у нижній частині бланка запиту, контекстне меню/Групповые операции або клацнути на кнопці панелі інструментів. У бланку запиту з'явиться рядок Групповая операция. В комірках рядка Групповая операция для кожного поля бланка запиту можна вибрати одну з наступних статистичних функцій: Sum (сума), Avg (середнє значення), Min (мінімальне значення), Max (максимальне значення), Count (кількість значень) або інші функції — First (перше значення) і Last (останнє значення).

У підсумкових запитах також можна задавати умови добору, вони можуть бути і параметричними [1-4].

Методичні рекомендації до виконання завдання №2

Тема: Вибірка і пошук інформації в MS Access.

Ціль заняття: Навчитися створювати багатотабличну базу даних. Одержати практичні навички створення і використання зв'язків між таблицями.

23

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

План

1.Створення багатотабличної бази даних у Access.

2.Витяг даних з бази за допомогою запитів.

Технологія виконання завдання

I.Створення багатотабличної бази даних

1.Запустіть Access.

2.Відкрийте базу даних Успішність.

3.Створіть таблицю "Дисципліни" – об'єкт Таблицы, Создание таблицы путем ввода данных. Створіть у ній поля Код і Найменування.

4.Перейдіть у режим Конструктора, зберігши таблицю з ім'ям "Дисципліни", відмовтеся від створення ключового поля. Переконайтеся, що при створенні таблиці шляхом уведення даних, типи полів, за замовчуванням, є текстовими. Якби у поля були введені дані, тип для кожного поля був би автоматично установлений відповідно до типу введених даних.

5.Установіть для поля Код тип числовой і зробіть його ключовим. У властивостях поля установіть Маску ввода, що містить три обов'язкових цифри, у властивості Подпись уведіть: Код дисципліни. Закрийте таблицю, зберігши внесені зміни.

6.Уведіть 5-6 дисциплін у таблицю "Дисципліни".

7.У режимі конструктора створіть таблицю Екран успішності з полями: №зал_книжки (числовий), Семестр (числовий), Код дисципліни (числовий), Оцінка (числовий). Поля №зал_книжки, Семестр і Код дисципліни зробіть ключовими.

8.Змініть тип даних полів №зал_книжки і Код дисципліни, вибравши Тип данных /Мастер подстановок. Укажіть, що для поля №зал_книжки використовується стовпець підстановки з таблиці Студенти, а для поля Код дисципліни – з таблиці Дисципліни.

9.У властивостях поля Оцінка установіть Маску ввода, що містить дві необов'язкові цифри, й Условие на значение, вважаючи, що значення поля може бути в діапазоні від 2 до 12. Уведіть відповідне Сообщение об ошибке.

10.Закрийте таблицю Екран успішності, зберігши її структуру.

24

11.Перейдіть у схему даних (рис. 1.3.). Додайте всі наявні таблиці й установіть між ними зв'язки, з урахуванням забезпечення цілісності даних, каскадного відновлення зв'язаних полів і каскадного видалення зв'язаних записів.

Рис. 1.3. Вікно схеми даних 12.Заповніть таблицю Екран успішності, уводячи значення полів

№зал_книжки і Код дисципліни, використовуючи стовпці підстановки. 13.Виконайте корегування даних у таблицях бази даних для наступних умов:

одного зі студентів відраховано;

зараховано нового студента;

додано нову дисципліну;

студент одержав дублікат залікової книжки з іншим номером.

II. Витяг даних з бази за допомогою запитів

1.У вікні Конструктора створіть запит «Студент 1», у якому будуть відомості про студентів, що проживають у Дніпропетровську, при цьому поле Місто не виводити на екран (рядок Вывод на экран вікна Конструктор). Відсортуйте інформацію по групах і прізвищах.

2.Створіть запит «Студент 2», у якому будуть відомості про іногородніх студентів, при цьому поле Місто виводиться на екран.

3.Створіть запит з полем Місяць, що обчислюється, для одержання інформації про студентів, у яких день народження:

25

функцію IIF для перевірки номера місяця і виконання, у залежності від результату, необхідних обчислень;

задайте сортування даних по групах і прізвищах по зростанню (за алфавітом);

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

6.Створіть параметричний запит "Успішність" для видачі інформації про успішність студента. Значення полів № залікової книжки і Семестр повинні вводитися, як параметри.

7.Створіть підсумковий запит для одержання інформації про середній і максимальний бал за результатами сесії для кожного студента. Для цього:

у вікні Конструктора запитів додайте таблиці Студенти, Дисципліни, Екран успішності;

додайте в бланк запиту рядок Групповая операция;

включіть в бланк запиту поля Група, Прізвище, Ім'я (таблиця Студенти), два поля Оцінка (таблиця Екран Успішності);

у рядку Групповая операция для полів Група, Прізвище, Ім'я укажіть вид операції – Группировка, а для полів Оцінка в одному випадку Avg (середнє), в іншому - Max (максимальне);

клацніть на значку ! (знак оклику) панелі інструментів, щоб побачити, як виконується запит. Зверніть увагу на ім'я і вміст полів, у яких виводиться середнє і максимальне значення оцінок;

поверніться у вікно Конструктора запитів (щигликом миші на значку трикутник, у лівому верхньому куті панелі інструментів);

клацніть правою кнопкою миші в полі Оцінка, що виводить середнє значення; в меню, що відкрилось, виберіть пункт свойства, у вікні, що відкрилося, "Свойства поля" установіть: Формат – фіксований, Число десятичных знаков – 2; Подпись – Середній бал;

установіть в такий же спосіб властивості другого поля Оцінка, але у властивості Подпись наберіть – Максимальний бал;

перевірте, як змінилося відображення полів оцінок у результуючій таблиці;

збережіть створений запит з ім'ям Підсумок1.

8.Створіть перехресний запит для одержання інформації про результати здачі студентами сесії, для чого:

27

у Конструкторі запитів додайте таблиці Студенти, Дисципліни, Екран успішності і створіть запит на вибірку. У запит уключіть поля з таблиці Студенти – Група, Прізвище, Ім'я; з таблиці Дисципліни – Найменування; з таблиці Екран успішності – Оцінка;

змініть тип запиту (Запрос/Перекрестный або кнопка Тип запроса на панелі інструментів);

у рядку "Перекрестная таблица" бланка запиту укажіть для полів Група, Прізвище, Ім'я, Семестр - заголовки строк, для поля Найменування - заголовки столбцов, для поля Оцінка - значение;

у рядку Групповая операция, для поля Оцінка вкажіть тип операції

First, для інших – Группировка.

Збережіть запит з ім'ям Перехр1.

9.Відповісти на контрольні питання.

Контрольні питання

1.Перелічіть основні вимоги до полів, між якими встановлюються зв'язки. Чи можуть бути встановлені зв'язки між полями з різними іменами, з різними типами даних?

2.Перелічіть можливі типи відносин між таблицями бази даних. Визначить типи відносин між таблицями Вашої бази даних.

3.Як змінити існуючий зв'язок між таблицями бази даних?

4.Як видалити існуючий зв'язок між таблицями бази даних?

5.Як додати (видалити) ще одну таблицю в Схему даних?

6.Як викликати вікно Конструктора таблиці, не виходячи з вікна Схеми даних?

7.Дайте визначення цілісності бази даних. Перелічіть обмеження, що діють після визначення цілісності даних у базі.

8.Для чого використовуються режими каскадного видалення зв'язаних записів і каскадного відновлення зв'язаних полів? При виконанні якого завдання Ви використовували ці режими?

9.Що називається запитом у Access, перелічіть можливі типи запитів?

10.Відкрийте вікно Конструктора запитів і охарактеризуйте його елементи. 11.Визначте призначення команд меню Запрос, знайдіть відповідні цим

командам кнопки панелі інструментів.

12.Охарактеризуйте призначення рядків бланка запиту у вікні Конструктора.

28

13.Як виконуються операції додавання, видалення і зміни порядку полів у бланку запиту, покажіть виконання цих операцій?

14.Як забезпечується можливість сортування результатів вибірки по запиту, покажіть на прикладі одного зі створених Вами запитів?

15.Як здійснюється включення в запит поля, що обчислюється? Наведіть приклад з лабораторної роботи.

16.Визначте переваги у використанні параметричних запитів, наведіть приклад з лабораторної роботи.

17.Дайте визначення перехресним запитам. Як створюється перехресна таблиця?

18.Охарактеризуйте особливості створення в запитах полів, що обчислюються, наведіть приклад такого запиту в лабораторній роботі.

19.Визначте послідовність дій при створенні багатотаблічних запитів.

20.Визначте призначення підсумкових запитів.

21.Як у бланк конструктора запитів увести рядок Групповая операция? 22.Перелічіть типи операцій, доступних у рядку Групові операції бланка

запиту.

23.Назвіть правила формування імені поля в запиті, по якому виконується групова операція.

24.Як відформатувати поля запиту (встановити кількість виведених розрядів, змінити ім'я поля)?

Чому ви навчилися?

Створювати багатотабличні бази даних.

Встановлювати зв’язки між таблицями.

Створювати стовпці підстановки.

Встановлювати параметри зв’язків між таблицями.

Корегувати дані багатотабличних баз.

Створювати запити на вибірку, параметричні, підсумкові та перехресні запити.

Сортувати дані результуючої таблиці.

Форматувати поля запиту для покращення вигляду результуючої таблиці.

29