Методична розробка Основні поняття теорії баз даних як основної компоненти інформаційних систем з курсу Інформаційні системи в адмініструванні податків, НУДПСУ

ДЕРЖАВНА ПОДАТКОВА АДМІНІСТРАЦІЯ УКРАЇНИ

НАЦІОНАЛЬНА АКАДЕМІЯ ПОДАТКОВОЇ СЛУЖБИ УКРАЇНИ

Методична розробка “Основні поняття теорії баз даних як основної компоненти інформаційних систем” з курсу "Інформаційні системи в адмініструванні податків"

для підготовки магістрів

за напрямом "Економіка і підприємництво"

спеціальності 6.050114 "Оподаткування"

спеціалізації "Фіскальне адміністрування"

Ірпінь 2003

Методична розробка складена на основі навчальної програми курсу " Інформаційні системи в адмініструванні податків" і “Автоматизація роботи в органах ДПСУ” затверджених  у 2003 році.

Зміст

Вступ

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

На сьогоднішній день в Україні зареєстровано близько 40 млн. фізичних осіб і близько 700 тис. юридичних осіб, які є платниками податку. Враховуючи, що за терміном “платник” приховано великий обсяг інформації, що його характеризує (форма власності, дані про місцезнаходження, рахунки в банках, сплати податків тощо) органи ДПС для прийняття своєчасних і зважених управлінських рішень повинні обробляти величезні обсяги інформації в стислі терміни. Отже, для ефективного функціонування податкової системи необхідно використовувати сучасні інформаційні технології, які базуються на сучасній комп‘ютерній техніці і сучасному програмному забезпеченню.

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

Інформатизація – це процес створення документованої інформації (інформаційний ресурс) з одного боку, а з іншого – створення методів обробки інформації, її зберігання, пошуку, накопичення.

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

Поняття інформаційної системи

Під терміном “Інформаційна система” (Information System - IS) розуміють сукупність ресурсів, які дозволяють виконувати збір, зберігання, коригування і розповсюдження інформації всередині організації.

Типова комп‘ютеризована інформаційна система складається з таких компонентів:

1. База даних;

2. Програмне забезпечення бази даних;

3. Прикладне програмне забезпечення бази даних;

4. Апаратне(технічне) забезпечення;

5. Правове забезпечення;

6. Організаційне та кадрове забезпечення.

7. Інформаційне забезпечення.

База даних. База даних є фундаментальною компонентою ІС і її розробку (проектування) і використання слід розглядати з точки зору самих широких вимог організації. База даних – це набір логічно пов'язаних даних, що використовується для задоволення інформаційних потреб організації. Щоб глибше вникнути в суть цього поняття розглянемо його детальніше. По-перше, база даних – це єдине сховище даних. По-друге, база даних однократно визначається, а потім одночасно використовується багатьма користувачами з різних підрозділів організації. Отже, замість розрізнених файлів з надлишковими даними розташованих в різних місцях, в базі даних зібрані дані з мінімальною долею надлишковості. База даних вже не належить якомусь окремому відділу, а є спільним, тобто корпоративним, ресурсом. Причому база даних містить не тільки дані цієї організації, а і їх визначення. В сукупності визначення даних називають системним каталогом або словником даних, а конкретне визначення даних – метаданим (тобто дані про даних). Наявність в базі не тільки даних а і їх визначень забезпечує в ній незалежність прикладних програм від даних.

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

• Дозволяє визначити базу даних, що здійснюється за допомогою мови визначення даних DDL(Data Definition Language). Мова DDL надає користувачеві засоби для задання типу даних, їх структури а також для обмежень інформації, що зберігається в базі;

• Дозволяє додавати, коригувати, видаляти і одержувати інформацію з бази даних. Як правило, це здійснюється за допомогою мови управління даними – DML (Data Manipulation Language), яку ще називають мовою запитів. Існує 2 різновиди мови DML – процедурна і непроцедурна, які відрізняються лише способом одержання даних з бази. Процедурні мови обробляють інформацію послідовно – запис за записом, а непроцедурні оперують наборами записів. Процедурні мови вказують ЯК отримати необхідні дані, а непроцедурні – ЩО потрібно одержати.

• Надає контрольований доступ до бази даних з допомогою таких засобів:

• Системи безпеки даних, що не дозволяє здійснювати несанкціонований доступ до БД зі сторони користувача;

• Системи підтримки цілісності даних;

• Системи управління паралельною роботою прикладних програм, яка контролює сумісний доступ до даних;

• Системи відновлення даних після відмови апаратного чи програмного забезпечення.

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

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

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

Правове забезпечення – сукупність правових норм (законодавчі акти, положення), які регламентують порядок отримання, перетворення і використання інформації. Правове забезпечення більше торкається вхідної, щодо ІС, інформації, її структури і виду. Так перелік вхідних документів, джерелом яких є платник, налічує близько 30. Серед них: статут, свідоцтво про реєстрацію в місцевих органах, баланс підприємства і т.п.

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

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

Життєвий цикл інформаційної системи

На сьогоднішній день ключем для досягнення успіху більшості комп’ютеризованих ІС є програмне забезпечення, а не апаратне обладнання. За останні десятиліття прикладні програми виросли з малих (по функціональних можливостях і кількості рядків програмного коду) до великих і складних програмних продуктів, що налічують десятки мільйонів рядків програмного коду (скажімо ОС Windows XP налічує їх близько 40 мільйонів). Багато з цих програм вимагають постійного супроводження (виправлення виявлених в процесі експлуатації помилок, переніс на інші обчислювальні платформи, тощо). Зусилля та ресурси на супроводження катастрофічно зростали. В результаті розробка і реалізація багатьох великих проектів затягувалась, їх вартість перевищувала заплановану, а закінчений продукт виявлявся ненадійним,  складним в експлуатації і супроводженні і мав недостатню швидкодію(продуктивність). Все це призвело до ситуації, яка відома з 60-х років минулого століття як "криза програмного забезпечення", яку не подолали і до сьогоднішнього дня. У Великобританії спеціальна група по вивченню організаційних аспектів інформатики OASIG (Organizational Aspects Special Interest Group)  дослідила цю проблему і сформулювала такі висновки(1996 р.):

1. Приблизно 80-90% комп‘ютеризованих систем не мають потрібної продуктивності;

2. При розробці біля 80% систем були перевищені попередньо заплановані витрати;

3. Менше 40% систем передбачали професійне навчання та підвищення кваліфікації користувачів в повному обсязі;

4. Гармонійно поєднати інтереси бізнесу і використаної технології вдалось не більше 25% систем;

5. Тільки 10 – 20% систем повністю відповідають всім критеріям досягнення успіху.

Невдачі при створенні програмного забезпечення були спричинені:

• відсутністю повного переліку вимог;

• відсутністю прийнятної методології розробки;

• недостатнім ступенем розподілу проекту на окремі його компоненти, які б піддавались ефективному управлінню і контролю.

Для вирішення цих проблем був запропонований структурний підхід для розробки програмного забезпечення, який назвали життєвим циклом інформаційних систем (Information System Lifecycle) (або життєвим циклом розробки програмного забезпечення Software Development Lifecycle).

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

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

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

• Структура ДПС як державного органу, її підрозділи та їх взаємодія між собою;

• Інформаційні потоки, які циркулюють всередині ДПС (регламентується нормативними документами, наказами, розпорядженнями виданими керівними органами ДПА);

• Зовнішні інформаційні потоки, що надходять до органів ДПС на підставі діючого законодавства. Вони є первинними по відношенню до потоків, які циркулюють всередині ДПА;

• Функції, які визначені чинним законодавством і покладені на ДПС.

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

2. Збір та аналіз вимог користувачів. На цьому етапі збираються і аналізуються вимоги користувачів зі всіх можливих областей застосування.

3. Проектування бази даних. Повний цикл розробки складається з концептуального, логічного і фізичного проектування бази даних.

4. Вибір цільової СУБД (необов‘язково). На цьому етапі вибирають найбільш підходящу для ІС організації СУБД для реалізації в ній розробленої бази даних.

5. Розробка прикладних програм. Визначення користувацького інтерфейсу і прикладних програм, які використовують і обробляють відповідним чином інформацію з бази даних.

