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

База данных хранит записи в специально организованном виде, чтобы информацию можно было легко найти и извлечь. Любая БД состоит из одной или нескольких таблиц. Электронная таблица состоит из строк и столбцов. Все строки имеют одинаковые столбцы, а каждый столбец содержит данные. В общем, для лучшего понимания, определимся, что таблицы в БД очень похожи на те, что вы видели в Excel-е.

Табличные данные могут быть вставлены, восстановлены, обновлены и удалены. Для пакета этих операций была создана специальная аббревиатура CRUD (Create-Read-Update-Delete).

Реляционные базы данных - это базы, где вся информация хранится в таблицах, связанных друг с другом специальными отношениями. Эти отношения позволяют нам извлекать и объединять данные из одной или нескольких таблиц с помощью одного запроса.

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

Шаг 1. Подготовка данных

Для того чтобы нам было с чем работать, я набрал в твиттере запрос “#databases” и сформировал таблицу из 10 записей:

Таблица 1

В первую очередь, давайте разберёмся с колонками:

Это реальные данные. Если хотите, вы можете их найти и обновить.

Хорошо. Теперь все наши данные находятся в одном месте. Даёт ли это нам возможность легко осуществить поиск по ним? Не совсем. Данная таблица далека от идеала. Во-первых, в некоторых столбцах у нас есть повторяющиеся записи: к примеру, в х “username” и “following_username”. Также колонка “following_username” нарушает правила реляционных моделей, т.к. её в ячейках присутствует более 1 значения (записи разделены запятыми).

К тому же у нас попадаются дубликаты и в строках.

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

Решение данной проблемы заключается в разделении Таблицы 1 на несколько таблиц. Давайте примемся за решение первой проблемы, а именно - устранение дубликатов в столбцах.

Шаг 2. Избавляемся от дубликатов в столбцах

Как было оговорено выше, столбцы “username” и “following_username” содержат дубликаты данных. Они возникли в результате того, что я хотел отобразить отношения между твиттами и пользователями. Давайте улучшим нашу структуру БД, разделив существующую таблицу на две: в одной будем хранить информацию, а в другой - отношения между записями.

Поскольку @Brett_Englebert подписан на @RealSkipBayless, то в таблице “following” отобразим это следующим образом: имя @Brett_Englebert поместим в колонку “from_user”, а @RealSkipBayless в “to_user.” Давайте посмотрим, как будет выглядеть таблица “following” после разделения Таблицы 1 :

Таблица 2. following

Таблица 3. users

Уже лучше. Теперь в таблице “users” (Таблица 3) у нас хранится только информация о твиттах, а в таблице following (Таблица 2) - зависимость пользователей.

Основатель теории реляционных баз данных, Эдгар Кодд, назвал бы этот процесс (удаления повторений из столбцов таблиц) приведением БД к первой нормальной форме.

Шаг 3. Удаление повторений из строк

Теперь мы займёмся устранением других проблем, а именно, избавимся от дубликатов в строках таблицы “users”. Поскольку пользователи @AndyRyder5 и @Brett_Englebert разместили по несколько твиттов, то их имена в таблице “users” (Таблица 3 ) дублируются в колонке full_name. Данная проблема также решается разделением таблицы “users”.

Поскольку текст твитта и время его создания являются уникальными данными, то их мы поместим в одну и ту же таблицу. Также нам нужно указать связь между твитами и пользователями. Для этого я создал специальный столбец username.

Таблица 4. tweets

Таблица 5. users

После разделения в таблице users (Таблица 5 ) у нас присутствуют уникальные (не повторяющиеся) строки.

Данный процесс удаления дубликатов из строк называется приведением ко второй нормальной форме.

Шаг 4. Объединяем таблицы на основе ключей

Итак, в результате наших действий, Таблица 1 была разбита на 3 части: following (Таблица 2), tweets (Таблица 4), users (Таблица 5). Все дубликаты устранены. Для того чтобы в дальнейшем мы могли с лёгкостью извлекать данные из этой структуры, независимые друг от друга таблицы мы должны связать специальными отношениями, которые будут давать нам информацию о том, какому пользователю принадлежит какой твит, и кто на кого подписан.

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

