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

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

Содержание

• Введение
• 1. Подготовительная работа
• 1.1 Программные продукты, используемые при проектировании базы данных
• 1.2 Создание структуры базы данных
• 2. Проектирование базы данных
• 2.1 Создание базы данных
• 2.2 Создание таблиц
• 2.3 Заполнение таблиц
• 2.4 Создание триггеров
• 2.5 Создание пользователей
• 2.5.1 Создание пользователей в операционной системе
• 2.5.2 Создание пользователей внутри SQL Server
• 2.6 Резервное копирование базы данных
• 2.7 Создание репликации
• 3. Создание запросов
• 3.1 Описание языка Transact-SQL
• 3.2 Создание запросов к базе данных
• 3.3 Создание представлений
• 3.4 Создание функции
• 3.5 Создание процедур
• 3.6 Экспорт данных в MS Excel
• Заключение
• Список использованных источников

Введение

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

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

В соответствии с заданием курсового проектирования следует создать базу данных (БД) «Библиотека» с использование программного проекта Microsoft SQL Server.

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

Проектирование базы данных «Библиотека» ведется для упрощения работы библиотекаря со всем ассортиментом предложенных книг и учебных пособий, а также для учёта взятых и оставшихся в наличии книг.

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

• база данных «Библиотека» представляет собой программу для управления базой данных;

• программа полностью автоматизирует труд оператора ПЭВМ: автоматически заносит вводимые пользователем данные в таблицы базы данных, изменяет структуру таблиц (добавление/удаление записей);

• при использовании программы значительно снижается трудоёмкость ведения базы данных взятых и оставшихся книг.

1. Подготовительная работа

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

Разработаем базу данных с помощью программного продукта Microsoft SQL Server 2008, а структуру базы данных создадим в программном продукте AllFusion ERwin Data Modeler.

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

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

Система SQL Server 2008 позволяет обращаться к данным из любого приложения, разработанного с применением технологий Microsoft .NET и Visual Studio.

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

AllFusion ERwin Data Modeler (ранее ERwin) - CASE-средство для проектирования и документирования баз данных, которое позволяет создавать, документировать и сопровождать базы данных, хранилища и витрины данных. Модели данных помогают визуализировать структуру данных, обеспечивая эффективный процесс организации, управления и администрирования деятельности предприятия.

AllFusion ERwin Data Modeler (ERwin) позволяет наглядно отображать сложные структуры данных. AllFusion ERwin Data Modeler имеет удобный для пользователя графический интерфейс, который упрощает разработку базы данных и автоматизирует множество трудоемких задач, уменьшая сроки создания высококачественных и высокопроизводительных транзакционных баз данных и хранилищ данных.

1.2 Создание структуры базы данных

Разработаем структуру базы данных (БД) Библиотека (Library), используя CASE-средство AllFusion ERwin Data Modeler (ERwin).

ERwin позволяет создавать логическую, физическую модели и модель, совмещающую логический и физический уровни.

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

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

Физический уровень зависит от конкретной СУБД. В физической модели содержится информация обо всех объектах БД. Физическая модель зависит от конкретной реализации СУБД.

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

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

Существуют следующие виды нормальных форм:

• первая нормальная форма (1NF). Сущность Е находится в первой нормальной форме, если и только если все атрибуты содержат только атомарные значения. Среди атрибутов не должно встречаться повторяющихся групп, т. е. нескольких значений для каждого экземпляра;

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

• третья нормальная форма (3 NF). Сущность Е находится в третьей нормальной форме, если она находится во второй нормальной форме и неключевые атрибуты сущности Е зависят от других атрибутов Е.

После третьей нормальной формы существуют нормальная форма Бойсса - Кодда, четвертая и пятая нормальные формы. На практике ограничиваются приведением к третьей нормальной форме.

Создадим логическую (см.рисунок 1) и физическую (см. рисунок 2) модели согласно третьей нормальной форме.



Рисунок 1

Рисунок 2

2. Проектирование базы данных

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

Создадим базу данных при помощи графического интерфейса SQL Server Management Studio. Щелкнем правой кнопкой мыши по контейнеру Database в Object Explorer и в контекстном меню выберем New Database (Новая база). Откроется диалоговое окно New Database, в левой части этого диалогового окна видим три вкладки: General, Filegroups, Options.

На вкладке General зададим имя базы данных Library.

