Базы, банки данных. Лекция 1 Термины и понятия Информация, данные Словом информация мы будем обозначать любые сведения о чем- либо, которые могут быть. - презентация

1 Базы, банки данных

2 Лекция 1 Термины и понятия

3 Информация, данные Словом информация мы будем обозначать любые сведения о чем- либо, которые могут быть восприняты, преобразованы, сохранены и найдены при помощи ЭВМ.

4 Данные- это форма, которую может принять информация Представление фактов, понятий или команд в формализованном виде, удобном для пересылки, интерпретации и обработки человеком или автоматически. Любое представление, которому приписано или может быть приписано какое-то значение.

5 Эти определения раскрывают два различных аспекта понятия. Первое утверждает, что данные – это одна из возможных форм представления информации. Второе указывает на то, что данные – это абстракция, символ, принимающий различные значения. Данные

6 Вовлеченная в деятельность часть реального мира, сведения о которой хранятся в БД, называется предметной областью (ПО) базы данных. Предметная область

7 Концептуальная модель ПО Описание ПО с точки зрения того вида деятельности, для которого предназначается БД.

8 Концептуальная модель ПО

9 Объекты – это вовлеченные в деятельность люди, предметы, места, происходящие события и т.п., информация о которых должна храниться в БД. Можно сказать, что объект есть то, что в словесном описании ПО обозначается именем существительным, возможно, с определениями. Это абстракция множества сходных в определенном смысле экземпляров.

10 Объекты Объекты ПО Учебный процесс. СТУДЕНТ, ПРЕПОДАВАТЕЛЬ, УЧЕБНАЯ ДИСЦИПЛИНА, АУДИТОРИЯ Объекты ПО Больница ПАЦИЕНТ, ПАЛАТА, ВРАЧ

11 Концептуальная модель ПО есть формальное описание объектов, их свойств и отношений.

12 Это динамическая информационная модель своей ПО, адекватно отражающая ее состояние в любой момент времени. База данных

13 Система базы данных База данных не может существовать вне некоторой совокупности средств поддержки. Она является информационным ядром человеко- машинной системы – системы базы данных (СБД), схематически показанной на рис 1.1.

14 Система базы данных

15 Система базы данных – это система специальным образом организованных данных, программных, технических, языковых, организационно- методических средств, предназначенная для поддержания динамической информационной модели ПО и коллективного многоцелевого использования данных.

16 Компоненты СБД

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

18 Компоненты СБД Технические средства СБД – это чаще всего универсальные ЭВМ с необходимым набором периферийных средств. Тенденция нашего времени – реализация СБД на сетях персональных ЭВМ. Организационно-методические средства - различные инструкции, методические и регламентирующие документы, предназначенные для пользователей различных категорий, такие как проектная документация, руководство пользователя и т.п.

19 Функции и состав АБД анализ предметной области; проектирование структуры БД; обеспечение целостности данных; первоначальная загрузка и ведение БД; защита данных; обеспечение восстановления БД; анализ обращений пользователей к БД; анализ эффективности функционирования СБД и развитие системы; работа с пользователями; подготовка и поддержание системных программных средств; организационно-методическая работа.

20 Принцип интегрированного хранения На предприятии должен поддерживаться только общий архив, в который поступают все сведения по мере их появления. Любой сотрудник получает нужную ему информацию только из этого архива Главная задача систем баз данных – поддержание интегрированных хранилищ информации. Избыточность в них есть, но ее ровно столько, сколько нужно, и, самое главное, она контролируема, т.е. обеспечена идентичность всех копий данных.

21 Независимость прикладных программ и данных Конечные пользователи СБД получают доступ к хранимым данным через посредство прикладных программ (ПП), работающих с общим полем данных во внешней памяти (см. рис. 1.3). Этого требует принцип интегрированного хранения. При этом ПП могут использовать только общие для всех форматы хранимых файлов, определенные проектировщиком БД. Игнорирование этого требования недопустимо.

22 Независимость прикладных программ и данных Рис 1.3 ПП вокруг данных

23 Независимость прикладных программ и данных Вариант 1 Вариант 2

24 Независимость прикладных программ и данных 1 Вариант: Каждая ПП использует только собственные файлы со всеми нужными данными. 2 Вариант: ПП создает собственные файлы, если нет подходящих файлов других ПП. В первом варианте неизбежна неконтролируемая избыточность. Данным нельзя доверять и ими невозможно управлять как общим ресурсом предприятия. Во втором варианте избыточности нет, но возникает ряд проблем проектирования и развития системы. Главная из них состоит в том, что если прикладной программист изменит структуры файлов своей ПП, скажем, с целью повышения эффективности программы, то придется переписать все ПП, использующие эти файлы. Прикладные программы оказываются взаимно зависимыми в этом смысле.

25 Независимость прикладных программ и данных Вариант ПП вокруг данных (рис. 1.3) лишен этих недостатков. Здесь нет ни неконтролируемой избыточности, ни взаимной зависимости ПП. Однако, если ПП получают доступ к данным непосредственно от ОС, то возникает проблема зависимости ПП от данных.

26 Независимость прикладных программ и данных Данные, хранящиеся во внешней памяти, будем называть хранимыми. Наименьшая (логическая) единица хранимых данных называется хранимым полем. Обычно во внешней памяти размещается много экземпляров хранимых полей, т.е. значений поля. Поле имеет имя и тип. Хранимые поля объединяются в хранимые записи. Хранимая запись – это набор связанных хранимых полей. Пример номер детали наименованиевес – тип хранимой записи P12шайба0,1 – экземпляр записи Хранимую запись следует понимать как тип, представленный во внешней памяти многими экземплярами. Набор всех экземпляров хранимых записей одного типа называется хранимым файлом.

27 Независимость прикладных программ и данных Вариант ПП вокруг данных предполагает, что все нужные типы хранимых записей и все их связи определены проектировщиком БД в виде схемы хранения данных. Прикладной программе известна часть схемы, содержащая необходимые ее пользователю данные. Тело программы содержит ссылки на доступные ей хранимые записи и поля. Если схема хранения изменится, то придется переписать все ПП, ориентированные на измененную часть схемы. !!! Требование независимости ПП и данных состоит в том, что все эти изменения не должны быть видны прикладным программам.

28 Лекция 2. ER модели. РМД.

29 Модель сущность – связь Концептуальная модель должна: быть точной и однозначной; адекватно отражать природу данных; не зависеть от локальных интерпретаций данных (внешних моделей), обусловленных различными аспектами их использования. Модель, удовлетворяющая этим требованиям, обеспечивает порождение любых локальных интерпретаций данных посредством формального преобразования концептуального представления. Создание концептуальной модели ПО – важнейший этап проектирования базы данных.

30 Модель сущность – связь Понятийные основы концептуального моделирования заложены американским исследователем П. Ченом, предложившим в 1976 году так называемую модель сущность–связь (Entity–Relationship, ER-модель).

31 Модель сущность – связь В концептуальной модели объекту ПО соответствует сущность, а свойству объекта – атрибут. Атрибут есть имя, принимающее значения на некотором множестве возможных значений. Сущность есть имя, поставленное в соответствие набору атрибутов.

32 Модель сущность – связь Имя сущности передает смысл соответствующего объекта ПО. Набор атрибутов сущности соответствует набору характеристик этого объекта, представляющих интерес с точки зрения бизнеса. То есть, атрибуты связаны общим смыслом, а их перечень определяется требованиями ПО. СТУДЕНТ = {фамилия, имя, номер зачетной книжки, номер группы} УЧЕБНАЯ ДИСЦИПЛИНА = {код, наименование, кафедра}. Набор значений атрибутов сущности называется экземпляром сущности. Сущность можно понимать как множество всех интерпретируемых наборов значений ее атрибутов

33 Возможный ключ сущности. Сущность как множество не может иметь двух идентичных экземпляров по определению. Подмножество атрибутов сущности, один и тот же набор значений которых не может встретиться в двух различных экземплярах, называется возможным ключом сущности. Например, подмножество атрибутов {номер зачетной книжки} является возможным ключом сущности СТУДЕНТ. Не может быть двух различных студентов, номера зачетных книжек которых одинаковы. Сущность УЧЕБНАЯ ДИСЦИПЛИНА имеет два возможных ключа – {код} и {наименование}. Возможные ключи являются механизмом идентификации экземпляров сущности. Указав значение ключа, мы, тем самым, указываем на конкретный экземпляр.

34 Связь Говорят, что объекты ПО состоят в связи, если хотя бы одному экземпляру одного из них можно поставить в соответствие (по определенному правилу) один или более экземпляров другого. Связь сущностей в концептуальной модели отображает множество связей между экземплярами соответствующих объектов ПО Говоря формально, связь есть отношение, определенное на декартовом произведении сущностей.

35 Связь {(П1,Т1), (П1,Т5), (П2,Т2), (П4,Т1)} а) связь объектов ПО;б) связь сущностей Связь есть отношение, определенное на декартовом произведении множеств допустимых значений возможных ключей связанных сущностей и собственных атрибутов.

36 Свойства связей и сущностей. Cвязь характеризуется арностью (степенью) – числом вступающих в нее сущностей. Замечание Существующая в реальном мире n-арная связь объектов ПО может быть представлена в концептуальной модели в виде n бинарных связей n + 1 сущностей. Для этого достаточно определить как сущность соответствующее связи отношение. Эта сущность будет представлять в модели не объект ПО, а множество фактов связи. Например, вместо тернарной связи ПОСТАВЩИК–ДЕТАЛЬ–ИЗДЕЛИЕ можно рассматривать три бинарных связи ПОСТАВЩИК– ПОСТАВКА, ДЕТАЛЬ–ПОСТАВКА и ИЗДЕЛИЕ–ПОСТАВКА, где ПОСТАВКА – сущность-ассоциация, в состав атрибутов которой входят возможные ключи сущностей ПОСТАВЩИК, ДЕТАЛЬ и ИЗДЕЛИЕ.

37 Свойства связей и сущностей. Число М экземпляров связи, которые могут быть образованы одним экземпляром сущности, называется мощностью (кардинальностью) связи со стороны этой сущности. а) связь 1:М; б) связь М:М

38 Свойства связей и сущностей. Связь типа 1:М называется направленной или функциональной, или специфическим соединением. В такой связи всегда можно указать родителя и потомка. Родителем называется сущность, мощность связи со стороны которой равна М (многосвязная). Мощность направленной связи со стороны потомка 0 или 1. Связь типа М:М называется ненаправленной или нефункциональной, или неспецифическим соединением. В такой связи нет родителя и потомка.

39 Свойства связей и сущностей. Рисунок Преобразование ненаправленной связи:

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

41 Иерархии сущностей. Родовой сущностью (супертипом, суперклассом) называется сущность, экземпляры которой классифицированы в один или более подтипов (подклассов). Категорией называется сущность, экземпляры которой представляют подтип (подкласс) родовой сущности.

42 Иерархии сущностей. В состав атрибутов родовой сущности входят только общие атрибуты всех категорий. Каждый экземпляр категории является одновременно экземпляром родовой сущности, т.е. категория наследует атрибуты родовой сущности. В связях родовой сущности могут участвовать экземпляры любой категории. Категория может иметь атрибуты и/или вступать в связи с другими сущностями, не свойственные всем экземплярам родовой сущности, а свойственные только экземплярам категории. Категории родовой сущности выделяются по некоторому признаку. Его значения одинаковы во всех экземплярах одной категории и различны в экземплярах разных категорий. Все множество категорий, выделенных по значениям этого признака, называется кластером категорий, а сам признак – дискриминатором кластера. Экземпляры родовой сущности могут классифицироваться по различным признакам, образуя несколько кластеров категорий. Наконец, любая категория может быть родовой сущностью, порождающей свои кластеры категорий. Таким образом, понятия родовой сущности и категории позволяют строить иерархии сущностей любой сложности.

43 Специфическое соединение реализуется путем передачи возможного ключа родителя в состав атрибутов потомка. Возможный ключ родителя в составе атрибутов потомка называется внешним ключом. Если внешний ключ полностью входит в состав какого-либо возможного ключа потомка, то передавшая его связь является идентифицирующей. В противном случае – неидентифицирующей. Потомок в идентифицирующей связи всегда идентификационно зависимая сущность. Потомок в неидентифицирующей связи может быть идентификационно независимой сущностью. Идентифицирующая связь всегда обязательная. Неидентифицирующая связь может быть обязательной или необязательной. Сущность может быть категорией родовой сущности и принадлежать определенному кластеру категорий. Сущность может быть родовой для нескольких кластеров категорий. Категория наследует все атрибуты родовой сущности, вступает во все ее связи и может иметь собственные атрибуты и вступать в собственные связи. Категория может быть родовой сущностью. Иерархии сущностей.

44 Свойства отношений Уникальность кортежей; неупорядоченность кортежей; неупорядоченность атрибутов; уникальность атрибутов; атомарность атрибутов; изменяемость отношений.

45 Свойства отношений Уникальность кортежей. Так как отношение есть множество кортежей, в нем не может быть дубликатов кортежей, то есть каждый кортеж встречается в отношении только один раз. Из этого свойства следует, что каждое отношение имеет некоторый набор атрибутов, значения которых уникально идентифицирует кортежи. Этот набор атрибутов называют возможным ключом отношения. Формальное определение этого понятия приведено в п Неупорядоченность кортежей. Это также следствие того, что отношение – множество кортежей. Множества неупорядоченны, если их упорядоченность специально не оговорена. Заметим, что в реальных структурах хранения данные так или иначе упорядочены. Однако учет этой упорядоченности в процедурах манипулирования данными сделал бы прикладные программы зависящими от физических структур хранения. Поэтому введение каких-либо гипотез об упорядоченности в концептуальную модель данных было бы ошибкой.

46 Свойства отношений Неупорядоченность атрибутов. Это свойство следует из определения схемы отношения как множества пар. Неупорядоченность атрибутов делает возможной модификацию схем отношений путем удаления атрибутов, вставки новых и переименования атрибутов и позволяет относительно просто определить ряд полезных операций над отношениями. Уникальность атрибутов. Одноименные атрибуты недопустимы, поскольку это может привести к появлению в схеме отношения дубликатов пар (домен, атрибут), что противоречит определению. Кроме того, только уникальность атрибутов может обеспечить возможность отнесения значения из кортежа к определенному домену.

47 Свойства отношений Атомарность значений атрибутов. Свойство следует из определения атрибута. Атрибут принимает значения на домене, а домен – подмножество простого типа. Таким образом, в реляционной теории не рассматриваются так называемые ненормализованные отношения. Изменяемость отношений. Реляционная модель данных рассматривает отношение как структурный тип. Тип определяется схемой отношения. Все кортежи – знаки типа – удовлетворяют одной и той же схеме. Тело отношения может изменяться во времени. Отдельные кортежи могут добавляться или удаляться. Могут изменяться значения атрибутов в существующих кортежах. Поэтому можно говорить об экземпляре (текущем значении) отношения с заданной схемой.

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

49 РМД

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

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

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

53 Состав РМД РМД СТРУКТУРНАЯЦЕЛОСТНОСТНАЯМАНИПУЛЯЦИОННАЯ

54 Состав РМД Структурная часть описывает, какие объекты рассматриваются реляционной моделью. Постулируется, что единственной структурой данных, используемой в реляционной модели, являются нормализованные n-арные отношения. Целостностная часть описывает ограничения специального вида, которые должны выполняться для любых отношений в любых реляционных базах данных. Это целостность сущностей и целостность внешних ключей. Манипуляционная часть описывает два эквивалентных способа манипулирования реляционными данными - реляционную алгебру и реляционное исчисление.

55 Базовые понятия реляционной модели данных РМД поддерживает только концептуальный уровень представления данных (данные представлены такими, какие они есть на самом деле). Все понятия структурной и целостностной частей РМД используются в современных графических ЯОД – входных языках компьютерных систем автоматизированного проектирования баз данных. Многие понятия РМД положены в основу SQL – входного языка современных коммерческих СУБД, обеспечивающего управление данными в БД на концептуальном уровне. Модель предложена американским математиком Е. Коддом в 1970 году.

56 Состав РМД Выделяется шесть базовых понятий РМД: Тип данных; Домен; Атрибут; Схема отношения; Кортеж; Отношение. Первые три относятся к элементам данных, остальные – к структурам, объединяющим элементы.

57

58 Структурная часть РМД Понятие тип данных в реляционной модели данных полностью адекватно понятию типа данных в языках программирования. Любые данные, используемые в программировании, имеют свои типы данных. Важно! Реляционная модель требует, чтобы типы используемых данных были простыми. Для уточнения этого утверждения рассмотрим, какие вообще типы данных обычно рассматриваются в программировании. Как правило, типы данных делятся на три группы:

59 Структурная часть РМД Тип данных ПростыеСтруктурированные Ссылочные

60 Структурная часть РМД Простые типы данных Простые, или атомарные, типы данных не обладают внутренней структурой. Данные такого типа называют скалярами. К простым типам данных относятся следующие типы: Логический. Строковый. Численный. Различные языки программирования могут расширять и уточнять этот список, добавляя такие типы как: Целый. Вещественный. Дата. Время. Денежный. Перечислимый. Интервальный.

61 Структурная часть РМД Структурированные типы данных Структурированные типы данных предназначены для задания сложных структур данных. Структурированные типы данных конструируются из составляющих элементов, называемых компонентами, которые, в свою очередь, могут обладать структурой. В качестве структурированных типов данных можно привести следующие типы данных: Массивы; Записи (Структуры).

62 Структурная часть РМД Понятие домена более специфично для баз данных, хотя и имеет некоторые аналогии с подтипами в некоторых языках программирования. В самом общем виде домен определяется заданием некоторого базового типа данных, к которому относятся элементы домена, и произвольного логического выражения, применяемого к элементу типа данных. Если вычисление этого логического выражения дает результат "истина", то элемент данных является элементом домена. Наиболее правильной интуитивной трактовкой понятия домена является понимание домена как допустимого потенциального множества значений данного типа. Следует отметить также семантическую нагрузку понятия домена: данные считаются сравнимыми только в том случае, когда они относятся к одному домену. В нашем примере значения доменов "Номера пропусков" и "Номера групп" относятся к типу целых чисел, но не являются сравнимыми. Заметим, что в большинстве реляционных СУБД понятие домена не используется.

63 Структурная часть РМД Домен характеризуется следующими свойствами: Домен имеет уникальное имя (в пределах базы данных). Домен определен на некотором простом типе данных или на другом домене. Домен может иметь некоторое логическое условие, позволяющее описать подмножество данных, допустимых для данного домена. Домен несет определенную смысловую нагрузку.

64 Структурная часть РМД Пример: Домен, имеющий смысл "возраст сотрудника" можно описать как следующее подмножество множества натуральных чисел:

65 Структурная часть РМД Атрибут есть имя, поставленное в соответствие домену и представляющее семантически значимое свойство объекта ПО. Если домену поставлено в соответствие имя, то говорят, что на домене определен атрибут. Атрибут принимает значения на домене. На одном и том же домене можно определить произвольное число атрибутов. Атрибуты, определенные на общем домене, наследуют его свойства.

66 Семантика доменов и атрибутов Домен и атрибут являются средствами интерпретации типа в терминах ПО. Пример: Атрибут скорость интерпретирует число 20 как скорость в км/час, а атрибут вес интерпретирует то же число как вес в кг.

