Базы данных (стр. 4 )

6.Окончательный результат.

Общее алгебраическое выражение можно записать в следующем виде:

2.3.3.Реляционное исчисление

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

В реляционном исчислении вводится следующий алфавит A:

·  Отдельные константы.

·  Индексные переменные.

·  Переменные выборки.

·  Одноместные (монадические) предикаты.

·  Двуместные предикаты (диадические предикаты или операции сравнения).

·  Логические символы, включая кванторы.

·  Разделители (круглые и квадратные скобки).

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

В реляционном исчислении используются следующие термы:

Терм диапазона (ТД). Одноместный предикат, за которым следует переменная выборки, называется термом диапазона. Терм диапазона , где v – переменная выборки, понимается как некоторое высказывание, заключающееся в том, что переменная выборки v имеет в качестве своего значения кортежи отношения Rj (диапазон отношения). Для определения N – го компонента выборки v вводится понятие операции индексной выборки, которая имеет вид v[N]. N индексная константа, указывающая на атрибут с номером N.

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

Терм объединения (ТО). Пусть l и m - индексные выборки, а a - отдельная константа. Тогда lqm и lqa называются термом диапазона, где q - двухместный предикат.

q = {>, <, =, ¹, ³, £}

В SQL появляются более сложные предикаты Like, Between, In, которые будут рассмотрены в своем месте.

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

1.  Любой ТД или ТО есть ППФ.

2.  Если F – ППФ, то -- ППФ.

3.  Если F, G – ППФ, то (FÚG), (FÙG) – ППФ.

4.  Если F – ППФ, в которой v переменная, то ($v)F, ("v)F – ППФ.

5.  Никакие другие формулы не являются ППФ.

Диапазонной ППФ является, свободная от кванторов ППФ, все термы которой являются ТД.

ППФ, имеющая четко определенные диапазоны для всех своих переменных, называется диапазонно – сепарабельной, и определяется как конъюнкция из форм:

где

1.n³1.

2. - соответствующие диапазонные ППФ по n различным переменным.

3.v или пусто, или это ППФ с тремя свойствами:

а) каждый квантор в v диапазонно связан.

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

в) v не содержит термы диапазона.

Запрос в РИ записывается в виде a - выражений, имеющих вид:

где

1.  w – диапазонно – сепарабельная ППФ.

2.  - термы, причем каждый состоит из переменных выборки или индексных переменных.

3.  Множество переменных выборки из входит в множество переменных в w.

2.3.3.1.Виды запросов РИ

Простой запрос. Найти фамилии всех студентов.

Список аргументов пуст (присутствует только диапазонная ППФ). Данный запрос можно трактовать, как проекцию отношения Student на домен FIOS.

- список цели. - терм диапазона.

Отображение состоит из имени отношения Student и имени атрибута FIOS. Значением являются все фамилии.

Множество различных фамилий из атрибута FIOS отношения Student копируется в рабочее поле w, которое можно рассматривать как унарное отношение с единственным атрибутом FIOS.

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

Выполняется отображение, которое моделирует обычный способ работы с таблицей. В данном случае просматривается столбец SGroup отношения Student и при удовлетворении условий строится множество различных пар <NZach, FIOS>, которые копируются в поле w. К w можно обращаться как к бинарному отношению с атрибутами и FIOS.

Запрос с единственным квантором существования. Найти предметы, которые сдавала группа АП71.

Этот запрос можно перефразировать следующим образом: Найти наименования тех предметов, для которых в отношении Vedom существует тот же код предмета и одновременно, для которых шифр группы равен АП71.

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

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

В этом примере используется естественное правило умолчания для введения квантора существования: если переменной нет в целевом списке, то для нее предполагается квантор существования.

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

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

Запрос с отрицанием. Найти студентов, которые не учатся в группе АП71.

Запрос с единственным квантором общности. Найти предметы, которые не сдавала группа АП71.

Учитывая, что получим:

Мощность реляционного исчисления запросов возрастает, если в нем разрешить использование функций. Здесь используем функцию count, подсчитывающую количество элементов в конечном множестве. Другие функции рассмотрим при описании QBE и SQL.

Запрос с функцией в целевом списке. Определить количество студентов в группе АП71.

Запрос с функцией в квалифицирующем выражении. Найти преподавателей, которые принимали более десяти экзаменов.

Практически все другие запросы можно рассматривать как комбинации этих запросов.

2.3.3.2.Алгоритм редукции

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

Алгоритм

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

Шаг 2. Берется декартово произведение этих диапазонов.

Шаг 3. Выборки, которые не удовлетворяют комбинации термов объединения, удаляются из отношения.

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

Шаг 5. Выполняется проекция в соответствии с целевым списком и тем самым определяется требуемое отношение.

 

2.3.3.3.Реляционное исчисление доменов

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

2.3.4.Описание языка SQL

Язык структурированных запросов SQL (Structured Query Language) представляет собой специализированный набор программных команд, позволяющий пользователю выполнять следующие задачи:

1.  Выбирать данные из одной или нескольких таблиц в одной или нескольких базах данных.

2.  Манипулировать данными, вставляя, удаляя или обновляя записи.

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

4.  Создавать, модифицировать или удалять таблицы в БД.

5.  Создавать или удалять индексы в таблицах.

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

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

Обычно SQL – оператор возвращает затребованные записи в виде динамического набора. Динамический набор представляет собой обновляемый набор записей, в котором фактически содержатся указатели на записи. Динамические наборы являются временными и после закрытия становятся недоступными.

2.3.4.1.Элементы SQL – оператора

SQL – оператор состоит из трех элементов:

1.  Объявления параметров – необязательные параметры, которые программа передает оператору SQL.

2.  Команды – сообщает СУБД тип выполняемой операции, например, Select.

3.  Объявления опций – указывает на условия фильтрации, порядок группировки или сортировки.

Синтаксис оператора:

[Объявление операторов] Команда [Опции]

Команды SQL – операторов:

Команда	Функция
Delete	Удаляет записи из таблицы
Insert	Добавляет в таблицу группу записей
Select	Выбирает группу записей и помещает их в динамический набор данных
Update	Задает значения полям таблицы

Оператор Select

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

Общий вид оператора:

Select [предикат] список – полей From список – таблиц [отношение между таблицами] [диапазон] [сортировка]

Элемент «список – полей» оператора Select имеет следующий вид:

[имя – таблицы.]поле1 [As Alias1] [, [имя - таблицы.]поле2 [As Alias2] [¼]]

Для выбора всех полей в указанной таблице используется (*).

Выдать список всех студентов:

Select * From Student

Если извлекаются отдельные поля, они включаются в список и разделяются запятыми. Если встречается пробел, название заключается в квадратные скобки.

Составить список всех фамилий студентов с годом их поступления.

Select FIOS, YearEnter From Student

Для выбора из множества таблиц нужно указать:

-  таблицы, из которых выбираются поля;

-  поля, из которых выбираются данные;

-  отношения между таблицами.

Имена таблиц и полей разделяются точками, например, Student. FIOS.

Имена используемых таблиц указываются в предложении From.

Отношения между таблицами определяются либо в предложении Where, либо Join.

Составить список студентов, обучающихся на ФИТ.

Select Student. FIOS From Student, Group Where Group. Sfacult = “ФИТ” And Group. Sgroup = Student. Sgroup

В операторе Select могут использоваться вычисляемые поля. Вычисляемое поле может быть результатом арифметической или строковой операции, или результатом разрешенной функции.

Выдать список студентов и время их обучения.

Select FIOS, Year(Date()) – YearEnter As [Срок обучения] From Student

Если имя вычисляемого поля не указывается, то ему автоматически назначается имя типа Expression0001. Вычисляемые поля хранятся в наборе записей, как поля доступные для чтения. Корректировать их нельзя. Чтобы вычисляемое поле получило имя, после описания поля следует указать предложение As и необходимое имя поля. Этот же подход можно использовать, чтобы переопределить имя поля.

