Преимущества использования SQL 2 страница

WHERE номер_группы - '8521');

Кроме того, подзапросы можно использовать во фразе FROM, ко­гда результат подзапроса играет роль таблицы - источника данных для главного запроса, или во фразе SELECT.

Операции реляционной алгебры в SQL

Далее рассмотрим, как в SQL реализуются операции реляционной алгебры.

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

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

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

SELECT с1.ФИО игрок1, с1.разряд разряд_игрока1, с2.ФИО игрок2, с2.разряд разряд_игрока2 FROM студент cl, студент с2;

Операция соединение представляет собой частный случай декарто­во произведения, для которого указывается условие соединения таблиц друг с другом. Достаточно примеров (расписание для группы и в ауди­тории) было приведено для этой операции и с использованием традици­онного синтаксиса, когда условие соединения таблиц указывается во фразе WHERE, и по стандарту ANSI с помощью фразы JOIN...ON.

Операция деление одной (делимое) таблицы на вторую (делитель) возможна, когда все записи второй таблицы содержатся в первой, а ре­зультатом этой операции являются таблица с записями, декартово от­ношение которых с делителем содержится в делимом. Например, вы­вести только то расписание, занятия по которому проходят и в 19-ом корпусе в 507 аудитории, и в КЦ в 313 аудитории. Тогда делимое - это таблица РАСПИСАНИЕ со всеми записями в ней (занятия во всех ауди­ториях всех корпусов), делитель представляет собой таблицу со сле­дующей структурой и содержанием (запрашиваемое условие):

ДЕЛИТЕЛЬ корпус аудитория     КЦ

 

В результате деления получаем необходимое расписание групп по запрашиваемому условию, SQL-запрос содержит вложенные подзапро­сы и выглядит следующим образом:

SELECT *

FROM расписание р WHERE NOT EXISTS

(SELECT * FROM делитель д WHERE NOT EXISTS

(SELECT * FROM расписание p2 WHERE р2.номер_группы = р.номер_группы

AND p2. тип_недели = р.тип_недели AND р2.день_недели =

р.день_недели

AND р2.номер_занятия = р.номер_занятия

AND р2.корпус = д.корпус AND р2.аудитория = д.аудитория));

Для операций объединение, пересечения и разность в SQL сущест­вуют собственные операторы UNION, INTERSECT, MINUS соответст­венно. Так как эти операции выполняются над 2-мя таблицами, они (по­следние) должны иметь одинаковый состав атрибутов, определенных на одинаковых доменах. Рассмотрим использование этих операторов SQL на примерах.

Например, для операции объединения имеем следующие таблицы о преподавателях и о сотрудниках вуза (рис. 3.44):

ПРЕПОДАВАТЕЛЬ ид преподавателя ФИО дата рождения пол адрес СОТРУДНИК ид сотрудника ФИО дата рождения пол адрес

Рис. 3.44. Отношения для объединения

 

В результате объединения этих таблиц, получим таблицу, содер­жащую сведения о преподавателях и сотрудниках:

SELECT *

FROM преподаватель UNION SELECT * FROM сотрудник;

Например, для операции пересечения имеем следующие таблицы о студентах и о жильцах, проживающих в общежитии:

СТУДЕНТ ФИО студента Серия и номер паспорта Пол Дата рождения ЖИЛЕЦ ФИО жильца Серия и номер паспорта Пол Дата рождения

Рис. 3.45. Отношения для пересечения

 

В результате пересечения этих таблиц, получим таблицу, содержа­щую сведения о студентах, проживающих в общежитии:

SELECT * FROM студент INTERSECT SELECT * FROM жилец;

Для операции разность в зависимости от порядка использования таблиц меняется результат выборки. Для примеров используем те же самые таблицы, что и для операции пересечение.

В результате вычитания таблицы студент из таблицы жилец, полу­чаем жильцов, не являющихся студентами:

SELECT * FROM жилец MINUS SELECT * FROM студент;

В результате вычитания таблицы жилец из таблицы студент, полу­чаем студентов, не проживающих в общежитии:

SELECT * FROM студент MINUS SELECT * FROM жилец;

Далее рассмотрим пример создания структуры таблиц с помощью DDL-операции (CREATE) SQL. Например, имеем следующую структу­ру, представленную на рис. 3.46.

Ограничения в структуре таблицы, которым должна удовлетворять каждая запись таблицы, могут быть представлены как на уровне опре­деленного столбца, так и на уровне всей таблицы: уникальность, огра­ничения целостности или условия для значений столбцов).

Для представленной выше структуры обозначим некоторые огра­ничения. В таблице студент ограничения будут следующими: —1 номер зачетки уникален у каждого студента;

—1 рассматриваем студентов, зачислившихся не моложе 14 лет и позд­нее 1950 г.;

—1 значения для пола студентов и типа финансирования вставляются из соответствующих таблиц-классификаторов;

—1 проживание в общежитии хранится как факт (да или нет), так как булевского типа в СУБД Oracle нет, поэтому этот факт хранится в виде 1 (да) или (нет).

СТУДЕНТ номер ФИО дата пол номер дата за­ тип фи­ прожива­ зачет­   рож­   груп­ числе­ нанси­ ние в об­ ки   дения   пы ния рования щежитии ИТОГОВАЯ АТТЕСТАЦИЯ

 

 

номер зачетки	дисциплина	средняя оценка
АТТЕСТАЦИЯ ПО СЕМЕСТРАМ
номер зачетки	дисциплина	семестр	оценка

 

Рис. 3.46. Отношения для создания таблиц БД

Запрос на создание такой структуры таблиц выглядит следующим образом:

CREATE TABLE СТУДЕНТ (

номерзачетки VARCHAR2(10) NOT NULL PRIMARY

KEY,

ФИО VARCHAR2(100) NOT NULL,

дата_рождения DATE NOT NULL,

пол NUMBER(l) NOT NULL REFERENCES

пол (ид),

номер_группы NUMBER(4) NOT NULL REFERENCES

группа (ид),

датазачисления DATE NOT NULL CHECK (да-

та_зачисления>ТО_ОАТЕ('01.01.1950','DD.MM.YYYY')),

типфинансирования NUMBER(l) NOT NULL REFERENCES

типфинансирования (ид), проживание в общежитии NUMBER(l) NOT NULL CHECK (прожи-

вание в общежитии IN (0,1)), CHECK (дата_зачисления>(дата_рождения+14*365)));

CREATE TABLE ИТОТОВАЯАТТЕСТАЦИЯ (

номер_зачетки VARCHAR2(10) NOT NULL REFERENCES сту­

дент (номер_зачетки), дисциплина NUMBER(4) NOT NULL REFERENCES дисцип­

лина (ид), средняяоценка NUMBER(1),

PRIMARY KEY (номер зачетки, дисциплина));

CREATE TABLE АТТЕСТАЦИЯ ПО СЕМЕСТРАМ (номер зачетки VARCHAR2(10) NOT NULL,

дисциплина NUMBER(4) NOT NULL,

семестр NUMBER(2) NOT NULL CHECK (семестр <13),

оценка NUMBER(l) NOT NULL CHECK (оценка

BETWEEN 2 AND 5), PRIMARY KEY (номер_зачетки, дисциплина, семестр), FOREIGN KEY (номер_зачетки, дисциплина)

REFERENCES итоговая_аттестация (номер_зачетки, дисциплина));

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

—1 если средняяоценка по дисциплине проставляется вручную, усло­вие будет:

средняя_оценка > М11\1(аттестация_по_семестрам.оценка) AND < МАХ(аттестация_по_семестрам.оценка));

—1 если средняя_оценка по дисциплине автоматически вычисляется

