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

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

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ

РОССИЙСКОЙ ФЕДЕРАЦИИ

МОСКОВСКАЯ ФИНАНСОВО-ЮРИДИЧЕСКАЯ АКАДЕМИЯ

КИРОВСКИЙ ФИЛИАЛ

КАФЕДРА «ИНФОРМАЦИОННЫЕ СИСТЕМЫ В ЭКОНОМИКЕ»

Курсовой проект по дисциплине

«Реляционные базы данных на основе SQL Server 8.0»

Импортирование и администрирование

на MS SQL SERVER 8.0 базы данных «Аптечный склад»

Киров 2007

Содержание

• Введение
• 1. Постановка задачи
• 2. Подготовка сервера к импорту базы данных
• 2.1 Обследование сервера с использованием хранимых процедур
• 3. Импорт данных
• 3.1 Фиксация результатов импорта
• 3.2 Обследование вновь импортированной базы данных
• 3.3 Дополнение базы данных необходимыми хранимыми процедурами и триггерами
• 3.4 Создание пользователя БД и нового login для него
• 3.5 Создание резервной копии БД
• 3.6 Разработка триггеров и хранимых процедур.
• 4. Разработка клиентского приложения
• Заключение
• Библиографический список

Введение

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

Структурой хранения данных в SQL Server 8.0 является база. Вся работа SQL Server 8.0 сводится к управлению базами данных (БД).

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

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

База данных SQL Server 8.0 хранится в самостоятельном, уникальном для каждой БД наборе файлов. Кроме того, журнал транзакций и сами данные обязательно хранятся отдельно. Это повышает отказоустойчивость базы данных в случае сбоев системы.

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

База данных является базовым элементом SQL Server 8.0 и своего рода контейнером, в котором располагаются объекты и данные. Любой объект должен принадлежать базе данных. Каждая база данных имеет свою систему безопасности, связанную с системой безопасности SQL Server. Любой пользователь при обращении к серверу работает в контексте какой-то базы данных. Каждой базе данных сопоставлен пользователь, который является ее владельцем. Этот пользователь имеет имя и ему предоставлены определенные права в базе данных.

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

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

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

В рамках данной курсовой работы будет рассмотрено администрирование сервера MS SQL 8.0 и создание клиентского приложения.

1. Постановка задачи

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

Необходимо осуществить импорт базы данных в формат SQL Server 8.0. Процесс импорта данных нужно зафиксировать и провести анализ результатов: общее количество импортированных таблиц, возможные ошибки при импорте (не импортировалась какая-либо таблица, или импортировалась только структура данной таблицы). При наличии ошибок попытаться устранить их средствами сервера или иными средствами.

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

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

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

2. Подготовка сервера к импорту базы данных

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

2.1 Обследование сервера с использованием хранимых процедур

Сведения о сервере можно узнать с помощью системных хранимых процедур.

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

sp_server_info - возвращает список текущих значений свойств SQL сервера (имя, версия, схема сортировки, применяемая в БД на сервере COLLATION-SEQ)

Attribute_id	Attribute_name	Attribute_value
1	DBMS_NAME	Microsoft SQL Server
2	DBMS_VER	Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19 2001 13:23:50 Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1, v.1039)
10	OWNER_TERM	owner
11	TABLE_TERM	table
12	MAX_OWNER_NAME_LENGTH	128
13	TABLE_LENGTH	128
14	MAX_QUAL_LENGTH	128
15	COLUMN_LENGTH	128
16	IDENTIFIER_CASE	MIXED
17	TX_ISOLATION	2
18	COLLATION_SEQ	charset=cp1251 collation=Cyrillic_General_CI_AS
19	SAVEPOINT_SUPPORT	Y
20	MULTI_RESULT_SETS	Y
22	ACCESSIBLE_TABLES	Y
100	USERID_LENGTH	128
101	QUALIFIER_TERM	database
Attribute_id	Attribute_name	Attribute_value
102	NAMED_TRANSACTIONS	Y
103	SPROC_AS_LANGUAGE	Y
104	ACCESSIBLE_SPROC	Y
105	MAX_INDEX_COLS	16
106	RENAME_TABLE	Y
107	RENAME_COLUMN	Y
108	DROP_COLUMN	Y
109	INCREASE_COLUMN_LENGTH	Y
110	DDL_IN_TRANSACTION	Y
111	DESCENDING_INDEXES	Y
112	SP_RENAME	Y
113	REMOTE_SPROC	Y
500	SYS_SPROC_VERSION	8.00.375

