Ключевые слова. В этой лекции мы обсудили важные аспекты языка SQL, относящиеся к механизмам обновления данных

Тесты

Заключение

В этой лекции мы обсудили важные аспекты языка SQL, относящиеся к механизмам обновления данных. В первом разделе были рассмотрены операторы прямого SQL, предназначенные для вставки, модификации и удаления данных из существующих таблиц. Операторы UPDATE и DELETE этой категории иногда называют поисковыми, поскольку в них включаются условия на строки таблицы, которые должны быть модифицированы или удалены. В языке SQL определены также позиционные операторы модификации и удаления строк, а также динамические позиционные варианты этих операторов, но для их обсуждения требуется общее рассмотрение встраиваемого и динамического SQL, что выходит за рамки данного курса. По мнению автора, поисковые версии операторов модификации и удаления хорошо характеризуют соответствующие возможности языка SQL. Заметим также, что оператор INSERT, представленный в этой лекции, специфицирован в языке SQL только в этом варианте.

Второй раздел лекции посвящен обсуждению возможностей языка SQL, связанных с применимостью операций обновления базы данных через виртуальные таблицы, в том числе, через представления. Мы рассмотрели ограничения языка SQL/92, накладываемые на виртуальные таблицы, к которым применимы операции обновления. Отмечалось, что эти ограничения являются достаточными, но не необходимыми для применения операций обновления. Был продемонстрирован подход стандарта SQL:1999, где предлагаются рекомендации, но не требования, которым следует придерживаться реализациям SQL, чтобы соответствовать стандарту.

Наконец, в третьем разделе лекции рассматривался механизм триггеров. В первом подразделе мы упомянули основные понятия триггеров, которые были введены при выполнении проекта System R. Далее были введены основные синтаксические конструкции, предназначенные для определения триггеров, а также была обсуждена их базовая семантика. В следующем подразделе обсуждались принципы выполнения триггеров, заложенные в стандарт SQL:1999. Наконец, в заключение раздела были рассмотрены имеющиеся взаимосвязи между ссылочными действиями и триггерами.

Одним из основных выводов лекции является то, что в стандарте SQL:1999 спецификации многих аспектов, относящихся к обновлению баз данных, обоснованы недостаточно убедительно. В ряде случаев разработчики стандарта ожидают улучшения спецификаций в следующих версиях стандарта.

Часть следующей лекции, относящаяся к средствам языка SQL, которые предназначены для управления транзакциями, также имеет непосредственное отношение к операторам обновления баз данных.

Пусть таблицы базы данных EMP-DEPT-PRO имеют следующий вид:

EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				15000.00	13000.00
				16000.00	14000.00
				14000.00	NULL
		NULL		16000.00	13000.00
			NULL	15000.00	14000.00
			NULL	17000.00	NULL
				17000.00	16000.00
				16000.00	15000.00
				17000.00	16000.00
				20000.00	NULL
				18000.00	17000.00
		NULL		18000.00	17000.00
				22000.00	20000.00
				22000.00	20000.00
	NULL			13000.00	12000.00
	NULL			14000.00	22000.00
	NULL		NULL	19000.00	18000.00

DEPT

DEPT_NO	DEPT_NAME	DEPT_MNG
	ALPHA
	BETA
	GAMMA

PRO

PRO_NO	PRO_TITLE	PRO_MNG
	Starburst
	Postgres
	Sedna
	Altair
	BizQuery

1 (1) Пусть имеется пустая таблица EMP_MNGS с заголовком

EMP_NO

DEPT_MNG

PRO_MNG

Какая из следующих таблиц EMP_MNGS является результатом выполнения оператора

INSERT INTO EMP_MNGS
(SELECT DISTINCT EMP_NO,
CASE WHEN EMP.DEPT_NO IS NOT NULL THEN DEPT_MNG ELSE NULL,
CASE WHEN EMP.PRO_NO IS NOT NULL THEN PRO_MNG ELSE NULL,
FROM EMP, DEPT, PRO
WHERE (EMP.DEPT_NO IS NULL OR EMP.DEPT_NO = DEPT.DEPT_NO)
AND (EMP.PRO_NO IS NULL OR EMP.PRO_NO = PRO.PRO_NO)
AND IF (EMP.DEPT_NO IS NOT NULL
AND EMP.PRO_NO IS NOT NULL)
THEN DEPT_MNG <> PRO_MNG);?

(а) -

EMP_MNGS

EMP_NO	DEPT_MNG	PRO_MNG

(б) +

EMP_MNGS

EMP_NO	DEPT_MNG	PRO_MNG
		NULL
		NULL
	NULL
	NULL
	NULL

(в)

EMP_MNGS

EMP_NO	DEPT_MNG	PRO_MNG
		NULL
	NULL
	NULL
	NULL

1 (2) Какая из следующих таблиц является результатом выполнения оператора

UPDATE EMP SET EMP_SAL = (SELECT (E.EMP_SAL + E.EMP_BONUS)/2
FROM EMP E, DEPT
WHERE EMP.DEPT_NO = DEPT.DEPT_NO
AND DEPT_MNG = E.EMP_NO)
WHERE DEPT_NO IS NOT NULL
AND PRO_NO = (SELECT PRO_NO
FROM EMP E, PRO
WHERE PRO_NAME = ‘Sedna’
AND PRO_MNG = E.EMP_NO
AND E.EMP_SAL < EMP.EMP_SAL);?

