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

Введение

SQL Server 2000 является довольно сложным продуктом, работу с которым можно рассматривать с разных сторон. В частности, можно выделить два основных раздела работы с сервером, каждый из которых при ближайшем рассмотрении может быть легко разделен на более мелкие блоки: О администрирование; О программирование.

Администрирование SQL Server 2000 в свою очередь можно разделить на две части: администрирование собственно сервера и администрирование баз данных. Таким образом, администрирование баз данных представляет собой отдельную область работы с SQL Server 2000. Оно включает разработку структуры базы данных, ее реализацию, проектирование системы безопасности, создание пользователей базы данных, предоставление им прав доступа, создание объектов и т. д. Кроме того, администратор базы данных должен периодически создавать резервные копии, выполнять проверку целостности данных и следить за размером файлов как самой базы данных, так и журнала транзакций. Указанный список можно легко продолжить

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

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

1. Анализ предметной области

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

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

2. Концептуальное проектирование базы данных

2.1 Разработка схемы базы данных

2.2 Разработка структуры таблиц

Описание таблиц базы данных

№ п.п	Название	Идентификатор	Назначение	Тип связи	Атрибуты для связи
1.	Продавцы	Seller	Список продавцов	1:М Изделия	Ид. № продавца
2.	Изделия	Products	Список изделий	М:1 Продавцы	Ид. № продавца
				1:М Изделия на аукционах	Ид. № изделия
3.	Аукционы	Auctions	Список аукционов	1:М Изделия на аукционах	Ид. № аукциона
4.	Покупатели	Buyers	Список покупателей	1:М Изделия на аукционах	Ид. № покупателя
5.	Изделия на аукционах	ProductsA	Список изделий на аукционах	М:1 Изделия	Ид. № изделия
				М:1 Аукционы	Ид. № аукциона
				М:1 Покупатели	Ид. № покупателя

2.3 Описание атрибутов таблиц

Описание атрибутов таблицы Продавцы

№ п/п	Название	Идентификатор	Тип	Размер	Ограничения	Знач. по умолчанию	Обязательное поле?	Признак ключа
1.	Ид. № продавца	ID_Seller	N	3	1ч999	-	+	PK
2.	ФИО продавца	FIO_Seller	С	35	Только буквы	-	+	AK
3.	Адрес продавца	Address_ Seller	С	40	-	-	+	-
4.	Телефон продавца	Phone_Sel-ler	С	15	-	-	-	-
5.	Пол продавца	Sex_Seller	B	1	0 или 1	1	+	-

Описание атрибутов таблицы Изделия

№ п/п	Название	Идентификатор	Тип	Размер	Ограничения	Знач. по умолчанию	Обязательное поле?	Признак ключа
1.	Ид. № изделия	ID_ Pro-duct	N	5	1ч99999	-	+	PK
2.	Наименование	Name	С	35	Только буквы	-	+	-
3.	Тип	Type	С	40	-	-	+	-
4.	Дата поступления	Date_D	D	8	01.01.1980ч01.01.2010	-	+	-
5.	Признак - продано	Sold	B	1	0 или 1	1	-	-
6.	Ид. № продавца	ID_Seller	N	3	1ч999	-	+	FK

Описание атрибутов таблицы Аукционы

№ п/п	Название	Идентификатор	Тип	Размер	Ограничения	Знач. по умолчанию	Обязательное поле?	Признак ключа
1.	Ид. № аукциона	ID_Auctions	N	3	1ч999	-	+	PK
2.	Дата проведения	Date_U	D	8	01.01.1980ч01.01.2010	-	+	-
3.	Место	Place	С	40	-	-	-	-
4.	Специфика	Specifics	С	60	-	-	-	-

Описание атрибутов таблицы Покупателя