На вкладке Filegroups, определим, к какой файловой группе будет относиться файл базы данных. Файловая группа (Filegroup) - это способ организации файлов БД. По умолчанию для любой базы данных создается файловая группа PRIMARY, и все создаваемые файлы будут относиться именно к ней. В создаваемой БД все таблицы можно условно поделим на две группы:

• пользовательские таблицы, которые постоянно изменяются пользователями;

• таблицы справочника, которые меняются очень редко.

Таким образом, при создании БД создадим дополнительную файловую группу USERS (вкладка Filegroups). Создадим новый файл данных USERS, и определим, что он будет относиться к этой файловой группе. Таблицы справочника оставим в файловой группе PRIMARY.

2.2 Создание таблиц

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

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

· PRIMARY KEY (первичный ключ) - уникально идентифицирует каждую строку таблицы. Значение в этом столбце либо в упорядоченном наборе столбцов не могут повторяться в более чем одной строке. Столбец PRIMARY KEY определен только с атрибутом NOT NULL. Таблица может иметь только один PRIMARY KEY, который может быть определен на одном или более столбцов;

· ограничения FOREIGN KEY задают и обеспечивают связи между таблицами. Справочные ограничения гарантируют, что значения в наборе столбцов, которые определены в FOREIGN KEY принимают те же самые значения, которые присутствуют в столбце PRIMARY KEY в справочной таблице.

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

Далее рассмотрим пример создания таблицы Books:

CREATE TABLE Books --имя таблицы

(Book_ID nchar(6) PRIMARY KEY, /*символьный тип данных длиной в 6 символов, первичный ключ*/

Theme nchar(3) not null /*символьный тип данных длиной в 6 символов, не может принимать значение null*/

FOREIGN KEY REFERENCES Theme(Theme_ID)

ON DELETE NO ACTION, --установление вторичного ключа

Autor text, --текстовый тип данных

Title text, --текстовый тип данных

Mockery int not null --целочисленный тип данных, не может принимать значение null

FOREIGN KEY REFERENCES Mockery(Mockery_ID)

ON DELETE NO ACTION, --установление вторичного ключа

Year_Edition date, --тип данных дата

Page int, --числовой тип данных

Cost money, --денежный тип данных

Edition int not null --целочисленный тип данных, не может принимать значение null

FOREIGN KEY REFERENCES Type_Edition(Edition_ID)

ON DELETE NO ACTION, --установление вторичного ключа

Quantity int, --целочисленный тип данных

Storage int not null --целочисленный тип данных, не может принимать значение null

FOREIGN KEY REFERENCES Storage(Storage_ID)

ON DELETE NO ACTION --установление вторичного ключа

)

ON USERS --принадлежность таблицы к файловой группе USERS

2.3 Заполнение таблиц

Заполнение таблиц осуществим с помощью оператора INSERT INTO. Таблицу Books в базе данных Library заполним следующим образом:

INSERT INTO Books

VALUES

('3297P8','A20','ProidakovI.V.','Englishvocabulary','145','2004','864','236.60','3','30','1'),

('6332H4','H91','Zyev M.N','History of Russia','308','2007','634','160.90','2','75','1'),

('1237F4','F12','AlekseevP.V.','The textbook on philosophy','546','2010','328','129.50','3','10','4'),

('4519L9','L45','Eremin N.P.','Latin language','546','2003','498','210.60','2','5','4'),

('1359S1','S92','KravchenkoA.I.','Sociology','800','2005','136','110.00','3','70','1'),

('0047S3','I41','SmirnovaG.N.','Designing of information systems','211','2001','512','156.00','2','50','1'),

('8830P4','P10','Shebetko A.I.','Test','422','1995','198','39.20','4','10','2'),

('3385A0','A20','LvovV.M.','Computer&InternetDictionary','145','1995','574','320.50','3','12','1'),

('6339I5','H91','Orlov A.S.','History of Russia','789','2002','520','174.00','2','30','1'),

('4512I0','I41','Leontev V.I.','Personal Conputer','789','2008','800','549','5','3','2'),

('8854P0','P10','Leontev A.A.','Psychology of dialogue','800','1997','366','30.00','3','15','2'),

('1212G8','F12','Grek O.V.','Philosophy','985','2000','230','149.50','1','10','3'),

('4120G1','I41','Grekyla A.N.','Informatics','800','2010','20','56.00','6','5','2'),

