Проектирование базы данных MySQL


Оглавление (нажмите, чтобы открыть):

Записки программиста

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

Время от времени я заглядываю на Toster.ru и иногда даже отвечаю там на вопросы. Чаще всего люди спрашивают две вещи — как стать программистом и как правильно спроектировать схему базы данных. Мне лично кажется очень странным, что так много людей задают последний вопрос. Мне почему-то всегда казалось, что это такая простая вещь, которую умеют вообще все. Но, раз так много людей интересуются, здесь я постараюсь дать достаточно развернутый и в то же время краткий ответ.

Я предполагаю, что SQL вы знаете. То есть, объяснять, что такое таблицы, строки, индексы, первичные ключи и ссылочная целостность, не требуется. Если это не так, боюсь, я вынужден отправить вас к соответствующей литературе. Благо, ее сейчас очень много.

Рисуем диаграмму

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

Нарисовать следюущую диаграмму заняло у меня порядко десяти минут:

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

Дополнение: Аналогичную диаграмму можно построить при помощи открытого инструмента PlantUML.

Генерируем SQL и скармливаем его СУБД

Нетрудно заметить, что данная диаграмма легко отображается в код для создания схемы базы данных на языке SQL. В DbSchema сгенерировать SQL можно, сказав Schema → Generate Schema and Data Script. Затем полученный скрипт можно скормить используемой вами СУБД:

Я использовал PostgreSQL. Информацию о том, как установить эту СУБД, вы найдете в этой заметке.

Итак, чем же я руководствовался при проектировании схемы?

Нормальные формы

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

Грубо говоря, таблица находится в первой нормальной форме (1НФ), если на пересечении любой строки и любого столбца в таблице находится ровно одно значение. В современных РСУБД это условие всегда выполняется. Даже если СУБД поддерживает множества или массивы, на пересечении строки и столбца хранится ровно одно значение типа множество или массив. Но в таблице (user varchar(100), phone integer) не может быть строки alex — 1234, 5678 . В 1НФ может быть только две сроки — alex — 1234 и alex — 5678 .

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

Таблица находится в третьей нормальной форме, если она находится в 2НФ и ни один неключевой атрибут не находится в транзитивной функциональной зависимости от первичного ключа. Например, рассмотрим таблицу (employee varchar(100) primary key, department varchar(100), department_phone integer) . Очевидно, что она находится в 2НФ. Но телефон отдела находится в транзитивной функциональной зависимости от имени сотрудника, так как сотрудник однозначно задает отдел, а отдел однозначно задает телефон отдела. Для приведения таблицы в 3НФ нужно разбить ее на две таблицы — employee — department и departmnet — phone .

Легко видеть, что нормализация уменьшает избыточность базы данных и препятствует внесению случайных ошибок. Например, если оставить таблицу из последнего примера в 2НФ, то можно по ошибке прописать одному и тому же отделу разные телефоны. Или рассмотрим компанию с пятью отделами и 1000 сотрудниками. Если у отдела поменялся номер телефона, то для его обновления в базе данных в случае 2НФ потребуется просканировать 1000 строк, а в случае с 3НФ только пять.

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

Отношение один ко многим

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

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

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

Жанры и страны в приведенной схеме иногда еще называют «словарями». Это сравнительно небольшие таблицы, состоящие из двух столбцов — id и названия. Если, например, мы захотим переименовать страну Russia в Russian Federation, нам придется поменять всего лишь одну строчку в таблице countries, а не править кучу строк в таблице artists, что может привести к очень большому количеству дисковых операций. Кроме того, если требуется отобразить в диалоге создания нового исполнителя выпадающий список с выбором страны, нам не придется делать дорогих группировок по таблице artists, достаточно сделать простую выборку из countries.

Отношение многие ко многим

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

Оно моделируется путем введения дополнительной таблицы. В нашем примере эта таблица называется albums_songs. Первичный ключ в этой таблицы состоит из двух внешних ключей — album_id и song_id. Теперь нетрудно с помощью пары join’ов получить все песни, входящие в данный альбом или все альбомы, в которые входит заданная песня. Кроме того, ничто не мешает завести в связующей таблице дополнительные столбцы. Например, столбец, хранящий номер трека, под которым песня входит в заданный альбом.

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

Отношение родитель-потомок (или общее-частное)

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

Один из способов моделирования такой ситуации заключается в введении по отдельной таблице на каждый из возможных подтипов. В приведенном примере это таблицы groups и persons. В качестве первичного ключа в каждой из этих таблиц используется artist_id, первичный ключ родительской таблицы artists. Кто-то при использовании такой схемы предпочитает добавить в родительскую таблицу столбец type, но, строго говоря, он является избыточным. Недостаток этого метода заключается в том, что можно создать исполнителя, являющегося как группой, так и человеком одновременно.

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

Что еще нужно принять во внимание

Принцип при моделировании других отношений тот же. Например, один человек имеет двух родителей и при этом один человек может иметь сколько угодно детей. Казалось бы, связь 2:N, этого мы не проходили. На самом деле, это просто две связи 1:N. Вводим столбцы mother_id, father_id и вперед. Да, связь в рамках одной таблицы, ну и что?

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

В общем, все, что нужно, это немного здравого смысла.

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

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

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

Заключение

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

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

Проблема с проектирование БД mysql для сайта сеиалов

Есть сущности. Сущности имеют информацию, которая разбивается по группам.

Например: сущность 1 имеет группы с номерами 1 и 2, в которых также хранятся сущности.

Например: в группе 1 хранится сущность А, а в группе 2 хранится сущность Б. Эти сущности одного типа.

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

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

Другое значение вставить нельзя, так-как идет ограничение уникальности. Проще на конкретном примере -> db_series. Ячейки с null в таблицах с номерами 5 — 8.

Бд проектируется для сайта сериалов. Сущности первого порядка — сериалы, группы — сезоны сериалов. Сущности хранящиеся в группа — это разумеется серии сезонов. Идентичные атрибуты — title , keywords , description , комментарии, скриншоты, постеры.

Есть ли для моего случая шаблон проектирования? Как избавиться от ячеек с null ?

Может разбить title и вставить непосредственно в таблицы сериалов, сезонов, комментариев и тп?

2 ответа 2

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

Ошибка NULL у вас возникает, скорее всего, не из-за неправильности архитектуры базы данных, а из-за ошибок в коде записи в базу.

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

