Команды управления транзакциями

Существуют следующие команды, позволяющие управлять транзакциями базы данных: COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION.

№24 SELECT * .

Предложения INTO, INTO TEMP, FROM.

Выбрать все строки (нет предложения WHERE) из таблицы kadry,

взять в них все столбцы (вместо перечисления столбцов стоит *),

оставить только различные строки (ключевое слово UNIQUE) и по-

местить результат во временную таблицу (INTO TEMP) x, которая

будет при этом создана с такими же столбцами, что и у kadry.

---------------------------------------------------------------

SELECT UNIQUE * FROM kadry INTO TEMP x

---------------------------------------------------------------

Выбирать можно из нескольких таблиц. При этом берутся все воз-

можные комбинации строк из первой таблицы со второй. Предполо-

жим, что таблице tab1 6 строк а в tab2 - 7 строк. Результат ни-

жеприведенного примера - таблица, содержащая три столбца и

7*6=42 строки.

---------------------------------------------------------------

SELECT tab1.a-tab2.b, tab1.a, tab2.b FROM tab1, tab2

---------------------------------------------------------------

Мы сейчас не будем уточнять, куда именно результирующая таблица

помещается. Но использовать ее можно по разному: ее можно пе-

регнать (INTO TEMP) во временную таблицу, ее можно отдать на

обработку другому оператору (если выборку осуществлял подзап-

рос), для нее можно создать курсор ("буфер" с указателем на те-

кущую строку), а можно положить ее (INTO) в простую программную

переменную (если выбрано не более одной строки).

Выбранные строки можно упорядочить по возрастанию (убыванию)

значения в столбце (столбцах)

---------------------------------------------------------------

SELECT a,b,c,d+e FROM tabl ORDER BY b,c

SELECT a,b,c,d+e FROM tabl ORDER BY 2,3

---------------------------------------------------------------

В ORDER BY предложении вместо имени столбца можно указывать его

порядковый номер в списке выборки (select-list). Вышеприведен-

ные операторы эквивалентны.