('1357S0','L45','Somov O.A.','Latin language','789','2007','156','200.00','3','2','4'),

('1134S6','S92','Soley N.A.','Sociology','926','1998','348','410.00','4','15','1');

2.4 Создание триггеров

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

Для базы данных Library создадим три триггера:

1) триггер типа UPDATE печатает определенный текст при каждой модификации таблицы Books:

--создание триггера TR_Print_Update

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'Print_Update' AND type = 'TR')

DROP TRIGGER Print_Update

GO

CREATE TRIGGER TR_Print_Update

ON Books

FOR UPDATE

AS

PRINT 'The Books table was updated'

GO

Чтобы проверить работу триггера, выполним модификацию строки:

UPDATE Books

SET Mockery=145

WHERE Year_Edition='2005'

Будет возвращено сообщение The Books table was updated (1 row(s) affected), так как в результате выполнения оператора UPDATE был запущен триггер. В данном триггере мы задали вывод сообщения, чтобы можно было увидеть работу триггера;

2) триггер типа DELETE будет сохранять все строки, удаленные из таблицы Books_Delivery, в таблицу Books_Delivery_Backup для последующего анализа данных. Для реализации триггера используем программу приведенную ниже:

-- создание таблицы Books_Delivery_Backup

CREATE TABLE Books_Delivery_Backup

( Subscriber int

FOREIGN KEY REFERENCES Subscriber(Subscriber_ID)ON DELETE NO ACTION,

Book_ID nchar(6) not null

FOREIGN KEY REFERENCES Books(Book_ID) ON DELETE NO ACTION,

Date_delivery date not null,

Date_return date not null,

Librarian int not null

FOREIGN KEY REFERENCES Librarian(Librarian_ID) ON DELETE NO ACTION

)

ON USERS

--создание триггера

IF EXISTS (SELECT name

FROM sysobjects

WHERE name = 'TR_Books_Delivery_Backup' AND type = 'TR')

DROP TRIGGER TR_Books_Delivery_Backup

GO

CREATE TRIGGER TR_Books_Delivery_Backup

ON Books_Delivery

FOR DELETE

AS

INSERT INTO Books_Delivery_Backup

SELECT * FROM deleted

GO

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

3) триггер DELETE для таблицы Subscriber будет выводить информацию о попытках удаления и количестве удаляемых строк:

CREATE TRIGGER TR_Subscriber_Del

ON Subscriber

FOR DELETE AS

PRINT 'Popitka udalenia '+STR(@@ROWCOUNT)+' strok in table Subscriber'

PRINT 'User '+CURRENT_USER

IF CURRENT_USER<>'dbo'

BEGIN

PRINT 'Udalenie zapresheno'

ROLLBACK TRANSACTION

END

ELSE

PRINT 'Udalene razresheno'

Данный триггер будет выводить информацию о количестве строк, которое пытается удалить пользователь, и имя пользователя, выполнившего команду DELETE. Если пользователь не 'dbo', то удаление запрещается и выдается соответствующее предупреждение.

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

--добавление строки

INSERT INTO Subscriber

VALUES ('13000','Aglullina','Liliya','Rafikonvna','02-02-1990','Gremychinsk, Vostochnaya 6-3','570402','Perm Agricultural Academy');

--удаление строки

DELETE FROM subscriber WHERE Subscriber_ID='13000'

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

Popitka udalenia 1 strok in table Subscriber

User dbo

Udalene razresheno

(1 row(s) affected)

2.5 Создание пользователей

2.5.1 Создание пользователей в операционной системе

Для того чтобы создать пользователей в операционной системе выполним следующую цепочку действий: зайдем в Start - Control Panel - User Accounts, далее выберем Manage another account. В открывшемся окне Manage Accounts выберем Create a new account. Далее в открывшемся диалоговом окне Create New Account введем имя пользователя Ivanova, выберем Standard User и нажмем Create Account. Затем вернемся в диалоговое окно Manage Accounts, выберем только что созданного пользователя, далее откроется окно Change an Account, выберем пункт Create a password и создадим для пользователя Ivanova пароль: 1, подтвердим его и нажмем Create password. Таким образом, пользователь Ivanova с заданным паролем будет успешно создан.

Аналогичным образом создадим пользователей Kalina, Somova, Nikiforova, Solnceva, Kirina и зададим им пароль «1» для входа в систему.

2.5.2 Создание пользователей внутри SQL Server

