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

Тема 7.2. Визначення схем відношень. Віртуальні таблиці

1. Визначення схем відношень

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

Визначення схем відношень

Предметом розгляду є конструкції об’яв, змін і видалення схем відношень, що зберігаються, або таблиць (tables) (як їх прийнято називати в SQL).

Типи даних. Більшість реляційних СУБД підтримують наступні атомарні типи даних.

1. Рядки символів постійної або змінної довжини. Тип CHAR(n) містить рядок із n символів, а VARCHAR(n) – рядок із кількістю символів від 0 до n.

2. Рядки бітів постійної або змінної довжини. Тип BIT(N) позначає рядки символів у вигляді ‘0’ або ‘1’постійної довжини n, а VARYING(N) – тип атрибутів, що містить від 0 до n символів ‘0’ або ‘1’.

3. Логічні значення. Відносяться до типу BOOLEAN. Його значеннями є TRUE, FALSE і UNKNOWN.

4. Цільні чисельні значення. Відносяться до типу INT (INTEGER) або SHORTINT (зберігання меншої кількості бітів).

5. Чисельні значення з плаваючою комою. Відносяться до типу FLOAT або REAL. В SQL також визначені стандартні типи, призначені для зберігання чисел з фіксованою довжиною мантиси і дрібної частини. Наприклад, тип DECIMAL (n, d). Так
DECIMAL (6, 2) може позначати число 1234,56.

6. Значення дат і часу. В SQL використовуються типи DATE і TIME. Значення цих типів по суті являються символьними рядками, які представлені у спеціальному форматі. Мова SQL дозволяє перетворювати їх у звичайні рядки символів, а також “перетворювати ” рядки символів, які задовольняють вимогам запису дат і часу, у значення DATE і TIME.

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

Приклад 6.21. Об’ява відношення MovieStar у SQL може мати такий вигляд

1) CREATE TABLE MovieStar (

2) name CHAR(30),

3) address VARCHAR (255),

4) gender CHAR (1),

5) birthdate DATE

);

Рис. 6.9. SQL – об’ява реляційної схеми відношення MovieStar.

Як видно із об’яви відношення , атрибут ім’я актора має ім’я name і тип змінної - CHAR(30), яка є рядком символів постійної довжини у кількості 30 символів. Атрибут адреса має ім’я address, а типом обрано VARCHAR (255), який описує символьні рядки довжиною від 0 до 255 символів. Атрибут стать актора буде мати ім’я gender, а типом даних - CHAR (1), який дозволяє описувати стать одним символом ‘M’ (від male – чоловік ) і ‘F’ (від female – жінка ). Атрибут дата народження буде мати ім’я birthdate, а тип даних – DATE.

Модифікація реляційних схем.

Для видалення реляційної схеми R із бази даних використовується команда

DROP TABLE R;

Для внесення змін у схему існуючої таблиці (відношення) використовується команда
ALTER TABLE, яка містить ім’я відношення, також додаткове речення виду

1) ADD, з ім’ям атрибута А і позначенням його типу, що означає – “додати атрибут А”.

2) DROP, з ім’ям атрибута А, що означає – “видалити атрибут А”.

Приклад 6.22. Щоб поповнити схему відношення MovieStar атрибутом phone (“номер телефону ”) рядкового типу постійної довжини, рівної 10, слід виконати команду

ALTER TABLE MovieStar phone CHAR (10);

Значення по умовчанню.

Іноді у момент створення або модифікації кортежу значення окремих його компонентів не відомі. Для подолання подібних проблем в SQL використовують значення NULL, що автоматично присвоюється любому компоненту, точне значення якого у даний момент не відомо. Хоча іноді доцільно надавати інше значення, що повинно надаватися системою по умовчанню (default value), якщо інші значення не відомі.

Приклад 6.23. При створенні схеми відношення MovieStar (рис.6.9) передбачити, щоб для компонентів атрибута gender в якості значення по умовчанню, використовувався рядок ‘?’, а для атрибута birthdate – вираз DATE ‘0000-00-00’, яке інтерпретується системою як “нульова дата”. Тоді рядки 4, 5 приймуть такий вигляд:

