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

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

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

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

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

Кафедра ИТАП

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

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

на тему: «Разработка баз данных экономической информационной системы по модели «Кондитерская фабрика» с использованием программного продукта Microsoft SQL Server 2008 R2»

Выполнил:студент 2 курса группы ПИб-21а

Вятченин Александр Анатольевич

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

Прохоров Анатолий Александрович

Пермь 2015



Содержание

• Введение
• 1. Подготовительная работа
• 1.1 Программные продукты, используемые при проектировании базы данных
• 1.2 Создание структуры базы данных
• 2. Проектирование базы данных
• 2.1 Создание базы данных
• 2.2 Создание таблиц
• 2.3 Заполнение таблиц
• 2.4 Создание триггеров
• 3. Создание запросов
• 3.1 Создание запросов к базе данных
• 3.2 Создание представлений
• 3.3 Создание функции
• Заключение
• Список использованных источников
• Приложение


Введение

программный база триггер запрос

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

В соответствии с заданием курсового проектирования следует разработать базу данных экономической информационной системы по модели «Кондитерская фабрика» с использование программного проекта Microsoft SQL Server 2008 R2.

База данных экономической информационной системы по модели «Кондитерская фабрика» создаётся для упрощения ведения учёта количества, наименования, даты доставки товара, а также множества других аспектов базы данных «Кондитерская фабрика»[1].

Цель курсового проекта: использование программы Microsoft SQL Server 2008 R2 для разработки базы данных экономической информационной системы по модели «Кондитерская фабрика» и закрепление знаний полученных на дисциплине «Проектирование и обслуживание баз данных MS SQL Server».

Задачами работы являются:

• анализ предметной области;

• проектирование БД;

• создание БД;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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



Рисунок 1 - Логическая модель данных

Рисунок 2 - Физическая модель данных



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

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

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

На вкладке Общие (General) зададим имя базы данных Кондитерская фабрика.

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

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

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

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



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

В создании таблиц нам поможет при помощи оператора CREATE TABLE языка Transact-SQL. CREATE TABLE создает новую таблицу, ее столбцы и ограничения целостности в существующей базе данных. Благодаря ERWin Data Modeler, можно из физической модели данных сгенерировать код воссоздания структуры базы данных на Transact SQL. Для этого используется функция Forward Engineer Schema Generation. В данном случае напишем код вручную.

Код создания некоторых из таблиц:

use candyshop_r2

create table Фабрики

(Номер_фабрики nchar(3) primary key,

Наименование char(50),

Город char(15))

create table Кондитерские_изделия

(Номер_изделия nchar(4) primary key,

Наименование char(50),

Номер_фабрики nchar(3) foreign key references Фабрики(Номер_фабрики) on delete no action,

Дата_изготовления date,

Срок_годности int,

Цена int)

create table Доставки

(Номер_доставки nchar(6) primary key,

Номер_водителя nchar(3) foreign key references Водители(Номер_водителя) on delete no action,

Номер_заказчика nchar(3) foreign key references Заказчики(Номер_заказчика) on delete no action,

Дата_вывоза date,

Дата_доставки date,

Сумма int)

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

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

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

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

Созданные таблицы можно посмотреть слева от окна запроса на создание таблиц раскрыв свиток Базы данных\ Кондитерская фабрика\ Таблицы (Рисунок 3).



Рисунок 3 - Таблицы в обозревателе

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

Заполнение таблиц осуществим с помощью оператора INSERT INTO. Таблицу «Фабрики» в базе данных «Кондитерская фабрика» заполним следующим образом:

use CandyShop_R2

insert into Фабрики

values

('001','Кондитерская фабрика ООО "Nestle Пермь"','Пермь'),

('002','Кондитерская фабрика ООО "Казань-Карамель"','Казань'),

('003','Завод кондитерских изделий "Дива"','Екатеринбург'),

('004','Кондитерская фабрика ООО "Вкус желаний"','Пермь');

Остальные таблицы заполняются аналогичным образом. Заполненные таблицы можно просмотреть с помощью запроса:

select *

from Кондитерские_изделия

Результат показан ниже (рисунок 4).

Рисунок 4 - Таблица «Кондитерские_изделия»

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

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

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

1) Триггер типа UPDATE печатает определенный текст, при каждой модификации таблицы «Водители». Триггер- (рисунок 5).

Рисунок 5 - Триггер типа UPDATE

Будет возвращено сообщение «В таблицу были внесены изменения (строк обработано: 1)», так как в результате выполнения оператора UPDATE был запущен триггер. В данном триггере мы задали вывод сообщения, чтобы можно было увидеть работу триггера;

2) Триггер типа DELETE будет сохранять все строки, удаленные из таблицы «Доставка», в таблицу «Доставка_Корзина» для последующего анализа данных. Код создания триггера DELETE, внесение строки в таблицу «Доставка» и её удаление, приведены на рисуноке 6.

Создание триггера «Доставка_Корзина»:

CREATE TRIGGER Корзина

ON Доставки

FOR DELETE

AS

INSERT INTO Доставки_Корзина

SELECT * FROM Доставки_Корзина

GO

Рисунок 6 - Триггер типа DELETE

Отметим, что надпись о количестве обработанных строк отобразилась дважды. Первый это непосредственно удаление строки, а второй запись ее в таблицу «Доставка_Корзина». Это возможно потому, что резервной таблице присвоены те же имена колонок и те же типы данных, что и в исходной таблице. Изначально таблица «Доставка_Корзина» не содержит записей, она будет заполняться по мере удаления записей из таблицы «Доставка»;

