Базы данных. Лекции БД. Лекция 5 Основные понятия информационных систем 5 История развития компьютеризации информационных процессов и систем. 5

• Название: Лекция 5 Основные понятия информационных систем 5 История развития компьютеризации информационных процессов и систем. 5
• Анкор: Базы данных
• Дата: 05.01.2022
• Размер: 1.07 Mb.
• Имя файла: Лекции БД.doc
• Тип: Лекция; #324711
• страница: 15 из 24

Лекция 12.

8.6.13.3.Условия, содержащие кортежи

В SQL кортеж представляется заключенным в скобки списком скалярных значений, например (123, ‘foo’) и (имя, адрес, доход). Первый из них в качестве своих компонентов содержит константы, а второй – атрибуты. Допустимо также смешение констант и атрибутов в одном кортеже.

Если в кортеже t столько же компонентов, как и в отношении Р, имеет смысл сравнивать t и Р в выражениях типа t IN P или t <> ANY P. Последнее означает, что в Р есть кортеж, отличающийся от t. При сравнении кортежа с членами отношения Р необходимо использовать установленный стандартный порядок атрибутов Р.

Ниже сформулирован запрос, касающийся продюсеров фильмов, в которых играет Харрисон Форд. Он состоит из «главного» запроса, вложенного в него запроса и третьего запроса, вложенного во второй.

SELECT имя

FROM продюсер

WHERE серт# IN

(SELECT сертП#

FROM фильмы

WHERE (название, год) IN

(SELECT Название_фильма, год_выпуска

FROM звезды_в_фильме

WHERE имя_звезды=’Харрисон Форд’

)

);
Нужно проанализировать каждый вложенный запрос, начиная изнутри. Поэтому мы начнем со строк (7) – (9). Этот подзапрос проверяет кортежи отношения «звезды_в_фильме», находит в них кортежи, компонентом «имя_звезды» которых является ’Харрисон Форд’, и возвращает названия и годы выпуска фильмов. Заметим, что ключом фильмов является не один атрибут «год», а оба атрибута «название» и «год», поэтому для идентификации фильма нужно строить кортежи, содержащие оба эти атрибута.

Теперь рассмотрим средний подзапрос, т.е. строки (4) – (6). Здесь ведется поиск кортежей отношения «фильмы», в которых есть годы и названия фильмов, выбранных в последнем подзапросе. Для каждого такого кортежа возвращается номер сертификата продюсера, поэтому результат данного подзапроса – множество номеров сертификатов продюсеров фильмов, в которых играет Харрисон Форд.

И, наконец, рассмотрим «главный» запрос в строках (1) – (3). Здесь проверяется отношение «продюсер» и ведется поиск кортежей, компонент серт# которых является одним из сертификатов множества, возвращенного средним подзапросом. Для каждого такого кортежа возвращается имя продюсера и порождается множество продюсеров фильмов, в которых играл Харрисон Форд.

Рассмотренный запрос, сформулированный при использовании вложенных подзапросов, можно записать в виде выражения типа select-from-where с указанием в пункте FROM всех отношений, упомянутых в главном запросе или подзапросе. Связки IN заменяются равенствами в пункте WHERE . Например,
SELECT имя

FROM продюсер, фильмы, звезды_в_фильме

WHERE серт#= сертП# AND

Название=название_фильма AND

Год=год_выпуска AND

Имя_звезды=’Харрисон Форд’;
Разница между этими двумя запросами связана со способом дублирования имен продюсеров и будет рассмотрена ниже.

8.6.13.4.Коррелирующие подзапросы

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

Найдем названия, использованные для двух или более фильмов. Внешний запрос касается поиска кортежей в отношении «фильмы». В подзапросе спрашивается, есть ли в каждом кортеже фильм с тем же названием и более позднего года выпуска. Итак,

1. 
   SELECT название
   
2. 
   FROM фильмы AS Old
   
3. 
   WHERE год < ANY
   
4. 
   (SELECT год
   
5. 
   FROM фильмы
   
6. 
   WHERE название=Old.название
   
7. 
   );
   

Как и в случае с другими вложенными запросами, мы начинаем с обработки внутреннего подзапроса, т.е. со строк (4) – (6). Если Old.название на строке (6) заменен постоянной строкой типа ‘Кинг Конг’, легко понять, что запрос касается года или годов, когда был снят фильм с названием «Кинг Конг». Данный подзапрос определяет название. Единственная проблема состоит в том, что значение Old.название неизвестно. Однако внешний запрос, находящийся на строках (1) – (3), обеспечивает такое значение, что позволяет выполнить внутренний подзапрос и определить истинность пункта WHERE , расположенного на строках (3) – (6).