sp_configure - отображает глобальные настройки текущего сервера: минимальные/максимальные значения параметров, текущее значение параметров (число подключений пользователей, время ожидания ввода логина)



name	minimum	maximum	сonfig_value	run_value
affinity mask	-2147483648	2147483647	0	0
allow updates	0	1	0	0
awe enabled	0	1	0	0
c2 audit mode	0	1	0	0
cost threshold for parallelism	0	32767	5	5
cursor threshold	-1	2147483647	-1	-1
default full-text language	0	2147483647	1033	1033
default language	0	9999	21	21
fill factor (%)	0	100	0	0
index create memory (KB)	704	2147483647	0	0
lightweight pooling	0	1	0	0
locks	5000	2147483647	0	0
max degree of parallelism	0	32	0	0
max server memory (MB)	4	2147483647	255	255
max text repl size (B)	0	2147483647	65536	65536
max worker threads	32	32767	255	255
media retention	0	365	0	0
min memory per query (KB)	512	2147483647	1024	1024
min server memory (MB)	0	2147483647	255	255
nested triggers	0	1	1	1
network packet size (B)	512	65536	4096	4096
open objects	0	2147483647	0	0
priority boost	0	1	0	0
query governor cost limit	0	2147483647	0	0
query wait (s)	-1	2147483647	-1	-1
recovery interval (min)	0	32767	0	0
remote access	0	1	1	1
remote login timeout (s)	0	2147483647	20	20
remote proc trans	0	1	0	0
name	minimum	maximum	сonfig_value	run_value
remote query timeout (s)	0	2147483647	600	600
scan for startup procs	0	1	0	0
set working set size	0	1	0	0
show advanced options	0	1	1	1
two digit year cutoff	1753	9999	2049	2049
user connections	0	32767	0	0
user options	0	32767	0	0

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

name	db_size	owner	dbid	created	status
ASBase	4.00 MB	anonymous	9	Apr 23 2007	Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=Cyrillic_General_CI_AS, SQLSortOrder=0, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics
master	17.31 MB	sa	1	Aug 6 2000	Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=Cyrillic_General_CI_AS, SQLSortOrder=0, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics
model	1.25 MB	sa	3	Aug 6 2000	Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=539, Collation=Cyrillic_General_CI_AS, SQLSortOrder=0, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics
msdb	14.00 MB	sa	4	Aug 6 2000	Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=Cyrillic_General_CI_AS, SQLSortOrder=0, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics
Northwind	4.25 MB	sa	6	Aug 6 2000	Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=Cyrillic_General_CI_AS, SQLSortOrder=0, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics
pubs	2.50 MB	sa	5	Aug 6 2000	Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=Cyrillic_General_CI_AS, SQLSortOrder=0, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics
tempdb	8.75 MB	sa	2	Apr 19 2007	Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=Cyrillic_General_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics

sp_help - предназначена для получения информации о различных объектах базы данных (В данном случае новой БД ASBase)