Процесс создания пользователей внутри SQL Server можно разделить на 3 этапа:

1) создание логина - учетной записи для подключения к SQL Server;

2) затем создание пользователя базы данных, которому соответствует этот логин;

3) предоставление пользователю необходимых разрешений.

Первое, что нужно сделать при предоставлении разрешений пользователю на информацию в SQL Server 2008 - это предоставить ему логин, т. е. учетную запись, которая будет использоваться для подключения к серверу SQL Server. Прежде, чем создать логин, определим, какой тип будем использовать.

В SQL Server 2008 существует два типа логина:

• логин Windows. При использовании логинов Windows в системные таблицы базы данных master записывается информация об идентификаторе учетной записи Windows (но не пароль). Аутентификация производится обычными средствами Windows при входе пользователя на свой компьютер.

• логин SQL Server. При использовании логина SQL Server пароль для этого логина хранится вместе с идентификатором логина в базе данных master. При подключении пользователя к серверу ему придется указать имя логина и пароль.

Для работы с БД Librarian внутри операционной системы создано шесть пользователей. Для пользователей Ivanova, Kalina, Somova создадим логины типа SQL Server, для пользователей Nikiforova, Solnceva, Kirina создадим логины Windows. Определим, что все пользователи имеют одинаковые права.

Создадим логин Windows для пользователя Nikiforova. Для этого откроем контейнер Security - Logins в Object Explorer в SQL Server Management Studio, выберем New Login. Откроется диалоговое окно Login New, вкладка General. Запишем имя логина NY-SQL-01\Nikiforova, выберем Windows Authentication, параметры Default database(База данных по умолчанию) и Default language (Язык по умолчанию) оставим без изменения (см. рисунок 3).

Рисунок 3

Далее перейдем на вкладку Status (Состояние) и настроим для этого логина дополнительные параметры:

• Permissions to connect to database engine (Разрешение на подключение к ядру баз данных) - по умолчанию для всех логинов устанавливается значение Grant, т. е. подключаться к SQL Server разрешено;

• Login enabled/disabled (Логин включен/отключен) -- по умолчанию все логины включены (см. рисунок 4).

Рисунок 4

На вкладке Server Roles (Серверные роли) можно предоставить права на уровне всего сервера, а не отдельной базы данных, по умолчанию выбрано public.Права этой роли автоматически получают все, кто подключился к SQL Server, и лишить пользователя членства в этой роли нельзя.

На вкладке User Mapping укажем, с какой базой данных может работать этот логин (см. рисунок 5).

Рисунок 5

В контейнере User mapped to this login, столбца Default schema (Схему по умолчанию) выберем db_owner, тем самым автоматически предоставив этой роли полные права на базу данных.

В контейнере Database roles membership for: Library (Роли базы данных) выберем: db_datareader (для чтения из таблиц и представлений (views) базы данных), db_datawriter (для добавления (insert), редактирования (update) и удаления (delete) записей таблиц и представлений базы данных), public выбрано автоматически. Далее нажмем OK, автоматически будет создан логин и пользователь Nikiforova для базы данных Library. Аналогичным образом создадим логины Windows для пользователей Solnceva и Kirina, и назначим им те же права.

Теперь создадим логин SQL Server для пользователя Ivanova. Откроем контейнер Security - Logins - New Login. Откроется диалоговое окно Login New, вкладка General. Запишем имя логина Ivanova, выберем SQL Server Authentication, Password: 1, Confirm password: 1, параметры Default database(База данных по умолчанию) и Default language (Язык по умолчанию) оставим без изменения (см. рисунок 6).

Рисунок 6

Установим переключатель Enforce password policy и Enforce password expiration

Enforce password policy (Использовать парольную политики) - позволяет определить требования к паролям

Enforce password expiration (Включить устаревание пароля) - определяет, будут ли на логин SQL Server распространяться те же требования по смене пароля через определенный промежуток времени, что и для учетных записей Windows.

Далее перейдем на вкладку Status (Состояние) и убедимся в правильности настроек Permissions to connect to database engine: Grant; Login enabled.

На вкладке User Mapping укажем следующие параметры (см. рисунок 7).



Рисунок 7

Далее нажмем OK, автоматически будет создан логин и пользователь логина Ivanova для базы данных Library.

Аналогичным образом создадим логины SQL Server для пользователей Kalina и Somova, и назначим им те же права.

2.6 Резервное копирование базы данных

