Home › Blogs › Pavlo Zhezhnych's blog

Створення схеми бази даних за допомогою SQL

Sat, 24/03/2007 - 14:22 — Pavlo Zhezhnych

Інформація в реляційних базах даних зберігається у вигляді зв’язаних таблиць. Кожна таблиця має власну схему (структуру), яка включає назву таблиці та набір полів (атрибутів, колонок). Сукупність усіх схем таблиць становлять схему реляційної бази даних.
Для створення схеми бази даних в мові SQL передбачений запит CREATE TABLE. За допомогою цього запиту створюється таблиця як об’єкт бази даних, яка не містить записів (даних).
Запит для створення таблиці має наступний вигляд:
CREATE TABLE назва_таблиці
({визначення_поля | обмеження_таблиці},…);
При створенні нової таблиці обов’язково потрібно вказати назву_таблиці, яка повинна бути унікальною в межах бази даних та список визначень_полів, записаних через кому. Поряд з визначеннями_полів допускаються обмеження_таблиці .
визначення_поля ::=
назва_поля тип [обмеження_поля …] [DEFAULT значення_за_замовчуванням]
Для кожного поля вказується назва_поля, його тип і (необов’язково) обмеження_поля , записані через пропуск. Назва_поля повинна унікальною в межах одної таблиці.
В SQL існують наступні основні типи для визначення даних:

  • Символьний тип з фіксованою довжиною:

    • CHARACTER [(довжина)]
    CHAR [(довжина)]
    Вищенаведені типи є еквівалентними та відображають текстову стрічку з фіксованою довжиною. Довжина – додатне ціле число, яке визначає максимальну кількість символів. Якщо стрічка містить менше символів ніж довжина, то решта заповнюється пропусками. Якщо параметр довжина не вказано, то значенням по замовчуванню вважається 1.

  • Символьний тип зі змінною довжиною:

    • CHARACTER VARYING (довжина)
    VARCHAR (довжина)
    Вищенаведені типи є еквівалентними та відображають текстову стрічку зі змінною довжиною. Тут довжина задає максимальну кількість символів та є обов’язковою. Реальна довжина стрічки може коливатися від 0 до параметра довжина.

  • Числовий тип:

    • NUMERIC [(точність[, масштаб])]
    NUMBER [(точність[, масштаб])]
    Параметри точність та масштаб є необов’язковими. Точність – це загальна кількість значущих цифр, які використовуються для відображення числа. Масштаб задає кількість значущих цифр справа після крапки. Якщо масштаб не вказати, то по замовчуванню його значення дорівнює 0.

  • Часові типи:

    • Тип даних DATE описує дати та містить три компоненти: YEAR (рік), MONTH (місяць), DAY (день). Дата записується у форматі ‘dd.mm.yyyy’, тобто 4 позиції виділяються на рік, по 2 позиції на місяць та день. Наприклад, 12 серпня 2004 року записується наступним чином:
    ’12.07.2004’
    Тип даних TIME описує час і також містить три компоненти: HOUR (година), MINUTE (хвилина), SECOND (секунда). Час записується у форматі ‘hh:nn:ss’, тобто по 2 позиції виділяються на кожну з компонент. Наприклад, час 10 година 38 хвилин та 54 секунд записується наступним чином:
    ’10:38:54’
    Тип даних TIMESTAMP є комбінацією попередніх двох . Наприклад, значення поля типу TIMESTAMP може мати наступне значення:
    ‘12.07.2004 10:38:54’

