Теоретические основы баз данных

1.1 Основные термины теории баз данных

 

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

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

· Реляционные (от англ. relation – «отношение», «зависимость», «связь») БД – совокупность отношений (таблиц) содержащих всю информацию, которая должна храниться. Таким образом реляционную базу данных можно рассматривать как хранилище данных, содержащее совокупность двумерных таблиц особого вида

· Таблицы (от лат. tabula – доска, таблица) – сведения, данные, представленные в виде упорядоченной системы, разделённые по строчкам и столбцам. В некоторых авторитетных источниках, таблицы в реляционных базах данных именуют сущностями.

· Ключи. В отношении всегда должен присутствовать некоторый атрибут (или набор атрибутов), однозначно определяющий каждый кортеж отношения и обеспечивающий уникальность строк таблицы. Такой атрибут (или набор атрибутов) называется первичным ключом отношения. Простой ключ – ключ, содержащий только один атрибут. В общем случае операции объединения выполняются быстрее в том случае, когда в качестве ключа используется самый короткий и самый простой из возможных типов данных. Сложный или составной ключ – ключ, состоящий из нескольких атрибутов. Внешний ключ – это атрибут (или множество атрибутов) одного отношения, являющийся ключом другого (или того же самого) отношения. Внешние ключи используются для установления логических связей между отношениями. Связь между двумя таблицами устанавливается путём присваивания значений внешнего ключа одной таблицы значениям ключа другой.

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

 

Существует три вида связей между таблицами. Вид создаваемой связи зависит от того, как заданы связанные столбцы.

Связи «один ко многим»

Связь «один ко многим» - наиболее распространённый вид связи. При такой связи каждой строке таблицы “А” может соответствовать множество строк таблицы “Б”, однако каждой строке таблицы “Б” может соответствовать только одна строка таблицы “А”. Связь «один ко многим» созаётся в том случае, когда только на один из связываемых столбцов наложено ограничение уникальности или он является первичным ключом.

Связи «многие ко многим»

При установлении связи многие «многие ко многим» каждой строке таблицы “А” может соответствовать множество строк таблицы “Б” и наоборот. Такая связь создаётся при помощи третьей таблицы называемой «соединительной», первичный ключ которой состоит из внешних ключей, связанных с таблицами “А” и “Б”.

Связи «один к одному»

При установлении связи «один к одному» каждой строке таблицы “А” может соответствовать только одна таблицы “Б” и наоборот. Связь «один к одному» создаётся в том случае, когда оба связанные столбца являются первичными ключами или на них наложены ограничения уникальности. Этот вид связи используется редко, поскольку в такой ситуации связываемые данные обычно можно хранить в одной таблицы. Использовать связь вида «один к одному» можно в указанных ниже случаях.

§ Чтобы разделить таблицу, содержащую слишком много столбцов.

§ Чтобы изолировать часть таблицы по соображениям безопасности.

§ Для хранения данных кратковременного использования, удалить которые проще всего путём очистки таблицы.

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

Создание связей между таблицами

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

 

· Индексы (англ. index) - объект базы данных, создаваемый с целью повышения производительности поиска данных. Таблицы в базе данных могут иметь большое количество строк, которые хранятся в произвольном порядке, и их поиск по заданному критерию путем последовательного просмотра таблицы строка за строкой может занимать много времени. Индекс формируется из значений одного или нескольких столбцов таблицы и указателей на соответствующие строки таблицы и, таким образом, позволяет искать строки, удовлетворяющие критерию поиска. Ускорение работы с использованием индексов достигается в первую очередь за счёт того, что индекс имеет структуру, оптимизированную под поиск — например, сбалансированного дерева.

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

· Триггеры (англ. trigger) – это хранимая процедура особого типа, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено наступлением определённого события (действием) – по сути добавлением ISERT или удалением DELETE строки в заданной таблице, или модификации UPDATE данных в определённом столбце заданной таблицы реляционной базы данных. Триггеры применяются для обеспечения целостности данных и реализации сложной бизнес-логики. Триггер запускается сервером автоматически при попытке изменения данных в таблице, с которой он связан. Все производимые им модификации данных рассматриваются как выполняемые в транзакции, в которой выполнено действие, вызвавшее срабатывание триггера. Соответственно, в случае обнаружения ошибки или нарушения целостности данных может произойти откат этой транзакции.

