Урок 13                                                                        Інформатика 11(ІКТ)

Поняття запиту до реляційної бази даних. Створення простих вибіркових запитів.  Параметризовані запити.

Мета.

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

Розвиваюча. Розвивати логічне та алгоритмічне мислення.

Виховна. Виховувати культуру мовлення.

Тип уроку. Урок засвоєння нових знань.

Учні повинні знати:

Учні повинні уміти:

Матеріали для роботи з учнями:

• Основи баз даних: Навч. посіб. / І.О.Завадський. - К.: Видавець І.О. Завадський, 2013.
• Мультимедійне обладнання;
• Презентація “Поглиблене семантичне моделювання”.
• База даних (Вікіпедія).

План

1. Організація початку уроку
2. Актуалізація опорних знань.
3. Мотивація навчальної діяльності.
4. Вивчення нового матеріалу.
5. Запитання до уроку.
6. Домашнє завдання.

Хід уроку

1. Організація початку уроку.

3. Актуалізація опорних знань.

1. Назвіть основні функції систем керу­вання базами даних.
2. Які операції з наборами однотипних об’єктів дає змогу виконувати таблич­ний процесор?
3. Для чого призначені і який формат ма­ють функції для роботи з базою даних у середовищі табличного процесора?
4. Як у табличному процесорі створюють розширений фільтр?

Тестування “Основи баз даних”

4. Мотивація навчальної діяльності.

Клієнти баз даних — люди або програми — часто потребують інформації, яку неможливо знайти в якійсь одній таблиці. Припустимо, потрібно дізнатися прізвища та імена вчителів, які викладають в НА класі. Очевидно, що цю інформацію не вдасться відобразити за допомогою фільтра, застосованого до таблиці вчителів, адже в ній немає жодної інформації про кла­си. З іншого боку, у таблицях Класи та Викладання немає ін­формації про прізвища та імена вчителів, тому фільтрація цих таблиць також не допоможе.

5. Вивчення нового матеріалу.

 У подібних випадках стануть у нагоді запити — універсальний засіб виконання майже будь-яких завдань з обробки даних у БД. Словом «запит» називають також саму задачу на кшталт «визначити, які вчителі викла­дають в 11А класі»; результатом її розв’язання буде список учителів, а також текст розв’язання, записаний мовою або поданий у спеціальній екранній формі.

Схема роботи з запитом

Роботу з будь-яким запитом можна поділити на три основні етапи:

• спочатку запит формулюють, тобто описують словесно;
• далі запит створюють, або реалізують у СКБД — «перек­ладають» текст запиту мовою або вводять його в ек­ранну форму;
• нарешті запит виконують, тобто дають СКБД команду отримати розв’язок задачі з обробки даних.

Запит створюють один раз, а виконують багаторазово. Кожен раз результати виконання можуть бути різними, залежно від того, які дані введено в таблиці, що ними оперує запит.

Зазначимо, що результатом виконання запиту в реляційній БД також є таблиця. Однак це не одна з тих таблиць, які входять до складу БД. Це так звана віртуальна таблиця, що існує не­тривалий час, а саме стільки, скільки потрібно клієнту, але не довше, ніж протягом одного сеансу роботи з СКБД. Ця табли­ця зберігається лише в оперативній пам’яті, і коли ви завер­шуєте роботу з СКБД, усі віртуальні таблиці видаляються, про­те зберігаються SQL-тексти запитів і для отримання віртуаль­ної таблиці під час наступного сеансу роботи з СКБД достатньо просто виконати запит.

Для допитливих.

Той факт, що результат виконання запиту є таблицею, — вкрай важливий, адже це означає, що одні запити можна використовувати як параметри інших, тобто будувати запити на основі запитів, а не таблиць. Інакше кажучи, як віртуальні таблиці, так і ті, що зберігаються в БД постійно, можна вважати однаковими з точки зору конс­труювання запитів. У теорії реляційних баз даних така влас­тивість мов маніпулювання даними називається реляційною замкненістю.

Створення запитів у СКБД Microsoft Access

У Microsoft Access передбачено два засоби автоматизованого створення запитів: майстер і конструктор. Можливості першого із них вкрай обмежені, тому ми розглядатимемо тільки другий і зробимо це на прикладі запиту визначити прізвища та імена вчителів, які викладають в 11А класі.

Отже, для створення запиту у MS Access 2003 необхідно у голо­вному вікні бази даних вибрати в меню об’єктів пункт Запросы і двічі клацнути посилання Создание запроса в режиме конструкто­ра, а в MS Access 2007/2010 — клацнути кнопку Конструктор запросов в області Другие на вкладці Создание. Буде відображено вікно конструктора запиту, що називається Запрос 1: запрос на выборку, а також вікно Добавление таблицы (рис. 8.6).

У вікні Добавление таблицы потрібно, утримуючи клавішу СІгІ, вибрати назви таблиць, дані з яких використовуватимуться в запиті, клацнути кнопку Добавить, а потім — Закрыть. Для нашого запиту достатньо двох таблиць, Учителі та Викладан­ня, оскільки назви класів містяться в таблиці Викладання, а прізвища та імена вчителів, які в цих класах викладають, — у таблиці Учителі. Якщо додати таблицю Класи, це не буде по­милкою, але призведе до зайвого ускладнення запиту, оскільки класи зв’язані з учителями через таблицю Викладання, яку доведеться додавати все одно.

