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

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
СРС 5_2
Что такое подзапрос?
Составить команду SQL, которая выводит список кадровых агентов, проживающих в том же штате, что и внешний кандидат Barbara Johnson.
Указать, истинно выражение или ложно.
Список столбца команды SELECT подзапроса, введенного с оператором сравнения, может включать только один столбец.
Что такое связанный подзапрос?
Сколько максимально возможных столбцов в таблице?

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Создание таблицы
Формулировка задачи:
Требуется сохранить подробности о газете, публикующей объявления для Tebisco, Inc., в базу Recruitment (прием на работу).
Код газеты и ее название не могут быть не указаны.

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Решение
1. Составить команду создания таблицы.
2. Создать таблицу в базе.
3. Проверить, создана ли эта таблица.
4. Вставить строку в таблицу.

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Составить команду создания таблицы
Таблицы: Таблица – это объект базы данных для хранения данных
Данные в таблице организованы в строки и столбцы
Каждая строка в таблице представляет уникальную запись, а каждый столбец – атрибут этой записи
Команда CREATE TABLE применяется для создания таблицы
Синтаксис
CREATE TABLE имя_таблицы
(имя_столбца типДанных [NULL | NOT NULL]
[IDENTITY (SEED,INCREMENT)],
имя_столбца типДанных …)
[ON {группаФайлов} | DEFAULT]
Результат
Newspaper – имя создаваемой таблицы

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Создать таблицу в базе
Действие
В окне Query Analyzer (Анализатор запроса) ввести данную команду CREATE TABLE
CREATE TABLE NewsPaper
(cNewsPaperCode char(4) NOT NULL,
cNewsPaperName char(20) NOT NULL,
vRegion varchar(20),
vTypeOfNewsPaper varchar(20),
vContactPerson varchar(35),
vHOAddress varchar(35),
cCity char(20),
cState char(20),
cZip char(10),
cCountryCode char(3),
cFax char(15),
cPhone char(15))
Нажать F5 для выполнения команды

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Проверить, создана ли эта таблица
Для просмотра структуры таблицы можно использовать команду sp_help
Синтаксис
sp_help имя_таблицы
Действие
В окне Query Analyzer набрать:
sp_help Newspaper
Нажать F5 для выполнения

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Вставить строку в таблицу
Команда INSERT
После того, как создана структура таблицы, в нее можно вставлять данные. Для этого применяется команда INSERT
Синтаксис
INSERT [INTO] имя_таблицы [список_столбцов]
VALUES (список_значений)

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Вставить строку в таблицу (продолжение)
Действие
В окне Query Analyzer (Анализатор запроса) ввести команду INSERT:
INSERT Newspaper
VALUES('0001', 'Texas Times', 'Texas', 'General', 'Jackson Demello',
'4723 West Alabama', 'Houston', 'Texas', '77015-4568', '001', '[phone]', '[phone]')
Нажать F5 для выполнения

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Практика-Создание таблицы
Формулировка задачи
Требуется сохранить подробности о университетах, к которым обратилась Tebisco, Inc. для приема на работу. Пример строки:
Код университета и его название не могут быть не указаны.

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Демонстрация-Удаление таблицы
Формулировка задачи
Удалить таблицу Newspaper из базы.

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Решение
1. Составить оператор удаления таблицы из базы.
2. Удалить таблицу Newspaper из базы.
3. Проверить, что таблица удалена из базы.

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Составить команду удаления таблицы из базы.
Команда DROP TABLE
удаляет таблицу Newspaper из базы
Синтаксис
DROP TABLE имя_таблицы
Результат
Команда удаления таблицы Newspaper :
DROP TABLE Newspaper

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Удалить таблицу Newspaper из базы
Действие
В окне Query Analyzer набрать:
DROP TABLE Newspaper
Нажать F5 для выполнения

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Проверить, что таблица удалена из базы
Для просмотра структуры таблицы набрать следующую команду:
sp_help Newspaper
Действие
Для просмотра структуры таблицы набрать следующую команду:
В окне Query Analyzer набрать:
sp_help Newspaper
Нажать F5 для выполнения

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Типы данных, определенные пользователем
Создание типа данных, определенного пользователем
Тип данных, определенный пользователем, создается процедурой sp_addtype , записанной в системе
Синтаксис
sp_addtype имя, [системный_тип_данных] [,'тип_NULL']
Игнорирование типа данных, определенного пользователем
Тип данных, определенный пользователем, игнорируется процедурой sp_droptype, записанной в системе
Синтаксис
sp_droptype type

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Демонстрация-Создание типов данных,
определенных пользователем
Формулировка задачи:
Таблицы NewsAd и Newspaper - неодинакового типа для атрибута cNewspaperCode. Создать тип данных typNewspaperCode, определенный пользователем, который может применяться для создания таблиц NewsAd и Newspaper. Создать табл. NewsAd, в которой код газеты типа typNewspaperCode.

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Решение
1. Определить несовместимость структур таблиц
2. Определить тип данных, определенный системой, который может преобразовываться в тип, определенный пользователем
3. Определить имя типа, определенного пользователем
4. Создать тип данных, определенный пользователем
5. Проверить, создан ли этот тип данных
6. Создать таблицу NewsAd с новым типом данных

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Определить несовместимость структур
таблиц
Типы данных, определенные пользователем
могут применяться для устранения несовместимости структур таблиц при использовании разных системных типов двумя атрибутами, которые должны иметь одинаковый системный тип.
Результат
Атрибут cNewspaperCode в табл. Newspaper (Газета) является типом char(4). Атрибут cNewspaperCode в табл. NewsAd является типом varchar(2). Тип и длина обоих атрибутов различны. Это увеличивает несовместимость структуры таблицы.

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Определить тип данных, определенный системой, который может переводиться в тип данных, определенный пользователем
Результат
Желательно, чтобы тип обоих атрибутов, определенный системой, был char(4).

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Определить имя типа, определенного
пользователем
Имя типа может иметь приставку ‘typ’ для определения типа, определенного пользователем
Результат
Имя типа, определенного пользователем, - typNewspaperCode

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Создать тип данных, определенный пользователем
Действие
В окне Query Analyzer набрать:
sp_addtype typNewspaperCode, 'char(4)'
Выполнить запрос, нажав 'Execute Query'

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Проверить, что таблица создана
Процедура sp_help, записанная в системе, дает особую информацию о заданном объекте
Синтаксис
sp_help datatype_name
Действие
В окне Query Analyzer набрать:
sp_help typNewspaperCode
Нажать F5 для выполнения

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Создать таблицу NewsAd с новым типом
данных
Действие
В окне Query Analyzer набрать:
CREATE TABLE NewsAd
(
cNewsAdNo char(4) NOT NULL,
cNewspaperCode typNewspaperCode NOT NULL,
dAdStartDate datetime,dDeadline datetime
)
Нажать F5 для выполнения

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Целостность данных
Целостность данных обеспечивает совместимость и точность данных, хранящихся в базе. В широком смысле она делится на следующие четыре категории:
Целостность сущностей-объектов
Целостность домена
Целостность ссылочных данных
Целостность, определяемая пользователем
Целостность сущностей-объектов
гарантирует то, что каждая строка может однозначно определяться по атрибуту "первичный ключ"
Целостность домена
гарантирует, что хранить в столбце допускается только действительный диапазон значений

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Целостность данных (продолжение)
Целостность ссылочных данных
гарантирует, что значения внешнего ключа соответствуют значению первичного ключа
Пользовательская целостность
относится к набору правил, задаваемых пользователем и не принадлежащих к категориям Целостности сущностей-объектов, домена и ссылочных данных

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Создание ограничений
Ограничения создаются для обеспечения целостности данных
Ограничения определяют правила для поддержания соответствия и верности данных
Ограничения создаются при создании таблицы или добавляются позже
Ограничения могут вводиться на двух уровнях:
уровень столбца
уровень таблицы

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Создание ограничений (продолжение)
Ограничения создаются для обеспечения целостности данных
Ограничение может создаваться одной из следующих команд:
CREATE TABLE
CREATE TABLE имя_таблицы
имя_столбца CONSTRAINT имя_ограничения тип_ограничения [,CONSTRAINT имя_ограничения тип_ограничения]
команда ALTER TABLE
ALTER TABLE имя_таблицы
[WITH CHECK | WITH NOCHECK]
ADD CONSTRAINT имя_ограничения имя_ограничения

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Создание ограничений (продолжение)
Сброс ограничений
Ограничение можно сбросить командой ALTER TABLE в Query Analyzer (Анализаторе запроса)
Синтаксис
ALTER TABLE имя_таблицы
DROP CONSTRAINT имя_переменной

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Создание ограничений (продолжение)
Типы ограничений
Ограничение PRIMARY KEY
определяется по одному или нескольким столбцам, значения которых однозначно опознают строки в таблице
Обеспечивает целостность сущностей-объектов
Синтаксис
[CONSTRAINT имя_ограничения PRIMARY KEY [CLUSTERED|NONCLUSTERED](имя_столбца [, имя_столбца [,имя_столбца [, …]]])]

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Создание ограничений (продолжение)
Ограничение UNIQUE
накладывает единственность на столбцы непервичного ключа
Можно создать несколько ограничений UNIQUE на таблицу
Синтаксис
[CONSTRAINT имя_ограничения UNIQUE [CLUSTERED | NONCLUSTERED](имя_столбца [, имя_столбца [, имя_столбца [, …]]])

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Создание ограничений (продолжение)
Ограничение FOREIGN KEY
удаляет несоответствие в двух таблицах, если данные в одной таблице зависят от данных в другой
Синтаксис
[CONSTRAINT имя_ограничения FOREIGN KEY (имя_столбца [, имя_столбца [, …]])
REFERENCES имя_таблицы (имя_столбца [,
имя_столбца [, …]])]

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Создание ограничений (продолжение)
Ограничение CHECK
накладывает целостность домена, ограничивая значения, вставляемые в столбец
Синтаксис
[CONSTRAINT имя_ограничения] CHECK (выражение)
Ограничение DEFAULT
Применяется для присвоения постоянного значения столбцу
Для столбца может создаваться только одно ограничение DEFAULT
Этот столбец не может быть столбцом IDENTITY
Синтаксис
[CONSTRAINT имя_ограничения] DEFAULT
(выражение_константа | NULL)

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Демонстрация - Применение ограничений
Формулировка задачи
Таблицы Newspaper и NewsAd завершены.
Создать таблицу Newspaper по следующим
правилам целостности данных:
Атрибут cNewspaperCode должен быть первичным ключом
Атрибут cPhone (телефон) должен быть формата ([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9])
Атрибут cCountrycode (код страны) должен быть ‘001' по умолчанию
Изменить таблицу NewsAd следующим образом:
cNewsAdNo (№ объявления в газете) должен быть первичным ключом
cNewspaperCode (код газеты) должен быть внешним ключом

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Решение
1. Определить способ наложения целостности данных.
2. Составить команду создания таблицы.
3. Создать таблицу с ограничениями.
4. Проверить ограничения, вставляя данные.

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Определить способ наложения целостности данных
Целостность данных налагается ограничениями
Результат
Для таблицы Newspaper (газета):
форму номера телефона можно задать ограничением CHECK
код страны можно задать ограничением DEFAULT
код газеты можно задать первичным ключом, применяя ограничение PRIMARY KEY
Для таблицы NewsAd:
столбец cNewsAdNo можно задать первичным ключом, применяя ограничение PRIMARY KEY
атрибут cNewspaperCode можно задать внешним ключом, применяя ограничение FOREIGN KEY

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Составить команду создания таблицы
Результат
Желательно, чтобы команда создания таблицы Newspaper была следующей:
CREATE TABLE Newspaper
(cNewspaperCode typNewspaperCode CONSTRAINT
pkNewspaperCode PRIMARY KEY,
cNewspaperName char(20) NOT NULL,
vRegion varchar(20),
vTypeOfNewspaper varchar(20),
vContactPerson varchar(35),
vHOAddress varchar(35),
cCity char(20),
cState char(20),
cZip char(10),
cCountryCode char(3) CONSTRAINT defCountryCode
DEFAULT(‘001’),
cFax char(15),

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Составить команду создания аблицы (продолжение)
cPhone char(15) CONSTRAINT chkPhone
CHECK(cPhone
LIKE('([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-
9][0-9][0-9]')))
Желательно, чтобы команды изменения таблицы NewsAd были следующими:
ALTER TABLE NewsAd
ADD CONSTRAINT pkNewsAdNo PRIMARY KEY
(cNewsAdNo)
ALTER TABLE NewsAd
ADD CONSTRAINT fkNewspaperCode
FOREIGN KEY (cNewspaperCode)
REFERENCES Newspaper(cNewspaperCode)

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Создать таблицу с ограничениями
Действие
В окне Query Analyzer набрать запрос
CREATE TABLE Newspaper
(
cNewspaperCode typNewspaperCode CONSTRAINT pkNewspaperCode PRIMARY KEY,
cNewspaperName char(20) NOT NULL,
vRegion varchar(20),
vTypeOfNewspaper varchar(20),
vContactPerson varchar(35),
vHOAddress varchar(35),
cCity char(20),
cState char(20),
cZip char(10),

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Создать таблицу с ограничениями
(продолжение)
Действие
cCountryCode char(3) CONSTRAINT defCountryCode DEFAULT('001'),
cFax char(15),
cPhone char(15) CONSTRAINT chkPhone CHECK(cPhone LIKE('([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'))
)
ALTER TABLE NewsAd
ADD CONSTRAINT pkNewsAdNo PRIMARY KEY (cNewsAdNo)
ALTER TABLE NewsAd
ADD CONSTRAINT fkNewspaperCode FOREIGN KEY (cNewspaperCode) REFERENCES Newspaper(cNewspaperCode)
Выполнить команды, кликнув на Execute Query

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Проверить ограничения, вставляя данные
Проверить ограничение, вставляя данные в табл. Newspaper
Действие

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Проверить ограничения, вставляя
данные (продолжение)
Проверить ограничение, вставляя данные в табл. NewsAd
Действие

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Демонстрация - Применение ограничений
Формулировка задачи
Создать таблицу College по следующим правилам целостности данных:
cCollegeCode (код университета) должен быть первичным ключом
телефон должен быть формата ([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9])
cCity (город) должен быть ‘New Orleans' по умолчанию
Создать таблицу CampusRecruitment так, чтобы она отвечала следующим правилам целостности данных:
столбец cCampusRecruitmentCode должен быть первичным ключом
столбец cCollegeCode должен быть внешним ключом

Создание таблиц и обеспечение целостности данных
Урок 3A / Слайд из 46
Выводы
Из этого урока Вы узнали, что:
Таблица – это объект базы данных для хранения данных
Таблица может быть создана командой CREATE TABLE
Команда INSERT применяется для вставки данных в таблицу
Команда DROP TABLE применяется для удаления таблицы
Тип данных, определенный пользователем, создается пользователем и основан на типе данных системы
Тип данных, определенный пользователем, создается процедурой sp_addtype , записанной в системе
Тип данных, определенный пользователем, сбрасывается процедурой sp_droptype, записанной в системе
sp_help дает информацию об объекте базы данных или типе данных, определяемом пользователем
Целостность данных обеспечивает полность, точность и надежность данных, хранящихся в базе