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

Упражнение 1:

Номер 1

Чему соответствует имя псевдонима, вводимого в разделе REFERENCING определения триггера над таблицей T конструкцией NEW TABLE?

Ответ:

(1) Содержимому таблицы T после выполнения операции, инициирующей триггер

(2) Набору строк, которые были обновлены в теле таблицы T

(3) Набору строк, удаленных из тела таблицы T

Номер 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);

Ответ:

(1)

EMP
`EMP_NO`	`DEPT_NO`	`PRO_NO`	`EMP_BDATE`	`EMP_SAL`	`EMP_BONUS`
`2440`	`1`	`177`	`1950`	`15000.00`	`13000.00`
`2441`	`1`	`155`	`1955`	`16000.00`	`14000.00`
`2442`	`1`	`208`	`1960`	`14000.00`	`NULL`
`2443`	`1`	`NULL`	`1960`	`16000.00`	`13000.00`
`2452`	`1`	`155`	`NULL`	`15000.00`	`14000.00`
`2453`	`1`	`234`	`NULL`	`17000.00`	`NULL`
`2444`	`2`	`177`	`1970`	`17000.00`	`16000.00`
`2445`	`2`	`208`	`1950`	`16000.00`	`15000.00`
`2446`	`2`	`155`	`1970`	`17000.00`	`16000.00`
`2447`	`2`	`177`	`1960`	`20000.00`	`NULL`
`2448`	`3`	`101`	`1955`	`18000.00`	`17000.00`
`2449`	`3`	`NULL`	`1950`	`18000.00`	`17000.00`
`2450`	`3`	`234`	`1960`	`22000.00`	`20000.00`
`2451`	`3`	`155`	`1960`	`16500.00`	`20000.00`
`2454`	`NULL`	`208`	`1950`	`13000.00`	`12000.00`
`2455`	`NULL`	`101`	`1950`	`14000.00`	`22000.00`
`2456`	`NULL`	`234`	`NULL`	`19000.00`	`18000.00`

(2)

EMP
`EMP_NO`	`DEPT_NO`	`PRO_NO`	`EMP_BDATE`	`EMP_SAL`	`EMP_BONUS`
`2440`	`1`	`177`	`1950`	`15000.00`	`13000.00`
`2441`	`1`	`155`	`1955`	`17500.00`	`14000.00`
`2442`	`1`	`208`	`1960`	`14000.00`	`NULL`
`2443`	`1`	`NULL`	`1960`	`16000.00`	`13000.00`
`2452`	`1`	`155`	`NULL`	`17500.00`	`14000.00`
`2453`	`1`	`234`	`NULL`	`17000.00`	`NULL`
`2444`	`2`	`177`	`1970`	`17000.00`	`16000.00`
`2445`	`2`	`208`	`1950`	`16000.00`	`15000.00`
`2446`	`2`	`155`	`1970`	`16500.00`	`16000.00`
`2447`	`2`	`177`	`1960`	`20000.00`	`NULL`
`2448`	`3`	`101`	`1955`	`18000.00`	`17000.00`
`2449`	`3`	`NULL`	`1950`	`18000.00`	`17000.00`
`2450`	`3`	`234`	`1960`	`22000.00`	`20000.00`
`2451`	`3`	`155`	`1960`	`18500.00`	`20000.00`
`2454`	`NULL`	`208`	`1950`	`13000.00`	`12000.00`
`2455`	`NULL`	`101`	`1950`	`14000.00`	`22000.00`

(3)

EMP
`EMP_NO`	`DEPT_NO`	`PRO_NO`	`EMP_BDATE`	`EMP_SAL`	`EMP_BONUS`
`2451`	`3`	`155`	`1960`	`18500.00`	`20000.00`

Номер 3

Чему соответствует имя псевдонима, вводимого в разделе REFERENCING определения триггера над таблицей T конструкцией OLD TABLE?

Ответ:

(1) Содержимому таблицы T после выполнения операции, инициирующей триггер

(2) Набору строк в исходном теле таблицы T, которые подвергались обновлению

(3) Набору строк, которые были обновлены в теле таблицы T

Упражнение 2:

Номер 1

Пусть V – это некоторое представление, допускающее операции обновления и определенное без раздела WITH CHECK OPTION. Возможны ли ситуации, когда система может отвергнуть какую-либо операцию обновления V?

