В случае реляционных баз данных трудно представить какие-либо общие рецепты по части физического проектирования. Здесь слишком много зависит от используемой СУБД. Поэтому ограничимся вопросами рассмотрения организации СУБД и ее архитектуры и более подробно вопросами логического проектирования реляционных баз данных, которые существенны при использовании любой реляционной СУБД.
Более того, мы не будем касаться очень важного аспекта проектирования - определения ограничений целостности (за исключением ограничения первичного ключа). Дело в том, что при использовании СУБД с развитыми механизмами ограничений целостности (например, SQL-ориентированных систем) трудно предложить какой-либо общий подход к определению ограничений целостности. Эти ограничения могут иметь очень общий вид, и их формулировка пока относится скорее к области искусства, чем инженерного мастерства. Самое большее, что предлагается по этому поводу в литературе, это автоматическая проверка непротиворечивости набора ограничений целостности.
Так что будем считать, что проблема проектирования реляционной базы данных состоит в обоснованном принятии решений о том:
· из каких отношений должна состоять БД;
· какие атрибуты должны быть у этих отношений.
Проект базы данных надо начинать с анализа предметной области и выявления требований к ней отдельных пользователей (сотрудников организации, для которых создается база данных). Подробнее этот процесс будет рассмотрен ниже, а здесь отметим, что проектирование обычно поручается человеку (группе лиц) – администратору базы данных (АБД). Им может быть как специально выделенный сотрудник организации, так и будущий пользователь базы данных, достаточно хорошо знакомый с машинной обработкой данных.
Объединяя частные представления о содержимом базы данных, полученные в результате опроса пользователей, и свои представления о данных, которые могут потребоваться в будущих приложениях, АБД сначала создает обобщенное неформальное описание создаваемой базы данных.
Типовая организация современной СУБД
Организация типичной СУБД и состав ее компонентов соответствует рассмотренному набору функций. Напомним, что мы выделили следующие основные функции СУБД:
- управление данными во внешней памяти; управление буферами оперативной памяти; управление транзакциями; журнализация и восстановление БД после сбоев; поддержание языков БД.
Логически в современной реляционной СУБД можно выделить:
- внутреннюю часть - ядро СУБД (часто его называют Data Base Engine),
- компилятор языка БД (обычно SQL),
- подсистему поддержки времени выполнения,
- набор утилит.
В некоторых системах эти части выделяются явно, в других - нет, но логически такое разделение можно провести во всех СУБД.
Ядро СУБД отвечает за управление данными во внешней памяти, управление буферами оперативной памяти, управление транзакциями и журнализацию.
Соответственно, можно выделить такие компоненты ядра (по крайней мере, логически), как менеджер данных, менеджер буферов, менеджер транзакций и менеджер журнала. Функции этих компонентов взаимосвязаны, и для обеспечения корректной работы СУБД все эти компоненты должны взаимодействовать по тщательно продуманным и проверенным протоколам.
Ядро СУБД обладает собственным интерфейсом, не доступным пользователям напрямую и используемым в программах, производимых компилятором SQL и утилитах БД.
Ядро СУБД является основной резидентной частью СУБД. При использовании архитектуры "клиент-сервер" ядро является основной составляющей серверной части системы.
Основной функцией компилятора языка БД является компиляция операторов языка БД в некоторую выполняемую программу. Основной проблемой реляционных СУБД является то, что языки этих систем (а это, как правило, SQL) являются непроцедурными, т. е. в операторе такого языка специфицируется некоторое действие над БД, но эта спецификация не является процедурой, а лишь описывает в некоторой форме условия совершения желаемого действия. Поэтому компилятор должен решить, каким образом выполнять оператор языка прежде, чем произвести программу. Применяются достаточно сложные методы оптимизации операторов, результатом компиляции является выполняемая программа, представляемая в некоторых системах в машинных кодах, (или в выполняемом внутреннем машинно-независимом коде).
В отдельные утилиты БД обычно выделяют такие процедуры, которые слишком накладно выполнять с использованием языка БД, например, загрузка и выгрузка БД, сбор статистики, глобальная проверка целостности БД и т. д. Утилиты программируются с использованием интерфейса ядра СУБД.
СУБД должна предоставлять доступ к данным любым пользователям, включая и тех, которые практически не имеют и (или) не хотят иметь представления о:
- физическом размещении в памяти данных и их описаний; механизмах поиска запрашиваемых данных; проблемах, возникающих при одновременном запросе одних и тех же данных многими пользователями (прикладными программами); способах обеспечения защиты данных от некорректных обновлений и (или) несанкционированного доступа; поддержании баз данных в актуальном состоянии и множестве других функций СУБД.
При выполнении основных из этих функций СУБД должна использовать различные описания данных.
2.1.9. Цель проектирования. Проектирование реляционной базы данных с использованием нормализации.
Отдельные БД могут объединять все данные, необходимые для решения одной или нескольких прикладных задач, или данные, относящиеся к какой-либо предметной области (например, финансам, студентам, преподавателям, кулинарии и т. п.). Первые обычно называют прикладными БД, а вторые – предметными БД (соотносящимся с предметами организации, а не с ее информационными приложениями).Первые можно сравнить с базами материально-технического снабжения или отдыха, а вторые – с овощными и обувными базами.)
Предметные БД позволяют обеспечить поддержку любых текущих и будущих приложений, поскольку набор их элементов данных включает в себя наборы элементов данных прикладных БД. Вследствие этого предметные БД создают основу для обработки неформализованных, изменяющихся и неизвестных запросов и приложений (приложений, для которых невозможно заранее определить требования к данным). Такая гибкость и приспосабливаемость позволяет создавать на основе предметных БД достаточно стабильные информационные системы, т. е. системы, в которых большинство изменений можно осуществить без вынужденного переписывания старых приложений.
Основывая проектирование БД на текущих приложениях, можно существенно ускорить создание высокоэффективной информационной системы, т. е. системы, структура которой учитывает наиболее часто встречающиеся пути доступа к данным. Поэтому прикладное проектирование до сих пор привлекает некоторых разработчиков. Однако по мере роста числа приложений таких информационных систем быстро увеличивается число прикладных БД, резко возрастает уровень дублирования данных и повышается стоимость их ведения.
Желание достичь и гибкости, и эффективности привело к формированию методологии проектирования, использующей как предметный, так и прикладной подходы.
В общем случае предметный подход используется для построения первоначальной информационной структуры, а прикладной – для ее совершенствования с целью повышения эффективности обработки данных.
При проектировании информационной системы необходимо провести анализ целей этой системы и выявить требования к ней отдельных пользователей.
Сбор данных начинается с изучения сущностей организации и процессов, использующих эти сущности. Сущности группируются по «сходству» (частоте их использования для выполнения тех или иных действий) и по количеству ассоциативных связей между ними (самолет – пассажир, преподаватель – дисциплина, студент – сессия и т. д.).
Сущности или группы сущностей, обладающие наибольшим сходством и (или) с наибольшей частотой ассоциативных связей объединяются в предметные БД.
Основная цель проектирования БД – это сокращение избыточности хранимых данных, а следовательно, экономия объема используемой памяти, уменьшение затрат на многократные операции обновления избыточных копий и устранение возможности возникновения противоречий из-за хранения в разных местах сведений об одном и том же объекте. Так называемый, «чистый» проект БД – «каждый факт в одном месте» можно создать, используя методологию нормализации отношений. И хотя нормализация должна использоваться на завершающей проверочной стадии проектирования БД, начнем обсуждение вопросов проектирования с рассмотрения причин, которые заставили Кодда создать основы теории нормализации
2.1.10. Нормальные формы.
Выше было дано определение первой нормальной формы (1НФ). Дадим более строгое определение, а также определения других нормальных форм.
Таблица находится в первой нормальной форме (1НФ) тогда и только тогда, когда ни одна из ее строк не содержит в любом своем поле более одного значения и ни одно из ее ключевых полей не пусто.
Таблица находится во второй нормальной форме (2НФ), если она удовлетворяет определению 1НФ и все ее поля, не входящие в первичный ключ, связаны полной функциональной зависимостью с первичным ключом.
Рассмотрим следующий пример схемы отношения:
СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ
(СОТР_НОМ, СОТР_ЗАРП, ОТД_НОМ, ПРО_НОМЕР, СОТР_ЗАДАН)
Первичный ключ:
СОТР_НОМ, ПРО_НОМ
Функциональные зависимости:
СОТР_НОМ ( СОТР_ЗАРП
СОТР_НОМ ( ОТД_НОМ
ОТД_НОМ ( СОТР_ЗАРП
СОТР_НОМ, ПРО_НОМ ( СОТР_ЗАДАН
Хотя первичным ключом является составной атрибут СОТР_НОМ, ПРО_НОМ, атрибуты СОТР_ЗАРП и ОТД_НОМ функционально зависят от части первичного ключа, атрибута СОТР_НОМ. В результате нельзя вставить в отношение СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ кортеж, описывающий сотрудника, который еще не выполняет никакого проекта (первичный ключ не может содержать неопределенное значение).
При удалении кортежа мы не только разрушаем связь данного сотрудника с данным проектом, но утрачиваем информацию о том, что он работает в некотором отделе. При переводе сотрудника в другой отдел мы будем вынуждены модифицировать все кортежи, описывающие этого сотрудника, или получим несогласованный результат. Такие неприятные явления называются аномалиями схемы отношения. Они устраняются путем нормализации.
Вторая нормальная форма (в этом определении предполагается, что единственным ключом отношения является первичный ключ). Отношение R находится во второй нормальной форме (2НФ) в том и только в том случае, когда находится в 1НФ, и каждый неключевой[2] атрибут полностью зависит от первичного ключа.
Можно произвести следующую декомпозицию отношения СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ в два отношения СОТРУДНИКИ-ОТДЕЛЫ и СОТРУДНИКИ-ПРОЕКТЫ:
СОТРУДНИКИ-ОТДЕЛЫ (СОТР_НОМ, СОТР_ЗАРП, ОТД_НОМ)
Первичный ключ:
СОТР_НОМ
Функциональные зависимости:
СОТР_НОМ ( СОТР_ЗАРП
СОТР_НОМ ОТД_НОМ
ОТД_НОМ СОТР_ЗАРП
СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМЕР, ПРО_НОМЕР, СОТР_ЗАДАН)
Первичный ключ:
СОТР_НОМ, ПРО_НОМ
Функциональные зависимости:
СОТР_НОМ, ПРО_НОМ ( СОТР_ЗАДАН
Каждое из этих двух отношений находится в 2НФ, и в них устранены отмеченные выше аномалии.
Если допустить наличие нескольких ключей, то определение второй нормальной формы будет иметь следующий вид:
Отношение R находится во второй нормальной форме (2НФ) в том и только в том случае, когда оно находится в 1НФ, и каждый неключевой атрибут полностью зависит от каждого ключа R.
Отношения с несколькими ключами слишком громоздки и относятся к ситуациям, редко встречающимся на практике.
Таблица находится в третьей нормальной форме (3НФ), если она удовлетворяет определению 2НФ и не одно из ее неключевых полей не зависит функционально от любого другого неключевого поля.
Рассмотрим отношение СОТРУДНИКИ-ОТДЕЛЫ, находящееся в 2НФ. Функциональная зависимость СОТР_НОМ СОТР_ЗАРП является следствием функциональных зависимостей СОТР_НОМ ОТД_НОМ и ОТД_НОМ СОТР_ЗАРП. Другими словами, заработная плата сотрудника является характеристикой не сотрудника, а отдела, в котором он работает (это не очень естественное предположение, но достаточное для примера).
В результате нельзя занести в базу данных информацию, характеризующую заработную плату отдела, до тех пор, пока в этом отделе не появится хотя бы один сотрудник (первичный ключ не может содержать неопределенное значение). При удалении кортежа, описывающего последнего сотрудника данного отдела, мы лишимся информации о заработной плате отдела. Чтобы согласованным образом изменить заработную плату отдела, мы будем вынуждены предварительно найти все кортежи, описывающие сотрудников этого отдела, т. е. в отношении СОТРУДНИКИ-ОТДЕЛЫ по-прежнему существуют аномалии. Их можно устранить путем дальнейшей нормализации.
Третья нормальная форма (определение дается в предположении существования единственного ключа). Отношение R находится в третьей нормальной форме (3НФ) в том и только в том случае, если находится в 2НФ и ни один из неключевых атрибутов в R атрибут нетранзитивно зависит от первичного ключа.
Произведем декомпозицию отношения СОТРУДНИКИ-ОТДЕЛЫ в два отношения СОТРУДНИКИ и ОТДЕЛЫ:
СОТРУДНИКИ (СОТР_НОМ, ОТД_НОМ)
Первичный ключ:
СОТР_НОМ
Функциональные зависимости:
СОТР_НОМ ОТД_НОМ
ОТДЕЛЫ (ОТД_НОМ, СОТР_ЗАРП)
Первичный ключ:
ОТД_НОМ
Функциональные зависимости:
ОТД_НОМ СОТР_ЗАРП
Каждое из этих двух отношений находится в 3NF и свободно от отмеченных аномалий.
Если отказаться ограничения о единственном ключе, то определение 3NF примет следующую вид:
Отношение R находится в третьей нормальной форме (3НФ) в том и только в том случае, если оно находится в 1НФ, и каждый неключевой атрибут не является транзитивно зависимым от какого-либо ключа R.
На практике третья нормальная форма схем отношений достаточна в большинстве случаев, и приведением к третьей нормальной форме процесс проектирования реляционной базы данных обычно заканчивается. Однако иногда полезно продолжить процесс нормализации.
Таблица находится в нормальной форме Бойса-Кодда (НФБК), если и только если любая функциональная зависимость между его полями сводится к полной функциональной зависимости от возможного ключа.
Рассмотрим пример схемы отношения:
СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМ, СОТР_ИМЯ, ПРО_НОМ, СОТР_ЗАДАН)
Возможные ключи:
СОТР_НОМ, ПРО_НОМ
СОТР_ИМЯ, ПРО_НОМ
Функциональные зависимости:
СОТР_НОМЕР CОТР_ИМЯ
СОТР_НОМ ПРО_НОМ
СОТР_ИМЯ CОТР_НОМ
СОТР_ИМЯ ПРО_НОМ
СОТР_НОМ, ПРО_НОМ CОТР_ЗАДАН
СОТР_ИМЯ, ПРО_НОМ CОТР_ЗАДАН
В этом примере мы предполагаем, что личность сотрудника полностью определяется как его номером, так и именем (это не очень жизненное предположение, но достаточное для примера).
В соответствии с определением отношение СОТРУДНИКИ-ПРОЕКТЫ находится в 3НФ. Однако тот факт, что имеются функциональные зависимости атрибутов отношения от атрибута, являющегося частью первичного ключа, приводит к аномалиям. Например, для того, чтобы изменить имя сотрудника с данным номером согласованным образом, потребуется модифицировать все кортежи, включающие его номер.
Нормальная форма Бойса-Кодда .Отношение R находится в нормальной форме Бойса-Кодда (НФБК) в том и только в том случае, если каждый детерминант[3] является возможным ключом.
Очевидно, что это требование не выполнено для отношения СОТРУДНИКИ-ПРОЕКТЫ. Необходимо произвести его декомпозицию к отношениям СОТРУДНИКИ и СОТРУДНИКИ-ПРОЕКТЫ.
СОТРУДНИКИ (СОТР_НОМ, СОТР_ИМЯ)
Возможные ключи:
СОТР_НОМ
СОТР_ИМЯ
Функциональные зависимости:
СОТР_НОМ CОТР_ИМЯ
СОТР_ИМЯ СОТР_НОМ
СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМ, ПРО_НОМ, СОТР_ЗАДАН)
Возможный ключ:
СОТР_НОМ, ПРО_НОМ
Функциональные зависимости:
СОТР_НОМ, ПРО_НОМ CОТР_ЗАДАН
Возможна альтернативная декомпозиция, если выбрать за основу СОТР_ИМЯ. В обоих случаях получаемые отношения СОТРУДНИКИ и СОТРУДНИКИ-ПРОЕКТЫ находятся в НФБК, и им не свойственны отмеченные аномалии.
В следующих нормальных формах (4НФ и 5НФ) учитываются не только функциональные, но и многозначные зависимости между полями таблицы.
Для их описания введено понятие полной декомпозиции таблицы.
Полной декомпозицией таблицы называют такую совокупность произвольного числа ее проекций, соединение которых полностью совпадает с содержимым таблицы.
Теперь можно дать определения высших нормальных форм.
Рассмотрим пример следующей схемы отношения:
ПРОЕКТЫ (ПРО_НОМ, ПРО_СОТР, ПРО_ЗАДАН)
Отношение ПРОЕКТЫ содержит номера проектов, для каждого проекта список сотрудников, которые могут выполнять проект, и список заданий, предусматриваемых проектом.
Сотрудники могут участвовать в нескольких проектах, и разные проекты могут включать одинаковые задания.
Каждый кортеж отношения связывает некоторый проект с сотрудником, участвующим в этом проекте, и заданием, который сотрудник выполняет в рамках данного проекта (предполагаем, что любой сотрудник, участвующий в проекте, выполняет все задания, предусмотренные этим проектом). По причине сформулированных выше условий единственным возможным ключом отношения является составной атрибут ПРО_НОМ, ПРО_СОТР, ПРО_ЗАДАН, и нет никаких других детерминантов. Следовательно, отношение ПРОЕКТЫ находится в НФБК. Но при этом оно обладает недостатками: если, например, некоторый сотрудник присоединяется к данному проекту, необходимо вставить в отношение ПРОЕКТЫ столько кортежей, сколько заданий в нем предусмотрено.
В отношении ПРОЕКТЫ существуют следующие две многозначные зависимости:
ПРО_НОМ ПРО_СОТР
ПРО_НОМ ПРО_ЗАДАН
Легко показать, что в общем случае в отношении R (A, B, C) существует многозначная зависимость R.A R.B в том и только в том случае, когда существует многозначная зависимость R.A R.C.
Четвертая нормальная форма .Отношение R находится в четвертой нормальной форме (4НФ) в том и только в том случае, если в случае существования многозначной зависимости A B все остальные атрибуты R функционально зависят от A.
В нашем примере можно произвести декомпозицию отношения ПРОЕКТЫ в два отношения ПРОЕКТЫ-СОТРУДНИКИ и ПРОЕКТЫ-ЗАДАНИЯ:
ПРОЕКТЫ-СОТРУДНИКИ (ПРО_НОМ, ПРО_СОТР)
ПРОЕКТЫ-ЗАДАНИЯ (ПРО_НОМ, ПРО_ЗАДАН)
Оба эти отношения находятся в 4НФ и свободны от отмеченных аномалий.
Во всех рассмотренных до этого момента нормализациях производилась декомпозиция одного отношения в два. Иногда это сделать не удается, но возможна декомпозиция в большее число отношений, каждое из которых обладает лучшими свойствами.
Рассмотрим, отношение
СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ (СОТР_НОМ, ОТД_НОМ, ПРО_НОМ)
Предположим, что один и тот же сотрудник может работать в нескольких отделах и работать в каждом отделе над несколькими проектами. Первичным ключом этого отношения является полная совокупность его атрибутов, отсутствуют функциональные и многозначные зависимости. Поэтому отношение находится в 4НФ. Однако в нем могут существовать аномалии, которые можно устранить путем декомпозиции в три отношения.
Введем понятие зависимость соединения. Отношение R (X, Y, ..., Z) удовлетворяет зависимости соединения * (X, Y, ..., Z) в том и только в том случае, когда R восстанавливается без потерь путем соединения своих проекций на X, Y, ..., Z.
Пятая нормальная форма. Отношение R находится в пятой нормальной форме в том и только в том случае, когда любая зависимость соединения в R следует из существования некоторого возможного ключа в R. Другими словами, таблица находится в пятой нормальной форме (5НФ) тогда и только тогда, когда в каждой ее полной декомпозиции все проекции содержат возможный ключ. Таблица, не имеющая ни одной полной декомпозиции, также находится в 5НФ.
Введем следующие имена составных атрибутов:
СО = {СОТР_НОМ, ОТД_НОМ}
СП = {СОТР_НОМ, ПРО_НОМ}
ОП = {ОТД_НОМ, ПРО_НОМ}
Предположим, что в отношении СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ существует зависимость соединения:
* (СО, СП, ОП)
На примерах легко показать, что при вставках и удалениях кортежей могут возникнуть проблемы. Их можно устранить путем декомпозиции исходного отношения в три новых отношения:
СОТРУДНИКИ-ОТДЕЛЫ (СОТР_НОМ, ОТД_НОМ)
СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМ, ПРО_НОМ)
ОТДЕЛЫ-ПРОЕКТЫ (ОТД_НОМ, ПРО_НОМ)
Пятая нормальная форма - это последняя нормальная форма, которую можно получить путем декомпозиции. Ее условия достаточно нетривиальны, и на практике 5NF не используется.
Заметим, что зависимость соединения является обобщением как многозначной зависимости, так и функциональной зависимости.
Четвертая нормальная форма (4НФ) является частным случаем 5НФ, когда полная декомпозиция должна быть соединением ровно двух проекций. Весьма не просто подобрать реальную таблицу, которая находилась бы в 4НФ, но не была бы в 5НФ.
2.1.11. Процедура нормализации.
Как уже отмечалось, нормализация – это разбиение таблицы на несколько, обладающих лучшими свойствами при обновлении, включении и удалении данных.
Можно дать и другое определение: нормализация – это процесс последовательной замены таблицы ее полными декомпозициями до тех пор, пока все они не будут находиться в 5НФ. На практике же достаточно привести таблицы к НФБК и с большой гарантией считать, что они находятся в 5НФ. Разумеется, этот факт нуждается в проверке, однако пока не существует эффективного алгоритма такой проверки. Поэтому остановимся лишь на процедуре приведения таблиц к НФБК.
Эта процедура основывается на том, что единственными функциональными зависимостями в любой таблице должны быть зависимости вида K F, где K – первичный ключ, а F – некоторое другое поле. Это следует из определения первичного ключа таблицы, в соответствии с которым K F всегда имеет место для всех полей данной таблицы. «Один факт в одном месте» говорит о том, что не имеют силы никакие другие функциональные зависимости. Цель нормализации состоит именно в том, чтобы избавиться от всех этих «других» функциональных зависимостей, т. е. таких, которые имеют иной вид, чем K F.
Следует рассмотреть два случая:
1. Таблица имеет составной первичный ключ вида, скажем, (К1,К2), и включает также поле F, которое функционально зависит от части этого ключа, например, от К2, но не от полного ключа. В этом случае рекомендуется сформировать другую таблицу, содержащую К2 и F (первичный ключ – К2), и удалить F из первоначальной таблицы:
Заменить T(K1,K2,F), первичный ключ (К1,К2), К2 F
на T1(K1,K2), первичный ключ (К1,К2),
и T2(K2,F), первичный ключ К2.
2. Таблица имеет первичный (возможный) ключ К и не являющееся возможным ключом поле F1, которое, конечно, функционально зависит от К, и другое неключевое поле F2, которое функционально зависит от F1. Решение здесь, по существу, то же самое – формируется другая таблица, содержащая F1 и F2, с первичным ключом F1, и F2 удаляется из первоначальной таблицы:
Заменить T(K, F1,F2), первичный ключ К, F1 F2
на T1(K, F1), первичный ключ К,
и T2(F1,F2), первичный ключ F1.
Для любой заданной таблицы, повторяя применение двух рассмотренных правил, почти во всех практических ситуациях можно получить в конечном счете множество таблиц, которые находятся в «окончательной» нормальной форме и, таким образом, не содержат каких-либо функциональных зависимостей вида, отличного от K F.
Для выполнения этих операций необходимо первоначально иметь в качестве входных данных какие-либо большие таблицы (например, универсальные отношения). Но нормализация ничего не говорит о том, как получить эти большие таблицы. В следующем разделе будет рассмотрена процедура получения таких исходных таблиц.
Рассмотренные правила для полной нормализации некоторого универсального отношения предполагают выполнение следующих шагов:
Шаг 1. Определение первичного ключа таблицы.
Шаг 2. Выявление полей, функционально зависящих от ключа (от части составного ключа).
Шаг 3. Формирование новых таблиц.
Шаг 4. Корректировка исходной таблицы.
Тема 2.2 Microsoft Access
Назначение любой системы управления базами данных (СУБД) - создание, ведение и обработка баз данных. Как в текстовом редакторе можно подготовить много разных документов, так в СУБД Access можно создать много разных баз данных.
Система управления базами данных предоставляет значительные возможности по работе с хранящимися данными, их обработке и совместному использованию. Можно выбирать любые поля, форматы полей, сортировать данные, вычислять итоговые значения. Можно отбирать интересующие данные по какому-либо признаку, менять их, удалять, копировать в другие таблицы.
Можно производить обмен данными между компонентами СУБД Access и другими приложениями Windows. Это могут быть рисунки, диаграммы и т. д. Поддерживается экспорт и импорт данных из текстовых файлов и электронных таблиц.
При коллективном использовании СУБД Access дает возможность защитить информацию так, что разные пользователи имеют разные права по просмотру или изменению информации: при этом предусмотрены средства обеспечения целостности данных.
Каждая база данных хранится на диске в виде файла с расширением mdb. При запуске СУБД Access появляется меню для работы с компонентами базы данных.
Ниже описываются основные компоненты базы даны:
Таблицы. Основная информация хранится в таблицах. Таблица -совокупность записей. Столбцы в таблице называются полями, а строки - записями. Количество записей в таблице ограничивается емкостью жесткого диска. Допустимое количество полей - 255. Таблиц в базе данных может быть несколько. Сведения по разным вопросам следует хранить в разных таблицах. Для работы таблицу необходимо открыть. Перед окончанием работы ее следует закрыть, предварительно сохранив все изменения, произведенные в ходе работы.
С таблицами можно работать в двух режимах - таблицы и конструктора. Переход из режима таблицы в режим конструктора таблицы и обратно производится щелчком по кнопке Вид, расположенной на панели инструментов. Ключевое поле - поле с уникальными записями. Таблицы связываются (дается указание на соответствие записей) по ключам; ключ может состоять из одного или из нескольких полей.
Все объекты базы данных можно импортировать, т. е. копировать из других баз данных, а не вводить заново. Если таблицы были связаны в старой базе данных, то они таким же образом будут связаны и в новой.
В режиме таблицы обычно просматривают, добавляют и изменяют данные. Можно также добавлять или удалять столбцы таблицы, изменять внешний вид таблицы (ширину столбцов, их порядок, вид и цвет шрифта и т. д). Можно проверить орфографию и напечатать табличные данные, фильтровать и сортировать записи. В режиме конструктора таблицы можно создать новую таблицу или изменить поля старой.
Формы. Форма представляет собой специальный формат экрана, используемый для разных целей, чаще всего для ввода данных в таблицу и просмотра одной записи. Формы позволяют вводить данные, корректировать их, добавлять и удалять записи. Можно создавать формы для работы, одновременно с несколькими взаимосвязанными таблицами. Форма, использующая данные из нескольких таблиц, должна быть основана на запросе, включающем данные из этих таблиц.
С применением форм можно представлять записи в удобном для пользователя виде - в виде привычных документов: бланков, экзаменационных ведомостей и т. д. Формы ввода-вывода позволяют вводить данные в базу, просматривать их, изменять значения полей, добавлять и удалять записи.
Все элементы, добавляемые в форму, - поля, надписи, списки, переключатели, кнопки, линии - являются элементами управления. Способ создания элемента управления зависит от того, какой элемент создается: присоединенный, свободный или вычисляемый.
Запросы. Запрос - это инструмент для анализа, выбора и изменения данных. С помощью запросов можно просматривать, анализировать и изменять данные из нескольких таблиц. Запросы используются также в качестве источника данных для форми отчетов.
С помощью Access могут быть созданы несколько видов запросов. Запрос на выборку выбирает данные из разных таблиц и других готовых запросов. Запрос-изменение изменяет или перемещает данные; к этому типу относятся Запрос на добавление, Запрос на удаление и Запрос на обновление. Запрос на создание таблицы сохраняет результаты выборки в отдельной таблице. Перекрестные запросы предназначены для группирования данных и представления их в компактном виде. Запрос можно создать самостоятельно или воспользоваться .Мастером запросов.
Элементы выражения в запросах могут быть связаны операторами:
арифметическими: *, +, -, /, ^
сравнения: <, <=, >, >=, =, <>;
логическими: And (И), Not (Нет), Or (Или);
Like - для использования логики замены в выражениях,
In - для определения, содержится ли элемент данных в списке значений;
Between...And - для выбора значений из определенного интервала.
Между условиями в разных полях одного столбца выполняется логическая операция ИЛИ (Or). Она истинна, когда истинно хотя бы одно из входящих в список условий.
Между условиями в разных полях одной строки выполняется логическая операция И (And). Она истинна, когда истинны все входящие в список условия.
Отчеты. Отчет - это гибкое и эффективное средство для организации данных при выводе на печать и вместе с тем это, способ вывода данных из базы на печать в том виде, в котором требуется пользователю, например, в виде справок об обучении, экзаменационных ведомостей, таблиц, объединенных каким-либо признаком, и др. С помощью отчета можно расположить информацию на листе в удобном для пользователя виде с различным оформлением. Можно разработать отчет самостоятельно с помощью Конструктора, использовать готовые варианты оформления (автоотчеты) или создать отчет с помощью Мастера.
Макросы и модули. Макросом называют набор из одной или более макрокоманд, выполняющих определенные операции, такие, как открытие форм или печать отчетов. Макросы могут быть полезны для автоматизации часто выполняемых задач. Например, при нажатии пользователем кнопки можно запустить макрос, который распечатает отчет. Модуль - это программа на языке Access Basic.
Тема 2.3. Введение в SQL (Structured Query Language)
Реляционная база данных представляется пользователю как совокупность таблиц и ничего кроме таблиц. На рисунке приведен пример реляционной базы данных ПАНСИОН. Этот простой пример используется для иллюстрации большинства вопросов, рассматриваемых в нашей книге. Поэтому советуем потратить немного времени, чтобы хорошо с ним разобраться*.
Кладовая пансионата периодически пополняется продуктами из списка, часть которого показана в таблице Продукты. Каждый продукт имеет кроме названия (столбец Продукт) уникальный номер этого продукта (столбец ПР). Химический состав продуктов приведен для 1 кг их съедобной части: основные пищевые вещества (белки, жиры и углеводы) даны в граммах, а минеральные вещества (калий, кальций, натрий) и витамины (B2, PP, C) - в миллиграммах.
В таблице Блюда представлены уникальные номера блюд (столбец БЛ), их названия, коды видов (см. таблицу Вид_блюд), основной продукт (столбец Основа), масса порции в граммах (столбец Выход) и приведенная стоимость в копейках приготовления одной порции (столбец Труд).
В таблице Рецепты приведена технология приготовления блюд. Их выделение в отдельную таблицу произведено потому, что одно и то же блюдо может иметь несколько разных рецептов.
Таблица Состав связывает между собой таблицы Блюда и Продукты, оговаривая, какая масса (в граммах) того или иного продукта (столбец Вес) должна входить в состав одной порции блюда. Так, порция блюда с номером 12 (Суп молочный) должна состоять из 350 г продукта с номером 7 (Молоко), 35 г продукта с номером 13 (Рис), 5 г продукта с номером 3 (Масло) и 5 г продукта с номером 16 (Сахар).
Шеф-повар ежедневно получает от завхоза сведения о количестве в килограммах имеющихся продуктов и их текущей стоимости (столбцы К_во и Стоимость таблицы Наличие). Используя эти сведения он определяет по таблице Состав перечень тех блюд, которые можно приготовить из этих продуктов, а также калорийность и стоимость таких блюд. При этом стоимость блюда складывается из стоимости и массы продуктов, необходимых для приготовления одной его порции, а также из трудозатрат на ее приготовление (см. таблицу Блюда). Калорийность же определяется по массе и калорийности каждого из продуктов блюда. (Для получения значения калорийности продукта исходят из того, что при окислении 1 г углеводов или белков в организме освобождается в среднем 4.1 ккал, а при окислении 1 г жиров - 9.3 ккал.)
Блюда | Рецепты | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
Поставщики | ||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Состав | Поставки | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
Продукты | Наличие | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
Вид_блюд | Трапезы | Меню | Выбор | Выбрано | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
|
|
Основные таблицы базы данных ПАНСИОН
|
Из за большого объема этот материал размещен на нескольких страницах:
1 2 3 4 5 6 7 8 |
