42324

Таблицы. SQL-операторы для работы с таблицами и индексами

Лабораторная работа

Информатика, кибернетика и программирование

Изучить способы создания изменения и удаления таблиц. Теоретические сведения Таблицы Tbles Firebird – реляционная СУБД поэтому все данные в Firebird хранятся в виде двумерных таблиц со строками и столбцами. Основные ограничения которым должны удовлетворять таблицы: Каждый столбец в таблице имеет уникальное имя. Первичный ключ это столбец который выбран для уникальной идентификации записей базы данных строк таблицы.

Русский

2013-10-29

197.5 KB

26 чел.

14     

  1.  Лабораторная работа №3. Таблицы
    1.  Цель работы

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

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

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

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

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

Все операции выполняются с помощью приложения "IBExpert". Отчет создается в редакторе "OpenOffice.pro".

  1.  Теоретические сведения
    1.  Таблицы (Tables)

Firebird – реляционная СУБД, поэтому все данные в Firebird хранятся в виде двумерных таблиц со строками и столбцами. Строки называются кортежами или записями, а столбцы – доменами или полями.

Каждая таблица хранит данные об объектах (сущностях) реального мира с одинаковыми свойствами. Тип сущности может быть объектом с физическим существованием или объектом с абстрактным существованием.

Примеры:

Работник    

Выдача книг в библиотеке

Клиент

Читатель, берущий книги в библиотеке

Книга  

Собственность библиотеки

Формуляр

Записи о выдаче книги

Автор

Автор(ы) книги

Каждый тип сущности идентифицируется именем и списком свойств (атрибутов). База данных обычно содержит много разных типов сущностей (таблиц). Каждая сущность с другой стороны имеет собственные значения для каждого свойства. Типы сущностей можно классифицировать на сильные и слабые (родительские и зависимые).


Слабый тип сущности — тип сущности, существование которого зависит от какого-то другого типа сущности.

Сильный тип сущности — тип сущности существование которого не зависит от какого-то другого типа сущности.

Книга — сильная сущность, формуляр (на каждый экземпляр книги) — слабая сущность.

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

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

  1.  Каждый столбец в таблице имеет уникальное имя.
  2.  Все данные в столбце должны быть одного типа.
  3.  Порядок строк и столбцов в таблице не имеет значения.
  4.  В таблице не может быть двух одинаковых строк.
  5.  Обеспечивать целостность данных.

Для обеспечения целостности данных в таблицах должен быть столбец первичного ключа. Первичный ключ — это столбец, который выбран для уникальной идентификации записей базы данных (строк таблицы). Первичный ключ любой таблицы не должен содержать пустого значения.

Язык, который управляет метаданными, называется Data Definition Language (DDL). К DDL относятся операторы для определения любых содержащихся в базе данных объектов, в том числе и таблиц.

Для создания таблиц используется оператор "CREATE TABLE", который приводит к созданию пустой таблицы. При создании таблиц задается имя таблицы, описание набора столбцов с их именами, типами и размерами, а также ограничения на хранящуюся в таблице информацию. Максимальная длина названий таблиц, полей и ограничений – 31 символ. Имена таблиц в пределах базы данных должны быть уникальными.

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

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

CREATE TABLE Имя_Таблицы(<описание столбца> [, <описание столбца> | <ограничение> ...] ); 

В этом операторе элемент <ограничение> означает ограничение, которое задается на уровне таблицы без одновременного описания столбца*.


<описание столбца> = Имя_Столбца { <тип данных> | COMPUTED [BY] (<вычисляемое выражение>) | Имя_Домена } [DEFAULT {Literal | NULL | USER}] [NOT NULL] [<ограничение поля>]

[COLLATE collation]; 

Здесь элемент <вычисляемое выражение> означает выражение, которое будет вычисляться каждый раз при необходимости вернуть значение этого вычисляемого поля. Элемент <ограничение поля> означает ограничение, которое задается при одновременном определении поля (функционально полностью аналогично варианту описания ограничения отдельной строкой при создании таблицы). Остальные элементы были описаны в предыдущей лабораторной работе.

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

CREATE TABLE Person(

 Pr_ID ID PRIMARY KEY, -- номер человека (первичный ключ)

 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) || '.')

   COLLATE PWX_CYRL

); 

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

В этом операторе || означает операцию конкатенации (соединения) строк, SUBSTRING() – функция, возвращающая подстроку (в приведенном примере – первую букву полей "Pr_FirstName" и "Pr_Patronymic").

Синтаксис функции SUBSTRING:

SUBSTRING( <строка> FROM <с позиции> [FOR <кол-во символов>])

Функция возвращает подстроку строки <строка>, начиная с символа <с позиции> и длиной максимум <кол-во символов>, если <кол-во символов> не указано, то до конца строки.

Если при описании поля не использовать домен, то 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 или выполнить изменения в таблицах (ALTER TABLE).

  1.  Индексы

Firebird (как и другие реляционные СУБД) хранит записи в таблицах в неупорядоченном виде. Записи, добавляемые в таблицу одна за другой, не обязательно окажутся "рядом". Данные, извлекаемые из таблицы, также не имеют какого-либо порядка, кроме того, который явно указан в запросе на выборку данных.

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

Реализация индексов на физическом уровне для нас совершенно не имеет значения. Важно только знать, что создание индексов может привести к значительному ускорению процессов поиска и сортировки.

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

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

  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;

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

Индекс характеризуется числом в пределах от 0 до 1, которое называется статистикой. Это число зависит от числа различных значений поля в таблице. Оптимизатор запросов Firebird использует это число для определения эффективности применения индекса в запросе. Статистика определяется при создании и перестройке индекса, а также по команде пересчета статистики. Когда число записей в таблице изменяется, пересчет статистики может улучшить производительность. Пересчет статистики не перестраивает индекс, а только определяет статистику.

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

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

  1.  Порядок выполнения работы

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

  1.  Создать на сервере sqledu02 (или на локальном компьютере) рабочую папку для хранения файлов, получаемых при выполнении лабораторной работы №3. Эта папка должна быть рядом с папками для предыдущих лабораторных работ и называться "ЛР3". Пример правильного названия рабочей папки при выполнении лабораторной работы №3: "sqledu02:\D:\Data\ЛР3".
  2.  Открыть окно "Редактор скриптов" в приложении "IBExpert".
  3.  Используя кнопку   [Загрузить из файла] на панели инструментов, открыть диалоговое окно "Open SQL File" и с его помощью открыть файл сценария,  созданный при выполнении лабораторной работы №2.
  4.  Используя кнопку [Save as] на панели инструментов, сохранить загруженный сценарий в папке "sqledu02:\D:\Data\ЛР3".  


  1.  Изменить в сценарии путь до файла с базой данных, чтобы база данных создавалась в папке "ЛР3".  
  2.  Добавить в сценарий команды создания двух таблиц в соответствии с индивидуальным заданием. При описании полей необходимо использовать и созданные ранее домены и непосредственно типы данных. В таблицах должно быть не менее одного вычисляемого поля. В каждой таблице должен быть создан первичный ключ.
  3.  Добавить в сценарий команды создания нескольких индексов. Необходимо создать не менее, чем по одному индексу для каждой из созданных таблиц.
  4.  Выполнить сценарий и сохранить его в папке "ЛР3".
  5.  Зарегистрировать созданную базу данных в программе "IBExpert" и подключиться к ней.
  6.  Создать в своей базе данных таблицу в диалоговом режиме. Она должна называться "TestDialog" и содержать десять полей разных типов с произвольными именами. Скопировать в отчет сценарий создания таблицы "TestDialog", который сгенерирует "IBExpert".
  7.  Создать в диалоговом режиме в таблице "TestDialog" три различных индекса и сценарии их создания скопировать в отчет.
  8.  Создать в папке "ЛР3" резервную копию базы данных.
  9.  Создать и сохранить в папке "ЛР3" файл с отчетом о выполнении лабораторной работы №3, который должен называться "Отчет3-xx.odt", где xx — это номер варианта задания.  

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

Вариант задания (Таблица 1) выбирается по формуле (N mod 24)+1, где N – последние две цифры зачетной книжки студента.

Таблица : Варианты заданий к лабораторной работе №3

№ варианта

Имя пользователя

Имя файла БД

Родительская таблица

Дочерняя таблица

1

TEAM001

SALARY.FDB

Цех

Рабочий

2

TEAM002

STUFF.FDB

Изделия

Собрано

3

TEAM003

STUFFPLUS.FDB

Изделия

Собрано

4

TEAM004

TELEPHONE.FDB

Абонент

Разговоры

5

TEAM005

TOOLS.FDB

Поставщик

Поставляет

6

TEAM006

EXAMIN.FDB

Группа

Студент

7

TEAM007

LIBRARY.FDB

Издательство

Книга

8

TEAM008

AVIA.FDB

Самолет

Рейс

9

TEAM009

SHOP.FDB

Изготовитель

Ассортимент


10

TEAM010

HCOMMAND.FDB

Команда

Спортсмен

11

TEAM011

STUDENT.FDB

Группа

Студент

12

TEAM012

SERVIS.FDB

Квалификация

Исполнитель

13

TEAM013

REGION.FDB

Область

Регион

14

TEAM014

OPTSHOP.FDB

Фирма

Товар

15

TEAM015

OPTLEKI.FDB

Изготовитель

Лекарства

16

TEAM016

SPORTSMEN.FDB

Спортсмен

Размещены

17

TEAM017

DANCING.FDB

Город

Танцор

18

TEAM018

BIRTHDOM.FDB

Мать

Ребенок

19

TEAM019

TENNIS.FDB

Команда

Спортсмен

20

TEAM020

CURSES.FDB

Оплата

Расценки

21

TEAM021

TEACHER.FDB

Оклады

Преподаватель

22

TEAM022

HOSPITAL.FDB

Врач

Больной

23

TEAM023

SOLDAT.FDB

Призывник

Направлен

24

TEAM024

YUSHOP.FDB

Изготовитель

Ассортимент

  1.    Ход работы 

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

  1.  Создание таблиц базы данных средствами IBExpert

Для создания таблицы в диалоговом режиме, нажмите в окне "Database Explorer" правую клавишу мыши на узле "Таблицы" или на одной из имеющихся таблиц и в открывшемся контекстном меню выберите команду "Новая таблица (Ctrl+N)". В результате откроется окно создания таблицы (Рис. 1).


В верхней части этого окна вводится имя создаваемой таблицы (в приведенном случае – "TESTDIALOG").  Длина имени таблицы не должна превышать 31 символ.

Сетка в средней части окна содержит сведения о полях таблицы. Чтобы добавить поле в таблицу, следует нажать клавишу [Insert] или [Стрелка вниз]. В столбце "Название" вводится имя создаваемого поля, в колонке "Тип" выбирается тип данных, либо в колонке "Домен"– созданный ранее домен. Для текстовых полей (CHAR и VARCHAR) в колонке "Длина" вводится размер поля. Чтобы задать полю ограничение "NOT NULL" достаточно установить флажок в колонке "Не пусто".  Подробное описание типов данных приведено в методических указаниях по выполнению предыдущей лабораторной работы.

В нижней части окна на вкладке "Описание поля" можно вводить для каждого поля поясняющие комментарии.

После ввода данных о всех полях таблицы следует нажать кнопку [Компиляция] (Ctrl+F9). В результате этого откроется диалоговое окно "Создание таблицы Имя_Таблицы…" (Рис. 2), в котором надо подтвердить (кнопка [Commit]) или отменить (кнопка [Откатить]) выполнение операции. В нижней части этого окна появится оператор скрипта создания таблицы.

Если закрыть это диалоговое окно кнопкой [Commit], то в окне "Database Explorer" появятся дополнительные строки и оно примет следующий вид (Рис. 3).


Теперь появится возможность добавить к таблице индексы. Для этого перейдите на вкладку "Индексы" (Рис. 4).


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

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

  1.  Нажмите в этой сетке клавишу [Insert]. В результате будет вставлена новая строка.
  2.  Задайте в колонке "Индекс" имя индекса.
  3.  Нажмите кнопку в колонке "На поле". В результате откроется окно с двумя списками (Рис. 5). В левом списке "Available fields" будут находиться поля, которые можно добавить к индексу, в правом списке "Included fields" будут находиться поля, которые уже включены в список. Для формирования перечня полей, которые будут входить в индекс, переместите нужные поля из левого списка в правый.


  1.  Если создается уникальный индекс, установите флажок в колонке "Уникальный".
  2.  Чтобы активировать индекс установите флажок в колонке "Активность".
  3.  В колонке "Тип сортировки" выберите способ сортировки.
  4.  Чтобы создать индекс нажмите кнопку [Компиляция] (Ctrl+F9).
  5.  В результате откроется диалоговое окно (Рис. 6), в котором надо под-твердить (кнопка [Commit]) или отменить (кнопка [Откатить]) выполнение операции.

После создания индекса его можно в любой момент изменить, если изменить параметры индекса и снова нажать кнопку [Компиляция].

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

