ответы к зачёту по Базам Данных (2009), страница 11

Логические типы (TRUE, FALSE, UNKNOWN). В стандарте UNKNOWN и NULL не различаются.

Точные числовые типы (SMALLINT, INTEGER). С дробной частью – NUMERIC (p,s), DECIMAL(p,s). P – число десятичных цифр, s – шкала (число десятичных цифр в дробной части). Для p,s возможны значения по умолчанию. Для NUMERIC р – строгое, для ), DECIMAL – максимально возможное. Пример: 1234,5 – в NUMERIC недопустимо, в ), DECIMAL допустимо.

Приближенные числовые типы (вещественные типы REAL, DOUBLE PRECISION, FLOAT(p)). Параметр р – число значимых цифр в мантиссе.

Символьные строки. CHAR(х) – строки фиксированной длины х (по умолчанию х = 1), VARCHAR – произвольной длины, но не более х (по умолчанию х = 1). CLOB (character large object) – этот тип данных предназначен для определения столбцов, хранящих большие и разные по размеру группы символов. При определении столбца задается спецификация CLOB (z), где z задает максимальный размер соответствующей группы символов. Максимально возможное значение параметра z определяется в реализации, но, очевидно, что оно должно быть существенно больше максимально возможного значения параметра x, присутствующего в типах CHAR и CHAR VARYING (z >> x; если длина меньше х, то справа добавляются пробелы).

Битовые строки (BIT, BIT VARYING(х), BLOB(x)).

Дата и время. DATE : “YYYY-MM-DD”, TIME “HH:MM-SS:FF” (число символов в FF определяется параметром р), TIMESTAMP “YYYY-MM-DD HH:MM-SS:FF”(по умолчанию р = 6, для секунд допустим диапазон 0..61 ), TIME WITH TIME ZONE – +HH:MM, -HH:MM по Гринвичу. Стандартные функции: CURRENT_DATE(_TIME, _STAMP).

Временные интервалы ( INTERVAL ‘start(p)[TO end (q)]’). Р—точность (по умолчанию p = 2), разница не привязана к началу/концу интервала.

( прим. остальных типов в лекциях Маши нет)

Преобразование типов. В SQL поддерживаются явные и неявные преобразования значений одного типа к значениям другого. Неявные (интуитивные) преобразования типов не всегда удобны, недостаточно гибки и иногда могут вызывать ошибки. Поэтому, как показывает предыдущий подраздел, число допустимых неявных преобразований типов в SQL весьма ограничено. Однако в SQL существует специальный оператор CAST, с помощью которого можно явно преобразовывать типы или домены в более широких пределах допускаемых преобразований. Конструкция имеет следующий синтаксис:

CAST ({scalar-expression | NULL } AS

{data_type | domain_name})

1. Средства работы с доменами в SQL.

Домен является долговременно хранимым, именованным объектом схемы базы данных. Домены можно создавать (определять), изменять (изменять определения) и ликвидировать (отменять определение). Имена доменов можно использовать при определении столбцов таблиц. Можно считать, что в SQL  определение домена представляет собой вынесенное за пределы определения индивидуальной таблицы «родовое» определение столбца, которое можно использовать для определения различных реальных столбцов реальных базовых таблиц.

Определение домена.

Для определения домена в SQL используется оператор CREATE DOMAIN. Общий синтаксис этого оператора следующий:⁸⁸⁾

domain_definition ::= CREATE DOMAIN domain_name [AS] data_type

[ default_definition ]

[ domain_constraint_definition_list ]

Здесь domain_name задает имя создаваемого домена⁸⁹⁾, data_type есть спецификация определяющего типа данных. В необязательных разделах default_definition и domain_constraint_definition_list специфицируются значение домена по умолчанию⁹⁰⁾ и набор ограничений целостности, которые будут применяться к любому столбцу, определенному на этом домене.

Раздел default_definition имеет вид

DEFAULT { literal | niladic_function | NULL }⁹¹⁾

Здесь literal представляет любое допустимое литеральное значение определяющего типа домена, NULL обозначает неопределенное значение, а niladic_function может задаваться в одной из следующих форм:

USER

CURRENT_USER

SESSION_USER

SYSTEM_USER

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP⁹²⁾

Если в операторе CREATE DOMAIN значение по умолчанию не специфицируется, считается, что такого значения нет. Однако позже к определению домена можно добавить раздел значения по умолчанию с помощью оператора ALTER DOMAIN. Кроме того, этот оператор позволяет удалить раздел значения по умолчанию из существующего определения домена.

Элемент списка domain_constraint_definition_list имеет вид

[CONSTRAINT constraint_name]

CHECK (conditional_expression)