name	owner	object type
sysconstraints	dbo	view
syssegments	dbo	view
firms	dbo	user table
movetov	dbo	user table
payments	dbo	user table
tov	dbo	user table
typew	dbo	user table
waybill	dbo	user table
syscolumns	dbo	system table
syscomments	dbo	system table
sysdepends	dbo	system table
sysfilegroups	dbo	system table
sysfiles	dbo	system table
sysfiles1	dbo	system table
sysforeignkeys	dbo	system table
sysfulltextcatalogs	dbo	system table
sysfulltextnotify	dbo	system table
sysindexes	dbo	system table
sysindexkeys	dbo	system table
sysmembers	dbo	system table
sysobjects	dbo	system table
syspermissions	dbo	system table
sysproperties	dbo	system table
sysprotects	dbo	system table
sysreferences	dbo	system table
systypes	dbo	system table
sysusers	dbo	system table

3. Импорт данных

Для импорта данных используются службы DST (Data Transformation Services), которые дают возможность преобразовать базы данных в формат SQL Server 8.0. Это удобно при необходимости перехода с одной СУБД на другую, так как структура базы данных может быть очень большой и в ней может быть накоплено большое количество данных.

Импорт данных можно осуществить в режиме пошагового диалога, который реализован в виде DTS Import/Export Wizard.

3.1 Фиксация результатов импорта

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

Место назначения остается по умолчанию Microsoft OLE DB Provider for SQL Server.



Затем определяется сервер, на который импортируются данные (INETSERVER).

Определяется способ аутентификации пользователей:

Windows NT authentication - аутентификация Windows NT (доступно только если установлена ОС семейства Windows NT)

SQL Server authentication - аутентификация SQL сервера.

Указывается имя пользователя и пароль (если необходимо).

Указывается имя и размер новой базы

