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

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

Номер 1

В каких из перечисленных ниже случаях значением столбца по умолчанию является NULL?

Ответ:

(1) в разделе DEFAULT определения столбца указано NULL

(2) в определении столбца отсутствует раздел DEFAULT, но столбец определяется на домене, в определении которого присутствует раздел DEFAULT NULL

(3) в определении столбца отсутствует раздел DEFAULT, и столбец определяется на базовом типе данных или на домене, в определении которого отсутствует раздел DEFAULT

Номер 2

Пусть при определении базовой таблицы указано ограничение UNIQUE (a, b, c), где a, b и c – составляют полный список имен столбцов определяемой таблицы. Будем считать, что все столбцы целого типа и могут содержать неопределенное значение. Какие из показанных ниже пар строк смогут находиться в теле таблицы?

Ответ:

(1) <NULL, NULL, 5>, <5, NULL, NULL>

(2) <NULL, NULL, NULL>, <5, NULL, NULL>

(3) <NULL, NULL, 5>, <NULL, NULL, 5>

Номер 3

Пусть в базовой таблице S определен внешний ключ (a, b, c), где a, b и c – имена столбцов определяемой таблицы. Будем считать, что все столбцы целого типа и могут содержать неопределенное значение. Предположим, что внешний ключ ссылается на первичный ключ таблицы T, состоящий из столбцов с такими же именами. При какой спецификации способа соответствия значение внешнего ключа <NULL, 1, 10> соответствует значению первичного ключа <25, 1, 10>?

Ответ:

(1) SIMPLE

(2) FULL

(3) PARTIAL

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

Номер 1

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

Ответ:

(1) операция удаления строки из таблицы T не выполнится

(2) строка из таблицы T будет удалена, а значением внешнего ключа в строках s1, s2 и s3 таблицы S станет <NULL, NULL, NULL>

(3) строка из таблицы T будет удалена, а значениями внешнего ключа в строках s1, s2 и s3 таблицы S станут <NULL, NULL, 5>, <25, 1, NULL> и <NULL, NULL, NULL> соответственно

Номер 2

Предположим, что в таблице объявлен возможный ключ, состоящий из n столбцов, для m из которых (m ≤ n) при определении столбца отсутствовала спецификация NOT NULL. В скольких строках таблицы столбцы возможного ключа смогут содержать неопределенные значения?

Ответ:

(1) m + n

(2) m * n

(3) 2^m – 1

Номер 3

Предположим, что проекты финансируются отделами. Каждый отдел финансирует не более одного проекта, один проект может финансироваться несколькими отделами, и общая сумма финансирования проекта не должна составлять менее 1000000.00 руб. Выполнение каких из приведенных ниже операторов изменит определения таблиц DEPT и PRO требуемым образом?

Ответ:

(1) ALTER TABLE DEPT ADD DEPT_PRO_INVEST NUMERIC (10, 2) DEFAULT NULL; ALTER TABLE DEPT ADD DEPT_PRO PRO_NO DEFAULT NULL; REFERENCES PRO ON DELETE SET NULL; ALTER TABLE DEPT ADD CONSTRAINT PRO_INVEST_VALUE CHECK (IF (DEPT_PRO IS NOT NULL) THEN (SELECT SUM (D.DEPT_PRO_INVEST) FROM DEPT D WHERE DEPT_PRO = D.DEPT_PRO) >= 1000000.00);

(2) ALTER TABLE DEPT ADD DEPT_PRO_INVEST NUMERIC (10, 2) DEFAULT NULL; ALTER TABLE PRO ADD PRO_DEPT DEPT_NO NO NULL; REFERENCES DEPT ON DELETE CASCADES; CHECK (SELECT SUM (DEPT_PRO_INVEST) FROM DEPT WHERE DEPT_NO = VALUE) >= 1000000.00);

(3) ALTER TABLE PRO ADD PRO_DEPT_INVEST NUMERIC (10, 2) DEFAULT NULL; ALTER TABLE PRO ADD PRO_DEPT DEPT_NO NO NULL; REFERENCES PRO ON DELETE CASCADES; CHECK (SELECT SUM (PRO_DEPT_INVEST) FROM PRO WHERE PRO_DEPT = VALUE) >= 1000000.00);

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

Номер 1

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

Ответ:

(1) ALTER TABLE EMP ADD EMP_ADVISER EMP_NO DEFAULT NULL REFERENCES EMP ON DELETE SET NULL CHECK (VALUE < 6);

(2) ALTER TABLE EMP ADD EMP_ADVISER EMP_NO NO NULL REFERENCES EMP ON DELETE CASCADE;

(3) ALTER TABLE EMP ADD EMP_ADVISER EMP_NO DEFAULT NULL REFERENCES EMP ON DELETE SET NULL; CHECK (IF (VALUE IS NOT NULL) THEN (SELECT COUNT (*) FROM EMP E WHERE E.EMP_ADVISER = VALUE) < 6);