Необязательный раздел CONSTRAINT constraint_name позволяет определить имя нового ограничения целостности. Если явное указание имени отсутствует, ограничению назначается имя, автоматически генерируемое системой. Что касается вида условного выражения, служащего собственно ограничением целостности, то в стандарте запрещается лишь прямое или косвенное использование в нем домена, в определение которого входит данное условное выражение.⁹³⁾ Однако наиболее естественным (и наиболее распространенным) видом ограничения домена является следующий:

CHECK (VALUE IN (list_of_valid_values))

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

Изменение домена.

Для изменения характеристик ранее определенного домена используется оператор SQL  ALTER DOMAIN. Синтаксис этого оператора выглядит следующим образом:

domain_alternation ::=

ALTER DOMAIN domain_name domain_alternation_action

domain_alternation_action ::=

domain_default_alternation_action

| domain_constraint_alternation_action

Как видно из синтаксических правил, при изменении определения домена можно выполнить действие по изменению раздела значения по умолчанию либо изменить ограничение домена. Для первого варианта действует следующий синтаксис:

domain_default_alternation_action ::=

SET default_definition

| DROP DEFAULT

В случае установки нового значения по умолчанию (SET) это значение автоматически применяется ко всем столбцам, определенным на данном домене. Более точно, это значение становится новым значением по умолчанию. Операция не оказывает влияния на состояние существующих строк таблиц базы данных. В случае отмены раздела значения по умолчанию в определении домена (DROP) существовашее значение домена по умолчанию становится значением по умолчанию каждого столбца, который определен на данном домене и для которого не специфицировано собственное значение по умолчанию.

Действие по изменению ограничения домена определяется следующим синтаксисом:

domain_constraint_alternation_action ::=

ADD domain_constraint_definition

| DROP CONSTRAINT constraint_name

Действие по добавлению нового определения ограничения домена (ADD) приводит к тому, что новое условие добавляется через AND к существующему ограничению домена. Если к моменту выполнения соответствующего оператора ALTER DOMAIN существуют столбцы некоторых таблиц, текущие значения которых противоречат новому ограничению, то СУБД должна отвергнуть этот оператор ALTER DOMAIN. Действие по отмене ограничения домена (DROP) приводит к исчезновению соответствующей части общего ограничения соответствующего домена, что, естественно, не влияет на существующие значения столбцов имеющихся таблиц.

Отмена домена.

Чтобы отменить ранее созданное определение домена, нужно воспользоваться оператором DROP DOMAIN в следующем синтаксисе:

DROP DOMAIN domain_name {RESTRICT | CASCADES}

1. Средства определения, изменения и отмены определения базовых таблиц в SQL.

Определение базовой таблицы.

Определение столбца: возможно использование определенного ранее домена.

Определение ограничений целостности: для столбца, для таблицы, для всей базы. Ограничения первичного ключа, ограничения внешнего ключа.

Пример:

(1) CREATE TABLE EMP (

(2) EMP_NO EMP_NO PRIMARY KEY,

(3) EMP_NAME VARCHAR(20) DEFAULT 'Incognito' NOT NULL,

(4) EMP_BDATE DATE DEFAULT NULL CHECK (

VALUE >= DATE '1917-10-24'),

(5) EMP_SAL SALARY,

(6) DEPT_NO DEPT_NO DEFAULT NULL REFERENCES

DEPT ON DELETE SET NULL,

(7) PRO_NO PRO_NO DEFAULT NULL,

(8) FOREIGN KEY PRO_NO REFERENCES PRO (PRO_NO)

ON DELETE SET NULL,

(9) CONSTRAINT PRO_EMP_NO CHECK

((SELECT COUNT (*) FROM EMP E

WHERE E.PRO_NO = PRO_NO) <= 50));

1. Базовые средства манипулирования данными в языке SQL.

К базовым средствам манипулирования относятся UPDATE, DELETE и INSERT.

INSERT для вставки строк в существующие таблицы:

Общий синтаксис оператора INSERT выглядит следующим образом:

INSERT INTO table_name

