Разработка база данных

Размещено на http://www.allbest.ru/

1. Язык SQL, структура СУБД MS SQL, построение запросов к БД, ASP.NET и его применение

1.1 Язык SQL, конструкции и данные

база данный масштабируемый запрос

Structured Query Language представляет собой непроцедурный язык, используемый для управления данными реляционных СУБД. Термин <непроцедурный> означает, что на данном языке можно сформулировать, что нужно сделать с данными, но нельзя проинструктировать, как именно это следует сделать. Иными словами, в этом языке отсутствуют алгоритмические конструкции, такие как метки, операторы цикла, условные переходы и др.Язык SQL, предназначенный для взаимодействия с базами данных, появился в середине 70-х гг. (первые публикации датируются 1974 г.) и был разработан в компании IBM в рамках проекта экспериментальной реляционной СУБД System R. Исходное название языка SEQUEL (Structured English Query Language) только частично отражало суть этого языка. Конечно, язык был ориентирован главным образом на удобную и понятную пользователям формулировку запросов к реляционным БД. Но, в действительности, он почти с самого начала являлся полным языком БД, обеспечивающим помимо средств формулирования запросов и манипулирования БД следующие возможности:

средства определения и манипулирования схемой БД;

средства определения ограничений целостности и триггеров;

средства определения представлений БД;

средства определения структур физического уровня, поддерживающих эффективное выполнение запросов;

средства авторизации доступа к отношениям и их полям1);

средства определения точек сохранения транзакции, и выполнения фиксации и откатов транзакций.

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

Рис.1 Разделение языка SQL на уровни

Язык SQL, соответствующий последним стандартам SQL:2003, SQL:1999, можно разбить на слои или уровни, такие, в котором каждый уровень включает все конструкции, входящие в более низкие уровни. В стандарте , язык разбивается на «базовый»(entry), «промежуточный»(intermediate) и «полный» (full) уровни. Эта классификация ориентирована, прежде всего, на производителей СУБД, в которых поддерживается SQL. Реализация базового уровня языка является обязательным условием хотя бы какого-то соответствия стандарту. Реализация промежуточного уровня желательна, и обычно именно такой уровень языка поддерживается ведущими компаниями-производителями SQL-ориентированных СУБД. Наконец, полный уровень языка является целью, к достижению которой следует стремиться. В данной классификации критерием отнесения той или иной возможности языка к некоторому уровню является оцениваемая создателями стандарта SQL (большая часть которых является сотрудниками ведущих компаний, производящих SQL-ориентированные СУБД) техническая сложность реализации этой возможности. Конечно, такая классификация важна и для программистов приложений баз данных, но только для того, чтобы оценить реальные возможности конкретной СУБД.

Уровни деления языка конструктивно, показаны на рис.1.Среди всех конструкций языка SQL можно выделить такие конструкции, которые можно было использовать при «прямом» (direct) взаимодействии конечного пользователя с СУБД (например, в интерактивном режиме). В некотором смысле этот уровень также является базовым, поскольку соответствующие средства языка в наибольшей степени отражают его ориентированность на работу с множествами. На следующем уровне, уровне «встраиваемого» (embedded) SQL, язык расширяется конструкциями, позволяющими использовать возможности прямого SQL в программах, написанных на традиционных языках программирования. Наконец, на уровне «динамического» (dynamic) SQL во встраиваемый SQL добавляются конструкции, позволяющие приложениям обращаться к СУБД с конструкциями прямого SQL, которые динамически образуются во время выполнения программы.

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

По определению, в язык SQL в качестве составных частей входят:

- язык манипулирования данными (Data Manipulation Language, DML)

- язык определения данных (Data Definition Language, DDL)

- язык управления данными (Data Control Language, DCL).

Хотя названия показывают, что это разные языки, на самом деле это не отдельные языки, а различные команды одного языка. Такое деление проведено только лишь с точки зрения различного функционального назначения этих команд.

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

SELECT (выбрать)

INSERT (вставить)

UPDATE (обновить)

DELETE (удалить)

Язык определения данных используется для создания и изменения структуры базы данных и ее составных частей - таблиц, индексов, представлений (виртуальных таблиц), а также триггеров и сохраненных процедур. Основными его командами являются:

CREATE DATABASE (создать базу данных)

CREATE TABLE (создать таблицу)

CREATE VIEW (создать виртуальную таблицу)

CREATE INDEX (создать индекс)

CREATE TRIGGER (создать триггер)

CREATE PROCEDURE (создать сохраненную процедуру)

ALTER DATABASE (модифицировать базу данных)

ALTER TABLE (модифицировать таблицу)

ALTER VIEW (модифицировать виртуальную таблицу)

ALTER INDEX (модифицировать индекс)

ALTER TRIGGER (модифицировать триггер)

ALTER PROCEDURE (модифицировать сохраненную процедуру)

