Методические указания к выполнению лабораторных и курсовой работ ЙошкарОла 2007
 Скачать 0.8 Mb.
|
|
МИНИСТЕРСТВО ОБЩЕГО И ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ РОССИЙСКОЙ ФЕДЕРАЦИИ РАБОТА С БАЗАМИ ДАННЫХ В АРХИТЕКТУРЕ КЛИЕНТ-СЕРВЕР Методические указания к выполнению лабораторных и курсовой работ  Йошкар-Ола 2007 Дано описание пяти лабораторных работ, связанных с доступом к SQL-ориентированным базам данных в архитектуре клиент-сервер. Первые две работы ориентированы на изучение языка баз данных SQL, последние три – программного интерфейса ODBC для разработки клиентских приложений баз данных. Для каждой лабораторной работы приводятся необходимые теоретические сведения, порядок выполнения, методические указания и варианты заданий. При выполнении лабораторных работ используется CУБД MS SQL Server 2000, хотя может использоваться любая другая SQL-ориентированная СУБД. Методические указания подготовлены на кафедре “Вычислительная техника” и предназначены для студентов специальности 22.01, изучающих дисциплину “Базы данных”. Введение В настоящее время все большее распространение получают информационные системы с архитектурой клиент-сервер [6, 15-16]. Основой таких систем являются SQL-ориентированные СУБД, выполняемые на выделенных серверах (SQL-серверах). Причинами роста популярности клиент-серверных систем являются успехи в области телекоммуникаций, высокая производительность систем данного класса, их большая информационная емкость, высокая надежность, безопасность, возможность одновременной работы с одним и тем же источником данных нескольких пользователей, легкость реконфигурации системы, позволяющая возможность поэтапного наращивания ресурсов базы данных и подключения новых пользователей, возможность распределенной обработки и работа с разнородными источниками данных. Последнее свойство определяется использованием в системе единого языкового стандарта для работы с базами данных – языка SQL [2,4-5,8-9]. Многие компьютерные фирмы производят программное обеспечение,“превращающее” обычный компьютер в SQL-сервер. К наиболее известным и распространенным SQL-серверам относятся: Microsoft SQL Server [7,10-14], Oracle Server, Sybase SQL Server, Informix Online, Watcom SQL Network Server, XDB-Enterprise, Gupta SQLBase Server, DB2, Borland InterBase Workgroup Server, Progress и другие. Стандарт SQL определяет подъязык данных, который используется в контексте другого языка, называемого включающим, для того, чтобы выполнять специализированные задачи управления базами данных. Такая взаимосвязь между включающим языком и подъязыком данных SQL называется стилем связывания. SQL-92 определяет три типа связывания: встроенный SQL, модульный язык и непосредственный вызов. В 1995 году американские и международные комитеты, ответственные за стандарт языка SQL, завершили работу над спецификацией нового стиля связывания, названного Call Level Interface (SQL/CLI – интерфейс уровня вызовов) [3,16]. SQL/CLI был одобрен как международный стандарт официально названный “ISO/IEC 9075-3:1995, Information Technology – Database Languages – SQL – Part 3, Call Level Interface (SQL/CLI)”. Преимущества SQL/CLI: независимость клиентских приложений от СУБД, возможность выполнения параллельных операций над базой данных, возможность параллельной обработки множества транзакций. SQL/CLI представляет собой множество функций, которые может использовать приложение для доступа к базам данных SQL. Оно содержит функции для запроса на выделение и на отказ от выделения ресурсов, функции для подключения к SQL-серверам и отключения от них, функции для исполнения операторов языка SQL, а также функции получения диагностической информации, управления завершением транзакций и получения информации о данной реализации. Системная модель SQL/CLI состоит из трех компонентов – приложения, реализации CLI и SQL-сервера. Приложение производит вызовы функций, определяемых SQL/CLI. Реализация SQL/CLI – это библиотека стадии исполнения, которая реализует функции CLI и связывается с данным приложением. Реализация CLI обращается к SQL-серверу, который обрабатывает операторы SQL. Компания Microsoft Corp. разработала инструментарий для разработки программного обеспечения, основанный на расширенной версии SQL/CLI и получивший название Open DataBase Connectivity (ODBC) [1,6,14,16]. ODBC обеспечивает общий API-интерфейс для доступа к самым разнообразным базам данных. Архитектура ODBC имеет четыре основных компонента: приложение, менеджер драйверов, драйвер и источник или источники данных. Центральным компонентом является менеджер драйверов. Механизм ODBC может работать практически в любой операционной системе. Несмотря на наличие других, более высоко-уровневых методов доступа к базам данных, таких как DAO и RDO, а также стремительное развитие и внедрение новых методов (ADO, OLE DB), ODBC прочно занимает свою нишу в общем ряду методов доступа к базам данных. “Фундаментальность” ODBC заключается в том, что он опирается на международный стандарт SQL/CLI. Программирование с помощью ODBC значительно сложнее, но выигрыш в скорости может быть существенным. Методические указания включают описание пяти лабораторных работ. Первые две работы ориентированы на изучение языка баз данных SQL, последние три – программного интерфейса ODBC для разработки клиентских приложений баз данных. Для каждой лабораторной работы приводятся необходимые теоретические сведения, порядок выполнения, методические указания и варианты заданий. Целью первой лабораторной работы является изучение спецификации запроса языка SQL, приобретение практических навыков составления и содержательной интерпретации запросов выборки данных, а также их выполнения на SQL-сервере с использованием клиентских утилит. В данной лабораторной работе используется готовая база данных, отражающая деятельность некоторой книготорговой компании. Используемая база данных состоит из четырех таблиц, структура и семантика которых представлена в описании работы. Содержимое таблиц приведено в приложении. Приведено 96 вариантов SQL-запросов и 96 вариантов запросов на естественном языке к данной базе данных. Запросы к базе данных имеют различную степень сложности и покрывают большинство возможностей языка SQL. Целью второй работы является изучение языков описания данных и манипулирования данными, входящими в SQL, приобретение навыков составления SQL-запросов для создания, удаления и модификации объектов базы данных, включая таблицы, представления и индексы, изучение операторов манипулирования данными, в том числе позиционных, основанных на использовании курсоров, а также триггеров, пользовательских функций и хранимых процедур. В данной работе также изучаются ограничения целостности таблицы, а именно ограничения уникальности и проверочные ограничения. При выполнении первых двух лабораторных работ рекомендуется использовать клиентские утилиты Query Analyzer и SQL Enterprise Manager (SQL-EM), входящими в состав MS SQL Server 2000. Последние три лабораторные работы нацелены на изучение ODBC API для создания клиентских приложений баз данных. Особенностью данных работ является их тесная взаимоподчиненность: следующая работа строится на результатах предыдущей. При выполнении лабораторных работ, связанных с доступом к базе данных с использованием ODBC, для разработки клиентских приложений рекомендуется использовать систему программирования Visual C++, хотя возможно использование и других систем, например, Visual Basic. Лабораторная работа N 1 Выборка данных из базы данных с использованием языка SQL Цель работы: изучить принципы работы с базой данных в архитектуре клиент-сервер, изучить спецификации запроса языка баз данных SQL, получить практические навыки составления и содержательной интерпретации запросов выборки данных (операторов SELECT), а также их выполнения на SQL-сервере с использованием клиентских утилит. Порядок выполнения работы 1. Изучить структуру и элементы SQL-запроса выборки, в том числе разделы FROM, WHERE, GROUP BY, HAVING, ORDER BY, а также предикаты условия поиска и агрегатные функции. 2. Изучить операции реляционной алгебры (соединение, пересечение, объединение, разность и др.). 3. Изучить утилиту Query Analyzer, входящую в набор клиентских утилит для СУБД SQL Server. 4. Изучить состав базы данных книготорговой компании (база данных pubs), структуру и семантику ее таблиц. 5. Получить у преподавателя номер варианта задания. 6. В соответствии с вариантом задания типа А произвести содержательную интерпретацию заданных SQL-запросов, выполнить их на SQL-сервере с использованием клиентских утилит Query Analyzer или SQL Enterprise Manager (SQL-EM), проинтерпретировать результаты выполнения запросов. 7. В соответствии с вариантом задания В составить SQL-запросы по их заданному содержательному описанию, выполнить SQL-запросы на SQL-сервере с использованием клиентских утилит Query Analyzer или SQLEM, проинтерпретировать результаты выполнения запросов. 8. Оформить отчет. Содержание отчета 1) Титульный лист; 2) цель работы; 3) тексты SQL-запросов и их содержательная интерпретация; 4) результаты выполнения запросов по заданиям типа А и В и их интерпретация; 5) выводы. Основные сведения Язык SQL Первый международный стандарт языка SQL был принят в 1989 г. (SQL/89). В конце 1992 г. Был принят новый международный стандарт SQL/92. “Родным” языком Microsoft SQL Server является язык Transact- SQL (T-SQL), являющийся диалектом стандартного языка SQL. T-SQL поддерживает большинство возможностей языков SQL/89 и SQL/92, а также ряд расширений, увеличивающих возможность программирования и гибкость языка. В частности, в язык T-SQL добавлены конструкции для задания последовательности операций управления в программе (например, if и while), локальных переменных и других конструкций, позволяющих писать более сложные запросы и строить программные объекты, хранящиеся на сервере, в том числе процедуры и триггеры. Язык SQL включает следующие языки: • язык определения данных (Data Definition Language или DDL), предназначенный для добавления, модификации и удаления данных в таблицах; • язык модификации данных (Data Modification Language или DML), предназначенный для добавления, модификации и удаления данных в таблицах. В синтаксических конструкциях при описании языка будут использоваться следующие соглашения. Нетерминальные элементы заключаются в угловые скобки <>. Необязательная конструкция заключается в квадратные скобки []. Запись вида {A}… означает повторение конструкции А произвольное число раз (включая нулевое). Вертикальные разделители | читаются как “ИЛИ” и служат для выбора одной из конструкций, заключенных в скобки. Оператор SELECT Оператор SELECT используется для запросов к базе данных и выборки результатов. Синтаксис оператора SELECT следующий: <оператор SELECT>::= SELECT [ALL | DISTINCT] <список выборки> <табличное выражение> ORDER BY <спецификация сортировки>] <табличное выражение>::= FROM <имя таблицы>[{,<имя таблицы>}…] [WHERE <условие поиска>] [GROUP BY <имя столбца> [{,<имя столбца>}…] [HAVING <условие поиска>] Если задано ключевое слово DISTINCT, то из результирующей таблицы удаляются повторяющиеся строки. Список выборки определяет, какие столбцы должны быть возвращены в результирующую таблицу. Данный список представляет список арифметических выражений над значениями столбцов таблиц из раздела FROM и констант. В простейшем случае он может быть, например, списком имен некоторых столбцов таблиц из раздела FROM. В случае, если вместо списка выборки стоит звездочка (*), то выбираются все столбцы таблиц из раздела FROM. В разделе FROM определяются таблицы, из которых будут извлекаться данные. Следует отметить, что рядом с именем таблицы можно указывать еще одно имя - синоним имени таблицы, который можно использовать в других разделах табличного выражения. Раздел WHERE служит своего рода фильтром при отборе данных. Выполнение раздела GROUP BY оператора выборки сводится к разбиению результирующей таблицы на множество групп строк, которое состоит из минимального числа таких групп, в которых для каждого столбца из списка столбцов раздела GROUP BY во всех строках каждой группы, включающей более одной строки, значения этого столбца совпадают. Результатом выполнения раздела HAVING является сгруппированная таблица, содержащая только те группы строк, для которых результат вычисления условия поиска является истинным. Условие поиска раздела HAVING задает условие на целую группу, а не на индивидуальные строки, поэтому в данном случае прямо можно использовать только столбцы, указанные в качестве столбцов группирования в разделе GROUP BY. Раздел ORDER BY позволяет установить желаемый порядок просмотра результирующей таблицы. Спецификация сортировки имеет следующий синтаксис: <спецификация сортировки>::= {<целое без знака> | <имя столбца>} [ASC | DESC] Как видно, фактически задается список столбцов, и для каждого столбца указывается порядок просмотра строк результирующей таблицы в зависимости от значений этого столбца (ASC - по возрастанию (умолчание), DESC - по убыванию). Указывать сортируемый столбец можно по имени или по порядковому номеру в результирующей таблице. Предикаты условия поиска В условии поиска могут использоваться следующие предикаты: предикат сравнения, предикат BETWEEN , предикат IN, предикат LIKE, предикат NULL, предикат с квантором и предикат EXISTS. Предикат IN определяется следующим образом: <предикат IN>::= <выражение> [NOT] IN (<значение> [,<значение>...] | .<подзапрос>) Значение предиката является истинным, когда значение левого операнда совпадает хотя бы с одним значением списка правого операнда. Использование ключевого слова NOT осуществляет отрицание результата. Подзапрос- это запрос, используемый в предикате условия поиска. Результатом выполнения подзапроса является единственный столбец. Предикат BETWEEN определяется следующим образом: <предикат BETWEEN>::= <выражение> [NOT] BETWEEN <выражение> AND <выражение> По определению результат x BETWEEN y AND z тот же самый, что результат логического выражения x>=y AND x<=z. Предикат LIKE имеет следующий синтаксис: <предикат LIKE>::= <имя столбца> [NOT] LIKE <шаблон>[ESCAPE Значение предиката LIKE является истинным, если шаблон является подстрокой заданного столбца. При этом, если раздел ESCAPE отсутствует, то при составлении шаблона со строкой производится специальная интерпретация символов-заместителей шаблона: символ подчеркивания (“_”) обозначает любой одиночный символ, символ процента (“%”) обозначает последовательность произвольных символов произвольной длины (может быть нулевой), парные квадратные скобки представляют любой символ, записанный в скобках. Если же раздел ESCAPE присутствует и специфицирует некоторый одиночный символ x, то пары символов “x_” и “x%” представляют одиночные символы “_” и “%” соответственно. Предикат NULL описывается синтаксическим правилом: <предикат NULL>::= <имястолбца> IS [NOT] NULL Значение “x IS NULL” является истинным, когда значение x неопределено. Предикат EXISTS имеет следующий синтаксис: <предикат EXISTS>::= EXISTS <подзапрос> Значение предиката является истинным, когда результат вычисления подзапроса не пуст. Агрегатные функции Агрегатные функции (функции множества) в запросе предназначены для вычисления некоторого значения для заданного множества строк. Таким множеством строк может быть группа строк, если агрегатная функция применяется к сгруппированной таблице, или вся таблица. В языке SQL определены следующие агрегатные функции: • AVG - функция определения среднего значения; • MAX - функция определения максимального значения; • MIN - функция определения минимального значения; • SUM - функция суммирования значений; • COUNT - функция для подсчета числа строк или значений. Грамматика агрегатных функций следующая: <агрегатная функция>::= COUNT(*) | функция> <имя столбца>) Вычисление функции COUNT(*) производится путем подсчета числа строк в заданном множестве. Функция типа distinct выполняет вычисления только над одним столбцом, а в вычислениях используются только уникальные значения столбца. При использовании функции типа all список значений формируется из значений арифметического выражения, вычисляемого для каждой строки заданного множества. Операции реляционной алгебры Большинство SQL-запросов требует одновременного обращения к нескольким таблицам. Часто такого рода запросы основываются на операциях реляционной алгебры, в частности, соединения, декартова произведения, объединения, пересечения и разности. При соединении двух таблиц по некоторому условию образуется результирующая таблица, строки которой являются конкатенацией (сцеплением) строк первой и второй таблиц и удовлетворяют этому условию. Операцию соединения можно реализовать с использованием обычного SQL-запроса типа SELECT-FROM-WHERE. По стандарту ANSI операция соединения таблиц может указываться явно в разделе FROM. Синтаксис раздела FROM в этом случае следующий: <раздел FROM>::= FROM <имя таблицы> [JOIN <имя таблицы> ON <условие соединения> ...] При выполнении декартова произведения двух таблиц производится таблица, строки которой являются конкатенацией строк первой и второй таблиц. Операцию декартова произведения можно реализовать с использованием SQL-запроса типа SELECT-FROM. По стандарту ANSI операция декартова произведения может указываться явно в разделе FROM с использованием ключевой фразы CROSS JOIN. При выполнении операции объединения двух таблиц производится таблица, включающая все строки, входящие хотя бы в одну из таблиц операндов. При этом число столбцов и типы данных этих столбцов должны быть одинаковыми для всех операндов. Для объединения результирующих таблиц операторов SELECT используется ключевое слово UNION. Операция пересечения двух таблиц производит таблицу, включающую все строки, входящие в обе исходные таблицы. Таблица, являющаяся разностью двух таблиц, включает все строки, входящие в таблицу - первый операнд, такие, что ни одна из них не входит в таблицу, являющуюся вторым операндом. Работа с утилитой QueryAnalyzer Клиентская утилита Query Analyzer используется для тестирования SQL-запросов. Query Analyzer предоставляет мощные средства для написания и отладки сложных наборов операторов Transact-SQL различных типов. Query Analyzer также предоставляет средства для анализа производительности запросов посредством выполнения планов или с помощью мастера настройки индексов Index Tuning Wizard. Вы можете запустить Query Analyzer из Enterprise Manager или из меню Start в Windows. Если вы запустили Query Analyzer из Enterprise Manager, Query Analyzer будет передавать информацию о соединении из Enterprise Manager: если вы подключены к серверу, то Query Analyzer будет соединяться с этим сервером, и если у вас по умолчанию установлена какая-либо база данных, то Query Analyzer будет выбирать именно эту базу данных. Если вы запустили Query Analyzer из меню Start или если вы не создали в Enterprise Manager соединения с сервером или базой данных, вы должны в Query Analyzer создать соединение вручную. Query Analyzer будет использовать текущую выбранную базу данных для разрешения ссылок в запросах и других операторах Transact-SQL. Пункты меню File управляют сохранением, чтением и печатью запросов, а также подключениями к серверам. Меню Edit позволяет копировать и искать строки. Меню Query управляет выполнением запросов и предлагает доступ к некоторым общим установкам подключения. Пункты меню Help и Window работают практически так же, как и в любом приложении Windows. Кнопки панели инструментов (toolbar) дают те же возможности, что и пункты меню, но при однократном нажатии отдельные кнопки особенно удобны. Первая кнопка слева (кнопка Новый запрос) позволяет создать новое окно запросов и новое подключение к данному серверу с использованием того же идентификатора пользователя и пароля. Кроме того, оно позволяет автоматически использовать ту же базу данных, что и текущее подсоединение. Одновременные подсоединения удобны, поскольку они позволяют работать как два отдельных пользователя, тестируя блокировку и многопользовательское поведение, или дают возможность быстро посмотреть значение, необходимое для написания сложного запроса в другом окне. Панель инструментов также содержит поле со списком, в котором отображается текущая выбранная база данных. Вы можете переключиться на другую базу данных, выбрав ее имя в панели инструментов или в меню Query. Окно Query отображает в строке заголовка имя сервера базы данных, текущую базу данных, текущую учетную запись и имя запроса. Query Analyzer первоначально отображает два окна: Object Browser и Query. При запуске Query Analyzer открывается одно окно Query, но вы в любое время можете открыть новое окно, нажав в панели инструментов Query Analyzer на кнопке New Query. Окно Query также предоставляет возможность отображения каждого вида запроса на отдельной вкладке, а не в панели. Это является наиболее удобным способом просмотра запроса. Простейшим способом использования окна Query является ввод операторов SQL. Окно Query анализатора запросов Query Analyzer предоставляет некоторый дополнительный сервис, выделяя различными цветами введенные вами операторы Transact-SQL. Вызвать утилиту Query Analyzer можно запустив загрузочный модуль isqlw.exe. Необходимыми динамическими библиотеками при работе утилиты являются: ntdblib.dll, sqlgui32.dll, sqlsvc32.dll и sqlqry32.dll. Использование сценариев SQL Сценарий представляет собой набор операторов Transact-SQL, хранимых в файле. Сценарии часто используются для постоянного хранения команд для записей, используемых для создания и заполнения объектов базы данных. Поскольку сценарии хранятся в тестовом файле, а не в базе данных, они могут быть использованы для воссоздания базы данных на других серверах. Хотя сценарии очень часто используются для создания объектов базы данных, они могут применяться не только для этого. В сценарий может быть включен любой допустимый оператор Transact-SQL. В сценарии операторы SQL группируются в пакеты. Сценарий может содержать один или несколько пакетов, а каждый пакет - один или более операторов SQL. В сценарии, содержащем более одного пакета, пакеты разделяются командой GO. Если сценарий не содержит команды GO, все операторы будут выполняться как один пакет. Использование ObjectBrowser Средство Object Browser представляет собой панель в левой части окна анализатора запросов Query Analyzer. Подобно тому как дерево консоли Console Tree в Enterprise Manager предоставляет иерархическое отображение объектов в установленном экземпляре SQL Server, вкладка Objects в Object Browser отображает иерархическое представление баз данных на сервере, с которым вы соединяетесь, а также другие распространенные объекты, используемые при создании программ Transact-SQL. Вкладка Objects в Object Browser несколько отличается от дерева консоли Console Tree в Enterprise Manager. В списке отображаются только объекты базы данных, поэтому такие элементы, как Logins и Data Transformation Services, невидимы. Для удобства таблицы в базе данных размещены в двух папках: UserTablesи SystemTables. В отличие от простого отображения списка таблиц базы данных, как это имеет место в дереве консоли Console Tree Enterprise Manager, Object Browser отображает столбцы, индексы, ограничения и триггеры, которые вы установили для таблицы, а также зависимости, представления и таблицы, которые зависят от данной таблицы. В Object Browser папка CommonObjectsсодержит встроенные типы данных и набор основных функций Transact-SQL, сгруппированных в категории. Для каждой функции папка Parametersсодержит описание каждого параметра, включая его имя и тип данных. Используя Object Browser, вы можете открывать таблицу или представление подобно тому, как вы отображаете строки в конструкторе запросов Query Designer в Enterprise Manager. При этом, возможно просматривать строки, или же вставлять новые, или редактировать существующие строки. Вы также можете использовать Object Browser для создания программ на Transact-SQL. Вы можете добавлять объекты в окно Query, используя технику «перетащить-и-оставить», а также автоматически создавать несколько разных типов сценариев из контекстного меню. Если вы в Object Browser нажмете правой кнопкой мыши на таблице или представлении и выберите Open, анализатор запросов Query Analyzer отобразит строки в таблице в окне Open Table. Наиболее простой и удобной возможностью при работе в Object Browser является использование приема «перетащить-и-оставить». Всякий раз, когда вам необходимо задать объект в Object Browser, вам достаточно просто перетащить его в панель редактирования Editor Pane окна Query, и его имя будет вставлено в окно редактирования. Скриптование объектов Скриптование является более сложной версией приема «перетащить-и-оставить» Object Browser. Скриптование создает полный оператор Transact-SQL и может выполняться из контекстного меню большинства объектов. Не все типы сценариев применимы ко всем типам объектов, а к таким типам, как параметры функции или столбцы, операция скриптования не может быть применена вообще. Опции скриптования
Сценарии могут быть записаны в новом окне Query, в файл сценария или в буфер обмена. Некоторые сценарии, такие как сценарии выполнения функции, используют замещаемые параметры. Query Analyzer предоставляет диалоговое окно для удобства замещения этих параметров соответствующими значениями. |