Резервное копирование - один из самых надежных способов сохранить и предохранить свои данные от потери или порчи. Различают три вида резервного копирования:

• полное резервное копирование (full backup);

• разностное (differential backup);

• резервное копирование журналов транзакций (transaction log backup).

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

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

Создадим полное резервное копирование базы данных Library, используя графический интерфейс Management Studio. Откроем окно резервного копирования из контекстного меню Tasks - Backup для базы данных Library. Определим следующие параметры резервного копирования:

• Database: Library - имя базы данных, резервное копирование которой производим.

• Recovery model (Режим восстановления): Full - информация о текущем режиме восстановления базы данных.

• Backup type (Тип резервного копирования): Full - тип резервного копирования.

• Backup component (Компонент для резервного копирования):Database - этот компонент позволяет выбрать резервное копирование всей базы данных или отдельных файловых групп.

• Backup set name (Имя резервной копии): Library-Full Database Backup - имя резервной копии

• Description (Описание) -- описание резервной копии.

• Backup set will expire (Резервная копия устареет) - позволяет указать срок (дату), после которой резервная копия будет считаться устаревшей.

• Destination (Назначение) Back up to: Disk, далее выберем Add и укажем место назначения резервной копии в виде файла на диске: C:\Backup\LibraryFull.bkp.

Далее перейдем на вкладку Options и выберем следующие параметры:

• Overwrite media (Перезаписать носитель) -- параметры, позволяющие определить режим перезаписи носителя, выберем Append to the existing media set (Добавить к существующему набору носителя).

• Reliability: Verify backup then finished (Проверить резервную копию после завершения) - проверка целостности резервной копии после завершения резервного копирования.

После этого настроим расписание резервного копирования. Для этого выберем Script - Script to job, откроется окно New Job. В диалоговом окне New Job зададим имя Full Back Up Database - Library, затем перейдем на вкладку Schedules и создадим расписание (см. рисунок 8).

Рисунок 8

Далее нажимаем ОК, и полное резервное копирование будет обновляться один раз в неделю в воскресенье в полдень.

Аналогичным образом настроим:

• разностное резервное копирование с именем Diff Back Up Database - Library, которое будет обновляться один раз в неделю, в воскресенье в 12.10 PM;

• резервное копирования журнала транзакций с именем Log Back Up Database - Library, которое будет обновляться ежедневно в течение каждых 6 часов, начиная с 12:00: PM, заканчивая в 11:59:59 AM;

• резервное копирования файловой группы Primary с именем Back Up FilesgoupPrimary - Library, файловой группы USERS - Back UP FilesgoupUSERS - Library с расписанием ежедневного обновления в 8:00:00 PM.

2.7 Создание репликации

Создадим репликацию транзакций для таблиц Books и Subscriber в базе данных Library. Сделаем так, что все изменения, которые вносятся в любую из этих таблиц, должны не позднее, чем через 10 минут отобразиться в одноименных таблицах новой базы данных Lib_Repl на сервере NY-SQL-01.

Настройку репликации начнем с настройки распространителя. Настройку распространителя осуществим при помощи мастера Configure Distribution Wizard. Откроем контейнер Replication (Репликация) в Object Explorer - Configure Distribution (Настроить распределение).

На первом экране мастера Distributor (Дистрибьютор) определим, что наш сервер будет выполнять роль распространителя для самого себя, поэтому выберем первый вариант.

На следующем экране мастера Snapshot Folder (Каталог моментальных снимков) определим каталог, в который будут помещаться моментальные снимки данных. Оставить каталог, предлагаемый по умолчанию.

На следующем экране Distribution Database (База данных распределения) определим имя и местонахождение файлов для создаваемой базы данных распределения.

Далее на экране Publishers (Издатели) выберем сервер NY-SQL-01, который сможет использовать этот распространитель.

На последнем экране мастера Wizard Actions (Действия мастера) дадим команду на настройку распространителя и создание базы данных распределения

После этого, нажмем кнопку Finish (Завершить) и настройка распространителя будет закончена.

Следующее действие, которое выполним после настройки распространителя - это создадим публикации. Публикацию создадим при помощи мастера, откроем контейнер Replication - Local Publications (Репликация - Локальные публикации) - New Publication (Новая публикация) в SQL Server Management Studio.

На экране Publication Databases (Базы данных публикации) выберем базу данных Library с информацией для публикации.