67 Семантика доменов и атрибутов Определяя домены и атрибуты, мы тем самым задаем некоторые ограничения на операции над данными, обусловленные смыслом данных. Например, вес и скорость нельзя сравнивать или использовать совместно в арифметических операциях, несмотря на то, что и вес, и скорость – числа. Эти атрибуты должны быть определены на различных доменах числового типа

68 Семантика доменов и атрибутов

69 Атрибуты, определенные на общем домене сравнимы. Например, длина и ширина должны быть определены на общем домене, т.к. их сравнения осмысленны. А атрибуты табельный номер и номер телефона должны быть определены на разных доменах. Кроме того, эти домены не могут быть числовыми. Никто не складывает и не умножает табельные и телефонные номера.

70 Структурная часть РМД Пусть D1, D2, …Dn – домены (необязательно различные) и А1, А2, …, Аn – атрибуты, определенные на соответствующих доменах. Определение. Множество R = {(D1, A1), (D2, A2),..., (Dn, An)} пар называется схемой отношения. Интуитивно схему отношения можно понимать как заголовок таблицы или как определение типа простой записи. Пусть R – схема отношения, Ai – атрибут схемы, ai – значение атрибута.

71 Структурная часть РМД Определение. Множество пар SR = {Si : Si = (Ai, ai), (Di, Ai) R, ai Di, i = 1, …, n} называется кортежем, соответствующим схеме R. Интуитивно кортеж представляется как строка таблицы с заданным заголовком или набор именованных значений типов, или экземпляр записи.

72 Структурная часть РМД Например, пусть номера – домен трехсимвольных строк, составленных из цифр 0, 1,...9, имена – домен строк символов русского алфавита, пробелов и точек, а схема отношения СЛУЖАЩИЙ имеет вид: {( номера, номер служащего), (имена, имя служащего)}. Кортежи этого отношения могут быть такими: {(номер служащего, 345), (имя служащего, Иванов И.И.)}, {(номер служащего, 938), (имя служащего, Петров П.П. )}.

73 Структурная часть РМД Отношение интуитивно можно понимать как таблицу, заголовком которой является строка атрибутов, а значимыми строками – строки их значений, или как плоский файл, однако это неточные представления. Определение. Множество кортежей SR, соответствующих одной и той же схеме R, называется отношением. Отношение характеризуется: арностью (степенью) – числом пар в схеме; мощностью – числом кортежей, составляющих тело отношения.

74 Структурная часть РМД Замечание 1. Обычно отношение и его схема обозначаются одним и тем же символом R. Если нам понадобится явно различить схему и отношение, мы сохраним это обозначение за отношением, а схему будем обозначать символом R( ).

75 Структурная часть РМД Экземпляр (значение) отношения – это набор кортежей с заданной схемой, существующий в некоторый фиксированный момент времени.

76 Структурная часть РМД Пример. Рассмотрим отношение "Сотрудники" заданное на доменах "Номер_сотрудника", "Фамилия", "Зарплата", "Номер_отдела". Т.к. все домены различны, то имена атрибутов отношения удобно назвать так же, как и соответствующие домены. Заголовок отношения имеет вид: Сотрудники (Номер_сотрудника, Фамилия, Зарплата, Номер_отдела) Пусть в данный момент отношение содержит три кортежа: (1,Иванов, 1000, 1) (2, Петров, 2000, 2) (3, Сидоров, 3000, 1)

77 Структурная часть РМД Номер_сотрудникаФамилияЗарплатаНомер_отдела 1Иванов Петров Сидоров30001

78 Структурная часть РМД Реляционный терминСоответствующий "табличный" термин База данныхНабор таблиц Схема базы данныхНабор заголовков таблиц ОтношениеТаблица Заголовок отношенияЗаголовок таблицы Тело отношенияТело таблицы Атрибут отношенияНаименование столбца таблицы Кортеж отношенияСтрока таблицы Степень (-арность) отношения Количество столбцов таблицы Мощность отношенияКоличество строк таблицы Домены и типы данныхТипы данные в ячейках таблицы

79 Структурная часть РМД

80 Выводы Реляционная модель данных состоит из трех частей: Структурной части. Целостной части. Манипуляционной части.

81 Выводы В классической реляционной модели используются только простые (атомарные) типы данных. Простые типы данных не обладают внутренней структурой. Домены - это типы данных, имеющие некоторый смысл (семантику). Домены ограничивают сравнения - некорректно, хотя и возможно, сравнивать значения из различных доменов. Отношение состоит из двух частей - заголовка отношения и тела отношения. Заголовок отношения - это аналог заголовка таблицы. Заголовок отношения состоит из атрибутов. Количество атрибутов называется степенью отношения. Тело отношения - это аналог тела таблицы. Тело отношения состоит из кортежей. Кортеж отношения является аналогом строки таблицы. Количество кортежей отношения называется мощностью отношения.

82 Выводы Отношение обладает следующими свойствами: В отношении нет одинаковых кортежей. Кортежи не упорядочены (сверху вниз). Атрибуты не упорядочены (слева направо). Все значения атрибутов атомарны. Реляционной базой данных называется набор отношений. Схемой реляционной базы данных называется набор заголовков отношений, входящих в базу данных.

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

84 Контрольная Нормализовать структуру Указать арность результирующего отношения Указать мощность результирующего отношения

85 Манипуляционная часть РМД Третья часть реляционной модели, манипуляционная часть, утверждает, что доступ к реляционным данным осуществляется при помощи реляционной алгебры или эквивалентного ему реляционного исчисления. В манипуляционной составляющей определяются два базовых механизма манипулирования реляционными данными - основанная на теории множеств реляционная алгебра и базирующееся на математической логике (точнее, на исчислении предикатов первого порядка) реляционное исчисление.

86 Манипуляционная часть РМД

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

88 Манипуляционная часть РМД Алгебра и исчисление обладают большой выразительной мощностью: очень сложные запросы к базе данных могут быть выражены с помощью одного выражения реляционной алгебры или одной формулы реляционного исчисления. Именно по этой причине именно эти механизмы включены в реляционную модель данных. Конкретный язык манипулирования реляционными БД называется реляционно полным, если любой запрос, выражаемый с помощью одного выражения реляционной алгебры или одной формулы реляционного исчисления, может быть выражен с помощью одного оператора этого языка. Известно, что механизмы реляционной алгебры и реляционного исчисления эквивалентны, т.е. для любого допустимого выражения реляционной алгебры можно построить эквивалентную (т.е. производящую такой же результат) формулу реляционного исчисления и наоборот.

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

90 Для формулы реляционного исчисления однозначная интерпретация, вообще говоря, отсутствует. Формула только устанавливает условия, которым должны удовлетворять кортежи результирующего отношения. Поэтому языки реляционного исчисления являются более непроцедурными или декларативными.

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

92 Реляционная алгебра Существует много подходов к определению реляционной алгебры, которые различаются набором операций и способами их интерпретации, но в принципе, более или менее равносильны. Мы опишем немного расширенный начальный вариант алгебры, который был предложен Коддом. В этом варианте набор основных алгебраических операций состоит из восьми операций, которые делятся на два класса - теоретико-множественные операции и специальные реляционные операции

93 Реляционная алгебра

94

95 Как мы увидим, алгебра и исчисление обладают большой выразительной мощностью: очень сложные запросы к базе данных могут быть выражены с помощью одного выражения реляционной алгебры или одной формулы реляционного исчисления. Именно по этой причине такие механизмы включены в реляционную модель данных. Конкретный язык манипулирования реляционными БД называется реляционно-полным, если любой запрос, формулируемый с помощью одного выражения реляционной алгебры или одной формулы реляционного исчисления, может быть сформулирован с помощью одного оператора этого языка. Известно (и мы не будем это доказывать), что механизмы реляционной алгебры и реляционного исчисления эквивалентны, т. е. для любого допустимого выражения реляционной алгебры можно построить эквивалентную (т. е. производящую такой же результат) формулу реляционного исчисления и наоборот. Почему же в реляционной модели данных присутствуют оба эти механизма? Дело в том, что они различаются уровнем процедурности. Выражения реляционной алгебры строятся на основе алгебраических операций (высокого уровня), и подобно тому, как интерпретируются арифметические и логические выражения, выражение реляционной алгебры также имеет процедурную интерпретацию. Другими словами, запрос, представленный на языке реляционной алгебры, может быть вычислен на основе выполнения элементарных алгебраических операций с учетом их приоритетности и возможного наличия скобок. Для формулы реляционного исчисления однозначная вычислительная интерпретация, вообще говоря, отсутствует. Формула только ставит условия, которым должны удовлетворять кортежи результирующего отношения. Поэтому языки реляционного исчисления являются в большей степени непроцедурными, или декларативными. Поскольку механизмы реляционной алгебры и реляционного исчисления эквивалентны, в конкретной ситуации для проверки степени реляционности некоторого языка БД можно пользоваться любым из этих механизмов. Заметим, что крайне редко алгебра или исчисление принимается в качестве полной основы какого-либо языка БД. Обычно (например, в случае языка SQL) язык основывается на некоторой смеси алгебраических и логических конструкций. Тем не менее знание алгебраических и логических основ языков баз данных часто применяется на практике

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

97 Специальные операции Результатом ограничения отношения по некоторому условию является отношение, включающее кортежи отношения-операнда, удовлетворяющее этому условию. При выполнении проекции отношения на заданный набор его атрибутов производится отношение, кортежи которого производятся путем взятия соответствующих значений из кортежей отношения- операнда. При соединении двух отношений по некоторому условию образуется результирующее отношение, кортежи которого являются конкатенацией кортежей первого и второго отношений и удовлетворяют этому условию. У операции реляционного деления два операнда - бинарное и унарное отношения. Результирующее отношение состоит из одноатрибутных кортежей, включающих значения первого атрибута кортежей первого операнда таких, что множество значений второго атрибута (при фиксированном значении первого атрибута) совпадает со множеством значений второго операнда.

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

99 Операция объединения Смысл в реляционной алгебре в целом остается теоретико- множественным. Но если в теории множеств операция объединения осмысленна для любых двух множеств-операндов, то в случае реляционной алгебры результатом операции объединения должно являться отношение. Если допустить в реляционной алгебре возможность теоретико- множественного объединения произвольных двух отношений (с разными схемами), то, конечно, результатом операции будет множество, но множество разнотипных кортежей, т.е. не отношение. Если исходить из требования замкнутости реляционной алгебры относительно понятия отношения, то такая операция объединения является бессмысленной.

100 Операция объединения Все эти соображения приводят к появлению понятия совместимости отношений по объединению: два отношения совместимы по объединению в том и только в том случае, когда обладают одинаковыми заголовками. Более точно, это означает, что в заголовках обоих отношений содержится один и тот же набор имен атрибутов, и одноименные атрибуты определены на одном и том же домене Объединением двух совместимых по типу отношений A и B называется отношение с тем же заголовком, что и у отношений A и B, и телом, состоящим из кортежей, принадлежащих или A, или B, или обоим отношениям.

101 Операция объединения Синтаксис операции объединения: A UNION B Замечание. Объединение, как и любое отношение, не может содержать одинаковых кортежей. Поэтому, если некоторый кортеж входит и в отношение A, и отношение B, то в объединение он входит один раз.

102 Операция объединения Таб ФамилияЗарплата 1Иванов1000 2Петров2000 3Сидоров3000 Таб ФамилияЗарплата 1Иванов1000 2Васильев2500 4Сидоров3000

103 Таб ФамилияЗарплата 1Иванов1000 2Петров2000 3Сидоров3000 2Васильев2500 4Сидоров3000 Операция объединения

104 Замечание. Как видно из приведенного примера, потенциальные ключи, которые были в отношениях A и B не наследуются объединением этих отношений. Поэтому, в объединении отношений A и B атрибут "Табельный номер" может содержать дубликаты значений. Если бы это было не так, и ключи наследовались бы, то это противоречило бы понятию объединения как "объединение множеств". Конечно, объединение отношений A и B имеет, как и любое отношение, потенциальный ключ, например, состоящий из всех атрибутов.

105 Пересечение Пересечением двух совместимых по типу отношений A и B называется отношение с тем же заголовком, что и у отношений A и B, и телом, состоящим из кортежей, принадлежащих одновременно обоим отношениям. Синтаксис операции пересечения: A intersect B

106 Пересечение Таб ФамилияЗарплата 1Иванов1000 Замечание. Казалось бы, что в отличие от операции объединения, потенциальные ключи могли бы наследоваться пересечением отношений. Однако это не так. Вообще, никакие реляционные операторы не передают результатирующему отношению никаких данных о потенциальных ключах.

107 Вычитание Вычитанием двух совместимых по типу отношений A и B называется отношение с тем же заголовком, что и у отношений и, и телом, состоящим из кортежей, принадлежащих отношению A и не принадлежащих отношению B. Синтаксис операции вычитания: A minus B

108 Вычитание Таб ФамилияЗарплата 2Петров2000 3Сидоров3000

109 Декартово произведение Декартовым произведением двух отношений A и B называется отношение, заголовок которого является сцеплением заголовков отношений A и B : а тело состоит из кортежей, являющихся сцеплением кортежей отношений A и B: таких, что

110 Декартово произведение Синтаксис операции декартового произведения A TIMES B Замечание. Мощность произведения A TIMES B равна произведению мощностей отношений A и B, т.к. каждый кортеж отношения соединяется с каждым кортежем отношения. Замечание. Если в отношения A и B имеются атрибуты с одинаковыми наименованиями, то перед выполнением операции декартового произведения такие атрибуты необходимо переименовать. Замечание. Перемножать можно любые два отношения, совместимость по типу при этом не требуется.

111 Декартово произведение поставщикаНаим_пост 1Иванов 2Петров 3Сидоров деталиНаим_дет 1Болт 2Гайка 3Винт

112 Декартово произведение поставщикаНаим_пост деталиНаим_дет 1Иванов1Болт 1Иванов2Гайка 1Иванов3Винт 2Петров1Болт 2Петров2Гайка 2Петров3Винт 3Сидоров1Болт 3Сидоров2Гайка 3Сидоров3Винт

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

114 Специальные реляционные операции Операция ограничения требует наличия двух операндов: ограничиваемого отношения и простого условия ограничения. Выборкой (ограничением, селекцией) на отношении A с условием c называется отношение с тем же заголовком, что и у отношения A, и телом, состоящем из кортежей, значения атрибутов которых при подстановке в условие c дают значение ИСТИНА. C представляет собой логическое выражение, в которое могут входить атрибуты отношения и (или) скалярные выражения.

115 Ограничение В простейшем случае условие c имеет вид X Ө Y где -Ө один из операторов сравнения (, =, и т.д.), а X и Y- атрибуты отношения или скалярные значения. Такие выборки называются - Ө выборки (тэта-выборки) или Ө -ограничения, Ө - селекции. Синтаксис операции выборки: A where c или соответственно: A where X Ө Y

116 Ограничение Таб ФамилияЗарплата 1Иванов1000 2Петров2000 3Сидоров3000 Результат выборки A WHERE ЗАРПЛАТА

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

118 Операция взятия проекции Операция взятия проекции также требует наличия двух операндов - проецируемого отношения A и списка имен атрибутов, входящих в заголовок отношения A. Проекцией отношения А по атрибутам X, Y, Z где каждый из атрибутов принадлежит отношению A, называется отношение с заголовком (X,Y,Z) и телом, содержащим множество кортежей вида (x,y,z) таких, для которых в отношении A найдутся кортежи со значением атрибута X равным x, значением атрибута Y равным y, …, значением атрибута Z равным z Синтаксис операции проекции: A[X,Y,…,Z]

119 Операция взятия проекции постНаим_постГород 1ИвановУфа 2ПетровМосква 3СидоровМосква 4СидоровЧелябинск

120 Операция взятия проекции Проекция A[Город] будет иметь вид Город Уфа Москва Челябинск

121 Операция взятия проекции Результатом проекции отношения A по списку атрибутов (a 1, a 2,..., a n ) является отношение, с заголовком, определяемым множеством атрибутов (a 1, a 2,..., a n ), и с телом, состоящим из кортежей вида таких, что в отношении A имеется кортеж, атрибут a1 которого имеет значение v1, атрибут a2 имеет значение v2,..., атрибут an имеет значение vn. Тем самым, при выполнении операции проекции выделяется "вертикальная" вырезка отношения-операнда с естественным уничтожением потенциально возникающих кортежей-дубликатов.

122 Операция соединения отношений Обычно рассматривается несколько разновидностей операции соединения: Общая операция соединения; Θ -соединение (тэта-соединение); Экви-соединение; Естественное соединение.

123 Тэта-соединение Синтаксис: (A TIMES B) WHERE XΘY Замечание: эта операция частный случай операции общего соединения. Иногда, для операции Θ-соединения применяют следующий, более короткий синтаксис: А[XΘY]B

124 Пример Номер поставщикаНаименование поставщика X Статус поставщика 1Иванов4 2Петров1 3Сидоров2 Номер деталиНаименование детали Y (Статус детали) 1Болт3 2Гайка2 3Винт1

=Y]B : Номер постав щика Наименование поставщик а X (Статус поставщ ика) Номер детал и Наименован ие детали Y (Стату с детал и) 1Иванов" title="Пример (продолжение) Ответ на вопрос "какие поставщики имеют право поставлять какие детали?" дает Ө соединение A[X>=Y]B : Номер постав щика Наименование поставщик а X (Статус поставщ ика) Номер детал и Наименован ие детали Y (Стату с детал и) 1Иванов" class="link_thumb"> 125 Пример (продолжение) Ответ на вопрос "какие поставщики имеют право поставлять какие детали?" дает Ө соединение A[X>=Y]B : Номер постав щика Наименование поставщик а X (Статус поставщ ика) Номер детал и Наименован ие детали Y (Стату с детал и) 1Иванов41Болт3 1Иванов42Гайка2 1Иванов43Винт1 2Петров13Винт1 3Сидоров22Гайка2 3Сидоров23Винт1 =Y]B : Номер постав щика Наименование поставщик а X (Статус поставщ ика) Номер детал и Наименован ие детали Y (Стату с детал и) 1Иванов"> =Y]B : Номер постав щика Наименование поставщик а X (Статус поставщ ика) Номер детал и Наименован ие детали Y (Стату с детал и) 1Иванов41Болт3 1Иванов42Гайка2 1Иванов43Винт1 2Петров13Винт1 3Сидоров22Гайка2 3Сидоров23Винт1"> =Y]B : Номер постав щика Наименование поставщик а X (Статус поставщ ика) Номер детал и Наименован ие детали Y (Стату с детал и) 1Иванов" title="Пример (продолжение) Ответ на вопрос "какие поставщики имеют право поставлять какие детали?" дает Ө соединение A[X>=Y]B : Номер постав щика Наименование поставщик а X (Статус поставщ ика) Номер детал и Наименован ие детали Y (Стату с детал и) 1Иванов">

126 Экви-соединение Операция соединения называется операцией эквисоединения, если условие соединения имеет вид (a = b), где a и b - атрибуты разных операндов соединения. Этот случай важен потому, что (a) он часто встречается на практике, и (b) для него существуют эффективные алгоритмы реализации. Синтаксис экви-соединения: A[X=Y]B

127 Пример Номер поставщика PNUM Наименование поставщика PNAME 1Иванов 2Петров 3Сидоров Номер детали DNUM Наименование детали DNAME 1Болт 2Гайка 3Винт

128 Пример (продолжение) Номер поставщика PNUM Номер детали DNUM Поставляемо е количество VOLUME

