Опубликован: 28.12.2011 | Доступ: свободный | Студентов: 7487 / 958 | Оценка: 3.81 / 3.53 | Длительность: 19:30:00
ISBN: 978-5-9963-0488-2
Лекция 1:

Диалект SQL фирмы ORACLE

Лекция 1: 1234 || Лекция 2 >
Проектирование реляционной базы данных

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

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

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

Устранение избыточности

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

Простой пример устранения избыточности показан ниже.

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


В других случаях устранение избыточности может выглядеть отнюдь не столь очевидно. Представьте, что в БД требуется хранить почтовый адрес, включая индекс. Часто для этого используют отдельные атрибуты для индекса и прочих частей адреса, таких как город, улица и так далее. Однако подобное хранение, строго говоря, избыточно, так как почтовый индекс однозначно определяется "словесной частью" адреса. Двойное хранение сведений будет держать открытой лазейку для появления содержательно неверных данных в БД, но чтобы воспрепятствовать этому, потребуется заметно усложнить схему и утяжелить БД (что же, идти на хранение ненужного для иной цели соответствия "словесной части" адреса почтовому индексу?) и, как следствие, — усложнить и замедлить, казалось бы, простые обращения к БД. В силу таких издержек разработчик в жизни нередко предпочитает простоту организации данных рискам, связанным с избыточностью (или, если удается, снимает проблему рассогласования данных с помощью ограничений целостности либо триггерных процедур).

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

Нормализация реляционной базы данных

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

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

Пусть в отношении с данными о сотрудниках присутствуют и номер отдела сотрудника, и название отдела. Чтобы устранить зависимость между номером и названием отдела в отношении "сотрудники", достаточно оставить в нем только один из двух атрибутов (скорее всего, это будет "номер отдела"), создать отношение "отделы" с атрибутами "номер отдела" и "название", объявить в нем "номер отдела" ключом. "Номер отдела" в "сотрудниках" следует объявить внешним ключом, связав его с "номером отдела" из "отделов":


Избавляться от подобного рода функциональных зависимостей в отношениях можно, пока они не окажутся приведенными к "нормальной форме BCNF", Бойса-Кодда (Boyce-Codd); по праву первенства ее следовало бы назвать "нормальной формой Хита" (Heath). В таких отношениях, образно выражаясь, "каждое сведение относится к ключу, всему ключу (со всеми его атрибутами) и только к ключу" (в оригинальной фразе на английском вместо "сведения" дословно сказано "факт"), с тем добавлением, что ключей может быть несколько. Иными словами, в отношении, удовлетворяющем BCNF, произвол значений определяется только правилами ключа — возможно, внешнего ключа и типов атрибутов. Несколько менее строгий вариант BCNF именуется "3-й нормальной формой".

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

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

Нормализация отношений способствует:

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

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

  • механическое сведение к нормальной форме BCNF в случае составных ключей может оказаться неоправданным и вступить в противоречие с необходимостью сохранить в модели определенную долю избыточности;
  • сохранение избыточности данных, в том числе вследствие недонормализованности отношений, способно приводить к аномалиям обновлений;
  • нормализованные данные может оказаться сложнее изменять;
  • нормализация неоднозначна и часто допускает разные способы дробления таблиц;
  • нормализация не решает всех проблем моделирования, заставляя разработчика БД прибегать к дополнительным правилам ограничения целостности данных.

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

Ортогонализация отношений

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

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


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

Лекция 1: 1234 || Лекция 2 >
Ярослав Прозоров
Ярослав Прозоров

В лекции № 7 "Введение в Oracle SQL" в подразделе "Несамостоятельность группировки с обобщениями ROLLUP, CUBE и GROUPING SETS"  представленная таблица сравнения содержит ошибки - окончания запросов пропущены. Видимо, ошибки вызваны некорректным переносом материала лекции.

Володимир Миколайчук
Володимир Миколайчук
Помогите разобраться поетапно с логикой запроса
-------TOOLS
NAME PRICE TYPE
drill 155 A
sawzall 192 N
mitre saw 292 M
router 86 I
RAD 145 M
jigsaw 128 I
screwdriver 77 P
------TOOL_TYPES
TYPE USAGE
A Always
I Often
M Sometimes
N Rarely
P Never

Запрос SQL:
SELECT t.type, SUM(t.price)
FROM tools t
GROUP BY t.type
HAVING SUM(t.price) >= (SELECT AVG(price)
FROM tools
WHERE type IN (SELECT type
FROM tool_types
WHERE usage = 'Often'));

И сколько строк он все таки вернет