close

Вход

Забыли?

вход по аккаунту

?

Brzsovskiy

код для вставкиСкачать
1
МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ
Федеральное государственное автономное образовательное учреждение высшего
образования "Санкт-Петербургский государственный университет аэрокосмического приборостроения"
___________________________________________________________________________
БАЗЫ ДАННЫХ
Учебное пособие
(Draft 0.5)
Санкт-Петербург
2015 г.
2
Составитель: Бржезовский А. В.
Рецензент:
В учебное пособие включены описания основных конструкций языка SQL (Structured
Query Language), обеспечивающих создание и ведение баз данных, формирование запросов к
базам данных. Рассмотрены управляющие конструкции SQL, возможности по обработке
данных посредством написания хранимых процедур и обеспечения активной целостности с
помощью триггеров.
В пособии рассмотрены также вопросы и средства для повышения производительности
SQL-запросов, решения проблем многопользовательского доступа к данным, организации
репликации в распределенных базах данных, создания хранилищ данных аналитических систем.
Синтаксические конструкции рассмотрены на примере диалекта Transact-SQL, реализованного в Microsoft SQL Server 2008, они могут иметь незначительные отличия от диалектов других SQL-серверов и других версий Microsoft SQL Server.
Учебное пособие предназначено для студентов, обучающихся по направлениям:
— 23100062Ф Программная инженерия;
— 01050062Ф Математическое обеспечение и администрирование информационных
систем;
— 01040062Ф Прикладная математика и информатика.
Учебное пособие подготовлено кафедрой компьютерных технологий и программной
инженерии и рекомендовано к изданию редакционно-издательским советом СанктПетербургского государственного университета аэрокосмического приборостроения.
3
1.
Создание таблиц базы данных
1.1
Базы данных и СУБД
Реляционная база данных (БД) или Data Base (DB) – это совокупность связанных таблиц для хранения информации об объектах (процессах, явлениях, фактах) некоторой предметной области. Для разработки баз данных и программных приложений, работающих с базами данных, часто используется язык SQL (Structured Query Language). Интерпретацию инструкций, формулируемых на языке SQL, хранение данных, обеспечение многопользовательской работы с данными, защиту, резервное копирование, восстановление данных и реализацию многих других функций обеспечивает специальный класс программных продуктов,
получивший название систем управления базами данных (СУБД) или Data Base Management
System (DBMS).
В настоящее время в связи с активным использованием систем, построенных в архитектуре клиент-сервер, в качестве синонима термина СУБД, часто используется термин SQLсервер. Примерами таких продуктов могут служить Oracle Database Server, SAP Sybase Adaptive Server Enterprise (ASE), SAP Sybase SQL Anywhere (SQLA), Microsoft SQL Server (MS
SQL), IBM DB2 Data Server (DB2) и др. Данные продукты относятся к классу RDBMS (Relational DBMS) или ORDBMS (Object-Relational DBMS). Как правило, в состав любой СУБД
или SQL-сервера входит приложение для администрирования баз данных и приложение,
обеспечивающее выполнение запросов к БД, в случае MS SQL эти возможности совмещены
в среде SQL Server Management Studio, далее по тексу – Management Studio. Для работы с БД
данное приложение должно быть запущено (Пуск\Все программы\Microsoft SQL Server
2008\Среда SQL Server Management Studio) и соединены (Соединить) с SQL-сервером
(Компонент Database Engine\(local)\Проверка подлинности Windows).
Для создания БД в окне, открываемом кнопкой Создать запрос, необходимо выполнить (кнопка ! Выполнить) оператор языка SQL:
create database <имя БД>1
go
например:
1
Скобки < > — указывают на необходимость подстановки синтаксической конструкции, в данном случае —
идентификатора.
4
use master
go
create database Университет
go
use Университет
go
В данном случае имя БД – Университет (имя БД должно быть уникально в рамках
сервера), оператор use задает БД, используемую по умолчанию, master – имя системной БД,
создаваемой автоматически при установке MS SQL. Созданная база данных появляется в дереве объектов (окно Обозреватель объектов) после обновления узла Базы данных (команда
контекстного меню Обновить). Удаление БД реализует оператор drop database, имеющий
аналогичный синтаксис.
Альтернативный способ создания БД — команда Создать базу данных…, вызываемая
из контекстного меню узла Базы данных дерева объектов, альтернативный способ удаления
БД — команда Удалить, вызываемая из контекстного меню узла, соответствующего удаляемой БД в дереве объектов.
1.2
Таблицы
Для сохранения информации в БД необходимо создать одну или несколько таблиц. Для
создания таблицы нужно выполнить оператор языка SQL create table, его упрошенный синтаксис приведен ниже:
create table <имя таблицы> (
<имя столбца 1> <тип 1>
[ , <имя столбца 2> <тип 2>
[ , … ] ]1 )
go
Альтернативный способ создания таблицы — команда Создать таблицу…, вызываемая из контекстного меню узла Таблицы дерева объектов, подчиненного узлу, соответствующему БД, в которой создается таблица. Созданная база данных появляется в дереве объектов (окно Обозреватель объектов) после обновления узла Таблицы (команда контекстного
меню Обновить).
Основные типы данных языка SQL (на примере диалекта Transact-SQL Microsoft SQL
Server 2008) приведены в таблице 1.1.
1
Скобки [ ] — указывают, что синтаксическая конструкция может быть опущена, т. е. таблица может состоять
из одного или нескольких столбцов.
5
Таблица 1.1.
Типы данных SQL
Тип
Форма записи
Размерность
Пример
char[(n)] или
character[(n)]
Последовательность символов длины
n, заключенных в знаки апострофа, если n опущено – 1 символ; для хранения
данных всегда отводится поле в n символов
1 .. 8000
‘char’
varchar[(n)]
см. char[(n)]; для хранения данных отводится поле длины, соответствующей
числу фактически заданных символов
1 .. 8000
‘varchar’
Последовательность символов переменной длины большой размерности
2^31–1
‘text’
То же, что и char в формате Unicode
1 .. 4000
‘nchar’
То же, что и varchar в формате Unicode
1 .. 4000
‘nvarchar’
То же, что и text в формате Unicode
2^30–1
‘ntext’
text1 или
varchar(max)
nchar[(n)]
nvarchar[(n)]
ntext или
(2 ГБ)
nvarchar(max)
bit
(2 ГБ)
0 или 1
0 .. 1
0
tinyint
Целое без знака
0 .. 255
11
smallint
Целое со знаком
–2^15 ..
2^15–1
–32233
int
Целое со знаком
–2^31 ..
2^31–1
2111222333
bigint
Целое со знаком
–2^63 ..
2^63–1
2111222333444
–12.44
decimal[(p[,s])]
или
numeric[(p[,s])]
Вещественное с фиксированной точкой (всего p значащих цифр, s значащих цифр после «.», по умолчанию s =
0)
–10^38+1..
real
Вещественное с плавающей точкой,
для хранения отводится 4 байта, эквивалентно float(24)
–3,40E+38..
10^38-1
–1,18E–38,0
и 1,18E–38..
3,40E+38
1
Сохранен для совместимости с предыдущими версиями, не рекомендован к использованию.
3.3E–5
6
float[(n)]
Вещественное с плавающей точкой,
для хранения отводится 4 байта (7
цифр), если n в диапазоне 1..24, 8 байт
(15 цифр), если n – 25..53
–1,79E+308..
–3.3E5
–2,23E–308,0
и 2,23E–308..
–1,79E+308
smallmoney
money
Вещественное с фиксированной точкой c 4-мя знаками после «.»
–214748,3648
..214748,3647
примерно
–55.3333
33.4455
–900..+900
триллионов
Дата и время с точностью до секунд,
дата от времени отделяется пробелом,
разделителями в поле даты служат «–»
или «/»,разделителем в поле времени –
«:»,
01/01/1900..
06/06/2079
'2000-05-08
12:35:29'
Дата и время с точностью до микросекунд, значения микросекунд от времени отделяются символом «.»
01/01/1753 ..
31/12/9999
'2000-05-08
12:35:29.999'
date
Дата
01/01/0001 ..
31/12/9999
'2000-05-08
12:35:29.999'
time
Время с точностью до наносекунд
00:00:00 ..
23:59:59
'10:10:10.99999
99'
smalldatetime
datetime
datetime2
datetimeoffset
Комбинация date и time
Комбинация date и time с указанием
смещения часового пояса
'2007/05/08
12:35:29.12345
67 +12:15'
Двоичные данные фиксированной
длины, если n опущено – 1 байт
1 .. 8000
—
varbinary[(n)]
Двоичные данные переменной длины,
если n опущено – 1 байт
1 .. 8000
—
image1 или
varbinary(max)
Двоичные данные переменной длины
2^31–1
—
timestamp1 или
Реализуемый на основе binary(8) автоматически формируемый код версии
binary[(n)]
1
Вещественное с фиксированной точкой c 4-мя знаками после «.»
(2 ГБ)
—
Сохранен для совместимости с предыдущими версиями, не рекомендован к использованию.
—
7
rowversion
строки
uniqueidentifier
16-байтовый идентификатор GUID.
—
—
xml
Синтаксически правильный XMLдокумент.
2^31–1
—
(2 ГБ)
Ниже приведен пример создания таблицы Студент (Ном_зач – номер зачетной книжки):
create table Студент (
Ном_Зач int,
ФИО varchar(50),
Группа char(5) )
go
Для удаления таблицы служит оператор drop table <имя таблицы>. Альтернативный
способ удаления таблицы — команда Удалить, вызываемая из контекстного меню узла, соответствующего удаляемой таблице в дереве объектов.
В определении столбцов таблицы можно помимо задания типа данных указывать директивы:
(i) null или not null — разрешение/запрет NULL-значений (NULL – специальная константа языка SQL, показывающая, что при вставке данных в таблицу не было задано значение соответствующего столбца);
(ii) default <значение по умолчанию>;
(iii) check <условие проверки>;
(iv) unique – требование, что бы все значения в столбце были различны;
(v) primary key – определение первичного ключа для однозначной идентификации записей в таблице.
Использование данных директив иллюстрирует пример, приведенный ниже (БД не может содержать двух одноименных таблиц, предыдущая версия таблицы Студент должна
быть удалена директивой drop table):
1
Отличается от типа данных timestamp, определенного в стандарте ISO. Сохранен для совместимости с предыдущими версиями, не рекомендован к использованию.
8
create table Студент (
Ном_Зач int primary key,
ФИО varchar(50) not null,
Сер_Ном_Пасп char(12) not null unique,
Гражданство varchar(50) default 'Российская Федерация',
Адрес varchar(250) null,
Факультет char(1) not null check (Факультет in ('1', '2', '3', '4')),
Группа char(5) not null )
go
В примере столбец Ном_Зач является первичным ключом таблицы, директива primary
key автоматически влечет ограничения not null (значение первичного ключа не может быть
неопределенным) и unique (все значения в столбцах первичного ключа должны быть различны). Столбцы ФИО и Группа являются обязательными (не содержат NULL-значений), столбец Адрес может не заполняться (допускает NULL-значения). Серия и номер паспорта студента (Сер_Ном_Пасп) обязательный столбец, кроме того, все значения в нем должны быть
различны. Столбец Гражданство является необязательным, но для него определено значение по умолчанию, которое будет подставляться, если соответствующее значение не указано
при вставке данных в таблицу. Для столбца Факультет задано условие проверки – значение
должно соответствовать одному из четырех элементов множества (оператор in возвращает
логическое значение И или Л, в зависимости от того принадлежит или не принадлежит элемент множеству). В общем случае в директиве check может задаваться логическое условие, в
котором используются имя столбца, константы, операции сравнения (>, >=, < …), логические
операции (and, or, not), рассмотренный выше оператор in, скобки.
СУБД будет отклонять попытки добавления записей в таблицы, если добавляемые значения нарушают ограничения столбцов, определенные в create table. Директивы primary key,
unique, check могут задаваться как на уровне столбца (в определении столбца), так и на
уровне таблицы (в конце create table), в последнем случае они обеспечивают возможности
создания составных ключей (включающих более одного столбца), проверки уникальности
сочетания значений различных столбцов, совместную проверку значений различных столбцов таблицы.
В приведенном ниже примере:
9
create table Студент (
Ном_Зач int primary key,
ФИО varchar(50) not null,
Сер_Пасп char(5) not null,
Ном_Пасп char(6) not null,
Факультет char(1) not null check (Факультет in ('1', '2', '3', '4')),
Группа char(5) not null,
unique (Сер_Пасп, Ном_Пасп),
check (Факультет = left(Группа,1)) )
go
директива unique использована для обеспечения уникальности значений столбцов
Сер_Пасп и Ном_Пасп, таким образом, серии и номера паспортов, хранимые в отдельных
столбцах таблицы, могут совпадать у различных студентов, но их сочетание должно быть
уникальным в каждой из записей. Для полей Факультет и Группа проверяется, что бы первый символ в столбце Группа совпадал с номером факультета.
Составные первичные ключи используются, когда в таблице нет столбца, значения которого обеспечивают уникальную идентификацию строк, примером такой таблицы может
служить:
create table Оценка (
Ном_Зач int not null,
Дисциплина varchar(50) not null,
Оценка tinyint not null,
primary key (Ном_Зач, Дисциплина) )
go
Студент получает оценки по разным дисциплинам, по одной дисциплине получают
оценки много студентов, таким образом, только сочетание значений в столбцах Ном_Зач и
Дисциплина позволяет произвести однозначную идентификацию записи.
Приведенный пример иллюстрирует возможность использования составного ключа,
вместе с тем, при создании таблиц следует стремиться к тому, что бы ключи были как можно
короче, поэтому использование столбца с типом данных varchar(50) в качестве ключевого не
является удачным. В таких случаях вводят специальный (системный, «суррогатный») ключ.
Так как значения данных ключей не имеет смысла с точки зрения предметной области и используются только в целях идентификации записей, организации ссылок, современные
СУБД предлагают механизм для их автоматического формирования. В MS SQL для этих целей реализована директива identity [ (<начальное значение> [ , <приращение> ] ) ], используемая в сочетании с типом данных int. Если <начальное значение> и <приращение> опущены, в качестве их значений используется 1. Примером автоматического формирования
10
ключа в таблице Оценка может служить следующий (предыдущая версия таблицы Оценка
должна быть удалена директивой drop table):
create table Оценка (
Id int identity primary key,
Ном_Зач int not null,
Дисциплина varchar(50) not null,
Оценка tinyint not null )
go
1.3
Ссылочная целостность
Большинство современных СУБД поддерживает декларативную ссылочную целостность в базе данных для обеспечения корректности ссылок между таблицами БД. Ссылочная
целостность определяется посредством создания внешних ключей директивой foreign key (
<столбец 1 ДТ> [ , <столбец 2 ДТ> [ , … ] ] ) references <родительская таблица> (
<столбец 1 РТ> [ , <столбец 2 РТ> [ , … ] ] ) в конце оператора create table. Здесь <столбец
i ДТ> – столбец дочерней таблицы (таблицы, из которой осуществляется ссылка), <столбец i
РТ> – столбец родительской таблицы (таблицы, на которую осуществляется ссылка). Кандидатом на внешний ключ в таблице Оценка является Ном_Зач:
create table Оценка (
Id int identity primary key,
Ном_Зач int not null,
Дисциплина varchar(50) not null,
Оценка tinyint not null,
foreign key (Ном_Зач) references Студент (Ном_Зач) )
go
Если имена столбцов в дочерней таблице совпадают с именами столбцов в родительской, 1-й список столбцов может быть опущен, если внешний ключ является простым, директива может быть указана в сокращенном виде непосредственно в определении столбца
таблицы:
create table Оценка (
Id int identity primary key,
Ном_Зач int not null references Студент,
Дисциплина varchar(50) not null,
Оценка tinyint not null)
go
При вставке данных в таблицу, а так же изменении данных, СУБД автоматически контролирует наличие значения внешнего ключа в поле родительской таблицы, на которое осуществляется ссылка. Если данное значение отсутствует – операция будет отклонена.
11
Для записей родительской и дочерней таблицы можно задать правила совместной обработки по отношению к операциям удаления и изменения, определив запрет выполнения
операции, каскадирование, установку NULL значения или значения по умолчанию – on { update | delete } { no action1 | cascade | set null | set default }2. В таблице:
create table Оценка (
Id int identity primary key,
Ном_Зач int not null,
Дисциплина varchar(50) not null,
Оценка tinyint not null,
foreign key (Ном_Зач) references Студент (Ном_Зач)
on delete no action
on update cascade )
go
ограничение ссылочной целостности запрещает удаление записи в таблице Студент,
если на соответствующую запись есть ссылки из таблицы Оценка, и требует автоматической
модификации значений в столбце Ном_Зач таблицы Оценка, при изменении соответствующих значений в таблице Студент.
1.4
Лабораторная работа 1
По аналогии с примерами, приведенными в п. 1.1 – 1.3, произвести создание БД и таблиц, определив правила проверки значений и задав ограничения ссылочной целостности.
Структура БД должна обеспечивать хранение сведений, необходимых для выполнения запросов, указанных в варианте задания.
Содержание отчета:
— схема БД;
— скрипт SQL для создания таблиц;
— описание структуры таблиц, ограничений на значения данных, ссылочной целостности, реализованных в БД.
Варианты заданий, приведены в ПРИЛОЖЕНИИ.
1
2
В других диалектах SQL — restrict.
Скобки { | } — указывают на необходимость выбора одной из синтаксических конструкций.
12
2.
Заполнение и модификация таблиц базы данных
2.1
Вставка данных в таблицы
Вставку данных в таблицы осуществляет команда insert into <имя таблицы> [ (
<столбец 1> [ , <столбец 2> [ , … ] ] ) ] values ( <значение 1> [ , <значение 2> [ , … ] ] ), при
этом <значение i> будет подставлено в <столбец i>. Если значения указываются для всех
столбцов таблицы, список столбцов может быть опущен, в данном случае последовательность указания значений должна строго соответствовать последовательности столбцов в
операторе create table.
Создадим в БД таблицу:
create table Студент (
Ном_Зач int primary key,
ФИО varchar(50) not null,
Факультет tinyint not null,
Группа char(7) not null )
go
Для ее заполнения можно воспользоваться следующими операторами:
insert into Студент values (11, 'Лисичкин', 4, '4001')
insert into Студент values (22, 'Сыроежкин', 4, '4001')
insert into Студент values (33, 'Груздев', 4, '4002КФс')
insert into Студент values (44, 'Сморчков', 4, '4002КФс')
insert into Студент values (55, 'Волнушкин', 5, '5001')
insert into Студент values (77, 'Строчков', 5, '5001')
insert into Студент values (88, 'Белов', 5, '5002К')
insert into Студент values (87, 'Краснов', 5, '5002К')
go
Создадим в БД таблицу:
create table Оценка (
Id int identity primary key,
Ном_Зач int not null,
Дисциплина varchar(50) not null,
Оценка tinyint not null,
foreign key (Ном_Зач) references Студент (Ном_Зач)
on delete cascade
on update cascade )
go
Для ее заполнения можно воспользоваться следующими операторами:
13
insert into Оценка (Ном_Зач, Дисциплина, Оценка) values (11, 'БД', 5)
insert into Оценка (Ном_Зач, Дисциплина, Оценка) values (11, 'ФиЛП', 4)
insert into Оценка (Ном_Зач, Дисциплина, Оценка) values (22, 'БД', 4)
insert into Оценка (Ном_Зач, Дисциплина, Оценка) values (22, 'ФиЛП', 4)
insert into Оценка (Ном_Зач, Дисциплина, Оценка) values (33, 'БД', 3)
insert into Оценка (Ном_Зач, Дисциплина, Оценка) values (33, 'ФиЛП', 4)
insert into Оценка (Ном_Зач, Дисциплина, Оценка) values (44, 'БД', 4)
insert into Оценка (Ном_Зач, Дисциплина, Оценка) values (44, 'ФиЛП', 3)
insert into Оценка (Ном_Зач, Дисциплина, Оценка) values (55, 'БД', 4)
insert into Оценка (Ном_Зач, Дисциплина, Оценка) values (55, 'АСУ', 5)
insert into Оценка (Ном_Зач, Дисциплина, Оценка) values (77, 'БД', 5)
insert into Оценка (Ном_Зач, Дисциплина, Оценка) values (77, 'АСУ', 4)
insert into Оценка (Ном_Зач, Дисциплина, Оценка) values (88, 'БД', 3)
insert into Оценка (Ном_Зач, Дисциплина, Оценка) values (88, 'АСУ', 3)
insert into Оценка (Ном_Зач, Дисциплина, Оценка) values (87, 'БД', 5)
insert into Оценка (Ном_Зач, Дисциплина, Оценка) values (87, 'АСУ', 5)
go
При вставке данных значения полей identity не указываются. Значение, присвоенное
данному полю можно получить, опросив глобальную системную переменную @@ identity:
select @@identity
go
Для просмотра данных в таблице можно воспользоваться простейшим вариантом оператора выборки данных: select * from <имя таблицы>:
select * from Студент
select * from Оценка
go
Вставку данных в таблицы возможна по результатам выборки из других таблиц оператором insert into <имя таблицы> [ ( <столбец 1> [ , <столбец 2> [ , … ] ] ) ] select <выражение 1> [ , < выражение 2> [ , … ] ] from <имя таблицы 1> [ , <имя таблицы 2> [ , … ] ]
[ where <условие> ]. В простейшем случае < выражение i> — это имя столбца.
Создадим в БД таблицу:
create table Группа (
Id smallint identity primary key,
Номер char(7) not null,
Факультет tinyint not null )
go
для ее заполнения можно воспользоваться следующим оператором:
insert into Группа (Номер, Факультет)
select distinct Группа, Факультет from Студент
go
14
Конструкция distinct обеспечивает исключение повторяющихся строк при выборке из
таблиц.
2.2
Изменение данных
Изменение данных в таблице осуществляет оператор update <имя таблицы> set <имя
столбца 1> = <выражение 1> [ , < имя столбца 2> = < выражение 2> [ , … ] ] [ where
<условие> ]. Здесь <условие> — это логическое выражение, построенное из имен столбцов,
констант, операций сравнения (>, <, = и др.), логических операции (and, or, not), оператора
проверки принадлежности множеству (in), скобок и некоторых других конструкций. Оператор update модифицирует все записи таблицы, для которых оказалось истинным <условие>.
Если раздел where опущен — будут изменены все записи таблицы.
Оператор:
update Оценка
set
Оценка = 4
where
Ном_зач = 87 and Дисциплина = 'БД'
go
изменит значение оценки, полученной указанным студентом по заданной дисциплине.
Оператор:
update Студент
set
Факультет = 3,
Группа = '3002К'
where
Группа = '5002К'
go
осуществит перевод студентов группы 5002К на 3-й факультет.
2.3
Удаление данных
Для удаления данных в таблице используется директива delete [ from ] <имя таблицы> [ where <условие> ]. Здесь <условие> играет ту же роль, что и в операторе update.
Оператор:
15
delete from Оценка
where
Ном_зач = 87 and Дисциплина = 'БД'
go
удалит информацию о сдаче экзамена указанным студентом по заданной дисциплине.
Если в таблице, в которую осуществляется вставка из другой таблицы, присутствует
столбец со свойством identity, для сохранения существующих ключей можно отключить генерацию значений identity с помощью директивы set identity_insert <имя таблицы> { on | off
}. В MS SQL в один момент времени только для одной таблицы в сеансе может быть установлено on, при необходимости установить on для другой таблицы, необходимо предварительно выполнить сброс — off для текущей.
2.4
Изменение определения таблицы
Таблица, созданная в БД (например, с помощью create table) существует, пока не будет
явно удалена (например, с помощью drop table). Так как при удалении таблицы происходит
удаление всех содержащихся в ней записей, в языке SQL предусмотрен оператор alter table,
позволяющий осуществить модификацию таблицы сохранив, если это возможно, содержащиеся в ней сведения.
Основные варианты синтаксиса и действий, выполняемых с помощью alter table:
(i) alter table <имя таблицы> add <определение столбца> — добавление столбца к
таблице, например:
alter table Студент add Ср_балл real
go
(ii) alter table <имя таблицы> drop column1 <имя столбца> — удаление столбца таблицы, например:
alter table Студент drop column Ср_балл
go
(iii) alter table <имя таблицы> alter column2 <определение столбца> — модификация
столбца таблицы3, например:
1
В других диалектах SQL — drop, delete.
В других диалектах SQL — alter, modify.
3
Изменение типа данных столбца возможно, если между старым и новым типами существует автоматическое
преобразование.
2
16
alter table Студент alter column ФИО varchar(70) not null
go
(iv) alter table <имя таблицы> rename <имя столбца> to <новое имя> — переименование столбца таблицы, в MS SQL не поддерживается, используется системная хранимая
процедура (СХП):
sp_rename 'Студент.Группа', 'Номер_группы'
go
(v) alter table <имя таблицы> rename <новое имя> — переименование таблицы, в MS
SQL не поддерживается, используется СХП:
sp_rename 'Оценка', 'Успеваемость'
go
(vi) alter table <имя таблицы> add constraint <определение ограничения>1 — добавление ограничения к таблице, например:
alter table Группа add constraint Уник_номер unique (Номер)
go
(vii) alter table <имя таблицы> drop constraint <имя ограничения>2 — удаление ограничения таблицы, например:
alter table Группа drop constraint Уник_номер
go
В качестве ограничений могут так же выступать помимо unique директивы primary key,
foreign key, check, default. В приведенных примерах Уник_номер — имя ограничения. Если
имя ограничения не указывается в create table или alter table, SQL-сервер присваивает ему
автоматически сформированное имя. SQL-сервер может отклонить создание ограничения,
если ему не соответствуют данные, ранее занесенные в таблицы.
Воспользуемся приведенными конструкциями для изменения текущей БД, зададим
уникальность для номера группы:
alter table Группа add constraint Уник_номер unique (Номер)
go
Повторно заполним таблицу Группа:
delete from Группа
go
insert into Группа (Номер, Факультет)
select distinct Номер_группы, Факультет from Студент
1
2
В других диалектах SQL допускается — add primary key… .
В других диалектах SQL допускается — drop primary key.
17
go
Добавим в Студент столбец Группа:
alter table Студент add Группа smallint
go
и заполним его:
update Студент
set Группа =
(select Id from Группа
where Группа.Номер = Студент.Номер_группы)
go
удалим столбец Номер_группы:
alter table Студент drop column Номер_группы
go
Удалим столбец Факультет:
alter table Студент drop column Факультет
go
Определим столбец Группа как внешний ключ:
alter table Студент add constraint Вн_кл_группа
foreign key (Группа) references Группа (Id)
on delete set null
on update cascade
go
Зададим уникальность для номера зачетной книжки:
alter table Студент add constraint Уник_ном_зач unique (Ном_Зач)
go
Создадим справочник дисциплин:
create table Дисциплина (
Id smallint identity primary key,
Наименование varchar(50) not null constraint Уник_наимен unique )
go
Заполним справочник дисциплин:
insert into Дисциплина (Наименование)
select distinct Дисциплина from Успеваемость
go
Добавим в Успеваемость столбец Id_Дисциплины:
18
alter table Успеваемость add Id_Дисциплины smallint
go
и заполним его:
update Успеваемость
set Id_Дисциплины =
(select Id from Дисциплина
where Дисциплина.Наименование =
Успеваемость.Дисциплина)
go
Удалим столбец Дисциплина:
alter table Успеваемость drop column Дисциплина
go
Определим столбец Id_Дисциплины как внешний ключ:
alter table Успеваемость add constraint Вн_кл_дисциплина
foreign key (Id_Дисциплины) references Дисциплина (Id)
on delete no action
on update cascade
go
Определим столбец Id_Дисциплины как обязательный:
alter table Успеваемость alter column Id_Дисциплины smallint not null
go
Зададим ограничение уникальности (предполагается, что дисциплины изучаются один
семестр и не может быть 2-х оценок по одной дисциплине):
alter table Успеваемость add constraint Уник_ном_зач_id_дисц
unique (Ном_Зач, Id_Дисциплины)
go
Зададим ограничение на значение оценки (фиксируются только положительные оценки,
истории двоек и н/а не ведется):
alter table Успеваемость add constraint Полож_оценка
check (Оценка in (3, 4, 5))
go
Для просмотра результирующей схемы БД необходимо выполнить команду Создать
диаграмму базы данных из контекстного меню узла Диаграммы баз данных в обозревателе
объектов.
Для получения текста SQL для создания таблицы можно выполнить команды Создать
сценарий для таблицы\Используя CREATE\Новое окно редактора запросов из контекстного узла, соответствующего таблице в обозревателе объектов.
19
Для получения полного текста SQL для создания БД необходимо выполнить команды
Задачи\Сформировать сценарии из контекстного узла, соответствующего базе данных в
обозревателе объектов.
2.5
Лабораторная работа 2
Выполнить вставку тестовых данных в таблицы, созданные в ходе выполнения лабораторной работы 1. В строках, вставляемых в таблицы, должны быть данные как удовлетворяющие, так и не удовлетворяющие условиям запросов, приведенных в варианте задания. В
случае внесения в таблицы ошибочных данных произвести их корректировку операторами
update и delete. При обнаружении недочетов в структуре БД произвести ее корректировку с
помощью alter table.
Содержание отчета:
— схема БД (если изменялась);
— наборы данных, содержащихся в таблицах БД;
— примеры использования insert, update и delete для корректных и некорректных данных (нарушающих ограничения и ссылочную целостность);
— примеры update и delete, вызывающих каскадные изменения и удаление данных;
— примеры использования alter table для корректировки структуры таблиц.
Варианты заданий приведены в ПРИЛОЖЕНИИ.
20
3.
Запросы на языке QBE
3.1
Язык QBE
Язык QBE (Query By Example) является языком запросов к базам данных, предназначенным для составления запросов пользователями, не являющимися специалистами в области разработки программного обеспечения. В настоящее время реализации языка можно
найти в форме графических конструкторов запросов в таких продуктах как Microsoft Access
(далее по тексту — Access), Microsoft Power Query для Excel или OpenOffice Base.
3.2
Импорт в Access БД MS SQL
Для подключения Access к БД MS SQL необходимо выполнить следующие действия:
(i) Запустить Access — Пуск\Все программы\Microsoft Office\Microsoft Access 2010.
(ii) Создать БД Access — Файл\Создать\Имя файла — Университет\Создать.
(iii)
Создать
источник
данных
ODBC
—
Пуск\Панель
управле-
ния\Администрирование\Источники данных (ODBC)\Добавить и ответить на вопросы
мастера (сервер — SQL Server, имя источника — Университет, БД по умолчанию — Университет).
(iv) Импортировать БД в Access — Внешние данные\База данных ODBC\Источник
данных компьютера\Университет и выбрать таблицы для импорта.
При импорте БД может происходить потеря части определений таблиц. Для восстановления первичных ключей необходимо открыть таблицу в конструкторе (команда Конструктор контекстного меню таблицы в окне Все объекты Access), выбрать столбец и выполнить
команду Ключевое поле контекстного меню.
Для восстановления внешних ключей необходимо:
(i) Пометить их как индексируемые поля — выбрать столбец в конструкторе таблицы и
установить значение свойства Индексированное поле в Да (Допускаются совпадения).
(ii) Проверить соответствие типов, аналогом identity в Access являются столбцы Счетчик, реализуемые на основе типа данных Длинное целое. Если внешние ключи, ссылающиеся на первичные ключи типа Счетчик будут иметь тип, отличный от Длинное целое, при
создании связей таблиц могут происходить ошибки.
(iii) Создать схему БД (рис. 3.1) — Работа с базами данных\Схема данных и выбрать
все имеющиеся таблицы. Далее необходимо создать связи путем перетаскивания первичного
ключа на внешний или наоборот.
21
Рис. 3.1.
3.3
Разработка запросов
Разработки запросов на языке QBE в Access выполняется в конструкторе запросов (Создание\Конструктор запросов), при создании нового запроса Access предлагает выбрать
подмножество таблиц БД, из которых будет осуществляться выборка данных. Вызов конструктора для изменения сохраненного запроса осуществляется командой Конструктор из
контекстного меню запроса в окне Все объекты Access.
Пример запроса, возвращающего дисциплины, изучаемые на 4-м факультете, приведен
на рис. 3.2. Для задания выбираемых значений и условий отбора необходимо перетащить
имя столбца Наименование из таблицы Дисциплина и имя столбца Факультет из таблицы
Группа в нижнюю часть окна в строку Поле:. Логическое выражение, задающее условие отбора, указывается в нижней части окна в строке Условие отбора:. Если (как в приведенном
примере) между таблицами существуют связи, условий, определяющих правила соединения
таблиц, задавать не нужно. В противном случае необходимо будет дополнить запрос условиями, показывающими равенство первичных и внешних ключей. Для выполнения запроса и
получения результирующего набора данных необходимо выполнить команду меню ! Вы-
22
полнить.
Для
возврата
в
режим
конструктора
используются
команды
Ре-
жим\Конструктор. По умолчанию Access не исключает повторяющиеся значения в результирующем наборе данных, для их исключения необходимо вызвать команду Свойства…
контекстного меню и установить свойство Уникальные значения в состояние Да.
Рис. 3.2.
Некоторые запросы требуют включения одной и той же таблицы дважды в конструктор
запроса (аналог псевдонимов таблиц в языке SQL). Для добавления таблицы к запросу необходимо выполнить команду контекстного меню Добавить таблицу…. Пример запроса, возвращающего студентов, получивших одинаковые оценки по БД и ФиЛП приведена рис. 3.3.
В приведенном примере различные записи таблицы Оценка берутся под псевдонимами
23
Оценка и Оценка_1, а связанные с ними различные записи таблицы Успеваемость — под
псевдонимами Успеваемость и Успеваемость_1.
Рис. 3.3.
Существуют случаи, когда запрос должен вернуть не сами данные, а результаты их обработки, полученные с помощью агрегатных функций. Пример запроса, возвращающего количество дисциплин, сданных заданным студентом, приведен на рис. 3.4. Для реализации
агрегатных функций необходимо выполнить команду Итоги на вкладке Конструктор, после чего выбрать требуемую агрегатную функцию: count — подсчет количества записей, avg
— вычисление среднего, min — нахождение минимума, max — нахождение максимума, sum
— суммирование значений в столбце. Сохраним запрос под именем Сдал_Сыроежкин
(Файл\Сохранить объект как). Если в приведенном запросе очистить поле Условие отбора:, запрос вернет количество дисциплин, сданных каждым из студентов. Сохраним запрос
под именем Сдали_Всего.
24
Рис. 3.4.
Рис. 3.5.
25
Источником данных для выполнения запроса может быть не только таблица, но и другой запрос. Пример запроса, возвращающего студентов, сдавших столько же дисциплин,
сколько сдал Сыроежкин, приведен на рис. 3.5.
3.4
Лабораторная работа 3
По аналогии с примерами, приведенными в п. 3.3 реализовать запросы а) .. е), указанные в варианте задания.
Содержание отчета:
— скриншоты окон конструктора запросов;
— скриншоты окон с наборами данных, возвращаемыми запросами.
Варианты заданий приведены в ПРИЛОЖЕНИИ.
26
4.
Запросы на языке SQL: выборка данных
4.1
Оператор select
Простейший вариант оператора был приведен в лабораторной работе 2, в более общем
случае синтаксис оператора выглядит следующим образом:
select <список выбора>
from <список таблиц>
where <условие>
go
Здесь <список выбора> — список столбцов таблиц или выражений с их участием, значения которых должен вернуть оператор, если <список выбора> содержит более одного элемента, они разделяются запятыми, для выборки всех столбцов указывается *; <список таблиц> содержит перечень таблиц, из которых осуществляется выборка, если в списке более
одного элемента, они разделяются запятыми, в разделе from могут использоваться также
представления и производные таблицы (см. следующие работы);
<условие> аналогично
условиям, рассмотренным в операторах delete и update, в условиях могут использоваться
подзапросы, кванторы и др. возможности (см. следующие работы).
Оператор select реализует в языке SQL все операторы, предусмотренные реляционной
алгеброй (РА). Пусть в БД существуют таблицы, созданные и модифицированные так, как
описано в лабораторной работе 2, а именно таблица Группа:
Id
7
8
9
10
Номер Факультет
3002К
3
4001
4
4002КФс
4
5001
5
таблица Студент:
Ном_Зач
11
22
33
44
55
77
87
88
ФИО
Лисичкин
Сыроежкин
Груздев
Сморчков
Волнушкин
Строчков
Краснов
Белов
Группа
8
8
9
9
10
10
7
7
27
таблица Дисциплина:
Id
1
2
3
Наименование
АСУ
БД
ФиЛП
таблица Успеваемость:
Id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
16
Ном_Зач
11
11
22
22
33
33
44
44
55
55
77
77
88
88
87
Оценка
5
4
4
4
3
4
4
3
4
5
5
4
3
3
5
Id_Дисциплины
2
3
2
3
2
3
2
3
2
1
2
1
2
1
1
тогда операцию выборки списка групп 4-го факультета, осуществит оператор:
select * from Группа where Факультет = 4
go
проекцию, возвращающую список всех групп, выполнит оператор:
select Номер from Группа
go
соединение, формирующее список групп и студентов, реализует оператор:
select * from Группа, Студент
where Id = Группа
go
оператор:
select Номер 'Группа', ФИО, Наименование 'Дисциплина', Оценка
from Группа, Студент, Дисциплина, Успеваемость
where Группа.Id = Группа and
Студент.Ном_Зач = Успеваемость.Ном_Зач and
Дисциплина.Id = Id_Дисциплины and
Номер = '4001'
go
28
использует комбинацию соединения выборки и проекции, что бы вернуть список оценок студентов группы 4001. Как следует из примера, в случае, если столбцы различных таблиц имеют одинаковые имена, для уточнения в качестве префикса может использоваться имя
таблицы, отделяемое от имени столбца точкой. Для удобства восприятия пользователем
набора данных, возвращаемого запросом, столбцам можно присваивать псевдонимы, используя директиву as или просто разделяя пробелом имя столбца (выражение) и псевдоним. Если
заданы, псевдонимы подставляются в заголовок набора данных, возвращаемого запросом.
По умолчанию для списка выбора действует директива all, поэтому запрос «По каким
дисциплинам были выставлены оценки?»:
select Наименование 'Есть оценки'
from Дисциплина, Успеваемость
where Дисциплина.Id = Id_Дисциплины
go
вернет каждую дисциплину столько раз, сколько записей встречается в таблице Успеваемость, а запрос:
select distinct Наименование 'Есть оценки'
from Дисциплина, Успеваемость
where Дисциплина.Id = Id_Дисциплины
go
исключит повторяющиеся значения.
Роль, аналогичную операции переименования атрибутов РА, играют псевдонимы таблиц, указываемые в разделе from и отделяемые от имени таблицы директивой as или пробелом. Псевдонимы необходимы, например, при выполнении соединения таблицы с собой. Запрос «Кто из студентов получил одинаковые оценки по БД и по ФиЛП?», может быть реализован в SQL с использованием псевдонимов:
select ФИО
from Студент, Дисциплина Д1, Дисциплина Д2,
Успеваемость У1, Успеваемость У2
where Д1.Наименование = 'БД' and Д2.Наименование = 'ФиЛП' and
Д1.Id = У1.Id_Дисциплины and Д2.Id = У2.Id_Дисциплины and
У1.Ном_Зач = Студент.Ном_Зач and
У2.Ном_Зач = Студент.Ном_Зач
go
В примере две записи из таблицы Дисциплина рассматриваются под псевдонимами Д1
и Д2, а две связанные с ними записи таблицы Успеваемость под псевдонимами У1 и У2, после чего выполняется их соединение.
29
Следует учитывать, что при использовании двух псевдонимов для одной таблицы, каждая запись может быть «выбрана» вначале под первым, а затем под вторым. Данную ситуацию иллюстрирует запрос «Какие студенты получили одинаковые оценки по БД?», если в
выражении С1.Ном_Зач > С2.Ном_Зач заменить > на < >, каждая пара студентов войдет в
результирующий набор данных дважды:
select С1.ФИО, С2.ФИО, У1.Оценка
from Студент С1, Студент С2, Дисциплина,
Успеваемость У1, Успеваемость У2
where Наименование = 'БД' and
Дисциплина.Id = У1.Id_Дисциплины and
Дисциплина.Id = У2.Id_Дисциплины and
У1.Оценка = У2.Оценка and
У1.Ном_Зач = С1.Ном_Зач and У2.Ном_Зач = С2.Ном_Зач and
С1.Ном_Зач > С2.Ном_Зач
go
Для удобства восприятия пользователем набора данных, возвращаемого запросом, он
может быть отсортирован. Порядок сортировки задается директивой order by <столбец 1> [
{ asc | desc } ] [ , <столбец 2> [ { asc | desc } ] [ , … ] ], здесь asc задает порядок сортировки по
возрастанию (действует по умолчанию), desc — по убыванию. Пример использования сортировки показывает запрос:
select С1.ФИО, С2.ФИО, У1.Оценка
from Студент С1, Студент С2, Дисциплина,
Успеваемость У1, Успеваемость У2
where Наименование = 'БД' and
Дисциплина.Id = У1.Id_Дисциплины and
Дисциплина.Id = У2.Id_Дисциплины and
У1.Оценка = У2.Оценка and
У1.Ном_Зач = С1.Ном_Зач and У2.Ном_Зач = С2.Ном_Зач and
С1.Ном_Зач > С2.Ном_Зач
order by У1.Оценка desc, С1.ФИО, С2.ФИО
go
4.2
Директивы, используемые в условиях запросов
В операторе select можно использовать директиву [ not ] in для проверки принадлежности или не принадлежности множеству, следующий оператор вернет всех студентов, за исключением обучающихся в группах 4001, 5001:
select Номер 'Группа', ФИО
from Группа, Студент
where Группа.Id = Группа and
Номер not in ( '4001', '5001')
go
30
Для неточного сравнения строк используется директива [ not ] like, запрос:
select * from Студент
where ФИО like 'С%'
go
вернет информацию о студентах, фамилии которых начинаются с буквы С. В шаблоне,
указываемом в операторе like можно использовать знак % для обозначения любой, в том
числе пустой последовательности символов и знак _ для обозначения произвольного символа
(символ должен присутствовать в строке). Если % или _ содержится в искомой строке, определяется символ escape, например условие like '#%%' escape '#' определяет символ #, как отменяющий действие символа %, в результате будут найдены строки, начинающиеся с данного символа.
С помощью [ ] в like можно задавать множество допустимых символов, например выборка студентов с ФИО, начинающейся с К или С:
select * from Студент
where ФИО like '[КС]%'
go
А с помощью ^ можно определять не вхождение символа в шаблон, например студенты, фамилии которых начинаются с буквы С, где вторая буква не ы:
select * from Студент
where ФИО like 'С[^ы]%'
go
Для удобства составления запросов, производящих отбор диапазонов значений, используется директива [not] between <нижняя граница> and <верхняя граница>. Примр использования директивы иллюстрирует запрос:
select * from Группа
where Номер between '4000' and '5000'
go
В случае если при вставке строки в таблицу не указывалось значение какого-либо
столбца, в качестве его значения подставляется специальная константа null. Константа null
не соответствует нулю или пустой строке, она говорит о том, что значение является неопределенным. Как следствие, любая операция сравнения с null -значением дает неопределенный
результат, для поиска в таблице значений отличных или совпадающих с null используется
директива is [ not ] null.
31
4.3
Лабораторная работа 4
По аналогии с примерами, приведенными в п. 4.1 реализовать запросы а) .. в), указанные в варианте задания. Самостоятельно предложить и реализовать запросы, демонстрирующие использование каждой из директив, приведенных в п. 4.2.
Содержание отчета:
— текст запросов на SQL;
— наборы данных, возвращаемые запросами.
Варианты заданий приведены в ПРИЛОЖЕНИИ.
32
5.
Запросы на языке SQL: агрегатные функции
5.1
Агрегатные функции
Часто результатом выполнения запроса должны быть не сами данные таблиц, а результаты их обработки. Для решения таких задач в языке SQL используются агрегатные функции:
― count – подсчет количества значений;
― sum – сумма значений;
― avg – вычисление среднего;
― min – поиск минимального значения;
― max – поиск максимального значения.
Определить количество студентов в университете можно, воспользовавшись следующим запросом:
select count(*) from Студент
go
количество групп на четвертом факультете – запросом:
select count(Id) from Группа
where Факультет = 4
go
а средний балл по университету – запросом:
select avg(Оценка) from Успеваемость
go
Часто требуется вычислить значение агрегатной функции, полученное не по всем записям таблицы, а по отдельным группам записей, такую возможность предоставляет директива
group by. Вначале осуществляется группировка записей таблицы по значениям столбцов,
указанных в group by, после чего для каждой группы вычисляется агрегатная функция. Запрос:
select Номер, count(Ном_Зач) as 'Количество студентов'
from Группа, Студент
where Группа = Id
group by Номер
go
позволит получить количество студентов в каждой из групп, а запрос:
select Номер, ФИО, avg(Оценка) as 'Ср. балл'
from Группа, Студент, Успеваемость
33
where Группа = Группа.Id and
Студент.Ном_Зач = Успеваемость.Ном_Зач
group by Номер, ФИО, Успеваемость.Ном_Зач
order by Номер, ФИО
go
сформирует значение среднего балла для каждого из студентов. Следует заметить, что
стандарт языка SQL предполагает, что столбцы, входящие в список выбора, должны входить
в список, в соответствии с которым осуществляется группировка, вместе с тем, некоторые
СУБД могут отступать от этих требований.
В общем случае использование агрегатной функции предполагает следующий синтаксис <имя функции> ( [ all | distinct ] <выражение> ). Особенности функции count:
— count(*) подсчитывает все значения, в том числе null;
— count(all <выражение>) подсчитывает все значения, отличные от null;
— count(distinct <выражение>) подсчитывает все различные значения, отличные от
null;
Остальные агрегатные функции игнорируют null значения, они возвращают null только
тогда, когда в столбце нет других значений. Сказанное иллюстрируют примеры, приведенные ниже:
Аргументом агрегатной функции в общем случае может быть выражение, при этом некоторые SQL сервера допускают, что бы аргументом была другая агрегатная функция, запрос:
Конструкция having играет ту же роль по отношению к группам записей, что и where
для отдельных строк и позволяет наложить условие на значение, сформированное агрегатной
функцией. Для получения перечня групп, в которых числится 25 и более студентов можно
воспользоваться запросом:
select Номер, count(Ном_Зач) as ' >= 25 студентов'
from Группа, Студент
where Группа = Id
group by Номер
having count(Ном_Зач) >= 25
go
а для нахождения групп, в которых есть студенты со средним баллом 5.0 – запросом:
select Номер, ФИО
from Группа, Студент, Успеваемость
where Группа = Группа.Id and
Студент.Ном_Зач = Успеваемость.Ном_Зач
group by Номер, ФИО, Успеваемость.Ном_Зач
having avg(Оценка) = 5
34
order by Номер, ФИО
go
В общем случае, запрос, содержащий where, group by, having и агрегатные функции
выполняется в следующей последовательности:
(i) отбираются записи таблицы в соответствии с условием, заданным во WHERE;
(ii) записи группируются в соответствии с порядком, определенным в GROUP BY;
(iii) для каждой группы записей вычисляется значение агрегатной функции;
(iv) на полученные значения накладывается условие отбора, определенное в HAVING.
5.2
Объединение, пересечение, разность запросов
С помощью директив union, intersect, except можно осуществлять операции объединения, пересечения и разности запросов, аналогичные соответствующим операциям РА над отношениями. Следующий запрос вернет общие дисциплины, изучаемые как студентами 4-го,
так и 5-го факультетов (пересечение):
select distinct Наименование
from Группа, Студент, Успеваемость, Дисциплина
where Группа = Группа.Id and
Студент.Ном_Зач = Успеваемость.Ном_Зач and
Id_Дисциплины = Дисциплина.Id and
Факультет = '4'
intersect
select distinct Наименование
from Группа, Студент, Успеваемость, Дисциплина
where Группа = Группа.Id and
Студент.Ном_Зач = Успеваемость.Ном_Зач and
Id_Дисциплины = Дисциплина.Id and
Факультет = '5'
go
Для получения множества дисциплин, изучаемых на 4-м и не изучаемых на 5-м факультете, в приведенном запросе директиву intersect следует заменить на except, а для получения
общего множества дисциплин — на директиву union. Часто SQL-сервера не реализуют intersect и except, так как соответствующая обработка может быть выполнена с помощью экзистенциальных запросов (см. лабораторную работу 6).
В общем случае директивы union, intersect, except могут быть использованы в последовательности из нескольких запросов, при этом следует учитывать следующее:
(i) во всех запросах должно совпадать количество элементов в списках выбора;
(ii) запросы должны возвращать элементы, имеющие совместимые типы данных;
35
(iii) в заголовке результирующего набора данных будут использованы имена элементов
или псевдонимы (если заданы), указанные в списке выбора первого запроса.
Следует отметить, что SQL-сервера, как правило, реализуют как теоретикомножественные
операции
объединения,
пересечения
и
разности,
так
и
мульти-
множественные. Пусть в отношении r кортеж t встречается n раз, а в отношении s — m раз,
тогда:
(i) объединение r и s будет содержать n + m вхождений кортежа t;
(ii) пересечение r и s будет содержать min(n, m) вхождений кортежа t;
(iii) разность r и s будет содержать max(0, n – m) вхождений кортежа t.
Способ выполнения операции теоретико-множественная или мульти-множественная
задается директивами distinct (действует по умолчанию) и all соответственно.
Сказанное иллюстрируют примеры, приведенные ниже:
5.3
Лабораторная работа 5
По аналогии с примерами, приведенными в п. 5.1 реализовать запросы г) .. е), указанные в варианте задания. Самостоятельно предложить и реализовать запросы, демонстрирующие использование каждой из директив, приведенных в п. 5.2.
Содержание отчета:
— текст запросов на SQL;
— наборы данных, возвращаемые запросами.
Варианты заданий приведены в ПРИЛОЖЕНИИ.
36
6.
Запросы на языке SQL: подзапросы
6.1
Запросы с подзапросам
Сложные операции выборки иногда невозможно выполнить с помощью одного запроса,
для решения таких задач в разделах where и having могут использоваться подзапросы. При
этом подзапрос, в свою очередь, может содержать подзапросы.
Подзапросы часто используются совместно с оператором in для формирования набора
данных, на основе которого внешний запрос проверяет некоторое условие. Предположим,
что требуется найти студентов, у которых нет оценок 3, запрос:
select Номер, ФИО
from Группа, Студент, Успеваемость
where Группа = Группа.Id and
Студент.Ном_Зач = Успеваемость.Ном_Зач and
Оценка <> 3
go
не даст требуемого результата, так как наличие хороших оценок по какому-либо предмету не исключает наличия троек по другим, решить задачу позволит запрос с подзапросом:
select Номер, ФИО
from Группа, Студент
where Группа = Группа.Id and
Студент.Ном_Зач not in
(select Студент.Ном_Зач
from Студент, Успеваемость
where Студент.Ном_Зач = Успеваемость.Ном_Зач
and Оценка = 3)
go
В приведенном примере подзапрос формирует множество номеров зачеток студентов, у
которых есть тройки по каким-либо дисциплинам, после чего внешний запрос отбирает данные, не принадлежащие этому множеству.
Теоретически уровень вложенности подзапросов ничем не ограничен, но в конкретных
реализациях SQL-серверов ограничения могут накладываться. При выполнении запроса с
подзапросами вначале выполняется самый вложенный запрос, затем запрос предыдущего
уровня и т.д. вплоть до основного запроса.
Подзапросы могут возвращать значения агрегатных функций, при этом, если подзапрос
используется с операцией сравнения, он должен возвращать единственное значение. Следующий запрос вернет студентов, имеющих средний бал выше, чем средний балл по университету:
37
select Номер, ФИО
from Группа, Студент, Успеваемость
where Группа = Группа.Id and
Студент.Ном_Зач = Успеваемость.Ном_Зач
group by Номер, ФИО, Успеваемость.Ном_Зач
having avg(Оценка) > (select avg(Оценка) from Успеваемость)
order by Номер, ФИО
go
Подзапросы могут использоваться в операторах манипулирования данными: в insert
для вставки данных в таблицы из других таблиц; в update и delete для формирования критериев выполнения операций и вычисления новых значений.
Создадим и заполним таблицу Дипл_с_отл данными о студентах, имеющих средний
балл выше 4.75:
create table Дипл_с_отл (Группа char(7), ФИО varchar(70), Ном_зач int)
go
insert into Дипл_с_отл (Группа, ФИО, Ном_Зач)
select Номер, ФИО, Студент.Ном_Зач
from Группа, Студент, Успеваемость
where Группа = Группа.Id and
Студент.Ном_Зач = Успеваемость.Ном_Зач
group by Номер, ФИО, Студент.Ном_Зач
having avg(Оценка) >= 4.75
order by Номер, ФИО
go
Удалим из нее записи о студентах, имеющих тройки:
delete from Дипл_с_отл
where Ном_Зач in
(select Студент.Ном_Зач
from Студент, Успеваемость
where Студент.Ном_Зач = Успеваемость.Ном_Зач and
Оценка = 3)
go
Добавим столбец Ср_Балл и заполним его:
alter table Дипл_с_отл add Ср_Балл real
go
update Дипл_с_отл
set Ср_Балл =
(select avg(Оценка) from Успеваемость
where Дипл_с_отл.Ном_Зач = Успеваемость.Ном_Зач)
go
38
Особенности использования подзапросов:
(i) список выбора в подзапросе должен содержать один элемент (за исключением подзапросов в insert into);
(ii) подзапросы могут использоваться в разделах select, from, where, having;
(iii) подзапросы не могут использоваться в разделах order by, group by;
(iv) если подзапрос используется с операцией сравнения, он должен возвращать единственное значение.
6.2
Экзистенциальные запросы
К особому классу относятся запросы с квантором exists, получившие название экзистенциальных, они имеют следующий синтаксис: { where | having } [ not ] exists ( <подзапрос> ). Квантор exists возвращает логическое значение «истина», если сформированный
подзапросом набор данных не пуст и «ложь» в противном случае, not инвертирует значение,
возвращаемое квантором.
Используя кванторы можно предложить другой вариант запроса, находящего студентов, у которых нет оценок 3:
select Номер, ФИО
from Группа, Студент
where Группа = Группа.Id and
not exists
(select *
from Успеваемость
where Студент.Ном_Зач = Успеваемость.Ном_Зач
and Оценка = 3)
go
Так как квантор возвращает логическое условие в зависимости от наличия или отсутствия данных, удовлетворяющих условию подзапроса, а сами данные не имеют никакого
значения, в списке выбора подзапроса часто используется *.
С помощью экзистенциальных запросов можно реализовать пересечение и разность запросов, так запрос, возвращающий общие дисциплины, изучаемые как студентами 4-го, так и
5-го факультетов (пересечение):
select distinct Наименование
from Группа, Студент, Успеваемость, Дисциплина
where Группа = Группа.Id and
Студент.Ном_Зач = Успеваемость.Ном_Зач and
Id_Дисциплины = Дисциплина.Id and
Факультет = '4'
intersect
select distinct Наименование
39
from Группа, Студент, Успеваемость, Дисциплина
where Группа = Группа.Id and
Студент.Ном_Зач = Успеваемость.Ном_Зач and
Id_Дисциплины = Дисциплина.Id and
Факультет = '5'
go
может быть переформулирован следующим образом:
select distinct Наименование
from Группа, Студент, Успеваемость, Дисциплина
where Группа = Группа.Id and
Студент.Ном_Зач = Успеваемость.Ном_Зач and
Id_Дисциплины = Дисциплина.Id and
Факультет = '4' and exists
(select *
from Группа Г, Студент С, Успеваемость У,
Дисциплина Д
where С.Группа = Г.Id and
С.Ном_Зач = У.Ном_Зач and
У.Id_Дисциплины = Д.Id and
Факультет = '5' and
Дисциплина.Id = Д.Id)
go
Для реализации разности перед квантором необходимо добавить not.1
С помощью экзистенциальных запросов можно реализовать оператор деления РА.
Создадим и заполним таблицу Д4Ф:
create table Д4Ф (Д varchar(50))
go
insert into Д4Ф (Д)
select distinct Наименование
from Группа, Студент, Успеваемость, Дисциплина
where Группа = Группа.Id and
Студент.Ном_Зач = Успеваемость.Ном_Зач and
Id_Дисциплины = Дисциплина.Id and
Факультет = '4'
go
Создадим и заполним таблицу ДФ:
create table ДФ (Ф tinyint, Д varchar(50))
go
insert into ДФ (Ф, Д)
select distinct Факультет, Наименование
1
Запросы могут вернуть различные наборы данных, если в таблице есть null значения (в отличие от операций
сравнения union, intersect и except рассматривают null значения как обычные константы).
40
from Группа, Студент, Успеваемость, Дисциплина
where Группа = Группа.Id and
Студент.Ном_Зач = Успеваемость.Ном_Зач and
Id_Дисциплины = Дисциплина.Id
go
Тогда следующий запрос даст ответ на вопрос на каких факультетах изучаются все
дисциплины из числа изучаемых на факультете 4:
select distinct ДФ.Ф from ДФ where not exists
(select * from Д4Ф where not exists
(select * from ДФ ДФ1
where ДФ1.Ф = ДФ.Ф and
ДФ1.Д = Д4Ф.Д))
go
Для удобства записи запросов в SQL предусмотрены кванторы any, all и some.
6.3
Лабораторная работа 6
По аналогии с примерами, приведенными в п. 6.2 реализовать запросы ж) .. и), указанные в варианте задания. Самостоятельно предложить и реализовать запросы, демонстрирующие использование запросов с подзапросами, описанных в п. 6.1.
Содержание отчета:
— текст запросов на SQL;
— наборы данных, возвращаемые запросами.
Варианты заданий приведены в ПРИЛОЖЕНИИ.
41
7.
7.1
Хранимые процедуры
Управляющие конструкции в языке SQL
Конструкции, обсуждавшиеся ранее, носили декларативный характер, они задавали
критерии выполнения операций, а не алгоритм их выполнения. Вместе с тем в языке SQL
присутствуют операторы, соответствующие процедурному подходу аналогичному принятому в языках программирования высокого уровня (последовательность, ветвление, цикл).
Управляющие конструкции предназначены для использования в:
— пакетных заданиях (ПЗ);
— хранимых процедурах (ХП);
— триггерах (Т).
ХП и Т будут обсуждаться ниже, ПЗ — это последовательность операторов, за которой
следует директива go, передающаяся на SQL-сервер и выполняющаяся как один пакет. В качестве примера можно оформить как один пакет создание, заполнение и выборку из таблицы
Дипл_с_отл:
create table Дипл_с_отл (Группа char(7), ФИО varchar(70), Ном_зач int)
insert into Дипл_с_отл (Группа, ФИО, Ном_Зач)
select Номер, ФИО, Студент.Ном_Зач
from Группа, Студент, Успеваемость
where Группа = Группа.Id and
Студент.Ном_Зач = Успеваемость.Ном_Зач
group by Номер, ФИО, Студент.Ном_Зач
having avg(Оценка) >= 4.75
order by Номер, ФИО
delete from Дипл_с_отл
where Ном_Зач in
(select Студент.Ном_Зач
from Студент, Успеваемость
where Студент.Ном_Зач = Успеваемость.Ном_Зач and
Оценка = 3)
select * from Дипл_с_отл
go
В диалекте Transact SQL в качестве разделителя операторов используется перевод
строки, MS SQL допускает так же использование в качестве разделителя точки с запятой.
SQL-сервера могут накладывать ограничения на использование операторов в ПЗ, типовыми
ограничениями являются:
(i) операторы create { default | function | procedure | rule | shema | trigger | view } нельзя
совмещать с другими в одном ПЗ;
42
(ii) нельзя изменить объект и воспользоваться изменением в одном ПЗ, например следующее ПЗ приведет к ошибке:
alter table Дипл_с_отл add Ср_Балл real
update Дипл_с_отл
set Ср_Балл =
(select avg(Оценка) from Успеваемость
where Дипл_с_отл.Ном_Зач = Успеваемость.Ном_Зач)
go
К числу управляющих операторов языка SQL относятся:
(i) составной оператор:
begin
<список операторов>
end
(ii) условный оператор:
if <условие>
< оператор>
[ else
< оператор> ]
(iii) оператор выбора:
case <исходное выражение>
when <выражение> then <результирующее выражение>
[…]
[ else <результирующее выражение> ]
end
или:
case
when <условное выражение> then <результирующее выражение>
[…]
[ else <результирующее выражение> ]
End
первый вариант синтаксиса предполагает, что значение для <исходное выражение>
последовательно сравнивается с <выражение>, при совпадении значений возвращается значение для <результирующее выражение>, расположенное в соответствующей ветви when,
если совпадений не найдено, выполняется ветвь else; во втором варианте синтаксиса последовательно вычисляются значения для <условное выражение>, если получена истина, возвращается значение для <результирующее выражение>, расположенное в соответствующей
ветви when, если для всех <условное выражение> получено ложное значение, выполняется
ветвь else;
(iv) оператор цикла:
43
while <условие>
< оператор>
в теле цикла могут использоваться операторы break для завершения цикла и передачи
управления первому оператору за циклом, и continue для завершения текущей итерации цикла и передачи управления первому оператору в теле цикла;
(v) безусловный переход:
<метка>:
[…]
go to <метка>
(vi) возврат:
return [ <целочисленное выражение> ]
(vii) приостановка выполнения на заданный интервал или до наступления момента времени:
waitfor { delay <интервал> | time <время> ]
<интервал> и <время> должны относиться к типу данных datetime и могут указываться посредством локальных переменных.
В ПЗ, ХП и Т можно объявлять локальные переменные с помощью оператора:
declare <имя> <тип данных> [ , … ]
имя локальной переменной в качестве префикса должно содержать символ @. Присвоение значений локальным переменным осуществляют операторы:
set <имя> = <выражение>
select <имя> = <выражение>
второй способ белее универсален, так как некоторые SQL-сервера используют директиву set для задания свойств сервера и баз данных.
Проиллюстрируем сказанное примерами:
(i) объявление и использование переменных:
declare @x int, @y int
set @x = 1
select @y = 2
select @x + @y
go
при этом, если далее выполнить ПЗ:
select @y
go
44
будет получено сообщение об ошибке, так как после завершения предыдущего ПЗ переменная @y становится неопределена;
(ii) использование if — поиск претендентов на диплом с отличием:
if exists
(select *
from Группа, Студент, Успеваемость
where Группа = Группа.Id and
Студент.Ном_Зач = Успеваемость.Ном_Зач
group by Номер, ФИО, Студент.Ном_Зач
having avg(Оценка) >= 4.75)
begin
print 'Претенденты на диплом с отличием'
(select Номер, ФИО, Студент.Ном_Зач
from Группа, Студент, Успеваемость
where Группа = Группа.Id and
Студент.Ном_Зач = Успеваемость.Ном_Зач
group by Номер, ФИО, Студент.Ном_Зач
having avg(Оценка) >= 4.75)
end
else
print 'Претендентов на диплом с отличием нет'
go
(iii) использование case — оформление результатов вывода:
alter table Успеваемость alter column Оценка tinyint null
go
insert into Успеваемость (Ном_Зач, Оценка, Id_Дисциплины)
values (11, null, 1)
go
select Номер 'Группа', ФИО, Наименование 'Дисциплина', 'Оценка' =
case Оценка
when 5 then 'Отлично'
when 4 then 'Хорошо'
when 3 then 'Удовлетворительно'
else 'Неизвестно'
end
from Группа, Студент, Дисциплина, Успеваемость
where Группа.Id = Группа and
Студент.Ном_Зач = Успеваемость.Ном_Зач and
Дисциплина.Id = Id_Дисциплины
go
(iv) использование while — увеличение стипендий студентам на 10%, пока средняя не
достигнет заданной величины:
45
alter table Студент add Стипендия smallmoney
go
update Студент
set Стипендия = Группа * 1000
go
while (select avg(Стипендия) from Студент) < 10000
update Студент
set Стипендия = Стипендия + Стипендия * 0.1
go
(iv) тот же результат с помощью goto:
AddMoney:
update Студент
set Стипендия = Стипендия + Стипендия * 0.1
if (select avg(Стипендия) from Студент) > 10000
return
goto AddMoney
go
7.2
Хранимые процедуры
ХП — это модуль, состоящий из SQL операторов и находящийся в БД на SQL-сервере,
вызываемый клиентскими приложениями. ХП может:
(i) получать и возвращать параметры;
(ii) вызывать другие процедуры;
(iii) возвращать код ошибки (с помощью return);
(iv) возвращать наборы данных, сформированные запросами в теле ХП.
ХП создается оператором, имеющим следующий синтаксис:
create { proc | procedure } <имя процедуры> [ ; <номер> ]
[ @<имя параметра 1> <тип данных 1>
[ = <значение по умолчанию 1> ] [ { out | output } ] [ readonly ]
[,…]
[ with recompile ]
as
<оператор>
<имя процедуры> — идентификатор уникальный в рамках БД;
<номер> — позволяет объединять процедуры в группы, например для возможности
удаления всей группы оператором drop procedure, в будущих версиях MS SQL планируется
отказаться от этой возможности;
@<имя параметра 1> — идентификатор локального параметра, областью действия которого является тело ХП;
46
<тип данных 1> — в ХП используется тот же набор типов данных, что и для столбцов
таблиц;
<значение по умолчанию 1> — используется, если значение параметра не было указано при вызове ХП;
out или output говорит о том, что значение параметра является возвращаемым;
readonly — значение параметра не может быть изменено в теле ХП;
recompile — требует перекомпиляции процедуры при каждом ее вызове (например, в
целях оптимизации планов выполнения запросов в теле ХП).
ХП вызывается оператором, имеющим следующий синтаксис:
[ { exec | execute } [ @<имя переменной> = ] <имя процедуры> [ ; <номер> ]
[ @<имя параметра 1> ] = { <значение параметра 1> |
@<имя переменной 1>] [ output ] }
[,…]
[ with recompile ]
@<имя переменной> — имя переменной, которой будут присвоен код возврата (ошибки), возвращаемый процедурой;
<значение параметра 1> — выражение, задающее значение для параметра процедуры;
@<имя переменной 1> — значение параметра задается переменной.
Примеры ХП:
(i) выборка данных:
create procedure Гр_4001_5001
as
select * from Группа, Студент where Группа = Id and Номер = '4001'
select * from Группа, Студент where Группа = Id and Номер = '5001'
go
Гр_4001_5001
go
(ii) процедура с параметром:
create procedure Гр_N @n char(7) = '4001'
as
select * from Группа, Студент where Группа = Id and Номер = @n
go
Гр_N '5001'
go
Гр_N
go
47
во втором вызове будет использовано значение по умолчанию;
(iii) процедура с возвращаемым параметром:
create procedure Кол_во_Студ_в_Гр_N @n char(7) = '4001', @Кол_во int = 0 out
as
select @Кол_во = count(*)
from Группа, Студент
where Группа = Id and Номер = @n
go
declare @x int
exec Кол_во_Студ_в_Гр_N '5001', @x out
select @x
go
Системная ХП (СХП) sp_helptext выводит текст ХП:
sp_helptext 'Кол_во_Студ_в_Гр_N'
go
В ХП можно создавать временные таблицы, имя временной таблицы начинается с символа #, временная таблица существует пока не завершено выполнение процедуры, в которой
она была создана. Временная таблица доступна в ХП, вызываемых из ХП, в которой она была создана.
Создадим и заполним таблицу Подразделение:
create table Подразделение (Id int, Наименование char(20), Подчинено int);
go
insert into Подразделение values (1, 'Гуап', null);
insert into Подразделение values (2, 'Факультет 4', 1);
insert into Подразделение values (3, 'Кафедра 43', 2);
insert into Подразделение values (4, 'Лаборатория 23-10', 3);
go
Тогда следующая ХП позволит вывести перечень подразделений, входящей в состав
заданного:
create procedure Орг_стр_ра @Подразд char(20)
as begin
declare @n int
select @n = 0
select @n = Id from Подразделение where Наименование = @Подразд
create table #Иерарх (Наимен char(20))
insert into #Иерарх
select Наименование from Подразделение where Подчинено = @n
select @n = 0
while @n <> (select count(*) from #Иерарх)
begin
select @n = count(*) from #Иерарх
48
insert into #Иерарх
select П1.Наименование
from Подразделение П1, Подразделение П2
where П1.Подчинено = П2.Id and
П2.Наименование in
(select Наимен from #Иерарх) and
П1.Наименование not in
(select Наимен from #Иерарх)
end
select * from #Иерарх
end
go
exec Орг_стр_ра 'Факультет 4'
go
Помимо ХП SQL поддерживает создание функций пользователя (create function), которые наряду со стандартными могут использоваться при формировании условий выполнения
запросов.
7.3
Лабораторная работа 7
По аналогии с примерами, приведенными в п. 7.2, создать в БД ХП, реализующие:
— вставку с пополнением справочников (вставляется информация о студенте, если указанный номер группы отсутствует в БД, запись добавляется в таблицу с перечнем групп);
— удаление с очисткой справочников (удаляется информация о студенте, если в его
группе нет больше студентов, запись удаляется из таблицы с перечнем групп);
— каскадное удаление (при наличии условия ссылочной целостности no action перед
удалением записи о группе удаляются записи о всех студентах этой группы);
— вычисление и возврат значения агрегатной функции (на примере одного из запросов
из задания);
— формирование статистики во временной таблице (например, для рассматриваемой
БД — для каждого факультета: количество групп, количество обучающихся студентов, количество изучаемых дисциплин, количество сданных дисциплин, средний балл по факультету).
Самостоятельно предложить и реализовать ПЗ или ХП, демонстрирующие использование конструкций, описанных в п. 7.1.
49
Содержание отчета:
— тексты ХП и их вызовов;
— наборы данных, возвращаемые ХП.
Варианты заданий приведены в ПРИЛОЖЕНИИ.
50
8.
8.1
Триггеры
Виды триггеров
Триггер — это процедура, запускаемая автоматически при модификации данных в таблице (insert, update, delete), позволяющая реализовать более сложные проверки, чем это возможно с помощью ограничений на значения данных и ссылочной целостности. Триггер всегда относится к одной таблице и к одной или нескольким из указанных операций. Некоторые
SQL-севера (например, ASE) ограничивают количество триггеров тремя, по одному для каждой операции модификации данных.
Триггеры могут быть двух уровней:
(i) уровня строки1 (for each row) — запускаются для каждой строки таблицы, затронутой изменением;
(ii) уровня оператора — запускаются для каждой из инструкций insert, update, delete,
применяемой к таблице.
Триггеры могут быть одного из трех видов:
(i) after — триггер запускается после внесения изменений в таблицу;
(ii) before — триггер запускается до внесения изменений в таблицу2;
(iii) instead of — триггер запускается вместо внесения изменений в таблицу.
У таблицы может быть только один триггер instead of для каждой из операций модификации данных.
В течение выполнения триггера существуют две таблицы inserted и deleted. Таблица inserted содержит новые версии строк (вставленные оператором insert или измененные оператором update). Таблица deleted содержит старые версии строк (удаленные оператором delete
или подлежащие изменению оператором update). Ссылка на указанные таблицы производится так же, как на основные таблицы БД.
8.2
Создание триггеров в диалекте Transact SQL
Триггер создается оператором языка SQL, имеющим следующий синтаксис:
1
2
В MS SQL не поддерживаются
В MS SQL не поддерживаются
51
create trigger <имя триггера>
on <имя таблицы>
{ for | after | instead of } { [ insert ] [ , ] [ update ] [ , ] [ delete ] }
as
<оператор>
Если при создании триггера использована директива for, after действует по умолчанию,
т. е. триггер запускается после внесения изменений в таблицу, в том числе после проверки
ограничений на значения данных и ссылочной целостности, выполнения каскадных операций. При создании триггера должна быть указана хотя бы одна из операций, при выполнении
которой он запускается.
MS SQL допускает создание для одной таблицы нескольких триггеров вида after, с помощью СХП sp_settriggerorder, можно указать, какой из триггеров будет запускаться первым, а какой последним, остальные триггеры будут стартовать случайным образом.
Переопределение триггера осуществляет оператор alter trigger, синтаксис к которого
аналогичен create trigger, для удаления триггера служит drop trigger <имя триггера>. СХП
sp_helptrigger <имя таблицы> выводит информацию о триггерах, а sp_helptext <имя триггера> возвращает определение триггера. Работу конкретных или всех триггеров для таблицы
можно приостановить и возобновить директивой alter table <имя таблицы> { enable | disable
} trigger { all | <имя триггера> [ , … ] } или операторами enable trigger и disable trigger.
Примеры триггеров:
(i) ограничение численности студентов в группе:
create trigger Вст_Студент
on Студент
instead of insert
as
if exists (select * from Студент
where Группа in (select Группа from inserted)
group by Группа
having count(Ном_Зач) >= 20)
print 'Слишком много студентов в группе'
else
insert into Студент select * from inserted
go
(ii) удаление группы, если в ней больше нет студентов:
52
create trigger Уд_Студент
on Студент
for delete
as
delete Группа
where Id in (select Группа from deleted) and not exists
(select * from Студент where Группа = Id )
go
(iii) контроль перерасхода стипендиального фонда:
create trigger Изм_Студент
on Студент
for update
as
if (select sum(Стипендия) from Студент) > 1000000
begin
print 'Стипендиальный фонд превышен'
rollback transaction
end
go
Рассмотренные триггеры относятся к классу DML, в них не разрешены инструкции:
— { alter | create | drop } { database | index };
— alter table, затрагивающие столбцы, первичный ключ и unique.
Часто СУБД поддерживают DDL триггеры, а так же триггеры входа.
8.3
Лабораторная работа 8
По аналогии с примерами, приведенными в п. 8.2, создать:
— триггеры каждого вида для каждой из операций, для обеспечения активной целостности данных;
— триггеры, реализующие вычисления в БД.
Содержание отчета:
— тексты триггеров;
— SQL операторы и наборы данных, иллюстрирующие работу триггеров.
Варианты заданий приведены в ПРИЛОЖЕНИИ.
53
9.
9.1
Индексация данных
Принципы индексации данных
В современных БД количество записей в таблицах может исчисляться миллионами
(например, БД связанные с населением, БД, используемые в службах техподдержки и контакт-центрах крупных корпораций), как следствие, сложные запросы в таких БД могут выполняться неприемлемо долго. Одним из способов решения этой проблемы является индексация данных.
В основе принципов индексации лежит тот факт, что в отсортированных массивах данных возможен двоичный поиск, который работает существенно быстрее, чем линейный.
Предположим, что в таблице Студент 1024 записи, тогда линейный поиск заданного студента потребует в среднем выполнения 512 операций сравнения (сложность линейного поиска N/2). Если данные отсортированы, возможен двоичный поиск: берется средний элемент,
сравнивается с ключом поиска, если ключ меньше — процедура поиска продолжается в
верхней половине массива, иначе — в нижней. В случае двоичного поиска для нахождения
записи о заданном студенте по уникальному ключу потребуется всего 10 операций сравнения
(сложность двоичного поиска log2(N)).
На практике в СУБД, чаще всего, реализуют индексы в форме сбалансированных деревьев, а основной эффект достигается за счет минимизации числа страниц, считываемых из
внешней памяти:
(i) СУБД хранят данные таблиц в виде цепочек страниц, типовые размеры которых соответствуют 2K, 4K, 8K или 16K (размер страницы задается при создании БД);
(ii) размер БД, как правило, не позволяет разместить ее в полном объеме в оперативной
памяти;
(iii) обмен с внешней памятью (чаще всего БД располагается на жестком диске) является гораздо более медленной операцией, чем чтение/запись оперативной;
следовательно, чем меньше страниц будет считываться из внешней памяти в оперативную в ходе выполнения запроса, тем быстрее он будет выполнен.
Решение об использовании индексов принимает оптимизатор запросов СУБД, таким
образом:
(i) задача разработчика БД — предложить систему индексов исходя из потенциального
множества запросов, которые к ней будут выполняться;
54
(ii) задача оптимизатора — построить как можно больше возможных планов выполнения запросов и выбрать план с минимальной стоимостью выполнения.
Создание индекса в простейшем случае обеспечивает оператор:
create index <имя индекса>
on <имя таблицы> ( <имя столбца 1> [ , <имя столбца 2> [ , … ] ] )
Например, если в таблице Студент используется поиск по ФИО, ускорить его поможет индекс:
create index Студент_ФИО
on Студент (ФИО)
go
9.2
Рекомендации по выбору индексов
Индексы ускоряют выборку данных, но приводят к расходу дополнительной памяти
(индекс дублирует часть данных основной таблицы) и дополнительным затратам времени
при модификации данных (вставка/удаление/модификация записей таблиц требует модификации и сортировки индекса). Выбор системы индексации в базе данных является нетривиальной задачей, вместе с тем можно дать ряд рекомендаций.
Индексы следует создавать:
(i) для полей, по которым происходит отбор данных (указанных в разделе where запросов);
(ii) для полей, используемых при сортировке выбираемых запросами наборов данных
(указанных в разделе order by запросов);
(iii) для полей, по которым происходит отбор диапазонов (указанных в конструкции between … and или эквивалентных логических выражениях, заданных в разделе where запросов);
(iv) для полей, по которым осуществляется соединение таблиц (указанных в конструкции join ... on или эквивалентных логических выражениях, заданных в разделе where запросов).
Индексы не следует создавать:
(i) для полей, по которым не происходит отбора записей и сортировки (не используемых в разделе where и других перечисленных выше разделах запросов);
(ii) для полей, содержащих мало различных значений: пол, группа крови и др., чем
больше различных значений содержит столбец таблицы, тем более эффективно будет рабо-
55
тать созданный по нему индекс (в массивах, содержащих много повторяющихся значений
двоичный поиск деградирует до линейного);
(iii) для таблиц, содержащих мало записей.
Относительно п. (iii) можно заметить следующее. Часто в БД используются небольшие
справочные таблицы, которые могут занимать всего одну страницу (2..16К) (в БД университета примером такой таблицы может быть Факультет, Номер — 1 байт, Название —
char(150) — 150 байт, длина записи — 151 байт + служебная информация, 10 факультетов,
размер таблицы ~1510 байт). Как было отмечено выше, основной эффект от использования
индексов состоит в минимизации количества страниц, читаемых из внешней памяти. Если
таблица умещается на одной или небольшом количестве страниц, создание индекса для нее
никак не повлияет на скорость выполнения запросов.
Как правило, индексы нельзя создавать для столбцов типов: bit, text, image. Поля типа
bit часто упаковываются в байты — 8 подряд идущих битовых полей в записи таблицы займут один байт. Поля типов text, image и эквивалентные им, предназначенные для хранения
текстовых и двоичных данных большого размера, не хранятся в таблицах. Они располагаются на отдельных страницах памяти, указатели на которые содержатся в записях таблиц БД.
9.3
Операторы языка SQL для создания и удаления индексов
В более общем случае синтаксис оператора для создания индекса выглядит следующим
образом:
create [ unique ] [ clustered | nonclustered ] index <имя индекса>
on <имя таблицы> ( <имя столбца 1> [ asc | desc ] [ , … ] )
[ with { fillfactor | max_rows_per_page } = n ]
go
Индекс может быть:
(i) Уникальным или неуникальным (директива unique). Уникальный индекс не допускает дублирования значений, как следствие, попытка добавить в таблицу строки, содержащие значения, уже имеющееся в столбцах индекса будет отклонена. По умолчанию (директива unique опущена), создается неуникальный индекс. Часто СУБД реализуют ограничение
unique, задаваемое в { alter | create } table путем создания уникального некластерного индекса. Создание уникального индекса не будет выполнено, если столбцы содержат повторяющиеся значения и не являются not null.
(ii) Кластерным или некластерным (директивы clustered и nonclustered). Создание кластерного индекса приводит к тому, что записи таблицы физически упорядочиваются в соответствии с порядком, определенным индексом. Как следствие, у таблицы может быть только
56
один кластерный индекс. Количество некластерных индексов зависит от СУБД, типовым
значением является 249. Часто СУБД реализуют ограничение primary key, задаваемое в { alter | create } table путем создания уникального кластерного индекса.
(iii) Простым (состоит из одного столбца) или составным (включает несколько столбцов таблицы). СУБД могут ограничивать число столбцов индекса и длину записи индекса
(сумма длин столбцов, образующих индекс), для MS SQL 2008 это 16 столбцов и 900 байт
соответственно. Записи в индексе могут упорядочиваться по возрастанию (asc) или по убыванию (desc), порядок сортировки задается отдельно для каждого столбца таблицы.
Имя индекса должно быть уникально в рамках таблицы.
Значение n для параметра fillfactor задает % заполнения страниц индекса при его создании (0 или 100 соответствует 100%). Альтернативой является указание количества строк
на странице индекса — max_rows_per_page (0 или 256 соответствует полному заполнению
страниц).
Созданный индекс, как и другие объекты БД, существует, пока не будет явно удален
директивой:
drop index <имя таблицы>.<имя индекса> [ , … ]
go
Перестройку индекса в целях устранения фрагментации осуществляет директива:
alter index <имя индекса> on <имя таблицы> rebuild
go
Информацию о существующих индексах таблицы возвращает СХП:
sp_helpindex <имя таблицы>
go
С учетом изложенного можно уточнить рекомендации по выбору индексов, кластерные
индексы целесообразно создавать для столбцов:
(i) являющихся первичными ключами;
(ii) по которым осуществляется сортировка;
(iii) по которым осуществляется отбор диапазонов;
(iv) по которым осуществляется соединение таблиц;
(v) которые нечасто изменяются.
Для столбцов, не включенных в кластерный индекс, создаются некластерные, при этом
следует стремиться что бы:
(i) индекс «покрывал» запрос — содержал все используемые в запросе ключи поиска;
(ii) осуществлялось префиксное сканирование индекса (обход по дереву).
57
Заметим, что сплошное сканирование индекса, как правило, эффективнее сканирования
таблицы, так как строки индекса короче и, считывая одну страницу индекса из внешней памяти, СУБД получает сведения о большем количестве строк, чем при чтении страницы данных таблицы.
9.4
Генерация тестовых данных
Для тестирования выполнения запросов и оценки эффективности выбранных индексов
необходимо, что бы в таблицах БД было количество записей, сопоставимое с тем, которое
будет достигнуто (возможно, в течение нескольких лет) в ходе реальной эксплуатации системы. Один из способов решения этой задачи — использование генератора тестовых данных.
Существует достаточно много продуктов, осуществляющих генерацию данных для тестирования запросов к БД в ходе ее разработки, здесь можно упомянуть CASE-системы
(например, SAP Sybase PowerDesigner), отдельные продукты (например, EMS Data Generator
for SQL Server), а так же продукты, входящие в средства разработки (например, в Microsoft
Vusual Studio).
Для генерации тестовых данных в SAP Sybase PowerDesigner необходимо:
(i) Создать источник данных ODBC (см. методические указания к лабораторной работе
№3).
(ii) Выполнить реинжиниринг в SAP Sybase PowerDesigner: File\Reverse Engineer\Data
Base…, после чего задать имя модели, выбрать СУБД, нажать Ok, выбрать созданный источник данных ODBC (в поле Using Data Source), установить соединение (Connect), нажать OK,
выбрать таблицы и еще раз нажать OK..
(iii) Собственно сгенерировать тестовые данные: Database\Generate Test Data\Number
of Rows, задать необходимое количество записей для каждой таблицы и нажать OK (рис. 9.1).
58
Рисунок 9.1.
Для тестирования запросов можно создать отдельную БД, один из способов сделать это
— получить скрипт для создания БД, выполнив команды Задачи\Сформировать сценарии… из контекстного узла соответствующей БД в дереве объектов Management Studio, после чего ответить на вопросы мастера, получить скрипт и выполнить скрипт в новой БД.
При вставке тестовых данных следует учитывать, что добавление данных, содержащих
значения полей identity возможно, если это предварительно разрешено директивой set
identity_insert <имя таблицы> { on | off }.1
Для выполнения скриптов большого размера (миллионы insert into) можно воспользоваться утилитой командной строки sqlcmd, утилита имеет достаточно много параметров, если соединение с SQL-сервером установлено из Management Studio, достаточно указать имя
БД и путь к файлу:
1
Некоторые SQL-сервера (в том числе MS SQL) требуют явного сброса (off) перед разрешением вставки полей
identity для другой таблицы.
59
sqlcmd -d"Университет1" -i"d:\mydb\mssql2008\testdataУниверситет1.sql"
9.5
Анализ использования индексов
Решение об использовании индексов принимает оптимизатор запросов СУБД, проанализировать используется ли индекс и каким образом происходит его сканирование можно из
планов выполнения запросов. Один из способов просмотра планов выполнения запросов —
использование директивы:
set showplan_text { on | off }
go
или более подробно с помощью директивы:
set showplan_all { on | off }
go
В MS SQL директивы отображения планов отключают выполнение запросов, для выполнения запросов необходимо установить значения параметров в off.
Для графического отображения плана выполнения запроса используются команды меню Management Studio Запрос\Показать предполагаемый план выполнения и
За-
прос\Включить действительный план выполнения. В планах выполнения запросов отображается
довольно
много
ru/library/ms175913(SQL.105).aspx)
и
пиктограмм
видов
сообщений
(http://msdn.microsoft.com/ru(http://msdn.microsoft.com/ru-
ru/library/ms191158(SQL.105).aspx), об использовании индексов говорят сообщения { clustered | nonclustered } index { seek | scan }, seek соответствует префиксному сканированию, а
scan — сплошному.
Повлиять на решения, принимаемые планировщиком запросов относительно порядка
соединения таблиц, использования индексов, алгоритмов выполнения соединений и др.
можно
с
помощью
подсказок
—
hints
ru/library/ms173815%28v=sql.105%29.aspx)
(http://msdn.microsoft.com/ru(http://msdn.microsoft.com/ru-
ru/library/ms173815%28v=sql.105%29.aspx).
9.6
Лабораторная работа 9
Произвести генерацию и вставку тестовых данных в БД, выполнить запросы из ЛР 4..6,
зафиксировать планы и время выполнения запросов, создать систему индексов для ускорения выполнения запросов, повторно выполнить запросы из ЛР 4..6, зафиксировать планы и
время выполнения.
60
Содержание отчета:
— операторы для создания индексов;
— планы и время выполнения запросов до и после индексации, пояснение произошедших изменений.
Варианты заданий приведены в ПРИЛОЖЕНИИ.
61
10.
Транзакции и блокировки
10.1
Транзакции
Транзакция — совокупность действий в БД, которая должна быть или полностью
успешно выполнена или полностью отклонена. Типовым примером транзакции является перевод средств в банковской системе:
(i) сумма списывается со счета A;
(ii) средства зачисляются на счет B.
Если между (i) и (ii) происходит программно-аппаратный сбой, БД оказывается в несогласованном состоянии. Механизм транзакций гарантирует, что либо оба действия будут
успешно выполнены, и БД окажется в согласованном состоянии, либо не будет выполнено
ни одно из них, и БД окажется в согласованном состоянии, в котором она находилась до
начала транзакции. Говоря о транзакциях, обычно отмечают ряд их свойств сокращенно обозначаемых как ACID (http://msdn.microsoft.com/ru-ru/library/ms190612%28SQL.105%29.aspx).
По умолчанию SQL-сервера рассматривают каждый оператор insert, update, delete, select и др. как отдельную транзакцию. Для оформления в виде транзакции группы операторов
используются директивы:
(i) begin { tran | transaction } [ <имя транзакции> ] — начинает новую транзакцию
(http://msdn.microsoft.com/ru-ru/library/ms188929(SQL.105).aspx);
(ii) save { tran | transaction } <имя точки сохранения> — сохраняет текущее состояние транзакции (http://msdn.microsoft.com/ru-ru/library/ms188378(SQL.105).aspx);
(iii) commit { tran | transaction } [ <имя транзакции > ] — фиксирует в БД изменения,
произведенные транзакцией, транзакция завершается успешно (http://msdn.microsoft.com/ruru/library/ms190295(SQL.105).aspx);
(iv) rollback { tran | transaction } [ { <имя транзакции > | <имя точки сохранения> } ]
— отменяет изменения, произведенные транзакцией, либо до первого begin transaction
(транзакция
завершается
с
откатом),
либо
до
указанной
(http://msdn.microsoft.com/ru-ru/library/ms181299(SQL.105).aspx).
Пусть в БД существует таблица Счет:
точки
сохранения
62
create table Счет (
Номер int primary key,
ФИО varchar(50) not null,
Сумма money not null)
go
оформим с помощью транзакций перевод денег с одного счета на другой:
begin tran
update Счет set Сумма = Сумма + 100 where Номер = 33
if (select Сумма from Счет where Номер = 44) >= 100
begin
update Счет set Сумма = Сумма - 100 where Номер = 44
commit tran
end
else
rollback tran
go
Таким образом, если на счете с номером 44 недостаточно средств, транзакция будет отклонена.
Транзакции могут использоваться в теле ХП и Т, рекомендации по оформлению транзакций приведены в (http://msdn.microsoft.com/ru-ru/library/ms187844(v=sql.105).aspx) и
(http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc32300.1550/html/sqlug/X16153.h
tm).
Транзакция может находиться в одном из четырех возможных состояний, текущее состояние
хранится
в
глобальной
системной
переменной
@@transtate
(http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32300.1550/html/sqlu
g/X27470.htm), в MS SQL не поддерживается.
Транзакции могут быть вложенными, текущий уровень вложенности содержится в глобальной
системной
переменной
@@trancount
(http://msdn.microsoft.com/ru-
ru/library/ms189336(v=sql.105).aspx).
SQL-сервера могут поддерживать два режима начала транзакций — неявные транзакции (транзакция начинается автоматически при выполнении очередного оператора DML) и
явные (транзакция начинается после begin transaction), переключение режимов осуществляет
директива
set
implicit_transactions
{
on
|
off
}
(http://msdn.microsoft.com/ru-
ru/library/ms190230%28v=sql.105%29.aspx)1.
1
В других диалектах SQL встречается set chained { on | off }
(http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc32300.1550/html/sqlug/X82816.htm)
63
Так как транзакции устанавливают и удерживают блокировки, при их разработке следует
придерживаться
ряда
рекомендаций
(http://msdn.microsoft.com/ru-
ru/library/ms187484%28v=sql.105%29.aspx).
10.2
Проблемы многопользовательского доступа к данным, их решение с помощью
блокировок
SQL-сервер обычно выполняет смесь транзакций, поступающих от различных пользовательских приложений. Количество пользователей в современных корпоративных информационных системах (КИС) может исчисляться сотнями, тысячами и более. В литературе
[Ошибка! Источник ссылки не найден.] [Ошибка! Источник ссылки не найден.]
[Ошибка! Источник ссылки не найден.] обычно рассматривается три основных проблемы
параллелизма, проиллюстрируем их на примере приведенной выше таблицы Счет:
(i) Потеря результатов обновления:
Момент времени
Транзакция А
Транзакция B
t1 —————— Чтение кортежа: Номер = 33, ————————————
Сумма = 200
t2 —————— ————————————
Чтение кортежа: Номер = 33,
Сумма = 200
t3 —————— Запись кортежа: Номер = 33, ————————————
Сумма = 220
t4 —————— ————————————
Запись кортежа: Номер = 33,
Сумма = 190
t5 —————— Фиксация транзакции
Фиксация транзакции
Рис. 10.1
Таким образом, параллельное выполнение транзакций A (увеличивает сумму на 20) и B
(списывает со счета 10) приводит к появлению некорректной суммы (190), вместо 210, как
было бы при последовательном выполнении транзакций.
(ii) Зависимость от незафиксированных результатов:
Момент времени
Транзакция А
Транзакция B
t1 —————— ————————————
Чтение кортежа: Номер = 33,
Сумма = 200
t2 —————— ————————————
Запись кортежа: Номер = 33,
64
Сумма = 190
t3 —————— Чтение кортежа: Номер = 33, ————————————
Сумма = 190
t4 —————— Запись кортежа: Номер = 33, ————————————
Сумма = 210
t5 —————— ————————————
Откат транзакции
t6 —————— Фиксация транзакции
————————————
Рис. 10.2
Транзакция A воспользовалась незафиксированным изменениям, выполненным транзакцией B, в результате сумма оказалась равной 210, а не 220, как при последовательном
выполнении транзакций.
(iii) Несовместный анализ:
Момент времени
Транзакция А
Транзакция B
t1 —————— Чтение кортежа: Номер = 33, ————————————
Сумма = 200; ∑ = 200
t2 —————— Чтение кортежа: Номер = 34, ————————————
Сумма = 300; ∑ = 500
t3 —————— ————————————
Чтение кортежа: Номер = 35,
Сумма = 300; ∑ = 500
t4 —————— ————————————
Запись кортежа: Номер = 35,
Сумма = 120
t5 —————— ————————————
Чтение кортежа: Номер = 33,
Сумма = 200
t6 —————— ————————————
Запись кортежа: Номер = 33,
Сумма = 150
t7 —————— Чтение кортежа: Номер = 35, ————————————
Сумма = 100; ∑ = 620
t8 —————— Фиксация транзакции
Фиксация транзакции
Рис. 10.3
Таким образом, транзакция A, суммируя средства на счетах, возвращает ∑ = 620, в то
время как на момент ее завершения на данных счетах ∑ = 570.
65
Чаще всего для решения проблем многопользовательского доступа к данным СУБД используют блокировки:
(i) блокировку записи — X-Lock (от eXclusive Lock);
(ii) блокировку чтения — S-Lock (от Shared Lock).
Если транзакция планирует осуществить чтение кортежа, она предварительно устанавливает блокировку чтения, если запись — блокировку записи, при этом действуют следующие правила совместимости блокировок:
(i) если на кортеж установлена X-Lock, то запросы на блокировку от других транзакций
отклоняются (ставятся в очередь);
(ii) если на кортеж установлена S-Lock, то запросы на S-Lock от других транзакций
удовлетворяются, а запросы на X-Lock отклоняются (ставятся в очередь).
Совместимость блокировок иллюстрирует таблица:
Таблица 10.1
Совместимость блокировок
Транзакция A \ B
X
S
—
X
Нет
Нет
Да
S
Нет
Да
Да
—
Да
Да
Да
Таким образом, при отсутствии блокировок удовлетворяется любой запрос на блокировку от другой транзакции, S-Lock совместимы с другими S-Lock, S-Lock и X-Lock, а так же
две X-Lock не совместимы.
Блокировки используются в сочетании в алгоритмом двухфазного блокирования:
(i) прежде чем начать работу с кортежем, транзакция должна установить блокировку;
(ii) после разблокирования какого-либо кортежа транзакция не должна устанавливать
других блокировок.
Иначе говоря, все операции блокирования должны предшествовать первой операции
разблокирования.
Блокировки позволяют решить проблемы многопользовательского доступа к данным:
(i) Решение проблемы потери результатов обновления:
Момент времени
Транзакция А
t1 —————— X-Lock кортежа: Номер = 33
Транзакция B
————————————
66
t2 —————— Чтение кортежа: Номер = 33, X-Lock кортежа: Номер = 33
Сумма = 200
t3 —————— Запись кортежа: Номер = 33, Ожидание
Сумма = 220
t4 —————— Фиксация транзакции, снятие Ожидание
блокировок
t5 —————— ————————————
Чтение кортежа: Номер = 33,
Сумма = 220
t6 —————— ————————————
Запись кортежа: Номер = 33,
Сумма = 210
t7 —————— ————————————
Фиксация транзакции, снятие
блокировок
Рис. 10.4
(ii) Решение проблемы зависимости от незафиксированных результатов:
Момент времени
Транзакция А
Транзакция B
t1 —————— ————————————
X-Lock кортежа: Номер = 33
t2 —————— ————————————
Чтение кортежа: Номер = 33,
Сумма = 200
t3 —————— X-Lock кортежа: Номер = 33
Запись кортежа: Номер = 33,
Сумма = 190
t4 —————— Ожидание
————————————
t5 —————— ————————————
Откат транзакции,
блокировок
снятие
t6 —————— Чтение кортежа: Номер = 33, ————————————
Сумма = 200
t7 —————— Запись кортежа: Номер = 33, ————————————
Сумма = 220
t8 —————— Фиксация транзакции, снятие ————————————
блокировок
Рис. 10.5
(iii) Решение проблемы несовместного анализа:
Момент времени
Транзакция А
Транзакция B
67
t1 —————— S-Lock кортежа: Номер = 33
————————————
t2 —————— Чтение кортежа: Номер = 33, ————————————
Сумма = 200; ∑ = 200
t3 —————— S-Lock кортежа: Номер = 34
X-Lock кортежа: Номер = 35
t4 —————— Чтение кортежа: Номер = 34, Чтение кортежа: Номер = 35,
Сумма = 300; ∑ = 500
Сумма = 100
t5 —————— S-Lock кортежа: Номер = 35
Запись кортежа: Номер = 35,
Сумма = 120
t6 —————— Ожидание
X-Lock кортежа: Номер = 33
t7 —————— Ожидание
Ожидание
Рис. 10.6
Таким образом, блокировки решают проблемы многопользовательского доступа, но создают проблему тупиков. Поэтому СУБД ведет граф зависимости транзакций, показывающий, какая транзакция ожидает снятия блокировок другой транзакцией. Если в графе обнаруживается цикл, одна из транзакций приносится в жертву — принудительно откатывается и
перезапускается через некоторый интервал времени или инициируется исключительная ситуация. В качестве жертвы, как правило, выбирается транзакция, сделавшая наименьший
объем изменений в БД.
Разработчик БД может повлиять на механизм обработки тупиков, задавая:
(i) приоритет (относительную важность) текущего сеанса по отношению к другим сеансам,
работающим
с
БД,
с
помощью
директивы
set
deadlock_priority
(http://msdn.microsoft.com/ru-ru/library/ms186736%28v=sql.105%29.aspx).
(ii) периодичность проверки возникновения тупика с помощью вызова СХП
sp_configure
"deadlock
checking
period"
(http://infocenter.sybase.com/archive/topic/com.sybase.help.ase_15.0.sag1/html/sag1/sag1149.htm).
На практике СУБД используют не две, а до двух десятков видов блокировок для достижения большей производительности и минимизации вероятности появления тупиков.
Кроме того, блокировки могут применяться к объектам на различных уровнях иерархии:
строке,
странице,
ru/library/ms175519%28v=sql.105%29.aspx)
ru/library/ms186396%28v=sql.105%29.aspx).
таблице
(http://msdn.microsoft.com/ru(http://msdn.microsoft.com/ru-
68
10.3
Уровни изоляции транзакций
Блокировки снижают возможности параллельного выполнения транзакций и приводят
к появлению тупиков, поэтому в стандарте языка SQL предложены 4 уровня изоляции транзакций. Выбирая уровень изоляции транзакций, разработчик БД может повлиять на логику
работы блокировок. В зависимости от выбранного уровня изоляции транзакций допускается
или исключается появление в БД следующих ситуаций:
(i) dirty read — «грязное» чтение, данная ситуация возникает, когда транзакция читает
незафиксированные данные, измененные другой транзакцией:
Момент времени
Транзакция А
Транзакция B
t1 —————— begin transaction
————————————
t2 —————— ————————————
begin transaction
t3 —————— update Счет
set Сумма = Сумма - 100
where Номер = 777
————————————
t4 —————— ————————————
select sum(Сумма)
from Счет
where Номер < 1000
t5 —————— ————————————
commit transaction
t6 —————— rollback transaction
————————————
Рис. 10.7
Как следует из рис. 10.7 транзакция B, получила значение суммы, не соответствующее
состоянию БД.
(ii) non-repeatable read — неповторяемое чтение, данная ситуация возникает, когда
транзакция дважды читает данные и получает различные значения из-за изменений, сделанных другой транзакцией:
Момент времени
Транзакция А
Транзакция B
t1 —————— begin transaction
————————————
t2 —————— ————————————
begin transaction
t3 —————— select Сумма from Счет
where Номер = 777
————————————
t4 —————— ————————————
update Счет
set Сумма = Сумма - 100
where Номер = 777
69
t5 —————— ————————————
commit transaction
t6 —————— select Сумма from Счет
where Номер = 777
————————————
t6 —————— commit transaction
————————————
Рис. 10.8
Как следует из рис. 10.8, транзакция A дважды выполняя один и тот же запрос, получает различные результаты.
(iii) phantom rows — фантомные строки, данная ситуация возникает, когда одна транзакция отбирает набор данных запросом по некоторому условию, вторая транзакция добавляет, удаляет или изменяет данные, так что строки перестают или начинают удовлетворять
условию запроса, после чего первая транзакция повторяет запрос и получает другой набор
данных:
Момент времени
Транзакция А
Транзакция B
t1 —————— begin transaction
————————————
t2 —————— ————————————
begin transaction
t3 —————— select * from Счет
where Номер < 1000
————————————
t4 —————— ————————————
insert into Счет values
(999, ‘Сыроежкин’, 200)
t5 —————— ————————————
commit transaction
t6 —————— select * from Счет
where Номер < 1000
————————————
t6 —————— commit transaction
————————————
Рис. 10.9
Как следует из рис. 10.9, транзакция A дважды выполняя один и тот же запрос, получит
различные наборы данных. Случаи (ii) и (iii) могут показаться похожими, основное отличие в
том, что в (ii) речь идет о стабильности отдельных строк, а в (iii) — наборов строк (диапазонов).
Исключить появление рассмотренных ситуаций в БД можно, изменяя уровни изоляции
транзакций:
70
Таблица 10.2
Уровни изоляции транзакций
Номер
Наименование
dirty read
non-repeatable
read
phantom rows
0
read uncommitted
Да
Да
Да
1
read committed
Нет
Да
Да
2
repeatable read
Нет
Нет
Да
3
serializable
Нет
Нет
Нет
Как следует из таблицы 2, на самом низком уровне изоляции (read committed) проявляются все рассмотренные выше ситуации, повышение уровня изоляции последовательно
приводит к их исключению. На самом высоком уровне изоляции (serializable) транзакции
выполняются так, как если бы работа с БД происходила последовательно. Низкие уровни
изоляции транзакций снижают вероятность возникновения тупиков и повышают параллелизм, так как на этих уровнях СУБД изменяет логику работы блокировок, отступая от рассмотренной в п. 2 ().
Установку текущего уровня изоляции транзакций осуществляет директива set transaction isolation level { read committed | read uncommitted | repeatable read | serializable 1}. Установленный уровень изоляции транзакций действует до конца сеанса или до тех пор, пока не
будет изменен другой директивой set transaction isolation level. Получить значение текущего
уровня изоляции транзакций можно, или выполнив директиву dbcc useroptions (MS SQL) или
опросив глобальную системную переменную @@isolation (в MS SQL не поддерживается).
Задать уровень изоляции для отдельного запроса или таблицы, не изменяя уровень изоляции, установленный для сеанса, можно с помощью табличных подсказок — hints
(http://msdn.microsoft.com/ru-ru/library/ms187373%28SQL.105%29.aspx)2.
10.4
Конфигурирование блокировок, отчеты о блокировках
Для конфигурирования блокировок используются директивы, позволяющие задать:
(i) тайм-аут установления блокировки — set lock_timeout <количество миллисекунд>,
значение параметра находится в глобальной системной переменной @@lock_timeout;
1
Некоторые SQL сервера допускают указание, как названия, так и номера уровня, в MS SQL не поддерживается.
2
В других диалектах SQL для этих целей может использоваться директива at isolation
(http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00938.1502/html/locking/locking105.htm)
71
(ii) максимальное количество блокировок — sp_configure ”locks”, <количество>, если
в качестве количества указан 0 — память распределяется автоматически; параметр, в том
числе,
оказывает
влияние
на
решение
о
повышении
уровня
блокировки
(http://msdn.microsoft.com/ru-ru/library/ms184286%28v=sql.105%29.aspx).
Для получения отчетов о блокировках используются директивы:
(i) sp_lock [ <процесс 1> [ , … ] ] — возвращает сведения о всех блокировках или блокировках
для
указанных
процессов
(сеансов
работы)
(http://msdn.microsoft.com/ru-
ru/library/ms187749%28SQL.105%29.aspx);
(ii) sp_who [ <имя пользователя> ] — возвращает сведения о всех процессах или процессах
указанного
пользователя
(http://msdn.microsoft.com/ru-
ru/library/ms174313%28v=sql.105%29.aspx).
Альтернативными способами получения сведений о блокировках являются системные
представления
sys.dm_tran_locks
ru/library/ms190345%28v=sql.105%29.aspx),
(http://msdn.microsoft.com/ru-
sys.syslockinfo
(http://msdn.microsoft.com/ru-
ru/library/ms189497(v=sql.105).aspx).
SQL-сервера поддерживают средства для предоставления отчетов о тупиках, это могут
быть
СХП,
как
например
(http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36273.1572/html/sprocs/X25628.
htm) или отдельные приложения, например, SQL Server Profiler, входящий в состав MS SQL.
SQL Server Profiler позволяет отображать данные о тупиках, в том числе, в графическом
виде. Запуск SQL Server Profiler — Пуск\Все программы\Microsoft SQL Server 2008\ Средства обеспечения производительности\SQL Server Profiler. Для выполнения трассировки
— Файл\Создать трассировку…\Соединить, далее на вкладке Выбор событий установить
флаг Показать все события, после чего выбрать раздел Locks, установить флаг Deadlock
graph (другие установленные флаги можно сбросить) и нажать кнопку Запустить
(http://msdn.microsoft.com/ru-ru/library/ms190465%28v=sql.105%29.aspx).
10.5
Лабораторная работа 10
Смоделировать в БД1 грязное чтение, неповторяемое чтение, фантомы, изменяя уровень изоляции транзакций продемонстрировать их исключение, сформировать отчеты о блокировках, пояснить их содержание.
1
В Management Studio каждое новое окно запроса является отдельным сеансом работы с БД.
72
Смоделировать в БД тупик (взаимную блокировку), получить с помощью приложения
SQL Server Profiler отчет о тупике, пояснить его содержание.
Содержание отчета:
— скрипты транзакций;
— отчеты о блокировках и пояснения к ним;
— отчет о тупике, пояснения к нему.
Варианты заданий приведены в ПРИЛОЖЕНИИ.
73
11.
Репликация данных
11.1
Понятие репликации
Современные СУБД, в том числе SQL-сервера, позволили реализовать базовый принцип построения информационных систем «одно данное должно храниться в одном месте и
изменяться один раз» за счет:
(i) интеграции в БД данных, необходимых всем пользователям приложений информационной системы предприятия;
(ii) обеспечения корректного многопользовательского доступа к данным, хранящимся в
БД.
Вместе с тем, по мере увеличения масштабов информационных систем такое решение
оказалось не всегда:
(i) удобным, из-за того что часть данных используется локально (в таких системах как
отдел социальной защиты районной администрации—городской комитет—министерство
или штаб-квартира транснациональной корпорации—представительство в стране—офисы в
регионах не обязательно вся информация, вводимая на нижнем уровне, должна стекаться в
центр);
(ii) возможным, из-за отсутствия постоянных высокоскоростных соединений между
территориально удаленными офисами или офисом и мобильными пользователями.
Для решения таких задач были предложены механизмы репликации, которые реализуют автоматическую передачу данных между несколькими БД, осуществляя в том числе:
— горизонтальную (отбор подмножества строк таблиц);
— вертикальную (отбор подмножества столбцов таблиц);
— смешанную фрагментацию (сочетание горизонтальной и вертикальной).
В литературе различают гомогенные и гетерогенные распределенные БД (РБД). В гомогенных на всех узлах используется одна и та же СУБД и часто БД с тождественными
структурами. В гетерогенных используются СУБД различных производителей, а БД могут
иметь различные структуры.
Типовыми случаями, когда оказываются полезны механизмы репликации, являются
(http://msdn.microsoft.com/ru-ru/library/ms151787%28v=sql.105%29.aspx)
или
(http://msdn.microsoft.com/ru-ru/library/ms152485%28v=sql.105%29.aspx)
или
(http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc32580.1571/doc/html/san1271105
857839.html).
74
11.2
Основные объекты и виды репликации
Основными объектами (понятиями), используемыми при организации репликации в MS
SQL являются:
(i) издатель (publisher) — сервер, предоставляющий данные для репликации из находящейся на нем БД;
(ii) публикация (publication) — набор объектов БД, предназначенных для репликации;
(iii) статья (article) — объект БД, предназначенный для репликации, включенный в
публикацию (таблица, представление, ХП и др.);
(iv) подписчик (subscriber) — сервер, получающий реплицируемые данные в расположенную на нем БД;
(v) подписка (subscription) — указывает, данные каких публикаций должен получать
подписчик, может быть:
— принудительной (push) — синхронизацию данных инициирует издатель;
— по запросу (pull) — синхронизацию данных инициирует подписчик;
(vi) распространитель (distributor) — сервер, выступающий в роли посредника между
издателями и подписчиками, в БД которого хранятся метаданные, состояние репликации,
очереди сообщений и др.
Различают следующие виды репликации:
(i) репликацию транзакций (transactional replication) — используется для синхронизации данных между SQL-серверами, между которыми существует постоянное соединение
(корпоративные сети);
(ii) репликацию слиянием (merge replication) — используется для синхронизации данных между SQL-серверами, между которыми отсутствует постоянное соединение (мобильные пользователи);
(iii) репликацию моментального снимка (snapshot replication) — используется для однократной или периодической передачи копии данных, в том числе для первоначальной синхронизации БД при репликации транзакций и слиянием.
MS SQL предоставляет для организации репликации следующие средства:
(i) мастера, входящие в состав Management Studio;
(ii) набор СХП;
(iii) объекты RMO (Replication Management Object).
75
Рассмотрим способ (ii)1.
11.3
Организация репликации транзакций
Издателем, подписчиком и распространителем могут быть как различные SQL-сервера,
так и один и тот же. Организуем репликацию на одном и том же сервере:
(i) Создание БД для публикации и подписки:
use master
go
create database main
go
create database node
go
(ii) Создание и заполнение таблиц в БД издателя:
use main
go
create table Факультет
(Номер tinyint primary key,
Название char(50) not null)
go
create table Группа
(Номер char(7) primary key,
Факультет tinyint null references Факультет (Номер)
on update cascade on delete no action,
Курс tinyint null)
go
create table Студент
(N_зач int primary key,
ФИО char(50) not null,
Группа char(7) null references Группа (Номер)
on update cascade on delete no action)
go
insert into Факультет values (4, 'Вычислительных ...')
go
insert into Факультет values (5, 'Информационных ...')
go
insert into Группа values ('4531', 4, 5)
go
insert into Группа values ('4831', 4, 2)
1
Встречаются альтернативные подходы, как например в
(http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc32410.1571/doc/html/san1273713816060.html), где
вводится RCL (Replication Command Language), как расширение SQL.
76
go
insert into Группа values ('5531', 5, 5)
go
insert into Группа values ('5831', 5, 2)
go
insert into Студент values (33, 'Лисичкин', '4531')
go
insert into Студент values (34, 'Сыроежкин', '4831')
go
insert into Студент values (35, 'Груздев', '5531')
go
insert into Студент values (36, 'Сморчков', '5831')
go
create table Дисциплина
(Код int primary key,
Название char(50) not null)
go
create table Специальность
(Номер char(7) primary key,
Название char(50) not null,
Квалификация char(20))
go
create table Уч_план
(Код int primary key,
Шифр_дисц char(5) not null,
Специальность char(7) not null references Специальность (Номер)
on update cascade on delete no action,
Семестр tinyint not null,
Дисциплина int not null references Дисциплина (Код)
on update cascade on delete no action,
Форма_контр char(2) not null,
unique (Шифр_дисц, Специальность, Семестр))
go
alter table Группа
add Специальность char(7) references Специальность (Номер)
on update cascade on delete no action
go
alter table Группа
add Кол_во_студ tinyint
go
alter table Студент
add Ср_балл real
go
create table Оценка
77
(N_зач int not null references Студент (N_зач)
on update cascade on delete no action,
Код_УП int not null references Уч_план (Код)
on update no action on delete no action,
Оценка tinyint,
Зачет bit,
Дата datetime,
primary key (N_зач, Код_УП))
go
insert into Дисциплина values (1, 'Программирование')
go
insert into Дисциплина values (2, 'Базы данных')
go
insert into Специальность values ('230105', 'Программное ...', 'инженер')
go
insert into Специальность
values ('230201', 'Информационные ...', 'инженер')
go
update Группа
set Специальность = '230105'
where Факультет = 4
go
update Группа
set Специальность = '230201'
where Факультет = 5
go
insert into Уч_план values (1, 'ОПД3', '230105', 2, 1, 'Э')
go
insert into Уч_план values (2, 'ОПД5', '230201', 3, 1, 'Э')
go
insert into Уч_план values (3, 'СД3', '230105', 6, 2, 'Э')
go
insert into Уч_план values (4, 'СД2', '230201', 5, 2, 'Э')
go
Схема полученной БД приведена на рис. 11.1.
78
Специальность
Номер
Название
Квалификация
Студент
N_зач
ФИО
Группа
Ср_балл
Уч_план
Группа
Код
Шифр_дисц
Номер
Специальность
Факультет
Семестр
Курс
Дисциплина
Специальность
Форма_контр
Оценка
Кол_во_студ
N_зач
Код_УП
Оценка
Зачет
Дата
Дисциплина
Факультет
Код
Номер
Название
Название
Рис. 11.1
Здесь Дисциплина — общий справочник наименований дисциплин, а таблица Уч_план
показывает, студенты какой специальности какие дисциплины в каких семестрах изучают.
(iii) Создание распространителя (назначение сервера в качестве распространителя) выполняет СХП sp_adddistributor, процедура должна вызываться на распространителе в базе
данных master1, единственным обязательным параметром является имя сервера (на сервере
должен быть запущен Агент SQL Server — команда Запустить из контекстного меню одноименного узла в обозревателе объектов Management Studio):
use master
go
exec sp_adddistributor 'AVB-XEON'
go
1
Если распространителем является отдельный сервер, СХП выполняется так же на издателе в БД master.
79
СХП sp_dropdistributor удаляет распространитель (выполняется на распространителе в
любой
базе
данных,
за
исключением
базы
данных
распространителя),
а
СХП
1
sp_helpdistributor выводит сведения о распространителе (выполняется на издателе) .
(iv) Создание базы данных распространителя осуществляет СХП sp_adddistributiondb,
единственным обязательным параметром является имя БД, процедура должна вызываться на
распространителе в базе данных master (если БД не существует, она создается, иначе помечается как БД распространителя и в ней создаются системные таблицы):
use master
go
exec sp_adddistributiondb 'distr'
go
СХП sp_changedistributiondb изменяет БД распространителя.2
(v) Настройку издателя на использование БД распространителя реализует СХП
sp_adddistpublisher, обязательными параметрами являются имя издателя и имя БД распространителя, процедура должна вызываться на распространителе в любой БД (перед ее вызовом должны быть выполнены sp_adddistributor и sp_adddistributiondb):
exec sp_adddistpublisher @publisher = 'AVB-XEON', @distribution_db = 'distr'
go
(vi)
Настройку
параметров
реплицируемой
БД
осуществляет
СХП
sp_replicationdboption, вызываемая на издателе или подписчике в любой БД, обязательными
параметрами являются имя БД, имя и значение свойства, для репликации слиянием значение
параметра @optname устанавливается в 'merge publish', для других — как показано ниже:
exec sp_replicationdboption @dbname = 'main', @optname = 'publish',
@value = 'true'
go
(vi) Создание моментального снимка для публикации транзакций реализует СХП
sp_addpublication, процедура вызывается на издателе в публикуемой БД, единственным обязательным параметром является имя публикации (здесь предполагается, что публикация
предназначена для репликации в БД на сервере в корпусе Гастелло, куда будут передаваться
сведения, относящиеся к студентам 1 и 2 курсов):
use main
go
exec sp_addpublication @publication = 'Гастелло', @repl_freq = 'continuous',
1
Аналогичные процедуры определены и для других объектов репликации, обсуждаемых ниже
(http://msdn.microsoft.com/ru-ru/library/ms174364(v=sql.105).aspx).
2
Аналогичные процедуры определены и для других объектов репликации, обсуждаемых ниже.
80
@allow_sync_tran = 'true'
go
Параметр @repl_freq имеет значение 'continuous' по умолчанию, которое говорит о
том, что реплицироваться будут изменения, выполненные всеми транзакциями, попавшими в
журнал. Параметр @allow_sync_tran разрешает для создаваемой публикации немедленно обновляемые подписки. Параметр @status определяет доступность публикации для подписчиков 'activ'e или 'inactive' (по умолчанию).
(vii) Создание агента моментального снимка для публикации выполняет СХП
sp_addpublication_snapshot, вызываемая на издателе в публикуемой БД, единственным обязательным является имя публикации:
use main
go
exec sp_addpublication_snapshot @publication = 'Гастелло', @frequency_type = 1
go
Параметр @frequency_type может принимать значения 1 — однократно, 4 — ежедневно
(по умолчанию), 8 — еженедельно, 16 — ежемесячно и др. Параметр @frequency_interval
позволяет дополнительно указать день недели, рабочий/выходной день и др.
(viii) Создание статей реализует СХП sp_addarticle, вызываемая на издателе в публикуемой БД, в качестве параметров процедуре передаются имя публикации, имя статьи и имя
объекта БД, входящего в статью:
use main
go
exec sp_addarticle @publication = 'Гастелло', @article = 'Г_Факультет',
@source_object = 'Факультет'
go
exec sp_addarticle @publication = 'Гастелло', @article = 'Г_Группа',
@source_object = 'Группа'
go
exec sp_addarticle @publication = 'Гастелло', @article = 'Г_Студент',
@source_object = 'Студент'
go
exec sp_addarticle @publication = 'Гастелло', @article = 'Г_Дисциплина',
@source_object = 'Дисциплина'
go
exec sp_addarticle @publication = 'Гастелло', @article = 'Г_Специальность',
@source_object = 'Специальность'
go
exec sp_addarticle @publication = 'Гастелло', @article = 'Г_Уч_план',
@source_object = 'Уч_план'
go
exec sp_addarticle @publication = 'Гастелло', @article = 'Г_Оценка',
@source_object = 'Оценка'
81
go
Имя статьи должно быть уникальным в рамках публикации, чаще всего публикуемыми
объектами БД являются таблицы, но могут быть и представления, ХП и др.
(ix) Горизонтальную фрагментацию задают СХП sp_articlefilter и sp_articleview, вызываемые на издателе в публикуемой БД, процедуры создают фильтр и представление, обеспечивающие фрагментацию данных:
use main
go
exec sp_articlefilter @publication = 'Гастелло', @article = 'Г_Группа',
@filter_name = 'Ф_Г_Группа', @filter_clause = 'Курс in (1, 2)'
go
exec sp_articleview @publication = 'Гастелло', @article = 'Г_Группа',
@view_name = 'П_Г_Группа', @filter_clause = 'Курс in (1, 2)'
go
exec sp_articlefilter @publication = 'Гастелло', @article = 'Г_Студент',
@filter_name = 'Ф_Г_Студент', @filter_clause =
'Группа in (select Номер from Группа where Курс in (1, 2))'
go
exec sp_articleview @publication = 'Гастелло', @article = 'Г_Студент',
@view_name = 'П_Г_Студент', @filter_clause =
'Группа in (select Номер from Группа where Курс in (1, 2))'
go
exec sp_articlefilter @publication = 'Гастелло', @article = 'Г_Уч_план',
@filter_name = 'Ф_Г_Уч_план', @filter_clause = 'Семестр <= 4'
go
exec sp_articleview @publication = 'Гастелло', @article = 'Г_Уч_план',
@view_name = 'П_Г_Уч_план', @filter_clause = 'Семестр <= 4'
go
exec sp_articlefilter @publication = 'Гастелло', @article = 'Г_Оценка',
@filter_name = 'Ф_Г_Оценка', @filter_clause =
'N_зач in (
select N_зач from Студент, Группа
where Студент.Группа = Группа.Номер and Курс in (1, 2))
and Код_УП in
(select Код from Уч_план
where Семестр <= 4)'
go
exec sp_articleview @publication = 'Гастелло', @article = 'Г_Оценка',
@view_name = 'П_Г_Оценка', @filter_clause =
'N_зач in (
select N_зач from Студент, Группа
where Студент.Группа = Группа.Номер and Курс in (1, 2))
and Код_УП in
(select Код from Уч_план
where Семестр <= 4)'
82
go
Параметр @filter_clause определяет условие отбора, записываемое по тем же синтаксическим правилам, что в разделе where SQL запросов. Фильтрация записей в таблице Группа
осуществляется по условию принадлежностb группы к 1 или 2 курсу ('Курс in (1, 2)'), для
Студент — по принадлежности группе соответствующего курса, для Уч_план — по номеру
семестра ('Семестр <= 4'), для Оценка — по комбинации двух последних условий.
(x) Вертикальную фрагментацию задает СХП sp_articlecolumn, вызываемая на издателе
в публикуемой БД, процедура позволяет исключать (@operation = 'drop') или включать
(@operation = 'add') столбец таблицы в публикацию:
use main
go
exec sp_articlecolumn @publication = 'Гастелло',
@article = 'Г_Студент', @column = 'Ср_балл', @operation = 'drop'
go
(xi) Активацию публикации можно осуществить с помощью СХП sp_changepublication,
вызов производится на издателе в публикуемой БД:
use main
go
exec sp_changepublication 'Гастелло', 'status', 'active'
go
(xii) Создание подписки осуществляет СХП sp_addsubscription, вызов производится на
издателе в публикуемой БД:
use main
go
exec sp_addsubscription @publication = 'Гастелло',
@subscriber = 'AVB-XEON', @destination_db = 'node',
@subscription_type = 'push', @update_mode = 'sync tran'
go
При вызове процедуры указывается имя публикации, сервера, целевой БД, подписка
является принудительной (@subscription_type = 'push'), параметр @update_mode = 'sync tran'
включает поддержку немедленно обновляемых подписок.
(xiii) Для начальной синхронизации БД main и node необходимо вызвать агента репликации — команда Запустить задание на шаге… из контекстного меню узла, соответствующего агенту моментального снимка (ветвь дерева объектов — Агент SQL Server\Задания),
имя агента можно уточнить, вызвав СХП:
use main
go
83
exec sp_helppublication_snapshot @publication = 'Гастелло'
go
Альтернативным способом является использование СХП:
use main
go
sp_startpublication_snapshot 'Гастелло'
go
В результате вызова агента моментального снимка в БД node будут созданы те же таблицы, что и в БД main, также будут переданы данные таблиц в соответствии с настроенной
горизонтальной (в БД node будут только данные о группах и студентах младших курсов и
учебных планах первых четырех семестров) и вертикальной (в БД node в таблице Студент
столбец Ср_балл будет отсутствовать) фрагментацией.
11.4
Выполнение репликации транзакций
Репликация выполняется автоматически, выполним вставку:
use main
go
insert into Студент (N_зач, ФИО, Группа) values (37, 'Волнушкин', '4831')
go
Так как группа 4831 относилась ко второму курсу, запись реплицируется в БД node.
Вследствие того, что подписка была создана как немедленно объявляемая, для корректного
выполнения репликации из БД node в БД main необходимо задать проверку подлинности
SQL Server или Windows для пользователя, редактирующего подписчика (выполняется на
подписчике в базе данных подписки):
use node
go
sp_link_publication 'AVB-XEON' , 'main' , 'Гастелло' , 1
go
В БД main таблица Оценка, не заполнялась, выполним вставку:
use node
go
insert into Оценка (N_зач, Код_УП, Оценка, Зачет, Дата)
values (34, 1, 5, 0, '2009/05/01')
go
insert into Оценка (N_зач, Код_УП, Оценка, Зачет, Дата)
values (36, 2, 4, 0, '2009/01/05')
go
84
После выполнения команд соответствующие строки появляются в БД main. Выполним
модификацию:
use node
go
update Студент set ФИО = 'Строчков' where ФИО = 'Сморчков'
go
После выполнения команды изменение отражается в БД main.
11.5
Лабораторная работа 11
Реализовать в БД репликацию, включающую вертикальную и горизонтальную фрагментацию данных таблиц. Выполнить модификацию данных, иллюстрирующую работу репликации.
Содержание отчета:
— скрипты для создания объектов репликации;
— операторы модификации данных для тестирования репликации, наборы данных в
таблицах.
Варианты заданий приведены в ПРИЛОЖЕНИИ.
85
12.
12.1
Хранилища данных
Концепция хранилищ данных
Создание и широкое внедрение корпоративных информационных систем (КИС), происходившее в конце прошлого века, привело к появлению парадоксальной ситуации: информация есть, информации много, но воспользоваться ею для анализа эффективности работы
предприятия, решения других экономических и социальных аналитических задач достаточно
сложно.
Существовало несколько причин такого положения дел, среди которых отмечались:
(i) несовместимость проектов отдельных подсистем КИС (наследие эпохи «стихийной
автоматизации», когда отдельные подсистемы создавались различными разработчиками с
применение различных СУБД и систем программирования);
(ii) плохую приспособленность существующих СУБД к выполнению аналитических запросов (выполнение сложного аналитического запроса к строго нормализованной базе данных требует выполнения соединений для большого количества таблиц и, как следствие, приводит к большим затратам времени на обработку запроса).
Ответом на данное положение дел со стороны разработчиков СУБД стала концепция
хранилищ данных — DW (Data Warehouse) и появление специального класса серверов для
реализации DW. Одним из первых подобных продуктов был Oracle Express Server, являвшийся многомерной СУБД для создания MDB (Multi-Dimensional Database), в котором физически хранение данных организовано в виде гиперкуба, имеющего N измерений (рис. 12.1).
Р
е
г
и
о
н
П родукты
Время
Рис. 12.1.
В ячейках гиперкуба хранятся значения показателей, например объемы продаж в натуральном и денежном измерении, полученная прибыль, затраты и пр. для конкретных сочетаний – например, для данного продукта, проданного в определенном регионе за заданный
86
временной промежуток. Измерения позволяют упорядочить данные в соответствии с хронологическими, географическими или другими классификационными признаками.
Альтернативное решение было предложено в Sybase IQ, где данные, рассматриваемые
пользователем в виде гиперкуба, находились в реляционной БД, в которой использовались
существенно отличающиеся механизмы (по сравнению с реляционными СУБД), такие как
вертикальное хранение (сначала данные 1-го столбца таблицы, затем 2-го и т. д.) и Bitmap
индексы.
Появление концепции хранилищ данных привело к разделению современных ИС на два
основных класса (рис. 12.2):
(i) системы OLTP (On-Line Transaction Processing), решающие в основном оперативные
(«учетные») задачи;
(ii) СППР (системы поддержки принятия решений) или DSS (Decision Support System).
Хранилище данных
Средства загрузки
данных
...
К
л
и
е
н
т
ы
–
А
н
а
л
и
т
и
к
и
Источники информации
Оперативные
БД
...
OLTP системы
Рис. 12.2.
Системы OLTP реализуются на основе файл-серверных или клиент-серверных архитектур, имеют нормализованные структуры баз данных, предназначены для автоматизации повседневных задач, решаемых персоналом «нижнего» звена (учет клиентов, договоров, заказов, взаиморасчетов, запасов и пр.).
87
В отличие от OLTP-систем, системы DSS ориентированы на руководство «верхнего»
звена — старших менеджеров, принимающих стратегические решения по проблемам развития корпорации.
Подход, основанный на использовании MDB для построения DSS, получил название
OLAP (On-Line Analytical Processing) или MOLAP (Multi-dimensional OLAP), а альтернативный, основанный на реляционных СУБД — ROLAP (relational OLAP).
Ниже в таблице дается краткое сравнение OLTP- и OLAP-технологий.
Таблица 12.1.
Сравнение OLTP- и OLAP-систем
Признак
OLTP
OLAP
Типы запросов
Статистические
(подсчет Аналитические (анализ тенсуммарных итогов)
денций, формирование прогнозов)
Типы отчетов
Стандартные (заранее регла- Произвольные, динамические
ментированные: за день, ме- (последовательности итерасяц, квартал, год и пр.)
тивно уточняемых отчетов)
Уровень агрегации данных
Детализированные данные
Возраст данных
Оперативные и выгруженные Исторические за большие
из БД в архив
временные периоды
Частота обновления, объем Высокая, добавление
добавляемых данных
большими порциями
В основном суммарные
не- Низкая, загрузка больших
информационных массивов,
загруженные ранее данные
не изменяются
Упорядоченность данных
Отсутствует
Данные упорядочены хронологически и по другим измерениям
Структура БД
Нормализованная, большое Ненормализованная, таблица
количество связанных таблиц фактов, ссылающаяся на несколько таблиц измерений
(ROLAP)
Системы, основанные на хранилищах данных, в последствии стали рассматривать как
предназначенные для сбора, интеграции и аналитической обработки информации, накопленной в различных источниках, таких как: оперативные БД (БД OLTP-систем), «плоские» файлы с наборами данных, данные внешних источников (например, данные доступные в глобальных сетях) и др. Важную роль в таких системах играют метаданные (базы метаданных)
— знания о структуре существующих баз данных и информационных источников, типе и
формате хранимой информации, связях между информационными объектами, семантике ин-
88
формационных объектов и их атрибутов, ограничениях на значения данных и ссылочной целостности и пр. (рис. 12.3).
Средства
формирования
запросов
Аналитич.
системы
СППР
Хранилище
данных
Метаданные
Средства
доступа
Средства конвертации и
обобщения
Репозиторий
БД
Репозиторий
БД
...
CASE
АИС
CASE
АИС
Рис. 12.3.
В настоящее время для организации хранилищ данных реализованы семейства продуктов (IBM Business Intelligence, Oracle Business Intelligence, SAP BusinessObjects и др.), обеспечивающие решение следующих групп задач:
(i) проектирование и реализация хранилища данных;
(ii) работа с метаданными, подготовка и загрузка информации в хранилище — ETL
(Extract, Transform, Load);
(iii) собственно выполнение аналитических запросов.
Процесс проектирования и реализации хранилища данных включает несколько этапов:
(i) Анализ типов запросов, составление словаря: формулируются типичные запросы
различных категорий пользователей, выявляются состав интересующих показателей, степень
детализации информации и пр.
(ii) Анализ информационных источников: собирается информация о структурах имеющихся баз данных, строится/анализируется нормализованная модель данных.
89
(iii) Выбор измерений: выбираются признаки по которым необходимо строить «срезы»
данных для различных категорий пользователей – склады, временные периоды, статьи затрат, виды продукции и пр.
(iv) Построение иерархии измерений: анализируются выбранные измерения, и строится
их иерархия (типовыми иерархическими структурами являются: временные – день, неделя,
месяц, квартал, год; географические – объект, район, город, регион, страна).
(v) Построение таблиц фактов: определяется состав фактических показателей для различных сочетаний и/или уровней измерений.
12.2
Схемы хранилищ данных
Рассмотрим вопросы проектирования хранилища данных с помощью технологии
ROLAP. В отличие от OLAP, хранилища, создаваемые по технологии ROLAP, базируются на
реляционной модели данных, при этом база данных имеет денормализованную структуру. В
литературе обсуждаются две основные схемы БД для организации хранилища данных:
(i) схема «звезда», в которой одна большая таблица фактов, представляющая собой соединение таблиц, содержащих сведения по различным измерениям, связана с несколькими
небольшими по размерности справочными таблицами;
(ii) схема «снежинка» – развитие схемы «звезда», позволяет представить иерархию измерений.
На рис. 12.4. приведен пример структуры реляционной базы данных для организации
DW, построенной по схеме «звезда». Здесь таблица с информацией о продажах и доходах
(таблица фактов) связана с четырьмя таблицами – измерениями (время, продукты, заказчики,
склады).
90
Заказчик
Заказчик ID
Название
Адрес
Сфера деятельности
Телефон
Факс
Проду кт
Проду кт ID
Описание
Категор ия
Цена за единицу
<FP,HG>
<FP>
<FP>
<FP>
<FP>
<FP>
<FP>
<FP>
<FP>
<FP>
Продажи
Проду кт ID
<FP>
Время ID
<FP>
Заказчик ID
<FP>
Склад ID
<FP>
Всего
<FP>
Доход
<FP>
Время ID = Вр емя ID
Склад
Склад ID
<FP,HG>
Название
<FP>
Адрес
<FP>
М енеджер <FP>
Телефон
<FP>
Факс
<FP>
Время
Время ID
<FP,HG>
Дата
<FP,HNG>
День недели
<FP>
День месяца
<FP>
Номер недели
<FP>
М есяц
<FP>
Квар тал
<FP>
Год
<FP>
Рис. 12.4.
Измерения в многомерной модели данных могут быть иерархическими. Как правило, в
любом хранилище данных может быть выделено, по крайней мере, два иерархических измерения:
(i) иерархия временных периодов: день–неделя–месяц–квартал–год;
(ii) пространственная иерархия: объект–регион–страна.
Если в рассмотренной модели явным образом отразить иерархию измерений, то получится схема «снежинка» (рис. 12.5.).
91
Категор ия
Категор ия ID
<FP>
Название
<FP>
Заказчик
Заказчик ID
Название
Адрес
Сфера деятельности
Телефон
Факс
Склад ID
Регион ID
Название
Адрес
М енеджер
Телефон
Факс
Склад
<FP,HG>
<FP>
<FP>
<FP>
<FP>
<FP>
<FP>
Регион
Регион ID
<FP>
Стр ана ID
<FP>
Название
<FP>
Стр ана
Стр ана ID
<FP>
Название
<FP>
<FP,HG>
<FP>
<FP>
<FP>
<FP>
<FP>
Проду кт
Проду кт ID
Категор ия ID
Описание
Цена за единицу
Продажи
Проду кт ID
<FP>
Время ID
<FP>
Заказчик ID
<FP>
Склад ID
<FP>
Всего
<FP>
Доход
<FP>
Неделя
Неделя ID
М есяц ID
Название
Номер в году
М есяц
М есяц ID
<FP>
Квар тал ID
<FP>
Название
<FP>
<FP>
<FP>
<FP>
<FP>
День
<FP>
<FP>
<FP>
<FP>
День ID
Неделя ID
Дата
День недели
День месяца
<FP>
<FP>
<FP>
<FP>
<FP>
Год
Год ID
Название
<FP>
<FP>
Квар тал
Квар тал ID
<FP>
Год ID
<FP>
Название
<FP>
Рис. 12.5.
В рассмотренных примерах в хранилище находилась только одна таблица фактов, в
общем случае таких таблиц может быть несколько. В разных таблицах фактов информация
может агрегироваться по различным сочетаниям измерений или на различных иерархических
уровнях. Например, для анализа тенденций развития фирмы на протяжении длительных временных периодов может потребоваться информация на уровне финансового года (рис. 12.6),
а не отдельного дня, как в моделях на рис. 12.4 и 12.5.
92
Категор ия
Категор ия ID
<FP>
Название
<FP>
Агрегир ованные продажи
Стр ана ID
<FP>
Категор ия ID
<FP>
Год ID
<FP>
Всего
<FP>
Проду кт
Проду кт ID
Категор ия ID
Описание
Цена за единицу
Заказчик
Заказчик ID
Название
Адрес
Сфера деятельности
Телефон
Факс
Склад ID
Регион ID
Название
Адрес
М енеджер
Телефон
Факс
Склад
<FP,HG>
<FP>
<FP>
<FP>
<FP>
<FP>
<FP>
Регион
Регион ID
<FP>
Стр ана ID
<FP>
Название
<FP>
Продажи
Проду кт ID
<FP>
Время ID
<FP>
Заказчик ID
<FP>
Склад ID
<FP>
Всего
<FP>
Доход
<FP>
Стр ана
Стр ана ID
<FP>
Название
<FP>
День
День ID
Неделя ID
Дата
День недели
День месяца
Неделя
Неделя ID
<FP>
М есяц ID
<FP>
Название
<FP>
Номер в году <FP>
М есяц
М есяц ID
<FP>
Квар тал ID
<FP>
Название
<FP>
<FP>
<FP>
<FP>
<FP>
<FP>
<FP>
<FP>
<FP>
<FP>
Год
Год ID
<FP>
Название <FP>
Квар тал
Квар тал ID
<FP>
Год ID
<FP>
Название
<FP>
Рис. 12.6.
12.3
Проектирование хранилища
Проектирование DW может быть выполнено, например, в SAP Sybase PowerDesigner.
Одним из способов получения многомерной модели — Multidimensional Diagram
93
(http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc38058.1650/doc/html/rad1232020
635006.html), является ее генерация на основе физической модели оперативной БД:
(i) Произведем реинжиниринг БД main (рис. 12.7).
Факультет
Дисциплина
Номер
tinyint
<pk>
Название
char(50)
msrepl_tran_v ersion uniqueidentifier
Код
int
<pk>
Название
char(50)
msrepl_tran_v ersion uniqueidentifier
Номер = Факультет
Код = Дисциплина
Группа
Уч_план
Номер
Факультет
Курс
Специальность
Кол_во_студ
msrepl_tran_v ersion
char(7)
<pk>
tinyint
<fk2>
tinyint
char(7)
<fk1>
tinyint
uniqueidentifier
Код
Шифр_дисц
Специальность
Семестр
Дисциплина
Форма_контр
msrepl_tran_v ersion
int
char(5)
char(7)
tinyint
int
char(2)
uniqueidentifier
<pk>
<ak>
<ak,fk2>
<ak>
<fk1>
Номер = Группа
Номер = Специальность
Студент
N_зач
ФИО
Группа
Ср_балл
msrepl_tran_v ersion
int
<pk>
char(50)
char(7)
<fk>
real
uniqueidentifier
N_зач = N_зач
Номер = Специальность
Код = Код_УП
Специальность
Номер
Название
Квалификация
msrepl_tran_v ersion
char(7)
<pk>
char(50)
char(20)
uniqueidentifier
Оценка
N_зач
Код_УП
Оценка
Зачет
Дата
msrepl_tran_v ersion
int
<pk,fk1>
int
<pk,fk2>
tinyint
bit
datetime
uniqueidentifier
Рис. 12.7.
(ii) Выполним команды меню Tools\Multidimensional Objects\Generate Cube и выберем
таблицы фактов и измерений, исключим ненужные атрибуты, измерения и сгенерируем модель (рис. 12.8).
94
Оценка
Оценка - Факультет_Группа_Студент
Оценка
Зачет
N_зач
Код_УП
Факультет_Группа_Студент
Факультет Номер <h:1>
Название
Группа Номер
<h:2>
Факультет
N_зач
<h:3>
Группа
Hierarchy_1 <Default> <h>
Оценка - Специальность_Группа_Студент
Оценка - Дисциплина_Уч_план
Специальность_Группа_Студент
Дисциплина Код <h:1>
Название
Уч_план Код
<h:2>
Дисциплина
Специальность Номер <h:1>
Название
Квалификация
Группа Номер
<h:2>
Специальность
N_зач
<h:3>
Группа
Hierarchy_1 <Default> <h>
Hierarchy_1 <Default> <h>
Дисциплина_Уч_план
Рис. 12.8.
(iii) Такие атрибуты как N_Зач, Группа, Код (в Уч_план) не имеют смысла с точки зрения анализа динамики успеваемости, исключим их из модели (рис. 12.9).
95
Оценка
Оценка - Факультет
Оценка
Зачет
Дата
Номер_Факульт
Номер_Спец
Код_Дисц
Факультет
Номер
<h:1>
Название
Hierarchy_1 <Default> <h>
Оценка - Дисциплина
Оценка - Специальность
Специальность
Дисциплина
Код
<h:1>
Название
Номер
<h:1>
Название
Квалификация
Hierarchy_1 <Default> <h>
Hierarchy_1 <Default> <h>
Рис.12. 9.
(iv) Мастер создания кубов автоматически связывает данныt оперативной БД с многомерной. Уточнить связи после модификации многомерной БД можно с помощью редактора
отображения — Tools\Mapping Editor… (рис. 12.10).
96
Рис. 12.10.
12.4
Реализация хранилища
Реализация DW может быть выполнена, например, средствами MS SQL:
(i) Создание проекта в среде Business Intelligence Development Studio (Пуск\Все программы\Microsoft SQL Server 2008\Среда SQL Server Business Intelligence Development Studio) — Файл\Создать\Проект, имя main_as.
97
(ii)
Развертывание
проекта
—
Проект\Свойства\Развертывание(Сервер:
Lo-
calhost)\Ok.
(iii) Создание источника данных — команда Создать источник данных… контекстного меню узла Источники данных в обозревателе решений, Далее\Создать, выбрать сервер и
БД (main), Ok\Далее, установить флаг Использовать текущую учетную запись службы,
Далее\Готово.
(iv) Создание представления источника данных — команда Создать представление
источника… контекстного меню узла Представления источников данных в обозревателе
решений, Далее\(Источник данных: main)\Далее, выбрать таблицы (рис. 12.7), Далее\Готово.
(v) Создание измерений — команда Создать измерение… контекстного меню узла
Измерения в обозревателе решений, Далее, установить флаг Использовать существующую
таблицу, Далее, выбрать таблицу, например Специальность, выбрать столбец имени —
Название, Далее\Готово. Аналогично создаются измерения Факультет и Дисциплина.
(vi) Создание куба — команда Создать куб… контекстного меню узла Кубы в обозревателе решений, Далее, установить флаг Использовать существующую таблицу, Далее,
выбрать таблицу Оценка, Далее\ Далее\ Далее\ Далее\Готово. При создании куба автоматически создаются таблицы измерений.
(vii) Построение (Построение\Построить main_as) и развертывание проекта (Построение\Развернуть main_as) приводят к созданию БД в среде Службы Analysis Service.
(viii) Для просмотра данных куба можно выполнить команду Обзор из контекстного
меню узла, соответствующего кубу в среде Службы Analysis Service или в среде Business Intelligence Development Studio.
12.5
Лабораторная работа 12
Сгенерировать и откорректировать многомерную модель DW, скорректировать отображение данных оперативной БД в DW.
Реализовать куб в среде Business Intelligence Development Studio.
Содержание отчета:
— модели БД и DW, отчет, отражающий Mapping;
— модель и пример обзора куба.
Варианты заданий приведены в ПРИЛОЖЕНИИ.
98
Библиографический список
1. Г. Гарсиа-Молина Г., Ульман Дж. Д., Д. Уидом Д. Системы баз данных. Полный
курс./Пер. с англ. — М.: Вильямс, 2003. — 1088 с.
2. Коннолли Т, Бегг К. Базы данных. Проектирование, реализация и сопровождение.
Теория и практика./Пер. с англ. —3-е изд. — М.: Вильямс, 2003. — 1436 с.
3. Дейт К. Дж. Введение в системы баз данных./Пер. с англ. — 8-е изд. — М.: Вильямс, 2005. — 1138 с.
4. Роб П. Системы баз данных: проектирование, реализация и управление. — 5-е изд.
— СПб.: БХВ - Петербург, 2004. — 1040 с.
5. Мейер Д. Теория реляционных баз данных. /Пер. с англ. — М.: Мир, 1987. — 608 с.
6. Цикритзис Д, Лоховски Ф. Модели данных./Пер. с англ.— М.: Финансы и статистика, 1985.. — 344 с.
7. Дейт К. Руководство по реляционной СУБД DB2./ Пер. с англ. — М.: Финансы и
статистика, 1988. — 320 с.
8. Дейт К. Введение в системы баз данных./ Пер. с англ. — М.: Наука, 1980. — 386 с.
9. Ульман Дж. Основы систем баз данных. /Пер. с англ. — М.: Финансы и статистика,
1983. — 334 с.
10. Хомоненко, А. Д. Базы данных: учебник для высших учебных заведений / А. Д. Хомоненко, В. М. Цыганков, М. Г. Мальцев ; ред. А. Д. Хомоненко. - 6-е изд., доп. и
перераб. - СПб. : КОРОНА-Век, 2010. - 736 с.
11. Карпова, И. П. Базы данных: курс лекций и материалы для практических занятий /
И. П. Карпова. - СПб. : ПИТЕР, 2013. - 240 с.
12. Советов, Б. Я. Базы данных : теория и практика: учебник / Б. Я. Советов, В. В. Цехановский, В. Д. Чертовской. - 2-е изд. - М. : Юрайт, 2012. - 464 с.
99
Содержание
Создание таблиц базы данных _________________________________________ 3
1.
1.1
Базы данных и СУБД ____________________________________________________ 3
1.2
Таблицы ________________________________________________________________ 4
1.3
Ссылочная целостность _________________________________________________ 10
1.4
Лабораторная работа 1 __________________________________________________ 11
Заполнение и модификация таблиц базы данных _______________________ 12
2.
2.1
Вставка данных в таблицы ______________________________________________ 12
2.2
Изменение данных ______________________________________________________ 14
2.3
Удаление данных _______________________________________________________ 14
2.4
Изменение определения таблицы _________________________________________ 15
2.5
Лабораторная работа 2 __________________________________________________ 19
Запросы на языке QBE _______________________________________________ 20
3.
3.1
Язык QBE______________________________________________________________ 20
3.2
Импорт в Access БД MS SQL _____________________________________________ 20
3.3
Разработка запросов ____________________________________________________ 21
3.4
Лабораторная работа 3 __________________________________________________ 25
Запросы на языке SQL: выборка данных ________________________________ 26
4.
4.1
Оператор select _________________________________________________________ 26
4.2
Директивы, используемые в условиях запросов ____________________________ 29
4.3
Лабораторная работа 4 __________________________________________________ 31
Запросы на языке SQL: агрегатные функции ___________________________ 32
5.
5.1
Агрегатные функции ____________________________________________________ 32
5.2
Объединение, пересечение, разность запросов ______________________________ 34
5.3
Лабораторная работа 5 __________________________________________________ 35
Запросы на языке SQL: подзапросы ____________________________________ 36
6.
6.1
Запросы с подзапросам __________________________________________________ 36
6.2
Экзистенциальные запросы ______________________________________________ 38
6.3
Лабораторная работа 6 __________________________________________________ 40
100
Хранимые процедуры ________________________________________________ 41
7.
7.1
Управляющие конструкции в языке SQL __________________________________ 41
7.2
Хранимые процедуры ___________________________________________________ 45
7.3
Лабораторная работа 7 __________________________________________________ 48
Триггеры ___________________________________________________________ 50
8.
8.1
Виды триггеров_________________________________________________________ 50
8.2
Создание триггеров в диалекте Transact SQL ______________________________ 50
8.3
Лабораторная работа 8 __________________________________________________ 52
Индексация данных _________________________________________________ 53
9.
9.1
Принципы индексации данных ___________________________________________ 53
9.2
Рекомендации по выбору индексов _______________________________________ 54
9.3
Операторы языка SQL для создания и удаления индексов ___________________ 55
9.4
Генерация тестовых данных _____________________________________________ 57
9.5
Анализ использования индексов __________________________________________ 59
9.6
Лабораторная работа 9 __________________________________________________ 59
10.
Транзакции и блокировки ____________________________________________ 61
10.1
Транзакции ____________________________________________________________ 61
10.2
Проблемы многопользовательского доступа к данным, их решение с помощью
блокировок 63
10.3
Уровни изоляции транзакций ____________________________________________ 68
10.4
Конфигурирование блокировок, отчеты о блокировках _____________________ 70
10.5
Лабораторная работа 10 _________________________________________________ 71
11.
Репликация данных _________________________________________________ 73
11.1
Понятие репликации ____________________________________________________ 73
11.2
Основные объекты и виды репликации ___________________________________ 74
11.3
Организация репликации транзакций_____________________________________ 75
11.4
Выполнение репликации транзакций _____________________________________ 83
11.5
Лабораторная работа 11 _________________________________________________ 84
12.
Хранилища данных __________________________________________________ 85
101
12.1
Концепция хранилищ данных ____________________________________________ 85
12.2
Схемы хранилищ данных ________________________________________________ 89
12.3
Проектирование хранилища _____________________________________________ 92
12.4
Реализация хранилища __________________________________________________ 96
12.5
Лабораторная работа 12 _________________________________________________ 97
Библиографический список ________________________________________________ 98
ПРИЛОЖЕНИЕ Варианты заданий _______________________________________ 102
102
ПРИЛОЖЕНИЕ
Варианты заданий
1. Создайте базу данных для хранения следующих сведений: алфавитный каталог книг в
библиотеке, читатель, формуляр читателя (выданные и возвращенные книги). Составьте запросы, позволяющие выбрать:
а) читателей, которые брали книги на прошлой неделе;
б) читателей, которые брали книги Ахо и Ульмана;
в) количество книг, находящихся на руках у каждого из читателей;
г) читателей, которые читают только книги жанра «приключения»;
д) читателей, у которых на руках две или более книги одного автора;
е) читателей, прочитавших более ста книг;
ж) читателей, взявших книги, которые больше никому не выдавались;
з) читателей, читающих книги всех жанров;
и) читателей, у которых на руках максимальное количество книг.
2. Создайте базу данных для хранения следующих сведений: кафедра, преподаватель,
дисциплина, группа, курс, вид занятия. Составьте запросы, позволяющие выбрать:
а) преподавателей, ведущих Базы данных на различных факультетах;
б) преподавателей, ведущих как Базы данных, так и Логическое программирование;
в) количество дисциплин для каждого преподавателя;
г) группы, у которых в среднем менее 4-х пар в неделю;
д) преподавателей, которые ведут занятия только на старших курсах;
е) преподавателей, которые ведут более двух видов занятий по одной дисциплине;
ж) преподавателей, ведущих более трех различных дисциплин;
з) преподавателей, ведущих все виды занятий;
и) преподавателей, ведущих занятия в максимальном количестве групп.
3. Создайте базу данных для хранения следующих сведений: турфирма, тур, страна, турист, путевка. Составьте запросы, позволяющие выбрать:
а) туристов, посещавших в прошлом году Италию и Францию;
б) турфирмы, продающие туры в Египет и Турцию;
в) туристов, побывавших только в одной стране;
103
г) туристов, пользовавшихся услугами двух и более турфирм;
д) количество путевок, проданных каждой из турфирм за прошлый год;
е) среднюю цену путевки в Тунис;
ж) туристов, побывавших во Франции более пяти раз;
з) туристов, побывавших во всех странах, в которые предлагаются туры;
и) пары туристов, которые всегда путешествуют вместе.
4. Создайте базу данных для хранения следующих сведений: студент, группа, дисциплина, лабораторная работа, рейтинг за сданную лабораторную работу. Составьте
запросы, позволяющие выбрать:
а) максимальный рейтинг, который может получить студент за работу №8 по БД;
б) работы и рейтинги, сданные и полученные конкретным студентом;
в) дисциплины, у которых есть лабораторные работы с одинаковыми названиями;
г) количество работ, сданных каждым студентом по БД;
д) студентов, у которых средний рейтинг за сданные лабораторные работы по БД
превышает 4;
е) студентов, не сдавших ни одной работы по БД;
ж) лабораторные по БД, которые нужно досдать Сыроежкину из группы 4000;
з) студентов, получивших одинаковый рейтинг за все работы по БД;
и) студентов, сдавших все работы по БД.
5. Создайте базу данных для хранения следующих сведений: студент, группа, факультет,
конференция, тема доклада. Составьте запросы, позволяющие выбрать:
а) студентов первого факультета, выступавших на конференции Информатика;
б) темы докладов студентов для заданной группы;
в) выступления, подготовленные двумя студентами различных факультетов;
г) количество докладов для каждой конференции;
д) среднее количество докладов, сделанных студентами третьего факультета на
конференциях;
е) студентов четвертого факультета не выступавших на конференциях;
ж) студентов, выступивших на трех или большем числе конференций;
з) студентов, выступивших на всех конференциях;
и) пары студентов, всегда выступающие вместе.
6. Создайте базу данных для хранения следующих сведений: компьютерный магазин,
модель компьютера, комплектующие. Составьте запросы, позволяющие выбрать:
а) модели компьютеров, в которых используются винчестеры Samsung;
104
б) модели компьютеров, в которых используются как накопители DVD, так и
FDD;
в) количество моделей, продаваемых в каждом из магазинов;
г) модели компьютеров, не имеющие накопителей DVD;
д) магазины, в которых средняя цена компьютера ниже, чем в других;
е) магазины, в которых продается наибольшее количество моделей;
ж) магазины, в которых не продаются модели, укомплектованные одновременно
оборудованием Intel и Samsung;
з) модели компьютеров, укомплектованные всеми типами периферийных
устройств;
и) модели компьютеров, продающиеся во всех магазинах.
7. Создайте базу данных для хранения следующих сведений: номер маршрута автобуса,
остановка, транспортная компания. Составьте запросы, позволяющие выбрать:
а) маршруты, выполняемые заданной компанией;
б) маршруты, которыми можно доехать до Дворцовой площади;
в) маршруты, имеющие общие остановки;
г) количество маршрутов, обслуживаемых каждой компанией;
д) компании, обслуживающие наибольшее число маршрутов;
е) компании, средняя продолжительность маршрутов которых ниже чем у других;
ж) компании, маршруты которых не останавливаются на Дворцовой площади;
з) компании, у которых нет маршрутов короче чем 10 остановок;
и) маршруты, которые включают все остановки заданного маршрута.
8. Создайте базу данных для хранения следующих сведений: театр, спектакль, жанр, актер. Составьте запросы, позволяющие выбрать:
а) спектакли жанра комедия;
б) спектакли, в которых занят заданный актер;
в) спектакли, идущие более чем в одном театре;
г) количество спектаклей для каждого из театров;
д) театры, в которых количество драм превышает число комедий;
е) спектакли, в которых занято наибольшее число актеров;
ж) спектакли одного актера;
з) театры, в которых идут спектакли всех жанров;
и) актеров, занятых только в одном театре.
105
9. Создайте базу данных для хранения следующих сведений: аптека, медикамент, цена,
производитель. Составьте запросы, позволяющие выбрать:
а) аптеки, в которых есть лекарства заданного производителя;
б) аптеки, в которых продается одно и то же лекарство различных производителей;
в) цена аспирина в различных аптеках;
г) количество наименований лекарств, продающихся в каждой из аптек;
д) аптеки, в которых цена аспирина минимальна;
е) средняя стоимость аспирина компании АБВ в аптеках;
ж) аптеки, в которых нет медикаментов, заданного производителя;
з) пары производителей, у которых нет ни одного одинакового медикамента;
и) аптеки, в которых есть все лекарства.
10. Создайте базу данных для хранения следующих сведений: фильм, студия, жанр, актер. Составьте запросы, позволяющие выбрать:
а) список фильмов, снятых заданной студией за заданный период;
б) перечень студий, в фильмах которых играл заданный актер;
в) актеров, снимавшихся как в комедиях, так и в мелодрамах;
г) студии, на которых количество мелодрам превышает число комедий;
д) актеров, снявшихся в десяти фильмах;
е) среднее количество фильмов каждого из жанров, снимающееся на студии.
ж) студии, на которых снимаются фильмы только одного жанра;
з) студии, на которых снимаются фильмы всех жанров;
и) студии, никогда не выпускавшие ремейков.
11. Создайте базу данных для хранения следующих сведений: модель автомобиля, производитель, дилер, город, цена. Составьте запросы, позволяющие выбрать:
а) перечень моделей для заданного дилера;
б) дилеров, представляющих одновременно Toyota и УАЗ;
в) дилеров, продающих автомобили иностранного производства;
г) количество автомобилей, проданных каждым из дилеров в прошлом году;
д) среднюю цену Toyota Camry у дилеров в Москве;
е) производителей, у которых наибольшее количество дилеров в СанктПетербурге;
ж) дилеров, не продающий одновременно Ford и Renault;
з) дилеров, предлагающие модели всех производителей;
106
и) дилеров, у которых нет моделей дороже 300000.
12. Создайте базу данных для хранения следующих сведений: подразделение, сотрудник,
должность, дети. Составьте запросы, позволяющие выбрать:
а) список сотрудников заданного подразделения;
б) подразделения, входящие в состав заданного;
в) сотрудников, у которых есть дети различного пола;
г) среднюю численность подразделений;
д) инженеров, у которых более пяти детей;
е) подразделения, в которых количество техников превышает количество инженеров;
ж) подразделения, в которых не работают совместители;
з) подразделения, в которых представлены все должности;
и) сотрудников, у которых все дети одного пола.
13. Создайте базу данных для хранения следующих сведений: издательство, автор, книга,
жанр. Составьте запросы, позволяющие выбрать:
а) перечень книг, выпущенных заданным издательством в прошлом году;
б) авторы, сотрудничающие с несколькими издательствами;
в) книги, написанные в соавторстве;
г) количество книг каждого жанра, выпущенных каждым издательством;
д) авторов, написавших наибольшее количество книг;
е) средний объем книг, выпускаемых заданным издательством;
ж) издательства, выпускающие только сказки и детективы;
з) издательства, выпускающие книги всех жанров;
и) издательства, не выпустившие ни одной книги в 2009 году.
14. Создайте базу данных для хранения следующих сведений: врач, специальность, пациент, прием. Составьте запросы, позволяющие выбрать:
а) список пациентов, принятых терапевтами вчера;
б) врачей, совмещающих различные специальности;
в) пациентов, посещавших и хирурга и кардиолога;
г) количество пациентов, принятых каждым из врачей за прошедший год;
д) врачи, принявшие меньше всего пациентов;
е) врачей, у которых количество принимаемых пациентов превышает среднее;
ж) пациентов, которые никогда не посещали хирурга;
з) пациентов, которые посетили всех специалистов;
107
и) врачи, не совмещающие работу по различным специальностям.
15. Создайте базу данных для хранения следующих сведений: группа, пара, аудитория,
корпус. Составьте запросы, позволяющие выбрать:
а) перечень занятий для заданной группы на завтра;
б) списки аудиторий по корпусам;
в) группы, у которых в один день есть и лабораторные и курсовое проектирование;
г) группы, у которых количество занятий во вторник превышает семь пар;
д) среднее количество пар для заданной группы;
е) аудитории, в которых занимается максимальное количество групп;
ж) группы, у которых во вторник все пары проходят в одном корпусе;
з) аудитории, в которых никогда не занимаются студенты четвертого факультета;
и) группы, у которых нет занятий по субботам.
16. Создайте базу данных для хранения следующих сведений: кафедра, преподаватель,
ученая степень, ученое звание, должность. Составьте запросы, позволяющие выбрать:
а) список преподавателей заданной кафедры;
б) преподавателей, совмещающих работу на нескольких кафедрах
в) кафедры, на которых работают кандидаты наук, не имеющие звания доцента;
г) количество преподавателей, занимающих одинаковые должности для каждой
из кафедр;
д) среднее число сотрудников на кафедрах;
е) кафедры, на которых число докторов наук больше чем число кандидатов;
ж) кафедры, на которых нет ни одного профессора;
з) кафедры, на которых преподаватели занимают все возможные должности;
и) кафедры, на которых представлены не все ученые степени.
17. Создайте базу данных для хранения следующих сведений: авиакомпания, авиарейс,
тип самолета. Составьте запросы, позволяющие выбрать:
а) список рейсов для заданной авиакомпании;
б) типы самолетов, используемые заданной авиакомпанией;
в) авиакомпании, у которых прямой и обратный рейс выполняют различные типы
самолетов;
г) направления, на которых работает более трех авиакомпаний;
д) количество авиарейсов, выполняемых между каждой парой аэропортов;
е) авиакомпании, выполняющие максимальное количество рейсов;
108
ж) авиакомпании, не работающие в Стамбуле;
з) авиакомпании, использующие все типы самолетов;
и) авиакомпании, у которых все самолеты одного производителя.
18. Создайте базу данных для хранения следующих сведений: город, район, квартира, цена. Составьте запросы, позволяющие выбрать:
а) перечень однокомнатных квартир, продаваемых в Московском районе;
б) квартиры, находящиеся на одной улице, но в различных районах;
в) двух- и трехкомнатные квартиры, имеющие одинаковую площадь;
г) средняя цена однокомнатной квартиры в городе;
д) районы, в которых продается наибольшее число объектов недвижимости;
е) районы, в которых минимальна стоимость квадратного метра;
ж) улицы, продолжительность которых ограничивается только одним районом;
з) районы, в которых не продаются однокомнатные квартиры;
и) районы, в которых продаются квартиры всех строительных серий.
19. Создайте базу данных для хранения следующих сведений: олимпиада, страна,
спортсмен, вид спорта, место. Составьте запросы, позволяющие выбрать:
а) страны, принявшие участие в зимней олимпиаде 2008 г.;
б) спортсменов, принявших участие как в летних, так и в зимних олимпиадах;
в) спортсменов, получивших золото по двум или более видам соревнований на
одной и той же олимпиаде;
г) среднее число спортсменов, выставляемых каждой страной на каждый из видов олимпиад;
д) страны, количество побед которых на зимних олимпиадах, превышает количество побед на летних;
е) страны, завоевавшие наибольшее количество наград в 2008 г.;
ж) страны, никогда не участвовавшие в зимних олимпиадах;
з) страны, не участвовавшие в олимпиадах в период 1991..2011 г.г.;
и) страны, завоевавшие призовые места по всем видам спорта.
20. Создайте базу данных для хранения следующих сведений: город, ж/д станция, поезд,
вагон, место, пассажир, билет. Составьте запросы, позволяющие выбрать:
а) пассажиров, покупавших билеты в прошлом месяце из СПб в Москву;
б) пассажиров, покупавших в течение месяца и прямые и обратные билеты;
в) поезда, в которых есть и купейные и плацкартные и сидячие вагоны;
г) количество поездов из СПб в Москву;
109
д) количество билетов, проданных на каждый поезд из СПб в Москву;
е) средняя цена места в купейном вагоне;
ж) поезда из СПб, делающие остановку в Окуловке и не делающие в Бологом;
з) поезда, у которых соотношение цены СВ и продолжительности в пути максимально;
и) поезда из СПб в Москву, делающие все остановки.
21. Создайте базу данных для хранения следующих сведений: оператор сотовой связи,
абонент, договор, услуги, тарифы, .опции, начисления, платежи. Составьте запросы,
позволяющие выбрать:
а) абонентов, пользующихся опцией АОН по какому-либо тарифу;
б) абонентов, поменявших в прошлом году тариф «нормальный» на тариф «оптимальный»;
в) тарифы, у которых есть одинаковые опции;
г) количество абонентов, пользующихся каждым из тарифов;
д) операторов, у которых средняя цена минуты выше, чем у других;
е) операторов, доходность которых выше чем у АБВ;
ж) абонентов, не осуществлявших платежей в прошлом квартале;
з) тарифы, включающие все возможные опции;
и) абонентов, которые всегда изменяли тарифы одновременно (в один день).
22. Создайте базу данных для хранения следующих сведений: Интернет-провайдер, абонент, договор, оборудование, обращение в техподдержку, результат. Составьте запросы, позволяющие выбрать:
а) абонентов, обращавшихся в техподдержку по вопросу неисправности CM440;
б) абонентов, обращавшихся в техподдержку дважды в прошлом месяце;
в) провайдеров, которые предоставляют доступ по тарифам как на скорости 50,
так 70 МБит;
г) провайдеров, предлагающих более семи моделей кабельных модемов;
д) провайдеров, предлагающих наибольшее число тарифов;
е) количество абонентов для каждого провайдера по каждому из тарифов;
ж) абонентов, никогда не изменявших тариф;
з) абонентов, пользовавшихся всеми тарифами;
и) абонентов, не обращавшихся в техподдержку более трех раз в год.
110
23. Создайте базу данных для хранения следующих сведений: сеть ресторанов, ресторан,
меню, состав блюд, бронирование столиков, заказы клиентов. Составьте запросы,
позволяющие выбрать:
а) блюда, в состав которых входит говядина;
б) блюда, в состав которых входят одинаковые ингредиенты;
в) рестораны сети, в которых одинаковые блюда имеют различную цену;
г) количество блюд, предлагаемых в сети АБВ;
д) количество блюд, в которые входит каждый из ингредиентов;
е) рестораны, предлагающие в точности столько же блюд, что и заданный;
ж) рестораны, в которых нет чека, размер которого превышает 20000;
з) рестораны, в которых средний размер чека минимален;
и) рестораны, выручка которых год от года возрастает.
24. Создайте базу данных для хранения следующих сведений: почта, письмо, заказное
письмо, бандероль, отправитель, получатель, прием/выдача корреспонденции. Составьте запросы, позволяющие выбрать:
а) людей, отправлявших заказные письма в прошлом месяце;
б) людей, отправлявших письма в прошлом месяце дважды по одному и тому же
адресу;
в) людей, отправлявших письма в прошлом месяце и получавших ответы;
г) количество писем, пересланных из СПб в Москву в прошлом году;
д) количество корреспонденции каждого из видов между СПб и Москвой;
е) средний вес бандеролей из Москвы в СПб;
ж) почтовые отделения, количество корреспонденции в которых больше чем в
других;
з) людей, отправляющих письма всегда из одного и того же отделения;
и) людей, отправивших в прошлом году все виды корреспонденции.
25. Создайте базу данных для хранения следующих сведений: специальность, дисциплина, семестр, форма контроля (экзамен, зачет, КП). Составьте запросы, позволяющие
выбрать:
а) перечень дисциплин для заданной специальности;
б) специальности, изучающие БД раньше ООП;
в) специальности, изучающие БД более одного семестра;
г) количество дисциплин для каждой из специальностей;
111
д) специальности, количество дисциплин для которых превышает соответствующее для 230105;
е) общие дисциплины у специальностей 230105, 010503 и 080116;
ж) специальности, у которых нет экзаменов по БД;
з) специальности, изучающие наибольшее количество дисциплин;
и) специальности, у которых предусмотрены не все формы контроля.
Документ
Категория
Без категории
Просмотров
7
Размер файла
1 863 Кб
Теги
brzsovskiy
1/--страниц
Пожаловаться на содержимое документа