Создание таблиц и индексов

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

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

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

Рисунок 5 - Окно Interactive SQL при создании таблицы группа и уникального индекса для данной таблицы.

Рисунок 6 - Таблица Студент

Выполнив данный код нажав комбинацию клавиш Ctrl+Enter мы получим таблицу, в которую затем мы можем внести записи. Однако стоит заметить т.к. между таблицей Student и Gruppa существует связь по полю Gruppa, то перед заполнением таблицы Student, необходимо заполнить соответствующие значения в таблицу Gruppa, иначе на экране появится сообщение об ошибке.

SQL коды для создания таблиц и индексов:

CREATE TABLE gruppa (

gruppa t_gruppa NOT NULL,

god_postuplenia varchar(20),

edvaizer VARCHAR(20)

);

CREATE UNIQUE INDEX XPKgruppa ON gruppa

(

gruppa

);

ALTER TABLE gruppa

ADD PRIMARY KEY (gruppa);

CREATE TABLE student (

kod_student INTEGER NOT NULL,

gruppa t_gruppa NOT NULL,

fio t_fio

);

CREATE UNIQUE INDEX XPKstudent ON student

(

kod_student

);

ALTER TABLE student

ADD PRIMARY KEY (kod_student);

CREATE TABLE Disciplina (

kod_disciplina INTEGER NOT NULL,

disciplina_p t_disciplina_p,

disciplina_s t_disciplina_s,

semestr INTEGER

);

CREATE UNIQUE INDEX XPKDisciplina ON Disciplina

(

kod_disciplina

);

ALTER TABLE Disciplina

ADD PRIMARY KEY (kod_disciplina);

CREATE TABLE specialnost (

shifr_specialnost VARCHAR(20) NOT NULL,

specialnost_p t_specialnost_p,

specialnost_s t_specialnost_s,

god_vvedenia varchar(20)

);

CREATE UNIQUE INDEX XPKspecialnost ON specialnost

(

shifr_specialnost

);

ALTER TABLE specialnost

ADD PRIMARY KEY (shifr_specialnost);

CREATE TABLE prepodavatel (

kod_prepod INTEGER NOT NULL,

fio t_fio NOT NULL,

doljnost t_doljnost NOT NULL

);

CREATE UNIQUE INDEX XPKprepodavatel ON prepodavatel

(

kod_prepod

);

ALTER TABLE prepodavatel

ADD PRIMARY KEY (kod_prepod);

CREATE TABLE vedomost (

shifr_specialnost VARCHAR(20) NOT NULL,

kod_prepod INTEGER,

kod_disciplina INTEGER NOT NULL,

kod_student INTEGER,

lc1 t_lc1,

lc2 t_lc2,

pr1 t_pr1,

pr2 t_pr2,

srs1 t_srs1,

srs2 t_srs2,

examen t_examen,

itog t_itog

);

CREATE UNIQUE INDEX XPKvedomost ON vedomost

(

shifr_specialnost,

kod_disciplina

);

ALTER TABLE vedomost

ADD PRIMARY KEY (shifr_specialnost, kod_disciplina);

CREATE TABLE uchebnii_plan (

kod_disciplina INTEGER NOT NULL,

shifr_specialnost VARCHAR(20) NOT NULL,

semestr t_semestr,

kol_vo_kreditov t_kol_vo_kreditov

);

CREATE UNIQUE INDEX XPKuchebnii_plan ON uchebnii_plan

(

kod_disciplina,

shifr_specialnost

);

ALTER TABLE uchebnii_plan

ADD PRIMARY KEY (kod_disciplina, shifr_specialnost);

ALTER TABLE student

ADD FOREIGN KEY (gruppa)

REFERENCES gruppa;

ALTER TABLE vedomost

ADD FOREIGN KEY (kod_prepod)

REFERENCES prepodavatel;

ALTER TABLE vedomost

ADD FOREIGN KEY (kod_student)

REFERENCES student;

ALTER TABLE vedomost

ADD FOREIGN KEY (shifr_specialnost)

REFERENCES specialnost;

ALTER TABLE vedomost

ADD FOREIGN KEY (kod_disciplina)

REFERENCES Disciplina;

ALTER TABLE uchebnii_plan

ADD FOREIGN KEY (shifr_specialnost)

REFERENCES specialnost;

ALTER TABLE uchebnii_plan

ADD FOREIGN KEY (kod_disciplina)

REFERENCES Disciplina;

