Задания по бд (Метод_указания)

Посмотреть архив целиком

72





УДК



УДК


Утверждено учебным управлением МЭИ


Рецензент: к.т.н., доцент Зубов В.С.


Подготовлено на кафедре прикладной математики


Лабораторный практикум по курсу “Базы данных”. Федин В.А., Бурцев А.П., Мотина Н.А., Рогова Н.Г. – М.: Изд-во МЭИ, 2002. – с.



Методические указания содержат



Указания предназначены






______________















Московский Энергетический институт, 2002 г.

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


Цель:

  1. Приобрести практический навык в создании таблиц БД и загрузки их данными.

  2. Освоить создание индексов.


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

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


Используемые технические средства:

1. SQL-сервер, работающий на компьютере Pentium II под управлением операционной системы Windows 2000 Advanced Server.

2. SQL Query Analyser, работающий на компьютере Pentium под управлением операционной системы Windows 2000 Professional.


Упражнения:

Сценарий входа в систему:

I Вход в сеть.

После загрузки системы WINDOWS 2000 перед пользователем высветится окно с названием “Вход в Windows”.В этом окне необходимо ввести логин и пароль работы с сетью (их необходимо получить у преподавателя). В результате этого на экране появится набор пиктограмм различных WINDOWS-приложений.


II Вход в систему MS SQL Server и диалоговой SQL:

Нажать кнопку “Пуск” и в появившемся меню выбрать пункт “Программы”; при этом появится следующее меню, в котором необходимо найти пункт с названием “Microsoft SQL Server” и выбрать его; в результате этого появляется третье меню (см. рис. 1).

В этом меню необходимо найти пункт с названием программы “SQL Query Analyser” и выполнить на нем один щелчок мыши.


III Установка связи пользователя с базой данных:

На экране появится диалоговое окно Connect to SQL Server.

В этом окне пользователю нужно ввести:

  1. Имя сервера (M708-SQL - выбрать из списка имен серверов и щелкнуть левой кнопкой мыши).

  2. Имя пользователя (например: STUD1 [Tab]);

  3. Пароль (например: stud1 [Tab]); (см. рис. 2)

Рис. 1

Рис. 2

Для перехода к вводу следующего имени или пароля используется клавиша Tab. Если все имена и пароль введены правильно, то пользователь получает доступ к основному окну Microsoft SQL Server Query Analyser, состоящее из двух областей (см. рис. 3).

Рис. 3

В левой области отображается структура таблиц. Правая область предназначена для ввода SQL-команд, результаты выполнения запросов отображаются по мере получения информации в специальном окне. В частности, в верхней области для выполнения запросов к БД вводятся SELECT-предложения, а в нижней - выдаются отчеты по этим запросам. По окончании ввода команды в верхней области для ее выполнения нажимается клавиша [F5]. Если команда не помещается в одну строчку, то для переноса нажимается [Enter].

Если SQL-команда вводится неверно, то в нижней области окна появляется сообщение SQL-сервера об ошибке.

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


1) Создание таблицы с именем poss.

В приложении VI приведена структура информационного объекта "Учащиеся России", состоящая из 12 полей. На основе характеристик каждого из приведенных полей в верхней области окна необходимо ввести следующую команду create table:

create table poss

(nomer integer,

fio char (40) not null,

data_rogden char (6),

pol char (1),

sem_polog_k char (1),

spec char (6),

kat_obuch_k char (2),

vuz_k integer,

mp char (2),

gp char (2),

mo char (2),

gok char (2)) [F5]

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

Если команда create table выполнится успешно, то в нижней области окна появится сообщение “The command(s) completed successfully”. Чтобы убедиться в том, что таблица создана в БД, необходимо в верхней области окна ввести команду sp_help [F5]. При этом в нижней области окна появится список всех таблиц БД.

Аналогично создаются вспомогательная таблица "вузы-города-страны СНГ" и четыре таблицы словаря БД, структуры которых также приведены в приложении VI.

Для создания этих таблиц в верхней области окна необходимо последовательно вводить следующие команды create table:

2) Создание таблицы с именем vuz_gorod:

create table vuz_gorod

(cod_vuza integer,

nazvanie_vuza char (25),

cod_goroda char (3),

nazvanie_goroda char (20),

cod_stran char (2),

nazvanie_stran char (15)) [F5]

