Добавил:

Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.

Вуз:

Хмельницкий национальный университет

Предмет:

[НЕСОРТИРОВАННОЕ]

Файл:

Lab3.doc

Скачиваний:

Добавлен:

19.08.2019

Размер:

251.39 Кб

Скачать

☆

<<< < Предыдущая 1 23 / 33

3. Создание таблиц и индексов

В стандарте SQL дано несколько вариантов определения оператора создания таблицы, однако его базовый формат имеет следующий вид:

CREATE TABLE [database.[owner].]table_name

(

{col_name column_properties [constraint]}

{next_col_name …}

)

где

column_properties = datatype [NULL | NOT NULL | IDENTITY[(seed, increment)]]

database.[owner].

Определяет владельца таблицы. Обычно владельцем таблицы является тот, кто ее создал.

datatype

Определяет тип создаваемой колонки - как системный, так и определяемый пользователем. Возможные типы данных приведены в таблице 1.

В определении столбца параметр IDENTITY указывает, что соответствующий столбец будет столбцом-счетчиком. Для таблицы может быть определен только один столбец с таким свойством. Можно дополнительно указать начальное значение и шаг приращения. Если эти значения не указываются, то по умолчанию они оба равны 1.

Для каждого столбца таблицы можно установить ограничение NOT NULL, запрещающее ввод в этот столбец нулевого значения.

constraint

Может включать в себя ограничение как для столбца, так и для всей таблицы.

Обеспечение целостности базы данных

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

Существует 4 группы правил целостности:

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

Целостность данных может быть обеспечена двумя способами.

Декларативный способ. Критерии, которым должны удовлетворять данные, задаются при определении объекта и являются частью определения базы данных.

Преимущества: контроль целостности выполняется автоматически MS SQL Server, такой способ полностью совместим со стандартом ISO SQL:2008.

Процедурный способ. Критерии описываются в пакетах операторов, выполнение которых и определяет целостность данных.

Преимущества: реализуется как на клиенте, так и на сервере с помощью различных программных средств.

Декларативная целостность

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

PRIMARY KEY. Определение поля или группы полей в качестве первичного ключа позволяет уникально идентифицировать каждую запись таблицы, т.к. в этом случае недопустимы повторяющиеся и неопределенные (NULL) значения.
FOREIGN KEY. Позволяет устанавливать связь между полями, содержащими идентичные данные. Данные в этом поле могут принимать значения, определенные в соответствующем первичном ключе, либо значения NULL.
UNIQUE. Определяет уникальность данных в некотором поле таблицы, причем данное ограничение допускает значения NULL. В одной таблице разрешено применение несколько таких ограничений, поля с таким ограничением могут быть использованы в качестве внешних ключей.
DEFAULT. Указывает значение поля по умолчанию, если значение явно не указано при вставке данных. Для поля можно применить только одно такое ограничение.
CHECK. Накладывает ограничение на значение поля в виде логического выражения, что позволяет определить диапазон допустимых значений поля. Причем данное ограничение позволяет ссылаться на значения других полей.

Ссылочная целостность

Внешние ключи представляют собой столбцы или наборы столбцов, предназначенные для связывания каждой из строк дочерней таблицы, содержащей этот внешний ключ, со строкой родительской таблицы, содержащей соответствующее значение потенциального ключа. Стандарт SQL предусматривает механизм определения внешних ключей с помощью предложения FOREIGN KEY, а фраза REFERENCES определяет имя родительской таблицы, т.е. таблицы, где находится соответствующий потенциальный ключ. При использовании этого предложения система отклонит выполнение любых операторов INSERT или UPDATE, с помощью которых будет предпринята попытка создать в дочерней таблице значение внешнего ключа, не соответствующее одному из уже существующих значений потенциального ключа родительской таблицы. Когда действия системы выполняются при поступлении операторов UPDATE и DELETE, содержащих попытку обновить или удалить значение потенциального ключа в родительской таблице, которому соответствует одна или более строк дочерней таблицы, то они зависят от правил поддержки ссылочной целостности, указанных во фразах ON UPDATE и ON DELETE предложения FOREIGN KEY. Если пользователь предпринимает попытку удалить из родительской таблицы строку, на которую ссылается одна или более строк дочерней таблицы, язык SQL предоставляет следующие возможности:

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

SET NULL - выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы записывается значение NULL;

SET DEFAULT - выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы заносится значение, принимаемое по умолчанию;

NO ACTION - операция удаления строки из родительской таблицы отменяется. Именно это значение используется по умолчанию в тех случаях, когда в описании внешнего ключа фраза ON DELETE опущена.

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

Все эти ограничения могут находиться в одном операторе CREATE TABLE. Синтаксис этих ограничений таков:

PRIMARY KEY :