Ответ:

(1) нет

(2) да, когда она противоречит условию выборки запроса, на котором определено представление V

(3) да, если представление V определено над некоторым представлением V1, которое определено с разделом WITH CHECK OPTION, и операция обновления противоречит условию выборки V1

Номер 2

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

Ответ:

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

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

(3) 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

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

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_EMPEMP_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_EMPEMP_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 после выполнения этой операции?

Ответ:

(1)

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`

(2)

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`	`25000.00`	`4000.00`
`2453`	`1`	`234`	`NULL`	`17000.00`	`NULL`
`2444`	`2`	`177`	`1970`	`27000.00`	`6000.00`
`2446`	`2`	`155`	`1970`	`27000.00`	`6000.00`
`2447`	`2`	`177`	`1960`	`20000.00`	`NULL`
`2450`	`3`	`234`	`1960`	`32000.00`	`10000.00`
`2451`	`3`	`155`	`1960`	`32000.00`	`10000.00`

LESS_RICH_EMP
`EMP_NO`	`DEPT_NO`	`PRO_NO`	`EMP_BDATE`	`EMP_SAL`	`EMP_BONUS`
`2452`	`1`	`155`	`NULL`	`25000.00`	`4000.00`
`2444`	`2`	`177`	`1970`	`27000.00`	`6000.00`
`2446`	`2`	`155`	`1970`	`27000.00`	`6000.00`
`2450`	`3`	`234`	`1960`	`32000.00`	`10000.00`
`2451`	`3`	`155`	`1960`	`32000.00`	`10000.00`

(3)

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`	`25000.00`	`4000.00`
`2444`	`2`	`177`	`1970`	`27000.00`	`6000.00`
`2445`	`2`	`208`	`1950`	`16000.00`	`15000.00`
`2446`	`2`	`155`	`1970`	`27000.00`	`6000.00`
`2448`	`3`	`101`	`1955`	`18000.00`	`17000.00`
`2449`	`3`	`NULL`	`1950`	`18000.00`	`17000.00`
`2450`	`3`	`234`	`1960`	`32000.00`	`10000.00`
`2451`	`3`	`155`	`1960`	`32000.00`	`10000.00`

LESS_RICH_EMP
`EMP_NO`	`DEPT_NO`	`PRO_NO`	`EMP_BDATE`	`EMP_SAL`	`EMP_BONUS`
`2452`	`1`	`155`	`NULL`	`25000.00`	`4000.00`
`2445`	`2`	`208`	`1950`	`16000.00`	`15000.00`
`2448`	`3`	`101`	`1955`	`18000.00`	`17000.00`
`2449`	`3`	`NULL`	`1950`	`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 – соответствующие “критические” средние значения.

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

Ответ:

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

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

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

Номер 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.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_DEPTEMP_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_EMPEMP_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 после выполнения этой операции?

Ответ:

(1)

RICH_DEPT
`EMP_NO`	`DEPT_NO`	`PRO_NO`	`EMP_BDATE`	`EMP_SAL`	`EMP_BONUS`
`2440`	`1`	`177`	`1950`	`15000.00`	`13000.00`
`2441`	`1`	`155`	`1955`	`16000.00`	`14000.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`
`2447`	`2`	`177`	`1960`	`10000.00`	`NULL`
`2448`	`3`	`101`	`1955`	`18000.00`	`17000.00`
`2450`	`3`	`234`	`1960`	`12000.00`	`20000.00`
`2451`	`3`	`155`	`1960`	`12000.00`	`20000.00`

RICH_EMP
`EMP_NO`	`DEPT_NO`	`PRO_NO`	`EMP_BDATE`	`EMP_SAL`	`EMP_BONUS`
`2441`	`1`	`155`	`1955`	`16000.00`	`14000.00`
`2442`	`1`	`208`	`1960`	`14000.00`	`NULL`
`2444`	`2`	`177`	`1970`	`17000.00`	`16000.00`
`2445`	`2`	`208`	`1950`	`16000.00`	`15000.00`
`2448`	`3`	`101`	`1955`	`18000.00`	`17000.00`

(2)

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`	`10000.00`	`NULL`
`2448`	`3`	`101`	`1955`	`18000.00`	`17000.00`
`2450`	`3`	`234`	`1960`	`12000.00`	`20000.00`
`2451`	`3`	`155`	`1960`	`12000.00`	`20000.00`

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

(3)

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`