№ п/п	Название	Идентификатор	Тип	Размер	Ограничения	Знач. по умолчанию	Обязательное поле?	Признак ключа
1.	Ид. № покупателя	ID_Buyer	N	3	1ч999	-	+	PK
2.	ФИО покупателя	FIO_Buyer	С	35	Только буквы	-	+	AK
3.	Адрес покупателя	Address_ Buyer	С	40	-	-	+	-
4.	Телефон покупателя	Phone_ Buy-er	С	15	-	-	-	-
5.	Пол покупателя	Sex_Buyer	B	1	0 или 1	1	+	-

Описание атрибутов таблицы Изделия на аукционах

№ п/п	Название	Идентификатор	Тип	Размер	Ограничения	Знач. по умолчанию	Обязательное поле?	Признак ключа
1.	Ид. № изделия	ID_ Pro-duct	N	5	1ч99999	-	+	FK
2.	Ид. № аукциона	ID_Auctions	N	3	1ч999	-	+	FK
3.	№ лота	Lot	N	3	1ч999	-	+	-
4.	Стартовая цена	Price_S	R	11,2	1ч99999999	-	+	-
5.	Цена покупки	Price_B	R	11,2	1ч99999999	-	-	-
6.	Ид. № покупателя	ID_Buyer	N	3	1ч999	-	-	FK

3. Реализация базы данных в среде СУБД MS SQL Server 2000

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

Физическая база данных представляет собой набор файлов, расположенных на диске. С этими файлами можно выполнять любые операции, разрешенные для обычных файлов: копирование, переименование, удаление и т. д. Конечно, делать этого не стоит, но все же выполнение перечисленных операций в случае необходимости возможно. Физическая структура базы данных описывает количество файлов данных и журнала транзакций, из которых состоит база данных, их первоначальный и текущий размер, положение на диске, имя, расширение, шаг приращения и некоторые другие параметры. Эти параметры необходимы только для правильного восприятия SQL Server 2000 базы данных. Для пользователей, работающих с базой данных, в подавляющем большинстве случаев ее физическая структура не имеет значения.

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

3.1 Создание базы данных

Создание базы данных возможно несколькими способами:

· средствами языка Transact-SQL;

· с помощью графической утилиты Enterprise Manager

· с помощью мастера создания базы данных Create Database Wizard.

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

Помимо этого, в системной таблице sysdatabases системной базы данных master SQL Server создается новая строка, которая описывает новую базу данных. В столбце filename этой строки содержится полный путь и имя первичного файла базы данных. Всю остальную информацию о параметрах базы данных, в том числе о количестве и размещении файлов данных и журнала транзакций, сервер получает из системных таблиц базы данных, размещенных в первичном файле.

Помимо имени первичного файла, таблица sysdatabases содержит также идентификационный номер базы данных (столбец dbid), идентификатор безопасности владельца базы данных (столбец sid), дату создания (столбец crdate), уровень совместимости (столбец cmptlevel) и другую информацию.

Создание новой базы данных выполняется с помощью окна Database Properties (рисунок 2). Открыть это окно можно разными способами:

· выбрав в контекстном меню папки Databases команду New Database; база данные таблица утилита

· щелкнув правой кнопкой мыши на пустом пространстве правой части и выбрав в открывшемся контекстном меню команду New Database;

· нажав в панели инструментов Enterprise Manager кнопку New Database;

· выбрав в меню Action (Действия ) команду New Database.

Окно свойств базы данных Database Properties имеет три вкладки. Первая вкладка General (рисунок 2) предназначена для указания имени базы данных и сопоставления, которое будет использоваться для базы данных. Остальные элементы управления вкладки General предназначены для предоставления пользователю различной информации о базе данных. На момент создания базы данных этой информации еще не существует, и поэтому указываются значения Unknown (неизвестно) и None (нет).

При выборе имени базы данных, которое должно быть введено в поле Name, следует придерживаться тех же правил, которые используются при непосредственной работе с командой Create Database. Сопоставление, которое будет иметь база данных, выбирается с помощью раскрывающегося списка Collation name. По умолчанию список содержит значение (Server default), что предписывает применять для базы данных то же сопоставление, которое было указано на уровне сервера при установке SQL Server 2000. Однако можно выбрать и любое другое сопоставление.