(а) +

EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				15000.00	13000.00
				16000.00	14000.00
				14000.00	NULL
		NULL		16000.00	13000.00
			NULL	15000.00	14000.00
			NULL	17000.00	NULL
				17000.00	16000.00
				16000.00	15000.00
				17000.00	16000.00
				20000.00	NULL
				18000.00	17000.00
		NULL		18000.00	17000.00
				22000.00	20000.00
				16500.00	20000.00
	NULL			13000.00	12000.00
	NULL			14000.00	22000.00
	NULL		NULL	19000.00	18000.00

(б) -

EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				15000.00	13000.00
				17500.00	14000.00
				14000.00	NULL
		NULL		16000.00	13000.00
			NULL	17500.00	14000.00
			NULL	17000.00	NULL
				17000.00	16000.00
				16000.00	15000.00
				16500.00	16000.00
				20000.00	NULL
				18000.00	17000.00
		NULL		18000.00	17000.00
				22000.00	20000.00
				18500.00	20000.00
	NULL			13000.00	12000.00
	NULL			14000.00	22000.00
	NULL		NULL	19000.00	18000.00

(в) –

EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				18500.00	20000.00

1 (3) Какая из следующих таблиц является результатом выполнения оператора

DELETE FROM EMP
WHERE NOT EXISTS (SELECT *
FROM EMP E
WHERE EMP.DEPT_NO = E.DEPT_NO
AND (EMP.EMP_SAL + EMP.EMP_BONUS) >
(E.EMP_SAL + E.EMP_BONUS))
AND EMP_SAL + EMP_BONUS = (SELECT MIN (E.EMP_SAL + E.EMP_BONUS)
FROM EMP E
WHERE EMP.PRO_NO = E.PRO_NO)?

(а) +

EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				16000.00	14000.00
				14000.00	NULL
		NULL		16000.00	13000.00
			NULL	15000.00	14000.00
			NULL	17000.00	NULL
				17000.00	16000.00
				16000.00	15000.00
				17000.00	16000.00
				20000.00	NULL
		NULL		18000.00	17000.00
				22000.00	20000.00
				22000.00	20000.00
	NULL			13000.00	12000.00
	NULL			14000.00	22000.00
	NULL		NULL	19000.00	18000.00

(б) -

EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				16000.00	14000.00
				14000.00	NULL
		NULL		16000.00	13000.00
			NULL	15000.00	14000.00
			NULL	17000.00	NULL
				17000.00	16000.00
				16000.00	15000.00
				17000.00	16000.00
				20000.00	NULL
		NULL		18000.00	17000.00
				22000.00	20000.00
				22000.00	20000.00
	NULL			13000.00	12000.00
	NULL			14000.00	22000.00
	NULL		NULL	19000.00	18000.00

(в)

EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				16000.00	14000.00
				14000.00	NULL
		NULL		16000.00	13000.00
			NULL	15000.00	14000.00
			NULL	17000.00	NULL
				17000.00	16000.00
				17000.00	16000.00
				20000.00	NULL
		NULL		18000.00	17000.00
				22000.00	20000.00
				22000.00	20000.00
	NULL			13000.00	12000.00
	NULL			14000.00	22000.00
	NULL		NULL	19000.00	18000.00

2 (1) Пусть имеются следующие два определения представлений:

CREATE VIEW LA_EMP AS
SELECT *
FROM EMP

WHERE EMP_SAL < (SELECT AVG (E.EMP_SAL)
FROM EMP E
WHERE E.DEPT_NO = EMP.DEPT_NO);

и

CREATE VIEW LA_LA_EMP AS
SELECT *
FROM LA_EMP

WHERE EMP_SAL < (SELECT AVG (E.EMP_SAL)
FROM LA_EMP E
WHERE E.DEPT_NO = LA_EMP.DEPT_NO);

При принятом в этих упражнениях примерном составе строк таблицы EMP после материализации этих представлений были бы получены следующие таблицы:

LA_EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				15000.00	13000.00
				14000.00	NULL
			NULL	15000.00	14000.00
				17000.00	16000.00
				16000.00	15000.00
				17000.00	16000.00
				18000.00	17000.00
		NULL		18000.00	17000.00

LA_LA_EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				14000.00	NULL
				16000.00	15000.00

Пусть над представлением LA_LA_EMP выполняется операция

UPDATE LA_LA_EMP SET EMP_SAL = EMP_SAL + 10000.00

Какие из приведенных ниже таблиц соответствуют материализованным представлениям LA_LA_EMP и LA_EMP после выполнения этой операции?

(а) -

LA_EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				15000.00	13000.00
				24000.00	NULL
			NULL	15000.00	14000.00
				17000.00	16000.00
				26000.00	15000.00
				17000.00	16000.00
				18000.00	17000.00
		NULL		18000.00	17000.00

LA_LA_EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				24000.00	NULL
				26000.00	15000.00

(б) +

LA_EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				15000.00	13000.00
				16000.00	14000.00
		NULL		16000.00	13000.00
			NULL	15000.00	14000.00
			NULL	17000.00	NULL
				17000.00	16000.00
				17000.00	16000.00
				18000.00	17000.00
		NULL		18000.00	17000.00

