Лекция 2. Компетенции специалистов БД. Нормализация реляционных таблиц (отношений). Группы операторов SQL.

**1-ый учебный вопрос: Компетенции специалиста по базам данных.** Описан в дополнительных материалах к лекции (Профессиональный стандарт АДБ РФ). Смотри в файлах к дисциплине. **2-ой учебный вопрос: Нормализация отношений.** В данном курсе будут рассмотрены принципы нормализации вплоть до 3NF. Остальные формы подробно описываются в учебнике Крёнке или в следующих источниках: http://citforum.ru/database/osbd/glava_23.shtml (до 5 NF); https://ru.wikipedia.org/wiki/Нормальная_форма (до 6NF). *Нормальная форма *- свойство отношения в реляционной модели данных, характеризующее его с точки зрения избыточности, потенциально приводящей к логически ошибочным результатам выборки или изменения данных. Нормальная форма определяется как совокупность требований, которым должно удовлетворять отношение. *Избыточность* – состояние реляционной базы данных, при котором в таблицах присутствуют лишние данные. *Нормализация* - процесс преобразования отношений базы данных к виду, отвечающему нормальным формам. Рассмотрим процессы нормализации на примерах. Пусть, существует некоторая таблица, позволяющая вести запись студентов в различные секции и кружки (рис. 1)  Таблица состоит из трёх атрибутов: НомерСтудента, Секция и Плата за занятие. НомерСтудента является первичным ключом. Считается, что на таблицу, в приведенном на рис. 1 виде, оказывают влияние аномалии. В реляционной модели данных различают аномалии удаления и вставки. Поясним смысл аномалий на конкретных примерах, основанных на данных из рис. 1. 1. Аномалия удаления (DA, delete anomaly) - удаление лишней информации при удалении записи (рис. 2)  При удалении некоторых данных может произойти потеря другой информации. Если удалить студента с номером 100, то будет потеряна информация о том, что во дворце спорта университета есть лыжная секция. Также, будет утеряна информация о цене занятия в выбранной секции. 2. Аномалия вставки (IA, insertion anomaly) - добавление лишней информации или возникновение противоречащих значений в некоторых столбцах при вставке новой записи, рис. 3.  В приведенную таблицу нельзя вставить данные о новых секциях и плате за них, пока ни один студент не запишется ни в одну из новых секций. Соответственно – студент не сможет записаться на секцию, если не будет знать о том, сколько стоит одно занятие. Для решения проблемы аномалий были придуманы правила нормализации. Применение правил нормализации хотя бы до третьей формы фактически гарантирует отсутствие аномалий. Переход к третьей нормальной форме осуществляется последовательно: от нереляционной модели к первой нормальной форме, от первой нормальной формы ко второй нормальной форме, от второй нормальной формы к третьей нормальной форме. О любой таблице данных, удовлетворяющей определению отношения, говорят, что она находится в *первой нормальной форме (first normal form, 1NF)*. Таблица на рис. 1 находится в первой нормальной форме. 1NF -> 2NF, рис. 4. Таблица находится во* второй нормальной форме* если каждый из ее неключевых столбцов зависит от всего ее первичного ключа. В соответствии с этим определением, если отношение имеет в качестве ключа единственный атрибут, то оно автоматически находится во второй нормальной форме. Поскольку ключ состоит из одного атрибута, то само собой разумеется, что каждый ключевой атрибут зависит от всего ключа, и частичных зависимостей быть не может. Таким образом, вторая нормальная форма представляет интерес только для тех отношений, которые имеют композитные ключи.  2NF -> 3NF, рис. 6. При характеристике второй нормальной формы необходимо ввести понятие транзитивности или транзитивной зависимости. *Транзитивная зависимость.* Если для столбцов А, В и С некоторой таблицы существуют функциональные зависимости А→В, В→С, говорят, что столбец С связан транзитивной зависимостью со столбцом А через столбец В (при этом столбец А не должен функционально зависеть ни от столбца В, ни от столбца С). Иными словами – если между неключевыми столбцами есть связь, речь, скорее всего идет о транзитивной зависимости, рис. 5.  Зависимость на рис. 5 между общежитием и платой является транзитивной (действительно, плата может изменяться в зависимости от удаленности общежития от университета, его комфортабельности, и т.д.). В этом случае говорится о том, что столбцы таблицы находятся в транзитивной зависимости, что приводит к возникновению аналогичных первому примеру аномалий. Аномалия удаления появляется при выписке (удалении) единственного студента из общежития, а аномалия вставки – при добавлении новых общежитий в фонд жилья. В данном случае студенты не будут знать о новом общежитии и стоимости проживания в нём до тех пор, пока один из них не будет размещён в нем (рис. 6).  Таблица преобразуется в третью нормальную форму из второй нормальной формы путём разбиения транзитивных зависимостей неключевых атрибутов таблицы. **3-ий учебный вопрос: Язык SQL.** Аббревиатура SQL расшифровывается как structured query language, что в переводе с английского означает «язык структурированных запросов». SQL определяется, как формальный непроцедурный язык программирования, применяемый для создания, модификации и управления данными в произвольной реляционной базе данных, управляемой соответствующей системой управления базами данных (СУБД). SQL основывается на исчислении кортежей. SQL не является полноценным языком программирования; он представляет собой всего лишь подъязык данных (data sublanguage). В нём имеются операторы только для создания и обработки данных. Язык SQL был разработан фирмой IBM в конце 1970-х годов и был принят Американским национальным институтом стандартов (ANSI) в качестве национального стандарта США в 1992 году. Изначально SQL был основным способом работы пользователя с базой данных и позволял выполнять следующий набор операций: создание в базе данных новой таблицы, добавление в таблицу новых записей, изменение записей, удаление записей, выборку записей из одной или нескольких таблиц (в соответствии с заданным условием), изменение структур таблиц. Со временем SQL усложнился - обогатился новыми конструкциями, обеспечил возможность описания и управления новыми хранимыми объектами (например, индексами, представлениями, триггерами и хранимыми процедурами). SQL состоит из четырёх составных групп операторов (рис. 6)  Совокупность операторов, служащих для определения данных, называют языком определения данных (data definition language, DDL). Совокупность операторов для обновления и запроса данных (иначе говоря, для манипулирования данными) – языком манипулирования данными (data manipulation language, DML). Язык определения доступа к данным (data control language, DCL) используется для контроля доступа к данным в БД. Язык управления транзакциями (transaction control language, TCL) используется для контроля обработки транзакций в БД. Группа операторов DDL (рис. 7).  Операторы DDL позволяют создавать, изменять и удалять отдельные объекты в БД. Допустимые типы объектов зависят от используемой СУБД и обычно включают базы данных, пользователей, таблицы и ряд более мелких вспомогательных объектов, например, роли и индексы. В языке SQL применяются три оператора определения данных: CREATE, ALTER, DROP. CREATE - создаёт объект БД (саму базу, таблицу, представление, триггер и т. д.). Ниже приведен общий вид команды создания таблицы при помощи оператора CREATE. CREATE TABLE <имя таблицы> (<имя столбца> [доп.параметры: первичный ключ, возможность NULL и т.д.] <тип столбца>) ALTER - изменяет объект БД (саму базу, таблицу, представление, триггер и т. д.). Ниже приведены общие виды команды изменения таблицы: добавление столбца и изменение свойства уже имеющегося столбца при помощи оператора ALTER. ALTER TABLE <имя таблицы> ADD <имя столбца> (свойства столбца) ALTER TABLE <имя таблицы> ALTER COLUMN <имя столбца> (новые свойства столбца) DROP - удаляет объект БД (саму базу, таблицу, представление, триггер и т. д.). Ниже приведен общий вид команды удаления таблицы при помощь оператора DROP. DROP TABLE <имя таблицы> В качестве объектов для определения, помимо таблиц (table), также могут выступать: базы данных (database), представления (view), индексы (index), триггеры (trigger) и хранимые процедуры (procedure). Группа операторов DCL (рис. 8).  Приведем общие виды основных команд для оператора SELECT. Простая выборка из таблицы: SELECT <столбец\столбцы> FROM <имя таблицы> Выборка из таблицы по условию: SELECT <столбец\столбцы> FROM <имя таблицы> WHERE <условия> Если добавить после оператора SELECT агрегатную функцию, то с выбранным столбцом можно осуществить вычислительную операцию. Покажем наиболее часто употребимые агрегатные функции: COUNT – выведет на экран количество строк, не NULL. SUM - производит арифметическую сумму всех выбранных значений. AVG - производит усреднение всех выбранных значений. MAX - производит наибольшее из всех выбранных значений. MIN - производит наименьшее из всех выбранных значений. Покажем общий вид команды на добавление значений в таблицу при помощи оператора INSERT: INSERT INTO <имя таблицы> (<столбцы, куда будут записаны новые значения>) VALUES(<имя столбца> <значение>, …) Покажем общий вид команды на изменение значений в таблице при помощи оператора UPDATE: UPDATE <имя таблицы> SET <столбец, куда будут записаны новые значения> = <значение> WHERE <условие> Покажем общий вид команды на удаление значений из таблицы при помощи оператора DELETE: DELETE FROM <имя таблицы> WHERE <условия> Группа операторов DML (рис. 9).  Группа операторов TCM (рис. 10).  SQL создавался как простой стандартизированный способ извлечения и управления данными, содержащимися в реляционной базе данных. Позднее он стал сложнее, чем задумывался, и превратился в инструмент разработчика, а не конечного пользователя. В настоящее время SQL (по большей части в реализации Oracle) остаётся самым популярным из языков управления базами данных.