Віртуальні таблиці візії. Перейменування атрибутів візії

Віртуальні таблиці (візії)

Визначення візії

  Інша категорія відношень (неперсистентні) не створюється на фізичному рівні.…  

Перейменування атрибутів візії

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

 

Приклад 13.11. Нехай нас цікавлять назви фільмів та їхні продюсери.

 

Напр., аби знайти ім’я (або імена) продюсера фільму Gone With the Wind, треба виконати запит

 

SELECT prodName FROM MovieProd WHERE movieTitle=’Gone With the Wind’

Модифікація змісту візій

В разі виконання певних досить жорстких умов припустимо звертатись до візії з командами вставки (INSERT), видалення (DELETE), та оновлення (UPDATE) кортежів. Для деяких порівняно простих візій (візій, що оновлюються – updatable) система в змозі перетворити команду модифікації такої візії в еквівалентну інструкцію зміни змісту базової таблиці. Стандарт SQL містить формальне визначення умов, які сприяють можливості модифікації візій. У грубому наближенні ці правила можна викласти так:

 

Візія припускає модифікацію, якщо вона визначена на основі запиту, який передбачає вибір (SELECT – не SELECT DISTINCT) значень певних атрибутів одного відношення R, яке в свою чергу також може бути візією, що відповідає цим вимогам).

CREATE VIEW S AS

SELECT DISTINCT L FROM R, S WHERE C;

 

Додаткові технічні нюанси:

• Речення WHERE C не повинно містити відношення R у складі жодного підзапиту.
• Список речення SELECT L має охоплювати достатньо атрибутів, аби при спробі вставки кортежу в візію система змогла присвоїти іншим атрибутам значення по замовчанню, передбачені в базовій таблиці. Тобто обов’язкові поля таблиці, у яких нема значень по замовчанню, мають бути у візії.

 

Приклад 13.12. Повернімося до прикладу 13.9.

Спробуємо вставити новий кортеж у візію.

Візія ParamountMovie задовольняє формальним вимогам стандарту SQL для модифікації, оскільки побудована на виборі з одної таблиці

 

Але є проблема:

Атрибут таблиці studioName не входить у набір атрибутів візії. Тому при додаванні кортежу компоненту studioName таблиці Movie буде присвоєне значення NULL, а не ‘Paramount’, як хотілось би.

 

Рішення. Треба внести у визначення візії атрибут studioName, незважаючи на те, що в відношенні-результаті виконання візії він буде містити одне для всіх кортежів значення ‘Paramount’. Оновлена інструкція створення візії:

Команда додавання кортежу в ParamountMovie тоді така:

 

Атрибути таблиці Movie такі, як lenth, inColor, producerC# невідомі під час додавання кортежу. Якщо для атрибуту lenth у схемі Movie передбачене значення по замовчанню 0, у відношення Movie було б додано кортеж:

 

Візії, що оновлюються, припускають також видалення кортежів.

 

Приклад 13.13. Нехай треба видалити з візії ParamountMovie фільми, в назві яких присутнє слово “Trek”. Для цього застосовуємо команду

 

Ця команда перетворюється в еквівалентну інструкцію видалення кортежів з таблиці Movie, де в реченні WHERE буде додаткова умова з визначення візії:

 

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

Рис.13.6. Перетворення команди оновлення візії на команду оновлення базової таблиці.

 

Нарешті, ще одна команда модифікації – видалення візії. Ії можна виконати незалежно від того, чи оновлювана візія. Приклад:

 

Після її виконання візія зникає, базова таблиця – ні. Якщо виконати команду

то зникне і базова таблиця, і візія.

 

Рис.13.7. Наслідки видалення візії та базової таблиці.

Тема 14. Обмеження та тригери в SQL

Активні елементи

  Погане (дороге) рішення: покласти піклування про коректність даних на…

Ключі відношень

Види ключів і обмеження

Якщо підмножина S атрибутів є ключем відношення R, будь-які два кортежі R повинні різнитися у значеннях компонентів хоч би одного з атрибутів S. Як наслідок, якщо схема відношення R містить оголошення ключа, R не може містити записи, що дублюються.

 

Обмеження ключа, як і багато інших обмежень, формулюються в контексті команди CREATE TABLE. Для кожного відношення можуть бути визначені:

• Лише один первинний ключ - парою службових слів PRIMARY KEY,
• Будь-яка кількість „просто унікальних” ключів - службовим словом UNIQUE.

 

