Введение в Oracle SQL

Пржиялковский В. В.






Введение в Oracle SQL













УИНТУИТ
  / НАЦИОНАЛЬНЫЙ ОТКРЫТЫЙ УНИВЕРСИТЕТ

С.ИНТУ ИТ

    У НАЦИОНАЛЬНЫЙ ОТКРЫТЫЙ УНИВЕРСИТЕТ


Введение в Oracle SQL 2-е издание, исправленное Пржиялковский В.В.


Национальный Открытый Университет “ИНТУИТ”
2016


2

Введение в Oracle SQL/ В.В. Пржиялковский - М.: Национальный Открытый Университет “ИНТУИТ”, 2016
Курс рассказывает о диалекте SQL, предлагаемом фирмой Oracle для работы с базами данных своего типа. Рассматриваются конструкции языка, касающиеся работы с моделью предметной области и имеющие технологический характер. Изложение сопровождается практическими примерами.
Широко распространенная СУБД Oracle представляет собой классическую реализацию систем на основе SQL. Курс рассказывает об основах диалекта SQL, реализованного этой СУБД. Улучшению понимания способствует ретроспективный взгляд на возникновение тех или иных конструкций языка, а также соотношение их с реляционной моделью, которой SQL обязан своим появлением, и с элементами стандарта ANSI/ISO, связанного с Oracle SQL взаимно-обратным влиянием.
Значительная часть утверждений в курсе проиллюстрирована примерами. (Все изложение касается варианта языка в последней версии 11.2 СУБД Oracle.)
(c) ООО “ИНТУИТ.РУ”, 2011-2016
(c) Пржиялковский В.В., 2011-2016

3

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




Рассматриваются понятия, которые определяют диалект SQL, предлагаемый фирмой Oracle, в его нынешнем состоянии и формируют контекст употребления этого диалекта. В основном это реляционная модель данных и реляционное проектирование, а также стандартный SQL.

Происхождение и объем диалекта SQL фирмы Oracle

Для успешного программирования в Oracle на SQL недостаточно знать сугубо формальное описание языка. Необходимо владеть более широким набором имеющих отношение к делу знаний. С этой целью ниже рассматривается цепочка понятий, подводящая к “диалекту SQL, предлагаемому фирмой Oracle”. Она устроена следующим образом: база данных -+ модель данных, СУБД; реляционная модель; язык запросов к данным и изменения данных -^ SQL -+ диалект SQL в Oracle.

База данных и модель данных

База данных

В буквальном переводе на русский язык “база данных” (БД) означает специально подготовленную на компьютере “основу” (base) для работы потребителей с “данными” (data). Непосредственным потребителем является, конечно, программа.

Общепринятого понятия базы данных, несмотря на широкое распространение самого явления, не существует. Вот некоторые примеры разнохарактерных определений.

  1. “Обычно большое собрание данных, организованных для особо быстрого и удобного способа поиска и извлечения (например, из ЭВМ)” (Merriam-Webster’s Collegiate Dictionary, www.merriam-webster.com/dictionary/database, датировано 1962 годом).
  2. “Собрание структуризованных данных в ЭВМ, поддерживаемое СУБД, которая обеспечивает различным приложениям различный вид данных” (F. Pascal, Understanding Relational Databases with examples in SQL-92, New York, NY: John Whiley & Sons, 1993).
  3. “База данных — набор аксиом. Результат на запрос к базе есть теорема. Процесс вывода теоремы из аксиом есть доказательство. Доказательство осуществляется манипулированием символов по условленным математическим правилам. Доказательство [то есть результат запроса к базе] настолько же здраво и логично (consistent), насколько здравы и логичны правила” (H. Darwen. The Duplicity of Duplicate Rows. Relational Database Writings 1989-1991, Reading, MA: Addison-Wesley, 1992).

Возможное обобщение этих и других определений:


4

