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

Министерство сельского хозяйства РФ

Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования

«Пермская государственная сельскохозяйственная академия имени академика Д.Н. Прянишникова»

Кафедра: ИТАП

КУРСОВОЙ ПРОЕКТ

«Проектирование и обслуживание баз данных в MS SQL Server»

на тему: «Проектирование базы данных «Сеть цветочных магазинов» с использованием программного продукта Microsoft SQL Server 2008R2»

Выполнил:

студент факультета Прикладной информатики

специальности __________

группы ________________

Проверил: ассистент кафедры ИТАП

Сукрушев Александр Владимирович

Пермь 2013

Содержание

Введение

Глава 1

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

2. Описание программных продуктов, используемых при проектировании БД

3. Создание структуры БД, нормализация БД

Глава 2

1. Проектирование БД

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

3. Создание автоматических задач по резервному копированию всей базы и отдельных таблиц с разными параметрами

Глава 3

1. Описание языка T-SQL

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

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

Заключение

Список используемой литературы

Введение

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

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

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

· добавление в таблицу новых записей;

· изменение записей;

· удаление записей;

· выборка записей из одной или нескольких таблиц (в соответствии с заданным условием);

· а, также, изменение структур таблиц.

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

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

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

· запросы на создание или изменение в базе данных новых или существующих объектов (при этом в запросе описывается тип и структура создаваемого или изменяемого объекта);

· запросы на получение данных;

· запросы на добавление новых данных (записей);

· запросы на удаление данных;

· обращения к СУБД.

Основным объектом хранения реляционной базы данных является таблица, поэтому все SQL-запросы - это операции над таблицами. В соответствии с этим, запросы делятся на:

· запросы, оперирующие самими таблицами (создание и изменение таблиц);

· запросы, оперирующие с отдельными записями (или строками таблиц) или наборами записей.

Каждая таблица описывается в виде перечисления своих полей (столбцов таблицы) с указанием

· типа хранимых в каждом поле значений;

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

· информации, необходимой для построения индексов.

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

· вставка новой строки;

· изменение значений полей строки или набора строк;

· удаление строки или набора строк.

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

· просмотреть полученный набор;

· изменить все записи набора;

· удалить все записи набора.

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

Целью курсового проекта является создание базы данных `Сеть цветочных магазинов'.

Задачи курсового проекта: создание таблиц, индексов, триггеров, файловых групп, пользователей, создание репликации, создание проекта, создание различных запросов, экспорт данных в MS Excel.

Глава 1

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

база данные microsoft server

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

Реализует запросы, осуществляет поиск сведений о магазине, поставщике, товаре.

2. Описание программных продуктов, используемых при проектировании БД

Microsoft SQL Server -- система управления реляционными базами данных (СУРБД), разработанная корпорацией Microsoft. Основной используемый язык запросов -- Transact-SQL, создан совместно Microsoft и Sybase. Transact-SQL является реализацией стандарта ANSI/ISO по структурированному языку запросов (SQL) с расширениями. Используется для работы с базами данных размером от персональных до крупных баз данных масштаба предприятия; конкурирует с другими СУБД в этом сегменте рынка.

SQL Server Management Studio -- утилита из Microsoft SQL Server 2005 и более поздних версий для конфигурирования, управления и администрирования всех компонентов Microsoft SQL Server. Утилита включает скриптовый редактор и графическую программу, которая работает с объектами и настройками сервера.

Главным инструментом SQL Server Management Studio является Object Explorer, который позволяет пользователю просматривать, извлекать объекты сервера, а также полностью ими управлять.

Также есть SQL Server Management Studio Express для Express версии сервера, которая является бесплатной. Однако в ней нет поддержки ряда компонентов (Analysis Services, Integration Services, Notification Services, Reporting Services) и SQL Server 2005 Mobile Edition.

3. Создание структуры БД, нормализация БД

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

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

Существует три основных типа нормальных форм: первая, вторая и третья.

Первая нормальная форма соответствует двумерной таблице.

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

Третья нормальная форма отличается от второй наличием идентификационных полей, по которым связываются таблицы. Это позволяет избегать повторений информации. (Рис.1)



Рисунок 1. Третья нормальная форма

Глава 2

1. Проектирование БД

В MS SQL Server создаем БД, нажав правой кнопкой в поле `Обозреватель объектов' на `Базы данных'. В выпадающем списке выбираем `Создать базу данных…'. Задаем ей имя `Сеть цветочных магазинов'.

