Лекція №4 - Система управління базами даних MS Access, НУДПСУ

ЛЕКЦІЯ №4

План

1. Система управління базами даних MS Access

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

3. Зв’язування таблиць та формування схеми даних

4. Робота с формами даних

Система управління базами даних MS Access

Microsoft Access – це система управління базами даних реляційного типа, за допомогою якої можна швидко розробити програмне забезпечення для збереження та аналізу даних [5, 6]. MS Access відноситься до програмного забезпечення класу RAD (Rapid Application Development), що можна перевести як «швидка розробка застосувань».

В файлі MS Access зберігаються дані разом з процедурами їх обробки у вигляді одного файлу з розширенням *.mdb[7, 8]. Основними об’єктами бази даних MS Access є:

• Таблиці, які використовуються для збереження даних;

• Запити, які використовуються для вибірки даних з таблиць та їх обробки;

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

• Звіти, які призначені для виведення даних на друк;

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

Кожний об’єкт бази даних Access в межах свого класу має унікальне ім’я. Інакше кажучи, всі таблиці повинні називатися по-різному, але, наприклад, таблиця та форма можуть мати однакові імена. Робота з усіма об’єктами бази даних здійснюється у двох режимах :

1. в режимі даних – використання об’єктів для обробки даних користувачем бази даних. В цьому режимі вводяться дані в таблиці та форми, виконуються запити, звіти і т.і. Перехід в цей режим здійснюється при відкритті довільного об’єкта.

2. в режимі структури - створення та зміна об’єктів (таблиць, форм, запитів, звітів, модулів, макросів). Перехід в цей режим здійснюється за допомогою команди Конструктор. Зміна властивостей об’єкта в режимі Конструктор здійснюється за допомогою діалогового вікна Свойства.

Розробка застосування у MS Access починається зі створення таблиць та схеми даних. Після того за потребою створюють запити, форми та звіти.

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

Як зазначено вище, в MS Access таблиці використовуються для збереження даних. В комірках таблиці зберігається лише фактична інформація. З метою запобігання надлишковості даних всі значення, які можуть бути обчислені або отримані як логічний наслідок існуючих даних, не вносяться в таблицю. Такі значення називаються обчислюваними і можуть бути отримані за допомогою обчислюваних полів у запитах, представлені користувачеві за допомогою форми та надруковані за допомогою звіту. Цим таблиці MS Access принципово відрізняється від таблиць табличного процесору MS Excel, в комірках яких можуть зберігатися також і обчислювані за допомогою формул значення.

У наступній таблиці викладені основні способи створення реляційних таблиць в СУБД MS Access. Для вибору методу створення таблиці необхідно натиснути відповідну кнопку у вікні бази даних на вкладці «Таблицы» або натиснути кнопку «Создать» та обрати у вікні метод.

Таблиця 4.3. Методи створення таблиць

При визначенні значень атрибутів можна використовувати такі типи даних:

1. Текстовый – рядок до 255 символів;

2. Числовой - числові значення;

3. Дата/время – значення дати та часу;

4. Денежный – грошові значення;

5. Счетчик – унікальні автоматично зростаючі цілі значення;

6. Логический– два можливих значення (True/False, Да/Нет, 0/1);

7. Поле МЕМО – великі текстові фрагменти (до 64 Кбайт);

8. Поле OLE – об’єкти з інших застосувань (малюнки, таблиці і т.і.);

9. Гиперссылка – гіперпосилання на ресурси у мережі Інтернет;

10. Мастер подстановок – дозволяє здійснити підстановку списку значень.

Назва атрибуту визначається у вікні конструктора таблиць у стовпці Имя поля, а типа даних – у стовпці Тип данных.

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

На вкладці Подстановка (див. рис. 4.3) можна визначити підстановку для заданого поля, яка дозволяє вводити дані в поле за допомогою вибору їх зі списку або поля зі списком. Підстановка здійснюється так:

• у полі Тип элемента управления необхідно обрати значення Список або Поле со списком (в першому випадку можна обирати значення тільки зі списку, а в другому можна визначити режим введення значення заданого типу, не заданого на етапі розробки);

• у полі зі списком «Тип источника строк» необхідно обрати значення «Таблица или запрос» або «Список значений» (у першому випадку набір значень списку буде відображати вміст бази даних, а у другому випадку – буде постійним);