Вообще говоря, в Таблице 4 и 5 мы уже это сделали. В таблице “users” первичным ключом является колонка “username”, потому что логин пользователя должен быть уникальным значением и не может повторяться. В таблице “tweets” мы используем данный ключ для обозначения связи между пользователем и твитом. Колонка “username” в таблице “tweets” называется внешним ключом.

Если вы когда-то работали с базами данных, то у вас может возникнуть вопрос: можем ли мы использовать колонку “username” в качестве первичного ключа?

С одной стороны, это может упростить процесс поиска, ведь мы не используем никаких числовых ID. С другой стороны, что если пользователь захочет поменять свой логин? Это может привести к огромному количеству проблем. Для того чтобы не попасть в подобную ситуацию, лучше воспользоваться числовыми ID. Всё зависит от вашей системы. Если вы предоставляете вашим пользователям возможность менять логины, то лучше в качестве первичного ключа использовать автоинкрементированное числовое поле ID. В противном случае, колонка “username” вполне подойдёт для этой роли. Я оставлю всё как есть.

Давайте посмотрим на таблицу tweets (Таблица 4). Первичный ключ должен быть уникальным для каждой строки. Какую колонку в данной таблице мы можем выбрать для этой роли? Колонка “created_at” не подойдёт, т.к. в принципе 2 разных пользователя могут в одно и то же время опубликовать запись. С колонкой “text” та же история: два разных пользователя могут создать твит с текстом “Hello World”. Колонка “username” в данной таблице является внешним ключом для обозначения связи между пользователем и твитом. Итак, поскольку все возможные варианты нам не подходят, то лучшим решением будет добавление колонки id, которая будет первичным ключом для данной таблицы.

Таблица 6. tweets с колонкой id

С таблицей following можем сделать то же самое, т.к. ни одна существующая колонка не подойдёт на роль первичного ключа. Колонки “from_user” и “to_user” являются внешними ключами и обозначают связь между подписками пользователей.

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

Ниже вы можете увидеть схему наших таблиц и связей между ними:

Системы Управления Базами Данных

Теперь, когда у нас есть реляционная БД, каким образом мы можем её имплементировать? Для этого мы можем воспользоваться системами управления базами данных (СУБД). Существует целый набор подобных программ, как платных, так и бесплатных. Среди платных можно выделить Oracle Database , IBM DB2 и Microsoft SQL Server . Бесплатные: MySQL , SQLite и PostgreSQL .

Чаще всего различные компании используют MySQL. Twitter в этом смысле - не исключение.

SQLite чаще используется при разработке приложений для iOS и Android, где хранится различного рода конфиденциальная информация. Браузер Google Chrome использует SQLite для хранения истории просмотров, кукисов, изображений...

PostgreSQL используется реже. Для неё существует полезное расширение PostGIS, которое делает данную СУБД удобной для хранения геолокационных данных. К примеру сервис OpenStreetMap исользует PostgreSQL.

Язык структурированных запросов (SQL)

После того, как вы выбрали подходящую для вас СУБД и установили её, следующим шагом было бы создание таблиц и управление данными. Для этого мы можем воспользоваться специальным языком SQL.

Создание БД development:

CREATE DATABASE development;

Создание таблицы Users:

CREATE TABLE users (full_name VARCHAR(100), username VARCHAR(100));

При создании полей нам необходимо указать тип хранимой информации и её размер. Колонки “full_name” и “username” будут типа VARCHAR, который предназначен для хранения строк символов. Размер 100 символов. Список всех типов вы можете найти .

Добавление записи:

INSERT INTO users (full_name, username) VALUES ("Boris Hadjur", "_DreamLead");

Извлечение всех записей пользователя _DreamLead:

Обновление записи:

Удаление записи:

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

Итог

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

Свойства реляционной таблицы

ОСНОВНЫЕ ПОНЯТИЯ БАЗ ДАННЫХ

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

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

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

Другие определения, имеющие отношение к БД и СУБД.

Банк данных (БнД) – это система специальным образом организованных данных – баз данных, программных, технических, языковых, организационно-методических средств, предназначенных для обеспечения централизованного накопления и многоцелевого использования данных.

Информационная система (ИС) – взаимосвязанная совокупность средств, методов и персонала, используемых для хранения, обработки и выдачи информации в интересах достижения поставленной задачи.

Основой практически любой информационной системы является база данных.

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

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