Создаем в только что созданной БД таблицу, нажав правой кнопкой в `Обозревателе объектов' на поле `Таблицы'. В выпадающем списке выбираем `Создать таблицу…'. Появляется пустая таблица, которую необходимо заполнить данными (задаем имена и типы данных столбцам). Эту процедуру мы повторяем для каждой из 5 таблиц. Вот что получается:

Рисунок 2. `Magazini' Рисунок 3. `Postavshiki'

Рисунок 4. `Sotrudniki' Рисунок 5. `Tovar'

Рисунок 6. `Zakazi'

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

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

Теперь нам нужно заполнить таблицы нашими данными. Для этого в `Обозревателе объектов' мы выбираем нужную таблицу, и, кликнув на нее правой кнопкой мыши, из выпадающего списка выбираем `Изменить первые 200 строк' и заполняем таблицы:

Рисунок 7. Таблица `Magazini'

Рисунок 8. Таблица `Postavshiki'

Рисунок 9. Таблица `Sotrudniki'

Рисунок 10. Таблица `Tovar'

Рисунок 11. Таблица `Zakazi'

2.2 Создание индексов

Создаем индексы в каждой таблице.

CREATE INDEX INDEX_Magazin

ON Magazini (ID_Magazina)

Рисунок 12. Индексы

2.3 Создание триггера

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

1) DML запускаются с помощью инструкций INSERT, UPDATE или DELETE.

2) DDL с помощью инструкций CREATE, ALTER, DROP.

1. Создадим запрос, нажав на кнопку NewQuery .

2. Пишем сам запрос:

CREATE TRIGGER [DROP_ALTER]

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE

AS

PRINT `Вы не можете удалять или изменять таблицы'

ROLLBACK

3. Проверим созданный триггер. Создадим запрос: NewQuery , следующего содержания:

DROP TABLE Zakazi

4. Вы увидите сообщение об ошибке:

`Вы не можете удалять или изменять таблицы'

Рисунок 13. Результат работы триггера

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

Для базы данных создадим пользователей Пользователь_1 и Пользователь_2 в операционной системе и внутри SQL Server.

Рисунок 14. Пользователи внутри SQL Server

Рисунок 15. Пользователи в ОС

Пользователю Пользователь_1 в базе данных `Сеть цветочных магазинов' были назначены права: db_accessadmin. Для пользователя Пользователь_2 в базе данных `Сеть цветочных магазинов' были заданы права: db_datareader, db_datawriter.

В ОС, как видно из рисунка, пользователям тоже достался разный доступ.

2.5 Создание файловых групп

Создаем две файловые группы: `Filegroup_1 и Filegroup_2.

Рисунок 16. Создание файловых групп

Рисунок 17. Создание файлов

3. Создание автоматических задач по резервному копированию всей базы и отдельных таблиц с разными параметрами

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

Сначала мы задаем устройства резервного копирования `Резервное копирование базы' и `Частичное копирование'. Затем мы уже в эти устройства делаем полную и частичную резервные копии.

Рисунок 18. Полное резервное копирование

Рисунок 19. Выбор файлов и файловых групп



Рисунок 20. Резервное копирование файлов и файловых групп

Глава 3

1. Описание языка T-SQL

Transact-SQL (T-SQL) -- процедурное расширение языка SQL компаний Microsoft (для Microsoft SQL Server). SQL был расширен такими дополнительными возможностями как:

· управляющие операторы,

· локальные и глобальные переменные,

· различные дополнительные функции для обработки строк, дат, математики.

· поддержка аутентификации MicrosoftWindows

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

Так же язык Transact-SQL является реализацией стандарта ANSI/ISO по структурированному языку запросов (SQL) с расширениями. Используется для работы с базами данных размером от персональных до крупных баз данных масштаба предприятия; конкурирует с другими СУБД в этом сегменте рынка.