{ [ (column_commalist) ] query_expression

| DEFAULT VALUES

где query_expression – может быть любым выражением-запросом.

Так например возможна вставкв всех строк какой-то таблицы:

INSERT INTO EMP (EMP_NO, EMP_NAME, EMP_BDATE) TABLE EMP_TEMP;

Вставка одной строки (используется конструктор строки):

INSERT INTO EMP

ROW (2445, 'Brown', '1985-04-08', 16500.00, 630, 772);

Оператор UPDATE для модификации существующих строк в существующих таблицах

Общий синтаксис оператора UPDATE выглядит следующим образом:

UPDATE table_name SET update_assignment_commalist

WHERE conditional_expression

update_assignment ::= column_name =

{ value_expression | DEFAULT | NULL }

Примеры:

Простой:

UPDATE EMP SET DEPT_NO = 632, EMP_SAL = EMP_SAL + 1000.00

WHERE PRO_NO = 772;

Сложный:

UPDATE EMP SET EMP_SAL = (SELECT AVG (EMP1_SAL)

FROM EMP EMP1

WHERE EMP.DEPT_NO = EMP1.DEPT_NO)

+ 1000.00, PRO_NO = NULL

WHERE (SELECT EMP1.EMP_SAL

FROM EMP EMP1, DEPT

WHERE EMP.DEPT_NO = DEPT.DEPT_NO

AND DEPT_MNG = EMP1.EMP_NO AND) > 30000.00;

Оператор DELETE для удаления строк в существующих таблицах

Общий синтаксис оператора DELETE выглядит следующим образом:

DELETE FROM table_name

WHERE conditional_expression

Пример:

Простой:

DELETE FROM EMP WHERE PRO_NO = 772;

Сложный:

DELETE FROM EMP WHERE EMP_SAL >

(SELECT EMP1.EMP_SAL

FROM EMP EMP1, DEPT

WHERE EMP.DEPT_NO = DEPT.DEPT_NO

AND DEPT.DEPT.MNG = EMP1.EMP_NO);

1. Понятие триггера. Механизм триггеров в SQL. Типы триггеров и их выполнение.

Триггером называется хранимая в базе данных процедура, автоматически вызываемая СУБД при возникновении соответствующих условий.

В языке обеспечиваются возможности определения триггеров, которые вызываются («срабатывают») при вставке одной или нескольких строк в указанную таблицу, при модификации одной или нескольких строк в указанной таблице или при удалении одной или нескольких строк из указанной таблицы.

Таблица, с которой связывается определение триггера, называется предметной таблицей (subject table), а оператор SQL, выполнение которого приводит к срабатыванию триггера, мы будем называть инициирующим (triggering SQL statement).

Основными областями использования механизма триггеров являются:

• Журнализация и аудит. С помощью триггеров можно отслеживать изменения таблиц, для которых требуется поддержка повышенного уровня безопасности. Данные об изменении таблиц могут сохраняться в других таблицах и включать, например, идентификатор пользователя, от имени которого выполнялась операция обновления; временную метку операции обновления; сами обновляемые данные и т. д.

• Согласование и очистка данных. С любым простым оператором SQL, обновляющим некоторую таблицу, можно связать триггеры, производящие соответствующие обновления других таблиц. Например, с операцией вставки новой строки в таблицу EMP (прием на работу нового служащего) можно было связать триггер, модифицирующий значения столбцов DEPT_EMP_NO и DEPT_TOTAL_SAL¹⁶²⁾ строки таблицы DEPT со значением столбца DEPT_NO, которое соответствует номеру отдела нового служащего.

• Операции, не связанные с изменением базы данных. В триггерах могут выполняться не только операции обновления базы данных. Стандарт SQL позволяет определять хранимые процедуры (которые могут вызываться из триггеров), посылающие электронную почту, печатающие документы и т. д.

Типы триггеров и их выполнение:

Триггеры BEFORE и AFTER

Если в определении триггера указано ключевое слово BEFORE, то триггер будет срабатывать непосредственно до выполнения операции обновления базовой таблицы соответствующим инициирующим оператором SQL. При задании ключевого слова AFTER триггер будет вызываться немедленно после выполнения инициирующего оператора.

Триггеры INSERT, UPDATE и DELETE

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

Триггеры ROW и STATEMENT

Если в определении триггера присутствует конструкция FOR EACH ROW, то триггер будет вызываться для каждой строки предметной таблицы, обновляемой инициирующим SQL-оператором. Если же задано FOR EACH STATEMENT (или явная спецификация FOR EACH отсутствует), то триггер сработает один раз на всем протяжении процесса выполнения инициирующего SQL-оператора.

Раздел WHEN

Включение в определение триггера раздела WHEN с соответствующим условным выражением позволяет более точно специфицировать условие применимости триггера. Вычисление условного выражения производится над строками предметной таблицы, и триггер срабатывает только в том случае, когда значением условного выражения является true. Понятно, что виды и интерпретация логических выражений, допускаемых в разделе WHEN, различаются у триггеров с FOR EACH ROW и у триггеров с FOR EACH STATEMENT. В первом случае условное выражение вычисляется для одной строки, которая должна быть обновлена инициирующим SQL-оператором. Во втором – условное выражение вычисляется для всей предметной таблицы целиком и, по всей видимости, должно базироваться на «кванторных» предикатах. Следует также понимать, что вычисление условия раздела WHEN данного триггера производится только в том случае, если произошло событие срабатывания триггера.