DROP DATABASE (удалить базу данных)

DROP TABLE (удалить таблицу)

DROP VIEW (удалить виртуальную таблицу)

DROP INDEX (удалить индекс)

DROP TRIGGER (удалить триггер)

DROP PROCEDURE (удалить сохраненную процедуру)

Язык управления данными используется для управления правами доступа к данным и выполнением процедур в многопользовательской среде. Более точно его можно назвать "язык управления доступом". Он состоит из двух основных команд:

GRANT (дать права)

REVOKE (забрать права)

С точки зрения прикладного интерфейса существуют две разновидности команд SQL:

интерактивный SQL

встроенный SQL.

Интерактивный SQL используется в специальных утилитах (типа WISQL или DBD), позволяющих в интерактивном режиме вводить запросы с использованием команд SQL, посылать их для выполнения на сервер и получать результаты в предназначенном для этого окне. Встроенный SQL используется в прикладных программах, позволяя им посылать запросы к серверу и обрабатывать полученные результаты, в том числе комбинируя set-ориентированный и record-ориентированный подходы.

Рассмотри команды языка манипулирования данными.

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

Все допустимые в SQL типы данных, которые можно использовать при определении столбцов, разбиваются на следующие категории:

точные числовые типы (exact numerics);

приближенные числовые типы (approximate numerics);

типы символьных строк (character strings);

типы битовых строк (bit strings);

типы даты и времени (datetimes);

типы временных интервалов (intervals);

булевский тип (Booleans);

типы коллекций (collection types);

анонимные строчные типы (anonymous row types);

типы, определяемые пользователем (user-defined types);

ссылочные типы (reference types).

В столбцах таблиц, определенных на любых типах данных, наряду со значениями этих типов, допускается сохранение неопределенного значения, которое обозначается ключевым словом NULL.

1.2 MS SQL и структура СУБД, запросы

Microsoft SQL Server - это законченное решение для управления и анализа данных, позволяющее оперативно развертывать масштабируемые Web-приложения нового поколения. SQL Server - ключевой компонент поддержки электронной коммерции, интерактивных деловых приложений и хранилищ данных, обеспечивающий масштабируемость, необходимую для поддержки растущих, динамических сред. В SQL Server предусмотрена широчайшая поддержка XML (eXtensible Markup Language) и других форматов, используемых в Интернете, функций производительности и доступности, гарантирующих своевременное решение поставленных задач, а также развитой функциональности управления и настройки, позволяющей авторизовать выполнение рутинных задач и снизить совокупную стоимость владения.

MS SQL Server - это реляционная СУБД, которая использует язык Transact-SQL для пересылки сообщений между компьютером клиента и компьютером клиента и компьютером, на котором работает SQL Server. Реляционная СУБД состоит и з механизма баз данных, собственно баз данных и приложений, необходимых для управления данными и компонентами реляционной СУБД. Реляционная СУБД организует данные в виде связанных строк и столбцов, составляющих базу данных. Реляционная СУБД отвечает за поддержку структуры базы данных и решает следующие задачи:

· Поддерживает связи между данными в базе;

· Гарантирует корректное хранение данных и выполнение правил, регламентирующих связи между ними;

· Восстанавливает данные после аварии системы, переводя их в согласованное состояние, зафиксированное до сбоя.

База данных (БД) SQL Server представляет собой реляционную базу данных,

совместимую c SQL (Structured Query Language) с интегрированной поддержкой XML для Интернет-приложений.

1.2.1 Базы данных

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

Кроме того, информацию из различных баз данных, поскольку данные не связаны с каким-либо определенным форматом.



1.2.2 Реляционные базы данных

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

Таблица описывает некоторый класс объектов, важных для организации. Например, в базе данных какой-либо компании одна таблица может быть предназначена для сведений о работниках, другая - для сведений о покупателях и еще одна - для сведений о магазинах. Любая таблица состоит из столбцов и строк (в теории реляционных баз данных это атрибуты и кортежы). Каждый столбец определяет атрибут класса объектов, представленных таблицей. Например, в таблице для сведений о работниках могут быть столбцы, определяющие такие атрибуты, как имя, фамилия, идентификационный номер, отдел, тарифный разряд и название должности работника. А каждая строка описывает отдельный экземпляр объекта, представленного таблицей.

1.2.3 Создание базы данных в среде MS SQL Server

Процесс создания базы данных в системе SQL-сервера состоит из двух этапов: сначала организуется сама база данных, а затем принадлежащий ей журнал транзакций. Информация размещается в соответствующих файлах, имеющих расширения *.mdf (для базы данных) и *.ldf. (для журнала транзакций).

В файле базы данных записываются сведения об основных объектах (таблицах, индексах, просмотрах и т.д.), а в файле журнала транзакций - о процессе работы с транзакциями (контроль целостности данных, состояния базы данных до и после выполнения транзакций).