[CONSTRAINT constraint_name]

PRIMARY KEY (col_name [, col_name2 [..., col_name16]])

UNIQUE:

[CONSTRAINT constraint_name]

UNIQUE (col_name [, col_name2 [..., col_name16]])

FOREIGN KEY:

[CONSTRAINT constraint_name]

[FOREIGN KEY (col_name)] REFERENCES ref_table (ref_col)]

NOT NULL (непустое_поле1[, непустое_поле2 [, ...]]) |

DEFAULT:

[CONSTRAINT constraint_name]

DEFAULT {constant_expression | niladic-function | NULL}

[FOR col_name]

CHECK :.

[CONSTRAINT constraint_name]

CHECK [NOT FOR REPLICATION] (expression)

Для получения информации об используемых ограничениях указанной таблицы предназначена специальная хранимая процедура: sp_helpconstraint ИмяТаблицы

Примеры:

1) создание таблицы Debts без определения связей и индексов:

CREATE TABLE Debts

(DebtID INT IDENTITY(1,1) PRIMARY KEY,

ReaderID INT NOT NULL,

BookID INT NOT NULL,

DebtDate datetime NOT NULL DEFAULT GETDATE(),

DebtPeriod INT NOT NULL DEFAULT 14,

DebtNotes TEXT)

2) создание таблицы Debts с указанием связей и ограничений

CREATE TABLE Debts

(DebtID INT IDENTITY(1,1) NOT NULL,

ReaderID INT NOT NULL,

BookID INT NOT NULL,

DebtDate datetime NOT NULL CONSTRAINT DebtDate_Default

DEFAULT GETDATE(),

DebtPeriod INT NOT NULL CONSTRAINT DebtPeriod_Default

DEFAULT 14,

DebtDeadline AS DATEADD(DAY,DebtPeriod,DebtDate),

DebtNotes TEXT,

CONSTRAINT Debts_PK PRIMARY KEY (DebtID),

CONSTRAINT Debts_Readers_FK1 FOREIGN KEY (ReaderID)

REFERENCES Readers(ReaderID),

CONSTRAINT Debts_Books_FK1 FOREIGN KEY (BookID)

REFERENCES Books(BookID))

Изменение таблицы

Для внесения изменений в уже созданные таблицы стандартом SQL предусмотрен оператор ALTER TABLE, предназначенный для выполнения следующих действий:

добавление в таблицу нового столбца;
удаление столбца из таблицы;
добавление в определение таблицы нового ограничения;
удаление из определения таблицы существующего ограничения;
задание для столбца значения по умолчанию;
отмена для столбца значения по умолчанию.

ALTER TABLE [database.[owner].]table_name

[ADD

{col_name column_properties column_constraints]}

[,{next_col_name|next_table_constraint}]...]

| DROP [CONSTRAINT]

constraint_name[,constraint_name]

Примеры:

Добавление PRIMARY KEY CONSTRAINT:

ALTER TABLE authors

ADD

CONSTRAINT UPKCL_auidind PRIMARY KEY (au_id)

Добавление FOREIGN KEY CONSTRAINT:

ALTER TABLE titles

ADD

CONSTRAINT FK_pub_id FOREIGN KEY (pub_id) REFERENCES publishers(pub_id)

Добавление UNIQUE CONSTRAINT:

ALTER TABLE titles

ADD

CONSTRAINT UNC_name_city UNIQUE (stor_name,city)

Добавление DEFAULT CONSTRAINT:

ALTER TABLE authors

ADD

DEFAULT 'UNKNOWN' FOR phone

Добавление CHECK CONSTRAINT:

ALTER TABLE authors

ADD