У сущностей «Сериалы», «Сезоны», «Серии» очень много общих свойств. Вы пишите год к сериалу, но информация о годах выхода сезонов и серий то же может быть интересна. Актерский состав у вас только к сериалу, но в дальнейшем может быть интересен и состав для конкретных серий (кстати рекомендую сделать справочник актеров и связующую таблицу в которой еще может быть отражена исполняемая роль и режиссеры могут быть в этой же таблице). На imdb.com и много где еще рейтинг есть не только у сериала целиком но и у серий.

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

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

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

IT-блог о веб-технологиях, серверах, протоколах, базах данных, СУБД, SQL, компьютерных сетях, языках программирования и создание сайтов.

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

Здравствуйте, уважаемые посетители моего скромного блога для начинающих вебразработчиков и web мастеров ZametkiNaPolyah.ru. Продолжаем рубрику Заметки о MySQL, в которой я уже успел рассмотреть установку и настройку MySQL сервера баз данных, а также рассмотрел способы изменения кодировок сервера MySQL при помощи команды SET NAMES и файла конфигураций my.ini. Сегодня будет краткая и если можно так сказать теоретическая статья, посвященная вопросу — что такое базы данных и какие базы данных бывают.

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

База данных. Математические модели, структура, определение.

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

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

Виды и типы баз данных

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

Иерархическая база данных, структура иерархических баз данных

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

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

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

Сетевая база данных, структура сетевых баз данных

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

Мастер Йода рекомендует:  Лучший видеокурс по шаблонам проектирования

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

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

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

Был когда-то такой математик – Эдгар Франк Кодд, умерший в 2003 году, который в восьмидесятых годах очень подробно описал структуру реляционных баз данных математическим языком. А если есть хорошо написанная математика, то соответственно есть и программная реализация. Останавливаться на биографии Э.Ф. Кодда я не буду, для этого есть различные энциклопедии. Именно благодаря Кодду реляционные базы данных стали активно развиваться. Поэтому-то, когда мы говорим базы данных, чаще всего мы подразумеваем именно реляционные базы данных.

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

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

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

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

Проектирование базы данных

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

  1. База данных должна быть как можно более компактна, то есть, неизыбыточна.
  2. База данных должна быть простой с точки зрения обработки.

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

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

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

Блог программиста

Практически любой современный сайт работает с базами данных, если не считать различные продающие страницы, так называемые одностраничники (landing page). И если в случае готовых CMS база данных формируется автоматически при установке системы, то при разработке сайта с нуля БД необходимо проектировать, а для этого есть удобный и мощный инструмент — MySQL Workbench.

Говоря о разработке сайта с нуля, я имею в виду не только сайты на голом PHP, но и на тех же фреймворках, например, Yii-фреймворк. Конечно, и в случае готовых CMS часто приходится корректировать БД и добавлять ее новыми таблицами, но тем не менее основа там уже реализована.

Примечание: в этом посте я говорю про MySQL Workbench исключительно в контексте разработки сайтов. Однако никто вам не запрещает использовать программу для проектирования баз данных для прикладного ПО.

Начало работы

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

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

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

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

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

Это лишь краткий список основных возможностей, подробней о которых я расскажу чуть ниже. А сейчас — пару слов об установке MySQL Workbench.

Как установить MySQL Workbench на Windows

Тут особых хитростей нет. Заходите на оффициальный сайт (ссылка указана выше), качаете установочный файл (MSI Installer) и ставите. При установке просто следуйте инструкциям, там всё просто.

Как установить MySQL Workbench на Linux

Здесь может быть чуть сложнее, если вы начнете читать многочисленные мануалы в интернете. На самом деле, столько способов описывают, и автор каждого явно хочет усложнить эту задачу. Я плюнул на все эти способы и поступил намного проще. Открываете Центр приложений, вводите в поиск «MySQL Workbench» и устанавливаете найденную программу.

На момент написания статьи у меня стоит Linux Ubuntu 14.04 LTS, установка Workbench прошла без ошибок, все компоненты исправно работают. Поэтому если у вас будут какие-то проблемы или вопросы, то указывайте в комментариях, какую ось используете.

Примечание: если будете скачивать через установку архива, скаченного с официального сайта, то обратите внимание на версию MySQL Workbench. Дело в том, что в случае с Ubuntu 14.04 в настоящее время нет версии для ОС для 32-разрядного процессора, только для 64. Поэтому либо ищите там же на сайте прежние версии программы, либо можете попробовать установить версию для Ubuntu 12.04.

На этом установка закончена. Запускаем приложение и приступаем к работе.

Создание новой модели данных

Для создания новой модели выбери в меню File->New Model или нажмите на плюсик внизу в списке всех моделей. Появится окно для построения таблиц, представлений, схем, ролей, скриптов и т.д. Несмотря на всю эту автоматизацию, я всё равно сначала примерно набрасываю список таблиц на бумаге, опираясь на предметную область, на поставленную задачу и специфику проекта. После этого создаю таблицы в Workbench, а затем уже составляю диаграмму.

Для создания новой таблицы на вкладке физической схемы (Physical Schemas) выберите «Add Table» и заполняйте поля — название таблицы, сравнение, движок и комментарий к таблице.

Далее переходим на вкладку «Columns» и создаем поля таблицы. Я не вдаюсь в подробности этого процесса, тут всё просто — вводите название поля, тип данных, параметры и комментарий к нему. Если не знаете, какой тип лучше использовать для тех или иных полей, то лучше сначала почитайте книги по БД, хотя бы одну, вместо того, чтобы делать абы как. На Хабре на этот счет тоже не мало статей.

Примечание: При выборе типа данных для поля изначально не указывается нигде поля для задания размерности. Но это не значит, что этого сделать нельзя. Советую указывать длину, не стоит отмечать тип поля просто INT, например. Укажите в скобках его размер (к примеру, INT (11)). Т.е. выбираете типа данных и в скобках уже сами дописываете его размер.

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

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

Я не рассматриваю создание представлений, поскольку там нет никаких сложных форм. Кликните «Add View», откроется вкладка нового представления, в которой будет вставлен SQL-редактор. В нем пишите уже код для создания любого нужного вам представления.

Создание диаграммы БД