3) Триггер INSERT для таблицы «Заказы» будет проверять указано ли количество изделия (хотя бы 1). Проверка триггера представлена на рисунке 7:

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

CREATE TRIGGER Триггер3

ON Заказы

AFTER INSERT

AS

IF (SELECT КОЛИЧЕСТВО FROM ЗАКАЗЫ) < 1

ROLLBACK

Рисунок 7 - Проверка триггера типа INSERT



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

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

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

Создадим два запроса к базе данных «Кондитерская фабрика»:

1) Найдем кондитерские изделия стоимость которых меньше двух недель:

SELECT Наименование,

Срок годности,

Дата_изготовления

FROM Кондитерские_изделия

WHERE Срок годности<14

2) Найдем все шоколадные батончики из таблицы «Кондитерские изделия»

SELECT Наименование

FROM Кондитерские_изделия

WHERE Наименование

LIKE '%Шоколадный батончик%'

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

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

Создадим три представления для разных таблиц базы данных:

1) Представление по таблице «Водители»:

CREATE VIEW Водители_представление

AS SELECT

Номер_водителя,

Фамилия,

Имя,

Отчество

FROM Водители

2) Представление для таблицы «Доставка», в котором выводятся все номера водителей меньше 13:

CREATE VIEW Доставки_представление

AS SELECT

Дата_вывоза,

Дата_доставки,

Номер_водителя

FROM Доставки

WHERE Номер_водителя<003

3) Представление для таблицы «Кондитерские изделия», в котором выводятся только те строки в которых цена изделия превышает 300 рублей:

CREATE VIEW Кондитерские_изделия_представление

AS SELECT

Наименование

FROM Кондитерские_изделия

WHERE Цена >300



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

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

CHARINDEX (`искомое_выражение', строковое_выражение)

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

use CandyShop_r2

SELECT Адрес_магазина

FROM Заказчики WHERE CHARINDEX('Пермь',Адрес_магазина)>0

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

Рисунок 8 - Результат запроса с использованием функции CHARINDEX



Заключение

В ходе выполнения курсового проекта были выполнены все поставленные задачи:

· построение нормализованной базы данных «Кондитерская фабрика»;

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

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

· таблицы были заполнены необходимыми данными.

Перспективой развития этого проекта может стать его практическая реализация. Так же были закреплены на практике теоретические знания, полученных на лекциях по «Проектирование и обслуживание баз данных MS SQL Server», получены новые навыки работы с базами данных.



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

1. Кузнецова, Л.С. «Технология и организация производства кондитерских изделий» - Москва: Издательский центр «Академия», 2013. - 480 с.

2. Нильсен, П. «SQL Server 2005. Библия пользователя». - Москва: ООО «И.Д. Вильямс», 2008. - 1232 с.

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

4. Ульман, Дж. Д. «Основы реляционных баз данных» - Москва: Издательство «Лори», 2006. - 374 с.

5. Грабер, М. «Введение в SQL». - Москва: Издательство «Лори»,1999. - 373 с.

6. Тиори, Т. «Проектирование структур баз данных». В 2 кн., - М.: Мир, 1985. Кн. 1. - 287 с.: Кн. 2. - 320 с.

7. Кондитерские фабрики России [Электронный ресурс] // Современная энциклопедия промышленности России. Заводы и их продукция, промышленные выставки [Офиц. сайт]. URL: http://www.wiki-prom.ru/71otrasl.html (дата обращения: 09.06.2015)



Приложение

SQL-код базы данных «Кондитерская фабрика»

USE CandyShop_r2

CREATE TABLE Фабрики

(

Номер_фабрики nchar(3) PRIMARY KEY,

Наименование char(50),

Город char(15)

)

CREATE TABLE Кондитерские_изделия

(

Номер_изделия nchar(4) PRIMARY KEY,

Наименование char(50),

Номер_фабрики nchar(3) FOREIGN KEY REFERENCES Фабрики(Номер_фабрики) ON DELETE NO ACTION,

Дата_изготовления date,

Срок_годности int,

Цена int

)

CREATE TABLE Доставки

(

Номер_доставки nchar(6) PRIMARY KEY,

Номер_водителя nchar(3) FOREIGN KEY REFERENCES Водители(Номер_водителя) ON DELETE NO ACTION,

Номер_заказчика nchar(3) FOREIGN KEY REFERENCES Заказчики(Номер_заказчика) ON DELETE NO ACTION,

Дата_вывоза date,

Дата_доставки date,

Сумма int

)

CREATE TABLE Водители

(

Номер_водителя nchar(3) PRIMARY KEY,

Фамилия char(15),

Имя char(15),

Отчество char(15)

)

CREATE TABLE Заказы

(

Номер_заказа nchar(5) PRIMARY KEY,

Номер_доставки char(6) FOREIGN KEY REFERENCES Доставки(Номер_доставки) ON DELETE NO ACTION,

Номер_изделия nchar(4) FOREIGN KEY REFERENCES Кондитерские_изделия(Номер_зизделия) ON DELETE NO ACTION,

Количество int

)

CREATE TABLE Заказчики

(

Номер_заказчика nchar(3) PRIMARY KEY,

Фамилия char(15),

Имя char(15),

Отчество char(15),

Адрес_магазина char(50)

)

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