Вкладка Data Files (рисунок 3) предназначена для определения файлов данных, из которых будет состоять создаваемая база данных. В верхней части вкладки Data Files расположена таблица Database files, с помощью которой собственно и определяются файлы базы данных. В столбце File Name указывается логическое имя файла, в столбце Location задается полный путь и имя файла операционной системы. Отметим, что указанный в столбце Location файл не должен существовать на момент создания базы данных. Путь и имя файла могут быть введены вручную или выбраны с помощью окна Locate Database File, открыть которое можно с помощью кнопки, расположенной в левой части столбца Location.

В столбце Initial size (MB) находится первоначальный размер, который файл будет иметь непосредственно после создания базы данных. Если отсутствует какой-либо суффикс, то подразумевается, что значение указано в мегабайтах.

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

Помимо сведений, указываемых в таблице Database files, файлы базы данных имеют дополнительные свойства, такие, как максимальный размер и шаг прироста. Управление этими свойствами осуществляется с помощью группы элементов управления File properties, расположенной в нижней части вкладки Data Files.

Вкладка Transaction Log (рисунок 4) предназначена для управления файлами журнала транзакций. Эта вкладка в значительной степени напоминает вкладку Data Files. Единственное различие между ними состоит в том, что при определении файлов журнала транзакций нельзя работать с группами файлов.

После того как все файлы базы данных будут определены, а также указано имя базы данных и сопоставление, остается только нажать кнопку ОК и Enterprise Manager приступит к непосредственному созданию базы данных. Для этого он сгенерирует код команды create database на основе введенных пользователем значений и выполнит его. Рассмотрение создания базы данных средствами Enterprise Manager можно считать оконченным.

3.2 Основные принципы создания таблиц

Вначале таблицы необходимо создать. Во время этой операции пользователь определяет имя таблицы, имена столбцов, тип хранимых в них данных, значения по умолчанию, возможность хранения неопределенных значений, первичный и внешний ключи и некоторые другие свойства. Создание таблиц в SQL Server 2000 возможно либо с помощью графического интерфейса Enterprise Manager, либо с помощью команд Transact-SQL. К сожалению, мастера создания таблиц в SQL Server 2000 нет.

При создании таблиц пользователь может для столбцов, помимо задания базовых свойств, таких, как имя, тип данных, размер и точность, указать ограничения целостности. Ограничения целостности (constraints) - это механизм контроля значений, которые могут храниться в полях строки. В SQL Server 2000 поддерживаются следующие ограничения целостности:

· Check - с помощью логических условий налагает ограничение на значения, которые могут храниться в столбце;

· Null - задает возможность хранения неопределенных значений;

· Default - определяет значение по умолчанию;

· Unique - гарантирует уникальность значений в столбце;

· Primary Key - определяет первичный ключ;

· Foreign Key - определяет внешний ключ;

· No Action - предписывает не выполнять в зависимой таблице никаких действий при удалении или обновлении строк в главной таблице;

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

Каждая база данных имеет свой собственный набор таблиц, посмотреть который можно средствами Enterprise Manager, открыв папку Tables. При открытии папки Tables базы данных в правом окне Enterprise Manager появляется список таблиц со следующими атрибутами: имя таблицы, владелец (обычно владелец базы данных data base owner-dbo), тип (пользовательская или системная) и дата создания таблицы. Для просмотра самой таблицы следует дважды щелкнуть по ней или в ее контекстном меню исполнить команду Properties.

В открывшемся окне представлены все свойства таблицы и ее столбцов. Кнопка Permission позволяет просмотреть и отредактировать права доступа к таблице: SELECT, INSERT, UPDATE, DELETE, EXES и DRI. Щелкнув по столбцу таблицы, можно просмотреть и отредактировать права доступа к столбцу. Если установлена на сервере служба MSSearch, то с помощью вкладки Full-text Indexing можно отредактировать параметры этой службы.

Создание таблицы выполняется с помощью окна New Table (рисунок 8), для открытия которого достаточно в контекстном меню папки Tables выбрать команду New Table.

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

