Метод_указания (Задания по бд), страница 4

1) select count(*) from poss

where gok='96' [F5]

В нижней области окна будет представлена цифра, равная общему количеству выпускников 1996 года в России (см. рис. 23).

2) select vuz.uch_zavedenie, count(*)

from poss join vuz on vuz_k=vuz.cod

where gok='96'

group by vuz.uch_zavedenie [F5]

Отчет будет представлен в нижней области окна (см. рис. 24).

Рис. 23

Рис. 24

Третий вариант запроса к базе данных

Выдать статистику по учащимся во всех городах России. Отчет должен начинаться с общей цифры - всего учащихся по России. Далее - таблица, включающая перечень названий городов с цифрами, не равными нулю (цифра - это количество учащихся в указанном городе; если количество учащихся в городе равно нулю, то этот город в отчет не включается).

Этот запрос выполняется аналогично запросу второго варианта, за исключением того, что вместо названий учебных заведений необходимо в отчете выдать названия городов. Для этого нужно вместо соединения таблиц poss и vuz по полю код учебного заведения выполнить соединение таблиц poss и vuz_gorod по полю код учебного заведения, так как поле "название города" принадлежит структуре таблицы vuz_gorod.

Поэтому здесь необходимо выполнить два следующих запроса:

1) выполнить запрос без условия выборки, используя в списке выборки агрегатную функцию count(*) для подсчета общего количества учащихся России;

2) выполнить запрос с условием соединения таблиц poss и vuz_gorod по полю код учебного заведения, используя предложение group by для группирования значений по полю "название города" и агрегатную функцию count(*) в списке выборки для оценки количества значений (названий городов) в каждой группе.

Таким образом, в верхней области окна необходимо ввести:

1) select count(*) from poss [F5]

В нижней области окна будет представлена цифра, равная общему количеству учащихся в России (см. рис. 25).

Рис. 25

2) select vuz_gorod.nazvanie_goroda, count(*)

from poss join vuz_gorod on vuz_k=vuz_gorod.cod_vuza

group by vuz_gorod.nazvanie_goroda [F5]

Отчет будет представлен в нижней области окна (см. рис. 26).

Рис. 26

Возможности SQL Query Analyser в плане выполнения сложных запросов к БД весьма ограничены (ограничения связаны с трудоемкостью набора длинных предложений SELECT и с реляционной ограниченностью языка SQL). Одним из способов преодоления ограничений является использование хранимых процедур, описанных в конспекте курса.

Для выполнения сложного запроса требуется, как правило, набирать очень длинное предложение SELECT (несколько предложений SELECT). На это необходимо довольно большой промежуток времени. Если сложный запрос делается очень часто, то его предложение SELECT (предложения SELECT) можно оформить как хранимую процедуру (ввести один раз), а затем для выполнения этого запроса запустить эту процедуру.

Для выполнения одномерных статистических запросов в SQL Query Analyser требуется задавать два предложения SELECT и получать два результата. Чтобы объединить оба результата в один отчет, необходимо задать оба предложения SELECT в одной хранимой процедуре.

Пример создания процедуры (смотри первый вариант запроса):

create procedure zapr1 as

select count(*) from poss where gp='90' or gp='91'

select spec, count(*) from poss

where gp='90' or gp='91' group by spec [F5]

Для выполнения этого запроса нужно запустить эту процедуру, т.е. в верхней области окна необходимо ввести:

zapr1 [F5]

Отчет будет представлен в нижней области окна (см. рис. 27).

Рис. 27

Замечание. Если во время набора команды create procedure будут допущены синтаксические ошибки, то после нажатия [F5] прекомпилятор в нижней половине окна выдаст сообщение об ошибке.

В этом случае процедура не будет загружена в БД. Убедиться в том, что процедура загружена в БД, можно с помощью команды sp_help.

Варианты заданий на выполнение лабораторной работы № 5 приведены в приложении IV. Задание необходимо выполнить двумя способами: 1) в диалоговом SQL; 2) с помощью хранимой процедуры.

ЛАБОРАТОРНАЯ РАБОТА № 6

Цель:

1. Практическое освоение операторов grant/revoke, union и create view для получения доступа к защищенным данным.

Справочный материал:

1. Конспект курса.

Упражнения:

Перед выполнением лабораторной работы необходимо войти в сеть, затем в систему MS SQL Server и SQL Query Analyser, а также установить связь пользователя с базой данных (аналогично описанному сценарию в лабораторной работе № 1).

1. Создать входные имена SQL-сервера VAF и APB из БД master (имя sa).

Для этого в верхней области окна необходимо ввести:

sp_addlogin VAF, teacher, stud1 [F5]

sp_addlogin APB, freedom, stud1 [F5]

1. Создать пользователей БД MPEI1 из БД MPEI1 (имя STUD1).

Для этого в верхней области окна необходимо ввести:

sp_adduser VAF [F5]

sp_adduser APB [F5]

3) Предоставить командное полномочие на выполнение команды create table пользователю VAF. Для этого в верхней области окна необходимо ввести:

grant create table to VAF [F5]

4) Предоставить объектное полномочие на выполнение команд select, update, insert в таблице, созданной пользователем STUD1, пользователю АРВ. Для этого в верхней области окна необходимо ввести:

grant select, update, insert, delete

on stud1.poss to APB [F5]

5) Выполнить запрос по двум информационным таблицам (например: poss и ukr): выдать данные по учащимся России и Украины, заканчивающим учебу в 95, 97 и 98 годах.

