Перевод цикла из 15 статей о проектировании баз данных.
Информация предназначена для новичков.
Помогло мне. Возможно, что поможет еще кому-то восполнить пробелы.
SQL – большая тема для повествования и его рассмотрение выходит за рамки данного руководства. Данная статья строго сфокусирована на изложении процесса проектирования баз данных . Позднее, в отдельном руководстве, я расскажу об основах SQL.
Правила реляционной модели диктуют, как информация должна быть организована в таблицах и как таблицы связаны друг с другом. В конечном счете результат можно предоставить в виде диаграммы базы данных или, если точнее, диаграммы «сущность-связь», как на рисунке (Пример взят из MySQL Workbench).
РСУБД.
РСУБД, которую я использовал для создания таблиц примеров – MySQL. MySQL – наиболее популярная РСУБД и она бесплатна.
Утилита для администрирования БД.
После установки MySQL вы получаете только интерфейс командной строки для взаимодействия с MySQL. Лично я предпочитаю графический интерфейс для управления моими базами данных. Я часто использую SQLyog. Это бесплатная утилита с графическим интерфейсом. Изображения таблиц в данном руководстве взяты оттуда.
Визуальное моделирование.
Существует отличное бесплатное приложение MySQL Workbench. Оно позволяет спроектировать вашу базу данных графически. Изображения диаграмм в руководстве сделаны в этой программе.
В следующей части я коротко расскажу об эволюции баз данных. Вы узнаете откуда взялись базы данных и реляционная модель данных.
Так выглядели профессионалы в сфере информационных технологий в 70-е. (Слева внизу находится Билл Гейтс).
Текстовые файлы и сегодня все еще используются для хранения малых объемов простой информации. Comma-Separated Values (CSV) - значения, разделённые запятыми, очень популярны и широко поддерживаются сегодня различным программным обеспечением и операционными системами. Microsoft Excel – один из примеров программ, которые могут работать с CSV–файлами. Данные, сохраненные в таком файле могут быть считаны компьютерной программой.
Выше приведен пример того, как такой файл мог бы выглядеть. Программа, производящая чтение данного файла, должна быть уведомлена о том, что данные разделены запятыми. Если программа хочет выбрать и вывести категорию, в которой находится урок "Database Design Tutorial" , то она должна строчка за строчкой производить чтение до тех пор, пока не будут найдены слова "Database Design Tutorial" и затем ей нужно будет прочитать следующее за запятой слово для того, чтобы вывести категорию Software .
Компьютерная программа могла бы осуществить поиск в столбце tutorial_id данной таблицы по специфическому идентификатору tutorial_id для того, чтобы быстро найти соответствующие ему заголовок и категорию. Это намного быстрее, чем поиск по файлу строка за строкой, подобно тому, как это делает программа в текстовом файле.
Современные реляционные базы данных созданы так, чтобы позволять делать выборку данных из специфических строк, столбцов и множественных таблиц, за раз, очень быстро.
Сегодня доступен широкий выбор систем управления базами данных: от небольших десктопных приложений до многофункциональных серверных систем с высокооптимизированными методами поиска. Вот некоторые из наиболее известных систем управления реляционными базами данных (РСУБД):
- Oracle
– используется преимущественно для профессиональных, больших приложений.
- Microsoft SQL server
– РСУБД компании Microsoft. Доступна только для операционной системы Windows.
- Mysql
– очень популярная РСУБД с открытым исходным кодом. Широко используется как профессионалами, так и новичками. Что еще нужно?! Она бесплатна.
- IBM
– имеет ряд РСУБД, наиболее известна DB2.
- Microsoft Access
– РСУБД, которая используется в офисе и дома. На самом деле – это больше, чем просто база данных. MS Access позволяет создавать базы данных с пользовательским интерфейсом.
В следующей части я расскажу кое-что о характеристиках реляционных баз данных.
Используя структурированный язык запросов (SQL), данные из разных таблиц, которые связаны ключом, могут быть выбраны за один раз. Для примера вы можете создать запрос, который выберет все заказы из таблицы заказов (orders), которые принадлежат пользователю с идентификатором (id) 3 (Mike) из таблицы пользователей (users). О ключах мы поговорим далее, в следующих частях.
Столбец id в данной таблице является первичным ключом. Каждая запись имеет уникальный первичный ключ, часто число. Столбец usergroup (группы пользователей) является внешним ключом. Судя по ее названию, она видимо ссылается на таблицу, которая содержит группы пользователей.
Когда вы создаете таблицу базы данных вы предоставляете тип данных для каждого столбца. К примеру, varchar – это тип данных для небольших фрагментов текста с максимальным количеством знаков, равным 255, а int – это числа.
Помимо типов данных РСУБД позволяет вам еще больше ограничить возможные для ввода данные. Например, ограничить длину или принудительно указать на уникальность значения записей в данном столбце. Последнее ограничение часто используется для полей, которые содержат регистрационные имена пользователей (логины), или адреса электронной почты.
Эти ограничения дают вам контроль над целостностью ваших данных и предотвращают ситуации, подобные следующим:
Ввод адреса (текста) в поле, в котором вы ожидаете увидеть число
- ввод индекса региона с длинной этого самого индекса в сотню символов
- создание пользователей с одним и тем же именем
- создание пользователей с одним и тем же адресом электронной почты
- ввод веса (числа) в поле дня рождения (дата)
То, как вы спроектируете базу данных будет оказывать непосредственное влияние на запросы, которые вам будет необходимо выполнить, чтобы получить данные из базы данных. Это еще одна причина, почему вам необходимо задуматься о том, какой должна быть ваша база. С хорошо спроектированной базой данных ваши запросы могут быть чище и проще.
Как говорилось ранее, проектирование базы данных – это вопрос идентификации данных, их связи и помещение результатов решения данного вопроса на бумагу (или в компьютерную программу). Проектирование базы данных независимо от РСУБД, которую вы собираетесь использовать для ее создания.
В следующей части подробнее рассмотрим первичные ключи.
Реляционная модель данных (РМД) некоторой предметной области представляет собой набор отношений, изменяющихся во времени. При создании информационной системы совокупность отношений позволяет хранить данные об объектах предметной области и моделировать связи между ними.
Реляционная база данных представляет собой хранилище данных, содержащее набор двухмерных таблиц, данные в таблицах должны удовлетворять следующим принципам.
1. Значения атрибутов (столбец, поле) должны быть атомарными (иными словами, каждое значение, содержащееся на пересечении строки и столбца, должно быть не расчленяемым на несколько значений).
2. Значения каждого поля должны принадлежать к одному и тому же типу.
3. Каждая запись в таблице уникальна.
4. Каждое поле имеет Уникальное имя.
5. Последовательность полей и записей в таблице не существенна
Отношение является важнейшим понятием и представляет собой двумерную таблицу, содержащую некоторые данные.
Сущность есть объект любой природы, данные о котором хранятся в базе данных. Данные о сущности хранятся в отношении.
Атрибуты представляют собой свойства, характеризующие сущность. В Структуре таблицы каждый атрибут именуется и ему соответствует заголовок некоторого столбца таблицы.
Ключом отношения называется совокупность его атрибутов, однозначно идентифицирующих каждый из кортежей отношения.
Ключи обычно используют для достижения следующих целей :
Исключения дублирования значений в ключевых полях;
Упорядочения записей. Возможно упорядочение по возрастанию или убыванию значений всех ключевых полей, а также смешанное упорядочение (по одним - возрастание, а по другим - убывание);
Организации связывания таблиц.
Важным является понятие внешнего ключа. Внешний ключ можно определить как множество атрибутов одного отношения R2, значения которых должны совпадать со значениями возможного ключа другого отношения R1 .
К отношениям можно применять систему операций, позволяющую получать одни отношения из других. Например, результатом запроса к реляционной БД может быть новое отношение, вычисленное на основе имеющихся отношений. Поэтому можно разделить обрабатываемые данные на хранимую и вычисляемую части. Основной единицей обработки данных в реляционных БД является отношение, а не отдельные его кортежи (записи).
Проектирование баз данных информационных систем является достаточно трудоемкой задачей. Оно осуществляется на основе формализации структуры и процессов предметной области, сведения о которой предполагается хранить в БД. Различают концептуальное и схемно-структурное проектирование.
Концептуальное проектирование БД ИС является в значительной степени эвристическим процессом. Адекватность построенной в его рамках инфологической модели предметной области проверяется опытным путем, в процессе функционирования ИС.
Перечислим этапы концептуального проектирования :
Изучение предметной области для формирования общего представления о ней;
Выделение и анализ функций и задач разрабатываемой ИС;
Определение основных объектов-сущностей предметной области и отношений между ними;
Формализованное представление предметной области.
При проектировании схемы реляционной БД можно выделить следующие процедуры :
Определение перечня таблиц и связей между ними;
Определение перечня полей, типов полей, ключевых полей каждой таблицы (схемы таблицы), установление связей между таблицами через внешние ключи;
Установление индексирования для полей в таблицах;
Разработка списков (словарей) для полей с перечислительными данными;
Установление ограничений целостности для таблиц и связей;
Нормализация таблиц, корректировка перечня таблиц и связей.
Проектирование БД осуществляется на физическом и логическом уровнях. Проектирование на физическом уровне реализуется средствами СУБд и зачастую автоматизировано.
Логическое проектирование заключается в определении числа и структуры таблиц, разработке запросов к БД, отчетных документов, создании форм для ввода и редактирования данных в БД и т. д.
Одной из важнейших задач логического проектирования БД является структуризация данных. Выделяют следующие подходы к проектированию структур данных :
Объединение информации об объектах-сущностях в рамках одной таблицы (одного отношения) с последующей декомпозицией на несколько взаимосвязанных таблиц на основе процедуры нормализации отношений;
Формулирование знаний о системе (определение типов исходных данных и взаимосвязей) и требований к обработке данных, получение с помощью CASE-системы готовой схемы БД или даже готовой прикладной информационной системы;
Осуществление системного анализа и разработка структурных моделей.
Простота и эффективность БД на основе реляционной модели по-прежнему обуславливает их доминирующее положение в программных приложениях. В настоящее время считается нормой использование реляционных БД в объектно-ориентированных программных системах. Судя по всему, это долгосрочная и устойчивая тенденция.
Реляционная БД состоит из множества двумерных таблиц. В таблицах хранятся различные данные. Например, в составе БД могут быть таблицы заказчиков, товаров, счетов и т. д. Типовая структура таблицы реляционной БД представлена на рис. 1.2.
Рис. 1.2. Типовая структура таблицы реляционной БД
Строки таблицы называют записями или кортежами . Столбцы называют атрибутами . На пересечении строки и столбца находится неделимое (атомарное) значение элемента данных. Набор допустимых значений атрибута (столбца) определяется его доменом . Домен может быть очень мал. Так, значениями атрибута Размер в таблице спортивных костюмов являются L, XL и XXL. И наоборот, домен атрибута Фамилия очень велик. В БД домен реализуется с помощью ограничения домена. Всякий раз при записи значения в БД проверяется его соответствие домену, зафиксированному для заданного атрибута. Таким образом, БД предохраняется от ввода недопустимых значений, например, даты 32 мая.
Виртуальным аналогом таблицы является представление , которое ведет себя с точки зрения клиента как обычная таблица, но не существует самостоятельно. Обычная таблица содержит данные. Представление же не содержит никаких данных, а только задает их источники (одну или несколько обычных таблиц, выбираемые строки, выбираемые столбцы). Фактически представление сохраняется в БД как запрос на создание определенного набора данных. Результат выполнения этого запроса является содержанием представления. При изменении данных в таблицах-источниках меняется и содержание представления.
Для выявления в таблице отдельной записи используют ключ. Первичный ключ (Primary Key, РК) имеет каждая таблица. Это столбец, однозначно определяющий каждую запись в таблице. В нашем примере в качестве РК может быть столбец Фамилия. Это правильно до появления, например, еще одного Бендера. Для обеспечения уникальности значения первичного ключа применяются две методики. Во-первых, может использоваться составной первичный ключ (Composite Primary Key), образуемый несколькими столбцами (естественными атрибутами) таблицы. Во-вторых, в качестве РК можно вводить в таблицу дополнительный столбец, не имеющий смысла с точки зрения предметной области. Его называют суррогатным ключом. Например, суррогатным ключом может быть Номер заказчика или Номер заказа.
Важную роль в реляционных БД играет еще один ключ - Внешний ключ . Внешний ключ (Foreign Key, FK) - это столбец одной таблицы, который ссылается на первичный ключ другой таблицы. С помощью внешних ключей устанавливаются связи между различными таблицами БД (пример приведен на рис. 1.3) – ускорение доступа к таблице с помощью индекса.
Рис. 1.3. Внешний ключ
В этом примере показано, что таблицы счетов и заказчиков связаны ключом Номер заказчика. Если обратиться к таблице счетов, то Номер счета будет первичным ключом, а Номер заказчика - внешним ключом.
Для обеспечения целостности данных БД внешние ключи должны удовлетворять ограничению ссылочной целостности. Оно означает, что каждому значению внешнего ключа в одной таблице должно соответствовать значение существующего первичного ключа в другой таблице. Это важнейшее из всех ограничений, так как оно обеспечивает непротиворечивость перекрестных ссылок между таблицами. Если корректность значений внешнего ключа не проверять, может нарушиться ссылочная целостность данных БД. Например, удаление строки из таблицы заказчиков может привести к тому, что в таблице заказов останутся записи о заказах, сделанных неизвестным теперь заказчиком (а кто же оплатит заказ?). Ограничения ссылочной целостности должны поддерживаться автоматически. Каждый раз при вводе или изменении данных БД средства управления проверяют ограничения и убеждаются в их соблюдении. Если ограничения нарушаются, изменение данных запрещается.
Кроме того, таблица может содержать вторичные ключи - индексы. Их используют как предметный указатель в книге. Чтобы найти в книге конкретный термин, не надо листать все страницы подряд - достаточно посмотреть в предметный указатель и найти нужный номер страницы. Например, можно создать индекс для столбца Фамилия (рис.1.4).
Рис. 1.4. Ускорение доступа к таблице с помощью индекса
В результате сформируется небольшая таблица, в которой хранятся только фамилии и ссылки на позицию записи в основной таблице. Теперь для поиска записей не надо просматривать всю большую таблицу. В итоге получаем выигрыш в быстродействии. Правда, при добавлении и удалении записей (в основной таблице) таблицу индекса нужно создавать заново. Это замедляет операции.
Оперативную обработку данных в реляционных БД выполняют хранимые проце дуры. Разновидностью хранимых процедур являются триггеры . Триггер всегда связан с конкретной таблицей и вызывается автоматически при наступлении определенного события (например, вставки, удаления или обновления записи).
Обсудим отношения между таблицами. После формирования таблиц решают, как объединить их данные при извлечении из БД. Первым шагом является определение связей между таблицами. После этого возможно создание запросов, форм и отчетов, в которых выводятся данные из нескольких таблиц сразу. Например, чтобы напечатать счет, надо взять данные из разных таблиц и скомпоновать их. Связь между таблицами устанавливает отношения между совпадающими значениями в ключевых полях. Рассмотрим разновидности отношений.
Отношение «один-к-одному» . В этом случае каждой строке (записи) одной таблицы ставится в соответствие строка другой таблицы (рис. 1.5).
Рис. 1.5. Отношение «один-к-одному»
Примером может быть отношение между таблицей сотрудников и таблицей их адресов. Такое отношение встречается редко, так как соответствующие данные легко поместить в одну таблицу.
Отношение «один-ко-многим» . Одной записи первой таблицы ставятся в соответствие несколько записей во второй таблице (рис. 1.6).
Рис. 1.6. Отношение «один-ко-многим»
Каждая запись второй таблицы не может иметь более одной соответствующей записи в первой таблице.
Отношение «многие-ко-многим» . Одной записи первой таблицы могут соответствовать несколько записей во второй таблице, а одной записи второй таблицы - несколько записей первой (рис. 1.7).
Рис. 1.7. Отношение «многие-ко-многим»
Как правило, для организации таких отношений требуется вспомогательная таблица, которая состоит из первичных ключей двух основных таблиц. Такое отношение возникает между заказами и товарами. Один заказ может включать несколько наименований товара, а одно наименование товара - входить в несколько заказов. Таким образом, должны существовать таблица заказов, таблица товаров и таблица с парами заказ-товар.
Рассмотрим теперь кратко нормализацию реляционных баз данных. Нормализация обеспечивает оптимизацию структуры БД. Она приводит к устранению избыточности в наборах данных. Выполняется нормализация БД последовательно, шаг за шагом. Правила нормализации оформлены в виде нормальных форм.
Первая нормальная форма (1НФ) требует, чтобы значения всех элементов данных в столбцах были атомарными. Вторая нормальная форма (2НФ) требует, чтобы каждый неключевой столбец полностью зависел от первичного ключа. Третья нормальная форма (3НФ) требует, чтобы все неключевые столбцы (атрибуты) были взаимно независимы и полностью зависели от первичного ключа. Зависимость существует, если, например, значения одного столбца вычисляются по данным других столбцов. Результатом проведения нормализации является оптимальная структура базы данных, в которой имеется необходимое дублирование данных, но отсутствует избыточное.
Правительство Российской Федерации
Национальный исследовательский университет
ВЫСШАЯ ШКОЛА ЭКОНОМИКИ
ПЕРМСКИЙ ФИЛИАЛ
Кафедра информационных технологий в бизнесе
Информационные технологии в офисной работе
Разработка информационной системы предприятия с помощью системы управления базами данных Access 2007
Учебно-методическое пособие
Пермь 2011
Информационные технологии в офисной работе. Разработка информационной системы предприятия с помощью системы управления базами данных Access 2007. Учебно-методическое пособие. НИУ ВШЭ ПФ, 2011 г., 40 ст.
Составители: Викентьева Ольга Леонидовна, Лебедев Валерий Викторович.
Учебно-методическое пособие составлено в соответствии с Государственным образовательным стандартом, учебной программой и концепцией дисциплины «Информационные технологии в экономике». Пособие предназначено для студентов и преподавателей ПФ ГУ ВШЭ и содержит серию практических занятий, раскрывающих возможности современных информационных технологий по созданию систем хранении, поиска и представления данных.
Рецензент: доцент кафедры информатики Пермского регионального института педагогических информационных технологий, кандидат педагогических наук, член-корреспондент Академии информатизации образования Кушев В.О.
Занятие 1. Проектирование реляционной базы данных
В обычном смысле БД представляет собой файл или множество файлов, имеющих определенную организацию. Однако при работе с обычными системами файловой обработки возникает ряд проблем, связанных, в частности, с избыточностью и зависимостью хранящихся в них данных. При проектировании БД эти проблемы решаются.
Пользователь должен принимать участие в процессе проектирования БД, так как только он может определить, какие данные необходимы для работы, указать связи, существующие между этими данными и обратить внимание на тонкости их обработки.
Информационные потребности отдельного пользователя обычно затрагивают лишь часть данных, хранящихся в информационной системе, и описание этих потребностей может не совпадать с описаниями потребностей других пользователей. Представление о том, какая именно информация необходима для работы, будет разным для разных групп пользователей, специалистов в различных областях – оно зависит от выполняемых ими обязанностей (специалист отдела кадров и сотрудники бухгалтерии, руководитель подразделения и т.д. нуждаются для выполнения своих функций в различных данных). Эти потребности и описываются на внешнем уровне представления данных (представления А, В, и С на рис.1).
Внешних описаний данных, хранящихся в БД, следовательно, может быть множество. Их необходимо свести в единое концептуальное представление , описывающее данные на уровне всей информационной системы в целом. Представление этих данных на внутреннем уровне определяется способом их хранения во внешней памяти.
Рассмотрим пример БД информационной системы фирмы, занимающейся поставками товаров в магазины города, причем будем учитывать только информационные потребности двух сотрудников фирмы (в упрощенной форме – иначе пример был бы слишком громоздким).
Рисунок 1. Формирование представления данных
Сотрудник, занимающийся связями с клиентами, для выполнения своих обязанностей нуждаются в информации, представленной на рис.2.
Для сотрудника, который работает с платежными формами, необходима другая информация о клиентах (рис.3).
Данные, используемые отдельными специалистами, находятся в единой информационной системе предприятия, в общей для них базе данных. Поэтому внешние представления отдельных пользователей должны быть интегрированы в концептуальном представлении, цель описания данных на концептуальном уровне – создание такого формального представления о данных, чтобы любое внешнее представление являлось его подмножеством. В процессе интеграции внешних представлений устраняются двусмысленности и противоречия в информационных потребностях отдельных пользователей. Концептуальное описание, представляющее всю БД, должно быть единственным.
Рисунок 2. Данные для первого сотрудника
Рисунок 3. Данные для второго сотрудника
В рассматриваемом примере концептуальное представление должно включать всю информацию, необходимую всем сотрудникам. Противоречия могут возникнуть вследствие того, что сотрудники, которые используют общую информацию, могут представлять ее себе по-разному (например: номер телефона может быть записан в разных форматах). Все эти противоречия должны быть ликвидированы, данные и форма их представления должны быть согласованы.
Тогда концептуальное описание определяется следующей информацией
Данные, описанные концептуальной схемой, должны быть записаны во внешней памяти, на ВЗУ, предназначенных для хранения информации, находящейся в БД. Внутреннее описание данных характеризует способ хранения данных во внешней памяти.
Правила описания данных определяются выбранной моделью данных (в данном случае рассматривается только реляционная модель – самая распространенная на настоящее время).
Если взять описание данных о клиентах фирмы, занимающейся поставками товаров в магазины города, из приведенного выше примера, представленное на рис 4, то данные, описанные этой таблицей не могут в таком виде быть представлены в реляционной БД, так как не все значения являются атомарными (компоненты строк «Владелец» и «Адрес» состоят из нескольких значений, т.е. значения этих атрибутов заменяются другими отношениями, расшифровываются ими; в отношении, описывающем владельца, поля «Адрес» и «Паспорт» также не являются атомарными, следовательно строится иерархия отношений).
При проектировании БД могут быть приняты различные решения, но существуют базовые требования, которые должны учитываться в процессе работы: множество отношений должно обеспечивать минимальную избыточность представления информации; манипулирование данными, корректировка отношений не должна приводить к нарушению целостности данных, двусмысленности и потере информации; перестройка набора отношений при добавлении в БД новых атрибутов должна быть минимальной.
Рисунок 4. Общее представление данных
Описание реальных объектов и взаимосвязей между ними во многом носит субъективный характер, но есть определенные общие правила, в частности, правила нормализации . В ходе нормализации обеспечивается защита целостности данных путем устранения дублирования данных. В результате представление данных об одном объекте может быть разбито на несколько более мелких связанных таблиц (декомпозиция без потерь). Ограничения, которые должны соблюдаться при проектировании реляционной БД, достаточно многочисленны. Соблюдение ограничений при определении конкретных отношений в БД связано с реализацией нормальных форм . Нормальные формы нумеруются последовательно, начиная от первой. Чем больше номер нормальной формы, которой удовлетворяет БД, тем больше ограничений на хранимые в БД данные должно соблюдаться. Можно к типичным для реляционных СУБД ограничениям ввести дополнительный набор ограничений, что приведет к увеличению числа нормальных форм.
В плохо спроектированной БД вся информация может храниться в одной таблице. Для описанного выше примера такая таблица могла бы содержать следующие столбцы:
Компоненты адреса «Улица» и «Дом» переименованы для соблюдения требования того, что имена столбцов должны быть уникальны (правила именования зависят от конкретной СУБД).
Какие недостатки имеет такое представление?
Первая нормальная форма требует, чтобы на любом пересечении строки и столбца находилось единственное значение, которое должно быть неделимо (требование атомарности). Кроме того, в реляционной таблице не должно быть повторяющихся строк и групп данных.
Требование атомарности выполнено – составные столбцы «Адрес» и «Владелец» (а для владельца «Адрес» и «Паспорт») разбиты на компоненты, которые включены в общую таблицу. Но у одного магазина может быть несколько владельцев, а один человек может владеть несколькими магазинами. Это приводит к тому, что в таблицу нужно будет включать все строки, представляющие «комбинации» магазинов и их владельцев, т.е. в различных строках будут повторяться группы данных (несколько раз будут повторяться данные о магазине – для каждого его владельца, а данные владельца будут повторяться для каждого его магазина). Такое представление данных ведет к огромной избыточности, к тому, что неэффективно будет расходоваться память на ВЗУ. Кроме того, дублирование информации может привести к проблемам при ее обработке: чтобы внести изменения в информацию о магазине (например, если у него изменится счет в банке) нужно изменить эти данные в нескольких записях, соответствующих разным владельцам.
При определении того, какие таблицы должны входить в БД, и того, какая информация в них должна храниться, следует учитывать следующее правило: каждая таблица описывает объект , существующий самостоятельно, обладающий собственными свойствами. Построение БД следует начать с создания представления каждого объекта в виде строк, содержащих его атрибуты, в соответствующей таблице; определения моделей взаимосвязи объектов. В рассматриваемом примере в БД фактически должна храниться информация об объектах двух типов: о магазинах и об их владельцах. Эту информацию следует поместить в две различные таблицы («Магазины» и «Владельцы»), имеющие следующие столбцы:
«Магазины»
«Владельцы»
Каждая строка таблицы «Магазины» будет описывать экземпляр соответствующего объекта (один магазин). А в каждой строке таблицы «Владельцы» будет находиться информация об одном владельце магазина.
При работе с информацией, хранящейся в БД, СУБД должна уметь отличать строки друг от друга. Атрибут или набор атрибутов, однозначно определяющий строку, – это ее первичный ключ.
Что можно выбрать в качестве первичного ключа для описанных выше таблиц?
Ключом отношения является такое множество атрибутов, что каждое сочетание их значений встречается только в одной строке отношения и никакое подмножество этих атрибутов этим свойством не обладает. Таким образом, ключ однозначно определяет строку, позволяет выбрать ее из всего множества строк отношения.
Определим ключ для таблицы «Магазины».
Если выбрать в качестве ключа атрибут «Название магазина», будет ли он удовлетворять указанному требованию? Нет, если в одном городе может быть несколько магазинов с одинаковыми названиями, расположенных в разных частях города. Чтобы гарантировать однозначность следует дополнить название магазина его адресом (по названию магазина и его адресу можно однозначно выбрать нужную строку в таблице), тогда ключ отношения будет составным.
Простым ключом, идентифицирующим нужный магазин, может быть номер расчетного счета в банке (если у каждого магазина единственный номер расчетного счета и каждый расчетный счет принадлежит одному магазину). Ключом может быть также ИНН (идентификационный номер) магазина.
Выберем в качестве первичного ключа атрибут «ИНН». Далее этот атрибут будет использоваться для организации связей между таблицами «Магазины» и «Владельцы» (эти связи должны отражать реальные взаимосвязи между магазинами и их владельцами).
Определимся с ключами и для таблицы «Владельцы».
Если бы можно быль предположить, что среди владельцев магазинов нет однофамильцев, то в качестве ключа можно было бы выбрать атрибут «Фамилия» рассматриваемого отношения. Но, к сожалению, владельцы магазинов могут быть не только однофамильцами, но и полными тесками (маловероятно, но вполне возможно). Поэтому в качестве ключа можно выбрать паспортные данные владельца, т.е. использовать для его идентификации составной ключ, включающий атрибуты «Серия» и «Номер». Этот ключ будем считать первичным. С его помощью установим связь между владельцем и его магазинами.
Первичные ключи обеспечат не только однозначность при поиске информации (они являются уникальными), но и позволят связать данные, находящиеся в двух таблицах.
Определим тип связи между таблицами «Магазины» и «Владельцы».
Если предположить, что один человек может владеть несколькими магазинами, но у каждого магазина есть единственный владелец, то следовало бы установить между этими таблицами связь «один-ко-многим». Для организации такой связи в БД можно было бы в строку таблицы «Магазины», содержащую информацию о магазине, включить внешний ключ , идентифицирующий владельца магазина, т.е. данные его паспорта – атрибуты «Серия» и «Номер». Организовать связь, включив ключ «ИНН», идентифицирующий магазины, в качестве внешнего ключа в таблицу «Владельцы», в данном случае нельзя, так как в этом случае информацию о владельце пришлось бы дублировать для каждого магазина.
Если сделать предположение о том, что один человек может быть владельцем только одного магазина, но у каждого магазина может быть несколько владельцев, получится связь «один-ко-многим», но в данном случае внешний ключ (ИНН магазина) пришлось бы включать в таблицу, содержащую сведения о владельцах.
В действительности каждый человек может оказаться владельцем нескольких магазинов и у каждого магазина может быть несколько владельцев, поэтому между таблицами «Магазины» и «Владельцы» должна быть установлена связь «многие-ко-многим», для организации которой создается специальная таблица, описывающая связи между магазинами и владельцами:
«Магазины-Владельцы»
| ИНН | Серия | Номер |
Эта таблица позволит по атрибуту «ИНН» магазина найти всех его владельцев (через данные их паспортов), а по составному атрибуту, включающему атрибуты «Серия» и «Номер» паспорта владельца найти в БД все магазины, которыми он владеет.
Для этого следует, создав таблицу «Магазины-Владельцы», установить связи «один-ко-многим» между таблицей «Магазины» и таблицей «Магазины-Владельцы», а также между таблицами «Владельцы» и «Магазины-Владельцы»:
«Магазины-Владельцы»
Установленные связи помогают СУБД поддерживать целостность, согласованность информации. Например, можно задать правила обновления информации в связанных таблицах при обновлении информации в основной таблице (при ликвидации магазина, например, должна быть удалена и перенесена в архив информация о нем из БД, причем не только строка из таблицы «Магазины», но и вся информация в связанных с ней таблицах, относящаяся к этому магазину).
Для удобства пользователей, ускорения поиска СУБД поддерживают возможность поиска не только по уникальным ключам. Например, найти в таблице можно все магазины с одинаковыми названиями или все магазины, принадлежащие одному владельцу.
Нормализация данных привела к разделению таблиц, выделению отношений «первичный ключ–внешний ключ» в меньшие таблицы. Результатом нормализации является уменьшение избыточности данных – уже не нужно дублировать данные о каждом владельце для каждого магазина.
Вторая нормальная форма требует, чтобы любой неключевой столбец зависел от своего первичного ключа (причем от всего ключа, а не от отдельных его компонентов). Отношение имеет вторую нормальную форму, если оно соответствует первой нормальной форме и не содержит неполных функциональных зависимостей. Неполная функциональная зависимость определяется двумя условиями: ключ отношения функционально определяет некоторый неключевой атрибут и часть ключа функционально определяет тот же неключевой атрибут.
Отношение, не соответствующее второй нормальной форме, характеризуется избыточностью хранимых данных.
В рассматриваемом примере набор атрибутов отношений и выбор ключей сделан таким образом, что таблицы соответствуют второй нормальной форме. Если бы этого соответствия не было, для приведения таблиц ко второй нормальной форме было бы необходимо выделить повторяющуюся информацию (часть ключа и определяемые ею неключевые атрибуты) в отдельную таблицу.
Например: в БД необходимо хранить информацию о товарах, которые поставлены в магазины. Эта информация включает атрибуты «Наименование», «Код» и «Цена» товара, а также «Количество» поставленного товара. Если включить эту информацию в таблицу «Поставки» в следующем представлении:
«Поставки»
(здесь «ИНН» идентифицирует магазин, в который выполнена поставка (это внешний ключ, используемый для создания связи «один-ко-многим» таблицы «Магазины» с данной таблицей), «Наименование» – название товара, «Код» – его уникальный код (товары с разными характеристиками и, следовательно, разными ценами могут иметь одно наименование, но коды будут разными), «Цена» – отпускная цена товара, «Количество» – количество поставленного товара), то может возникнуть избыточность.
Для определения строки, представляющей поставку товара в конкретный магазин, можно задать составной ключ, включающий атрибуты «ИНН» и «Код». Эта информация дает возможность определить цену товара и его количество, поставленное в данный магазин, а также вычислить общую стоимость товара. Если предположить, что товар поставляется во все магазины по одной и той же цене, и цена не изменяется со временем, то неключевой атрибут «Цена» определяется не только составным ключом «ИНН» + «Код», но и его частью – атрибутом «Код». Таким образом, одна и та же цена повторяется во всех строках таблицы, где содержится информация о поставке одного и того же товара. Это ведет к избыточности. Наименование товара также определяется его кодом. Поэтому информацию, относящуюся только к товару и не зависящую от магазина, можно вынести в отдельную таблицу:
Здесь ключевое поле «Код» позволит связать данные, находящиеся в таблице «Поставки», с данными из таблицы «Товары»
Таким образом, приведение ко второй нормальной форме ликвидировало избыточность путем выделения новых таблиц: таблица «Поставки» разбита на две таблицы «Поставки» и «Товары», между которыми установлена связь.
Третья нормальная форма еще больше повышает требования: отношение соответствует второй нормальной форме и среди его атрибутов отсутствуют транзитивные функциональные зависимости (ни один неключевой столбец не должен зависеть от другого неключевого столбца, он может зависеть только от первичного ключа).
В рассматриваемом примере несоответствие третьей нормальной форме проявилось бы при выполнении такого условия: все товары с одинаковым наименованием имеют одну цену (наименование определялось бы кодом, а цена – наименованием товара). В этом случае появилась бы избыточность, так как цена для данного наименования товара повторялась бы столько раз, сколько различных кодов этого товара используется.
Избавиться от избыточности можно было бы, разбив таблицу «Товары» на две таблицы (одна включала бы атрибуты «Код» и «Наименование», а вторая «Наименование» и «Цена»).
Однако в рассматриваемом примере ситуация другая: товары имеют разные коды, если их характеристики различны, следовательно, должны отличаться и цены.
Четвертая нормальная форма запрещает независимые отношения типа «один ко многим» между ключевыми и неключевыми столбцами. Проще говоря, в одну таблицу нельзя помещать разнородную информацию, т.е. данные, между которыми нет непосредственной связи.
Это правило можно рассмотреть на следующем примере. Сотрудник, занимающийся связями с клиентами, для выполнения своих обязанностей собирается использовать информацию о членах семей владельцев магазинов. Эту информацию не следует включать в таблицу «Владельцы», так как трудно определить, сколько места нужно резервировать в строках таблицы, соответствующих конкретным людям, для хранения данных о их семейном положении, – один может быть одиноким, а другой – многодетным отцом. Информацию о членах семьи нужно вынести в отдельную таблицу, каждая строка которой будет содержать информацию об одном члене семьи, включив в нее внешний ключ, идентифицирующий владельца магазина, для организации связи с таблицей «Владельцы».
Пятая нормальная форма обычно завершает процесс нормализации. На этом этапе все таблицы разбиваются на минимальные части для устранения в них избыточности. Каждый фрагмент неключевых данных в таблицах должен встречаться только один раз. Это снимает проблемы с обновлением информации в БД: все изменения неключевой информации должны вноситься только один раз, что обеспечивает возможность управления целостностью данных.
Процесс проектирования БД является очень важным этапом в разработке информационных систем. Именно качество проектирования во многом определяет эффективность использования БД.
В настоящее время широко используются специальные средства, облегчающие процесс разработки информационных систем (CASE-средства – Computer-Aided Software/System Engineering).
Вопросы для самоконтроля :
1. Что представляет собой база данных?
2. Что такое внешнее представление данных?
3. В чем сущность концептуального представления данных?
4. Что такое модель данных?
5. Что такое нормализация?
6. Что такое ключ отношения?
7. Какой ключ называется внешним?
8. Какие связи могут быть организованы в базе данных?
9. В чем сущность каждой из пяти нормальных форм?
Задание для самостоятельной работы:
Спроектировать базы данных некоторой фирмы, занимающейся обслуживанием клиентов. База данных нужна трем сотрудникам фирмы. Первый из них занимается учетом услуг, оказываемых фирмой, и нуждается в следующей информации:
Второй сотрудник собирает сведения об исполнителях и его интересует:
Третий сотрудник работает с клиентами и ему важно знать.
Проектирование баз данных информационных систем является достаточно трудоемкой задачей. Оно осуществляется на основе формализации структуры и процессов предметной области, сведения о которой предполагается хранить в БД. Различают концептуальное и схемно- структурное проектирование.
Концептуальное проектирование БД ИС является в значительной степени эвр"истическим процессом. Адекватность построенной в его рамках инфологической модели предметной области проверяется опытным путем, в процессе функционирования ИС.
Перечислим этапы концептуального проектирования:
* изучение предметной области для формирования общего представления о ней;
* выделение и анализ функций и задач разрабатываемой ИС;
* определение основных объектов-сущностей предметной области и отношений между ними;
* формализованное представление предметной области.
При проектировании схемы реляционной БД можно выделить следующие процедуры:
*определение перечня таблиц и связей между ними;
*определение перечня полей, типов полей, ключевых полей каждой таблицы (схемы таблицы), установление связей между таблицами через внешние ключи;
*установление индексирования для полей в таблицах;
* разработка списков (словарей) для полей с перечислительными данными;
* установление ограничений целостности для таблиц и связей;
* нормализация таблиц, корректировка перечня таблиц и связей. Проектирование БД осуществляется на физическом и логическом уровнях. Проектирование на физическом уровне реализуется средствами СУБД и зачастую автоматизировано.
Логическое проектирование заключается в определении числа и структуры таблиц, разработке запросов к БД, отчетных документов, создании форм для ввода и редактирования данных в БД и т. д.
Одной из важнейших задач логического проектирования БД является структуризация данных. Выделяют следующие подходы к проектированию структур данных:
*объединение информации об объектах-сущностях в рамках одной таблицы (одного отношения) с последующей декомпозицией на несколько взаимосвязанных таблиц на основе процедуры нормализации отношений;
* формулирование знаний о системе (определение типов исходных данных и взаимосвязей) и требований к обработке данных, получение с помощью СА5Е-системы готовой схемы БД или даже готовой прикладной информационной системы;
* осуществление системного анализа и разработка структурных
Человечество сегодня переживает информационный взрыв. Объем информации, поступающей к человеку через все информационные средства, непрерывно растет. Поэтому для каждого человека, живущего в информационном обществе, очень важно овладение средствами оптимального решения задачи накопления, упорядочения и рационального использования информации.
Возможности человека в обработке информации резко возросли с использованием компьютеров. В применении ЭВМ для решения задач информационного обслуживания можно выделить два периода:
начальный период, когда решением задач обработки информации, организацией данных занимался небольшой круг людей - системные программисты. Этот период характерен тем, что создавались программные средства для решения конкретной задачи обработки данных. При этом для решения другой задачи, в которой использовались эти же данные, нужно было создавать новые программы;
период системного применения ЭВМ. Для решения на ЭВМ комплекса задач создаются программные средства, оперирующие одними и теми же данными, использующие единую информационную модель объекта. Эти средства не зависят от характера объекта, его модели, их можно применять для информационного обслуживания различных задач. Человечество пришло к организации информации в информационных системах.
Информационными системами (ИС) называют большие массивы данных вместе с программно-аппаратными средствами для их обработки. Различают следующие виды ИС: фактографические, документальные и экспертные системы.
Фактографическая ИС - это массив фактов - конкретных значений данных об объектах реального мира.
Информация в фактографической ИС хранится в четко структурированном виде, поэтому она способна давать однозначные ответы на поставленные вопросы, например: «Кто является победителем Чемпионата России по гимнастике в 1999 году?», «Кому принадлежит автомобиль марки AUDI 80 с регистрационным номером РА899Р77?», «Какой номер телефона в бухгалтерии МГУ?», «Кто стал Президентом России на выборах в марте 2002 года?» и т. д. Фактографические ИС используются буквально во всех сферах человеческой деятельности - в науке, материальном производстве, на транспорте, в медицине, государственной и общественной жизни, торговле, криминалистике, искусстве, спорте.
Документальные информационные системы обслуживают принципиально иной класс задач, которые не предполагают однозначного ответа на поставленный вопрос. Базу данных таких систем образует совокупность неструктурированных текстовых документов (статьи, книги, рефераты, тексты законов) и графических объектов, снабженная тем или иным формализованным аппаратом поиска. Цель системы, как правило, - выдать в ответ на запрос пользователя список документов или объектов, в какой-то мере удовлетворяющих сформулированным в запросе условиям. Например: выдать список всех статей, в которых встречается слово «Пушкин». Принципиальной особенностью документальной системы является ее способность, с одной стороны, выдавать ненужные пользователю документы (например, где слово «Пушкин» употреблено в ином смысле, чем предполагалось), а с другой - не выдавать нужные (например, если автор употребил какой-то синоним или ошибся в написании). Документальная система должна уметь по контексту определять смысл того или иного термина, например, различать «ромашка» (растение), «ромашка» (тип печатающей головки принтера).
Экспертные системы (ЭС) - интеллектуальные системы, призванные играть роль «советчика», построены на базе формализованного опыта и знаний эксперта. Ядром ЭС являются базы знаний, в которых собраны знания экспертов (специалистов) в определенной области, на основе которых ЭС позволяет моделировать рассуждения специалистов из данной предметной области.
Указанная классификация и отнесение ИС к тому или иному типу устарели, так как современные фактографические системы часто работают с неструктурированными блоками информации (текстами, графикой, звуком, видео), снабженными структурированными описателями.