Момент запуска триггера определяется с помощью ключевых слов BEFORE (триггер запускается до выполнения связанного с ним события; например, до добавления записи) или AFTER (после события). В случае, если триггер вызывается до события, он может внести изменения в модифицируемую событием запись (конечно, при условии, что событие – не удаление записи).

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

 

в которой «висит» триггер, и т.п.).

Кроме того, триггеры могут быть привязаны не к таблице, а к представлению (VIEW). В этом случае с их помощью реализуется механизм «обновляемого представления». В этом случае ключевые слова BEFORE и AFTER влияют лишь на последовательность вызова триггеров, так как собственно событие (удаление, вставка или обновление) не происходит.

 

Нормализация Базы Данных

Результатом работы с АИС магазина компьютерных товаров является чек, который оформляет продавец. В этом чеке должна содержаться информация о количестве товаров в чеке, количестве каждого товара, стоимости товара, сумме покупки, скидке по товару, сумме покупки со скидкой. Если речь идёт о сети магазинов, то необходимо указать ещё и информацию о магазине (название, адрес), в котором данный чек был оформлен. А т.к. заработная плата продавцов зависит от их личных продаж, то в чеке необходимо указать и данные продавца, который оформил данный чек. Исходя из этого инфологическая модель базы данных будет выглядеть следующим образом:

 

Товар

Продавец

Магазин

Чек

Продаёт

Оформляет

Указывает

Рисунок 1 - Инфологическая модель

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

В результате нормализации обеспечивается:

· регулярность описаний данных;

· возможность присоединения новых полей, записей, связей без изменения существующих подсхем (или внешних моделей) и, соответственно, ПП. это обычно требует больших дополнительных затрат по сопровождению;

· максимальная гибкость при обработке произвольных запросов с рабочих мест пользователей.

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

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

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

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

1 избыточность данных; 2 аномалии обновления; 3 аномалии удаления; 4 аномалии ввода;

Можно легко представить себе пример ненормализованной таблицы, с которой будет очень неудобно работать:

 

Таблица 1 - Пример ненормализованной таблицы

Чек
Магазин	Дата покупки	Время покупки	Продавец	Товар	Цена товара	Количество товара	Сумма покупки	Скидка %	Сумма покупки с учётом скидки	Вид оплаты
Маг. 1	01.01.01	13:12	Иванов	ПК						нал
Маг. 1	01.01.01	13:12	Иванов	Мышь						нал
Маг. 1	01.01.01	13:12	Иванов	Колонки						нал

 

В нескольких записях таблицы повторяется одна и та же информация, следовательно, возникает избыточность данных. Такая таблица не может использоваться в магазине, т.к. на каждую единицу товара, купленную одним покупателем, нужно будет выбивать отдельный чек. Из таблицы видно, что в полях «Магазин», «Продавец» повторяются одни и те же данные, этого можно избежать созданием отдельных таблиц «Магазины» и «Продавцы», а в чеке указывать только коды продавцов и магазинов. Избавиться от необходимости выбивать отдельный чек на каждую единицу товара, можно путём создания таблиц «Товар», «Категории товара», «Скидки» и «Позиции в чеке». Поля «Сумма покупки» и «Сумма покупки с учётом скидки» должны заполняться расчётными данными, это можно реализовать с помощью компонентов интерфейса, который я разработал в Delphi (это я описал в пункте 2.3 Создание пользовательского интерфейса для работы с базой данных) – в среде Delphi добавить компоненты, которые позволят вывести данные о стоимости покупки и стоимости покупки со скидкой на напечатанный чек. Этим мы значительно сократим размеры базы данных в будущем, при введении её в эксплуатацию, т.е. заполнении таблиц.

Первая нормальная форма (1NF).

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

Таким образом таблицы «Магазины», «Продавцы», «Товары», «Скидки», «Позиции в чеке», «Категории товаров», «Чек» в 1NF, будут выглядеть следующим образом:

 

Таблица 3 - 1NF "Магазины"

Магазины
Код	Название	Адрес
	«Кибертрон»	Ул. Центральная 1
	«Кибертрон мини»	Ул. Южная 5
	«Кибертрон сервис»	Ул. Северная 13

 

 

Таблица 4 - 1NF "Продавцы"

Продавцы
Код	Фамилия	Имя	Отчество
	Иванов	Пётр	Сергеевич
	Сидорова	Елена	Александровна
	Василенко	Артём	Михайлович

 

 