Третий этап. Создав все объекты мы сможем увидеть их на дереве нашей базы (рис.7).

Затем необходимо автоматизировать уникальные значения таблиц, т.к. при работе с глобальной базой данных указать уникальное значение не всегда возможно. Так как вставкой записей могут заниматься одновременно несколько пользователей. Угадать последнее вставленное значение уникального значения практически невозможно, так что необходимо как-то автоматизировать этот процесс. С этой целью в базе данных предусмотрены специальные объекты – генераторы.

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

Рисунок 7 - Окно IBConsole после создания таблиц

Рассмотрим установку генератора для таблицы Student(Студент), где уникальным является kod_student (номер студента). Создадим генератор, возвращающий уникальное значение для поля kod_student в таблице STUDENT и установим стартовое значение равное 1. Для этого введите в окно запросов ISQL следующее:

CREATE GENERATOR kod_student_gen

Рисунок 8 - Окно IBConsole после создания генераторов.

Теперь присвоим нашему генератору начальное значение. Так как в таблице STUDENT уже есть записи, то нужно инициализировать генератор наибольшим имеющимся в базе значением поля kod_student. Посмотреть его можно, набрав в окне ISQL запрос

select MAX(kod_student) from STUDENT

В окне результатов появится примерно следующее:

MAX

===

Значение поля равно 4 так как мы вручную внесли уже четыре записи. По умолчанию у созданного генератора значение равно 0. Наберите и выполните запрос:

SET GENERATOR kod_student_gen TO 4

Теперь текущее значение генератора равно 4. Функция gen_id() увеличивает значение генератора на заданное число и возвращает полученное значение.

С помощью генератора kod_student_gen автоматизировано получение уникального числового значения для ключевого поля kod_student, однако при каждой вставке новой записи приходится вызывать функцию gen_id(kod_student_gen,1). Сервер позволяет автоматизировать и эту задачу c помощью механизма, который называется триггером.

Триггер - это некоторая процедура, обрабатываемая автоматически SQL-сервером при возникновении определенного события в базе данных - вставки, удаления и изменения записи. Непосредственно из программы к триггерам обратиться нельзя. Нельзя и передавать им входные параметры и получать от них значения выходных параметров. Триггеры всегда реализуют действие. Триггер связан с определенной таблицей базы и никогда не вызывается пользователем напрямую. Вместо этого он срабатывает (еще говорят - «стреляет») при попытке выполнить действие, заданное в его определении.

По событию изменения таблицы БД триггеры различаются на вызываемые при:

¨ добавлении новой записи

¨ изменении существующей записи

¨ удалении записи

По отношению к событию, влекущему их вызов, триггеры различаются на:

· Выполняемые до наступления события

· Выполняемые после наступления события

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

· Изменения в триггерах не влекут необходимости изменения программного кода в клиентских приложениях и не требует распространения новых версий клиентских приложений у пользователей [8].

При операциях вставки, удаления и изменения записи в теле триггера можно обращаться к значениям полей текущей записи при помощи специальных переменных NEW и OLD, например, узнать значение поля first_name при вставке записи можно, воспользовавшись выражением NEW.kod_student. Создадим для таблицы STUDENT триггер, который будет подставлять в поле kod_student очередное уникальное значение. Введите в окне ISQL и выполните следующий запрос:

SET TERM!!;

CREATE TRIGGER kod_student_gen FOR STUDENT

BEFORE INSERT AS

BEGIN

new.kod_student=gen_id(kod_student_gen,1);

END!!

SET TERM;!!

Этот триггер будет срабатывать перед вставкой записи в таблицу STUDENT, присваивая полю kod_student уникальное значение, полученное от генератора
kod_student_gen:

new.kod_student=gen_id(kod_student_gen,1);

Затем получим еще два генератора и два триггера для таблиц PREPODAVATEL и DISCIPLINA, чтобы автоматизировать вставку новых записей. Ниже представлены SQL коды для реализации этих задач:

Для таблицы PREPODAVATEL

CREATE GENERATOR kod_PREPOD_gen

select MAX(kod_PREPOD) from prepodavatel

SET GENERATOR kod_prepod_gen TO 1

SET TERM!!;

CREATE TRIGGER kod_prepod_gen FOR PREPODAVATEL

BEFORE INSERT AS

BEGIN

new.kod_prepod=gen_id(kod_prepod_gen,1);

END!!

SET TERM;!!

Для таблицы DISCIPLINA