Мартьянова А.Е. Базы данных и знаний. Учебное пособие

Подождите немного. Документ загружается.

Мартьянова А.Е. Базы данных и знаний 211

Рис. III.13. Диалоговое окно Drop Object

Как правильно выбрать тип данных

Недопустимо называть объекты именами команд или использовать

для этой цели другие зарезервированные слова. Типы данных – это пол-

ноценные объекты базы данных, которые хранятся в системной таблице

systypes вместе с их кодовыми значениями. Для отображения типов дан-

ных и их кодовых значений (в столбце type) можно использовать инст-

рукцию SELECT.

При создании таблиц важно правильно выбрать типы данных для

каждой из колонок. Наверное, вы хотели бы, чтобы позволялось хранить

нужные данные, но не разрешалось хранить недопустимые данные (на-

пример, символьное значение в колонке для числовых данных). Эта за-

бота и решается благодаря типам данных. При выборе типа данных вам

полезно

ответить на два следующих вопроса:

• Подходит ли этот тип данных для вашей разновидности данных?

• Какой тип данных следует использовать – с фиксированной либо с

переменной длиной?

Выбор подходящего типа данных является довольно понятным

процессом. Данные, которые будут вводиться в колонку, должны соот-

ветствовать типу данных, заданному для этой колонки.

Поэтому вам сле-

дует выбрать такой тип данных, который лучше всего охватит диапазон

значений, которые могли бы храниться в колонке для всего срока экс-

плуатации вашего приложения, и в то же время стремясь ограничить не-

Мартьянова А.Е. Базы данных и знаний 212

нужный расход места на диске. Ненужный расход места на диске – это

место на диске, выделенное для элементов данных, хранящихся в колон-

ке, но не используемое. Например, предположим, у вас есть колонка, в

которой нужно хранить одно целое число в диапазоне от 1 до 100. Оче-

видно, что эти значения могут храниться в

типе integer, но каждое целое

число типа integer занимает 4 байта. Данные с типом tinyint могут хра-

нить значения от 0 до 255 и для них нужен только 1 байт места. В данном

случае tinyint будет наилучшим выбором, потому что это экономит место

на диске, необходимое для хранения данных из таблиц.

Затем вы должны определить, какой тип данных следует

использо-

вать – с фиксированной либо с переменной длиной. Если все значения

данных из колонки будут иметь приблизительно одинаковые размеры, то

более эффективным станет применение типа данных с фиксированной

длиной, так как обработка данных, имеющих типы переменной длины,

вызывает повышенную нагрузку. Вообще говоря, типы данных с пере-

менной длиной следует применять,

только если вы предполагаете значи-

тельные различия в длине данных, хранимых в данной колонке, и когда

данные из колонки обновляются редко. К данным с переменной длиной

относятся varchar, nvarchar, varbinary, text, ntext и image. Применение ти-

пов данных с переменной длиной может привести к значительной эко-

номии места для хранения данных. Например, если вы зададите тип

дан-

ных с фиксированной длиной, достаточной для хранения значений в ко-

лонке с наибольшей возможной длиной, то для всех значений, занимаю-

щих меньше места, истратится столько же места, как и для этого самого

объемного значения. Результатом этого станут огромный ненужный рас-

ход места на диске,

потому что лишь немногие строки будут расходовать

все максимально допустимое место для данных. В большинстве строк

будет использовано меньше места, а все неиспользуемое место станет

потерянным. А если вы зададите, что будет примениться тип данных с

переменной длиной, то короткие значения будут расходовать лишь то

место, которое им действительно нужно. Но опять-таки, применение ти-

пов данных с переменной длиной повышает нагрузку на процессор. По-

этому, если вам требуется тип данных с переменной длиной, то приме-

няйте тип данных с фиксированной длиной. Если из соображений расхо-

дования места для хранения данных нужно применять тип данных с

пе-

ременной длиной, то, конечно, применяйте его.