4) gender CHAR (1) DEFAULT ‘?’

5) birthdate DATE DEFAULT DATE ‘0000-00-00’

Якщо у схему відношення MovieStar треба додати атрибут phone із значенням по умовчанню “Номер невідомий”, то команда SQL буде мати вигляд

ALTER TABLE MovieStar ADD phone CHAR (16) DEFAULT ‘ Номер невідомий ’

Індекси

Індекс (index) атрибута А деякого відношення R – це структура даних, яка дозволяє прискорити пошук деякого конкретного значення, що зберігається у компонентах атрибута А. Хоча технологія створення індексів не регламентується жодним стандартом SQL( включаючи і SQL-99), у більшості комерційних СУБД передбачені засоби, які “заставляють “ систему конструювати індекс для визначеного атрибуту конкретного відношення. Типова синтаксична форма такої команди

CREATE INDEX YearIndex ON Movie (year);

Пізніше усі SQL - запити, де у реченні WHERE буде згадуватися атрибут year, будуть виконуватися системою значно швидше, оскільки процесор запитів зможе звузити область пошуку до групи кортежів, які містять у компоненті year значення, що задовольняє заданій умові.

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

Приклад 6.24. Відношення Movie має два ключових атрибути title і year. У реченнях WHERE , як правило, першим відшукується значення title. Тоді команда створення індексу буде мати вигляд

CREATE INDEX KeyIndex ON Movie (title, year);

Знайомство із технологіями вибору індексів.

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

· Наявність індексу для певного атрибуту призводить до суттєвого підвищення швидкості обробки запитів і іноді прискорює операції з’єднання за участю атрибута.

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

Вибір адекватних індексів – одна із найскладніших стадій процесу проектування. Для її успішного “подолання ” бажано отримати достовірну відповідь на питання, яка номенклатура типових запитів та інших команд користувачів до бази даних. Якщо запити до відношення повинні виконуватися частіше, ніж команди його модифікації, доцільно створити індекси для самих популярних атрибутів відношення.

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

Приклад 6.25. Розглянемо відношення StarsIn (movieTitle, movieYear, starName).

Припустимо, що над відношенням проводяться операції (запити Q₁ і Q₂, а також процедури вставки I).

Q₁ Знайти назви і дати випуску кінофільмів, у зйомці яких приймав участь конкретний актор:

SELECT movieTitle, movieYear

FROM StarsIn

WHERE starName = s;

Q₂ Знайти імена акторів, які приймали участь у зйомці конкретного кінофільму:

SELECT starName

FROM StarsIn

WHERE movieTitle= t AND movieYear = y;

I Вставити у відношення StarsIn новий кортеж:

INSERT INTO StarsIn VALUES (t, y, s);

де t, y, s – константи

Припустимо, що існують такі умови.

1. Відношення StarsIn займає 10 дискових блоків, тому перегляд усього відношення оцінюється величиною 10.

2. У середньому кожний актор приймає участь у зйомках трьох фільмів, а у кожному фільмі знімаються 3 актори.

3. Оскільки вірогідно, що кортежі, в яких згадується або кінофільм або актор, можуть бути розподілені по усім 10 блокам, що охоплюють відношення StarsIn, для пошуку 3-х кортежів, що відповідають заданому кінофільму або актору, треба виконати 3 звертання до диску – навіть при наявності індексів для атрибута starName і пари атрибутів movieTitle і movieYear.

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

5. Аналогічно, при виконанні вставки кортежу, одна дискова операція потрібна для читання блоку, у якому буде розміщено новий кортеж, а друга – для зберігання цього блоку. Також розуміється, що навіть при відсутності індексу, система здатна відшукати блок, в який слід помістити додатковий кортеж, без необхідності перегляду усього відношення.

Таблиця 6.1

Рис.6.10 Функції трудомісткості операцій, залежні від множини обраних індексів.