У верхній частині вікна конструктора запиту відображається фрагмент схеми БД, що містить ті таблиці, які використовують­ся в запиті (рис. 8.7). У нижній частині вікна розміщено бланк запиту — прототип таблиці, яка відображатиметься в результа­ті виконання запиту (рис. 8.7). У стовпцях цієї таблиці розміщу­ють поля, перетягуючи їх з верхньої частини, а також задають параметри відображення полів та умови відбору їхніх значень.

Призначення рядків бланка запиту таке:

• Поле — назва поля, що відображатиметься в таблиці-резуль- таті запиту або значення якого мають задовольняти умову;
• Имя таблицы — назва таблиці, у якій міститься поле;
• Сортировка — визначення способу сортування рядків у таб- лиці-результаті;
• Вывод на экран — прапорець, який слід встановити, якщо поле відображатиметься в таблиці-результаті;
• Условие отбора — умова, яку мають задовольняти значення полів;
• или — частина умови, з’єднана з іншими частинами сполуч­ником «або».

Бланк нашого запиту має виглядати так, як на рис. 8.7: відо­бражаються поля прізвище та ім'я таблиці Учителі, а зна­ченням поля клас таблиці Викладання має бути НА.

Щоб виконати цей запит, потрібно клацнути кнопку ! (За­пуск) на панелі інструментів. Яким має бути результат, пока­зано на рис. 8.8.

Після того як ви переглянете результати запиту, вікно таблиці результатів потрібно закрити кнопкою х . Буде відображено запитання, чи зберігати зміни макету або структури об’єкта Запрос 1. Клацніть кнопку Да і у вікні, що з’явиться, введіть назву запиту, наприклад Учителі 11А. Коли ви клацнете кноп­ку OK, запит відобразиться в розділі запитів у головному вікні бази даних (рис. 8.9) або в категорії Все объекты Access в обла­сті переходів.

Редагування запитів

Якщо в головному вікні бази даних Access 2003 вибрати в ме­то об’єктів команду Запросы, у правій частині цього вікна ві­добразяться всі наявні в базі запити. У MS Access 2007/2010 усі запити можна побачити в групі Запросы категорії Все объекты Access в області переходів. За потреби змінити якийсь запит клацніть його правою кнопкою миші та виберіть з кон­текстного меню команду Конструктор. Відкриється знайоме вам вікно конструктора запиту, у якому можна змінити як набір таблиць, що використовуються в запиті, так і його бланк.

• Щоб видалити з запиту таблицю, клацніть її у верхній час­тині вікна конструктора правою кнопкою та виберіть у контекстному меню команду Удалить таблицу.
• Щоб додати певну таблицю, скористайтеся кнопкою (Відобразити таблицю).
• Для видалення з запиту зв’язка між таблицями його пот­рібно клацнути правою кнопкою миші та вибрати з меню, що з’явиться, команду Удалить.
• Якщо знадобиться видалити з бланка запиту поле, клац­ніть його заголовок (тонку сіру смужку над назвою поля) правою кнопкою миші та виберіть з меню, що відкриється, команду Вырезать.
• Щоб змінити параметри полів у бланку, введіть потрібні значення з клавіатури або виберіть їх зі списку, що роз­кривається кнопкою  у клітинках рядків Поле, Имя таблицы, Сортировка.

Завдання 8.2

Створіть та виконайте запит «Визначити прізвища та імена вчителів, які викладають в НА класі».

Завдання 8.3

Створіть та виконайте запит «Визначити прізвище та спеціаль­ність класного керівника 11Б класу».

Приклади запитів

Приклад 1

Розглянемо трохи складніший запит: визначити імена та прі­звища учнів, яким викладає Сошко Катерина Миколаївна. У цьому запиті потрібно використати всі чотири таблиці, які є в базі даних школа: з таблиці Учні взяти прізвища та імена, на значення полів таблиці Учителі накласти умову прізвище = Сошко, ім'я = Катерина, по батькові = Миколаївна, а ско­риставшись таблицями Викладання та Класи, з’єднати вчителя з учнями.

Який вигляд матиме вікно конструктора цього запиту, показа­но на рис. 8.10. Зауважте, що зв’язок «один-до-одного» між таблицями Класи та Учителі було видалено, оскільки його зміст — «учитель є класним керівником» — ніяк не відобра­жається в умові запиту. Якби ми цей зв’язок залишили, то реалізували б інший запит, а саме «Визначити імена та прі­звища учнів, у яких викладає та є класним керівником Сошко Катерина Миколаївна». Нагадаємо, що зв’язок з бланка запиту видаляють так само, як і зі схеми БД: на зв’язку потрібно клацнути правою кнопкою миші та вибрати у меню, що з’явиться, команду Удалить.

Якщо ви правильно реалізували цей запит і ввели в базу дані, вказані у завданнях до розділів 4 і 5, то в результаті його ви­конання маєте отримати Максим Шпак та Хоменко Ірина.

Приклад 2

Визначити, у яких класах навчаються хлопці, що народилися у 1996 році.

Для створення цього запиту достатньо однієї таблиці — Учні, у якій міститься інформація і про класи. Тому цей запит загалом можна реалізувати за допомогою фільтра. Щоправда, нам пот­рібно відобразити значення одного поля — класи, а після філь­трації відображатимуться всі поля. Вікно конструктора цього запиту наведено на рис. 8.11.

Зверніть увагу на такі обставини:

• На записи таблиці Учні накладено дві умови, з’єднані спо­лучником «і»: стать має бути чоловічою і дата народжен­ня — 1996 рік. Тому ці умови розміщено в одному рядку Условие отбора.
• Умову «народилися у 1996 році» записано як *1996. Бук­вально цей запис можна інтерпретувати так: значення по­ля дата народження закінчується числом 1996. Символ «*» тут є знаком підстановки, що позначає довільний набір символів.

Приклад З

Відобразити всю інформацію про класних керівників 10А та 11Б класів.

Для реалізації цього запиту знадобляться таблиці Класи та Учителі, а таблиця Викладання буде зайвою, оскільки про ви­кладання в умові запиту не йдеться. Вікно конструктора запи­ту зображено на рис. 8.12.

І  в цьому запиті варто звернути увагу на дві обставини: «Всю інформацію» означає «значення всіх полів», але на бланку за­питу всі поля таблиці Учителі перелічувати не потрібно. За­мість цього достатньо записати в імені поля символ *, який і означає «всі поля».

Умову запиту можна переформулювати так: «Відобразити всю інформацію про вчителів, які є класними керівниками 10А або 11Б класу». Тобто, хоча в умові вжито сполучник та, насправ­ді її частини з’єднані сполучником або і тому записані в різних рядках: Условие отбора та или.

Завдання 8.4

Створіть та виконайте запит «Отримати всю інформацію про вчителів, які викладають учням, що народилися після 1 січня 1996 року».

Для допитливих.

Існують засоби автоматизованого створення запитів, значно потужніші, ніж конструктор у СКБД Microsoft Access. Так, у СКБД Paradox реалізовано графічну мову маніпулювання даними, що називається QBE (Query by Example — запит за зразком). Її можливості навіть ширші за можливості мови SQL.

6. Завдання для самостійного виконання.

1. Реалізуйте та виконайте такі запити:

• Відобразити всі дані про учнів, у яких класний ке­рівник — Корбут Василь Петрович.
• Визначити спеціальності вчителів, які викладають в 11    класах.

2. Для кожної з баз даних, створених у завданнях для самос­тійного виконання з попередніх розділів, сформулюйте, ре­алізуйте та виконайте по одному запиту, у якому викорис­товується більше однієї таблиці.
3. Реалізуйте та виконайте такі запити:

• Визначити прізвища та дати народження учнів, яким викладає Корбут Василь Петрович або Сошко Катерина Миколаївна.
• Визначити прізвища та дати народження учнів, яким викладають Корбут Василь Петрович та Сошко Катерина Миколаївна.
• Визначити прізвища та дати народження учнів, яким не викладає Корбут Василь Петрович.

7. Питання для роздумів.

Чим відрізняється запит «Визначити класи, у яких не ви­кладає Сошко Катерина Миколаївна» від запиту «Визначи­ти класи, у яких викладає не Сошко Катерина Миколаїв­на»? Наведіть приклади даних, на яких ці запити давати­муть різні результати.

8. Висновки.

• Запити — універсальний засіб виконання майже будь-яких завдань з обробки даних у БД.
• Запит спочатку формулюють, тобто описують словесно, по­тім створюють, або реалізують, у СКБД — «перекладають» текст запиту мовою SQL або вводять його в екранну форму, і,  нарешті виконують, тобто дають СКБД команду отрима­ти розв’язок задачі з обробки даних.
• Результатом виконання запиту в реляційній БД є віртуаль­на таблиця, яка існує стільки, скільки потрібно клієнту.
• Створюючи запит, у вікні конструктора необхідно вказати таблиці, дані з яких використовуватимуться, поля, значен­ня яких потрібно відобразити на екрані, та умови, яким мають відповідати значення цих або інших полів.

9. Домашнє завдання.

1. Вивчити теоретичний матеріал.
2. Виконати завдання.

• Відсортуйте записи у таблиці Вчителі Прізвище в алфавітному порядку.
• За допомогою фільтрації відобразіть дані про учителів чоловіків, які викладають  математику.
• Змініть у таблиці Вчителі  предмет математика на географія.

10. Завдання для досліджень.

1. Навчіться використовувати майстер побудови запитів у Microsoft Access та визначте, які різновиди запитів він до­зволяє створювати. Наведіть приклад запиту, який немож­ливо створити за допомогою майстра, але можна реалізува­ти в конструкторі запитів.
2. Сформулюйте для бази даних школа запит, який неможли­во реалізувати засобами конструктора запитів.