Обмеження посилальної цілісності вимагає, що якщо певні значення існують у певних компонентах одного відношення, вони повинні існувати і в компонентах первинного ключа іншого відношення. Встановлення такого зв’язку передбачається в команді CREATE TABLE службовими словами REFERENCES або FOREIGN KEY.

Оголошення первинного ключа

   

Ключі та індекси

Рис.14.6. Пошук кортежів з певним значенням ключа за допомогою індексу  

Оголошення зовнішнього ключа

Оголошення зовнішнього ключа тягне дві вимоги:

• Адресуються або первинний ключ, або унікальний ключ. Разом вони інколи називаються власний ключ.
• У відношенні, яке адресується (напр., адресується відношення Деталі з відношення Залишки) має існувати відповідний кортеж.

 

Приклад 14.2. Нехай треба оголосити відношення

яке має первинний ключ name та зовнішній ключ presC#, який посилається на атрибут cert# відношення

 

Рис.14.7. Варіант 1 оголошення зовнішнього ключа шляхом надання атрибуту відповідного статусу.

Рис.14.8. Варіант 2 оголошення зовнішнього ключа у вигляді окремого речення

Стратегії забезпечення посилальної цілісності

  Існують 3 стратегії забезпечення посилальної цілісності, тобто запобігання…   По замовчанню – заборона таких операцій, які порушують цілісність. Стратегія каскадної модифікації…

Обмеження рівня атрибутів та кортежів

Розглянемо різновид обмежень, у відповідності з якими компоненти певних атрибутів відношення можуть мати значення лише з зарані обумовленої підмножині припустимих значень.

Обмеження NOT NULL

Одне з найпростіших обмежень рівня окремого атрибуту дозволяє заборонити присвоєння компонентам атрибутів значення NULL.

 

Приклад 14.15. У відношенні Studio (приклад 14.31) для заборони присвоєння компонентам presC# значень NULL треба доповнити рядок 4 так:

Наслідком цього оголошення, крім неможливості додавання кортежів без серт.№ президента, є неможливість застосування стратегії set-null (рядок 5).

Обмеження CHECK рівня атрибуту

  Обмеження CHECK перевіряється, коли компонент, для якого установлене…  

Обмеження CHECK рівня кортежу

  Оскільки обмеження рівня кортежу, умова перевіряється до кожного додавання…  

Модифікація обмежень

Приклад обмеження рівня атрибуту:  

Тригери в SQL

Тригери ще називають правилами „подія-умова-дія” (event-condition-action rules, ECA rules). Особливості тригерів як обмежень: Тригер…   Приклад 14.8 (простий). Нехай є відношення

Тригери INSTEAD OF

  Цей засіб вдало застосовується до візій, оскільки останні рідко дозволяють…  

Тригери у Transact SQL

   

Тема 15. Збережені процедури – 2 год.

Збережені процедури (stored procedures) – фрагменти програмного коду, який зберігається і виконується на сервері БД.

Переваги збережених процедур

Продуктивність

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

 

Рис.15.1. Збережені процедури зберігаються в БД

Централізація бізнес-логіки

Модифікувати збережену процедуру відповідно до нових вимог можна в будь-який момент, в одному місці. Тим самим усі програми, що використовують її, негайно прийдуть у відповідність з новими бізнес-правилами без безпосередньої модифікації.

Можливості програмування

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

 

Якщо розробникам зручно писати складні програми на таких мовах, як C + +, то

потім ці програми можна викликати з SQL Server засобами збережених процедур особливого типу, які називаються розширеними збереженими процедурами. Скомпільований на C++ код у цьому разі також зберігається в БД.

Безпека

Користувачам можна надати право на виконання збереженої процедури без безпосереднього доступу до об'єктів бази даних, з якими працює збережена процедура, виконавши тим самим інкапсуляцію даних. Крім того, якщо збережену процедуру зашифрувати при створенні або модифікації, користувачам не вдасться прочитати команди Transact-SQL, які містить процедура. Ці функції безпеки дозволяють ізолювати від користувача структуру бази даних, що забезпечує цілісність даних і надійність бази.

Категорії збережених процедур

Системні збережені процедури

починаються з префікса sp_. Вони призначені для підтримки функцій SQL Server (зокрема, процедур для роботи з переліком об’єктів сервера). До цього…   Наприклад, дуже часто адміністратору потрібно переглянути відомості про користувачів і

Локальні збережені процедури