Итак, как же выбрать нужные для ваших таблиц типы данных и их

длины? Следуйте уже принятым ранее основным решениям и делайте

выбор, который наилучшим образом будет соответствовать потребно-

212

Мартьянова А.Е. Базы данных и знаний 213

стям вашего приложения. Вообще говоря, определяясь с размерами ко-

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

потребностях в будущем.

КОНТРОЛЬНЫЕ ВОПРОСЫ к заданию

1. Опишите назначение языка DDL (Data Definition Language).

2. Опишите два аспекта целостности базы данных – на уровне

отдельных объектов и на уровне базы данных в целом

3. Сформулируйте определение транзакции.

4. Опишите назначение журнала транзакций.

5. Перечислите виды конфликтов между транзакциями.

6. Перечислите самые распространенные SQL-серверы.

7. Опишите процесс создания базы данных и журнала транзак-

ций в СУБД SQL Server 2000.

8. Опишите процесс удаления базы данных в СУБД SQL Server

2000.

9. Опишите процесс создания таблиц в СУБД SQL Server 2000.

10. Перечислите

основные предопределенные (системные) типы

данных, используемые в СУБД SQL Server 2000.

11. Как создать пользовательский тип данных в СУБД SQL

Server 2000?

12. Найдите соответствие основных типов данных Access и

СУБД SQL Server 2000 (см. табл. II.1 работы VI). Подготовьте сравни-

тельную характеристику для основных типов данных.

ЗАДАНИЕ на проектирование БД «Учебный процесс» в СУБД

SQL Server 2000

1. Создайте БД и журнал транзакций БД

«Учебный процесс».

2. Создайте по описанию таблицу «ГРУППА» БД «Учебный

процесс» с использованием системных типов данных.

3. Самостоятельно создайте таблицы «СТУДЕНТ»,

«КАФЕДРА», «ПРЕПОДАВАТЕЛЬ», «ПРЕДМЕТ», «УЧЕБНЫЙ ПЛАН»

и «УСПЕВАЕМОСТЬ» БД «Учебный процесс» с использованием соот-

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

Предполагается, что SQL Server уже запущен, и для наших целей

воспользуемся SQL Server Enterprise Manager

(Пуск/

Программы/Microsoft SQL Server/ Enterprise Manager).

Мартьянова А.Е. Базы данных и знаний 214

Работа XII. MS SQL Server. Таблицы. Заполнение БД

Проверьте тип данных всех созданных в предыдущей работе таб-

лиц: «СТУДЕНТ», «КАФЕДРА», «ПРЕПОДАВАТЕЛЬ», «ПРЕДМЕТ»,

«УЧЕБНЫЙ ПЛАН» и «УСПЕВАЕМОСТЬ» БД «Учебный процесс» (см.

табл. III.3 – III.9). Установите правильно значение «Allow Nulls» на поля

таблиц.

Ниже будет показано, как установить ключи, индексы и провероч-

ные условия на значения соответствующих полей таблиц.

Таблица III.3. Описание свойств полей таблицы ГРУППА

Name Field Set

Primary

Key

Data

Type

Length Allow

Nulls

Indexes Check

Constraints

Код группы Да smallint 2 Нет Да

Обозначе-

ние группы

varchar 8 Нет

Количество

студентов

tinyint 1 Да >=0 And

<=35

Средний

балл в

группе при

поступле-

нии

real 4 Да >2 And <5

Or 0

Таблица III.4. Описание свойств полей таблицы СТУДЕНТ

Name Field Set

Primary

Key

Data

Type

Length Indexes Allow Nulls

Код студента Да int 4 Да Нет

Код группы Foreign smallint 2 Нет

Номер зачетной

книжки

varchar 10 Нет

Фамилия varchar 15 Нет

Имя varchar 15 Да

Отчество varchar 15 Да

Год рождения smallint 2 Да

Адрес varchar 25 Да

Домашний те-

лефон

varchar 10 Да