Для создания диаграммы используем меню Model->Add Diagram (Ctrl+T) либо на вкладке «EER Diagrams» кликаем по кнопке добавления новой диаграммы. У вас откроется клетчатое поле для рисования. Выбираем слева в списке таблиц нужные таблицы и перетаскиваем их мышью на эту клетчатую область. В результате сразу же будет построена полная диаграмма этих таблиц с учетом созданных нами ранее связей.

Вот и всё рисование. Ничего сложного.

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

Синхронизация с сервером

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

И так, выбираете в меню Database->Synchronize Model… и в появившемся окне вводите параметры вашего сервера. Я показываю на примере локального сервера (какой, кстати, вариант сервера вы при этом используете — не важно, у меня установлен XAMPP).

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

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

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

Заключение

Я показал здесь основные моменты в работе с MySQL Workbench. По крайней мере, этого вполне хватает, чтобы проектировать базы данных для проектов различной сложности. В своей работе я использую программу в основном только в тех случаях, когда требуется проектировать с нуля всю базу данных. Если же требуется создать 1-2 небольших таблички, то иногда можно быстрее сделать через phpMyAdmin или аналогичные инструменты. Вопрос выбора лишь в сложности таблиц и самой базы.

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

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

Быстрый старт: Визуальное проектирование базы данных в MySQL Workbench

Цель данного поста — помочь начинающему разработчику быстро освоится и спроектировать простенькую базу с помощью инструмента для визуального проектирования баз данных MySQL Workbench от компании Oralce и получить её ER-модель и SQL-дамп.

Ну что же, меньше слов да больше смысла! Внешний вид окна программы, раздел «Моделирование данных» выглядит так:

Для того, чтобы открыть существующую модель надо нажать на ссылку: Open Existing EER Model, для создания новой модели – выбрать параметр: Create New EER Model, чтобы создать модель «сущность-связь» из существующей базы данных – нажать на параметр: Create EER Model From Existing Database, а для создания EER модели из SQL скрипта нужно выбрать: Create EER Model From SQL Script.
Для создания новой модели, воспользуемся ссылкой Create New EER Model, после нажатия на неё отобразится окно с параметрами:

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

Сначала создадим таблицу users, которая будет хранить данные о пользователях информационной системы, в поле table Name впишем имя таблицы, в разделе формы Columns создадим поля таблицы:
— Первое поле id будет содержать уникальный номер пользователя, зададим ему свойства: Auto Increment, Not Null, Primary key и Unique, в разделе Data type выберем целочисленный тип integer.
— Второе поле fio, где будет хранится Ф.И.О. пользователя, установим полю свойства: Not Null, Primary key,, в разделе Data type выберем строковый тип VARCHAR и зададим количество символов в 255.
— Третье поле login, будет содержать логин пользователя, оно должно быть уникальным, как и поле id, поэтому установим ему свойство Unique и зададим количество символов в 255.
— Следующие поля: password содержащее пароль, e_mail содержащее адрес электронной почты и поле type содержащее тип пользователя будут без особых свойств, со строковым типом VARCHAR длинной в 255 символов, за исключением последнего поля type которому хватит с 45 символов.
После проделанных манипуляций форма с именем таблицы users будет выглядеть так:

На диаграмме появится таблица users c полями и индексами:

Аналогичным способом создадим таблицу settings с настройками доступа к базе данных ИС, содержащую поля id, host для указания имени хоста (адреса сервера), db – имени базы данных, user и password с именем пользователя и паролем, для установки ИС на удалённый сервер.

Далее по уже известному методу создадим таблицу shops которая будет хранить данные о магазинах в полях: id типа integer – ключевое, ненулевое, уникальное с автоинкрементом, поле name хранящее название магазина, поле address – его физический адрес, поле tel – телефон магазина, site – интернет сайт магазина и поле email с электронным адресом магазина.

Затем создадим таблицу products хранящую данные о продукции магазина в полях: id типа integer – ключевое, ненулевое, уникальное с автоинкрементом, поле name хранящее название магазина, ключевое, ненулевое поле целочисленного типа shop_id хранящее номер магазина, поле type_id с информацией о номере товара из таблицы видов продукции. Поле brand – брэнд изготовителя длинной в 255 символов, поле model – с моделью товара, поле data – с данными и характеристиками товара типа Tinytext, поле img с полным адресом до изображения товара длинной в 255 символов, и поля price с ценой товара и warranty с информацией о сроках гарантии на товар длинной в 45 символов.

Созданные нами таблицы settings, shops и products выглядят следующим образом:

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

Вид таблицы таков:

Последние две таблицы это orders и deliveries, первая содержит информацию о заказах клиентов, а последняя данные о доставке продукции.

Поля таблицы orders: id ключевое, ненулевое, уникальное поле целочисленного типа с автоинкрементом, поле shop_id содержащее номер магазина – ключевое, ненулевое целочисленного типа, поле product_id хранящее номер продукта – ключевое, ненулевое целочисленного типа, поле fio с номером пользователя совершившего заказ — ключевое, ненулевое целочисленного типа, поле date c датой заказа – типа DATE, поле quantity с количеством заказанных товаров – целочисленного типа, поле tel с номером телефона заказчика – строкового типа длинной в 255 символов и поле confirm содержащее информацию о подтверждении заказа – логического типа.

Поля таблицы deliveries: order_id с номером заказа — ключевое, ненулевое, уникальное поле целочисленного типа с автоинкрементом, поле поле fio с номером пользователя совершившего заказ — ключевое, ненулевое целочисленного типа, поле address хранящее адрес доставки товара указанный клиентом – строкового типа длинной в 255 символов, поле time хранящее желаемое время доставки товара – строкового типа длинной в 255 символов, поле date с датой совершения заказа клиентом – типа DATE и поле логического типа confirm хранящее информацию о доставке товара.

Мастер Йода рекомендует:  На каких движках делать сайты

Таблицы orders и deliveries выглядят так:

Мы создали базу данных, состоящую из семи таблиц, теперь необходимо связать таблицы, мы уже создали ключевые поля целочисленного типа, они и станут основой для связывания.
Для того чтобы связать две таблицы к примеру products и product_type, необходимо дважды щёлкнуть левой кнопкой мыши на диаграмму с таблицей products и выбрать вкладку Foreign keys (внешние ключи), далее в поле Foreign key name ввести уникальное имя внешнего ключа, дважды щёлкнуть по вкладке Referenced table и выбрать таблицу product_type, затем в форме расположенной правее выбрать ссылающееся поле type_id и выбрать в всплывающем списке поле id.