Создание базы данных в системе SQL-сервер осуществляется командой CREATE DATABASE. Следует отметить, что процедура создания базы данных в SQL-сервере требует наличия прав администратора сервера.

<определение_базы_данных> ::=

CREATE DATABASE имя_базы_данных

[ON [PRIMARY]

[ <определение_файла> [,...n] ]

[,<определение_группы> [,...n] ] ]

[ LOG ON {<определение_файла>[,...n] } ]

[ FOR LOAD | FOR ATTACH ]

Рассмотрим основные параметры представленного оператора. При выборе имени базы данных следует руководствоваться общими правилами именования объектов. Если имя базы данных содержит пробелы или любые другие недопустимые символы, оно заключается в ограничители (двойные кавычки или квадратные скобки). Имя базы данных должно быть уникальным в пределах сервера и не может превышать 128 символов. При создании и изменении базы данных можно указать имя файла, который будет для нее создан, изменить имя, путь и исходный размер этого файла. Если в процессе использования базы данных планируется ее размещение на нескольких дисках, то можно создать так называемые вторичные файлы базы данных с расширением *.ndf. В этом случае основная информация о базе данных располагается в первичном (PRIMARY) файле, а при нехватке для него свободного места добавляемая информация будет размещаться во вторичном файле. Подход, используемый в SQL-сервере, позволяет распределять содержимое базы данных по нескольким дисковым томам. Параметр ON определяет список файлов на диске для размещения информации, хранящейся в базе данных.

После создания общей структуры базы данных можно приступить к созданию таблиц, которые представляют собой отношения, входящие в состав проекта базы данных.Таблица - основной объект для хранения информации в реляционной базе данных. Она состоит из содержащих данные строк и столбцов, занимает в базе данных физическое пространство и может быть постоянной или временной. Поле, также называемое в реляционной базе данных столбцом, является частью таблицы, за которой закреплен определенный тип данных. Каждая таблица базы данных должна содержать хотя бы один столбец. Строка данных - это запись в таблице базы данных, она включает поля, содержащие данные из одной записи таблицы.

<определение_таблицы> ::=

CREATE TABLE имя_таблицы

(имя_столбца тип_данных

[NULL | NOT NULL ] [,...n])

Главное в команде создания таблицы - определение имени таблицы и описание набора имен полей, которые указываются в соответствующем порядке. Кроме того, этой командой оговариваются типы данных и размеры полей таблицы.

Реляционная база данных представляет собой совокупность отношений, содержащих всю необходимую информацию и объединенных различными связями.

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

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

Для поддержания ссылочной целостности данных во многих СУБД имеется механизм так называемых внешних ключей. Смысл этого механизма состоит в том, что некоему атрибуту (или группе атрибутов) одного отношения назначается ссылка на первичный ключ другого отношения; тем самым закрепляются связи подчиненности между этими отношениями. При этом отношение, на первичный ключ которого ссылается внешний ключ другого отношения, называется master-отношением, или главным отношением; а отношение, от которого исходит ссылка, называется detail-отношением, или подчиненным отношением. После назначения такой ссылки СУБД имеет возможность автоматически отслеживать вопросы "ненарушения" связей между отношениями, а именно:

· если попытаться вставить в подчиненную таблицу запись, для внешнего ключа которой не существует соответствия в главной таблице (например, там нет еще записи с таким первичным ключом), СУБД сгенерирует ошибку;

· если попытаться удалить из главной таблицы запись, на первичный ключ которой имеется хотя бы одна ссылка из подчиненной таблицы, СУБД также сгенерирует ошибку.

· если попытаться изменить первичный ключ записи главной таблицы, на которую имеется хотя бы одна ссылка из подчиненной таблицы, СУБД также сгенерирует ошибку.

Далее устанавливаются связи между объектами (таблицами и столбцами) и производится очень важная операция для исключения избыточности данных - нормализация таблиц. Каждый из различных типов связей должен быть смоделирован в базе данных. Существует несколько типов связей:

· связь "один-к-одному"

· связь "один-ко-многим"

· связь "многие-ко-многим".

Связь "один-к-одному" представляет собой простейший вид связи данных, когда первичный ключ таблицы является в то же время внешним ключом, ссылающимся на первичный ключ другой таблицы. Такую связь бывает удобно устанавливать тогда, когда невыгодно держать разные по размеру (или по другим критериям) данные в одной таблице. Например, можно выделить данные с подробным описанием изделия в отдельную таблицу с установлением связи "один-к-одному" для того чтобы не занимать оперативную память, если эти данные используются сравнительно редко.