LA_LA_EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				15000.00	13000.00
			NULL	15000.00	14000.00

(в) -

LA_EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				15000.00	13000.00
				14000.00	NULL
			NULL	15000.00	14000.00
				17000.00	16000.00
				16000.00	15000.00
				17000.00	16000.00
				18000.00	17000.00
		NULL		18000.00	17000.00

LA_LA_EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				14000.00	NULL
				16000.00	15000.00

2 (2) Пусть имеются следующие два определения представлений:

CREATE VIEW RICH_DEPT AS

SELECT *
FROM EMP
WHERE (SELECT AVG (E.EMP_SAL)
FROM EMP E
WHERE E.DEPT_NO = EMP.DEPT_NO) >
(SELECT AVG (E.EMP_SAL)
FROM EMP E
WHERE E.PRO_NO = EMP.PRP_NO);

и

CREATE VIEW RICH_EMP AS

SELECT *
FROM RICH_DEPT
WHERE EMP_SAL > (SELECT AVG (E.EMP_SAL + E.EMP_BONUS)
FROM EMP E
WHERE E.DEPT_NO = EMP.DEPT_NO);

При принятом в этих упражнениях примерном составе строк таблицы EMP после материализации этих представлений были бы получены следующие таблицы:

RICH_DEPT

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				14000.00	NULL
				17000.00	16000.00
				16000.00	15000.00
				20000.00	NULL
				18000.00	17000.00
				22000.00	20000.00
				22000.00	20000.00

RICH_EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				20000.00	NULL
				22000.00	20000.00
				22000.00	20000.00

Пусть над представлением RICH_EMP выполняется операция

UPDATE RICH_EMP SET EMP_SAL = EMP_SAL - 10000.00

Какие из приведенных ниже таблиц соответствуют материализованным представлениям RICH_DEPT и RICH_EMP после выполнения этой операции?

(а) +

RICH_DEPT

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				15000.00	13000.00
				16000.00	14000.00
				14000.00	NULL
			NULL	15000.00	14000.00
				17000.00	16000.00
				16000.00	15000.00
				10000.00	NULL
				18000.00	17000.00
				12000.00	20000.00
				12000.00	20000.00

RICH_EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				16000.00	14000.00
				14000.00	NULL
				17000.00	16000.00
				16000.00	15000.00
				18000.00	17000.00

(б) –

RICH_DEPT

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				14000.00	NULL
				17000.00	16000.00
				16000.00	15000.00
				10000.00	NULL
				18000.00	17000.00
				12000.00	20000.00
				12000.00	20000.00

RICH_EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				10000.00	NULL
				12000.00	20000.00
				12000.00	20000.00

(в) -

RICH_DEPT

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				14000.00	NULL
				17000.00	16000.00
				16000.00	15000.00
				20000.00	NULL
				18000.00	17000.00
				22000.00	20000.00
				22000.00	20000.00

RICH_EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				20000.00	NULL
				22000.00	20000.00
				22000.00	20000.00

2 (3) Пусть имеются следующие два определения представлений:

CREATE VIEW RICH_EMP AS
SELECT *
FROM EMP
WHERE EMP_SAL = (SELECT MAX (E.EMP_SAL)
FROM EMP E
WHERE E.DEPT_NO = EMP.DEPT_NO)
OR EMP_BONUS = (SELECT MAX (E.EMP_BONUS)
FROM EMP E
WHERE E.DEPT_NO = EMP.DEPT_NO);

и

CREATE VIEW LESS_RICH_EMP AS
SELECT *
FROM RICH_EMP
WHERE EMP_SAL + EMP_BONUS =
(SELECT MIN (RE.EMP_SAL + RE.EMP_BONUS)
FROM RICH_EMP RE
WHERE RE.DEPT_NO = RICH_EMP.DEPT_NO);

При принятом в этих упражнениях примерном составе строк таблицы EMP после материализации этих представлений были бы получены следующие таблицы:

RICH_EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				16000.00	14000.00
			NULL	15000.00	14000.00
			NULL	17000.00	NULL
				17000.00	16000.00
				17000.00	16000.00
				20000.00	NULL
				22000.00	20000.00
				22000.00	20000.00

LESS_RICH_EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
			NULL	15000.00	14000.00
				17000.00	16000.00
				17000.00	16000.00
				22000.00	20000.00
				22000.00	20000.00

Пусть над представлением RICH_EMP выполняется операция

UPDATE RICH_EMP SET EMP_SAL = EMP_SAL + 10000.00,
EMP_BONUS = EMP_BONUS – 10000.00

Какие из приведенных ниже таблиц соответствуют материализованным представлениям RICH_EMP и LESS_RICH_EMP после выполнения этой операции?

(а) -

RICH_EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				16000.00	14000.00
			NULL	15000.00	14000.00
			NULL	17000.00	NULL
				17000.00	16000.00
				17000.00	16000.00
				20000.00	NULL
				22000.00	20000.00
				22000.00	20000.00

LESS_RICH_EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
			NULL	15000.00	14000.00
				17000.00	16000.00
				17000.00	16000.00
				22000.00	20000.00
				22000.00	20000.00

(б) -

RICH_EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				16000.00	14000.00
			NULL	25000.00	4000.00
			NULL	17000.00	NULL
				27000.00	6000.00
				27000.00	6000.00
				20000.00	NULL
				32000.00	10000.00
				32000.00	10000.00