Условие в строке (3) выполняется, если любой фильм с тем же названием, что и Old.название, снят позже, чем фильм кортежа, являющегося значением переменной кортежа Old. Условие истинно, если год в кортеже Old не является последним годом, когда был снят фильм с тем же названием. Значит запрос на строках (1) – (3) порождает на один фильм меньше, чем все множество фильмов с одинаковым названием: фильм, снятый дважды, указывается один раз, фильм, снятый трижды, указывается дважды и т.д.

При записи коррелирующего запроса важно знать правила определения области действия имен. В общем случае атрибут подзапроса принадлежит одной из переменных кортежа в пункте FROM, если этот атрибут входит в схему отношения для данной переменной. В противном случае рассматривается запрос, в который непосредственно входит данный подзапрос, затем следующий, «окружающий», запрос и т.д. Например, «год» на строке (4) и «название» на строке (6) указывают на атрибуты переменной кортежа, пробегающей по всем атрибутам экземпляра отношения «фильмы», введенного на строке (5), к которому адресован подзапрос, расположенный на строках (4) – (6).

Однако, ставя перед атрибутом переменную кортежа и точку, можно указать, что атрибут принадлежит именно этой переменной. Именно поэтому вводится псевдоним Old для отношения «фильмы» внешнего запроса и в строке (6) делается ссылка на Old.название. если бы два отношения из пункте FROM в строках (2) и (5) различались, псевдоним не был бы нужен и запрос относился бы непосредственно к атрибутам отношения, указанного в строке (2).
Задания. Запишите перечисленные ниже запросы, используя в каждом ответе по крайней мере один подзапрос. Записать каждый запрос в различных формах (т.е. с помощью различных множеств операторов EXISTS, IN, ALL, ANY).

1. 
   Найдите производителей ПК со скоростью не менее 266.
   
2. 
   Найдите принтеры, имеющие самую высокую цену.
   
3. 
   ! Найдите ПК-блокноты, скорость которых меньше скорости любого ПК.
   
4. 
   ! найдите номер модели продукта (ПК, ПК-блокнота или принтера), имеющего самую высокую цену.
   
5. 
   ! найдите производителей самых дешевых цветных принтеров.
   
6. 
   !! найдите производителей принтеров с самым быстрым процессором среди всех ПК, имеющих наибольший объем RAM.
   

8.6.14.Дубликаты

Отношения в SQL являются мультимножествами, а не множествами, и один кортеж может входить в отношение многократно. Получить результат в виде множества можно, как уже говорилось ранее с помощью квалификатора DISTINCT в предложении SELECT.

Замечание. Теоретически DISTINCT можно ставить после каждого слова SELECT. Фактически удаление дубликатов из отношения может дорого обойтись. В общем случае отношение должно быть упорядочено так, чтобы идентичные кортежи следовали один за другим. Только при такой группировке кортежей можно определить, следует ли удалять данный кортеж. Время, необходимое для упорядочения отношения и удаления дубликатов, зачастую превышает время выполнения самого запроса. Поэтому, если мы хотим, чтобы запросы выполнялись быстро, к устранению дубликатов следует прибегать выборочно.

Вернемся еще раз к запросу о продюсерах фильмов, в которых играет Харрисон Форд:

1. 
   SELECT имя
   
2. 
   FROM продюсер
   
3. 
   WHERE серт# IN
   
4. 
   (SELECT сертП#
   
5. 
   FROM фильмы
   
6. 
   WHERE (название, год) IN
   
7. 
   (SELECT Название_фильма, год_выпуска
   
8. 
   FROM звезды_в_фильме
   
9. 
   WHERE имя_звезды=’Харрисон Форд’
   
10. 
    )
    
11. 
    );
    

Оказывается, что для этого запроса с использованием подзапросов проблема дубликатов в ответе не возникает. Действительно, подзапрос строки (4) порождает номер сертификата продюсера Джорджа Лукаса несколько раз. Однако в «главном» запросе строки (1) каждый кортеж отношения «продюсер» рассматривается только один раз. Предполагается, что в этом отношении есть только один кортеж для Джорджа Лукаса, поэтому только он удовлетворяет условию пункта WHERE на строке (3).

В отличие от оператора SELECT, сохраняющего дубликаты по умолчанию и удаляющего их только при наличии ключевого слова DISTINCT, операция объединения, пересечения и разности обычно удаляют дубликаты кортежей. Для предотвращения удаления дубликатов за операторами UNION, INTERSECT или EXCEPT ставится ключевое слово ALL.

Операторы INTERSECT ALL и EXCEPT ALL обозначают пересечение и разность мультимножеств. Если R и S – отношения, результатом выражения
R INTERSECT ALL S
будет отношение, в котором число вхождений кортежа t равно минимальному числу его вхождений в R и в S.