“Совокупность всех данных некоторой прикладной области” [для использования в программных системах] (Филиппов В. И. Общее описание системы КОМПАС. // Автоматизация программирования. Москва: ВЦ АН СССР, 1989).

Существенные элементы в определениях БД:

  • Модель. Всякая БД, независимо от того, сознает это ее разработчик или нет, воплощает собой некоторую “модель данных” “предметной области”: понятийную (говоря по-иному, “концептуальную”, “бизнес-модель”), логическую (данных) и физическую (организации данных))¹) .
  • Собственно БД. Организованные для долговременного хранения, обычно на внешнем носителе, данные общего пользования.
  • СУБД (система управления базой данных). Компьютерная программа для управления данными и доступа к ним. Во всех промышленных системах прикладные программы для работы с данными не имеют возможности обратиться к данным БД иначе как через СУБД.

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

  • ни одна модель по определению не в состоянии учесть все обстоятельства предметной области и обязательно чего-то не будет учитывать (на деле, наоборот, “учитывать всего лишь кое-что”);
  • существует опасность “неадекватного” моделирования, когда модель формально построена корректно (например, СУБД не видит ошибок в запросах и выдает ответы), но некорректно отражает предметную область, причем формального аппарата для обнаружения подобных расхождений не существует;
  • чем дольше используется модель, тем чаще возникает необходимость ее подправить и привести в (лучшее) соответствие предметной области.

Последнее обстоятельство (необходимость внести в модель данных изменения) может быть вызвано как субъективными причинами, из-за небрежного начального проектирования модели, так и объективными — из-за изменения самой предметной области. Например, в базах личностных данных до 2000-х годов сведения о браке достаточно моделировались парой “муж—жена”, тогда как с наступлением XXI века это все чаще становится неприемлемым, и старые базы требуется подправлять.

Перечисленные проблемы моделирования вообще автоматически сопровождают и моделирование с помощью БД, а проблема внесения изменений в модель, используемую в БД, часто к тому же усугубляется технологическими сложностями, вынуждающими в жизни откладывать перестройку БД “до последнего момента”.

Что касается СУБД, то в БД именно эта программа обеспечивает “особо быстрый и удобный способ поиска и извлечения”, притом берет на себя решение этих задач монопольно, запрещая прикладным программам обращаться к данным в обход себя.


5

Относительно терминологии нередко бытуют вольности словоупотребления. Например, в материалах фирмы Oracle часто говорят о “системе базы данных” (database system), вероятно, подразумевая под этим сосуществующую пару “СУБД— БД”. В склонном к упрощениям американском английском слово “система” в полном термине порою выпадает, в результате чего пару СУБД — БД часто именуют просто словом database. По той же причине вместо “тип СУБД” часто говорят просто “СУБД”, и тогда возникает двусмыслица: СУБД как конкретная работающая программа и СУБД как конкретный набор программного обеспечения для обслуживания доступа приложений к данным. Это не исключение: подобная многосмыслица имеется и для понятия “модель данных”, о чем будет сказано ниже, а также многих других понятий в базах данных в частности и в информационных технологиях вообще. Иногда в этом ничего страшного нет и можно сориентироваться по контексту употребления, но иногда такие вольности приводят к туману в понимании и в выражении мыслей.

СУБД

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

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

В число функций, которые обеспечивают современные СУБД, входят следующие:

  • Поддержка логической модели данных (определение данных, оперирование данными).
  • Восстановление данных (транзакции, журнализация, контрольные точки).
  • Управление одновременным доступом к данным в БД.
  • Безопасность данных (права доступа и прочее).
  • Самостоятельная оптимизация выполнения операций.
  • Прочие, в том числе вытекающие из перечисленных (администрирование, статистика, распределение данных и т. д.).

Реляционный подход к моделированию данных

Наиболее существенное влияние на современные промышленные виды СУБД, включая Oracle, оказал реляционный подход к моделированию данных. Он основывается на использовании “реляционной теории”, частью которой является “реляционная модель”. Последняя берет свое начало со статьи своего основателя, Э. Кодда (E. F. Codd), “Derivability, Redundancy, and Consistency of Relations Stored in Large Data Banks“,


6

опубликованной в IBM Research Report RJ599 в 1969 году. Впоследствии реляционная модель пережила всплеск интенсивного изучения и уточнения широким кругом специалистов, а в настоящее время она развивается главным образом усилиями К. Дейта (C. J. Date), сподвижника и коллеги Кодда во времена создания модели.

Реляционная модель данных

Все, что я съел, и все, что я выпил, осталось со мною;

Все остальное, что есть, право, не стоит щелчка.

Сарданапала, Надпись на могильной плите