LESS_RICH_EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
			NULL	25000.00	4000.00
				27000.00	6000.00
				27000.00	6000.00
				32000.00	10000.00
				32000.00	10000.00

(в) +

RICH_EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
				16000.00	14000.00
			NULL	25000.00	4000.00
				27000.00	6000.00
				16000.00	15000.00
				27000.00	6000.00
				18000.00	17000.00
		NULL		18000.00	17000.00
				32000.00	10000.00
				32000.00	10000.00

LESS_RICH_EMP

EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
			NULL	25000.00	4000.00
				16000.00	15000.00
				18000.00	17000.00
		NULL		18000.00	17000.00

3 (1) Предположим, что в базе данных поддерживаются две специальные “аудиторские” таблицы AUDIT1 и AUDIT2 следующей структуры:

AUDIT1

EMP_NO

EVENT_TS

OLD_SAL

OLD_BONUS

NEW_SAL

NEW_BONUS

AUDIT2

DEPT_NO

EVENT_TS

AVG_DEPT_SAL

AVG_DEPT_BONUS

В таблице AUDIT1 фиксируются все события, соответствующие изменению зарплаты служащих. В столбце EVENT_TS сохраняется временная метка соответствующего события. В таблице AUDIT2 регистрируются события, связанные с такими обновлениями данных о сотрудниках, при которых среднее значение зарплаты или премиальных некоторого отдела становится меньше среднего значения зарплаты или премиальных служащих, участвующих в некотором проекте, в котором принимает участие и хотя бы один сотрудник данного отдела. В столбце EVENT_TS таблицы AUDIT2 сохраняется временная метка соответствующего события, а в столбцах AVG_DEPT_SAL и AVG_DEPT_BONUS – соответствующие “критические” средние значения.

Какие из приводимых ниже определений триггеров обеспечат требуемую функциональность?

(а) +

CREATE TRIGGER EMP_INSERT AFTER INSERT ON EMP
REFERENCING NEW ROW AS NEW_EMP
FOR EACH ROW
INSERT INTO AUDIT1
ROW (NEW_EMP.EMP_NO, CURRENT_TIMESTAMP, 0, 0,
NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS);

CREATE TRIGGER EMP_UPDATE AFTER UPDATE OF EMP_SAL, EMP_BONUS ON EMP
REFERENCING OLD ROW AS OLD_EMP NEW ROW AS NEW_EMP
FOR EACH ROW
INSERT INTO AUDIT1
ROW (NEW_EMP.EMP_NO, CURRENT_TIMESTAMP,
OLD_EMP.EMP_SAL, OLD_EMP.EMP_BONUS,
NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS);

CREATE TRIGGER EMP_DELETE AFTER DELETE ON EMP
REFERENCING OLD ROW AS OLD_EMP
FOR EACH ROW
INSERT INTO AUDIT1
ROW (OLD_EMP.EMP_NO, CURRENT_TIMESTAMP,
OLD_EMP.EMP_SAL, OLD_EMP.EMP_BONUS, 0, 0);

CREATE TRIGGER EMP_INSERT1 AFTER INSERT ON EMP
INSERT INTO AUDIT2
(SELECT DISTINCT NE.DEPT_NO, CURRENT_TIMESTAMP,
AVG (NE.EMP_SAL),
AVG (NE.EMP_BONUS)
FROM EMP NE
WHERE NE.DEPT_NO = NEW_EMP.DEPT_NO
GROUP BY NE.DEPT_NO
HAVING AVG (NE.EMP_SAL), AVG (NE.EMP_BONUS) <
(SELECT AVG (NE1.EMP_SAL), AVG (NE1.EMP_BONUS)
FROM EMP NE1
WHERE NE.PRO_NO = NE1.PRO_NO));

CREATE TRIGGER EMP_UPDATE1 AFTER UPDATE OF EMP_SAL, EMP_BONUS ON EMP
INSERT INTO AUDIT2
(SELECT DISTINCT NE.DEPT_NO, CURRENT_TIMESTAMP,
AVG (NE.EMP_SAL),
AVG (NE.EMP_BONUS)
FROM NEW_EMP NE
WHERE NE.DEPT_NO = NEW_EMP.DEPT_NO
GROUP BY NE.DEPT_NO
HAVING AVG (NE.EMP_SAL), AVG (NE.EMP_BONUS) <
(SELECT AVG (NE1.EMP_SAL), AVG (NE1.EMP_BONUS)
FROM NEW_EMP NE1
WHERE NE.PRO_NO = NE1.PRO_NO));

CREATE TRIGGER EMP_DELETE1 AFTER DELETE ON EMP
INSERT INTO AUDIT2
(SELECT DISTINCT NE.DEPT_NO, CURRENT_TIMESTAMP,
AVG (NE.EMP_SAL),
AVG (NE.EMP_BONUS)
FROM NEW_EMP NE
WHERE NE.DEPT_NO = NEW_EMP.DEPT_NO
GROUP BY NE.DEPT_NO
HAVING AVG (NE.EMP_SAL), AVG (NE.EMP_BONUS) <
(SELECT AVG (NE1.EMP_SAL), AVG (NE1.EMP_BONUS)
FROM NEW_EMP NE1
WHERE NE.PRO_NO = NE1.PRO_NO));