как среднее арифметическое, условие будет: средняяоценка = К01МО(АУО(аттестация_по_семестрам.оценка)),1);

4. ПРОЦЕСС ПРОЕКТИРОВАНИЯ БД

Эффективность современных информационных систем (ИС) в сфере управления во многом определяется результатом проектирования базы данных, являющейся информационной моделью предметной об­ласти (ПрО). Традиционно процесс проектирования баз данных включа­ет три этапа:

1. Концептуальное проектирование, в результате которого собранные требования к данным ПрО анализируются и оформляются в виде кон­цептуальной информационной модели предметной области (КИМПО);

2. Выбор СУБД, в среде которой физически будет реализована база данных;

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

Последние 2 этапа проектирования, как правило, не представляют сложностей для их выполнения. Так, выбор СУБД зависит от известных факторов, выполнение которых позволит удовлетворить основным тре­бованиям создаваемого проекта ИС. После того, как был принято реше­ние об использовании выбранной СУБД, этап физического проектиро­вания БД благодаря существованию современных CASE-средств полно­стью автоматизирован и не требует непосредственного участия челове­ка. В отличие от последних этапов, концептуальное проектирование традиционно представляет собой сложный процесс, плохо поддающий­ся формализации, отсутствуют готовые рецепты в «приготовлении» КИМПО для сложной ПрО, к числу которых относятся и вузы. Поэтому этот процесс рассматривается как своего рода искусство [11].

Любая методика проектирования схемы БД определяется:

] процедурой - последовательностью действий формирования модели ПрО;

] моделью данных (понятиями и нотацией) - язык, на котором будет описана полученная модель ПрО.

Далее рассмотрим каждый этап процесса проектирования БД в со­ответствии с этим определением.

4.1. Построение концептуальной информационной модели ПрО

4.1.1. Основные подходы для концептуального моделирования

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

4.1.1.1. Декомпозиционный подход

Декомпозиционный подход базируется на системном анализе ПрО и предполагает последовательное, многоуровневое разбиение модели­руемой системы на подсистемы до тех пор, пока не станет очевидным информационное поле составных частей. Это позволяет учесть не толь­ко существующие, но и будущие потребности. Главным недостатком такого подхода является сложность его реализации (необходимо актив­ное участие руководителей различных уровней). Успех и значение де­композиционного метода состоит не только и не столько в том, что сложное целое расчленяется на все менее сложные и, в конечном счете, простые части, а в том, что, будучи соединены надлежащим образом, эти части снова образуют единое целое. В результате последовательная декомпозиция системы на подсистемы приводит к формированию ие­рархической древовидной структуры [12].

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

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

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

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

Методика построения дерева целей и функций [14] предназначена для анализа оргсистем и основана на следующих положениях: —1 использование принципа декомпозиции; —1 использование достаточно общих оснований декомпозиции; —1 использование некоторых дополнительных принципов и ограниче­ний.

Иерархическая многоуровневая структура целей и функций являет­ся прямым результатом использования принципа декомпозиции в про­цессе структуризации целей системы. Разбиение цели i-ro уровня на подцели (i+l)-ro уровня осуществляется путем использования стандарт­ных оснований декомпозиции - стандартных моделей системного ана­лиза, описывающих инвариантные характеристики сложных систем, и определения порядка их использования. Высокий уровень абстрактно­сти этих моделей позволяет использовать их для любых типов систем, причем для описания различных аспектов систем. Однако, чтобы при­менить формальную модель к рассматриваемой системе, необходимо придать ей конкретное содержание, т.е. решить, какие аспекты реальной системы включать как элементы модели избранного типа, а какие нет, считая их несущественными.

К числу дополнительных принципов и ограничений для построения ДЦФ относятся:

—1 принцип полноты - достижение совокупности возникающих при декомпозиции целей должно быть достаточным условием для достиже­ния глобальной цели;