В Microsoft Access існують наступні основні типи даних, які є аналогічні до відповідних типів стандарту SQL:

  • Символьний тип:

    • CHARACTER
    Довжина поля символьного типу може становити від 1 до 255 символів. Microsoft Access не резервує місце в базі даних, якщо дійсна довжина поля є меншою за встановлену максимальну довжину. Тобто цей тип даних відповідає символьному типу даних зі змінною довжиною в SQL.

  • Числовий тип:

    • BIT, SMALLINT, INTEGER
    REAL, FLOAT, DECIMAL
    Довжина числових полів вимірюється не в символах, що є традиційним для СКБД, а в байтах. Перша група типів позначає цілі числа довжиною 1, 2 та 4 байти відповідно. Друга група типів позначає дійсні числа довжиною 4, 8 та 17 байтів.

  • Часовий тип:

    • DATETIME
    Часовий тип даних в Access аналогом типу TIMESTAMP в SQL, тобто служить для збереження інформації як про дати, так і про час. Розмірність поля становить 8 байт і забезпечує збереження дат від 100 до 9999 року. В SQL-запитах дата записується у форматі #dd.mm.yyyy#. Наприклад, 12 серпня 1998 року записується наступним чином:
    #12.07.2004#
    Час записується у форматі #hh:nn:ss#. Наприклад, час 10 година 38 хвилин та 54 секунд записується наступним чином:
    #10:38:54#

Окрім основних типів в Access можна використовувати додаткові типи даних:

  • Текстовий тип:

    • TEXT
    Тип TEXT призначений для збереження довгих текстів. Довжина поля текстового типу може становити до 65535 символів.

  • Тип OLE-об’єкта:

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

Усі обмеження цілісності можна поділити на наступні типи:

  • Первинний ключ (PRIMARY KEY);
  • Унікальний ключ (UNIQUE);
  • Обмеження обов’язковості (NULL/NOT NULL);
  • Зовнішній ключ (FOREIGN KEY/REFERENCE);
  • Обмеження контролю значень (CHECK).

Обмеження цілісності можуть відноситись або до цілої таблиці (обмеження_таблиці) або до окремого поля (обмеження_поля). В якості обмежень_таблиці можуть служити усі обмеження крім обмежень обов’язковості.
обмеження_таблиці ::=
[CONSTRAINT назва_обмеження]
{PRIMARY KEY (поле,…)
| UNIQUE (поле,…)
| FOREIGN KEY (поле,…) REFERENCES таблиця[(поле,…)]
| CHECK (предикат)}
В якості обмежень_поля можуть служити усі без винятку обмеження цілісності.
обмеження_поля ::=
[CONSTRAINT назва_обмеження]
{NOT NULL
| PRIMARY KEY
| UNIQUE
| REFERENCES таблиця [(поле,…)]
| CHECK (предикат)}
Слід відзначити, що Microsoft Access не підтримує в SQL-запитах обмеження контролю значень (частина CHECK) . Крім того, ключове слово CONSTRAINT є обов’язковим для усіх обмежень крім обмежень обов’язковості (обмеження NOT NULL не вимагає ключового слова CONSTRAINT).
Розглянемо спроектовану за допомогою ER-діаграми схему бази даних (рис. 1).
Приклад ER-діаграми в нотації Баркера
Рис. 1. Приклад ER-діаграми в нотації Баркера.
Запити на створення відповідних таблиць матимуть наступний вигляд.