(б) -

CREATE TRIGGER EMP_INSERT AFTER INSERT ON EMP
REFERENCING NEW ROW AS NEW_EMP
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO AUDIT1
ROW (NEW_EMP.EMP_NO, CURRENT_TIMESTAMP, 0, 0,
NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS);
INSERT INTO AUDIT2
(SELECT DISTINCT NE.DEPT_NO, CURRENT_TIMESTAMP,
AVG (NE.EMP_SAL), AVG (NE.EMP_BONUS)
FROM EMP NE
WHERE NE.DEPT_NO = NEW_EMP.DEPT_NO
GROUP BY NE.DEPT_NO
HAVING AVG (NE.EMP_SAL), AVG (NE.EMP_BONUS) <
(SELECT AVG (NE1.EMP_SAL), AVG (NE1.EMP_BONUS)
FROM EMP NE1
WHERE NE.PRO_NO = NE1.PRO_NO));
END;

CREATE TRIGGER EMP_UPDATE AFTER UPDATE OF EMP_SAL, EMP_BONUS ON EMP
REFERENCING OLD ROW AS OLD_EMP NEW ROW AS NEW_EMP
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO AUDIT1
ROW (NEW_EMP.EMP_NO, CURRENT_TIMESTAMP,
OLD_EMP.EMP_SAL, OLD_EMP.EMP_BONUS,
NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS);

INSERT INTO AUDIT2
(SELECT DISTINCT NE.DEPT_NO, CURRENT_TIMESTAMP,
AVG (NE.EMP_SAL), AVG (NE.EMP_BONUS)
FROM EMP NE
WHERE NE.DEPT_NO = NEW_EMP.DEPT_NO
GROUP BY NE.DEPT_NO
HAVING AVG (NE.EMP_SAL), AVG (NE.EMP_BONUS) <
(SELECT AVG (NE1.EMP_SAL), AVG (NE1.EMP_BONUS)
FROM EMP NE1
WHERE NE.PRO_NO = NE1.PRO_NO));
END;

CREATE TRIGGER EMP_DELETE AFTER DELETE ON EMP
REFERENCING OLD ROW AS OLD_EMP
FOR EACH ROW
BEGIN ATOMIC
BEGIN ATOMIC
INSERT INTO AUDIT1
ROW (OLD_EMP.EMP_NO, CURRENT_TIMESTAMP,
OLD_EMP.EMP_SAL, OLD_EMP.EMP_BONUS, 0, 0);
INSERT INTO AUDIT2
(SELECT DISTINCT NE.DEPT_NO, CURRENT_TIMESTAMP,
AVG (NE.EMP_SAL), AVG (NE.EMP_BONUS)
FROM EMP NE
WHERE NE.DEPT_NO = NEW_EMP.DEPT_NO
GROUP BY NE.DEPT_NO
HAVING AVG (NE.EMP_SAL), AVG (NE.EMP_BONUS) <
(SELECT AVG (NE1.EMP_SAL), AVG (NE1.EMP_BONUS)
FROM EMP NE1
WHERE NE.PRO_NO = NE1.PRO_NO));
END;

(в) -

CREATE TRIGGER EMP_UPDATE AFTER ON EMP
REFERENCING OLD ROW AS OLD_EMP NEW ROW AS NEW_EMP
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO AUDIT1
ROW (NEW_EMP.EMP_NO, CURRENT_TIMESTAMP,
OLD_EMP.EMP_SAL, OLD_EMP.EMP_BONUS,
NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS);

INSERT INTO AUDIT2
(SELECT DISTINCT NE.DEPT_NO, CURRENT_TIMESTAMP,
AVG (NE.EMP_SAL), AVG (NE.EMP_BONUS)
FROM EMP NE
WHERE NE.DEPT_NO = NEW_EMP.DEPT_NO
GROUP BY NE.DEPT_NO
HAVING AVG (NE.EMP_SAL), AVG (NE.EMP_BONUS) <
(SELECT AVG (NE1.EMP_SAL), AVG (NE1.EMP_BONUS)
FROM EMP NE1
WHERE NE.PRO_NO = NE1.PRO_NO));
END;

3 (2) Предположим, что в базе данных поддерживаются две отдельные таблицы RDEMP и RMEMP с такой же структурой, что и таблица EMP, но содержащие данные о служащих, имеющих самый большой доход в своем отделе и среди участников своего проекта соответственно (если для некоторого служащего размер премиальных неизвестен, премиальные не учитываются в общей сумме его дохода). Какие из приводимых ниже определений триггеров обеспечат требуемое наполнение этих таблиц?

(а) +

CREATE TRIGGER EMP_INSERT1 AFTER INSERT ON EMP
REFERENCING NEW ROW AS NEW_EMP
FOR EACH ROW
WHEN (NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0)>=
(SELECT RDEMP.EMP_SAL + COALESCE (RDEMP.EMP_BONUS, 0)
FROM RDEMP
WHERE RDEMP.DEPT_NO = NEW_EMP.DEPT_NO)
OR NOT EXISTS (SELECT *
FROM RDEMP
WHERE RDEMP.DEPT_NO = NEW_EMP.DEPT_NO))
BEGIN ATOMIC
DELETE FROM RDEMP
WHERE RDEMP.EMP_NO = NEW_EMP.EMP_NO
AND RDEMP.EMP_SAL + COALESCE (RDEMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0);
INSERT INTO RDEMP
ROW (NEW_EMP.EMP_NO, NEW_EMP.DEPT_NO, NEW_EMP.PRO_NO,
NEW_EMP.EMP_BDATE, NEW_EMP.EMP_SAL,

NEW_EMP.EMP_BONUS);
END;