Таблица 5 - 1NF "Товары"

Товары
Код	Наименование	Код категории	Цена за единицу	Остаток на складе
	ASUS ER8596
	Defender 85
	GeForce k560

 

Таблица 6 - 1NF "Скидки"

Скидки
Код	Вид скидки	Размер скидки %
	Карта клиента
	Сезонная акция

 

Таблица 7 - 1NF "Категории товаров"

Категории товаров
Код	Название
	Материнские платы
	Клавиатуры

 

Таблица 8 - 1NF "Позиции в чеке"

Позиции в чеке
Код	Код чека	Код товара	Количество купленного товара

 

Таблица 9 - 1NF "Чек"

Чек
Код	Код магазина	Дата покупки	Время покупки	Код продавца	Сумма покупки	Код скидки	Сумма покупки со скидкой
		19.03.2016	11:35
		19.03.2016	11:35

 

Теперь все таблицы базы данных соответствуют первой нормальной форме.

Вторая нормальная форма (2NF): устранение избыточных данных

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

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

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

 

от части составного потенциального ключа.

Вторая нормальная форма по определению запрещает наличие не ключевых

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

. Процесс приведения к 2NF позволяет избавиться от большей части повторяющихся данных.

При приведении базы данных к 2 NF таблицы преобразовались:

 

Магазины		Продавцы		Категории		Чек
Код		Код		Код		Код
Название		Фамилия		Название категории		Дата покупки
Адрес		Имя				Время покупки
		Отчество				Код продавца
						Код скидки
Позиции в чеке				Скидка		Код магазина
Код чека		Товары		Код скидки		Сумма покупки
Количество куп-го товара		Код		Вид скидки		Сумма со скидкой
Код товара		Код категории		Размер скидки
		Цена за единицу
		Остаток на складе
		Наименование

Рисунок 2 - 2NF

Рисунок 2 – показывает, что все таблицы соответствуют второй нормальной форме. Следовательно, можно приступить к формированию третьей нормальной формы.

Третья нормальная форма (3NF)

В 3NF необходимо исключить транзитивную зависимость между полями. Чтобы перейти к 3NF необходимо выполнить следующие действия:

1) Определить все поля, от которых зависят другие поля.

2) Создать новую таблицу для каждого такого поля или группы полей и группы зависящих от него полей переместить в другую таблицу.

3) Удалить перемещенные поля из исходной таблицы.

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

 

 

Позиции в чеке				Продавцы
Код				Код
Код чека				Фамилия
Код товара				Имя
Количество купленного товара				отчество
		Чек
		Код		Скидки
Товары		Дата покупки		Код
Код		Время покупки		Вид скидки
Код категории		Код продавца		Размер скидки
Цена за единицу		Код скидки
Остаток на складе		Код магазина
Наименование		Сумма покупки
		Сумма со скидкой		Магазины
Категории товаров				Код
Код				Название
Название категории				Адрес

Рисунок 3 - Логическая схема

 

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

 

Типы данных и команды SQL.

Microsoft SQL Server поддерживает большинство типов данных SQL 2003. Также SQL Server поддерживает дополнительные типы данных, используемые для однозначной идентификации строк данных в таблице и на многих серверах, например, UNIQUEIDENTIFIER, что соответствует аппаратной философии «роста в ширину», исповедуемой Microsoft (т. е. внедрение базы на множестве серверов на платформах Intel), вместо «роста в высоту» (т. е. внедрение на одном огромном мощном UNIX-сервере или Windows Data Center Server).

Основные типы данных используемые в Microsoft SQL Server:

1) Символьные типы данных - содержат буквы, цифры и специальные символы.

· CHAR или CHAR(n) -символьные строки фиксированной длины. Длина строки определяется параметром n. CHAR без параметра соответствует CHAR(1). Для хранения таких данных всегда отводится n байт вне зависимости от реальной длины строки.

· VARCHAR(n) - символьная строка переменной длины. Для хранения данных этого типа отводится число байт, соответствующее реальной длине строки.

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

· INTEGER или INT - целое, для хранения которого отводится, как правило, 4 байта. (Замечание: число байт, отводимое для хранения того или иного числового типа данных, зависит от используемой СУБД и аппаратной платформы, здесь приводятся наиболее "типичные" значения) Интервал значений от - 2147483647 до + 2147483648

· SMALLINT - короткое целое (2 байта), интервал значений от - 32767 до +32768

