Бази даних (СУБД Microsoft Office Access 2011)

ЛекцІя Бази даних (СУБД Microsoft Office Access 2011)

Бази даних (СУБД Microsoft Office Access 2011)

База даних (БД) – це систематизоване сховище інформації з певної предметної області, до якого можуть мати доступ багато прикладних програм

Система управління базами даних (СУБД) – це програмні засоби для створення, ведення і використання БД.

Особливості використання Б.Д.

• Дані в БД створюються і зберігаються як єдине ціле для розв`язання всіх завдань предметної області, тобто відпадає необхідність дублювання даних (економія пам`яті комп`ютера);

• Кожна прикладна програма вибирає з БД дані для розв`язання тільки свого завдання;

• Незалежність прикладних програм від даних (зміни в даних не викликають необхідність зміни програми і навпаки)

Реляційні БД – це БД, дані в яких подані у вигляді двомірних таблиць, за допомогою яких можна описати предметну область.

В ієрархічній БД всі елементи розташовуються у послідовності від вищого до нижчого.

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

У нинішній час використовуються здебільшого реляційні БД: Microsoft Access, Corel Paradox, Oracle, SQL Server.

Склад музичної групи
Код	Прізвище	Ім’я	Дата народження	Місце народження	Інструмент
00001	Леннон	Джан	09.10.40	Ліверпуль	Соло-гітара
00002	Маккартні	Пол	18.06.42	Ліверпуль	Бас-гітара
00003	Харрісон	Джордж	25.02.43	Ліверпуль	Ритм-гітара
00004	Старр	Рінго	07.07.40	Ліверпуль	Ударні

Запис БД – це рядок таблиці, конкретна реалізація (значення) поняття предметної області.

Поле БД – це стовпець у таблиці даних, властивість (атрибут) даного поняття предметної області.

Для кожного поля задається тип жених, що можуть знаходитися в ньому.

Записи в таблиці відрізняються значеннями своїх полів.

Ключове поле БД – (ключ БД) поле або декілька полів, що однозначно визначають (ідентифікують) запис. Наприклад, для таблиці «Склад музичної групи» ключовим є поле «Код».

Початок роботи з Access

Створення відкриття і збереження бази даних.

Два способи створення бази даних

Створення порожньої БД, а після цього додавання в неї об`єктів. Такий спосіб є найбільш гнучким , але вимагає окремого визначення кожного елемента БД.
Створення за допомогою Майстра бази даних певного типу зі всіма необхідними об`єктами – це найпростіший спосіб створення бази даних.

В обох випадках є можливість змінити і поширити створену БД.

Об`єкти Access:

Об`єкт	Опис
Таблиця	Містить дані у вигляді двомірної таблиці. Таблиці – основа БД, від них залежать всі інші об`єкти.
Запит	Вибирає дані з таблиці на основі заданої умови.
Форма	Вибирає дані з таблиці або запиту на підставі описаного користувачем формату. Форми дозволяють переглядати, редагувати і друкувати дані. У форматі можна відображати дані, взяті з декількох таблиць або запитів.
Веб-сторінка	Перетворює будь-який об`єкт БД у веб-сторінку і дозволяє іншим користувачам переглядати, вводити та аналізувати дані за допомогою інтернету.
Макрос	Автоматизує стандартні дії.
Модуль	Автоматизує складні операції, які не можна описати макросами.

Проектування бази даних