CONSTRAINT CK_zip CHECK (zip LIKE '[0-9][0-9][0-9][0-9][0-9]'

Добавление новой колонки

ALTER TABLE publishers

ADD

country varchar(30) NULL DEFAULT('USA')

Удаление таблицы

Удаление таблицы выполняется командой:

DROP TABLE имя_таблицы

Удалить можно любую таблицу, даже системную. К этому вопросу нужно подходить очень осторожно. Однако удалению не подлежат таблицы, если существуют объекты, ссылающиеся на них. К таким объектам относятся таблицы, связанные с удаляемой таблицей посредством внешнего ключа. Поэтому, прежде чем удалять родительскую таблицу, необходимо удалить либо ограничение внешнего ключа, либо дочерние таблицы. Если с таблицей связано хотя бы одно представление, то таблицу также удалить не удастся. Кроме того, связь с таблицей может быть установлена со стороны функций и процедур. Следовательно, перед удалением таблицы необходимо удалить все объекты базы данных, которые на нее ссылаются, либо изменить их таким образом, чтобы ссылок на удаляемую таблицу не было.

Например:

DROP TABLE titles1

Индексы

Индекс сервера MS SQL SERVER - это объект схемы, который может ускорить выборку строк за счет использования указателя. Индексы могут создаваться явно или неявно (автоматически). Если индекс по нужному столбцу отсутствует, поиск осуществляется по всей таблице.

Индекс обеспечивает прямой и быстрый доступ к строкам таблицы. Его назначение - уменьшить количество операций ввода/вывода с диском за счет использования индексированного пути для быстрого поиска данных. Индекс автоматически используется и сопровождается сервером MS SQL SERVER. После создания индекса никакого другого вмешательства пользователя не требуется.

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

Примечание: При удалении таблицы командой DROP удаляются и индексы.

Имеется два типа индексов.

Уникальный индекс. Сервер MS SQL SERVER создает такой индекс автоматически, когда вы задаете для столбца таблицы ограничение PRIMARY KEY или UNIQUE. Имя индекса - это имя. присвоенное ограничению.
Неуникальный индекс. Этот тип индекса может создать пользователь. Например, для повышения производительности выборки можно создать индекс по столбцу внешнего ключа (FOREIGN KEY) для соединения в запросе.

Создание индекса.

Индекс по одному или нескольким столбцам создается с помощью команды CREATE INDEX.

Синтаксис команды CREATE INDEX:

CREATE INDEX index

ON table (column [ , column] - . .) ;

где:

index – имя индекса.
table – имя таблицы, на которую создается индекс.
column – имя столбца таблицы, по которому создается индекс

Например:

CREATE INDEX idx_department ON dbo.department (dnumber)

Большое количество индексов для таблицы еще не означает, что производительность запросов возрастет. Каждая DML операция, выполняемая над таблицей с индексами, требует обновления индекса. Чем больше индексов связано с таблицей, тем больше усилий требуется от сервера на обновление всех индексов после операции DML.

Когда создавать индекс:

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

Когда не создавать индекс:

Таблица небольшого размера.
Столбцы редко используются в качестве условия запроса.
Предполагается, что большинство запросов выбирают более 2-4% строк.
Таблица часто обновляется. Если у таблицы имеется один или несколько индексов, то команды DML применяемые к строкам таблицы, занимают сравнительно больше времени из-за сопровождения индексов.

Удаление индекса.

Модифицировать индексы невозможно. Единственный способ изменить индекс - это удалить его и создать заново. Определение индекса удаляется из словаря данных с помощью команды DROP INDEX. Чтобы удалить индекс, необходимо быть его владельцем или иметь привилегию DROP ANY INDEX.

Синтаксис команды DROP SEQUENCE:

DROP INDEX idx_department;

Примеры команд для работы с индексами.

CREATE INDEX emp_ename_idx ON EMPLOYEE (last_name);

В примере создается индекс EMP_ENAME_IDX, для увеличения скорости доступа к столбцу last_name таблицы EMPLOYEE.

Удаление индекса EMP_ENAME_IDX.

DROP INDEX emp_ename_idx;

Задание на лабораторную работу

Изучить ER-диаграмму, приведенную в вашем варианте индивидуального задания, при необходимости - модифицировать ее.
Заданная ER-диаграмма дает лишь ориентировочное представление о структуре данных заданной Вам предметной области, Вы можете модифицировать ее (но не в сторону упрощения!), добавив новые сущности, атрибуты, связи..
Конвертировать ER-диаграмму в концептуальную схему, отображаемую на реляционные таблицы, и нормализовать таблицы до формы не ниже Нормальной Формы Бойса-Кодда.
Составить SQL-скрипт для создания таблиц базы данных, включив в него все требуемые логикой предметной области декларативные ограничения целостности (первичные и внешние ключи, проверочные ограничения и т.п.)

Варианты заданий.

Вариант №1 Предметная область: Библиотека

Вариант №2 Предметная область: Университет

Примечание: Циклы дисциплин: гуманитарный, общеинженерный, математический, компьютерный и т.д.

Вариант №3 Предметная область: Оптовая торговля

Вариант №4 Предметная область: Производство

Вариант №5 Предметная область: Предпринимательство

Примечание: профиль - продуктовый, галантерейный, канцелярский и т.п.

Вариант №6 Предметная область: Автомастерская

Вариант №7 Предметная область: Обучение

Категория дисциплины - гуманитарная, математическая, компьютерная, общеинженерная и т.д. Вид контроля - зачет, экзамен.

Вариант №8 Предметная область: Управление проектами

Вариант №9 Предметная область: Поликлиника

Текущее состояние - лечится, вылечился, направлен в стационар, умер. Социальный статус пациента - учащийся, работающий, врем. неработающий, инвалид, пенсионер Специализация врача - терапевт, невропатолог и т.п. Квалификация врача - 1-я, 2-я, 3-я категория.