6. Створення прототипів (необов'язково). На цьому етапі створюється робоча модель бази даних, яка дозволяє розробникам або користувачам представити і оцінити остаточний вид і способи функціонування ІС.

7. Реалізація. На цьому етапі створюється реальна база даних і прикладне програмне забезпечення.

8. Конвертування і завантаження даних (необов'язково). На цьому етапі виконується перетворення і завантаження даних (і прикладних програм) зі старої системи в нову.

9. Тестування. Створена реалізація бази даних тестується на наявність помилок і перевіряється відповідність всім вимогам, що висувались користувачами.

10. Експлуатація і супроводження. На цьому етапі реалізація бази даних вважається повністю розробленою і реалізованою. Вся система знаходитиметься під постійним спостереженням і, в разі необхідності, вноситимуться потрібні зміни.

Зауважимо, що попередником ІС, побудованих на основі СУБД, були інформаційні системи, які створювались на основі файлових систем.  Детальніше ознайомитись з принципами роботи таких систем можна в [1]. Тут ми не будемо розглядати такі системи, лише наведемо причини по яких організації на сучасному етапі відмовились будувати свої ІС на основі файлових систем:

1. Відокремленість і ізоляція даних;

2. Дублювання даних;

3. Залежність від даних;

4. Несумісність файлів;

5. Фіксовані запити і, як наслідок, швидке збільшення кількості програм.

В подальшому основну увагу ми зосередимо на перших двох компонентах ІС, а саме – базі даних та її системі управління (СУБД). На малюнку 1 представлена традиційна схема обробки даних на основі СУБД.

Історія розвитку СУБД

Вважається, що розвиток СУБД розпочався ще в 60-ті роки, коли розроблявся проект польоту корабля Apollo на Місяць. В той час не існувало ніяких систем здатних обробляти і відповідно управляти величезним обсягом даних, які були потрібні для реалізації цього проекту. В результаті роботи над цим проектом було розроблено програмне забезпечення під назвою GUAM (Generalized Update Access Method). Основна ідея GUAM ґрунтувалась на тому, що малі компоненти об’єднуються разом як частини крупніших компонент до тих пір поки не буде зібрано воєдино весь проект. Такі СУБД назвали ієрархічними. Другим помітним досягненням 60-х років було створення мережних (network) СУБД (на основі теоретико-графського підходу), що істотно вплинуло на розвиток інформаційних систем того покоління. Мережева СУБД створювалась для представлення  більш складного взаємозв'язку між даними чим ті якими моделювались за допомогою ієрархічних структур. Вважається, що ці два типи СУБД є СУБД першого покоління.

СУБД другого покоління розроблялись на основі реляційної моделі даних. Реляційна модель вперше була запропонована Е.Ф.Коддом в 1970 році в його фундаментальній статті ”Реляційна модель даних для великих сумісно використовуваних банків даних”. Нині публікація цієї статті вважається поворотним пунктом в історії розвитку систем баз даних, при цьому варто зауважити, що ще раніше була запропонована модель, що базувалась на теорії множин (Childs, 1968). Цілі створення реляційної моделі формулювались таким чином.

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

• Створення міцного фундаменту для вирішення проблем несуперечності і надлишковості даних. В статті Кодда вводиться поняття нормалізованих таблиць, тобто таблиць без повторювання груп.

• Розширення мови управління даними операціями над множинами.

Запропонована реляційна модель була втілена в комерційному проекті, який розроблявся в кінці 70-х років в дослідницькій лабораторії корпорації IBM в Сан-Хосе, штат Каліфорнії, під керівництвом Астрахана (Astrahan). Була створена система під назвою System R, прототип істинної реляційної системи управління бази даних (РСУБД). Цей проект зарекомендував себе як дуже важливе джерело інформації про такі проблеми реалізації:

• як управляти паралельністю;

• оптимізація запитів;

• управління транзакціями;

• забезпечення захисту і цілісності даних;

• технологія відновлення даних;

• врахування людського фактору;

• розробка користувацького інтерфейсу.

Виконання проекту стимулювало публікацію наукових статей з теорії реляційних баз і створення інших прототипів РСУБД. Розробка над цим проектом дала поштовх для створення мови структурованих запитів SQL (Structured Query Language), як засобу доступу до інформації з реляційної бази даних, також для створення комерційних РСУБД, які з’явилися на ринку на початку 80-х років ( SQL/DS і DB 2 фірми IBM, а також ORACLE фірми ORACLE Corporation). В 1986 році ANSI (Американський національний інститут стандартизації) зареєстрував перший стандарт на мову SQL, в якому зафіксовані команди мови. В 1987 році ANSI-стандарт SQL був прийнятий ISO (International Organization for Standardization – Міжнародна організація по стандартизації), як всесвітній стандарт. В 1992 році ANSI вніс корективи в стандарт 1986 року. Нині діє стандарт 1992 року, відомий як SQL-92.   SQL є мовою реляційних баз даних, а не мовою системного програмування. ANSI SQL не містить ні засобів управління виконання програми (циклів, умовних переходів), ні засобів для створення форм і звітів.

Другим проектом, який відіграв помітну роль в розробці реляційної моделі, був проект INGRES (INteractive GRaphics REtrieval System), робота над яким проводилась в Каліфорнійському університеті (місто Берклі) приблизно в той же час, що і   проект System R. Ці дослідження призвели до появи академічної версії INGRES, яка внесла значний вклад для загального визнання реляційної моделі даних. Пізніше, на основі даного проекту був створений комерційний продукт INGRES.

Третім проектом  була система Peterlee Relational Test Vehicle (Засіб для тестування відношень) наукового центру корпорації IBM розташованого в місті Петерлі (Великобританія). Цей проект був більш теоретичним ніж перших два. Його результати мали велике і навіть принципове значення для обробки запитів і їх оптимізації, а також для функціонального розширення системи.

Комерційні  системи на основі реляційної моделі даних почали з’являтись в кінці 70-х – на початку 80-х років. На сьогоднішній день існує декілька сотень різних реляційних СУБД як для мейнфреймів так для ПК, хоч багато з них не повністю задовольняють точному визначенню реляційної моделі даних. Розроблені на основі моделі Кодда реляційні бази даних стали стандартами в комп‘ютерній індустрії.

Реляційна модель даних

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

Історично склалось так, що різними термінами позначаються одні і ті ж поняття. Нижче наведена таблиця трьох варіантів термінів реляційної моделі.

Відношення – це плоска таблиця, яка складається з рядків і стовпчиків.

Атрибут – це поіменований стовпчик відношення.

В реляційній моделі відношення використовуються для зберігання інформації про об’єкти, представлені в базі даних. Відношення мають вид двомірної таблиці, в якій рядки відповідають окремим записам, а стовпчики – атрибутам. При цьому атрибути можуть розташовуватись в любому порядку і незалежно від їх переупорядковування відношення буде залишатись одним і тим же, а тому матиме один і той же зміст. Приклад відношення АДРЕСНА_КНИГА наведено нижче. Уявімо собі телефонний довідник. Він містить множину рядків, кожен з яких відповідає певному індивідууму. Для кожного з них в ній представлені деякі незалежні дані, наприклад, номер телефону, адреса. Уявімо собі таку книгу у вигляді таблиці, яка містить рядки і стовпчики. Кожний рядок  відповідає певному індивідууму, кожний стовпчик містить значення певного типу даних: прізвище, номер телефону і адреса, - які є в кожному рядку. Телефонна  книга може  виглядіти таким чином:                    

Мал.2.Відношення “ АДРЕСНА_КНИГА”

Відношення АДРЕСНА_КНИГА має атрибути – Прізвище, Телефон, Адреса.

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

Кожний атрибут реляційної бази даних визначається на деякому домені. Наприклад домен атрибуту Адреса відношення АДРЕСНА_КНИГА містить назви всіх вулиць м. Києва.

Кортеж – це рядок відношення.

Елементами відношення є кортежі або рядки таблиці. У відношенні АДРЕСНА_КНИГА кожний рядок містить 3 значення, по одному для кожного атрибуту.

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

В нашому прикладі відношення “АДРЕСНА_КНИГА” має степінь 3.

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

Кардинальність відношення – це кількість кортежів відношення.

В нашому прикладі відношення “АДРЕСНА_КНИГА” має кардинальність 3.

Реляційна база даних – це набір нормалізованих відношень.

Математичні відношення

Для більш глибшого розуміння суті терміну відношення розглянемо декілька математичних понять. Припустимо ми маємо дві множини - D₁,D₂ . Декартовим добутком цих двох множин (позначається як D₁ х D₂) є множина всіх можливих  пар (a,b) де а належить множині D₁ , b - D₂ . Записується це так

D= D₁ х D₂ ={(a,b) |  a є D₁ , b є D₂}

Приклад. Нехай D₁ ={2,4} i D₂ ={1,3,5}. Тоді D₁ х D₂ ={(2,1),(2,3),(2,5),(4,1),(4,3),(4,5)}.

Потужність декартового добутку 2-х множин (кількість елементів) дорівнює добутку потужностей цих множин. Якщо через n позначити потужність множини D, через n₁ - потужність множини D₁ , через n₂ - потужність множини D_{2 ,} тоді

n=n₁ · n_{2 .}

Люба підмножина декартового добутку називається відношенням. Наприклад, підмножина {(2,1),(4,1)} множини D задає відношення R ={(2,1),(4,1)}.

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

R = {(a,b) | a Î D₁ , b Î D₂  i b=1}

На основі тих же множин можна задати друге відношення S , в якому перший елемент в 2 рази більший другого. Отже

S = {(a,b) | a Î D₁ , b Î D₂  i a=2b}

Зауважимо, що в нашому прикладі є тільки одна пара а саме – (2,1). Отже

S = {(2,1)}.

Зауважимо також, що множини D₁ і D₂  є доменами відношень   R i S  .  

Поняття відношення легко розповсюдити  на m доменів. А саме декартовий добуток m доменів D₁,D₂, … , D_m  визначається наступним чином

D₁xD₂x … xD_m ={(d₁,d₂, … ,d_m) | d₁ Î D₁, d₂ Î D₂, … , d_m Î D_m}

Властивості відношень.

Відношення повинні задовольняти таким вимогам:

• Відношення має ім‘я, яке відрізняється від імен інших відношень (унікальність імені);

• Кожна клітинка відношення містить атомарне (неподільне) значення;

• Кожний атрибут має унікальне ім‘я;

• Значення атрибуту беруться з одного і того ж домену;

• Порядок розташування атрибутів не має ніякого значення;

• Кожний кортеж є унікальним, тобто дублікатів кортежів не повинно бути;

• Порядок розташування кортежів у відношенні не має значення.

Для ілюстрації змісту цих вимог розглянемо відношення АДРЕСНА_КНИГА. Оскільки кожна клітинка повинна містити тільки одне значення, то недопускається зберігання в одній і тій же клітинці двох номерів телефонів одного і того індивідуума. Іншими словами, відношення не можуть містити повторювальних груп. Про відношення, яке має таку властивість, говорять, що воно нормалізоване і знаходиться в 1-й нормальній формі (1 НФ).

Імена стовпчиків відповідають іменам атрибутів відношення. Значення атрибуту Телефон беруться з домену "Номери телефонів" – множини номерів телефонів м. Києва. Недопускається розташування в цьому рядку інших значень, наприклад, поштових індексів. Стовпчики можна міняти місцями при умові, що атрибути переміщаються разом з його значеннями. Таким чином, таблиця буде представляти те ж відношення, якщо атрибут Телефон розташувати після атрибуту Адреса.

Значна частина властивостей відношень походить від властивостей математичних відношень.

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

• У множині елементи не повторюються. Аналогічно, відношення не містить кортежів-дублікатів.

• Кожний елемент в любому кортежі декартового добутку множин приймає єдине значення. Аналогічно, кожна клітинка відношення містить єдине значення.

Реляційні ключі

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

Суперключ – це атрибут або множина атрибутів, яка єдиним чином визначає кортеж даного відношення.

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

Приклад. Нехай задано відношення R (kod,rik,kvartal,typ,sum), де атрибут kod задає ідентифікаційний код підприємства, атрибут rik задає рік, kvartal – квартал, typ – код податку, sum – відповідну суму податку. Якщо вважати, що код підприємства kod є унікальним, тобто не має двох різних підприємств з одним і тим же кодом, тоді множина всіх атрибутів N={kod,rik,kvartal,typ,sum }а також множина K={kod,rik,kvartal,typ} є суперключами, множина K є потенційним ключем, а множина {rik,kvartal,typ} не є суперключем, а отже і потенційним ключем.

Первинний ключ – це потенційний ключ, який вибрано для однозначного визначення кортежу всередині відношення.

Оскільки реляційні відношення не містять кортежів-дублікатів, завжди можна задати первинний ключ для даного відношення. В найгіршому випадку вся множина атрибутів може використовуватись як первинний ключ. У вище згаданому відношенні R множина атрибутів K є первинним ключем, причому єдиним.

Зовнішній ключ  – це первинний ключ, в якому один або більше атрибутів належить декільком відношенням.

Приклад. Нехай крім R визначено ще відношення Q (kod,name), де атрибут задає ідентифікаційний код підприємства, атрибут name – назву підприємства. Первинним ключем для Q, очевидно є множина атрибутів M={ kod }. Оскільки атрибут kod є і в K і M, то kod є зовнішнім ключем.

Реляційна цілісність

Визначають два правила цілісності реляційної моделі – цілісність сутності і цілісність посилань. Перш ніж розглянути ці два правила, розглянемо поняття NULL.

NULL означає невизначеність. Тобто, якщо значення певного атрибуту невизначене, говорять, що цей атрибут приймає значення NULL.

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

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

Пояснимо це на прикладі. Припустімо що значення атрибуту kod відношення R пробігає множину D₁ а значення атрибуту kod відношення Q – множину D₂. Тоді різниця множин D₁ \ D₂ має бути пустою.

Реляційна алгебра

Існує декілька варіантів набору операцій, які включають в реляційну алгебру. Спочатку Кодд запропонував 8 операторів, потім було доповнено ще декількома. Ми розглянемо 5 основних операцій реляційної алгебри, а саме виборка(selection), проекція (projection), декартів добуток (cartesian product), об’єднання (union) і різниця. На основі цих 5 можна вивести додатково операції з’єднання (join), перетину (intersection) і ділення (division). Операції виборки і проекції є унарними оскільки вони оперують з одним відношенням, а інші операції бінарні – вони оперують з двома відношеннями. Наведемо, скориставшись загальноприйнятими символічними позначеннями, неформальні визначення цих операцій.

Декартів добуток. Операція декартового добутку двох відношень (записується так: RxS) визначає нове відношення, яке є результатом конкатенації (зчеплення) кожного кортежу відношення R з кожним кортежем відношення S. Приклад декартового добутку представлено на мал.3.

Виборка. Результатом операції виборки s_{предикат} над відношенням  є знову відношення, яке містить лише ті кортежі, які задовольняють заданій умові (предикату). Предикат задає умову відбору на значення атрибутів відношення. Приклад операції виборки: s_B>1(U)=W, де W є таке відношення:

Проекція - Õ_{атр1, … ,атрN}(R) визначає нове відношення, що містить кортежі складені зі значень атрибутів атр1, … ,атрN і при цьому кортежі-дублі виключаються. Іншими словами, з таблиці, яка є представленням відношення R викидаються всі стовпчики, які не входять до списку атрибутів атр1, … ,атрN, потім з одержаної таблиці викидаються рядки-дублікати. Приклад: Õ_A,B(V) , Õ_A,B(L) матимуть вид:

Об’єднання. Операція об’єднання застосовується лише до відношень, які мають однакові множини атрибутів і однакові домени для відповідних атрибутів. Якщо це виконується, відношення називають сумісними по об’єднанню. Отже, якщо R i S сумісні по об’єднанню відношення, тобто мають одну і ту ж множину атрибутів A,  і мають множини кортежів I i J, то об’єднання Q=RÈS маємножину атрибутів A і множину кортежів I È J. Нижче наведено приклад об’єднання відношень.

Перетин. Операція перетину застосовується лише до сумісних по об’єднанню  відношень. Отже, якщо R i S сумісні по об’єднанню відношення, тобто мають одну і ту ж множину атрибутів A,  і мають множини кортежів I i J, то перетин Q=RÇS маємножину атрибутів A і множину кортежів I Ç J. Нижче наведено приклад перетину  відношень.

Різниця. Різниця R-S складається з кортежів відношення R яких немає у S. При цьому R і S сумісні по об’єднанню відношення. Множина кортежів R-S співпадає з теоретико-множинною різницею I \ J.

Декартів добуток. Якщо відношення R має  множину атрибутів А і множину кортежів I, відношення S – множину атрибутів B і множину кортежів J, тоді декартів добуток Rх S цих відношень матиме множину атрибутів АхB і множину кортежів IxJ.

Операції з’єднання

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

• Тета-з’єднання (q-join);

• З’єднання по еквівалентності (equal-join), яке є частинним випадком тета-з’єднання;

• Натуральне з’єднання (natural join);

• Зовнішнє з’єднання (outer join).

Тета-з’єднання (q-join). Операція тета-з’єднання відношень R µ_F S визначає відношення, яке містить кортежі з декартового добутку RxS, що задовольняють предикату F. Предикат F має вид R.a_i Ñ S.b_j де замість Ñ можна вказати один із операторів зрівнювання (<, <=, >, >=, =).

Наведемо приклад тета-з’єднання відношень T i U, представлених на мал.3. Відношення G=T µ_T.B<=U.B U, яке є тета-з’єднанням відношень T i U по предикату T.B<=U.B матиме вид:

З’єднання по еквівалентності (equal-join) – це тета-з’єднання предикат якого містить тільки оператор =. На мал.3 представлено відношення V, яке є з’єднанням по еквівалентності відношень T і  U.

Натуральне з’єднання (natural join) – це з’єднання по еквівалентності по всіх спільних атрибутах цих відношень.

Ліве зовнішнє з’єднання (left join). Лівим зовнішнім з’єднанням (left join) відношеннь R і S, називається з’єднання, в якому кортежі відношення R , які не мають однакових значень в спільних атрибутах відношення S, також включаються в результуюче відношення. Приклад лівого з’єднання показано на мал.3 (відношення L) .

Нормалізовані відношення

При проектуванні бази даних в реляційній СУБД основною метою розробки логічної моделі даних є створення точного представлення даних, зв‘язків між ними і необхідних обмежень. Для досягнення цієї мети необхідно перш за все визначить необхідний набір відношень. Метод, який використовують для вирішення цієї задачі, називається нормалізацією. Спочатку було запропоновано тільки 3 види  нормальних форм: перша (1НФ) , друга (2НФ) і третя (3НФ), які ми і розглянемо. Потім з’явились визначення четвертої (4НФ) і п‘ятої (5НФ). Слід зауважити, що на практиці 4 і 5 нормальні форми зустрічаються рідко.

Функціональні залежності. Нехай відношення R має 2 атрибути A i B. Говорять, що атрибут B функціонально залежить від атрибуту A і це позначають A ® B, якщо кожне значення атрибуту A відповідає лише одному значенню атрибуту B, тобто, якщо в двох різних кортежах відношення R атрибут A приймає однакове значення, тоді і атрибут B в цих кортежах приймає однакове значення. Аналогічним чином можна розповсюдити функціональну залежність атрибуту від множини атрибутів. А саме, говорять, що атрибут B функціонально залежить від атрибутів A₁, A₂,... A_n, якщо у відношенні є 2 кортежі у яких ці атрибути приймають значення a_1, a₂,... a_n відповідно, тоді однакове значення у цих кортежах приймає атрибут B.

Наведемо приклади. Нехай відношення S(ID,P,G,F) має такі атрибути:

• ID – табельний номер студента;

• P – Прізвище, ім'я, по-батькові;

• G – Назва групи, в якій навчається студент;

• F – Назва факультету, на якому навчається студент.

Тоді очевидні такі функціональні залежності: 1) ID® G(Студент може числитись лише в одній групі); 2) ID, P® G(теж саме);3) G® F(Одна і та ж група належить одному факультетові). А залежності  P® G не існує, оскільки є однофамільці.

