17219

Использование операций реляционной алгебры для формирования запросов на выборку данных средствами SQL

Лабораторная работа

Информатика, кибернетика и программирование

Лабораторная работа № 3 Тема: Использование операций реляционной алгебры для формирования запросов на выборку данных средствами SQL. Цель работы: Изучить специальные и теоретикомножественные операции реляционной алгебры и возможность их примирения к формировани...

Русский

2013-06-30

83.5 KB

16 чел.

Лабораторная работа № 3

Тема: Использование операций реляционной алгебры для формирования запросов на выборку данных средствами SQL.

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

Структура лабораторной работы

  1.  Общие сведения об операторах реляционной алгебры.
    1.  Оператор объединения.
    2.  Использование предикатов IN, BETWEEN, LIKE, is NULL.
    3.  Вложенные (подчиненные) запросы.
    4.  Формирование запросов на SQL по формулам реляционной алгебры.
  2.  Задание к лабораторной работе.
  3.  Оформление отчета.
  4.  Контрольные вопросы.

1. Общие сведенья об операторах реляционной алгебры

1.1. Оператор объединения.

В SQL предусмотрена возможность выполнения операции реляционной алгебры "ОБЪЕДИНЕНИЕ" (UNION) над отношениями, являющимися результатами оператора SELECT. Естественно, эти отношения должны быть определены по одной схеме. Пример: получить все Интернет - ссылки, хранимые в базе данных publications. Эти ссылки хранятся в таблицах publishers и wwwsites. Для того чтобы получить их в одной таблице, необходимо построить следующие запрос:

      SELECT publisher,url FROM publishers

      UNION

      SELECT site,url FROM wwwsites;

1.2. Использование предикатов IN, BETWEEN, LIKE, is NULL

При задании логического условия в предложении WHERE могут быть использованы операторы IN, BETWEEN, LIKE, is NULL.

Операторы IN (равен любому из списка) и NOT IN (не равен ни одному из списка) используются для сравнения проверяемого значения поля с заданным списком. Этот список значений указывается в скобках справа от оператора IN.

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

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

Например, получить из таблицы titles сведения о книгах, изданные в 2000, 2001 и 2002 годах.

SELECT *

FROM titles

WHERE yearpub IN (2000, 2001, 2002);

Получить сведения об изданиях, которые не издавались в 2000, 2001 и 2002 годах.

