Лабораторная работа № 11. Создание и изменение объектов базы данных средствами SQL

Цель работы: научиться создавать и изменять средствами SQL таблицы и индексы, задавать ограничения целостности.

Под созданием и изменением здесь подразумевается только создание и изменение структуры и параметров таблиц, а не хранящихся в них данных. Часть языка SQL, служащая для решения этих задач, называется языком описания данных (Date Definition Language – DDL).

1.Создание таблицы. Для создания таблицы в SQL служит команда CREATE TABLE

Синтаксис простейшего варианта команды CREATE TABLE:

CREATE TABLE <Имя таблицы >
(<имя поля > <тип данных>[(<размер>)],
<имя поля > <тип данных>[(<размер>]...);

В стандартном языке SQL применяются следующие типы данных:

INTEGER	До 10 цифр и знак
SMALL	До 5 цифр и знак
DECIMAL(p,q)	0<p<16 всего позиций, q – цифр после запятой
FLOAT	Вещественное, определяется СУБД (REAL в ACCESS)
DOUBLE PRECISION	Вещественное, определяется СУБД, точность и диапазон больше, чем у FLOAT
CHAR(n)	Строка из n (n<256) символов

Практически во всех СУБД, поддерживающих SQL,применяются дополнительно следующие типы данных:

VARCHAR(n)	Строка из n символов (nmax >4096 определяется СУБД)
DATE	Формат определяется специальной командой (по умолчанию mm/dd/yy)
TIME	Формат определяется специальной командой (по умолчанию hh.mm.ss)
DATETIME	Комбинация даты и времени
MONEY	Денежный

 

Подробнее о типах данных, поддерживаемых СУБД ACCESS, смотрите в справке ACCESS в ответе на вопрос «Типы данных SQL».

Пример команды на создание таблицы «Страна» (название, площадь, численность населения в млн чел.):

CREATE TABLE Страна
(название CHAR(60),
площадь REAL,
население REAL);

Самостоятельно с помощью команды CREATE TABLE создайте таблицу «Изделие» со следующими атрибутами:

· название

· цена

· вес

· дата изготовления

· фирма-производитель

Подберите соответствующие типы данных.

Создание запросов на SQL в Access начинается вызовом конструктора запросов. Для этого в окне базы данных нужно выбрать объект «Запросы», пункт меню «Создать» и в окне «Новый запрос» пункт «Конструктор». Далее, не выбирая таблицу, закройте окно «Добавление таблицы» и перейдите в режим SQL. Переход в режим SQL: меню Access ® Вид ® Режим SQL.

Набрав в окне SQL запрос на создание таблицы «Изделие», выполните его.

Введите в созданную таблицу данные о трёх произвольных товарах. В режиме конструктора таблиц проверьте, как интерпретировал типы данных ACCESS.

 

2.Внесение изменений в структуру таблицы. Команда ALTER TABLE.

Синтаксис:

ALTER TABLE < имя таблицы> {ADD {COLUMN < имя поля> < тип поля> [(< размер>)] [NOT NULL] [CONSTRAINT <имя индекса> ] |
ALTER COLUMN <имя поля> < тип поля> [(< размер>)]|
CONSTRAINT < описание составного индекса> } |
DROP {COLUMN <имя поля> | CONSTRAINT < имя индекса> } }

Команда, с помощью которой к таблице «Страна» добавляется поле «столица» выглядит так:

ALTER TABLE Страна
ADD COLUMN столица VARCHAR(40) NOT NULL UNIQUE;

На поле «столица» наложены 2 ограничения: не допускается пустое поле (NOT NULL) и название столицы должно быть уникальным (UNIQUE).

 

Для добавления к таблице «Страна» поля, являющегося первичным ключом, служит команда

ALTER TABLE Страна

ADD COLUMN Id_strana INTEGER NOT NULL PRIMARY KEY;

В поле Id_strana должен храниться номер записи. Приведённая выше команда не создаёт автоматического счётчика.

Самостоятельно с помощью команд ALTER TABLE добавьте к таблице «Изделие» следующие поля:

· Id_izdelie – номер записи и первичный ключ;

· сорт - сорт (1, 2, …), не допускается пустых полей.

 

3.Создание таблицы с ограничениями столбцов и ограничениями таблицы.

В качестве примера создадим таблицу «отдых» связанную с таблицей «страна». Тип связи «многие к одному». Таблица «отдых» будет иметь следующие поля:

· Id_otd – номер записи (первичный ключ),

· Id_st - внешний ключ, связывающий с таблицей «Страна»,

· курорт – название курорта,

· гостиница

· продолжительность – продолжительность отдыха в днях.

Совокупность значений полей «Id_st», «курорт» и «гостиница» должна быть уникальной, то есть, потенциальным ключом. Таким образом в таблице «курорт» будет 2 ключа. Описанная таблица создаётся следующей командой:

CREATE TABLE отдых
(Id_otd INTEGER NOT NULL PRIMARY KEY,
Id_st INTEGER REFERENCES Страна(Id_strana),
курорт CHAR(80),
гостиница CHAR(60),
стоимость REAL,
продолжительность SMALLINT,
UNIQUE (Id_st,курорт,гостиница)
)

Самостоятельно с помощью команд CREATE TABLE создайте таблицу «поставка», связанную с таблицей «Изделие».

Таблица «Поставка» должна иметь следующие поля:

· номер записи (первичный ключ),

· внешний ключ, связывающий с таблицей «Изделие»;

· адрес клиента,

· дату поставки,

· количество товара,

· стоимость доставки.

Совокупность значений внешнего ключа, адреса клиента и даты поставки должна быть уникальной, то есть, потенциальным ключом.

После создания таблицы «Поставка» введите в неё несколько записей. Для того, чтобы убедиться в правильной работе потенциального ключа, попытайтесь ввести две записи с одинаковыми значениями внешнего ключа, адреса клиента и даты поставки.

Добавьте таблицы «Изделие» и «Поставка» к схеме данных (Меню ACCESS ® Схема данных). ACCESS должен автоматически обнаружить связь «один ко многим», заданную при создании таблиц командой CREATE TABLE.

 

4.Создание индекса. Если таблица велика (обычно большой считается таблица, содержащая сотни тысяч записей), то для ускорения поиска в ней данных строятся индексы. Синтаксис команды для создания индекса следующий:

CREATE UNIQUE INDEX <имя индекса>

ON <имя таблицы> <(список полей)>

Для построения индекса по полю «название» в таблице «Страна» служит следующая команда:

CREATE [UNIQUE] INDEX названиеIn

ON страна (название)

Самостоятельно постройте уникальный индекс для поля «Название» таблицы «Изделие». Проверьте его действие, введя две записи с одним наименованием изделия.