CREATE TRIGGER EMP_INSERT2 AFTER INSERT ON EMP
REFERENCING NEW ROW AS NEW_EMP
FOR EACH ROW
WHEN (NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0)>=
(SELECT RMDEMP.EMP_SAL + COALESCE (RMEMP.EMP_BONUS, 0)
FROM RMEMP
WHERE RMEMP.DEPT_NO = NEW_EMP.DEPT_NO)
OR NOT EXISTS (SELECT *
FROM RMEMP
WHERE RMEMP.PRO_NO = NEW_EMP.PRO_NO))
BEGIN ATOMIC
DELETE FROM RMEMP
WHERE RMEMP.EMP_NO = NEW_EMP.EMP_NO
AND RMEMP.EMP_SAL + COALESCE (RMEMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0);
INSERT INTO RMEMP
ROW (NEW_EMP.EMP_NO, NEW_EMP.DEPT_NO, NEW_EMP.PRO_NO,
NEW_EMP.EMP_BDATE, NEW_EMP.EMP_SAL,

NEW_EMP.EMP_BONUS);
END;

CREATE TRIGGER EMP_UPDATE1 AFTER UPDATE OF EMP_SAL, EMP_BONUS ON EMP
REFERENCING NEW ROW AS NEW_EMP
FOR EACH ROW
WHEN (NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0)>=
(SELECT RDEMP.EMP_SAL + COALESCE (RDEMP.EMP_BONUS, 0)
FROM RDEMP
WHERE RDEMP.DEPT_NO = NEW_EMP.DEPT_NO)
OR NOT EXISTS (SELECT *
FROM RDEMP
WHERE RDEMP.DEPT_NO = NEW_EMP.DEPT_NO))
BEGIN ATOMIC
DELETE FROM RDEMP
WHERE RDEMP.EMP_NO = NEW_EMP.EMP_NO
AND RDEMP.EMP_SAL + COALESCE (RDEMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0);
INSERT INTO RDEMP
ROW (NEW_EMP.EMP_NO, NEW_EMP.DEPT_NO, NEW_EMP.PRO_NO,
NEW_EMP.EMP_BDATE, NEW_EMP.EMP_SAL,

NEW_EMP.EMP_BONUS);
END;

CREATE TRIGGER EMP_UPDATE2 AFTER UPDATE OF EMP_SAL, EMP_BONUS ON EMP
REFERENCING NEW ROW AS NEW_EMP
FOR EACH ROW
WHEN (NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0)>=
(SELECT RMEMP.EMP_SAL + COALESCE (RMEMP.EMP_BONUS, 0)
FROM RMEMP
WHERE RMEMP.DEPT_NO = NEW_EMP.DEPT_NO)
OR NOT EXISTS (SELECT *
FROM RMEMP
WHERE RMEMP.PRO_NO = NEW_EMP.PRO_NO))
BEGIN ATOMIC
DELETE FROM RMEMP
WHERE RMEMP.EMP_NO = NEW_EMP.EMP_NO
AND RMEMP.EMP_SAL + COALESCE (RMEMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0);
INSERT INTO RMEMP
ROW (NEW_EMP.EMP_NO, NEW_EMP.DEPT_NO, NEW_EMP.PRO_NO,
NEW_EMP.EMP_BDATE, NEW_EMP.EMP_SAL,

NEW_EMP.EMP_BONUS);
END;

CREATE TRIGGER EMP_DELETE1 AFTER DELETE ON EMP
REFERENCING OLD ROW AS OLD_EMP
FOR EACH ROW
WHEN (NEW_EMP IN (SELECT *
FROM RDEMP
WHERE RDEMP.DEPT_NO = OLD_EMP.DEPT_NO))
BEGIN ATOMIC
DELETE FROM RDEMP
WHERE RDEMP.EMP_NO = OLD_EMP.EMP_NO;
INSERT INTO RDEMP
(SELECT *
FROM EMP
WHERE NOT EXISTS (SELECT *
FROM RDEMP
WHERE RDEMP.DEPT_NO = EMP.DEPT_NO)
AND EMP.DEPT_NO = OLD_EMP.DEPT_NO
AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0)= (SELECT MAX (E.EMP_SAL + COALESCE (E.EMP_BONUS, 0))
FROM EMP E
WHERE E.DEPT_NO = EMP.DEPT_NO);
END;

CREATE TRIGGER EMP_DELETE2 AFTER DELETE ON EMP
REFERENCING OLD ROW AS OLD_EMP
FOR EACH ROW
WHEN (NEW_EMP IN (SELECT *
FROM RMEMP
WHERE RMEMP.DEPT_NO = OLD_EMP.DEPT_NO))
BEGIN ATOMIC
DELETE FROM RMEMP
WHERE RMEMP.EMP_NO = OLD_EMP.EMP_NO;
INSERT INTO RMEMP
(SELECT *
FROM EMP
WHERE NOT EXISTS (SELECT *
FROM RMEMP
WHERE RMEMP.PRO_NO = EMP.PRO_NO)
AND EMP.DEPT_NO = OLD_EMP.DEPT_NO
AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0)= (SELECT MAX (E.EMP_SAL + COALESCE (E.EMP_BONUS, 0))
FROM EMP E
WHERE E.PRO_NO = EMP.PRO_NO);
END;