Для определения, из каких таблиц следует выбирать информацию, используется предложение From, которое имеет следующий общий вид:

From table1 [In DB1] [As Alias1] [, table2 [In DB2] [As Alias2]

Чаще всего выбираются все записи, удовлетворяющие критерию. Это соответствует предикату All, то есть запись:

Select * From Student

идентична записи:

Select All * From Student.

Для выбора уникальных значений используется предикаты Distinct и Distinctrow.

Если указан Distinct, выбирается также одна запись для каждого значения заданного поля. Distinctrow позволяет отбросить все повторяющиеся значения в исходной таблице.

Отношения между таблицами

Чтобы указать отношения между таблицами можно использовать предложения:

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

-  Where применяется для фильтрации записей, возвращаемых запросом; может использоваться аналогично предложению Inner Join.

Предложение Join имеет следующий основной формат:

Table1 {Inner/Left/Right} Join Table2 On Table1.Key1 q Table2.Key2

q = {<, <=, >, >=, =, <>}

Table1 называется левой таблицей, а Table2 – правой таблицей.

Зависимость возвращаемых записей от типа предложения Join:

Тип оператора Join	Записи из левой таблицы	Записи из правой таблицы
Inner	Только записи соответствующие правой таблице	Только записи соответствующие левой таблице
Left	Все записи	Только записи соответствующие левой таблице
Right	Только записи соответствующие правой таблице	Все записи

Предложение Where можно использовать для связи двух таблиц аналогично предложению Inner Join.

Составить список ведомостей по предметам, указав предметы для которых экзамены не сдаются.

Select Predmet. Predm, Vedom. Nved From Predmet Left Join Vedom On Predmet. CodePredm = Vedom. Code. Predm

Выбор критерия фильтрации

Условия критерия фильтрации задается следующим синтаксисом:

Where логическое выражение

Существует четыре типа логических выражений (предикатов) в предложении Where: сравнение, Like, In, Between.

Предикат сравнения имеет следующий вид:

<выражение1> <операция сравнения> <выражение2>

<операция сравнения> = {=, <, >, <>, >=, <=}.

Составить список студентов, имеющих отличные оценки.

Select Student. FIOS From Student, Ekzamen Where Ekzamen. CodOcen = 5 And Ekzamen. Nzach = Student. NZach

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

<выражение> Like <шаблон>

Шаблон допускает использования групповых имен и списков символов.

Список символов должен отвечать трем требованиям:

1.  Список заключается в квадратные скобки.

2.  Первый и последний символ должны разделяться дефисом.

3.  Диапазон символов должен задаваться в возрастающем порядке.

Шаблоны, используемые в предикате Like

Групповое имя	Используется для сравнения	Пример	Пример результата
* или %	Со множеством символов	S*	Smith, Sheep, Same
?	Отдельный символ	An?	Any, And
#	С отдельной цифрой	354#	3543, 3540
[список]	С отдельным символом списка	[c-f]	c, d, e, f
[!список]	С отдельным символом, не входящим в список	[!c-f]	A, b, g, h, ¼
Комбинация	В соответствии с шаблоном	A? t*	Art, antique, artist

Составить список студентов, фамилии которых со второй буквы фамилии включают комбинацию символов «етр».

Select * From Student Where Fios Like “?етр*”

Предикат In позволяет сравнивать выражение с несколькими значениями. Формат:

<Выражение> In <список значений>

Составить список студентов, поступавших в институт в 2000, 2003 и 1999 году.

Select FIOS From Student Where YearEnter In (2000,2003, 1999)

Предикат Between позволяет искать выражение, имеющее значение в заданном диапазоне. Применимо к строкам, числам и датам. Формат:

<выражение> [Not] Between <значение1> And <значение2>

Перед датой используется символ #.

Выдать список студентов, имеющих положительные оценки.

Select Student. FIOS, Student. Nzach From Student, Ekzamen Where Ekzamen. CodeOcen Between 3 And 5 And Student. Nzach = Ekzamen. Nzach

Предложения Where могут работать с комбинированными условиями, которые объединяются с помощью связок And и Or.

Для сортировки значений используется предложение Order By. Для сортировки можно выбирать несколько полей. В этом случае поля разделяются запятыми. По умолчанию принята сортировка по возрастанию. Порядок сортировки можно изменить, указав после имени поля слово Desc.

Выдать список студентов по алфавиту.

Select * From Student Order By FIOS

Выдать список студентов по убыванию годов их поступления, отсортировав их по алфавиту для каждого года.

Select * From Student Order By YearEnter Desc, FIOS

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

Эти функции имеют синтаксис:

<функция>(<аргумент>)

Выражение может относиться к одному полю, а может быть результатом вычислений на основе вычислений одного или нескольких полей. Функция Count позволяет использовать групповое имя (*).

Основные функции

Функции	Возвращаемые значения
Avg	Среднее арифметическое значение поля в записях, отвечающих условию Where
Count	Число записей, отвечающих условию
Min	Минимальное значение поля, отвечающее условию
Max	Максимальное значение поля
Sum	Сумма всех значений поля, в записях отвечающих условию
First	Значение поля в первой записи набора записей
Last	Значения поля в последней записи набора записей
StDev	Среднеквадратичное отклонение значений поля в записях, удовлетворяющих условию
Var	Дисперсия

Определить общее количество студентов.

Select Count (*) From Student

В большинстве случаев с этими функциями используется предложение Group By. Предложение Group By объединяет в группы записи с общим значением некоторого поля.

Определить среднее количество студентов в группах по факультетам.

Select SFacult, Avg(QStud) From Group Group By Sfacult

В предложении Group By можно использовать несколько полей. При этом возвращается по одной записи для каждой уникальной комбинации полей. Если указывается несколько полей, то они разделяются запятыми.

Определить средний балл для каждого студента.

Select Student. FIOS, Avg(Ekzamen. CodeOcen) From Student, Ekzamen Where Student. Nzach = Ekzamen. Nzach Group BY Student. FIOS Order By Student. FIOS

В предложение Group By может входить необязательная конструкция Having. Предложение Having работает аналогичноWhere, однако анализирует только значения результирующих записей. Предложение Having определяет, какие из выбранных записей отображать, Where – какие записи выбирать из базовых таблиц.

Определить студентов, имеющих средний балл больший трех с половиной.

Select Student. FIOS, Avg(Ekzamen. CodeOcen) From Student, Ekzamen Where Student. Nzach = Ekzamen. Nzach Group BY Student. FIOS Having Avg (Ekzamen. CodeOcen) > 3.5 Order By Student. FIOS

Создание таблиц.

При выполнении Select результат запроса помещается в динамический набор. Поскольку это временные наборы, их содержимое существует, пока набор открыт. Чтобы сохранить информацию в операторе Select используется предложение Insert Into или Into. Для создания таблицы используется оператор SQL, имеющий следующий синтаксис:

Select <список выбора> Into <новая таблица> From <исходная таблица> [Where <критерий добавления>]

Создать таблицу, в которой хранятся данные о не аттестованных студентах.

Select * Into BadStudent From Ekzamen Where Ekzamen. CodeOcen = 0

Чтобы скопировать исходную таблицу, требуется в качестве аргумента <список выбора> указать * и опустить предложение Where.

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

Insert Into <таблица приемник> Select <список выбора> From <таблица источник> [ Where <критерий добавления> ]

Добавить в таблицу BadStudent информацию о двоечниках

Insert Into BadStudent Select * From Ekzamen Where Ekzamen. CodeOcen = 2

В программном коде также допустимы следующие формы этой операции:

Insert Into BadStudent Select “ак51/5”, 5, 2