Номер 3

Пусть V – это некоторое представление, допускающее операции обновления и определенное c разделом WITH GLOBAL CHECK OPTION. Предположим, что представление V определено над некоторым представлением V1, которое определено без раздела WITH CHECK OPTION. В каких из перечисленных ситуаций система не должна отвергать данную операцию обновления V?

Ответ:

(1) когда она не противоречит условию выборки запроса, на котором определено представление V

(2) когда она не противоречит условию выборки запроса, на котором определено представление V1

(3) когда она не противоречит условию выборки запроса, на котором определено представление V, и не противоречит условию выборки запроса, на котором определено представление V1, как если бы V2 было определено с разделом WITH GLOBAL CHECK OPTION

Упражнение 4:

Номер 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);

Ответ:

(1)

EMP_MNGS
`EMP_NO`	`DEPT_MNG`	`PRO_MNG`
`2440`	`2448`	`2440`
`2441`	`2448`	`2446`
`2442`	`2448`	`2442`
`2452`	`2448`	`2446`
`2453`	`2448`	`2456`
`2444`	`2444`	`2440`
`2445`	`2444`	`2442`
`2446`	`2444`	`2446`
`2447`	`2444`	`2440`
`2448`	`2456`	`2448`
`2450`	`2456`	`2446`

(2)

EMP_MNGS
`EMP_NO`	`DEPT_MNG`	`PRO_MNG`
`2440`	`2448`	`2440`
`2441`	`2448`	`2446`
`2442`	`2448`	`2442`
`2443`	`2448`	`NULL`
`2452`	`2448`	`2446`
`2453`	`2448`	`2456`
`2444`	`2444`	`2440`
`2445`	`2444`	`2442`
`2446`	`2444`	`2446`
`2447`	`2444`	`2440`
`2448`	`2456`	`2448`
`2449`	`2456`	`NULL`
`2450`	`2456`	`2446`
`2454`	`NULL`	`2442`
`2455`	`NULL`	`2448`
`2456`	`NULL`	`2456`

(3)

EMP_MNGS
`EMP_NO`	`DEPT_MNG`	`PRO_MNG`
`2440`	`2448`	`2440`
`2441`	`2448`	`2446`
`2442`	`2448`	`2442`
`2443`	`2448`	`NULL`
`2452`	`2448`	`2446`
`2453`	`2448`	`2456`
`2444`	`2444`	`2440`
`2445`	`2444`	`2442`
`2446`	`2444`	`2446`
`2447`	`2444`	`2440`
`2454`	`NULL`	`2442`
`2455`	`NULL`	`2448`
`2456`	`NULL`	`2456`

Номер 2

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

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)

Ответ:

(1)

EMP
`EMP_NO`	`DEPT_NO`	`PRO_NO`	`EMP_BDATE`	`EMP_SAL`	`EMP_BONUS`
`2441`	`1`	`155`	`1955`	`16000.00`	`14000.00`
`2442`	`1`	`208`	`1960`	`14000.00`	`NULL`
`2443`	`1`	`NULL`	`1960`	`16000.00`	`13000.00`
`2452`	`1`	`155`	`NULL`	`15000.00`	`14000.00`
`2453`	`1`	`234`	`NULL`	`17000.00`	`NULL`
`2444`	`2`	`177`	`1970`	`17000.00`	`16000.00`
`2445`	`2`	`208`	`1950`	`16000.00`	`15000.00`
`2446`	`2`	`155`	`1970`	`17000.00`	`16000.00`
`2447`	`2`	`177`	`1960`	`20000.00`	`NULL`
`2449`	`3`	`NULL`	`1950`	`18000.00`	`17000.00`
`2450`	`3`	`234`	`1960`	`22000.00`	`20000.00`
`2451`	`3`	`155`	`1960`	`22000.00`	`20000.00`
`2454`	`NULL`	`208`	`1950`	`13000.00`	`12000.00`
`2455`	`NULL`	`101`	`1950`	`14000.00`	`22000.00`
`2456`	`NULL`	`234`	`NULL`	`19000.00`	`18000.00`

(2)