(б) -

CREATE TRIGGER EMP_INSERT AFTER INSERT ON EMP
REFERENCING NEW ROW AS NEW_EMP
FOR EACH ROW
BEGIN ATOMIC
WHEN (NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0)>=
(SELECT RDEMP.EMP_SAL + COALESCE (RDEMP.EMP_BONUS, 0)
FROM RDEMP
WHERE RDEMP.DEPT_NO = NEW_EMP.DEPT_NO)
OR NOT EXISTS (SELECT *
FROM RDEMP
WHERE RDEMP.DEPT_NO = NEW_EMP.DEPT_NO))
BEGIN ATOMIC
DELETE FROM RDEMP
WHERE RDEMP.EMP_NO = NEW_EMP.EMP_NO
AND RDEMP.EMP_SAL + COALESCE (RDEMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0);
INSERT INTO RDEMP
ROW (NEW_EMP.EMP_NO, NEW_EMP.DEPT_NO, NEW_EMP.PRO_NO,
NEW_EMP.EMP_BDATE, NEW_EMP.EMP_SAL,

NEW_EMP.EMP_BONUS);
END;

WHEN (NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0)>=
(SELECT RMDEMP.EMP_SAL + COALESCE (RMEMP.EMP_BONUS, 0)
FROM RMEMP
WHERE RMEMP.DEPT_NO = NEW_EMP.DEPT_NO)
OR NOT EXISTS (SELECT *
FROM RMEMP
WHERE RMEMP.PRO_NO = NEW_EMP.PRO_NO))
BEGIN ATOMIC
DELETE FROM RMEMP
WHERE RMEMP.EMP_NO = NEW_EMP.EMP_NO
AND RMEMP.EMP_SAL + COALESCE (RMEMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0);
INSERT INTO RMEMP
ROW (NEW_EMP.EMP_NO, NEW_EMP.DEPT_NO, NEW_EMP.PRO_NO,
NEW_EMP.EMP_BDATE, NEW_EMP.EMP_SAL,

NEW_EMP.EMP_BONUS);
END;
END;

CREATE TRIGGER EMP_UPDATE AFTER UPDATE OF EMP_SAL, EMP_BONUS ON EMP
REFERENCING NEW ROW AS NEW_EMP
FOR EACH ROW
BEGIN ATOMIC
WHEN (NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0)>=
(SELECT RDEMP.EMP_SAL + COALESCE (RDEMP.EMP_BONUS, 0)
FROM RDEMP
WHERE RDEMP.DEPT_NO = NEW_EMP.DEPT_NO)
OR NOT EXISTS (SELECT *
FROM RDEMP
WHERE RDEMP.DEPT_NO = NEW_EMP.DEPT_NO))
BEGIN ATOMIC
DELETE FROM RDEMP
WHERE RDEMP.EMP_NO = NEW_EMP.EMP_NO
AND RDEMP.EMP_SAL + COALESCE (RDEMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0);
INSERT INTO RDEMP
ROW (NEW_EMP.EMP_NO, NEW_EMP.DEPT_NO, NEW_EMP.PRO_NO,
NEW_EMP.EMP_BDATE, NEW_EMP.EMP_SAL,

NEW_EMP.EMP_BONUS);
END;
WHEN (NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0)>=
(SELECT RMEMP.EMP_SAL + COALESCE (RMEMP.EMP_BONUS, 0)
FROM RMEMP
WHERE RMEMP.DEPT_NO = NEW_EMP.DEPT_NO)
OR NOT EXISTS (SELECT *
FROM RMEMP
WHERE RMEMP.PRO_NO = NEW_EMP.PRO_NO))
BEGIN ATOMIC
DELETE FROM RMEMP
WHERE RMEMP.EMP_NO = NEW_EMP.EMP_NO
AND RMEMP.EMP_SAL + COALESCE (RMEMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0);
INSERT INTO RMEMP
ROW (NEW_EMP.EMP_NO, NEW_EMP.DEPT_NO, NEW_EMP.PRO_NO,
NEW_EMP.EMP_BDATE, NEW_EMP.EMP_SAL,

NEW_EMP.EMP_BONUS);
END;

END;