129 Пример (продолжение) Ответ на вопрос, какие детали поставляются поставщиками, дает экви-соединение P[Pnum=Pnum]SPJ

130 Пример (продолжение) Номер поставщ ика PNUM1 Наименован ие поставщика PNAME Номер поставщ ика PNUM2 Номер детали DNUM Поставляем ое количество VOLUME 1Иванов Иванов Иванов Петров Петров Сидоров311000

131 Операция деления отношений Эта операция наименее очевидна из всех операций реляционной алгебры и поэтому нуждается в более подробном объяснении. Пусть заданы два отношения - A с заголовком {a1, a2,..., an, b1, b2,..., bm} и B с заголовком {b1, b2,..., bm}. Будем считать, что атрибут bi отношения A и атрибут bi отношения B не только обладают одним и тем же именем, но и определены на одном и том же домене. Назовем множество атрибутов {aj} составным атрибутом a, а множество атрибутов {bj} - составным атрибутом b. После этого будем говорить о реляционном делении бинарного отношения A(a,b) на унарное отношение B(b). Результатом деления A на B является унарное отношение C(a), состоящее из кортежей v таких, что в отношении A имеются кортежи такие, что множество значений {w} включает множество значений атрибута b в отношении B.

132 Деление отношений Пусть даны отношения A(X 1,X 2,…,X n, Y 1, Y 2, …, Y n ) и B(Y 1, Y 2, …, Y n ), причем атрибуты (Y 1, Y 2, …, Y n ) - общие для двух отношений. Делением отношений A на B называется отношение с заголовком (X 1,X 2,…,X n ) и телом, содержащим множество кортежей (x 1,x 2,…,x n ) таких, что для всех кортежей (y 1, y 2, …, y n ) в отношении A найдется кортеж (x 1,x 2,…,x n, y 1, y 2, …, y n ).

133 Деление отношений Отношение A выступает в роли делимого, отношение B выступает в роли делителя. Деление отношений аналогично делению чисел с остатком. Синтаксис операции деления: A devidby B

134 Пример В примере с поставщиками, деталями и поставками ответим на вопрос, "какие поставщики поставляют все детали?".

135 Пример (продолжение) Номер поставщика PNUM Номер детали DNUM

136 Пример (продолжение) Номер детали DNUM 1 2 3

137 Пример (продолжение) Номер поставщика PNUM 1 Деление X devidby И дает список номеров поставщиков, поставляющих все детали:

138 Структурированный язык запросов SQL Язык РБД SQL (Structured Query Language) предназначен для определения ресурсов данных, манипулирования данными и управления доступом к данным на концептуальном уровне. SQL не является языком программирования и не содержит никаких процедурных средств. Строго говоря, это подъязык данных.

139 Структурированный язык запросов SQL Первая версия SQL была разработана фирмой IBM в начале 70-х годов[1] и использовалась в качестве входного языка экспериментальной СУБД System-R. Описание языка было впервые опубликовано в 1974 году. К середине 80-х годов многие производители коммерческих СУБД создали свои реализации SQL. Во избежание «вавилонского смешения языков» в мире реляционных СУБД Международная Организация Стандартизации и Американский Национальный Институт Стандартов (ISO/ANSI) приняли в 1986 году первый стандарт SQL1. В 1989 году этот стандарт был пересмотрен. Сейчас на него ссылаются как на SQL1 или SQL-89. В 1992 году принят новый стандарт, известный ныне под названиями SQL2, SQL- 92, SQL/92. Распространенные в настоящее время коммерческие СУБД, за исключением наиболее «продвинутых», ориентированы на стандарт SQL1. Мы при изложении возможностей языка будем в основном придерживаться требований SQL2, т.к. этот стандарт практически полностью включает SQL1 и является основой для перспективных разработок.[1] [1] Она называлась SEQUEL (Structured English Query Language). [1]

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

141 Структурированный язык запросов SQL

142 Интерактивный SQL дает возможность пользователю работать с базой данных в интерактивном режиме. Вводимые пользователем команды SQL немедленно исполняются. Статический SQL – записанный заранее код SQL, используемый в прикладных программах. Предусмотрены две версии статического SQL. Встроенный SQL – код SQL, включённый в код исходного текста прикладной программы на одном из ЯВУ. Эта версия является расширением базового ЯВУ за счет операторов SQL. Модульный SQL не предполагает включения операторов SQL в базовый язык. Приложение состоит из отдельных модулей SQL- операторов, скомпонованных с модулями других языков. Динамический SQL – код SQL, генерируемый во время исполнения прикладной программы. Он заменяет статический в тех случаях, когда необходимый код SQL не может быть определен при написании приложения, так как он зависит от оперативных решений пользователя.

143 Структурированный язык запросов SQL Категории язык определения данных DDL – Data Definition Language язык манипулирования данными DML – Data Manipulation Language операторы управления данными DCS – Data Control Statements

144 Язык определения данных (DDL – Data Definition Language) включает все операторы, используемые для определения схемы БД и объектов схемы; CREATE SCHEMA - создать схему базы данных DROP SHEMA - удалить схему базы данных CREATE TABLE - создать таблицу ALTER TABLE - изменить таблицу DROP TABLE - удалить таблицу CREATE DOMAIN - создать домен ALTER DOMAIN - изменить домен DROP DOMAIN - удалить домен CREATE COLLATION - создать последовательность DROP COLLATION - удалить последовательность CREATE VIEW - создать представление DROP VIEW - удалить представление

145 Язык манипулирования данными (DML – Data Manipulation Language) содержит операторы, используемые для выборки и обновления данных; SELECT - отобрать строки из таблиц INSERT - добавить строки в таблицу UPDATE - изменить строки в таблице DELETE - удалить строки в таблице COMMIT - зафиксировать внесенные изменения ROLLBACK - откатить внесенные изменения

146 Операторы управления данными (DCS – Data Control Statements) используются для определения привилегий пользователей. CREATE ASSERTION - создать ограничение DROP ASSERTION - удалить ограничение GRANT - предоставить привилегии пользователю или приложению на манипулирование объектами REVOKE - отменить привилегии пользователя или приложения Имеются и другие категории, например, операторы определения транзакций, диагностические операторы и т.д. Стандарт SQL2 определяет свыше 50 операторов.

147 Объект Стандарт трактует объект как нечто, имеющее уникальное имя и определение, постоянно сохраняемые в системном каталоге СУБД. Например, объектами являются схемы, явно определенные таблицы, домены, правила (утверждения). Объект создается пользователем, имеющим соответствующую привилегию CREATE. Этот пользователь является владельцем объекта и может распоряжаться им по своему усмотрению. В частности, он может передавать привилегии другим пользователям. Объект может быть уничтожен только его владельцем. Исполняя оператор уничтожения объекта, система удалит из своего каталога его имя и определение.

148 Объект Стандартное имяРусское название ASSERTIONУтверждение CHARACTER SETНабор символов COLLATIONСравнение DOMAINДомен SCHEMAСхема TABLEТаблица TRANSLATIONТрансляция VIEWПредставление

149 Идентификатор авторизации Для того чтобы SQL-система могла обеспечить защиту данных, каждый источник SQL-операторов (SQL-агент) получает уникальное имя – идентификатор авторизации (Authorization ID). ID сохраняется в системном каталоге и может быть именем физического лица, группы или прикладной программы. Какие бы то ни было действия в БД выполняются SQL-системой только от имени конкретного ID. С ID могут быть связаны привилегии, определяющие конкретные действия в БД, которые разрешено выполнять пользователю. Обычно привилегии определяют действия ID при работе с интерактивным SQL или в рамках приложения. Однако при работе с приложениями привилегии часто назначаются приложениям, а не ID, от имени которых они запускаются. В этом случае ID, имеющий привилегию запуска приложения, как бы получает его привилегии. Они распространяются только на сеанс работы с приложением и недействительны в интерактивном режиме. Идентификатор авторизации (ID) – идентификатор источника операторов SQL и одновременно объект, с которым связаны привилегии. Может быть пользователем или модулем, владельцем схемы SQL и ее содержимого.

150 Сеанс Установление связи с СУБД (подключение) определяет начало сеанса работы пользователя. При подключении система получает информацию о том, какой ID будет являться SQL-агентом. Сеанс – набор SQL-операторов, исполняемых от имени определенного ID с момента его подключения к системе до момента отключения. SQL-агенту разрешено выполнять несколько подключений. Поэтому одновременно могут существовать несколько сеансов одного и того же ID. В каждом из них могут использоваться различные источники данных. Активным в любой момент времени является только один сеанс – текущий. Пользователь может изменять текущий сеанс.

151 Тип данных Все значения любого столбца в любой SQL-таблице должны принадлежать одному и тому же типу данных. Тип данных должен быть частью определения самого столбца или частью определения домена, из которого выбираются значения столбца. Стандарт определяет шесть категорий типов данных. Каждый тип имеет собственное имя и может быть только простым. Допускаются синонимы имен, например, CHARACTER эквивалентно CHAR. Имена типов или их синонимы используются в определениях столбцов и доменов.

152 Домен Понятие домена в SQL отличается от аналогичного понятия в РМД. Стандарт не определяет домен как логическую категорию значений, которые можно сравнивать. Согласно стандарту, домен есть просто именованная совокупность значений данных. Имя и определение домена (см. п. 0) сохраняются в специальной таблице системного каталога. На имя домена можно ссылаться внутри определения таблицы как на тип данных. Домен (DOMAIN) – объект, который может использоваться как альтернатива типу данных для столбцов. Домен определяет тип данных и может также задавать некоторые другие элементы, например, значение по умолчанию, одно или несколько ограничений.

153 Таблица Таблица в SQL является аналогом отношения РМД. Однако в отличие от отношения, схема таблицы может не содержать уникального подмножества атрибутов (столбцов). Стандарт не требует обязательного указания первичного и альтернативных ключей при определении таблицы. С точки зрения SQL-системы в этом нет необходимости. Она автоматически присваивает каждой строке уникальный внутренний номер – суррогатный ключ. Однако этот ключ пользователю не виден и вне системы смысла не имеет. Это приводит к тому, что в таблицах SQL могут (с точки зрения пользователя) содержаться дубликаты строк и совершенно непонятно, как их следует интерпретировать. Таблица (TABLE) – объект, являющийся набором значений, которые могут разделяться на строки и столбцы, так что на пересечении строки и столбца располагается только одно значение. Все значения в столбце принадлежат одному типу данных (домену). Столбцы имеют имена, уникальные в пределах таблицы.

154 Таблица

155 Представление Кроме базовых таблиц, физически сохраняемых во внешней памяти, в схеме могут быть определены виртуальные таблицы – представления. Представление имеет собственное имя, зарегистрированное в системном каталоге. На него можно ссылаться как на имя таблицы в операторах манипулирования данными. Заголовок представления, в отличие от заголовка таблицы, не определяется явно путём специфицирования столбцов. Определение представления содержит запрос к другим таблицам, который связывается с именем представления и сохраняется в системном каталоге. Запрос исполняется при обработке оператора манипулирования данными, в котором встретилась ссылка на представление. Результат запроса и является содержимым представления. Таким образом, представление, в отличие от временной таблицы, всегда отражает текущее состояние базовых таблиц, существующее в момент исполнения запроса. Представление (VIEW) есть объект, содержащий именованный запрос, на который можно ссылаться в операторах манипулирования данными. Механизм представлений обеспечивает возможность предъявления хранимых данных в удобной и привычной для конечного пользователя форме. Кроме того, он является средством ограничения доступа к данным.

156 Язык манипулирования данными В состав языка манипулирования данными (DML) входят четыре оператора, предназначенных для извлечения и обновления данных. Эти операторы (таблица 6.3) применяются только к именованным таблицам. Их общая особенность состоит в том, что они описывают конечный результат обработки данных, а не процедуру обработки. SQL-система сама определяет, где находятся нужные данные, и какие пути доступа к ним существуют, и сама генерирует эффективные последовательности операций для их получения (обновления).

157 Язык манипулирования данными НазваниеНазначение SELECTОсуществляет выборку данных из одной или более таблиц,собирая их в неименованную временную таблицу. INSERTДобавляет новые строки в существующую таблицу. UPDATEОбновляет значения указанных полей в строках существующей таблицы. DELETEУдаляет строки существующей таблицы.

158 Оператор выборки данных Оператор SELECT реализует концепции реляционной алгебры и реляционного исчисления и является наиболее сложной и богатой возможностями командой языка. Результатом действия оператора является неименованная таблица, на которую невозможно сослаться из других команд SQL. Тем не менее, произведенный оператором SELECT результат может быть использован для управления работой любого другого оператора DML. Кроме того, оператор SELECT используется в определениях ограничений, а представления можно трактовать как именованные операторы SELECT. Оператор SELECT часто используется в предикатах SQL в качестве переменной в простом операторе сравнения или в качестве определения множества допустимых значений переменной в специальных предикатах. Это обеспечивает возможность вложения команд выборки данных, благодаря чему запрос любой сложности может быть сформулирован в виде одного оператора SELECT, содержащего несколько подзапросов – вложенных команд SELECT. Многие основные и вспомогательные конструкции SQL созданы для описания понятий, связанных с выборкой данных. Поэтому мы начнем знакомство с языком именно с оператора SELECT, являющегося фундаментальным средством.

159 Оператор выборки данных Синтаксическая диаграмма оператора имеет вид: SELECT [ DISTINCT ] {элемент_целевого_списка.,..} | [ спецификатор. ]* FROM { { имя_таблицы [ [AS] псевдоним [(имя_столбца.,..)] ]} | { подзапрос [AS] псевдоним [(имя_столбца.,..)] } | соединение }.,.. [ WHERE предикат ] [ GROUP BY { [спецификатор.]имя_столбца }.,..] [ HAVING предикат ] [ ORDER BY {{ имя_целевого_столбца [ ASC | DESC ]}.,..} | {{ положительное_целое [ ASC | DESC ]}.,..} ] ;

160 Оператор выборки данных Из синтаксической диаграммы видно, что в общем случае оператор может включать шесть предложений. Два из них – SELECT и FROM – являются обязательными и входят в состав любого конкретного оператора выборки. Они указывают, что должно быть выбрано (целевой список предложения SELECT) и из каких таблиц (список таблиц в предложении FROM). Остальные четыре предложения (WHERE, GROUP BY, HAVING, ORDER BY) определяют условия, которым должны удовлетворять выходные данные. Порядок следования предложений в любом реальном операторе SELECT должен соответствовать указаному в диаграмме. Прежде чем познакомиться с деталями синтаксических конструкций, рассмотрим с концептуальной точки зрения процесс исполнения оператора в целом.

161 Обработка оператора SELECT Последовательность обработки предложений в операторе SELECT не вполне соответствует порядку их следования в синтаксической диаграмме

162 Обработка оператора SELECT Первым всегда обрабатывается предложение FROM. Оно описывает источник данных – набор таблиц, содержащих запрашиваемые данные. В результате обработки в рабочем буфере системы создается неименованная временная таблица, которую мы будем называть далее F-таблицей. С концептуальной точки зрения она представляет собой расширенное прямое произведение таблиц, указанных после ключевого слова FROM. Необязательные предложения WHERE, GROUP BY, HAVING, если они использованы в команде, обрабатываются в указанном порядке после предложения FROM. В результате обработки в рабочих буферах системы создаются неименованные временные таблицы, которые мы будем называть W-, G- и H-таблицами соответственно. Каждая из них является преобразованием таблицы предыдущего уровня. W-таблица содержит только те строки F-таблицы, на которых предикат предложения WHERE принял значение TRUE. G-таблица представляет собой F- или W-таблицу, строки которой собраны в группы с одинаковыми наборами значений в столбцах, указанных после ключевого слова GROUP BY. H-таблица включает только такие группы строк из G-таблицы, на которых предикат предложения HAVING принял значение TRUE.

163 Обработка оператора SELECT Выходная таблица запроса является результатом обработки предложения SELECT. В простейшем случае её можно представлять себе как реляционную проекцию F-, W-, G- или H-таблицы на столбцы, указанные после ключевого слова. На самом деле это неточно, так как элементами целевого списка запроса могут быть не только имена столбцов источника, но и выражения, агрегатные функции и т.п. Предложение ORDER BY всегда обрабатывается после предложения SELECT. Результатом обработки является выходная таблица запроса, строки которой упорядочены по значениям столбцов, указанных после ключевого слова ORDER BY. В процессе обработки оператора SELECT никаких изменений в физической базе данных не происходит. Система манипулирует копиями базовых таблиц в своих рабочих буферах.

164 Алгоритм исполнения оператора SELECT 1. Вычислить и поместить в рабочий буфер F-таблицу – расширенное прямое произведение таблиц, указанных после слова FROM. 2. Если в состав оператора включено предложение WHERE, то для каждой строки F-таблицы вычислить значение предиката, указанного после ключевого слова. Вычислить и поместить в рабочий буфер W-таблицу, составленную из строк F-таблицы, на которых предикат принял значение TRUE. 3. Если в состав оператора включено предложение GROUP BY, то вычислить и поместить в рабочий буфер G-таблицу, собрав строки рабочего буфера в группы с одинаковыми значениями столбцов, указанных после ключевого слова, иначе выполнить п Если в составе оператора присутствует ключевое слово HAVING, то для каждой группы строк G-таблицы вычислить значение предиката, указанного после ключевого слова. Вычислить и поместить в рабочий буфер H-таблицу, составленную из строк G- таблицы, на которых предикат принял значение TRUE.

165 Алгоритм исполнения оператора SELECT 5. Если в рабочем буфере находится F-таблица или W-таблица, то для каждой строки рабочего буфера вычислить значения столбцов, указанных после ключевого слова SELECT, и сформировать строку выходной таблицы запроса. Иначе, если в рабочем буфере находится G-таблица или H-таблица, то для каждой группы строк рабочего буфера вычислить значения столбцов, указанных после ключевого слова SELECT, и сформировать одну строку выходной таблицы запроса. 6. Если в составе оператора присутствует ключевое слово ORDER BY, то отсортировать строки выходной таблицы запроса по значениям столбцов, указанных после ключевого слова.

166 Предложение SELECT Предложение SELECT описывает целевой список – заголовок выходной таблицы запроса. Список может задаваться посредством перечисления элементов. элемент_целевого_списка ::= { {[спецификатор.]имя_столбца} | выражение | агрегатная_функция } [ AS имя_целевого_столбца ] } | символьная_константа

167 Предложение SELECT спецификатор – имя или псевдоним источника данных; имя_столбца – имя столбца источника данных; выражение – допустимое выражение для вычисления значения; агрегатная_функция – функция множества значений (столбца), возвращающая скалярное значение [2, п.2.5.5]; имя_целевого_столбца – имя столбца выходной таблицы; обычно используется для именования столбцов, содержащих значения выражений или агрегатных функций; символьная_константа – любая последовательность допустимых символов. В этом случае порядок следования столбцов выходной таблицы запроса задаётся явно, а типы и длины значений столбцов наследуются от соответствующих столбцов базовых таблиц. Если для определения целевого списка используется конструкция [спецификатор.]*, то в заголовок выходной таблицы войдут (в соответствующем порядке) все столбцы указанного источника данных. Примеры допустимых предложений SELECT приведены ниже.