Выражение “модель данных” часто понимается в двух разных смыслах: как формальное описание некоторой конкретной предметной области и как инструмент для составления подобных описаний. Нужный смысл обычно приходится определять по контексту. Здесь выражение “реляционная модель данных” понимается как инструмент составления в БД конкретных описаний конкретных предметных областей.

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

  • “Тип данных” (type) — множество допустимых величин (“область определения”) и операций. Для всех типов существуют операции сравнения и присвоения. Величинам не запрещено иметь структуру, например, объекта.
  • “Отношение” (relation) — множество атрибутов: уникальных имен с уточнением типа данных; плюс множество “наборов величин” (“рядов”), соответствующих атрибутам. Величины в наборах могут быть представлены только единичными значениями соответствующих атрибутам типов, то есть быть скалярами (“1-я нормальная форма”).
  • “Переменная отношения” (relation variable) — переменная типа отношения конкретного вида, необходимое понятие для определения в базе данных действий по “обновлению отношений”, “внесения изменений в данные”. В нарушение точности и в силу поверхностно-ознакомительного характера настоящего материала далее вместо названия “переменная отношения” будет употребляться просто “отношение” (подобно вольному употреблению слова “целое” вместо “переменная целого типа”).
  • “Ключ” (key) — группа атрибутов, значения которых во всех наборах в отношении различны, но ни одна подгруппа этих атрибутов таким свойством уже не обладает (свойство “минимальности” ключа). В частности, группа может состоять из единственного атрибута. Ключ в отношении обязан иметься всегда, а если их несколько, один из них обязан быть назначен “первичным” (primary).
  • “Внешний ключ” (foreign key) — группа атрибутов, значения которых в каждом наборе величин отношения обязаны совпадать со значениями ключа возможно другого отношения. Внешние ключи в отношении не обязательны и


7

    провозглашаются по потребностям моделирования.
  “ “Операции” (operation) — множество общих действий над отношениями, дающих в результате опять-таки отношения (“замкнутость операций”). Используются для получения новых отношений в нуждах последующего моделирования или при извлечении из базы нужных данных. Перечень операций можно определять по-разному; в первых предложениях модели приводилось восемь операций (проекции, соединения, отбора и пр.), уже не минимальный набор, как компромисс между отсутствием избыточности и удобством употребления.
  • “Реляционная база данных” (relational database) — набор отношений.

 Тип данных” иногда называют“доменом” (domain), но иногда под“доменом” разумеют только“область определения” величин.“Набор величин” (tuple) по-русски иначе называют “кортежем” или “n-кой”.

Для удобства отношения часто изображают в виде таблиц, хотя такое представление неправомерно (в отношении не определен ни порядок атрибутов, ни порядок наборов величин, в отличие от таблицы). В SQL, на основе которого построена в том числе СУБД Oracle, понятие“отношения” (а точнее, понятие“переменной отношения”) как инструмента моделирования заменено как раз на“таблицу”. Другим представлением данных отношения может быть гиперкуб, и к нему тоже иногда удобно прибегать в рассуждениях об имеющейся БД.

Если отказаться от определительного слова-кальки“реляционный”, то термин реляционная БД” можно перевести как“БД отношений” (точнее,“БД построенная посредством отношений”; отношений как инструмента, а не объекта моделирования: иначе исходный термин был бы relation database). Точно так же термин“реляционная модель” можно перевести как“модель отношений”, то есть“система понятий для построения модели предметной области в виде набора отношений”. По ряду причин, в том числе исторического и языкового характеров, этого не было в свое время сделано.

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

Приведенный взгляд на реляционную БД (набор отношений и операции) характерен для реляционной алгебры. Это не единственная точка зрения. Каждый набор величин в переменной отношения можно понимать как истинное высказывание (“предикат”): имеется такой-то сотрудник с такими-то свойствами; такой-то отдел и так далее. Тем самым реляционная база данных в каждый момент времени представляет собой набор истинных высказываний о предметной области, сформулированный через отношения. По сути, набор высказываний в переменных отношений и образует модель предметной области, представленную базой данных. Такой взгляд на реляционную БД характерен для реляционного исчисления. Оба взгляда на реляционную модель хорошо изучены и доказана их выразительная равносильность.


8

Проектирование реляционной базы данных


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

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

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

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

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

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

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

С ст рудники                 
 Зарплата Комиссионные Доход
 1600     300          1900 
 1250     500          1750 

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


9

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

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

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

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

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

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


10