Проектирование. 380305БИ_Проектирование баз данных_лабораторные_ПоповВБ.docx. Лабораторная работа 1. Интерфейс субд mysql. Предоставление доступа и привилегий. Утилиты, входящие в состав субд цель работы
 Скачать 1.15 Mb.
|
|
Задание (общее): 1. Убедиться, что в таблице поставщиков S имеются строки с Вашими фамилиями (задание выполнялось в третьей лабораторной работе). 2. Откорректировать экранную форму, созданную в третьей лабораторной работе для работы с таблицей поставок SPJ, обеспечив возможность ввода и модификации данных. Занести произвольным образом несколько строк (5-10 строк) о поставках, связанных с Вашими фамилиями. 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. Контрольные вопросы Кто является владельцем базы данных? Какими правами обладают другие пользователи по отношению к Вашей базе данных? Какими правами обладает администратор базы данных по отношению к Вашей базе данных? Каким образом предоставляются права на пользование базой данных и отдельными ее таблицами? Каким образом изымаются права на пользование базой данных и отдельными ее таблицами? Что такое внешняя база данных? Как идентифицируется таблица внешней базы данных? Как идентифицируется таблица внешней распределенной базы данных? Пример выполнения   Вариант 4 Для каждой поставляемой для некоторого изделия детали выдать ее номер, номер изделия и соответствующее общее количество деталей.   2. Выдать номера изделий, для которых детали полностью поставляет поставщик S6.  Контрольные вопросы Кто является владельцем базы данных? Далее схема предоставления полномочий строится по следующему принципу. Каждый объект в БД имеет владельца — пользователя, который создал данный объект. Владелец объекта обладает всеми правами-полномочиями на данный объект, в том числе он имеет право предоставлять другим пользователям полномочия по работе с данным объектом или забирать у пользователей ранее предоставленные полномочия. Какими правами обладают другие пользователи по отношению к Вашей базе данных? Пользователи или группы пользователей получают права, которые определяют, какие действия они могут выполнять в базе данных. Некоторые пользователи могут только подключаться к базе данных. Это пользователи с правами только на чтение. Другие пользователи могут подключаться к базе данных и создавать наборы данных. Другие пользователи могут подключаться к базе данных и редактировать наборы данных, но не создавать или удалять их. Некоторые пользователи могут выполнять задачи администрирования, например, создавать файлы резервных копий или выполнять операцию сжатия. Какими правами обладает администратор базы данных по отношению к Вашей базе данных? Полный доступ к базе данных и всем ее объектам Каким образом предоставляются права на пользование базой данных и отдельными ее таблицами? Команды GRANT и REVOKE позволяют системным администраторам создавать пользователей MySQL, а также предоставлять права пользователям или лишать их прав на четырех уровнях привилегий: Глобальный уровень Глобальные привилегии применяются ко всем базам данных на указанном сервере. Эти привилегии хранятся в таблице mysql.user. Уровень базы данных Привилегии базы данных применяются ко всем таблицам указанной базы данных. Эти привилегии хранятся в таблицах mysql.db и mysql.host. Уровень таблицы Привилегии таблицы применяются ко всем столбцам указанной таблицы. Эти привилегии хранятся в таблице mysql.tables_priv. Уровень столбца Привилегии столбца применяются к отдельным столбцам указанной таблицы. Эти привилегии хранятся в таблице mysql.columns_priv. Что такое внешняя база данных? Текущей базой данных называется база данных, открытая с помощью операторов use Database или с помощью утилиты mysqladmin. Любая другая база данных называется внешней. Как идентифицируется таблица внешней базы данных? Для ссылки на таблицу во внешней базе данных необходимо указать имя этой базы данных как часть имени таблицы, например, salesdb:contracts, где salesdb - имя внешней базы данных, contracts - имя таблицы. Лабораторная работа №6. Представления, хранимые процедуры, функции, триггеры Цель работы: познакомиться с возможностями MySQL по работе с хранимыми процедурами, функциями, триггерами, представлениями. Представления Представления (views) можно сравнить с временными таблицами, наполненными динамически формируемым содержимым.. В настоящей реализации есть две возможности создания представлений: с использованием алгоритма временных таблиц MySQL и с созданием самостоятельной таблицы. Нас интересует именно второй способ (первый был реализован, скорее всего, исходя из соображений совместимости и унификации). Такие представления позволяют значительно снизить объём кода, в котором часто повторялись простые объединения таблиц. К ним (после создания) применимы любые запросы, возвращающие результат в виде набора строк. То есть команды SELECT, UPDATE, DELETE, можно применять так же, как и к реальным таблицам. Важно и то, что посредством представлений можно более гибко распоряжаться правами пользователей базы данных, так как в этом случае есть возможность предоставлять доступ на уровне отдельных записей различных таблиц. Создание представлений Для создания представлений используется команда CREATE VIEW Синтаксис команды CREATE VIEW CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] Пример создания и работы простейшего представления: Create View v as Select column 1 from T Insert into v Values (1) Select * from v Результат +---------+ | column1 | +---------+ | 1 | +---------+ 1 row in set (0.00 sec) Представление может быть создано на основе различных параметров предложения SELECT, при этом можно ссылаться на другие таблицы и представления. Конструкция может использовать оператор UNION и другие подзапросы. Синтаксис команды ALTER VIEW Для внесения изменений в представление используется команда ALTER VIEW ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] Синтаксис команды DROP VIEW Для удаления представления используется команда DROP VIEW VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT | CASCADE] ПРИМЕР mysql> CREATE TABLE t (qty INT, price INT); mysql> INSERT INTO t VALUES(3, 50); mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t; mysql> SELECT * FROM v; +------+-------+-------+ | qty | price | value | +------+-------+-------+ | 3 | 50 | 150 | Хранимые процедуры и функции В СУБД MySQL появилась возможность создания и хранения функций и процедур. Объявление и работа с процедурами и функциями отличаются в следующем: в заголовке функции помимо описания формальных параметров обязательно указывается тип возвращаемого ею результата; для возврата функцией значения в точку вызова среди ее операторов должен быть хотя бы один, в котором имени функции или переменной Result присваивается значение результата; вызов процедуры выполняется отдельным оператором; вызов функции может выполняться там, где допускается ставить выражение, в частности, в правой части оператора присваивания. Пользовательские функции по функциональности похожи на хранимые процедуры. Разница заключается в том, что возможностей у них меньше (в частности, они должны возвращать только одно значение, например, скалярное или табличное), но их удобнее использовать с точки зрения синтаксиса. Как процедуры, так и функции могут возвращать значения (в виде набора записей). Различие состоит в том, что функция вызывается из запроса, а процедура из отдельной команды. На настоящий момент реализация хранимых процедур не поддерживает никаких внешних языков, но (по крайней мере, так заявляется) соответствует стандарту SQL:2003, позволяющему применять условные конструкции, итерации и обработку ошибок. Пример создания хранимой процедуры в MySQL 5: CREATE PROCEDURE p () LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT 'A Procedure' <-- SELECT CURRENT_DATE, RAND() FROM t В данном случае мы создали процедуру с именем p, которая возвращает текущую дату и псевдослучайное число из таблицы t. Пример ее вызова и возвращаемого результата: mysql> call p2() +--------------+-----------------+ | CURRENT_DATE | RAND() | +--------------+-----------------+ | 2005-06-27 | 0.7822275075896 | +--------------+-----------------+ 1 row in set (0.26 sec) Query OK, 0 rows affected (0.26 sec) Чуть более сложный пример создания и использования функции: CREATE FUNCTION factorial (n DECIMAL(3,0)) RETURNS DECIMAL(20,0) DETERMINISTIC BEGIN DECLARE factorial DECIMAL(20,0) DEFAULT 1; DECLARE counter DECIMAL(3,0); SET counter = n; factorial_loop: REPEAT SET factorial = factorial * counter; SET counter = counter - 1; UNTIL counter = 1 END REPEAT; RETURN factorial; END В приложении: INSERT INTO t VALUES (factorial(pi)) SELECT s1, factorial (s1) FROM t UPDATE t SET s1 = factorial(s1) WHEREfactorial(s1) < 5 Разумеется эффективность применения хранимых процедур существенно возрастает при вызове их с параметрами (аргументами). Ниже дан пример процедуры с обработкой переданных ей параметров: CREATEPROCEDUREp1 (INparameter1 INT) BEGIN DECLARE variable1 INT; SET variable1 = parameter1 + 1; IF variable1 = 0 THEN INSERT INTO t VALUES (17); END IF; IF parameter1 = 0 THEN UPDATE t SET s1 = s1 + 1; <-- ELSE UPDATE t SET s1 = s1 + 2; ENDIF; END; Вызов процедуры теперь будет таким: mysql> CALL p2(0) // Query OK, 2 rows affected (0.28 sec) и в результате запроса мы получим: mysql> SELECT * FROM t +----+ | s1 | +----+ | 6 | | 6 | +------+ 2 rows in set (0.01 sec) Кроме условных, возможны и любые циклические конструкции: CREATE PROCEDURE p3 () BEGIN DECLARE v INT; SET v = 0; WHILE v < 5 DO INSERT INTO t VALUES (v); SET v = v + 1; END WHILE; END; Вызов процедуры: mysql> CALL p3() +------+ | s1 | +------+ ………… | 0 | | 1 | | 2 | | 3 | | 4 | +------+ Query OK, 1 row affected (0.00 sec) Также применимы итерации, переходы, словом, всё, что предполагает стандарт. Внутри функций и хранимых процедур осуществлена реализация курсоров, но, к сожалению, она пока ограничена (ASESITIVE, READ ONLY и NONSCROLL): CREATE PROCEDURE p25 (OUT return_val INT) BEGIN DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END; |