Перша нормальна форма (1НФ). Відношення, в якому на перетині кожного рядка і кожного стовпчика міститься лише одне значення, знаходиться в 1НФ.

Друга нормальна форма (2НФ). Відношення, яке знаходиться в 1 НФ і кожний атрибут A якого, що не входить в первинний ключ, повністю визначається первинним ключем і ніяка підмножина атрибутів первинного ключа не визначає A.

Третя нормальна форма (3НФ). Транзитивна залежність. Якщо для атрибутів деякого відношення є залежності A ® B i B ® C, то говорять, що атрибут C транзитивно залежить від атрибуту A. Відношення, яке знаходиться в 1НФ і 2НФ і не мають атрибутів, що не входять в первинний ключ, які транзитивно залежали б від атрибутів з первинного ключа.

Відношення S перебуває в 1НФ і в 2НФ і не перебуває в 3НФ, оскільки F транзитивно залежить від ID.    

Реляційна модель звільняє користувача від взаємодії з фізичною структурою даних, їх розташуванням на фізичних носіях. Замість цього вона базується на логічних зв‘язках, які описуються з допомогою реляційної мови. Спочатку Кодд визначив 13 правил реляційної моделі. В подальшому Кодд розширив своє визначення реляційної моделі, які відомі як RM/V1 RM/V2 (реляційна модель версії 1 і версії 2). RM/V1 складається з 50 характеристик, а RM/V2 містить 333 характеристик.

Коли СУБД можна вважати реляційною

Існує декілька сотень СУБД для мейнфреймів і персональних комп’ютерів. Кожний розробник СУБД заявляє, що розроблена ним СУБД є реляційною. На превеликий жаль багато з них не є реляційними. Стурбований тим, що потенційні можливості реляційної моделі спотворюються, Кодд (Codd, 1985) запропонував 13 правил визначення реляційних систем.

На протязі багатьох років запропоновані Коддом правила мали масу нарікань зі сторони спеціалістів. Одні заявляли, що ці правила є чисто теоретичними вправами, інші – що їх СУБД задовольняють багатьом цим правилам, якщо не всім. Ці правила можна розбити на 5 функціональних груп:

1. Фундаментальні правила.

2. Структурні правила.

3. Правила цілісності.

4. Правила управління даними.

5. Правила незалежності від даних.

Фундаментальні правила (правила 0 і 12)

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

Це правило означає, що СУБД не повинна використовувати нереляційні операції для виконання таких видів робіт як визначення даних, маніпулювання даними.

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

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

Структурні правила (правила 1 і 6)

Правило 1 – представлення інформації. Вся інформація в реляційній базі даних представляються в явному виді на логічному рівні і тільки одним способом – у вигляді значень в таблицях.

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

Правило 6 – поновлення представлень. Всі представлення, які є теоретично поновлюваними, повинні бути поновлюваними в даній системі. Зауважимо, що на сьогодні ні одна система не підтримує цього правила.

Правила цілісності (правила 3 і 10)

Правило 3 – систематична обробка невизначених значень (NULL). Невизначене значення, яке задається з допомогою NULL, підтримується для систематичного представлення відсутньої або неприпустимої інформації.

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

Правила маніпулювання даними (правила 2,4,5 і 7)

Ідеальна РСУБД повинна підтримувати 18 функцій управління даними. Вони визначають повноту мови запитів (тут термін “запит” включає і операцію вставки, поновлення і видалення).

Правило 2 – гарантований доступ. Для всіх і кожного елементу даних реляційної бази даних повинен бути гарантований доступ на основі комбінації імені таблиці, значення первинного ключа і значення імені стовпчика.

Правило 4 – динамічний інтерактивний каталог, побудований за правилами реляційної моделі. Опис бази даних повинен представлятись на логічному рівні таким же чином як і звичайні дані. Це дозволить користувачам використовувати для звертання до цього опису ту ж реляційну мову, що для даних.

Це правило вказує на те, що повинен існувати лише одна мова для маніпулювання як даними так і метаданими.

Правило 5 – вичерпна мова даних. Реляційна система може підтримувати декілька мов і різні режими роботи з терміналами. Але повинна існувати по меншій мірі одна мова, оператори котрої реалізовували такі конструкції:

1) визначення даних;

2) визначення представлень;

3) команди маніпулювання даними (доступні як в інтерактивному режимі так і з програм);

4) обмеження цілісності;

5) авторизація користувачів;

6) організація транзакцій (запуск, фіксація, відкат).

Слід зауважити, що новий стандарт ISO для мови SQL забезпечує виконання всіх цих функцій.

Правило 7 – високорівневі операції вставки, поновлення і видалення. Здатність обробляти базові або похідні відношення (представлення) як єдиний операнд повинна відноситись не тільки до процедури виборки даних а і до операцій вставки, поновлення і видалення.

Правила незалежності від даних (правила 8,9 і 11)

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

Правило 8 – фізична незалежність від даних. Прикладні програми і засоби роботи з терміналами повинні залишатись логічно незмінними при внесенні любих змін в способи зберігання даних або методи доступу до них.

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

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

Основні типи даних визначених стандартом ISO

В базах даних і мові SQL існує 6 скалярних типів даних, визначених стандартом ISO. Їх короткий опис наведено в таблиці. В деяких випадках для спрощення маніпулювання і перетворення даних типу character i bit об’єднуються в один тип під загальною назвою “строкові типи даних”, а дані типів exact numeric i approximate numeric – під назвою “числові типи даних”. В подальшому ми використовуватимемо квадратні дужки для позначення необов’язкового елементу, наприклад, [a].

Символьні дані ( тип character)

Символьні дані - це послідовності символів з визначеного розробником СУБД набору символів. Для визначення даних символьного типу використовується такий формат:

         CHARACTER [VARYING] [LENGTH]

CHARACTER може бути скорочено до CHAR а CHARACTER VARYING до VARCHAR. Наприклад, NAME char (20) – означає, що поле з назвою NAME має символьний формат з довжиною 20 byte. Зауважимо, що в кожному записі під поле NAME відведено 20 байт (фіксована довжина), а ADRESA VARCHAR(50) означає, що поле ADRESA має змінну довжину(максимум до 50 символів). Поле типу VARCHAR займає в пам‘яті рівно стільки місця, скільки необхідно для зберігання реального значення поля, але не більше зазначеного у визначенні числа (в наведеному прикладі – 50). Поля типу   VARCHAR можуть мати любу довжину, яка не перевищує деякого максимуму, визначеного в конкретній СУБД.

Бітові дані (тип bit )

Для визначення даних бітового типу використовується такий формат:      

         BIT [VARYING] [length]

Наприклад, bit_str BIT(4) – означає, що в полі bit_str зберігається строка бітів фіксованої довжини 4.

Точні числові поля (Exact NUMERIC)

Тип точних числових даних використовується для чисел, які мають точне представлення в комп’ютері. Існує декілька способів визначення даних точного числового типу:

         NUMERIC [precision[,scale]]

         DECIMAL [precision[,scale]]

         INTEGER

         SMALLINT

Зауваження. INTEGER може бути скороченим до INT а DECIMAL до DEC, precision – кількість десяткових цифр числа, а scale – кількість десяткових цифр після коми.

Наприклад, d DECIMAL (8,4) означає, що поле d є числовим полем в якого 8 знаків (включно з крапкою) і 4 знаки після десяткової крапки. NUMERIC співпадає з DECIMAL.

INTEGER ціле число. SMALLINT – коротке ціле.

Наближені числові поля.

FLOAT [precision]

REAL

DOUBLE PRECISION       

Параметр precision задає значність мантиси і залежить від конкретної реалізації.

FLOAT – число з плаваючою точкою, яке представлене в експоненціальній формі з основою 10, наприклад,  FLOAT (8).

REAL – співпадає з FLOAT, за виключенням того, що розмір не використовується.

DOUBLE PRECISION (або DOUBLE) – співпадає з REAL, хіба що точність для конкретної реалізації перевищує за REAL.

Дата і час (тип datetime)

DATE

TIME [time_precision] [with_time_zone]

TIMESTAMP [time_precision] [with_time_zone]

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

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

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

Формати представлення дат

Формат TIME

Завдяки формату визначеного для дат, над полями типу DATE можна виконувати арифметичні операції, зрівнювати їх.

Наприклад, до дати можна додати число і одержати нову дату. Різниця 2-х дат є число.

Коротка характеристика сучасних СУБД

На сьогоднішній день на інформаційному ринку присутні такі СУБД:

1. DB2. СУБД розроблена фірмою IBM. На сьогоднішній день ця СУБД сумарно зберігає і управляє найбільшим об‘ємом інформації.

2. ORACLE. Ця СУБД розроблена одноіменною фірмою ORACLE. Друга СУБД по сумарному об‘єму інформації.

3. MS SQL Server. Ця СУБД розроблена фірмою Microsoft і глибоко інтегрована в операційну систему Windows NT.

4. Sybase. Ця СУБД розроблена фірмою Sybase, слід зауважити, що до недавнього часу команди робітників фірми Sybase і фірми Microsoft працювали разом. Тому ці СУБД (Sybase і SQL Server) дуже схожі. Зараз ці команди працюють кожна над своїм проектом.

5. Інші СУБД до яких слід віднести такі: Interbase, Informix.

1) Вище згадані СУБД є СУБД клієнт-серверного типу. Що таке архітектура клієнт-сервер? В архітектурі клієнт-сервер множина комп‘ютерів об‘єднана в мережу, в якій всі комп‘ютери поділяються на клієнтів і серверів. Користувачі безпосередньо взаємодіють з клієнтами для виконання переважної кількості функцій. Сервери виконують різні інтенсивні завдання на запити клієнтів. СУБД, як правило, знаходиться на сервері і займається обслуговуванням вимог клієнтів. Вимоги клієнта до сервера формулюється на мові SQL. Оскільки мова SQL лаконічна, мережа не перевантажена передачею детальних інструкцій від клієнта до сервера. Одержавши команду SQL, сервер може виконувати цю команду без подальшої участі клієнта. В архітектурі клієнт/сервер важливим є питання про зв‘язки. Клієнт повинен бути зв‘язаним з сервером для взаємодії з ним.

2) Кожна з цих СУБД містить в собі систему ідентифікації і аутентифікації, тобто системи розподілу прав доступу до даних. Обробка інструкцій GRANT (призначення привілеїв доступу) і REVOKE (відміна привілею).

3) Кожна з цих СУБД підтримує обробку транзакцій. Транзакція – це послідовність команд DML (Update, Insert, Delete), об‘єднаних в групу, які або виконуються всі, або не виконуються жодна. Помилка транзакції призводить до того, що вся послідовність може бути відмінена (Canceled), або для неї може бути виконаний відкат. Люба транзакція закінчується інструкцією COMMIT WORK (для того, щоб зберегти зміни), або інструкцію ROLLBACK (при відмові від внесених змін або у випадку неполадок в системі). Якщо транзакція не може бути відновлена з якихось причин, потрібно виконати її відкат.

4) Кожна з цих СУБД підтримує ведення журналу транзакцій.

5) Кожна з цих СУБД містить засоби для зберігання і виконання процедур і тригерів.

Крім вищезгаданих СУБД клієнт/серверного типу на інформаційному ринку функціонують СУБД настольного типу, які призначені функціонувати на ПЕОМ. Ці СУБД не підтримують ні транзакцій, не мають системи розподілу доступу, не ведуть для зберігання і обробки процедур і тригерів. До цього класу СУБД можна віднести такі:

1. dBASE (на ринку програмного забезпечення з 1983 року).

2. FoxBase, FoxPro.

3. Paradox.

4. Clipper.

5. Clarion.

6. MS Access.

СУБД цього класу мають одну характерну рису (крім MS Access) – кожна таблиця в таких базах є окремим файлом з тим же ім‘ям. Це означає, що крім засобів самої СУБД ми можемо скористатись засобами операційної системи для перегляду інформації з таблиць, змінювати інформацію в таблицях, і таке інше. Це є порушенням правил Кодда. Отже, ці СУБД не є реляційними. Вони не підтримують в повному обсязі мови SQL.

СУБД MS Access займає проміжне місце між локальними базами і базами клієнт/серверного типу. Доступ до таблиць можливий лише засобами СУБД (всі таблиці базі зберігаються в одному файлі операційної системи з розширенням mdb). Є система ідентифікації, хоча вона реалізована засобами середовища Access, а не через мову SQL, як цього вимагає стандарт SQL-92.

Основи мови SQL

SQL - це абревіатура від Structured Query Language, що в перекладі з англійської означає мова структурованих запитів. Мова SQL орієнтована на реляційні бази даних, про які велась мова в попередніх лекціях. В ідеалі, люба мова для роботи з базами повинна надавати користувачеві такі можливості:

• створювати бази даних і таблиці з повним описом їх структури;

