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?


 

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

65730. ЦІННІСНИЙ ДИСКУРС СТУДЕНТСЬКОЇ МОЛОДІ В ПЕРІОД РИНКОВИХ ТА ДЕМОКРАТИЧНИХ ТРАНСФОРМАЦІЙ 178 KB
  Актуальність дослідження проблеми ціннісного дискурсу студентської молоді обумовлена необхідністю формування відповідей на виклики які сьогодні делегує студентській молоді дійсність економічні політичні та соціокультурні зміни як всередині країни так і на міжнародному рівні.
65731. Моделі та методи аналізу сервіс-орієнтованих інформаційно-обчислювальних систем 289.5 KB
  Математичне моделювання є одним з найбільш поширених методів що дозволяє вирішити цю задачу. Використання ж методів імітаційного моделювання не дозволяє отримати формалізовані залежності між показниками якості та параметрами системи.
65732. ПЕДАГОГІЧНІ УМОВИ ПІДВИЩЕННЯ КВАЛІФІКАЦІЇ ВЧИТЕЛІВ МИСТЕЦЬКИХ ДИСЦИПЛІН У СИСТЕМІ ПІСЛЯДИПЛОМНОЇ ОСВІТИ 170.5 KB
  У сучасному постіндустріальному суспільстві ідеал гармонійно розвинутої особистості також набуває нового змісту тому система художньоестетичного виховання потребує появи вчителя нової формації особливо вчителя мистецьких дисциплін який має вирішувати ряд основних завдань...
65733. ФУНКЦІЇ ОРГАНІВ ДОСУДОВОГО СЛІДСТВА В КРИМІНАЛЬНОМУ ПРОЦЕСІ УКРАЇНИ 182 KB
  У науці кримінального процесу концепція щодо визначення кримінальнопроцесуальних функцій має більш як сторічну історію. Це пояснюється насамперед тим що правильне визначення кримінально-процесуальних функцій та їх...
65734. Активізація залучення прямих іноземних інвестицій в економіку України в умовах глобальної конкуренції 265.5 KB
  В умовах глобальної конкуренції роль прямих іноземних інвестицій ПІІ полягає в залученні не лише необхідних обсягів капіталу а й сучасних технологій методів управління та висококваліфікованих менеджерів. Разом з тим глобалізація світогосподарських взаємин за участю прямих іноземних інвестицій...
65735. Удосконалення розрахунку напружено-деформованого стану мостових конструкцій з урахуванням динамічного впливу вантажних поїздів 697.5 KB
  Штучні споруди є невідємною та важливою складовою транспортної системи країни однак до цього часу в Україні відсутні рекомендації з визначення швидкісних режимів руху поїздів мостами. Більш ніж 10 залізничних мостів в Україні внаслідок наявності дефектів є барєрними обєктами що призводить...
65736. ЖАНРОВІ МОДИФІКАЦІЇ В ПОЕЗІЇ В. СКОТТА ТА ПОЕТІВ-«ЛЕЙКІСТІВ» 139.5 KB
  Скотта і поетів лейкістів яких обєднували схожі світоглядні позиції та напрямки естетичних пошуків ще недостатньо повно висвітлені в науці особливо в аспекті засвоєння народних традицій що вплинули на картину світу британського романтизму на його ранньому етапі й зумовили подальші художні відкриття.
65737. МОДЕЛЮВАННЯ І ПРОГНОЗУВАННЯ ДІЇ НЮХОВОГО НАНОБІОСЕНСОРА НА ОСНОВІ МОЛЕКУЛИ БІЛКА ТИПУ GPCR 481.5 KB
  Причинами цього є поперше те що сучасна кремнієва електроніка досягає межі мініатюризації і для виведення її на якісно новий рівень розвитку створення так званого квантового комп'ютера необхідна нова фізична елементна база з елементами розміру порядку нанометра тобто розміру молекули.
65738. Робота та розрахунок сталевих нагельних з’єднань дерев’яних конструкцій за повторних навантажень 1.33 MB
  Велике значення мають дослідження міцнісних і деформативних характеристик нагельних з'єднань дерев'яних елементів при одноразових та повторних навантаженнях оскільки при експлуатації значна кількість дерев'яних конструкцій знаходяться саме в таких умовах.