3) Создание таблицы с именем vuz:

create table vuz

(cod integer,

uch_zavedenie char (25)) [F5]

4) Создание таблицы с именем family:

create table family

(cod char (1),

family_status char (12)) [F5]

5) Создание таблицы с именем kat_obuch:

create table kat_obuch

(cod char (2),

kat_obucheniya char (16)) [F5]

6) Создание таблицы с именем gorod:

create table gorod

(cod char (3),

nazvanie_goroda char (20)) [F5]

Если все эти команды будут выполнены без ошибок, то с помощью команды sp_help можно вывести список названий всех созданных таблиц (см. рис. 4).


Рис. 4

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

7) Загрузка таблицы poss.

В таблицу poss загружается 2000 записей; с помощью оператора insert (первая форма) можно загрузить только одну запись. Поэтому для загрузки таблицы poss требуется 2000 операторов insert (первая форма). Если их задавать "вручную", то это займет очень много времени.

Чтобы значительно ускорить процесс загрузки, необходимо на дискете подготовить файлы, каждый из которых содержит набор пакетов. Каждый пакет содержит одну команду insert и признак конца пакета - go (см. конспект курса). Количество пакетов в файле ограничено (порядка 200-250), так как ограничен по памяти буфер ввода команд (операторов, пакетов) SQL Query Analyser.

Таким образом, для загрузки таблицы poss в БД пользователю необходимо:

а) вставить дискету с подготовленными файлами в дисковод;

б) с помощью меню “File” последовательно открыть файлы (команда меню Open) на дискете и, тем самым, переписывать их в верхнюю область окна;

в) запустить на выполнение полученный в верхней области окна набор пакетов, нажав клавишу [F5].

Остальные таблицы БД загружаются аналогично. Чтобы убедиться, что таблицы загружены данными, необходимо в верхней области окна ввести команду: SELECT * FROM <имя таблицы> [F5] для каждой таблицы, например: SELECT * FROM kat_obuch [F5].

В нижней области окна будет распечатана таблица kat_obuch.

8) Создание индексов для таблицы poss.

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

а) номер;

б) пол;

в) код семейного положения;

г) специальность;

д) код категории обучения;

е) код учебного заведения;

ж) год приема;

з) год окончания.

Индексы создаются с помощью команды create index, причем для поля "номер" создается уникальный индекс, а для остальных полей - простые негрупповые индексы.

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

а) create unique index nomind on poss (nomer) [F5]

б) create index polind on poss (pol) [F5]

в) create index semind on poss (sem_polog_k) [F5]

г) create index specind on poss (spec) [F5]

д) create index katind on poss (kat_obuch_k) [F5]

е) create index vuzind on poss (vuz_k) [F5]

ж) create index gpind on poss (gp) [F5]

з) create index goind on poss (gok) [F5]

Информацию о созданных индексах можно посмотреть с помощью команды sp_helpindex <имя таблицы>.

9) Создание индексов для остальных таблиц БД.

Для вспомогательной таблицы "вузы-города-страны СНГ" индексы создаются для трех ключевых полей:

а) код учебного заведения:

б) код города;

в) код страны.

Для поля "Код учебного заведения" создается уникальный индекс. В верхней области окна необходимо последовательно ввести три команды:

а) create unique index codvgind on vuz_gorod (cod_vuza) [F5]

б) create index codgoind on vuz_gorod (cod_goroda) [F5]

в) create index codvsind on vuz_gorod (cod_stran) [F5]

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

а) create unique index codsind on family (cod) [F5]

б) create unique index codkind on kat_obuch (cod) [F5]

в) create unique index codgind on gorod (cod) [F5]

г) create unique index codvind on vuz (cod) [F5]

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

Примеры команды drop (см. конспект курса):

1. drop table poss

2. drop index poss.nomind

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

truncate table poss

В качестве заданий к данной лабораторной работе пользователям предлагается создать таблицы БД, соответствующие другим странам СНГ (Украина, Белоруссия и др.) и имеющие такую же структуру, что и таблица poss. Далее, каждому пользователю необходимо загрузить свою таблицу данными с дискеты, а также создать индексы для своей таблицы.


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


Цель:

  1. Научиться использовать предложения оператора SELECT и некоторые функции для выполнения простых запросов к БД в режиме диалогового (интерактивного) SQL.

  2. Приобрести практические навыки в использовании операторов INSERT, UPDATE и DELETE.


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

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