Далее подтверждаем создание базы из существующей, выбираем переносимые таблицы. На данном этапе можно настроить типы данных и размерность для каждой таблицы и вида (во избежание ошибок меняем предложенный MS SQL Server формат текстовых данных nvarchar на varchar (ранее в моей практике встречалась ошибка. Когда при выборе формата nvarchar текстовые данные превращались в, так называемые «крукозябры» вне зависимости от исходной кодировки данных). Переходим к следующему этапу.

Отказываемся от создания DTS пака, ставим галочку «Run immediately» («Понеслась»), жмем далее. Мастер сообщит о завершении работы с ним и предложит нажать «Finish». После чего выведется отчет о конвертации и создании БД.

Как видно на скриншоте база создана без ошибок, теперь следует протестировать её.

3.2 Обследование вновь импортированной базы данных

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



Выполняем процедуру sp_helpdb ASBase. И получаем результат:

name	db_size	owner	dbid	created	status
ASBase	4.00 MB	INETSERVER\Administrator	9	May 2 2007	Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=Cyrillic_General_CI_AS, SQLSortOrder=0, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics

Таблица показывает владельца базы данных «ASBase», её размер, владельца, дату создания.

name	fileid	filename	filegroup	size	maxsize	growth	usage
ASBase_dat	1	E:\MSSQL\MSSQL\Data\ASBase.mdf	PRIMARY	2048 KB	Unlimited	10%	data only
ASBase_log	2	E:\MSSQL\MSSQL\Data\ASBase.ldf	NULL	2048 KB	Unlimited	10%	log only

Данная таблица показывает свойства файлов базы данных «ASBase».

3.3 Дополнение базы данных необходимыми хранимыми процедурами и триггерами

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

Table_Qualifier	Table_Owner	Table_Name	Table_Type	Remarks
ASBase	dbo	syscolumns	SYSTEM TABLE	NULL
ASBase	dbo	syscomments	SYSTEM TABLE	NULL
ASBase	dbo	sysdepends	SYSTEM TABLE	NULL
ASBase	dbo	sysfilegroups	SYSTEM TABLE	NULL
ASBase	dbo	sysfiles	SYSTEM TABLE	NULL
ASBase	dbo	sysfiles1	SYSTEM TABLE	NULL
ASBase	dbo	sysforeignkeys	SYSTEM TABLE	NULL
ASBase	dbo	sysfulltextcatalogs	SYSTEM TABLE	NULL
ASBase	dbo	sysfulltextnotify	SYSTEM TABLE	NULL
ASBase	dbo	sysindexes	SYSTEM TABLE	NULL
ASBase	dbo	sysindexkeys	SYSTEM TABLE	NULL
ASBase	dbo	sysmembers	SYSTEM TABLE	NULL
Table_Qualifier	Table_Owner	Table_Name	Table_Type	Remarks
ASBase	dbo	sysobjects	SYSTEM TABLE	NULL
ASBase	dbo	syspermissions	SYSTEM TABLE	NULL
ASBase	dbo	sysproperties	SYSTEM TABLE	NULL
ASBase	dbo	sysprotects	SYSTEM TABLE	NULL
ASBase	dbo	sysreferences	SYSTEM TABLE	NULL
ASBase	dbo	systypes	SYSTEM TABLE	NULL
ASBase	dbo	sysusers	SYSTEM TABLE	NULL
ASBase	dbo	firms	TABLE	NULL
ASBase	dbo	movetov	TABLE	NULL
ASBase	dbo	payments	TABLE	NULL
ASBase	dbo	tov	TABLE	NULL
ASBase	dbo	typew	TABLE	NULL
ASBase	dbo	waybill	TABLE	NULL
ASBase	dbo	sysconstraints	VIEW	NULL
ASBase	dbo	syssegments	VIEW	NULL

Структуру таблиц в SQL Server см. Приложение А.

Для просмотра колонок таблицы и их значений применяем процедуру sp_columns tov:

TableQualifier	TableOwner	TableName	Column_name	Data_type	Type_name
ASBase	dbo	tov	TOVID	6	float
ASBase	dbo	tov	TNMED	12	varchar
ASBase	dbo	tov	TNFCT	12	varchar
ASBase	dbo	tov	TNCNF	12	varchar
ASBase	dbo	tov	TTRN	6	float
ASBase	dbo	tov	TMNN	6	float
ASBase	dbo	tov	TDATEB	11	smalldatetime
ASBase	dbo	tov	TDATEE	11	smalldatetime
ASBase	dbo	tov	TAMOUNT	6	float
ASBase	dbo	tov	TPRICE	6	float
ASBase	dbo	tov	TOPT	6	float
ASBase	dbo	tov	TROZN	6	float

Подробную информацию о таблицах базы данных (тип, владелец, когда создана таблица) можно просмотреть с помощью процедуры sp_help tov:

NAME	OWNER	TYPE	CREATEDDATATIME
tov	dbo	user table	23.04.2007 9:01

Выполним недокументированную процедуру фирмы Microsoft для таблицы table1:sp_mshelpcolumns 'tov'

Col_name	Col_id	Col_tyupename	Col_len	Col_prec	Col_basetypename
TOVID	1	float	8	NULL	float
TNMED	2	varchar	255	NULL	varchar
TNFCT	3	varchar	150	NULL	varchar
TNCNF	4	varchar	25	NULL	varchar
TTRN	5	float	8	NULL	float
TMNN	6	float	8	NULL	float
TDATEB	7	smalldatetime	4	NULL	smalldatetime
TDATEE	8	smalldatetime	4	NULL	smalldatetime
TAMOUNT	9	float	8	NULL	float
TPRICE	10	float	8	NULL	float
TOPT	11	float	8	NULL	float
TROZN	12	float	8	NULL	float

Просмотреть кто работает на сервере можно с помощью процедуры sp_who:

spid	ststus	loginame	hostname	blk	dbname	cmd
1	sleeping	sa		0	NULL	LOG WRITER
2	background	sa		0	NULL	LOCK MONITOR
3	background	sa		0	NULL	LAZY WRITER
4	background	sa		0	master	SIGNAL HANDLER
5	background	sa		0	master	TASK MANAGER
6	background	sa		0	master	TASK MANAGER
7	sleeping	sa		0	NULL	CHECKPOINT SLEEP
8	background	sa		0	master	TASK MANAGER
9	background	sa		0	master	TASK MANAGER
10	background	sa		0	master	TASK MANAGER
11	background	sa		0	master	TASK MANAGER
12	background	sa		0	master	TASK MANAGER
13	background	sa		0	master	TASK MANAGER
14	background	sa		0	master	TASK MANAGER
51	sleeping	anonymous	INPUT-1	0	sales	AWAITING COMMAND
52	runnable	INETSERVER\ Administrator	INETSERVER	0	ASBase	SELECT

3.4 Создание пользователя БД и нового login для него

Для назначения каких-либо определенных прав пользователям применяются учетные записи пользователей (SQL Server login). Каждая учетная запись идентифицируется именем, может иметь пароль и определяет набор прав доступа к SQL Server.

Добавить нового пользователя и назначить ему соответствующие права можно из Enterprise Manager.

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

-- В первую очередь необходимо проверить существует ли пользователь

if exists (select * from master.dbo.syslogins where loginname = 'User1') begin

if exists (select * from ASBase.dbo.sysusers where name = 'User1' and islogin=1)

-- И если существует удалить его перед созданием

exec sp_dropuser 'User1'

Exec sp_droplogin 'User1'

End

-- Далее создаем новый логин

exec sp_addlogin 'ch', null, 'ASBase'

-- Проверим существовал ли одноименный пользователь в БД

if exists (select * from ASBase.dbo.sysusers where name = 'ch' and islogin=1)

-- Если существовал - удалим его

exec sp_dropuser 'User1'

-- И создаем нового пользователя (атрибуты процедуры - логин, системное имя и группа - пока пусть будет в public)

exec sp_adduser'сh','ch','public'

-- Перед созданием новой группы проверим существовала ли она ранее

if exists (select * from ASBase.dbo.sysusers where name = 'StUser' and islogin=0)

--И удалим ее если да

exec sp_dropRole ' StUser '

-- Создадим группу StUser

exec sp_addrole ' StUser '

declare @memuid smallint

select @memuid = uid from sysusers where name = 'ch' and isaliased = 0

if @memuid is null - проверим на принадлежность пользователя группе

exec sp_droprolemember ' StUser ', 'ch'

--И добавим пользователя в группу

exec sp_addrolemember ' StUser ','ch'

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

GRANT select,update,delete,insert ON waybill TO [StUser]

GRANT select,update,delete,insert ON movetov TO [StUser ]

GRANT select,update,delete,insert ON firms TO [StUser]

GRANT select,update,delete,insert ON tov TO [StUser]

GRANT select,update,delete,insert ON payments TO [StUser]

GRANT select ON typew TO [StUser] - пользователь будет иметь полный доступ ко всем таблицам кроме typew (таким образом организована защита от изменения, изменять эту таблицу не имеет смысла).

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

3.5 Создание резервной копии БД

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

При первом копировании предлагает только имя папки, где расположится файл копии. Имя файла необходимо указать.

Выбрать в ЕМ БД ASBase. В контекстном меню БД отметить пункт “Все задачи(All tasks)\Backup Database…”.

В появившемся диалоговом окне в закладке “Option”отметить все доступные флажки (проверить запись после завершения и проверить имя). В закладке “General” отметить радиокнопки “Database-complete” и “Append to media”. Этим будет задан режим полного копирования БД+LOG в один файл с добавлением данных в конец файла. Для создания файла копии нажать кнопку “Add…”. В появившемся окне “Select backup destination” предложит путь к файлу E:\MSSQL\MSSQL\BACKUP\. Дописать имя файла «ASBase». Получится E:\MSSQL\MSSQL\BACKUP\ASBase . Если файлы копий уже созданы, то их можно удалить (устарели) или просмотреть их содержимое. Сам процесс копирования можно выполнить немедленно, нажав кнопку «ОК» в закладке “General”, или по расписанию, используя раздел “Shedule” в закладке “General”.

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

Для успешного восстановления после отказа БД, ее надо перевести, используя опции БД, в режим “Single user”и “select into/bulk copy”. Далее выполнить восстановление. При отказе в восстановлении БД можно удалить и попытаться восстановить из копии. В этом варианте могут начаться проблемы по настройкам БД и пользователям БД. Выполнить: удалить БД ASBase. Запустить мастер восстановления БД указать ему имя БД восстановления ASBase и имя копии выбрать из списка ASBase. Проверяется запросом SELECT работоспособность восстановленной БД.

Резервное копирование можно осуществлять и в Query Analyzer. Используем скрипт:

use ASBase

exec sp_addumpdevice 'disk','ASBase',' E:\MSSQL\MSSQL\BACKUP\ASBase.dat'

backup database ASBase to ASBase

В скрипте вначале создается и именуется процедурой sp_addumpdevice устройство копирования, так как без «визуального» мастера сервер не имеет нужных ему указаний. Устройство связывается с физическим файлом. Оператор backup database копирует указанную БД на указанное устройство.

Для восстановления БД используется скрипт:

Restore database ASBase from disk=' E:\MSSQL\MSSQL\BACKUP\ASBase.dat' with replace

3.6 Разработка триггеров и хранимых процедур

Часто разработчикам приходится реализовывать сложные алгоритмы поддержания целостности данных. Имеется много средств, предоставляющих разработчикам достаточно эффективные механизмы обеспечения целостности данных. Но этих средств часто бывает недостаточно. Например, с помощью таких механизмов как PRIMARY KEY, FOREIGN KEY, UNIQUE, нельзя разрешить изменение данных только в том случае, если в одном из столбов находится определённое значение.

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

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

Создадим для таблицы typew триггер type_i_tr, который будет запрещать вставку новых строк в таблицу (ввиду того, что не может быть других типов накладных и добавление их бессмысленно), выдавая при этом сообщение 'Вставка строк запрещена':

CREATE TRIGGER typepw_i_tr

ON typew FOR INSERT AS

PRINT 'Insertion of the rows prohibited

ROLLBACK TRAN

--Создав таблицу истории:

IF NOT EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'LogT')