На следующем экране Publication Type (Тип публикации) выберем тип публикации Transactional Publication (Транзакционная публикация).

На экране Articles (Статьи) создадим статью для публикации. Для этого в списке объектов раскроем узел Tables (Таблицы) и установим флажки напротив таблиц Books и Subscriber.

На следующем экране Snapshot Agent настроим параметры запуска агента снятия моментальных снимков. По умолчанию переключатель стоит в положении Create a snapshot immediately (Создать моментальный снимок немедленно). Это значит, что моментальный снимок данных публикации будет сделан сразу после публикации и будет сохраняться в папке, чтобы можно было бы настраивать подписки.

На следующем экране Agent Security (Безопасность агента) выберем учетные записи, от имени которых будут работать Snapshot Agent и Log Reader Agent. Настроим работу агентов от имени учетной записи SQL Server Agent. Для этого на экране настройки безопасности агентов, нажмем на кнопку Security Settings (Настройки безопасности), выберем флажок Run under the SQL Service Agent Account (Запускать под учетной записью SQL Service Agent).

После этого нажмем кнопку Finish, определим имя для публикации (назовите ее Lib_publication) и выполним ее создание.

Далее создадим подписку. Подписку настроим при помощи мастера подписки Local Subscriptions (Локальные подписчики) для сервера - New Subscriptions (Новая подписка) контекстного меню контейнера

На экране мастера Distribution Agent Location (Местонахождение Distribution Agent) выберем тип создаваемой подписки - принудительная.

На следующем экране Subscribers (Подписчики) выберем сервер NY-SQL-01, который будет подписчиком для нашей публикации.

Далее выберем базу данных, на которую будут передаваться реплицируемые данные. Выберем значение New Database (Новая база данных) и создать новую базу данных - Lib_Repl.

На следующем экране Distribution Agent Security (Безопасность Distribution Agent) настроим учетную запись для Distribution Agent и параметры его подключения к распространителю и подписчику. В данном случае используем для него ту же учетную запись, что и для SQL Server Agent.

Далее настроим расписание репликации. Установим, что репликация будет производиться с интервалом в 10 минут. Поэтому в списке Agent Schedule (Расписание агента) на экране Synchronization Schedule (Расписание синхронизации) выберем параметр Define Schedule (Определить расписание) и настройте параметры для создаваемого расписания: Frequency Occurs: Daily, Recurs every: 10 minutes;

На следующем экране мастера определим время инициализации базы данных подписчика. Выберем вариант - Immediately (Немедленно). На этом же экране при необходимости можно снять флажок Initialize (Инициализация).

Далее нажмем кнопку Finish и произведем создание подписки. В базе данных подписки будет автоматически создан требуемый набор таблиц и будет произведена загрузка данных в эти таблицы. Если же мы внесем какие-либо изменения в опубликованные таблицы источника, то в течение 10 минут эти изменения будут отреплицированы и отображены в базе данных подписчика.

база данные библиотека

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

3.1 Описание языка Transact-SQL

Язык SQL является стандартом для всех СУБД. SQL Server 2008 использует модифицированную версию языка - Transact-SQL (T-SQL), дополненную специфическими функциями администрирования и управления проектами.

Transact-SQL - это усовершенствование стандартного языка программирования SQL. Язык T-SQL применяется для взаимодействия между приложениями и SQL Server. В T-SQL имеются все возможности языков DDL и DML стандартного SQL, а кроме этого имеются также расширенные функции, системные хранимые процедуры и конструкции для программирования (такие, как IF and WHILE), обеспечивающие большую гибкость программирования. По мере выхода новых версий SQL Server возможности T-SQL растут.

Язык DDL (data definition language, язык определения данных) применяется для определения объектов баз данных (таких как базы данных, таблиц и представления) и для управления этими объектами. Операторы языка DDL включают в себя команды CREATE, ALTER и DROP для каждого из объектов, с которым производится работа.

Язык DML (data manipulation language, язык манипулирования данными) применяется для манипулирования данными, содержащимися в объектах базы данных, для чего применяются такие операторы, как SELECT, INSERT, UPDATE и DELETE. При помощи этих операторов можно соответственно выбирать строки с данными, вставлять новые строки, изменять имеющиеся строки и удалять ненужные строки.

3.2 Создание запросов к базе данных

Запрос - это средство выбора необходимой информации из базы данных.

