Введение в модель данных SQL

Предположим, что в базе данных поддерживается таблица RDEMP, в которой с момента приема служащего на работу и вплоть до его увольнения фиксируется рейтинг служащего в его отделе по доходам, т.е. при каждой смене общей суммы дохода служащего, изменяющей его рейтинг, сохраняется временная метка этого события, размеры зарплаты и премиальных служащего, а также число служащих в данном отделе, получающих больший доход, и число служащих с меньшим доходом. Таблица RDEMP имеет следующую структуру:
EMP_NO
EVENT_TS
NEW_SAL
NEW_BONUS
NUMBER_LESS
NUMBER_MORE
Какие из приводимых ниже определений триггеров обеспечат требуемое наполнение этой таблицы?

(Ответ считается верным, если отмечены все правильные варианты ответов.)

Варианты ответа

CREATE TRIGGER EMP_INSERT AFTER INSERT ON EMP  REFERENCES NEW TABLE AS NEW_EMP  INSERT (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 TABLE AS NEW_EMP  INSERT INTO RDEMP    (SELECT NEW.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 NEW.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))     FROM NEW_EMP     WHERE ((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)))     <> (SELECT RDEMP.NUMBER_LESS, RDEMP.NUMBER_MORE      FROM RDEMP.EVENT_TS =             SELECT MAX (RDE.EVENT_TS)             FROM RDEMP RDE             WHERE RDE.EMP_NO = RDEMP.EMP_NO));CREATE TRIGGER EMP_DELETE AFTER DELETE ON EMP  REFERENCING OLD TABLE AS OLD_EMP  DELETE FROM RDEMP  WHERE RDEMP.EMP_NO IN (SELECT EMP_NO            FROM OLD_EMP);

(Верный ответ)

CREATE TRIGGER EMP_INSERT AFTER INSERT ON EMP  FOR EACH ROW  REFERENCING NEW ROW AS NEW_EMP  INSERT (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  FOR EACH ROW  REFERENCING NEW ROW AS NEW_EMP  INSERT INTO RDEMP    (SELECT NEW.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 NEW.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))     FROM NEW_EMP     WHERE ((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)))     <> (SELECT RDEMP.NUMBER_LESS, RDEMP.NUMBER_MORE      FROM RDEMP.EVENT_TS =             SELECT MAX (RDE.EVENT_TS)             FROM RDEMP RDE             WHERE RDE.EMP_NO = RDEMP.EMP_NO));CREATE TRIGGER EMP_DELETE AFTER DELETE ON EMP  FOR EACH ROW  REFERENCING OLD TABLE AS OLD_EMP  DELETE FROM RDEMP  WHERE RDEMP.EMP_NO IN (SELECT EMP_NO            FROM OLD_EMP);

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 (*)      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)))     <> (SELECT RDEMP.NUMBER_LESS, RDEMP.NUMBER_MORE      FROM RDEMP.EVENT_TS =             SELECT MAX (RDE.EVENT_TS)             FROM RDEMP RDE             WHERE RDE.EMP_NO = RDEMP.EMP_NO))    INSERT (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_DELETE AFTER DELETE ON EMP  REFERENCING OLD ROW AS OLD_EMP  DELETE FROM RDEMP  WHERE RDEMP.EMP_NO = OLD_EMP.EMP_NO;

(Верный ответ)

Похожие вопросы

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

Предположим, что в базе данных поддерживаются две специальные “аудиторские” таблицы 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 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.PRO_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
2442	1	208	1960	14000.00	NULL
2444	2	177	1970	17000.00	16000.00
2445	2	208	1950	16000.00	15000.00
2447	2	177	1960	20000.00	NULL
2448	3	101	1955	18000.00	17000.00
2450	3	234	1960	22000.00	20000.00
2451	3	155	1960	22000.00	20000.00

RICH_EMP
EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
2447	2	177	1960	20000.00	NULL
2450	3	234	1960	22000.00	20000.00
2451	3	155	1960	22000.00	20000.00

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

UPDATE RICH_EMP SET EMP_SAL = EMP_SAL - 10000.00

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

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

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);

