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

Некоммерческое акционерное общество

«АЛМАТИНСКИЙ УНИВЕРСИТЕТ ЭНЕРГЕТИКИ И СВЯЗИ»

Кафедра «Инженерная кибернетика»

КУРСОВАЯ РАБОТА

По дисциплине: «Система управления базами данных (СУБД)»

На тему: «Реализация проекта "Торговая фирма" в среде выбранной системы управления базами данных»

Специальность «Автоматизация и управление»

Выполнил: Курамшин Р. Группа: АУТ-12-4

Проверил: профессор, к.т.н. Ибраева Л.К..



Содержание

Введение

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

2. Разработка концептуальной модели предметной области базы данных «Торговая фирма»

3. Реализация разработанного проекта в среде выбранной СУБД

3.1 Задания

3.2 Преобразование концептуальной схемы для модели «Торговая фирма»

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

3.4 Вопросы к базе данных

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

4.1 Настройка соединения ADO и организация доступа к данным базы данных

Заключение

Список литературы



Введение

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

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



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

торговый клиентский база данные

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

- Продукция

- Производитель

- Клиенты

- Торговый агент

При работе с системой пользователь имеет возможность решать следующие задачи:

- какие товары имеют продажную цену более 200 долларов?

- какие из них имеют закупочную цену менее 150 долларов?

- Какие товары произведены в определенной стране?

- Кто их производители?

- Какой торговый агент продал товар стоимостью выше 300 долларов?

- Даты заказов

- Какова заработная плата торговых агентов?

- Сколько заказов оформил торговый агент в текущем месяце?

- определить самый востребованный товар у каждого клиента;

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

- определить сколько заказов было совершено в текущем месяце;

- рассчитать на какую сумму и какое количество товаров приобрел определенный клиент;

- определить на какую сумму было продано определенного товара за месяц;



2. Разработка концептуальной модели предметной области базы данных «Торговая фирма»

Разработку модели начнем с выделения основных сущностей. По описанию предметной области можно выделить следующие объектные множества:

- Клиент

- Товар

- Торговый агент

- Производитель

Объектное множество «Клиент» имеет следующие атрибуты:

- Код клиента(ключевое поле)

- Наименование клиента

- Адрес

- Телефон

Объектное множество «Производитель» имеет следующие атрибуты:

- Код производителя

- Наименование производителя

- Страна

- Телефон



Из описания предметной области товар имеет производителя. Причем у каждого товара есть только один производитель, но один производитель может выпускать много товаров. Тогда между объектными множествами «Производитель» и «Товар» необходимо установить связь «один-ко-многим». Тогда первичный ключ объектного множества «Производитель» будет являться внешним ключом к объектному множеству «Товар».

Объектное множество «Товар» имеет следующие атрибуты:

- код товара(первичный ключ)

- Наименование товара

- код производителя(внешний ключ)

- закупочная цена

- продажная цена

- количество



Клиент делает заказы на товар. Объединяем объектное множество «Клиент» и «Товар» и создаем составное объектное множество «Заказы».

Объектное множество «Торговый агент» имеет следующие атрибуты:

- код агента

- ФИО агента

- Адрес

- Телефон

- Заработная плата



Торговый агент оформляет заказы.

Торговый агент может оформить много заказов. Но каждый заказ оформляется только одним торговым агентом. Тогда между объектными множествами «Торговый агент» и «Заказы» необходимо установить связь «Один-ко-многим». Тогда первичный ключ объектного множества «Торговый агент» будет являться внешним ключом объектного множества «Заказы».

Концептуальная модель базы данных «Торговая фирма»

Основные предметно-значимые атрибуты сущностей:

- Производитель ( код производителя, наименование, страна, телефон).

- Клиенты (код клиента, ФИО, адрес, телефон).

- Торговый агент (код агента, ФИО, телефон, адрес, заработная плата)

- Товар (код товара, код производителя, наименование товара, закупочная цена, продажная цена, количество).