Таким образом, оба поля таблицы оказываются связанны, затем нужно задать тип отношений связи между таблицами, откроем окно, кликнув на появившуюся связь между таблицами, и выберем вкладку Foreign Key и в разделе Cardinality выберем тип связи один ко многим, и закроем окно. На диаграмме отобразится связь таблиц:

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

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


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

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

Большинство таблиц находятся в отношении один-ко-многим, за исключением таблиц deliveries и orders находящихся в отношении одни-к-одному, т.к. доставлен, может быть только одни заказ, т.е. у одного заказа только одна доставка. Остальные связи наглядно указаны выше.

Теперь загрузим нашу базу данных на сервер. Для этого создадим новое подключение к базе данных, щёлкнув по ссылке New connection в стартовом окне программы:

Затем заполним поля в открывшимся окне:

Укажем имя соединения в поле Connection Name, выберем метод соединения в списке Connection Method, зададим имя хоста и порт во вкладке Parameters, укажем имя пользователя и пароль если он есть и нажмём на кнопку ОК. Затем откроем вкладку EER Diagram, в панели выберем пункт Database и нажмём на параметр Forward Engineer:

После того как появится окно, нажимаем на кнопку “Next”, выбираем параметр Export MySQL Table Objects и нажимаем на кнопку “Next”:

После нажатия кнопки появится вкладка с SQL кодом, можно сохранить его нажав кнопку “Save to file” если это необходимо, а затем нажать на кнопку “Next”. Появится окно с параметрами соединения:

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

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

Проектирование информационных систем на базе MySQL и Internet (стр. 1 из 3)

Министерство образования Российской Федерации

Нижегородский Государственный Технический Университет

Нижегородский Вечерний Факультет

Кафедра: Компьютерные технологии в проектировании и производстве

Курсовая работа

«Проектирование информационных систем»

Проектирование информационных систем

2 Анализ задания

3 Проектирование базы данных

4 Разработка CGI-программ и HTML-документов

5 Схема размещения информации на Internet узле

6 Контрольный пример

8 Список литературы

Разработать информационную систему для реализации конкурсного отбора кадров на базе программного обеспечения MySQL и Internet.

Разрабатываемая система позволяет предприятию через Internet объявить конкурс на имеющиеся у нее вакантные должности, собирать и хранить сведения об участниках конкурса в виде заполняемых ими анкет.

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

Проектирование базы данных

Реализация базы данных с помощью MySQL.

После запуска MySQL в командной строке создаем базу данных Konti:

C:\mysql\bin\mysqladmin create Konti

После этого в созданной базе создаем таблицы t1 и t2:

create table t1(prof varchar(30) not null primary key, knkrs int not null, ed varchar(30) not null, cpc varchar (30) not null, crok int not null);

Поле “prof” — является ключевым и содержит наименование вакансии.

Поле “knkrs” — содержит количество анкет, заявленных на данную вакансию.

Поле “ed” — содержит сведения о необходимом образовании претендента на данную вакансию.

Поле “cpc” — содержит сведения о необходимой специализации претендента на данную вакансию.

Поле “crok” — содержит сведения о необходимом опыте работы претендента по своей специализации.

create table t2(fio varchar(30) not null primary key, eda varchar(30) not null, cpca varchar(30) not null, croka int not null, adress varchar(60) not null, vak varchar(30) not null);

поле “fio” (ключевое поле) – фамилия имя и отчество претендента;

поле “eda” – образование претендента;

поле “cpca” – специальность претендента;

поле “croka” – срок работы претендента по специальности;

поле “adress” – адрес претендента;

поле “vak” – выбранная вакансия претендента;

Разработка CGI -программ и HTML -документов

Файл Kontinental-HH.htm — главная страница.

Содержит две кнопки:

«Просмотр вакансий» – запускает скрипт list.cgi и предназначена для пользователя информационной системы — предоставляет ему возможность ознакомиться со списком вакансий на предприятии, узнать текущий конкурс на нее и заполнить анкету;

«База данных” (пароль) – запускает скрипт BDvakans.cgi и предназначена для администратора — предоставляет ему возможность вносить в базу данных новые вакансии, удалять занятые, а также редактировать список присланных анкет, отбирая наиболее перспективные для деятельности предприятия.

ОАО «Континенталь-НН» &reg

Отдел кадров

ПРОФЕССИОНАЛЬНЫЙ ПОДХОД К ЧЕЛОВЕЧЕСКИМ РЕСУРСАМ

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

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

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

Визуальное создание баз данных с помощью MySQL Workbench

    Марья Тулупова 4 лет назад Просмотров:

1 Визуальное создание баз данных с помощью MySQL Workbench Author: Pablo Pastor on Apr 14th 2010 Translated by: Jevgeni Agapitov 20th March 2011 Содержание Визуальное создание баз данных с помощью MySQL Workbench. 1 Что такое MySQL Workbench. 2 Планирование базы данных. 3 Отправляемся в MySQL Workbench. 4 Уточнение концепции. 5 Создание таблиц. 6 Переход в визуальный режим. 7 Рисование отношений. 8 Создание кода SQL. 11 Подключение к серверу MySQL. 13 Заключение. 14

2 Что такое MySQL Workbench? MySQL Workbench является мощным инструментом, разработанным для решения трех основных задач: Разработка на языке SQL: Позволяет пользователю подключаться к существующей базе данных для редактирования и выполнения SQL запросов. Моделирование данных: полное визуальное проектирование и моделирование баз данных. Администрирование баз данных: Заменяет программу администрирования mysql. Имеющийся графический интерфейс служит для запуска / остановки серверов, создания учетных записей, редактирования конфигурационных файлов и т.д. В этом учебном пособии мы сосредоточимся на аспекте моделирования данных, создадим базу данных с нуля, а затем только бегло познакомимся с редактором выражений SQL, чтобы выполнить наш сценарий SQL и создать базу данных на сервере MySQL. Программа MySQL Workbench доступна для Windows, Linux и Mac OSX. Существуют два варианта программы: свободный Community OSS Edition и коммерческий Standard Edition. Community Edition, как и следовало ожидать, поставляется с открытым исходным кодом и по лицензии GPL. Это полностью функциональная программа, и именно ее мы будем использовать в этом учебном пособии. В коммерческом варианте добавлены некоторые дополнительные функции, такие как проверка схемы и модели и автоматизированное создание документации.