Локальні збережені процедури зазвичай знаходяться в призначеній для користувача базі даних. Як правило, їх створюють для вирішення певних завдань, які відповідають бізнес-логіці прикладної системи, в її базі даних.

 

Рис.15.4. Локальні збережені процедури прикладної системи

Тимчасові збережені процедури

Тимчасова збережена процедура схожа на локальну, однак вона існує лише до закриття з'єднання, в якому створена, або до завершення роботи SQL Server. Залежно від типу така процедура видаляється після завершення роботи сервера або розриву з'єднання. Мінливість обумовлена тим, що тимчасові процедури зберігаються в базі даних TempDB. Ця БД створюється лише в оперативній пам’яті, тому при кожному запуску сервера ця база створюється заново, а після закриття сервера всі об'єкти цієї БД зникають.

 

У TempDb зручно також створювати тимчасові таблиці (з префіксом ##).

 

Щоб гарантувати унікальність імені тимчасової процедури, SQL Server додає до нього набір символів підкреслення і унікальний номер з'єднання. Тимчасова збережена про-

цедура видаляється з TempDB при закритті з'єднання, в якому вона створена.

Розширені збережені процедури

Розширені процедури звертаються до зовнішніх програм, скомпільованих у вигляді 32-розрядних DLL. Деякі системні збережені процедури також є розширеними. Наприклад, збережена процедура xp_sendmail, яка посилає за заданою адресою поштове повідомлення з вкладеним результуючим набором, є системною та розширеною одночасно. Угода про іменування передбачає використання в іменах розширених збережених процедур префікса хр_. Але інколи ця угода порушується.

Основи програмування у Transact-SQL

Змінні у Transact-SQL

  Всі глобальні змінні визначаються SQL Server, і не можна визначити їх…  

Блоки операторів

При управлінні ходом виконання Transact-SQL зручно інтерпретувати певну групу команд як блок. Transact-SQL дозволяє зробити це з допомогою пари команд BEGIN ... END.

 

Якщо за будь-якої командою управління ходом виконання є ключове слово BEGIN, Transact-SQL буде застосовувати цю команду управління для всіх операторів, розташованих між BEGIN і END.

Умовне виконання

Оператор IF, є найпростішим з набору команд управління ходом виконання. Якщо логічний вираз, наступний за командою IF, має значення TRUE, то буде виконаний наступний оператор або блок операторів. Якщо логічне вираз має значення FALSE, то наступний оператор або блок операторів буде пропущений.

 

Необов'язкова команда ELSE дозволяє задавати оператор або блок операторів, який буде виконуватися, якщо логічний вираз має значення FALSE. Наприклад, команди Transact-SQL, що подаються нижче, повертають 'Істина', якщо @ test має значення "істина", і "Брехня" („Хибність”), якщо ні.

 

IF @test SELECT 'Істина'ELSE SELECT 'Брехня' Оператор IF з блоками команд:IF @testBEGIN <Команда 1> ... <Команда m>ENDELSEBEGIN <Команда 1> ... <Команда n>END

CASE

В SQL Server CASE є функцією, а не командою. Вона використовується не сама по собі, як IF, а як частина оператора SELECT або UPDATE.

Синтаксис простої структури CASE:

CASE значення WHEN вираз_один THEN результуючий_вираз_один WHEN вираз_два THEN результуючий_вираз_два ... WHEN вираз_n THEN результуючий_вираз_n [ELSE альтернативное_ результуючий_вираз]END

У цій формі функції CASE, вираз результуючий_вираз повертається, тільки в тому випадку, якщо вираз, наступний за ключовим словом WHEN, дорівнює зазначеному значенню значення. Можна використовувати у виразі будь-яку кількість фраз WHEN. Фраза ELSE необов'язкова - вона виконується, тільки якщо всі фрази WHEN оцінюються як FALSE.

Рис.15.5. Фрагмент збереженої процедури з функцією CASE

Цикли

Цикл дозволяє виконувати оператор або блок операторів до тих пір, поки виконується (є істинною) зазначена умова.

 

Простий цикл WHILE

Найпростіша форма циклу WHILE містить булевий вираз і оператор або блок операторів. Оператори будуть повторюватися, поки логічний вираз не стане FALSE. Якщо при першій оцінці булевий вираз має значення FALSE, то оператор або блок операторів не буде виконуватися взагалі.

 

Рис.15.6. Приклад простого циклу

 

Складні цикли WHILE

Синтаксис оператора WHILE також дозволяє здійснювати і більш складну логіку.

Фраза BREAK викликає вихід з циклу; виконання продовжується з оператора, наступного за фразою END блоку оператора структури WHILE.

Рис.15.7. Приклад використання BREAK для виходу з циклу

 

Фраза CONTINUE повертає виконання на початок циклу, при цьому оператори, наступні за CONTINUE в межах блоку операторів, будуть пропущені.

 

Рис.15.8. Приклад використання CONTINUE для виходу на початок циклу (% : @counter по модулю 2)

 

Якщо потрібно, можна використовувати команди BREAK і CONTINUE в одному і тому ж операторі WHILE.

 

Використання збережених процедур

Виклик збереженої процедури

  EXECUTE ім’я_процедури Або

Створення збереженої процедури

  CREATE PROCEDURE ім’я_процедури[список_параметрів]AS оператори_процедури  

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

 

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

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

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

♦ Програмні засоби захисту — це комплекс математичних, алгоритмічних і програмних засобів, що сприяють вирішенню проблеми захисту даних.

 

Далі йтиметься лише про програмні засоби захисту.

 

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

 

Безпека даних може гарантуватися такими механізмами.

♦ Керування правами доступу. Адміністратор може визначити, яким користу­вачам до яких даних дозволяється доступ і які саме операції над цими… ♦ Ідентифікація та підтвердження автентичності всіх користувачів або… ♦ Автоматичне ведення журналів доступу до даних. У цих журналах протоко­люються операції, виконані над даними…

Відміна повноважень

REVOKE INSERT ON Orders FROM Adrian; - Відміняє повноваження INSERT для Adrian в таблиці Orders.   1.5. Обов’язкові методи захисту

Використання представлень для фільтрації повноважень

Вы можете сделать действия привилегий более точными, используя представления. Всякий раз, когда вы передаете привилегию в базовой таблице пользователю, она автоматически распространяется на все строки, а при использовании возможных исключений UPDATE и REFERENCES, на все столбцы таблицы. Создавая представление которое ссылается на основную таблицу и затем переносит привилегию на представление, а не на таблицу, вы можете ограничивать эти привилегии любыми выражениями в запросе содержащимся в представлении. Это значительно улучшает базисные возможности команды GRANT.

Хто може створювати представлення?

Чтобы создавать представление, вы должны иметь привилегию SELECT во всех таблицах на которые вы ссылаетесь в представлении. Если представление - модифицируемое, любая привилегия INSERT, UPDATE, и DELETE которые вы имеете в базовой таблице, будут автоматически передаваться представлению. Если вы испытываете недостаток в привилегиях на модификацию в базовых таблицах, вы не сможете иметь их и в представлениях которые создали, даже если сами эти представления - модифицируемые. Так как внешние ключи не используются в представлениях, привилегия REFERENCES никогда не используется при создании представлений.

Обмеження повноваження SELECT для певних стовпців

Предположим вы хотите дать пользователю Claire способность видеть только столбцы snum и sname таблицы Salespeople. Вы можете сделать это, поместив имена этих столбцов в представление

CREATE VIEW Clairesview AS SELECT snum, sname FROM Salespeople;

и предоставить Claire привилегию SELECT в представлении, а не в самой таблице Продавцов:

GRANT SELECT On Clairesview to Claire;

Вы можете создать привилегии специально для столбцов наподобие использования других привилегий, но, для команды INSERT, это будет означать вставку значений по умолчанию, а для команды DELETE, ограничение столбца не будет иметь значения. Привилегии REFERENCES и UPDATE, конечно, могут сделать столбцы специфическими не прибегая к представлению.

Обмеження повноважень для певних рядків

Затем Вы должны передать привилегию UPDATE в этой таблице для Adrian: GRANT UPDATE ON Londoncust TO Adrian; В этом отличие привилегии для определенных строк от привилегии UPDATE для…  

Надання доступу тільки до вибраних даних

 

Друга возможность состоит в том, чтобы предлагать пользователям доступ к уже извлеченным данным, а не к фактическим значением в таблице. Агрегатные функции, могут быть весьма удобными в применении такого способа. Вы можете создавать представление которое дает счет, среднее, и общее количество для порядков на каждый день порядка:

CREATE VIEW Datetotals AS SELECT odate, COUNT (*), SUM (amt), AVG (amt) FROM Orders GROUP BY odate;

Теперь вы передаете пользователю Diane - привилегию SELECT в представлении Datetotals:

GRANT SELECT ON Datetotals TO Diane;