• при виборі значення «Таблица или запрос» у властивості «Источник строк» необхідно вказати таблицю або запит, за допомогою яких буде сформовано список можливих значень. При цьому у властивості «Присоединенный столбец» визначають номер стовпця, значення якого буде вноситись до поля (тип обраного стовпця має співпадати з типом поля), а у властивості «Число столбцов» вказують кількість стовпців, які будуть відображені на екрані при відкритті списку з шириною, визначеною властивістю «Ширина столбцов».

•  при виборі значення «Список значений» у властивості «Источник строк» необхідно задати набір значень списку.

Наприклад, в учбовій базі даних «Борей», яка входить до пакету MS Office в якості демонстраційного приклада (команда Справка, Примеры баз даннях…, Учебная база даннях „Борей”) в полі «Код Типа» таблиці «Товары» використовується підстановка у вигляді поля зі списком, у якому відображається другий стовпець „Категория” таблиці «Типы» та здійснюється підстановка значення з поля „Код типа” (значення властивості «Присоединенный столбец » становить 1), яке не відображаються у списку (значення властивості «Ширина столбцов» становить 0 см). Такий спосіб підстановки спрощує операцію вибору типу товару, бо користувач обирає назву категорії товару, а не його числовий код.

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

Зв’язування таблиць та формування схеми даних

Окремі таблиці не дозволяють представити структуру та взаємозв’язки об’єктів предметної області. Тому наступним етапом створення схеми даних є встановлення зв’язків. Зв’язок – це асоціація (посилання), що встановлюється між таблицями та дозволяє запобігти надлишковості даних. Зв’язок між таблицями встановлюється по полям, які мають однаковий тип даних. Встановлення зв’язків у Access здійснюється у вікні «Схема данных» (див.рис.4.4), для відкриття якого необхідно виконати команду Сервис ® Схема данных. У цьому вікні зв’язки між таблицями відображаються у вигляді ліній, які проведено між полями таблиці.

Зв’язки між таблицями можуть мати тип „один до одного” або „один до багатьох”. Зв’язок „один до одного” (1:1) встановлюється, коли кожному запису у першій таблиці відповідає один запис з другої таблиці та навпаки (взаємо однозначна відповідність). В цьому випадку обидва поля, за якими встановлюється зв’язок, мають бути ключовими або принаймні унікальними. Зв’язки такого типу використовуються, коли необхідно розбити велику таблицю на декілька менших таблиць з метою, наприклад, відокремлення конфіденційної інформації. Наприклад, в базі даних «Борей» таблицю «Сотрудники» можна розбити на дві таблиці «Сотрудники личные данные» та «Сотрудники служебные данные» та пов’язати їх по полю «Код сотрудника».

Зв’язок„один до багатьох” (1:М) використовується, коли кожному запису першої таблиці (головна таблиця) може відповідати декілька записів другої таблиці (підпорядкованої таблиці) з тим самим значенням пов’язаного поля. Наприклад, в базі даних «Борей» зв’язок „один до багатьох” встановлено між таблицями «Поставщики» та «Товары» по полю «Код поставщика» (рис. 4.4). Постачальник може поставляти декілька товарів, однак кожний товар може мати тільки одного постачальника. Поле «Код поставщика»в таблиці «Поставщики» и «Товары» має сумісні типи, а саме у першій таблиці воно має тип „Счетчик”, а в другій таблиці – числовий тип.

Між об’єктами предметної області може існувати співвідношення „багато до багатьох”, коли кожному екземпляру одного об’єкту відповідає багато об’єктів іншого типу та навпаки. Такий тип відношення моделюється за допомогою додаткової таблиці, яка пов’язується з першими двома зв’язками типу „один до багатьох”. Наприклад, в базі даних «Борей» зв’язок між об’єктами «Товары» та «Заказы» типу „багато до багатьох” реалізовано за допомогою проміжної таблиці «Заказано», в якій ключ складається з двох полів «Код заказа» та «Код товара». Ключове поле «Код заказа» таблиці «Заказы» пов’язано з одноіменим полем з таблиці «Заказано» зв’язком типу „один до багатьох”.

Для встановлення зв’язку між таблицями необхідно виконати такі дії:

1. закрити всі таблиці та виконати команду Сервис ® Схема данных;

2. додати необхідні таблиці до вікна схеми даних;

3. перетягнути мишею поле зв’язку з однієї таблиці в іншу;