168 Предложение FROM Предложение FROM определяет исходные данные для запроса – источник данных. Обычно источниками данных являются именованные таблицы. имя_таблицы – имя базовой таблицы или представления. [AS] псевдоним[(имя_столбца.,..)] – необязательная конструкция, определяющая новое имя таблицы и, возможно, новые имена её столбцов. Ключевое слово AS смысловой нагрузки не несёт. Псевдоним используется для ссылок в последующих предложениях команды и действителен только внутри того оператора SELECT, в котором определён. Обычно именованным таблицам назначают псевдонимы для того, чтобы облегчить понимание запроса человеком. Однако в ряде случаев псевдонимы необходимы для разрешения конфликтов имён. Стандарт SQL1 допускал в предложении FROM только ссылки на имена базовых таблиц и представлений. В отличие от этого, SQL2 разрешает использовать в качестве источников данных неименованные таблицы – результаты запросов или явно заданных реляционных операций соединения.

169 Предложение FROM Конструкция подзапрос [AS] псевдоним [(имя_столбца.,..)] предназначена для обеспечения ссылок на столбцы результата запроса. Здесь подзапрос – заключённый в круглые скобки оператор SELECT произвольной сложности. В этом случае псевдоним обязателен. Он используется как локальное имя неименованной таблицы, произведённой подзапросом.

170 Пример 1 Получить имена и статусы всех поставщиков. SELECT S.Snam, S.St FROM S; Этот оператор произведёт следующую таблицу:

171 Пример 1 SnamSt Иван100 Николай50 Григорий80 Петр20 Константин100 Иван100 Сергей90 Владимир30 Егор100

172 Пример 2 Получить имена и статусы всех поставщиков, использовать оператор переименования колонок, а также исключить дубликаты строк. SELECT DISTINCT Snam AS Имя, St AS Статус FROM S; Во временной таблице изменятся имена столбцов Snam и St на Имя и Статус соответственно.

173 Пример 2 ИмяСтатус Владимир30 Григорий80 Иван100 Константин100 Николай50 Петр20 Егор100 Сергей90

174 Пример 3 Использование скалярных выражений и переименований колонок в запросах SELECT Pnum AS Номер, Pnam AS Наименование, We/1000 AS Вес, 'кг' AS Ед. измер. FROM P;

175 Пример 3 Номер НаименованиеВесЕд. измер. P1корпус0,3кг P2разъём0,003кг P3кнопка0,005кг P4корпус0,8кг P5панель0,1кг P6кинескоп1,2кг P7кнопка0,01кг P8панель0,45кг P9лампочка0,001кг

176 Пример 4 Получить всю информацию о поставщиках, имеющих статус выше 90. SELECT * FROM S WHERE St > 90;

177 Пример 4 SnumSnamStCi S5Константин100Яя S9Иван100Лесото S9Егор100Яя S1Иван100Томск

178 Пример 5 Получить значения номеров и наименований изделий, производимых в Томске. SELECT Jnum, Jnam FROM J WHERE Ci = Томск;

179 Пример 5 JnumJnam J3дисплей J6клавиатура

180 Insert Insert into table TableName (columns defenition) Values (values defenition)

181 Update Update TableName set ColumnName=Values Where ColumnName=Values2

182 Alter Alter Table Tablename

183 Delete Delete from TableName

184 Drop Drop table TableName

185 SQL РА Отношение R R.a1R.a2 A1 A2 B1 B3 B4 CREATE TABLE R(a1 CHAR(1), a2 INT, PRIMARY KEY(a1,a2)) Отношение S S.b1S.b2 1h 2g 3h CREATE TABLE S(b1 INT PRIMARY KEY, b2 CHAR(1))

186 Операция выборки в SQL Выборка σ (a2=1) ( R )={(a, 1), (b, 1)} записывается следующим образом: SELECT a1, a2FROM RWHERE a2=1

187 Операция проекции в SQL Проекция Π b2 (S)={(h), (g)) записывается следующим образом: SELECT b2FROM S

188 Декартово произведение Декартово произведение RxS двух отношений (двух таблиц) определяет новое отношение - результат конкатенации (т.е. сцепления) каждого кортежа (каждой записи) из отношения R с каждым кортежем (каждой записью) из отношения S. RxS={(a, 1, 1, h), (a, 2, 1, h), (b, 1, 1, h),... }SELECT R.a1, R.a2, S.b1, S.b2

189 Операция соединения по двум отношениям Формат операции: FROM имя_таблицы_1 {INNER | LEFT | RIGHT} JOIN имя_таблицы_2 ON условие_соединения Существуют различные типы операций соединения: тета-соединение R F S; соединение по эквивалентности R =S; естественное соединение R S; внешнее соединение R S; R S; полусоединение R F S.

190 Естественное соединение называется соединение по эквивалентности двух отношений R и S, выполненное по всем общим атрибутам, из результатов которого исключается по одному экземпляру каждого общего атрибута SELECT R.a1, R.a2, S.b2FROM R, SWHERE R.a2=S.b1 SELECT R.a1, S.b1, S.b2FROM R INNER JOIN S ON R.a2=S.b1

191 Естественное соединение R S, F=(R.a2=S.b1) R.a1 R.a2 или S.b1 S.b2 a1h a2g b3h b1h

192 Проекция SELECT *FROM Сделка, Товар WHERE Сделка.КодТовара=Товар.КодТовара SELECT *FROM Товар INNER JOIN Сделка ON Товар.КодТовара=Сделка.КодТовара

193 Тета-соединение отношений в SQL SELECT R.a1, R.a2, S.b1, S.b2FROM R, SWHERE R.a2=S.b1 SELECT R.a1, R.a2, S.b1, S.b2FROM R INNER JOIN S ON R.a2=S.b1

194 Внешнее соединение. R S, F=(R.a2=S.b1) R.a1R.a2S.b1S.b2 a11h a22g b11h b33h b4null Левое SELECT R.a1, R.a2, S.b1, S.b2FROM R LEFT JOIN S ON R.a2=S.b1 Правое SELECT R.a1, R.a2, S.b1, S.b2FROM R RIGHT JOIN S ON R.a2=S.b1

195 Объединение SELECT R.a1, R.a2 FROM R UNION SELECT S.b2, S.b1 FROM S

196 Пересечение SELECT R.a1, R.a2 FROM R,S WHERE R.a1=S.b1 AND R.a2=S.b2 SELECT R.a1, R.a2FROM R WHERE R.a1 IN (SELECT S.b1 FROM S WHERE S.b1=R.a1) AND R.a2 IN (SELECT S.b2 FROM S WHERE S.b2=R.a2)

197 Разность SELECT R.a1, R.a2FROM R WHERE NOT EXISTS (SELECT S.b1,S.b2 FROM S WHERE S.b1=R.a2 AND S.b2=R.a1)

198 Таблица 5.6. Отношение R имяполроствозраствес aж bм cж Отношение S имяполвозраст aж20

199 Деление CREATE VIEW T1AS SELECT рост,весFROM R CREATE VIEW TT ASSELECT S.имя, S.пол, S.возраст, T1.рост, T1.весFROM S, T1 CREATE VIEW T2ASSELECT TT.рост, TT.весFROM TTWHERE NOT EXISTS (SELECT R.рост, R.вес FROM R WHERE TT.имя=R.имя AND TT.пол=R.пол AND TT.возраст=R.возраст AND TT.рост=R.рост AND TT.вес=R.вес) SELECT T1.рост, T1.весFROM T1WHERE NOT EXISTS (SELECT T2.рост,T2.вес FROM T2 WHERE T1.рост=T2.рост AND T1.вес=T2.вес) T1=П C (R) роствес TT=(S X T1)-R имяполвозрастроствес T2=П C ((S X T1)-R) роствес T=T1-T2 роствес

200 РМД. Целостность.

201 Целостность данных Модель БД отражает реальное состояние ПО в любой фиксированный момент времени в виде текущей конфигурации хранимых данных. Очевидны два основных требования к этой конфигурации. Любое хранимое в БД значение любого семантически значимого атрибута в любой момент времени должно быть истинным значением характеристики соответствующего объекта ПО. Состояние БД в любой момент времени должно иметь осмысленную интерпретацию в терминах ПО.

202 Целостность данных Текущее состояние БД является целостным, если возможна осмысленная интерпретация его в терминах ПО. Целостность данных и адекватность данных – не одно и то же. Данные могут быть целостными, но при этом не соответствующими действительности. Обеспечение адекватности – проблема пользователя, поддержание целостности может быть проблемой СУБД.

203 Целостность данных Правила, о которых мы только что говорили, являются специфическими в том смысле, что они применяются к одной конкретной БД. Их называют внешними ограничениями целостности (ОЦ) данных. Внешние ОЦ – это обусловленные требованиями конкретной ПО правила, соблюдение которых обеспечивает интерпретируемость хранимых данных. Все БД, основанные на РМД, кроме специфических правил (внешних ОЦ), подчиняются еще общим правилам целостности. Эти правила называются внутренними ОЦ РМД. Однако, прежде чем обсуждать их, введем важнейшие понятия возможного, первичного и внешнего ключей отношения.

204 Целостность данных Определение. Пусть R( ) – схема отношения и K R( ) – подмножество атрибутов схемы. Подмножество К называется возможным (потенциальным) ключом отношения, если А) в любой момент времени в текущем значении R нет двух кортежей с одинаковым значением К; Б) никакое подмножество L К не обладает свойством А). Свойство А) называется свойством уникальности, а свойство Б) – свойством неизбыточности. Возможный ключ отношения – это уникальное неизбыточное подмножество его атрибутов.

205 Целостность данных Потенциальные ключи служат средством идентификации объектов предметной области, данные о которых хранятся в отношении. Объекты предметной области должны быть различимы. Потенциальные ключи служат единственным средством адресации на уровне кортежей в отношении. Точно указать какой-нибудь кортеж можно только зная значение его потенциального ключа Важно заметить, что понятие возможного ключа – логическое. Его не следует путать с физическим понятием уникального индекса Если потенциальный ключ содержит единственный атрибут, то он называется простым. В противном случае – составным. Если отношение имеет несколько возможных ключей, то один из них выделяется и помечается как первичный. Тогда все остальные возможные ключи называются альтернативными. Первичные ключи обеспечивают механизмы связи отношений

206 Целостность данных Определение. Пусть В – базовое отношение и FK – некоторое подмножество атрибутов его схемы FK B ( ). FK называется внешним ключом (Foreign Key) отношения В, если А) существует базовое отношение А с первичным ключом РК (Primary Key); Б) каждое значение FK в текущем значении В всегда совпадает со значением РК некоторого кортежа в текущем значении А.

207 Целостность данных Отношения А и В называются связанными. Отношение А называется родительским или ссылочным, В – потомком или ссылающимся. РК называют родительским (ссылочным) ключом. FK – ключом-потомком (ссылающимся).

208 Целостность данных поставщика Наименование поставщика Номер детали Наименование детали Поставляемое количество 1Иванов1Болт100 1Иванов2Гайка200 1Иванов3Винт300 2Петров1Болт150 2Петров2Гайка250 3Сидоров3Винт1000

209 Целостность данных Что произойдет, если изменилось наименование поставщика? Т.к. наименование поставщика повторяется во многих кортежах отношения, то это наименование нужно одновременно изменить во всех кортежах, где оно встречается, иначе данные станут противоречивыми. То же самое с наименованиями деталей. Значит, данные хранятся в нашем отношении с большой избыточностью

210 Целостность данных Далее, как отразить факт, что некоторый поставщик, например Петров, временно прекратил поставки деталей? Если мы удалим все кортежи, в которых хранится информация о поставках этого поставщика, то мы потеряем данные о самом Петрове как потенциальном поставщике. Выйти из этого положения, оставив в отношении кортеж типа (2, Петров, NULL, NULL, NULL) мы не можем, т.к. атрибут "Номер детали" входит в состав потенциального ключа и не может содержать null-значений. То же самое произойдет, если некоторая деталь временно не поставляется никаким поставщиком. Получается, что мы не можем хранить информацию о том, что есть некий поставщик, если он не поставляет хотя бы одну деталь, и не можем хранить информацию о том, что есть некоторая деталь, если она никем не поставляется.

211 Целостность данных Подобные проблемы возникают потому, что мы смешали в одном отношении различные объекты предметной области - и данные о поставщиках, и данные о деталях, и данные о поставках деталей. Говорят, что это отношение плохо нормализовано (просто нормализованным оно является хотя бы потому, что оно есть отношение и, следовательно, автоматически находится в 1НФ).

212 Целостность данных В реляционных базах данных основными являются взаимосвязи типа "один-ко-многим". Взаимосвязи типа "много-ко-многим" реализуются использованием нескольких взаимосвязей типа "один-ко-многим". Отношение, входящее в связь со стороны "один" (например, "Поставщики"), называют родительским отношением. Отношение, входящее в связь со стороны "много" (например, "Поставки"), называется дочернем отношением.

213 Целостность данных Разнесем данные по трем отношениям: "Поставщики", "Детали", "Поставки".

214 Целостность данных "Поставщики выполняют Поставки", "Детали поставляются через Поставки". Эти две взаимосвязи косвенно определяют новую взаимосвязь между "Поставщиками" и "Деталями": "Детали поставляются Поставщиками". Эти фразы отражают различные типы взаимосвязей. Чтобы более точно отразить предметную область, можно иначе переформулировать фразы: "Один Поставщик может выполнять несколько Поставок", "Одна Деталь может поставляться несколькими Поставками". Это пример взаимосвязи типа "один-ко-многим". Взаимосвязь между "Поставщиками" и "Деталями" можно переформулировать так: "Несколько Деталей может поставляться несколькими Поставщиками". Это пример взаимосвязи типа "много-ко-многим".

215 Целостность данных Механизм реализации взаимосвязи "один-ко-многим" состоит в том, что в дочернее отношение добавляются атрибуты, являющиеся ссылками на ключевые атрибуты родительского отношения. Эти атрибуты и являются внешними ключами, определяющими, с какими кортежами родительского отношения связаны кортежи дочернего отношения. Такие атрибуты еще называют мигрирующими из родительского отношения.

216 Номер поставщикаНаименование поставщика 1Иванов 2Петров 3Сидоров Номер деталиНаименование детали 1Болт 2Гайка 3Винт Номер поставщикаНомер деталиПоставляемое количество

217 Внутренние ограничения целостности РМД. Выделяют три разновидности внутренних ОЦ: целостность домена, целостность сущности, ссылочная целостность.

218 Целостность домена Домены являются одной из важнейших составляющих РМД. Механизм доменов обеспечивает: принципиальную возможность ограничения сравнений и блокирования бессмысленных операций в БД; принципиальную возможность формального контроля ввода данных. Если предикат, определяющий домен, принял на вводимом значении атрибута значение.F., система не должна принимать это значение атрибута. Таким образом, если система поддерживает домены, то она способна предотвратить грубые ошибки пользователя. Поэтому при проектировании концептуальной модели очень важно правильно определить домены всех хранимых атрибутов. Правило целостности домена: всякий атрибут может принимать значения только из своего домена.

219 Целостность сущности Правило целостности сущности часто формулируют как требование уникальности значений первичного ключа. Однако, на самом деле это не так. Требование целостности сущности состоит в том, что каждый кортеж любого отношения должен быть идентифицируем. Иначе говоря, в БД не должна храниться информация о чем-то таком, что мы не можем идентифицировать. Это требование связано с проблемой представления незнания в БД. В реальной жизни в базу данных нередко приходится включать кортежи, содержащие неизвестные значения некоторых атрибутов. Правило целостности сущности: ни один компонент первичного ключа отношения не может принимать неопределенное значение.

220 Ссылочная целостность Требование ссылочной целостности: ни в какой момент времени в базе данных не может быть значений ссылающегося ключа, которых нет среди существующих значений родительского ключа. или ни в какой момент времени в БД не может быть определенных (не Null-) значений ссылающегося ключа, которых нет среди существующих значений родительского ключа.

221 Операции обновления и целостность данных Целостность доменаЦелостность сущности Ссылочная целостность INSERTДА DELETEНЕТ ДА UPDATEДА

222 Контроль целостности домена. Правило целостности домена должно проверяться при любой попытке ввода/обновления значения любого атрибута, определенного на этом домене. Для того чтобы РСУБД могла это делать, правило должно быть сформулировано в виде предиката и связано с доменом специальным предложением ЯОД – предложением объявления домена (см. п ). Обрабатывая это предложение, система вносит в свой каталог имя домена и реализует процедуру вычисления значений предиката. Процедура связывается с именем домена и сохраняется для дальнейшего исполнения. Атрибут связывается со своим доменом строкой предложения определения базового отношения (см. п ). Это определение также сохраняется в системном каталоге. При попытке присваивания атрибуту значения в новом или существующем кортеже система находит по каталогу имя соответствующего домена и вычисляет значение связанного с ним предиката на новом значении атрибута. Если предикат принял значение.F., обновление отвергается.

223 Контроль целостности сущности Правило целостности сущности должно проверяться при любой попытке добавления нового кортежа в базовое отношение (операция INSERT) или обновления значения первичного ключа (операция UPDATE). Для этого в РСУБД может быть определена стандартная процедура, возвращающая логическое значение. Входными данными для нее являются имя обновляемого отношения и вводимое значение набора атрибутов первичного ключа. Процедура возвращает значение.F., если хотя бы один атрибут набора принял значение Null или значение набора уже существует в текущем множестве значений первичного ключа обновляемого отношения. Процедура связывается с конкретным отношением в процессе обработки строки определения первичного ключа в предложении определения отношения (см. п ). В соответствующий раздел системного каталога заносится перечень атрибутов, входящих в состав первичного ключа. При попытке выполнить операцию UPDATE модуль контроля обновлений может установить по каталогу, входит ли обновляемый атрибут в первичный ключ. Если да, то процедура запускается. Попытка выполнения операции INSERT всегда инициирует эту процедуру. Обновление отвергается, если процедура контроля целостности сущности возвратила значение.F.

224 Ссылочная целостность Для поддержания обычно используются две основные стратегии: RESTRICT (ОГРАНИЧИТЬ) - не разрешать выполнение операции, приводящей к нарушению ссылочной целостности. CASCADE (КАСКАДИРОВАТЬ) - разрешить выполнение требуемой операции, но внести каскадные изменения в другие отношения так, чтобы не допустить нарушения ссылочной целостности. Дополнительными стратегиями поддержания ссылочной целостности являются: SET NULL (УСТАНОВИТЬ В NULL) - все некорректные значения внешних ключей изменять на null-значения. SET DEFAULT (УСТАНОВИТЬ ПО УМОЛЧАНИЮ) - все некорректные значения внешних ключей изменять на некоторое значение, принятое по умолчанию. В реальных СУБД можно также отказаться от использования какой- либо стратегии поддержания ссылочной целостности: IGNORE (ИГНОРИРОВАТЬ) - выполнять операции, не обращая внимания на нарушения ссылочной целостности.

225 Нотации

226 Нотация Чена

227 Нотация Мартина

228 Нотация IDEF1X

229

230 Нотация Баркера

231 Трехуровневая архитектура СБД. ANSI SPARC Эта архитектурная концепция предлагает использовать три уровня представления данных: внешний уровень – представления данных для различных конечных пользователей. внутренний уровень – представление данных в памяти ЭВМ, но без конкретных технических деталей (схема хранения). концептуальный уровень – обобщенное логическое представление данных, не содержащее никаких ссылок на реализацию.

232 Трехуровневая архитектура СБД

233

234 Пример: Пусть в нашей ПО имеется объект СЛУЖАЩИЙ, характеризующийся свойствами: табельный номер номер отдела зарплата Есть два конечных пользователя БД – КП1 и КП2. Для КП1 представляют интерес только табельный номер и номер отдела, для КП2 – табельный номер и зарплата. Объект имеет три уровня представления или описания.