­ иерархическая модель;

­ сетевая модель;

­ реляционная модель.

РЕЛЯЦИОННАЯ МОДЕЛЬ ДАННЫХ

Теоретической основой этой модели является теория отношений и основной структурой данных – отношение. Именно поэтому модель получила название реляционной (от английского слова relation - отношение).

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

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

Свойства реляционной таблицы

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

· каждый элемент таблицы - один элемент данных;

· все столбцы (поля, атрибуты) в таблице однородные, т.е. все элементы в одном столбце имеют одинаковый тип (числовой, символьный и т.д.) и длину;

· каждый столбец имеет уникальное имя;

· одинаковые строки (записи, кортежи) в таблице отсутствуют;

· порядок следования строк и столбцов может быть произвольным.

Каждое поле содержит одну характеристику объекта предметной области. В записи собраны сведения об одном экземпляре этого объекта.

Ключи

Поле, каждое значение которого однозначно определяет соответствующую запись, на­зывается простым ключом (ключевым полем). Ключ, состоящий из нескольких полей называется составным ключом . В СУБД Access в качестве ключа может быть использован Счетчик, который автоматически возрастает на единицу при вводе в таблицу новой записи. Такой ключ называют искусственным. Он семантически не связан ни с одним полем таблицы. Из-за этого он допускает повторный ввод одних и тех же записей. Но с помощью него просто устанавливать связь между таблицами. Основное свойство ключа – уникальность, неповторимость.

Типы связей между таблицами

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

Связи между таблицами бывают трех типов:

«один-к-одному» (1:1) – одной записи в главной таблице соответствует одна запись в подчиненной таблице,

«один-ко-многим» (1:М) – одной записи в главной таблице соответствует несколько записей в подчиненной таблице,

«многие-ко-многим» (М:М) – нескольким записям в главной таблице соответствуют несколько записей в подчиненной таблице. Или одной записи в первой таблице может соответствовать несколько записей во второй таблице. И одной записи во второй таблице могут соответствовать несколько записей в первой таблице.

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

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

Чтобы связать две реляционные таблицы, необходимо ключ главной таблицы ввести в состав подчиненной таблицы. Название ключа может быть другим, но обязательно одинаковыми с первичным ключом должны быть тип и размер вторичного ключа в подчиненной таблице. Для удобства лучше обозначение вторичного ключа оставлять таким же, как и первичного. Однако если ключом выбран Счетчик , то вторичный ключ должен иметь тип Числовой - длинное целое (но не Счетчик !). Вторичный ключ – это или обычное поле, или часть первичного ключа в подчиненной таблице.

СУБД Access для реализации связи «многие-ко-многим» требует создать таблицу связи и ввести в нее в качестве вторичных ключей первичные ключи двух таблиц, которые должны иметь такую связь (М:М). После этого устанавливается связь 1:М каждой из двух таблиц с таблицей связи. Между двумя таблицами таким образом реализуется связь М:М. Если в БД «Моя библиотека» создать таблицы Книги и Авторы, то связь между ними будет вида М:М, так как одной записи в таблице Книги (реквизиты одной книги) может соответствовать несколько записей в таблице Авторы. Потому что у одной книги может быть несколько авторов. В свою очередь, одной записи в таблице Авторы могут соответствовать несколько записей в таблице Книги, так как один автор может написать несколько книг. Таблицу связи можно назвать КнигиАвторы, в которую будут включены ключи обеих таблиц – Книги и Авторы. Если требуется, в таблицу связи можно включить и другие поля.

Среди реляционных баз данных следует различать корпоративные и настольные базы данных.

Из корпоративных реляционных СУБД наиболее распространенными являются: Oracl, IBM DB2, Sybase, Microsoft SQL Server, Informix. Из постреляционных СУБД известна СУБД Cache компании InterSystems.

Наиболее известны в настоящее время следующие настольные БД: Microsoft Access, Paradox (фирмы Borland), FoxPro (Microsoft), dBase IV (IBM), Clarion.

Эти СУБД занимают более 90% всего рынка СУБД.

В следующем разделе дана краткая характеристика СУБД Microsoft Access.

Реляционная модель

