Схема бази даних та цілісність даних в Access

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

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

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

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

Встановити зв’язок між двома таблицями можна при наступних умовах:

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

− обидві таблиці зберігаються в одній базі даних Access.

Якщо головна таблиця зв’язується з підпорядкованою за первинним простим або складеним ключем (або унікальним індексом) головної таблиці, Access визначає, що таблиці знаходяться у відношеннях 1:М або 1:1. Для таблиць, які знаходяться в таких відношеннях, у схемі даних можна встановити параметри підтримки цілісності даних.

Забезпечення цілісності даних означає, що Access при модифікації даних у зв’язаних таблицях бази контролює дотримання наступних умов:

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

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

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

Якщо для зв’язку забезпечується підтримка цілісності, то в Access можна задати наступні режими:

− каскадне видалення зв’язаних записів – такий режим при видаленні записів з головної таблиці забезпечує автоматичне видалення усіх зв’язаних записів з підпорядкованої таблиці. Каскадне видалення підпорядкованих записів виконується на усіх рівнях, якщо даний режим задано на кожному рівні;

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

Включення таблиць у схему бази даних і

Визначення зв’язків між ними

Розглянемо процес створення схеми бази даних ПОСТАЧАННЯ ТОВАРІВ.

6. При активному вікні Постачання Товарів: База данных (Database) на панелі інструментів натисніть на кнопку Схема даних(Relationship).

7. У вікні Show Table, що відкрилось, виберіть вкладку Таблиці (Tables) і, натиснувши кнопку Добавити (Add), розмістіть у вікні Схема даних (Relationship) усі раніше створені таблиці бази даних, список яких відображається у діалоговому вікні.

8. Натисніть кнопку Закрити (Close). В результаті у вікні Схема даних (Relationship) будуть представлені усі таблиці бази даних ПОСТАЧАННЯ ТОВАРІВ зі списками своїх полів і виділеними жирним шрифтом ключами.

9. Для визначення зв’язку між таблицями ПОКУПЕЦЬ та ДОГОВІР виділіть у головній таблиці ПОКУПЕЦЬ ключове поле КОД_ПОК, за яким встановлюється зв’язок, і при натисненій кнопці мишки перемістіть його у відповідне поле підпорядкованої таблиці ДОГОВІР.

10. Оскільки поле зв’язку є унікальним ключем в головній таблиці зв’язку, а в підпорядкованій таблиці не є ключовим, Access виявить відношення один-до-багатьох між записами цих таблиць. Значення один-до-багатьох (One-To-Many) буде відображатись у вікні Изменение связи (Edit Relationships) у рядку Тип отношения (Relationship Type). Необхідно відмітити доступний для цього типу відношень параметр Забезпечення цілісності даних (Enforce Referential Integrity).Якщо таблиці ПОКУПЕЦЬ та ДОГОВІР є порожніми або раніше були заповнені даними, які відповідають вимогам цілісності, між таблицями буде встановлено зв’язок, яка позначається на схемі як 1:¥. В іншому випадку з’явиться повідомлення про неможливість встановити зв’язок з перевіркою цілісності.

11. Встановіть параметри Каскадне оновлення зв’язаних полів (Cascade Update Related Fields) та Каскадне видалення зв’язаних записів (Cascade Delete Related Records). Це забезпечить автоматичне коригування даних у взаємозв’язаних таблицях для збереження цілісності даних.

12. Встановіть зв’язок за складеним ключем НОМ_НАКЛ та КОД_СК між таблицями НАКЛАДНА – ВІДВАНТАЖЕННЯ. Для цього в головній таблиці НАКЛАДНА виділіть обидва цих поля, утримуючи клавішу Ctrl, і перемістіть їх в підпорядковану таблицю ВІДВАНТАЖЕННЯ.

13. У вікні Зміна зв’зку (Edit Relationships) для кожного поля складеного ключа головної таблиці Таблиця/ запит (Table/ Ouery) виберіть відповідне поле підпорядкованої таблиці Зв’язана таблиця/ запит (Related Table/ Query). У цьому ж вікні встановіть режим Забезпечення цілісності даних (Enforce Referential Integrity) та інші параметри зв’язку.

14. Встановіть зв’язок для інших пар таблиць бази даних ПОСТАЧАННЯ ТОВАРІВ:

СКЛАД ® НАКЛАДНА (ключ КОД_СК),

ДОГОВІР ® ПОСТАЧ_ПЛАН (ключ НОМ_ДОГ),

ТОВАР ® ВІДВАНТАЖЕННЯ (ключ КОД_ТОВ),