235 Трехуровневая архитектура СБД Каждому из них обслуживающая его ПП предоставляет ту информацию о СЛУЖАЩем, в которой он заинтересован, скрывая другие характеристики.

236 Трехуровневая архитектура СБД Внешний уровень 1 (COBOL) 01 EMP#DCL 02 ЕМPNOPIC X (6) 03 DEPNOPIC X (4) Внешний уровень 2 (PL/1) 1 EMP 2 EMP CHAR (6) 3 SAL FIXED BIN (31); Первые строчки этих текстов объявляют имена объекта. Вторые и третьи строчки указывают имена и типы характеристик объекта. Для КП1 – это табельный номер и номер отдела, а для КП2 – табельный номер и зарплата. Отметим, что в разных представлениях имена объекта и его характеристик могут быть различными.

237 Трехуровневая архитектура СБД Концептуальный уровень СЛУЖАЩИЙ Номер служащего CHAR (6) Номер отдела CHAR (4) Зарплата NUMERIC (5)

238 Трехуровневая архитектура СБД На внутреннем уровне объект СЛУЖАЩИЙ представлен типом внутренней записи STORED_EMP. Внутренний уровень STORED_EMPLENGTH= 20 PREFIXTYPE = BYTE (6),OFFSET= 0 EMP#TYPE = BYTE (6),OFFSET= 6, INDEX = EmPXDEPT#TYPE = BYTE (4),OFFSET=12, PAYTYPE =FULLWORD,OFFSET=16 Указана длина записи – 20 байт. Запись состоит из четырех хранимых полей – PREFIX, EMP#, DEPT# и PAY. Поле PREFIX содержит необходимую служебную информацию. Три поля данных соответствуют свойствам СЛУЖАЩего. Поле EMP# связано с индексом ЕМРХ, обеспечивающим быстрый поиск по значениям ЕМР#. Этот индекс определен на внутреннем уровне, но не виден уже на концептуальном.

239 Трехуровневая архитектура СБД

240 Внешнее представление состоит из множеств экземпляров некоторых типов внешних записей. Эти внешние записи, как видно из примера, отнюдь не должны совпадать с хранимыми. Подъязык данных пользователя определен в терминах внешних записей. Значит, операции выборки/обновления будут определяться над множеством внешних записей. На концептуальном уровне данные представлены такими, какие они есть на самом деле. Концептуальное представление состоит из множества экземпляров каждого типа концептуальной записи. Она вовсе не обязана совпадать с внешней или хранимой записью. Концептуальное представление задается концептуальной схемой, которая включает определения типов концептуальных записей. Для определения концептуальной схемы используется специальный ЯОД, не содержащий средств определения структур хранения и методов доступа к данным. Внутреннее представление состоит из множества экземпляров каждого типа внутренней (хранимой) записи. Это представление, так же, как и внешнее и концептуальное, не связано с физическим уровнем, т.е. с блоками, цилиндрами, дорожками и т.д. Оно описывается внутренней схемой, которая определяет типы хранимых записей, способы представления хранимых полей, физическую последовательность хранения записей, существующие индексы и т.д. Внутренняя схема описывается с помощью внутреннего ЯОД, отличающегося как от ЯОД подъязыка данных внешнего представления, так и от концептуального ЯОД. Всё, что ниже этого уровня, – уровень ОС.

241 Трехуровневая архитектура СБД Отображения определяют соответствия между представлениями верхнего и нижнего уровней. Отображение концептуальный внутренний ставит в соответствие концептуальным полям и записям хранимые. Это соответствие является взаимно однозначным. При изменении структур хранения отображение концептуальный внутренний изменяется так, чтобы концептуальная схема осталась неизменной. Аналогично отображение внешний концептуальный ставит в соответствие концептуальным полям и записям внешние. Отображения реализуются посредством статей словаря данных системы и специальных модулей СУБД, обеспечивающих преобразования данных.

242 Трехуровневая архитектура СБД Доступ к данным в трехуровневой архитектуре

243 Трехуровневая архитектура СБД Шаг 1. ПП обращается к СУБД с запросом на чтение записи внешней модели. Шаг 2. СУБД, используя схемы ВМД и КМД и описание отображения внешний концептуальный, определяет, какие записи КМД необходимы для формирования требуемой записи ВМД. Шаг 3. СУБД, используя схемы КМД и ВНМД и описание отображения концептуальный внутренний, определяет, какие записи внутренней модели необходимы для формирования затребованных записей КМД и совокупность физических записей, которые должны быть для этого считаны с физического носителя. Шаг 4. СУБД выдает ОС запрос на считывание в свои буферы необходимых записей физической базы данных (ФБД). Шаг 5. ОС считывает затребованные записи и помещает их в системные буферы СУБД. Шаг 6. На основании имеющихся схем моделей и описаний отображений СУБД формирует в своем буфере затребованную внешнюю запись. Шаг 7. СУБД пересылает сформированную внешнюю запись в рабочую область (РО) ПП. Шаг 8. СУБД передает в ПП сообщение о результатах выполнения запроса. Процедура записи данных из ПП в ФБД выполняется аналогично

244 Нормализация

245 Функциональные зависимости Пусть задана переменная отношения r, и X и Y являются произвольными подмножествами заголовка r («составными» атрибутами). В значении переменной отношения r атрибут Y функционально зависит от атрибута X в том и только в том случае, если каждому значению X соответствует в точности одно значение Y. В этом случае говорят также, что атрибут X функционально определяет атрибут Y (X является детерминантом (определителем) для Y, а Y является зависимым от X). Будем обозначать это как r.X-> r.Y.

246 Функциональные зависимости Функциональная зависимость соответствует математическому понятию функции и является отношением типа «многие-к-одному» между двумя множествами атрибутов определенного отношения. Для данного отношения R зависимость A>B выполняется для отношения R тогда и только тогда, когда любые два кортежа отношения R с одинаковыми значениями A имеют одинаковые значения B.

247 Функциональные зависимости Очевидно, что если СЛУ_НОМ является первичным ключом отношения СЛУЖАЩИЕ, то для этого отношения справедлива функциональная зависимость (Functional Dependency – FD) СЛУ_НОМ-> СЛУ_ИМЯ.

248 Функциональные зависимости СЛУ_НОМ-> СЛУ_ИМЯ СЛУ_НОМ-> СЛУ_ЗАРП СЛУ_НОМ-> ПРО_НОМ СЛУ_НОМ-> ПРОЕКТ_РУК {СЛУ_НОМ, СЛУ_ИМЯ}-> СЛУ_ЗАРП {СЛУ_НОМ, СЛУ_ИМЯ}-> ПРО_НОМ {СЛУ_НОМ, СЛУ_ИМЯ}-> {СЛУ_ЗАРП, ПРО_НОМ} … ПРО_НОМ-> ПРОЕКТ_РУК и т.д.

249 Функциональные зависимости Поскольку имена всех служащих различны, то выполняются и такие FD (2): СЛУ_ИМЯ-> СЛУ_НОМ СЛУ_ИМЯ-> СЛУ_ЗАРП СЛУ_ИМЯ-> ПРО_НОМ и т.д. FD (3): СЛУ_ЗАРП-> ПРО_НОМ

250 Функциональные зависимости Однако заметим, что природа FD группы (1) отличается от природы FD групп (2) и (3). Логично предположить, что идентификационные номера служащих должны быть всегда различны, а у каждого проекта имеется только один руководитель. Поэтому FD группы (1) должны быть верны для любого допустимого значения переменной отношения СЛУЖАЩИЕ_ПРОЕКТЫ и могут рассматриваться как инварианты, или ограничения целостности этой переменной отношения.

251 Функциональные зависимости FD группы (2) базируются на менее естественном предположении о том, что имена всех служащих различны. Это соответствует действительности для примера, но возможно, что с течением времени FD группы (2) не будут выполняться для какого- либо значения переменной отношения СЛУЖАЩИЕ_ПРОЕКТЫ.

252 Функциональные зависимости Наконец, FD группы (3) основана на совсем неестественном предположении, что никакие двое служащих, участвующие в разных проектах, не получают одинаковую зарплату. Опять же, данное предположение верно для примера, но, скорее всего, это случайное совпадение. В дальнейшем нас будут интересовать только те функциональные зависимости, которые должны выполняться для всех возможных значений переменных отношений

253 Функциональные зависимости Заметим, что если атрибут A отношения r является возможным ключом, то для любого атрибута B этого отношения всегда выполняется FD A B (в группе (1) к этим FD относятся все FD, детерминантом которых является СЛУ_НОМ). Обратите внимание, что наличие в отношении СЛУЖАЩИЕ_ПРОЕКТЫ FD ПРО_НОМ ПРОЕКТ_РУК приводит к некоторой избыточности этого отношения. Имя руководителя проекта является характеристикой проекта, а не служащего, но в нашем случае содержится в теле отношения столько раз, сколько служащих работает над проектом.

254 Функциональные зависимости FD A-> B называется тривиальной, если A B (т. е. множество атрибутов A включает множество B или совпадает с множеством B). Очевидно, что любая тривиальная FD всегда выполняется. Например, в отношении СЛУЖАЩИЕ_ПРОЕКТЫ всегда выполняется FD {СЛУ_ЗАРП, ПРО_НОМ}-> СЛУ_ЗАРП. Частным случаем тривиальной FD является A-> A. Поскольку тривиальные FD выполняются всегда, их нельзя трактовать как ограничения целостности, и поэтому они не представляют интереса с практической точки зрения. Однако в теоретических рассуждениях их наличие необходимо учитывать.

255 Функциональные зависимости FD A C называется транзитивной, если существует такой атрибут B, что имеются функциональные зависимости A-> B и B-> C и отсутствует функциональная зависимость C-> A.

256 Замыкание Множество всех ФЗ, которые задаются данным множеством функциональных зависимостей S, называется замыканием S и обозначается символом S+.

257 Аксиома Армстронга Правило вывода Армстронга: пусть в перечисленных ниже правилах A, B и C произвольные подмножества множества атрибутов заданного отношения R, а символическая запись AB означает объединение A и B. 1. Рефлективность: если B является подмножеством A, то A>B. 2. Дополнение: если A>B, то AС>BС. 3. Транзитивность: если A>B и B>C, то A>C. Каждое из этих правил может быть непосредственно доказано на основе определения функциональной зависимости.

258 Дополнительные правила определения замыкания На практике, на основе приведенных правил выводят дополнительные правила: 1. Самоопределение: A>A. 2. Декомпозиция: если A>BC, то A>B и A >C. 3. Объединение: если A>B и A>C, то A>BC. 4. Композиция: если A>B и C>D, то AC >BD, где D другое произвольное подмножество множества атрибутов R.

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

260 Теорема Хита

261 Декомпозиция без потерь Анализ показывает, что в случае декомпозиции (1) мы не потеряли информацию о служащих – про каждого из них можно узнать имя, размер зарплаты, номер выполняемого проекта и имя руководителя проекта. Вторая декомпозиция не дает возможности получить данные о проекте служащего, поскольку Иванов и Иваненко получают одинаковую зарплату, следовательно, эта декомпозиция приводит к потере информации. Что же привело к тому, что одна декомпозиция является декомпозицией без потерь, а вторая – нет?

262 Декомпозиция без потерь Заметим, что при проведении декомпозиции мы использовали операцию взятия проекции. Каждое из отношений СЛУЖ, СЛУ_ПРО и ЗАРП_ПРО является проекцией исходного отношения СЛУЖАЩИЕ_ПРОЕКТЫ. В случае декомпозиции (1) отсутствие потери информации означает, что в результате естественного соединения отношений СЛУЖ и СЛУ_ПРО мы гарантированно получим отношение, заголовок и тело которого совпадают с заголовком и телом отношения СЛУЖАЩИЕ_ПРОЕКТЫ. Следует отметить, что это произойдет для любых допустимых (и согласованных) значений переменных отношений СЛУЖАЩИЕ_ПРОЕКТЫ, СЛУЖ и СЛУ_ПРО, поскольку у всех этих переменных атрибут СЛУ_НОМ является возможным ключом. Однако если выполнить естественное соединение отношений СЛУ и ЗАРП_ПРО, то будет получено отношение

263 Декомпозиция без потерь Результат естественного соединения отношений СЛУЖ и ЗАРП_ПРО

264 Декомпозиция без потерь Схема этого отношения, естественно (поскольку соединение – естественное), совпадает со схемой отношения СЛУЖАЩИЕ_ПРОЕКТЫ, но в теле появились лишние кортежи, наличие которых и приводит к утрате исходной информации. Интуитивно понятно, что это происходит потому, что в отношении ЗАРП_ПРО отсутствуют функциональные зависимости СЛУ_ЗАРП ПРО_НОМ и СЛУ_ЗАРП ПРОЕКТ_РУК, но точнее причину потери информации в данном случае мы объясним несколько позже.

265 Теорема Хита Пусть задано отношение r {A, B, C} (A, B и C, в общем случае, являются составными атрибутами) и выполняется FD A-> B. Тогда r = (r PROJECT {A, B}) NATURAL JOIN (r PROJECT {A, C}).

266 Декомпозиция без потерь по теореме Хита

267 В отношении СЛУЖАЩИЕ_ОТДЕЛЫ_ПРОЕКТЫ атрибут СЛУ_НОМ не является возможным ключом, но, как показано на предыдущем слайде, наличия FD СЛУ_НОМ-> СЛУ_ОТД оказывается достаточно для декомпозиции этого отношения без потерь.

268 Первые шаги нормализации При проектировании базы данных решаются две основные проблемы. Каким образом отобразить объекты предметной области в абстрактные объекты модели данных, чтобы это отображение не противоречило семантике предметной области и было, по возможности, лучшим (эффективным, удобным и т. д.)? Часто эту проблему называют проблемой логического проектирования баз данных. Как обеспечить эффективность выполнения запросов к базе данных, т. е. каким образом, имея в виду особенности конкретной СУБД, расположить данные во внешней памяти, создания каких дополнительных структур (например, индексов) потребовать и т. д.? Эту проблему обычно называют проблемой физического проектирования баз данных.

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

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

271 Нормальные формы В теории реляционных баз данных обычно выделяется следующая последовательность нормальных форм: первая нормальная форма (1NF); вторая нормальная форма (2NF); третья нормальная форма (3NF); нормальная форма Бойса-Кодда (BCNF); четвертая нормальная форма (4NF); пятая нормальная форма, или нормальная форма проекции-соединения (5NF или PJ/NF).

272 Вложенность нормальных форм

273 Нормальные формы Основные свойства нормальных форм состоят в следующем: каждая следующая нормальная форма в некотором смысле лучше предыдущей нормальной формы; при переходе к следующей нормальной форме свойства предыдущих нормальных форм сохраняются. В основе процесса проектирования лежит метод нормализации, т. е. декомпозиции отношения, находящегося в предыдущей нормальной форме, на два или более отношений, которые удовлетворяют требованиям следующей нормальной формы.

274 Первая нормальная форма Теорема : отношение находится в первой нормальной форме (1NF), тогда и только тогда, когда все значения атрибутов атомарны, и все неключевые атрибуты функционально зависят от ключа.

275 Первая нормальная форма Следствие из теоремы 1: любое нормализованное отношение находится в первой нормальной форме. Если отношение находится только в 1НФ и не находится в более высокой нормальной форме, то ему свойственны все аномалии обработки информации, рассмотренные ранее.

276 Вторая нормальная форма Пусть имеется переменная отношения СЛУЖАЩИЕ_ПРОЕКТЫ_ЗАДАНИЯ {СЛУ_НОМ, СЛУ_УРОВ, СЛУ_ЗАРП, ПРО_НОМ, СЛУ_ЗАДАН}. Новые атрибуты СЛУ_УРОВ и СЛУ_ЗАДАН содержат, соответственно, данные о разряде служащего и о задании, которое выполняет служащий в данном проекте. Будем считать, что разряд служащего определяет размер его заработной платы и что каждый служащий может участвовать в нескольких проектах, но в каждом проекте он выполняет только одно задание. Тогда очевидно, что единственно возможным ключом отношения СЛУЖАЩИЕ_ПРОЕКТЫ_ЗАДАНИЯ является составной атрибут {СЛУ_НОМ, ПРО_НОМ}.

277 Вторая нормальная форма Возможное значение переменной отношения СЛУЖАЩИЕ_ПРОЕКТЫ_ЗАДАНИЯ

278 Вторая нормальная форма Диаграмма множества FD отношения СЛУЖАЩИЕ_ПРОЕКТЫ_ЗАДАНИЯ

279 Аномалии обновления Во множество FD отношения СЛУЖАЩИЕ_ПРОЕКТЫ_ЗАДАНИЯ входит много FD, в которых детерминантом является не возможный ключ отношения (соответствующие стрелки в диаграмме начинаются не с {СЛУ_НОМ, ПРО_НОМ}, т. е. некоторые функциональные зависимости атрибутов от возможного ключа не являются минимальными). Это приводит к так называемым аномалиям обновления. Под аномалиями обновления понимаются трудности, с которыми приходится сталкиваться при выполнении операций добавления кортежей в отношение (INSERT), удаления кортежей (DELETE) и модификации кортежей (UPDATE).

280 Аномалии обновления Обсудим сначала аномалии обновления, вызываемые наличием FD СЛУ_НОМ-> СЛУ_УРОВ (эти аномалии связаны с избыточностью хранения значений атрибутов СЛУ_УРОВ и СЛУ_ЗАРП в каждом кортеже, описывающем задание служащего в некотором проекте).

281 Аномалии обновления Добавление кортежей. Мы не можем дополнить отношение СЛУЖАЩИЕ_ПРОЕКТЫ_ЗАДАНИЯ данными о служащем, который в данное время еще не участвует ни в одном проекте (ПРО_НОМ является частью первичного ключа и не может содержать неопределенных значений). Между тем часто бывает, что сначала служащего принимают на работу, устанавливают его разряд и размер зарплаты, а лишь потом назначают для него проект. Удаление кортежей. Мы не можем сохранить в отношении СЛУЖАЩИЕ_ПРОЕКТЫ_ЗАДАНИЯ данные о служащем, завершившем участие в своем последнем проекте (по той причине, что значение атрибута ПРО_НОМ для этого служащего становится неопределенным). Между тем характерна ситуация, когда между проектами возникают перерывы, не приводящие к увольнению служащих. Модификация кортежей. Чтобы изменить разряд служащего, мы будем вынуждены модифицировать все кортежи с соответствующим значением атрибута СЛУ_НОМ. В противном случае будет нарушена естественная FD СЛУ_НОМ СЛУ_УРОВ (у одного служащего имеется только один разряд).

282 Аномалии обновления Для преодоления этих трудностей можно произвести декомпозицию переменной отношения СЛУЖАЩИЕ_ПРОЕКТЫ_ЗАДАНИЯ на две переменных отношений – СЛУЖ {СЛУ_НОМ, СЛУ_УРОВ, СЛУ_ЗАРП} и СЛУЖ_ПРО_ЗАДАН {СЛУ_НОМ, ПРО_НОМ, СЛУ_ЗАДАН}. На основании теоремы Хита эта декомпозиция является декомпозицией без потерь, поскольку в исходном отношении имелась FD {СЛУ_НОМ, ПРО_НОМ}-> СЛУ_ЗАДАН.

283 Аномалии обновления Значения переменных отношений