Число вхождений кортежа t в результат выражения
R EXCEPT ALL S
равно разности между числом вхождений t в R и числом его вхождений в S при условии, что эта разности положительна.
Задания.

1. 
   ! Для каждого ответа из предыдущего задания определить, может ли результат запроса содержать дубликаты кортежей. Если да, то перепишите запрос так, чтобы дубликаты были исключены. Если нет, запишите запрос без помощи подзапросов.
   
2. 
   Сколькими способами можно сформулировать запросы на выборку, реализующие разность и пересечение отношений? Сформулировать запросы всеми известными вам способами.
   
3. 
   !!! Прием стеклотары. Имеется фирма, занимающаяся приемом стеклотары. Фирма имеет несколько пунктов приема. На каждый из пунктов выдаются деньги, которые затем выдаются сдатчикам стеклотары. Для учета средств используются два базовых отношения Приход(пункт_приема, дата, сумма) и Расход(пункт_приема, дата, сумма). То есть фиксируется когда, где и сколько было выдано средств и, соответственно, потрачено на принятую стеклотару. Требуется построить запрос (один), результаты выполнения которого представляется в виде таблицы Учет_средств(пункт_приема, дата, Сумма_прихода, Сумма_расхода). Считать, что в один день реализуется максимум одна выдача денег на каждом из пунктов приема (может и не быть, если остались деньги с прошлого дня) и максимум одна сдача стеклотары (т.е. отчет о расходах). Задачу решить, используя Access-SQL.
   
4. 
   !!! Предыдущую задачу решить при условии, что в один и тот же день может быть несколько приходов и расходов.
   

8.7.Оператор INSERT

Этот оператор вносит строки в таблицы.

Синтаксис
INSERT INTO <имя таблицы>

[(<имя столбца>.,..)]

<выражение запроса> | <конструктор значений таблицы>

| {DEFAULT VALUES};
Вставляемые строки являются результатом выполнения запроса или представляют собой конструкторы значений строк из списка конструкторов значений таблицы. Естественно, эти значения должны относиться к тому же типу данных, что и столбцы, в которые они вносятся. Список имен столбцов указывает на те столбцы, куда будут вставлены значения, при этом во все столбцы, которых нет в списке, автоматически будет занесено значение по умолчанию. Если в один из таких столбцов нельзя записать значение по умолчанию (например, когда установлено ограничение NOT NULL, но не определено никаких других значений), то выполнение оператора INSERT будет прервано. Если же не будет указан список имен столбцов, то во все столбцы таблицы должны быть занесены значения, предусмотренные в операторе INSERT.

Приведем синтаксис конструктора значений строки и таблицы.
Элемент конструктора::=

{выражение для вычисления значения}

| NULL | DEFAULT
конструктор значений строки::=

элемент конструктора

| (элемент конструктора.,..)

| подзапрос строки
конструктор значений таблицы::=

VALUES конструктор значений строки.,..
Если конструктор значений строки состоит из одного элемента, круглые скобки опускаются. Конструктор значений таблицы – это набор конструкторов значений строк, соответствующих группе строк.

Конструкторы значений строк должны удовлетворять следующим правилам:

• 
  Значение NULL и DEFAULT может быть определено, если конструктор значений строки входит в состав запроса, формирующего строки для оператора INSERT.
  
• 
  Если используется подзапрос, он должен относиться к строке. Другими словами, если подзапрос содержит более одной строки, возникает ошибка.
  
• 
  Если в результате исполнения подзапроса не было получено ни одной строки, значение конструктора строки становится равным NULL.
  

Пример конструктора значений строки:

(24, NULL, ‘Деметриус’)
В конструкторе значений таблицы все конструкторы значений строки должны иметь одинаковое число элементов (включая NULL), а вертикальное выравнивание столбцов должно быть таким, чтобы они содержали совместимые значения, т.е. значения каждого столбца должны соответствовать друг другу по типу данных. Пример конструктора значений таблицы:
VALUES (24, NULL, ‘Деметриус’),

(98, 77, ‘Ламарк’),

(0, 444, NULL)
В заключение приведем примеры вставки строк в таблицу. Предположим, нужно добавить Сидни Гринстрит к списку кинозвезд, занятых в фильме «Мальтийский сокол». Для этого формируется предложение:

1. 
   INSERT INTO ЗвездыВ(названиеФильма, Год, имяЗвезды)
   
2. 
   VALUES(‘Мальтийский сокол’,1942,’Сидни Гринстрит’);
   

Поскольку в строке (1) перечислены все атрибуты отношения ЗвездыВ, компоненты, принимаемые по умолчанию не нужны. Значения в строке (2) соответствуют атрибутам в строке (1) в заданном порядке. Если все атрибуты обеспечены значениями, список атрибутов, следующий за именем отношения, можно пропустить и записать просто:
INSERT INTO ЗвездыВ

