В этом посте разберем, как сделать контрольную работу по базам данных с использованием СУБД InterBase ( на примере одного варианта - базы данных пункта видеопроката) и программы IBExpert.
Покажем, как создать и зарегистрировать базу данных, создать ее компоненты – домены и таблицы, составить запросы и хранимые процедуры. Ну и заодно поработаем с языком SQL.
Само задание состоит в следующем. Нужно создать базу данных «Видеопрокат». Состоять она должна из двух таблиц – «Картотека» и «Клиенты». Таблица «Картотека» состоит из полей: Идент. номер фильма (ключевое поле), название фильма, режиссер, жанр (боевик, мелодрама, комедия) Таблица «Клиенты»: номер по порядку (ключевое поле), ФИО клиента, ИН фильма, дата выдачи. [минимум 5 записей]. Для таблицы «Клиенты» для реализации ссылочной целостности необходимо построить внешний ключ по полю «ИН фильма». Для поля «жанр» таблицы «Картотека» создать свой домен с ограничение на ввод значений (боевик, мелодрама, комедия).
Нужно создать просмотр, содержащий поля: название фильма, ФИО клиента, дата выдачи, а также хранимую процедуру, которая:
Покажем, как создать и зарегистрировать базу данных, создать ее компоненты – домены и таблицы, составить запросы и хранимые процедуры. Ну и заодно поработаем с языком SQL.
Само задание состоит в следующем. Нужно создать базу данных «Видеопрокат». Состоять она должна из двух таблиц – «Картотека» и «Клиенты». Таблица «Картотека» состоит из полей: Идент. номер фильма (ключевое поле), название фильма, режиссер, жанр (боевик, мелодрама, комедия) Таблица «Клиенты»: номер по порядку (ключевое поле), ФИО клиента, ИН фильма, дата выдачи. [минимум 5 записей]. Для таблицы «Клиенты» для реализации ссылочной целостности необходимо построить внешний ключ по полю «ИН фильма». Для поля «жанр» таблицы «Картотека» создать свой домен с ограничение на ввод значений (боевик, мелодрама, комедия).
Нужно создать просмотр, содержащий поля: название фильма, ФИО клиента, дата выдачи, а также хранимую процедуру, которая:
- возвращает название фильмов, указанного жанра по названию фильма
- возвращает жанр и ФИО клиента
- возвращает название фильма, взятого последним
1. Создаем базу данных и ее компоненты
Базу данных можно создать несколькими способами:
– с помощью isql (в диалоговом режиме);
- с помощью инструмента Create Database, входящего в программу IBExpert;
- в средах программирования, например, в Delphi, C++Builder есть соответствующие компоненты с функциями создания базы;
- с помощью скриптов.
Мы будем использовать метод создания БД с помощью скрипта. Это наиболее универсальный и быстрый способ. В дальнейшем для создания базы достаточно будет запустить скрипт из IBExpert или IBConsol, предварительно поменяв место расположения создаваемой базы.
Итак, запускаем IBExpert. Для написания скрипта воспользуемся инструментом Script Executive. Вводим следующий текст:
Базу данных можно создать несколькими способами:
– с помощью isql (в диалоговом режиме);
- с помощью инструмента Create Database, входящего в программу IBExpert;
- в средах программирования, например, в Delphi, C++Builder есть соответствующие компоненты с функциями создания базы;
- с помощью скриптов.
Мы будем использовать метод создания БД с помощью скрипта. Это наиболее универсальный и быстрый способ. В дальнейшем для создания базы достаточно будет запустить скрипт из IBExpert или IBConsol, предварительно поменяв место расположения создаваемой базы.
Итак, запускаем IBExpert. Для написания скрипта воспользуемся инструментом Script Executive. Вводим следующий текст:
Здесь заданы диалект базы данных, название и директорию (имя БД может иметь любое расширение, принято для Firebird использовать .fdb, для InterBase 6.x и ранее – .gdb, для InterBase с версии 7 - .ib), задали пользователя и его пароль и установили набор символов по умолчанию – WIN1251.
Базу данных можно создавать на удаленном компьютере. Для этого в строку с указанием пути нужно добавить его сетевое имя.
Запускаем скрипт командой Run (зеленый треугольник). Если все прошло удачно, то программа выдаст сообщение об успешном создании базы данных:
Базу данных можно создавать на удаленном компьютере. Для этого в строку с указанием пути нужно добавить его сетевое имя.
Запускаем скрипт командой Run (зеленый треугольник). Если все прошло удачно, то программа выдаст сообщение об успешном создании базы данных:
Далее для комфортной работы в IBExpert с нашей базой данных ее нужно зарегистрировать. Для этого выбираем пункт меню Database->Register Database. В поля ввода вносим все необходимые данные и нажимаем на кнопку Register.
База данных video появилась в списке зарегистрированных баз данных в Database Explorer.
Далее перейдем к созданию доменов. Домен – это один из объектов реляционной базы данных, при создании которого можно создать некоторые характеристики, а затем использовать ссылку на домен при определении столбцов таблиц. Один и тот же домен может использоваться в определении любого количества столбцов в таблицах базы данных. Создадим несколько доменов для нашей базы данных и запишем их в отдельный скрипт после запуска.
Перед созданием доменов нужно подключиться к базе данных. Это сделано в первых четырех строках скрипта.
Для домена, который будет определять столбец с названием жанра, установлено ограничение check – возможны только три значения (боевик, мелодрама, комедия). Сохраняем и запускаем скрипт.
Создаем две таблицы – «Картотека» и «Клиенты».
Листинг скрипта:
К каждой таблице создаем генераторы. При добавлении строк генератор будет увеличивать код фильма и клиента на 1. Это сделано, чтобы вручную не рассчитывать каждый раз новое значение. Получить значение генератора можно с помощью функции GEN_ID().
Программно выполняем добавление данных в таблицы.
Листинг скрипта добавления записей:
На этом заканчиваем создание компонентов и перейти к формированию запросов и хранимых процедур
2. Запросы и хранимые процедуры
По заданию нужно сформировать запрос, выдающий следующую информацию: название фильма, ФИО клиента, дата выдачи. Запрос будем создавать в IBExpert в SQL Editor.
Создаем запрос для просмотра выданных фильмов. Сортируем по названию фильма
В этом запросе мы делаем выборку фильмов, но только тех, которые выданы, то есть их коды есть в таблице клиентов.
Результаты запроса:
В Interbase, кроме описательных (декларативных) средств, есть еще и императивные, выполняемые средства. Это языковые средства, используемые в хранимых процедурах и триггерах.
Для написания хранимых процедур и триггеров используется расширение языка SQL, язык хранимых процедур и триггеров.
Хранимые процедуры являются программами. Они хранятся в области метаданных базы данных в системных таблицах и выполняются на стороне сервера, что во многих случаях может сильно сократить сетевой трафик. К хранимым процедурам могут обращаться любые программы, работающие с базой данных – хранимые процедуры, триггеры, клиентские приложения. Хранимые процедуры бывают двух видов – выполняемые и выбора. Они могут получать входные параметры и возвращать выходные значения вызвавшему их компоненту.
Создадим хранимую процедуру, которая возвращает названия фильмов указанного жанра.
После выполнения этого кода в базе данных будет создана хранимая процедура PROC_NAME_FILM. К ней можно обратиться, например, из запроса:
Результат выполнения запроса
Еще одна хранимая процедура должна по названию фильма возвращать его жанр и имя клиента, которому этот фильм выдан. Для получения необходимой информации создадим в процедуре левое внешнее объединение. В результате из таблицы фильмов будут выбраны жанры тех фильмов, чье название в точности совпадает с запрошенным.
Хранимая процедура 2:
Пишем запрос на выборку результата процедуры.
Результат
Но каждый раз набирать в точности полное название фильма не очень удобно, поэтому изменим процедуру, чтобы поиск проводился, если найдено не точное соответствие, а указанные символы. Сделаем это с помощью оператора LIKE в условии выборки WHERE.
Если исключить в запросе невыданные фильмы,
то получится следующий результат
Если убрать условие невыданных фильмов
Еще один вариант изменения условия – привести параметры поиска к верхнему регистру, тогда будет некритично, если первая буква в параметре запроса строчная, а не прописная, тогда как в базе хранится название с прописной буквы.
Третья хранимая процедура должна возвращать название фильма (или фильмов), взятого последним. На этом примере покажем использование переменных в хранимых процедурах. Используем две выборки. В первой будем искать максимальную (то есть последнюю) дату выдачи фильма. Это можно выполнить, используя условие MAX. Затем присваиваем это значение переменной DateV (объявленной с помощью Declare variable) и в следующей выборке она будет служить условием отбора в выражении WHERE после левого объединения таблиц CLIENTS и KARTOTEKA.
Текст создания процедуры в базе данных:
Обратимся к этой процедуре с помощью запроса:
Результат выполнения:
Вот и все, все требования реализовали. создана база данных «Видеопрокат». Ее можно расширять, добавлять запросы, триггеры и хранимые процедуры. Конечно, работать с ней простому пользователю неудобно, поэтому нужно писать программу для пользовательского интерфейса. Но это уже другое задание.
База данных video появилась в списке зарегистрированных баз данных в Database Explorer.
Далее перейдем к созданию доменов. Домен – это один из объектов реляционной базы данных, при создании которого можно создать некоторые характеристики, а затем использовать ссылку на домен при определении столбцов таблиц. Один и тот же домен может использоваться в определении любого количества столбцов в таблицах базы данных. Создадим несколько доменов для нашей базы данных и запишем их в отдельный скрипт после запуска.
Для домена, который будет определять столбец с названием жанра, установлено ограничение check – возможны только три значения (боевик, мелодрама, комедия). Сохраняем и запускаем скрипт.
Создаем две таблицы – «Картотека» и «Клиенты».
Листинг скрипта:
/*----------------------------------------------------------*/Устанавливаем имена столбцов, их домены. Прописываем первичные (primary key) и внешние (foreign key) ключи. Прописываем условия ссылочной целостности – при удалении фильма из картотеки код фильма у клиента будет устанавливаться в NULL, при обновлении будет обновляться.
set sql dialect3;
SET NAMES WIN1251;
CONNECT 'J:\Bases\VIDEO.GDB'
USER 'SYSDBA' PASSWORD 'masterkey';
/* Картотека*/
create table KARTOTEKA
(
ID_FILM d_integer not null, /* код фильма */
NAME_FILM d_char50, /* название фильма */
FIO_REG d_char30, /* фИО режиссера */
GANR d_char_GANR, /* жанр фильма */
constraint PK_Film primary key (ID_FILM)
);
create generator GEN_FILM; /* генератор */
commit;
/* Клиенты*/
create table CLIENTS
(
ID_CLIENT d_integer not null, /* код клиента */
FIO_CLIENT d_char30, /* ФИО клиента */
DATE_V d_date, /* дата выдачи */
ID_FILM d_integer, /* код фильма */
constraint PK_CLIENT primary key (ID_CLIENT),
constraint FK_CLIENT_FILM foreign key (ID_FILM) references KARTOTEKA (ID_FILM)
on delete set null
on update cascade
);
create generator GEN_CLIENT;
commit;
exit;
/*----------------------------------------------------------*/
К каждой таблице создаем генераторы. При добавлении строк генератор будет увеличивать код фильма и клиента на 1. Это сделано, чтобы вручную не рассчитывать каждый раз новое значение. Получить значение генератора можно с помощью функции GEN_ID().
Программно выполняем добавление данных в таблицы.
Листинг скрипта добавления записей:
/*--------------------------------------------------------------------*/
set sql dialect3;
SET NAMES WIN1251;
CONNECT 'J:\Bases\VIDEO.GDB'
USER 'SYSDBA' PASSWORD 'masterkey';
/*Добавляем фильмы*/
INSERT INTO KARTOTEKA(ID_FILM, NAME_FILM, FIO_REG, GANR)
values (GEN_ID(GEN_FILM, 1), 'Бой с тенью 3D: Последний раунд', 'Алексей Сидоров', 'боевик');
commit;
INSERT INTO KARTOTEKA(ID_FILM, NAME_FILM, FIO_REG, GANR)
values (GEN_ID(GEN_FILM, 1), 'Исходный код', 'Дункан Джонс', 'боевик');
commit;
/*Ну и так далее, добавляем другие фильмы*/
/*Добавляем клиентов*/
INSERT INTO CLIENTS(ID_CLIENT, FIO_CLIENT, DATE_V, ID_FILM)
values (GEN_ID(GEN_CLIENT, 1), 'Иванов А.А.', null, NULL);
commit;
/*Ну и так далее, добавляем других клиентов*/
exit;
/*---------------------------------------------------------------------*/
На этом заканчиваем создание компонентов и перейти к формированию запросов и хранимых процедур
2. Запросы и хранимые процедуры
По заданию нужно сформировать запрос, выдающий следующую информацию: название фильма, ФИО клиента, дата выдачи. Запрос будем создавать в IBExpert в SQL Editor.
Создаем запрос для просмотра выданных фильмов. Сортируем по названию фильма
В этом запросе мы делаем выборку фильмов, но только тех, которые выданы, то есть их коды есть в таблице клиентов.
Результаты запроса:
В Interbase, кроме описательных (декларативных) средств, есть еще и императивные, выполняемые средства. Это языковые средства, используемые в хранимых процедурах и триггерах.
Для написания хранимых процедур и триггеров используется расширение языка SQL, язык хранимых процедур и триггеров.
Хранимые процедуры являются программами. Они хранятся в области метаданных базы данных в системных таблицах и выполняются на стороне сервера, что во многих случаях может сильно сократить сетевой трафик. К хранимым процедурам могут обращаться любые программы, работающие с базой данных – хранимые процедуры, триггеры, клиентские приложения. Хранимые процедуры бывают двух видов – выполняемые и выбора. Они могут получать входные параметры и возвращать выходные значения вызвавшему их компоненту.
Создадим хранимую процедуру, которая возвращает названия фильмов указанного жанра.
После выполнения этого кода в базе данных будет создана хранимая процедура PROC_NAME_FILM. К ней можно обратиться, например, из запроса:
Результат выполнения запроса
Еще одна хранимая процедура должна по названию фильма возвращать его жанр и имя клиента, которому этот фильм выдан. Для получения необходимой информации создадим в процедуре левое внешнее объединение. В результате из таблицы фильмов будут выбраны жанры тех фильмов, чье название в точности совпадает с запрошенным.
Хранимая процедура 2:
Пишем запрос на выборку результата процедуры.
Результат
Но каждый раз набирать в точности полное название фильма не очень удобно, поэтому изменим процедуру, чтобы поиск проводился, если найдено не точное соответствие, а указанные символы. Сделаем это с помощью оператора LIKE в условии выборки WHERE.
Если исключить в запросе невыданные фильмы,
то получится следующий результат
Если убрать условие невыданных фильмов
Еще один вариант изменения условия – привести параметры поиска к верхнему регистру, тогда будет некритично, если первая буква в параметре запроса строчная, а не прописная, тогда как в базе хранится название с прописной буквы.
Третья хранимая процедура должна возвращать название фильма (или фильмов), взятого последним. На этом примере покажем использование переменных в хранимых процедурах. Используем две выборки. В первой будем искать максимальную (то есть последнюю) дату выдачи фильма. Это можно выполнить, используя условие MAX. Затем присваиваем это значение переменной DateV (объявленной с помощью Declare variable) и в следующей выборке она будет служить условием отбора в выражении WHERE после левого объединения таблиц CLIENTS и KARTOTEKA.
Текст создания процедуры в базе данных:
/*-----------------------------------------------------------------*/
set sql dialect3;
SET NAMES WIN1251;
CONNECT 'J:\Bases\VIDEO.GDB'
USER 'SYSDBA' PASSWORD 'masterkey';
create procedure PROC_Last_Film
returns (name_film varchar(50), date_v date)
AS
declare variable dateV date;
BEGIN
for SELECT MAX(Date_V)
FROM Clients
INTO :dateV
DO
begin
select Name_Film, date_V
from clients
left outer join kartoteka
on clients.id_film = kartoteka.id_film
where clients.date_v = :dateV
into :name_film, :date_v;
end
suspend;
end
/*------------------------------------------------------------------*/
Обратимся к этой процедуре с помощью запроса:
Результат выполнения:
Вот и все, все требования реализовали. создана база данных «Видеопрокат». Ее можно расширять, добавлять запросы, триггеры и хранимые процедуры. Конечно, работать с ней простому пользователю неудобно, поэтому нужно писать программу для пользовательского интерфейса. Но это уже другое задание.