Этапы проектирования базы данных

Рынок DBaaS

Вначале коротко о рынке. Если вы уже знакомы с этой информацией, переходите сразу к блоку «Четыре в одном», но нам показалось, эти данные довольно интересны.

По прогнозу Technavio, в ближайшие годы мировой рынок DBaaS будет демонстрировать экспоненциальный рост — более чем на 65% ежегодно. Вместо того, чтобы вкладывать большие средства в аппаратные платформы, многие компании склонны инвестировать средства в услуги с еженедельной, ежеквартальной или ежегодной оплатой по подписке.

Объем работ по поддержке собственных многочисленных баз данных и серверов может быть весьма серьезным. Стандартизация, когда все в одной среде, переводит процесс на уровень выше и упрощает работу с БД. Тут ключевое слово – «упрощает», отмечают аналитики IDC.

Судя по результатам опросов, реляционные облачные СУБД – в числе самых популярных сервисов публичных облаков. Их используют 35% респондентов, экспериментируют -14%, планируют внедрение – 12% (источник – RightScale).

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

По данным Forrester, AWS – лидер рынка DBaaS. Amazon Relational Database Service позволяет работать с БД Oracle, Microsoft SQL Server, MySQL, MariaDB и PostgreSQL в среде EC2. Из 100 тыс. исследованных 2ndWatch экземпляров БД 67% представляли Amazon RDS.

Рост оборота мирового рынка DBaaS в млн. долларов (по данным 451 Research).

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

Рост сервиса Oracle DBaaS в мире.

Аналитики Markets&Markets прогнозируют, что рынок облачных СУБД/DBaaS вырастет с 1,07 млрд. долларов в 2014 году до 14,05 млрд. долларов к 2019 году при ежегодных темпах роста (CAGR) в 46%.

Выбор системы управления и программных средств БД

От выбора системы управления БД зависит практическая реализация информационной системы. Наиболее значимыми критериями в процессе выбора становятся параметры:

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

Ошибки в выборе СУБД практически наверняка впоследствии спровоцируют необходимость корректировать концептуальную и логическую модели.

[править] Основные шаги проектирования РБД с использованием объектного подхода

1. Выделить объекты ПО и определить связи между ними (1:1, 1:М, М:N).
2. Представить каждый объект ПО в виде таблицы, определив для нее первичный ключ и описав ограничения на значения данных.
3. Представить каждую взаимосвязь вида 1:1 или 1:М с помощью внешнего ключа, добавив его в таблицу, находящуюся со стороны «многие».
4. Представить каждую взаимосвязь вида М:N в виде отдельной таблицы с составным первичным ключом. Ввести в эту таблицу атрибуты, описывающие связь.
5. Выполнить процедуру нормализации отношений (таблиц) в БД.
6. Повторить перечисленные шаги пока не будет получен окончательный проект БД.

БД считается правильно спроектированной когда «один факт хранится один раз».

Этапы проектирования базы данных

Этап начальной разработки

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

Концептуальное проектирование базы данных

• анализ требований к базе данных, выявление представлений конечных пользователей и требований к обработке транзакций;
• определение сущностей, атрибутов и связей;
• разработка ER-диаграмм (от ER — Entity-Relationship — Сущность-Связь);
• нормализация;
• проверка модели данных, выявление основных процессов (правила ввода, обновления и удаления данных);
• проверка отчётов, запросов, представлений, целостности, совместного использования и безопасности.

Используйте проверочные ограничения

База данных — это не просто набор таблиц. В неё встроено много инструментов, которые помогут с сохранностью и качеством данных.

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

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

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

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

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

Основные этапы проектирования баз данных

Концептуальное (инфологическое) проектирование

Пример концептуальной схемы

Концептуальное (инфологическое) проектирование — построение семантической модели предметной области, то есть информационной модели наиболее высокого уровня абстракции. Такая модель создаётся без ориентации на какую-либо конкретную СУБД и модель данных. Термины «семантическая модель», «концептуальная модель» и «инфологическая модель» являются синонимами. Кроме того, в этом контексте равноправно могут использоваться слова «модель базы данных» и «модель предметной области» (например, «концептуальная модель базы данных» и «концептуальная модель предметной области»), поскольку такая модель является как образом реальности, так и образом проектируемой базы данных для этой реальности.

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

