4. Лекція

ТЕМА: Створення бази даних і проектування таблиць

МЕТА: Визначити процес створення бази даних. Описати оператори створення, зміни бази даних. Розглянути можливість вказівки імені файлу або декількох файлів для зберігання даних, розмірів і місцеположення файлів. Проаналізувати оператори створення, зміни, видалення призначених для користувача таблиць. Навести опис параметрів для оголошення стовпців таблиці. Дати поняття і характеристика індексів. Розглянути оператори створення і зміни індексів. Визначити роль індексів в підвищенні ефективності виконання операторів SQL.

ПЛАН

1 База даних

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

1.2 Створення бази даних в середовищі MS SQL Server

1.3 Зміна бази даних

1.4 Видалення бази даних

2 Таблиця

2.1 Створення таблиці

2.2 Зміна таблиці

2.3 Видалення таблиці

2.4 Індекси

2.5 Створення індексу

2.6 Видалення індексу

1 База даних

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

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

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

Стандарт SQL залишає за розробниками СУБД право вибору конкретного механізму створення і знищення каталогів, проте механізм створення і видалення схем регламентується за допомогою операторів CREATE SCHEMA і DROP SCHEMA. В стандарті також вказано, що в рамках оператора створення схеми повинна існувати можливість визначення діапазону привілеїв, доступних користувачам створюваної схеми. Проте конкретні способи визначення подібних привілеїв в різних СУБД розрізняються.

В даний час оператори CREATE SCHEMA і DROP SCHEMA реалізовані в дуже небагатьох СУБД. В інших реалізаціях, наприклад, в СУБД MS SQL Server, використовується оператор CREATE DATABASE.

1.2 Створення бази даних в середовищі ms sql Server

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

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

<визначення_бази_даних> ::=

CREATE DATABASE ім’я_бази_даних

[ON [PRIMARY]

[ <визначення_файла> [,...n] ]

[,<визначення_групи> [,...n] ] ]

[LOG ON {<визначення_файла>[,...n]} ]

[FOR LOAD | FOR ATTACH ]

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

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

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

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

Параметр PRIMARY визначає первинний файл. Якщо він опущений, то первинним є перший файл в списку.

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

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

<визначення_файла>::=

([NAME=логічне_ім’я_файла]

FILENAME=фізичне_ім’я_файла

[,SIZE=розмір_файла ]

[,MAXSIZE={max_розмір_файла |UNLIMITED }]

[, FILEGROWTH=величина_приросту ] )[,...n]

Тут логічне ім'я файлу – це ім'я файлу, під яким він пізнаватиметься при виконанні різних SQL-команд.

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

Параметр SIZE визначає первинний розмір файлу; мінімальний розмір параметра – 512 Кб, якщо він не вказаний, за умовчанням приймаються 1 Мб.

Параметр MAXSIZE визначає максимальний розмір файлу бази даних. При значенні параметра UNLIMITED максимальний розмір бази даних обмежується вільним місцем на диску.

При створенні бази даних можна дозволити або заборонити автоматичне зростання її розміру (це визначається параметром FILEGROWTH ) і вказати приріст за допомогою абсолютної величини в Мб або процентним співвідношенням. Значення може бути вказано в кілобайтах, мегабайтах, гігабайтах, терабайтах або відсотках (%). Якщо вказано число без суфікса МБ, КБ або %, то за умовчанням використовується значення MБ. Якщо розмір кроку зростання вказаний у відсотках (%), розмір збільшується на задану частину у відсотках від розміру файлу. Вказаний розмір округляється до найближчих 64 КБ.

Додаткові файли можуть бути включені в групу:

<визначення_групи>::=FILEGROUP ім’я_групи_файлів

<визначення_файла>[,...n]

Приклад 4.1. Створити базу даних, причому для даних визначити три файли на диску С, для журналу транзакцій – два файли на диску С.

CREATE DATABASE Archive

ON PRIMARY ( NAME=Arch1 FILENAME=’c:\user\data\archdat1.mdf’ SIZE=100MB, MAXSIZE=200, FILEGROWTH=20)

(NAME=Arch2 FILENAME=’c:\user\data\archdat2.mdf’ SIZE=100MB, MAXSIZE=200, FILEGROWTH=20)

(NAME=Arch3 FILENAME=’c:\user\data\archdat3.mdf’ SIZE=100MB, MAXSIZE=200, FILEGROWTH=20)

LOG ON

(NAME=Archlog1 FILENAME=’c:\user\data\archlog1.ldf’ SIZE=100MB, MAXSIZE=200, FILEGROWTH=20)

(NAME=Archlog2 FILENAME=’c:\user\data\archlog2.ldf’ SIZE=100MB, MAXSIZE=200, FILEGROWTH=20)

Приклад 4.1. Створення бази даних.

1.3 Зміна бази даних

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

<зміна_бази_даних> ::=

ALTER DATABASE ім’я_бази_даних