3 Планирование базы данных Для изучения MySQL Workbench мы будем использовать в качестве примера очень простую базу данных для дистанционного обучения. Пусть группа учителей хочет предложить дистанционное обучение на различные темы, используя Skype или любое другое программное обеспечение для видеоконференций. Будем считать, что в нашем небольшом проекте мы должны хранить следующую информацию: При разработке схемы мы должны знать отношения между этими группами данных, так что лучше подумать об этом сейчас! Один учитель может преподавать многие предметы Один предмет может преподаваться многими учителями Каждого курс преподает только один учитель Один учитель может преподавать много курсов Один студент может изучать много курсов Один курс изучает много студентов Один курс может иметь несколько часов (в неделю) В какой-то конкретный день и час может быть несколько курсов В одном курсе изучается один предмет Один предмет может изучаться во многих к курсах Теперьу нас есть все данные, необходимые для решения задачи

4 Отправляемся в MySQL Workbench Пришло время запустить MySQL Workbench. В разделе моделирования данных на главном экране нажмите на ссылку Create new EER Model (Создать новую EER модель), и появится следующее окно: Когда мы создаем новую модель базы данных, она содержит схему по умолчанию MyDB. Мы можем переименовать ее и использовать в качестве нашей схемы БД. Модель базы данных может иметь несколько различных схем. Каталог справа показывает все элементы нашей схемы и позволяет в случае необходимости перетаскивать элементы на диаграмму. Наличие отдельных разделов для физической схемы и диаграммы EER и возможность включать несколько схем в одну модель базы данных может привести к путанице. В следующем разделе будут объяснены эти понятия и связь между ними.

5 Уточнение концепции Физическая схема содержит все необходимое для определения базы данных: таблицы, столбцы, типы, индексы, ограничения и т.д. Это то, что мы действительно определяем. Каждый объект, добавленный в графическую модель, также показывается в физической схеме. Это, по сути, визуальный способ определить наши схемы. Мы можем иметь несколько схем для одной и той же модели базы данных, так же мы можем иметь несколько баз данных на сервере MySQL. Каждая схема будет базой данных MySQL. Например, на следующем экране у нас есть две закладки схем: Если мы сгенерируем сценарий SQL для создания базы данных, у нас будет два отдельных выражения CREATE DATABASE — на самом деле в сценарии будут выражения CREATE SCHEMA, которые являются просто синонимами. CREATE SCHEMA IF NOT EXISTS `schema1`; CREATE SCHEMA IF NOT EXISTS `schema2`; Эти две схемы будут показаны как базы данных на сервере MySQL, если использовать выражение SHOW DATABASES. Теперь, что такое EER диаграммы? EER обозначает Extended (or Enhanced) Entity- Relationship (расширенная диаграмма сущность-связь). EER диаграммы просто способ моделирования данных и отношений между данными с использованием стандартных символов. EER модели могут быть достаточно сложными, но MySQL Workbench использует только подмножество всех возможных графических элементов, потому что целью такой диаграммы (в этой программе) является, чтобы каждый элемент отображался в физической схеме. Мы можем использовать EER диаграммы для определения всей базы данных или только ее частей. Например, у нас имеется схема с пятью таблицами, затем мы можем создать новую диаграмму, чтобы определить с помощью визуального редактора еще две таблицы. Схема будет содержать только две таблицы, но эти две таблицы также будут включены в схему вместе с пятью предыдущими.

6 Создание таблиц Вернемся к нашему примеру. Мы должны переименовать схему по умолчанию, дважды щелкнув имя mydb. Теперь у нас есть две возможности продолжить работу: мы можем начать добавлять таблицы в физическую схему (на вкладке Model) с помощью значка Add Table или мы можем открыть вкладку EER Diagram и добавить все таблицы на ней. Я предпочитаю с самого начала добавить новую диаграмму и визуально создавать на ней таблицы, однако для того, чтобы показать, как сделать это обоими методами, мы создадим первые две таблицы на вкладке Model, а затем продолжим на вкладке EER Diagram. При нажатии на кнопку Add Table (Добавить таблицу), редактор таблиц откроется в виде вкладки как показано ниже: Используя редактор таблиц, изменим имя таблицы и перейдем на вкладку Columns (на вкладках внизу редактора) для доступа к столбцам. Мы можем выбрать тип данных (есть выпадающий список всех типов данных MySQL), присвоить, если это необходимо, значение по умолчанию, и у нас есть семь флажков, чтобы отметить какое-либо из следующих свойств: PK Primary key (первичный ключ) NN Not null (не null, т.е. значение должно быть определено) UQ Unique (уникальное значение) BIN Binary (двоичное значение) UN Unsigned (беззнаковое значение) ZF Zero fill (заполнено нулями) AI Autoincrement (автоинкремент автоматическое увеличение на 1)

7 Переход в визуальный режим Это еще один из способов добавления таблиц, хотя мы также можем создавать их с помощью диаграмм. Если сейчас мы нажмем на значок Add Diagram (Добавить диаграмму), откроется новая, пустая вкладка диаграмм, и это не то, что мы хотим. Мы хотим, чтобы две таблицы, которые мы только что создали, были на диаграмме. Перейдите в меню и выберите Model/Create Diagram from Catalog Objects (Модель/Создание диаграммы из каталога объектов), теперь у нас есть схема, и мы готовы продолжать. Выберите слева значок таблицы; указатель примет форму руки с таблицей. Затем щелкните в любом месте холста, чтобы создать новую таблицу. Теперь вы просто должны дважды щелкните таблицу, и в редакторе появится вкладка для изменения имени, столбцов, типов и т.д. — так же, как это делалось раньше. После ввода деталей столбца для новых таблиц, мы будем готовы начать рисовать отношения.