284 Аномалии обновления Теперь мы можем легко справиться с операциями обновления. Добавление кортежей. Чтобы сохранить данные о принятом на работу служащем, который еще не участвует ни в каком проекте, достаточно добавить соответствующий кортеж в отношение СЛУЖ. Удаление кортежей. Если кто-то из служащих прекращает работу над проектом, достаточно удалить соответствующий кортеж из отношения СЛУЖ_ПРО_ЗАДАН. При увольнении служащего нужно удалить кортежи с соответствующим значением атрибута СЛУ_НОМ из отношений СЛУЖ и СЛУЖ_ПРО_ЗАДАН. Модификация кортежей. Если у служащего меняется разряд (и, следовательно, размер зарплаты), достаточно модифицировать один кортеж в отношении СЛУЖ.

285 Функциональные зависимости Диаграммы FD в переменных отношений СЛУЖ и СЛУЖ_ПРО_ЗАДАН Диаграмма множества FD отношения СЛУЖАЩИЕ_ПРОЕКТЫ_ЗАДАНИЯ

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

287 Вторая нормальная форма (2NF) Переменные отношений СЛУЖ и СЛУЖ_ПРО_ЗАДАН находятся в 2NF (все неключевые атрибуты отношений минимально зависят от первичных ключей СЛУ_НОМ и {СЛУ_НОМ, ПРО_НОМ} соответственно). отношение СЛУЖАЩИЕ_ПРОЕКТЫ_ЗАДАНИЯ не находится в 2NF (например, FD {СЛУ_НОМ, ПРО_НОМ}-> СЛУ_УРОВ не является минимальной).

288 Вторая нормальная форма (2NF) Любая переменная отношения, находящаяся в 1NF, но не находящаяся в 2NF, может быть приведена к набору переменных отношений, находящихся в 2NF. В результате декомпозиции мы получаем набор проекций исходной переменной отношения, естественное соединение значений которых воспроизводит значение исходной переменной отношения (т. е. это декомпозиция без потерь). Для переменных отношений СЛУЖ и СЛУЖ_ПРО_ЗАДАН исходное отношение СЛУЖАЩИЕ_ПРОЕКТЫ_ЗАДАНИЯ воспроизводится их естественным соединением по общему атрибуту СЛУ_НОМ.

289 Третья нормальная форма В произведенной декомпозиции переменной отношения СЛУЖАЩИЕ_ПРОЕКТЫ_ЗАДАНИЯ множество FD переменной отношения СЛУЖ_ПРО_ЗАДАН предельно просто – в единственной нетривиальной функциональной зависимости детерминантом является возможный ключ. При использовании этой переменной отношения какие-либо аномалии обновления не возникают. Однако переменная отношения СЛУЖ не является такой же совершенной.

290 Аномалии обновления Функциональные зависимости переменной отношения СЛУЖ порождают аномалии обновления. Они вызываются наличием транзитивной FD СЛУ_НОМ-> СЛУ_ЗАРП (через FD СЛУ_НОМ-> СЛУ_УРОВ и СЛУ_УРОВ-> СЛУ_ЗАРП). Эти аномалии связаны с избыточностью хранения значения атрибута СЛУ_ЗАРП в каждом кортеже, характеризующем служащих с одним и тем же разрядом.

291 Для преодоления этих трудностей произведем декомпозицию переменной отношения СЛУЖ на две переменных отношений СЛУЖ1 {СЛУ_НОМ, СЛУ_УРОВ} и УРОВ {СЛУ_УРОВ, СЛУ_ЗАРП}. По теореме Хита, это снова декомпозиция без потерь по причине наличия, например, FD СЛУ_НОМ-> СЛУ_УРОВ.

292 Возможная декомпозиция Диаграммы FD в отношениях СЛУЖ1 и УРОВ Тела отношений СЛУЖ1 и УРОВ

293 Третья нормальная форма Это преобразование обратимо, т. е. любое допустимое значение исходной переменной отношения СЛУЖ является естественным соединением значений отношений СЛУЖ1 и УРОВ. Также можно заметить, что мы избавились от трудностей при выполнении операций обновления. Добавление кортежей. Чтобы сохранить данные о новом разряде, достаточно добавить соответствующий кортеж к отношению УРОВ. Удаление кортежей. При увольнении последнего служащего, обладающего данным разрядом, удаляется соответствующий кортеж из отношения СЛУЖ1, и данные о разряде сохраняются в отношении УРОВ. Модификация кортежей. При изменении размера зарплаты, соответствующей некоторому разряду, изменяется значение атрибута СЛУ_ЗАРП ровно в одном кортеже отношения УРОВ.

294 Третья нормальная форма Трудности, которые мы испытывали, были связаны с наличием транзитивной FD СЛУ_НОМ-> СЛУ_ЗАРП. Наличие этой FD на самом деле означало, что атрибут СЛУ_ЗАРП характеризовал не сущность служащий, а сущность разряд.

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

296 Аномалии обновления Добавление кортежей. Невозможно сохранить данные о новом разряде (и соответствующем ему размере зарплаты), пока не появится служащий с новым разрядом. (Первичный ключ не может содержать неопределенные значения.) Удаление кортежей. При увольнении последнего служащего с данным разрядом мы утратим информацию о наличии такого разряда и соответствующем размере зарплаты. Модификация кортежей. При изменении размера зарплаты, соответствующей некоторому разряду, мы будем вынуждены изменить значение атрибута СЛУ_ЗАРП в кортежах всех служащих, которым назначен этот разряд (иначе не будет выполняться FD СЛУ_УРОВ СЛУ_ЗАРП).

297 Третья нормальная форма Отношения СЛУЖ1 и УРОВ оба находятся в 3NF (все неключевые атрибуты нетранзитивно зависят от первичных ключей СЛУ_НОМ и СЛУ_УРОВ). Отношение СЛУЖ не находится в 3NF (FD СЛУ_НОМ СЛУ_ЗАРП является транзитивной). Любое отношение, находящееся в 2NF, но не находящееся в 3NF, может быть приведено к набору отношений, находящихся в 3NF. Мы получаем набор проекций исходного отношения, естественное соединение которых воспроизводит исходное отношение (т. е. это декомпозиция без потерь). Для отношений СЛУЖ1 и УРОВ исходное отношение СЛУЖ воспроизводится их естественным соединением по общему атрибуту СЛУ_УРОВ.

298 Независимые проекции отношений. Теорема Риссанена Обратите внимание, что для переменной отношения СЛУЖ {СЛУ_НОМ, СЛУ_УРОВ, СЛУ_ЗАРП}, кроме декомпозиции на отношения СЛУЖ1 {СЛУ_НОМ, СЛУ_УРОВ} и УРОВ {СЛУ_УРОВ, СЛУ_ЗАРП}, возможна и декомпозиция на отношения СЛУЖ1 {СЛУ_НОМ, СЛУ_УРОВ} и СЛУЖ_ЗАРП {СЛУ_НОМ, СЛУ_ЗАРП}. Оба отношения, полученные путем второй декомпозиции, находятся в 3NF, и эта декомпозиция также является декомпозицией без потерь. Тем не менее вторая декомпозиция, в отличие от первой, не устраняет проблемы, связанные с обновлением отношения СЛУЖ. Например, по-прежнему невозможно сохранить данные о разряде, которым не обладает ни один служащий. Посмотрим, с чем это связано.

299 Условия независимости проекций отношения Отношения СЛУЖ1 и УРОВ могут обновляться независимо (являются независимыми проекциями), и при этом результат их естественного соединения всегда будет таким, как если бы обновлялось исходное отношение СЛУЖ. Это происходит потому, что FD отношения СЛУЖ трансформировались в индивидуальные ограничения первичного ключа отношений СЛУЖ1 и УРОВ. При второй декомпозиции FD СЛУ_УРОВ->СЛУ_ЗАРП трансформируется в ограничение целостности сразу для двух отношений (такого рода ограничения целостности называются ограничениями базы данных, и их поддержка гораздо более накладна с технической точки зрения). Понятно, что в процессе нормализации декомпозиция отношения на независимые проекции является предпочтительной. Необходимые и достаточные условия независимости проекций отношения обеспечивает теорема Риссанена.

300 Теорема Риссанена Проекции r1 и r2 отношения r являются независимыми тогда и только тогда, когда: каждая FD в отношении r логически следуетиз FD в r1 и r2; общие атрибуты r1 и r2 образуют возможный ключ хотя бы для одного из этих отношений.

301 Теорема Риссанена Мы не будем приводить доказательство этой теоремы, но продемонстрируем ее верность на примере двух показанных выше декомпозиций отношения СЛУЖ. В первой декомпозиции (на проекции СЛУЖ1 и УРОВ) общий атрибут СЛУ_УРОВ является возможным (и первичным) ключом отношения УРОВ, а единственная дополнительная FD отношения СЛУЖ (СЛУ_НОМ-> СЛУ_ЗАРП) логически следует из FD СЛУ_НОМ-> СЛУ_УРОВ и СЛУ_УРОВ->СЛУ_ЗАРП, выполняемых для отношений СЛУЖ1 и УРОВ соответственно. Вторая декомпозиция удовлетворяет второму условию теоремы Риссанена (СЛУ_НОМ является первичным ключом в каждом из отношений СЛУЖ1 и СЛУ_ЗАРП), но FD СЛУ_УРОВ-> СЛУ_ЗАРП не выводится из FD СЛУ_НОМ-> СЛУ_УРОВ и СЛУ_НОМ-> СЛУ_ЗАРП.

302 Нормальная форма Бойса- Кодда До сих пор в определениях нормальных форм мы предполагали, что у декомпозируемого отношения имеется только один возможный ключ. На практике чаще всего бывает именно так. Но имеется один частный случай, который (почти) удовлетворяет требованиям 2NF и 3NF, но, тем не менее, порождает аномалии обновления. Это тот случай, когда у отношения имеется несколько возможных ключей, и некоторые из этих возможных ключей «перекрываются», т. е. содержат общие атрибуты.

303 Нормальная форма Бойса- Кодда Диаграмма FD отношения СЛУЖ_ПРО_ЗАДАН1 Возможное значение переменной отношения СЛУЖ_ПРО_ЗАДАН1

304 Нормальная форма Бойса- Кодда В отношении СЛУЖ_ПРО_ЗАДАН1 служащие уникально идентифицируются как по номерам удостоверений, так и по именам. Следовательно, существуют FD СЛУ_НОМ-> СЛУ_ИМЯ и СЛУ_ИМЯ->СЛУ_НОМ. Но один служащий может участвовать в нескольких проектах, поэтому возможными ключами являются {СЛУ_НОМ, ПРО_НОМ} и {СЛУ_ИМЯ, ПРО_НОМ}.

305 Нормальная форма Бойса- Кодда Очевидно, что, хотя в отношении СЛУЖ_ПРО_ЗАДАН1 все FD неключевых атрибутов от возможных ключей являются минимальными и транзитивные FD отсутствуют, этому отношению свойственны аномалии обновления. Например, в случае изменения имени служащего требуется обновить атрибут СЛУ_ИМЯ во всех кортежах отношения СЛУЖ_ПРО_ЗАДАН1, соответствующих данному служащему. Иначе будет нарушена FD СЛУ_НОМ->СЛУ_ИМЯ, и база данных окажется в несогласованном состоянии.

306 Нормальная форма Бойса- Кодда Причиной отмеченных аномалий является то, что в требованиях 2NF и 3NF не требовалась минимальная функциональная зависимость от первичного ключа атрибутов, являющихся компонентами других возможных ключей. Проблему решает нормальная форма, которую исторически принято называть нормальной формой Бойса- Кодда и которая является уточнением 3NF в случае наличия нескольких перекрывающихся возможных ключей. Переменная отношения находится в нормальной форме Бойса-Кодда (BCNF) в том и только в том случае, когда любая выполняемая для этой переменной отношения нетривиальная и минимальная FD имеет в качестве детерминанта некоторый возможный ключ данного отношения.

307 Нормальная форма Бойса- Кодда Переменная отношения СЛУЖ_ПРО_ЗАДАН1 может быть приведена к BCNF путем одной из двух декомпозиций: СЛУЖ_НОМ_ИМЯ {СЛУ_НОМ, СЛУ_ИМЯ} и СЛУЖ_НОМ_ПРО_ЗАДАН {СЛУ_НОМ, ПРО_НОМ, СЛУ_ЗАДАН} с множеством FD и значениями, показанными на, и СЛУЖ_НОМ_ИМЯ {СЛУ_НОМ, СЛУ_ИМЯ} и СЛУЖ_ИМЯ_ПРО_ЗАДАН {СЛУ_ИМЯ, ПРО_НОМ, СЛУ_ЗАДАН} (FD и значения результирующих переменных отношений выглядят аналогично). Очевидно, что каждая из декомпозиций устраняет трудности, связанные с обновлением отношения СЛУЖ_ПРО_ЗАДАН1.

308 Нормальная форма Бойса- Кодда

309 Нормальные формы В этой лекции мы обсудили три начальные нормальные формы отношений – вторую и третью нормальные формы и нормальную форму Бойса-Кодда, – которые производятся путем декомпозиции без потерь исходного отношения на две проекции, где отсутствуют аномалии изменений, существовавшие в исходном отношении по причине наличия функциональных зависимостей с нежелательными свойствами.

310 Нормальные формы Нормализация схемы базы данных способствует более эффективному выполнению системой управления базами данных операций обновления базы данных, поскольку сокращается число проверок и вспомогательных действий, поддерживающих целостность базы данных. При проектировании реляционной базы данных почти всегда добиваются второй нормальной формы всех входящих в базу данных отношений. В часто обновляемых базах данных обычно стараются обеспечить третью нормальную форму отношений. На нормальную форму Бойса-Кодда внимание обращают гораздо реже, поскольку на практике ситуации, в которых у отношения имеется несколько составных перекрывающихся возможных ключей, встречаются нечасто

311 PERL

312 История PERL Первая версия программы PERL была написана Лэрри Уоллом в 1986 году, когда он являлся системным администратором одного проекта UNIX, связанного с созданием многоуровневой безопасной сети, объединявшей несколько компьютеров, разнесенных на большие расстояния. Работа была выполнена, но потребовалось создание отчетов на основе большого числа файлов с многочисленными перекрестными ссылками между ними. Первоначально Ларри предполагал использовать для этих целей фильтр awk, но оказалось, что последний не мог управлять открытием и закрытием большого числа файлов на основе содержащейся в них же самих информации о расположении файлов. Первая версия языка не содержала многих возможностей, которые можно найти в последней версии Perl. В дальнейшем сам Ларри Уолл позаимствовал у Генри Спенсера (Henry Spencer) пакет для работы с регулярными выражениями и модифицировал его для языка Perl. Другие функциональные возможности были разработаны не только Ларри Уоллом, но и его друзьями и коллегами, и включены в состав языка.

313 PERL Perl это интерпретируемый язык, оптимизированный для просмотра содержимого текстовых файлов, выделения из них информации и генерирования отчетов на основе этой информации. Он обладает большим набором преимуществ как язык сценариев общего назначения, которые проявляются через его характерные черты и возможности. Первым в цепочке достоинств языка Perl мы назовем его интерпретируемость. Разработка решений с помощью компилируемых языков программирования потребовала бы на много больше времени, чем использование одного интерпретируемого: ведь цикл разработки программ на таком языке короче и проще, чем на компилируемом. Мы постепенно создаем программу, добавляя необходимые операторы, и сразу же получаем результаты, когда она завершена: интерпретатор perl постепенно компилирует все операторы во внутренний байт-код и программа готова к выполнению, как только в ней поставлена последняя точка (точнее точка с запятой, завершающая последний оператор). Для небольших по объему программ это достаточное преимущество, так как отладка занимает много времени.

314 PERL Практическая направленность, т. е. он создавался из практических соображений решения задач администрирования и разработки приложений для UNIX, а это означает, что он обладает следующими важными свойствами: полнотой; простотой использования; эффективностью. Под полнотой Perl понимается его способность решать все возникающие в системе UNIX в связи с ее администрированием задачи.

315 PERL Для работы с базами данных можно самому написать соответствующее приложение на языке С, а можно воспользоваться свободно распространяемыми модулями дополнительных расширений возможностей Perl, включающих работу с многочисленными популярными системами управления базами данных. Способность Perl работать с сокетами TCP/IP сделала его популярным для реализации информационных систем взаимодействия с сетевыми серверами любых типов, использующих сокеты в качестве механизма обмена информацией. Именно эта возможность в сочетании с использованием Perl для создания CGI-сценариев послужила широкому распространению языка на других многочисленных платформах.

316 HTML Простейший html-документ выглядит следующим образом: Название Тело документа

317 HTML Test Form Введите Ваше имя: Введите Ваш возраст: Введите занимаемую Вами должность:

318 Pl скрипт #!/usr/local/perl/bin/perl # Чтение и преобразование данных с формы read(STDIN, $buffer, = split(/&/, $buffer); foreach $pair { ($name, $value) = split(/=/, $pair); $value =~ tr/+/ /; $value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg; $value =~ s/ //g; $input{$name} = $value; } # Сохранение в файл #$targetfile = "names.html"; #

319 Средства разработки серверных приложений В каждой форме должна присутствовать одна кнопка Submit, при нажатии которой формируется http-запрос, включающий результаты заполнения формы, и этот запрос направляется на вход приложения, указанного в параметре action. Первым механизмом, обеспечивающим взаимодействие клиента с серверными приложениями, стал CGI (Common Gateway Interface, общий шлюзовой интерфейс). В ответ на действия пользователя, используя CGI, Web-сервер вызывает внешнюю программу (CGI-приложение) и передает ей информацию, полученную от клиента (например, переданную Web- браузером). Далее CGI-приложение обрабатывает полученную информацию, и результаты ее работы передаются клиенту.

320 Средства разработки серверных приложений Пользователь заполняет экранную форму, описанную в html-файле с помощью тега, и нажимает на кнопку "Submit". Возможен также запрос при непосредственном использовании адреса CGI-приложения - указывая его в строке Location браузера: в тэге ; с помощью средств включения сервера (SSI) и т. д.

321 Средства разработки серверных приложений На основе информации из формы б раузер формирует HTTP-запрос и отправляет его серверу. Информация приводится к виду param1=value1¶m2=value2...¶mN =valueN где parami - имя соответствующего поля ввода, valuei - введенное в него значение.

322 Средства разработки серверных приложений Символы, за исключением букв латинског о алфавита, цифр, символа подчеркивания, дефиса и точки при этом передаются в закодированном виде %XX, где XX -- шестнадцатеричное представление данного символа. Символ пробела может быть заменен символом "+". Если указано, что при передаче должен использоваться метод GET, эта строка пере дается непосредственно в URL: bin/script.cgi?param1=value1¶m2=value2

323 Средства разработки серверных приложений При использовании метода POST через заголовок передается информация о типе содержимого запроса (для форм это, как правило, application/x-www-form-urlencoded), а также длина строки. Сама строка в этом случае передается непосредственно в теле запроса. В заголовках запроса также передается значительное количест во вспомогательной информации: тип браузера, адрес страницы, с которой был произведен запрос, и т. д. Вся эта информация передается в HTTP-заголовках, имеющих вид "Имя: значение". Отделяются друг от друга заголовки с помощью символа новой строки, завершается их список еще одним символов новой строки.

324 Средства разработки серверных приложений CGI GETPOST STDINQUERY_STRING