Язык T-SQL является реализацией SQL-92 (стандарт ISO для языка SQL) с множественными расширениями. T-SQL позволяет использовать дополнительный синтаксис для хранимых процедур и обеспечивает поддержку транзакций (взаимодействие базы данных с управляющим приложением).

2. Создание запросов к БД

Запрос на выбор сотрудников определенного года рождения:

SELECT Familiya,Imya,Otchestvo,Dolzhnost,Data_Rozhdenia,Adress,Telefon

FROM Sotrudniki

WHERE Data_Rozhdenia BETWEEN '1970' AND '1985'

Рисунок 16. Запрос 1

Запрос на выборку количества заказываемого товара

SELECT [Cena(rub)],ID_Magazina,ID_Tovara,ID_Zakaza,[Kolichestvo(sht)]

[Summa(rub)]

FROM Zakazi

WHERE [Kolichestvo(sht)] > '400'

And [Summa(rub)]>'40000'

Запрос на слияние двух таблиц по одному и тому же столбцу:

SELECT Imya_Magazina,[Kolichestvo(sht)],[Cena(rub)],[Summa(rub)]

FROM Zakazi, Magazini

WHERE Zakazi.ID_Magazina=Magazini.ID_Magazina



Рисунок 17. Запрос 2

Рисунок 18. Запрос 3

Запрос на слияние двух таблиц и выборку определенного номера поставщика. Уменьшение цены реализации.

SELECT pos.ID_Postavshika, pos.Imya_Postavshika, tov.Naimenovanie, tov.Cena_Realizacii

FROM Postavshiki as pos

JOIN Tovar as tov

ON pos.ID_Postavshika=tov.ID_Postavshika

WHERE pos.ID_Postavshika ='311'

ORDER BY tov.Cena_Realizacii desc

Рисунок 19. Запрос 4

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

ALTER VIEW [View_1]

AS

SELECT Zakazi.ID_Tovara, Postavshiki.Imya_Postavshika, Tovar.Naimenovanie

FROM Postavshiki

INNER JOIN Tovar

ON Postavshiki.ID_Postavshika=Tovar.ID_Postavshika

INNER JOIN Zakazi

ON Tovar.ID_Tovara=Zakazi.ID_Tovara

Рисунок 20. Запрос 5

С помощью функции выведены все заказы, значение цены которых равно 150:

CREATE FUNCTION Function_1

(@Cena nvarchar(100))

RETURNS TABLE AS

RETURN

(SELECT*FROM Zakazi

WHERE [Cena(rub)] = @Cena) GO

Рисунок 21. Запрос 6

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

CREATE PROCEDURE PROCEDURE_1

AS

SELECT Familiya,Imya,Otchestvo FROM Sotrudniki

ORDER BY Familiya

Go

EXEC PROCEDURE_1

Go

Рисунок 22. Запрос 7

Обработка ошибок ввода некорректных данных программным способом

Рисунок 23. Проверка оповещения об ошибке

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

Экспортировать данные можно с помощью Visual Studio и непосредственно через MS Excel. Мы экспортируем данные непосредственно через MS Excel. В MS Excel выбираем получить внешние данные из других источников - с сервера SQL Server, выбираем нужную таблицу.

Рисунок 24. Выбор сервера

Рисунок 25. Выбор таблицы в нашей базе данных

Рисунок 26. Результат экспорта в MSExcel

Заключение

В результате работы была создана структура базы данных и в дальнейшем спроектирована база данных `Сеть цветочных магазинов'. Закреплены навыки работы с языком SQL, которые были применены в написании запросов, процедур, представлений, функций, триггеров. Была выведена отчетная информация с помощью экспорта данных в программный продукт MS Excel. Поставленные цели и задачи в курсовом проекте были успешно выполнены.

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

1. Электронный справочник SQLServer 2008.-Режим доступа: http://msdn.microsoft.com.

2. Электронная энциклопедия.- Режим доступа: http://wikipedia.com.

3. Роберт Э. Уолтерс, Майкл Коулс, SQL Server 2008: ускоренный курс для профессионалов Accelerated SQL Server 2008. -- М.: «Вильямс», 2008. -- С. 768.

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

...