Балл при посту-

плении

real 4 Да

214

Мартьянова А.Е. Базы данных и знаний 215

Таблица III.5. Описание свойств полей таблицы КАФЕДРА

Name Field Set

Primary

Key

Data

Type

Length Indexes Allow Nulls

Код кафедры Да smallint 2 Да Нет

Название

кафедры

varchar 20 Нет

Телефон varchar 10 Да

Заведующий varchar 15 Да

Фото image 16 Да

Таблица III.6. Описание свойств полей таблицы ПРЕПОДАВАТЕЛЬ

Name Field Set

Primary

Key

Data

Type

Length Indexes Allow Nulls

Код

преподавателя

Да smallint 2 Да Нет

Код кафедры Foreign smallint 2 Нет

Табельный

номер

varchar 10 Нет

Фамилия varchar 15 Нет

Имя varchar 15 Да

Отчество varchar 15 Да

Домашний

адрес

varchar 25 Да

Домашний

телефон

varchar 10 Да

Должность varchar 15 Да

Ученая степень varchar 15 Да

Ученое звание varchar 10 Да

Таблица III.7. Описание свойств полей таблицы ПРЕДМЕТ

Name Field Set

Primary

Key

Data

Type

Length Indexes Allow Nulls

Код предмета Да smallint 2 Да Нет

Название

предмета

varchar 20 Нет

Программа text 16 Да

Мартьянова А.Е. Базы данных и знаний 216

Таблица III.8. Описание свойств полей таблицы УЧЕБНЫЙ ПЛАН

Name

Field

Set Primary

Key

Data

Type

Length Allow

Nulls

Indexes Check

Constraints

Код

учебно-

го

плана

Да smallint 2 Нет Да

Код

препо-

давате-

ля

Foreign smallint 2 Нет

Код

предме-

та

Foreign smallint 2 Нет

Часы smallint 2 Да >0 And

<=300

Вид за-

нятия

varchar 10 Да

Се-

местр

tinyint 1 Да

Вид

сдачи

varchar 10 Да

Таблица III.9. Описание свойств полей таблицы УСПЕВАЕМОСТЬ

Name

Field

Set Primary

Key

Data

Type

Lengt

Allow

Nulls

Indexes Check

Con-

straints

Код

студен-

та

Fo-

reign

int 4 Нет

Код

учебно-

го пла-

на

Fo-

reign

Да

smallint 2 Нет

Да

Оценка tinyint 1 Да >=0 And

<=5

Дата

сдачи

small-

datetime

4 Да

216

Мартьянова А.Е. Базы данных и знаний 217

Создание и использование условий на значения столбцов

Условия на значения столбцов (constraints) определяются для

обеспечения целостности данных в таблицах и отдельных столбцах.

Инструкция CREATE

TABLE дает возможность создавать первичные и

внешние ключи, определять уникальные столбцы и строки, а также

указывать проверочные (check) и устанавливаемые по умолчанию

(default) условия на значение столбца.

Условие на значение столбца PRIMARY KEY

Условие на значение столбца PRIMARY

KEY (первичный ключ)

используется для обеспечения уникальности данных в заданных столб-

цах и в целом для обеспечения ссылочной целостности.

Определение условия PRIMARY

KEY для таблицы устанавливает

определенные условия на значение первичного ключа, что гарантирует

уникальность всех строк таблицы. Это делается путем запрещения ввода

одинаковых значений в один столбец (или в несколько столбцов).

Кроме того, условие на значение первичного ключа запрещает ввод

значений NULL

в столбец или столбцы, для которых оно определено.

PRIMARY

KEY создает для столбца, указанного в условии, уникальный

индекс, позволяющий реализовать более быстрое считывание строк в

таблице.

Определение условия PRIMARY

KEY в одной таблице само по

себе не обеспечивает ссылочной целостности. Необходимо также

определить соответствующие внешние ключи для других таблиц, строки

которых будут комбинироваться со строками таблицы, где определено

условие PRIMARY

KEY.

Установить это условие очень просто – выберите нужный столбец

и нажмите кнопку Set primary key (Установить первичный ключ) на

панели инструментов окна проектирования таблицы. В результате этих

действий в крайнем левом столбце появится значок «ключа»,

показывающий, что для выбранного вами столбца «Код группы»

определено условие PRIMARY

KEY (рис. III.14).

Мартьянова А.Е. Базы данных и знаний 218

Рис. III.14. Определение условия PRIMARY KEY для одного столбца таблицы

В случае, когда необходимо определить условие PRIMARY KEY

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

«УСПЕВАЕМОСТЬ», последовательность действий будет иной:

1. Нажмите кнопку Manage Indexes/Keys (Управление

индексами/ключами) на панели инструментов окна Design Table

имя_таблицы, в результате чего на экране появится диалоговое окно

Properties (Свойства), в котором необходимо выбрать вкладку

Indexes/Keys (Индексы/Ключи), представленную на рис. III.15.

2. Теперь в полях Column Name (

Имя столбца) выберите имена тех

столбцов, которые вы хотите включить в первичный ключ. После закры-

тия этого диалогового окна условие на значение PRIMARY KEY будет

установлено для всех заданных столбцов.

Обратите внимание на флажок Create as CLUSTERED, который

позволяет определять тип индекса – кластеризованный или некластери-

зованный MS SQL Server.

Кластеризованный индекс таблицы (или представления) – в кото-

ром логический

порядок значений ключа совпадает с физическим поряд-

ком хранения данных в таблице (или представлении). Иными словами,

кластеризованный индекс определяет физический порядок, в котором

данные хранятся в таблице (или в представлении) [8].

218

Мартьянова А.Е. Базы данных и знаний 219

Рис. III.15. Вкладка Indexes/Keys диалогового окна Properties

Некластеризованный индекс – в уотором столбцы хранятся в по-

рядке, отличном от физического порядка данных (противоположен кла-

стеризованному индексу).

Определение условия PRIMARY KEY может быть осуществлено и

через кнопку Set primary key при одновременном выделении всех полей,

входящих в первичный ключ таблицы (рис. III.16). После чего следует

через кнопку Manage Indexes/Keys (Управление индексами/ключами) в

диалоговом окне Properties (

Свойства), на вкладке Indexes/Keys (Индек-

сы/Ключи) просмотреть устанавливаемые параметры.

Мартьянова А.Е. Базы данных и знаний 220

Рис. III.16. Определение условия PRIMARY KEY для нескольких столбцов таблицы

Условие на значение столбца FOREIGN KEY

Условие на значение FOREIGN

KEY (внешний ключ) применяется

вместе с ранее определенным условием PRIMARY

KEY в ассоциирован-

ной таблице. Условие на значение FOREIGN

KEY ставит в соответствие

один или несколько столбцов таблицы идентичному набору столбцов

другой таблицы, для которых определено условие на значение PRIMARY

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

ределено условие PRIMARY

KEY, автоматически обновляются значения

столбцов других таблиц, для которых определено условие FOREIGN

KEY.

Условия на значения PRIMARY

KEY и FOREIGN KEY обеспечи-

вают совпадение строк ассоциированных таблиц, поэтому они могут

быть использованы в последующих объединениях. Автоматическое об-

новление соответствующих столбцов в различных таблицах после того,

как для них определены условия на значения столбцов PRIMARY

KEY и

FOREIGN

KEY, называется декларативной ссылочной целостностью

(declarative referential integrity). Эта характеристика была добавлена к

SQL Server еще в версии 6.0.

Соответствующие столбцы, для которых определены условия на

значения PRIMARY

KEY и FOREIGN KEY, не обязательно должны

иметь одно и то же имя. Хотя обычно используются именно одинаковые

для обоих столбцов имена, чтобы было более понятно, какие именно

220