8 Рисование отношений В вертикальной панели инструментов слева есть шесть инструментов, доступных для создания отношений. Не беспокойтесь о последнем, мы объясним его назначение позже. Для отношений 1:1 и 1: n, мы имеем два различных типа символов: идентифицирующие и неидентифицирующие. Что это значит? Отношение считается идентифицирующим, когда одна таблица полностью зависит от других, и не может существовать без них. Строка в такой таблице зависит от строки в другой таблице. Типичным примером является создание отдельной таблицы для хранения телефонов пользователей. Их необходимо хранить в другой таблице, потому что у одного пользователя может быть несколько телефонов, но каждая строка в этой таблице, полностью зависит от пользователя — она относится к пользователю. Вы должны знать, что отношения бывают определенных типов. Если мы хотим создать физические таблицы в MySQL, то отношения между таблицами должны быть каким-то образом отображены. Есть несколько правил, которые определяют отношения между таблицами: отношение 1:1. Первичный ключ для одной из таблиц включен в качестве внешнего ключа в другой таблице. отношение 1:n. Первичный ключ из таблицы 1 добавляется в качестве внешнего ключа в таблицу n. отношение n:m. Создается новая таблица (таблица связи). Первичный ключ состоит из первичных ключей двух оригинальных таблиц. Выявление отношения обычно используются для создания связующих таблиц для отношения «многие-ко-многим». Эти новые таблицы полностью зависят от двух оригинальных таблиц. Кроме того, в случае идентифицирующих отношений 1:1 и 1: n введенный внешний ключ становится частью первичного ключа для этой таблицы, образуя составной первичный ключ. Хорошим является то, что MySQL Workbench знает эти правила лучше, чем большинство из нас. Мы просто рисуем линии отношений, и внешние ключи или таблицы связей будут создаваться автоматически. Как мы убедимся в ближайшее время, это также можно сделать вручную. Чтобы нарисовать отношение, щелкните по значку, а затем по двум таблицам, которые необходимо связать. Для отношения «один-ко-многим» щелкните вначале по таблице «многие», а затем по таблице «один». Давайте посмотрим, как сделать это для отношения n:m учителя-предметы и для отношения 1:n учителя-курсы.

9 Имя по умолчанию, назначаемое для внешних ключей, а также для таблиц связи может быть изменено на глобальном уровне в Edit/Preferences/Model Tab, или только для данного проекта в Model/Model Options. Если мы не хотим, чтобы таблицы и внешние ключи были созданы таким образом, мы можем использовать таинственный «шестой символ». «Шестой символ» создает связь с использованием уже существующих колонок, т.е. вы уже включили необходимые внешние ключи в таблицы и создали необходимые таблицы связи (таблицы, отображающие отношение n:m). Поскольку таблицы связи уже созданы, нам не нужно отношение n:m, доступно только отношение 1: n. Когда все отношения определены, наша схема должна выглядит так:

10 Помните, что для диаграмм используются обозначения по умолчанию MySQL Workbench, но вы можете их изменить в Model/Object Notation и Model/Relationship Notation. Это пример нашей модели с классическими обозначениями: На данный момент наша модель готова, и мы можем сгенерировать код SQL для создания базы данных MySQL.

11 Создание кода SQL Выберите File/Export/Forward Engineer SQL CREATE Script. Для генерации файла с кодом SQL понадобятся только три экрана мастера! У нас даже есть возможность просматривать и редактировать сгенерированный код SQL перед его сохранением:

12 И это все. Щелкните по кнопке Finish, скрипт будет создан и сохранен. Теперь мы можем использовать его когда захотим. Например, мы можем загрузить его с помощью командной строки MySQL клиента: mysql> SOURCE scriptname.sql Или, чтобы закончить работу, можно использовать MySQL Workbench, подключившись к серверу MySQL и запустив сценарий.

13 Подключение к серверу MySQL Выбирете Database/Manage Connections, и щелкните по кнопке New. Если вы не установите пароль здесь, то позже при необходимости вам будет предложено это сделать. Нажмите кнопку «Test Connection (Проверить подключение)», чтобы проверить, верны ли параметры, а затем нажмите кнопку «Close (Закрыть)». Теперь, чтобы загрузить сценарий, мы будем использовать редактор SQL. В главном меню выберите Database/Query Database; в появившемся окне вам будет предложено выбрать подключение, а затем на вкладке откроется редактор SQL. Теперь, чтобы выполнить сценарий SQL, щелкните значок молнии, и ваша база данных будет создана! Мы могли бы также сформировать базу данных MySQL непосредственно из модели, без ссылки на сам файл, используя меню Database/Forward Engineer, однако, я считаю полезным создать сценарий, а затем использовать его как хочется.

14 Заключение MySQL Workbench является мощной программой. Мы просмотрели только несколько основных возможностей в части моделирования данных, и заглянули в редактор SQL только во второй половине этого учебного пособия. Мы узнали, как визуально создавать базу данных и рисовать диаграммы, которые могут храниться в качестве документации. Вы можете экспортировать диаграммы как PNG, SVG, PDF или PostScript файл. Спасибо за чтение, и дайте мне знать, что вы думаете!

Основы проектирования БД в MySQL

Мало какое веб-приложение сегодня не работает с базой данных. При этом можно однозначно сказать, что самая популярная в мире веб-приложений СУБД — MySQL. В том числе потому, что современные инструменты работы с MySQL позволяют даже абсолютным новичкам работать с этой СУБД почти так же просто, как с таблицами в Excel. В то же время, это более сложная система и уже с начала работы с ней будет весьма полезно понимать некоторые важные принципы и практические нюансы.

Например — что такое нормализация данных и почему в большинстве случаев имеет смысл применять 3НФ (третью нормальную форму). Если использование 3НФ не является для вас естественной привычкой — вам точно стоит посетить этот вебинар. В прямом эфире будут разобраны эти и другие темы на примере проектирования БД для небольшого интернет-магазина.

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

Все возможности веб-разработки в самой полной профессии от GeekBrains — «Веб-разработчик».

Проектирование баз данных

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

Проектирование баз данных

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

Мастер Йода рекомендует:  Сколько стоят операции над list, set и dict в Python Разбираемся с временной сложностью

Таблица 2-1. База данных CD, состоящая из одной таблицы

Stevie Wonder Talking Book Motown You Are the Sunshine of My Life, Maybe Your Baby, Superstition, . . .

(Для краткости мы опустили большую часть -песен.) На первый взгляд, эта таблица нам подходит, поскольку в ней есть все необходимые данные. При более близком рассмотрении, однако, мы сталкиваемся с некоторыми проблемами. Возьмем, к примеру, Herbie Hancock. Название ансамбля повторяется дважды — для каждого CD. Это повторение неприятно по нескольким причинам. Во-первых, при вводе данных нам приходится вводить одно и то же несколько раз. Во-вторых, что более важно, при изменении каких-либо данных приходится изменять их в нескольких местах. Что если, к примеру, в Herbie вкралась орфографическая ошибка? Пришлось бы исправлять данные в двух строках. Та же проблема возникнет, если имя Herbie Hancock в будущем изменится (а ля Jefferson Airplane или John Cougar). С добавлением к нашей коллекции новых дисков Herbie Hancock увеличивается объем работы, необходимой для поддержания непротиворечивости данных.

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

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

