17219

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

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

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

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

Русский

2013-06-30

83.5 KB

15 чел.

Лабораторная работа № 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?


 

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

23071. Сфери національних інтересів України 62.5 KB
  Сфери національних інтересів України. Конспект вельмишановного Пана Перепелиці каже про національні інтереси України – ніхера тому все що є в конспекті по національним інтересам дивіться в 3 питанні національні інтереси життєво важливі матеріальні інтелектуальні і духовні цінності Українського народу як носія суверенітету і єдиного джерела влади в Україні визначальні потреби суспільства і держави реалізація яких гарантує державний суверенітет України та її прогресивний розвиток; Стаття 6. Пріоритети національних інтересів Пріоритетами...
23072. Поняття національного інтересу 40 KB
  Сфери національних інтересів України. Формується з інтересів кожної конкретної особистості кожного соціального прошарку. Завдання еліти – продукування інтересів в суспільство. Еліта має мобілізувати націю на реалізацію національних інтересів.
23073. Поняття загроза та небезпека національним інтересам України. Види загроз національній безпеці України 38.5 KB
  ТЕМА: Загрози національній безпеці. Потенційні загрози – це становище при якому існують певні зазіхання але при цьому відсутні умови при яких вони переходять в намагання завдати шкоду національним інтересам України. Вимоги до органів мають упереджувати загрози: ефективно виявляти загрози та ефективно на них реагувати; адекватно реагувати на виникнення загрози; Методика: визначення чинників що спричиняють загрозу національним інтересам України; класифікувати загрози звідкіля виходять і в якій сфері знаходяться: політика економіка...
23074. Суб`єкти національної безпеки 37.5 KB
  Суб`єкти національної безпеки З конспекту. ТЕМА: Система національної безпеки України. Суб`єкти національної безпеки. Вимоги до системи національної безпеки.
23075. Вимоги до системи національної безпеки 33 KB
  Вимоги до системи національної безпеки З конспекту Кожна країна створює певну систему органів які могли б реагувати на загрози така система називається системою національної безпеки. Система забезпечення національно міжнародної безпеки включає певну діяльність органів по підтримці стану захищеності. Наявність механізмів які забезпечують стан безпеки. Система національної безпеки – наявність певних органів тільки обмежена територією певної країни.
23076. Роль та повноваження органів спеціальної компетенції в системі забезпечення національної безпеки України 69.5 KB
  Роль та повноваження органів спеціальної компетенції в системі забезпечення національної безпеки України. Національний банк України відповідно до основних засад грошовокредитної політики визначає та проводить грошовокредитну політику в інтересах національної безпеки України; міністерства Служба безпеки України та інші центральні органи виконавчої влади в межах своїх повноважень забезпечують виконання передбачених Конституцією і законами України актами Президента України Кабінету Міністрів України завдань здійснюють...
23077. Вимірювання напруг при механічних деформаціях поляризаційним методом 447 KB
  Різницю фаз Δ що виникає між двома взаємно перпендикулярними лінійнополяризованими хвилями визначають за формулою 16 де λ довжина хвилі; σ1 σ2 головні нормальні напруги; d товщина деталі; с стала фотопружності яка залежить від матеріалу деталі. Таким чином при постійній товщині зразка лінії однакового зсуву фаз відповідають лініям однакових різниць нормальних напруг або лініям рівних максимальних дотичних напруг оскільки максимальна дотична напруга τmax пов'язана з...
23078. Дослідження анізотропних кристалів під поляризаційним мікроскопом 458 KB
  Прилади: поляризаційний мікроскоп клин або компенсатор Берека набір шліфів і пластинок з одновісних та двовісних кристалів вирізаних під різними кутами до оптичної осі. Різниця яку вносить пластинка залежить від її товщини матеріалу зразка та орієнтації оптичної осі відносно зрізу. Форма і розміщення ізохромат залежать від напряму оптичної осі відносно зрізу товщини зразка і довжини хвилі Форма і розміщення ізогір залежать від орієнтації осі відносно зрізу і взаємного положення поляризатора та аналізатора. Для пластинки вирізаної...
23079. Вимірювання оптичних сталих металів та напівпровідників за допомогою компенсатора Бабіне 278.5 KB
  Відомо що лінійнополяризоване світло яке падає на межу поділу діелектрик провідне середовище після відбиття перетворюється на еліптичнополяризоване крім того випадку коли напрям коливань електричного вектора лежить в площині падіння або в перпендикулярній площині. Вимірюючи параметри еліптичнополяризованого світла а саме; зсув фаз Δ між р та s складовими електричного вектора відбитої хвилі азимут відновленої поляризації ψ а також кут падіння світлової хвилі на площину дзеркала φ можна обчислити оптичні сталі n і κ з співвідношень...