- Заказы (номер заказа, код клиента, код товара, код торгового агента дата заказа, количество, сумма заказа).



3. Реализация разработанного проекта в среде выбранной СУБД

3.1 Задания

1.Выполните преобразование концептуальной модели рассматриваемой вами предметной области в реляционную модель:

- преобразуйте объектные множества и атрибуты; определите первичные ключей;

- преобразуйте отношения; определите внешние ключи;

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

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

2.Выберите среду реализации. Среда реализации - MS SQL Server.

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

4.Заполните таблицы данными. Приведите в пояснительной записке образцы данных в таблицах, примеры добавления, удаления, редактирования записей.

5.Сформулируйте вопросы к базе данных. Установите межтабличные связи.

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



3.2 Преобразование концептуальной схемы для модели «Торговая фирма»

В соответствии с рассмотренными правилами для модели «Торговая компания» преобразуем вначале объектные множества. Получим следующие реляционные таблицы:

- Производитель ( код производителя, наименование, страна, телефон).

- Клиент (код клиента, ФИО, адрес, телефон).

- Торговый агент (код агента, ФИО, телефон, адрес, заработная плата)

- Товар ( код товара, наименование товара, закупочная цена, продажная цена, количество).

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

- Заказы (номер заказа, код клиента, код товара, дата заказа, количество, сумма заказа).

Так как объектные множества «Производитель» и «Товар» связаны отношением «один-ко-многим», ключ таблицы «Производитель» должны разместить в таблице «Товар» в качестве внешнего ключа. Тогда структура таблицы «Товар» будет иметь вид:

- Товар ( код товара, код производителя, наименование товара, закупочная цена, продажная цена, количество).

Итак, реляционная схема базы данных «Торговая компания» имеет вид (ключевые поля выделены курсивом):

- Производитель ( код производителя, наименование, страна, телефон).

- Клиенты (код клиента, ФИО, адрес, телефон).

- Торговый агент (код агента, ФИО, телефон, адрес, заработная плата)

- Товар (код товара, код производителя, наименование товара, закупочная цена, продажная цена, количество).

Внешние ключи: код производителя является внешним ключом, ссылающимся на ключ таблицы «Производитель»;

- Заказы (номер заказа, код клиента, код товара, код торгового агента дата заказа, количество, сумма заказа).

Внешние ключи: код клиента является внешним ключом, ссылающимся на ключ таблицы «Клиент»; код товара является внешним ключом, ссылающимся на ключ таблицы «Товар»; код торгового агента является внешним ключом, ссылающимся на ключ таблицы «Торговый агент».

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

Для начала создаем базу данных.

create database Торговая_фирма

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

create table Производитель

(Код_производителя int not null primary key,

Наименование_производителя nvarchar(max) null,

Страна nvarchar(max) null,

Телефон nvarchar(max))

create table Клиенты

(Код_клиента int not null primary key,

Наименование_клиента nvarchar(max),

Адрес_клиента nvarchar(max),

Телефон_клиента nvarchar(max))

create table Торговый_агент

(Код_агента int not null primary key,

ФИО_агента nvarchar(max),

Телефон_агента nvarchar(max),

Адрес_агента nvarchar(max),

Заработная_плата money)

create table Товар

(Код_товара int not null primary key,

Наименование_товара nvarchar(max),

Код_производителя int not null,

Закупочная_цена money,

Продажная_цена money,

Количество_товара int)

create table Заказы

(Номер_заказа int not null primary key,

Код_клиента int not null,

Код_товара int not null,

Код_агента int not null,

Дата_заказа smalldatetime,

Количество int,

Сумма_заказа money)

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

Торговый агент



В таблице «Торговый агент» поле заработная плата рассчитывается как 5 % от сумм заказов, которые он оформил.

update Торговый_агент

set Заработная_плата=0.05*(select SUM(Сумма_заказа) from Заказы where

Торговый_агент.Код_агента=Заказы.Код_агента)

