Файл: Методичка-ЛАБЫ по Поектированию ИС.doc

Варианты заданий на составление запросов по модификации информации из таблиц базы данных

Вариант 1.

1. Увеличить на 10 рейтинг всех поставщиков, рейтинг которых в настоящее время меньше, чем рейтинг поставщика S4.

2. Постройте таблицу, содержащую список номеров изделий, которые либо находятся в Лондоне, либо для них поставляются детали каким-нибудь поставщиком из Лондона.

Вариант 2.

1. Удалить все изделия, для которых нет поставок деталей.

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

Вариант 3.

1. Увеличить размер поставки на 10 процентов для всех поставок тех поставщиков, которые поставляют какую-либо красную деталь.

2. Построить таблицу с комбинациями "цвет детали-город, где хранится деталь", исключая дубликаты пар (цвет-город).

Вариант 4.

1. Построить таблицу, содержащую список номеров деталей, которые поставляются либо каким-нибудь поставщиком из Лондона, либо для какого-либо изделия в Лондон.

2. Вставить в таблицу S нового поставщика с номером S10 с фамилией Уайт из города Нью-Йорк с неизвестным рейтингом.

Вариант 5.

1. Удалить все изделия из Рима и все соответствующие поставки.

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

Вариант 6.

1. Изменить цвет красных деталей с весом менее 15 фунтов на желтый.

2. Построить таблицу с номерами изделий и городов, где они изготавливаются, такие, что второй буквой названия города является "О".

Вариант 7.

1. Увеличить на 10 рейтинг тех поставщиков, объем поставки которых выше среднего.

2. Построить таблицу с упорядоченным списком номеров и фамилиями поставщиков, поставляющих детали для изделия с номером J1.

Контрольные вопросы

1. Что такое коррелированный запрос? Чем отличается коррелированный запрос от некоррелированного?

2. Какие существуют ограничения на формирование коррелированного запроса?

3. Каким образом сохранить результаты запроса в таблице?

4. Какими средствами SQL реализуются следующие операции реляционной алгебры: ограничение, декартово произведение, выбор, пересечение, объединение, разность, соединение?

5. Что такое внешнее соединение?

6. Как реализован в SQL квантор существования?

7. Как реализован в SQL квантор всеобщности?

8. В каких случаях вместо фразы IN можно использовать операцию сравнения?

9. Какие существуют средства группирования в SQL? Как они используются?

Лабораторная работа 3 полномочия на использование схемы бызы данных. Работа с внешними схемами базы данных Цель работы

Ознакомиться со средствами предоставления полномочий на использование схем и баз данных и таблиц и основами работы с внешними базами данных.

Содержание работы и методические указания к ее выполнению

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

Представление прав реализуется оператором Grant. Оператор Grant в форме

Grant usage on schema schema-name to {public/<список users>/Group group-name }

предоставляет права указанным категориям на доступ к объектам схемы schema-name базы данных.

Hазначение опций следующее:

public - пользователи группы public;

<список users>- перечень пользователей на уровене Unix, например, sb01, sb02 и т.д.

Group group-name - пользователи группы group-name;

Оператор Grant в форме

Grant {all/insert/delete/select/update}

on {имя_таблицы/view/synonym}

to {public/<список users>}

предоставляет права на уровне отдельной таблицы. Hазначение опций следующее:

insert, delete, select, update - права на выполнение указанной операции с таблицей;

insert, delete, select, update - права на выполнение всех операций

имя таблицы, view, synonym - идентификация таблицы, представлений, синонимов.

Отнятие прав реализуется оператором Revoke. Оператор Revoke в форме

Revoke usage on schema schema-name from {public/<список users>/Group group-name }

отбтрает права у указанной категории на доступ к объектам схемы schema-name базы данных.

Оператор Revoke в форме

Revoke {all/insert/delete/select/update}

on {имя_таблицы/view/synonym}

from {public/<список users>}

отнимает права на уровне отдельной таблицы.

Та схема, имя которой зафиксировано в окне редактора SQL программы phpPgAdmin (Schema search path) является текущей. Любая другая схема базы данных называется внешней. Для ссылки на таблицу во внешней схеме базы данных (при условии, что Вам даны такие полномочия владельцем) необходимо указать имя этой схемы базы данных как часть имени таблицы, например, schema.table, где schema - имя схемы базы данных, table - имя таблицы.

Возможна и более общая форма записи:

database.schema.table

где database - имя базы данных. Однако в качестве имени базы данных может выступать только текущая база данных (та, к которой осуществлен connect). СУБД PostgreSQL не поддерживает работу с внешними базами данных.

