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?


 

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

76639. История в системе социально-гуманитарных наук. Основы методологии исторической науки. Факторы самобытности российской истории 35 KB
  История наука о развитии человеческого общества во всем его многообразии. История общества представляет собой совокупность конкретных и многообразных действий и поступков отдельных людей человеческих сообществ находящихся в определенной взаимосвязи составляющих все человечество. В системе социально-гуманитарных дисциплин история может играть роль всеобщей базы которая постепенно накапливается.
76640. Восточные славяне в древности 32.5 KB
  Однако историки сходятся во мнении что реальными предшественниками русских людей были восточные славяне принадлежащие к группе индоевропейских народов. В результате разгрома сарматами славяне продвигаются на север в лесную зону ассимилируя литовско-латышские и финно-угорские племена. Главными действующими лицами в нем были германцы и славяне.
76641. Особенности становления государственности в России и в мире. Киевская Русь 32.5 KB
  Центром этого государства был Киев. Название этого государства неизвестно. Иногда его называют Каганат русов поскольку глава этого государства по аналогии с соседним хазарским носил титул Кагана. На эти сведения опирается так называемая норманская теория происхождения русского государства.
76642. Крещение Руси 34.5 KB
  Оно имеет длительную историю: распространение христианства на Руси началось задолго до крещения на Днепре и продолжалось еще в течение полутора веков. Православные источники связывают проникновение христианства на территорию Киевской Руси с миссионерской деятельностью апостола Андрея Первозванного в I веке н. Владимир предпринял первую религиозную реформу суть которой состояла в попытке слияния разнородных богов всех племен Киевской Руси в единый пантеон во главе с княжеским богом Перуном.
76643. Феодальная раздробленность Руси 27 KB
  Как и в Западной Европе тенденции к политической раздробленности на Руси проявились рано. Именно с этого времени историческая наука ведет отсчет феодальной раздробленности на Руси. В первые полтора века существования Киевской Руси дружина полностью находилась на содержании у князя.
76645. Русские земли в 15 в. и европейское средневековье. Складывание централизованного государства. Возвышение Москвы 39 KB
  Возвышение Москвы Как и в Западной Европе после периода феодальной раздробленности на Руси в XIVXV вв. На Руси хотя экономические связи между отдельными княжествами без сомнения развивались но общий всероссийский рынок возник позже только в XVII в. Таким образом политические процессы на Руси опережали экономические. Усилиями нескольких поколений выдающихся деятелей на Руси складывается такое государство.
76646. Россия в 16 в. в контексте развития европейской цивилизации. Иван-4 – первый царь Всея Руси. Опричина 35 KB
  Период опричнины В 1560 г. царь вводит новый порядок управления государством получивший название опричнины. Политическим и административным центром опричнины стал особый двор со своей Боярской думой и приказами. В опричнине была особая казна и особое опричное войско: первоначально одна тысяча к концу опричнины шесть тысяч.
76647. Россия в 16 в. в контексте развития европейской цивилизации. «Смутное время». Воцарение династии Романовых 38 KB
  Главной отраслью экономики России оставалось с х а основными с х культурами были рожь и овес. За счет освоения новых земель в Поволжье в Сибири на юге России производилось больше с х продукции чем в прошлом веке хотя методы обработки земли оставались прежними с помощью сохи бороны; плуг внедрялся медленно. – период в истории России названный Смутным временем.