ЛАБОРАТОРНАЯ РАБОТА № 7

 

Цель:

1. Знакомство с использованием контрольных ограничений и правил для поддержки целостности данных в БД.

 

Справочный материал:

1. Конспект курса.

Упражнения:

Перед выполнением лабораторной работы необходимо войти в сеть, затем в систему MS SQL Server и SQL Query Analyser, а также установить связь пользователя с базой данных (аналогично описанному сценарию в лабораторной работе № 1).

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

- на уровне поля;

- на уровне таблицы.

(см. конспект курса).

1) Создание контрольных ограничений на уровне поля.

Рассмотрим структуру таблицы poss:

а) поле номер (nomer) - контрольным ограничением для него является промежуток чисел от 1 до 700000;

б) поле фамилия, имя, отчество (fio) - контрольным ограничением для него является запрет использования символов "." (точка) и "-" (минус);

в) поле дата рождения (data_rogden) разбивается на три подполя:

1. день рождения (d_rogd) - контрольным ограничением является промежуток символов от "01" до "31";

2. месяц рождения (m_rogd) - контрольным ограничением является промежуток символов от "01" до "12";

3. год рождения (g_rogd) - контрольным ограничением является промежуток символов от "00" до "99";

г) поле пол (pol) - контрольным ограничением для него является использование либо символа "М", либо символа "Ж";

д) поле код семейного положения (sem_polog_k) - контрольным ограничением для него является список из четырех значений "0", "1", "2", "3";

е) поле специальность (spec) - контрольным ограничением для него является промежуток символов от "000000" до "190046";

ж) поле код категории обучения (kat_obuch_k) - контрольным ограничением является промежуток символов от "01" до "17";

з) поле код учебного заведения (vuz_k) - контрольным ограничением является промежуток чисел от 128955 до 8199999;

и) поле месяц приема (mp) - контрольным ограничением является промежуток символов от "01" до "12";

к) поле год приема (gp) - контрольным ограничением является промежуток символов от "00" до "99";

л) поле месяц окончания (mo) - контрольным ограничением является промежуток символов от "01" до "12";

м) поле год окончания (gok) - контрольным ограничением является промежуток символов от "00" до "99".

На основе перечисленных для каждого поля структуры таблицы poss контрольных ограничений можно создать таблицу с именем poss1. Для этого в верхней области окна необходимо ввести:

create table poss1

(nomer integer check (nomer between 1 and 700000),

fio char (40) not null check (fio not like '%.%' or fio not like '%-%'),

d_rogd char(2) check (d_rogd between '01' and '31'),

m_rogd char(2) check (m_rogd between '01' and '12'),

g_rogd char(2) check (g_rogd between '00' and '99'),

pol char(1) check (pol like 'M' or pol like 'Ж'),

sem_polog_k char(1) check (sem_polog_k in ('0', '1', '2', '3')),

spec char(6) check (spec between '000000' and '190046'),

kat_obuch_k char(2) check (kat_obuch_k between '01' and '17'),

vuz_k integer check (vuz_k between 128955 and 8199999),

mp char(2) check (mp between '01' and '12'),

gp char(2) check (gp between '00' and '99'),

mo char(2) check (mo between '01' and '12'),

gok char(2) check (gok between '00' and '99')) [F5]

 

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

2) Создание контрольных ограничений на уровне таблицы.

Рассмотрим некоторые значения поля "категория обучения":

а) значения "п/ф(вуз)" и "п/ф(техн.)" (коды "01" и "02") обозначают обучаемого на подготовительных факультетах (в вузе и техникуме), срок обучения которого не превышает одного года;

б) значения "студент" и "студент-заочник" (коды "03" и "04") обозначают обучаемого в высшем учебном заведении, срок обучения которого не превышает 5 или 6 лет;

в) значения "учащийся" и "бакалавр" (коды "05" и "17") обозначают учащегося либо среднего учебного заведения, либо высшего учебного заведения, срок обучения которого не превышает 4 или 5 лет;

г) значение "аспирант" (код "07") обозначает обучаемого в аспирантуре, срок обучения которого 3 года;