Заказы

В таблице «Заказы» сумма заказа вычисляется как произведение продажной цены товара в таблице «Товар» на количество в таблице «Заказы».

update Заказы

set Сумма_заказа=Количество*(select Товар.Продажная_цена from товар where товар.Код_товара=Заказы.Код_товара



Товар

В таблице «Товар» наценка на товар составляет 10% от закупочной цены.

update Товар

set Продажная_цена=Закупочная_цена+Закупочная_цена*0.1

Клиент



Производитель

Схема данных «Торговая компания»

3.4 Вопросы к базе данных

1) Какие товары имеют продажную цену более 20000?

select наименование_товара, Продажная_цена

from Товар

where Продажная_цена>20000



2) Какие товары имеют закупочную цену менее 7000?

select наименование_товара, закупочная_цена

from Товар

where Закупочная_цена<10000

3) Какие товары произведены в определенной стране и кто их производители?

select наименование_товара, наименование_производителя, Страна

from товар, Производитель

where товар.Код_производителя=Производитель.Код_производителя and Страна='Китай'



4) Какой торговый агент продал товар стоимостью выше 15000?

select ФИО_агента, Наименование_товара, Продажная_цена

from Торговый_агент, Товар, Заказы

where Торговый_агент.Код_агента=заказы.Код_агента and Заказы.Код_товара=Товар.код_товара and товар.Продажная_цена>30000

5) Сколько заказов оформил каждый торговый агент в текущем месяце?

select ФИО_агента, count(номер_заказа) as количество_оформленных_заказов

from Торговый_агент, заказы

where Торговый_агент.Код_агента=Заказы.Код_агента and Дата_заказа between '01.10.2014' and '31.10.2014'

group by ФИО_агента

6) Определить сколько заказов было совершено в текущем месяце



select count(Номер_заказа) as кол_заказов_за_месяц

from Заказы

where Дата_заказа between '01.09.2014' and '30.09.2014'

7) Определить на какую сумму было продано каждого товара за месяц

select наименование_товара, Sum(Сумма_заказа) as общая_сумма

from Товар, Заказы

where Товар.код_товара=Заказы.Код_товара and Заказы.Дата_заказа between '01.09.2014' and '30.09.2014'

group by Наименование_товара

8) Найти торговых агентов, чьи заказы не оплачены, а также сумму заказа, и клиента который не оплатил заказ, и вывести их телефоны.

select ФИО_агента, номер_заказа, сумма_заказа, Наименование_клиента, телефон_клиента, оплата

from Заказы, Торговый_агент, Клиенты

where Торговый_агент.Код_агента=Заказы.Код_агента and клиенты.Код_клиента=Заказы.Код_клиента and Оплата='False'



Создадим для нашей базы данных процедуры для облегчения обновления данных.

Для начала добавим в таблицу «Товар» столбец остаток, который будет отображать оставшееся количество товара на складе.

alter table товар

add остаток int

Далее рассчитаем остаток товара на складе. Остаток будет равен разнице между количеством товара на складе и количеством заказанного товара.

update Товар

set остаток=Количество_товара-(select sum(количество) from Заказы

where товар.код_товара=заказы.Код_товара)



Создадим процедуру обновления остатка товара на складе для удобства.

create procedure остаток

as update Товар

set остаток=Количество_товара-(select sum(количество) from Заказы

where товар.код_товара=заказы.Код_товара)

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

create procedure сумма_заказа

as update Заказы

set Сумма_заказа=Количество*(select Товар.Продажная_цена from товар where товар.Код_товара=Заказы.Код_товара)



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

create view Товар_клиенты

as select наименование_товара, наименование_производителя, страна, продажная_цена

from товар, Производитель

where Производитель.Код_производителя=Товар.Код_производителя

create view Счет_на_оплату

as select номер_заказа, Наименование_клиента, Наименование_товара, ФИО_агента, Дата_заказа, Количество, Сумма_заказа, Оплата