Упражнения:

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

1) Выполнение запроса по одной таблице БД, например: выдать названия всех городов России (исключая дубликаты).

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

select distinct nazvanie_goroda from vuz_gorod where cod_stran='01' [F5]

где '01' - это код России.

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

Замечание: Если в команде будет допущена ошибка, то вместо отчета будет выдано окно с сообщением об ошибке (см. рис.5).

2) Выполнение запроса по одной таблице БД с использованием функции BETWEEN, например:

выдать фамилии всех студентов и студентов-заочников России.

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

Рис. 5

select fio from poss where kat_obuch_k between '03' and '04' [F5]

где '03' и '04' - это коды, соответствующие категориям обучения "студент" и "студент-заочник".

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

3) Выполнение запроса по одной таблице БД с использованием функции IN, например:

выдать данные по учащимся России, закончившим учебу в 95, 97, 98 годах.

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

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

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

4) Выполнение запроса по одной таблице БД с использованием функции LIKE, например:

выдать сведения по учащимся России с фамилией КУТЕПОВ (А).

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

select * from poss where fio like '%КУТЕПОВ%' [F5]

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

5) Выполнение запроса по одной таблице БД с использованием функции NULL, например:

выдать данные по учащимся России, у которых отсутствует учетный номер.

Рис. 6

Рис. 7

Рис. 8

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

select * from poss where nomer is null [F5]

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

Замечание: Функции BETWEEN, IN, LIKE, NULL могут использоваться с операцией NOT.

Рис. 9

6) Выполнение запроса по одной таблице БД с использованием сложного условия, например:

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

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

select * from poss where (gp='87' or gp='93') and not spec='000101' [F5]

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

Рис. 10

7) Выполнение запроса по одной таблице БД с сортировкой записей, например:

выдать коды и названия всех учебных заведений Белоруссии, отсортировав записи по возрастанию кодов.

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

select cod_vuza, nazvanie_vuza from vuz_gorod where cod_stran='03' order by cod_vuza [F5]

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

Замечание: Во фразе order by вместо имени поля (cod_vuza) можно использовать порядковый номер позиции этого поля в списке выборки (для cod_vuza этот номер равен 1), т.е. можно указать order by 1. Другими словами, запрос будет выглядеть так:
select cod_vuza, nazvanie_vuza

from vuz_gorod

where cod_stran='03' order by 1

Рис. 11

8) Выполнение запроса и выдача количества записей по одной таблице БД с использованием функции COUNT(*), например:

выдать количество учебных заведений России.

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

select count(*) from vuz_gorod where cod_stran='01' [F5]

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

Замечание: Для использования агрегатных функций AVG и SUM необходимо в структуре таблицы иметь поля числового типа, например: количество учащихся. Агрегатные функции count, min, max могут быть применены к полям любого типа.

9) Выполнение запроса по одной таблице БД с группировкой записей, например:

выдать названия городов и учебных заведений России, сгруппировав записи по городам и учебным заведениям.

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

Рис. 12

select nazvanie_goroda, nazvanie_vuza from vuz_gorod where cod_stran='01' group by nazvanie_goroda, nazvanie_vuza [F5]

Отчет будет представлен в нижней части окна (см. рис. 13). Помимо отображения результатов выполнения запроса в табличном виде SQL Query Analyser позволяет просматривать графическое отображение процесса выполнения запроса (см. рис. 14). Чтобы его увидеть, необходимо выбрать пункт меню “Query”, а затем “Show Execution Plan” или нажать Ctrl+K.

Рис. 13

Рис. 14

10) Выполнение запроса по одной таблице БД с использованием фразы HAVING, например:

выдать учетные номера учащихся, встречающиеся больше одного раза.

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

select nomer from poss group by nomer having count(*)>1 [F5]

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

Упражнения на использование операторов update, insert и delete.

11) Установка в качестве значения поля fio ваших фамилии, имени и отчества вместо ФИО учащегося с минимальным учетным номером.

Это делается за 3 шага:

Определение минимального учетного номера учащегося.

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

select min (nomer) from poss [F5]

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

Изменение значения поля fio в записи с найденным номером.

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

update poss set fio='<фамилия> <имя> <отчество>' where nomer=<минимальный номер> [F5]