Базы данных

Конспект лекций | назад | содержание | вперед

Раздел 2. Реляционная модель базы данных В этом разделе обсуждаются следующие темы: отображение логического представления данных в реляционной модели; основные компоненты реляционной модели базы данных: сущности, атрибуты связи между сущностями; каким образом сущности и их атрибуты организуются в таблицы; операторы реляционной базы данных, словарь данных и системный каталог; каким образом решается проблема избыточности данных в реляционной баз данных;почему важно индексирование. Тема 2.1. Логическое представление данных Ранее было показано, что в базе данных хранятся и обрабатываются как данные, так и метаданные. Вы также убедились в том, что доступом к данным и к структуре базы данных управляет СУБД. Такая конструкция — размещение СУБД между приложением и базой данных — устраняет большую часть проблем, унаследованных от систем файлов. За такую гибкую схему приходится платить усложнением физической организации. Фактически структура базы данных, которая требуется для иерархических и сетевых моделей, зачастую настолько сложна, что уменьшает эффективность проекта БД в целом. Реляционная модель базы данных изменила такое положение дел, дав возможность проектировщику сосредоточиться на логическом представлении данных и их связей, а не на физических особенностях хранения данных. В реляционной базе данных как бы используется автоматическая коробка передач, избавляющая нас от необходимости пользоваться педалью сцепления и рычагом переключения передач. Говоря кратко, реляционная модель позволяет рассматривать данные с логической точки зрения, а не с физической. На практике логическое представление данных удобно тем, что оно очень напоминает простую концепцию хранения данных в файле. Несмотря на то, что таблица, в отличие от файла, имеет преимущества независимости по данным и структурной независимости, действительно, с концептуальной точки зрения таблица напоминает файл. Поскольку мы можем полагать, что связанные записи хранятся в независимых таблицах, реляционная модель проще для понимания, чем предшествующие ей иерархическая и сетевая модели. Логическая простота позволяет обеспечить простую и эффективную методологию проектирования. Поскольку таблица играет такую выдающуюся роль в реляционной модели, она заслуживает самого тщательного изучения. Поэтому мы начнем наше обсуждение с детального исследования структуры таблицы и ее содержимого. 2.1.1. Сущности и атрибуты Реляционное проектирование начинается с определения необходимых сущностей. Вспомните, что сущность это персона, местоположение или предмет, сведения о которых подлежат сбору и хранению. Например, в университете интересующими нас сущностями могут быть студенты, преподаватели, курсы и т. д. Если вы работаете в авиакомпании, сущностями здесь могут считаться пилоты, маршруты, оборудование и любое число дополнительных объектов, о которых необходимо собирать информацию. Сущности группируются по их общим свойствам. Например, все студенты колледжа группируются вместе, формируя набор сущностей. Набор сущностей - это именованная совокупность сущностей, объединенных общими свойствами. В идеальном случае имя набора сущностей отражает его содержимое, чтобы напоминать проектировщику базы данных о своем функциональном предназначении внутри БД. Например, сведения о студентах университета могут храниться в наборе сущностей с именем STUDENT. Другими словами, набор сущностей STUDENT содержит множество сущностей "студент". Подобным образом сведения о преподавателях могут храниться в наборе сущностей FACULTY. Каждая сущность имеет некоторые свойства, называемые атрибутами. Например, сущность STUDENT может иметь следующие атрибуты: идентификационный номер, средний балл, дата поступления, дата рождения домашний адрес, номер телефона и профилирующая дисциплина. Подобным образом авиакомпания может определить такие атрибуты для сущности AIRCRAFT: бортовой номер, дата последнего технического осмотра, общий налет в часах и налет в часах со времени последнего технического осмотра. Каждый атрибут нужно именовать надлежащим образом, чтобы его имя напоминало пользователю о содержимом атрибута. Например, в сущности STUDENT атрибут студента "дата рождения" может храниться под именем STU_DOB, a атрибут студента "домашний телефон" может храниться под именем STU_HOME_PHONE. Точно так же для сущности AIRCRAFT (авиалайнер) атрибут авиалайнера "бортовой номер" может храниться под именем AC_N UMBER, а атрибут "общий налет в часах" может храниться под именем AC_HRS_FLOWN. 2.1.2. Таблицы и их свойства Логическое представление реляционных баз данных упрощается созданием связей между данными на основе (логической) конструкции, называемой таблицей. Под таблицей понимается двумерная структура, состоящая из строк и столбцов. Пользователь должен понимать, что таблица содержит группу связанных сущностей, т. е. набор сущностей; по этой причине термины набор сущностей и таблица чаще всего означают одно и то же. Таблица также называется отношением (relation), поскольку создатель реляционной модели Э. Ф. Кодд использовал термин "отношение" как синоним слова "таблица". Примечание . Термин "отношение" пришел из математической теории множеств, которую Кодд использовал для создания своей модели. Поскольку реляционная модель основана на соединениях (links), позволяющих использовать связи (relationships) сущностей, многие пользователи баз данных неправильно думают, что термин "отношение" (relation) применяется к таким связям. Оказывается, что табличное представление данных помогает определить связи сущностей, значительно упрощая задачу проектирования БД.Свойства реляционной таблицы сведены в таблице 2.1. Таблица 2.1. Свойства реляционной таблицы
№ | Свойство
1 | Таблица представляет собой двумерную структуру, состоящую из строк и столбцов
2 | Каждая строка таблицы (кортеж) представляет собой отдельную сущность внутри набора сущностей
3 | Каждый столбец таблицы представляет собой атрибут, и у каждого столбца есть свое имя
4 | На каждом пересечении строки и столбца имеется единственное значение
5 | Каждая таблица должна иметь атрибут или несколько атрибутов, уникально идентифицирующих каждую строку
6 | Все значения в столбце должны отображаться в одинаковом формате. Например, если атрибуту присваивается формат целого, то все значения в столбце, представляющем данный атрибут, должны быть целыми
7 | Каждый столбец имеет определенный диапазон значений, называемый доменом атрибута
8 | Порядок следования строк и столбцов для СУБД не существенен
Примечание. В реляционных базах данных используется строгая терминология. К сожалению, в среду баз данных иногда проникает терминология систем файлов. Таким образом, мы можем обнаружить, что строки иногда называют записями, а столбцы полями. Подчас таблицы называют файлами. С технической точки зрения, подмена терминов не всегда уместна: таблица базы данных представляет собой логическое, а не физическое понятие, а термины "файл", "запись" и "поле" описывают физические понятия. Тем не менее, учитывая, что таблица это логическое, а не физическое понятие, вы можете (на концептуальном уровне) считать строки таблицы записями, а столбцы полями. Для иллюстрации свойств, перечисленных в таблице 2.1, мы будем использовать таблицы, представленные на рис. 2.1. Рис. 2.1. Список значений атрибутов таблицы STUDENT С помощью таблицы STUDENT, представленной на рис. 2.1, мы можем сделать следующие выводы в соответствии со свойствами таблиц, представленными в таблице 2.1. Таблица STUDENT представляет собой двумерную структуру, составленную из восьми строк (кортежей) и двенадцати столбцов. Можно также считать, что таблица состоит из восьми записей и двенадцати атрибутов (полей). Каждая строка в таблице STUDENT описывает отдельную сущность из имеющегося набора сущностей (набор сущностей представлен таблицей STUDENT). Обратите внимание, что строка (запись, или сущность), определенная как STU_NUM=321452, задает свойства (атрибуты, или поля) студента по имени Бунин Владимир. Данная таблица содержит набор сущностей STUDENT, включающий восемь отдельных сущностей (строк). Каждый столбец представляет атрибут, и каждый столбец имеет свое имя. На пересечении строк и столбцов содержится единственное значение. Каждая таблица должна иметь первичный ключ. В общих чертах первичный ключ (primary key) — это атрибут (или несколько атрибутов), уникально идентифицирующий данную сущность (строку). В нашем примере STU_NUM (идентификационный номер студента) выбран в качестве первичного ключа. Если внимательно присмотреться к данным, представленным на рис. 2.1, то можно увидеть, что фамилию (STU_LNAME) нельзя использовать в качестве первичного ключа, поскольку может встретиться несколько студентов с одинаковой фамилией. Даже комбинация фамилии (STU_LNAME) и имени (STU_FNAME) не подходит для первичного ключа, поскольку, как видно из рис. 2.1, вполне вероятно существование нескольких студентов с именем и фамилией Иван Симин. Все значения в столбце соответствуют свойствам атрибута сущности. Например, столбец средних оценок студента (STU_GPA) содержит только элементы STU_GPA для каждой из строк таблицы. Диапазон допустимых значений столбца называется доменом (domain). Поскольку значения STU_GPA ограничены диапазоном 0—4, то доменом этого столбца будет интервал значений [0,4]. Порядок следования строк и столбцов не имеет значения для пользователя. Тема 2.2. Ключи Ключ состоит из одного или более атрибутов, определяющих другие атрибуты (например, номер счета идентифицирует все атрибуты счета, такие как дата выписки, имя клиента и т. д.). Один тип ключа, называемый первичным ключом, уже был определен. Структура таблицы STUDENT, представленная на рис. 2.1, в которой определен и описан первичный ключ, выглядит достаточно простой. Однако поскольку роль первичного ключа в реляционной конфигурации столь значительна, мы подробно исследуем его свойства. Существует еще несколько типов ключей, заслуживающих внимание. В этом разделе мы узнаем, что представляют собой суперключ, потенциальный ключ (candidate key) и вторичный ключ (secondary key). Роль ключа основана на концепции определяемости. В контексте таблиц базы данных выражение "А определяет В" означает, что зная величину А, вы можете найти (определить) значение атрибута В. Например, если вы знаете STU_NUM в таблице STUDENT, то вы можете найти (определить) фамилию студента, средний балл, номер его телефона и т.д. Выражение "А определяет В" в сокращенном виде записывается как АÒ В. Если А определяет В, С и D, то нужно писать АÒ В, С, D. Поэтому, используя атрибуты таблицы STUDENT, мы можем записать выражение "STU_NUM определяет STU_LNAME" в виде:STU_NUMÒ STU_LNAME На самом деле, значение STU_NUM в таблице STUDENT определяет значения всех атрибутов студента. Например, можно записать:STU_NUMÒ STU_LNAME, STU_FNAME, STUJNIT иSTU_NUMÒ STU_LNAME, STU_FNAME, STUJNIT, STU_DOB, STU_TRANSFER И наоборот, атрибут STU_NUM не определяется атрибутом STU_LNAME, поскольку вполне вероятно, что имеется несколько студентов с фамилией Smith. Принцип определяемости очень важен, поскольку он используется при формулировании центральной концепции реляционных баз данных — функциональной зависимости. Функциональную зависимость нестрого можно сформулировать так: Атрибут В функционально зависит от атрибута А, если А определяет В. Более точно: Атрибут В функционально зависит от атрибута А, если каждое значение в столбце А определяет одно и только одно значение в столбце В. Используя содержимое таблицы STUDENT , можно сказать, что STU_PHONE функционально зависит от STU_NUM. Например, значение STU_NUM, равное 321 452, определяет значение STU_PHONE, равное 2134. С другой стороны, STU_NUM не зависит функционально от STU_PHONE, поскольку значение STU_PHONE, равное 2267, ассоциируется с двумя значениями STU_NUM: 324 274 и 324 291 (очевидно, некоторые студенты пользуются одним телефоном). Подобным образом значение STU_NUM, равное 324 273, определяет значение STU_LNAME Симин. Но значение STU_NUM не зависит функционально от STU_LNAME, поскольку фамилию Симин могут иметь несколько студентов. Определение функциональной зависимости должно быть обобщено таким образом, чтобы охватить те случаи, когда значение определяющего атрибута встречается в таблице более одного раза. Тогда функциональную зависимость можно определить так: Атрибут А определяет атрибут В (т. е. В функционально зависит от А), если все строки в таблице, совпадающие по значению атрибута А, также совпадают по значению атрибута В. Следует иметь в виду, что может потребоваться более чем один атрибут для определения функциональной зависимости, т. е. ключ может состоять из более, чем одного атрибута. Такие мультиатрибутные ключи называются составными ключами. Любой атрибут, который является частью составного ключа, называется ключевым атрибутом. Например, в таблице STUDENT фамилия студента не может служить ключом. С другой стороны, комбинация фамилии, имени, инициалов и домашнего телефона, по всей вероятности, уникально определяет оставшиеся атрибуты. Например, мы можем написать: STU_LNAME,STU_FNAME,STUJNIT,STU_PHONEÒ STU_HRS, STU_CLASS или STU_LNAME, STU_FNAME, STUJNIT, STU_PHONEÒ STU_HRS, STU_CLASS, STU_GPA или STU_LNAME, STU_FNAME, STUJNIT, STU_PHONE -> STU_HRS, STU_CLASS, STU_GPA, STU_DOB Учитывая возможность существования составного ключа, мы можем усовершенствовать определение функциональной зависимости и определить полную функциональную зависимость: Если атрибут (В) функционально зависит от составного ключа (А), но не зависит ни от какого-либо подмножества этого составного ключа, то говорят, что атрибут (В) полностью функционально зависит от (А). Потенциальный ключ это атрибут или набор атрибутов, который позволяет уникально идентифицировать отдельные экземпляры типа сущностей. Комбинация STU_LNAME, STU_FNAME, STUJNIT, STU_PHONE может считаться потенциальным ключом, если вы не учитываете случай, когда два студента имеют одинаковые фамилии, имена и телефонные номера. Если мы включим номер социального страхования студента в качестве одного из атрибутов таблицы STUDENT — например, с именем STU_SOCSECNUM — как он, так и STU_NUM могут быть потенциальными ключами, поскольку и тот и другой однозначно идентифицируют студента. В этом случае выбор STU_NUM в качестве первичного ключа можно было оставить на усмотрение проектировщика или согласовать этот вопрос с заказчиком. Первичный ключ это потенциальный ключ, который выбран в качестве первичного ключа. В пределах таблицы первичный ключ должен быть уникальным, чтобы однозначно идентифицировать каждую строку. Когда дело обстоит так, то говорят, что таблица проявляет целостность на уровне сущности (entity integrity). Для обеспечения целостности на уровне сущности в первичном ключе недопустимы пустые значения, null (т. е. полное отсутствие данных). Примечание . Пустое значение, null не есть нуль (zero) или пробел. Нажатие на клавиатуре клавиши <пробел> создает пропуск (пробел). Значение null создается в том случае, если вы нажимаете на клавиатуре клавишу <Enter> (Ввод) без предварительного ввода данных любого сорта.Хотя пустых значений, кроме тех, что обнаружены в первичном ключе, не всегда можно избежать в остальных атрибутах, их надо использовать с осторожностью. Если значения null применяются некорректно, они могут создавать проблемы, поскольку в них может вкладываться различный смысл. Например, отсутствие данных (null) может означать: неизвестное значение атрибута; известное, но неверное значение атрибута; некорректное условие. В зависимости от сложности программного обеспечения пустые значения (null) могут создавать проблемы при использовании таких функций, как COUNT, AVERAGE и SUM. Вдобавок значения null могут создать путаницу в логике при связывании реляционных таблиц. Контролируемая избыточность делает реляционную базу данных работоспособной. Таблицы внутри базы данных совместно используют общие атрибуты, что позволяет связывать таблицы друг с другом. Обратите внимание, например, что две таблицы PRODUCT (товар) и VENDOR (поставщик) на рис. 2.2 совместно используют атрибут VEND_CODE. Рис. 2.2. Пример простой реляционной базы данных Если внимательно посмотреть на рис. 2.2, то легко заметить, что значение VEND_CODE в одной таблице может использоваться для указания соответствующего значения в другой. Например, значение VEND_CODE, равное 235 в таблице PRODUCT, указывает на поставщика Георгий Орлов в таблице VENDOR. Следовательно, мы видим, что продукт Пила поставляется от Георгий Орлов, и по этому вопросу с ним можно связаться по телефону 615-899-3425. Помните наше соглашение об именах — мы используем префикс PROD, чтобы подчеркнуть "принадлежность" атрибутов таблице PRODUCT. Точно так же префикс VEND в VEND_CODE таблицы PRODUCT означает, что VEND_CODE указывает на некую другую таблицу в базе данных. В нашем случае префикс VEND используется для указания на таблицу VENDOR в базе данных. Связь между таблицами PRODUCT и VENDOR на рис. 2.2 может быть представлена реляционной схемой, приведенной на рис. 2.3. Рис. 2.3. Реляционная схема базы данных CH2_SALE_CO Мы можем описать связь, исходя из того, что она создается, когда две таблицы совместно используют один атрибут с общим значением. Конкретнее, первичный ключ одной таблицы вновь появляется в качестве внешнего (foreign) ключа в связанной таблице. Внешний ключ (foreign key) это атрибут, значение которого совпадает со значением первичного ключа в связанной таблице. Например, на рис. 2.2 VEND_CODE — первичный ключ таблицы VENDOR, и он же выступает в роли внешнего ключа в таблице PRODUCT. Поскольку таблица VENDOR не связана с третьей таблицей, в ней отсутствует внешний ключ. Если внешний ключ содержит значения, совпадающие с первичным ключом или пустые значения (null), говорят, что таблица (или таблицы), использующая такой ключ, проявляет целостность на уровне ссылки (referential integrity). Другими словами, целостность на уровне ссылки означает, что в том случае, если внешний ключ содержит некое значение, то это значение ссылается на существующий действительный кортеж (строку) в другом отношении. Обратите внимание, что между таблицами PRODUCT и VENDOR, представленными на рис. 2.2, целостность на уровне ссылки обеспечивается. Примечание . Помните, что пустое значение (null) не является значением в полном смысле. Например, на рис. 2.1 таблица STUDENT показывает, что значение STU_INIT для Юлии Брюловой пусто (null), т. е. студентка не имеет инициала. Другими словами, пустое значение в этом случае означает отсутствие какого-либо значения. Необходимо помнить, что пустое значение (null) это не то же самое, что пробел. Наконец, вторичный ключ определяется как ключ, который используется только для поиска данных. Предположим, данные клиента хранятся в таблице CUSTOMER, где в качестве первичного ключа используется идентификационный номер клиента. Вряд ли стоит рассчитывать, что клиенты помнят свой идентификационный номер. Однако необходимую информацию можно найти по фамилии клиента и номеру его телефона. В данном случае в качестве первичного ключа используется идентификационный номер клиента, вторичный ключ — это комбинация фамилии клиента и его номера телефона. Нужно иметь в виду, что вторичный ключ не требует уникальности. Например, фамилия клиента и домашний телефон могут встретиться не один раз, если несколько членов семьи Симиных живут в одном месте с одним телефонным номером. Точно так же комбинация фамилии и почтового индекса может привести к нескольким совпадениям, которые затем придется "разгрести" для поиска необходимой информации. Эффективность вторичного ключа в сужении области поиска зависит от заданных вами ограничений. Например, хотя вторичный ключ CUS_CITY и можно использовать при поиске информации по клиенту, все же значения атрибута "New York" или "Sydney" вряд ли можно считать подходящим результатом, если только вы не хотите проверять миллионы возможных совпадений. (Конечно, в качестве вторичного ключа все же лучше использовать CUS_CITY, чем CUS_COUNTRY.) Различные типы ключей таблиц реляционных баз данных приведены в табл. 2.2. Таблица 2.2. Ключи реляционных баз данных
Тип ключа | Определение
Суперключ | Атрибут (или комбинация атрибутов), уникально идентифицирующий каждую сущность в таблице
Потенциальный ключ | Минимальный суперключ. Суперключ, который не содержит подмножества атрибутов, которое само по себе является суперключом
Первичный ключ | Потенциальный ключ, выбранный для уникальной идентификации всех остальных значений атрибутов в любой строке. НЕ может содержать пустых значений (null)
Вторичный ключ | Атрибут (или комбинация атрибутов), используемый исключительно в целях поиска данных
Внешний ключ | Атрибут (или комбинация атрибутов) в одной таблице, значения которого должны или совпадать со значениями первичного ключа в другой таблице, или быть пустыми (null)
Тема 2.3. Правила целостности Впоследствии будет показано, что правила целостности реляционной базы данных очень важны для хорошего проекта БД. Многие (но далеко не все) системы управления реляционными базами данных (РСУБД) автоматически обеспечивают целостность данных. Однако куда проще и безопаснее убедиться самому, что проект приложения соответствует правилам целостностей на уровнях сущности и ссылок, о которых упоминалось ранее в этой главе. Эти правила представлены в табл. 2.3. Таблица 2.3. Правила целостности
ЦЕЛОСТНОСТЬ НА УРОВНЕ СУЩНОСТИ
Требование | Все элементы первичного ключа уникальны и никакая часть первичного ключа не может быть пустой (null)
Назначение | Гарантирует, что каждая сущность будет иметь уникальную идентификацию, а значения внешнего ключа могут должным образом ссылаться на значения первичного ключа
Пример | Счет не может иметь несколько дублирующихся значений и не может иметь пустое значение (null). Короче говоря, все счета уникально идентифицируются своим номером
ЦЕЛОСТНОСТЬ НА УРОВНЕ ССЫЛКИ
Требование | Внешний ключ может иметь или пустое значение (если только он не является частью первичного ключа данной таблицы), или значение, совпадающее со значением первичного ключа в связанной таблице. (Каждое непустое значение внешнего ключа должно ссылаться на существующее значение первичного ключа.)
Назначение | Допускается, что атрибут не имеет соответствующего значения, но атрибут не может принимать недопустимые значения. Выполнение правила целостности на уровне ссылки делает невозможным удаление строки в одной таблице, где первичный ключ имеет обязательное соответствие со значением внешнего ключа в другой таблице
Пример | Клиенту может быть не назначен (еще) торговый агент, но невозможно назначить клиенту несуществующего агента
Правила целостности, представленные в табл. 2.3, проиллюстрированы на рис.2.4. На рис. 2.4 обратите внимание на следующие особенности. Целостность на уровне сущности . Первичный ключ таблицы CUSTOMER — CUS_CODE. В столбце первичного ключа нет пустых значений и все его элементы уникальны. Точно так же первичным ключом таблицы AGENT служит атрибут AGENT_CODE, и в столбце первичного ключа также отсутствуют пустые элементы (null). Целостность на уровне ссылки . Таблица CUSTOMER содержит внешний ключ AGENT_CODE, который связывает элементы таблицы CUSTOMER с таблицей AGENT. Строка CUS_CODE, идентифицированная номером 10013 (первичный ключ), содержит пустой элемент во внешнем ключе AGENT_CODE, поскольку клиенту Ольхину П. еще не назначен торговый агент. Остальные элементы AGENT_CODE в таблице CUSTOMER соответствуют элементам AGENT_CODE таблице AGENT. Рис. 2.4. Правила целостности Чтобы избежать пустых значений (null), некоторые проектировщики используют специальные коды, которые называются флагами (flags) для обозначения отсутствия значения. Используя в качестве примера рис. 2.4, можно использовать код -99 в качестве значения атрибута AGENT_CODE в четвертой строке таблицы CUSTOMER для указания на то, что покупателю Ольхину П. еще не назначен торговый агент. Если используется такой флаг, таблица AGENT должна содержать фиктивную строку (фиктивного агента) со значением AGENT_CODE, равным -99. Таким образом, первая запись таблицы AGENT будет содержать значения, представленные в табл. 2.4. Таблица 2.4. Фиктивное значение, используемое в качестве флага
AGENT_ CODE | AGENT_ AREACODE | AGENT_ PHONE | AGENT_ LNAME | AGENT_ YTD_SALES
-99 | 000 | 000-0000 | None | $0.00
Тема 2.4. Системный каталог Системный каталог содержит метаданные. Системный каталог описывает все объекты внутри БД, включая данные об именах таблиц, создателе таблицы и дате ее создания, количестве столбцов в каждой таблице, типе данных соответствующих столбцов, именах индексных файлов, создателе индекса, авторизованных пользователях, полномочиях доступа и т. д. Современное программное обеспечение реляционных БД, как правило, сопровождается системным каталогом. Системный каталог фактически является базой данных, созданной системой, а таблицах этой БД хранятся свойства и информация о базе данных, созданной проектировщиком. Поэтому к таблицам системного каталога можно точно так же обращаться с запросами, как и к любым таблицам базы данных проектировщика. В сущности, системный каталог автоматически создает документацию по базе данных. По мере добавления в БД новых таблиц СУБД с помощью такой документации проверяет и устраняет омонимы и синонимы. В контексте баз данных слово "омоним" обозначает использование одного имени для обозначения разных атрибутов. Например, мы можем написать C_NAME для обозначения названия компании в таблице CUSTOMER и также использовать C_NAME для обозначения названия поставщика в таблице INVENTORY (инвентарь). Чтобы избежать путаницы, необходимо стараться избегать омонимов в базе данных, и словарь данных в этом смысле может оказать неоценимую помощь. В контексте баз данных синоним противоположен по значению омониму и подразумевает использование различных имен для обозначения одних и тех же атрибутов. Например, "машина" и "автомобиль" могут относиться к одному и тому же объекту. Синонимов тоже следует избегать. Поэтому, если один и тот же атрибут встречается в нескольких таблицах, то везде его лучше именовать одинаково. Другими словами, в идеальном случае внешний ключ должен иметь то же имя, что и первичный ключ в порождающей таблице. Вот почему на рис. 2.4 в качестве внешнего ключа в таблице CUSTOMER используется AGENT_CODE, и он же используется в качестве первичного ключа в таблице AGENT. Примечание . С точки зрения реляционных баз данных, в идеальной таблице имя каждого внешнего ключа совпадает с именем первичного ключа в связанной таблице. Например, в таблице INVOICE может использоваться ключ CUS_CODE для связи каждого счета с таблицей CUSTOMER, в которой CUS_CODE является первичным ключом. Такая практика также желательна и с точки зрения документирования БД. Тема 2.5. Связи в реляционной базе данных Связи в базе данных можно классифицировать по типу "один-к-одному" (1:1), один-ко-многим" (1:М) и "многие-ко-многим" (M:N или М:М). Как и предполагается запись 1:1 означает, что данная сущность может быть связана только с одной другой сущностью и наоборот. Например, один декан управляет одним факультетом и на факультете может быть только один декан. Поэтому сущности ДЕКАН и ФАКУЛЬТЕТ представляют тип связи 1:1. Связь 1:1 . Существование связи типа 1:1 часто означает, что компоненты сущности определены не совсем корректно. Вдобавок это указывает, что две данные сущности фактически находятся в одной таблице! Однако иногда бывает, что связи 1:1 вполне уместны. Например, предположим, вы управляете базой данных компании, в которой работают пилоты, бухгалтеры, механики, клерки, продавцы, обслуживающий персонал и т. д. Пилоты имеют множество атрибутов, которых нет у других сотрудников, например, лицензию, медицинский сертификат, налетанные часы, дату прохождения контроля профессиональной пригодности и допуск к полетам от постоянной медицинской комиссии. Если вы поместите все специфичные только для пилотов атрибуты в таблицу СОТРУДНИКИ, то в ней будет множество пустых мест (null) для сотрудников, не являющихся пилотами. Чтобы избежать этого, лучше всего выделить атрибуты пилота в отдельную таблицу ПИЛОТ и установить ее связь с таблицей СОТРУДНИКИ как 1:1. (Поскольку у пилотов есть много атрибутов, имеющихся у всех сотрудников, — имя, дата рождения и дата приема на работу — эти атрибуты разумнее хранить в таблице СОТРУДНИКИ). Связь 1:М практически идеальна для реляционной модели и является в ней основным кирпичиком. На самом деле связь 1:М настолько важна, что позже мы покажем, как можно конвертировать в нее другие типы связей, чтобы повысить эффективность базы данных. Для выявления связей сущностей и их моделирования начнем с исследования связи между двумя сущностями — ХУДОЖНИК и КАРТИНА. Поскольку картина создается только одним художником, но каждый художник может написать несколько картин, мы имеем дело со связью 1:М. Эту связь мы можем изучать с помощью ER-модели (модели "сущность-связь"). ER-модель обеспечивает простое и наглядное представление о связях между сущностями. Для отображения ER-модели мы будем использовать ER-диаграмму (ERD). Изображать ERD мы будем с помощью популярной формы представления на базе модели Чена, а также с помощью модели "птичья лапка". Мы будем придерживаться следующих соглашений при изображении ERD: как в модели Чена, так и в модели "птичья лапка" для представления сущностей используются прямоугольники; как в модели Чена, так и в модели "птичья лапка" в качестве имен сущностей используются имена существительные, изображаемые заглавными буквами. При меры: EMPLOYEE, INVOICE, DEPARTMENT. Обратите внимание, что имена сущностей приведены в единственном числе, форма множественного числа (EMPLOYEES, INVOICES и DEPARTMENTS) нежелательна; названия связей представляют собой глаголы в активной или пассивной форме Примеры: "пишет", "управляется", "изготовлена", "работает"; в модели Чена названия связей располагаются внутри ромбов. Ромб присоединяется к прямоугольнику, изображающему сущность, с помощью так называемой линии связи. В модели "птичья лапка" названия связей просто пишутся выше, ниже или рядом с линией связи, соединяющей прямоугольники, изображающие сущности; в модели Чена для обозначения части связи, означающей "один", используется цифра 1. В модели "птичья лапка" для этого используется черточка, пересекающая линию связи; в модели Чена для изображения части связи, означающей "многие", используется буква М. При изображении связи "многие-ко-многим" используются буквы М и N. В модели "птичья лапка" для части связи, представляющей "многие", используется стилизованное изображение трехпальцевой птичьей лапки (отсюда и название модели). Для связи "многие-ко-многим" такое изображение используется с обеих сторон линии связи. На рис. 2.5 представлены ER-диаграммы, отражающие связь 1:М между сущностями ХУДОЖНИК и КАРТИНА (художник пишет много картин, но каждая картина написана именно этим художником) и выполненные с учетом этих соглашений. Ориентация ER-диаграммы не играет существенной роли. Примечание . Необходимо помнить, что реляционная таблица эквивалентна набору сущностей. Однако разработчики, а, следовательно, и пользователи ER-моделей применяют термин "сущность" вместо "набор сущностей". Для отображения ER-моделирования на практике мы также будем использовать такую договоренность. Поэтому, когда вы встречаете слово "сущность" в контексте обсуждения ER-моделирования, помните, что здесь имеется в виду набор сущностей. Поэтому в ER-моделировании лучше всего сущности представлять в виде таблиц. Рис. 2.5. Связи между PAINTER и PAINTING Связь, представленная в предыдущей ER-модели, проиллюстрирована таблицами ХУДОЖНИК и КАРТИНА, показанными на рис. 2.6. В этих таблицах следует обратить внимание на следующие особенности: каждая картина написана только одним художником, но каждый ХУДОЖНИК может написать несколько картин. Например, в таблице КАРТИНА указаны три картины художницы 123 (Georgette P. Ross); в таблице ХУДОЖНИК может быть всего лишь одна строка для любой данной строки таблицы КАРТИНА, но в таблице КАРТИНА может быть несколько строк для данной строки таблицы ХУДОЖНИК. Точно также студенты университета знают, что на каждом КУРСе может иметься несколько групп, но каждая группа относится только к данному курсу. Например, на курсе БУХГАЛТЕРИЯ II могут иметься две группы: одна занимается по понедельникам, средам и пятницам с 10:00 до 10:50, другая - по вторникам с 18:00 до 20:40. Поскольку каждая группа обычно называется классом (КЛАСС), то между КУРС и КЛАСС имеет место связь 1:М, которую можно описать следующим образом: на каждом курсе (КУРС) может быть несколько групп (КЛАСС), но каждая группа (КЛАСС) имеет отношение только к данному курсу (КУРС); в таблице КУРС будет только одна строка для каждой данной строки таблицы КЛАСС, но в таблице КЛАСС может быть несколько строк для каждой данной строки таблицы КУРС; на рис. 2.6 изображена ER-модель связи 1:М между КУРС и КЛАСС. Эта же связь представлена на рис. 2.7. Рис. 2.6. Связь 1:М между КУРС и КЛАСС Используя рис. 2.7, договоримся о некоторой важной терминологии. Обратите внимание, что CLASS_CODE в таблице CLASS однозначно идентифицирует каждую строку. Поэтому мы выбираем CLASS_CODE в качестве первичного ключа. Однако комбинация CRS_CODE и CLASS_SECTION тоже уникально идентифицирует каждую строку в этой таблице. Другими словами, составной ключ, в который входят CRS_CODE и CLASS_SECTION, представляет собой потенциальный ключ. Рис. 2.7. Связь 1:М Связь "один-ко-многим" (1:М) очень просто реализуется в реляционной модели. Нужно только убедиться, что первичный ключ таблицы, представляющей сторону "один”, является внешним ключом таблицы, представляющей строну "многие". Например, на рис. 20 первичный ключ таблицы КУРС - CRS_CODE - является внешним ключом таблицы КЛАСС. Связь "многие-ко-многим" (M:N) труднее реализовать в реляционной среде. Однако мы покажем, что связь M:N можно разбить на несколько связей 1:М. Для исследования связи "многие-ко-многим" (M:N) рассмотрим типичный колледж в котором каждый студент (СТУДЕНТ) может входить в несколько групп (КЛАСС), а в каждой группе может быть несколько студентов (СТУДЕНТ). На рис. 2.8 представлена ER-диаграмма связи M:N. Рис. 2.8. Связь M:N между СТУДЕНТ и КЛАСС Обратите внимание на особенности ER-диаграммы, представленной на рис. 2.8. В каждой группе (КЛАСС) может быть несколько студентов (СТУДЕНТ), а каждый студент (СТУДЕНТ) может посещать несколько групп (КЛАСС). В таблице КЛАСС может быть несколько строк для данной строки в таблице СТУДЕНТ и в таблице СТУДЕНТ может быть несколько строк для данной строки таблицы КЛАСС. Чтобы рассмотреть связь M:N более детально, давайте представим себе небольшой колледж с двумя студентами, каждый из которых входит в три группы. В табл. 2.5 представлен регистрационный список двух студентов. Примечание . Система образования в примере предусматривает, что каждый лекционный курс как правило, разбивается на учебные группы или классы (CLASS). Поскольку студент занимается на нескольких курсах, он может входить в несколько групп (говорят, что студент "берет" - take - несколько курсов). Таблица 7. Пример регистрационного списка студентов
Фамилия студента | В какие группы записался
Иванов | Бухгалтерский учет 1, АССТ-211, код 10014Введение в микрокомпьютеры, QM-261, код 10021 Введение в статистику, CIS-220, код 10018
Петров | Бухгалтерский учет 1, АССТ-211, код 10014Введение в микрокомпьютеры, QM-261, код 10021 Введение в статистику, CIS-220, код 10018
Хотя связь M:N можно логически представить в виде ER-диаграмм (см. рис. 2.8), ее трудно изобразить на реляционной схеме (рис. 2.9) по двум главным причинам. Таблицы содержат много избыточной информации. Обратите внимание, что в таблице STUDENT несколько раз встречается значение STU_NUM. В реальной ситуации дополнительные атрибуты студента, например, адрес, специализация, домашний телефон и т. д. также содержатся в таблице STUDENT, и каждый из этих атрибутов повторяется в каждой записи, представленной в этой таблице. Точно так же в таблице CLASS содержится множество дублирующихся данных: каждый студент, который выбирает занятия в данной группе, создает запись в таблице CLASS. Проблема станет еще острее, если в таблицу CLASS включить такие атрибуты, как кредитные часы и описание курса. Такая избыточность приводит к аномалиям, которые мы обсуждали ранее. При такой структуре и наполнении этих двух таблиц реляционные операции становятся очень сложными и, как правило, приводят к снижению производительности и ошибкам при выводе информации. К счастью, можно легко избежать проблем, причиной которых является связь M:N, создав промежуточную сущность. Поскольку такая таблица используется для связывания таблиц, изначально связанных связью M:N, в структуру составной сущности включены (в качестве внешнего ключа), по крайней мере, первичные ключи связываемых таблиц. Проектировщик базы данных имеет две основные возможности для определения первичного ключа составной таблицы: использовать комбинацию этих внешних ключей или создать новый первичный ключ. Примечание. Каждый курс дает студенту определенное количество "кредитов" или "кредитных часов". Это число примерно равняется количеству часов по данному предмету, которое студент проводит в классе в течение недели. Обычно за один курс студенту засчитывается от 3 до 5 кредитов. Рис. 2.9. Связь "многие-ко-многим" между STUDENT и CLASS Необходимо помнить, что каждая сущность в ER-модели представляется таблицей. Поэтому для связывания таблиц CLASS и STUDENT мы можем создать таблицу (промежуточную) ENROLL (регистрационный список), представленную на рис. 2.10. Рис. 2.10. Связь "многие-ко-многим" между STUDENT и CLASS В этом примере первичный ключ таблицы ENROLL представляет собой комбинацию внешних ключей CLASS_CODE и STU_NUM. Но проектировщик может принять решение создать первичный ключ, состоящий из одного атрибута, например, ENROLL_LINE, используя его различные значения для уникальной идентификации каждой строки таблицы ENROLL (пользователи Microsoft Access могут использовать тип данных Счетчик)— для автоматического создания такого значения в каждой строке. Поскольку таблица ENROLL связывает две таблицы, она еще называется связующей таблицей. Другими словами, связующая таблица представляет собой реализацию составной сущности. Примечание . В дополнение к связующим атрибутам составная таблица ENROLL может содержать такие значимые атрибуты, как GRADE (оценка за курс). Фактически составная таблица может содержать любое количество атрибутов, которые пожелает учесть проектировщик. Связующая таблица ENROLL приводит связь M:N к виду 1:М. Заметим, что составная сущность, представленная таблицей ENROLL, должна содержать, по крайней мере, первичные ключи таблиц CLASS и STUDENT (соответственно CLASS_CODE и STU_NUM), для которых она служит связующим звеном. Особое внимание обратите на то, что таблицы STUDENT и CLASS теперь содержат только одну строку на каждую сущность. Связующая таблица ENROLL содержит несколько вхождений значения внешнего ключа, но такая контролируемая избыточность не вызовет аномалию данных при надлежащем обеспечении целостности на уровне ссылки. При необходимости можно ввести дополнительные атрибуты. В данном случае для создания отчетов выбран атрибут ENROLL_GRADE. Также обратите внимание, что первичный ключ таблицы ENROLL состоит из двух атрибутов CLASS_CODE и STU_NUM, поскольку для определения оценки студента по данному курсу необходимо знать как код группы (CLASS_CODE), так и идентификационный номер студента (STU_NUM). Естественно, это соглашение отражается и на ER-модели. Исправленная связь представлена на рис. 2.11. Рис. 2.11. Замена связи M:N на две связи 1:М На рис. 2.11 видно, что составная сущность с именем ENROLL представляет собой связующую таблицу между таблицами STUDENT и CLASS. В ER-диаграмме Чена для указания составной сущности использован ромб внутри прямоугольника. Расширим рис. 2.11 так, чтобы включить связь 1:М между COURSE и CLASS (рис. 2.12). Рис. 2.12. Расширенная ER-модель Реляционная схема, соответствующая ER-диаграмме на рис. 2.12, представлена на рис. 2.13. Рис. 2.13. Реляционная схема для ER-диаграммы Тема 2.6. Избыточность данных В разделе 1 мы пришли к выводу, что избыточность данных является причиной аномалии данных. Такие аномалии могут снизить эффективность БД. Мы также выяснили, что реляционная база данных позволяет контролировать избыточность данных с помощью общих атрибутов, совместно используемых таблицами. Такие общие атрибуты называются внешними ключами. Надлежащее использование внешних ключей играет решающую роль при контроле избыточности данных. Однако стоит подчеркнуть, что, строго говоря, использование внешних ключей не устраняет избыточность данных, поскольку внешний ключ может повторяться несколько раз. Тем не менее, правильное использование внешних ключей минимизирует избыточность данных, т. е. вероятность появления деструктивных аномалий в данных. Контроль избыточности данных имеет очень важное значение, и может случиться так, что нам придется фактически увеличить уровень избыточности данных с целью заставить БД выполнять определенные критические задачи. Давайте рассмотрим небольшую систему учета счетов. В систему входят клиенты (CUSTOMER), которые могут приобретать один или несколько товаров (PRODUCT), тем самым инициируя выписку счета (INVOICE). Поскольку клиент может купить более одного товара за один раз, счет может содержать несколько строк (LINE), в каждой из которых представлены сведения о купленном товаре. Таблица PRODUCT должна содержать цену товара, обеспечивая согласованное ценообразование по каждому продукту, который может появиться в счете. Таблицы этой системы представлены на рис. 2.14. Реляционная схема показана на рис. 2.15. Рис. 2.14. Небольшая система учета счетов Рис. 2.15. Реляционная схема для системы учета счетов Если внимательно посмотреть на таблицы этой системы (рис. 2.14) и на связи, представленные на реляционной схеме (рис. 2.15), то можно проследить типовые продажи. Например, отследив связи между четырьмя таблицами, мы обнаружим, что клиент 10014 (Орлов) приобрел две позиции 8 марта 2004 года, что было отражено в выписанном счете за номером 1001: одну пилу и два напильника (Подсказка: найдите две первых строки в таблице LINE, затем еще два совпадения PROD_CODE в таблице LINE со значениями PROD_CODE в таблице PRODUCT. Проследите связь INV_NUMBER таблицы LINE с таблицей INVOICE и строки 1001 таблицы INVOICE с таблицей CUSTOMER через CUS_CODE). Для выписки правильного чека будет использоваться прикладная программа, в которой число приобретенных позиций в строке счета (LINE_UNITS) умножается на цену каждой единицы (LINE_PRICE), результаты суммируются, начисляется соответствующий налог и т. д. Затем можно использовать другие программы, которые могут создавать отчеты по продажам, отслеживать и сравнивать объем продаж за неделю, месяц или год. В процессе исследования продаж разумно предположить, что цена товара, которая заносится в чек клиента, извлекается из таблицы PRODUCT, поскольку именно там хранится информация о товаре. Но почему та же цена товара встречается снова в таблице LINE? He является ли это избыточностью данных? Да, конечно, является. Но здесь избыточность необходима для эффективной работы системы. Копирование цены продукта из таблицы PRODUCT в таблицу LINE позволяет обеспечить ретроспективную точность транзакции. Предположим, что мы не записываем LINE_PRICE в таблице LINE, а используем для этого атрибут PROD_PRICE (цена товара) из таблицы PRODUCT при расчете выручки. Теперь предположим, что атрибут PROD_PRICE таблицы PRODUCT изменился. Новые цены должным образом будут отражены во всех текущих расчетах. Но, к сожалению, при расчете доходов и от старых сделок теперь также будут учитываться новые цены, что не соответствует действительности! Поэтому расчет доходов по всем последующим транзакциям будет неправильным, и это делает невозможным сравнение объемов продаж во времени. С другой стороны, если цены на товар копируются из таблицы PRODUCT и хранятся вместе с параметрами транзакции в таблице LINE, то эта цена в точности соответствует именно той сделке, которая имела место в то время. В дальнейшем станет понятно, что такая запланированная избыточность — обычное явление в хорошо спроектированных БД. Наконец, может вызвать удивление использование атрибута LINE_NUMBER в таблице LINE. Почему нельзя в качестве составного первичного ключа использовать комбинацию атрибутов INV_NUMBER и PROD_CODE и разве при этом атрибут LINE_NUMBER не является избыточным? Да конечно, атрибут LINE_NUMBER избыточен, но эта избыточность достаточно часто создается самим программным обеспечением выписки счетов, в котором номера строк счета генерируются автоматически. В данном случае эта избыточность не является необходимой. Но, учитывая то, что номера строк создаются автоматически, эта избыточность не будет причиной аномалии данных. Подключение атрибута LINE_NUMBER дает еще одно преимущество: порядок извлечения данных по счетам будет всегда соответствовать тому порядку, в котором эти данные были введены. Если код товара использовать в качестве составляющей первичного ключа, индексация упорядочит эти коды, как только выписка счета завершена, а данные сохранены. Вы только представьте себе возможную путаницу, когда клиент звонит и сообщает вам, что "во второй позиции в моем счете неправильно указана цена", а вы находите счет, в котором порядок строк совершенно не такой, как у клиента! Тема 2.7. Индексы Предположим, что вам необходимо найти определенную книгу в библиотеке. Будете ли вы искать книгу, просматривая каждую книгу библиотеки, до тех пор, пока не будет найдена нужная книга? Конечно, нет: вы воспользуетесь библиотечным каталогом, который индексирован по заголовкам, темам и авторам. Индекс (как в компьютерной, так и в неавтоматизированной системе) "указывает" вам местоположение вашей книги, тем самым обеспечивая быстрый и удобный поиск книги. Другой пример: вам необходимо найти в этой книге тему "ER-модели". Есть ли смысл листать все страницы в книге, пока вы не наткнетесь на искомую тему? Конечно же нет, проще обратиться к алфавитному указателю (индексу), найти фразу "ER-модель" и рядом с ней ссылку на нужную страницу. В каждом таком случае для быстрого поиска нужной позиции использовалось индексирование. Индексы в реляционной базе действуют так же, как только что описанные индексы. С концептуальной точки зрения индекс состоит из индексного ключа и набора указателей. Индексный ключ в сущности представляет собой контрольную точку индекса. Предположим, нам необходимо в базе данных MUSEUM (музей) найти все картины данного художника. Без индексирования мы должны считывать каждую строку таблицы КАРТИНЫ и искать в ней соответствие атрибута PAINTING_NUM выбранному нами художнику. Однако если мы проиндексируем таблицу ХУДОЖНИК по индексному ключу PAINTER_NUM, необходимо будет лишь найти подходящий атрибут PAINTER_NUM в индексе и найти соответствующего ему художника. Вообще говоря, такое индексирование должно выглядеть примерно так, как это представлено на рис. 2.16. Более формально — индексирование это упорядоченное расположение ключей и указателей. Каждый ключ указывает местоположение данных, идентифицированных ключом.
P_NUM (индексный ключ) | Указатели на таблицу PAINTING
123 | 1,2,4
126 | 3,5
Рис. 2.16. Компоненты индекса Вопросы для самопроверки 1. Что такое сущность? Как сущность связана с атрибутом? 2. Какие свойства имеет таблица? 3. В чем отличие между первичным и внешним ключом? 4. Может ли первичный ключ включать несколько атрибутов? 5. Какие виды целостности данных вы знаете? 6. Как реализуется на практике связь типа многие-ко-многим? 7. Для чего используются индексы? 8. Что такое отношение? 9. Что хранит системный каталог? 10. Что означает связь один-к-одному между данными?

назад | содержание | вперед