Связь "один-ко-многим" в большинстве случаев отражает реальную взаимосвязь сущностей в предметной области. Она реализуется уже описанной парой "внешний ключ-первичный ключ", т.е. когда определен внешний ключ, ссылающийся на первичный ключ другой таблицы. Именно эта связь описывает широко распространенный механизм классификаторов. Имеется справочная таблица, содержащая названия, имена и т.п. и некие коды, причем, первичным ключом является код. В таблице, собирающей информацию - назовем ее информационной таблицей - определяется внешний ключ, ссылающийся на первичный ключ классификатора. После этого в нее заносится не название из классификатора, а код. Такая система становится устойчивой от изменения названия в классификаторах. Имеются способы быстрой "подмены" в отображаемой таблице кодов на их названия как на уровне сервера БД (для клиент-серверных СУБД), так и на уровне пользовательского приложения.

Связь "многие-ко-многим" в явном виде в реляционных базах данных не поддерживается. Однако имеется ряд способов косвенной реализации такой связи, которые с успехом возмещают ее отсутствие. Один из наиболее распространенных способов заключается во введении дополнительной таблицы, строки которой состоят из внешних ключей, ссылающихся на первичные ключи двух таблиц. Например, имеются две таблицы: КЛИЕНТ и ГРУППА_ИНТЕРЕСОВ. Один человек может быть включен в различные группы, в то время как группа может объединять различных людей. Для реализации такой связи "многие-ко-многим" вводится дополнительная таблица, назовем ее КЛИЕНТЫ_В_ГРУППЕ, строка которой будет иметь два внешних ключа: один будет ссылаться на первичный ключ в таблице КЛИЕНТ, а другой - на первичный ключ в таблице ГРУППА_ИНТЕРЕСОВ. Таким образом в таблицу КЛИЕНТЫ_В_ГРУППЕ можно записывать любое количество людей и любое количество групп.

Итак, после определения таблиц, полей, индексов и связей между таблицами посмотрим на проектируемую базу данных в целом и проанализируем ее, используя правила нормализации, с целью устранения логических ошибок. Важность нормализации состоит в том, что она позволяет разбить большие отношения, как правило, содержащие большую избыточность информации, на более мелкие логические единицы, группирующие только данные, объединенные "по природе". Таким образом, идея нормализации заключается в следующем. Каждая таблица в реляционной базе данных удовлетворяет условию, в соответствии с которым в позиции на пересечении каждой строки и столбца таблицы всегда находится единственное значение, и никогда не может быть множества таких значений.

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

· данные легко обновлять или удалять

· исключается возможность рассогласования копий данных

· уменьшается возможность введения некорректных данных.

Рис.

Процесс нормализации заключается в приведении таблиц в так называемые нормальные формы. Существует несколько видов нормальных форм: первая нормальная форма (1НФ), вторая нормальная форма (2НФ), третья нормальная форма (3НФ), нормальная форма Бойса-Кодда (НФБК), четвертая нормальная форма (4НФ), пятая нормальная форма (5НФ).

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

В конечном счете мы получаем работоспособную базу данных, в которую можно вводить информацию. После ввода информации, необходимы инструменты для получения и вывода требуемой пользователю информации. Для этого используются команды - запросы. Основное назначение базы данных SQL Server - хранение данных и обеспечение их доступности для авторизованных приложений и пользователей. Чтобы получить досту к данным и корректировать их, применяют приложения и утилиты, предназначенные для пересылки на SQL Server запросов на извлечение и модификацию данных. Большинство операций можно реализовать средствами одного из четырех операторов: SELECT, INSERT, UPDATE, DELETE.

1.2.3Формирование запросов средствами языка SQL

Оператор SELECT

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

Если оператор SELECT выполняется из приложения на другом языке программирования, то формируется результирующий набор, размещаемый в памяти приложения или сервера БД, а затем приложение извлекает данные из результирующего набора в свои переменные.

Оператор SELECT имеет в стандарте SQL92 следующее формальное описание:

SELECT [DISTINCT]