CREATE TABLE LogT (User_name nvarchar(256), Date_time datetime, User_action nvarchar(256))

--Мы сможем отразить в ней попытку добавления записи:

INSERT INTO logT VALUES (SUSER_SNAME(), GETDATE(),'trying to insert record into table payments')

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

sp_columns typew

Колонки:

TYPEID 4 int

TYPENAME 12 varchar

Посмотрим сколько строк в таблице в текущий момент:

SELECT COUNT(*) FROM typew - результат - 6.

Теперь же попробуем вставить новую строку (с помощью команды INSERT) и снова посмотрим, сколько строк имеется в таблице:

INSERT INTO typew VALUES

(6, 'Дарение')

GO

SELECT COUNT(*) FROM typew

В результате мы получаем сообщение

Insertion of the rows prohibited

, результат запроса количества строк - 6 (строки добавлена не была) и в таблице LogT добавится записьв которой будет указано имя пользователя, время и действие:

trying to insert record into table payments

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

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

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

--Выберем базу в которой создадим процедуру

Use ASBase

--Напишем выполняемый процедурой запрос

CREATE PROCEDURE all_count AS

SELECT 'waybill' as Table_name, COUNT(wbillid) as Row_count FROM waybill

union

SELECT 'typew', COUNT(typeid) FROM typew