Реляционная модель базы данных была предложена в 1969 г. математиком и научным сотрудником фирмы IBM Э.Ф. Коддом (E.F. Codd). Некоторые начальные сведения о реляционных базах данных содержатся в обзорной статье “БД и СУБД ” 2. Поскольку в настоящее время именно реляционные базы данных являются доминирующими, в этой статье (а также в статьях “Описание данных ”, “Обработка данных ” и “Проектирование БД ” 2) подробно рассматриваются наиболее существенные понятия реляционной модели.

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

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

Нередко слово “реляционная” (relational ) в термине “реляционная модель” трактуют, основываясь на том, что в реляционной базе данных устанавливаются связи (relate ) между таблицами. Такое объяснение удобно, но оно не является точным. В оригинальной системе терминов Кодда термины связи (relations ), атрибуты (attributes ) и кортежи (tuples ) употреблялись там, где большинство из нас пользуется более привычными терминами таблицы, столбцы (поля) и строки (записи).

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

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

Таблица “Человек”

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

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

Ключи

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

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

В этой таблице ни поле Дата, ни Время суток, ни Температура не являются ключами - в каждом из этих полей значения могут повторяться. Зато комбинация полей Дата+Время суток является уникальной и однозначно определяет строку таблицы. Это и есть составной ключ.

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

В этой таблице можно выбрать целых три ключа. Один из них - простой (ИНН), два другие - составные (Серия+Номер общегражданского паспорта и Серия+Номер заграничного паспорта). В такой ситуации разработчик выбирает наиболее удобный с точки зрения организации БД ключ (в общем случае - ключ, на поиск значения которого требуется наименьшее время). Выбранный ключ в этом случае часто называют главным, или первичным , ключом, а другие комбинации столбцов, из которых можно сделать ключ, - возможными , или альтернативными, ключами. Отметим, что хотя бы один возможный ключ в таблице имеется всегда, так как строки не могут повторяться и, следовательно, комбинация всех столбцов гарантированно является возможным ключом.

При изображении таблиц первичные ключи таблиц принято выделять. Например, соответствующие поля часто подчеркивают. А Microsoft Access выделяет ключевые поля полужирным шрифтом.

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

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

Нормальные формы, нормализация

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

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

Во-вторых, требуется, чтобы в таблице был назначен первичный ключ .

Указанные требования являются необходимыми, но недостаточными. В теории реляционных баз данных вводятся понятия так называемых “нормальных форм” - требований к организации данных в таблицах. Нормальные формы нумеруются последовательно, по мере ужесточения требований. В правильно спроектированной БД таблицы находятся как минимум в третьей нормальной форме. Соответственно, мы рассмотрим первые три нормальные формы. Напомним, что мы имеем дело с таблицами, удовлетворяющими двум сформулированным выше основным требованиям.

Первая нормальная форма (1НФ)

Первая нормальная форма предписывает, что все данные, содержащиеся в таблице, должны быть атомарными (неделимыми ). Перечень соответствующих атомарных типов данных определяется СУБД. Требование 1НФ совершенно естественное. Оно означает, что в каждом поле каждой записи должна находиться только одна величина, но не массив и не какая-либо другая структура данных. Приведем осмысленный пример таблицы, которая не находится в 1НФ. Пусть у нас имеются списки оценок учеников по некоторому предмету.

Так как значение поля Оценки не является атомарным, таблица не соответствует требованиям 1НФ.

О возможном способе представления списка оценок написано в методических рекомендациях к статье “Проектирование БД” 2.

Вторая нормальная форма (2НФ)

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

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

Здесь уместно задаться вопросом: а в чем практический смысл 2НФ? Какая польза от этих ограничений? Оказывается - большая. Допустим, что в приведенном выше примере разработчик проигнорирует требования 2НФ. Во-первых, скорее всего возникнет так называемая избыточность - хранение лишних данных. Ведь если для одной записи с данной датой уже хранится время восхода, то для всех других записей с данной датой оно должно быть таким же и хранить его, вообще говоря, незачем.

Обратим внимание на слова “должно быть”. А если не будет? Ведь на уровне БД это никак не контролируется - ключ в таблице составной, одинаковые даты могут быть (и по смыслу скорее всего будут). И никакие формальные ограничения (а наше понимание, что “такого не может быть”, к таковым не относится) не запрещают указать разное время восхода для одной и той же даты.

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