LA_EMP
EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
2440	1	177	1950	15000.00	13000.00
2442	1	208	1960	14000.00	NULL
2452	1	155	NULL	15000.00	14000.00
2444	2	177	1970	17000.00	16000.00
2445	2	208	1950	16000.00	15000.00
2446	2	155	1970	17000.00	16000.00
2448	3	101	1955	18000.00	17000.00
2449	3	NULL	1950	18000.00	17000.00

LA_LA_EMP
EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
2442	1	208	1960	14000.00	NULL
2445	2	208	1950	16000.00	15000.00

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

UPDATE LA_LA_EMP SET EMP_SAL = EMP_SAL + 10000.00

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

Пусть A является столбцом таблицы T типа CHARACTER (5), а B – столбцом таблицы T типа CHARACTER VARYING (8). Пусть при вставке в строку s этой таблицы в столбец A было занесено значение ‘LET’, а в столбец B – ‘IT’. В столбце какого из перечисленных ниже типов могло бы быть сохранено значение выражения A || B || ‘BE’, вычисленное для значений строки s? (Здесь “_” обозначает символ пробела.)

Пусть в базовой таблице S определен внешний ключ (a, b, c), где a, b и c – имена столбцов определяемой таблицы. Пусть для этого внешнего ключа специфицирован способ соответствия PARTIAL. Будем считать, что все столбцы целого типа, и для них специфицированы значения по умолчанию 4, 44 и 444 соответственно. Предположим, что внешний ключ ссылается на первичный ключ таблицы T, состоящий из столбцов с такими же именами. Пусть в таблице S присутствуют строки s1, s2 и s2 со значениями внешнего ключа <NULL, NULL, 5>, <25, 1, NULL> и <25, 1, 5> соответственно. Что произойдет при удалении строки t из таблицы T со значением первичного ключа <25, 1, 5>, если при определении внешнего ключа (a, b, c) таблицы S указано ссылочное действие ON DELETE SET DEFAULT и строки s1, s2 и s2 соответствуют исключительно строке t?

Пусть A и B являются столбцами таблицы T типа CHARACTER (5), и пусть в при вставке в строку s этой таблицы в столбец A было занесено значение ‘ONE’, а в столбец B – ‘TIME’. Каким будет значение выражения A || B при его вычислении для значений строки s? (Здесь “_” обозначает символ пробела.)

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

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);

RICH_EMP
EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
2441	1	155	1955	16000.00	14000.00
2452	1	155	NULL	15000.00	14000.00
2453	1	234	NULL	17000.00	NULL
2444	2	177	1970	17000.00	16000.00
2446	2	155	1970	17000.00	16000.00
2447	2	177	1960	20000.00	NULL
2450	3	234	1960	22000.00	20000.00
2451	3	155	1960	22000.00	20000.00

LESS_RICH_EMP
EMP_NO	DEPT_NO	PRO_NO	EMP_BDATE	EMP_SAL	EMP_BONUS
2452	1	155	NULL	15000.00	14000.00
2444	2	177	1970	17000.00	16000.00
2446	2	155	1970	17000.00	16000.00
2450	3	234	1960	22000.00	20000.00
2451	3	155	1960	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 после выполнения этой операции?

Пусть в базовой таблице S определен внешний ключ (a, b, c), где a, b и c – имена столбцов определяемой таблицы. Пусть для этого внешнего ключа специфицирован способ соответствия PARTIAL. Будем считать, что все столбцы целого типа и могут содержать неопределенное значение. Предположим, что внешний ключ ссылается на первичный ключ таблицы T, состоящий из столбцов с такими же именами. Пусть в таблице S присутствуют строки s1, s2 и s3 со значениями внешнего ключа <NULL, NULL, 5>, <25, 1, NULL> и <25, 1, 5> соответственно. Пусть в таблице T присутствуют строки t1, t2 со значениями первичного ключа <25, 1, 5> и <25, 2, 5> соответственно. Что произойдет при удалении из таблицы T строки t1, если при определении внешнего ключа (a, b, c) таблицы S указано ссылочное действие ON DELETE SET NULL?

Пусть имеются четыре мультимножества M1, M2, M3 и M4, содержащие общий элемента a, причем в мультимножестве M1 содержится n дубликатов a, в мультимножестве M2 – m дубликатов a, в мультимножестве M3 – k дубликатов, а в мультимножестве M4– p дубликатов. Сколько дубликатов элемента a будет содержаться в результате операции (M1 INTERSECT M2) MINUS (M3 UNION M4)?