На малюнку представлена ​​таблиця (ставлення ступеня 5), що містить деякі відомості про працівників гіпотетичного підприємства. Рядки таблиці відповідають кортежам. Кожен рядок фактично є описом одного об'єкта реального світу (в даному випадку працівника), характеристики якого містяться в стовпцях. Реляційні відносини відповідають наборам сутностей, а кортежі – сутностям. Стовпці в таблиці, що представляє реляційне ставлення, називають атрибутами.

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

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

Первинний ключ- у реляційній моделі даних один з потенційних ключів відношення, вибраний як основний ключ (або ключ за замовчуванням).

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

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

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

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

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

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

Наприклад, зв'язок між відносинами ВІДДІЛ і СПІВРОБІТНИК створюється шляхом копіювання первинного ключа "Номер_відділу" з першого відношення до другого. Таким чином, щоб отримати список працівників даного підрозділу, необхідно: 1) З таблиці ВІДДІЛ встановити значення атрибуту "Номер_відділу" , що відповідає даному "Найменуванню_відділу". 2) вибрати з таблиці СПІВРОБІТНИК всі записи, значення атрибута "Номер_відділу"яких дорівнює отриманому на попередньому кроці. Для того, щоб дізнатися, в якому відділі працює співробітник, потрібно виконати зворотну операцію: 1) Визначаємо "Номер_відділу"з таблиці СПІВРОБІТНИК. 2) За отриманим значенням знаходимо запис у таблиці ВІДДІЛ.

18. Нормалізація в реляційних БД, поняття нормальної форми під час проектування баз даних.

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

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

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

Функціональні залежності.

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

19. 1НФ: Основні визначення та правила перетворення.

Для обговорення першої нормальної форми необхідно дати два визначення:

Простий атрибут - атрибут, значення якого атомарні (неподільні).

Складний атрибут - виходить з'єднанням декількох атомарних атрибутів, які можуть бути визначені на одному або різних доменах (його називають вектор або агрегат даних).

Визначення першої нормальної форми:

відношення знаходиться в 1NF, якщо значення всіх його атрибутів атомарні. . Інакше це взагалі таблиця і такі атрибути необхідно декомпозувати.

Розглянемо приклад:

У базі даних відділу кадрів підприємства необхідно зберігати відомості про службовців, які можна спробувати подати щодо