4. у вікні „Изменение связей” (два раз ліва кнопка миші на зв’язку, див.рис.4.5) визначити властивості встановленого зв’язку.

При введенні даних та внесенні змін до таблиць існує необхідність підтримувати цілісність (несуперечливість) даних. Умовами цілісності даних називають набір правил, які використовуються для підтримки між табличних зв’язків та заборони на випадкове поновлення або знищення пов’язаних таблиць. Встановлення прапорця „Обеспечение целостности данных" дозволяє забезпечити виконання цих умов, а саме

• додавати запис у підпорядковану таблицю (зі сторони „багато”) можна лише при наявності відповідного запису у головній таблиці;

• заборонено видаляти запис з головної таблиці, якщо у підпорядкованій таблиці є відповідний запис.

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

Пом’якшити правила зміни та видалення записів у пов’язаних таблицях, якшо встановити прапорці  "Каскадное обновление связанных полей" та "Каскадное удаление связанных записей", а саме

• встановлення прапорця "Каскадное обновление связанных полей" включає режим автоматичної зміни пов’язаних записів у підпорядкованій таблиці при зміні значень первинного ключа у головній таблиці;

• встановлення прапорця "Каскадное удаление связанных записей" включає режим автоматичного видалення пов’язаних записів з підпорядкованої таблиці при видаленні значення первинного ключа з головної таблиці.

Форми

Форми є основою інтерфейсу користувача. Форми виконують дві важливих функції в базах даних:

• дозволяють вводити та редагувати дані в таблицях та запитах (форми даних);

• дозволяють здійснювати управління роботою програми (кнопочні та діалогові форми).

Перелік всіх форм розташовано на вкладниці «Формы» у головному вікні бази даних. Для кожної форми можна за допомогою відповідної кнопки на панелі інструментів форми виконати одну з таких операцій:

• Открыть – відкрити форму в режимі даних;

• Конструктор – відкрити форму в режимі конструктора для зміни структури форми.

Робота с формами даних

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

На рис. 4.6 відображена форма «Товары», яка створена на основі однойменної таблиці в базі даних «Борей». У ній відображається повна інформація щодо одного з товарів, згідно структури таблиці «Товары». Внизу форми розміщені кнопки переходу по записам (наступний, попередній, на перший, на останній). Просмотр та редагування даних у формі здійснюється в «режиме формы» або в «режиме таблицы», які відрізняються тільки формою представлення даних. Перемикання між різними режимами здійснюється за допомогою команд меню «Вид» або за допомогою кнопки «Вид», яка розташована на панелі інструментів. За допомогою форми можна змінити значення окремих атрибутів товару або додати новий товар в таблицю.

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

Для роботи з формою використовується панель інструментів „Режим формы”, яка містить кнопки для виконання операцій сортування, фільтрації, пошуку записів, створення нового запису, видалення поточного запису, перегляду властивостей форми і т.і.

Підпорядковані форми

Підпорядкована форма – це форма, яка пов’язана з іншою (головною) формою за деякою сукупністю полей. Підпорядковані форми використовуються для представлення даних таблиць та запитів, які пов’язані відношенням “один до багатьох”, причому головна форма представляє сторону “один”, а підпорядкована форма - сторону “багато”. Відображення даних у підпорядкованій формі синхронизується з головною формою таким чином, що у підпорядкованій формі відображаються тільки ті записи, які пов’язані з поточним записом головної форми. На рис.4.7 зображено форму  бази даних «Борей», яка містить підпорядковану форму.

Створення та редагування форм даних

Для створення форми необхідно натиснути кнопку «Создать» на вкладинці «Формы» у головному вікні бази даних. У діалоговому вікні «Новая форма» необхідно визначити спосіб її побудови:

• у режимі «Конструктор»;

• за допомогою майстра побудови форм (Мастер форм);

• за допомогою засобу «Автоформа» (в столбец, ленточная, табличная) на основі визначеного джерела даних (таблиці чи запиту).

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

Процес створення форми за допомогою майстра форм включає такі операції:

1. У вікні «Новая форма» обрати пункт «Мастер форм»;

2. У наступному вікні «Создание форм» обрати поля, які необхідно відобразити у формі:

- у списку «Таблицы и запросы» обрати таблиці або запити, які будуть виступати джерелом даних для форми;