Создание таблицы выполняется с помощью окна New Table (рисунок 6), для открытия которого достаточно в контекстном меню папки Tables выбрать команду New Table.

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

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

В SQL Server 2000 имеется средство, облегчающее понимание структуры базы данных, позволяющее наглядно представлять структуру таблиц и связей между ними. Это средство называется диаграммой. Диаграммы определены только на уровне Enterprise Manager и являются надстройкой над объектами базы данных.

При выборе первого из них столбец конфигурируется в качестве уникального глобального идентификатора строки, что соответствует указанию ключевого слова rowguidcol при описании столбца в команде create table. Таким образом, при добавлении в таблицу новой строки в соответствующий столбец будет автоматически помешаться уникальное значение. Только один столбец в таблице может быть сконфигурирован как уникальный глобальный идентификатор строки. Так же можно задавать ограничения с помощью команды Check Constrains

После того как будут сконфигурированы параметры всех столбцов, необходимо сохранить сконфигурированную таблицу. Для этого достаточно нажать кнопку Save, расположенную в панели инструментов. При этом будет выведено окно Choose Name, с помощью которого следует ввести имя, которое будет присвоено сконфигурированной таблице. На этом работу по созданию таблиц можно закончить. Однако можно определить в таблице ограничения целостности, проиндексировать те или иные столбцы, а также указать в какой группе файлов должна быть расположена таблица. Для управления этими свойствами используется окно Properties (Свойства), которое открывается нажатием кнопки Table and Index Properties (Свойства таблиц и индексов) на панели инструментов.

Для просмотра созданных диаграмм необходимо в окне Enterprise Manager выбрать требуемую базу данных и открыть папку Diagram. Диаграмма не имеет свойств, для просмотра диаграммы необходимо дважды щелкнуть на ней левой клавишей мыши.

Окно просмотра диаграммы.

4. Доступ и обработка данных с помощью утилиты Enterprise Manager

4.1 Создание представлений для доступа к данным

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

Рисунок 9. Окно списка представлений базы данных.

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

Создание представлений в SQL Server 2000 можно выполнить различными методами:

· с помощью Enterprise Manager;

· с помощью мастера Create View Wizard;

· средствами Transact-SQL.

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

Рисунок 10. Создание представлений

При работе с Enterprise Manager список представлений, имеющихся в базе данных, находится в папке Views (Представления). Как видно из рисунка 11, для каждого представления указывается его Имя (столбец Name), владелец (столбец Owner), тип (type) и дата создания (Create Date). Представление может быть отнесено к одному из двух типов - системному или пользовательскому.

Для создания нового представления достаточно выбрать в контекстном меню папки Views команду New View (Новое представление) или нажать на кнопку New (Создать) на панели инструментов.

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

4.2 Создание и управление индексами

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

Индекс - это своего рода предметные указатели для таблиц баз данных.

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

В SQL Server 2000 реализованы эффективные алгоритмы поиска нужного значения в упорядоченной последовательности данных. Одним из таких алгоритмов является алгоритм деления пополам.

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

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

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

В MS SQL Server 2000 реализованы следующие типы индексов:

• кластерные индексы

• некластерные индексы

• уникальные индексы.

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

Некластерные индексы являются наиболее типичными. Они не требуют перестройки физической структуры таблицы, а лишь реализуют ссылки на соответствующие строки. Эти ссылки имеют следующую структуру:

Ё идентификационный номер файла, в котором хранится строка

Ё идентификационный номер страницы данных

Ё номер строки на странице

Ё содержимое столбца.

При создании в таблице первичного ключа с помощью слов Primary Key сервер автоматически создает для него кластерный индекс, если он не был задан ранее или для первичного ключа не задан NONEKLUSTERED.

Уникальные индексы гарантируют уникальные значения в индексируемом столбце.

Он является надстройкой для таблицы и может быть реализован как для кластерного, так и для некластерного индекса.

При определении индекса надо задавать параметр факта заполнения (fill factor).

Право на создание индекса имеет только владелец таблицы. Индекс можно создать тремя способами:

a. мастером Create Index Wizard

b. с помощью Enterprise Manager

c. с помощью языка Transact-SQL

4.3 Создание запросов

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

Запросы необходимы для выборки данных из таблиц созданной базы данных. Выборка данных из базы данных производится с помощью команды SELECT:

Select [Name]

From [Products]

Where [Date_D]='23.04.2008'

Order by [Type] DESC,

[Name] ASC

В общем случае команда SELECT начинается с ключевого слова SELECT, за ним идет список имен столбцов, которые Вы хотите видеть, или *(звездочка) если Вы хотите видеть все столбцы. За тем идет ключевое слово FROM, далее имя таблицы, к которой делается запрос.

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

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

Логические операторы:

· AND логическое "И"

· OR логическое "ИЛИ"

· NOT логическое отрицание

Оператор AND сравнивает два логических значения и возвращает TRUE (истина), если оба значения истинны (т.е. равны TRUE), в остальных случаях - FALSE (ложь). Оператор OR возвращает TRUE, если хотя бы один из аргументов равен TRUE. Оператор NOT возвращает TRUE, если его аргумент равен FALSE и наоборот.

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

Рассмотрим теперь специальные операторы: IN, BETWEEN, LIKE, IS NULL.Оператор IN определяет список значений, в который должно входить значение поля

SELECT *

FROM Salespeople

WHERE city = 'Москва' OR

city = 'Хабаровск'

Но есть есть более простой способ:

SELECT *

FROM Salespeople

WHERE city IN ( 'Москва', 'Хабаровск' )

Результат этого запроса:

SNUM SNAME CITY COMM

1001 Иванов Москва 12.0

1002 Петров Хабаровск 13.0

Набор значений для оператора IN заключается в круглые скобки, значения разделяются запятыми.

Оператор BETWEEN похож на оператор IN. В отличие от списка допустимых значений BETWEEN определяет диапазон значений. В запросе Вы должны указать слово BETWEEN затем начальное значение, ключевое слово AND и конечное значение

Оператор LIKE применим только к символьным полям, с которыми он используется, чтобы находить подстроки. Т.е. он ищет поле символа, чтобы видеть совпадает ли с условием часть его строки. В качестве условия он использует специальные символы:

select * from seller where address_seller like '%Пенза%'

union all

select * from buyers where addres_buyer like '%Пенза%'

SQL Server предоставляет несколько агрегатных функций:

· COUNT - производит подсчет строк, удовлетворяющих условию запроса

· SUM - вычисляет арифметическую сумму всех значений колонки

· AVG - вычисляет среднее арифметическое всех значений

· MAX - определяет наибольшее из всех выбранных значений

· MIN - определяет наименьшее из всех выбранных значений

Функции SUM и AVG применимы только к числовым полям. С COUNT, MAX, MIN могут использоваться числовые или символьные поля. При использовании с символьными полями MAX, MIN сравнивают значения в алфавитном порядке. Агрегатные функции при своей работе игнорируют значения NULL.

Параметры ASC и DESC определяют метод сортировки ключевых элементов - соответственно по возрастанию или по убыванию

4.3 Хранимые процедуры и триггеры

Часто разработчикам приходится реализовывать сложные алгоритмы поддержки целостности данных. Использование ограничений целостности Primary Key, Foreign Key и других предоставляют разработчикам достаточно эффективные механизмы обеспечения целостности данных. Однако их бывает недостаточно. Например, с помощью упомянутых механизмов нельзя разрешить изменение данных в том случае, если в одном из столбцов находится определенное значение.

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

Триггеры (triggers) в SQL Server 2000 представляют собой набор команд Transact-SQL, выполняемых автоматически при осуществлении тех или иных модификаций данных в таблице. Физически триггеры являются ни чем иным, как хранимыми процедурами специального типа. Каждый триггер связан с конкретной таблицей и запускается сервером автоматически каждый раз, когда пользователи пытаются произвести вставку, изменение или удаление данных. Триггер получает всю информацию о выполняемых пользователем изменениях в таблице. Разработчик реализовывает в триггере необходимые проверки и изменения данных в других таблицах базы данных.

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