СЛУЖАЧИЙ (НОМЕР_СЛУЖАЧОГО, ІМ'Я, ДАТА_НАРОДЖЕННЯ, ІСТОРІЯ_РОБОТИ, ДІТИ).

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

 ІСТОРІЯ_РОБОТИ (ДАТА_ПРИЙОМУ, НАЗВА, ІСТОРІЯ_ЗАРПЛАТИ),

 ІСТОРІЯ_ЗАРПЛАТИ (ДАТА_ПРИЗНАЧЕННЯ, ЗАРПЛАТА),

 ДІТИ (ІМ'Я_ДИТИНИ, РІК_НАРОДЖЕННЯ).

Їхній зв'язок представлений на рис. 3.3.

Рис.3.3. Початкове ставлення.

Для приведення вихідного відношення СЛУЖАЮЧИЙ до першої нормальної форми необхідно декомпозувати його на чотири відносини, оскільки це показано на наступному малюнку:

Рис.3.4. Нормалізована безліч відносин.

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

Алгоритм нормалізації описаний Е.Ф.Коддом так:

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

20. 2НФ: Основні визначеннята правила перетворення.

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

Визначення:

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

Приклад:

Нехай є відношення ПОСТАВКИ (N_ПОСТАЧАЛЬНИКА, ТОВАР, ЦІНА).
Постачальник може постачати різні товари, а той самий товар може поставлятися різними постачальниками. Тоді ключ відношення - "N_постачальника + товар". Нехай всі постачальники поставляють товар за тією ж ціною. Тоді маємо такі функціональні залежності:

• N_постачальника, товар -> ціна
• товар -> ціна

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

• ПОСТАВКИ (N_ПОСТАЧАЛЬНИКА, ТОВАР)
• ЦІНА_ТОВАРУ (ТОВАР, ЦІНА)

Таким чином, можна дати

Визначення другої нормальної форми: Відношення знаходиться в 2НФ, якщо воно знаходиться в 1НФ і кожен неключовий атрибут функціонально залежить від ключа.

21. 3НФ: Основні визначеннята правила перетворення.

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

Визначення:

Нехай X, Y, Z – три атрибути деякого відношення. У цьому X --> Y і Y --> Z, але зворотне відповідність відсутня, тобто. Z -/-> Y та Y -/-> X. Тоді Z транзитивно залежить від X.
Нехай є відношення ЗБЕРІГАННЯ ( ФІРМА, СКЛАД, ОБСЯГ), яке містить інформацію про фірми, що отримують товари зі складів, та обсяги цих складів. Ключовий атрибут - "фірма". Якщо кожна фірма може отримувати товар тільки з одного складу, то в цьому відношенні є такі функціональні залежності:

• фірма -> склад
• склад -> Об `єм

При цьому виникають аномалії:

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

Для усунення цих аномалій необхідно декомпозувати вихідне відношення на два:

• ЗБЕРІГАННЯ ( ФІРМА, СКЛАД)
• ОБСЯГ_СКЛАДУ ( Склад, ОБ `ЄМ)

Визначення третьої нормальної форми:

Відношення знаходиться в 3НФ, якщо воно знаходиться в 2НФ і кожен ключовий атрибут не транзитивно залежить від первинного ключа.

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

СУБД – це програмні засоби для створення, наповнення, оновлення та видалення БД.

Одиницею інформації, що зберігається в БД, є таблиця. Кожна таблиця є сукупністю рядків і стовпців, де рядки відповідають екземпляру об'єкта, конкретної події чи явищу, а стовпці – атрибутам (ознакам, характеристикам, параметрам) об'єкта, події чи явища. Кожен рядок містить інформацію про конкретну подію.

У термінах БД стовпці таблиці називаються полями, та її рядки – записами.

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

Пов'язані відносинами таблиці взаємодіють за принципом головна-підлегла. Одна й та сама таблиця може бути головною до однієї таблиці БД і дочірньої до іншої.

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

Сутність - Відображення об'єкта в пам'яті людини або комп'ютера.

Атрибут - Конкретне значення будь-якої з властивостей сутності.

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

Поле зв'язку – це поле, яким дві таблиці пов'язані.

Первинні та вторинні ключі

У кожній таблиці БД може існувати первинний ключ – це поле чи табір полів, що однозначно ідентифікує запис.

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

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

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

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

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

Реляційні відносини між таблицями

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

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

Подібно до зв'язку одним, зв'язок один до одного може бути жорстким і нежорстким.

Таблиці

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

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

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

Кожен стовпчик у таблиці має своє ім'я, яке зазвичай служить заголовком стовпця. Всі стовпці в одній таблиці повинні мати унікальні імена, однак дозволяється надавати однакові імена стовпцям, які розташовані в різних таблицях. У реляційній моделі даних атрибути відносин не впорядковані, тобто звернення до полів завжди відбувається за іменами, а не за розташуванням. Однак у мові SQL допускається індексне вказівку стовпців таблиць, у своїй стовпці розглядаються порядку зліва направо (їх порядок визначається під час створення таблиці) .

У будь-якій таблиці завжди є щонайменше 1 стовпець. У стандарті ANSI/ISO не вказується максимально допустима кількість стовпців у таблиці, проте майже у всіх комерційних СУБД ця межа існує. У СУБД Firebird ця межа становить 32767 стовпців.

У РМДреляційної моделі даних для позначення рядка відношення використовується поняття кортеж. Поданням кортежу фізично є рядок таблиці бази даних. Рядки таблиці немає імен і певного порядку. У таблиці може бути будь-яка кількість рядків. Цілком допустиме існування таблиці з нульовою кількістю рядків. Така таблиця називається порожньою. Порожня таблиця зберігає структуру, визначену її стовпцями, просто не містять дані. Зазвичай, не накладається обмежень кількість рядків у таблиці, й у багатьох СУБД розмір таблиць обмежений лише вільним дисковим простором комп'ютера. В інших СУБД є максимальна межа, проте вона дуже висока – близько двох мільярдів рядків, а іноді й більше.

Проілюструємо найбільш наочно структуру однієї з таблиць навчальної бази даних (див. додаток А). На рис. 1.1 наведено структуру таблиці Abonent, що містить відомості про абонентів компаній житлово-комунального господарства.

Мал. 1.1.Структура реляційної таблиці Abonent

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

Кожен вертикальний стовпець таблиці є сукупністю значень конкретного атрибута об'єкта. Наприклад, у стовпці AccountCD містяться унікальні номери особових рахунків абонентів. У стовпці Phone містяться телефонні номери абонентів.

На перетині кожного рядка з кожним стовпцем таблиці міститься точно одне значення даних. Наприклад, у рядку, який представляє абонента Конюхова В. С., у стовпці Fio міститься значення "Конюхов В.С.". У стовпці AccountCD того ж рядка міститься значення "015527", яке є номером особового рахунку абонента з ПІБ Конюхов В.С.

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

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

У навчальній базі даних визначено такі домени:

§ Boollean (Логічний): SMALLINT. Поля, що визначаються на цьому домені, можуть набувати лише цілочисельних значень, що дорівнює 0 або 1. Це досягається накладенням в домені умови перевірки (CHECK) на значення, що приймаються цим доменом.

§ Money (Гроші): NUMERIC(15,2). Домен призначений визначення у таблицях полів, що зберігають грошові суми.

§ PKField (Поле ПК): INTEGER. Домен призначений для визначення первинних та зовнішніх ключів таблиць. Обмеження обов'язковості даних (NOT NULL) цей домен не накладено. Воно накладається при оголошенні первинного ключа таблиці. Це зроблено для того, щоб визначити зовнішній ключ на цьому домені без умови NOT NULL.

§ TMonth (Місяць): SMALLINT. Домен призначений визначення в таблицях полів, що містять номери місяців. Цілочисленні значення в такому полі можуть перебувати в діапазоні 1...12.

§ TYear (Рік): SMALLINT. Домен призначений визначення полів, містять номер року. Цілочисленні значення можуть перебувати в діапазоні 1990...2100.

Оскільки рядки в реляційній таблиці не впорядковані, не можна вибрати рядок за номером у таблиці. У таблиці немає «першого», «останнього» або «тринадцятого» рядка. Тоді яким чином можна вказати в таблиці конкретний рядок, наприклад рядок для абонента з ПІБ Аксьонов С.А.?

Ключовим елементом данихназивається такий елемент, яким можна визначити значення інших елементів даних.

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

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

Повернемося до розгляду таблиці Abonent навчальної бази даних (рис. 1.1). На перший погляд, первинним ключем таблиці Abonent можуть бути і стовпець AccountCD, і стовпець Fio. Однак якщо будуть зареєстровані 2 абоненти з однаковими ПІБ, то стовпець Fio більше не зможе виконувати роль первинного ключа. На практиці як первинні ключі таблиць зазвичай слід вибирати ідентифікатори, такі як унікальний номер особового рахунку абонента (AccountCD в таблиці Abonent), ідентифікатор вулиці (StreetCD в таблиці Street) і т.д.

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

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

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

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

Первинний ключ

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

Унікальний ключ

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

• унікальних ключів для однієї таблиці може бути кілька (запитання на засипку для тих, хто прочитав статтю про нормалізацію: правила якоїсь нормальної форми при цьому будуть порушені? ;)
• Унікальні ключі можуть мати значення NULL, при цьому якщо є декілька рядків із значеннями унікального ключа NULL, такі рядки згідно зі стандартом SQL 92 вважаються різними (унікальними).

Зовнішній ключ

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

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

І найголовніше. Усі значення зовнішнього ключа повинні збігатися з будь-яким із значень батьківського ключа. (Зауважимо у дужках щодо збігу/розбіжності: нюанси виникають, коли у значеннях стовпців вторинного ключа зустрічається NULL. Давайте поки в ці нюанси вдаватися не будемо). Поява значень зовнішнього ключа, для яких немає відповідних значень батьківського ключа, є неприпустимим. Ось тут ми плавно переходимо до поняття цілісності посилання.

Посилальна цілісність

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

Цікавіші моменти виникають, коли ми видаляємо чи змінюємо рядки батьківської таблиці. Як при цьому не допустити появи рядків дочірньої таблиці, що "бовтаються в повітрі"? Для цього існують правила цілісності ON UPDATE і ON DELETE, які, за стандартом SQL 92, можуть містити наступні інструкції:

• CASCADE – забезпечує автоматичне виконання у дочірній таблиці тих самих змін, які були зроблені у батьківському ключі. Якщо батьківський ключ був змінений - ON UPDATE CASCADE забезпечить такі самі зміни зовнішнього ключа в дочірній таблиці. Якщо рядок батьківської таблиці видалено, ON DELETE CASCADE забезпечить видалення всіх відповідних рядків дочірньої таблиці.
• SET NULL – при видаленні рядка батьківської таблиці ON DELETE SET NULL встановить значення NULL у всіх стовпцях вторинного ключа у відповідних рядках дочірньої таблиці. При зміні батьківського ключа ON UPDATE SET NULL встановить значення NULL у відповідних шпальтах відповідних рядків (про як:) дочірньої таблиці.
• SET DEFAULT - працює аналогічно SET NULL, лише записує у відповідні осередки не NULL, а значення, встановлені за замовчуванням.
• NO ACTION (встановлено за замовчуванням) - при зміні батьківського ключа жодних дій із зовнішнім ключем у дочірній таблиці не провадиться. Але якщо зміна значень батьківського ключа призводить до порушення посилальної цілісності (тобто до появи рядків дочірньої таблиці, що "висять у повітрі"), то СУБД не дасть зробити такі зміни батьківської таблиці.

Ну а зараз – від спільного до приватного.

Ключі та посилальна цілісність у MySQL та Oracle

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

• NO ACTION (встановлюється за умовчанням) у більш жорсткому, ніж за стандартом SQL 92, варіанті: забороняється зміна та видалення рядків батьківської таблиці, котрим є зв'язані рядки в дочірніх таблицях.
• ON DELETE CASCADE.

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

MySQL версії 4.1 (остання на момент написання статті стабільна версія) дозволяє в командах CREATE/ALTER TABLE задавати фрази REFERENCES/FOREIGN KEY, але в роботі їх не враховує і реально зовнішні ключі не створює. Відповідно правила посилальної цілісності, що реалізуються через зовнішні ключі, MySQL не підтримуються. І всі турботи щодо забезпечення цілісності та несуперечності інформації в базі MySQL лягають на плечі розробників клієнтських додатків.

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

• Переклад

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

4. ТАБЛИЦІ І ПЕРВИННІ КЛЮЧІ

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

У таблиці є 6 уроків. Усі 6 – різні, але кожного уроку значення однакових полів зберігаються у таблиці, саме: tutorial_id (ідентифікатор уроку), title (заголовок) і category (категорія). Tutorial_id – первинний ключтаблиці уроків. Первинний ключ – це значення, яке є унікальним для кожного запису в таблиці.
У таблиці клієнтів нижче за customer_id – первинний ключ. У разі первинний ключ – також унікальне значення (число) кожної записи.

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

У базі даних первинні ключі застосовуються для ідентифікації. У житті первинні ключі довкола нас скрізь. Щоразу, коли ви стикаєтеся з унікальним числом, це число може бути первинним ключем у базі даних (може, але не обов'язково має використовуватися як таке. Всі бази даних здатні автоматично генерувати унікальне значення для кожного запису у вигляді числа, яке автоматично збільшується і вставляється разом з кожним новим записом [Т.зв. синтетичний або сурогатний первинний ключ - прим.перев.]).

Декілька прикладів

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

Що поєднує ці приклади? Те, що у всіх їх як первинний ключ вибирається унікальне, не повторюване значення кожної записи. Ще раз. Значення поля таблиці бази даних, вибраного як первинний ключ, завжди унікально.

Що характеризує первинний ключ? Характеристики первинного ключа

Первинний ключ використовується для ідентифікації записів.

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

Первинний ключ є унікальним.

Первинний ключ має унікальне значення. Уявіть, що його значення не є унікальним. Тоді його не можна було б використовувати для того, щоб ідентифікувати дані в таблиці. Це означає, що будь-яке значення первинного ключа може зустрітися в стовпці, який обраний як первинний ключ, лише один раз. РСУБД влаштовані так, що не дозволять вам вставити дублікати у поле первинного ключа, отримайте помилку.
Ще один приклад. Уявіть, що у вас є таблиця з полями first_name та last_name і є два записи:

| first_name | last_name |
| vasya | pupkin |
| vasya | pupkin |

Тобто. є два Васі. Ви хочете вибрати з таблиці якогось конкретного Васю. Як це зробити? Записи нічим один від одного не відрізняються. Отут і допомагає первинний ключ. Додаємо стовпець id (класичний варіант синтетичного первинного ключа) та…

Id | first_name | last_name |
1 | vasya | pupkin |
2 | vasya | pupkin |

Тепер кожен Вася є унікальним.

Типи первинних ключів.

Зазвичай первинний ключ – числове значення. Але він також може бути будь-яким іншим типом даних. Не є звичайною практикою використання рядка як первинний ключ (рядок – фрагмент тексту), але теоретично і практично це можливо.
Складові первинні ключі.
Часто первинний ключ складається з одного поля, але може бути і комбінацією кількох стовпців, наприклад, двох (трьох, чотирьох…). Але ви пам'ятаєте, що первинний ключ завжди унікальний, а отже, потрібно, щоб комбінація n-го кількості полів, в даному випадку 2-х, була унікальна. Докладніше про це розповім пізніше.

Автонумерація.

Поле первинного ключа часто, але завжди, обробляється самої базою даних. Ви можете, умовно кажучи, сказати базі даних, щоб вона сама автоматично надавала унікальне числове значення кожного запису під час її створення. База даних зазвичай починає нумерацію з 1 і збільшує це число для кожного запису на одну одиницю. Такий первинний ключ називається автоінкрементним чи автонумерованим. Використання автоінкрементних ключів – добрий спосіб завдання унікальних первинних ключів. Класична назва такого ключа – сурогатний первинний ключ [Як згадувалося вище. - прим. перев.]. Такий ключ не містить корисної інформації, що стосується сутності (об'єкта), інформація про яку зберігається в таблиці, тому він і називається сурогатним.

5. ЗВ'ЯЗУВАННЯ ТАБЛИЦЬ ЗА ДОПОМОГОЮ ЗОВНІШНІХ КЛЮЧІВ

Коли я починав розробляти бази даних, я часто намагався зберігати інформацію, яка здавалася спорідненою, в одній таблиці. Я міг, наприклад, зберігати інформацію про замовлення у таблиці клієнтів. Адже замовлення належать клієнтам, правда? Ні. Клієнти та замовлення являють собою окремі сутності у базі даних. І тому й іншому потрібна власна таблиця. А записи у цих двох таблицях може бути пов'язані у тому, щоб встановити відносини з-поміж них. Проектування бази даних – це вирішення двох питань:

• визначення того, які сутності ви хочете зберігати у ній
• які зв'язки між цими сутностями існують

Один-до-багатьом.

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

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

Яку інформацію ми зберігатимемо? Вирішуємо перше запитання.

Для початку ми визначимо якусь інформацію про замовленняхі про клієнтівми зберігатимемо. Щоб це зробити, ми повинні поставити собі запитання: “Які поодинокі блоки інформації відносяться до клієнтів, а які поодинокі блоки інформації відносяться до замовлень?”

Проектуємо таблицю клієнтів.

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

• customer_id (primary key) – ідентифікатор клієнта
• first_name - ім'я
• last_name - по-батькові
• address - адреса
• zip_code – поштовий індекс
• country - країна
• birth_date – дата народження
• username – реєстраційне ім'я користувача (логін)
• password – пароль

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

Створення таблиці в SQLyog. Зверніть увагу, що вибрано прапорець первинного ключа (PK) для поля customer_id. Поле customer_id є первинним ключем. Також вибрано прапорець Auto Incr, що означає, що база даних автоматично підставлятиме унікальне числове значення, яке, починаючи з нуля, щоразу збільшуватиметься на одну одиницю.

Проектуємо таблицю замовлень.
Які мінімальні блоки інформації, необхідні нам, належать до замовлення?

• order_id (primary key) – ідентифікатор замовлення
• order_date – дата та час замовлення
• customer – клієнт, який зробив замовлення

Нижче приклад таблиці в SQLyog.

Ці дві таблиці ( клієнтіві замовлень) пов'язані тому, що поле customerу таблиці замовлень посилається на первинний ключ ( customer_id) таблиці клієнтів. Такий зв'язок називається зв'язком із зовнішнього ключа. Ви повинні уявляти зовнішній ключ як просту копію (копію значення) первинного ключа іншої таблиці. У нашому випадку значення поля customer_idз таблиці клієнтівкопіюється до таблиці замовленьпри вставці кожного запису. Таким чином, у нас кожне замовлення прив'язане до клієнта. І замовлень у кожного клієнта може бути багато, як і говорилося вище.

Створення зв'язку із зовнішнього ключа.

Ви можете запитати себе: “Як я можу переконатися або як я можу побачити, що поле customer у таблиці замовлень посилається на поле customer_id у таблиці клієнтів”. Відповідь проста – ви не можете зробити це тому, що я ще не показав вам, як створити зв'язок.
Нижче вікно SQLyog з вікном, яке я використовував для створення зв'язку між таблицями.

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

У вікні вище ви можете бачити, як поле customer таблиці замовлень зліва зв'язується з первинним ключем (customer_id) таблиці клієнтів праворуч.

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

Замовлення пов'язані з клієнтами через поле customer, яке посилається на таблицю клієнтів.

На зображенні ви бачите, що клієнт maryпомістила три замовлення, клієнт pabloпомістив один, а клієнт john- жодного.
Ви можете запитати: “А щосаме замовили всі ці люди?” Це гарне питання. Ви, можливо, очікували побачити замовлені товари в таблиці замовлень. Але це поганий приклад проектування. Як би ви помістили численні продукти в єдиний запис? Товари- Це окремі сутності, які повинні зберігатися в окремій таблиці. І зв'язок між таблицями замовленьі товарівбуде зв'язком одним-багатьом. Я розповім про це далі.

6. СТВОРЕННЯ ДІАГРАМИ СУТНІСТЬ-ЗВ'ЯЗОК

Раніше ви дізналися, як записи з різних таблиць зв'язуються один з одним у реляційних базах даних. Перед створенням та зв'язуванням таблиць важливо, щоб ви подумали про сутності, які існують у вашій системі (для якої ви створюєте базу даних) і вирішили яким чином ці сутності б зв'язувалисяодин з одним. У проектуванні баз даних сутності та їх відносини зазвичай надаються в діаграмі сутність-зв'язок (англ. entity-relationship diagram, ERD). Ця діаграма є результатом процесу проектування бази даних.

Сутність.

Ви можете поставити запитання, що ж таке сутність. Нуу… це “річ” у системі. Там. Моя Мама завжди хотіла, щоб я став учителем, тому що я дуже добре пояснюю різні речі.

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

Давайте візьмемо інтернет-магазин для прикладу. Інтернет-магазин продає товари. Товарміг би стати очевидною сутністю у системі інтернет-магазину. Товари замовляютьсяклієнтами. Ось ми з вами й побачили ще дві очевидні сутності: замовленняі клієнти.

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

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

Давайте не буде надто академічним.

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

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

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

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

Зв'язки.

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

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