—1 принцип суперпозиции целей - цели одного уровня дерева должны быть относительно независимыми, для того чтобы цель любого уровня являлась аддитивной суммой входящих в нее подцелей; —1 принцип конечности декомпозиции - алгоритм декомпозиции дол­жен завершать свою работу за конечное число шагов, а результатом его работы должно быть конечное дерево.

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

Далее необходимо выяснить, какие стандартные модели как осно­вания декомпозиции предлагает использовать методика построения ДЦФ. Для декомпозиции глобальной цели оргсистемы при соблюдении описанных выше основных положений и принципов используются ба­зовые модели системного анализа, к числу которых относятся также модель черного ящика, модель состава системы и модель структуры (рис. 4.1). Эти виды моделей широко используются для формирования моделей организаций. Например, модель черного ящика используется для описания взаимодействия организации с окружающей средой. Мо­дель состава используются для отображения состава функций организа­ции, целей, задач, персонала и т.д. Модель структуры используется для отображения структуры подчиненности в организации, коммуникаци­
онных взаимодействий и т.д. Указанные виды моделей систем исполь­зуются чаще всего в статическом варианте, но они также могут исполь­зоваться и в динамическом варианте [12]

Модели на уровне

 

Структуры

состава

ВХОДОВ-ВЫХОДОВ

Эле мен­ты сис­темы и сис­тема в це­лом

Вы­хо­ды

Вхо ДЫ

Про цесс

О	о
о	о

О

о	о
о	о

Сис­тема

Л О

 

Модель состава

Модель структуры

Модель черного ящика

 

Рис. 4.1. Базовые модели системного анализа

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

* материальная продукция;

* энергия;

* информация;

* кадры;

* финансы.

Информационные и ресурсные входы любой оргсистемы опреде­ляются ее взаимодействиями с системами целеполагания (рис. 4.2), к числу которых относятся:

• вышестоящие системы - формирующие глобальную цель и основ­ные требования к конечному продукту деятельности системы;

• нижестоящие (подведомственные) системы - возможности которых определяют объем и качество конечного продукта, при этом их объективные потребности должны своевременно удовлетворяться исследуемой системой;

• актуальная среда - объединяющая в себе системы, которые потреб­ляют конечные продукты деятельности исследуемой системы и, следовательно, предъявляют основные требования как к количест­ву, так и к качеству конечных продуктов.

Рис. 4.2. Модель взаимодействия системы с системами окружающей среды

 

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

В совокупности целеполагагающие системы как входы и классифи­катор конечных продуктов как выходы составляют модель черного ящика исследуемой системы.

Далее рассмотрим «внутреннее содержание» (структуру) иссле­дуемой системы. Основными элементами любой оргсистемы, как из­вестно, являются субъект деятельности, объект деятельности и средства деятельности (рис. 4.3). Субъект деятельности (кадры - К) - человек, который осуществляется производство конечных продуктов. Объект деятельности (предметы труда - ПД) - собственно конечные продукты, их составляющие либо промежуточные объекты, являющиеся объекта­ми преобразования. Средства деятельности (СД) - средства, с помощью которых обеспечивается выполнение этапов жизненного цикла конеч­ных продуктов.

	ПД
Ресурсы —►			—► КП
	сд		к

Рис. 4.3. Модель состава оргсистемы

 

Кроме того, необходимо выявить взаимодействие элементов струк­туры оргсистемы по достижению намеченных целей, сделать описание того, как достичь конечных результатов. Так как действия по производ­ству конечных продуктов осуществляется в процессе деятельности, то модель структуры представляет собой модель процесса и опирается на модель жизненного цикла деятельности по производству конечных продуктов, представленную на рис. 4.4. Для получения любого КП сис­темы, необходимо осуществить его жизненный цикл, состоящий из сле­дующих этапов: выявление потребности в КП, обеспечение производст­ва КП и его потребления.