Говорят, что таблица находится в 3НФ, если она соответствует 2НФ и все не ключевые столбцы взаимно независимы.

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

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

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

Отметим, что наличие указанной выше зависимости разработчик определяет, анализируя предметную область, - никакими формальными методами подобную коллизию увидеть нельзя. При изменении свойств предметной области зависимость между столбцами может и исчезнуть. Например, если в пределах одного города вводятся различные коды (как 495 и 499 в Москве), соответствующие столбцы перестают быть связанными с точки зрения нарушения требований 3НФ.

В теории реляционных баз данных рассматриваются и формы высших порядков - нормальная форма Бойса - Кодда, 4НФ, 5НФ и даже выше. Большого практического значения эти формы не имеют, и разработчики, как правило, всегда останавливаются на 3НФ.

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

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

Многотабличные БД, связи между таблицами, внешние ключи

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

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

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

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

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

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

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

Чтобы отнести ученика к некоторому классу, заведем в таблице “Ученик” дополнительное поле Номер класса. (Понятно, что его тип должен полностью совпадать с типом поля Номер класса в таблице “Класс”.) Теперь мы можем связать таблицы “Ученик” и “Класс” по совпадающим значениям полей Номер класса (мы не случайно назвали эти поля одинаково, на практике так часто поступают, чтобы легко ориентироваться в связывающих полях). Заметим, что одной записи в таблице “Класс” может соответствовать много записей в таблице “Ученик” (и на практике скорее всего соответствует - трудно представить себе класс из одного ученика). О таких таблицах говорят, что они связаны отношением “один ко многим ”. А поле Номер класса в таблице “Ученик” называют внешним ключом . Как видим, назначение внешних ключей - связывание таблиц. Отметим, что внешний ключ всегда ссылается на первичный ключ связанной таблицы (т.е. внешний ключ находится на стороне “многих”). Связанный с внешним первичный ключ называют родительским , хотя этот термин используется реже.

Проиллюстрируем сказанное схемой в стиле Microsoft Access (подробнее о “Схеме данных” Access написано в статье “Описание данных” 2).

Теперь вспомним об учителях и предметах. Анализируя предметную область (только так - ведь истинное положение вещей из самой формальной модели извлечь невозможно), мы замечаем, что тип связи между сущностями “учитель” и “предмет” иной, нежели рассмотренный выше. Ведь не только один предмет могут вести много учителей, но и один учитель может вести много предметов. Таким образом, между этими сущностями имеется связь “многие ко многим ”. Тут уже не обойтись введением дополнительных полей (попробуйте!). Связи “многие ко многим” всегда разрешаются посредством введения дополнительной таблицы. А именно, организуем таблицу “Учитель-Предмет”, имеющую следующую структуру:

Таблица “Учитель-Предмет”

Эта таблица имеет составной ключ, образованный из двух ее полей. И таблица “Учитель”, и таблица “Предмет” связаны с данной таблицей отношением “один ко многим” (разумеется, в обоих случаях “многие” находятся на стороне “Учитель-Предмет”). Соответственно, в таблице “Учитель-Предмет” имеются два внешних ключа (оба - части составного первичного ключа, что не воспрещается), служащие для связи с соответствующими таблицами.

На практике, помимо рассмотренных отношений “один ко многим” и “многие ко многим”, встречается и отношение “один к одному ”. С точки зрения теории такое отношение интереса не представляет, так как две таблицы, связанные отношением “один к одному”, всегда можно просто объединить в одну. Тем не менее в реальных базах данных отношение “один к одному” применяется для оптимизации обработки данных. Проиллюстрируем сказанное примером.

Допустим, мы храним очень много разнообразной информации о людях - данные их всевозможных документов, телефоны, адреса и пр. Скорее всего боRльшая часть этих данных будет использоваться очень редко. А часто нам потребуются лишь фамилия, имя, отчество и телефон. Тогда имеет смысл организовать две таблицы и связать их отношением “один к одному”. В одной (небольшой) таблице хранить часто используемую информацию, в другой - остальную. Естественно, что таблицы, связанные отношением “один к одному”, имеют один и тот же первичный ключ.

Правила целостности

Реляционная модель определяет два общих правила целостности базы данных: целостность объектов и ссылочная целостность.