union

SELECT 'payments', COUNT(payid) FROM payments

union

SELECT 'firms', COUNT(firmid) FROM firms

union

SELECT 'tov', COUNT(tovid) FROM tov

union

SELECT 'waybill', COUNT(wbillid) FROM waybilld

Для получение информации о хранимых процедурах, находящихся в БД, используется следующий запрос SELECT * FROM sysobjects WHERE type = 'P'

Текст процедуры можно узнать, если осуществить процедуру sp_helptext [имя процедуры]

4. Разработка клиентского приложения

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

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

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

Для подключения к серверной базе данных средствами Borland C++ Builder Используются компоненты класса TADOConnection, для настроек подключения используется *.udl файл (т.к. он удобнее, чем простая строка соединения).

В *.udl файле на вкладке «Подключения» указываем сервер, тип авторизации (при необходимости логин и пароль пользователя), базу данных. Затем в закладке «Поставщик данных» определяем поступающие данные Microsoft OLE DB Provider for SQL Server. После завершения основных настроек жмем «Проверить подключение», чтоб проверить связь с БД.

При создании приложения определяем *.udl файл, который будет выполнять функции строки подключения компонента ADOConnection (в данной программе подключение к БД происходит на этапе выполнения, в проекте же подключение не настроено). Далее определяем компоненты с которыми будем работать - в данном проекте использовались ADOTable - для прямой работы с таблицами, ADOQuery - для организации связного курсора и создания выборок, ADOCommand - для выполнения команд (т.к. ADOQuery требует результат от сервера, а некоторые команды и хранимые процедуры результата не возвращают).

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

Ниже приведен листинг основных запросов используемых в приложении:

SELECT Payments.payid, Firms.fname, Payments.pdate,

--IIF(Payments.pout=1,"От Партнера","Партнеру ") as pout,

case Payments.pout when 1 then 'Партнеру '

when 0 then 'От Партнера'

end as pout,

Payments.psum

FROM

payments

LEFT OUTER JOIN firms

ON Payments.firmid = Firms.firmid

SELECT Waybill.wbillid, Waybill.wdate, Typew.typename, Firms.fname,

Waybill.wcoef*SUM(Movetov.mamount*Movetov.mprice) as ss, Waybill.wpayedsum,

Waybill.wretsum, Waybill.wretdate, Waybill.wcoef

FROM

Waybill,Typew,Firms,Movetov

where Waybill.wbillid = Movetov.wbillid

and Firms.firmid = Waybill.firmid

and Typew.typeid = Waybill.typeid

GROUP BY Waybill.wbillid, Waybill.wdate, Waybill.wretdate,

Waybill.wcoef, Typew.typename, Waybill.wpayedsum, Firms.fname,

Waybill.wretsum

ORDER BY Waybill.wbillid

SELECT Tov.Tnmed,Tov.Tnfct,Tov.Tprice,Movetov.mamount

FROM

Tov,Movetov

where Movetov.wbillid =:ID

and Tov.tovid = Movetov.tovid

ORDER BY Tov.Tnmed

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

Для создания HTML отчета используется с процедура:

"exec sp_makewebtask 'C:\\Report.html','" + Memo1->Lines->Text + "'"

Memo1->Lines->Text - содержит запрос набранный в редакторе запросов. Файл создается на диске С на сервере.

Заключение

тестирование сервер программное обеспечение

В ходе проекта был протестирован сервер с помощью хранимых процедур MS SQL SERVER 8.0, импортирована на сервер и протестирована база формата Dbase IV. Для вновь импортированной базы были написаны триггеры и хранимые процедуры, а также заведен пользователь и определены для него права. В среде Borland C++ Builder было Разработано программное обеспечение, обеспечивающее возможность работать с вышеописанной базой данных, при реализации данного клиентского приложения была выбрана двухуровневая модель «Клиент-Сервер.»

Библиографический список

1. Мамаев Е.В. Microsoft SQL Server 78.0:проектирование и реализация баз данных. -СПб.: БХВ - Санкт-Петербург, 2000 - 416 с.

2. Документация Microsoft® SQL Server™ 2000.

3. Архангельский А.Я. Разработка в среде программирования C++Builder, 2000 - 211с.

4.Администрирование Microsoft SQL Server 2000. Учебный курс MCSA / MCSE, MCDBA.-Москва: Русская Редакция 2003.

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