{ ADD FILE <визначення_файла>[,...n]

[TO FILEGROUP ім’я_групи_файлів ]

| ADD LOG FILE <визначення_файла>[,...n]

| REMOVE FILE логічне_ім’я_файла

| ADD FILEGROUP ім’я_групи_файлів

| REMOVE FILEGROUP ім’я_групи_файлів

| MODIFY FILE <визначення_файла>

| MODIFY FILEGROUP ім’я_групи_файлів

<властивість_групи_файлів>}

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

В базу даних можна додати (ADD) нові файли даних (у вказану групу файлів або в групу, прийняту за умовчанням) або файли журналу транзакцій.

Параметри файлів і груп файлів можна змінювати (MODIFY).

Для видалення з бази даних файлів або груп файлів використовується параметр REMOVE. Проте видалення файлу можливе лише за умови його звільнення від даних. Інакше сервер не дозволить видалення.

Як властивості групи файлів використовуються наступні:

READONLY – група файлів використовується тільки для читання; READWRITE – в групі файлів дозволяються зміни; DEFAULT – вказана група файлів приймається за умовчанням.

1.4 Видалення бази даних

Видалення бази даних здійснюється командою:

DROP DATABASE ім’я_бази_даних [,...n]

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

2 Таблиця

2.1 Створення таблиці

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

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

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

Приступаючи до створення таблиці, необхідно мати відповіді на ряд питань:

1. Як називатиметься таблиця?

2. Як називатимуться стовпці (поля) таблиці?

3. Які типи даних будуть закріплені за кожним стовпцем?

4. Який розмір пам'яті повинен бути виділений для зберігання кожного стовпця?

5. Які стовпці таблиці вимагають обов'язкового введення?

6. З яких стовпців полягатиме первинний ключ?

Базовий синтаксис оператора створення таблиці має наступний вигляд:

<визначення_таблиці> ::=

CREATE TABLE ім’я_таблиці

(ім’я_колонкитип_даних

[NULL | NOT NULL ] [,...n])

Приведений стандарт співпадає з реалізацією оператора створення таблиці в середовищі MS SQL Server.

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

Ключове слово NULL використовується для вказівки того, що в даному стовпці можуть міститися значення NULL. Значення NULL відрізняється від пропуску або нуля – до нього вдаються, коли необхідно вказати, що дані неприступні, опущені або недопустимі. Якщо вказано ключове слово NOT NULL, то відхилюватимуть будь-які спроби помістити значення NULL в даний стовпець. Якщо вказаний параметр NULL, приміщення значень NULL в стовпець дозволено. За умовчанням стандарт SQL припускає наявність ключового слова NULL.

Ми використовували спрощену версію оператора CREATE TABLE стандарту SQL. Його повна версія приводиться при обговоренні питань забезпечення цілісності даних.

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

CREATE TABLE Товар

(Назва VARCHAR(50) NOT NULL

Ціна MONEY NOT NULL

Тип VARCHAR(50) NOT NULL

Сорт VARCHAR(50)

МістоТовару VARCHAR(50))

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

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

CREATE TABLE Клієнт

(Фірма VARCHAR(50) NOT NULL

Прізвище VARCHAR(50) NOT NULL

Ім'я VARCHAR(50) NOT NULL

По Батькові VARCHAR(50)

МістоКлієнта VARCHAR(50)

Телефон CHAR(10) NOT NULL)

Приклад 4.3. Створення таблиці для збереження відомостей про постійних клієнтів.

2.2 Зміна таблиці

Структура існуючої таблиці може бути модифікована за допомогою команди ALTER TABLE, спрощений синтаксис якої представлений нижче:

ALTER TABLE ім’я_таблиці

{[ADD [COLUMN] ім’я_колонкитип_даних [NULL | NOT NULL ]]

| [DROP [COLUMN] ім’я_колонки]}

В середовищі MS SQL Server спрощений синтаксис команди модифікації таблиці має вигляд:

ALTER TABLE ім’я_таблиці

{[ALTER COLUMN ім’я_колонки

\новий_тип_даних [(точність[,масштаб])]

[ NULL | NOT NULL ]}]

| ADD { [ім’я_колонки тип_даних]

| ім’я_колонки AS вираз } [,...n]

| DROP {COLUMN ім’я_колонки}[,...n]

}

Команда дозволяє додавати і видаляти стовпці, змінювати їх визначення.

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

Проте існує спосіб додавання обов'язкових полів в існуючу таблицю. Для цього необхідно:

• додати в таблицю новий стовпець, визначивши його з атрибутом NULL (тобто стовпець не зобов'язаний містити яких-небудь значень);

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

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

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

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

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

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

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

• кількість десяткових знаків числового типу даних може бути зменшене або збільшене;

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

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

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

Приклад 4.4. Додати в таблицю Клієнт поле для номера розрахункового рахунку.

ALTER TABLE Клієнт ADD Роз_рахунок CHAR(20)

Приклад 4.4. Додавання в таблицю Клієнт поля для номера розрахункового рахунку.