Выявление по­	—►	Производство	—►	Потребление
требности в КП		КП		КП

Рис. 4.4. Модель жизненного цикла конечного продукта

 

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

Ниже рассмотрим применение описанной методики построения ДЦФ для исследуемой системы вуз.

1. Построение ДЦФ начинается с формулирования глобальной цели ис­следуемой системы. Глобальная цель описывает конечный продукт дея­тельности системы в наиболее общей, качественной форме, удобной для последующей декомпозиции. Для любого вуза глобальной целью, по- видимому, является обеспечение подготовки специалистов с высшим образованием, научных кадров в различных областях знаний, проведе­ние фундаментальных и прикладных научных исследований, а также распространения знаний среди населения (повышения квалификации, образовательные курсы).

2. Далее производим построения ДЦФ, производя интерпретацию опи­санных в методике стандартных моделей и соответствующих им осно­ваний декомпозиции.

2.1. Принимая во внимание представленный в методике классифи­катор конечных продуктов, выделяем конечные продукты деятельности вуза как системы:

- Специалисты различной квалификации

- Ученые

- Фундаментальные и прикладные знания

- Изобретения (результаты интеллектуальной деятельности)

Перечисленный состав конечных продуктов выделен по признакам

существенности для вуза в настоящее время. Естественно, что каждый из перечисленных классов может быть подвергнут дальнейшей деком­позиции. Так специалисты могут разделены по уровням квалификации (бакалавры, магистры), по профилям обучения (гуманитарные, техниче­ские), по специальностям и т.д. В такой детализации, однако, нет необ­ходимости при построении ДЦФ для выявления основных типов объек­тов ПрО ввиду отсутствия существенных различий в технологии подго­товки специалистов.

2.2. К системам целеполагания, которые взаимодействуют с вузом как с системой, относятся:

- органы управления ВПО (Федеральное агентство по образования, Министерство образования и науки) - в качестве вышестоящей сис­темы, определяющей основные требования к конечным продуктам вуза;

- предприятия (как «заказчики» конечных продуктов) и собственно население (потребности которого удовлетворяются вузом) - в качест­ве подведомственных систем;

- население (как потребители конечных продуктов) - в качестве ак­туальной среды.

Если на этих первых этапах предлагаемые методикой основания соответствовали требованиям рассматриваемой ПрО, то на последних этапах необходимо применить усилия для интерпретации типовых ос­нований декомпозиции в условиях исследуемой системы.

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

3.1. Уточнение требований целесообразно осуществить в соответствии с этапами жизненного цикла производства КП: выявление потребности в КП - производство КП - потребление (использование) КП.

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

Далее для рассматриваемой ПрО в соответствии с методикой при­водим интерпретацию требований, выявленных для вышестоящей сис­темы, к конечному продукту - специалистам, выпускаемых вузом.

* Выявление потребности - обеспечение возможности получения высшего образования.

о Предмет труда:

* абитуриент, желающий получить высшее образование;

* документ, подтверждающий полученные им необходимые зна­ния (аттестат, диплом).

о Средства труда:

* абитуриент (с помощью своих знаний);

* денежные средства (для платного обучения), о Кадры:

* приемная комиссия.

* Производство КП - подготовка дипломированного специалиста (обучение населения для получения высшего образования).

о Предмет труда:

* студент, получающий высшее образование, о Средства труда:

* преподаватель (с помощью своих знаний);

* оборудование;

* учебная литература; о Кадры:

* преподаватель.

* Потребление КП - выпуск специалиста, о Предмет труда:

* Выпускник-специалист, получивший высшее образование;

* диплом.

о Средства труда:

* студент (с помощью своих знаний), о Кадры:

* государственная аттестационная комиссия.

В результате использования методики построения ДЦФ, руково­дствуясь принципами элементарности и существенности целей и функ­ций, уже при декомпозиции на 3-ем уровне выявляются следующие ти­пы объектов ПрО: