VI Международная студенческая научная конференция Студенческий научный форум - 2014

ВВЕДЕНИЕ

База данных – это информационная модель, позволяющая упорядоченно хранить данные о группе объектов, обладающих одинаковым набором свойств. Программное обеспечение, предназначенное для работы с базами данных, называется система управления базами данных (СУБД). СУБД используются для упорядоченного хранения и обработки больших объемов информации.

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

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

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

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

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

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

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

• построить концептуальную модель базы данных;

• выбрать СУБД для разработки базы данных;

• построить логическую модель базы данных;

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

1 РАЗРАБОТКА КОНЦЕПТУАЛЬНОЙ МОДЕЛИ БАЗЫ ДАННЫХ

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

• информация о продуктах: категория, название, производитель;

• информация о поставках: поставщик, продукт, дата поставки, количество, магазин;

• информация о поставщиках: название, адрес, директор;

• информация о магазине: адрес, директор.

База данных «Сеть магазинов» должна выполнять следующие задачи:

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

• предоставлять полную информацию о товарах по запросам сотрудников и потребителей услуг сети магазинов.

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

Полученная ER-диаграмма приведена на рисунке 1.

Рисунок 1 - ER-диаграмма

2 ЛОГИЧЕСКОЕ ПРОЕКТИРОВАНИЕ

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

• База данных должна удовлетворять актуальным информационным потребностям;

• База данных должна удовлетворять актуальным требованиям за приемлемое время, т. е. заданным требованиям производительности;

• База данных должна удовлетворять выявленным и вновь возникающим требованиям конечных пользователей;

• База данных должна легко расширяться при реорганизации и расширении предметной области;

• База данных должна легко изменяться при изменении программной и аппаратной среды;

• Загруженные в базу данных корректные данные должны оставаться корректными;

• Данные до включения в базу данных должны проверяться на достоверность;

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

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

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

Таблица 1 – Таблица атрибутов для сущности «Продукты»

Поле	Ключи	Размер	Формат	Свойства
Код	первичный ключ	11	bigint	Not null
Код категории	внешний ключ	11	bigint	Not null
Название	отсутствует	255	char	Not null
Код производителя	внешний ключ	11	bigint	Not null

Таблица 2 – Таблица атрибутов для сущности «Поставка»

Поле	Ключи	Размер	Формат	Свойства
Код	первичный ключ	11	bigint	Not null
Код поставщика	внешний ключ	11	bigint	Not null
Код продукта	внешний ключ	11	bigint	Not null
Дата поставки	отсутствует	255	char	Not null
Количество	отсутствует	11	bigint	Not null
Код магазина	внешний ключ	11	bigint	Not null

Таблица 3 – Таблица атрибутов для сущности «Категория»

Поле	Ключи	Размер	Формат	Свойства
Код	первичный ключ	11	bigint	Not null
Название	отсутствует	255	char	Not null

Таблица 4 – Таблица атрибутов для сущности «Производитель»

Поле	Ключи	Размер	Формат	Свойства
Код	первичный ключ	11	bigint	Not null
Название	отсутствует	255	char	Not null
Адрес	отсутствует	255	char	Not null

Таблица 5 – Таблица атрибутов для сущности «Поставщик»

Поле	Ключи	Размер	Формат	Свойства
Код	первичный ключ	11	bigint	Not null
Название	отсутствует	255	char	Not null
Адрес	отсутствует	255	char	Not null
Директор	отсутствует	255	char	Not null

Таблица 5 – Таблица атрибутов для сущности «Магазин»

Поле	Ключи	Размер	Формат	Свойства
Код	первичный ключ	11	bigint	Not null
Адрес	отсутствует	255	char	Not null
Директор	отсутствует	255	char	Not null

3 ФИЗИЧЕСКОЕ ПРОЕКТИРОВНИЕ

3. 1 Создание базы данных и таблиц

Ниже представлен код создания базы данных на языке Т-SQL:

create database samoylov on

( name = 'samoylov',

filename = 'E:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAsamoylov.mdf',

size = 10 mb)

log on (

name = 'samoylov_log',

filename = 'E:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAsamoylov_log.ldf',

size = 3 mb)

Код создания таблицы «Магазин»:

create table Store (id_store bigint primary key, address char (40), manager char (40))

Код создания таблицы «Продукты»:

create table Products (id_products bigint primary key, category bigint, name char (40), manufacturer bigint)

Код создания таблицы «Производитель»:

create table Manufacturer (id_manufacturer bigint primary key, name char (40), address char (40))

Код создания таблицы «Поставка»:

create table Delivery (id_delivery bigint primary key,supplier bigint, product bigint, delivery_date char (40), number bigint, store bigint)

Код создания таблицы «Поставщик»:

create table Supplier (id_supplier bigint primary key, name char (40), address char (40), manager char (40))

Код создания таблицы «Категория»:

create table Category (id_category bigint primary key, name char (40))

3. 2 Добавление внешних ключей

Ниже представлен код SQL-запросов на добавление внешних ключей в таблицы.

Добавление внешних ключей в таблицу «Поставка»:

alter table Delivery add constraint fk_supplier foreign key (supplier) references Supplier (id_supplier)

alter table Delivery add constraint fk_product foreign key (product) references Products (id_products)

alter table Delivery add constraint fk_store foreign key (store) references Store (id_store)

Добавление внешних ключей в таблицу «Продукты»:

alter table Products add constraint fk_category foreign key (category) references Category (id_category)

alter table Products add constraint fk_manufacturer foreign key (manufacturer) references Manufacturer (id_manufacturer)

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

Рисунок 2 – Схема базы данных

3. 3 Заполнение таблиц

Заполнение таблицы «Магазин»:

insert into Store values (1, 'Гагарина', 'Васильев')

insert into Store values (2, 'Садовая', 'Абаджев')

insert into Store values (3, 'Ленина', 'Смирнов')

insert into Store values (4, 'Московская', 'Соколов')

insert into Store values (5, 'Коммунистическая', 'Новиков')

insert into Store values (6, 'Балаковская', 'Зайцев')

insert into Store values (7, 'Топорковская', 'Павлов')

insert into Store values (8, 'Чапаева', 'Федоров')

insert into Store values (9, 'Сванидзе', 'Алексеев')

insert into Store values (10, 'Вольская', 'Яковлев')

Заполнение таблицы «Поставщик»:

insert into Supplier values (1, 'ОАО Лето', 'Московская', 'Штерман')

insert into Supplier values (2, 'ОАО Ягуар', 'Факел Социализма', 'Эйзенштейн')

insert into Supplier values (3, 'ОАО Тополь', 'Владимировская', 'Дмитриев')

insert into Supplier values (4, 'ОАО Фонарь', 'Пугачева', 'Шульга')

insert into Supplier values (5, 'ОАО Град', 'Еремеева', 'Доценко')

insert into Supplier values (6, 'ОАО Небо', 'Краснармейска', 'Соловьев')

insert into Supplier values (7, 'ОАО Пушка', 'Пушкинская', 'Бородин')

insert into Supplier values (8, 'ОАО Радуга', 'Петросянская', 'Петров')

insert into Supplier values (9, 'ОАО Весна', 'Красная', 'Голубев')

insert into Supplier values (10, 'ОАО Ежевика', 'Пульцеровская', 'Козлов')

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

Запрос на выборку всех данных из таблицы «Категория»:

select * from Category

Рисунок 3 – Результат запроса

Запрос на выборку «ГМО» из таблицы «Категория»:

select * from Category inner join Products on id_products = id_category where Category.name = 'ГМО'

Рисунок 4 – Результат запроса

Запрос на выборку продуктов, поставленных с 1.01.2012 по 1.03.2012:

select Products.name, Delivery.delivery_date, Delivery.number from Delivery inner join Products on Delivery.product = Products.id_products where (Delivery.delivery_date > '2012-01-01') and (Delivery.delivery_date < '2012-03-01')

Рисунок 4 – Результат запроса

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

select Supplier.name, sum (Delivery.number) as [Общая поставка] from Delivery inner join Supplier on Delivery.supplier = Supplier.id_supplier group by name

Рисунок 5 – Результат запроса

Запрос на выборку всех продуктов, название которых начинается на букву «М»:

select name from Products where name like 'М%'

Рисунок 6 – Результат запроса

Запрос на выборку общего количества поставок одной и той же фирмы:

select Supplier.name, Delivery.number from Delivery inner join Supplier on Delivery.supplier = Supplier.id_supplier order by Supplier.name compute sum (Delivery.number) by Supplier.name

Рисунок 7 – Результат запроса

Запрос на выборку максимального количества завезенного в магазины продукта из таблицы «Поставка»:

select Products.name, Delivery.number from Delivery inner join Products on Delivery.product = Products.id_products where number = (select MAX(number) from Delivery)

Рисунок 8 – Результат запроса

3. 5 Создание представлений

Код создания представления:

create view Predstavlenie as select name from Products where name like 'М%

Рисунок 9 – Представление

3. 6 Создание процедур и триггеров

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

create procedure del_date @a date

as

begin

select * from Delivery where delivery_date < @a

end

Выполним данную процедуру

exec del_date '2012-01-01'

Рисунок 10 – Демонстрация работы процедуры

Код создания триггера, запрещающий вводить в поле «дата поставки» (delivery_date) дату больше текущей даты:

create trigger Tr_category1 on Delivery for insert

as begin

if ((select delivery_date from inserted) > getdate()) rollback end

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

3. 7 Создание транзакции

Код создания транзакции на добавление 11 и 12 строки в таблицу «Магазин»:

BEGIN TRAN

INSERT INTO Store VALUES (11, 'Еремеева', 'Шишкин')

INSERT INTO Store VALUES (12, 'Коломбо', 'Тугарин')

IF (@@error = 0) BEGIN

COMMIT TRAN

END

ELSE BEGIN

PRINT 'возникла ошибка'

ROLLBACK TRAN

END

Рисунок 12 – Результат транзакции

ЗАКЛЮЧЕНИЕ

В ходе выполнения курсового проекта была создана распределенная база данных «Сеть магазинов». В базе хранятся данные о продуктах, удобно отсортированные по категориям и производителю, датах поставок, количестве и в какие магазины были осуществлены поставки. Также имеется исчерпывающая база по поставщикам, с указанием ФИО директоров, адресов и названий фирм. И хотя мощности MS SQL не были востребованы полностью, однако были отработаны основные навыки по добавлению, удалению данных, созданию разнообразных запросов, транзакций, процедур и триггеров. Таким образом, достигнута цель – получение практических навыков создания автоматизированных информационных систем, основанных на распределенных базах данных.

СПИСОК ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВ

1. Базы данных / О. Л. Голыцина [и др.]. – 2-е изд., доп. и перераб. - М.: Инфра-М, 2007. – 400 с.: ил.

2. Базы данных: учеб. для высш. учеб. заведений / А. Д. Хомоненко [и др.]; под ред. проф. А. Д. Хомоненко. – 4-е изд., доп. и перераб. – СПб.: Корона принт, 2004. – 736 с.

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

4. Малыхина М. П. Базы данных: основы, проектирование, использование / М. П. Малыхина. – СПб.: БХВ-Петербург, 2004. – 512 с.: ил.

5. Рудикова Л. В. Microsoft Office для студента / Л. В. Рудикова. – СПб.: БХВ-Петербург, 2005. – 592 с.: ил.

6. Грофф Дж. Р. SQL: Полное руководство SQL: The Complete Reference / Дж. Р. Грофф, П. Н. Вайнберг; пер. с англ. под ред. В. Р. Гинзбурга. – Изд. 2-е, перераб. и доп. – Киев: BHV, 2001. – 816 с: ил.

7. Дейт, К. Дж. Введение в системы баз данных: An Introduction to Database Systems / К. Дж. Дейт; пер. с англ. – Изд. 8-е. - М.: «Вильямс», 2005. – 1327 с.: ил.

8. Кренке Д. Теория и практика построения баз данных: Database processing / Д. Кренке; пер. с англ. – Изд. 9-е. – СПб.: Питер, 2005. – 859 с.: ил. – (Классика computer science).

9. Омельченко Л. Н. Самоучитель Visual FoxPro 8 / Л. Н. Омельченко. - СПб: БХВ-Петербург, 2003. – 674 с.: ил.

10. Форта Б. Освой самостоятельно SQL: 10 минут на урок: Teach your self SQL: in 10 minutes / Б. Форта; пер. с англ. – М.: Вильямс, 2005. – 288 с.: ил.

Код для цитирования: Скопировать