SELECT publisher

 FROM ((titles AS a INNER JOIN publishers AS b ON

        a.pub_id = b.pub_id)

 WHERE yearpub NOT IN (2000, 2001, 2002);

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

Например, запрос типа: перечислить издательства, которые издали книги в количестве не менее 3 и не более 10 будет выглядеть как

SELECT publisher, COUNT(title)

 FROM ((titles AS a INNER JOIN publishers AS b ON

        a.pub_id = b.pub_id)

 GRROUP BY publisher

 HAVING COUNT(title) BETWEEN 3 AND 10;

Граничные значения, в данном случае значения 3 и 10, входят в множество значений, с которыми производится сравнение. Оператор BETWEEN может использоваться как для числовых, так и для символьных типов полей.

Оператор LIKE применим только к символьным полям типа CHAR или VARCHAR (см. л.р. № 1). Этот оператор просматривает строковые значения полей с целью определения, входит ли заданная в операторе LIKE подстрока (образец поиска) в символьную строку-значение проверяемого поля.

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

  •  ? — любой один знак в данном месте.
  •  * — любое количество любых знаков после предшествующих ему символов.
  •  # — одна любая цифра.

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

SELECT *

FROM titles

WHERE title LIKE ‘*история*;

Обращаем внимание на то, что рассмотренные выше операторы сравнения «=, <, >, <=, >=, <>» и операторы IN, BETWEEN и LIKE нельзя использовать для проверки содержимого поля на наличие в нем пустого значения NULL. Для этих целей предназначены специальные операторы is NULL (является пустым) и IS NOT NULL (является не пустым).

Например, получить список издателей (publisher), которые не имеют электронного адреса (поле url не заполнено).

SELECT publisher

FROM publisher

WHERE url IS NULL;

1.3. Вложенные (подчиненные) запросы

SQL позволяет использовать одни запросы внутри других запросов, то есть вкладывать запросы друг в друга. Предположим, известно название издательства (VHS), но неизвестно значение поля PUB_ID для него. Чтобы извлечь данные обо всех изданиях, можно сформулировать следующий запрос:

SELECT title

 FROM titles

 WHERE pub_id = (SELECT pub_id FROM publishers

                 WHERE publisher = ‘VHS’);

Как работает запрос SQL со связанным подзапросом?

• Выбирается строка из таблицы, имя которой указано во внешнем запросе.

• Выполняется подзапрос и полученное значение применяется для анализа этой строки в условии предложения WHERE внешнего запроса.

• По результату оценки этого условия принимается решение о включении или не включении строки в состав выходных данных.

• Процедура повторяется для следующей строки таблицы внешнего запроса.

Следует обратить внимание, что приведенный выше запрос корректен только в том случае, если в результате выполнения указанного в скобках подзапроса возвращается единственное значение. Если в результате выполнения подзапроса возвращается несколько значений, то этот подзапрос будет ошибочным. В данном примере это произойдет, если в таблице publishers будет несколько записей со значениями поля publisher = ‘VHS.

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

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

Данные обо всех издательствах (таблица publishers), издавших литературу в 2000 году можно выбрать с помощью следующего запроса:

SELECT publisher

 FROM publishers

 WHERE pub_id IN (SELECT pub_id FROM titles

                  WHERE yearpub = ‘2000’);

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

Пусть требуется определить издательства, количество изданий которых больше чем у издательства ‘VHS’:

SELECT publisher, COUNT(b.pub_id)

 FROM publishers AS a INNER JION titles AS b

      ON a.pub_id = b.pub_id

 GROUP BY publisher

 HAVING COUNT(pub_id)>(SELECT COUNT(b.pub_id)

                   FROM publishers AS a INNER JION

                        titles AS b

                   ON a.pub_id = b.pub_id

                   WHERE publisher = ‘VHS’);

1.4. Формирование запросов на SQL по формулам реляционной алгебры

Любой запрос, сформулированный на естественном языке можно записать в виде формулы реляционной алгебры и наоборот. При переводе запроса с языка формул на язык SQL необходимо, несмотря на последовательность записи операций в формуле, распределить эти операции в соответствии с синтаксисом инструкции SELECT.

Например, реализовать запрос средствами SQL сформулированный в виде формулы реляционной алгебры

Запрос на SQL

SELECT author, title, yearpub

 FROM (authors AS a INNER JOIN titleauthors AS b

      ON a.au_id = b.au_id) INNER JION titles AS c

      ON b.title_id = c. title_id

 where yearpub > 1990

2. Задание к лабораторной работе

Замечания по ходу выполнения лабораторной работы.

При выполнении заданий лабораторной работы все вычисляемые поля заменять синонимами, используя опцию AS в предложении SELECT.

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

Реализовать следующие запросы средствами SQL:

  1.  Получить общее, минимальное, максимальное и среднее количество поставляемых деталей (таблица будет содержать одну строку).
  2.  Получить общее количество всех поставляемых деталей и общую стоимость всех поставок (таблица будет содержать одну строку).
  3.  Получить общий список наименований материалов и наименований деталей (использовать операцию объединения).
  4.  Получить список поставщиков, которые поставляют деталей «Д1» больше чем эти детали поставляет поставщик с номером «1»
  5.  Получить список деталей, цена которых больше цены «стальной гайки» (номер «стальной гайки» Д1). Определить и обосновать (устно), какой параметр выбора предпочтительнее (название или номер детали).
  6.  Получить список деталей (название и номер детали), количество поставок которых находится в диапазоне от 20 до 40.
  7.  Получить список деталей, поставляемых поставщиками из «Харькова» «Колей» и «Машей». Список поставщиков задать явно (перечислением) и воспользоваться оператором IN. Отсортировать список в обратном порядке.
  8.  Получить список деталей, поставляемых поставщиками из города, начинающегося с буквы «С».
  9.  Получить список название деталей и поставщиков, поставляемых детали с номером, содержащим цифру «8».
  10.  Получить список поставщиков, адрес и наименование детали, которые поставляют хотя бы одну такую же деталь, как и поставщик «2».
  11.  Получить список поставщиков, которые не поставляют ни одной детали (использовать либо оператор IN (NOT IN), либо LEFT JOIN или RITGH JOIN для реализации операции вычитания).
  12.  Получить список названий деталей и материала, из которого они сделаны, количество поставок которых меньше чем количество поставок деталей «БОЛТ» сделанных из «Чугуна». Здесь под термином «количество» понимается не значение поля KOL (количество поставок) таблицы POSTKA, а количество поставляемых деталей соответствующее заданному материалу посчитанного функцией COUNT в поле MATER (наименование материала) таблицы SP_MATER.

Реализовать запросы на SQL, по заданным операциям реляционной алгебры (все имена атрибутов переименовать в естественно-языковую форму).

  1.  
  2.  
  3.  
  4.  

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

  1.  

Для реализации 5-го запроса воспользоваться эквивалентной формулой, реализующей операцию пересечения через композицию операций вычитания.

  1.  

Для реализации 6-го запроса использовать в качестве очередного источника данных сохраненный запрос Ti, сформулированный на предыдущем шаге.

  1.  
    1.  
    2.  
    3.  

3. Оформление отчета

  1.  Титульный лист оформляется согласно традиционным требованиям, включая Номер работы, Номер группы и ФИО студента, а также кто принимал данную работу.
  2.  Содержание должно включать Тему лабораторной работы и Ход ее выполнения.
  3.  Ход выполнения работы должен содержать все инструкции SQL (SELECT) реализованные в лабораторной работе. Условия заданий записывать не обязательно.
  4.  Вывод.

4. Контрольные вопросы

  1.  Виды операций реляционной алгебры.
  2.  Теоретико-множественные операции реляционной алгебры.
  3.  Какие отношения называются совместимыми по типу?
  4.  Операция объединения.
  5.  Операция пересечения.
  6.  Операция вычитания.
  7.  Операция декартова произведения.
  8.  Реализация теоретико-множественных операций реляционной алгебры.
  9.  Какие типы соединений (JOIN) реализуют операция вычитания?
  10.  В каком случае обязательно указывать имя таблицы перед именем поля?
  11.  Какая разница между предложениями WHERE и HAVING?
  12.  Использование предикатов.
  13.  Свойства подчиненных запросов.
  14.  В каких случаях нельзя использовать логические операции сравнения с подчиненными запросами?
  15.  В каких случаях используется оператор IN?


 

А также другие работы, которые могут Вас заинтересовать

50190. Експериментальна перевірка співвідношення невизначеностей Гейзенберга для фотонів 132 KB
  Прилади і обладнання Гелій−неоновий лазер типу ЛГ56 щілина з мікрометричним гвинтом екран з міліметровою шкалою Теоретичні відомості та опис установки В даній лабораторній роботі пропонується перевірити експериментально співвідношення невизначеностей Гейзенберга для координати і відповідної проекції імпульсу фотонів зокрема: . 3 Цей вираз є робочою формулою для перевірки...
50191. Нечеткая логика 68 KB
  Согласно заданным вариантам разработать программу на любом алгоритмическом языке, способную: А. Различать степени изменения лингвистической переменной в трех степенях – «Очень – Нормально – Слабо» Б. Изменять порог чувствительности. Больно – Терпимо – Дайте два
50192. Определение теплопроводности твердого тела (пластина) 213 KB
  Плеханова технический университет Кафедра Общей и технической физики лаборатория виртуальных экспериментов Определение теплопроводности твердого тела пластина Методические указания к лабораторной работе № 18 для студентов всех специальностей САНКТПЕТЕРБУРГ 2010 УДК 531 534 075. Цель работы: определить коэффициент теплопроводности твердых тел методом сравнения с теплопроводностью эталонного материала. Поток тепла dQ протекающего через однородную перегородку толщиной и площадью при разности температур определяется формулой...
50193. Нечеткая логика 68 KB
  Различать степени изменения лингвистической переменной в трех степенях – «Очень – Нормально – Слабо» Б. Изменять порог чувствительности. Маленький – средний - большой
50194. Работа с текстовыми файлами 55.5 KB
  Цель: Приобрести практические навыки в проектировании структуры файла а также закрепить навыки по вводу данных в текстовый файл и их обработке. Этим звеном является файловая структура. Ввод и вывод информации в файл обеспечивается с помощью так называемого указателя на файл который является указателем на файловую структуру в памяти.
50195. ИЗУЧЕНИЕ ПРИНЦИПА ДЕЙСТВИЯ И ХАРАКТЕРИСТИК ЭЛЕКТРОННЫХ ЛАМП 336.5 KB
  Важнейшей характеристикой диода является зависимость силы тока текущего через лампу анодного тока от разности потенциалов между катодом и анодом анодного напряжения. Анодный ток зависит от анодного напряжения и от температуры катода. При постоянной температуре катода анодный ток возрастает с увеличением анодного напряжения . Поскольку механизм возникновения электрического тока в этом случае отличается от механизма возникновения тока в проводниках то зависимость анодного тока от анодного напряжения не описывается законом Ома.
50197. Развитие русской социологической мысли: этапы, школы, представители 17.23 KB
  Социологическая мысль в России развивается как часть общемировой социологической науки. Изменение общественных отношений, вызванное развитием капитализма в России после реформ 60-70-х гг.
50198. Нечеткая логика 68 KB
  Согласно заданным вариантам разработать программу на любом алгоритмическом языке, способную: А. Различать степени изменения лингвистической переменной в трех степенях – «Очень – Нормально – Слабо» Б. Изменять порог чувствительности. Негр – Мулат – Белый