Отчет о выполнении лабораторной работы №3 необходимо оформить на листах формата A4. Для создания отчета следует использовать шаблон отчета для соответствующей лабораторной работы, который необходимо открыть с помощью приложения OpenOffice.

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

  1.  Цель работы.
  2.  Краткое описание предметной области в соответствии с вариантом.


  1.  Данные о версии СУБД, использованной в лабораторной работе.
  2.  Перечисление всех использованных при выполнении лабораторной работы команд главного меню приложения "IBExpert" с краткими пояснениями выполняемых действий.
  3.  Перечень файлов, полученных при выполнении лабораторной работы с указанием их имен, места расположения, даты изменения и размеров (сценарий, база данных, резервная копия базы данных, файл с отчетом).
  4.  Письменные ответы на контрольные вопросы.
  5.  Выводы.
  6.  Распечатку сценария создания базы данных с комментариями, которые должны содержать сведения об авторе, дате создания, всех выполняемых действиях и пояснения к таблицам.
    1.  Контрольные вопросы
  7.  Что такое таблица в Firebird? Какими свойствами должны обладать таблицы?
  8.  Как по-другому называются строки и столбцы таблиц?
  9.  Как задаются первичные ключи?
  10.  Как задаются вычисляемые поля?
  11.  Как изменить название поля в таблице?
  12.  Что такое индекс?
  13.  Для чего используются индексы?
  14.  Какие отрицательные последствия имеет использование индексов?
  15.  В каком случае применение индексов не дает положительного результата?
  16.  Каковы четыре способа улучшения производительности индексов?
  17.  Как сделать индекс по убыванию?
  18.  Сколько системных доменов имеется в созданной вами базе данных?

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

    ?


 

А также другие работы, которые могут Вас заинтересовать

49794. Расчет переходных процессов 185.88 KB
  При всех изменениях в электрической цепи: включении выключении коротком замыкании колебаниях величины какого-либо параметра и т. Расчет переходных процессов с применением классического метода Для электрической цепи рисунок 1 и исходных данных таблица 1 найти закон изменения тока при замыкании ключа К. 7 Так как следовательно: Так как следовательно: Закон изменения тока на индуктивности будет иметь вид: Для нахождения закона изменения тока на индуктивности при переходном процессе необходимо рассчитать входное...
49795. Создание сайта Радиотехника и освоение человеком космического пространства 290.5 KB
  Структура и компоненты HTMLдокумента. Гиперссылки в документах HTML. В Internet составители компьютерных страниц делают практически то же самое подчеркивая либо выделяя в документах ключевые слова или пиктограммы.
49798. Расчёт параметров систем передачи непрерывных сообщений дискретными сигналами 874.5 KB
  Распределение ошибки передачи сообщения по источникам искажений. Расчёт информационных характеристик источника сообщения и канала связи. Для преобразования непрерывного сообщения в цифровую форму используются операции дискретизации и квантования. В составе цифрового канала предусмотрены устройства для преобразования непрерывного сообщения в цифровую форму – АЦП аналого-цифровой преобразователь на передающей стороне и устройства преобразования цифрового сигнала в непрерывный – ЦАП...
49799. Индивидуальный жилой дом в г. Рязань 368.27 KB
  Оконные проемы в стенах запроектированы без четвертей, чтобы обеспечить тепловую защиту от продувания через оконные проемы утеплитель делают выступающим на 30 мм над оконными и дверными проемами уложены железобетонные перемычки. Они передают нагрузку от вышележащих конструкций на стены.
49800. Нахождение корней нелинейного уравнения 3.95 MB
  Блок-схемы реализующие численные методы -для метода дихотомии: Блок-схема для метода хорд: Блок-схема для метода Ньютона: Листинг программы unit Unit1; interfce uses Windows Messges SysUtils Vrints Clsses Grphics Controls Forms Dilogs TeEngine Series ExtCtrls TeeProcs Chrt Menus OleCtnrs StdCtrls xCtrls OleCtrls VCF1 Mth; type TForm1 = clssTForm GroupBox1: TGroupBox; OleContiner2: TOleContiner; MinMenu1: TMinMenu; N1: TMenuItem; Chrt1: TChrt; Series1:...
49801. Создание программы для новой базы данных на языке Pascal 118.13 KB
  Цель моей курсовой работы заключается в создании программы в которой можно: создавать новую базу данных открывать базу из файла сохранение базы в файл добавление записей удаление записей поиск записей по одному из полей вывод базы данных на экранб сортировка и вывод на экран. Исходя из целей постонавления задач мне необхадимо создать базуданных которая будет содержать всю информацию: 1 номер цеха. Программа должна выполнять следующие дополнительные функции: создание новой базы данных; открытие базы из файла; сохранение базы в файл;...