• виконувати основні операції маніпулювання даними (вставка, поновлення, видалення даних з таблиць);

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

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

• мова DDL (Data Definition Language), засобами якої визначаються структури бази;

• мова DML (Data Manipulation Language), засобами якої здійснюється виборка і поновлення даних.

Мова SQL може використовуватись двома способами. Перший передбачає інтерактивний режим роботи, який полягає в тому, що користувач з терміналу вводить окремі SQL-оператори. Другий метод полягає в тому, що SQL-оператори вбудовуються певним чином у процедурні мови програмування (C, C++ ). В основному ці 2 форми SQL працюють однаково. На практичних роботах ми будемо користуватись інтерактивною формою.

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

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

Запис SQL-операторів. Мова SQL містить зарезервовані слова, а також слова, які визначаються користувачем. Зарезервовані слова є постійною частиною самої мови SQL і мають фіксоване значення. Їх потрібно записувати так як це визначено мовою, їх не можна розбивати на частини для переносу з одного рядка в інший. Слова, що визначаються користувачем, задаються самим користувачем ( у відповідності з визначеними синтаксичними правилами) і є іменами різних об’єктів бази – таблиць, стовпчиків, представлень, індексів, тощо.

Слова в операторі розташовуються у порядку визначеному синтаксичними правилами мови. Більшість компонентів SQL-оператора не чутливі до регістру. Це означає, що можна використовувати як малі так і великі букви. Наприклад, зарезервоване слово Select можна записати і так: SELECT – обидва записи еквівалентні. В подальшому ми будемо притримуватись таких правил:

• Великі букви використовуватимемо для написання зарезервованих слів мови SQL.

• Малі букви латинського алфавіту будемо використовуватимемо для написання слів, які визначаються користувачем.

• Вертикальна риска ( | ) вказує на необхідність вибору одного з декількох наведених значень – наприклад, a | b | c.

• Фігурні дужки визначають обов’язковий елемент – наприклад, { a }.

• Квадратні дужки визначають необов’язковий елемент – наприклад, [a].

• Трикрапка ( … ) використовується для необов’язкового повтору певної конструкції від 0 до декількох раз – наприклад, {a|b}[,c…]. Цей запис означає, що після a або b записується від нуля до декількох повторів с, відокремлених комами.

На практиці для визначення таблиць використовуються DDL-оператори, а для заповнення цих таблиць даними і виборки з них використовуються DML-оператори. Спочатку ми розглянемо DML-оператори, а потім DDL-оператори.

До DML-операторів мови SQL відносяться такі оператори:

• SELECT – виборка даних з бази;

• INSERT – вставка даних у таблицю;

• UPDATE – поновлення (заміна) даних в таблиці;

• DELETE – видалення даних з таблиці.

Числові константи SQL. Любе число є константою, наприклад, 200,3,0.9.

Символьні або текстові константи. Люба послідовність букв і цифр є символьною константою. Символьні константи поміщаються в одинарні кавички, наприклад, ‘Школа’,’5-та школа’,’200’. Зауважимо, що константи 200 і ‘200’ є різними в SQL. Константи типу TIMESTAMP записуються у виді:

{ts 'YYYY-mm-dd hh:MM:SS'}

де YYYY означає рік, mm – номер місяця(1 – 12), dd – календарний день(1 – 31)

Константи типу DATE записуються у виді:

{d 'YYYY-mm-dd'}

Отже, константа {ts '2003-9-11 8:30:00'} позначає 11 вересня 2003 року 8 годин 30 хвилин.

В мові SQL константи як числові так і нечислові називають одним словом – літерал.

Приклад використання літералів в операторі INSERT:

INSERT INTO ustanova(kod,nazva) VALUES(10234,’Академія ДПС України’)

Цей оператор вставляє в таблицю ustanova новий рядок значення першої колонки стає 10234 (ця колонка має числовий формат), а другої колонки (вона має текстовий формат) – “Академія ДПС України”.

В мові SQL діють арифметичні операції – додавання, віднімання, множення і ділення. Ці операції позначаються:

• + - додавання (сума);

• - - віднімання (різниця);

• * - множення (добуток);

• / - ділення (частка).

Алгебричні вирази. Люба числова константа є алгебричним виразом. Люба назва стовпчика таблиці є алгебричним виразом. Алгебричні вирази називають сумісними по типу, якщо вони мають однаковий тип (символьний, числовий). Якщо A i B алгебричні вирази числового типу , то A+B, A-B, A*B, A/B - алгебричні вирази числового типу. Наприклад, 100/2, (column_name*0.2)/1.2, column_name1*column_name2+100. Якщо вирази А і B мають строковий тип, то A+B є строковим виразом. Наприклад, ‘Академія’+’ ДПСУ’, column_name3 + ’зараховано’, column_name3+ column_name4. У вище наведених прикладах column_name1, column_name2 означали назви стовпчиків числового типу, а column_name3, column_name4 – назви стовпчиків символьного типу.

В мові SQL використовуються такі оператори зрівнювання:

Якщо вирази A i B вирази одного типу, то вирази:

A = B, A < B, A > B, A <= B, A >= B, A <> B

називатимемо предикатами. Предикати приймають 2 значення: ІСТИНА і НЕІСТИНА. Наприклад, column_name1>1000, column_name3=‘Академія’.

Більш складні предикати будуються з допомогою булевих операцій AND, OR або NOT.

Якщо P_1, P₂ – предикати, то

P₁ AND P₂  , P₁ OR  P₂ , NOT P₁

також предикати.

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

Закони Де Моргана:

1-й закон.Предикат NOT(P₁ OR  P₂) еквівалентний предикатові NOT P₁ AND NOT P₂, тобто на однакових наборах значень P₁, P₂ приймають однакові значення. Ця еквівалентність записується так:

NOT(P₁ OR  P₂) « NOT P₁ AND NOT P₂

2-й закон:

NOT(P₁ AND  P₂) « NOT P₁ OR NOT P₂

Дистрибутивний закон:

Q AND (P₁ OR P₂) « (Q AND P₁ ) OR (Q AND P₂ )

Продемонструємо використання цих законів. Нехай в колонці з назвою S деякої таблиці зберігається інформація про нараховані суми. Предикат, що визначає рядки в яких нарахована сума НЕ лежить в межах від 100  до 1000 грн. матиме вид:

NOT(S>=100 AND S<=1000)

Згідно 2-му закону Де Моргана, він еквівалентний:

NOT (S>=100) OR NOT( S<=1000)

В свою чергу

NOT (S>=100)  « S<100

NOT( S<=1000) )  « S>1000

Отже

NOT(S>=100 AND S<=1000) )  « S<100 OR S>1000

В мові SQL передбачено також використання скалярних функцій над символьними даними, даними типу DATE, TIMESTAMP а також функцій, що перетворюють один тип даних в інший. Деякі розробники СУБД відійшли від стандарту SQL-92, тому ми подаємо таблицю, в якій вказані функції стандарту SQL-92 і відповідні їм функції СУБД Access.

Прості запити

Призначення оператора SELECT полягає в тому, щоб вибирати необхідні дані з однієї або декількох таблиць. Це виключно потужний оператор, який здатний виконувати дії, еквівалентні  операторам реляційної алгебри selection, projection i join. Загальний формат оператора SELECT має вид:

SELECT [DISTINCT | ALL] {* | column_expression [ AS new_name][,…]}

      FROM table_name [alias][,…]

      [WHERE condition]

      [GROUP BY  column_list] [HAVING condition]

      [ORDER BY column_list]

Параметр column_expression – це ім’я стовпчика або алгебричного виразу над декількох імен. Параметр table_name – це є ім’я існуючої в базі таблиці або представлення, до яких потрібно доступитись. Необов’язковий параметр alias – це скорочення для імені таблиці table_name. Обробка елементів оператора SELECT виконується в такій послідовності:

1. FROM  - Визначаються імена таблиць, які використовуються

2. WHERE - Виконується фільтрація рядків згідно заданої умови відбору

3. GROUP BY - Утворюються групи рядків з однаковими значеннями в зазначених колонках

4. HAVING – Фільтруються групи рядків об'єкту згідно вказаної умови

5. SELECT – Встановлюється, які стовпчики повинні бути у вихідних даних

6. ORDER BY – Визначається порядок результату виконання оператора.

Порядок фраз в операторі SELECT не можна змінювати. Тільки дві фрази –SELECT і FROM є обов’язковими, всі інші можуть бути пропущеними. Результат запиту до таблиць є таблиця. Символ *  в операторі SELECT означає, що будуть вибрані всі стовпчики таблиці.

Приклади. Нехай в таблиці table_a зберігається інформація про покупки товарів, а в table_b – про продаж цих товарів. Ці таблиці мають такий вид:

      table_a                                                 table_b

В стовпчики “kod” в обох таблицях заноситься код товару. В стовпчик “suma” таблиці table_a заноситься вартість закупленого товару, а в таблиці table_b – сума проданого товару. В стовпчик “data” таблиці table_a заноситься дата закупки товару, а в table_b – дата продажу товару.

Задача. Одержати перелік товарів з відповідними сумами і датами, які були закуплені. Відповідний оператор SELECT матиме вид:

      SELECT kod,suma,data

      FROM table_a

Оскільки не задана умова відбору фраза WHERE не потрібна. Результат запиту матиме вид:

Точно такий же результат буде після виконання такого SELECT’у:

      SELECT *

      FROM table_a

Якщо потрібно відобразити лише код товару і відповідну суму, причому в першій колонці результату має бути сума а у другій - код, оператор SELECT  матиме вид:

      SELECT suma, kod

      FROM table_a

Результат запиту матиме вид:

(6 рядків)

Використання ключового слова DISTINCT. Задача. Потрібно скласти перелік кодів товарів які закуповувались. Якщо оператор SELECT задати таким чином:

      SELECT kod

      FROM table_a

Результат запиту матиме вид:

(6 рядків)

Зверніть увагу на дубльовані рядки, оскільки, на відміну від операції реляційної алгебри projection, оператор SELECT не виключає дубльованих рядків. Для видалення з результуючої таблиці дубльованих рядків використовується ключове слово DISTINCT. Відредагований запит матиме вид:

      SELECT DISTINCT kod

      FROM table_a

Результат запиту матиме вид:

(3 рядки)

При використанні ключового слова ALL оператор SELECT повертає всі рядки.

За змовчуванням (не вказано ні DISTINCT ні ALL), вважається, що діє ALL.

Обчислювальні поля. Задача. Одержати перелік товарів з відповідними сумами і відповідними сумами податку ПДВ, які закладені у вартість закуплених товарів. Відповідний оператор SELECT матиме вид:

      SELECT kod,suma,(suma*0.2)/(1+0.2) AS pdv

      FROM table_a

 Результат запиту матиме вид:

(6 рядків)

Умова відбору (ключове слово WHERE)

В наведених вище прикладах в результаті виконання оператора SELECT вибирались всі рядки вказаної таблиці. Якщо потрібно задати критерій відбору рядків це задається після ключового слова WHERE. Існує 5 основних типів умов відбору (або, згідно термінології ISO, предикатів).

• Зрівняння. Зрівнюються результати обчислення одного виразу з результатами обчислення другого виразу.

• Діапазон. Перевіряється, чи попадає результат обрахунку виразу в заданий діапазон.

• Приналежність до множини. Перевіряється, чи належить результат обчислення виразу до заданої множини значень.

• Відповідність шаблону. Перевіряється, чи відповідає деяке строкове значення заданому шаблону.

• Значення NULL. Перевіряється, чи даний стовпчик має невизначене значення NULL.

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

Зрівняння

Необхідно скласти перелік товарів, ціна покупки яких перевищила 90.

      SELECT kod,suma,data

      FROM table_a

      WHERE suma>90

Результат запиту матиме вид:

(2 рядки)

Одержати перелік товарів з відповідними сумами і відповідними сумами податку ПДВ, в яких сума податку ПДВ перевищує 10. Відповідний оператор SELECT матиме вид:

      SELECT kod,suma,(suma*0.2)/(1+0.2) AS pdv

      FROM table_a

      WHERE suma*0.2/1.2 >10

Результат запиту матиме вид:

Необхідно скласти перелік покупок товару з кодом 1, ціна покупки яких не перевищувала 80.

      SELECT kod,suma,data

      FROM table_a

      WHERE kod=1 AND suma<=80

Результат запиту матиме вид:

Якщо потрібно одержати перелік товарів, які були закуплені після 10 січня 2002 року, потрібно задати такий оператор:

      SELECT *

      FROM table_a

      WHERE data> {d'2002-1-10'}

Результат запиту матиме вид:

Використання діапазонів (BETWEEN / NOT BETWEEN)

Необхідно скласти перелік товарів, ціна покупки яких лежить в межах від  50 до 100 грн.

      SELECT kod,suma,data

      FROM table_a

      WHERE suma BETWEEN 50 AND 100

Результат запиту матиме вид:

Умови відбору з перевіркою входження у множину (IN / NOT IN)

Задача. Необхідно скласти список покупок, які здійснювались 1.1.2002 і 2.2.2002 року. Запит матиме такий вид:

      SELECT *

      FROM table_a

      WHERE data IN( {d'2002-01-01'},{d'2002-02-02'})

Результат запиту матиме вид:

Умови відбору з допомогою шаблону (LIKE / NOT LIKE)

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

% Символ проценту представляє любу послідовність з нуля і більше символів.

_  Символ підкреслювання представляє любий одиночний символ.

Всі останні символи в шаблоні представляють самі себе. Наприклад:

• Adresa LIKE ‘П%’ – цей шаблон означає, що перший символ значення стовпчика Adresa повинен бути символом П, а всі останні символи можуть бути любими;

