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?


 

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

77425. Общая характеристика ООО 38 KB
  Участники не отвечают по его обязательствам и несут риск связанный с деятельностью общества и несут риск в размере долей внесенных в капитал. Общества должны иметь печать которая содержит полное фирменное наименование на русском языке и указание на место нахождения общества. В случае несостоятельности банкротства общества по вине его участников или по вине других лиц которые имеют право давать показания на указанных лиц может быть возложена субсидиарная ответственность по обязательствам. Место нахождения общества определяется местом его...
77426. Уставный капитал общества 34 KB
  Доля участника – условная величина представляющая собой стоимостную оценку вклада участника в уставной капитал общества. Вклад участника – реальное имущество вносимое участником в уставной капитал общества Согласно статье 15 ФЗ Об ООО в качества вклада участник может вносить деньги ценные бумаги другие вещи или имущественные права право пользования например либо иные права которые имеют денежную оценку право на объекты индивидуальной собственности. Законом или уставом общества может быть установлен запрет на внесение какоголибо...
77427. Увеличение и уменьшение уставного капитала 45.5 KB
  Иногда нужно увеличить уставный капитал общества не хватает оборотных средств у общества; требование для получения лицензии; размывка долей. Статья 17 предусматривает 3 способа увеличения уставного капитала: За счет имущества общества Данное увеличение происходит за счет стоимости чистых активов. Увеличение уставного капитала происходит за счет чистых активов общества размер чистых активов определяется на основании годовой отчетности общества за год предшествующий году в течение которого принято это решение.
77428. Общее собрание ООО и его компетенция. Подготовка, порядок созыва и проведения общего собрания ООО. Принятие решения на собрании 26.26 KB
  32 закона об обществах с ограниченной ответственностью. Компетенция общего собрания участников общества определяется уставом общества в соответствии с настоящим Федеральным законом. К компетенции общего собрания участников общества относятся: 1 определение основных направлений деятельности общества а также принятие решения об участии в ассоциациях и других объединениях коммерческих организаций; 2 изменение устава общества в том числе изменение размера уставного капитала общества; 3 утратил силу; 4 образование исполнительных органов...
77429. Доля общества в уставном капитале 39.5 KB
  Но в законе есть случаи когда общество вправе или обязано приобрести долю: Устав общества может предусматривать необходимость получения согласия остальных участников на отчуждение доли или ее части третьим лицам; либо запрет на отчуждение доли третьим лицам а участник общества отказался приобретать долю или ее часть или не дает согласие на отчуждение доли третьему лицу. В этом случае общество обязано приобрести по требованию участника общества принадлежащую ему долю либо ее часть. Если общее собрание участников общества принимает решение...
77430. Порядок формирования и правовой режим имущества общества с ограниченной ответственностью. Вклады участников в имущество общества, а не в уставный капитал общества 16.18 KB
  Вклады участников в имущество общества а не в уставный капитал общества. Судьба долей уставного капитала при внесении вклада в имущество общества. Формируется на первоначальном этапе при учреждении общества.
77431. Права и обязанности участников ООО (имущественные и неимущественные права) 18.64 KB
  В зависимости от вида участия членов ООО в деятельности общества их права разделяются на имущественные и неимущественные. К числу неимущественных прав участников ООО в соответствии с ГК РФ и Законом об ООО относятся: право на участие в управлении обществом; право на получение информации о деятельности общества; право на ознакомление с документацией общества в том числе с бухгалтерскими книгами; право требовать проведения аудиторской проверки; право принимать участие в распределении прибыли.Включение последнего права участников ООО в...
77432. Право участника общества с ограниченной ответственностью на продажу доли в уставном капитале общества и его реализация 30.84 KB
  Преимущественное право покупки доли участников общества с ограниченной ответственностью и его реализация. Форма сделки направленной на продажу доли. Отчуждение участником общества своей доли ее части третьим лицам допускается если иное не предусмотрено уставом общества.