Агапов И.Е. Лабораторный практикум. Базы данных

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

3. Лабораторная работа №3.

Таблицы

3.1. Цель работы

Изучить способы создания, изменения и удаления таблиц. Получить

навыки использования приложения "IB Expert" для создания, удаления и из-

менения структуры таблиц. Изучить SQL-операторы для работы с таблицами

и индексами.

Время выполнения: 4 часа.

3.2. Исходные данные

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

предыдущих лабораторных работ.

3.3. Используемые программы

1. Программа "IB Expert".

2. Редактор "Microsoft Word" для создания отчета.

3.4. Теоретические сведения

3.4.1. Таблицы (Tables)

InterBase – реляционная СУБД, поэтому все данные в InterBase хранят-

ся в виде двумерных таблиц со строками и столбцами. Строки называются

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

В этой лабораторной работе рассматриваются не правилами проекти-

рования правильного набора таблиц для хранения данных о некоторой пред-

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

необходимых таблиц.

Основные ограничения, которым должны удовлетворять таблицы:

1. Каждый столбец в таблице имеет уникальное имя.

2. Все данные в столбце должны быть одного типа.

3. Порядок строк и столбцов в таблице не имеет значения.

4. В таблице не может быть двух одинаковых строк.

Часть языка SQL, которая управляет метаданными, называется

Data

Definition Language (DDL). К DDL относятся операторы для определения лю-

бых содержащихся в базе данных объектов, в том числе и таблиц. Операто-

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

SQL, и поэтому без изменений будут работать и во многих других СУБД.

Для создания таблиц используется оператор "CREATE TABLE", кото-

рый приводит к созданию пустой таблицы

без строк. При создании таблиц

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

размерами, а также ограничения на хранящуюся в таблице информацию.

Максимальная длина названий таблиц, полей и ограничений – 31 символ.

Имена таблиц в пределах базы данных должны быть уникальны.

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

таблицы, а также либо

тип данных, либо выражение для вычисления значе-

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

Упрощенный синтаксис оператора создания таблицы:

CREATE TABLE Имя_Таблицы(

<описание колонки> [,

<описание колонки> | <ограничение> ...]

);

В этом операторе элемент <ограничение> означает ограничение, ко-

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

PT.

<описание колонки> = Колонка {

<тип данных>

| COMPUTED [BY] (<вычисляемое выражение>)

| Имя_Домена

}

[DEFAULT {Literal | NULL | USER}]

[NOT NULL] [<ограничение поля>];

Здесь элемент <вычисляемое выражение> означает выражение,

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

этого вычисляемого поля. Элемент <ограничение поля> означает огра-

ничение, которое задается при одновременном определении поля (функцио-

нально полностью аналогично варианту описания ограничения отдельной

строкой при создании таблицы). Остальные элементы были

описаны в пре-

дыдущей лабораторной работе.

Пример создания таблицы "Человек" (домены ID и Name описаны на

странице 17):

CREATE TABLE Person(

Pr_ID ID, -- номер человека

Pr_LastName Name, -- фамилия

Pr_FirstName Name, -- имя

Pr_Patronymic CHAR(30), -- отчество

Pr_FIO COMPUTED BY ( -- фамилия и инициалы

Pr_LastName || ' ' ||

SUBSTRING(Pr_FirstName FROM 1 FOR 1) || '.' ||

SUBSTRING(Pr_Patronymic FROM 1 FOR 1) || '.')

);

P Ограничения изучаются в другой лабораторной работе.

Первые три поля заданы с использованием доменов, тип поля

Pr_Patronymic задан непосредственно при создании таблицы, а поле

Pr_FIO является вычислимым.

В этом операторе || означает операцию конкатенации (соединения)

строк, SUBSTRING() – функция, возвращающая подстроку (в приведенном

примере – первую букву полей "Pr_FirstName" и "Pr_Patronymic").

Если при описании поля не использовать домен, то Firebird все

равно

создает домен специально для этого поля. Имя таких доменов состоит из

символов "RDB$" и порядкового номера. С такими системными доменами

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

Для удаления таблицы используется оператор DROP TABLE.

DROP TABLE Person; -- Пример удаления таблицы

Оператор ALTER TABLE предназначен для изменения структуры таб-

лицы вместо ее удаления и повторного создания. С его помощью можно из-

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

добавлять и удалять ограничения.

Примеры операторов ALTER TABLE:

-- добавление поля "День рождения":

ALTER TABLE Person ADD Birthday DATE;

-- удаление поля "Отчество":

ALTER TABLE Person DROP Pr_Patronymic;

-- переименование поля "День рождения":

ALTER TABLE Person

ALTER COLUMN Birthday TO Pr_Birthday;

Если требуется изменить тип поля, например, увеличить число симво-

лов, хранимых в поле, то необходимо изменять домен этого поля оператором

ALTER DOMAIN или выполнить изменения в системных таблицах.

3.4.2. Индексы

InterBase (как и другие реляционные СУБД) хранит записи в таблицах в

неупорядоченном виде. Записи, добавляемые в таблицу одна за другой, не

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

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

выборку информации.

Индекс – это упорядоченный указатель на записи таблицы.

Индекс со-

стоит из пар значений "значение поля" – "физическое расположение записи",

поэтому по значению поля (или полей), входящего в индекс, при помощи ин-

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

жащая это значение.

Устройство индексов на физическом уровне для нас совершенно не

имеет значения. Важно только знать, что создание индексов может привести

к значительному ускорению процессов поиска и сортировки.

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

ний – например, на поле, хранящие пол человека, которое содержит только

два значения – "м" и "ж".

Использование индексов имеется два отрицательных последствия:

1. Для индексов дополнительно тратится дисковое пространство.

2. Наличие индексов замедляет модификацию данных в таблице.

Индексы создаются при помощи команды CREATE INDEX.

Синтаксис оператора создания индекса:

CREATE [UNIQUE] [ASC[ENDING] | DESC[ENGING]] INDEX

Имя_Индекса ON Таблица(Поле [, Поле ...]);

Индекс может быть либо возрастающим (ASC[ENDING]), либо убы-

вающим (DESC[ENGING]). Если при создании индекса указать UNIQUE, то

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

ных полей.

Пример создания индекса:

-- индекс по фамилии человека:

CREATE INDEX Index_Person_Name ON Person(Pr_LastName);

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

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

новить его производительность.

Чтобы пересоздать индекс, можно использовать следующие способы:

1. Перестроить индекс с помощью команды ALTER INDEX. Для этого надо

последовательно выполнить две команды:

ALTER INDEX Имя_Индекса INACTIVE;

ALTER INDEX Имя_Индекса ACTIVE;

2. Удалить индекс (командой DROP INDEX), а затем повторно создать его.

3. Выполнить резервное копирование и восстановление базы данных.

Индекс характеризуется числом в пределах от 0 до 1, которое называ-

ется статистикой. Это число зависит от числа различных значений поля в

таблице. Оптимизатор запросов InterBase использует это число для опреде-

ления эффективности применения

индекса в запросе. Статистика определяет-

ся при создании и перестройке индекса, а также по команде пересчета стати-

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

улучшить производительность. Пересчет статистики не перестраивает ин-

декс, а только определяет статистику.

Команда пересчета статистики индекса:

SET STATISTICS INDEX Имя_Индекса;

3.5. Задание

Лабораторную работу следует выполнять в следующем порядке:

1. Создать на сервере pi_srv рабочую папку для хранения файлов, получае-

мых при выполнении лабораторной работы №3. Эта папка должна быть

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

"ЛР3".

2. Скопировать в эту папку файл сценария, созданный при выполнении ла-

бораторной работы

№2.

3. Открыть окно "Script Executive" в приложении "IB Expert" и прочитать в

него файл сценария, скопированный ранее в папку "ЛР3".

4. Изменить в сценарии комментарии, в которых должно быть отмечено, что

это сценарий для лабораторной работы №3 и исправить путь до файла с

базой данных, чтобы база данных создавалась в папке "ЛР3".

5. Добавить

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

дуальным заданием. Должно получиться не менее 5 таблиц. При описании

полей необходимо использовать и созданные ранее домены и непосредст-

венно типы данных. В таблицах должно быть не менее трех вычислимых

полей.

6. Добавить в сценарий команды создания нескольких индексов. Необходи-

мо создать

не менее чем по одному индексу для каждой из созданных таб-

лиц.

7. Выполнить сценарий и сохранить его в папке "ЛР3".

8. Зарегистрировать созданную базу данных в программе "IB Expert" и под-

ключиться к ней.

9. Создать в своей базе данных таблицу в диалоговом режиме. Она должна

называться "TestDialog" и содержать десять полей разных

типов с произ-

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

"TestDialog", который сгенерирует "IB Expert".

10. Создать в диалоговом режиме в таблице "TestDialog" три различных ин-

декса и сценарии их создания скопировать в отчет.

11. Создать в папке "ЛР3" резервную копию базы данных.

12. Создать и сохранить в папке "ЛР3" файл с отчетом

о выполнении лабора-

торной работы №3, который должен называться "Отчет.doc".

3.6. Ход работы

Действия по созданию таблиц и индексов с помощью команд сценария

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

ненным ранее при выполнении предыдущих лабораторных работ.

Для создания таблицы в диалоговом режиме, нажмите в окне "Database

Explorer" правую клавишу мыши на узле "Tables" или на одной из имеющих-

ся таблиц и в открывшемся

меню выберите команду "New Table… (Ctrl+N)"

(рис. 11). В результате откроется окно создания таблицы (рис. 12).

Рис. 11. Окно "Database Explorer" с перечнем таблиц

Рис. 12. Окно формирования таблицы в диалоговом режиме

В верхней части этого окна вводится имя создаваемой таблицы (в при-

веденном случае – "TESTDIALOG").

Сетка в средней части окна содержит сведения о полях таблицы. Чтобы

добавить поле в таблицу, следует нажать клавишу [Insert] или [Стрелка вниз].

В колонке "Field Name" вводится имя создаваемого поля, в колонке "Field

Type" выбирается

тип данных, либо в колонке "Domain"– созданный ранее

домен. Для текстовых полей (CHAR и VARCHAR) в колонке "Size" вводится

размер поля. Чтобы задать полю ограничение "NOT NULL" достаточно уста-

новить флажок в колонке "Not Null".

В нижней части окна на вкладке "Field description" можно вводить для

каждого поля поясняющие комментарии.

После введения данные о всех полях таблицы следует нажать кнопку

[Compile] (Ctrl+F9). В результате этого откроется диалоговое окно

"Creating Table Имя_Таблицы…" (рис. 13), в котором надо подтвердить

(кнопка [Commit]) или отменить (кнопка [Rollback]) выполнение операции.

Рис. 13. Диалоговое окно создания таблицы

Если закрыть это диалоговое окно кнопкой [Commit], то в окне, пока-

занном на рис. 12 появятся дополнительные вкладки и оно примет следую-

щий вид (рис. 14).

Рис. 14. Окно просмотра и редактирования таблицы

Теперь появится возможность добавить к таблице индексы. Для этого

перейдите на вкладку "Indices" (рис. 15).

Рис. 15. Окно просмотра и редактирования индексов

Сетка на этой вкладке позволяет просматривать, редактировать, созда-

вать и удалять индексы.

Для создания индекса выполните следующие действия:

1. Нажмите в этой сетке клавишу [Insert]. В результате будет вставлена но-

вая строка.

2. Задайте в колонке "Index" имя индекса.

3. Нажмите кнопку

в колонке "On field". В результате откроется окно с

двумя списками (рис. 15). В левом списке "Available fields" будут нахо-

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

cluded fields" будут находиться поля, которые уже включены в список.

Для формирования перечня полей, которые будут входить в индекс, пере-

местите нужные поля из левого списка в

правый.

4. Если создается уникальный индекс, установите флажок в колонке

"Unique".

5. Чтобы активизировать индекс установите флажок в колонке "Active".

6. В колонке "Sorting" выберите способ сортировки.

7. Чтобы создать индекс нажмите кнопку

[Compile] (Ctrl+F9).

8. В результате откроется диалоговое окно (рис. 16), в котором надо под-

твердить (кнопка [Commit]) или отменить (кнопка [Rollback]) выполнение

операции.

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

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

[Compile].

Рис. 16. Диалог создания индекса

3.7. Отчет о выполнении работы

Отчет о выполнении лабораторной работы №3 необходимо оформить

на листах формата A4.

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

мые в следующей последовательности:

1. Краткое описание таблиц, предназначенных для моделирования предмет-

ной области в соответствии с вариантом.

2. Распечатка сценария создания базы данных с комментариями, которые

должны содержать сведения об авторе

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

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

3. Перечень файлов, полученных при выполнении лабораторной работы с

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

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

3.8. Контрольные вопросы

1. Что такое таблица в InterBase? Какими свойствами должны обладать таб-

лицы?

2. Как по-другому называются строки и столбцы таблиц?

3. Как задаются вычислимые поля?

4. Как изменить название поля в таблице?

5. Что такое индекс?

6. Для чего используются индексы?

7. Какие отрицательные последствия имеет использование индексов?

8. В каком

случае применение индексов не дает положительного результата?

9. Каковы четыре способа улучшения производительности индексов?

10. Как сделать индекс по убыванию?

11. Сколько системных доменов имеется в созданной вами базе данных?

4. Лабораторная работа №4.

Ограничения

4.1. Цель работы

Изучить используемые в Firebird типы ограничений. Получить навыки

использования программы "IB Expert" для создания, изменения и удаления

ограничений. Изучить SQL-операторы для работы с ограничениями.

Время выполнения: 2 часа.

4.2. Исходные данные

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

предыдущих лабораторных работ.

4.3. Используемые программы

Программы "IB Expert" и "Microsoft Word".

4.4. Теоретические сведения

Ограничения базы данных – это правила, которые проверяются при со-

хранении информации в базе данных.

Главным преимуществом использования ограничений является воз-

можность реализации проверки данных, а значит части бизнес-логики при-

ложения, на уровне базы, т.е. централизовать ее. Это существенно упрощает

разработку приложений баз данных.

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

используется

четыре вида ограничений (CONSTRAINTS):

1. Ограничение первичного ключа (Primary key).

2. Ограничения уникальности (Unique).

3. Ограничения внешнего ключа (Foreign keys).

4. Контрольные ограничения (Checks).

Все четыре вида ограничений могут создаваться двумя способами:

1. На основе одного поля таблицы (одновременно при создании этого поля).

2. На основе нескольких полей (без одновременного создания поля).

Ограничения задаются либо

при создании таблицы в операторе

CREATE TABLE, либо добавляются позже в операторе ALTER TABLE.

Удалить ограничение можно в операторе ALTER TABLE.

Упрощенный синтаксис задания ограничений при создании поля при-

веден ниже (использование элемента <ограничение поля> поясняется на

странице 22).

<ограничение поля> = [CONSTRAINT Название_Ограничения]

<описание ограничения поля> [<ограничение поля> ...]