- у списку «Доступные поля» обрати поля, які необхідно відобразити у формі, шляхом натиснення на кнопку «>» для вибору окремих полів або натиснення на кнопку «>>» для вибору всіх полів (для вилучення полів можна використати кнопки «<» або «<<»).

- після додавання полів з усіх необхідних таблиць або запитів натиснути кнопку «Далее».

1. Обрати зовнішній вигляд форми: «в один столбец», «ленточный», «табличный», «выровненный»;
2. Обрати стиль оформлення зовнішнього вигляду форми;

3. визначити назву форми.

Для швидкого створення простої форми, які містить усі поля таблиці або запиту, можна скористатись засобом «Автоформа» у вікні «Новая форма». У цьому випадку майстер побудує просту форму, у якій поля будуть розташовані в залежності від обраного типу форми.

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

Усі властивості основних об’єктів бази даних та елементів керування групують згідно їх призначення на групи, кожній з яких відповідає певна вкладинка у вікні властивостей:

1. Макет – властивості, які встановлюються загальний вигляд елементу або об’єкту;

2. Данные - властивості, які стосуються даних, що пов’язані з елементом або об’єктом;

3. События – властивості, які дозволяють визначити процедури, що будуть виконанні при виникненні певної події;

4. Другие – додаткові властивості;

5. на вкладці Все розташовані усі властивості.

Для більшості елементів управління визначені наступні властивості:

1. Имя – використовується для ідентифікації елемента;

2. Вывод на экран – визначає, чи буде елемент відображено на екрані у режимі форми;

3. Формат поля – визначає спосіб відображення елементу  у режимі форми;

4. Данные – визначає, які дані будуть відображені за допомогою елементу управління;

5. Значение по умолчанию – значення, яке буде автоматично просвоюватись елементу при додаванні нового запису;

6. Условие на значение – визначає умову, якій має задовольняти вводиме значення;

7. Сообщение об ошибке – текст повідомлення, яке будо відображено у вікні повідомлення при порушенні умови на вводиме значення і т.д.

Але окрім наведених властивостей кожний елемент керування має деякі притаманні лише йому властивості. Наприклад, поле зі списком має наступні важливі властивості:

1. Присоединенный столбец – вказує, який стовпець приєднано до базового поля. Дані цього стовпця зберігаються у полі при виборі рядка у списку;

2. Число столбцов – задає кількість стовпців;

3. Ширина столбцов  - визначає ширину кожного стовпця;

4. Источник строк – визначає дані, які будуть використані як елементи списку.

Розглянемо основні операції з елементами управління, а саме:

• зміна розмірів елементів управління  або  Формат, Размер;

• переміщення елемента управління та пов’язаного з ним підпису ;
  

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

Розмістити елемент на передній / на задній план Формат, на передний план/на задний план

Елементи керування можна додавати до форми за допомогою майстра або без використання майстра. У Access наявні наступні майстри побудови елементів керування: Группа переключателей, Список і Поле со списком, Кнопка, Подчиненная форма/отчет.

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

1. Объект «поле со списком» будет использовать значения из таблицы или запроса
2. Будет введен фиксированный набор значений
3. Поиск записи в форме на основе значения, которое содержит поле со списком

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

1. обрати таблицю або запит

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

3. Якщо джерелом списку є таблиця з єдиним ключом, то встановити або зняти прапорець Скрыть ключевой столбец.

4. Якщо форма має джерело даних, то обрати варіант обробки значення:

5. ввести назву списку

• Запомнить значение, тобто зберегти значення у визначеному полі бази даних

• Сохранить в поле, тобто запам'ятати значення для подальшого використання як значення поточного поля.

Обчислювані поля

У формі можуть бути використані обчислювані поля, тобто поля, в яких відображаютсья результати обчислень деяких виразів. Вираз представляє собою правильно побудовану алгебраїчну формулу, яка складається з таких складових:

• ідентифікатори елементів таблиць, запитів, форм, звітів (назви елементів управління, таблиць, запитів та інших об’єктів беруть у квадратні дужки [ , ];
  

• константи, зокрема логічні константи Истина, Ложь, перед та після дати розміщується символ решітки «#», текстові константи беруть у лапки;

• арифметичні оператори: додавання, віднімання, множення та ділення чисел (+, -, *, /);

• оператори порівняння (=, <, >, <=, >=, <>), логічні оператори (and, or, not), оператор злиття (конкатенації) текстових рядків (&);

• стандартні функції Access.

З повагою ІЦ "KURSOVIKS"!