CREATE TRIGGER EMP_DELETE AFTER DELETE ON EMP
REFERENCING OLD ROW AS OLD_EMP
FOR EACH ROW
BEGIN ATOMIC
WHEN (NEW_EMP IN (SELECT *
FROM RDEMP
WHERE RDEMP.DEPT_NO = OLD_EMP.DEPT_NO))
BEGIN ATOMIC
DELETE FROM RDEMP
WHERE RDEMP.EMP_NO = OLD_EMP.EMP_NO;
INSERT INTO RDEMP
(SELECT *
FROM EMP
WHERE NOT EXISTS (SELECT *
FROM RDEMP
WHERE RDEMP.DEPT_NO = EMP.DEPT_NO)
AND EMP.DEPT_NO = OLD_EMP.DEPT_NO
AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0)= (SELECT MAX (E.EMP_SAL + COALESCE (E.EMP_BONUS, 0))
FROM EMP E
WHERE E.DEPT_NO = EMP.DEPT_NO);
END;
WHEN (NEW_EMP IN (SELECT *
FROM RMEMP
WHERE RMEMP.DEPT_NO = OLD_EMP.DEPT_NO))
BEGIN ATOMIC
DELETE FROM RMEMP
WHERE RMEMP.EMP_NO = OLD_EMP.EMP_NO;
INSERT INTO RMEMP
(SELECT *
FROM EMP
WHERE NOT EXISTS (SELECT *
FROM RMEMP
WHERE RMEMP.PRO_NO = EMP.PRO_NO)
AND EMP.DEPT_NO = OLD_EMP.DEPT_NO
AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0)= (SELECT MAX (E.EMP_SAL + COALESCE (E.EMP_BONUS, 0))
FROM EMP E
WHERE E.PRO_NO = EMP.PRO_NO);
END;

END;

(в) +

CREATE TRIGGER EMP_INSERT AFTER INSERT ON EMP
FOR EACH STATEMENT
BEGIN ATOMIC
DELETE * FROM RDEMP;
INSERT INTO RDEMP (SELECT *
FROM EMP
WHERE EMP_SAL =
(SELECT MAX (E.EMP_SAL + COALESCE (E_BONUS, 0))
FROM EMP E
WHERE E.DEPT_NO = EMP.DEPT_NO);
DELETE * FROM RMEMP;
INSERT INTO RMEMP (SELECT *
FROM EMP
WHERE EMP_SAL =
(SELECT MAX (E.EMP_SAL + COALESCE (E_BONUS, 0))
FROM EMP E
WHERE E.PRO_NO = EMP.PRO_NO);
END;

CREATE TRIGGER EMP_UPDATE AFTER UPDATE ON EMP
FOR EACH STATEMENT
BEGIN ATOMIC
DELETE * FROM RDEMP;
INSERT INTO RDEMP (SELECT *
FROM EMP
WHERE EMP_SAL =
(SELECT MAX (E.EMP_SAL + COALESCE (E_BONUS, 0))
FROM EMP E
WHERE E.DEPT_NO = EMP.DEPT_NO);
DELETE * FROM RMEMP;
INSERT INTO RMEMP (SELECT *
FROM EMP
WHERE EMP_SAL =
(SELECT MAX (E.EMP_SAL + COALESCE (E_BONUS, 0))
FROM EMP E
WHERE E.PRO_NO = EMP.PRO_NO);
END;

CREATE TRIGGER EMP_DELETE AFTER DELETE ON EMP
FOR EACH STATEMENT
BEGIN ATOMIC
DELETE * FROM RDEMP;
INSERT INTO RDEMP (SELECT *
FROM EMP
WHERE EMP_SAL =
(SELECT MAX (E.EMP_SAL + COALESCE (E_BONUS, 0))
FROM EMP E
WHERE E.DEPT_NO = EMP.DEPT_NO);
DELETE * FROM RMEMP;
INSERT INTO RMEMP (SELECT *
FROM EMP
WHERE EMP_SAL =
(SELECT MAX (E.EMP_SAL + COALESCE (E_BONUS, 0))
FROM EMP E
WHERE E.PRO_NO = EMP.PRO_NO);
END;

3 (3) Предположим, что в базе данных поддерживается таблица RDEMP, в которой с момента приема служащего на работу и вплоть до его увольнения фиксируется рейтинг служащего в его отделе по доходам, т.е. при каждой смене общей суммы дохода служащего, изменяющей его рейтинг, сохраняется временная метка этого события, размеры зарплаты и премиальных служащего, а также число служащих в данном отделе, получающих больший доход, и число служащих с меньшим доходом. Таблица RDEMP имеет следующую структуру:

EMP_NO

EVENT_TS

NEW_SAL

NEW_BONUS

NUMBER_LESS

NUMBER_MORE

Какие из приводимых ниже определений триггеров обеспечат требуемое наполнение этой таблицы?

(а) +

CREATE TRIGGER EMP_INSERT AFTER INSERT ON EMP
REFERENCING NEW ROW AS NEW_EMP
FOR EACH ROW
INSERT INTO RDEMP ROW (NEW_EMP.EMP_NO, CURRENT_TIMESRAMP,
NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS,
(SELECT COUNT (*)
FROM EMP
WHERE EMP.DEPT_NO = NEW_EMP.DEPT_NO
AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0) <
NEW_EMP.EMP_SAL + COALESCE (NEW_EMP.EMP_BONUS, 0)),
(SELECT COUNT (*)
FROM EMP
WHERE EMP.DEPT_NO = NEW_EMP.DEPT_NO
AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0) >
NEW_EMP.EMP_SAL + COALESCE (NEW_EMP.EMP_BONUS, 0)),

CREATE TRIGGER EMP_UPDATE AFTER UPDATE OF EMP_SAL, EMP_BONUS ON EMP
REFERENCING NEW ROW AS NEW_EMP
FOR EACH ROW
WHEN (((SELECT COUNT (*)
FRO

Дата добавления: 2014-01-03