• Adresa LIKE ‘П___’ - цей шаблон означає, що значення стовпчика Adresa повинно мати довжину 4 і починатись з символу П;

• Adresa LIKE ‘%п’ – цей шаблон означає, значення стовпчика Adresa повинно закінчуватись символом п;

• Adresa LIKE ‘%ДПС%’ – цей шаблон означає, що значення стовпчика Adresa повинно містити підслово ДПС;

• Adresa NOT LIKE ‘П%’ – цей шаблон означає, що перший символ значення стовпчика Adresa НЕ повинен бути символом П, а всі останні символи можуть бути любими.

Приклад. Нехай таблиця ustanova має такі стовпчики: kod, Nazva, Adresa, в яких заноситься інформація про ідентифікаційний код підприємства, його назву і адресу відповідно. Тоді запит

      SELECT *

      FROM ustanova

      WHERE Adresa LIKE’%Ірпінь%’

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

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

      SELECT *

      FROM ustanova

      WHERE NOT Nazva LIKE’%a%’ AND NOT Nazva LIKE’%б%’

Використання значення NULL в умовах відбору (IS NULL / NOT NULL)

Для знаходження незаповнених значень певного стовпчика деякої таблиці використовується спеціальне ключове слово IS NULL. Наприклад, для формування списку підпрємств з незаповненими адресами потрібно написати такий запит:

      SELECT *

      FROM ustanova

      WHERE Adresa IS NULL

і навпаки, запит

      SELECT *

      FROM ustanova

      WHERE Adresa IS NOT NULL

сформує список підприємств із заповненими адресами.

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

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

Імена таблиць і стовпчиків

Повне ім’я стовпчика складається з імені таблиці, безпосередньо за яким стоїть крапка, а за нею - ім’я стовпчика. Наприклад, table_a.kod,table_b.suma, table_a.suma. В наведених раніше прикладах імена таблиць можна було опускати, оскільки запити використовували тільки одну таблицю. Навіть формуючи запит до декількох таблиць їх імена можна опустити, якщо імена цих таблиць різні. Одначе так буває не завжди.

З’єднання таблиць (join)

Припустимо, необхідно встановити зв’язок між закупленим товаром і проданим. Для цього потрібно прирівняти код купленого товару до коду проданого товару, тобто table_a.kod=table_b.kod. Отже запит матиме вид:

SELECT a.kod AS kod_a,a.suma AS suma_a, b.kod AS kod_b,b.suma AS suma_b

      FROM table_a a,table_b b

      WHERE a.kod=b.kod

Результат запиту матиме вид:

Тепер пояснимо роботу оператора SELECT, в якому використовується декілька таблиць. На основі таблиць, які зустрічаються після ключового слова FROM (в нашому випадку – table_a i table_b) , будується декартовиий добуток множин рядків таблиць. В одержаному добутку перевіряється істинність предикату заданого у WHERE, якщо предикат приймає значення істина, рядок поміщається в рельтуючу множину, в противному разі – не включається. Тепер стає зрозумілим, чому в результаті є 6 рядків в яких kod_a i kod_b приймають значення 1 (в таблиці table_a значення 1 в колонці kod зустрічається 3 рази, а у таблиці table_b відповідно 2 рази, отже в декартовому добутку буде 6 рядків) і 4 рядки, в яких kod_a i kod_b приймають значення 2 (в таблиці table_a значення 2 в колонці kod зустрічається 2 рази і в таблиці table_b 2 рази, отже в декартовому добутку буде 4 рядків). Оскільки, в table_b в колонці kod 3 не зустрічається, тому в результаті не має рядка з kod_a=kod_b=3.

В наведеному вище прикладі продемонстровано з’єднання (join) двох таблиць. З’єднання, яке використовує предикати, які побудовані виключно на рівностях, називається еквіз’єднанням. Еквіз’єднання є найбільш поширеним типом з’єднанням, але існують і інші типи з’єднання. Фактично в з’єднанні можна використовувати любу операцію зрівняння. Наприклад,

 SELECT a.kod AS kod_a,a.suma AS suma_a, b.kod AS kod_b,b.suma AS suma_b

      FROM table_a a,table_b b

      WHERE a.kod>b.kod

Результат запиту матиме вид:

Наведемо приклад більш практичної задачі. Для обчислення прибуткового податку з громадян в Excel’і користуються такою формулою

Якщо(Дохід<=17; 0;

    Якщо(Дохід<=85; (Дохід-17)*0.1;

       Якщо(Дохід<=170; (Дохід-85)*0.15+6.8;

          Якщо(Дохід<=1020; (Дохід-170)*0.2+19.55;

            Якщо(Дохід<=1700; (Дохід-1020)*0.3+189.55;

                  (Дохід-1700)*0.4+393.55)))));

Нижче наведена таблиця tpp обрахування прибуткового податку на зарплату:

Якщо D – дохід, то податок P на прибуток обраховується за формулою:

(D-d_l)*p+c.

Рядок, з якого вибираються відповідні d_l, p i c, визначається нерівностями:

d_l<D<=d_r

Очевидно, ці нерівності однозначно визначають рядок таблиці tpp.

Нехай таблиця Vidomist має вид:

де через kod позначено ідентифікаційний код особи, priz – прізвище особи, D – суму сукупного доходу.

Для того, щоб сформувати відомість нарахованих сум податку на прибуток, необхідно застосувати такий оператор SELECT:

      SELECT a.kod,a.priz, (a.D-b.d_l)*b.p+b.c AS podatok

      FROM Vidomist a, tpp b

      WHERE (a.D>b.d_l) AND (a.D<=b.d_r)

Стандарт SQL-92 додатково надає і таку можливість створення цього з’єднання:

      FROM Vidomist a INNER JOIN tpp b ON((a.D>b.d_l) AND (a.D<=b.d_r))

Таким чином, запит виглядатиме так:

      SELECT a.kod,a.priz, (a.D-b.d_l)*b.p+b.c AS podatok

      FROM Vidomist a INNER JOIN tpp b ON((a.D>b.d_l) AND (a.D<=b.d_r))

Запит, який з’єднує таблиці table_a i table_b матиме вид:

SELECT a.kod AS kod_a,a.suma AS suma_a, b.kod AS kod_b,b.suma AS suma_b

FROM table_a a INNER JOIN table_b b ON a.kod=b.kod

Ліве з’єднання (LEFT JOIN)

Ліве з’єднання таблиць, наприклад table_a і table_b, застосовують тоді, коли потрібно, щоб результат містив всі рядки однієї таблиці (наприклад table_a) і лише ті рядки з іншої таблиці (наприклад table_b), для яких в першій таблиці є відповідники. Наприклад, ліве з’єднання таблиць table_a і table_b матиме вид:

SELECT a.kod AS kod_a,a.suma AS suma_a, b.kod AS kod_b,b.suma AS suma_b

FROM table_a a LEFT JOIN table_b b ON a.kod=b.kod

Результат запиту матиме вид:

Для стовпчика kod таблиці table_a зі значенням 3 у таблиці table_b немає відповідника, тому в результуючій таблиці у стовпчиках, які відповідають стовпчикам таблиці table_b стоять значення NULL.

Наведемо приклад з’єднання 3-х таблиць. Змінимо структуру таблиці Vidomist, а саме:

де стовпчики year i mounth означають відповідно рік і місяць виплати зарплати. Додамо нову таблицю Osoba, в якій зберігається інформація про працівників:

Задача. Потрібно сформувати відомість зарплати за 2-й місяць 2002 року з колонками: “Прізвище”, “Нараховано”, “Сума податку”, “До виплати”.

Відповідний SQL-оператор матиме вид:

SELECT a.priz AS Прізвище, b.D AS Нараховано, (b.D-c.d_l)*c.p+c.c AS [Сума податку], b.D-((b.D-c.d_l)*c.p+c.c) AS [До виплати]

FROM (Osoba a INNER JOIN Vidomist b ON a.kod=b.kod) INNER JOIN tpp c ON((b.D>c.d_l) AND (b.D<=c.d_r))

WHERE b.year=2002 AND b.mounth=2

Еквівалентна форма даного SQL-оператора матиме вид:

SELECT a.priz AS Прізвище, b.D AS Нараховано, (b.D-c.d_l)*c.p+c.c AS [Сума податку], b.D-((b.D-c.d_l)*c.p+c.c) AS [До виплати]

FROM Osoba a, Vidomist b, tpp c

WHERE a.kod=b.kod AND (b.D>c.d_l) AND (b.D<=c.d_r) AND

b.year=2002 AND b.mounth=2

Результуюча таблиця матиме вид:

Сортування результату (фраза ORDER BY)

Взагалі кажучи, рядки результуючої таблиці SQL-запиту не впорядковані якимось чином. Але їх можна впорядкувати певним чином, для чого в операторі SELECT використовується фраза ORDER BY. Фраза ORDER BY містить список розділених комами ідентифікаторів стовпчиків таблиць по яким потрібно впорядкувати результуючу таблицю. Фраза ORDER BY дозволяє впорядкувати вибрані записи в порядку зростання (ASC) або спадання (DESC) значення стовпчиків, незалежно від того є вони в результуючій таблиці чи ні. Але в деяких діалектах вимагається, щоб фраза ORDER BY містила лише назви стовпчиків, які зустрічаються в списку виборки оператора SELECT. Отже, якщо потрібно впорядкувати результат запиту з попереднього прикладу по коду, необхідно в кінці SQL-оператора додати ORDER BY a.kod. Оператор матиме вид:

SELECT a.kod AS kod_a,a.suma AS suma_a, b.kod AS kod_b,b.suma AS suma_b

      FROM table_a a LEFT JOIN table_b b ON a.kod=b.kod

      ORDER BY a.kod

Результат запиту матиме вид:

а результат SQL-оператора

SELECT a.kod AS kod_a,a.suma AS suma_a, b.kod AS kod_b,b.suma AS suma_b

      FROM table_a a LEFT JOIN table_b b ON a.kod=b.kod

      ORDER BY a.kod, a.suma

матиме вид:

Групування результатів (фраза GROUP BY). Агрегатні функції

Стандарт ISO містить визначення 5 агрегатних функцій:

Всі ці функції оперують зі значеннями одного стовпчика і повертають єдине значення. Функції COUNT, MIN, MAX можуть застосовуватись як до числових стовпчиків, так і до нечислових, а функції SUM i AVG застосовують лише до числових стовпчиків. За виключенням COUNT(*), при обчисленні значення функцій значення NULL не враховуються. Варіант COUNT(*) є особливим випадком застосування функції COUNT. COUNT(*) повертає загальну кількість рядків результуючої таблиці, незалежно від того містяться там NULL-значення, дубльовані рядки або любі інші значення. Якщо до застосування агрегатних функцій необхідно виключити дубльовані значення, то потрібно перед іменем стовпчика, який є аргументом агрегатної функції помістити ключове слово DISTINCT, наприклад COUNT(DISTINCT kod). Слід зауважити, що не всі діалекти підтримують цю конструкцію. Скажімо Access не підтримує такого використання COUNT.

Стандарт ISO допускає використання ключового слова ALL (тобто всіх), що означає, що виключати дублі непотрібно. За змовчуванням, діє ALL.

Слід зауважити, що ключове слово DISTINCT в кожному запиті можна вказати не більше одного разу. Агрегатні функції можуть використовуватись тільки в списку фрази SELECT, або в складі HAVING. У всіх інших випадках використання агрегатних недопустимо. Якщо список у фразі SELECT є  агрегатні функції і в тексті запиту відсутня фраза GROUP BY, тоді список у фразі SELECT не повинен містити назви стовпчиків. Наприклад, такий запит

      SELECT kod, SUM(suma)

      FROM table_a

є некоректним, оскільки назва стовпчика kod не повинна зустрічатись у списку фрази SELECT.

      Приклади використання агрегатних функцій. Запит

      SELECT COUNT(kod)

      FROM table_a

Поверне число 6 – кількість рядків table_a. Запит

      SELECT MAX(suma)

      FROM table_a

поверне максимальне значення стовпчика suma – 150. Запит

      SELECT MIN(suma) AS suma_min,AVG(suma) AS suma_avg

      FROM table_a

поверне таку таблицю результату:

де suma_min означає найменше значення стовпчика suma, а suma_avg середнє значення стовпчика suma таблиці table_a. Запит

      SELECT MAX(suma),MIN(suma),AVG(suma)

      FROM table_a

      WHERE data<{d'2002-01-15'}

поверне, відповідно, максимальне, мінімальне і середнє значення стовпчика suma для покупок, які були здійснені до 15-01-2002 року. Це числа 150, 55 і 101,666666666667 відповідно.

Використання фрази GROUP BY

Наведені вище приклади підсумкових даних подібні підсумковим рядкам, які поміщаються в кінці звітів. Правда, досить часто у звітах вимагається формувати проміжні підсумки. Для цієї цілі в операторі SELECT вказується фраза GROUP BY. Стандарт ISO вимагає, щоб фрази SELECT і GROUP BY були тісно пов’язані між собою. При використанні в операторі SELECT фрази GROUP BY фраза SELECT повинна містити тільки такі типи елементів:

• імена стовпчиків;

• агрегатні функції;

• константи;

• вирази, що містять перечисленні вище елементи.

Всі імена стовпчиків, які перераховані в списку SELECT’у повинні бути присутніми і у фразі GROUP BY.

На конкретних прикладах пояснимо як потрібно користуватись групуванням. Нагадаємо, що в таблиці table_a міститься інформація про закупки товарів. Якщо уважно подивитись, в цій таблиці є по декілька рядків, у яких значення стовпчика kod (код товару) повторюється, наприклад 1 зустрічається 3 рази. Група рядків, у яких kod=1, матиме вид:

Якщо потрібно дізнатись на яку суму закуплено кожного товару, необхідно сформувати такий запит:

      SELECT kod,SUM(suma) AS suma_tov

      FROM table_a

      GROUP BY kod

Результуюча таблиця цього запиту матиме вид:

Отже, оператор SELECT поступає таким чином, спочатку формує групи по значеннях стовпчиків, які перелічені у фразі GROUP BY (в нашому випадку одне поле kod), потім для кожної групи виконує агрегатну функцію і результат заносить у результуючу таблицю. Якби потрібно було крім сум вказати і кількість покупок кожного товару, запит був би таким:

      SELECT kod,SUM(suma) AS suma_tov, COUNT(kod) AS kilkist_pokupok

      FROM table_a

      GROUP BY kod

Результуюча таблиця цього запиту матиме вид:

Якщо ж потрібно дізнатись на яку суму закуплено кожного товару до 20.1.2002року, необхідно сформувати такий запит:

SELECT kod,SUM(suma) AS suma_tov

      FROM table_a

      WHERE data<{d'2002-01-20'}

      GROUP BY kod

Результуюча таблиця цього запиту матиме вид:

Використання фрази HAVING

Фраза HAVING призначена для сумісного використання з фразою GROUP BY для обмеження відбору груп, які відбираються для результуючої таблиці запиту. Хоч фрази HAVING і WHERE мають схожий синтаксис, їх призначення різне. WHERE призначене для фільтрації рядків, які використовуються в тому числі для групування, фраза HAVING призначена для фільтрації груп, які заносяться в результуючу таблицю. На практиці предикат фрази HAVING повинен містить одну з агрегатних функцій. Наприклад, якщо потрібно дізнатись на яку суму закуплено кожного товару, причому сумарна вартість товару не перевищувала б 200 грн., необхідно сформувати такий запит:

      SELECT kod,SUM(suma) AS suma_tov

      FROM table_a

      GROUP BY kod

      HAVING SUM(suma)<=200

Результуюча таблиця цього запиту матиме вид:

Якщо потрібно сформувати список товарів (з включенням в результуючу таблицю коду товару, сумарну вартість цього товару і кількість покупок цього товару), середня вартість покупки товару перевищувала б 50, запит мав би вид:

      SELECT kod,SUM(suma) AS suma_tov, COUNT(kod) AS kilkist_pokupok

      FROM table_a

      GROUP BY kod

      HAVING AVG(suma)>50

Результуюча таблиця цього запиту матиме вид:

Якщо ж до попереднього запиту додати умову: покупки здійснювались до 20.01.2002 року, запит матиме вид:

      SELECT kod,SUM(suma) AS suma_tov, COUNT(kod) AS kilkist_pokupok

      FROM table_a

      WHERE data<{d'2002-01-20'}

      GROUP BY kod

      HAVING AVG(suma)>50

Результуюча таблиця цього запиту матиме вид:

Підзапити

Звернімося до таблиць Vidomist(kod,year,month,D), tpp(d_l,d_r, p,c) i Osoba(kod, priz). Нагадаємо, що в таблиці Vidomist (kod,year,month,D – її поля) зберігається інформація про нараховану зарплату співробітників за певний рік і місяць. Таблиця tpp містить дані для обрахування податку на прибуток, а таблиця Osoba інформацію про персональний склад. Нехай нам потрібно сформувати список осіб з такими колонками: “ Прізвище ”,

” Нараховано ”, які одержують нараховану зарплату вищу за середню нараховану зарплату за 2-й місяць 2002 року. Середню нараховану зарплату за 2-й місяць 2002 року визначить такий запит:

SELECT AVG(D)

FROM Vidomist

WHERE year=2002 AND mounth=2

Середня нарахована зарплата становить 500 грн.

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

SELECT a.priz AS Прізвище, b.D AS Нараховано

FROM (Osoba AS a INNER JOIN Vidomist AS b ON a.kod=b.kod)

WHERE b.year=2002 AND b.mounth=2 AND b.D> (SELECT AVG(D) FROM Vidomist WHERE year=2002 AND mounth=2)

Результуюча таблиця матиме вид:

Пояснимо цей SELECT-оператор. Запит

SELECT AVG(D)

FROM Vidomist

WHERE year=2002 AND mounth=2

який розташований у фразі WHERE першого SELECT’у (він взятий у дужки) називають підзапитом. На відміну від основного запиту, якого ще називають зовнішнім запитом, підзапит ще називають внутрішнім запитом. Цей під запит знаходить середню нараховану зарплату. Потім запит відбирає ті рядки, в яких нарахована зарплата  перевищує число (в цьому конкретному випадку воно дорівнює 500), знайдене під запитом.

Якщо ж потрібно скласти список співробітників, які мають нараховану зарплату вище середньої, вказавши при цьому на скільки вона перевищує середню, то запит матиме вид:

SELECT a.priz AS Прізвище, b.D AS Нараховано, b.D-(SELECT AVG(D) FROM Vidomist WHERE year=2002 AND mounth=2) AS Перевищення

FROM (Osoba AS a INNER JOIN Vidomist AS b ON a.kod=b.kod)

WHERE b.year=2002 AND b.mounth=2 AND b.D> (SELECT AVG(D) FROM Vidomist WHERE year=2002 AND mounth=2)

Результуюча таблиця матиме вид:

Якщо ж середню величину зарплати обраховувати виходячи з суми, яку повинні видавати на руки (нарахована мінус податок на прибуток) і запит має повертати такі колонки: “ Прізвище ”,” Нараховано ”,” Сума податку ” і  “ До виплати ”, то запит матиме вид:

SELECT a.priz AS Прізвище, b.D AS Нараховано, (b.D-c.d_l)*c.p+c.c AS [Сума податку], b.D-((b.D-c.d_l)*c.p+c.c) AS [До виплати]

FROM (Osoba AS a INNER JOIN Vidomist AS b ON a.kod=b.kod) INNER JOIN tpp AS c ON (b.D>c.d_l) AND (b.D<=c.d_r)

WHERE b.year=2002 AND b.mounth=2 AND (b.D-((b.D-c.d_l)*c.p+c.c))> (SELECT AVG(b.D-((b.D-c.d_l)*c.p+c.c)) FROM Osoba a, Vidomist b, tpp c WHERE a.kod=b.kod AND (b.D>c.d_l) AND (b.D<=c.d_r) AND b.year=2002 AND b.mounth=2)

Результуюча таблиця матиме вид:

Підзапит цього запиту має вид:

SELECT AVG(b.D-((b.D-c.d_l)*c.p+c.c))

FROM Osoba a, Vidomist b, tpp c

WHERE a.kod=b.kod AND (b.D>c.d_l) AND (b.D<=c.d_r) AND b.year=2002 AND b.mounth=2

Підзапит може вказуватись безпосередньо після операторів зрівнювання (=, <, >, <=, >=, <>) у фразах WHERE i HAVING. Текст під запиту потрібно брати в дужки. До підзапитів застосовують такі правила і обмеження.

1. В підзапитах не повинна використовуватись фраза ORDER BY, хоч вона може використовуватись у зовнішньому запиті.

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

3. Якщо підзапит є одним з двох операндів оператора зрівнювання, то підзапит повинен вказуватись в правій частині оператора зрівнювання.

4. Якщо підзапит є одним з двох операндів оператора зрівнювання, то під запит має повертати єдине значення.

Існує три типи підзапитів.

• Скалярний підзапит – це підзапит, який повертає єдине значення. У вищенаведених прикладах використовувався скалярний підзапит.

• Строковий підзапит – це підзапит, який повертає значення декількох стовпчиків у вигляді одного рядка.

• Табличний підзапит - це підзапит, який повертає значення одного або більше стовпчиків таблиці, які розташовані в більш ніж одному рядку. Табличний під запит може використовуватись кругом де допускається вказувати таблицю – наприклад, як операнд предикату IN.

Ключові слова ANY i ALL

Ключові слова ANY i ALL використовуються лише з підзапитами, які повертають один стовпчик чисел. Якщо перед підзапитом стоятиме ключове слово ALL, умова зрівняння вважається істинною, якщо вона виконується для всіх значень в результуючому стовпчику підзапиту. Наприклад, запит

SELECT *

FROM Vidomist

WHERE D> ALL (SELECT D FROM Vidomist WHERE kod IN (1,2))

Результат наведено нижче

В даному конкретному випадку внутрішній запит

SELECT D FROM Vidomist WHERE kod IN (1,2)

відбере такі значення: {367.78,1309.55,400,500}.

Очевидно, умова D> ALL (SELECT D FROM Vidomist WHERE kod IN (1,2)) еквівалентна умові D> (SELECT MAX(D) FROM Vidomist WHERE kod IN (1,2)).

Власне кажучи, цей запит еквівалентний запиту (дасть той же результат)

      SELECT *

      FROM Vidomist

      WHERE D> (SELECT MAX(D) FROM Vidomist WHERE kod IN (1,2))

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

D> ANY(SELECT D FROM Vidomist WHERE kod IN (1,2))

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

Результатом запиту

      SELECT *

      FROM Vidomist

      WHERE D> ANY (SELECT D FROM Vidomist WHERE kod IN (1,2))

буде таблиця:

Очевидно, цей запит буде еквівалентний  запиту

      SELECT *

      FROM Vidomist

      WHERE D> (SELECT MIN(D) FROM Vidomist WHERE kod IN (1,2))

Ключові слова EXISTS i NOT EXISTS

Ключові слова EXISTS i NOT EXIST використовуються лише з підзапитами. Результатом їх виконання є логічне значення “ІСТИНА” або “ХИБНЕ”. Для ключового слова EXISTS результат дорівнює “ІСТИНА” в тому і лише в тому випадку, коли результуюча таблиця підзапиту містить хоча б один рядок. Якщо результуюча таблиця підзапиту пуста, результат обробки ключового слова EXISTS буде рівним “ХИБНЕ”. Для ключового слова NOT EXISTS використовуються правила обробки, протилежні по відношенню до ключового слова EXISTS. Оскільки по ключовим словам перевіряється лише наявність рядків результуючої таблиці, ця таблиця може містити довільне число як рядків так і стовпчиків. Приклад використання ключового слова NOT EXISTS наведено вище

      SELECT *

      FROM Vidomist a

      WHERE NOT EXISTS

(SELECT * FROM Osoba WHERE a.kod=b.kod)

Наведений запит поверне ті рядки з таблиці Vidomist, які не відповідають жодному рядку з таблиці Osoba, тобто є такими, що порушують цілісність посилань. Отже, якщо цей запит поверне пусту таблицю, цілісність посилань не порушена.

Комбінування результуючих таблиць (операції UNION, INTERSECT і EXCEPT)

В мові SQL можна використовувати звичні операції над множинами – об’єднання (UNION), перетин (INTERSECT) і різницю (difference), які дозволяють комбінувати результати виконання двох або більше запитів в єдину результуючу таблицю. На таблиці, до яких застосовують теоретико-множинні операції, накладаються певні обмеження. А саме:

• таблиці повинні мати однакову кількість стовпчиків;

• типи i-го стовпчика обох таблиць мають бути однаковими;

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

Наведемо приклад використання операції UNION. Потрібно сформувати відомість нарахованої зарплати за лютий місяць 2002 року з обов'язковим включенням до списку всіх працівників незалежно від того нараховували їм зарплату чи ні. Відповідний запит матиме вид:

      SELECT a.priz AS Прізвище, b.D AS [Нарахована сума]

      FROM Osoba a, Vidomist b

      WHERE a.kod=b.kod AND b.year=2002 AND b.mounth=2

      UNION

      SELECT priz,NULL

      FROM Osoba

      WHERE NOT kod IN(SELECT kod

      FROM Osoba a, Vidomist b

      WHERE a.kod=b.kod AND b.year=2002 AND b.mounth=2)

Перший оператор SELECT (до UNION) сформує список працівників, яким у лютому 2002 року нарахували зарплату, а другий оператор SELECT (після UNION) сформує список працівників, яким не нарахували зарплату у лютому 2002 року.

Результуюча таблиця матиме вид:

Поновлення бази даних

Мова SQL є повнофункціональною мовою маніпулювання даними, яка може використовуватись не лише для виборок даних з бази, але і для внесення змін у базу даних. Оператори модифікації інформації в базі даних не настільки складні як оператор SELECT. Є три оператори модифікації даних в базі. Це

• INSERT – використовується для вставки нових даних у таблицю

• UPDATE - використовується для модифікації вже існуючої в таблиці інформації

• DELETE - використовується для видалення з таблиці рядку(ів) даних.

Вставка нових даних

Існує дві форми оператора INSERT. Перша використовується для вставки єдиного рядка в таблицю. Ця форма має такий формат:

      INSERT INTO table_name [(column_list)]

      VALUES (data_value_list)

Тут параметр table_name (Ім’я таблиці) означає або ім’я таблиці або ім’я представлення. Параметр column_list (Список стовпчиків) є списком імен одного або більше стовпчиків розділених комами. Параметр column_list не є обов’язковим. Якщо він відсутній, вважається, що використовується список всіх стовпчиків таблиці, причому імена стовпчиків розташовані в тому ж порядку в якому вони були задані в операторі CREATE TABLE (створення таблиці).

Якщо в операторі INSERT вказується конкретний список імен стовпчиків, то любі пропущені в ньому стовпчики повинні допускати значення NULL.

Параметр data_value_list (список значень) повинен наступним чином відповідати параметру column_list:

• кількість елементів в обох списках повинна бути однакова;

• повинна бути пряма відповідність між позиціями одного і того ж елементу в обох списках, тобто перший елемент списку data_value_list  відповідає першому елементу списку column_list, другий елемент списку data_value_list  відповідає другому елементу списку column_list і т.д.