де р₁, р₂ – частки часу, що витрачається системою на обробку запитів Q₁, Q₂ ; 1-р₁-р₂ – частка часу, що витрачається системою на операцію вставки I

У останньому рядку рис.6.10 вказані сумарні значення трудомісткості виконання усіх трьох операцій. Тоді для стовпця “Індексів немає ” отримуємо вираз

10р₁+10р₂+2(1-р₁-р₂) = 2+8р₁+8р₂

Розрахувати сумарні значення трудомісткості виконання усіх трьох операцій для різних варіантів утворення індексів, якщо р₁ = 0,5, р₂ = 0,1. Результуючі значення привести у рядку 5 табл.6.1. Так для стовпця “Індексів немає ” отримуємо сумарне значення

2+8*0,5+8*0,1 = 6,8.

Аналізуючи дані рядка 6 табл.6.1, приходимо до висновку, що для значень р₁=0,5;
р₂= 0,1 найменша сумарна трудомісткість операцій має у випадку, якщо створити індекс тільки для атрибуту starName. Для ситуації рядка 7 доцільним є створення індексу для атрибутів movieTitle і movieYear, для ситуації рядка 8 доцільним є створення обох індексів.

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

Відношення, визначене за допомогою команди CREATE TABLE, фізично існує у базі даних і може знаходитися у одному і тому ж стані необмежено довгий час, поки не буде змінене командами INSERT, DELETE, UPDATE, або інструкцією DROP.

В SQL підтримується визначення відношень і іншої категорії, які, на відміну від “звичайних відношень, не створюються на фізичному рівні. Такі відношення прийнято називати віртуальними таблицями (virtual tables), або представленнями (viewes). До віртуальних таблиць можна звертатися із запитами, а у деяких випадках – і з командами модифікації.

Об’ява віртуальної таблиці.

Вираз об’яви віртуальної таблиці у простішій формі складається із наступних частин:

1) пари службових слів CREATE VIEW;

2) назви R віртуальної таблиці;

3) службового слова AS;

4) запиту Q, який слугує визначенням віртуальної таблиці; при звертанні до таблиці із деяким запитом система виконує запит Q, а потім використовує його результат для обробки заданого запиту.

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

CREATE VIEW R AS Q;

Приклад 6.26. Необхідно створити віртуальну таблицю, яка представляє частину відношення

Movie(title, year, length, inColor, studioName, producerC#), а саме значення компонентів title і year тих кортежів, які містять інформацію про кінофільми, зняті на студії “Paramount”. Для рішення задачі досить виконати команду:

1) CREATE VIEW ParamountMovie AS

2) SELECT title, year

3) FROM Movie

4) WHERE studioName = ‘Paramount’ ;

Рядок 1 містить назву віртуальної таблиці ParamountMovie. Повна конструкція віртуальної таблиці приведена у рядках 2 – 4 .

Запити до віртуальних таблиць.

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

SELECT title

FROM ParamountMovie

WHERE year = 1979

У процесі аналізу запиту до віртуальної таблиці система перетворить його у аналог звертання до базової таблиці ( у даному випадку – Movie ), який буде мати наступний вигляд:

SELECT title

FROM Movie

WHERE studioName = ‘Paramount’ AND year = 1979

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

Приклад 6.27. Створити віртуальну таблицю MovieProd на основі двох таблиць Movie і MovieExec, яка має містити назву і дату випуску кінофільму, також ім’я продюсера.

CREATE VIEW MovieProd AS

SELECT title, year, name

FROM Movie, MovieExec

WHERE producerC#= cert#;

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

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

Приклад 6.28. Надати нові імена атрибутам віртуальної таблиці у прикладі 6.27.

CREATE VIEW MovieProd (movieTitle, movieYear, prodName) AS

SELECT title, year, name

FROM Movie, MovieExec

WHERE producerC#= cert#;

Модифікація віртуальних таблиць.

При виконанні певних, хоча і жорстких умов, можлива модифікація віртуальної таблиці.

Поиск по сайту: