42327

Ограничения целостности. SQL-операторы для работы с ограничениями

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

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

Ограничения целостности Цель работы Изучить используемые в Firebird типы ограничений целостности. Изучить SQLоператоры для работы с ограничениями. Теоретические сведения Ограничения целостности данных представляют собой такие ограничения которые вводятся с целью предотвратить помещение в базу противоречивых данных. Ограничения внешнего ключа Foreign keys ссылочная целостность.

Русский

2013-10-29

124.5 KB

13 чел.

12     

  1.  Лабораторная работа №4. Ограничения целостности
    1.  Цель работы

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

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

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

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

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

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

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

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

Главным преимуществом использования ограничений является возможность реализации проверки данных, а значит части бизнес-логики приложения, на уровне СУБД, т.е. централизовано. Это существенно упрощает разработку приложений баз данных.

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

  1.  Обязательные данные (NOT NULL).
  2.  Ограничение первичного ключа (Primary key).
  3.  Требования данного предприятия.
  4.  Ограничения внешнего ключа (Foreign keys) — ссылочная целостность.
  5.  Ограничения атрибутов (Checks).

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

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

Ограничения задаются либо при создании таблицы в операторе CREATE TABLE, либо добавляются позже в операторе ALTER TABLE.


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

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

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

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

<описание ограничения поля {

  PRIMARY KEY |

  UNIQUE |

  REFERENCES Другая_Таблица[(Поле [, Поле ...])] |

  CHECK (Проверяемое_Условие)

}

Упрощенный синтаксис задания ограничений на основе нескольких полей таблицы или без одновременного создания поля имеет следующий вид (способ использования описываемого элемента <ограничение> приведен в методических указаниях по выполнению предыдущей лабораторной работы):

<ограничение> = [CONSTRAINT Имя_Ограничения]

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

<Описание ограничения> = {

   {PRIMARY KEY | UNIQUE} (Поле [, Поле ...]) |

   CHECK (Проверяемое_Условие) |

   FOREIGN KEY (Поле [, Поле ...])

       REFERENCES Другая_Таблица[(Поле [, Поле ...])]

   [ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]

   [ON UPDATE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]   

}

Как видно из синтаксиса задания ограничений, каждое ограничение может иметь имя, записываемое после ключевого слова CONSTRAINT. Все ограничения в базе данных должны иметь уникальные имена. Если при задании ограничения не задать его имени, то оно будет назначено сервером СУБД автоматически. При выполнении лабораторных работ необходимо каждому ограничению обязательно давать имя.

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

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

Первичные ключи выполняют следующие функции:


  1.  Они служат средством различения отдельных записей.
  2.  Используются в ссылочной целостности.
  3.  При создании первичного ключа автоматически создается индекс, поэтому они ускоряют поиск и сортировку.

Первичные ключи необходимо создавать для всех таблиц. Первичный ключ таблицы может быть составным (состоящим из нескольких полей). Все поля первичного ключа должны быть объявлены NOT NULL.

Пример задания ограничения первичного ключа с помощью добавления в таблицу ограничения (ADD CONSTRAINT):

ALTER TABLE Customer

  ADD CONSTRAINT PK_Customer PRIMARY KEY (Cust_No);

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

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

Пример задания ограничения уникальности:

ALTER TABLE Employee ADD CONSTRAINT Unique_Phone UNIQUE (Phone);

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

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

Ограничение внешнего ключа означает, что данное поле соответствует первичному ключу в таблице "Другая_Таблица" и в этой таблице имеется строка с указанным значением. Дополнительные режимы ON DELETE и ON UPDATE задают действия, производимые при удалении или изменении ключевых полей в родительской (здесь "Другая_Таблица") таблице.

Возможны следующие варианты:

NO ACTION – нет действий (принимается по умолчанию).

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

SET DEFAULT — при удалении (замене) строк родительской таблицы, соответствующие им поля в дочерней таблице переустанавливаются в значения по умолчанию.


SET NULL — при удалении (замене) строк родительской таблицы, соответствующие им поля в дочерней таблице переустанавливаются в NULL.

При определении внешнего ключа автоматически генерируется индекс.

Пример задания ограничения внешнего ключа:

ALTER TABLE Book ADD CONSTRAINT FK_Book

  FOREIGN KEY (B_PubNo) REFERENCES Publisher (B_NO)

ON DELETE SET NULL

ON UPDATE CASCADE;

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

  1.  Ограничения атрибутов (Checks)

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

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

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

В ограничениях атрибутов можно использовать большой набор операторов для проверки вставляемых и изменяемых значений. Полный синтаксис создания ограничения атрибутов здесь не приводится из-за своего большого размера. С ним можно ознакомиться в соответствующих учебниках.

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

Пример ограничения атрибутов, которое гарантирует, что поле "Num" будет иметь значение от 0 до 4:

ALTER TABLE Property

   ADD CONSTRAINT

     Check_Invalid_Number CHECK (Num >= 0 AND Num <= 4);

Ограничения атрибутов реализованы при помощи системных триггеров.

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

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


  1.  Создать на сервере sqledu02 (или на локальном компьютере) рабочую папку для хранения файлов, получаемых при выполнении лабораторной работы №4. Эта папка должна быть рядом с папками для предыдущих лабораторных работ и называться "ЛР4". Пример правильного названия рабочей папки при выполнении лабораторной работы №4: "sqledu02:\D:\Data\ЛР4".
  2.  Открыть окно "Редактор скриптов" в приложении "IBExpert".
  3.  Используя кнопку   [Загрузить из файла] на панели инструментов, открыть диалоговое окно "Open SQL File" и с его помощью открыть файл сценария,  созданный при выполнении лабораторной работы №3.
  4.  Используя кнопку [Save as] на панели инструментов, сохранить загруженный сценарий в папке "sqledu02:\D:\Data\ЛР4".
  5.  Изменить соответствующим образом в сценарии текст комментариев и сделать так, чтобы база данных теперь создавалась в папке "ЛР4".
  6.  Добавить в операторы создания таблиц создание ограничений четырех видов (ограничения первичного ключа создавались в предыдущей лабораторной работе). С помощью ограничений внешнего ключа должны быть заданы имеющиеся связи между таблицами и в случае необходимости (слабая сущность) изменить первичный ключ таблицы, включив в него поле созданного внешнего ключа..
  7.  Выполнить сценарий и сохранить его в папке "ЛР4".
  8.  Зарегистрировать созданную базу данных в программе "IBExpert" и подключиться к ней.
  9.  Создать в своей базе данных таблицу в диалоговом режиме. Она должна называться "TestConstraints" и содержать десять полей разных типов с произвольными именами.
  10.  Добавить в диалоговом режиме к этой таблице по одному ограничению каждого типа. Скопировать в отчет сценарий создания этой таблицы, который сгенерирует "IBExpert".
  11.  Создать в папке "ЛР4" резервную копию базы данных.
  12.  Создать и сохранить в папке "ЛР4" файл с отчетом о выполнении лабораторной работы №4, который должен называться "Отчет4-xx.odt", где xx — это номер варианта задания.   
    1.  Варианты заданий

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

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

№ варианта

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

Имя файла БД

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.     Ход работы 

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

Для создания ограничений с помощью программы "IBExpert" в диалоговом режиме необходимо выполнить следующие действия:

  1.  Подключиться к базе данных.
  2.  Открыть окно с таблицей, в которую будут добавляться ограничения.
  3.  Перейти на вкладку "Ограничения". На этой вкладке имеется четыре дополнительных вкладки для просмотра и редактирования ограничений четырех типов (Рис. 1).


  1.  Чтобы создать новое ограничение надо перейти на нужную вкладку и на-жать клавишу [Insert]. В результате в сетку с перечнем имеющихся ограничений будет добавлена строка с новым ограничением.
  2.  После задания желаемых значений в новой строке необходимо нажать кнопку [Компиляция] (Ctrl+F9), которая приведет к автоматическому созданию оператора ограничения и его выполнению. На рис. 2 показан результат создания первичного ключа для таблицы "TESTDIALOG".

  1.  


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

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

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

  1.  Цель работы.
  2.  Краткое описание предметной области в соответствии с вариантом.
  3.  Данные о версии СУБД, использованной в лабораторной работе.
  4.  Перечисление всех использованных при выполнении лабораторной работы команд главного меню приложения "IBExpert" с краткими пояснениями выполняемых действий.
  5.  Перечень файлов, полученных при выполнении лабораторной работы с указанием их имен, места расположения, даты изменения и размеров (сценарий, база данных, резервная копия базы данных, файл с отчетом).
  6.  Письменные ответы на контрольные вопросы.
  7.  Выводы.
  8.  Распечатку сценария создания базы данных с комментариями, которые должны содержать сведения об авторе, дате создания, всех выполняемых действиях и пояснения к ограничениям целостности.

  1.  Контрольные вопросы
  2.  Дать определение термина "Ограничение целостности".
  3.  Какие существуют варианты операторов создания ограничений?
  4.  Какие особенности имеют поля, входящие в первичный ключ и ограничение уникальности?
  5.  Какие функции имеет ограничение первичного ключа?
  6.  Что такое внешний ключ?
  7.  Зачем в полях внешнего ключа могут разрешаться пустые значения?
  8.  Что такое ограничение атрибутов?
  9.  При помощи чего реализованы ограничения атрибутов?
  10.  Какие имена получают ограничения первичного ключа, если их не задать в явном виде? Какие имена при этом будут иметь автоматически создаваемые индексы?
  11.  Какие имена получают ограничения уникальности, если их не задать в явном виде? Какие имена при этом будут иметь автоматически создаваемые индексы?


  1.  Какие имена получают ограничения внешнего ключа, если их не задать в явном виде? Какие имена имеют индексы, автоматически создаваемые для ограничений внешнего ключа?
  2.  Какие имена назначаются системным триггерам для реализации ограничений атрибутов?


 

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

