Автор работы: Пользователь скрыл имя, 23 Сентября 2013 в 11:04, курсовая работа
Structured Query Language представляет собой непроцедурный язык, используемый для управления данными реляционных СУБД. Термин <непроцедурный> означает, что на данном языке можно сформулировать, что нужно сделать с данными, но нельзя проинструктировать, как именно это следует сделать.
Листинг 2.5
SELECT *
FROM ПРОЕКТ
WHERE МаксТрудозатраты > 100;
Результат имеет следующий вид:
1200 КВ3 Подготовка налогового отчета бухгалтерия 145.0
1400 КВ4 Планирование номенклатуры изделий Маркетинг 138.0
1500 КВ4 Анализ портфеля Финансы 110.0
Объединив описанные выше методы, мы можем выбрать из таблицы определенные столбцы и строки. Например, следующий оператор извлекает из таблицы СОТРУДНИК столбцы Имя и Отдел сотрудников бухгалтерии:
Листинг 2.6.
SELECT Имя, Отдел
FROM СОТРУДНИК
WHERE Отдел = 'Бухгалтерия';
Этот запрос выдаст следующую таблицу:
Мария Селезнева Бухгалтерия
Маргарита Сперанская Бухгалтерия
Еще одна форма предложения WHERE предполагает задание списка значений, которые может иметь столбец. Это будем делать с помощью ключевого слова IN:
Листинг 2.7.
SELECT Имя, Телефон, Отдел
FROM Сотрудник
WHERE Отдел IN ('Бухгалтерия', 'Финансы', 'Маркетинг');
Результат таков:
Мария Селезнева 285-8879
Николай Григорьев 287-0098 Маркетинг
Хасан Джабраилов 287-9981 Финансы
Маргарита Сперанская 285-1273
Дмитрий Чазов 287-3222 Маркетинг
Строка исходного отношения войдет в результирующую таблицу, если её столбец Отдел имеет значения 'Бухгалтерия', 'Финансы', 'Маркетинг'.
Чтобы выбрать строки, у которых столбец Отдел не равен ни одному из этих значений, используется ключевое слово NOT IN:
Листинг 2.8.
SELECT Имя, Телефон, Отдел
FROM СОТРУДНИК
WHERE Отдел NOT IN ('Бухгалтерия', 'Финансы', 'Маркетинг');
Результат этого запроса будет следующим:
Владимир Петров Инф. Системы
Александр Чижов 287-0123 Инф.Системы
В запросах также используются встроенные функции. Полезность встроенных функций состоит в том, что их можно применять к группам строк и данных. Следующий оператор подсчитывает количество сотрудников в каждом отделе:
Листинг 2.9.
SELECT Отдел, COUNT(*)
FROM СОТРУДНИК
GROUP BY Отдел;
Результат будет таким:
Бухгалтерия 2
Инф. Системы 2
Маркетинг 2
Финансы 1
Ключевое слово GROUP BY предписывает СУБД отсортировать таблицу по указанному столбцу, а затем применить встроенные функции к группам строк, имеющим одинаковые значения данного столбца.
Все запросы, приведенные выше, считывают данные из одиночных таблиц. Однако, для получения требуемой информации необходимо обрабатывать более одной таблицы. Допустим, что нам необходимо знать имена сотрудников, проработавших в рамках любого из своих назначений более 40 часов. Имена сотрудников хранятся в таблице СОТРУДНИК, а количество отработанных ими часов – в таблице НАЗНАЧЕНИЕ. При обращении к одиночной таблице запрос был бы следующим:
Листинг 2.10.
SELECT DISTINCT Имя
FROM СОТРУДНИК
WHERE ТабельныйНомер IN (100,500);
В этом запросе получим сотрудников с номерами 100 и 500, у которых более 40 часов трудозатрат, но так как нам неизвестны эти номера, узнать их можно инициировав следующий запрос:
Листинг 2.11.
SELECT НомерСотрудника
FROM НАЗНАЧЕНИЕ
WHERE ФактТрудозатраты >40;
Если объединить эти два SQL-оператора при помощи вложенного запроса (subquery):
Листинг 2.12.
SELECT DISTINCT Имя
FROM СОТРУДНИК
WHERE ТабельныйНомер IN
(SELECT НомерСтрудника
FROM НАЗНАЧЕНИЕ
WHERE ФактТрудозатраты >40);
Результатом этого оператора будет следующее сообщение
Мария Селезнева
Николай Григорьев
Хасан Джабраилов
Маргарита Сперанская
Вложенность запросов может быть расширена до трех, четырех и более уровней. Предположим, что необходимо узнать имена сотрудников, проработавших более 40 часов в проекте, который оплачивается бухгалтерией.
Идентификаторы проектов, оплачиваемых бухгалтерией, узнаем при помощи следующего запроса:
Листинг.2.13.
SELECT ИдПроекта
FROM ПРОЕКТ
WHERE Отдел = 'Бухгалтерия';
Номера сотрудников, проработавших более 40 часов в рамках этих проектов, возвратит следующий запрос:
Листинг.2.14.
SELECT НомерСотрудника
FROM НАЗНАЧЕНИЕ
WHERE ФактТрудозатраты>40
AND ИдПроекта IN
(SELECT ИдПроекта
FROM ПРОЕКТ
WHERE Отдел = 'Бухгалтерия');
Имена сотрудников ,номера которых узнали в предыдущем запросе, можно определить с помощью следующего оператора:
Листинг 2.15.
SELECT DISTINCT Имя
FROM СОТРУДНИК
WHERE ТабельныйНомер IN
(SELECT НомерСотрудника
FROM НАЗНАЧЕНИЕ
WHERE ФактТрудозатраты>40
AND ИдПроекта IN
(SELECT ИдПроекта
FROM ПРОЕКТ
WHERE Отдел = 'Бухгалтерия'));
Листинг 2.15 является примером составного запроса с применением вложенных запросов.
Вложенные запросы подходят для обработки нескольких таблиц до тех пор, пока результаты (столбцы в предложении SELECT) относятся к одной и той же таблице. Если же необходимо извлечь данные из двух или более таблиц, при помощи вложенного запроса это сделать не удастся. Вместо этого необходимо использовать операцию соединения (join).
Основная идея – создать новое отношение, связав между собой содержимое двух или более исходных отношений. Следующий запрос является примером соединения.
Листинг 2.16.
SELECT Имя, ФактТрудозатраты
FROM СОТРУДНИК, НАЗНАЧЕНИЕ
WHERE ТабельныйНомер = НомерСотрудника;
Смысл этого оператора
заключается в том, что создается
новая таблица с двумя
В результате получается следующая таблица:
Мария Селезнева 17.50
Мария Селезнева 45.75
Николай Григорьев 75.00
Хасан Джабраилов 12.50
Маргарита Сперанская
Маргарита Сперанская 70.50
Дмитрий Чазов
Дмитрий Чазов
Таблицу, получившуюся в результате соединения, можно обрабатывать так же, как и любую другую таблицу. Можно сгруппировать строки по именам сотрудников и просуммировать количество часов, отработанных ими.
Листинг 2.17.
SELECT Имя, SUM(ФактТрудозатраты)
FROM СОТРУДНИК, НАЗНАЧЕНИЕ
WHERE ТабельныйНомер = НомерСотрудника;
GROUP BY Имя;
Результатом будет следующая таблица:
Дмитрий Чазов
Николай Григорьев 75.00
Хасан Джабраилов 20.25
Маргарита Сперанская 63.25
Мария Селезнева 40.50
При необходимости получения информации о названии каждого проекта, имен всех сотрудников, работающих в проекте, и фактические трудозатраты каждого из сотрудников , нужно соединить все три таблицы.
Листинг 2.18.
SELECT ПРОЕКТ.НАЗВАНИЕ, ФактТрудозатраты, СОТРУДНИК.Имя
FROM ПРОЕКТ, НАЗНАЧЕНИЕ, СОТРУДНИК
WHERE ПРОЕКТ.ИдПроекта = НАЗНАЧЕНИЕ.ИдПроекта
AND СОТРУДНИК.ТабельныйНомер = НАЗНАЧЕНИЕ.НомерСотрудника;
Результатом соединения будет следующая таблица:
КВ3 Анализ портфеля 17.50 Мария Селезнева
КВ3 Анализ портфеля 12.50 Хасан Джабраилов
КВ3 Анализ портфеля 8.00 Маргарита Сперанская
КВ3 Анализ портфеля 20.25 Дмитрий Чазов
КВ3 Подготовка налогового отчета 45.75 Мария Селезнева
КВ3 Подготовка налогового отчета 70.50 Маргарита Сперанская
КВ3 Подготовка налогового отчета 40.50 Роман Бережной
КВ4 Планирование номенклатуры изделий 75.00 Николай Григорьев
КВ4 Планирование номенклатуры изделий 20.25 Галина Викторова
КВ4 Планирование номенклатуры изделий 25.25 Дмитрий Чазов
Все вышеперечисленные примеры запросов используются в языке SQL для получения данных из таблиц СУБД. Они могут применяться к любым базам использующим стандарт SQL-92. И могут быть применены также для тестирования знаний студентов в области составления запросов.
Теперь построим информационную модель предприятия при помощи программы ERwin. Для построения логической модели прежде всего необходимо определить набор сущностей и задать связи между ними. Допустим нам необходимо создать БД для справочника начинающего географа. Требования к приложению следующие:
Для дальнейшей разработки была выбрана Реляционная схема с суррогатными ключами. Именно эту схему будем использовать для разработки. Все ключи представлены как суррогатные в формате IDENTITY(n,m), где n – начальное значение суррогатного ключа, а m – приращение. Все ключи имеют приращение, равное 1, но разные начальные значения.
Атрибут TRANSACTION.CustomerID может иметь пустые значения; это позволяет создать строку в таблице TRANSACTION до того, как произведение приобретет какой-нибудь клиент. Все прочие внешние ключи являются обязательными.
Таким образом ER-диаграмма модели данных создана. Физическая схема базы данных генерируется на основе логической схемы и набора установок, определяющих, какие элементы должны войти в схему БД. Сгенерированный SQL-сценарий создания БД приведен в листинге 2.19.
Но для целостности рассмотрим создание базы данных и модели данных необходимых для корректной работы. Они основаны на использовании MS SQL Server 2000, 2005 и 2008, с различными доработками, а также среды Visual Studio.NET.
Есть два способа создания и модификации таблиц и других структур SQL Server. Первый способ – использовать SQL-операторы CREATE или ALTER. Второй способ использовать графические возможности SQL Server.
Есть несколько способов передать оператор CREATE на выполнение SQL Server. Простейший из них – использовать анализатор запросов SQL Query Analyzer. Для этого в главном меню Enterprise Manager выбираем команду SQL Query Analyzer, вводим оператор CREATE TABLE в окно анализатора кода или используя команду ОТКРЫТЬ ЗАПРОС, загрузить готовый файл с запросами. Если оператор или файл содержит синтаксические ошибки, отчет о них будет представлен ниже, в окне под текстом оператора. Исправив ошибки нажимаем на зеленую стрелку и происходит выполнение операторов с помощью которых происходит создание таблиц и индексов. Результат исполнения мы видим на рис.
Рис.2.3 созданные таблицы в результате выполнения SQL-запроса
Рис.2.4 столбцы и свойства таблицы
Чтобы убедиться, что связи созданы правильно, щелкаем правой кнопкой мыши на вкладке ДИАГРАММЫ и в открывшемся меню выбираем СОЗДАТЬ ДИАГРАММУ БАЗЫ ДАННЫХ. В появившемся мастере выбираем требуемые таблицы, результатом является следующая диаграмма, рис.2.5.
Рис. 2.5. диаграмма базы данных
Окружение, в котором находятся современные приложения баз данных, использующие Интернет-технологии, весьма многообразно и сложно по составу. Типичный веб-сервер должен публиковать приложения, содержащие данные из множества различных источников.
ADO(Active Data Objects) – простая объектная модель, которая используется потребителями данных для обработки любых данных OLE DB. К ней можно обращаться из сценарных языков, таких как Jscript и VBScript, а также из Visual Basic, Java, C# и C++.
Благодаря абстракциям OLE DB и объектной структуре, объектная модель ADO и её интерфейсы остаются одними и теми же независимо от типа обрабатываемых данных. Вызывать ADO рекомендуется на веб-сервере, используя ASP-страницы. Такие страницы содержат смесь DHTML (или XML) и программных конструкций на языках VBScript или JavaScript.
IIS (Internet Information Server, информационный сервер Интернет) – это веб-сервер, встроенный в операционные системы Windows. ASP является ISAPI-расширением IIS. С практической точки зрения это означает, что всякий раз, когда IIS получает файл с расширением .asp, он посылает этот файл программе ASP для обработки.