Номер 2

Пусть в базовой таблице 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?

Ответ:

(1) операция удаления строки из таблицы T не выполнится

(2) строка из таблицы T будет удалена, а значением внешнего ключа в строках s1, s2 и s3 таблицы S станет <NULL, NULL, NULL>

(3) строка из таблицы T будет удалена, а значениями внешнего ключа в строках s1, s2 и s3 таблицы S станут <NULL, NULL, 5>, <NULL, NULL, NULL> и <NULL, NULL, NULL> соответственно

Номер 3

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

Ответ:

(1) INITIALLY IMMEDIATE NOT DEFERRABLE

(2) INITIALLY IMMEDIATE DEFERRABLE

(3) INITIALLY DEFERRED

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

Номер 1

Предположим, что служащие, участвующие в проектах, могут получать дополнительную премию, равную средней основной заработной плате участников соответствующего проекта. Выполнение каких из приведенных ниже операторов изменит определение таблицы EMP требуемым образом?

Ответ:

(1) ALTER TABLE EMP ADD EMP_PRO_BONUS SALARY DEFAULT NULL; ALTER TABLE EMP ADD CONSTRAINT PRO_BONUS CHECK (IF (EMP_PRO_BONUS IS NOT NULL) THEN PRO_NO IS NOT NULL) ALTER TABLE DEPT ADD CONSTRAINT MORE_TOTAL_INCOME CHECK (DEPT_TOTAL_SAL >= (SELECT SUM(EMP_SAL + COALESCE(EMP_BONUS,0) + COALESCE(EMP_PRO_BONUS,0)) FROM EMP WHERE EMP.DEPT_NO = DEPT_NO));

(2) ALTER TABLE EMP ADD EMP_PRO_BONUS SALARY DEFAULT NULL; ALTER TABLE EMP DROP CONSTRAINT TOTAL_INCOME; ALTER TABLE DEPT ADD CONSTRAINT TOTAL_INCOME CHECK (DEPT_TOTAL_SAL >= (SELECT SUM(EMP_SAL + COALESCE(EMP_BONUS,0) + COALESCE(EMP_PRO_BONUS,0)) FROM EMP WHERE EMP.DEPT_NO = DEPT_NO));

(3) ALTER TABLE EMP ADD EMP_PRO_BONUS SALARY DEFAULT NULL; ALTER TABLE EMP ADD CONSTRAINT PRO_BONUS_VALUE CHECK (IF (EMP_PRO_BONUS IS NOT NULL) THEN EMP_PRO_BONUS = (SELECT AVG (E.EMP_SAL) FROM EMP E WHERE PRO_NO = E.PRO_NO)); ALTER TABLE EMP ADD CONSTRAINT PRO_BONUS CHECK (IF (EMP_PRO_BONUS IS NOT NULL) THEN PRO_NO IS NOT NULL) ALTER TABLE DEPT ADD CONSTRAINT MORE_TOTAL_INCOME CHECK (DEPT_TOTAL_SAL >= (SELECT SUM(EMP_SAL + COALESCE(EMP_BONUS,0) + COALESCE(EMP_PRO_BONUS,0)) FROM EMP WHERE EMP.DEPT_NO = DEPT_NO));

Номер 2

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

Ответ:

(1) INITIALLY IMMEDIATE NOT DEFERRABLE

(2) INITIALLY IMMEDIATE DEFERRABLE

(3) INITIALLY DEFERRED

Номер 3

Каким будет значение по умолчанию столбца, определенного без указания собственного значения по умолчанию и без ограничения NOT NULL, после отмены определения домена, на котором данный столбец был определен, если у домена было задано значение по умолчанию?

Ответ:

(1) NULL

(2) у столбца не будет значения по умолчанию

(3) останется значение по умолчанию домена

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

Номер 1

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

Ответ:

(1) INITIALLY IMMEDIATE NOT DEFERRABLE

(2) INITIALLY IMMEDIATE DEFERRABLE

(3) INITIALLY DEFERRED

Номер 2

Пусть в базовой таблице 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?

Ответ:

(1) операция удаления строки из таблицы T не выполнится

(2) строка из таблицы T будет удалена, а значением внешнего ключа в строках s1, s2 и s2 таблицы S станет <4, 44, 444>

(3) строка из таблицы T будет удалена, а значениями внешнего ключа в строках s1, s2 и s2 таблицы S станут <NULL, NULL, 444>, <4, 44, NULL> и <4, 44, 444> соответственно

Номер 3

Предположим, что в таблице объявлен возможный ключ со спецификацией MATCH PARTIAL, состоящий из n столбцов, для m из которых (m ≤ n) при определении столбца отсутствовала спецификация NOT NULL. На сколько строк таблицы, на которую ведет ссылка, может ссылаться одна строка ссылающейся таблицы?

Ответ:

(1) m + n

(2) m * n

(3) 2^m

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