Сущности в базе данных

Сущность — это важная вещь или объект, сведения о котором нужно сохранить. Не все вещи являются сущностями, а только те, данные о которых должны быть сохранены. Сведения о сущностях имеют вид атрибутов и/или связей. Если некий кандидат на то, чтобы быть сущностью, не имеет атрибутов или связей, в действительности он не является сущностью. В модели базы данных сущности представляются в виде прямоугольника с заголовком. Заголовок является именем сущности.

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

NULL лежит в основе проблемы, связанной с отсутствующей информацией. Он специально используется тогда, когда какая-то часть данных отсутствует. Рассмотрим, к примеру, ситуацию, когда на CD нет данных о длительности каждой песни. У каждой песни есть длительность, но, глядя на коробку, вы не можете сказать, какова она. Хранить длительность как О нежелательно, поскольку это было бы неверно. Вместо этого вы записываете длительность как NULL. Если вы считаете, что можно сохранить ее как 0 и использовать 0 для обозначения «неизвестной длины», то можете попасть в одну из тех западней, которые привели к проблеме 2000-го года. В старых системах не только год хранится как две цифры, но и придается особое значение величине 9-9-99.

В нашем примере база данных ссылается на ряд объектов — CD, название CD, название ансамбля, песни и название фирмы звукозаписи. Какие из них являются сущностями, а какие — атрибутами?

Обратите внимание, что мы определяем несколько видов данных (название CD, название ансамбля и т. д.), относящихся к каждому CD, и без которых описать CD совершенно невозможно. Поэтому CD является одним из тех объектов, которые мы хотим описать, и, похоже, является сущностью. Начнем разработку модели данных с изображения CD как сущности.
По общепринятому соглашению об именовании сущностей имя сущности должно быть в единственном числе. Поэтому мы называем таблицу, в которой хранятся CD «CD», а не «CDs». Мы используем это соглашение, поскольку каждая сущность дает имя экземпляру. Например, «San Francisco 49ers» является экземпляром сущности «Футбольная команда», а не «Футбольные команды».

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

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

Е. Ф. Кодд (Е. F. Codd), занимавшийся исследовательской работой в IBM, впервые представил концепцию нормализации в нескольких важных статьях, написанных в 1970-е годы. Задача нормализации остается той же самой и сегодня: устранить из базы данных некоторые нежелательные характеристики. В частности ставится задача устранить некоторые виды избыточности данных и благодаря этому избежать аномалий при изменении данных. Аномалии изменения данных — это сложности при операциях вставки, изменения и удаления данных, возникающие из-за структуры базы данных. Дополнительным результатом нормализации является конструкция, хорошо соответствующая реальному миру. Поэтому в результате нормализации модель данных становится более ясной.

Например, предположим, что мы ошиблись при вводе «Herbie Hancock» в нашу базу данных и хотим исправить ошибку. Нам потребовалось бы рассмотреть все диски этого исполнителя и исправить имя. Если изменения производятся с помощью приложения, позволяющего одновременно редактировать только одну запись, нам придется редактировать много строк. Было бы гораздо лучше запомнить имя «Herbie Hancock» лишь один раз и редактировать его в одном месте.

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

Общее понятие нормализации подразделяется на несколько «нормальных форм». Говорят, что сущность находится в первой нормальной форме, когда все ее атрибуты имеют единственное значение. Чтобы признать сущность находящейся в первой нормальной форме, нужно удостовериться в том, что каждый атрибут сущности имеет единственное значение для каждого экземпляра сущности. Если в каком-либо атрибуте есть повторяющиеся значения, сущность не находится в 1NF.

Вернувшись к нашей базе данных, мы обнаруживаем, что повторяющиеся значения есть в атрибуте Song (песня), поэтому очевидно, что база не находится в 1NF. Сущность с повторяющимися значениями указывает на то, что мы упустили еще по крайней мере одну сущность. Обнаружить другие сущности можно, взглянув на каждый атрибут и задавшись вопросом «что описывает эта вещь?»

Что описывает атрибут Song? Он перечисляет все песни на CD. Поэтому Song — это еще один объект, о котором мы собираем данные, и, возможно, он является сущностью. Мы добавим его в свою диаграмму и придадим атрибут Song Name (название песни). Чтобы покончить с сущностью Song, спросим себя, чем еще мы хотели бы ее охарактеризовать. Мы отметили ранее, что длительность песни мы также хотели бы сохранить.
Теперь, когда Song Name и Song Length являются атрибутами сущности Song, мы имеем модель данных с двумя сущностями в 1NF. К сожалению, мы не указали никакого способа связать вместе CD и Song.

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

  • Он уникален для каждого экземпляра сущности.
  • Для каждого экземпляра сущности он имеет значение, отличное от NULL в течение всего срока существования экземпляра.
  • В течение всего времени существования экземпляра его значение не меняется.

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

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

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

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

У каждой стороны связи есть имя, описывающее связь. Возьмем две гипотетические сущности — Служащий и Отдел. Один вариант связи между ними состоит в том, что Служащий «приписан» к Отделу. Этот Отдел «отвечает» за Служащего. Таким образом, связь со стороны Служащий называется «приписан», а со стороны Отдел — «отвечает».

Степень, называемая также кардинальным числом, показывает, сколько экземпляров описывающей сущности должны описывать один экземпляр описываемой сущности. Степень выражается с помощью двух разных значений- «один-к-одному» (1) и «один-ко-многим» (М). Служащий приписан одновременно только к одному отделу, поэтому у сущности Служащий связь с сущностью Отдел «один-к-одному». В обратном направлении, отдел отвечает за многих служащих. Поэтому мы говорим, что у сущности Отдел связь с сущностью Служащий «один-ко-многим». В результате в Отделе может быть и только один Служащий.

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

сущность1имеет [одну и только одну одну или много] сущностъ2

Согласно этой формуле связь между Служащим и Отделом можно выразить так:

Каждый Служащий должен быть приписан к одному и только одному

Каждый Отдел может отвечать одному или многим Служащим.

Можно использовать эту формулу для описания сущностей в нашей модели данных. В каждом CD содержится много или одна Song, и каждая Song содержится хотя бы в одном CD. В нашей модели данных эту связь можно показать, проведя линию между двумя сущностями. Степень обозначается прямой линией для связи «один и только один» и «птичьей лапой» для связи «один-ко-многим>>. На рис. 2-5 показаны эти обозначения.

Как это применимо к связи между Song и CD? На практике Song может содержаться на многих CD, но для нашего примера мы этим пренебрежем.

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

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

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

В нашем примере «Herbie Hancock» является Band Name (названием ансамбля) для двух разных CD. Это показывает, что Band Name не полностью зависит от идентификатора CD ID. Это дублирование представляет собой проблему, поскольку если мы допустили ошибку при вводе «Herbie Hancock», придется исправлять значение в нескольких местах. Это указывает нам, что Band Name должно быть частью новой сущности, связанной с CD. Как и раньше, мы решаем эту задачу, задав вопрос: «Что описывает название ансамбля?» Оно описывает ансамбль, или, вообще говоря, исполнителя. Исполнитель — еще один объект, о котором мы собираем данные, и потому, возможно, является сущностью. Мы добавим его к нашей схеме с атрибутом Band Name. Поскольку исполнитель может не быть ансамблем, мы переименуем атрибут как Artist Name. На рис. 2-7 показано новое состояние модели.

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

Вначале мы присвоили атрибут Band Name сущности CD. Поэтому было естественным установить прямую связь между Artist и CD. Но верно ли это? При ближайшем рассмотрении оказывается, что следует установить прямую связь между Artist и Song. У каждого Artist есть одна или много Song. Каждая Song исполняется одним и только одним Artist. Правильные связи показаны на рис. 2-9.

Это не только более разумно, чем связь между Artist и CD, но и решает проблему дисков-сборников.

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

Если одна сторона имеет степень «один или много», а другая сторона имеет степень «один и только один», то это связь «один-ко-многим» или «1-к-М». Все связи в нашей модели — это связи «один-ко-многим». Этого можно было ожидать, поскольку связи «один-ко-многим» наиболее распространены.

И наконец, последний тип связей — когда обе стороны имеют степень «один-ко-многим». Такого типа связи называются «многие-ко-мно-гим», или «М-к-М». В предыдущей версии нашей модели данных связь Artist-CD имела тип «многие-ко-многим».

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

Связи «многие-ко-многим» встречаются чаще, чем «один-к-одному». В этих связях часто есть некоторые данные, которыми мы хотим охарактеризовать связь. Взглянем, например, на предыдущую версию нашей модели данных на рис. 2-8, в которой была связь «многие-ко-многим» между Artist и CD. Artist имеет связь с CD, поскольку у исполнителя есть одна или несколько Song на этом CD. Модель данных на рис. 2-9 фактически является другим представлением этой связи «многие-ко-многим».

Все связи «многие-ко-многим» нужно разрешать с помощью следующей технологии:

  1. Создайте новую сущность, иногда называемую сущностью-связкой. Назовите ее подходящим образом. Если вы не можете придумать подходящее название, образуйте его из сочетания имен связываемых сущностей, например ArtistCD. В нашей модели Song является сущностью-связкой для связи Artist-CD.
  2. Свяжите новую сущность с двумя исходными. Каждая из исходных сущностей должна иметь связь «один-ко-многим» с сущностью-связкой.
  3. Если в новой сущности нет очевидного уникального идентификатора, введите в нее идентифицирующие атрибуты исходных сущностей и сделайте эту пару уникальным идентификатором новой сущности.

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

Наша модель все еще не приобрела вторую нормальную форму. Значение атрибута Record Label (фирма звукозаписи) имеет только одно значение для каждого CD, но одно и то же значение его присутствует в нескольких СD. Ситуация сходна с той, которая была с атрибутом Band Name. И точно так же дублирование указывает на то, что Record Label должна быть частью отдельной сущности. Каждая Record Label выпускает один или много CD. Каждый CD выпускается одной и только одной Record Label. Модель этой связи представлена на рис. 2-10.

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

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

Если бы мы пожелали отслеживать адрес Record Label, то столкнулись бы с проблемами для третьей нормальной формы. В сущности Record Label должны быть атрибуты State Name (название штата) и State Abbreviation (сокращенное название штата). Хотя для учета CD эти данные и не нужны, мы добавим их к нашей модели для иллюстрации проблемы.

Значения State Name и State Abbreviation удовлетворяют первой нормальной форме, поскольку имеют только одно значение в каждой записи сущности Record Label. Проблема в том, что State Name и State Abbreviation взаимозависимы. Иными словами, поменяв State Abbreviation для какой-либо Record Label, мы вынуждены будем также изменить State Name. Мы произведем нормализацию, создав сущность State с атрибутами State Name и State Abbreviation. На рис. 2-12 показано, как связать эту новую сущность с сущностью Record Label.

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

Методология логического моделирования данных

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

  1. Выявить и смоделировать сущности.
  2. Выявить и смоделировать связи между сущностями.
  3. Выявить и смоделировать атрибуты.
  4. Указать уникальный идентификатор для каждой сущности.
  5. Провести нормализацию.

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

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

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

С какой целью мы создавали логическую модель данных? Вам нужно создать базу данных, чтобы хранить информацию о CD. Модель данных — это только промежуточный шаг. В конечном итоге вы хотели бы получить базу данных MySQL или mSQL, в которой можно хранить данные. Как это сделать? При проектировании физической базы данных логическая модель переводится в набор операторов SQL, которые определяют вашу базу данных MySQL или mSQL.

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

  1. Объекты становятся таблицами в физической базе данных.
  2. Атрибуты становятся колонками в физической базе данных. Для каждой колонки нужно выбрать подходящий тип данных.
  3. Уникальные идентификаторы становятся колонками, не допускающими значение NULL. В физической базе данных они называются первичными ключами (primary keys). Вы можете также пожелать создать уникальный индекс по идентификатору, чтобы обеспечивать уникальность. Учтите, что в mSQL нет понятия первичного ключа, есть просто уникальные индексы. К MySQL это не относится.
  4. Отношения моделируются в виде внешних ключей (foreign keys). Мы коснемся их позднее.

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

Таблица 2-2. Определения физических таблиц для базы, данных CD

Добавить комментарий