28984. Понятие и структура СУБД 40.5 KB
  СУБД совокупность языковых и программных средств предназначенных для создания и ведение использование БД. Структура СУБД. При помощи отладчиков происходит объединение отдельных блоков СУБД.
28985. RDA модель 31 KB
  Коды компонента представления и прикладного компонента совмещены и выполняются на компьютере клиенте кот реализует как функции ввода и отображения данных так и прикладные функции т. Поддержка разного интерфейса компонента представления. Проблемы с администрированием приложений поэтому при необходимости изменения прикладного компонента необходимо переписывать всю программу целиком.
28986. DBS модель 31 KB
  На компьютере клиенте находится представительские прикладные функции которые реализует прикладной компонент реализуемый на компьютере сервере в виде хранящих процедур. Компонент доступа к информационным ресурсам связан с ядром БД так же находится на компьютере сервере совместно с прикладным. Пользователь через компонент представления вызывает необходимые процедуры из прикладного компонента. Прикладной компонент формируется внутри SQLзапрос компонента доступа извлекает необходимую информацию из БД обрабатывает ее и отправляет ее через...
28987. AS модель 32.5 KB
  Компонент представления расположен на ПКклиенте. Прикладной компонент выполняется на сервере приложений отдельный удаленный ПК. Доступ к информационным ресурсам необходимый для решения прикладных задач обеспечивается аналогично RDAмодели: SQL запрос к компоненту доступа обратно на сервере приложения обрабатываются данных где происходит их обработка и результат отправляется на ПКклиент. Отличия от RDA модели и от DBS модели заключаются в том что прикладной компонент вынесен на отдельный ПК группу ПК так называемое трехзвенная схема.
28988. FS модель 30.5 KB
  Суть: один из ПК сети является файлсервером ПК сервер содержит файлы с данными работает под управлением сетевой ОС. Пользователь забирает файл с данными с ПКсервера и обрабатывает в своем приложении. Файловый сервер обеспечивает доступ к файлом и БД предоставляет в общее пользование дисковое пространство для хранения информации и данных а так же сетевое оборудование. ограниченное колво операций с файлами; 3.
28989. Способы связи ПК и соответствующие виды компьютерных сетей. Виды взаимодействия (доступа) ПК в сети 23 KB
  Виды взаимодействия доступа ПК в сети. образуются распределенные корпоративные компьютерные сети соединённые каналами связи нескольких локальных сетей доступ из вне строго регламентирован; глобальные сети общего доступа совокупность ЛВС и индивидуальных ПК; специальные сети пример: сеть банкоматов сеть кассовых аппаратов сеть телеконференций. Сетевой ресурс ресурс одного компьютера доступный с другим ПК в сети информация программы сетевое оборудование. Сервер ПК сети который представляющий сетевой ресурс.
28990. Локальные компьютерные сети: типы, классификации, состав 30 KB
  Для создания сети компьютеры должны быть связаны линейными связями: витая пара оптиковолоконный кабель беспроводные радиоканалы WIFI. Применяемые технические средства и топологии сети определяет скорость передачи информации в такой сети. Работу сети организует системный администратор который назначает пользователю сетевой ресурс права доступа к нему а так же имя и пароль пользователю.
28991. Звезда шина 63.5 KB
  Топология физическое расположение компьютеров узлом коммутации и линий связи в сети а также их организационное взаимодействие. Все ПК подключены к единому узлу коммутации.
28992. Кольцо 44.5 KB
  Кольцо. ПК подключены к повторителям кот соединены шиной данных в однонаправленное кольцо. Обмен: Кольцо бывает тактированное и маркерное.