Правило целостности объектов очень простое. Оно требует, чтобы первичные ключи таблиц не содержали неопределенных (пустых) значений .

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

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

Индексация

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

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

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

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

Во-вторых, выполняя с детьми простые запросы к базам данных (соответствующий материал изложен в статье “Обработка данных” 2), необходимо иметь дело с правильными с точки зрения реляционной теории таблицами. Не требуется объяснять ученикам, что эти таблицы правильные, а “вот если бы…, то таблица была бы неправильной”, но недопустимо использовать плохие примеры.

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

Моделируемые предметные области должны быть не слишком большими;

Они должны быть очень хорошо знакомы учащимся (в этом смысле изрядно поднадоевший всем проект “Школа” - не худший выбор!);

Наивно ожидать, что, прослушав основы теории, ученики смогут что-то спроектировать сами. Каждый шаг необходимо проходить вместе с ними, подробно аргументируя свои действия.

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

Фундаментальные модели

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

Иерархическая имеет древовидную структуру и составляется из данных разных уровней, между которыми существуют связи. Сетевая модель БД представляет собой более сложный шаблон. Ее структура напоминает иерархическую, а схема расширенная и усовершенствованная. Разница между ними в том, что потомственные данные иерархической модели могут иметь связь только с одним предком, а у сетевой их может быть несколько. Структура реляционной базы данных гораздо сложнее. Поэтому ее следует разобрать более подробно.

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

Такая модель была разработана в 1970-х годах доктором науки Эдгаром Коддом. Она представляет собой логически структурированную таблицу с полями, описывающую данные, их отношения между собой, операции, произведенные над ними, а главное - правила, которые гарантируют их целостность. Почему модель называется реляционной? В ее основе лежат отношения (от лат. relatio) между данными. Существует множество определений этого типа базы данных. Реляционные таблицы с информацией гораздо проще систематизировать и придать обработке, нежели в сетевой или иерархической модели. Как же это сделать? Достаточно знать особенности, структуру модели и свойства реляционных таблиц.

Процесс моделирования и составления основных элементов

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

Моделирование таблиц и проектирование реляционных баз данных производится посредством бесплатных инструментов, таких как Workbench, PhpMyAdmin, Case Studio, dbForge Studio. После детальной проектировки следует сохранить графически готовую реляционную модель и перевести ее в готовый SQL-код. На этом этапе можно начинать работу с сортировкой данных, их обработку и систематизацию.

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

Каждый источник по-своему описывает ее элементы, поэтому для меньшей путаницы хотелось бы привести небольшую подсказку:

• реляционная табличка = сущность;
• макет = атрибуты = наименование полей = заголовок столбцов сущности;
• экземпляр сущности = кортеж = запись = строка таблички;
• значение атрибута = ячейка сущности= поле.

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

1. Сущность. Таблица реляционной базы данных может быть одна, а может быть целый набор из таблиц, которые характеризируют описанные объекты благодаря хранящимся в них данным. У них фиксированное количество полей и переменное число записей. Таблица реляционной модели баз данных составляется из строк, атрибутов и макета.
2. Запись - переменное число строк, отображающих данные, что характеризируют описываемый объект. Нумерация записей производится системой автоматически.
3. Атрибуты - данные, демонстрирующие собой описание столбцов сущности.
4. Поле. Представляет собой столбец сущности. Их количество - фиксированная величина, устанавливаемая во время создания или изменения таблицы.

Теперь, зная составляющие элементы таблицы, можно переходить к свойствам реляционной модели database:

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

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

Основные характеристики полей реляционных БД

Названия полей должны быть уникальными в рамках одной сущности. Типы атрибутов или полей реляционных баз данных описывают, данные какой категории хранятся в полях сущностей. Поле реляционной базы данных должно иметь фиксированный размер, исчисляемый в символах. Параметры и формат значений атрибутов определяют манеру исправления в них данных. Еще есть такое понятие, как "маска", или "шаблон ввода". Оно предназначено для определения конфигурации ввода данных в значение атрибута. Непременно при записи неправильного в поле должно выдаваться извещение об ошибке. Также на элементы полей накладываются некоторые ограничения - условия проверки точности и безошибочности ввода данных. Существует некоторое обязательное значение атрибута, которое однозначно должно быть заполнено данными. Некоторые строки атрибутов могут быть заполнены NULL-значениями. Разрешается ввод пустых данных в атрибуты полей. Как и извещение об ошибке, есть значения, которые заполняются системой автоматически - это данные по умолчанию. Для ускорения поиска любых данных предназначено индексированное поле.