Проектування БД для ефективної обробки даних у конкретній предметній області – досить складний процес. Тільки в самих простих випадках достатньо однієї таблиці для опису предметної області. Звичайно вимагається декілька взаємопов`язаних таблиць, тобто значення поля однієї таблиці використовується в іншій таблиці.

Деякі поради щодо проектування БД

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

Робота з таблицями Access

Створення таблиць

У вікні БД (див. с. 60) можна за допомогою вкладки «Таблиця» створити таблицю одним із таких способів:

Спосіб	Опис
Режим таблиці	Виводить бланк (форму) абстрактної таблиці, яка потім може приймати конкретні форму і зміст.
Конструктор	У режимі конструктора можна безпосередньо вказати параметри всіх елементів структури таблиці.
Майстер таблиць	Дозволяє вибрати поля, що включаються в таблицю, із широкого списку зразків полів різних типів (ділові контакти, ведення домашнього господарства тощо)
Імпорт таблиць	Імпорт даних у таблицю з інших файлів
Зв`язок з таблицями	Встановлення зв`язків з даними, що зберігаються в таблицях інших БД (не обов`язково Access), які дозволять змінювати ці дані.

Редагування таблиць

Дія	Способи виконання
Переключення між режимом конструктора і режимом таблиці	Кнопка панелі інструментів у режимі конструктора
	Кнопка панелі інструментів у режимі таблиці
Додавання поля в таблицю в режимі таблиці	1.Активізувати комірку в стовпці, ліворуч від якого необхідно додати новий стовпець. 2.Меню «Вставка»\ команда «Столбец». 3.Подвійне клацання на заголовку нового стовпця, введення ім`я поля
Створення або заміна ключового слова	Кнопка панелі інструментів у режимі конструктора
	Меню «Правка»\ команда «Ключевое поле».
Створення зв`язків між таблицями	1.Закрити всі відкриті таблиці. 2.Кнопка панелі інструментів. 3.Вибрати поле в одній таблиці й перетягти його за допомогою миші на відповідне поле в другій таблиці. 4.Створити зв`язок, клацнувши по кнопці «Создать»

Переваги використання ключових полів

Швидкість роботи. Використовуючи ключ, Access створює індекси, що дозволяють прискорити роботу запитів і ряду інших функцій.
Упорядкування. Access автоматично сортує і відображає записи БД в порядку зростання або зменшення ключового поля.
Відсутність збігів. Access не дозволяє користувачу вводити дані з таким само ключем, як і в існуючого запису.
Зв`язки. Access організує зв`язки між таблицями, використовуючи загальний ключ.

Сортування і фільтрація даних

Дія	Способи виконання
Сортування даних у полі	Контекстне меню\ команда «Сортировка по …»
	Кнопки панелі інструментів у режимі таблиці
Накладення фільтра на записи згідно з виділеним фрагментом	Контекстне меню\ команда «Фильтр по выделенному»
	Кнопка панелі інструментів у режимі таблиці
Зміна критеріїв фільтрації	Контекстне меню заголовка таблиці\ команда «Изменить фильтр»
	Кнопка панелі інструментів у режимі таблиці
Застосування фільтра	Контекстне меню заголовка таблиці\ команда «Применить фильтр»
	Кнопка панелі інструментів у режимі таблиці
Вилучення фільтра (відновлення вмісту таблиці)	Контекстне меню заголовка таблиці\ команда «Удалить фильтр»
	Кнопка панелі інструментів у режимі таблиці

Запити Access 2003

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

Тип запиту	Опис
Запит на вибірку	Дані, що задовольнять умови відбору, вибираються з однієї або декількох таблиць і виводяться в певному порядку.
Запит з параметрами	При створенні запиту необхідно у вікні діалогу ввести певні відомості, наприклад, умови відбору записів або значення для вставлення в поле. Зручно використати як базове джерело даних для форм або звітів.
Перехресні запити	Відображають результати статистичних розрахунків, виконаних за даними з одного поля. Ці результати групуються за двома наборами даних у форматі перехресної таблиці, створюючи номери рядків і заголовки стовпців.
Запит на зміну	Дозволяють в одній операції внести зміни в багато записів. Існує чотири види запитів на зміну: на створення таблиці, на вилучення записів, на додавання і поновлення записів.
Запит SQL	Запитом SQL називають запит, що створюється за допомогою інструкції SQL (мови структурних запитів). Прикладами запитів SQL є запит на об`єднання, запит до серверу, управляючий запит та ін.

