База данных. ЭУМК Базы данных. Пояснительная записка Теоретический раздел Практический раздел Раздел контроля знаний Вспомогательный раздел Пинск
 Скачать 2.33 Mb.
|
|
УЧРЕЖДЕНИЕ ОБРАЗОВАНИЯ ПОЛЕССКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ Н. Н. Коваленко Базы данных Специальность 1-40 05 01 «Информационные системы и технологии (по направлениям)» Пояснительная записка Теоретический раздел Практический раздел Раздел контроля знаний Вспомогательный раздел Пинск ПолесГУ 2015 ПОЯСНИТЕЛЬНАЯ ЗАПИСКА Учебно-методический комплекс по дисциплине ”Базы данных“ представляет собой совокупность материалов, регламентирующих содержание учебной и учебно-методической работы по организации изучения дисциплины. Цель УМК – обеспечить базовый объем учебно-методических материалов, необходимых при изучении учебной дисциплины кафедры, способствующих повышению эффективности организации учебного процесса и самостоятельной работы студентов. Задачи УМК: организовать подготовку специалистов по дисциплине ”Базы данных“ рабочего учебного плана; обеспечить взаимосвязь компонентов УМК по дидактическому и тематическому соответствию всех компонентов УМК с Государственным образовательным стандартом и рабочей учебной программой по дисциплине; создать основы для планирования учебно-методической аудиторной и внеаудиторной работы студентов и преподавателей при изучении дисциплины; обеспечить полное (стопроцентное) оснащение учебного процесса учебно- методическими материалами. УМК включает разделы: теоретический, практический, контроля знаний и вспомогательный. Теоретический раздел УМК содержит материалы для теоретического изучения учебной дисциплины в объеме, установленном типовым учебным планом по специальности (направлению специальности). Практический раздел УМК содержит материалы для проведения лабораторных, практических, семинарских и иных учебных занятий и организовывается в соответствии с типовым учебным планом по специальности (направлению специальности, специализации) и (или) с учебным планом учреждения высшего образования по специальности (направлению специальности, специализации). Раздел контроля знаний УМК содержит материалы текущей и итоговой аттестации, иные материалы, позволяющие определить соответствие результатов учебной деятельности обучающихся требованиям образовательных стандартов высшего образования и учебно-программной документации образовательных программ высшего образования. Вспомогательный раздел УМК содержит элементы учебно-программной документации образовательной программы высшего образования, программно- планирующей документации воспитания, учебно-методической документации, перечень учебных изданий и информационно-аналитических материалов, рекомендуемых для изучения учебной дисциплины. ТЕОРЕТИЧЕСКИЙ РАЗДЕЛ 1. Основы теории реляционных баз данных (БД) 1.1. Модели данных и механизмы реализации БД. Реляционная модель. 1.2. Реляционная алгебра и реляционное исчисление 2. Проектирование реляционных баз данных 2.1. Логическое проектирование модели БД 2.2. Нормализация данных 2.3. Физическая организация БД 2.4. Системы управления базами данных (СУБД) 3. Использование языков запросов. Стандарты, структура, возможности и применение языка SQL 3.1.Языки запросов 3.2. Язык SQL, основные конструкции и работа с данными 3.3. Пользовательские процедуры и функции 3.4. Использование курсоров 4. Обеспечение целостности и эффективности работы с базами данных 4.1. Целостность баз данных. Управление транзакциями 4.2. Повышение производительности баз данных 4.3. Администрирование и управление объектами базы данных 4.4. Методы доступа к базам данных из прикладных программ 4.5. Базы данных в клиент-серверной архитектуре. 1. Основы теории реляционных баз данных (БД) 1.1. Модели данных и механизмы реализации БД. Реляционная модель. 1.2. Реляционная алгебра и реляционное исчисление 1.1. Модели данных и механизмы реализации БД. Реляционная модель. Историю технологии БД принято отсчитывать с начала 1960-х гг., когда появились первые попытки создания специальных программных средств управления базами данных. За прошедшие десятилетия возникали и использовались различные подходы к организации баз данных. Для описания и сравнения некоторых из них мы воспользуемся понятием модели данных, предложенным в 1969 г. Эдгаром Коддом. Кодд ввел это понятие для описания конкретного реляционного подхода к организации БД. Соответственно, он говорил о реляционной модели данных, различным теоретическим и реализационным аспектам которой в основном посвящен этот курс. Однако понятие модели данных оказалось удобным не только для описания реляционного подхода и сравнения реализаций реляционных СУБД, но и для реализационно-независимого представления и сопоставления других подходов к организации баз данных. В модели данных описывается некоторый набор родовых понятий и признаков, которыми должны обладать все конкретные СУБД и управляемые ими базы данных, если они основываются на этой модели. Наличие модели данных позволяет сравнивать конкретные реализации, используя один общий язык. Хотя понятие модели данных было введено Коддом, наиболее распространенная трактовка модели данных, по-видимому, принадлежит Кристоферу Дейту, который воспроизводит ее (с различными уточнениями) применительно к реляционным БД практически во всех своих книгах. Согласно Дейту реляционная модель состоит из трех частей, описывающих разные аспекты реляционного подхода: структурной части, манипуляционной части и целостной части. В структурной части модели данных фиксируются основные логические структуры данных, которые могут применяться на уровне пользователя при организации БД, соответствующих данной модели. Например, в модели данных SQL основным видом структур базы данных являются таблицы, а в объектной модели данных – объекты ранее определенных типов. Манипуляционная часть модели данных содержит спецификацию одного или нескольких языков, предназначенных для написания запросов к БД. Эти языки могут быть абстрактными, не обладающими точно проработанным синтаксисом (что свойственно языками реляционной алгебры и реляционного исчисления, используемым в реляционной модели данных), или законченными производственными языками (как в случае модели данных SQL). Основное назначение манипуляционной части модели данных – обеспечить эталонный «модельный» язык БД, уровень выразительности которого должен поддерживаться в реализациях СУБД, соответствующих данной модели. Наконец, в целостной части модели данных (которая явно выделяется не во всех известных моделях) специфицируются механизмы ограничений целостности, которые обязательно должны поддерживаться во всех реализациях СУБД, соответствующих данной модели. Например, в целостной части реляционной модели данных категорически требуется поддержка ограничения первичного ключа в любой переменной отношения, а аналогичное требование к таблицам в модели данных SQL отсутствует. В этой лекции мы применим понятие модели данных для обзора как подходов, предшествовавших появлению реляционных баз данных, так и подходов, которые возникли позже. Мы не будем касаться особенностей каких- либо конкретных систем; это привело бы к изложению многих технических деталей, которые, хотя и интересны, но находятся несколько в стороне от основной цели курса. Начнем с рассмотрения общих подходов к организации трех типов ранних систем, а именно, систем, основанных на инвертированных списках, иерархических и сетевых систем управления базами данных. Эти системы активно использовались в течение многих лет, задолго до появления работоспособных реляционных СУБД. На самом деле некоторые из ранних систем используются даже в наше время, накоплены громадные базы данных, и одной из актуальных проблем информационных систем является использование этих систем совместно с современными. Все ранние системы не основывались на каких-либо абстрактных моделях. Как мы упоминали, понятие модели данных фактически вошло в обиход специалистов в области БД только вместе с реляционным подходом. Абстрактные представления ранних систем появились позже на основе анализа и выявления общих признаков у различных конкретных систем. В ранних системах доступ к БД производился на уровне записей. Пользователи этих систем осуществляли явную навигацию в БД, используя языки программирования, расширенные функциями СУБД. Интерактивный доступ к БД поддерживался только путем создания соответствующих прикладных программ с собственным интерфейсом. Можно считать, что уровень средств ранних СУБД соотносится с уровнем файловых систем примерно так же, как уровень языка Cobol соотносится с уровнем языков ассемблера. Заметим, что при таком взгляде уровень реляционных систем соответствует уровню языков Ада или APL. Навигационная природа ранних систем и доступ к данным на уровне записей заставляли пользователей самих производить всю оптимизацию доступа к БД, без какой-либо поддержки системы. После появления реляционных систем большинство ранних систем было оснащено «реляционными» интерфейсами. Однако в большинстве случаев это не сделало их по-настоящему реляционными системами, поскольку оставалась возможность манипулировать данными в естественном для них режиме. К числу наиболее известных и типичных представителей систем, в основе которых лежит эта модель данных, относятся СУБД Datacom/DB, выведенная на рынок в конце 1960-х гг. компанией Applied Data Research, Inc. (ADR) и принадлежащая в настоящее время компании Computer Associates, и Adabas (ADAptable DAtabase System), которая была разработана компанией Software AG в 1971 г. и до сих пор является ее основным продуктом. Организация доступа к данным на основе инвертированных таблиц используется практически во всех современных реляционных СУБД, но в этих системах пользователи не имеют непосредственного доступа к инвертированным таблицам (индексам). Кстати, когда мы будем рассматривать внутренние интерфейсы реляционных СУБД, можно будет увидеть, что они очень близки к пользовательским интерфейсам систем, основанных на инвертированных таблицах. База данных в модели инвертированных таблиц похожа на БД в модели SQL, но с тем отличием, что пользователям видны и хранимые таблицы, и пути доступа к ним Строки таблиц упорядочиваются системой в некоторой физической, видимой пользователям последовательности. Физическая упорядоченность строк всех таблиц может определяться и для всей БД (так делается, например, в Datacom/DB). Для каждой таблицы можно определить произвольное число ключей поиска, для которых строятся индексы. Эти индексы автоматически поддерживаются системой, но явно видны пользователям. При манипулирование данными поддерживаются два класса операций: Операции, устанавливающие адрес записи и разбиваемые на два подкласса: прямые поисковые операторы (например, установить адрес первой записи таблицы по некоторому пути доступа); операторы, устанавливающие адрес записи при указании относительной позиции от предыдущей записи по некоторому пути доступа. Операции над адресуемыми записями. Вот типичный набор операций: LOCATE FIRST – найти первую запись таблицы T в физическом порядке; возвращается адрес записи; LOCATE FIRST WITH SEARCH KEY EQUAL – найти первую запись таблицы T с заданным значением ключа поиска k; возвращается адрес записи; LOCATE NEXT – найти первую запись, следующую за записью с заданным адресом в заданном пути доступа; возвращается адрес записи; LOCATE NEXT WITH SEARCH KEY EQUAL – найти cледующую запись таблицы T в порядке пути поиска с заданным значением k; должно быть соответствие между используемым способом сканирования и ключом k; возвращается адрес записи; LOCATE FIRST WITH SEARCH KEY GREATER – найти первую запись таблицы T в порядке ключа поиска k cо значением ключевого поля, большим заданного значения k; возвращается адрес записи; RETRIVE – выбрать запись с указанным адресом; UPDATE – обновить запись с указанным адресом; DELETE – удалить запись с указанным адресом; STORE – включить запись в указанную таблицу; операция генерирует и возвращает адрес записи. Общие правила определения целостности БД отсутствуют. В некоторых системах поддерживаются ограничения уникальности значений некоторых полей, но в основном вся поддержка целостности данных возлагается на прикладную программу. Типичным представителем Иерархическая модель данных (наиболее известным и распространенным) является СУБД IMS (Information Management System) компании IBM. Первая версия системы появилась в 1968 г. Иерархическая БД состоит из упорядоченного набора деревьев; более точно, из упорядоченного набора нескольких экземпляров одного типа дерева. Тип дерева состоит из одного «корневого» типа записи и упорядоченного набора из нуля или более типов поддеревьев (каждое из которых является некоторым типом дерева). Тип дерева в целом представляет собой иерархически организованный набор типов записи. Здесь тип записи Отдел является предком для типов записи Руководитель и Служащие, а Руководитель и Служащие – потомки типа записи Отдел. Смысл полей типов записей в основном должен быть понятен по их именам. Поле Рук_Отдел типа записи Руководитель содержит номер отдела, в котором работает служащий, являющийся данным руководителем (предполагается, что он работает не обязательно в том же отделе, которым руководит). Между типами записи поддерживаются связи (правильнее сказать, типы связей, поскольку реальные связи появляются в экземплярах типа дерева). Все экземпляры данного типа потомка с общим экземпляром типа предка называются близнецами. Для иерархической базы данных определяется полный порядок обхода дерева: сверху-вниз, слева-направо. Заметим, что в терминологии IMS вместо термина запись использовался термин сегмент, а под записью базы данных понималось все дерево сегментов. Примерами типичных операций манипулирования иерархически организованными данными могут быть следующие: найти указанный экземпляр типа дерева БД (например, отдел 310); перейти от одного экземпляра типа дерева к другому; перейти от экземпляра одного типа записи к экземпляру другого типа записи внутри дерева (например, перейти от отдела к первому сотруднику); перейти от одной записи к другой в порядке обхода иерархии; вставить новую запись в указанную позицию; удалить текущую запись. В иерархической модели данных автоматически поддерживается целостность ссылок между предками и потомками. Основное правило: никакой потомок не может существовать без своего родителя. Заметим, что аналогичная поддержка целостности по ссылкам между записями без связи «предок- потомок», не обеспечивается. Примером такой «внешней» ссылки является содержимое поля Рук_Отдел в экземпляре типа записи Руководитель. Типичным представителем систем, основанных на сетевой модели данных, является СУБД IDMS (Integrated Database Management System), разработанная компанией Cullinet Software, Inc. и изначально ориентированная на использования на мейнфреймах компании IBM. Архитектура системы основана на предложениях Data Base Task Group (DBTG) организации CODASYL (COnference on DAta SYstems Languages), которая отвечала за определение языка программирования COBOL. Отчет DBTG был опубликован в 1971 г., и вскоре после этого появилось несколько систем, поддерживающих архитектуру CODASYL, среди которых присутствовала и СУБД IDMS. В настоящее время IDMS принадлежит компании Computer Associates. Сетевой подход к организации данных является расширением иерархического подхода. В иерархических структурах запись-потомок должна иметь в точности одного предка; в сетевой структуре данных у потомка может иметься любое число предков. Сетевая БД состоит из набора записей и набора связей между этими записями, а если говорить более точно, из набора экземпляров каждого типа из заданного в схеме БД набора типов записи и набора экземпляров каждого типа из заданного набора типов связи. Тип связи определяется для двух типов записи: предка и потомка. Экземпляр типа связи состоит из одного экземпляра типа записи предка и упорядоченного набора экземпляров типа записи потомка. Для данного типа связи L с типом записи предка P и типом записи потомка C должны выполняться следующие два условия: каждый экземпляр типа записи P является предком только в одном экземпляре типа связи L; каждый экземпляр типа записи C является потомком не более чем в одном экземпляре типа связи L. На формирование типов связи не накладываются особые ограничения; возможны, например, следующие ситуации: тип записи потомка в одном типе связи L1 может быть типом записи предка в другом типе связи L2 (как в иерархии); данный тип записи P может быть типом записи предка в любом числе типов связи; данный тип записи P может быть типом записи потомка в любом числе типов связи; может существовать любое число типов связи с одним и тем же типом записи предка и одним и тем же типом записи потомка; и если L1 и L2 - два типа связи с одним и тем же типом записи предка P и одним и тем же типом записи потомка C, то правила, по которым образуется родство, в разных связях могут различаться; типы записи X и Y могут быть предком и потомком в одной связи и потомком и предком – в другой; предок и потомок могут быть одного типа записи. Вот примерный набор операций манипулирования данными: найти конкретную запись в наборе однотипных записей (например, служащего с именем Иванов); перейти от предка к первому потомку по некоторой связи (например, к первому служащему отдела 625); перейти к следующему потомку в некоторой связи (например, от Иванова к Сидорову); перейти от потомка к предку по некоторой связи (например, найти отдел, в котором работает Сидоров); создать новую запись; уничтожить запись; модифицировать запись; включить в связь; исключить из связи; переставить в другую связь и т.д. Ограничения целостности Имеется (необязательная) возможность потребовать для конкретного типа связи отсутствие потомков, не участвующих ни в одном экземпляре этого типа связи (как в иерархической модели). 1 Заметим, что перечисляемые ниже характеристики в полной мере относятся и к другим не реляционным подходам к организации баз данных, которые возникли до появления реляционного подхода или почти одновременно с ним. В частности, подобными свойствами обладают системы, основанные на подходах MUMPS (наиболее известной в России является реализация этого подхода в СУБД Cache компании Intersystems) и Pick (этот подход реализован во многих СУБД, в частности, в СУБД UniVerse и UniData семейства U2 компании IBM). Принципы реляционной модели были сформулированы в 1969—1970 годах Э. Ф. Коддом (E. F. Codd). Идеи Кодда были впервые подробно изложены в статье «A Relational Model of Data for Large Shared Data Banks», ставшей классической. Строгое изложение теории реляционных баз данных (реляционной модели данных) в современном понимании можно найти в книге К. Дж. Дейта. «C. J. Date. An Introduction to Database Systems» (« Кристофер Дейт, Введение в системы баз данных»). Общая характеристика реляционной модели данных Согласно Дейту реляционная модель состоит из трех частей, описывающих разные аспекты реляционного подхода: структурной части, манипуляционной части и целостной части. В структурной части модели фиксируется, что единственной структурой данных, используемой в реляционных БД, являются нормализованные n-арное отношения. По сути в предыдущей лекции мы рассматривали именно понятия и свойства структурной составляющей реляционной модели. В целостной части реляционной модели данных фиксируются два базовых требования целостности, которые должны поддерживаться в любой реляционной СУБД: 1. требованием целостности сущностей. Конкретно требование состоит в том, что любой кортеж любого отношения должен быть отличим от любого другого кортежа этого отношения, т.е. другими словами, любое отношение должно обладать первичным ключом. 2. требованием целостности по ссылкам (внешним ключам). Оно является несколько более сложным требованием. Сложные сущности реального мира представляются в реляционной БД в виде нескольких кортежей нескольких отношений. Пример: Надо реализовать сущность ОТДЕЛ с атрибутами: ОТД_НОМЕР (номер отдела), ОТД_КОЛ (количество сотрудников) ОТД_СОТР (набор сотрудников отдела). Для каждого сотрудника надо хранить: СОТР_НОМЕР (номер сотрудника), СОТР_ИМЯ (имя сотрудника) СОТР_ЗАРП (заработная плата сотрудника). При правильном проектировании соответствующей БД в ней появятся два отношения: ОТДЕЛЫ ( ОТД_НОМЕР, – первичный ключ ОТД_КОЛ ) и СОТРУДНИКИ ( СОТР_НОМЕР, - первичный ключ СОТР_ИМЯ, СОТР_ЗАРП, СОТР_ОТД_НОМ ) Атрибут СОТР_ОТД_НОМ появляется в отношении СОТРУДНИКИ не потому, что номер отдела является собственным свойством сотрудника, а лишь для того, чтобы иметь возможность определить полную сущность ОТДЕЛ. Атрибут такого рода называется внешним ключом - его значения однозначно определяет кортеж некоторого другого отношения (т.е. задает значение его первичного ключа). Другими словами, отношение, в котором определен внешний ключ, ссылается на соответствующее отношение, в котором такой же атрибут является первичным ключом. Требование целостности по ссылкам (требование внешнего ключа) состоит в том, что для каждого значения внешнего ключа в ссылающемся отношении, в отношении, на которое ведет ссылка, должен найтись кортеж с таким же значением первичного ключа, либо значение внешнего ключа должно быть неопределенным (т.е. ни на что не указывать). Для нашего примера это означает, что если для сотрудника указан номер отдела, то этот отдел должен существовать. При обновлении ссылающегося отношения (вставке новых кортежей или модификации значения внешнего ключа в существующих кортежах) достаточно следить за тем, чтобы не появлялись некорректные значения внешнего ключа. При удалении кортежа из отношения, на которое ведет ссылка, существуют три подхода: 1. заключается в том, что запрещается производить удаление кортежа, на который существуют ссылки (т.е. сначала нужно либо удалить ссылающиеся кортежи, либо соответствующим образом изменить значения их внешнего ключа). 2. при удалении кортежа, на который имеются ссылки, во всех ссылающихся кортежах значение внешнего ключа автоматически становится неопределенным. 3. (каскадное удаление) состоит в том, что при удалении кортежа из отношения, на которое ведет ссылка, из ссылающегося отношения автоматически удаляются все ссылающиеся кортежи. Значение атрибута СОТР_ОТД_НОМ в любом кортеже отношения СОТРУДНИКИ должно соответствовать значению атрибута ОТД_НОМ в некотором кортеже отношения ОТДЕЛЫ. В развитых реляционных СУБД обычно можно выбрать способ поддержания целостности по ссылкам для каждой отдельной ситуации определения внешнего ключа. Конечно, для принятия такого решения необходимо анализировать требования конкретной прикладной области. В манипуляционной части реляционной модели утверждаются два фундаментальных механизма манипулирования реляционными БД - реляционная алгебра и реляционное исчисление. Первый механизм базируется в основном на классической теории множеств, а второй - на математической логике. Все эти механизмы обладают одним важным свойством: они замкнуты относительно понятия отношения. Это означает, что выражения реляционной алгебры и формулы реляционного исчисления определяются над отношениями и результатом вычисления также являются отношения. Алгебра и исчисление обладают большой выразительной мощностью: очень сложные запросы к базе данных могут быть выражены с помощью одного выражения реляционной алгебры или одной формулы реляционного исчисления. Именно по этой причине именно эти механизмы включены в реляционную модель данных. В реализациях конкретных реляционных СУБД сейчас не используется в чистом виде ни реляционная алгебра, ни реляционное исчисление. Фактическим стандартом доступа к реляционным данным стал язык SQL (Structured Query Language). Язык SQL представляет собой смесь операторов реляционной алгебры и выражений реляционного исчисления, использующий синтаксис, близкий к фразам английского языка и расширенный дополнительными возможностями, отсутствующими в реляционной алгебре и реляционном исчислении. Вообще, язык доступа к данным называется реляционно-полным, если он по выразительной силе не уступает реляционной алгебре (или, что то же самое, реляционному исчислению), т.е. любой оператор реляционной алгебры может быть выражен средствами этого языка. Именно таким и является язык SQL. 1.2. Реляционная алгебра и реляционное исчисление Основная идея реляционной алгебры состоит в том, что коль скоро отношения являются множествами, то средства манипулирования отношениями могут базироваться на традиционных теоретико-множественных операциях, дополненных некоторыми специальными операциями, специфичными для баз данных. Рассмотрим расширенный начальный вариант алгебры, который был предложен Коддом. В этом варианте набор основных алгебраических операций состоит из восьми операций, которые делятся на два класса - теоретико- множественные операции и специальные реляционные операции. В состав теоретико-множественных операций входят операции: объединения отношений; пересечения отношений; взятия разности отношений; прямого произведения отношений. Специальные реляционные операции включают: ограничение отношения; проекцию отношения; соединение отношений; деление отношений. Кроме того, в состав алгебры включается операция присваивания, позволяющая сохранить в базе данных результаты вычисления алгебраических выражений, и операция переименования атрибутов, дающая возможность корректно сформировать заголовок (схему) результирующего отношения. Главное отличие теоретико-множественных операций (за исключением декартова произведения) от всех других, выполняемых СУБД, состоит в том, что каждая таблица должна иметь одинаковое число колонок и каждая пара колонок для любой позиции таблицы должна быть определена с одинаковым типом и масштабом. Общая интерпретация реляционных операций Если не вдаваться в некоторые тонкости, то все операции предложенного выше набора обладают очевидной и простой интерпретацией. При выполнении операции объединения двух отношений производится отношение, включающее все кортежи, входящие хотя бы в одно из отношений. Замечание. Объединение, как и любое отношение, не может содержать одинаковых кортежей. Поэтому, если некоторый кортеж входит и в отношение , и отношение , то в объединение он входит один раз. Пример . Пусть даны два отношения и с информацией о сотрудниках: Табельный номер Фамили я Зарплата 1 Иванов 1000 2 Петров 2000 3 Сидоров 3000 Таблица 1 Отношение A Табельный номер Фамилия Зарплата 1 Иванов 1000 2 Пушник ов 2500 4 Сидоров 3000 Таблица 2 Отношение B Объединение отношений и будет иметь вид: Табельный номер Фамилия Зарплата 1 Иванов 1000 2 Петров 2000 3 Сидоров 3000 2 Пушник ов 2500 4 Сидоров 3000 Таблица 3 Отношение A UNION B Замечание. В объединении отношений и атрибут "Табельный номер" может содержать дубликаты значений. Объединение отношений в SQL: (Select * from a) union (select * from b) Операция пересечения двух отношений производит отношение, включающее все кортежи, входящие в оба отношения. Пример . Пусть даны два отношения и с информацией о сотрудниках: Табельный номер Фамили я Зарплата 1 Иванов 1000 2 Петров 2000 3 Сидоров 3000 Таблица 4 Отношение A Табельный номер Фамилия Зарплата 1 Иванов 1000 2 Пушник ов 2500 4 Сидоров 3000 Таблица 5 Отношение B Для отношений и пересечение имеет вид: Табельный номер Фамили я Зарплата 1 Иванов 1000 Таблица 6 Отношение A INTERSECT B Пересечение отношений в SQL: select a.* from a,b where a.табельный_номер=b. табельный_номер and a.фамилия=b. фамилия and a.зарплата=b. зарплата Отношение, являющееся разностью двух отношений включает все кортежи, входящие в первое отношение, такие, что ни один из них не входит во второе отношение. Пример . Пусть даны два отношения и с информацией о сотрудниках: Табельный номер Фамили я Зарплата 1 Иванов 1000 2 Петров 2000 3 Сидоров 3000 Таблица 7 Отношение A Табельный номер Фамилия Зарплата 1 Иванов 1000 2 Пушник ов 2500 4 Сидоров 3000 Таблица 8 Отношение B Для отношений и вычитание имеет вид: Табельный номер Фамили я Зарплата 2 Петров 2000 3 Сидоров 3000 Таблица 9 Отношение A MINUS B Разность отношений в SQL: Select a.* from a where not exists (select * from B where a.табельный_номер=b. табельный_номер and a.фамилия=b. фамилия and a.зарплата=b. зарплата) При выполнении прямого произведения двух отношений производится отношение, кортежи которого являются конкатенацией (сцеплением) кортежей первого и второго операндов. Пример . Пусть даны два отношения и с информацией о поставщиках и деталях: Номер поставщика Наименование поставщика 1 Иванов 2 Петров 3 Сидоров Таблица 10 Отношение A (Поставщики) Номер детали Наименование детали 1 Болт 2 Гайка 3 Винт Таблица 11 Отношение B (Детали) Декартово произведение отношений и будет иметь вид: Номер поставщик а Наименовани е поставщика Номе р детал и Наименовани е детали 1 Иванов 1 Болт 1 Иванов 2 Гайка 1 Иванов 3 Винт 2 Петров 1 Болт 2 Петров 2 Гайка 2 Петров 3 Винт 3 Сидоров 1 Болт 3 Сидоров 2 Гайка 3 Сидоров 3 Винт Таблица 12 Отношение A TIMES B Произведение отношений в SQL: Select a.*, b.* from a,b Замечание. Сама по себе операция декартового произведения не очень важна, т.к. она не дает никакой новой информации, по сравнению с исходными отношениями. Для реальных запросов эта операция почти никогда не используется. Однако операция декартового произведения важна для выполнения специальных реляционных операций. Как мы увидим ниже, основной смысл включения операции расширенного прямого произведения в состав реляционной алгебры состоит в том, что на ее основе определяется действительно полезная операция соединения. Результатом ограничения отношения по некоторому условию является отношение, включающее кортежи отношения, удовлетворяющее этому условию. Пример . Пусть дано отношение с информацией о сотрудниках: Табельный номер Фамили я Зарплата 1 Иванов 1000 2 Петров 2000 3 Сидоров 3000 Таблица 13 Отношение A Результат выборки будет иметь вид: Табельный номер Фамили я Зарплата 1 Иванов 1000 2 Петров 2000 Таблица 14 Отношение A WHERE Зарплата<3000 Ограничение отношения в SQL: Select * from A where a.Зарплата>3000 При выполнении проекции отношения на заданный набор его атрибутов производится отношение, кортежи которого создаются путем взятия соответствующих значений из кортежей отношения Замечание. Операция проекции дает "вертикальный срез" отношения, в котором удалены все возникшие при таком срезе дубликаты кортежей. Пример . Пусть дано отношение с информацией о поставщиках, включающих наименование и месторасположение: Номер Наименование Город поставщика поставщика поставщика 1 Иванов Уфа 2 Петров Москва 3 Сидоров Москва 4 Сидоров Челябинск Таблица 15 Отношение A (Поставщики) Проекция будет иметь вид: Город поставщика Уфа Москва Челябинск Таблица 16 Отношение A[Город поставщика] Проекция отношения в SQL: Select a.город_поставщика from a group by a. город_поставщика При соединении двух отношений по некоторому условию образуется результирующее отношение, кортежи которого являются конкатенацией кортежей первого и второго отношений и удовлетворяют этому условию. Пример. Пусть имеются отношения , и , хранящие информацию о поставщиках, деталях и поставках соответственно (для удобства введем краткие наименования атрибутов): Номер поставщика PNUM Наименование поставщика PNAME 1 Иванов 2 Петров 3 Сидоров Таблица 17 Отношение P (Поставщики) Номер детали DNUM Наименование детали DNAME 1 Болт 2 Гайка 3 Винт Таблица 18 Отношение D (Детали) Номер поставщик а PNUM Номер детали DNU M Поставляемо е количество VOLUME 1 1 100 1 2 200 1 3 300 2 1 150 2 2 250 3 1 1000 Таблица 19 Отношение PD (Поставки) Ответ на вопрос "какие детали поставляются поставщиками", более просто записывается в виде соединения трех отношений (для удобства просмотра порядок атрибутов изменен, это является допустимым по свойствам отношений): Номер поставщика PNUM Наименование поставщика PNAME Номер детали DNUM Наименование детали DNAME Поставляемое количество VOLUME 1 Иванов 1 Болт 100 1 Иванов 2 Гайка 200 1 Иванов 3 Винт 300 2 Петров 1 Болт 150 2 Петров 2 Гайка 250 3 Сидоров 1 Болт 1000 Таблица 20 Отношение P JOIN PD JOIN D Естественное соединение в SQL: Select p.pnum, p.pname, d.dnum, d.dname, pd.volume From p, d, pd Where p.pnum=pd.pnum and d.dnum=pd.dnum (SELECT p.pnum, p.pname, d.dnum, d.dname, pd.volume FROM p, d, pd WHERE p.pnum=pd.pnum and d.dnum=pd.dnum) – естественное соединение или SELECT p.pnum, p.pname, d.dnum, d.dname, pd.volume FROM p INNER JOIN (d INNER JOIN pd ON d.dnum=pd.dnum) ON d.dnum=pd.dnum A JOIN B = B JOIN A; (коммутативность) A JOIN (B JOIN C) = (A JOIN B) JOIN C; (ассоциативность) (A JOIN B) JOIN C = B JOIN (A JOIN C). Виды соединения: o Полусоединение (Semijoin). Т.е выбираются данные из одной таблицы из двух связанных Примером могло бы быть множество всех продуктов, которые были проданы в течение сентября 2009 года: PRODUCT JR ORDER SELECT P.id, P. наименование FROM PRODUCT P, ORDER O WHERE (O.id = P.id) AND (O. Дата_продажи BETWEEN 01.09.2009 AND 30.09.2009); Или SELECT P.id, P. наименование FROM PRODUCT P INNER JOIN ORDER O ON O.id = P.id WHERE O. Дата_продажи BETWEEN 01.09.2009 AND 30.09.2009; o Внешнее соединение (Outerjoin). Например, вывести все названия товаров и в скольких накладных они встречались, что бы не потерять те товары, которые ни разу не покупали. id наименованиеколичество 11 макароны 100 56 чипсы 50 589 масло 86 759 оливки 100 id Дата_продажиколичество 11 01.09.2009 20 56 01.09.2009 30 589 20.09.2009 50 PRODUCT JR ORDER SELECT P.наименование, COUNT(*) колич_продаж FROM PRODUCT P, ORDER O WHERE P.id = O.id(+); SELECT P.наименование, COUNT(*) колич_продаж FROM PRODUCT P LEFT JOIN ORDER O ON P.id = O.id; o Самосоединение (Selfjoin). Самосоединение является эквисоединением таблицы с самой собой. Это также называется рекурсивным соединением. id наименованиеколичество 11 Макароны 100 56 Чипсы 50 589 Масло 86 759 оливки 100 id Дата_продажиколичество 11 01.09.2009 20 56 01.09.2009 30 589 20.09.2009 50 589 21,09,2009 10 наименование колич_продаж Макароны 1 Чипсы 1 Масло 2 оливки 0 Пример, который выводит список имен всех служащих и назначенных им руководителей. EMPLOYEE SELECT E.ФИО Подчиненный, M.ФИО Руководитель FROM EMPLOYEE E, EMPLOYEE M WHERE E.ID_руководителя = M. ID; o Агрегация (Aggregation). Цель агрегации состоит в том, чтобы предоставить для таблицы статистическую информацию, такую как сумма или среднее множества чисел. Пример скалярной агрегации (просто возвращает единственное значение как вывод), который не имеет какого-либо предложения GROUP BY : SELECT SUM(ЗАРПЛАТА) FROM EMPLOYEE; SELECT COUNT(*) FROM EMPLOYEE WHERE ЗАРПЛАТА=8000; Синтаксис SQL для выполнения функции агрегации всегда включает предложение GROUP BY . В результате некоторая новая таблица: ID ФИО ID_руководителя Зарплата 101 Емельянов 10000 103 Орлова 101 8000 106 Суханова 101 8000 109 Иванов 106 5000 |