д) значение "аспирант-заочник" (код "08") обозначает обучаемого в аспирантуре, срок обучения которого 4 года;

е) значения "повышение квалификации" и "стажер" (код "10" и "11") обозначают обучаемого в высшем учебном заведении, срок обучения которого не превышает 1 или 2 года.

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

convert (integer, gok) - convert (integer, gp)

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

create table poss2

(nomer integer check (nomer between 1 and 700000),

fio char (40) not null check (fio not like '%.%' or fio not like '%-%'),

d_rogd char(2) check (d_rogd between '01' and '31'),

m_rogd char(2) check (m_rogd between '01' and '12'),

g_rogd char(2) check (g_rogd between '00' and '99'),

pol char(1) check (pol like 'M' or pol like 'Ж'),

sem_polog_k char(1) check (sem_polog_k in ('0', '1', '2', '3')),

spec char(6) check (spec between '000000' and '190046'),

kat_obuch_k char(2) check (kat_obuch_k between '01' and '17'),

vuz_k integer check (vuz_k between 128955 and 8199999),

mp char(2) check (mp between '01' and '12'),

gp char(2) check (gp between '00' and '99'),

mo char(2) check (mo between '01' and '12'),

gok char(2) check (gok between '00' and '99'),

constraint kat_obuch_const check

((kat_obuch_k in ('01', '02') and

(convert (integer, gok) - convert (integer, gp))=1) or

(kat_obuch_k in ('03', '04') and

(convert (integer, gok) - convert (integer, gp)) in (5, 6)) or

(kat_obuch_k in ('05', '17') and

(convert (integer, gok) - convert (integer, gp)) in (4, 5)) or

(kat_obuch_k='07' and (convert (integer, gok) -

convert (integer, gp))=3) or

(kat_obuch_k='08' and (convert(integer, gok) -

convert (integer, gp))=4) or

(kat_obuch_k in('10', '11') and

(convert (integer, gok) - convert (integer, gp)) in (1, 2)))) [F5]

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

Создание правила

Если контрольное ограничение на уровне поля не было создано в команде create table или созданное контрольное ограничение не совсем удовлетворяет владельца БД, то это ограничение можно создать с помощью команды create rule.

Например, контрольное ограничение для поля kat_obuch_k было задано с помощью функции between ‘01’ and ‘17’ в команде create table poss1, а в текущем сеансе работы владельцу БД понадобилось уточнить конкретные значения этого поля, например задать следующий список: ‘01’, ’02’, ’03’, ’04’, ’05’, ‘07’, ’08’, ’10’, ’11’, ’17’. Это делается с помощью следующей команды:

create rule rule1 as @kat_obuch in ('01', '02', '03', '04', '05', '07', '08', '10', '11', '17') [F5]

После создания правила rule1 (загрузки его в БД как объекта) необходимо связать его с таблицей БД poss1 с помощью процедуры sp_bindrule (см. конспект курса):

sp_bindrule 'rule1', 'poss1.[kat_obuch_k]'

Проверка действия правила осуществляется оператором insert (или update), т.е. при вводе записей в таблицу.

 

Задание:

1) Создать контрольные ограничения на уровне поля, описанные выше, для вашей информационной таблицы в вашей БД;

2) Создать следующие контрольные ограничения на уровне таблицы:

А) значение поля «Год рождения» меньше значения поля «Год приема», которое, в свою очередь, меньше значения поля «Год окончания»;

Б) если значение поля «Код категории обучения» равно ‘07’, ‘08’ или ‘09’ (аспирант, аспирант-заочник или ординатура), то первые два символа в значении поля «Специальность» больше ‘00’; в остальных случаях (по всем другим значениям поля «Код категории обучения») первые два символа значения поля «Специальность» равны ‘00’;

В) если значение поля «Пол» равно «Ж», то фамилия в поле «Фамилия Имя Отчество» должна заканчиваться на букву «А», иначе, если значение поля «Пол» равно «М», то фамилия в поле «Фамилия Имя Отчество» не должна заканчиваться на букву «А»;

3) Создать правило для вашей информационной таблицы, описанное выше.