Создадим четыре запроса к базе данных Library:

1) найдем все книги, выданные в феврале 2010 года используя таблицу Books_Delivery. Код программы будет следующим:

Select *

from Books_Delivery

Where Date_delivery between '2010-02-01' and '2010-02-28

2) найдем книги, которые брал абонент 13581, отсортируем по дате выдаче. Наберем следующий код программы:

SELECT p.Subscriber,v.Autor,

v.Title,p.Date_delivery

FROM Books_Delivery as p

JOIN Books as v

ON (p.Book_ID=v.Book_ID)

WHERE p.Subscriber=13581

ORDER BY p.Date_delivery

3) найдем всю зарегистрированную в библиотеке литературу, выпущенную не позднее 2000 года. Код программы будет следующим:

SELECT Book_ID,Year_Edition

FROM Books

WHERE Year_Edition>='2000'

ORDER BY Year_Edition

3.3 Создание представлений

Представление - это виртуальная таблица, определяемая запросом, содержащим оператор SELECT. Эта виртуальная таблица состоит из данных одной или нескольких реальных таблиц, а для пользователей представление выглядит, как реальная таблица. Пользователи могут обращаться к этим виртуальным таблицам в операторах Transaсt-SQL таким же образом, как и к таблицам. К представлению можно применять операции SELECT, INSERT, UPDATE и DELETE.

Создание представлений осуществляется с помощью оператора CREATE VIEW языка T-SQL. Создадим три представления для разных таблиц базы данных Library:

1) представление по таблице Subscriber, в котором имеется доступ только к колонкам Subscriber_ID, Surname, First_Name, Name, используя оператор T-SQL:

2)

CREATE VIEW Subscriber_vw

AS

SELECT Subscriber_ID,

Surname,

First_Name,

Name

FROM Subscriber

3) представление для таблицы Books, при котором выбраны все колонки данной таблицы, но есть условие на тип издания - учебное пособие (Books.Edition=3) и издательство (Books.Mockery=145). Напишем следующий код:

CREATE VIEW View_1

AS

SELECT *

FROM Books

WHERE Books.Edition=3 and Books.Mockery=145

4) представление для таблицы Books, при котором выбраны колонки Book_ID, Theme, при условии тема - English language (Books.Theme='A20')

CREATE VIEW View_2

AS

SELECT Books.Book_ID,

Books.Theme

FROM Books

WHERE Books.Theme='A20'

3.4 Создание функции

Выполним две функции:

1) найдем сочетание символов "st" в строковом выражении. Для этого используем функцию - CHARINDEX, которая возвращает начальную позицию подстроки в строке. Функция CHARINDEX имеет следующий синтаксис:

CHARINDEX (искомое_выражение, строковое_выражение[, стартовая_позиция])

Выполним запрос:

SELECT Title

FROM Books

WHERE CHARINDEX('st', Title) > 0

Данный запрос будет выводить те названия книг, в которых имеется сочетание символов "st". Если искомая строка не будет обнаружена, то функция CHARINDEX возвращает 0. Результат выполнения запроса будет содержать следующие строки (см. рисунок 9).

Рисунок 9

2) создадим функцию DATEADD, возвращающую дату и время.

Синтаксис DATEADD (datepart, number, date)

Данная функция возвращает значение типа datetime, которое получается добавлением к дате date количества интервалов типа datepart, равного number. Можно к заданной дате добавить любое число лет, дней, часов, минут и т.д.

Пусть сегодня 18/12/2010, и мы хотим узнать, какой день будет через неделю. Напишем следующий код:

SELECT DATEADD (day, 7, current_timestamp)AS Data

В результате получим (см. рисунок 10).

Рисунок 10

SELECT DATEADD (ww, 1, current_timestamp)AS Data

Получим следующий результат (см. рисунок 11).

Рисунок 11

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

3.5 Создание процедур

Процедура - это набор операторов T-SQL, который компилируется системой SQL Server в единый "план исполнения". Этот план сохраняется в кэш-области памяти для процедур при первом выполнении хранимой процедуры, что позволяет использовать этот план повторно; системе SQL Server не требуется снова компилировать эту процедуру при каждом ее запуске. Все операторы процедуры обрабатываются при вызове процедуры.

Создадим хранимую процедуру с использованием оператора T-SQL CREATE PROCEDURE. Оператор CREATE PROCEDURE имеет следующий синтаксис:

CREATE PROC[EDURE] имя_процедуры

[ {@имя_параметра тип_данных} ] [= по_умолчанию][OUTPUT]

[,...,n]

AS оператор(ы)_t-sql

Создадим процедуру, которая будет выбирать (и возвращать) три колонки данных для таблицы Library, в которой значение колонки Librarian=1. Ниже описан код программы, который наберем в окне запроса:

IF EXISTS (SELECT name

FROM sysobjects

WHERE name = 'Library_Procedure' AND type = 'P')

DROP PROCEDURE Library_Procedure

CREATE PROCEDURE Library_Procedure

AS

SELECT Subscriber, Book_ID, Librarian

FROM Books_Delivery

WHERE Librarian=1

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

Library_Procedure

GO

3.6 Экспорт данных в MS Excel

Экспорт данных из MS SQL Server в MS Excel выполним с помощью программы SQL Server Import and Export Data. Откроем эту программу из меню Start - All programs - Microsoft SQL Server - Import and Export Data. Окно SQL Server Import and Export Data Wizard откроется автоматически (см. рисунок 12).

Рисунок 12

Создадим отчет, выгружающий информацию с SQL Server в файл Excel.

На первом экране мастера Choose a Data Source (Выберите источник данных) необходимо выбрать то место, откуда будут извлекаться. Выполним следующие настройки:

• Data source: SQL Server Native Client 10.0

• Server name: local

• Authentication: Use Windows Authentication

• Database: Library

На следующем экране мастера Choose a destination (Выберите назначение) выберем источник данных, в который данные будут перемещены. В данном случае выберем Microsoft Excel и введем путь к файлу Excel (C:\Users\Administrator\Documents\Library.xls).

На следующем экране Specify Table Copy or Query (Укажите копирование таблицы или запрос) переставим переключатель в положение Write a query to specify the data to transfer (Написать запрос для выбора передаваемых данных), так как будем выгружать данные, возвращаемые запросом.

На следующем экране Provide a Source Query (Обеспечить запрос для источника) введем следующий текст запроса:

SELECT p.Subscriber, v.Autor v.Title, p.Date_delivery

FROM Books_Delivery as p

JOIN Books as v

ON (p.Book_ID=v.Book_ID)

WHERE p.Subscriber=13581

ORDER BY p.Date_delivery

В следующем диалоговом окне Select Source Tables and Views (Выберите таблицы и представления источника) не будем применять каких-либо настроек, поэтому нажимаем Next.

На следующем экране мастера Save and Execute Package (Сохранить и запустить пакет) выберем действие Run immediately (Выполнить немедленно) - то есть запустить пакет на выполнение сразу после завершения работы мастера.

Последнее, что сделаем, - запустим пакет на выполнение при помощи кнопки Finish (Завершить) и убедимся, что его сохранение и выгрузка данных происходят нормально.

Результат выполнения данного отчета будет выглядеть следующим образом (см. рисунок 13).

Рисунок 13

Аналогичным образом выполним отчет по таблицам Books и Books_Delivery базы данных Library в файл Library_1.

Заключение

В данном курсовом проекте была разработана база данных «Библиотека». Программа полностью автоматизирует труд библиотекаря и организует надежное хранение и обработку данных.

При разработке данного курсового проекта были приобретены навыки работы в MS SQL Server 2008 и AllFusion ERwin Data Modeler.

Список использованных источников

1. Вебер Э. Microsoft SQL Server 2008. Разработка баз данных. Учебный курс Microsoft (+ CD-ROM). - М.: Русская Редакция, 2010. - 496 с.

2. Виейр Р. Программирование баз данных Microsoft SQL Server 2005. Базовый курс: Пер. с англ. - М.: ООО «И.Д. Вильямс», 2007. - 832 с.: ил.

3. Кузин А.В. Базы данных: учеб. пособие для студ. высш. учеб. заведений/ Кузин А.В., Левонисова С.В. - 2-е изд., стер. - М.: Издательский центр «Академия», 2008. - 320 с.

4. Петкович Д. Microsoft SQL Server 2008. Руководство для начинающих. - СПб.: БХВ-Петербург, 2009. - 752 с.

5. Федоров А.Г. Microsoft SQL Server 2008. Обзор ключевых новинок. - М.: Русская Редакция, 2008. - 128 с.

6. http://msdn.microsoft.com/ru-ru/

7. http://www.INTUIT.ru

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