База данных. ЭУМК Базы данных. Пояснительная записка Теоретический раздел Практический раздел Раздел контроля знаний Вспомогательный раздел Пинск
 Скачать 2.33 Mb.
|
|
Подчиненный Руководитель Орлова Емельянов Суханова Емельянов Иванов Суханова ID ФИО ID_руководителя Зарплата ID_Отдела 101 Емельянов 10000 1026 103 Орлова 101 8000 205 106 Суханова 101 8000 205 109 Иванов 106 5000 63 SELECT ID_Отдела, AVG(зарплата) FROM EMPLOYEE GROUP BY ID_Отдела; У операции реляционного деления два операнда - бинарное и унарное отношения. Результирующее отношение состоит из одноатрибутных кортежей, включающих значения первого атрибута кортежей первого операнда таких, что множество значений второго атрибута (при фиксированном значении первого атрибута) совпадает со множеством значений второго операнда. Замечание. Типичные запросы, реализуемые с помощью операции деления, обычно в своей формулировке имеют слово "все" - "какие поставщики поставляют все детали?". Пример. В примере с поставщиками, деталями и поставками ответим на вопрос, "какие поставщики поставляют все детали?". Номер поставщи ка PNUM Номер детал и DNUM Поставляемо е количество VOLUME 1 1 100 1 2 200 1 3 300 2 1 150 2 2 250 3 1 1000 Таблица 21 отношение PD (Поставки) В качестве делимого возьмем проекцию, содержащую номера поставщиков и номера поставляемых ими деталей: Номер поставщика PNUM Номер детали DNUM 1 1 1 2 1 3 2 1 ID_Отдела Зарплата 1026 10000 205 8000 63 5000 2 2 3 1 Таблица 22 Проекция X=PD[PNUM,DNUM] Проекция в SQL: Select pnum,dnum from pd В качестве делителя возьмем проекцию, содержащую список номеров всех деталей (не обязательно поставляемых кем-либо): Номер детали DNUM 1 2 3 Таблица 23 Проекция Y=D[DNUM] Проекция в SQL: Select dnum from pd group by dnum Деление дает список номеров поставщиков, поставляющих все детали: Номер поставщика PNUM 1 Таблица 24 Отношение X DEVIDEBY Y Оказалось, что только поставщик с номером 1 поставляет все детали. Операция переименования производит отношение, тело которого совпадает с телом операнда, но имена атрибутов изменены. Например, представим себе, что у отношений при их произведении имеются атрибуты с одинаковыми именами. Каким был бы заголовок результирующего отношения? Поскольку это множество, в нем не должны содержаться одинаковые элементы. Но и потерять атрибут в результате недопустимо. А это значит, что в этом случае вообще невозможно корректно выполнить операцию прямого произведения. Аналогичные проблемы могут возникать и в случаях других операций. Для их разрешения в состав операций реляционной алгебры вводится операция переименования. Ее следует применять в любом случае, когда возникает конфликт именования атрибутов в отношениях при выполнении реляционной операции. Тогда к одному из отношений сначала применяется операция переименования, а затем основная операция выполняется уже безо всяких проблем. Операция присваивания позволяет сохранить результат вычисления реляционного выражения в существующем отношении БД. Особенности теоретико-множественных операций реляционной алгебры Хотя в основе теоретико-множественной части реляционной алгебры и лежит классическая теория множеств, но соответствующие операции реляционной алгебры обладают некоторыми особенностями. Некоторые реляционные операторы (например, объединение) требуют, чтобы отношения имели одинаковые заголовки. Действительно, отношения состоят из заголовка и тела. Операция объединения двух отношений есть просто объединение двух множеств кортежей, взятых из тел соответствующих отношений. Но будет ли результат отношением? Во-первых, если исходные отношения имеют разное количество атрибутов, то, очевидно, что множество, являющееся объединением таких разнотипных кортежей нельзя представить в виде отношения. Во-вторых, пусть даже отношения имеют одинаковое количество атрибутов, но атрибуты имеют различные наименования. Как тогда определить заголовок отношения, полученного в результате объединения множеств кортежей? В-третьих, пусть отношения имеют одинаковое количество атрибутов, атрибуты имеют одинаковые наименования, но определенны на различных доменах. Тогда снова объединение кортежей не будет образовывать отношение. Будем называть отношения совместимыми по типу, если они имеют идентичные заголовки, а именно, Отношения имеют одно и то же множество имен атрибутов, т.е. для любого атрибута в одном отношении найдется атрибут с таким же наименованием в другом отношении, Атрибуты с одинаковыми именами определены на одних и тех же доменах. Некоторые отношения не являются совместимыми по типу, но становятся таковыми после некоторого переименования атрибутов с помощью вспомогательного оператора переименования атрибутов. Не все операторы реляционной алгебры являются независимыми - некоторые из них выражаются через другие реляционные операторы. Операторы соединения, пересечения и деления можно выразить через другие реляционные операторы, т.е. эти операторы не являются примитивными. Оставшиеся реляционные операторы (объединение, вычитание, декартово произведение, выборка, проекция) являются примитивными операторами - их нельзя выразить друг через друга. Реляционное исчисление Предположим, что мы работаем с базой данных, обладающей схемой СОТРУДНИКИ ( СОТР_НОМ, СОТР_ИМЯ, СОТР_ЗАРП, ОТД_НОМ) и ОТДЕЛЫ ( ОТД_НОМ, ОТД_КОЛ, ОТД_НАЧ), и хотим узнать имена и номера сотрудников, являющихся начальниками отделов с количеством сотрудников больше 50. Если бы для формулировки такого запроса использовалась реляционная алгебра, то мы получили бы алгебраическое выражение, которое читалось бы, например, следующим образом: выполнить соединение отношений СОТРУДНИКИ и ОТДЕЛЫ по условию СОТР_НОМ = ОТД_НАЧ; ограничить полученное отношение по условию ОТД_КОЛ > 50; выдать результат предыдущей операции состоящий из значений атрибутов СОТР_ИМЯ, СОТР_НОМ. Мы четко сформулировали последовательность шагов выполнения запроса, каждый из которых соответствует одной реляционной операции. Если же сформулировать тот же запрос с использованием реляционного исчисления, то мы получили бы формулу, которую можно было бы прочитать, например, следующим образом: Выдать СОТР_ИМЯ и СОТР_НОМ для сотрудников таких, что существует отдел с таким же значением ОТД_НАЧ и значением ОТД_КОЛ большим 50. Во второй формулировке мы указали лишь характеристики результирующего отношения, но ничего не сказали о способе его формирования. В этом случае система должна сама решить, какие операции и в каком порядке нужно выполнить над отношениями СОТРУДНИКИ и ОТДЕЛЫ. Обычно говорят, что алгебраическая формулировка является процедурной, т.е. задающей правила выполнения запроса. А логическая - описательной (или декларативной), поскольку она всего лишь описывает свойства желаемого результата. Как мы указывали в начале лекции, на самом деле эти два механизма эквивалентны и существуют не очень сложные правила преобразования одного формализма в другой. 2. Проектирование реляционных баз данных 2.1. Логическое проектирование модели БД 2.2. Нормализация данных 2.3. Физическая организация БД 2.4. Системы управления базами данных (СУБД) 2.1. Логическое проектирование модели БД Проектирование базы данных является одним из важнейших этапов жизненного цикла БД, который включает: проектирование БД; проектирование приложений; реализацию БД; разработку специальных средств администрирования БД; эксплуатацию БД. Процесс проектирования БД представляет собой последовательность переходов от неформального словесного описания информационной структуры предметной области к формализованному описанию объектов предметной области в терминах некоторой модели. Можно выделить следующие этапы проектирования базы данных: системный анализ предметной области; инфологическое проектирование; выбор СУБД; даталогическое проектирование (логическое проектирование); Физическое проектирование. В рамках системного анализа предметной области необходимо провести подробное словесное описание предметной области и реальных связей, которые существуют между описываемыми объектами. В общем случае существуют два подхода к выбору состава и структуры предметной области: Функциональный подход. Применяется тогда, когда заранее известны задачи, для решения которых создается база данных. В этом случае можно четко выделить минимально необходимый набор объектов предметной области, которые должны быть описаны. Предметный подход. Информационные потребности будущих пользователей БД жестко не фиксированы. Невозможно точно выделить минимальный набор объектов предметной области, которые необходимо описать. В этом случае в описание предметной области включаются такие объекты и взаимосвязи, которые наиболее характерны и наиболее существенны для нее. БД, конструируемая при этом, называется предметной, то есть она может быть использована при решении множества разнообразных, заранее не определенных задач. Чаще всего на практике рекомендуется использовать некоторый компромиссный вариант, который, с одной стороны, ориентирован на конкретные задачи или функциональные потребности пользователей, а с другой стороны, учитывает возможность наращивания новых приложений. Системный анализ должен заканчиваться: подробным описанием объектов предметной области, информацию о которых требуется хранить в БД; формулировкой конкретных задач, которые будут решаться с использованием данной БД, с кратким описанием алгоритмов их решения; описанием выходных документов, которые должны генерироваться в системе; описанием входных документов, которые служат основанием для заполнения данными БД. Инфологическое проектирование создает инфологическую модель предметной области, не привязанную к конкретной СУБД. Инфологическое проектирование, прежде всего, связано с попыткой представления семантики предметной области в модели БД. Реляционная модель данных в силу своей простоты и лаконичности не позволяет отобразить семантику, то есть смысл предметной области. Логическое проектирование приводит к разработке схемы БД, то есть совокупности схем отношений, которые адекватно моделируют абстрактные объекты предметной области и семантические связи между этими объектами. Логическое проектирование заключается в определении числа и структуры таблиц, формировании запросов к базе данных, определении типов отчетных документов, разработке алгоритмов обработки информации, создании форм для ввода и редактирования данных в базе и решении ряда других задач. Решение задач логического проектирования базы данных определяется спецификой предметной области. Наиболее важной является проблема структуризации данных. При проектировании структур данных для автоматизированных систем можно выделить три подхода: Сбор информации об объектах решаемой задачи в рамках одной таблицы (одного отношения) и последующая декомпозиция ее на несколько взаимосвязанных таблиц на основе процедуры нормализации отношений. Формулирование знаний о системе (определение типов исходных данных и их взаимосвязей) и требований к обработке данных, получение с помощью CASE-системы (системы автоматизированного проектирования и разработки баз данных) готовой схемы базы данных или даже готовой прикладной информационной системы. Структурирование информации для использования в информационной системе в процессе проведения системного анализа на основе совокупности правил и рекомендаций. Первый из названных подходов является классическим и исторически первым. Решение проблем проектирования на физическом уровнево многом зависит от используемой СУБД, зачастую автоматизировано и скрыто от пользователя. В ряде случаев пользователю предоставляется возможность настройки отдельных параметров системы. Проблемы проектирования реляционных баз данных Рассмотрим основные проблемы, имеющие место при определении структур данных в отношениях реляционной модели. Избыточное дублирование данных. Следует различать простое (неизбыточное) и избыточное дублирование данных. Наличие первого из них допускается в базах данных, а избыточное дублирование данных может приводить к проблемам при обработке данных. Пример неизбыточного дублирования данных приведен на рис 1. Для сотрудников, находящихся в одном помещении, номера телефонов совпадают. Однако для каждого служащего номер телефона уникален. Поэтому ни один из номеров не является избыточным. Действительно, при удалении одного из номеров телефонов будет утеряна информация о том, по какому номеру можно дозвониться до одного из служащих Сотрудники_Телефоны Фамилия Телефон Иванов 216-30-67 Петров 216-30-45 Сидоров 216-30-45 Егоров 216-30-45 Сергеев 216-30-23 Рис. 1. Неизбыточное дублирование Пример избыточного дублирования представлен на рис. 2, где приведено отношение, в которое дополнено атрибутом Номер_комнаты. Естественно предположить, что все служащие в одной комнате имеют один и тот же телефон. В рассматриваемом отношении имеется избыточное дублирование данных. Номера телефонов Петрова и Сидорова , например, можно узнать из кортежа со сведениями о Егорове. Сотрудники_Телефоны_Комнаты Фамилия Телефон Комнаты Иванов 216-30-67 109 Петров 216-30-45 112 Сидоров 216-30-45 112 Егоров 216-30-45 112 Сергеев 216-30-23 120 Рис. 2. Избыточное дублирование На рис. 3. приведен пример неудачного отношения Сотрудники_Телефоны_Комнаты приведен пример отношения, в котором вместо телефонов Сидорова и Егорова поставлены прочерки (неопределенные значения). Сотрудники_Телефоны_Комнаты Фамилия Телефон Комнаты Иванов 216-30-67 109 Петров 216-30-45 112 Сидоров - 112 Егоров - 112 Сергеев 216-30-23 120 Рис. 3. Избыточное дублирование Неудачность подобного способа исключения избыточности заключается в следующем. Во-первых, при программировании придется потратить дополнительные усилия на создание механизма поиска информации для прочерков таблицы. Во-вторых, память все равно выделяется под атрибуты с прочерками, хотя дублирование данных и исключено. В-третьих, что особенно важно, при исключении из коллектива Петрова кортеж со сведениями о нем будет исключен из отношения, а значит уничтожена информация о телефоне 112-й комнаты, что недопустимо. Возможный способ выхода из данной ситуации приведен на рис. 4. Здесь показаны два отношения, полученные путем декомпозиции исходного отношения. Первое из них содержит информацию о сотрудниках и номерах комнат, где они работают, а второе – информацию о номерах телефонов в каждой из комнат. Теперь, если Петров уволится из учреждения и, как следствие этого, будет удалена всякая информация о нем из базы данных, это не приведет к утере информации о номере телефона в 112-й комнате. Сотрудники_Комнаты Телефоны_Комнаты Фамилия Комнаты Телефон Комнаты Иванов 109 216-30-67 109 Петров 112 216-30-45 112 Сидоров 112 216-30-23 120 Егоров 112 Сергеев 120 Рис. 4. Исключение избыточного дублирования Процедура декомпозиции исходного отношения на два новых является основной процедурой нормализации отношений. Аномалии. Избыточное дублирование данных создает проблемы при обработке кортежей отношения, названные Э. Коддом «аномалиями обновления отношения». Он показал, что для некоторых отношений проблемы возникают при попытке удаления, добавления или редактирования их кортежей. Аномалиями будем называть такую ситуацию в таблицах БД, которая приводит к противоречиям в БД либо существенно усложняет обработку данных. Выделяют три основные вида аномалий: аномалии модификации (или редактирования), аномалии удаления и аномалии добавления. Аномалии модификации проявляются в том, что изменение значения одного данного может повлечь за собой просмотр всей таблицы и соответствующее изменение некоторых других записей таблицы. Например, изменение номера телефона в комнате 112 (рис.2), что представляет собой один единственный факт, потребует просмотра всей таблицы Сотрудники_Телефоны_Комнаты и изменения поля Телефон в записях, относящихся к Петрову, Сидорову и Егорову. Аномалии удаления состоят в том, что при удалении какого-либо данного из таблицы может пропасть и другая информация, которая не связана напрямую с удаляемым данным. В той же таблице удаление записи о сотруднике Иванове приводит к исчезновению информации о номере телефона, установленного в 109-й комнате. Аномалии добавления возникают в случаях, когда информацию в таблицу нельзя поместить до тех пор, пока она неполная, либо вставка новой записи требует дополнительного просмотра таблицы. Примером может служить операция добавления нового сотрудника в таблицу на рис. 2. Очевидно, будет противоестественным хранение сведений в этой таблице только о комнате и номере телефона в ней, пока никто из сотрудников не помещен в нее. Более того, если в данной таблице поле Фамилия является ключевым, то хранение в ней неполных записей с отсутствующей фамилией служащего просто недопустимо из-за неопределенности значения ключевого поля. Вторым примером возникновения аномалии добавления может быть ситуация включения в таблицу нового сотрудника. При добавлении таких записей для исключения противоречий желательно проверить номер телефона и соответствующий номер комнаты хотя бы с одним из сотрудников, сидящих с новым сотрудником в той же комнате. Если же окажется, что у некоторых сотрудников, сидящих в одной комнате, имеются разные телефоны, то вообще не ясно, что делать (то ли в комнате несколько телефонов, то ли какой-то из номеров ошибочный). 2.2. Нормализация данных Проектирование БД является одним из этапов жизненного цикла информационной системы. Основной задачей, решаемой в процессе проектирования БД, является задача нормализации отношений. Рассматриваемый ниже метод нормальных форм является классическим методом проектирования реляционных БД. Этот метод основан на фундаментальном в теории реляционных баз данных понятии зависимости между атрибутами отношений. Исследование зависимостей позволяет грамотно проектировать схемы баз данных, получать отношения, обладающие необходимыми свойствами. Состав атрибутов отношения должен удовлетворять двум основным требованиям: между атрибутами не должно быть нежелательных функциональных зависимостей; группировка атрибутов должна обеспечивать минимальное дублирование данных, их обработку и обновление без трудностей. Удовлетворение этих требований достигается нормализацией отношений базы данных. |