• типи даних елементів списку data_value_list повинні бути сумісними з типами даних відповідних стовпчиків з column_list.

Приклад використання конструкції INSERT…VALUES. Оператор:

INSERT INTO Osoba

VALUES(4,’Мельник’)

всталяє новий рядок в таблицю Osoba, в якому стовпчик kod приймає значення 4, а стовпчик priz – Мельник.

Якщо потрібно вставити новий рядок в таблицю з використанням лише деяких стовпчиків, потрібно в круглих дужках перерахувати через кому імена відповідних стовпчиків (параметр column_list). Наприклад, в table_a потрібно додати рядок, в якому kod=4, suma=245, а data невизначена. Оператор INSERT матиме вид:

      INSERT INTO table_a (kod,suma)

      VALUES(4,245)

Друга форма оператора INSERT дозволяє скопіювати множину рядків однієї таблиці в іншу. Цей оператор має такий формат:

      INSERT INTO table_name [(column_list)]

      SELECT …

Параметри table_name і column_list мають той же формат і зміст що й при вставці в таблицю одного рядка. Рядки, які вставляються в указану таблицю, є рядками результуючої таблиці, сформованої оператором SELECT. Всі наведені вище обмеження, що стосувались першої форми оператора INSERT, дійсні і в цьому випадку.

Наведемо приклад. Нехай нам потрібно формувати баланс по закупкам товарів за певний період і зберігати в новій таблиці balans. Таблиця balans має два стовпчики – kod - код товару і suma - загальна сума вартості цього товару.

Оператор INSERT, який поповнює таблицю balans сумарними даними з table_a за період з 1.1.2002 по 31.1.2002 року, матиме вид:

      INSERT INTO balans(kod,suma)

      SELECT kod, SUM(suma)

      FROM table_a

      WHERE data>={d'2002-01-01'} AND data<={d'2002-01-31'}

      GROUP BY kod

      ORDER BY kod

Результатом виконання цього оператора є таблиця balans:

Нижче наведено приклад застосування другої форми оператора INSERT, який поповнює таблицю Vidomist даними з тієї ж таблиці Vidomist. Припустімо, що в 3-му місяці зарплату кожного співробітника збільшили рівно в 1.2 рази по відношенню до зарплати в 2-му місяці. Відповідний оператор INSERT матиме вид:

      INSERT INTO Vidomist (kod,year,mounth,D)

      SELECT kod, year,3,D*1.2

      FROM Vidomist

      WHERE mounth=2

Після виконання цього оператора таблиця Vidomist матиме вид:

Модифікація даних (оператор UPDATE)

Оператор UPDATE змінює значення певних стовпчиків в заданій таблиці. Цей оператор має формат:

      UPDATE table_name

      SET column_name1=data_value1 [,column_name2=data_value2 ...]

      [WHERE  предикат]

Параметр table_name визначає ім’я таблиці бази даних, параметри column_name1, column_name2, ... визначають імена стовпчиків, значення яких потрібно замінити новими значеннями - data_value1, data_value2, ... відповідно.

Фраза WHERE  предикат необов’язкова. Якщо вона опущена, значення стовпчиків будуть змінені в усіх рядках таблиці. Якщо ж фраза WHERE присутня, то зміни відбудуться лише в тих рядках, які задовольняють умові відбору. Наприклад, оператор

      UPDATE Vidomist

      SET D=D*1.05

      WHERE mounth=2

збільшує зарплату всім співробітникам за 2-й місяць.

Приклад використання підзапиту в операторі UPDATE. Небхідно зарплату Сидорова за 3-й місяць збільшити на 100 гривень. Оператор UPDATE матиме вид:

      UPDATE Vidomist

      SET D = D+100

WHERE mounth=2 AND kod=(SELECT kod FROM Osoba WHERE PRIZ='Сидоров')

Використання INNER JOIN, LEFT JOIN в операторі UPDATE

Вище згадувалась таблиця balans, в якій зберігалась сумарні по видам товарів витрати на закупівлю товару за певний період. Якщо нам потрібно дізнатись власне баланс по кожній групі товарів(вартість закупки мінус вартість продажу), нам потрібно застосувати такий оператор UPDATE:

      UPDATE balans a INNER JOIN table_b b ON a.kod=b.kod

      SET a.suma=a.suma-b.suma

      WHERE b.data >={d'2002-01-01'} AND b.data<={d'2002-01-31'}

Детальніше пояснимо роботу вищенаведеного оператора. Конструкція

balans a INNER JOIN table_b b ON a.kod=b.kod

задає умову з’єднання таблиць balans i table_b. Оператор UPDATE для кожного рядка таблиці balans послідовно рядок за рядком проглядає таблицю table_b і для рядків для яких виконується умова

a.kod=b.kod

виконує операцію, яка вказана у фразі SET , тобто заміщує старе значення a.suma новим, а саме (a.suma- b.suma). В нашому випадку в таблиці table_b для першого рядка {1,195} таблиці balans є 2 рядки, в яких поле kod=1, а саме {1,50} і {1,47}. Отже, спочатку UPDATE замінить 195 на 195-47=98, а потім 145 замінить на 145-47=98. В результаті, перший рядок таблиці balans матиме вид {1,98}. Аналогічним чином оператор UPDATE поступає для другого рядка таблиці balans і т.д. Після виконання оператора UPDATE, таблиця balans матиме вид:

Зауваження. Не у всіх діалектах мови SQL підтримується використання конструкції INNER JOIN в операторі UPDATE.

Видалення даних з бази(оператор DELETE)

Оператор DELETE використовують для видалення даних з певної таблиці. Цей оператор має такий формат:

      DELETE FROM table_name

      [WHERE предикат]

Параметр table_name визначає ім’я таблиці. Фраза WHERE необов’язкова. Якщо вона відсутня, будуть видалені всі рядки з таблиці. Якщо фраза WHERE присутня, будуть видалені тільки ті рядки з таблиці, для яких предикат істинний.

Наведемо приклад застосування оператора DELETE. Необхідно видалити з таблиці Vidomist рядки з найбільшою і найменшою сумою нарахованої зарплати. Оператор DELETE, який вирішує поставлену задачу матиме вид:

DELETE FROM Vidomist

WHERE (D=(Select MAX(D) FROM Vidomist)) OR (D=(Select MIN(D)

FROM Vidomist))

Результуюча таблиця матиме вид:

Визначення даних

Мова DDL (Data Definition Language), як частина мови SQL, дозволяє створювати і видаляти різні об’єкти бази даних – наприклад, таблиці, представлення, індекси тощо. Основними операторами мови SQL, призначеними для визначення даних, є:

Створення баз даних

В різних СУБД процедура створення баз даних різна. В клієнт-серверних системах право створення баз даних як правило закріплене за адміністратором бази даних (АБД). В персональних системах база даних створюється в процесі інсталяції і настройки параметрів самої СУБД. Стандарт ISO не визначає процедури створення екземпляру бази даних, тому в кожному з діалектів мови SQL використовується свій власний підхід. Наприклад, в СУБД ORACLE створення екземпляру бази є частиною процесу інсталяції системи.

У відповідності із  стандартом ISO, таблиці та інші об’єкти бази даних існують в деякому середовищі(environment). Крім того, кожне середовище містить в собі один або більше каталогів (catalog), а кожний каталог – набір схем (shema). Схема – це поіменована сукупність взаємопов’язаних об’єктів бази даних ( всі об’єкти бази даних повинні бути описані в тій чи іншій схемі). Об’єктами схеми можуть бути таблиці, представлення, домени і набори символів. Стандарт залишає право вибору конкретного механізму створення і знищення каталогів за розробником СУБД, але регламентує механізм створення і знищення схем. Оператор визначення схеми має такий формат:

      CREATE SHEMA [name | AUTHORIZATION creator-identifier]

Таким чином, якщо творцем схеми під іменем sql_test буде користувач Ivanov, то даний оператор виглядатиме таким чином:

      CREATE SHEMA sql_test AUTHORIZATION Ivanov

Схема видаляється з допомогою оператора DROP SHEMA, який має такий формат:

      DROP SHEMA name [RESTRICT | CASCADE]

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

На сьогоднішній день оператори CREATE SHEMA і DROP SHEMA реалізовані в небагатьох СУБД.

Створення таблиць (оператор CREATE TABLE)

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

      CREATE TABLE table_name

      (column_name data_type [NULL | NOT NULL][,…])

В результаті виконання цього оператора буде створена таблиця з іменем table_name, яка матиме один або більше стовпчиків з іменами що задаються параметрами column_name і матимуть тип, що задається параметрами data_type.

Ключове слово NULL визначає, що дане поле може містити значення NULL. Ключове слово NOT NULL визначає, що дане поле не може містити значення NULL. За змовчуванням, стандарт ISO визначає, що діє ключове слово NULL.

Стовпчики первинних ключів завжди визначаються з ключовим словом NOT NULL.

Приклад використання оператора CREATE TABLE. Оператор

      CREATE TABLE Osoba (

                   kod INTEGER NOT NULL,

                   priz VARCHAR(50) NOT NULL)

створить таблицю Osoba, в якій є 2 стовпчики – kod i priz. Поле kod має цілочисельний тип, а поле priz має тип VARCHAR, тобто символьний і яке містить не більше 50 символів. Оператор, який створить таблицю table_a, якою ми користувались на попередніх лекціях, матиме вид

      CREATE TABLE table_a(

         kod INTEGER NOT NULL,

         suma DECIMAL(8,2) NOT NULL,

         data DATE )

В стандарті ISO наведено декілька варіантів визначення цього оператора. Базовий формат цього оператора має вид:

      CREATE TABLE table_name

      {(column_name data_type [NOT NULL] [UNIQUE]

      [DEFAULT default_option] [CHECK (search_condition)] [,…]}

      [PRIMARY KEY (list_of_columns),]

      {[UNIQUE (list_of_columns)] [,…]}

      {FOREIGN KEY (list_of_foreign_key_columns)

      REFERENCES parent_table_name [list_of_candidate_key_columns)],

       [MATCH {partial | FULL}]

       [ON UPDATE referential_action]

       [ON DELETE referential_action] [,…]}

      {[CHECK (searh_condition)] [,…])}

Дана версія оператора CREATE TABLE містить не тільки визначення даних (параметри table_name, column_name, data_type і специфікатор NOT NULL, які ми розглядали у спрощеному варіанті) але й засоби визначення цілісності бази даних, ключів і таке інше.

Необов’язкова фраза DEFAULT задає значення - default_option, яке буде вставлятись оператором INSERT для невизначеного значення даного стовпчика (default_option може бути літералом).

Необов’язкова фраза UNIQUE задає унікальність значення цього поля, тобто не повинно існувати двох і більше рядків в таблиці з одноковим значення цього поля. Для стовпчиків, які вказані у фразі UNIQUE повинен застосовуватись режим NOT NULL для їх можливих значень. Система заблокує виконання любого оператора INSERT аба UPDATE який створює рядок з дубльованими значеннями стовпчиків, які вказані у фразі UNIQUE.

Необов’язкова фраза CHECK (search_condition). Кожний стовпчик має власний домен, тобто набір допустимих значень. Наприклад, для визначення наукового ступеня співробітника достатньо всього 3 значення – не має, кадидат і доктор. Тому домен для поля Naukov_stupin можна визначити як набір з 3-х значень- ‘не має’, ‘кадидат’,  ‘доктор’. Фраза

CHECK (Naukov_stupin IN(‘не має’, ‘кадидат’,  ‘доктор’))

задаватиме допустимі значення для поля Naukov_stupin. Тобто ніякі інші значення в це поле СУБД не дозволить внести.

Всі інші фрази оператора CREATE TABLE, що залишились називають обмеженнями для таблиці і можуть відокремлюватись (не обов’язково) фразою

      CONSTRAINT constraint_name.

Використання цієї фрази дозволить в подальшому відмінити те чи інше обмеження за допомогою оператора ALTER TABLE.

Фраза PRIMARY KEY визначає стовпчик або стовпчики, які утворюють первинний ключ таблиці. Визначення любої таблиці повинно містити не більше однієї фрази PRIMARY KEY. Система заблокує виконання любого оператора INSERT аба UPDATE який створює рядок з дубльованими значеннями в стовпчиках, вказаних у фразі PRIMARY KEY. Наприклад, якщо S₁ i S₂ – стовпчики, які вказані у фразі PRIMARY KEY, і в таблиці вже є рядок зі значеннями a i b у цих стовпчиках відповідно, то спроба вставити новий рядок зі значеннями a i b у цих стовпчиках буде заблокована.

Фраза FOREIGN KEY дозволяє визначити зовнішні ключі таблиці. Фраза містить такі елементи:

• список list_of_foreign_key_columns складається з імен одного або більше стовпчиків, які утворюють даний зовнішній ключ;

• фразу REFERENCES, яка визначає батьківську таблицю з іменем parent_table_name, яка містить потенційний ключ, поля якого визначені зовнішнім ключем (задаються списком list_of_candidate_key_columns);

• необов’язкову фразу ON UPDATE для визначення правила поновлення даних, які задаються параметром referential_action. Цей параметр може приймати одне з таких значеннь: CASCADE, SET NULL, SET DEFAULT, NO ACTION. Якщо фраза ON UPDATE опущена, за змовчуванням використовується правило NO ACTION;

• необов’язкову фразу ON DELETE для визначення правила поновлення даних, які задаються параметром referential_action. Цей параметр може приймати одне з таких значень: CASCADE, SET NULL, SET DEFAULT, NO ACTION. Якщо фраза ON DELETE опущена, за змовчуванням використовується правило NO ACTION.

Правила підтримки цілісності посилань, які задаються у фразі FOREIGN KEY після ON UPDATE і ON DELETE:

