При определении столбцов в операторе CREATE TABLE можно определить столбец счетчик, значение которого будут автоматически генерироваться сервером. Для этого нужно указать свойство identity (<нач.значение>, <шаг>)
Такое свойство можно указать только для одного столбца в таблице, обычно это PK. Например при создании таблицы D dnum int identity (1, 1) primary key
Следует учитывать, что в этом случае при добавлении данных оператором INSERT INTO не надо задавать значение столбца dnum. Поэтому команда добавления данных в таблице D в таблицу D будет выглядеть след.образом: insert into D (dnum, dprice) VALUES (‘шуруп’,10)
52. Изменение структуры таблицы. Пример.
Для модификации структуры таблицы и ее ограничений используется оператор ALTER TABLE. Он может быть применен к одному столбцу или ограничению и может быть записан в след.осн.формах:
1) Добавление нового столбца в таблицу ALTER TABLE <табл> ADD <столбец> <тип>
2) Добавление ограничения в таблицу ALTER TABLE <табл> ADD CONSTRAINT <имя огранич><ограничение>
3) Изменение свойств столбца в таблице ALTER TABLE <табл.> ALTER COLUMN <столбец><тип> [ограничение]
4) Удаление столбца из таблицы ALTER TABLE <табл> DROP COLUMN <столбец>
5) Удаление ограничения из таблицы ALTER TABLE <табл> DROP CONSTRAINT <имя огранич.>
Пример: добавить в таблицу PD новый столбец pdnum, означающий номер поставки и сделать его PK
1) ALTER TABLE PD ADD pdnum int
2) ALTER TABLE PD ADD CONSTRAINT PK_PD primary key(pdnum)
3) ALTER TABLE PD ADD CONSTRAINT unique_pnum_dnum unique(pnum,dnum)
Для удаления таблиц используется оператор DROP TABLE <табл>. Удалять можно только пустые таблицы. Оператор также нельзя использовать для удаления таблицы. Оператор нельзя использовать для удаления таблицы, на которую ссылается ограничение FOREIGN KEY. В этом случае следует удалить сначала ссылающиеся ограничение или ссылающееся таблицу.
55. 57. Умолчания. Способы задания умолчаний. Ограничения на значения. Способы задания ограничений на значения. Примеры.
Ограничение на проверку CHECK может быть реализована с помощью объекта БД. Таким специальным объектом является правило, которое создается с помощью следующего оператора:
Для того чтобы ассоциировать правило с некоторым объектом существуют 2 процедуры- процедуры-привязки. Sp_bindrule- <имя правила, имя объекта> sp_unbindrule /отменяет привязку правила к столбцу
Sp_bindrule ‘R1’, ‘PD_volume’
Уничтожение происходит с помощью оператора DROP RULE <имя правила>
По умолчанию-специальный системный объект, который создается оператором CREATE DEFAULT <имя> AS <выражение>. CREATE DEFAULT D1 AS 0.
Для ассоциации с другими объектами существуют процедуры sp_binddefault, sp_unbinddefault
Sp_binddefault ‘D1’,’D.dprice’. Уничтожение: DROP DEFAULT <имя>
58. Определяемые пользователем типы данных. Пример.
Это специфические форматы которые создаются на основе системных или уже созданных типов данных. Соответствие домену в реляционной алгебре. Задается этот тип следующим образом:
CREATE TYPE <имя типа> FROM <базовый тип> {null | not null}
Базовым типом не может быть cursor, table, timestamp.
Пример: создать тип «Валюта» для хранения 3-х букв: ‘руб’, ‘$’, ‘EUR’
CREATE default D1 as ‘руб’ sp_bindefault D1, currency
DECLARE с currency
59. Представления. Особенности модификации данных через представления. Пример.
Представления или просмотры- это виртуальные таблицы, информация в которых не хранится постоянно как в базовых таблицах, а формируется динамически при обращении к ним. Фактически, представление- это хранимый запрос, который выполняется каждый раз при участии представления в какой-либо команде. Создание выполняется с помощью CREATE VIEW <имя представления> AS <SELECT- оператор> [WITH CHECK OPTION].
Использование представлений позволяет обеспечить каждому пользователю наиболее подходящий способ использования данных, что решает проблему простоты их использования и безопасности.
CREATE VIEW Кадры AS SELECT N, ФИО, Адрес, Телефон,… FROM Сотрудники GO
CREATE VIEW Глав.Бух AS SELECT N, ФИО, Оклад, Надбавка FRIM Сотрудники GO
К представлениям можно обращаться как и к базовым таблицам.
Пример: вывести всю информацию из представления кадры SELECT * FROM Кадры WHERE ФИО=’Иванов’. Можно создать представление, основанное на других представлениях
CREATE VIEW Программисты AS SELECT * FROM Кадры WHERE Должность= ‘Программист’ GO
При изменении базовых таблиц изменяются основанные на них представления. В свою очередь данные в базовых таблицах изменяются при модификации представлений. Представление может быть модифицировано, если выполняются след. условия:
1) Представление основано на одной таблице и включает все столбы, определяемые с ограничением NOT NULL
2) Содержит PK таблицы, если PK не является счетчиком
3) Не используют группировку, подзапросы, агрегатные функции, вычисляемые поля в качестве полей вывода. Если представления удолетворяют перечисленным условиям, то к нему можно применять операторы INSERT, UPDATE, DELETE.
Особенности модификаций данных через представления.
Пример: создать представление, содержащее информацию о деталях с ценой <15 руб
CREATE VIEW V1 AS SELECT * FROM D WHERE dprice<15 GO
1) Операция удаления DELETE FROM V1 из представления в таблице D удаляется 1 строка о деталях «болт»
2) Операция вставки INSERT INTO V1 VALUES (4, ‘шуруп’, 10) в этом случае и в представлении и в таблице D вставляется одна строка о деталях «шуруп»
INSERT INTO V1 VALUES (5, ‘шайба’, 50). В этом случае и в представление и в таблицу добавляется строка, которая сразу удаляется из представления
3) Операция обновления UPDATE V1 SET dprice=dprice*20. Обновляются 2 строки, которые сразу исчезают из представления.
Если в операторе CREATE VIEW есть предложение WITH CHECK OPTION, то СУБД проверяет изменения, производимые через представления на соответствие критериям предложения WHERE оператора SELECT, на котором основано представление. Изменения разрешаются только в том случае, если результирующие строки отображаются в представлении
При 1-ом вызове пользовательской хранимой процедуры СУБД генерирует и оптимизирует план выполнения хранимой процедуры. Хранимые процедуры сохраняются в БД в оптимизированном виде, поэтому скорость их выполнения значительно выше скорости выполнения обычных SQL-запросов.
Для создания хранимой процедуры используют оператор: CREATE PROC <имя хп> [<список параметров>] AS <SQL-операторы> GO
Описание каждого параметра из списка параметров осуществляется следующим образом:
@ <имя параметра> <тип> [=<значение по умолчанию>] [OUTPUT]
Слово OUTPUT используется для выходных параметров
Пример : разработать хранимую процедуру для вывода наименования поставщика с заданным номером
CREATE PROC P1 (@pnum int) AS DECLARE @pname char(15) SELECT @pname=pname FROM P WHERE pnum=@pnum PRINT @pname GO
Для запуска хр.процедуры используется оператор EXEC. EXEC <имя ХП> <список параметров>
Пример: EXEC P1 1
Пример: разработать ХП, которая возвращает цену детали в валюте, пользователь задает N детали, наименование валюты.
WHEN ‘руб’ THEN @dprice WHEN ‘$’ THEN @dprice/30 WHEN ‘EUR’ THEN @dprice/40 ELSE PRINT ‘валюты нет’ END GO
Для того, чтобы использовать возвращемы параметр необходимо объявить переменную для его хранения. Напр, в script или в другой хранимой процедуре, которая вызывает данную хранимую процедуру. Например: DECLARE @price money EXEC P2 2,’руб’, @price OUTPUT
Если в EXEC перечислены не все параметры, которые были опеределены в операторе CREATE PROC или они перечислены в другом порядке, то необходимо указать имена формальных параметров
EXEC P2 @dnum=2, @newprice= @price money OUTPUT
В теле хранимой процедуры можно использовать оператор RETURN <целочисленное выражение> для завершения выполнения процедуры. Обычно с помощью оператора возвращают код ошибки. Получить возвращаемый оператором выражение можно следующим образом:
EXEC @ <имя переменной>= <имя ХП>
Если в операторе RETURN не указывается выражение, то возвращают код ошибки. То же самое значение нахождения 3 системных переменных @@ERROR. Прииспользовании в хранимой процедуре списка зарезервированных значений, напр, курса валют, желательно оформить их в виде отдельных таблиц БД
Удаление процедуры DROP PROC <имя ХП>
62. Типы функций. Скалярные функции. Пример.
Создание пользовательских функций выполняется с помощью оператора
CREATE FUNCTION <имя функции> [<список параметров>]
1) Скалярные функции: возвращают выражение скалярного типа, которые указываются после слова RETURNS. Возвращающее выражение указывается после слова RETURN . Тело функции состоит из SQL-операторов, BEGIN и END являются обязательными.
Пример: разработать функцию, которая по заданному номеру поставщика возвращает его значение.
CREATE FUNCTION F1 (@pnum int) RETURNS char(10)
BEGIN DECLARE @pname char (10) SELECT @pname=pname FROM P WHERE pnum=@pnum RETURN @pname END
PRINT F1(2) DECLARE @name char (10) SET @name=F1(2)
63. Однооператорные табличные функции. Пример.
Однооператорные табличные функции тело содердит единственный оператор RETURN. Возвращает функции значение типа TABLE. Особенность данного типа заключается в том, что структура возвращаемого типа TABLE создается автоматически в ходе выполнения SELECT оператора вложенного в оператор RETURN.
Пример: разработать функцию для определения наименования поставщивов, которые поставляют детали в настоящее время.
CREATE FUNCTION F2()
RETURNS TABLE AS
RETURN (SELECT pname FROM PD WHERE pnum IN (SELECT pnum FROM PD))
Использовать такое значение можно как переменную табличного типа: SELECT * FROM F2() DECLARE @p TABLE (pname char (10)) INSERT INTO @p select * from F2()
64. Многооператорные табличные функции. Пример.
Тело представляет собой последовательность SQL-операторов; операторы BEGIN и END являются обязательными, функция возвращает значение типа TABLE; возвращаемое значение формируется с помощью оператора INSERT INTO, выполняющийся в теле функций. Кроме того в теле функции допускается использование других операторов языка SQL, которые можно изменитьсзначения, размещаемые в выходной таблице. В операторе INSERT INTO требуется явно указать имя объекта, куда необходимо вставить строки. Поэтому в предложении RETURNS требуется указать имя параметра типа TABLE, а также описать его структуру. В операторе RETURN в теле функции не нужно указывать имя возвращаемого параметра, сервер автоматически возвратит параметр, описанный в предложении RETURNS.
Пример: разработать функцию, которая определяет для каждого поставщика суммарный объем поставок, а также отклонение его от среднего суммарного объема поставок. Выполнять только для тех поставщиков, которые поставляют детали в настоящее время.
INSERT INTO @t (SELECT pnum, sum(volume) NULL FROM PD GROUP BY pnum)
SET @avg_sum_volume = (SELECT avg(sum_volume) FROM @t)
UPDATE @t SET dif = sum_volume - @avg_sum_volume RETURN
END
65. DML-триггеры. AFTER- триггеры. Пример.
Триггер- это специальный тип хранимой процедуры, который активируется при выполнении определенных действий над БД. С их помощью обеспечивается целостность данных в БД и реализуются ограничения, накладываемые на предметную область. Хотя триггер и является разновидностью ХП, вызвать его непосредственно нельзя. Он реагирует только на событие, для которого он определен. Триггер рассматривается как единое целое, т.е как одна транзакция, которая либо фиксируется при выполнении, либо откатывается, если триггер вызывает ошибку. По типу событий, на которые реагируют триггеры их разделяют на 2 группы:
1) DML-триггеры, которые реагируют на команды языка DML.
2) DDL-триггеры, которые реагируют на команды языка DDL.
DML-триггер INSERT, UPDATE, DELETE
Создание такого триггера выполняется с помощью оператора CREATE TRIGGER <имя триггера> ON {<имя таблицы> | <имя представления>} <тип поведения триггера> <тип команды> AS <SQL-операторы>
Триггеры разделяют по типу команды, на которые они реагируют INSERT, UPDATE, DELETE. Они могут быть перечислены в любом порядке через запятую, но хотя бы одна команда обязательно должна быть указана. По типу поведения триггеры делятся на :1) AFTER-триггеры 2) INSTEAD OF- триггеры
1) AFTER-триггеры. Такой триггер запускается после вызвавшей его команды. Если команда по какой-либо причине не была успешно завершена, то триггер также не выполняется. Следует отметить, что команда пользователя и в теле триггера осуществляются с помощью одной транзакции. Поэтому если выполняется откат триггера, то будут отклонены и пользовательские изменения. Для каждой из команд INSEERT, UPDATE, DELETE можно определить несколько AFTER-триггеров.
2) INSTEAD OF-триггер. Такой триггер выполняется вместо вызвавшей его команды, таким образом, перехватывая команду, вводимую пользователем. Если команды, указываемые в теле INSTEAD OF-триггера, приводят к нарушению ограничения в таблице, то происходит откат триггера. Для каждой команды INSERT, UPDATE, DELETE можно определить только один INSTEAD OF – триггер.
Если для связи между 2-мя таблицами выше определена одна из стратегий: каскадировать, установить в NULL или установить по умолчанию, то для главной таблицы в этой связи не допускается создание INSTEAD OF- триггеров для соответствующих команд UPDATE и DELETE. Это объясняется тем, что операция обновления и удаления в этом случае реализуются в системе с помощью INSTEAD OF-триггеров ON DELETE CASCADE. INSTEAD OF DELETE для главной таблицы создать нельзя
Пример: пусть требуется разработать триггер, который запускается при выполнении некоторой операции над таблицами при заданных условиях.
1) При создании after-триггера следует учитывать, что операция уже была завершена и поэтому таблица находится в состоянии, соответствующем успешному выполнению операции. Тогда алгоритм поведения AFTER-триггера можно записать следующим образом: если условие не выполняется, то откатить транзакцию. Откат транзакции означает, что были отменены все действия, выполненные пользователем, и таблица вернется в исходное состояние. Откат транзакции выполняется оператором ROLLBACK TRAN. Если после данного оператора указаны другие операторы, то они будут выполнены. Поэтому если после отката следует завершить выполнение триггера, то надо записать оператор RETURN.
2) При создании INSTEAD OF триггера следует учитывать, что операция еще не была завершена и поэтому таблица находится в исходном состоянии. Тогда алгоритс поведения такого триггера можно записать следующим образом: если условиевыполняется, то выполнить операцию. Для контроля изменений при выполнении команд INSERT, UPDATE,DELETE СУБД создает 2 таблицы: inserted и deleted.
Структура этих таблиц идентична структуре таблиц, для которой определяется триггер. Для каждого триггера создается свой комплект их 2-х таблиц и никакой другой триггер не имеет к ним доступа. В зав-ти от типа команды, которая вызвала триггер, содержание этих таблиц может быть различным.
1) Если триггер вызвала команда INSERT, то в таблице inserted находятся данные вносимые пользователем. Таблица deleted пуста.
2) Если триггер вызвала команда DELETE, в таблице deleted находятся данные, которые хочет удалить пользователь, таблица inserted пуста.
3) Если триггер вызвала команда UPDATE, то в таблице deleted находятся старые значения данных, которые пользователь пытается изменить, а в таблице inserted находятся новые значения данных, которые пользователь пытается занести вместо соответствующих данных в таблице deleted.
Анализируя данные в таблице inserted и deleted можно отследить все изменения выполненные пользователем. Обе таблицы доступны только для чтения.
Пример: написать триггер, который разрешает заносить информацию о новой детали в таблицу D, если цена детали указана и не превышает 50 руб.
INSERT INTO D VALUES (4, ‘шуруп’, 10)
1) Создание AFTER-триггера. В данном случае в таблице D уже помещена информация о новой детали, та же самая информация находится в таблице inserted, таблица deleted пуста.
CREATE TRIGGER T1 ON D AFTER INSERT
AS DECLARE @dprice money
SELECT @dprice= dprice
FROM inserted
IF (@dprice IS NULL) OR (@dprice>50)
BEGIN
PRINT ‘откат’
ROLLBACK TRAN
END GO
2) Создание INSTEAD OF триггера. В данном случае таблица D находится в исходном состоянии. В таблице inserted находится строка вставленная пользователем. Таблица deleted пуста.
CREATE TRIGGER T2 ON D INSTEAD OF INSERT
AS DECLARE @dnum int, @dname char (10), @dprice money
SELECT @dprice= dprice FROM inserted
IF (@dprice IS NOT NULL) AND (@dprice<=50) INSERT INTO D VALUES (@dnum, @dname,@dprice)
ELSE PRINT ‘Ошибка’ GO
При модификации нескольких записей можно реализовать 2 различных варианта алгоритма работы триггера.
1) Недопускать модификацию ни одной записи в таблице, если в множестве модифицируемых записей есть хоть одна запись, которая не удолетворяет условию
2) Осуществить модификацию только тех записей, которые удолетворяют условию
Первый вариант можно реализовать как с помощью AFTER, так и INSTEAD OF триггера. Второй вариант только с помощью INSTEAD OF- триггера.
1) а) AFTER триггер
CREATE TRIGGER T1_1 ON D AFTER INSERT
AS IF EXISTS (SELECT * FROM inserted WHERE (dprice IS NULL) OR (dprice>50)) ROLLBACK TRAN
GO
б) INSTEAD OF триггер
CREATE TRIGGER T1_2 ON D INSTEAD OF INSERT
AS IF NOT EXISTS (SELECT * FROM inserted WHERE (dprice IS NULL) OR (dprice> 50)) INSERT INTO D SELECT * FROM inserted
68.DDL-триггеры. Пример.
Триггеры DDL срабатывают в ответ на разнообразные события языка определения данных SQL. Эти события соответствуют операторам языка транзакции SQL, начинающимися ключевыми словами create, alter, drop. С помощью DDL-триггеров можно:
1) предотвратить внесение определенных изменений в БД;
2) выполнить в БД некоторые действия в ответ на изменение в БД;
3) записывать изменения, выполненные в БД
Синтаксис DDL-триггера:
CREATE TRIGGER <имя триггера> ON {ALL SERVER | DATABASE} AFTER {<имя события>| <группа событий>} AS <SQL-операторы>. Триггеры DDL нельзя использовать в качестве триггера INSTEAD OF. Триггеры DDL могут срабатывать в ответ на событие, возникающее в текущей БД или на текущем сервере. Если ALLSERVER, то событие возникло на текущем сервере, если DATABASE, то событие возникло в текущей БД. Если указан параметр <имя события>, то триггер запускается только при возникновении события. В качестве примера события для БД можно привести следующие события : CREATE TABLE, ALTER TABLE, DROP TABLE. В качестве примера событий для сервера можно привести следующие события CREATE DATABASE, ALTER DATABASE, DROP DATABASE. Если указаны <группа событий>, то триггер запускается при возникновении любого события из данной группы. В качестве примера для БД можно привести следующую группу событий: DDL_TABLE_EVENTS, включающая команды CREATE, ALTER и DROP TABLE, а также DDL_VIEW_EVENTS, включающая команды CREATE, ALTER, DROP VIEW. Данные группы объединены в группы более высокого уровня. Например, группа событий DDL_DATABASE_LEVEL_EVENTS включает все группы событий уровня БД. DDL_SERVER_LEVEL_EVENTS включает все группы событий уровня сервера.
Пример: разработать триггер DDL, который запрещает выполнять создание и удаление таблиц в текущей БД.
CREATE TRIGGER T7
ON DATABASE
AFTER CREATE_TABLE, DROP_TABLE
AS PRINT ‘Операции удаления и создания запрещены’ ROLLBACK TRAN
Для включения или отключения триггера используется оператор ALTER TABLE <имя таблицы> {ENABLE | DISABLE} TRIGGER {<имя триггера> / ALL } При указании параметра ALL происходит исключение всех параметров присоединенных к таблице.
69. Модификация данных через представления с помощью INSTEAD OF- триггеров.
CREATE VIEW V1 AS SELECT * FROM (P INNER JOIN PD ON P.pnum = PD. pnum) INNER JOIN (D ON PD .dnum=D.dnum)
Данное представление является немодифицированным, так как основано на нескольких таблицах
Задача: обеспечить вставку данных через данное представление
Для обеспечения вставки данных через данное представление разработаем триггер, который будет заменять команду вставки данных через представление командами вставки данных в базовую таблицу.
Допустим, что пользователю разрешено вносить только одну строку.
IF NOT EXISTS (SELECT * FROM D WHERE dnum = @dnum)
INSERT INTO D VALUES (@dnum,@dname,@dprice)
IF NOT EXISTS (SELECT * FROM PD WHERE pnum= @pnum AND dnum=@dnum )
INSERT INTO PD VALUES (@pnum,@dnum,@vol)
Для того, чтобы обеспечить вставку набора данных через представление необходимо уметь осуществить навигацию по данному набору данных, чтобы обработать каждую строку в отдельности. Данную возможность предоставляет курсор- системный объект.
70.Курсор. Использование курсоров. Пример.
Курсор- это специальный объект, который позволяет создавать, хранить набор данных, а также осуществлять навигацию по заданному набору данных.
Обычно курсоры используются в телах хранимой процедуры или триггеров для обеспечения сложного алгоритма обработки данных.
При работу с курсорами выделяют следующие этапы:
1) объявление курсора
2) открытие курсора
3) выборка данных из курсора
4) закрытие
5) уничтожение
Объявление курсора осуществляется оператором DECLARE <имя> CURSOR [FORWARD_ONLY | SCROLL] [STATIC | DYNAMIC | KEYSET | FASTFORWARD] FOR <SELECT-оператор>
Направление выборки определяет возможность перемещения по курсору. Если указана опция FORWARD_ONLY, то в таком курсоре можно переходить только к следующим записям, если SCROLL – переход к любой записи.
Открытие курсора: OPEN <имя курсора> выполняет SELECT оператора из курсора, после это курсор заполнен данными.
Выборка данных из курсора: FETCH <направление> FROM <имя курсора> INTO <список переменных>
Список переменных должен быть указан в том порядке, в котором указан в операторе SELECT, который заполняет курсор данными.
Направление: FIRST- первая запись, NEXT- следующая запись, LAST- последняя запись, ABSOLUTE n- запись с номером n, RELATIVE n – запись, отстающая от n от текущей.
Закрытие курсора CLOSE <имя курсора>
Закрытый курсор можно открыть повторно.
Уничтожение курсора DEALLOCATE <имя курсора>
После уничтожения курсора его имя может быть использовано повторно. Для того, чтобы обеспечить навигацию по набору данных хотелось бы иметь возможность получать информацию о результатах предыдущей команды вывода. Данная информация хранится в переменной @@FETCH_STATUS
0-выборка успешна, -1- произошел выход за пределы курсора, -2- попытка обращаться к удаленной или измененной строке.
Пример: создать курсор для вывода значений dnum
DECLARE C1 CURSOR STATIC SCROLL
FOR SELECT dnum FROM D DECLARE @dnum int OPEN C1
FETCH FIRST FROM C1 INTO @dnum WHILE @@FETCH_STATUS =0
BEGIN PRINT @dnum FETCH NEXT FROM C1 INTO @dnum
END CLOSE C1 DEALLOCATE C1
71.Курсоры. Типы курсоров. Пример.
Статический курсор вводится ключевым словом STATIC. Для него набор данных формируется только 1 раз, создается полная копия набора и помещается в таблицу temp dB. Ключевой курсор вводится ключевым словом KEYSET. При создании такого курсора сохраняются только ключевые атрибуты. По данным ключа извлекаются недостающие данные. При попытке обращения к удаленным или измененным данным @@FETCH_STATUS= -2. Динамический курсор вводится ключевым словом DYNAMIC. Копии данных не создаются, производится аналогичная выборка из базовых таблиц. Большие временные затраты. Быстрый последовательный курсор вводится ключевым словом FAST_FORWARD. После открытия курсора сразу открывается первая запись. Реализуется автоматическое закрытие курсора (команда FETCH выполнять не надо)
Пример: разработать ХП, которая выводит информацию о поставщиках в виде N, Фамилия, выводит список деталей и суммарный объем поставок, суммарное количество деталей.
CREATE PROC P1
AS DECLARE @pnum int, @dnum int, @pname char(10), @dname char(15), @vol int, @cost money, @count_d int, @sum int
DECLARE C1 CURSOR STATIC SCROLL
FOR
SELECT P.pnum, pname, count(PD. dnum), sum (volume), sum (dprice*volume) FROM ((P INNER JOIN PD ON P.pnum = PD.pnum INNER JOIN D ON D.dnum=PD.dnum) GROUP BY P.pnum, pname
OPEN C1
FETCH FIRST FROM C1
INTO @pnum, @pname, @count, @sum, @cost WHILE @@FETCH_STATUS =0
BEGIN
PRINT @pname PRINT @pnum
PRINT @cost
DECLARE C2 CURSOR SCROLL
FOR SELECT D.dnum, dname, volume FROM (P INNER JOIN PD ON D.dnum= PD.dnum)
WHERE pnum=@pnum
OPEN C2 FETCH FIRST FROM C2
INTO @dnum, @dname, @vol WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @dnum, @dname,@vol
FETCH NEXT FROM C2 …
END
FETCH NEXT FROM C1..
END
Тема 7. Проектирование реляционных БД.
72. Избыточность данных. Аномалии обновлений. Суть метода нормальных форм. Нормализация и денормализация.
В настоящее время при проектировании РБД используются 2 метода непосредственно для проектирования:
1) ER- метод (Entity- Relation)- «сущность-связь»
2) Метод нормализации форм, который используется для проверки правильности результата проектирования.
Основную цель, которую преследует разработчик РБД заключается в группировке атрибутов в отношении. Таким образом, чтобы минимизировать избыточность данных. Чтобы продемонстрировать проблему, связанную с избыточностью данных, рассмотрим следующий пример.
Пример: пусть требуется в БД хранить информацию об итогах сессии на некотором факультете, а именно: ФИО студента, N гр, ФИО препод, предмет, должность препод, Оценка. Причем на предметную область накладываются ограничения
1) ФИО студента и препод. Уникальны
2) В одной группе, учится много студентов, но одному студенту соответствует один номер группы.
3) Препод может занимать только одну должность
4) Препод может принимать экзамен по нескольким предметам, но за каждый предметом закреплен только один преподаватель
Метод НФ предполагает, что вся информация первоначальна хранится в одном отношении.
При работе с отношениями, содержащие избыточные данные возникают проблемы, которые называются модификациями. Поскольку аномалии проявляются при выполнении операций, изменяющих состояние БД, то различают следующие виды аномалий:
Причина аномалий- это избыточность данных порождающая тем, что в отношении хранятся разнородная информация. Устранить аномалию можно выполнив нормализацию отношений.
Нормализация- это формальный метод анализа отношений, на основе первичных или потенциальных ключей и существующих зависимостей между атрибутами. Процесс нормализации заключается в последовательном переводе отношения из первой НФ (1НФ) в более высокие НФ по определенным правилам. Каждой НФ соответствует свой набор ограничений. Выделяют следующую последовательность НФ:1НФ, 2НФ, 3НФ, БКНФ(Бойса-Кодда),4НФ,5НФ(проекционно-соединительная НФ), ДКНФ (доменно-ключевая НФ)
Каждая НФ сохраняет свойства предшествующей НФ. Переход к следующей НФ осуществляется декомпозицией исходного отношения на 2 или более отношений удовлетворяющих ограничениям целевой НФ
73. Суть метода нормальных форм. Функциональные зависимости. Транзитивные зависимости. 1НФ, 2НФ. Алгоритм приведения к 2НФ.
Определение 1НФ- отношение, в котором на пересечении конкретной строки и столбца находится 1 значение принадлежащее 1НФ. Определение функциональной зависимости: пусть R- отношение, а Х, Y –произвольные атрибуты или множество атрибутов отношения R. Y функционально зависит от X (X->Y) или Х функционально определяет Y, если одному значению Х соответствует только одно значение Y. Обратное необязательно. Х называют детерминантом AP? А Y зависимой частью
Пример: ФИО студента-> N гр
ФЗ называется полной, если Y не зависит от любого собственного подмножества Х. Если детерминант состоит из одного атрибута, то ФЗ автоматически является полной. Определение транзитивной зависимости: зависимость X-> Y называется транзитивной, если есть такой атрибут Z, что существует зависимости Х->Z ,Z->Y, но отсутствует зависимость Z-> Х. Тогда говорят, что Y транзитивно зависит от Х или Х транзитивно определяет Y. Неключевым атрибутом называется любой атрибут отношения, невходящий в состав потенциального ключа.
Определение 2НФ- отношение, находится в 2НФ, если оно находится в 1НФ и каждый неключевой атрибут функционально полно зависит от любого потенциального ключа, в частности PK.
Алгоритм приведения ко 2НФ следующий:
1) Выписать все имеющиеся в отношении зависимости
2) Если в отношении существуют зависимости неключевых атрибутов от части составного ключа, то проводят декомпозицию исх. Отношения следующим образом: те атрибуты, которые зависят от части ключа выносят в отдельное отношение вместе с этой частью ключа. В новом отношении PK будет детерминировать ФЗ. В исх.отношении остаются все ключевые атрибуты.
74. Суть метода нормальных форм. Функциональные зависимости. Транзитивные зависимости 3НФ. Алгоритм приведения к 3НФ.
Определение 3НФ-отношение находится в 3НФ, если оно находится во 2НФ и в нем нет транзитивных зависимостей неключевых атрибутов от любого потенциального ключа, в частности PK.
Алгоритм приведения к 3НФ следующий:
1) Если в отношении обнаружена транзитивная зависимость, то проводится декомпозиция, отношений следующим образом: те неключевые атрибуты, которые зависят от других неключевых атрибутов, совместно выносятся в отдельное отношение. PK в новом отношении становится детерминантом ФЗ. Он также остается в исходном отношении.
75.Суть метода БКНФ. Алгоритм приведения к БКНФ.
Определение БКНФ-отношение находится в БКНФ, если оно находится в 3НФ и детерминаты всех ФЗ являются потенциальными.
Алгоритм приведения к БКНФ: атрибуты, которые зависят от детерминанта не являюшиеся потенциальными ключами выносятся вместе с детерминантом в новом отношении с ключом. Он также остается в исходном отношении.
76. Многозначные зависимости. 4НФ. Алгоритм приведения к 4НФ.
Определение многозначной зависимости: пусть R- отношение, а X, Y, Я – атрибуты или непересекающиеся множества атрибутов отношения R. Говорят, что атрибуты Y и Z многозначно зависят от X или X многозначно определяет Y и Z, что записывается следующим образом: X-> Y| Я
Если из того, что в отношении существует кортеж r1(x, y, z) и r2(x1, y1, z1) следует, что в отношении существует кортежи r3(x, y, z1) и r4(x, y1,z). Говорят, что атрибуты Y и Z ведут себя симметрично по отношению к детерминанту многозначной зависимости Х.
Многозначная зависимость Х, Y, Z называется нетривиальной, если не существует ФЗ X-> Y и X->Z
Зависимость в нашем случае является нетривиальной.
Определение 4НФ-отношение находится в 4НФ, если оно находится в БКНФ и не содержит нетривиальные многозначные зависимости.
Алгоритм приведения:
1) Пусть К отношение, а X, Y, Z – атрибуты или непересекающиеся множества атрибутов. Если в отношении R выявляется нетривиальная многозначная зависимость X, Y, Z, то требуется декомпозировать исходное отношение в R1[X, Y] R2[X,Z]
Тема 4: Реализация операций реляционной алгебры в языке SQL.
32. Операция объединения в реляционной алгебре и ее реализация в языке SQL.
№
фамилия
Иванов
Петров
Сидоров
Объединением (A UNION B) двух совместимых по типу отношений и называется отношение с тем же заголовком, что и у отношений и , и телом, состоящим из кортежей, принадлежащих или , или , или обоим отношениям.
№
фамилия
Иванов
Сидоров
№
фамилия
A UION B
B
Иванов
Петров
A
33. Операция пересечения в реляционной алгебре и ее реализация в языке SQL.
Пересечением (A INERSECT B) двух совместимых по типу отношений и называется отношение с тем же заголовком, что и у отношений и , и телом, состоящим из кортежей, принадлежащих одновременно обоим отношениям и .
ПР.: вывести номера деталей, которые поставляются поставщиком 1 и 2.
35. Операция внутреннего соединения в реляционной алгебре и ее реализация в языке SQL.
Внутренним соединением (A JOIN B) называютотношение A и B, выполненоe по всем общим атрибутам, причем из результирующего отношения исключают по одному экземпляру каждого общего атрибута.
Естественное соединение эквивалентно следующей последовательности реляционных операций:
Переименовать одинаковые атрибуты в отношениях
1.Выполнить декартово произведение отношений
2.Выполнить выборку по совпадающим значениям атрибутов, имевших одинаковые имена
4.Переименовать атрибуты, вернув им первоначальные имена
ПР.: вывести информацию о поставках в виде отношения R.
½ Select pname, dname, volume
½ FROM (P INNER JOIN PD ON P.pnum=PD.pnum) INNER JOIN D ON PD.dnum=D.dnum
36. Операция левого внешнего соединения в реляционной алгебре и ее реализация в языке SQL.
Тип соединения "левое (внешнее)". Левое соединение таблиц А и В включает в себя все строки из левой таблицы А и те строки из правой таблицы В, для которых обнаружено совпадение. Для строк из таблицы А, для которых не найдено соответствия в таблице В, в столбцы, извлекаемые из таблицы В, заносятся значения NULL. A LEFT JOIN B
37. Операция правого внешнего соединения в реляционной алгебре и ее реализация в языке SQL.
Тип соединения "правое (внешнее)". Правое соединение таблиц А и В включает в себя все строки из правой таблицы В и те строки из левой таблицы А, для которых обнаружено совпадение. Для строк из таблицы В, для которых не найдено соответствия в таблице А, в столбцы, извлекаемые из таблицы А заносятся значения NULL. A RIGHT JOIN B
38. Реализация операции вычитания с помощью подзапросов.
39. Реализация операции пересечения с помощью подзапросов.
40. Реализация операции деления с помощью подзапросов.
Смещение m от 0 (от начала АПространства) p,q – двухкомпонентный адрес
p- № сегмента, q – смещение относительно начала этого сегмента
Сущ-ет 2 подхода к преобразованию виртуальных адресов к физическим:
1) Загрузка совместно с заменой виртуальных адресов физическими
При таком подходе замена адресов выполняется 1 раз. Программа, которая называется «перемещающий загрузчик» имеет первоначальный адрес загрузки (это адрес оперативной памяти, начиная с которого будет размещена программа и код в относительных (виртуальных) адресах выполняет загрузку с одновременным увеличением виртуальных адресов на величину начального адреса загрузки)
Программа загружается в память в виртуальных адресах. Начальный адрес загрузки фиксируется в специальном регистре, а непосредственно преобразование виртуальных адресов в физические производится во время выполнения программы при обращении к памяти. В результате некоторый виртуальный адрес пересчитывается в физический столько раз сколько обращений к нему производится.
«+»Более гибкое (связано с тем, что этот подход позволят перемещать программный код процесса во время выполнения)
«-»Менее экономичен из-за многократных преобразований одних и тех же адресов
Виртуальная память – это картина памяти, формируемая ОС для процесса. Деятельность ОС по созданию такой картины называется виртуализацией памяти.
Типичная ситуация, когда объем ВАп превышает доступный объем оператив.памяти, это достигается за счет виртуализации.
Реально ОС имеет в своем распоряжении некоторый объем физической оператив.памяти ввиде установленных модулей + объем, который ей разрешено использовать на диске. Эта память распределяется между всеми процессами.
«Правило хорошего тона» процессов: на взаимодействие процессов накладывается целый ряд различных ограничений в силу которых процессы должны вести себя корректно друг по отношение к другу. Основная цель этого правила – 1ин процесс не должен претендовать на всю доступную память. На 1ин процесс 200-500 Мб памяти.
Виды алгоритмов распределения памяти :
1) Распределение памяти без использования внешней памяти
Предполагают, что размер ВАп каждого процесса меньше объема оператив.памяти. Использовались в ранних мультипрограммных ОС в 60-70 годах.
2) Распределение памяти с использование внешней памяти
Реализуют механизм виртуальной память
a. Страничное распределение
Ед-ей перемещения между памятью и диском является страница – это часть ВАп фиксированного и небольшого объема
b. Сегментное распределение
\-\-\- Является сегмент – часть ВАп произвольного объема, содержащая осмысленную с некоторой точки зрения совокупность данных (Н: программа, массив..)
c. Сегментно-страничное распределение
Обеденяет элементы предыдущих классов, при этом ВАп структурируется иерархически. Делятся на сегменты, а затем сегменты делятся на страницы. Основной ед-ей является страницы.
Для временного хранения вытесненных на диск сегментов и страниц отводится специальная область или специальный файл, называемый страничным файлом или файлом свопинга. Чем больше страничный файл, тем больше одновременно работающих приложений в ОС, но тем медленнее их работа из-за многократной перекачки перемещаемых эл-ов на диск и обратно. Этот размер страничного файла является настраиваемым параметром.
Виртуализация оператив.памяти осуществляется совокупностью аппаратных средств процессора и программных средств ОС.
Включает решение следующих задач:
· Размещение данных
Образы процессов или их частей в запоминающих устройствах разного типа
· Выбор образов процессов или их частей для перемещения из оператив.памяти на диск и обратно
· Перемещение данных между памятью и диском
· Преобразование виртуальных адресов в физические
Решение этих задач осуществляется автоматически, т.е. без участия программиста и не отображается в логике работы приложений.
Виртуализация осуществляется на основе 2 подходов:
· Свопинг
Между оператив.памятью и дискам перемещаются образы процессов полностью
«+»более простой в реализации
«-»обладает избыточностью при подкачке или выгрузке: часто для активизации процесса или освобождение памяти не требуется перемещение всего образа процесса. Избыточность приводит к замедлению работы системы и неэффективному использованию памяти. Если образ процесса превышает объем оператив.памяти, то его невозможно загрузить на выполнение. Применяется как дополнительный подход ко 2му, включающийся только при серьезных перегрузках системы.
· Виртуальная память
Между оператив.памятью и диском перемещаются части образов процесса
Страничное распределение:
· Виртуальные страницы
ВАп делится на виртуальные страницы, но в общем случае размер ВАп не является кратным размеру страницы. Последняя страница дополняется фиктивной областью.
· Физические страницы
Вся оперативная память машины делиться на физические страницы для каждого процесса создается таблица страниц. Информационная структура, содержащая все записи виртуальных машин. Запись таблицы страниц называется дескриптором страницы (одна строчка в таблице) , содержит номер физической страницы и управляющую информацию. (признак присутствия страницы в оперативной памяти, признак модификации, признак обращения к странице, признак не выгружаемости). Таблицы страниц размещаются в оперативной памяти, адрес каждой из них включается в контекст соответствующего процесса. При нехватке оперативной памяти один или несколько процессов выгружаются на диск.
Действия при обращении к памяти.
· Поиск в ВАп процесса номера виртуальной страницы.
· Определение соответствующего этому номеру элемента таблицы страниц
· Анализ признака присутствия.
· Если страница находится в озу, то виртуальный адрес заменятся физическим, в противном случае происходит страничное прерывание. Оно состоит из того что процесс переводится в состояние ожидания. Программа обработки прерывания находит в страничном файле требуемую страницу файла и загружает ее в свободную физическую страницу.
Преобразование виртуального адреса в физический
· (p,sv) виртуальный адрес некоторого объекта
· (n,sf) его физический адрес
· Где p n номера виртуальной и физической страниц
· Sv sf смещения в пределах страниц
Св-ва:
1) Объем страницы выбирается равным степени двойки.
2) Последовательность адресов внутри виртуальной страницы отображается без изменения те sv = sf
Факторы, влияющие на производительность системы при страничном распределении.
· Частота страничных прерываний и размеры страницы. (чтобы уменьшить частоту следует увеличить размер страницы, увеличение размера страницы уменьшает размер таблицы страниц и как результат уменьшает затраты памяти. Если страница велика, то значит и велика фиктивная область)
· Время доступа к таблице страниц(таблицу страниц желательно размещать в быстрых запоминающих устройствах)
· Критерий выбора страницы на выгрузку. (вытесняется страница к которой в последнее время было меньше всего обращений, вытесняется первая попавшаяся страница, вытесняется дольше всего не использовавшаяся страница)
Сегментное распределение:
· ВАп Делиться на сегменты (возможность задания дифференцированных прав доступа процесса к его сегментам, возможность организации совместного использования фрагментов программ разными процессами)
· Основные типы сегментов на уровне ОС (сегмент данных, сегмент кода, системные сегменты)
· Максимальный размер сегмента определяется разрядностью виртуального адреса (Н: для 32 разрядной организации максимальный размер будет 4 гига)
· В таблице сегментов процесса для каждого сегмента указывается (начальный физ. адрес сегмента в озу, размер сегмента, права доступа к сегменту, признаки модификации, присутствия и обращения к данному сегменту за последний интервал времени, другая информация)
· Если виртуальные адресные пространства нескольких процессов включают один и тот же сегмент, то в таблице сегментов этих процессов делаются ссылки на один и тот же участок озу.
· Действия при обращении к памяти (аналогичные действия при страничном организации)
· Преобразование виртуального адреса в физический (виртуальный адрес при сегментной организации памяти может быть представлен парой (g,s) где g -номер сегмента, а s- смещение в сегменте, имеет произвольный размер)
· Недостатки метода (избыточность, фрагментация памяти, нельзя получить адрес начала сегмента по его номеру)
Сегментно страничное распределение:
· Сегментация (все виртуальные сегменты образуют одно непрерывное виртуальное адресное пространство, адрес в ВАп задается парой номер сегмента и смещение относительно начала сегмента, также для каждого процесса создается таблица сегментов, в отличие от дескриптора сегмента при сегментном распределении содержащего физический адрес сегмента в данном случае в дескриптор заноситься начальный или базовый линейных виртуальный адрес сегмента, пара базовый виртуальный адрес сегмента и смещения однозначно преобразуется в линейный виртуальный адрес, который затем преобразуется в физический адрес же страничный механизм)
· Страничный механизм (деление общего линейного ВАп процесса в физической памяти осуществляется как при страничном организации)
· Преобразование виртуального адреса в физический (1 этап: вычисляется адрес поля дескриптора сегмента и анализируются права доступа к сегменту, 2 этап: преобразование такое же как при страничном распределении)
Кэширование данных :
Память ВМ представлена совокупностью запоминающих устройств (ЗУ) различных видов, эта память может быть представлена в виде иерархии по сл признакам: время доступа к данным, объем, стоимость хранения 1-го бита. (чем больше быстродействие, тем больше стоимости хранения данных на 1 бит и меньше объем устройства) Компромиссом является кэш-память.
и 
называется отношение с тем же заголовком, что и у отношений 
отношение A и B, выполненоe по всем общим атрибутам, причем из результирующего отношения исключают по одному экземпляру каждого общего атрибута.