EMP
`EMP_NO`	`DEPT_NO`	`PRO_NO`	`EMP_BDATE`	`EMP_SAL`	`EMP_BONUS`
`2441`	`1`	`155`	`1955`	`16000.00`	`14000.00`
`2442`	`1`	`208`	`1960`	`14000.00`	`NULL`
`2443`	`1`	`NULL`	`1960`	`16000.00`	`13000.00`
`2452`	`1`	`155`	`NULL`	`15000.00`	`14000.00`
`2453`	`1`	`234`	`NULL`	`17000.00`	`NULL`
`2444`	`2`	`177`	`1970`	`17000.00`	`16000.00`
`2445`	`2`	`208`	`1950`	`16000.00`	`15000.00`
`2446`	`2`	`155`	`1970`	`17000.00`	`16000.00`
`2447`	`2`	`177`	`1960`	`20000.00`	`NULL`
`2449`	`3`	`NULL`	`1950`	`18000.00`	`17000.00`
`2450`	`3`	`234`	`1960`	`22000.00`	`20000.00`
`2451`	`3`	`155`	`1960`	`22000.00`	`20000.00`

(3)

EMP
`EMP_NO`	`DEPT_NO`	`PRO_NO`	`EMP_BDATE`	`EMP_SAL`	`EMP_BONUS`
`2441`	`1`	`155`	`1955`	`16000.00`	`14000.00`
`2442`	`1`	`208`	`1960`	`14000.00`	`NULL`
`2443`	`1`	`NULL`	`1960`	`16000.00`	`13000.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`
`2449`	`3`	`NULL`	`1950`	`18000.00`	`17000.00`
`2450`	`3`	`234`	`1960`	`22000.00`	`20000.00`
`2451`	`3`	`155`	`1960`	`22000.00`	`20000.00`
`2454`	`NULL`	`208`	`1950`	`13000.00`	`12000.00`
`2455`	`NULL`	`101`	`1950`	`14000.00`	`22000.00`
`2456`	`NULL`	`234`	`NULL`	`19000.00`	`18000.00`

Номер 3

Чему соответствует имя псевдонима, вводимого в разделе REFERENCING определения триггера над таблицей T конструкцией NEW ROW?

Ответ:

(1) Текущей строке из числа строк, появляющихся в таблице T в результате выполнения операции, которая инициирует триггер

(2) Набору строк в исходном теле таблицы T, которые подвергались обновлению

(3) Содержимому таблицы T после выполнения операции, инициирующей триггер

Упражнение 5:

Номер 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_EMPEMP_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_EMPEMP_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 после выполнения этой операции?

Ответ:

(1)

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`	`24000.00`	`NULL`
`2452`	`1`	`155`	`NULL`	`15000.00`	`14000.00`
`2444`	`2`	`177`	`1970`	`17000.00`	`16000.00`
`2445`	`2`	`208`	`1950`	`26000.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`	`24000.00`	`NULL`
`2445`	`2`	`208`	`1950`	`26000.00`	`15000.00`

(2)

LA_EMP
`EMP_NO`	`DEPT_NO`	`PRO_NO`	`EMP_BDATE`	`EMP_SAL`	`EMP_BONUS`
`2440`	`1`	`177`	`1950`	`15000.00`	`13000.00`
`2441`	`1`	`155`	`1955`	`16000.00`	`14000.00`
`2443`	`1`	`NULL`	`1960`	`16000.00`	`13000.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`
`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`
`2440`	`1`	`177`	`1950`	`15000.00`	`13000.00`
`2452`	`1`	`155`	`NULL`	`15000.00`	`14000.00`

(3)

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`

Номер 2

Пусть V – это некоторое представление, допускающее операции обновления и определенное c разделом WITH LOCAL CHECK OPTION. Предположим, что представление V определено над некоторым представлением V1, которое определено без раздела WITH CHECK OPTION. В каких из перечисленных ситуаций система может отвергнуть какую-либо операцию обновления V?

Ответ:

(1) когда она противоречит условию выборки запроса, на котором определено представление V

(2) когда она противоречит условию выборки запроса, на котором определено представление V1

(3) если представление V1 определено с разделом WITH CHECK OPTION над некоторым представлением V2, которое определено с разделом WITH CHECK OPTION, и операция обновления противоречит условию выборки V2

Номер 3

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

EMP_NO

EVENT_TS

NEW_SAL

NEW_BONUS

NUMBER_LESS

NUMBER_MORE

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

Ответ:

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

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

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

Введение в модель данных SQL - тест 7