Замечание. Ряд СУБД (например, Informix, DB2) поддерживают работу с внешними базами данных, в том числе распределенными. Текущей базой данных является база данных, к которой осуществлен connect. Любая другая база данных называется внешней. Для ссылки на таблицу во внешней базе данных необходимо указать имя этой базы данных как часть имени таблицы, например, salesdb:contracts, где salesdb - имя внешней базы данных, contracts - имя таблицы. К имени базы данных можно добавить имя сервера, т.е. сетевой машины, где запущен еще один сервер баз данных баз данных, и таким образом в случае распределенной базы данных обращение к таблице contracts базы данных salesdb, размещенной на сервере central, будет выглядеть следующим образом: salesdb@central:contracts.

Последовательность выполнения лабораторной работы:

1. Используя программу phpPgAdmin занести в таблицу поставщиков S строки с фамилиями членов бригады.

2. Занести произвольным образом в таблицу поставок SPJ несколько строк (3-5 строк) о поставках, связанных с занесенными фамилиями.

3. Выполнить два запроса к базе данных согласно номеру Вашего варианта. При выполнении запроса данные должны выбираться из таблиц Вашей собственной схемы базы данных.

4. Повторить задание п. 3 с той разницей, что сведения о номенклатуре деталей и изделий (таблицы P и J) должна браться из собственной схемы базы данных, а сведения о поставщиках и поставках (таблицы S и SPJ) должны браться из схемы базы данных соседней бригады. Предварительно необходимо узнать имя этой схемы базы данных. Убедитесь в невозможности выполнения задания.

5. Обеспечьте, чтобы владелец используемой Вами внешней схемы базы данных предоставил Вам полномочия на просмотр используемых Вами таблиц в его схеме базы данных, дав соответственно ему такие же полномочия для выполнения аналогичных действий.

6. Повторите задание п. 4. Сравните результаты с результатами, полученными в п. 3.

7. Сделайте попытку изменить информацию о поставщиках-владельцах схемы базы данных (город, рейтинг и т.д.) в таблице S внешней схемы базы данных. Убедитесь в невозможности выполнения задания.

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

9. Повторите задание п. 7. Проверьте успешность выполнения действий.

10. Дождавшись, когда владелец внешней схемы базы данных закончит выполнение п. 9, сделайте попытку удалить из таблицы S используемой Вами внешней схемы базы данных поставщиков с именами, принадлежащими владельцам схемы базы данных, и связанные с ними поставки из таблицы SPJ. Убедитесь в невозможности выполнения задания.

11. Обеспечьте, чтобы владелец используемой Вами внешней схемы базы данных предоставил Вам полномочия на удаление из используемых Вами таблиц в его схеме базы данных, дав соответственно ему такие же полномочия для выполнения аналогичных действий.

12. Повторите задание п. 10. Проверьте успешность выполнения действий.

13. Отнимите предоставленные Вами права на пользование Вашей схемы базы данных.

Варианты заданий

Вариант 1.

1. Выдать список всех поставок, в которых количество деталей находится в диапазоне от 300 до 750 включительно.

2. Получить номера изделий, использующих по крайней мере одну деталь, поставляемую поставщиком S6.

Вариант 2.

1. Выдать цвета деталей, поставляемых поставщиком S6.

2. Получить номера и фамилии поставщиков, поставляющих деталь Р1 для какого-либо изделия в количестве, большем среднего объема поставок детали Р1 для этого изделия.

Вариант 3.

1. Выдать названия изделий, для которых поставляются детали поставщиком S6.

2. Получить номера и названия изделий, для которых поставщик S6 поставляет несколько деталей каждого из поставляемых им типов.

Вариант 4.

1. Для каждой поставляемой для некоторого изделия детали выдать ее номер, номер изделия и соответствующее общее количество деталей.

2. Получить номера изделий, для которых детали полностью поставляет поставщик S6.

Вариант 5.

1. Выдать номера и фамилии поставщиков, поставляющих детали для какого-либо изделия с деталью P1 в количестве, большем, чем средний объем поставок детали P1 для этого изделия.

2. Получить номера изделий, использующих только детали, поставляемые поставщиком S6.

Вариант 6.

1. Выдать общее число изделий, для которых поставляет детали поставщик S6.

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

Вариант 7.

1. Выдать общее количество деталей P1, поставляемых поставщиком S6.

2. Получить номера и фамилии поставщиков, поставляющих по крайней мере одну деталь, поставляемую по крайней мере одним поставщиком, который поставляет по крайней мере одну красную деталь.