Слайд 1Заняття 7. Віртуальні таблиці
(view – представлення)
Слайд 2Базові та віртуальні таблиці
(TABLE and VIEW)
Базові таблиці (TABLE):
фізичні об’єкти
БД, які містять дані і зберігаються в пам’яті комп’ютера на
жорсткому диску;
запит на вибірку даних до базових таблиць – тимчасова результатна таблиця, доступна лише тому, хто виконав запит.
Віртуальні таблиці (VIEW):
не є фізичними об’єктами зберігання даних;
дозволяють повертати певні поля таблиць у вигляді зв’язаного набору даних, які можуть бути доступні багатьом користувачам і існують в базі даних до тих пір, поки не будуть спеціально видалені.
Слайд 3Дані у віртуальних таблицях, подібно як і в результатних таблицях
запиту, вибираються з базових таблиць, тобто представляються в тому чи
іншому вигляді.
Параметри представлення даних у віртуальних таблицях зберігаються у розділі метаданих бази.
Працювати з віртуальними таблицями можна як зі звичайними базовими таблицями.
Будь-який новий запит до віртуальної таблиці ініціює прихований запит до базових таблиць, який комбінується з цим новим запитом.
Слайд 4Використання віртуальних таблиць (представлень)
надбудова для адаптації бази даних для різних
категорій користувачів;
потреба надати користувачу дані не в тому форматі, в
якому вони зберігаються у базі даних:
представлення забезпечують «персоналізацію» даних, перетворюючи набір нормалізованих таблиць в одну або декілька віртуальних таблиць, зрозумілих користувачу;
у представленнях можна переіменовувати поля таким чином, щоб користувачі з різним рівнем підготовки отримували дані у зрозумілих термінах;
Слайд 5вирішення проблеми захисту даних:
адміністратор бази даних надає користувачам доступ до
певної інформації лише через віртуальні таблиці;
адміністратор бази даних визначає
для користувачів такі набори результатів, які не дозволять їм бачити дані, призначені для інших користувачів;
основа для інших представлень або запитів:
представлення можуть вкладатись одне в інше, і при цьому кожен рівень буде виконувати свою конкретну функцію з перетворення базового набору даних;
складний запит, що має ієрархічну структуру, легше сформулювати, використовуючи віртуальні таблиці як допоміжні.
Слайд 6Загальний синтаксис інструкції CREATE VIEW
CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED
| MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL
SECURITY { DEFINER | INVOKER }]
VIEW <ім’я віртТабл> [(<список стовпців>)]
AS <запит SELECT>
[WITH [CASCADED | LOCAL] CHECK OPTION]
Слайд 7Створення віртуальних таблиць
Мінімально допустима інструкція створення віртуальної таблиці:
CREATE VIEW
віртТабл> AS ;
Віртуальній таблиці присвоюють ім’я, яке не повинно
співпадати ні з одним іменем базової таблиці.
За ключовим словом AS записується SQL-запит на вибірку даних.
Інструкція CREATE VIEW не вибирає дані з таблиць і не відображає їх, а лише дає вказівку СУБД запам’ятати команду SELECT як представлення з іменем <ім’я віртТабл>.
<ім’я віртТабл> використовується потім для формування запитів до цієї віртуальної таблиці.
Слайд 8Створення представлення (VIEW)
Приклад 1. (віртуальна таблиця, побудована на декількох
базових): створити віртуальну таблицю, в якій відображено імена продавців, які
обслуговують кожного замовника:
CREATE VIEW Service AS
SELECT Customers.cname AS custname, Sellers.sname AS selname
FROM Customers JOIN Sellers
ON Sellers.snum = Customers.snum;
В результаті буде створена віртуальна таблиця Service до якої можна звертатись з запитами.
Слайд 9Приклад 2. (віртуальна таблиця, побудована на декількох базових): створити віртуальну
таблицю, в якій відображено операцію-купівлі-продажу, ім’я продавця, що обслуговує дану
операцію, та суму комісійних продавця, яку він отримає за проведення операції:
CREATE VIEW Sale_Orders AS
SELECT Orders.onum AS onum,
Sellers.sname AS salename,
Orders.amt * Sellers.comm AS paym
FROM Orders JOIN Sellers
ON Sellers.snum = Orders.snum;
В результаті буде створена віртуальна таблиця Sale_Orders, до якої можна звертатись з запитами.
Слайд 10Приклад 3. (віртуальна таблиця, побудована на одній базовій): створити віртуальну
таблицю списку продавців:
CREATE VIEW Sellerslist AS
SELECT sname, city, comm
FROM Sellers;
Слайд 11Типи віртуальних таблиць
Базові представлення
Представлення з’єднання таблиць
Представлення окремих записів
Представлення окремих полів
Підсумкові представлення
Слайд 12Базові представлення
Будуються через вибірку даних з базових таблиць (див. приклад
3).
Користувачу не потрібні первинні і зовнішні ключі таблиць, тому у
віртуальні таблиці вибираються лише поля з даними.
У таких представленнях немає необхідності виконувати операції фільтрування або впорядкування базових даних.
Такий набір результатів є більш змістовним з точки зору кінцевих даних.
Слайд 13Базове представлення з’єднання таблиць
Використовується для зв’язування усіх таблиць бази даних.
Це
базове представлення, в якому присутні усі дані бази без зовнішніх
ключів.
Для формування таких базових представлень в СУБД необхідно вказувати у фразі SELECT класифікатори даних, особливо, коли вибираються поля з однаковими іменами.
Базові представлення, побудовані як з окремих базових таблиць, так і з’єднаних базових таблиць формують основу для інших запитів, які використовують усі дані.
Базові представлення відображають базові запити до усіх даних.
Представлення з’єднання таблиць будуються через вибірку конкретних полів, в якій присутні дані з однієї або декілька зв’язаних таблиць (можуть бути і небазовими).
Слайд 14Представлення окремих записів
Використовують у запиті, який їх будує, фразу
фільтрування рядків WHERE.
У деяких СУБД можна використовувати і фразу ORDER
BY для сортування рядків за значеннями окремих полів, однак у цьому випадку накладаються обмеження на модифікацію даних через представлення.
Такі представлення можна побудувати на основі базових представлень.
Слайд 15Опція для представлень окремих записів
Опція CHECK для перевірки умови фільтрування
при операціях модифікації даних.
Інструкція побудови такого представлення:
CREATE VIEW
AS
SELECT {<стовпець> AS <псевдонім>, ...}
FROM <базова_таблиця> WHERE <умова пошуку> WITH CHECK OPTION;
СУБД зберігає <умову пошуку> разом з представленням.
Кожен раз, коли користувач виконує через цю віртуальну таблицю інструкції DML, СУБД перевіряє кожну дію на відповідність критеріям у фразі WHERE.
Будь-які дії, що не відповідають цим критеріям, виконуватись не будуть.
Операції модифікації для такого типу віртуальних таблиць повинні стосуватись лише до тих рядків, які задовольняють умову фільтрування.
Слайд 16Представлення окремих полів
Використовують у запиті, який їх будує, фразу SELECT
з переліком конкретних стовпців, які необхідно вибрати.
Вибираються усі записи (без
фрази WHERE).
За потребою можна будувати комбіновані представлення окремих полів з фільтрованими записами. Такі представлення рекомендується будувати на основі базових представлень.
У деяких СУБД можна використовувати і фразу ORDER BY для сортування рядків за значеннями окремих полів, однак у цьому випадку накладаються обмеження на модифікацію даних через представлення.
Такі представлення можна побудувати на основі базових представлень.
Слайд 17Підсумкові представлення
Використовуються при аналізі даних.
Рекомендується будувати на основі базових
представлень, щоб не турбуватись про операції з’єднання таблиць.
При побудові
використовуються обчислювані стовпці, агрегатні функції, групування та інші операції, необхідні для представлення підсумкових даних.
Не допускають модифікацію своїх даних.
Слайд 18Обмеження модифікації даних у віртуальних таблицях
Будь-які зміни даних через інструкції
UPDATE, INSERT та DELETE повинні стосуватись стовпців лише однієї базової
таблиці.
Забороняється застосовувати інструкцію DELETE до віртуальних таблиць, визначених на декількох базових таблицях (В деяких СУБД це правило розповсюджується на інструкції UPDATE, INSERT та DELETE).
Забороняється модифікувати дані через віртуальну таблицю, визначену із використанням підзапитів.
Інструкція INSERT використовується лише в тому випадку, якщо віртуальна таблиця містить усі NOT NULL значення базової таблиці.
Слайд 19Забороняється модифікувати дані через віртуальну таблицю, визначену:
із застосуванням у
фразі SELECT ключового слова DISTINCT;
з використанням агрегатних функцій;
з використанням
фраз GROUP BY та HAVING.
Не дозволяється оновлювати обчислювані стовпці, тобто стовпці, значення яких є результатами обчислення виразів.
Слайд 20Зміна схеми бази даних і віртуальні таблиці
Віртуальні таблиці повинні забезпечувати
незалежність користувацьких програм від змін у логічній структурі БД.
Якщо
представлення залежить від видаленого об’єкта (базової або віртуальної таблиці), то воно не може використовуватись – його необхідно видалити.
Якщо замість видаленої базової таблиці створено нову базову таблицю, навіть з подібною структурою, то представлення і в цьому випадку необхідно видалити і побудувати заново.
Слайд 21Видалення віртуальної таблиці
Інструкція:
DROP VIEW ;
При видаленні віртуальної таблиці дані,
які в ній відображались, залишаються без змін, оскільки віртуальна таблиця
не є об’єктом зберігання даних.
Слайд 22Завдання 1. Створити представлення для власної бази даних
Створити 2 представлення
окремих полів із кількох таблиць (мінімум трьох), одне з них
із забороною оновлення даних.
Створити 1 підсумкове представлення або представлення окремих записів.
Створити 1 базове представлення із можливістю оновлення даних. Оновити деякі дані в представлені та перевірити чи вони оновилися в базовій таблиці.
Створити 1 тимчасову таблицю. Перевірити доступ до неї після завершення сеансу роботи з базою даних.