ТОВАР ® ПОСТАЧ_ПЛАН (ключ КОД_ТОВ),

ДОГОВІР ® НАКЛАДНА (ключ НОМ_ДОГ).

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

Перевірка підтримки цілісності

Перевіримо, як впливають встановлені параметри підтримки цілісності при внесенні змін у таблиці.

1. Відкрийте таблиці ПОКУПЕЦЬ та ДОГОВІР в режимі таблиці.

2. Змініть значення ключового поля КОД_ПОК в запису головної таблиці ПОКУПЕЦЬ, яка має підпорядковані записи. Для завершення режиму редагування перейдіть на інший запис. Переконайтесь, що в усіх записах підпорядкованої таблиці ДОГОВІР для договорів, які були заключені даним покупцем, автоматично зміниться значення поля КОД_ПОК. Зміна відбувається через встановлений параметр каскадного оновлення зв’язаних полів.

3. Змініть значення ключа КОД_ПОК в підпорядкованій таблиці ДОГОВІР на значення, яке не існує у записах таблиці ПОКУПЕЦЬ. І переконайтесь, що така зміна є недопустима, оскільки при підтримці цілісності не може існувати запис підпорядкованої таблиці з ключем, якого нема в головній таблиці.

4. Переконайтесь, що неможливо включити новий запис в підпорядковану таблицю ДОГОВІР з неіснуючим значенням ключа КОД_ПОК в головній таблиці.

5. Виділіть в головній таблиці ПОКУПЕЦЬ запис, в якого є підпорядковані записи, і виконайте команду Правка/ Видалити. Переконайтесь, що разом з видаленими записами в головній таблиці видаляються усі підпорядковані записи в таблиці ДОГОВІР, оскільки був встановлений параметр каскадного видалення зв’язаних записів. Зауважимо, що якщо каскадне видалення записів не дозволене, то неможна видалити запис в головній таблиці, якщо є зв’язані з ним записи в підпорядкованій.

Перегляд та введення даних у зв’язані таблиці

З використанням підтаблиць

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

1. Відкрийте таблицю СКЛАД в режимі таблиці.

2. Виберіть запис про склад, для якого необхідно ввести накладні, і натисніть знак плюс (+) в рядку вибраного запису. Відкриється підпорядкована таблиця НАКЛАДНА. Якщо Access автоматично не визначить підтаблицю таблиці СКЛАД, то натисніть на значку плюс (+) в рядку запису або виконайте команду Вставка/ Підтаблиця (Insert/ Subdatasheeet) і у вікні Вставка підтаблиці (Insert Subdatasheeet) виберіть таблицю НАКЛАДНА. Якщо в головній і підпорядкованій таблицях поля зв’язку мають різні імена, то в цьому вікні можна ввести імена полів зв’язку. При складеному ключі імена полів розділяються крапкою з комою (;).

3. Створіть декілька накладних, зв’язаних з вибраним записом таблиці СКЛАД. При цьому значення ключа зв’язку КОД_СК вводиться у поле підпорядкованого запису автоматично. Таким чином, для створення підпорядкованого запису достатньо ввести лише частину НОМ_НАКЛ складеного ключа і описові реквізити.

4. Виберіть запис про накладну, для якої необхідно ввести рядки специфікації – рядки підпорядкованої таблиці ВІДВАНТАЖЕННЯ, і натисніть на значок плюс (+) у рядку вибраного запису. Відкриється підпорядкована таблиця ВІДВАНТАЖЕННЯ.

5. Введіть декілька рядків в таблицю ВІДВАНТАЖЕННЯ. При цьому значення ключа зв’язку КОД_СК і НОМ_НАКЛ вводиться у поле підпорядкованого запису автоматично. Таким чином, для створення підпорядкованого запису достатньо ввести лише частину КОД_ТОВ складеного ключа і кількість.

6. Виконайте команду Формат/ Підтаблиця/ Розвернути все (Format/ Subdatasheeet/ Expand All). Відкриються усі записи підпорядкованої таблиці, розбиті на підмножини, зв’язані з конкретними записами головної таблиці (при умові, що для зв’язку задано параметри підтримки цілісності).

Щоб закрити усі записи підпорядкованої таблиці, виконайте команду Звернути все (Collapse All).

 

Порядок виконання роботи:

1. В середовищі програми Access створіть таблиці бази даних що була спроектована в лабораторній роботі №1;
2. Створіть зв’язки між таблицями;
3. Внесіть дані в таблиці;
4. Оформіть та здайте звіт.