{ {function_agregate | expr [AS new_field_name] } .,:

| specification.*

| *

[INTO list_variable]

FROM {{ имя_таблицы [AS] [table_alias] [(field .,:)]}

| {subquery [AS] subquery_alas [(field .,:)]}

| union_table

| constructor_of_table_value

| {TABLE имя_таблицы [AS] alias [(field .,:)]}

} .,:

[WHERE condition]

[GROUP BY {{ имя_таблицы | alias }.field} .,: {COLLATE name}]

[HAVING condition]

[{ UNION | INTERSECT | EXCEPT } [ALL]

[CORRESPONDING [BY (field.,:)]]

SELECT_operator | {TABLE имя_таблицы} | constructor_of_table_value

[ORDER BY] {{field_result [ASC|DESC]}.,:}

|{{ integer [ASC|DESC]}.,:} ;

Функции агрегирования

Фраза GROUP BY оператора SELECT применяется для определения группы строк, над которыми выполняются функции агрегирования. Если в операторе SELECT указана фраза GROUP BY, то все имена столбцов, указываемые в списке для определения создаваемого результирующего набора, должны быть указаны с функциями агрегирования, поскольку для каждой группы строк в результирующий набор будет включена только одна строка, содержащая значения, полученные функциями агрегирования над данной группой строк.

К функциям агрегирования относятся следующие функции языка SQL:

· COUNT - подсчет количества всех значений столбцов, за исключением значения NULL и с учетом указания фраз ALL или DISTINCT.

· COUNT (*) - подсчет количества всех значений столбцов в группе.

· AVG - определение среднего значения.

· SUM - подсчет суммы всех значений группы. Если при этом получаемое значение выходит за пределы суммируемого типа данных, то инициируется ошибка выполнения SQL-оператора.

· MAX - определение максимального значения из группы.

· MIN - определение минимального значения из группы.

Фраза HAVING оператора SELECT определяет предикат аналогично фразе WHERE, но применяемый к строкам, полученным в результате выполнения функций агрегирования.

Упорядочивание результирующего набора

Фраза ORDER BY применяется для упорядочивания результирующего набора, которое выполняется в соответствии со значениями столбцов, указанных в списке после фразы ORDER BY. Сначала производится упорядочивание по первому указанному столбцу, потом по второму и т.д. При упорядочивании можно указать опцию ASC (по возрастанию) или DESC (по убыванию).

Например:

SELECT f1,f2 FROM tbl1 ORDER BY f2;

Объединение запросов

Язык SQL предоставляет два способа объединения таблиц:

· указывая соединяемые таблицы (в том числе подзапросы) во фразе FROM оператора SELECT. Сначала выполняется соединение таблиц, а уже потом к полученному множеству применяются указанные фразой WHERE условия, определяемое фразой GROUP BY агрегирование, упорядочивание данных и т.п.;

· определяя объединение результирующих наборов, полученных при обработке оператора SELECT. В этом случае два оператора SELECT соединяются фразой UNION, INTERSECT, EXCEPT или CORRESPONDING.

UNION-объединение

Фраза UNION объединяет результаты двух запросов по следующим правилам:

· каждый из объединяемых запросов должен содержать одинаковое число столбцов;

· тип значений из попарно объединяемых столбцов должен быть одинаковым или приводимым. Так, нельзя объединять значения из столбца типа integer и столбца типа varchar;

· из результирующего набора автоматически исключаются совпадающие строки

· если в строку вставляется какая-либо константа, добавляемая в запросе, то ее значение также влияет на идентичность строк

1.3 ASP.NET и его применение

Microsoft .NET Framework -- это платформа для создания, развертывания и запуска web-сервисов и приложений. Она предоставляет высокопроизводительную, основанную на стандартах многоязыковую среду, которая позволяет интегрировать существующие приложения с приложениями и сервисами следующего поколения, а также решать задачи развертывания и использования интернет-приложений. .NET Framework состоит из трех основных частей -- общеязыковой среды выполнения (common language runtime), иерархического множества унифицированных библиотек классов и компонентной версии ASP, называемой ASP .NET.

ASP .NET -- это часть технологии .NET, используемая для написания мощных клиент-серверных интернет-приложений. Она позволяет создавать динамические страницы HTML. ASP .NET возникла в результате объединения более старой технологии ASP (активные серверные страницы) и .NET Framework. Она содержит множество готовых элементов управления, применяя которые, можно быстро создавать интерактивные web-сайты. Можно использовать сервисы, предоставляемые другими сайтами, прозрачно для пользователей нашего сайта. В общем, возможности ASP .NET ограничены только нашим воображением.

Что такое динамические страницы HTML и чем они отличаются от статических. Статическая страница содержит код на языке гипертекстовой разметки HTML. Когда автор страницы пишет ее, он определяет, как будет выглядеть страница для всех пользователей. Содержание страницы будет всегда одинаковым, независимо от того, кто и когда решит ее просмотреть. Языка HTML вполне достаточно для отображения информации, которая редко изменяется и не зависит от того, кто ее просматривает. Страница HTML -- простой ASCII-текст, следовательно, клиент может работать в любой операционной системе.

Динамическая web-страница должна быть создана "на лету" программой, исполняющейся на интернет-сервере. Широко применяется механизм шлюзов CGI (Common Gateway Interface). Вначале пользователь получает статическую страницу с формой. Вам известно, что в теге FORM существует атрибут ACTION. Именно он задает адрес (URL) исполняемого приложения. На сервере находятся исполняемые файлы программ, написанных, например, на C/С++ или Delphi, которые по протоколу HTTP принимают данные из входного потока или из переменных окружения и записывают в стандартный выходной поток готовую страницу.

Пользователю в ответ на запрос посылается HTML-код, который был специально сгенерирован для него. Это может быть, например, результат поиска в поисковой системе. CGI-скрипты могут быть написаны на интерпретируемом языке (Perl) или даже скрипте командной строки. Входной и выходной потоки переназначаются. На вход интернет-сервер принимает данные, введенные пользователем. После обработки полученных данных пользователю возвращается результирующая страница. При исполнении cgi-программа загружается в память сервера, а при завершении -- удаляется. Когда 100 клиентов одновременно обращаются к серверу, в памяти создаются 100 процессов, для размещения кода каждого из которых нужна память. Это отрицательно сказывается на масштабируемости. Напомним, что масштабируемость -- это возможность плавного роста времени ответа программной системы на запрос с ростом числа одновременно работающих пользователей. Для решения этой проблемы Microsoft была предложена альтернатива -- ISAPI (Internet Server Application Programming Interface) расширения и фильтры. Вместо исполняемых файлов используются DLL-библиотеки. Код DLL находится в памяти все время и для каждого запроса создает не процессы, а нити исполнения. Все нити используют один и тот же программный код. ISAPI-приложение выполняется в процессе IIS-сервера. Это позволяет повысить производительность и масштабируемость.

ASP .NET -- это не продолжение ASP. Это концептуально новая технология Microsoft, созданная в рамках идеологии .NET. В ASP .NET заложено все для того, чтобы сделать весь цикл разработки web-приложения более быстрым, а поддержку -- более простой. ASP .NET основана на объектно-ориентированной технологии, но сохранила модель разработки asp: вы создаете программу и помещаете ее в директорию, выделенную сервером, и она будет работать. В ASP .NET появилось много новых функций, а существовавшие ранее в asp значительно усовершенствованы.

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

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

Компиляция происходит на сервере в момент первого обращения пользователя к странице. Если программист изменил текст страницы, программа перекомпилируется автоматически. При написании кода можно использовать набор компонентов, поставляемых с .NET.

Платформа .NET Framework предоставляет приложениям среду выполнения, сама непосредственно взаимодействуя с операционной системой. Выше лежит интерфейс ASP .NET-приложений, на котором в свою очередь базируются web-формы (ASP .NET-страницы) и web-сервисы. Интерфейс .NET Framework позволяет стандартизировать обращение к системным вызовам и предоставляет среду для более быстрой и удобной разработки. CLR обеспечивает единый набор сервисов для всех языков.

ASP .NET использует технологию доступа к данным ADO .NET, которая обеспечивает единый интерфейс для доступа к базам данных SQL Server и файлам XML. Кроме того, усиленная модель безопасности позволяет обеспечивать защиту клиента и сервера от несанкционированного доступа.

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

Прежде чем работать с данными, нужно убедиться, что:

· в определенное поле введена информация;

· текст в поле "Адрес" имеет форму электронного адреса (с @ и с точкой);

· дата рождения разумна, например, пользователь не сообщил о себе, что ему 300 лет или 1 годик;

· пароль достаточно сложен и не совпадает с логином.

Проверка может происходить и на стороне клиента, и на сервере. При валидации на стороне клиента в страницу встраивается код на Javascript. Если данные в форме не проходят проверку, страница просто не будет отправлена на сервер. Таким образом мы избежим лишнего трафика и не будем загружать сервер. С другой стороны, валидация на стороне сервера более надежна. Javascript-код хакеры могут легко посмотреть и отправить неправильные данные, которые пройдут эту проверку. Наконец, Javascript можно просто выключить в настройках браузера. При валидации на стороне сервера данные проверяются программой на полноценном языке. Ее код пользователю неизвестен. В результате проверки генерируется новая страница с сообщениями об ошибках. Самая разумная стратегия -- применять комбинацию этих методов. Предварительная проверка у клиента защитит от опечаток, а серьезная проверка на сервере -- от злонамеренного взлома.

Существует целый ряд серверных элементов управления, которые не занимаются выводом информации, а проверяют данные, введенные пользователем. ASP .NET 2.0 сам определяет тип браузера и генерирует наиболее подходящий для данного случая код. Если браузер поддерживает Javascript-код, который он может послать, то валидация или ее часть происходит на стороне клиента. Если браузер не поддерживает Javascript, то вся валидация происходит на сервере

Классы валидаторов образуют иерархию, во главе которой стоит абстрактный класс BaseValidator.

Рис.

1.3.1 Работа ASP.NET с базами данных

Соединение в ADO.NET может происходить с помощью различных провайдеров. В настоящее время рекомендуется работать с помощью провайдера MS SQL или Oracle. Эти провайдеры сами написаны на управляемом коде .NET. Еще один провайдер, OLEDB, позволяет получить доступ к другим источникам данных -- Access, Excel, MySql, SAP. Провайдер OLEDB написан на неуправляемом коде, но может работать вместе с .NET.

Классы ADO .NET объединены в несколько пространств имен.

System.Data -- это ядро ADO .NET. Оно содержит классы, необходимые для связи посредством любых провайдеров данных. Эти классы представляют таблицы, строки, столбцы, DataSet (множество взаимосвязанных таблиц). Там определены интерфейсы (в смысле языка C#) соединений с базами данных, команд, адаптеров данных.

System.Data.Common -- базовые классы для всех провайдеров данных -- DbConnection, DbCommand, DbDataAdapter.

В System.Data.OleDb находятся классы, позволяющие работать с источниками данных OleDb, в том числе с MS SQL версии 6.0 и ниже. Там находятся такие классы, как OleDbConnection, OleDbDataAdapter и OleDbCommand.

System.Data.Odbc содержит классы, которые работают с источниками данных ODBC посредством провайдера .NET ODBC. Классы имеют аналогичные имена с префиксом Odbc.

System.Data.SqlClient. Здесь определен провайдер данных для СУБД SQL Server версии 7.0 и выше. Содержатся классы SqlConnection, SqlTransaction, SqlCommand и другие.

В System.Data.SqlTypes находятся классы, представляющие типы данных СУБД SQL Server.

Классы ADO .NET делятся на 3 типа. Классы типа Disconnected определяют базовую структуру данных, например, DataTable. Они независимы от каких-либо провайдеров данных и могут создаваться и заселяться данными непосредственно в программе. Классы Shared -- базовые и общие для всех провайдеров. Классы Data Provider -- специфические для разных провайдеров.



2. Разработка модуля тестирования sql-запросов с применением ASP.NET

Аббревиатура SQL расшифровывается как structured query language, «язык структурированных запросов». SQL не является полноценным языком программирования; он представляет собой подъязык данных(data sublanguage). В нем имеются операторы только для создания и обработки баз данных. Однако операторы SQL можно встраивать в программы на сценарных языках, например VBScript, или универсальных языках программирования, таких как Java или C#; таким образом , в реальности ограниченность возможностей SQL не представляет проблемы.

С помощью SQL можно определять структуры базы данных, а также запрашивать и обновлять информацию в базе данных. Совокупность команд, служащих для определения данных, называют иногда языком определения данных (data definition language, DML), а совокупность команд для обновления и запроса данных - языком манипулирования данными (data manipulation language, DML).

2.1 примеры построения запросов

С помощью SQL можно создавать и модифицировать структуры баз данных. Таблицы создаются с помощью оператора CREATE TABLE. Иногда вместе с оператором CREATE TABLE используется оператор ALTER для указания первичных и вторичных ключей.

ОПЕРАТОР CREATE TABLE

Основная функция этого оператора - создание новой таблицы и описание её столбцов и типов данных. Кроме того, этот оператор позволяет определять первичные ключи с некоторыми ограничениями ссылочной целостности, а также задавать ограничения на столбцы и таблицы.

Для примера - таблице дано имя ПРОЕКТ. В скобках приведены определения четырех столбцов. Определение каждого столбца состоит из трех частей: имени столбца, его типа данных и необязательных ограничений на этот столбец. Как и все операторы SQL, оператор CREATE TABLE завершается точкой с запятой.

Листинг 2.1. базовый формат оператора CREATE TABLE

CREATE TABLE ПРОЕКТ

(ИдПроекта Integer Primary Key,

Название Char(25) Unique Not Null,

Отдел VarChar(100) Null,

МаксТрудозатраты Numeric(6.1) Default 100);

В листинге 2.1 столбец ИдПроекта принадлежит к типу данных Integer (целочисленный) и имеет свойство Primary Key. Следующий столбец, Название имеет тип данных Character (строковый) с максимальной длиной 25 символов. Поскольку этот столбец является альтернативным ключом, в его определение были добавлены ключевые слова Unique Not Null. Они означают, что значения столбца Название являются уникальными в таблице ПРОЕКТ и что этот столбец обязан иметь значение.

Третий столбец, Отдел , принадлежит к типу данных VarChar(100) и имеет свойство Null. VarChar обозначает строку переменной длины. Таким образом, в разных строках значения столбца Отдел могут различаться по длине, и максимально возможная длина строки равна 100 символам. Ключевое слово Null указывает на то, что пустые символы допустимы.

Четвертый столбец таблицы ПРОЕКТ, МаксТрудозатраты, имеет тип данных Numeric(6.1). Это означает число из шести цифр с одной цифрой после десятичной точки. (Десятичная точка не хранится и поэтому не считается за цифру.). Так, например, хранимое в этом столбце значение 123456 будет отображаться СУБД в виде 12345.6. По умолчанию, поскольку не указано иного, столбцу МаксТрудозатраты присваивается свойство Null. Запись Default 100 указывает, что при создании новой строки в таблице ПРОЕКТ столбцу МаксТрудозатраты необходимо присвоить начальное значение, равное 100.

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

Чтение заданных столбцов из одиночной таблицы

Следующий оператор запросит (прочтет) три из четырех столбцов таблицы ПРОЕКТ:

Листинг 2.2

select Название, Отдел, МаксТрудозатраты

from ПРОЕКТ;

Имена запрашиваемых столбцов перечисляются после ключевого слова SELECT , а имя отношения, из которого считываются данные, указывается после ключевого слова FROM. Результатом этого оператора при использовании данных из таблицы ПРОЕКТ будет следующая таблица:

Рис.

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

Порядок столбцов в результирующей таблице определяется порядком следования их имен после ключевого слова SELECT. Если изменить порядок имен столбцов в операторе SELECT следующим образом:

Листинг 2.3

Рис.

Чтение заданных строк из одиночной таблицы

Запросы приведенные выше выбирают определенные столбцы всех строк таблицы. Кроме этого можно выбрать все столбцы определенных строк. Например, следующий оператор запрашивает все столбцы из тех строк таблицы ПРОЕКТ, которые содержат сведения о проектах финансового отдела:

Листинг 2.4.

SELECT ИдПроекта, Название, Отдел, МаксТрудозатраты

FROM ПРОЕКТ

WHERE Отдел = 'Финансы';

Результат имеет следующий вид:

Рис.



Шаблон SELECT/FROM/WHERE - является фундаментальным шаблоном построения SQL-операторов SELECT. В предложении WHERE могут содержаться различного рода условия. Например, следующий запрос извлекает все столбцы тех строк таблицы ПРОЕКТ, где значение столбца МаксТрудозатраты превышает 100:

Листинг 2.5

SELECT *

FROM ПРОЕКТ

WHERE МаксТрудозатраты > 100;

Результат имеет следующий вид:

Рис.

Чтение заданных строк и столбцов из одиночной таблицы

Объединив описанные выше методы, мы можем выбрать из таблицы определенные столбцы и строки. Например, следующий оператор извлекает из таблицы СОТРУДНИК столбцы Имя и Отдел сотрудников бухгалтерии:

Листинг 2.6.

SELECT Имя, Отдел

FROM СОТРУДНИК

WHERE Отдел = 'Бухгалтерия';

Этот запрос выдаст следующую таблицу:

Рис.



Еще одна форма предложения WHERE предполагает задание списка значений, которые может иметь столбец. Это будем делать с помощью ключевого слова IN:

Листинг 2.7.

SELECT Имя, Телефон, Отдел

FROM Сотрудник

WHERE Отдел IN ('Бухгалтерия', 'Финансы', 'Маркетинг');

Результат таков:

Рис.

Строка исходного отношения войдет в результирующую таблицу, если её столбец Отдел имеет значения 'Бухгалтерия', 'Финансы', 'Маркетинг'.

Чтобы выбрать строки, у которых столбец Отдел не равен ни одному из этих значений, используется ключевое слово NOT IN:

Листинг 2.8.

SELECT Имя, Телефон, Отдел

FROM СОТРУДНИК

WHERE Отдел NOT IN ('Бухгалтерия', 'Финансы', 'Маркетинг');

Результат этого запроса будет следующим:



Рис.

Встроенные функции и группировка

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

Листинг 2.9.

SELECT Отдел, COUNT(*)

FROM СОТРУДНИК

GROUP BY Отдел;

Результат будет таким:

Рис.

Ключевое слово 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

Маргарита Сперанская 8.00

Маргарита Сперанская 70.50

Дмитрий Чазов 20.25

Дмитрий Чазов 25.25

Обработка результирующей таблицы

Таблицу, получившуюся в результате соединения, можно обрабатывать так же, как и любую другую таблицу. Можно сгруппировать строки по именам сотрудников и просуммировать количество часов, отработанных ими.

Листинг 2.17.

SELECT Имя, SUM(ФактТрудозатраты)

FROM СОТРУДНИК, НАЗНАЧЕНИЕ

WHERE ТабельныйНомер = НомерСотрудника;

GROUP BY Имя;

Результатом будет следующая таблица:

Дмитрий Чазов 45.50

Николай Григорьев 75.00

Хасан Джабраилов 20.25

Маргарита Сперанская 63.25

Мария Селезнева 40.50

Соединение трех таблиц

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

Листинг 2.18.



Результатом соединения будет следующая таблица:

Таблица

Все вышеперечисленные примеры запросов используются в языке SQL для получения данных из таблиц СУБД. Они могут применяться к любым базам использующим стандарт SQL-92. И могут быть применены также для тестирования знаний студентов в области составления запросов.

2.2 Создание модели данных

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

* Ведение учета географических единиц и объектов.

* Информация о любом географическом объекте, в пределах земного шара.

* Отслеживание изменений информации о географических объектах.

* Генерация отчетов об географических объектах.

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

Атрибут TRANSACTION.CustomerID может иметь пустые значения; это позволяет создать строку в таблице TRANSACTION до того, как произведение приобретет какой-нибудь клиент. Все прочие внешние ключи являются обязательными.

...