Схема двумерной реляционной таблицы базы данных

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

Базовые правила нормализации реляционной сущности

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

2. Для таблицы, которая уже приведена к 1НФ, наименование любого неидентифицирующего столбца должно быть зависимым от уникального идентификатора таблицы (2НФ).

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

Базы данных: реляционные связи между таблицами

Существует 2 основных реляционных табличек:

• «Один-многие». Возникает при соответствии одной ключевой записи таблицы №1 нескольким экземплярам второй сущности. Значок ключа на одном из концов проведенной линии говорит о том, что сущность находится на стороне «один», второй конец линии зачастую отмечают символом бесконечности.

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

Существование ключей в реляционной базе данных

Первичный и вторичный ключи определяют потенциальные отношения базы данных. Реляционные связи модели данных могут иметь только один потенциальный ключ, это и будет primary key. Что же он собой представляет? Первичный ключ - это столбец сущности или набор атрибутов, благодаря которому можно получить доступ к данным конкретной строки. Он должен быть уникальным, единственным, а его поля не могут содержать пустых значений. Если первичный ключ состоит всего из одного атрибута, тогда он называется простым, в ином случае будет составляющим.

Кроме первичного ключа, существует и внешний (foreign key). Многие не понимают, какая между ними разница. Разберем их более детально на примере. Итак, существует 2 таблицы: «Деканат» и «Студенты». Сущность «Деканат» содержит поля: «ID студента», «ФИО» и «Группа». Таблица «Студенты» имеет такие значения атрибутов, как «ФИО», «Группа» и «Средний бал». Так как ID студента не может быть одинаковым для нескольких студентов, это поле и будет первичным ключом. «ФИО» и «Группа» из таблицы «Студенты» могут быть одинаковыми для нескольких человек, они ссылаются на ID номер студента из сущности «Деканат», поэтому могут быть использованы в качестве внешнего ключа.

Пример модели реляционной базы данных

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

Необходимо провести связи, чтобы получилась полноценная реляционная база данных. Запись "ИН-41", как и "ИН-72", может присутствовать не единожды в табличке "Деканат", также фамилия, имя и отчество студентов в редких случаях могут совпадать, поэтому данные поля никак нельзя сделать первичным ключом. Покажем сущность «Студенты».

Как мы видим, типы полей реляционных баз данных совершенно различаются. Присутствуют как цифровые записи, так и символьные. Поэтому в настройках атрибутов следует указывать значения integer, char, vachar, date и другие. В таблице "Деканат" уникальным значением является только ID студента. Данное поле можно взять за первичный ключ. ФИО, группа и телефон из сущности "Студенты" могут быть взяты как внешний ключ, ссылающийся на ID студента. Связь установлена. Это пример модели со связью «один к одному». Гипотетически одна из таблиц лишняя, их можно легко объединить в одну сущность. Чтобы ID-номера студентов не стали всеобще известными, вполне реально существование двух таблиц.

Реляционные БД

Реляционная база данных состоит из одной или нескольких связанных таблиц, структуру которых образуют столбцы и строки.

В реляционных базах данных приняты следующие обозначения:

Отношение - таблица;

Поле- набор однотипных записей для нескольких объектов (столбец);

Кортеж (запись) - строка таблицы, содержащая набор нескольких записей соответствующих одному объекту;

Атрибут - запись в строке одного поля.

Сущность - любой различимый объект, информация о котором хранится в базе данных.

Ключевые поля

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

Различают следующие виды ключей:

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

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

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

В зависимости от количества полей образующих ключ выделяют:

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

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

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

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

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

Часто в качестве первичного ключа выбирают искусственно созданное поле, значения атрибутов которого не имеют фактического смысла. Таки полями могут быть Код или Номер , эти поля содержат только числовое обозначение строки, причем зачастую это обозначение выставляет компьютер при помощи счетчика. Такие коды не подвержены изменениям в отличие от полей содержащих фактические данные, т.к. Фамилия, Номер телефона, Адрес и т.д. могут меняться и повторятся.

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