from заказы, клиенты, производитель, товар, торговый_агент

where Клиенты.Код_клиента=Заказы.Код_клиента and Производитель.Код_производителя=Товар.Код_производителя and

Товар.код_товара=Заказы.код_товара and Торговый_агент.код_агента=заказы.код_агента

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

create trigger Остаток_товара

on заказы

for

insert as

declare @остаток1 int, @остаток2 int

select @остаток1=Заказы.Количество, @остаток2=Товар.Количество_товара

from Товар, Заказы

where товар.Код_товара=заказы.Код_товара

if @остаток1>@остаток2

begin

rollback tran

raiserror('Недостаточно товара на складе',16,3)

end

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



При оформлении или редактирования данных в базе, необходимо предусмотреть, чтобы данные о неоплаченных заказах нельзя было удалить. Для этого создадим триггер. Для начала необходимо в таблицу «Заказы» добавить столбец «Оплата», который показывал бы, оплачен ли товар или нет.

alter table заказы

add Оплата bit

Далее создаем сам триггер.

create trigger заказ_не_оплачен

on заказы

for

delete as

declare @номер_заказа int, @код_клиента int, @код_товара int, @код_агента int, @дата_заказа smalldatetime, @количество int, @сумма_заказа money, @оплата bit

select *

from Заказы, Товар, Клиенты, Торговый_агент

where Заказы.Код_товара=Товар.код_товара and Заказы.Код_агента=Торговый_агент.Код_агента and заказы.Код_клиента=Клиенты.Код_клиента

if @оплата='False'

begin

rollback tran

raiserror ('Данные невозможно удалить, заказ не оплачен',16,3)

end



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

4.1 Настройка соединения ADO и организация доступа к данным базы данных

Чтобы обеспечить сеанс связи приложения с базой данных, необходимо установить соединение между ними и настроить его параметры. Для этих целей был выбран компонент ADOConnection. В окне инспектора объектов в свойствах ADO Connection выбрали Connection String. В появившемся окне шелкнули на кнопке Build; в окне «Свойства связи с данными» на вкладке «Поставщик данных» выбрали SQL Native Client; далее из предоставленного списка выбрали нашу БД Торговая фирма. Для локальных баз данных и использовании в нем запросов был использован компонент Query (ADOQuery), представленный классом TQuery. Он обеспечивает самый быстрый и простой доступ к таблице. Также был выбран источник данных (DataSource), осуществляющий обмен информацией между компонентами Dataset и компонентами визуализации и управления данными.

Data Module

Для визуального отображения таблиц на форме программы был выбран компонент DBGrid. DBNavigator служит для ввода новых данных, редактирования старых, удаления и других операций.



Основная форма программы

В данной основной форме программы отображаются кнопки для открытия всех таблиц нашей БД. Запросы расположены внутри форм, где расположены таблицы.

Далее приведены формы программы

Форма «Данные о производителе»



Форма «Товары»

В таблице «Товары» расположена панель отбора данных по названию производителя, и по цене.

Форма «Клиенты»



Форма «Заказы»

В форме «Заказы» реализованы кнопки выбора неоплаченных и оплаченных заказов.

Выбор запроса



Запрос «Торговые агенты продали товар стоимостью выше 15000»

Форма «Реквизиты компании»



Заключение

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

Разработанная модель предметной области отвечает всем требованиям работы. Она является наглядным представлением описания данной предметной области.

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

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

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



Список литературы

1. Ибраева Л.К. Проектирование баз данных. Конспект лекций для студентов всех форм обучения специальности 5В0702 - «Автоматизация и управление». - Алматы: АУЭС, 2010.

2. Ибраева Л.К. Проектирование баз данных. Конспект лекций для студентов всех форм обучения специальности 5В0702 - «Автоматизация и управление». - Алматы: АУЭС, 2010.

3. Базы данных, учебник для высших учебных заведений под редакцией Хомоненко А.Д С - П Корона 2000.

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

...