3) Вещественные типы данных - описывают числа с дробной частью.

· FLOAT и SMALLFLOAT - числа с плавающей точкой (для хранения отводится обычно 8 и 4 байта соответственно).

· DECIMAL(p) - тип данных аналогичный FLOAT с числом значащих цифр p.

· DECIMAL(p,n) - аналогично предыдущему, p - общее количество десятичных цифр, n - количество цифр после десятичной запятой.

4) Денежные типы данных - описывают, естественно, денежные величины. Если

в ваша система такого типа данных не поддерживает, то используйте DECIMAL (p, n).

· MONEY (p, n) - все аналогично типу DECIMAL (p, n). Вводится только потому, что некоторые СУБД предусматривают для него специальные методы форматирования.

5) Дата и время - используются для хранения даты, времени и их комбинаций. Большинство СУБД умеет определять интервал между двумя датами, а также уменьшать или увеличивать дату на определенное количество времени.

· DATE - тип данных для хранения даты.

· TIME - тип данных для хранения времени.

· INTERVAL - тип данных для хранения временного интервала.

 

· DATETIME - тип данных для хранения моментов времени (год + месяц + день + часы + минуты + секунды + доли секунд).

6) Двоичные типы данных - позволяют хранить данные любого объема в двоичном коде (оцифрованные изображения, исполняемые файлы и т.д.). Определения этих типов наиболее сильно различаются от системы к системе, часто используются ключевые слова:

· BINARY

· BYTE

· BLOB

7) Последовательные типы данных - используются для представления возрастающих числовых последовательностей.

· SERIAL - тип данных на основе INTEGER, позволяющий сформировать уникальное значение (например, для первичного ключа). При добавлении записи СУБД автоматически присваивает полю данного типа значение, получаемое из возрастающей последовательности целых чисел.

В заключение следует сказать, что для всех типов данных имеется общее значение NULL - "не определено". Это значение имеет каждый элемент столбца до тех пор, пока в него не будут введены данные. При создании таблицы можно явно указать СУБД могут ли элементы того или иного столбца иметь значения NULL (это не допустимо, например, для столбца, являющего первичным ключом).

Основные команды Microsoft SQL Server:

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

Поэтому, в язык SQL в качестве составных частей входят:

· язык манипулирования данными (Data Manipulation Language, DML)

· язык определения данных (Data Definition Language, DDL)

· язык управления данными (Data Control Language, DCL).

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

Язык манипулирования данными используется, как это следует из его названия, для манипулирования данными в таблицах баз данных. Он состоит из 4 основных команд:

 

SELECT	(выбрать)
INSERT	(вставить)
UPDATE	(обновить)
DELETE	(удалить)

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

CREATE DATABASE	(создать базу данных)
CREATE TABLE	(создать таблицу)
CREATE VIEW	(создать виртуальную таблицу)
CREATE INDEX	(создать индекс)
CREATE TRIGGER	(создать триггер)
CREATE PROCEDURE	(создать сохраненную процедуру)
ALTER DATABASE	(модифицировать базу данных)
ALTER TABLE	(модифицировать таблицу)
ALTER VIEW	(модифицировать виртуальную таблицу)
ALTER INDEX	(модифицировать индекс)
ALTER TRIGGER	(модифицировать триггер)
ALTER PROCEDURE	(модифицировать сохраненную процедуру)
DROP DATABASE	(удалить базу данных)
DROP TABLE	(удалить таблицу)
DROP VIEW	(удалить виртуальную таблицу)
DROP INDEX	(удалить индекс)
DROP TRIGGER	(удалить триггер)
DROP PROCEDURE	(удалить сохраненную процедуру)

Язык управления данными используется для управления правами доступа к данным и выполнением процедур в многопользовательской среде. Более точно его можно назвать "язык управления доступом". Он состоит из двух основных команд:

GRANT	(дать права)
REVOKE	(забрать права)

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

· интерактивный SQL

· встроенный SQL.

Интерактивный SQL используется в специальных утилитах (типа WISQL или DBD), позволяющих в интерактивном режиме вводить запросы с использованием команд SQL, посылать их для выполнения на сервер и получать результаты в предназначенном для этого окне. Встроенный SQL используется в прикладных программах, позволяя им посылать запросы к серверу и обрабатывать полученные результаты, в том числе комбинируя set-ориентированный и record-ориентированный подходы.