Додаток 5. Ms Access як система управлінняреляційними базами даних
До баз даних звертаються в тих випадках, коли виникає необхідність зберігати і обробляти великі об’єми інформації. Використання текстового процесора для цих цілей неефективноз багатьох причин, наприклад, із-за незручності пошуку і фільтрації даних. Як, наприклад, в редакторові MS Word одержативідразу всі входження слова«Access»в тексті документа?Електронні таблиці більше підходять для розробки інформаційних застосувань, проте обмеження на розмір аркушу, відсутність механізмів зв’язування і підтримки цілісності даних заставляють відмовитися від електронних таблиць при роботі з великими базами даних складної структури. Крім вказаних причин, файли текстового процесора і електронних таблиць не дозволяютьодночасно кільком користувачам змінювати дані навіть у випадку, якщо вони працюють з різними сторінками документа.
З найзагальнішої точки зору база даних є набір записів іфайлів, організованих спеціальним чином.Іншими словами, інформація в базах даних зберігається в структурованому вигляді. Тут ми познайомимося з базами, що мають реляційну структуру організації даних.
Реляційна модель даних припускає, що дані представляються тільки одним способом, а саме у вигляді таблиць. Кожний рядок (строчка) в таблиці містить інформацію, що відноситься до деякого конкретного об’єкту. Ця інформація є набором фактів, при цьому в колонці (стовпчик) (її називають у також атрибутом або полем) міститься конкретний факт. Колонки мають заголовки (імена), які і служать для здобування потрібних фактів. Оскільки порядок колонок вважається невизначеним, то їх імена є єдиним засобом доступу до відповідного факту. Наприклад, таблиця Співробітники може мати колонки з іменами ПІБ_співробітника і Телефон, що припускає наявність в цих колонках інформаціїпро прізвище і телефон співробітника відповідно. Звичайно, СУБД не в змозі відстежувати порушення сенсу інформації, про це повинен піклуватися розробник додатку. Єдине, що може перевірити система при введенні інформації, — це типданих, що вводяться, оскільки для кожної колонки тип даних визначається при створенні таблиці. При спробі ввести інформацію, тип якої несумісний з типом даних колонки, буде отримано повідомлення про помилку перетворення типу. Слід відмітити, що поняття домену (як допустимої безлічі значень) в реляційнійтеорії частково вирішує цю проблему. Але оскільки домени підтримуються далеко не всіма СУБД (і Access не виключення), ми незупинятимемося на цьому.
Значення стовпців будуть атомарними, тобто вони можуть бути визначені тільки на простих типах даних; іншими словами, значенням стовпця не може бути таблиця. Рядки таблиці (їх називають такожзаписами або кортежами) неупорядковані, що означає, що для доступу до певного запису використовується не його порядковий номер, алише значення в певному стовпці або поєднання значень в декількох стовпцях. У зв’язку з цим особливої важливості набуваєпотенційний ключ — стовпець або набір стовпців(складений ключ), значення в якому (або відповідно набір значень) унікально для всієї таблиці. Наявність ключа для таблиці означає принципову можливість відрізнити один об’єкт бази даних від іншого.Це не завжди можливо реалізувати природним чином, наприклад, для групи однакових товарів, що не мають, скажімо, заводських номерів. У такому разі може використовуватися так званий синтетичний ключ, тобто стовпець, значення в якому не несуть ніякій інформації, а просто містять унікальні значення. Для цих цілей в Access навіть є спеціальний тип данихлічильник, який автоматично генерує унікальні значення при додаванні новому запису в таблицю.
Отже, реляційна теорія розглядає базу даних як набір таблиць. Візьмемо як приклад дві таблиці — СпівробітникиіПацієнти. Інформація в цих таблицях логічно зв’язана, оскількикожен пацієнт обслуговується або спостерігається якимсь співробітником. Такий зв’язок забезпечується наявністю в таблиці Пацієнти стовпця, що ідентифікуєспівробітника, який спостерігає цього пацієнта в таблиці Співробітники. Такий стовпець (наприклад, Код_співробітника) повинен містити значення, співпадаючі із значеннями відповідного стовпця (скажімо,ТабНомер) в таблиціСпівробітники.Для зв’язування значення в стовпці ТабНомер повинні бути унікальними, а саме цей стовпець повинен бути потенційним ключем, інакше ми не зможемо сказати, який співробітникспостерігає даного пацієнта. Природно, унікальність не потрібна для стовпця Код_співробітника, оскільки один співробітник може спостерігати будь-яку кількість пацієнтів. Таким чином, реляційна модель реалізує зв’язок не за допомогою яких-небудь покажчиків, а тільки на підставі співпадаючих значень в стовпцях різних таблиць.
У будь-якій реляційній таблиці може опинитися не один потенційний ключ, а декілька. Серед цих потенційних ключів можна вибрати один (і лише один)первинний ключ. Первинний ключ, на відміну від потенційних, повинен мати значенняв кожному рядку таблиці, тобто інформація повинна бути відома. Потенційний ключ не має цього обмеження, внаслідок чого поле потенційного ключа може містити спеціальні NULL-значення, що означають відсутність інформації.
Наявність первинного ключа забезпечує так зване правило категоріальної цілісності, або цілісності об’єктів, яке свідчить, що кожен об’єкт в базі даних повинен бутиоднозначно ідентифікований. Як наслідок, збереження запису в базі даних неможливе до тих пір, поки не буде заповнено значення первинного ключа.
Повернемося до зв’язку між таблицями. Стан бази даних, коли в таблиці Пацієнтив стовпці Код_співробітника є значення,відсутнє в стовпці ТабНомер таблиці Співробітник, називається неузгодженим. Для такого пацієнта співробітник невідомий. Ця ситуація, яка може виникнути при видаленні рядка з таблиці Співробітники або за рахунок помилки при введенні інформації пропацієнта, називається втратою посилальної цілісності. Ясно, що наявність таких "вільних" пацієнтів породила б масу проблем при роботі з базою даних.
На щастя, СУБД автоматично забезпечує посилальну цілісність за допомогоюзовнішніх ключів. Зовнішнім ключем називаютьтакий набір стовпців однієї таблиці, який служить потенційним ключем іншої (або тій же самій) таблиці. Зовнішній ключ забезпечує узгоджений стан двох таблиць. У нашому прикладі таким зовнішнім ключем може бути стовпець Код_співробітника в таблиці Пацієнти. Так от, якщо призначити стовпець Код_співробітника зовнішнім ключем до таблиціСпівробітники, тоді система буде слідкувати за узгодженістю даних, зокрема, не можна буде ввести в цей стовпець значення, не відповідне жодному з співробітників (відсутнє в стовпці ТабНомер). Також не можна буде видалити співробітника (запис з таблиці Співробітники), якщо у цього співробітника є пацієнти (зв’язані записи в таблиці Пацієнти). Таким чином, дії, що порушують посилальну цілісність, не виконуються; замість цього генеруватиметься повідомлення про помилку.
Відзначимо, що зовнішній ключ може посилатися на потенційний ключ в своїй власній таблиці; в цьому випадку він називаєтьсярекурсивним зовнішнім ключем. Типовим прикладом такого зв’язку виглядає ієрархічний зв’язок типу начальник-підлеглий.
Крім категорійної і посилальної цілісності реляційна модель декларує ще один тип обмежень, який називаєтьсяперевірочним обмеженням.
Перевірочне обмеження встановлюється для стовпця — це обмеження на введення допустимих значень в даний стовпець. Це може бути просте перерахування значень або діапазон (наприклад, between 1 and 100 — число між 1 і 100). Проте тут допускаютьсяі складніші вирази, які можуть мати посилання на інші таблиці бази даних. Ці обмеження перевіряються при всякій зміні значення у відповідному стовпці. Якщо перевірочне обмеження порушується, зміна анулюється, і видається відповідне повідомлення про помилку.
Принципово неможливо забезпечити цілісність даних, використовуючи зберігання кожної таблиці в окремому файлі. Це пов’язано з тим, що інформація про структури зберігання і обмеження цілісності даних (метадані) повинні використовуватися системою, що реалізовує доступ. Якщо ж доступ можна організувати в обхід метаданих, то можна і привести базу даних в неузгоджений стан. MS Access зберігає всі дані і метадані в одному файлі (.mdb), що гарантує перевірку всіхобмежень при доступі до даним за допомогою будь-яких додатків, що підключаються до даного файлу бази даних.
Реляційна модель була вперше запропонована в 1970 році К.Ф. Коддом [1], який також ввів дві мови маніпуляції даними — реляційна алгебра іреляційне числення. (Авторам невідомий повний переклад російською мовою робіт Кодда, проте вичерпне виклад реляційній теорії можна також почерпнути з фундаментальної книги До. Дж. Дейта [2].) Жоден з цих мов не використовується безпосередньо в реалізаціях СУБД. Проте вони послужили базою для створення мови SQL, яка є на сьогодні єдиною стандартизованоюмовою взаємодії з реляційними базами даних і підтримується всіма провідними виробниками на ринку реляційних СУБД. MS Access не буде тут виключенням. Підтримуваний цим продуктом діалект мови SQL відповідає вимогам стандарту SQL-92 (ANSI і ISO).
Це і багато що інше дозволяє стверджувати, що MS Access є істинно реляційною СУБД, і вивчення цієї програми дозволить освоїти головні принципи, які лежать в основі будь-яких інших продуктів, що використовують реляційну модель.
Засоби розробки додатків у MS Access
Під додатком MS Access ми розуміємо таку програму роботи з базою даних, створювану в середовищі MS Access, працювати з якоюзможе непідготовлений користувач. Користувач, який вводить інформацію в базу даних, щоб після деякої обробки отримати на виході (на екрані або в друкарському вигляді) всі необхідні йому документи, не зобов’язаний володіти навиками роботи з MS Access. Все, що йому може потрібно для виконання своєїроботи, повинен забезпечити простий і зручний інтерфейс додатку, що створюється розробником. Додаток повинен забезпечити захист від випадкових помилок при введенні, а також виникнення недокументованих ситуацій. Повідомлення системи про помилки, зрозумілі фахівцеві з баз даних, в додатку повинні бути прив’язані до специфіки тієї предметної області, яку моделює програма, і “не лякати” кінцевого користувача.
MS Access забезпечує захист додатку, надаючи різні права неоднаковим категоріям користувачів. Ці права визначаються обліковими записами користувачів. Авторизований вхід дозволить, наприклад, одним користувачам або групам користувачів лише проглядати дані з бази, тоді як інші зможуть редагувати їх і вводити нову інформацію. При цьомуправа надаються не тільки на таблиці і запити, які безпосередньо пов’язані з даними, але і на елементи інтерфейсу, наприклад форми і звіти. При здачі додатку замовникові зазвичай забороняється змінювати інтерфейс і програмний код. Але для певних облікових записів це вирішується (повністю або частково) з тим, щоб була можливість своєчасно відображати в додаткузміни, що відбуваються в предметній області.
MS Access дозволяє створювати системи меню і панелі інструментів, а також управляти ними програмним чином, внаслідок чого завершений додаток вже не міститиме нічого, що відноситься до середовища розробки, пропонуючи лише ті засоби, які обумовлені специфікою завдань, що вирішуються розробленим додатком. Можна навіть “заховати” вікно бази даних, яке дає безпосередній доступ до її об’єктів і об’єктів додатку, а також видалити з додатку всі тексти програм.
У даному виданні ми розглядаємо створення такого додатку на прикладі відносно простої бази даних. В процесі розробки додатку ви познайомитеся із створенням основних об’єктів MS Access, а також з програмуванням на мові VBA (Visual Basic for Applications), яка забезпечує програмну підтримку всіх продуктів пакету MS Office і дозволяєреалізувати ті завдання бізнес-логіки, які не виконуються стандартними засобами.
Розробка додатку для роботи з базами даних включає наступні етапи:
формулювання і уточнення завдань, які повинні вирішуватися в рамках додатку;
проектування таблиць, що моделюють логічну модель даних предметної області;
створення макету додатку і призначеного для користувача інтерфейсу за допомогою форм і звітів;
автоматизація бізнес-логіки предметної області за допомогою подієвих процедур і макросів;
оформлення проекту у вигляді закінченого додатку за допомогою створення головної форми запуску, управління системою меню і панелями інструментів.
На першому етапі розробникові слід з’ясувати собі ті завдання, які повинен вирішувати додаток. Це дозволить, в першу чергу, правильно спроектувати таблиці. Зазвичай на цьому етапі розробник тісно взаємодіє із замовником, щоб зрозуміти специфіку тієї області діяльності, в якій будереалізована робота з базою даних. Важливість цього етапу полягає в тому, що помилки, які тут можуть бути допущені,спричинять за собою значні зміни в додатку, якщо вони будуть відмічені на одному з подальших етапів розробки. Такі помилки ускладнюють подальшу розробку і супровід програмного продукту.
Проектування таблиць
Створення нової бази даних. Перш ніж приступити до проектування таблиць, потрібно відкритинаявну базу даних або створити нову. Щоб створити нову базу даних, запуститеMS Access(наприклад, за допомогою командиПуск=>Програми=>Microsoft Office=>Microsoft Office Access 2003).
Тут і далі символ стрілки “=>” розділяє послідовновиконувані дії користувача. Приведена вище в дужках послідовністьПуск=>Програми...означає, що потрібно клацнути на кнопці Пуск, потім в меню Програмизайти в підменюMicrosoft Office, де вибрати командуMicrosoft Office Access 2003.
Наступні дії із створення файлу бази даних залежать від версії пакету Microsoft Office. Це може бути увідне вікно (у версіях до Microsoft Office 2000 включно або панель завдань в Microsoft Office XP). Ми всюдив цьому практикумі дотримуватимемося останньої на даний момент версії Microsoft Office Access 2003.
У панелі завдань клацніть на гіперпосиланні Нова база даних....Тепер потрібно дати ім’я файлу бази даних і вказати теку, в якій вона зберігатиметься. Виконавши ці дії, клацніть на кнопціСтворити.
На відміну від багатьох інших застосувань, в яких створювані документи можна записати на диск у будь-який час, в Access файл бази даних відразу створюється на диску. Це обумовлено специфікою роботи з базами даних, що вимагає попереднього створення структури, в якій згодом зберігатимуться всі об’єкти бази даних. Тому по команді Зберегти в меню Файл поточний об’єкт (таблиця, форма, звіт і так далі) запам’ятовується вже в існуючомуфайлі бази даних. У той же самий час збереження даних (записів) виконується автоматично, коли редагований запис перестає бути поточним, тобто при переході до будь-якого іншого запису. Якщо ж з якої–небудь причини перехід до іншого запису після редагування поточної недоцільний, для збереження запису можна виконатикоманду Записи=>Зберегти запис.
У панелі завдань є інші варіанти створення нової бази даних з наявного файлу бази даних (файл .mdb) або проекту (файл .adp). Проект єдодаток, призначений для роботи з базами даних на SQL Server.Крім того, можна створити базу даних на основі наявних шаблонів типових баз даних, причому для створення деяких з них передбачена допомога майстрів. Таким чином, у вас є можливість створити придатну для роботи базу даних всього лише за декілька хвилин, практично не уміючи працювати в MS Access. Якщопередбачається розробити базу даних, близьку за структурою до однієї з типових, можна скористатися останньою як заготівкою.
При відкритті або створенні бази даних на екрані з’являється головне її вікно, показане на рис. 1. Відмітимо, що закриття цього вікна приводить до завершення роботи додатку, але не означає кінець роботи з Access. Головне вікно бази даних маєряд вкладок, кожна з яких містить відповідні об’єкти бази даних (форми, звіти, запити...) і стандартні кнопки: Відкрити, Конструктор, Створити. Крім цих кнопок, безпосередньо у вікні є декілька гіперпосилань для швидшого вибору команди створення об’єкту. Як правило, це майстри і режим конструктора. На рис. 1 у вікні бази данихвідкрита вкладка Таблиці, тому після клацання на одній з цих кнопок або гіперпосилань виконуватиметься відповідна операція для таблиці.
Тому, щоб створити таблицю бази даних, потрібно у вікні бази даних перейти на вкладку Таблиці, вибрати одне з гіперпосиланьабо клацнути на кнопці Створити. У другому випадку на екрані з’явиться діалогове вікно вибору способу створення таблиці (рис. 2). ВаріантРежим таблицідозволяє відразу вводити інформаціюв таблицю з прийнятими за умовчанням іменами стовпців (полів); при цьому тип даних поля визначається за типомінформації, що вводиться. Змінити ім’я стовпця можна, якщо виконати подвійне клацання на імені поля. Це, а також зміна типів полів, можна зробити в режиміКонструктор— основномурежимі створення або зміни структури таблиці.
Тому, щоб створити таблицю бази даних, потрібно у вікні бази даних перейти на вкладку Таблиці, вибрати одне з гіперпосиланьабо клацнути на кнопці Створити. У другому випадку на екрані з’явиться діалогове вікно вибору способу створення таблиці (рис. 79). ВаріантРежим таблицідозволяє відразу вводити інформаціюв таблицю з прийнятими за умовчанням іменами стовпців (полів); при цьому тип даних поля визначається за типомінформації, що вводиться. Змінити ім’я стовпця можна, якщо виконати подвійне клацання на імені поля. Це, а також зміна типів полів, можна зробити в режиміКонструктор— основномурежимі створення або зміни структури таблиці.
Рис. 79. Вибір способу створення таблиці
Повернемося до вибору способу побудови таблиці (див. рис. 79). Майстер таблицьдозволяє в діалозі з користувачем виконатипобудову таблиці на базі типових полів типових таблиць.
Пункти списку Імпорт таблицьіЗв’язок з таблицямидозволяють відповідно помістити в поточну базу даних копії таблиць, що знаходяться в іншій базі даних, або встановити з ними зв’язок. Останнє можна використовувати для організації роботи в мережі декількох користувачів або додатків, що працюють з одними і тими ж даними.
Щоб зрозуміти це, слідує більш докладніше зупинитися на понятті індексу.
Індекс (абоіндексний файл) — це файл, що містить значення з вибраного поля і покажчики на відповідні значенняфайлу в початковій таблиці, який називається індексованим. При виконанні запиту, в умовах відбору якого присутні значення з індексованого поля, спочатку є видимим індексний файл до знаходження потрібного значення, після чого по покажчику відразу знаходиться потрібний запис в індексованому файлі. Наявність індексного файлу, що навіть містить все без виключення значення з індексованого файлу, скорочує кількість дискових операцій введення-виводу, що істотно прискорює виконання запиту. Це відбувається за рахунок того, що в індексному файлі містяться відсортовані значення індексованого поля. Тому для знаходження певного значення в середньому потрібно буде проглянути лише половину записів в цьому файлі при його скануванні. Крім того, індексний файл значно менше початкового, оскільки не містить всіх полів і тому займає менший дисковий простір, чим початковий файл. В результаті для його перегляду потрібна в середньому менша кількість дискових операцій, які будуть найповільнішою ланкою при роботі з базою даних. З іншого боку, використання індексу уповільнює операції додавання і видалення записів, оскільки вони вимагають внесення відповідних змін і в індексний файл.
Як видно, в Access передбачено два типи індексованих полів – поля, що допускають і не допускають введення однакових значень. Наприклад, якщо в базі даних є таблиця страхових компаній, то поле з назвою компанії можна зробити індексованим із забороною значень, що повторюються. Це не дозволить двічі ввести в базу даних одну і ту ж компанію. Спроба це зробити супроводжуватиметься повідомленням про помилку.
У індексах можуть використовуватися кілька полів з індексованого файлу. Такі індекси називаються складеними. Складенііндекси прискорюють виконання запитів, що відбирають записи по декількох полях. Які індекси створювати в той або інший момент експлуатації бази даних, залежить безпосередньовід завдань, які повинні вирішувати проектоване застосування.
Щоб створити складений індекс в MS Access, потрібно в режимі конструктора необхідної таблиці виконати команду Вид/Індекси. Потім в стовпецьІндексвікнаІндекси(рис. 15) необхідно ввести назва індексу, а в стовпціІм’я поля– перерахуватиодне за одним всі необхідні поля індексованої таблиці, не повторюючи в кожному рядку назву індексу. При цьому імена полів можна вибирати із списку, що розкривається. Окрім перерахованих дій, в стовпці Порядок сортування слід вказати, як сортуватимуться значення по кожному полю індексу. Легко зрозуміти, що порядок сортування прискорюватиме вибірку, особливо у разі значень індексних полів, що повторюються (неунікальний індекс). Відзначимо, що для таблиці може бути створене декілька індексів (рис. 80).Зазвичай індекси створюються відповідно до призначення бази даних і, як правило, включають ті поля таблиці, за якими імовірно вестиметься пошук в запитах до бази даних. Якщо заздалегідь не можна визначити, скільки індексів буде потрібно для тієї або іншої таблиці і які поля слід включити в кожен з них, до цього питання можна повернутися вже в процесі експлуатації бази даних, проаналізувавши продуктивність і характер виконуваних запитів. Сказане не відноситься до первинних ключів, для яких індекс створюється автоматично, що цілком виправдано в більшості випадків.
Рис. 80. Створення складеного індексу Пацієнт_дama