VALUES(‘Мальтийский сокол’,1942,’Сидни Гринстрит’);
Однако в этом случае мы должны быть уверены, что порядок значений совпадает со стандартным порядком расположения атрибутов отношения.

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

Пример. Предположим, нужно вставить в отношение

Студия(название, адрес, Псерт#) все студии, которые упомянуты в отношении Фильмы(название, год, продолжительность, цветной, назв_студии, сертП#), но не входят в отношение Студия. Поскольку адреса и имена президентов таких студий определить невозможно, для атрибутов «адрес» и «Псерт#» вставляемых кортежей Студия приходится применять значений NULL:
INSERT INTO Студия(название)

SELECT DISTINCT назв_студии

FROM Фильмы

WHERE назв_студии NOT IN

(SELECT название

FROM Студия);
Строки (5) и (6) порождают все названия студий в отношении Студия. Строка (4) устанавливает, что в число этих студий не входит ни одной студии из отношения Фильмы. Итак, строки (2) – (6) порождают множество названий студий, входящих в Фильмы, но не в Студия. Применение DISTINCT в строке (2) гарантирует, что каждая студия входит в это множество только один раз, независимо от того, с каким числом фильмов она связана. И, наконец, строка (1) вставляет каждую из этих студий со значением NULL для атрибутов «адрес» и «Псерт#» в отношение Студия.

8.8.Операция удаления (DELETE)

Оператор DELETE удаляет строки из временных или постоянных базовых таблиц, представлений или курсоров, причем в двух последних случаях действие оператора распространяется на те базовые таблицы, из которых извлекались данные в эти представления или курсоры. Форма записи операции удаления:
DELETE FROM <имя отношения > [WHERE <условие>];
Если предложение WHERE отсутствует, удаляются все строки из таблицы или представления (представление должно быть обновляемым).

Пример. Требуется удалить из отношения Продюсер(имя, адрес, серт#, доход) всех продюсеров фильмов, чистый доход которых менее 10 млн.дол.
DELETE FROM Продюсер

WHERE доход < 10000000;

8.9.Операции обновления (оператор UPDATE)

Оператор UPDATE изменяет данные в таблице. Команда имеет следующий синтаксис
UPDATE <имя таблицы>

SET {имя столбца = {выражение для вычисления значения столбца

| NULL

| DEFAULT}.,..}

[ {WHERE <предикат>}];
Могут быть заданы значения для любого количества столбцов. В качестве альтернативы допускается определять значение в явном виде: значение NULL или значение по умолчанию (DEFAULT) для данного столбца. Ссылка на «выражение» может относиться к текущим значениям в изменяемой таблице. Это позволяет, например, умножить на 2 значения всех столбцов.
Имя столбца = имя столбца * 2
Разрешается также значения одних столбцов присваивать другим столбцам. При вычислении значений столбцов допускается использование подзапросов, выражений CASE и CAST.

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

Пример. Изменяется отношение Продюсер(имя, адрес, серт#, доход): ставится префикс През. перед именем каждого продюсера, являющегося президентом студии. Условие, предъявляемое к нужным кортежам, состоит в том, что номера сертификатов таких продюсеров должны входить в компонент сертП# некоторого кортежа из отношения Студия. Это обновление представляется выражением:
UPDATE продюсер

SET имя = ‘През.’ || имя

WHERE серт# IN (SELECT сертП# FROM Студия);
Строка (3) проверяет, является ли номер сертификата из кортежа отношения Продюсер одним из номеров сертификата президентов в отношении Студия.

Строка (2) выполняет обновление выбранных кортежей. Оператор || означает конкатенацию строк. Новая строка становится значением этого компонента – ‘През. ’ стоит перед старым значением «имя».
Задания. Записать перечисленные ниже обновления базы данных компьютеров, описанной ранее.

С помощью предложений INSERT ввести в БД информацию о том, что ПК модели 1100 сделан производителем С, имеет скорость 500, RAM 64, жесткий диск 8, 24х CD и цену 700 дол.

! Вставьте в БД информацию о том, что для каждого ПК есть ПК-блокнот, имеющий такую же скорость и RAM, диск 12х CD, при этом номер его модели превышает номер ПК на 1100, а цена превышает цену ПК на 300 дол.

Удалите ПК с жесткими дисками объемом менее 2 Гбайт.

Удалите все ПК-блокноты, выпускаемые производителями, которые не выпускают принтеры.

Производитель А купил предприятие производителя В. Измените продукты, выпущенные В, так, чтобы они стали продуктами А.

Измените объем RAM каждого ПК вдвое и добавьте к его жесткому диску объем 5 Гбайт.

! добавьте один дюйм к размеру экрана каждого ПК-блокнота, выпущенного производителем Е, и уменьшите его цену на 100 дол.