325 Средства разработки серверных приложений Одновременно передается и служебная информация: REQUEST_METHOD=GET QUERY_STRING= CONTENT_LENGTH= CONTENT_TYPE= GATEWAY_INTERFACE=CGI/1.1 REMOTE_ADDR= REMOTE_HOST= SCRIPT_NAME=/cgi-bin/var.cgi SCRIPT_FILENAME=d:/usr/cgi-bin/var.cgi SERVER_NAME=localhost SERVER_PORT=80 SERVER_PROTOCOL=HTTP/1.1 SERVER_SOFTWARE=Apache/1.3.9 (Win32) HTTP_ACCEPT=image/gif, image/x-xbitmap, image/jpeg, image/pjpeg, application/vnd.ms-excel, application/x-shockwave-flash, application/vnd.ms-powerpoint, application/msword, */* HTTP_USER_AGENT=Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0) HTTP_HOST=localhost

326 Средства разработки серверных приложений Обработав информацию, программа, как правило, либо переадресует браузер на некоторый существующий документ с помощью http-заголовка Location, либо формирует виртуальный документ, посылая информацию на стандартный вывод (stdout). Телу документа предшествуют HTTP- заголовки, описывающие тип возвращаемых данных, управляющие кэшированием, работой с cookies и т. д. Все это передается серверу. Сервер пересылает ответ CGI- приложения браузеру, дополняя их при необходимости кодом возврата и вспомогательными заголовками. При этом используется один из двух способов -- перенаправление броузера на новый адрес с помощью http-заголовка Location, либо формирование виртуального документа. В последнем случае значение, переданное в заголовке Content-type, используется броузером для интерпретации идущей следом инфор мации -- например, text/html для виртуальных html-документов.

327 Средства разработки серверных приложений Браузер, основываясь на заголовках HTTP, интерпретирует ответ CGI-приложения и выводит его для просмотра пользователем. Реализовать CGI-приложение можно на любом языке, способном генерировать код для серверной платформы или для которого доступен интерпретатор. Так, простейшее CGI-приложение может быть реализовано на языке пакетных файлов DOS, на Delphi, С/С++, Tcl, Visual Basic, AppleScript, FoxPro, Perl и т. д. Основные недостатки классического CGI -- каждое взаимодействие клиента с сервером, во-первых, является независимым от предыдущих и последующих взаимодействий, во-вторых, приводит к запуску на сервере отдельного процесса. Первый недостаток является тяжелым наследием HTTP-протокола, не имеющего гарантированно работающих средств идентификации клиентов, и все существующие решения способны лишь слегка сгладить этот недостаток. Борьба со вторым недостатком идет гораздо успешнее, и вполне приемлемые решения существуют для обеих наиболее распространенных серверных платформ.

328 Средства разработки серверных приложений Для серверов, основанных на Unix- платформах, одним из самых популярных решений является использование mod_perl модуля, интегрирующего в web-сервер Apache интерпретатор языка Perl. Это позволяет резко уменьшить нагрузку на сервер при запуске CGI-приложений, написанных на Perl. mod_perl позволяет разработчику писать на Perl полноценные подключаемые модули Apache.

329 Введение в PERL PERL (Practical Extraction and Report Language практический язык извлечений и отчетов), применяемом для обработки потоков информации. Изначально предполагалось, что он будет использоваться в ОС Unix, но в дальнейшем Perl стали переносить на другие платформы, и сейчас он существует в самых разных версиях для Unix, Windows, MS-DOS, OS/2, MacOS, Amiga, Atari ST, VMS, Plan 9 и др.

330 Введение в PERL Язык используется в качестве средства выполнения программ со стороны сервера. Его синтаксис унаследован в первую очередь от С, в него добавлены расширенные средства для работы со строками, регулярными выра жениями, ассоциативными массивами и т. д. Это интерпретируемый язык, изначально созданный для Unix- систем, сейчас его интерпретаторы доступны для большинства популярных архитектур, что делает особенно легким перенос приложений. Было бы неверно говорить о Perl, как исключительно о средстве разработки CGI. Встроенные в язык возможности, великолепная переносимость, огромное количество существующих бибилиотек, делают его исключительно удобным средством для системного администрирования, сетевого программирования, обработки текстов и т.п.

331 Введение в PERL П рограмма представляет собой файл, содержащий набор Perl операторов и начинающийся со строки вида #!/usr/local/perl/bin/Perl.exe указывающей путь до интерпретатора Perl.

332 Переменные PERL Переменные Скалярные $ Списковые Хэши %

333 Операции ОписаниеПример + - * / %Арифметическиеprint 2*7+4/(8%3); print int(127/15); #целая часть **Возведение в степень print 2**16; ++ --Инкремент- декремент $i++; & (И) | (ИЛИ) ^ (ИСКЛ) ~ (ИНВЕРТ) > (СДВИГ) Побитовые$x=3; $y=4; print $x|$y; print $x&$y;

334 Операции ОписаниеПример == !=() = Числовые операции сравнения if($x==9){print "Ok!";} Eq(=); ne(); lt( ); le( ); cmp( ) строковые операции сравнения if($game eq 'doom'){print "You are doomer!\n";} || (OR); &&(AND); !(NOT) Логическиеif(($x==9)||($game eq 'doom')){print "hello you!\n";} ?:Условный оператор Этот оператор работает так же как и в С. Если выражение перед '?' истинно то выполняется аргумент перед ':' - иначе после ':'. $i = 1; $i > 1 ? print "больше" : print "меньше"; Результат: меньше 1 ? print "больше" : print "меньше"; Результат: меньше">

335 Операции ОписаниеПример,Последователь ное вычисление $x=10,$y=20;.Конкатенация$x=' v.ru'; xПовторение$x='1234'x5; #$x=' ' =~Сопоставление с образцом if($url=~/http/){print "HTTP";} !~То же но с отрицанием if($url!~/http/){print "No HTTP";} = += -= *= /= %= **= |= &= ^= ~= >=.= x= Присваивание$x+=$y; =.= x= Присваивание$x+=$y;">

336 Строки могут быть в двойных и одинарных кавычках, разница между ними состоит в том,что в одинарных не осуществляется подстановка переменных, а в двойных осуществляется, Например: $x='qwerty';print 'my var is $x'; #выведет my var is $x print "my var is $x"; #выведет my var is qwerty Списки: Спискочные переменные начинаются с символа конструируются следующим образом

337 Массивы Также можно список использовать как lvalue: st;

338 Можно обращаться к нескольким,выбраным элементам массива(срезу массива): 400); местами элементы Обратится к скалярному значению -элементу массива можно $имя_массива[индекс], сдесь обратите внимание на знак '$'- мы ведь обращаемся к скаляру-элементу.

339 Хеш Теперь немного о хешах: хеш это такой массив который состоит из пар ключ-значение, весь хеш обозначается %хеш,к отдельным элементам доступ $хеш{скалярное выражение} конструируется хеш так: $my_hash{1}="doom";$my_hash{'quake'}=" m";$my_hash{1+2}=100; Хеш может быть также сконструирован из массива с четным числом элементов где пары превращаются в ключ-значение %hash=(1,20,2,100);#аналогично $hash{1}=20;$hash{2}=100; удаление из хеша -операция delete: delete $hash{1}; есть функции выдающие ключи и значения соответственно. %hash;

340 Основные части Perl скрипта В общем случае любой Perl скрипт состоит из четырех ключевых частей: 1. Настройка. Первая часть скрипта обязательно запускает интерпретатор и устанавливает переменный, используемые в теле скрипта. Для запуска интерпретатора необходимо знать правильный путь к программе. 2. Чтение входных данных. Эта часть "считывает" и сохраняет в переменных входные данные в удобной для обработки форме. Эта часть обычно неизменна во всех скриптах. 3. Обработка входных данных. Эта часть соответствующим образом обрабатывает введенные данные. Она может быть простой (около 5 строк) или очень сложной (более 1000 строк) в зависимости от выполняемой задачи. 4. Вывод результатов. Пользователь обычно ожидает какого-либо ответа на свои действия. Эта часть достаточно проста в реализации.

341 Работа с базами данных Для работы с базами данных в Perl используется стандартный интерфейс программирования DBI, обеспечивающий доступ к большинству существующих СУБД с помощью подключаемых драйверов. Схемы подключения к разли чным СУБД (точнее, правила формирования имени источника данных) могут незначительно различаться, мы рассмотрим работу с использованием mySQL. В первую очередь необходимо подключить модуль DBI: use DBI;

342 Подключение Далее подключаемся к базе данных: my $dbh = DBI->connect('DBI:mysql:hostname:base:port', 'user, 'password, { RaiseError => 1, AutoCommit => 1}); Здесь $dbh -- дескриптор базы данных, используемый в дальнейшей работе, DBI: mysql:hostname:base:port -- имя источника данных, включающее имя драйвера, имя хоста, базы, к которой мы подключаемся, и номер порта, на который настроен sql-сервер, user/password -- имя и пароль пользователя, имеющего доступ к базе, в последнем параметре передаются различные флаги. По завершении работы желательно закрыть дескриптор: $dbh->disconnect();

343 Выполнение запросов Возможно использование двух способов работы с базой. В случае, если нам нужно только передать информацию в базу, используется 1.метод do, параметром которого является строка SQL-запроса: $dbh->do("insert into mytable values (1,1)"); Если же необходимо получить информацию из базы, используется следующая процедура: Получаем дескриптор команды с помощью метода prepare: my $sth = $dbh->prepare ("select * from mytable where field1>1"); 2.Выполняем команду: $sth->execute(); 3.Использование переменных $sth = $dbh->prepare ("select * from mytable where field1>?"); $sth->execute(1); prepare ("select * from mytable where field1>1"); 2.Выполняем команду: $sth->execute(); 3.Использование переменных $sth = $dbh->prepare ("select * from mytable where field1>?"); $sth->execute(1);">

344 Получение данных 3.Получаем данные. Используется один из четырех методов: fetchrow_array fetchrow_hash fetchrow_arrayref fetchrow_hashref Методы возвращают соответственно массив, хэш, ссылку на массив, ссылку на хэш, в которых хранятся значения полей текущей записи.

345 print " \n"; # заполняем список таблиц $sth = $dbh->prepare("show tables"); $sth->execute; while = $sth->fetchrow_array()) { print " $line[0] \n"; } print " $sth->finish; prepare("show tables"); $sth->execute; while (@line = $sth->fetchrow_array()) { print " $line[0] \n"; } print " $sth->finish;">

346 Функции DBI $scalar = $sth- >affected_rows; Сколько записей вставлено или изменено. $scalar = $sth- >info; Статистика о предыдущих запросах ALTER TABLE или LOAD DATA FROM INFILE. $arrref = $sth- >is_blob; Массив битов, специфицирующих является ли данное поле BLOB. $arrref = $sth- >is_not_null; Массив битов, специфицирующих является ли данное поле NULL. $arrref = $sth- >is_pri_key; Массив битов, специфицирующих является ли данное поле первичным ключом. $arrref = $sth- >is_num; Массив битов, специфицирующих является ли данное поле числом.

347 Функции DBI $scalar = $sth- >insert_id; Значение, присвоенное столбцу с помощью AUTO_INCREMENT последним INSERT. $arrref = $sth- >length; Массив длин всех полей в байтах. $arrref = $sth- >name; Имена всех столбцов. $scalar = $sth- >numrows; Количество возвращаемых записей. $scalar = $sth- >numfields; Количество возвращаемых полей. $arrref = $sth- >table; Имена каждого столбца в таблице. $arrref = $sth- >type; Тип каждого столбца, определен в mysql.h. Доступен с помощью &Mysql::CHAR_TYPE, &Mysql::INT_TYPE, &Mysql::REAL_TYPE

348

349 Хранилища данных. Анализ данных Модуль 1: Хранилища данных. Microsoft OLAP 2005

350 Хранилища данных. Анализ данных Обзор История вопроса Проблема единого взгляда на хранение, доступ, обработку и анализ данных On-Line Analytical Processing (OLAP) – оперативный анализ данных Сходство и отличия OLTP и OLAP систем Смена реляционной парадигмы на иерархическую Унифицированная модель данных. Элементы, атрибуты, иерархии.

351 Хранилища данных. Анализ данных Обзор Динамика модели данных - время - структуры данных Навигационный подход Индивидуальность наблюдателя Многомерные агрегированные данные Аппаратные возможности Стоимостные характеристики (TOC)

352 Хранилища данных. Анализ данных Тема 1. Что такое OLAP? первая работа - Кен Айверсон (Ken Iverson) «Язык программирования» (A Programming Language, APL) первый программный продукт для многомерного анализа данных Express Статья Е.Ф.Кодда: - 12 правил OLAP (1993) - дополнительные правила OLAP (1995) [1] Codd, E. F., Codd, S. B., Salley, C. T.: Providing OLAP (On- Line Analytical Processing) to user-analysts: An IT mandate. Technical report, 1993.

353 OLAP Первое четкое определение OLAP (On-line Analytical Processing) предложено в 1993 году Е.Ф.Коддом (E.F.Codd) в статье, опубликованной при поддержке Arbor Software (теперь - Hyperion Software). Статья включала 12 правил, которые сейчас уже стали широко известными и описаны на сайте любого поставщика OLAP приложений. Позже, в 1995 году, к ним были добавлены еще шесть менее известных правил, все они были разделены на четыре группы и названы "характеристиками" (features). Вот эти правила, дающие определение OLAP приложения с комментариями Найджела Пендса (Nigel Pendse), одного из создателей сайта OLAP Report. Хранилища данных. Анализ данных

354 Основной способ логического представления данных – МНОГОМЕРНЫЕ КУБЫ (OLAP – кубы)

355 OLAP и OLTP системы OLTP – оперативная транзакционная обработка данных OLAP – оперативная аналитическая обработка данных

356 Характеристики OLTP системы Большой объем информации Часто различные БД для разных подразделений Нормализованная схема, отсутствие дублирования информации Интенсивное изменение данных Транзакционный режим работы Транзакции затрагивают небольшой объем данных Обработка текущих данных – мгновенный снимок Много клиентов Малое время отклика – несколько секунд Характеристики OLAP системы Большой объем информации Синхронизированная информация из различных БД с использованием общих классификаторов Ненормализованная схема БД с дубликатами Данные меняются редко, Изменение происходит через пакетную загрузку Выполняются сложные нерегламентированные запросы над большим объемом данных с широким применением группировок и агрегатных функций. Анализ временных зависимостей Небольшое количество работающих пользователей – аналитики и менеджеры Большее время отклика (но все равно приемлемое) – несколько минут

357 Реализация OLAP Типы OLAP - серверов MOLAP (Multidimensional OLAP) - и детальные данные, и агрегаты хранятся в многомерной БД. ROLAP (Relational OLAP) - детальные данные храняться в реляционной БД; агрегаты хранятся в той же БД в специально созданных служебных таблицах. HOLAP (Hybrid OLAP) - детальные данные храняться в реляционной БД, а агрегаты хранятся в многомерной БД.

358 Правила Кодда для реляционных БД 1. Правило информации. 2. Правило гарантированного доступа. 3. Правило поддержки недействительных значений. 4. Правило динамического каталога, основанного на реляционной модели. 5.Правило исчерпывающего подъязыка данных. 6. Правило обновления представлений. 7. Правило добавления, обновления и удаления. 8. Правило независимости физических данных. 9. Правило независимости логических данных. 10. Правило независимости условий целостности. 11. Правило независимости распространения. 12. Правило единственности.

359 Хранилища данных. Анализ данных Характеристики OLAP Четыре группы характеристик OLAP по Кодду: основные характеристики (многомерность модели данных, интуитивные механизмы манипулирования данными, доступность данных, пакетное извлечение данных, архитектура «клиент–сервер», прозрачность, многопользовательская работа); специальные характеристики (обработка ненормализованных данных, хранение результатов отдельно от исходных данных, выделение отсутствующих данных, обработка отсутствующих значений); характеристики построения отчетов (гибкое построение отчетов, стабильная производительность при построении отчетов, автоматическое регулирование физического уровня); управление размерностью (общая функциональность, неограниченное число измерений и уровней агрегирования, неограниченные операции между данными различных измерений).

360 Основные характеристики 1. Многомерность модели данных. С этим утверждением мало кто спорит, и оно считается основной характеристикой OLAP. Частью этого требования считается возможность построения различных проекций и разрезов модели. 2. Интуитивные механизмы манипулирования данными. Кодд считает, что манипулирование данными должно производится с помощью действий непосредственно в ячейке таблиц, без применения меню или сложных. Можно предположить, что это подразумевает использование операций с мышью, но Кодд этого не утверждает. Многие продукты не выполняют этого правила. С нашей точки зрения, эта характеристика незначительно влияет на качество процесса анализа данных. Мы считаем, что программа должна предлагать возможность выбора модели работы, т.к. не всем пользователям нравится одно и то же. Хранилища данных. Анализ данных

361 Основные характеристики 3. Доступность. OLAP это Посредник. Кодд особенно подчеркивает, что ядро OLAP является программой промежуточного уровня между гетерогенными источниками данных и пользовательским интерфейсом. Большинство продуктов обеспечивают эти функции, но удобство доступа к данным часто оказывается ниже чем это хотелось бы другим поставщикам программ. 4. Пакетное извлечение данных. Это правило требует, чтобы продукты предлагали как собственные базы для хранения анализируемых данных, так и динамический (live) доступ к внешним данным. Мы согласны с Коддом в этом пункте и сожалеем, что лишь немногие OLAP продукты соответствуют ему. Даже те программы, которые предлагают такие функции, редко делают их легкими и достаточно автоматизированными. В результате, Кодд поддерживает многомерное представление данных плюс частичный предварительный обсчет больших многомерных баз данных с прозрачным сквозным доступом к детальной информации. Сегодня это рассматривается как определение гибридного OLAP, которая становится наиболее популярной архитектурой, так что Кодд очень точно увидел основные тенденции в этой области. Хранилища данных. Анализ данных

362 Основные характеристики 5. Архитектура "клиент-сервер". Кодд считает, что не только каждый продукт должен быть клиент-серверным, но и что каждая серверная компонента OLAP продуктов должна быть достаточно интеллектуальной для того, чтобы разные клиенты могли быть подключены с минимальными усилиями и программированием. Это намного более сложный тест, чем простая клиент- серверная архитектура и относительно мало продуктов проходит его. Мы могли бы возразить, что этот тест, возможно, сложнее, чем надо и не стоит диктовать разработчикам архитектуру системы. 6. Прозрачность. Этот тест также сложен, но необходим. Полное соответствие означает, что пользователь, скажем, электронной таблицы может получить полный доступ к средствам, предоставляемым ядром OLAP и может при этом даже не знать о том, откуда получены эти данные. Для того чтобы достичь этого, продукты должны предоставлять динамический доступ к гетерогенным источникам данных и полнофункциональный модуль, встраиваемый в электронную таблицу. Между электронной таблицей и хранилищем данных при этом размещается OLAP сервер. Хранилища данных. Анализ данных

363 Основные характеристики 7. Многопользовательская работа. Кодд определяет, что для того, чтобы считаться стратегическим OLAP инструментом, приложения должны работать не только на чтение и интерпретацию данных, и, соответственно, они должны обеспечивать одновременный доступ (включая и извлечение, и обновление данных), целостность и безопасность. Хранилища данных. Анализ данных

364 Специальные характеристики 8. Обработка ненормализованных данных. Это означает возможность интеграции между ядром OLAP и ненормализованным источником данных. Кодд выделяет то, что при обновлении данных, выполненном в среде OLAP, должна быть возможность изменять ненормализованные данные во внешних системах. 9. Хранение OLAP результатов отдельно от исходных данных. В действительности, это имеет отношение к реализации продукта, а не к его возможностям, но мало кто будет спорить с этим утверждением. По сути, Кобб поддерживает широко принятую систему, в соответствии с которой OLAP приложения должны строить анализ непосредственно на основе данных транзакции и изменения в данных OLAP должны храниться отдельно от данных транзакции. Хранилища данных. Анализ данных

365 Специальные характеристики 10. Выделение отсутствующих данных. Это означает, что отсутствующие данные должны отличаться от нулевого значения. Как правило, все современные OLAP системы поддерживают эту характеристику. 11. Обработка отсутствующих значений. Все отсутствующие значения должны быть проигнорированы при анализе, вне зависимости от их источника. Хранилища данных. Анализ данных

366 Характеристики построения отчетов 12. Гибкое построение отчетов. Различные измерения должны выстраиваться любым способом в соответствии с потребностями пользователя. Большинство продуктов соответствует этому требованию в рамках специальных редакторов отчетов. Хотелось бы, чтобы такие же возможности были доступны и в интерактивных средствах просмотра, но это встречается значительно реже. Это - одна из причин, по которой мы предпочитаем, чтобы функционал анализа и построения отчетов был объединен в одном модуле. 13. Стабильная производительность при построении отчетов. Это означает, что производительность системы при построении отчетов не должна существенно падать при увеличении размерности или величины базы данных. 14. Автоматическое регулирование физического уровня. OLAP система должна автоматически регулировать физическую структуру для адаптации ее к типу и структуре модели. Хранилища данных. Анализ данных

367 Управление размерностью 15. Общая функциональность. Все измерения должны иметь одинаковые возможности в структуре и функциональности. 16. Неограниченное число измерений и уровней агрегирования. Фактически, под неограниченным числом Кодд подразумевает 15-20, т.е. число, заведомо превышающее максимальные потребности аналитика. 17. Неограниченные операции между данными различных измерений. Кодд полагает, что для того, чтобы приложение называлось многомерным, оно должно поддерживать любые вычисления с использованием данных всех измерений. Хранилища данных. Анализ данных

368 OLAP: Тест FASMI FASMI – Fast Analysis of Shared Multidimensional Information Быстрый анализ разделяемой многомерной информации Fast: ответ на запрос в течение 1-20 с Analysis: любой сложный логический и статистический анализ для бизнес– приложений Shared: защищенный многопользовательский доступ Multidimensional: многомерное представление данных включая иерархии Information: большое количество данных и информации

369 Хранилища данных. Анализ данных Категории OLAP анализа Многомерный анализ данных и отчетность Интеллектуальный анализ - Data Mining или Business Intelligence

370 Хранилища данных. Анализ данных OLAP: Решаемые задачи Понимание бизнеса: интегрированный взгляд Поддержка принятия решений Бизнес прогнозы

371 Хранилища данных. Анализ данных Пользователи OLAP систем Руководители и менеджмент Бизнес-аналитики, маркетологи и аналитики по планированию развития Руководители среднего и младшего звена Рядовые сотрудники Сотрудники ИТ служб Другие приложения

372 Хранилища данных. Анализ данных Рынок OLAP решений

373 Хранилища данных. Анализ данных Тема 2: Планирование OLAP проекта Сбор и анализ бизнес требований Сбор и анализ технических требований Выявление ограничений Уточнение модели и процессов

374 Хранилища данных. Анализ данных Модуль 2: Логическая архитектура OLAP-проекта

375 Хранилища данных. Анализ данных Тема 1: Логическая архитектура OLAP Server и объекты Логическая архитектура OLAP основана на унифицированной многомерной модели данных (UDM – Unified Dimensional Model)

376 Хранилища данных. Анализ данных Логическая архитектура: объекты Для определения и изменения UDM модели используется XML DDL описание модели – XML файл FoodMart T08:00:00Z Unprocessed T08:00:00Z 1033 Default

377 Хранилища данных. Анализ данных UDM модель и OLAP Server UDM Server – Microsoft Analysis Services Engine OLAP сервера исполняет DDL описания, написанные на XML/A

378 Хранилища данных. Анализ данных Планирование OLAP куба Изучить данные и бизнес процессы, построить UDM модель Выбрать главные объекты куба - измерения (dimensions) и факты (facts) Факт - числовая мера, домен в таблице фактов по которому выполняется агрегирование в кубе Измерение – формирует ось для куба и связывается по ключу с таблицей фактов Выбрать уровень детализации измерения (grain)

379 Хранилища данных. Анализ данных Схемы связывания Facts и Dimensions в хранилище Нормализованная схема Схема ЗВЕЗДА Схема СНЕЖИНКА Схема КОМБИНИРОВАННАЯ

380 Хранилища данных. Анализ данных Компоненты таблицы фактов Таблицы Dimensions Таблицы Dimensions customer_dimcustomer_dim 201 ALFI Alfreds product_dimproduct_dim Chai fact_Sales таблица Measures customer_key product_key time_key quantity_sales amount_sales Foreign Keys ,789 Уровень детализации ( grain) в таблице фактов fact_Sales определяется самым нижним уровнем детализации, хранимым в каждом измерении 134 1/1/2000 time_dimtime_dim

381 Хранилища данных. Анализ данных Схема ЗВЕЗДАEmployee_DimEmployee_Dim EmployeeKey EmployeeID... EmployeeID... Time_DimTime_Dim TimeKey TheDate... TheDate... Shipper_DimShipper_Dim ShipperKey ShipperID... ShipperID... Customer_DimCustomer_Dim CustomerKey CustomerID... CustomerID... Product_DimProduct_Dim ProductKey ProductID... ProductID... Fact Table Sales_Fact TimeKey EmployeeKey ProductKey CustomerKey ShipperKey TimeKey EmployeeKey ProductKey CustomerKey ShipperKey Sales Amount Unit Sales... Sales Amount Unit Sales...

382 Хранилища данных. Анализ данных Схема СНЕЖИНКА Иерархии определяются с помощью нескольких таблиц размерностей Лучше нормализована чем размерность, заданная на одной таблице

383 Хранилища данных. Анализ данных Схема Родители-дети Сотрудник

384 Хранилища данных. Анализ данных Схема связывания Facts и Dimensions Звезда (Star) – денормализованная схема. Элементы каждого уровня иерархии извлекаются из колонки одной и той же таблицы источника. Количество уровней в иерархии равно количеству столбцов таблицы-источника. Снежинка (Snowflake) – нормализованная схема. Элементы разных уровней иерархии извлекаются из нескольких таблиц, связанных внешними ключами. Субординация (Parent-Child) - разбалансированные деревья. Элементы разных уровней извлекаются из двух столбцов одной таблицы. Таблица консервативна при динамике дерева.

385 Хранилища данных. Анализ данных Тема 2: Проектирование измерений Объект измерение (dimension) Свойства атрибутов измерений Ключи атрибутов Медленно меняющиеся измерения Измерения с пропущенными данными

386 Хранилища данных. Анализ данных Объект измерение (dimension) Customer Атрибут измерения – домен с ограниченным списком значений. Атрибут имеет свойства: Type, Usage, KeyColumn, NameColumn, ValueColumn, OrderBy, OrderByAttributeID, MemberNameUnique, Translations,

387 Хранилища данных. Анализ данных Свойства атрибутов измерений Наличие нескольких видов имён Компромисс: краткость и семантика Удобство внутрисистемных ссылок Индивидуальность наблюдателя

388 Хранилища данных. Анализ данных REGION West CA OR East MA NY REGION West East STATEREGION CAWest ORWest MAEast NYEast Отличия OLAP и Relational Dimensions OLAP Relational

389 Хранилища данных. Анализ данных Измерения и иерархии

390 Хранилища данных. Анализ данных Создание разных иерархий (индивидуализация наблюдателя) Department Dimension Department.Management Region 1 Department.Region Region 2 Department A Department D Department C Department B Manager 1 Department B Department D Department C Department A Manager 2 Two Hierarchies

391 Хранилища данных. Анализ данных Сортировка элементов Для наблюдателя – удобство Для системы – производительность и корректность работы (родственные функции – First Child, Parallel Period...) Сортировка по ключу элемента (по умолчанию для числовых типов) Сортировка по имени элемента (по умолчанию – для символьных типов ) Сортировка по значению свойства элемента (member property).

392 Хранилища данных. Анализ данных Сортировка извне Использование оператора Order({M},F) в программе на языке MDX {M} – множество (мультимножество) элементов F – функция, по значению которой (на элементе) будут упорядочены элементы множества (не обязательно одной координаты)

393 Хранилища данных. Анализ данных Группировка элементов измерения по уровням All Верхний уровень Уровень группировки 0-1 км 1-5 км 5+ км

394 Хранилища данных. Анализ данных Демонстрация: Измерения Гиперкуб AdventureWorks - Демонстрация размерностей Time, Product, Employee

395 Хранилища данных. Анализ данных Лаб.работа: Измерения (dimension) Создать проект Cube_Sales, подключив источник данных - AdventureWorks DB Lab A. Создать измерение Time по схеме звезда. Создать иерархию Time: год-квартал-месяц- день месяца. Упорядочить номера месяцев в году по номеру месяца (иначе месяца сортируются по алфавиту названия месяца с ошибкой) Создать измерение Product по схеме снежинка: ProductCategory-ProductSubcategory- ProductName 1. Создать измерение Employees по схеме Parent- Child 2. Выбрать меру SalesAmount в фактовой таблице InternetSales 3. Построить куб и посмотреть его

396 Хранилища данных. Анализ данных Лабораторные работы Lab B. Создать измерение Time с двумя иерархиями Год-Квартал-Месяц: Time.CalendarYear, Time.Fiscal.Year 'Quarter ' + convert(CHAR, DatePart(quarter,"dbo"."TimeMonth"."Month")) Lab C. Создать измерение Customer_Name с группировкой по двум уровням – Customer_group и Customer. Взять фактовые таблицы Sales_Dollars и Sales_Units, сделать новый куб Customer Group Sales

397 Хранилища данных. Анализ данных Модуль 3: Измерения – расширенная функциональность

398 Хранилища данных. Анализ данных Тема 1: Меняющиеся измерения Динамические измерения (calculated members) Медленно меняющиеся измерения (Slowly Changed Dimensions - SHD) Деформированные измерения (ragged dimensions)

399 Хранилища данных. Анализ данных Динамические элементы (Calculated members) Отсутствуют в источнике, вычисляются при развёртке наблюдателем Располагаются на любом измерении (по умолчанию – функция). Пример - Цена со скидкой, пересчёт физической единицы измерения (тонны-баррели) По другим координатам – средний покупатель, тринадцатый месяц.

400 Хранилища данных. Анализ данных Собственная свёртка элемента Унарные операторы – хранятся в столбце таблицы-источника измерения Пример: План счетов бухгалтерии. Индивидуальное формирование вышележащих значений в дереве путём сложения, вычитания, деления, умножения или игнорирования.

401 Хранилища данных. Анализ данных Операции свертки элемента Операторы (+) (-) (*) (/) (~) применяются к Total Value узла выше Siblings

402 Хранилища данных. Анализ данных Порядок вычисления функций при динамических элементах Формула динамического элемента определяет функцию По одной координате – сумма По другой – частное Порядок вычисления задаётся параметром Solve Order По умолчанию – порядок следования элементов в редакторе куба

403 Хранилища данных. Анализ данных Деформированные измерения (Ragged Dimension) Country State City No States Variable Depth – поставить свойство в скобки Level Property – установить Hide Member If

404 Хранилища данных. Анализ данных Деформации координаты (Ragged Dimension) Деформированная звезда – отсутствие у некоторых элементов вышестоящих родственников. Губерния для Москвы Петербурга. (Hide Member If) Деформация в схеме субординации (Parent-Child). Прямая подчинённость рядовых сотрудников директору. (Skipped Level Column)

405 Хранилища данных. Анализ данных Свойства атрибутов измерений Наличие нескольких видов имён Компромисс: краткость и семантика Удобство внутрисистемных ссылок Индивидуальность наблюдателя

406 Хранилища данных. Анализ данных Связи элементов координаты Rigid – зависимый элемент связан с определяющим элементом жестко и не может поменять родителя Flexible - зависимый элемент связан с определяющим элементом гибко и может поменять родителя. Пример: сотрудник переехал, сменился его адрес, в иерархии География сотрудник будет прикреплен к другому городу.

407 Хранилища данных. Анализ данных Медленно меняющиеся измерения (Slowly Changed Dimensions - SHD) Тип 1 медленно меняющегося измерения Тип 2 медленно меняющегося измерения Тип 3 медленно меняющегося измерения Быстро меняющееся измерение

408 Хранилища данных. Анализ данных Ключи атрибутов Member Key – используется в системе для ссылок от фактовой таблицы куба к элементу координаты. Натуральное число. Примеры: Identity, GUID. Краткость. Нет семантики Surrogate Key - Прикладной или суррогатный ключ – используется извне куба (наблюдателем и/или программой) для поддержки бизнес логики MemberKey и Surrogate Key могут не совпадать, если элементы измерения меняются со временем (для медленно меняющихся измерений)

409 Хранилища данных. Анализ данных Демонстрация: Медленно меняющиеся измерения Гиперкуб AdventureWorks

410 Хранилища данных. Анализ данных Виртуальные измерения Не занимают места Не увеличивают время обработки куба Не снижают производительность в запросах, в которых не упоминаются

411 Хранилища данных. Анализ данных Обычная координата (Regular) Свойство элемента (Member Property) Свойство элемента базовой координаты должно быть задано до формирования виртуальной координаты База для виртуальных координат

412 Хранилища данных. Анализ данных Координата Time Системная координата на основе поля типа Datetime (smalldatetime) источника Имеет встроеннные возможности – типовые виды иерархии (минута – час – день – месяц- квартал – год) Подвижное начало года (финансовый год) Набор ориентированных функций для бизнес-аналитики (число месяцев или дней от начала года или квартала и т.д.)

413 Хранилища данных. Анализ данных Дополнительные координаты для времени Требования наблюдателей Одна иерархическая координата : уровни Год, Квартал, Месяц, Число Дополнительная иерархическая координата – уровни Час, Минута Ортогональные (независимые) координаты Год, Месяц, Число, Час

414 Хранилища данных. Анализ данных Нарушение иерархичности Несворачиваемость недели в месяцы- кварталы-годы Наличие у элемента более одного родительского элемента

415 Хранилища данных. Анализ данных Анализ по неделям Дополнительная координата Дополнительная таблица с уровнями Неделя, День. Имена недель – граничные даты (первого и последнего дня в неделе) Собственный календарь, охватывающий весь интересующий период в прошлом, настоящем и будущем Проблема первого дня в неделе в T-SQL и OLAP

416 Хранилища данных. Анализ данных Координаты без таблиц: Связи ссылочные или многие-ко-многим Элементы могут быть извлечены из фактовой таблицы. Гарантия целостности Снижение производительности при переработке куба Реализуются оператором Select Distinct

417 Хранилища данных. Анализ данных Эвристическая координата Строится из эвристической модели (Data Mining) Использует иерархическую классификацию, построенную моделью Data Mining

418 Хранилища данных. Анализ данных Лаб.работа: Сложные измерения Lab A Использовать ранее созданный куб Sales Добавить измерение Geography и сделать в нем иерархию Country-Region-City. Связать Employee с Geography Построить SCD измерение Employee типа 1 – у сотрудника меняется телефон и типа 2 - меняется адрес Построить куб, обновить измерение и посмотреть его

419 Хранилища данных. Анализ данных Лабораторные работы Lab B. Динамические расчеты для финансовых приложений с Custom Rollup Operators. 1. Изучить колонки и их значения из таблицы счетов Account на вкладке Schema - Browse Data. 2. Создать новую размерность Account типа Parent-Child из таблицы Account с колонками Account_id и Parent_id. 3. На вкладке Advanced pane заменить All Level property на No, т.к. All Account имеет только одного ребенка Net Profit. 4. На вкладке Dimension Members pane отсортировать счета в координате Account по целому ключу в помощью Sort Order property. 5. На Browse Data в окне Dimension Tree щелкнуть уровень Account Id level, в конце Advanced Properties установить Unary Operators property и щелкнуть (…) кнопку. Выбрать Enable unary operators check box, установить Use an existing column, click Operator (это имя колонки в таблице Account !) from the Existing column.

420 Хранилища данных. Анализ данных Лабораторные работы Lab C. Виртуальные размерности. 1. Создать виртуальную размерность Gender на основе размерности Customers (сначала создать свойство Gender). 2. Создать новый куб Gender Cube на основе таблицы custfact, в качестве мер выбрать колонки store_sales и store_cost. В этот куб включить реальную координату Customers и виртуальную координату Gender.

421 Хранилища данных. Анализ данных Лабораторные работы Lab D. Виртуальные размерности. 1. Создать виртуальную размерность Quarter на основе существующей размерности (Existing Dimensions) BrandTime в кубе Brand Cube. 2. Обработайте размерность Quarter, а потом куб Brand Cube без designing aggregations. Убедитесь, что после обработки в кубе появилось две временных размерности.

422 Хранилища данных. Анализ данных Модуль 4: Физическая архитектура OLAP проекта

423 Хранилища данных. Анализ данных Обзор Разработка физического уровня куба Секции в реляционных данных Секции в многомерных данных

424 Хранилища данных. Анализ данных Тема 1. Разработка физического уровня куба Секции куба Тип хранения данных (Storage Mode) - ROLAP, MOLAP,HOLAP Упреждающее кеширование (Proactive Caching) Удаленные (remote) секции

425 Хранилища данных. Анализ данных MOLAP 40% agg ROLAP 0% agg MOLAP 20% agg Секции куба (partitions) Partition – секция или патиция – физическая единица хранения, определенная для куба. Секции могут иметь различную моду хранения и физически располагаться на различных серверах. Секции обрабатываются независимо друг от друга, в том числе параллельно при наличии нескольких процессоров

426 Хранилища данных. Анализ данных Режим MOLAP Источник - реляционный Таблицы координат Таблица фактов Источник - многомерная модель Координаты Данные Обобщенные данные

427 Хранилища данных. Анализ данных Режим ROLAP Источник – реляционный Таблицы координат Таблиц фактов Данные Обобщённые данные Источник – многомерная модель Метаданные

428 Хранилища данных. Анализ данных Режим HOLAP Источник – реляционный Таблицы координат Таблица фактов Источник – многомерная модель Обобщённые значения

429 Хранилища данных. Анализ данных Выбор типа хранения данных ТипЗадержкаЗапросыОбработкаРазмер MOLAPБольшаяБыстроБыстрая Средни й ROLAPМалая Медленн о Медленна я Большо й HOLAPСредняяСреднеБыстраяМалый

430 Хранилища данных. Анализ данных Упреждающее кеширование (Proactive Caching) Стандартные сценарии Пользовательские сценарии

431 Хранилища данных. Анализ данных Упреждающее кеширование – стандартные сценарии Real-time ROLAP Real-Time HOLAP Low-Latency MOLAP Medium-Latency MOLAP Automatic MOLAP Scheduled MOLAP MOLAP

432 Хранилища данных. Анализ данных Упреждающее кеширование – пользовательские сценарии Приложение пользователя отправляет команду XML/A NotifyTableChange в Analysis Services

433 Хранилища данных. Анализ данных Демонстрация Выбор типа хранения данных ROLAP, MOLAP, HOLAP в разных секциях куба AdventureWorks