Сутність Стандарт SQL
Предмет CREATE TABLE rf_course (
id VARCHAR(50) PRIMARY KEY,
name VARCHAR(200) NOT NULL,
recnote VARCHAR(1000));
Тема CREATE TABLE rf_theme(
id VARCHAR(50) PRIMARY KEY,
course_id VARCHAR(50) NOT NULL REFERENCES rf_course (id),
lessontype VARCHAR(2) NOT NULL CHECK(lessontype IN (‘lc’,’lb’,’pr’)),
name VARCHAR(200) NOT NULL,
recnote VARCHAR(1000));
Група CREATE TABLE rf_group (
id VARCHAR(50) PRIMARY KEY,
name VARCHAR(200) NOT NULL,
recnote VARCHAR(1000));
Розклад CREATE TALE dc_schedule(
id NUMBER(10) PRIMARY KEY,
evdate DATE NOT NULL,
weektype NUMBER(1) NOT NULL
CHECK(weektype BETWEEN 0 AND 2),
lessonnum NUMBER(2) NOT NULL
CHECK(lessonnum BETWEEN 0 AND 10),
lessontype VARCHAR(2) NOT NULL CHECK(lessontype IN (‘lc’,’lb’,’pr’)),
course_id VARCHAR(50) NOT NULL REFERENCES rf_course (id),
group_id VARCHAR(50) NOT NULL REFERENCES rf_group(id),
recnote VARCHAR(1000));
Заняття CREATE TALE dc_lesson(
id NUMBER(10) PRIMARY KEY,
evdate DATE NOT NULL,
lessonnum NUMBER(2) NOT NULL
CHECK(lessonnum BETWEEN 0 AND 10),
course_id VARCHAR(50) NOT NULL REFERENCES rf_course (id),
group_id VARCHAR(50) NOT NULL REFERENCES rf_group(id),
recnote VARCHAR(1000));
Сутність Microsoft Access
Предмет CREATE TABLE rf_course (
id CHARACTER(50) CONSTRAINT rf_course_pk PRIMARY KEY,
name CHARACTER(200) NOT NULL,
recnote TEXT);
Тема CREATE TABLE rf_theme(
id CHARACTER(50) CONSTRAINT rf_theme_pk PRIMARY KEY,
course_id CHARACTER(50) NOT NULL CONSTRAINT rf_theme_course_fk REFERENCES rf_course (id),
lessontype CHARACTER(2) NOT NULL,
name CHARACTER(200) NOT NULL,
recnote TEXT);
Група CREATE TABLE rf_group (
id CHARACTER(50) CONSTRAINT rf_group_pk PRIMARY KEY,
name CHARACTER(200) NOT NULL,
recnote TEXT);
Розклад CREATE TALE dc_schedule(
id INTEGER CONSTRAINT dc_schedule_pk PRIMARY KEY,
evdate DATE NOT NULL,
weektype NUMBER(1) NOT NULL
CHECK(weektype BETWEEN 0 AND 2),
lessonnum NUMBER(2) NOT NULL,
lessontype CHARACTER(2) NOT NULL,
course_id CHARACTER(50) NOT NULL CONSTRAINT dc_schedule_course_fk REFERENCES rf_course (id),
group_id CHARACTER(50) NOT NULL CONSTRAINT dc_schedule_group_fk REFERENCES rf_group(id),
recnote TEXT);
Заняття CREATE TALE dc_lesson(
id INTEGER CONSTRAINT dc_schedule_pk PRIMARY KEY,
evdate DATE NOT NULL,
lessonnum NUMBER(2) NOT NULL,
course_id CHARACTER(50) NOT NULL CONSTRAINT dc_schedule_course_fk REFERENCES rf_course (id),
group_id CHARACTER(50) NOT NULL CONSTRAINT dc_schedule_group_fk REFERENCES rf_group(id),
recnote TEXT);

Для виконання запиту на створення таблиці в MS Access слід скористатися закладкою Queries вікна бази даних (рис. 2).
Закладка Queries вікна бази даних
Рис. 2. Закладка Queries вікна бази даних.
За допомогою кнопки New створюється новий запит (в діалоговому вікні, яке при цьому висвітлюється, слід вибирати Design View). При створенні нового запиту MS Access пропонує скористатися графічним конструктором запиту. Для переходу в режим SQL необхідно скористатися пунктом меню View/SQL View (Вигляд/Вигляд SQL). Наприклад, MS Access дозволяє варіант запиту як показано на рис. 3.
Запит на створення нової таблиці
Рис. 3. Запит на створення нової таблиці.
Для виконання запиту на створення таблиці необхідно скористатися пунктом меню Query/Run (Запит/Виконати). В результаті створюється відповідна таблиця в базі даних, яка відображається в закладці Tables вікна бази даних.