• CASCADE – виконується поновлення/видалення рядка з батьківської таблиці з автоматичним поновленням/видаленням всіх рядків дочірньої таблиці, пов’язаних з даним рядком батьківської таблиці;

• SET NULL – виконується видалення рядка з батьківської таблиці, а у зовнішні ключі дочірньої таблиці, пов’язаних з даним рядком батьківської таблиці заноситься значенння NULL. Цей варіант застосовується лише для тих стовпчиків зовнішнього ключа, у визначенні яких задано NULL;

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

• NO ACTION операція видалення рядка з батьківської таблиці відміняється. Це значення використовується за змовчуванням.

Приклади. Оператор

     CREATE TABLE Vidom(

     kod INTEGER NOT NULL,

     year SMALLINT NOT NULL,

    mounth SMALLINT NOT NULL,

     D REAL NOT NULL DEFAULT 0 ,

     CONSTRAINT ind PRIMARY KEY (kod,year, mounth))

створить таблицю з назвою Vidom, яка матиме стовпчики - kod, year, mounth, D і первинний ключ з іменем ind, який визначається стовпчиками kod,year, mounth. Причому поля kod,year, mounth, D не можуть приймати значення NULL, а поле D за змовчуванням прийматиме значення 0, тобто оператор INSERT, що не містить явного задання значення поля D, автоматично підставлятиме значення 0. Приклад використання CHECK для недопущення вводу помилкових значень.

      CREATE TABLE Osoba

      (ID_OSOBA INTEGER NOT NULL,

      PRIZ VARCHAR(50) NOT NULL,

      NAME VARCHAR(50) NOT NULL,

      POBAT VARCHAR(50) NOT NULL,

      SEX CHAR(4) CHECK (SEX IN (‘жін.’,’чол.’)),

      ID_FACULT SMALLINT NOT NULL)

Цей оператор створить таблицю Osoba з відповідними стовпчиками, причому в стовпчик SEX система не дозволить ввести нічого крім значень ‘жін.’і ’чол.’.

Слід зауважити, що не всі діалекти SQL підтримують фразу DEFAULT (Access 97 не підтримує цієї фрази).

Видалення таблиць (оператор DROP TABLE)

Непотрібні таблиці видаляються з бази з допомогою оператора DROP TABLE, який має формат:

      DROP TABLE table_name [RESTRICT | CASCADE]

Наприклад, для видалення таблиці Vidomist з бази необхідно застосувати оператор:

      DROP TABLE Vidomist

Якщо в операторі вказано ключове слово RESTRICT  і в базі існує хоча б один об’єкт, який пов’язаний з таблицею table_name (наприклад, VIEW в якому використовується ця таблиця), операція видалення буде відхилена. Якщо в операторі вказано ключове слово CASCADE, автоматично буде видалена таблиця table_name і всі об’єкти, які пов’язані з цією таблицею.

Створення індексу (оператор CREATE INDEX)

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

      CREATE [UNIQUE] INDEX index_name

      ON table_name(column [ASC | DESC][,…])

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

      CREATE  INDEX ind_osoba ON Osoba (kod ASC)

створює індекс ind_osoba по полю kod у висхідному порядку для таблиці Osoba.

Якщо в операторі вказано ключове слово UNIQUE, автоматично буде підтримуватись унікальність значень ключа. Тобто СУБД не дозволить існування двох рядків з однаковим

значенням індексу. В нашому прикладі це означає, що СУБД не допустить двох і більше рядків у таблиці Osoba з однаковим кодом.

Видалення індексу (оператор DROP INDEX)

Оператор DROP INDEX видаляє з бази створений раніше індекс. Він має формат:

      DROP INDEX index_name

Наприклад, оператор

      DROP INDEX ind_osoba

видаляє з бази створений раніше індекс ind_osoba для таблиці Osoba.

Модифікація структури таблиці (оператор ALTER TABLE)

Для зміни структури існуючої таблиці стандартом ISO передбачено оператор ALTER TABLE. Визначення цього оператора містить шість параметрів для виконання таких операцій:

• вставка в таблицю нового стовпчика;

• видалення стовпчика з таблиці;

• видалення з визначення таблиці існуючого обмеження;

• визначення для зазначеного стовпчика значення за змовчуванням;

• відміна встановленого значення за змовчуванням для даного стовпчика.

Оператор ALTER TABLE має такий формат:

      ALTER TABLE table_name

      [ADD [COLUMN] column_name data_type [NOT NULL] [UNIQUE]

      [DEFAULT default_option] [CHECK (search_condition)]]

      [DROP [COLUMN] column_name [RESTRICT | CASCADE]]

      [ADD [CONSTRAINT [constrain_name]] table_constraint_definition]

      [DROP CONSTRAINT  constrain_name [RESTRICT | CASCADE]]

      [ALTER [COLUMN] SET DEFAULT default_option]

      [ALTER [COLUMN] DROP DEFAULT]

Тут параметри мають ті ж призначення, що і в операторі CREATE TABLE. Параметр table_constraint_definition може приймати одне з таких значень: PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK. У фразі DROP COLUMN column_name  задається ім’я стовпчика, який видаляється з таблиці. Кваліфікатор RESTRICT означає, що якщо на даний стовпчик посилається який небудь інший об’єкт бази, він з таблиці не видалиться; кваліфікатор  CASCADE означає, що крім стовпчика вказаної таблиці будуть видалені всі посилання на даний стовпчик в базі. За змовчуванням діє кваліфікатор RESTRICT.

Оператор ALTER TABLE реалізовано не у всіх діалектах SQL. В деяких діалектах підтримується урізаний варіант цього оператора (не дозволяється видаляти вже існуючі стовпчики).

Використання транзакцій

Стандарт ISO визначає модель транзакцій, яка використовує оператори COMMIT і ROLLBACK. Більшість (але не всі) комерційних реалізацій мови SQL підтримують цю модель, яка вперше була реалізована в СУБД DB2 корпорації IBM. Транзакцією називається логічна одиниця роботи, що складається з одного або більше SQL-операторів, яка обробляється системою як єдина і неподільна дія. В стандарті вказується, що в мові SQL транзакція автоматично запускається любим  ініціалізуючого транзакцію SQL-оператором (наприклад, SELECT, INSERT, UPDATE). Зміни, що внесені в базу даних в ході виконання даної транзакції, не будуть видимі для любих інших транзакцій, що виконуються паралельно, до тих пір поки ця транзакція не буде завершена. Завершення транзакції може бути виконано одним з наступних способів:

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

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

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

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

В мові SQL заборонено використовувати вкладені транзакції. За допомогою оператора SET TRANSACTION користувачі мають змогу налаштовувати процедуру обробки транзакій. Основний формат цього оператора має вид:

SET TRANSACTION

[READ ONLY | READ WRITE] |

[ISOLATION LEVEL READ UNCOMMITTED | READ COMMITTED |

REPEATABLE READ | SERIALIZED]

Кваліфікатори READ ONLY і READ WRITE вказують, що в транзакціях допускається лише виконання операцій читання або читання і записування відповідно. За змовчуванням використовується кваліфікатор READ WRITE (якщо тільки не вибрано рівень ізоляції READ UNCOMMITTED). Показник рівня ізоляції визначає степінь взаємодії з іншими транзакціями.

Властивості транзакцій. Існують деякі властивості, які властиві кожній транзакції. Нижче наведено чотири основних властивостей.

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

• Узгодженність. Кожна транзакція повинна переводити базу даних з одного узгодженого стану в інший узгоджений стан.

• Ізольованість. Всі транзакції виконуються незалежно одна від одної. Тобто, проміжні результати незавершеної транзакції недоступні для інших транзакцій.

• Довготривалість. Результати успішно завершених (зафіксованих) транзакцій зберігаються в базі постійно і не повинні бути втраченими в результаті наступних аварійних ситуацій.

Управління доступом даних

Для організації захисту інформації в базі даних в мові SQL передбачені оператори GRANT i REVOKE. Механізм захисту побудований на використанні ідентифікаторів користувачів, які надають ним відповідні права і привілеї. Ідентифікатором користувача називається звичний ідентифікатор мови SQL (допустима послідовність символів) для ідентифікації певного користувача бази даних. Кожному користувачеві бази адміністратор бази даних (АБД) назначає певний ідентифікатор, який пов’язаний з певним паролем. Кожний SQL-оператор виконується від імені деякого користувача. Ідентифікатор користувача вказує на об’єкти бази даних з якими даний користувач може працювати а також які операції доступні для даного користувача.

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

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

• SELECT – право вибирати дані з таблиць;

• INSERT – право вставляти в таблицю нові рядки;

• UPDATE – право змінювати дані в таблиці;

• DELETE – право видаляти рядки з таблиці;

• REFFERENCES – право посилатись на стовпчики вказаної таблиці.

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

Надання привілеїв іншим користувачам (оператор GRANT)

Кожний користувач в середовищі SQL має спеціальний ідентифікатор (послідовність символів), який називають ідентифікатором користувача (authorization ID). В багатокористувацьких системах є процедура входу в систему, у відповідності з якою здійснюється доступ до бази даних. Ця процедура визначає ID, який пов’язаний з даним користувачем. Кожний користувач має свій ID, хоча для різних користувачів допускається використання однакових ID. Кожний користувач має свій набір привілеїв, тобто множину тих дій, які йому дозволено виконувати (вхід в систему це мінімальний привілей). Привілеї з часом можуть змінюватись: нові привілеї назначатись – старі відмінятись. Привілей дається конкретному користувачеві для окремої таблиці або представлення. Користувач, який створив дану таблицю, має всі права на неї. Він також може передати частину з них іншому користувачеві.

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

• SELECT – користувач з цим привілеєм може виконувати запити для цієї таблиці;

• INSERT - користувач з цим привілеєм може виконувати оператор INSERT для цієї таблиці;

• UPDATE - користувач з цим привілеєм може виконувати оператор UPDATE для цієї таблиці. Привілей обмежується множиною стовпчиків цієї таблиці;

• DELETE - користувач з цим привілеєм може виконувати оператор DELETE для цієї таблиці.

• REFERENCES - користувач з цим привілеєм може визначити зовнішній ключ, який використовує один або декілька стовпчиків.

Механізм SQL дозволяє виконати призначення користувачам цих привілеїв з допомогою оператора GRANT. Оператор GRANT має такий формат:

         GRANT {privileges_list | ALL PRIVILEGES}

         ON object_name

         TO {autorization_id_list | PUBLIC}

         [WITH GRANT OPTION]

Параметр privileges_list задає список, що містить один або більше стандартних привілеїв розділених комами. Крім того, для зручності, введено ключове слово ALL PRIVILEGES, що означає надання всіх привілеїв.

Параметр object_name задає ім’я об’єкту бази даних (таблиці, представлення, домену, набору символів).

Фраза WITH GRANT OPTION є необов’язковою. З її допомогою задають всім вказаним у списку autorization_id_list  користувачам передавати іншим користувачам всі надані їм привілеї. Якщо ця фраза опущена, одержувач привілеїв не зможе передавати свої права іншим користувачам.

Приклади. Надання всіх привілеїв щодо таблиці Osoba користувачеві Stud.

GRANT ALL PRIVILEGES

ON Osoba

TO Stud WITH GRANT OPTION

В результаті користувач з ідентифікатором Stud одержить право на модифікацію даних (вставлять, видалять, а також поновлювати рядки) в таблиці Osoba, а також вибирати дані з цієї таблиці. Оскільки в цьому операторі присутня фраза WITH GRANT OPTION, користувач Stud може передавати всі або частину своїх прав іншим користувачам.

Наступний приклад показує як надати користувачеві з ідентифікатором Admin привілеї SELECT і UPDATE  для стовпчика PRIZ таблиці Osoba.

GRANT SELECT,UPDATE(PRIZ)

ON Osoba

TO Admin

Оскільки фраза WITH GRANT OPTION відсутня, користувач Admin не зможе передати свої права іншим користувачам.

Наступний приклад показує як надати декільком користувачам з ідентифікаторами Admin і Stud привілею SELECT для таблиці Osoba.        

GRANT SELECT

ON Osoba

TO Admin, Stud

Приклад надання привілею SELECT всім користувачам:

GRANT SELECT

ON Osoba

TO PUBLIC

Використання ключового слова PUBLIC означає, що всі користувачі(які вже зареєстровані в базі, так і майбутні) одержать право використання таблиці Osoba в операторі SELECT.

Відміна наданих привілеїв (оператор REVOKE)

Для відміни наданих користувачам привілеїв використовують оператор REVOKE. Він має такий формат:

         REVOKE [GRANT OPTION FOR] {privilege_list | ALL PRIVILEGES}

         ON object_name

         FROM { autorization_id_list | PUBLIC} [RESTRICT | CASCSADE]

Ключове слово ALL PRIVILEGES означає відміну всіх привілеїв для вказаного користувача, які надані йому раніше.

Наприклад, якщо потрібно відмінити привілей на оператор SELECT для таблиці Osoba, наданий всім користувачам в попередньому прикладі, потрібно застосувати такий оператор REVOKE:

         REVOKE SELECT

         ON Osoba

         FROM PUBLIC

Якщо потрібно відмінити привілей на оператор SELECT для таблиці Osoba, наданий користувачеві з ідентифікатором Stud, необхідно застосувати такий оператор REVOKE:

         REVOKE SELECT

         ON Osoba

         FROM Stud.

Література

1. Томас Конноли, Каролин Бегг, Анна Страчан. Базы данных. Проектирование, реализация и сопровождение. Теория и практика. Москва: Издательский дом "Вильямс", 2000г. – 1120 с.

2. Мартин Грабер. Введение в SQL. Москва: - Издательство "Лори", 1996 г. – 380 с.

З повагою ІЦ “KURSOVIKS”!