Деякі поради щодо проектування запитів

Визначити, яка таблиця (таблиці) містить необхідну інформацію;
Визначити зв`язки таблиць (чи мають ці таблиці відповідні ключі);
Визначити тип запиту, що буде створюватися;
Виділити умови і критерії полів, які повинні задовольняти записи;
Продумати необхідні обчислення;
Визначити порядок сортування;
Визначити ім`я, під яким буде збережений запит.

Форми Access 2003

Форми СУБД – це засіб відображення інформації з БД із використанням елементів управління для зручної і наочної роботи з даними – введення, редагування, відображення. Форми складають інтерфейс БД.

Елементи управління СУБД – це графічні елементи (поле, кнопка, малюнок), які розміщуються у формі або звіті для відображення даних або для прикрашання.

Принципи використання форм:

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

Дія

Способи виконання

Створення форми за допомогою авто форми (виводяться всі поля і записи з вибраної таблиці чи запиту)

1. Вкладка «Форми»\ кнопка «Создать» у вікні БД (див. с. 60).

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

3. Вибрати в списку вид авто форми.

Створення форми за допомогою майстра

1. Вкладка «Форми»\ кнопка «Создать» у вікні БД (див. с. 60).

2. Вибрати в списку «Мастер форм».

3. Додержуватись інструкцій Майстра.

4. Якщо створена форма не відповідає вимогам, є можливість змінити її в режимі конструктора.

Звіти Access 2003

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

Складові частини звіту

Заголовок. Інформація на початку першої сторінки(назва).
- Верхній колонтитул. Інформація на початку першої сторінки(заголовки стовпців таблиць).
- Область даних. Відображення даних із таблиць або запитів.
- Примітка групи. Інформація в кінці групи даних (підсумок за групою).
- Нижній колонтитул. Інформація в кінці кожної сторінки (номер сторінки).
- Область приміток звіту. Інформація в кінці останньої сторінки (підсумкові обчислення по всіх записах звіту)

Дія

Способи виконання

Створення звіту за допомогою авто звіту (виводяться всі поля і записи з базової таблиці або запиту)

1. Вкладка «Отчет»\ кнопка «Создать» у вікні БД (див. с. 60).

2. Вибрати таблицю чи запит, на основі яких необхідно створити звіт або відкрити таблицю чи запит у будь-якому режимі.

3. Вибрати в списку вид автозвіту.

Створення звіту за допомогою Майстра

1. Вкладка «Отчет»\ кнопка «Создать» у вікні БД (див. с. 60).

2. Вибрати в списку «Мастер отчетов».

3. Додержуватись інструкцій Майстра.

4. Якщо створений звіт не відповідає вимогам, є можливість змінити її в режимі конструктора.

Автоматизація виконання завдань за допомогою макросів Access 2011

Макрос Access – найбільш потужний засіб автоматизації регулярно виконуваних завдань серед макросів програм інтегрованого пакету Microsoft Office.

Дія	Способи виконання
Створення макросу	1. Вкладка «Макроси»\ кнопка «Создать» у вікні БД (див. с. 60). 2. У комірці стовпця «Макрокоманда» натиснути кнопку розкриття списку, що відкриває список макрокоманд. 3. Вибрати ім`я макрокоманди. 4. У нижній половині вікна вказати аргумент макрокоманди. 5. Для додавання до макросу інших макрокоманд перейти на наступний рядок і повторити кроки 3, 4. 6. Макрокоманди виконуються в порядку їхнього розташування в бланку.
Виконання макросу	Вкладка «Макроси» кнопка «Запуск» у вікні БД (див. с. 60).
Редагування макросу	Вкладка «Макроси» кнопка «Конструктор» у вікні БД.
Вилучення макросу	1. Вкладка «Макроси» у вікні БД. 2. Вибрати макрос для вилучення і натиснути клавішу <Delete>

Завдання, при розв`язанні яких зручно використовувати макроси

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

При створенні чи роботі з таблицями БД, що містять велику кількість записів, часто виникає потреба знайти інформацію тільки для певного об’єкта. З метою спрощення пошуку та впорядкування певних записів у СУБД Access реалізовані команди сортування та фільтрування інформації – Сортировка, Фильтр по выделенному, Расширенный фільтр.

Сортировка, Фильтр по виділеному, Розширенний фільтр

З метою простого відбору даних у таблиці, їх можна упорядковувати та фільтрувати. Упорядковувати дані можна у режимі таблиці за зростанням або за спаданням. Для цього використовуються кнопки Панелі інструментів або команда Записи/ Сортировка. Розрізняють сортування за зростанням і за спаданням. Наприклад на мал. 1 відображені записи відсортовані в алфавітному порядку:

Мал. 1

Фільтрація – виведення на екран лише тих записів, які задовольняють певну умову.

Відфільтровані дані з’являються на екрані у вигляді таблиці.

Існують такі види фільтрів:

Фильтр по выделенному – використовується, якщо потрібно відфільтрувати дані за одним полем таблиці.

Для його застосування слід виконати дії:

Виділити у полі відбору один із записів, який нас цікавить;

Виконати команду Записи/ Фильтр/ Фильтр по выделенному або натиснути кнопку на Панели инструментов.

Наприклад на мал. 2 відображені записи про учнів, яким виповнилося 16 років: Мал. 2

Расширенный фильтр – використовується, якщо потрібно відфільтрувати дані за кількома полями таблиці.

Для його застосування слід виконати дії:

Виконати команду Записи/ Фильтр/ Расширенный фильтр або натиснути кнопку на Панели инструментов;

На екрані з’явиться список полів таблиці та порожня структура фільтра (мал. 3);

Мал. 3

Перетягнути поля відбору у рядок Поле структури;

Заповнити рядок Условия отбора (мал. 4);

Мал. 4

Виконати команду Фильтр/ Применить фильтр або натиснути кнопку на Панели инструментов (мал. 5).

Мал. 5

Щоб скасувати будь-який фільтр, необхідно виконати команду Записи/ Удалить фильтр або натиснути кнопку на Панели инструментов.

Виконання завдання згідно варіантів.

Завдання на створення Б.Д.

Варіант І

1. Створіть базу даних «Мої друзі», яка складається з таблиць:

«Друзі» (поля: порядковий номер, прізвище, ім'я, дата народження, телефон, вік);
«Тварини» (поля: номер друга, тварина, кличка);
«Хобі» (поля: номер друга, хобі, кількість років хобі).

Продумайте та створіть зв'язки між таблицями БД.
Внесіть у БД інформацію (хоча б про 5 ваших друзів).

Варіант ІІ

1. Створіть базу даних «Наш клас», яка складається з таблиць:

« Загальна інформація » (поля: порядковий номер, прізвище, ім'я, по батькові, дата народження, домашня адреса, телефон);

«Позашкільні уподобання» (поля: номер учня, назва позашкільного закладу, гурток чи секція);

«Успішність» (поля: номер учня, математика, інформатика, фізика).

Продумайте та створіть зв'язки між таблицями БД.
Внесіть у БД інформацію про 5 учнів вашого класу.

Варіант III

1. Створіть базу даних « Прокат фільмів », яка складається з таблиць:

«Відеокасети» (поля: порядковий номер, назва касети, кількість екземплярів);

«Відеодиски» (поля: порядковий номер, назва диска, кількість екземплярів);

«Клієнти» (поля: номер клієнта, серія паспорту, завдаток, номер касети, номер диска, дата видачі, дата повернення).

Примітка. При заповненні таблиці «Клієнти» мати на увазі, що фільми видаються під заставу або паспорта, або грошей, тому в кожному записі таблиці «Клієнти» заповнюється одне з двох полів: Серія паспорта або Застава, в іншому полі ставиться «-» для серії паспорта чи «0» для застави. Крім того, клієнт, що повернув касети чи диски, з таблиці вилучається.

Продумайте та створіть зв'язки між таблицями БД.
Внесіть у кожну з таблиць БД по 5 записів.

Варіант IV

1. Створіть базу даних «Бібліотека», яка складається з таблиць:

«Підручники» (поля: порядковий номер, назва підручника, клас, кількість екземплярів);

«Художня література» (поля: порядковий номер, назва книги, кількість екземплярів)

• «Відвідувачі» (поля: порядковий номер, прізвище, ім'я, клас, номер підручника, номер книги, дата видачі художньої літератури, дата повернення художньої літератури).

Продумайте та створіть зв'язки між таблицями БД.
Внесіть у кожну таблицю БД по 5 записів.

Варіант V

1. Створіть базу даних «Рахунки», яка складається з таблиць:

«Клієнти» (поля: порядковий номер, прізвище, ім'я, по батькові, вулиця, будинок, квартира, телефон);

«Рахунок» (поля: номер клієнта, дата розрахунку, спосіб розрахунку, сума, відмітка про оплату);

«Фірми» (поля: назва фірми, номер клієнта).

Продумайте та створіть зв'язки між таблицями БД.
Внесіть у кожну таблицю БД по 5 записів.

Завдання на фільтрацію Б.Д:

Варіант І

У таблиці «Друзі» БД відсортувати прізвища учнів у алфавітному порядку. У таблиці «Хобі» дані про кількість років хобі відсортувати за спаданням.
У відкритій БД за допомогою фільтра виведіть усі дані з таблиці «Друзі» про своїх однолітків.
У відкритій БД за допомогою фільтра виведіть усі дані з таблиці «Друзі» про своїх однолітків, які користуються послугами мобільного зв'язку того самого оператора, що і ви.

Варіант II

У таблиці «Загальна інформація» відсортувати прізвища учнів у алфавітному порядку. У таблиці «Успішність» дані про успішність учнів з математики відсортувати за спаданням.
У відкритій БД за допомогою фільтра дайте відповідь на запитання, скільки учнів відвідують музичну школу.
У відкритій БД виведіть на екран за допомогою фільтра загальну інформацію лише про тих учнів, прізвище яких починається з букви «Л», а номер телефону починається на «57».

Варіант III

У таблицях «Відеокасети» і «Відеодиски» дані про кількість екземплярів відсортувати за спаданням. У таблиці «Клієнти» відсортувати дані в порядку зростання дати видачі.
У відкритій БД за допомогою фільтра виведіть на екран усі дані з таблиці «Відеодиски» про диски, які наявні лише в одному екземплярі.
У відкритій БД виведіть на екран за допомогою фільтра клієнтів, які взяли сьогодні касети чи диски під заставу паспорта.

Варіант IV

У таблицях «Підручники» і «Художня література» відсортувати дані про кількість екземплярів за спаданням. У таблиці «Відвідувачі» відсортувати дані у порядку зростання класу.
У відкритій Б Д за допомогою фільтра дайте відповідь на запитання, скільки підручників повинен отримати учень 11 -го класу.
У відкритій БД за допомогою фільтра виведіть на екран усю інформацію про літературу, яку має на руках конкретний учень 10-го класу (прізвище учня вибрати самостійно).

Варіант V

У таблиці «Клієнти» БД відсортувати прізвища клієнтів у алфавітному порядку. У таблиці «Рахунки» дані про суми оплати відсортувати за спаданням.
У відкритій БД за допомогою фільтра виведіть усі дані з таблиці «Рахунок» про клієнтів, які розрахувались готівкою.
У відкритій БД за допомогою фільтра виведіть на екран інформацію про клієнтів, номер телефону яких починається на «57», а прізвище — на «С».

Бази даних (СУБД Microsoft Office Access 2011)