Для того, чтобы выполнить этот запрос, например пользователю АРВ, который имеет объектное полномочие на работу с таблицей poss, но не имеет полномочий на таблицу ukr, необходимо:

а) владельцу БД stud1 (STUD1) создать и загрузить таблицу с именем ukr (по аналогии с таблицей poss);

б) предоставить объектное полномочие на выполнение команды select в таблице ukr пользователю АРВ, т.е. ввести команду:

grant select on stud1.ukr to APB [F5]

в) выйти из SQL Query Analyser (disconnect) и войти в SQL Query Analyser с именем APB;

г) так как таблицы poss и ukr имеют одинаковую структуру, то для этого запроса можно использовать оператор union, т.е. ввести команду:

select * from stud1.poss where gok in ('95', '97', '98')

union

select * from stud1.ukr where gok in ('95', '97', '98') [F5]

В нижней области окна будет предоставлен отчет (см. рис. 28).

Рис. 28

д) отменить предоставленное пользователю АРВ полномочие на таблицу ukr, т.е. выйти из SQL Query Analyser (disconnect), войти в SQL Query Analyser с именем STUD1 и ввести команду:

revoke select on ukr from APB [F5]

6) Создать представление с именем pr1 на основе таблицы ukr.

Допустим, что пользователь АРВ не знает имена полей таблицы ukr, но знает, что структура этой таблицы аналогична структуре таблицы poss. Для работы с таблицей ukr ему необходимо создать следующее представление:

create view pr1

(nomer1, fio1, data_rogden1, pol1, sem_polog_k1,

spec1, kat_obuch_k1, vuz_k1, mp1, gp1, mo1, gok1)

as select * from ukr [F5]

Замечание. Предварительно пользователю АРВ владельцем БД STUD1 должны быть предоставлены полномочия на использование команды create view и команды select в таблице ukr:

grant create view to APB [F5]

grant select on ukr APB [F5]

7) Предоставить вспомогательную таблицу vuz_gorod и четыре таблицы словаря БД в общее пользование. Для этого необходимо ввести следующие команды grant для каждой таблицы (команды вводятся владельцем БД STUD1):

grant all on vuz_gorod to public [F5]

grant all on vuz to public [F5]

grant all on family to public [F5]

grant all on kat_obuch to public [F5]

grant all on gorod to public [F5]

Задание:

1. Из БД master (входное серверное имя sa, пароль compute) создать 2 входных серверных имени, имена произвольны;

2. Используя эти имена, в своей БД (выйти из БД master и войти в свою) создать двух пользователей вашей БД;

3. Предоставить первому пользователю командное полномочие (произвольное), а второму – объектное (тоже произвольное);

4. Создать представление на основе сложного оператора SELECT (типа оператора SELECT из лабораторной работы №3 или №4).

ЛАБОРАТОРНАЯ РАБОТА № 7

Цель:

1. Знакомство с использованием контрольных ограничений и правил для поддержки целостности данных в БД.

Справочный материал:

1. Конспект курса.

Упражнения:

Перед выполнением лабораторной работы необходимо войти в сеть, затем в систему MS SQL Server и SQL Query Analyser, а также установить связь пользователя с базой данных (аналогично описанному сценарию в лабораторной работе № 1).

Самым простым способом поддержки целостности данных является создание правил или контрольных ограничений, причем двух типов:

- на уровне поля;

- на уровне таблицы.

(см. конспект курса).

1) Создание контрольных ограничений на уровне поля.

Рассмотрим структуру таблицы poss:

а) поле номер (nomer) - контрольным ограничением для него является промежуток чисел от 1 до 700000;

б) поле фамилия, имя, отчество (fio) - контрольным ограничением для него является запрет использования символов "." (точка) и "-" (минус);

в) поле дата рождения (data_rogden) разбивается на три подполя:

1. день рождения (d_rogd) - контрольным ограничением является промежуток символов от "01" до "31";

2. месяц рождения (m_rogd) - контрольным ограничением является промежуток символов от "01" до "12";

3. год рождения (g_rogd) - контрольным ограничением является промежуток символов от "00" до "99";

г) поле пол (pol) - контрольным ограничением для него является использование либо символа "М", либо символа "Ж";

д) поле код семейного положения (sem_polog_k) - контрольным ограничением для него является список из четырех значений "0", "1", "2", "3";

е) поле специальность (spec) - контрольным ограничением для него является промежуток символов от "000000" до "190046";

ж) поле код категории обучения (kat_obuch_k) - контрольным ограничением является промежуток символов от "01" до "17";

з) поле код учебного заведения (vuz_k) - контрольным ограничением является промежуток чисел от 128955 до 8199999;

и) поле месяц приема (mp) - контрольным ограничением является промежуток символов от "01" до "12";

к) поле год приема (gp) - контрольным ограничением является промежуток символов от "00" до "99";

л) поле месяц окончания (mo) - контрольным ограничением является промежуток символов от "01" до "12";

м) поле год окончания (gok) - контрольным ограничением является промежуток символов от "00" до "99".

На основе перечисленных для каждого поля структуры таблицы poss контрольных ограничений можно создать таблицу с именем poss1. Для этого в верхней области окна необходимо ввести:

create table poss1

(nomer integer check (nomer between 1 and 700000),

fio char (40) not null check (fio not like '%.%' or fio not like '%-%'),

d_rogd char(2) check (d_rogd between '01' and '31'),

m_rogd char(2) check (m_rogd between '01' and '12'),

g_rogd char(2) check (g_rogd between '00' and '99'),