Поместить значения из столбцов в переменные: (Поскольку lname

используется и как имя переменной, и как имя столбца, то имя

столбца предваряется знаком (@)

---------------------------------------------------------------

SELECT customer_num, @lname,city INTO cnum,lname,town

FROM customer

Группировка GROUP BY.

Группировка используется для для "сплющивания" группы (строк) в

одну.

Результат запроса содержит одну строку для каждого множества

строк, удовлетворяющих WHERE предложению и содержащих одно и то

же значение в указанном столбце.

---------------------------------------------------------------

SELECT dolvnostx, COUNT(*), AVG(zarplata) FROM kadry

GROUP BY dolvnostx

---------------------------------------------------------------

Получить количество работающих и их среднюю зарплату по каждой

должности из штатного расписания.

---------------------------------------------------------------

SELECT dolvnostx, COUNT(*), AVG(zarplata) FROM kadry GROUP BY 1

---------------------------------------------------------------

Эквивалентная запись.

Предложение HAVING накладывает дополнительные условия на

группу.

---------------------------------------------------------------

SELECT order_num, AVG(total_priece) FROM items

GROUP BY order_num HAVING COUNT(*) > 2

---------------------------------------------------------------

Этот запрос возвращает номера ордеров и среднее значение

total_price в заявках для всех ордеров, имеющих не менее двух

заявок.

Внешнее соединение таблиц.

Строки из таблицы, присоединенной внешним образом (на внешнее

соединение указывает ключевое слово OUTER) будут выбираться не

смотря на то, удовлетворяют они условиям WHERE предложения или

нет. В некоторых случаях это полезно, когда у вас есть главная

таблица и есть вспомогательная, и данные из главной таблицы вам

нужно получить в любом случае. Пример внешнего соединения:

---------------------------------------------------------------

SELECT company, order_num

FROM customer c, OUTER orders o

WHERE c.customer_num=o.customer_num

---------------------------------------------------------------

Запрос находит названия компаний и номера ордеров, которые они

послали. Если же компания ордеров не присылала, то ее название

все равно будет выбрано, а номер ордера в этой строке будет ра-

вен NULL. (А если бы мы запустили запрос без параметра OUTER,

то названия этих компаний вообще бы не попали в выборку.)

№27 Агрегатные функции

К агрегирующим функциям относятся функции вычисления суммы (SUM), максимального (SUM) и минимального (MIN) значений столбцов, арифметического среднего (AVG), а также количества строк, удовлетворяющих заданному условию (COUNT).

SELECT count(*), sum (budget), avg (budget),

min (budget), max (budget)

FROM department

WHERE head_dept = 100

вычислить: количество отделов, являющихся подразделениями отдела 100 (Маркетинг и продажи), их суммарный, средний, мини- мальный и максимальный бюджеты

COUNT	SUM	AVG	MIN	MAX
5	3800000.00	760000.00	500000.00	1500000.00

К выбранным строкам можно применять агрегатные функции COUNT(*)

- количество, MAX(column) и MIN(column) - максимальное и мини-

мальное значение в столбце, SUM(column) - сумма всех значений в

столбце, AVG(column) - среднее значение в столбце.

Поместить в переменную num количество строк в таблице orders, в

которых столбец customer_num равен 101:

SELECT COUNT(*) INTO num

FROM orders WHERE customer_num=101

Пример с использованием соединения таблиц. Находится среднее

значение зарплаты превосходящей 300 (столбец zarplata принадле-

жит одной из таблиц), при условии совпадения столбцов dolvnost

в двух таблицах.

---------------------------------------------------------------

SELECT AVG (zarplata) FROM table1,table2

WHERE table1.dolvnost=table2.dolvnost and zarplata>300

№30 DML – язык манипулирование данных

Data Manipulation Language. Операции ввода, изменения и удаления данных являются командами языка DML. Одна или несколько подряд идущих операций DML называются транзакциями. Про транзакции и как с ними управлять мы с Вами разберем в конце главы. А сейчас постаремся понять как часто используется эти операторы DML.

INSERT

Команда INSERT используется для ввода строк в таблицу. При использовании синтаксиса указанного ниже вы в один момент сможете ввести только одну строку:

INSERT INTO table [(column1 [, column2 [, ... columnn]])]

VALUES (value1[, value2[,...valuen]])

где

table     – название таблицы, в которую будут вставлены строки

column  – это название столбцов

value     – это значения, которые будут вставлены в соотвествующие столбцы

Стоит также отметить, что вы можете и не указывать имена столбцов в таком случаи данные идущие VALUES будут расставлены по соотвествующим столбцам согласно порядку столбцов.

Также Вы можете опустить тот или иной столбец при этом в этот столбец будет вставлена null или же указать самим в разделе VALUES в качестве значения тому или иному столбцу значение null. Также ваши данные обязательно должны соотвествовать типу столбцов или же в крайнем случаи должны иметь возможность подвергаться автоматическому преобразованию.

Есть еще понятие ограничене, которые мы изучим в следующих главах – так вот если эти ограничения есть, то они должны не нарушаться.

INSERT INTO scott.dept (deptno, dname, loc)

VALUES (50, 'IT', 'Baku');

Выполните этот запрос и в ответ увидите, что 1 rows inserted, то есть все прошло успешно и информация добавлена. Конечно мы могли и не писать имена всех столбцов как и было сказано выше

INSERT INTO scott.dept

VALUES (60, 'HR', 'Moscow')

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

 

INSERT INTO table [(column1 [, column2 [, ... columnn]])]

SUBQUERY

 

где

table                – название таблицы, в которую будут вставлены строки

column              – это название столбцов

SUBQUERY         – подзапрос при помощи, которого полученные данные будут

введены в таблицу

 

INSERT INTO scott.bonus (ename, job, sal ,comm)

SELECT ename, job, sal ,comm

FROM scott.emp

WHERE job = 'MANAGER'

На этом примере видно, что имена столбцов двух таблиц совпадает, но это не является обязательным условием, обязательное условие является, то чтобы типы у них были подходящими. 

UPDATE

Мы с вами говорили, что в DML есть команда, которая может изменять данные. Так вот это команда UPDATE, которая изменяет данные при этом она может за одну команду изменить как одну так и все строки таблицы, а в некоторых случаях она и не изменить какую-либо строку, все зависит от тех условиях, которые мы назначем.

Разберем ее синтаксис.

UPDATE  table

SET column1 = value1 [, columnn = valuen]

[WHERE condition]

где

table     – это названия таблицы, которую необходимо изменить

column – это названия столбцов, которые будут зменены

value     – это те значения, которые будет принимать столбцы

WHERE condition – это раздел условия, которым должны соотвествоать

изменяемые данные

Стоит отметить, если убрать раздел WHERE из команды, то это будет означать изменения значения по всем строкам таблицы. Также если не одна строка не будет удовлетворять условиям, то ничего не будет изменено.

UPDATE scott.emp

SET sal='2500'

WHERE empno=7369

Стоит также отметить, что при необходимости вы также можете использовать подзапросы в команде UPDATE и это будет выгляедть примерно так

UPDATE  table

SET column1 = subquery1[, column2 = subquery2 ]

[WHERE  expression1=subquery3, expression2=subquery4]

UPDATE scott.emp

SET sal=(select 3000

from dual)

WHERE empno in (select empno

from scott.emp

where ename = 'SMITH') 

DELETE

А сейчас пришел черед последней из команд DML – это команда удаления DELETE При помощи этой команды, вы можете за один раз удалить все строки таблицы или только те, которые будут отвечать вашим условиям или вообще никакие, опять таки все зависит от условий, которые мы выставляем.

Синтаксис этой команды таков:

DELETE FROM TABLE

[WHERE condition] 

Как видно это команда имеет очень простой синтаксис, если еы обратили внимания здесь даже невозможно указать имена столбцов. Естественно ведь комада удаляет строку целиком, а не чистит значения определенных столбцов Для чистки определенных столбцов вы можете вооружится командой UPDATE  и значением null

DELETE FROM scott.emp

WHERE ename = 'SMITH' 

Если убрать условия из этого запроса – вы почистите, то есть удалите все строки из таблицы emp. Не советуем этого делать так как нам еще эта таблица понадобится в следующих главах. Естественно в качестве условия вы можете использовать определенный подзапрос.

№50 Нормальная форма 

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

Процесс преобразования отношений базы данных (БД) к виду, отвечающему нормальным формам, называется нормализацией. Нормализация предназначена для приведения структуры БД к виду, обеспечивающему минимальную логическую избыточность, и не имеет целью уменьшение или увеличение производительности работы или же уменьшение или увеличение физического объёма базы данных. Конечной целью нормализации является уменьшение потенциальной противоречивости хранимой в базе данных информации. Как отмечает К. Дейт общее назначение процесса нормализации заключается в следующем:

• исключение некоторых типов избыточности;

• устранение некоторых аномалий обновления;

• разработка проекта базы данных, который является достаточно «качественным» представлением реального мира, интуитивно понятен и может служить хорошей основой для последующего расширения;

• упрощение процедуры применения необходимых ограничений целостности.

Устранение избыточности производится, как правило, за счёт декомпозиции отношений таким образом, чтобы в каждом отношении хранились только первичные факты.

Роль нормализации в проектировании реляционных баз данных

При том, что идеи нормализации весьма полезны для проектирования баз данных, они отнюдь не являются универсальным или исчерпывающим средством повышения качества проекта БД. Это связано с тем, что существует слишком большое разнообразие возможных ошибок и недостатков в структуре БД, которые нормализацией не устраняются. Несмотря на эти рассуждения, теория нормализации является очень ценным достижением реляционной теории и практики, поскольку она даёт научно строгие и обоснованные критерии качества проекта БД и формальные методы для усовершенствования этого качества. Этим теория нормализации резко выделяется на фоне чисто эмпирических подходов к проектированию,^[3] которые предлагаются в других моделях данных. Более того, можно утверждать, что во всей сфере информационных технологий практически отсутствуют методы оценки и улучшения проектных решений, сопоставимые с теорией нормализации реляционных баз данных по уровню формальной строгости.

Нормализацию иногда упрекают на том основании, что «это просто здравый смысл», а любой компетентный профессионал и сам «естественным образом» спроектирует полностью нормализованную БД без необходимости применять теорию зависимостей.^[4] Однако, как указывает К. Дейт, нормализация в точности и является теми принципами здравого смысла, которыми руководствуется в своём сознании зрелый проектировщик, то есть принципы нормализации — это формализованный здравый смысл. Между тем, идентифицировать и формализовать принципы здравого смысла — весьма трудная задача, и успех в её решении является существенным достижением. В одних случая нормализация увеличивает производительность, в других — уменьшает; объём же базы данных при нормализации как правило уменьшается.

Первая нормальная форма (1NF) — базовая нормальная форма отношения в реляционной модели данных.

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

В реляционной модели отношение всегда находится в первой нормальной форме по определению понятия отношение.

Что же касается различных таблиц, то они могут не быть правильными представлениями отношений и, соответственно, могут не находиться в 1NF. В соответствии с определением К. Дж. Дейта для такого случая, таблица нормализована (эквивалентно — находится в первой нормальной форме) тогда и только тогда, когда она является прямым и верным представлением некоторого отношения. Конкретнее, рассматриваемая таблица должна удовлетворять следующим пяти условиям:

1. Нет упорядочивания строк сверху-вниз (другими словами, порядок строк не несет в себе никакой информации).

2. Нет упорядочивания столбцов слева-направо (другими словами, порядок столбцов не несет в себе никакой информации).

3. Нет повторяющихся строк.

4. Каждое пересечение строки и столбца содержит ровно одно значение из соответствующего домена (и больше ничего).

5. Все столбцы являются обычными^[1].

«Обычность» всех столбцов таблицы означает, что в таблице нет «скрытых» компонентов, которые могут быть доступны только в вызове некоторого специального оператора взамен ссылок на имена регулярных столбцов, или которые приводят к побочным эффектам для строк или таблиц при вызове стандартных операторов. Таким образом, например, строки не имеют идентификаторов кроме обычных значений потенциальных ключей (без скрытых «идентификаторов строк» или «идентификаторов объектов»). Они также не имеют скрытых временных меток^[1].

Пример

Сотрудник	Номер телефона
Иванов И. И.	283-56-82 390-57-34
Петров П. П.	708-62-34

Сотрудник	Номер телефона
Иванов И. И.	283-56-82
Иванов И. И.	390-57-34
Петров П. П.	708-62-34

Исходная ненормализованная (то есть не являющаяся правильным представлением некоторого отношения) таблица:

Таблица, приведённая к 1NF (являющаяся правильным представлением некоторого отношения):

Атомарность атрибутов

Вопрос об атомарности атрибутов решается на основе семантики данных, то есть их смыслового значения. Атрибут атомарен, если его значение теряет смысл при любом разбиении на части или переупорядочивании. Следовательно, если какой-либо способ разбиения на части не лишает атрибут смысла, то атрибут неатомарен.

Одно и то же значение может быть атомарным или неатомарным в зависимости от смысла этого значения. Например, значение «4286» является

• атомарным, если его смысл — «пин-код кредитной карты» (при разбиении на части или переупорядочивании смысл теряется)

• неатомарным, если его смысл — «набор цифр» (при разбиении на части или переупорядочивании смысл не теряется)

Хорошим способом принятия решения о необходимости разбиения атрибута на части является вопрос: «будут ли части атрибута использоваться по отдельности?». Если да, то атрибут следует разделить (но так, чтобы сохранились осмысленные части атрибута). Далее необходимо снова задаться тем же вопросом для новой структуры и так до тех пор, пока не останется атрибутов, допускающих разбиение.

Примеры неатомарного атрибута, часто встречающиеся на практике: составные поля в виде строки идентификаторов, разделённых, скажем, запятыми: 100, 32, 168, 1045.

Исходное назначение 1NF

Исходное назначение 1NF, которую предложил Э. Ф. Кодд в статье «Реляционная модель данных для больших совместно используемых банков данных»^[2](«A Relational Model of Data for Large Shared Data Banks»^[3]), вообще не было связано с борьбой с аномалиями или избыточностью. Кодд предложил использовать «простые домены» (simple domains) только для облегчения будущей программной реализации, а именно:

• для облегчения хранения отношений в виде двумерных массивов

Отношение, все домены которого являются простыми, может быть представлено при хранении двухмерным массивом с однородными столбцами.

• для облегчения передачи данных в гетерогенных системах

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

Вторая нормальная форма (2NF) — одна из возможных нормальных форм таблицы реляционной базы данных.

Определение

Переменная отношения находится во второй нормальной форме тогда и только тогда, когда она находится в первой нормальной форме и каждый неключевой атрибут неприводимо зависит от ее потенциального ключа.^[1]

Неприводимость означает, что в составе потенциального ключа отсутствует меньшее подмножество атрибутов, от которого можно также вывести данную функциональную зависимость.^[1] Для неприводимой функциональной зависимости часто используется эквивалентое понятие «полная функциональная зависимость».^[1]

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

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

Сотрудник	Должность	Зарплата	Наличие компьютера
Гришин	Кладовщик	20000	Нет
Васильев	Программист	40000	Есть
Иванов	Кладовщик	25000	Нет

Пример приведения отношения ко второй нормальной форме

Пусть в следующем отношении первичный ключ образует пара атрибутов {Сотрудник, Должность}:

Зарплату сотруднику каждый начальник устанавливает сам (хотя её границы зависят от должности). Наличие же компьютера у сотрудника зависит только от должности, то есть зависимость от первичного ключа неполная.

В результате приведения к 2NF получаются два отношения:

Сотрудник	Должность	Зарплата
Гришин	Кладовщик	20000
Васильев	Программист	40000
Иванов	Кладовщик	25000

Должность	Наличие компьютера
Кладовщик	Нет
Программист	Есть

Третья нормальная форма (3NF) — одна из возможных нормальных форм отношения реляционной базы данных. 3NF была изначально сформулирована Э. Ф. Коддом в 1971 году.

Определение

Переменная отношения R находится в 3NF тогда и только тогда, когда выполняются следующие условия:

• R находится во второй нормальной форме.

• ни один неключевой атрибут R не находится в транзитивной функциональной зависимости от потенциального ключа R.

Пояснения к определению:

Неключевой атрибут отношения R — это атрибут, который не принадлежит ни одному из потенциальных ключей R.

Функциональная зависимость множества атрибутов Z от множества атрибутов X (записывается X → Z, произносится «икс определяет зет») являетсятранзитивной, если существует такое множество атрибутов Y, что X → Y и Y → Z. При этом ни одно из множеств X, Y и Z не является подмножеством другого, то есть функциональные зависимости X → Z, X → Y и Y → Z не являются тривиальными.

Определение 3NF, эквивалентное определению Кодда, но по-другому сформулированное, дал Карло Заниоло в 1982 году. Согласно ему, переменная отношения находится в 3NF тогда и только тогда, когда для каждой из её функциональных зависимостей X → A выполняется хотя бы одно из следующих условий:

• Х содержит А (то есть X → A — тривиальная функциональная зависимость)

• Х — суперключ

• А — ключевой атрибут (то есть А входит в состав потенциального ключа).

Определение Заниоло четко определяет разницу между 3NF и более строгой нормальной формой Бойса-Кодда (НФБК): НФБК исключает третье условие («А — ключевой атрибут»).

«Ничего, кроме ключа»

Запоминающееся и, по традиции, наглядное резюме определения 3NF Кодда было дано Биллом Кентом: каждый неключевой атрибут «должен предоставлять информацию о ключе, полном ключе и ни о чём, кроме ключа».^[1]

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

Крис Дэйт говорит о резюме Кента как о «интуитивно привлекательной характеристике» 3NF, и замечает, что с небольшим изменением она может служить и как определение более строгой нормальной формы Бойса-Кодда: «Каждый атрибут должен предоставлять информацию о ключе, полном ключе и ни о чём, кроме ключа». Вариант определения 3NF Кента является менее строгим, чем вариант НФБК Дэйта, поскольку первая утверждает только, что неключевые атрибуты зависят от ключей. Первичные атрибуты (которые являются ключами или их частями) вовсе не должны быть функционально зависимыми; каждый из них предоставляет информацию о ключе предоставлением самого ключа или его части. Здесь следует отметить, что это правило справедливо только для неключевых атрибутов, так как применение его ко всем атрибутам будет полностью запрещать все сложные альтернативные ключи, поскольку каждый элемент такого ключа будет нарушать условие «полного ключа».

Пример

Рассмотрим в качестве примера отношение, которое находится во 2NF, но не соответствует 3NF:

R1
Сотрудник	Отдел	Телефон
Гришин	Бухгалтерия	11-22-33
Васильев	Бухгалтерия	11-22-33
Петров	Снабжение	44-55-66

В отношении атрибут «Сотрудник» является первичным ключом. Личных телефонов у сотрудников нет, и телефон сотрудника зависит исключительно от отдела.

Таким образом, в отношении существуют следующие функциональные зависимости: Сотрудник → Отдел, Отдел → Телефон, Сотрудник → Телефон.

Зависимость Сотрудник → Телефон является транзитивной, следовательно, отношение не находится в 3NF.

R3
Сотрудник	Отдел
Гришин	Бухгалтерия
Васильев	Бухгалтерия
Петров	Снабжение

R2
Отдел	Телефон
Бухгалтерия	11-22-33
Снабжение	44-55-66

В результате разделения отношения R1 получаются два отношения, находящиеся в 3NF:

Исходное отношение R1 при необходимости легко получается в результате операции соединения отношений R2 и R3.

№54 ER-моделирование БД

ER-моделью- семантическая модель данных «Сущность-Связь» (Entity-Relationship, реляционной семантической моделью. ) Модель «сущность-связь» была предложена в 1976 году Питером Пин-Шен Ченом.

Сущность – это реальный или представляемый объект, информация о котором должна сохраняться и быть доступной.⁴³⁾ В диаграммах ER-модели  сущность представляется в виде прямоугольника, содержащего имя сущности. При этом имя сущности – это имя типа, а не некоторого конкретного экземпляра этого типа.⁴⁴⁾ Для большей выразительности и лучшего понимания имя сущности может сопровождаться примерами конкретных экземпляров этого типа.

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

• трехточечный вход в прямоугольник сущности, если для этой сущности в связи могут (или должны) использоваться много (many) экземпляров сущности;

• одноточечный вход, если в связи может (или должен) участвовать только один экземпляр сущности.

Обязательный конец связи изображается сплошной линией, а необязательный – прерывистой линией.

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

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