Триггеры различаются по типу команд, на которые они реагируют:

· INSERT TRIGGER - запускаются при попытке вставить данные с помощью команды INSERT;

· UPDATE TRIGGER - запускаются при попытке изменения данных с помощью команды UPDATE;

· DELETE TRIGGER - запускаются при попытке удаления данных с помощью команды DELETE.

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

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

В SQL Server 2000 различают несколько типов хранимых процедур:

· системные хранимые процедуры, предназначенные для работы с системными данными;

· расширенные хранимые процедуры, представляющие собой динамически подключаемые программы, которые в операционной среде могут играть роль самостоятельного приложения;

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

Заключение

Microsoft SQL Server 2000 - это законченное предложение в области баз данных и анализа данных для быстрого создания масштабируемых решений электронной коммерции, бизнес-приложений и хранилищ данных. Оно позволяет значительно сократить время выхода этих решений на рынок, одновременно обеспечивая масштабируемость, отвечающую самым высоким требованиям. В сервер SQL Server 2000 включена поддержка языка XML и протокола HTTP, средства повышения быстродействия и доступности, позволяющие распределить нагрузку и обеспечить бесперебойную работу, функции для улучшения управления и настройки, снижающие совокупную стоимость владения. Кроме того, SQL Server 2000 полностью использует все возможности операционной системы Windows, включая поддержку до 32 процессоров и 64 ГБ ОЗУ.

Система управления базами данных SQL Server 2000 предоставляет пользователям широкие возможности по разработке и сопровождению баз данных. Для этого в составе системы имеется набор графических средств (Enterprise Manager, Query Analyzer), языковых средств (язык Transact-SQL), набор хранимых процедур.

Основными задачами в процессе разработки и сопровождения баз данных в среде SQL Server 2000 являются создание, модификация и удаление баз данных, таблиц, а также объектов баз данных, таких как индексы, представления, запросы, хранимые процедуры и триггеры.

В результате выполнения курсового проекта была создана база данных обслуживания фирмы, проводящей аукционны. На основе полученных знаний о MS SQL Server 2000 сделали вывод, что эта программа позволяет не только создавать базы данных, но обработку данных и выдачу ответов на запросы.

Список используемых источников

1. А.В.Сивохин, С.В.Шибанов, С.В.Самуилов. Разработка и сопровождение БД в среде MS SQL Server 2000 (Текстовый документ). - Пенза, 2004.

2. Е. Мамаев. MS SQL Server 2000. - СПб.: Питер 2002.

3. Л. Шкарина Язык SQL: учебный курс, - СПб.: Питер, 2001, - 592 с.

4. Конноли Т., Бегг Л., Страчан А. Базы данных. Проектирование, реализация и сопровождение. Теория и практика. 2-е издание. - М.: Вильямс, 2000.

Приложение

Таблица «Аукционы»

Таблица «Покупатели»

Таблица «Продавцы»

Таблица «Продукты»

Таблица «Продукты на аукционах»

Запрос на выборку данных №1

Задание: Выдать список всех изделий, которые были получены в заданную дату. Список упорядочить по типу изделия по убыванию, по наименованию - по возрастанию.

Запрос на выборку данных №2

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

Запрос на выборку данных №3

Задание: Вывести список всех выставляемых на аукцион изделий, стартовая цена которых ниже средней по изделиям.

Запрос на выборку данных №4

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

Запрос на выборку данных №5

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

Представление №1

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

Представление №2

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

Процедура №1

Задание: написать хранимую процедуру, которая обеспечит корректное удаление поставщика, предварительно удалив все поставленные им единицы хранения

Процедура №2

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

Диаграмма

Триггер №1

Задание: При удалении записи о покупателе проверять, были ли совершены им какие-либо покупки. Покупатель, совершивший хотя бы одну покупку, не может быть удален.

Триггер №2

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

Триггер №3

Размещено на Allbest.ru