Чаще всего концептуальная модель базы данных включает в себя:

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

Логическое (даталогическое) проектирование

Пример логической схемы для реляционной модели данных.

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

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

На этапе логического проектирования учитывается специфика конкретной модели данных, но может не учитываться специфика конкретной СУБД.

Физическое проектирование

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

Результатом физического проектирования логической схемы выше на языке SQL может являться следующий скрипт:

CREATE TABLE IF NOT EXISTS Department ( -- Факультет
  id INT NOT NULL,
  name VARCHAR(45),
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS Group (
  id INT NOT NULL,
  name VARCHAR(45) ,
  depart_id INT NOT NULL,
  UNIQUE INDEX depart_id_UNIQUE (depart_id ASC),
  PRIMARY KEY (id, depart_id),
  CONSTRAINT depart_fk
    FOREIGN KEY (depart_id)
    REFERENCES Department (id)
);

CREATE TABLE IF NOT EXISTS Student (
  first_name VARCHAR(16) NOT NULL,
  last_name VARCHAR(45) NOT NULL,
  email VARCHAR(255),
  group_id INT NOT NULL,
  PRIMARY KEY (last_name, first_name, group_id),
  INDEX group_fk_idx (group_id ASC),
  CONSTRAINT group_fk
    FOREIGN KEY (group_id) REFERENCES Group (id)
);

Правило 5: Следите за данными, заполненные разделителями

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

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

Итак, теперь применим второе правило 1-й нормальной формы: «Избегайте повторения групп». Вы можете видеть на приведенном выше рисунке, что мы создали отдельную таблицу учебных планов — Syllabus Table, а затем сделал отношение «многие ко многим» (many-to-many relationship) к таблице предметов — Subject Table.

При таком подходе поле «Syllabus» в основной таблице больше не повторяется и имеет разделители данных.

Литература

• Дейт К. Дж. Введение в системы баз данных = Introduction to Database Systems. — 8-е изд. — М.: «Вильямс», 2006. — 1328 с. — ISBN 0-321-19784-4.
• Когаловский М.Р. Перспективные технологии информационных систем. — М.: ДМК Пресс; Компания АйТи, 2003. — 288 с. — ISBN 5-279-02276-4.
• Когаловский М.Р. Энциклопедия технологий баз данных. — М.: Финансы и статистика, 2002. — 800 с. — ISBN 5-279-02276-4.
• Кузнецов С. Д. Основы баз данных. — 2-е изд. — М.: Интернет-Университет Информационных Технологий; БИНОМ. Лаборатория знаний, 2007. — 484 с. — ISBN 978-5-94774-736-2.
• Коннолли Т., Бегг К. Базы данных. Проектирование, реализация и сопровождение. Теория и практика = Database Systems: A Practical Approach to Design, Implementation, and Management. — 3-е изд. — М.: «Вильямс», 2003. — 1436 с. — ISBN 0-201-70857-4.
• Гарсиа-Молина Г., Ульман Дж., Уидом Дж. Системы баз данных. Полный курс. — М.: «Вильямс», 2003. — 1088 с. — ISBN 5-8459-0384-X.

Разница между реляционной и иерархической базой данных

Определение

Реляционная база данных — это база данных, основанная на реляционной модели данных, как было предложено Э. Ф. Коддом в 1970 году. Иерархическая база данных — это тип базы данных, которая организует данные в древовидную структуру. Следовательно, это объясняет принципиальное различие между реляционной и иерархической базой данных.

Основанная модель

То есть; Реляционная база данных основана на реляционной модели. В отличие от этого, иерархическая база данных основана на иерархической модели.

Способ хранения данных

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

Извлечение данных

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

Таким образом, это важное различие между реляционной и иерархической базой данных

Заключение

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

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

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

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

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

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

Список полезной литературы

1. Учимся проектированию Entity Relationship — диаграмм // Хабр URL: https://habr.com/ru/post/440556/ (дата обращения: 02.01.2021).
2. Технологии баз данных. Лекция 3. Модель «Сущность-связь». URL: https://docplayer.ru/27886777-Model-sushchnost-svyaz-tehnologii-baz-dannyh-lekciya-3.html (дата обращения: 02.01.2021).
3. Entity Relationship Diagram. URL: https://plantuml.com/ru/ie-diagram (дата обращения: 03.01.2021).
4. Transact-SQL Reference (Database Engine) // Microsoft Docs URL: https://docs.microsoft.com/ru-ru/sql/t-sql/language-reference?view=sql-server-ver15 (дата обращения: 05.01.2021).
5. Нормализация отношений. Шесть нормальных форм // Хабр URL: https://habr.com/ru/post/254773/ (дата обращения: 05.01.2021).
6. Материалы для скачивания по SQL Server // Microsoft URL: https://www.microsoft.com/ru-ru/sql-server/sql-server-downloads (дата обращения: 05.01.2021).
7. Другой пример проектирования базы данных (MySQL). URL: https://pro-prof.com/forums/topic/db_example

Проектирование реляционной базы данных. Преобразование модели в реляционную

Преобразование концептуальной модели данных в реляционную — важная часть проектирования БД. Процесс включает в себя:
— построение набора предварительных таблиц;
— указание РК;
— выполнение нормализации.

Из набора таблиц состоят наши объекты, а из полей таблиц — атрибуты объектов:

Итак, мы определились с таблицами, полями, РК и FK. Следует отметить, что в таблицах «Журнал покупок» и «Журнал поставок» РК составные, т. к. состоят из 2-х полей.

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

Очевидно, что в поле «Темы» одни и те же названия встречаются регулярно. Для хранения таких данных нужны дополнительные ресурсы памяти. Кроме того, при дублировании данных можно допустить ошибку во время ввода значений атрибута, вследствие которой БД перейдёт в состояние несогласованности.
2. Устранение различных аномалий, связанных с обновлением, удалением, модификацией и пр. Пример аномалии модификации — чтобы поменять название темы, нам придётся смотреть все строки и менять название в каждой из них.

Нормализация бывает:
— 1-й нормальной формы (1НФ);
— 2НФ;
— 3НФ;
— НФБК (нормальной формы Бойса-Кодда);
— 4НФ;
— 5НФ.

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

Если говорить о реляционных базах данных, то минимум — это 1НФ. Однако в процессе проектирования специалисты по СУБД стремятся нормализовать базу хотя бы до уровня 3НФ, исключив тем самым избыточность данных и аномалии

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

Итак, в процессе проектирования мы преобразовали концептуальную модель в реляционную. Следующий этап — реализация её в конкретной СУБД. Для этого потребуется как сама СУБД, так и знание языка SQL. Например, прекрасно подойдёт СУБД MySQL или какая-нибудь другая СУБД.

Ключи в БД

Первичный ключ (РК, primary key) — столбец, значения которого различны во всех строках. РК бывают логические (естественные) и суррогатные (искусственные).

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

Вносим первичные ключи в наши таблицы:

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

Теперь становится ясно, что сообщение id=2 относится к теме «О рыбалке» (id=4), которая создана Васей, а остальные принадлежат теме «О рыбалке», созданной Кириллом (id=1). Такое поле будет называться внешний ключ (FK, foreign key). При этом каждое значение данного поля сопоставляется с каким-либо первичным ключом из таблицы «Темы». В результате устанавливается однозначное соответствие между темами и сообщениями.

Ещё момент: допустим, добавляется новый пользователь по имени Вася.

Как узнать, какой же из «Васей» оставил сообщение? Для этого поля «Автор» в наших таблицах «Сообщения» и «Темы» мы тоже сделаем внешними ключами:

Итак, наша база данных фактически готова. Схематично она выглядит так:

В этой небольшой базе данных лишь 3 таблицы. А что делать, если их 10 либо 200? Ясно, что всё не так просто. Именно поэтому любое проектирование реляционных баз данных начинается с разработки концептуальной модели данных.

Признаки БД, чем отличаются от электронных таблиц

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

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

Примечание

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

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

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

1. Excel не предусматривает установку реляционных связей между таблицами. Благодаря связям в Access, исключается ненужное дублирование информации и ошибки при обработке данных. Также допускается совместное использование данных из разных таблиц.
2. Access обеспечивает хранение в таблицах нескольких миллионов записей. При этом скорость их обработки сохраняется на высоком уровне.
3. Access включает возможность организации одновременной работы с базой данных нескольких десятков пользователей, которые в реальном времени могут видеть изменения, выполненные другими пользователями.
4. Данные в Access сохраняются автоматически после завершения редактирования текущей записи. В случае Excel для этого нужно запустить команду «Сохранить».
5. Таблицы в Access характеризуются заранее предопределенной жесткой структурой. Невозможно в один столбец записать разные типы данных или форматировать отдельные ячейки.
6. Прямо в таблице базы данных Access нельзя выполнять вычисления, подобные действия реализуются с применением запросов.

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

Логическое проектирование БД

Логическая структура БД должна соответствовать логической модели предметной области и учитывать связь модели данных с поддерживаемой СУБД

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

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

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

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

Схемы базы данных формируются с помощью одного из двух разнонаправленных подходов:

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

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

2 Построение концептуальной модели

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

На диаграмме выделены роли кассира и менеджера, а также основные отношения между сущностями. На диаграмме нет роли администратора, но его роль заключается в:

1. создании всех таблиц базы;
2. добавлении залов и рядов в них;
3. добавлении кассиров и менеджеров.

На диаграмме не отражена роль посетителя, так как:

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

На диаграмме проставлены кратности связей, например, видно, что один менеджер может добавить много (N) прокатов. В этой базе не оказалось связей типа N:M, сложных или рекурсивных связей — такие связи являются препятствиями в проектировании и решаются изменением ее структуры.

Для формирования схемы данных необходимо сначала дополнить ER-диаграмму реквизитами сущностей (уточнить ее) — результат приведен на рисунке.

• система не должна позволять продавать несколько билетов на одно и то же место при одном показе фильма. Это значит, что вторичным ключем для Билета должен быть кортеж (id_screening, row, seat). Однако, тогда нет необходимости в id билета — на билеты не ссылается ни одна таблица, это поле может быть удалено. Изначально id был добавлен потому, что обычно на билетах в кинотеатрах печатается номер;
• билет хранит поле id_hall, это было сделано для того, чтобы посетитель кинотеатра мог найти свой кинозал. Однако, билет, выдаваемый пользователю — это не тоже самое, что информация о билетах, хранимая в базе данных. Билет базы данных хранит также поле id_screening, а Показ уже ссылается на id_hall. Таким образом, в базе нет смысла хранить id_hall в таблице билетов.

Исправленная ER-диаграмма приведена ниже:

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

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

Правило 7. Тщательно выбирайте производные столбцы

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

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

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

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

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

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

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

Когда существует только один экземпляр объекта A для каждого экземпляра объекта B, говорят, что между ними существует связь «один-к одному» (часто обозначается 1:1). Можно указать этот тип связи в ER-диаграмме линией с тире на каждом конце:

1:1

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

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

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

Эта связи возникают, когда запись в одной таблице связана с несколькими записями в другой. Например, один клиент мог разместить много заказов, или у читателя может быть сразу несколько книг, взятых в библиотеке. Связи «один- ко-многим» (1:M) обозначаются так называемой «меткой ноги вороны», как в этом примере:

1:Mодной1

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

Когда несколько объектов таблицы могут быть связаны с несколькими объектами другой. Говорят, что они имеют связь «многие-ко-многим» (M:N). Например, в случае студентов и курсов, поскольку студент может посещать много курсов, и каждый курс могут посещать много студентов.

На ER-диаграмме эти связи отображаются с помощью следующих строк:

один-ко-многим

Для этого нужно создать между этими двумя таблицами новую сущность. Если между продажами и продуктами существует связь M:N, можно назвать этот новый объект «sold_products», так как он будет содержать данные для каждой продажи. И таблица продаж, и таблица товаров будут иметь связь 1:M с sold_products. Этот вид промежуточного объекта в различных моделях называется таблицей ссылок, ассоциативным объектом или таблицей связей.

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

Обязательно или нет?

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

один не может существовать без другого

Рекурсивные связи

Иногда при проектировании базы данных таблица указывает на себя саму. Например, таблица сотрудников может иметь атрибут «руководитель», который ссылается на другое лицо в этой же таблице. Это называется рекурсивными связями.

Лишние связи

Лишние связи — это те, которые выражены более одного раза

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

Так как единственный способ, которым ученикам назначают учителей — это предметы.

Приведём пример

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

В теории всё можно расположить в одной таблице, а именно:

Однако такое расположение противоречит атомарности, причём в столбцах «Созданные сообщения» и «Созданные темы» возможно неограниченное число значений. Целесообразнее всего разбить таблицу на три:

Теперь таблица «Пользователи» соответствует правилам. Но вот таблицы «Сообщения» и «Темы» — нет, т. к. не должно быть 2-х одинаковых строк. В нашем же случае один и тот же пользователь может написать 2 одинаковых сообщения:

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

Создаем базу данных

Управление базами данных как объектами

Будем считать, что наша небольшая экскурсия по запросам и командам SQL со стороны «торгового зала» завершена. Заглянем теперь в его «служебные помещения» и познакомимся с тем, как создается сама база данных. Эта часть языка SQL не столь стандартизирована и сильно отличается в различных реализациях. Поэтому в дальнейших примерах я буду придерживаться синтаксиса, принятого в самой популярной на веб-серверах системе — MySQL.

MySQL — продукт шведской компании MySQL AB. Ее основатели — Дэвид Аксмарк, Аллан Ларсон и Майкл Видениус (последний больше известен по прозвищу — Монти). По одной из версий, первая часть названия продукта (My) — не что иное, как англизированная запись имени дочери М. Видениуса. Однако точно за происхождение названия сегодня не могут поручиться даже отцы-создатели. Существует версия, по которой «my» — это префикс, с которого начинались названия рабочих каталогов на их компьютерах.

Из всех команд чаще всего нам будут нужны три: CREATE (создать), ALTER (изменить) и DROP (уничтожить).

Чтобы создать новую базу данных с названием, ну скажем, OUR_SHOP, следует выполнить команду:

Еще лучше сразу при ее создании установить нужную кодировку (ведь по умолчанию в MySQL используется latin1). В итоге команда будет выглядеть так.

Если вы забыли сделать это сразу, не беда. Для того и существуют команды по изменению:

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

Управление таблицами

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

Разберем эту команду подробнее. Тип INT устанавливается для столбцов с целочисленными данными, тип VARCHAR(100) обеспечивает хранение строк с длиной не более 100 символов, DECIMAL(10,2) соответствует действительным числам с не более чем десятью знаками и точностью в два знака после запятой.

Столбец ID объявлен первичным ключом (PRIMARY KEY).

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

NOT NULL означает запрет на пустые значения в столбце, иными словами, гарантирует обязательность заполнения.

Команда DEFAULT задает значение по умолчанию — то, которое будет записываться в базу при добавлении новой строки, если не указано иное. В нашем случае она обеспечивает автоматическое объявление товара штучным (код = 1) в случае, если при добавлении новых строк не будет указан другой код.

Признак UNIQUE обеспечивает уникальность значений в колонке (в нашем случае — уникальность названий товаров).

Если в будущем вы захотите перенастроить объявленные командой CREATE столбцы таблицы, сделать это можно командой ALTER. Например, таблицу GOODS можно нарастить строчной колонкой REMARK (подкоманда ADD):

Поработав с ней немного и убедившись, что 50 символов для примечания явно недостаточно, увеличиваем максимальный размер строки до 250 (блок CHANGE):

Так как имя столбца мы не изменяли (новое совпадает со старым), то его просто повторяем в этой команде (как бы меняем само на себя).

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

Удалить таблицу целиком можно командой DROP:

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

Подводим итоги проектирования

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

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

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

В заключение, добавим, что умение проектировать базы вам никогда не помешает. А научиться всему этому вы сможете на нашем курсе «Реляционные СУБД». Ждём вас!