Проектирование и реализация базы данных в архитектуре 'клиент-сервер'

Проектирование и реализация базы данных в архитектуре 'клиент-сервер'

1. Цель выполнения курсового проекта

Целями данного курсового проекта являются: закрепление знаний и навыков, полученных в рамках курса «Базы данных», и получение практического опыта проектирования и реализации баз данных в архитектуре «клиент-сервер».

2. Анализ предметной области

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

On-line магазин компьютерных дисков с играми GameMarket.net

Магазин занимается продажей компьютерных дисков с играми через интернет с помощью платежной системы «WebMoney» с возможностью доставки покупателю наземной почтой, курьером или авиапочтой (каждый вид доставки имеет свою стоимость и скидки). Каждый покупатель имеет собственный аккаунт, где хранятся его ФИО, логин, пароль, адрес электронной почты, полный домашний адрес, номер счета WebMoney», размер накопительной скидки и контактный телефон. У каждого покупателя есть счета (номера счетов), которые содержат информацию о способе, стоимости и дате отправки купленного покупателем товара, а также скидку на доставку. У каждой продажи есть свой уникальный номер. Покупатель может купить несколько дисков сразу и они будут отправлены единой посылкой в течение 5 дней. Если этот же покупатель совершит еще одну покупку в день его предыдущей покупки, то этот товар будет оправлен в первой посылке. В противном случае новый заказ будет выслан другой посылкой через 5 дней.

Магазин обслуживает персонал. Продавцы занимаются оформлением счетов покупателей. Каждый сотрудник имеет собственный аккаунт, где хранятся его ФИО, логин, пароль, должность, адрес электронной почты, полный домашний адрес, номер счета WebMoney», оклад, процентная ставка к зарплате с продажи товара и контактный телефон.

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

Магазин сотрудничает с поставщиками дисков. У каждого поставщика есть название, адрес электронной почты, адрес, номер счета WebMoney» и контактный телефон. Поставка товара идет партиями, каждая партия имеет свой номер. В партии указан какой товар, по какой цене, в каком количестве, когда и кем поставляется.

Цена на диск складывается из цены последней поставки плюс процент для получения прибыли.

На каждую покупку распространяется фиксированная скидка. Также каждый покупатель имеет индивидуальную накопительную скидку. При общей сумме покупок свыше 1000 руб - 1%, свыше 5000 руб - 5%, свыше 10 000 руб - 10%.

2.2 Ограничения, присутствующие в предметной области

1)      Автоматическое добавление в поле «Количество на складе» таблицы «Товары» количества товара, указанного при осуществлении поставки, а также заполнение поля «Цена последней поставки».

2)      При осуществлении продажи - автоматическое уменьшение числа товара на складе на количество проданного товара, а также вывод на экран ошибки в случае, когда количество на продажу больше, чем количество товара на складе.

)        Проверка того, чтобы дата осуществления покупки, дата выпуска товара и дата поставки товара были не больше текущей.

)        Автоматическая установка даты отправки товара (дата покупки + 5 дней).

)        Подсчет накопительной скидки для покупателя при осуществлении новой покупки.

2.3 Основные задачи, решаемые в предметной области

1)      Подсчет конечной цены продажи.

2)      Подсчет зарплаты сотрудников.

)        Поиск дисков по названию/разработчику/издателю.

)        Поиск покупателя, купившего товара на наибольшую сумму.

)        Поиск самого активного продавца по количеству оформленных счетов.

)        Вывод статистики используемых способов доставки в процентном соотношении.

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

)        Подсчет общей прибыли магазина за заданный период.

)        Поиск самого популярного товара по результатам продаж.

)        Вывод истории заказов заданного клиента.

3. Проектирование инфологической модели данных

Реляционная база данных содержит как структурную, так и семантическую информацию. Структура базы данных определяется числом и видом включенных в нее отношений, и связями типа «один-ко-многим», существующими между кортежами этих отношений. Семантическая часть описывает множество функциональных зависимостей, существующих между атрибутами этих отношений [1].

 К сожалению, не все отношения одинаково желательны. Таблица, отвечающая минимальному определению отношения, может иметь быть неэффективную или неподходящую структуру. Для некоторых отношений изменение данных может привести к нежелательным последствиям, называемых аномалиями модификации (modification anomalies). Аномалии могут быть устранены путем разбиения исходного отношения на два или более новых отношения. В большинстве случаев нормализация является более предпочтительной [3].

3.1 Первая нормальная форма

Отношения, которые соответствуют всем свойствам отношений, находятся в первой нормальной форме:

On-Line Магазин (ФИО покупателя, логин покупателя, пароль покупателя, адрес электронной почты покупателя, полный домашний адрес покупателя, номер счета «WebMoney» покупателя, размер накопительной скидки покупателя, контактный телефон покупателя, дата отправки товара, скидка на доставку, способ доставки, стоимость доставки, ФИО сотрудника, логин сотрудника, пароль сотрудника, должность сотрудника, адрес электронной почты сотрудника, полный домашний адрес сотрудника, номер счета «WebMoney» сотрудника, оклад сотрудника, процентная ставка к зарплате с продажи товара сотрудника, контактный телефон сотрудника, номер счета, номер партии, название игры, разработчик игры, издатель игры, дата выпуска игры, описание игры, количество дисков на складе, процент надбавки на цену покупки у поставщика для получения прибыли, цена последней поставки, номер продажи, дата продажи, скидка на продажу, количество на продажу, количество товара в партии, цена поставки партии, дата поставки партии, название поставщика, адрес электронной почты поставщика, адрес поставщика, номер счета «WebMoney» поставщика, контактный телефон поставщика)

3.2 Вторая нормальная форма

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

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

PK (ФИО покупателя, ФИО сотрудника, номер продажи, название игры, номер партии)

PK ФИО покупателя, логин покупателя, пароль покупателя, адрес электронной почты покупателя, полный домашний адрес покупателя, номер счета «WebMoney» покупателя, размер накопительной скидки покупателя, контактный телефон покупателя.

PK ФИО сотрудника, логин сотрудника, пароль сотрудника, должность сотрудника, адрес электронной почты сотрудника, полный домашний адрес сотрудника, номер счета «WebMoney» сотрудника, оклад сотрудника, процентная ставка к зарплате с продажи товара сотрудника, контактный телефон сотрудника.

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

PK номер продажи, дата продажи, скидка на продажу, количество на продажу, номер счета, дата отправки товара, скидка на доставку, способ доставки, стоимость доставки.

PK номер партии, количество товара в партии, цена поставки партии, дата поставки партии, название поставщика, адрес электронной почты поставщика, адрес поставщика, номер счета «WebMoney» поставщика, контактный телефон поставщика.

Декомпозиция:

Покупатели (ФИО покупателя (РК), логин покупателя, пароль покупателя, адрес электронной почты покупателя, полный домашний адрес покупателя, номер счета «WebMoney» покупателя, размер накопительной скидки покупателя, контактный телефон покупателя)

Сотрудники (ФИО сотрудника (РК), логин сотрудника, пароль сотрудника, должность сотрудника, адрес электронной почты сотрудника, полный домашний адрес сотрудника, номер счета «WebMoney» сотрудника, оклад сотрудника, процентная ставка к зарплате с продажи товара сотрудника, контактный телефон сотрудника)

Продажа (номер продажи (РК), номер счета, дата отправки товара, скидка на доставку, способ доставки, стоимость доставки, дата продажи, скидка на продажу, количество на продажу, ФИО сотрудника (FК), ФИО покупателя (FК), название игры (FК))

Товар (название игры (РК), разработчик игры, издатель игры, дата выпуска игры, описание игры, количество дисков на складе, процент надбавки на цену покупки у поставщика для получения прибыли, цена последней поставки)

Партии товара (номер партии (РК), количество товара в партии, цена поставки партии, дата поставки партии, название поставщика, адрес электронной почты поставщика, адрес поставщика, номер счета «WebMoney» поставщика, контактный телефон поставщика, название игры (FK))

3.3 Третья нормальная форма

Отношение находится в третьей нормальной форме, если оно находится во второй нормальной форме и отсутствует транзитивная зависимость между атрибутами:

Номер партии → название игры

Номер партии → название поставщика

Номер партии → адрес электронной почты поставщика

Номер партии → адрес поставщика

Номер партии → номер счета «WebMoney» поставщика

Номер партии → контактный телефон поставщика

Название игры название поставщика

Название игры адрес электронной почты поставщика

Название игры адрес поставщика

Название игры номер счета «WebMoney» поставщика

Название игры контактный телефон поставщика

Декомпозиция:

Покупатели (ФИО покупателя (РК), логин покупателя, пароль покупателя, адрес электронной почты покупателя, полный домашний адрес покупателя, номер счета «WebMoney» покупателя, размер накопительной скидки покупателя, контактный телефон покупателя)

Сотрудники (ФИО сотрудника (РК), логин сотрудника, пароль сотрудника, должность сотрудника, адрес электронной почты сотрудника, полный домашний адрес сотрудника, номер счета «WebMoney» сотрудника, оклад сотрудника, процентная ставка к зарплате с продажи товара сотрудника, контактный телефон сотрудника)

Продажа (номер продажи (РК), номер счета, дата отправки товара, скидка на доставку, способ доставки, стоимость доставки, дата продажи, скидка на продажу, количество на продажу, ФИО сотрудника (FК), ФИО покупателя (FК), название игры (FК))

Товар (название игры (РК), разработчик игры, издатель игры, дата выпуска игры, описание игры, количество дисков на складе, процент надбавки на цену покупки у поставщика для получения прибыли, цена последней поставки)

Партии товара (номер партии (РК), количество товара в партии, цена поставки партии, дата поставки партии, название игры (FK), название поставщика (FK))

Поставщики (название поставщика (РК), адрес электронной почты поставщика, адрес поставщика, номер счета «WebMoney» поставщика, контактный телефон поставщика)

3.4 Четвертая нормальная форма

Отношение находится в четвертой нормальной форме, если оно находится в третьей нормальной форме, и отсутствуют многозначные зависимости между ключами:

название игры → → номер счета

название игры → → дата отправки товара

название игры → → скидка на доставку

название игры → → способ доставки

название игры → → стоимость доставки

название игры → → ФИО сотрудника

название игры → → ФИО покупателя

Декомпозиция:

Покупатели (ФИО покупателя (РК), логин покупателя, пароль покупателя, адрес электронной почты покупателя, полный домашний адрес покупателя, номер счета «WebMoney» покупателя, размер накопительной скидки покупателя, контактный телефон покупателя)

Сотрудники (ФИО сотрудника (РК), логин сотрудника, пароль сотрудника, должность сотрудника, адрес электронной почты сотрудника, полный домашний адрес сотрудника, номер счета «WebMoney» сотрудника, оклад сотрудника, процентная ставка к зарплате с продажи товара сотрудника, контактный телефон сотрудника)

Счета (номер счета (РК), дата отправки товара, скидка на доставку, способ доставки, стоимость доставки, ФИО сотрудника (FК), ФИО покупателя (FК))

Продажа (номер продажи (РК), дата продажи, скидка на продажу, количество на продажу, название игры (FК), номер счета (FK))

Товар (название игры (РК), разработчик игры, издатель игры, дата выпуска игры, описание игры, количество дисков на складе, процент надбавки на цену покупки у поставщика для получения прибыли, цена последней поставки)

Партии товара (номер партии (РК), количество товара в партии, цена поставки партии, дата поставки партии, название игры (FK), название поставщика (FK))

Поставщики (название поставщика (РК), адрес электронной почты поставщика, адрес поставщика, номер счета «WebMoney» поставщика, контактный телефон поставщика)

3.5 Описание основных сущностей и их атрибутов

Описание основных сущностей и их атрибутов приводится в таблице 3.1.

Таблица 3.1. Описание сущностей и атрибутов

3.6 Выявление связей между сущностями

В рассматриваемой предметной области можно выделить связи, приведенные в таблице 3.2:

Таблица 3.2 - Связи сущностей

4. Концептуальная модель

В качестве СУБД была выбрана PostgreSQL по следующим причинам:

·        PostgreSQL является бесплатной СУБД.

·        Отличная интеграция с языком высокого уровня Java.

·        Как следствие предыдущего пункта, PostgreSQL - идеальное решение для реализации web-приложений, написанных на Java.

·        Поддержка БД практически неограниченного размера.

·        Мощные и надёжные механизмы транзакций и репликации (механизм синхронизации содержимого нескольких копий объекта (например, содержимого базы данных). Репликация - это процесс, под которым понимается копирование данных из одного источника на множество других и наоборот) [4].

·        Наследование.

·        Легкая расширяемость.

В дальнейшем в работе для создания концептуальной модели данных используется CASE-средство ERwin, которое позволяет быстро и наглядно спроектировать модель в виде диаграмм «сущность-связь», а затем сгенерировать SQL код базы данных. Так как ERwin 7.3 не поддерживает PostgreSQL, в качестве СУБД была выбрана MySql 5.x, потому что SQL синтаксис и основные типы данных в PostgreSQL и MySql совпадают.

4.1 Логический уровень модели данных

В ERwin результат проектирования на концептуальном уровне представляется логической моделью данных (рисунок 4.1).

В логической модели данных отображаются сущности и атрибуты, ключевые атрибуты в модели представлены в сущности, над чертой. Внешние ключи (мигрирующие атрибуты из родительской сущности) обозначаются как (FK - Foreign Key)[2]. Логический уровень означает прямое отображение фактов из реальной жизни. Они именуются на естественном языке, с любыми разделителями слов (пробелы, запятые и т.д.). На логическом уровне не рассматривается использование конкретной СУБД, не определяются типы данных (например, целое или вещественное число) и не определяются индексы для таблиц[2].

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

Главное достоинство суррогатного ключа состоит в том, что он никогда не изменяется, поскольку не является информативным полем таблицы (не несёт никакой информации об описываемом записью объекте) [4].

При использовании суррогатных ключей не следует озадачивать пользователя вводом значений, которые не несут для него никакой информации. Они генерируются автоматически независимо от пользователя [1].

Введем суррогатные ключи для сущностей «Покупатели», «Сотрудники», «Поставщики» и «Товар», кроме того, атрибуты «ФИО покупателя» и «ФИО сотрудника» разделим на три атрибута («фамилия», «имя», «отчество») каждый и сгруппируем их в составные альтернативные ключи. Так же альтернативными ключами сделаем атрибуты «название поставщика» и «название игры». Альтернативные ключи (AK - Alternative Key) служат для ускорения поиска по базе данных.

Рисунок 4.1 - Модель данных на логическом уровне в нотации IDEF1x

4.2 Физический уровень модели данных

Модель данных на физическом уровне отличается от модели данных на логическом уровне тем, что она полностью ориентирована на выбранную СУБД, т.е. в отличие от логической модели, в которой не имеет значения, какой конкретно тип данных имеет атрибут, в физической модели данных важно описать информацию о конкретных физических объектах - таблицах, полях, индексах, процедурах и т.д [2]. Для СУБД PostgreSQL характерно то, что все объекты базы данных, должны иметь англоязычное наименование.

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

Рисунок 4.2 - Модель данных на физическом уровне в нотации IDEF1x

Соответствия между объектами логического и физического уровня показаны в следующих таблицах:

инфологической модель база данные

4.3 Сгенерированный в ERwin SQL код таблиц

CREATE TABLE Vendor

(_ID NUMERIC NOT NULL,_Name VARCHAR(30),VARCHAR(100),VARCHAR(12),VARCHAR(20),_account_number VARCHAR(20),KEY (Vendor_ID)

);TABLE Goods

(_ID NUMERIC NOT NULL,VARCHAR(25),VARCHAR(25),VARCHAR(25),TEXT,_of_price_increasing INTEGER,_at_storehouse NUMERIC,_of_last_delivery DOUBLE PRECISION,_of_release DATE,VARCHAR(50),KEY (Goods_ID)

);TABLE Consignment

(_ID NUMERIC NOT NULL,_date DATE,_ID NUMERIC NOT NULL,_count INTEGER,_price DOUBLE PRECISION,_ID NUMERIC NOT NULL,KEY (Consignment_ID),

(Vendor_ID) REFERENCES Vendor (Vendor_ID),

(Goods_ID) REFERENCES Goods (Goods_ID)

); TABLE Buyers

(_ID NUMERIC NOT NULL,_Name VARCHAR(20),_Name VARCHAR(20),_Name VARCHAR(20),VARCHAR(20),VARCHAR(20),VARCHAR(20),_account_number VARCHAR(20),_address VARCHAR(100),VARCHAR(12),_Discount INTEGER,KEY (Buyer_ID)

); TABLE Shop_Staff

(_ID NUMERIC NOT NULL,_Name VARCHAR(20),_Name VARCHAR(20),_Name VARCHAR(20),VARCHAR(20),VARCHAR(20),VARCHAR(20),VARCHAR(20),_account_number VARCHAR(20),_address VARCHAR(100),VARCHAR(12),_rate NUMERIC,DOUBLE PRECISION,KEY (Employee_ID)

);TABLE Accounts

(_ID NUMERIC NOT NULL,_ID NUMERIC NOT NULL,_of_sending DATE,_on_shipping INTEGER,_ID NUMERIC NOT NULL,_of_shipping VARCHAR(30),_of_shipping DOUBLE PRECISION,KEY (Account_ID),

(Buyer_ID) REFERENCES Buyers (Buyer_ID),

(Employee_ID) REFERENCES Shop_Staff (Employee_ID)

);TABLE Reteil

(_ID NUMERIC NOT NULL,_ID NUMERIC NOT NULL,_count INTEGER,INTEGER,_ID NUMERIC NOT NULL,_of_reteil DATE,KEY (Reteil_ID),

(Account_ID) REFERENCES Accounts (Account_ID),

(Goods_ID) REFERENCES Goods (Goods_ID)

);

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

5.1 Последовательности

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

Список последовательностей:

id_accounts - последовательность для суррогатного ключа таблицы Accounts_vendor - последовательность для суррогатного ключа таблицы Vendor _goods - последовательность для суррогатного ключа таблицы Goods_reteil - последовательность для суррогатного ключа таблицы Reteil_buyers - последовательность для суррогатного ключа таблицы Buyers_staff - последовательность для суррогатного ключа таблицы Shop_Staff_consignment - последовательность для суррогатного ключа таблицы Consignment

5.2 Триггеры

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

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

Разработанные триггеры представлены в таблице 5.1.

Таблица 5.1. Описание разработанных триггеров.

.3 Представления

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

Таблица 5.2. Описание разработанных представлений.

5.4 Хранимые процедуры

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

Разработанные хранимые процедуры описаны в таблице 5.3.

Таблица 5.3. Описание разработанных хранимых процедур.

6. Реализация базы данных в среде серверной СУБД PostgreSQL 8.4

Для реализации базы данных была использована программа «SQL Manager 2007 for PostgreSQL», которая является GUI-оболочкой, предназначенной для разработки и администрирования баз данных PostgreSQL.

Пример таблицы представлен на рисунке 6.1.

Рис. 6.1. Таблица buyers.

6.1 Представления

.        Unclaimed_Goods (Вывод товаров, которые не продавались более месяца).

CREATE OR REPLACE VIEW "public"."Unclaimed_Goods" (,_at_storehouse,_of_last_reteil)g.name,.count_at_storehouse,(r.date_of_reteil) AS date_of_last_reteilgoods g,rg.goods_id = r.goods_id AND

(((SELECT max(r.date_of_reteil) AS max FROM reteil r WHERE g.goods_id =.goods_id)) + 30) <= now()BY g.name,.count_at_storehouse;

Результат работы:

6.2 Триггеры

.        consigment_date_check (Проверка даты поставки партии (она должна быть меньше или равна текущей))

CREATE TRIGGER "consigment_date_check" BEFORE INSERT OR UPDATE "public"."consignment" FOR EACH ROW PROCEDURE "public"."cons_date_check"();OR REPLACE FUNCTION "public"."cons_date_check" () RETURNS trigger AS

$body$NEW.consignment_date>now() THEN EXCEPTION 'Введенная дата больше текущей!';

end if;new;;

$body$'plpgsql'ON NULL INPUTINVOKER100;

Результат работы:

Триггеры «goods_date_check», «reteil_date_check» имеют аналогичный код и результат работы.

.        goods_update_from_consig (Автоматическое добавление в поле «Количество на складе» таблицы «Товары» количества товара, указанного при осуществлении поставки, а также заполнение поля «Цена последней поставки»)

CREATE TRIGGER "goods_update_from_consig" AFTER INSERT "public"."consignment" FOR EACH ROW PROCEDURE "public"."goods_works"();OR REPLACE FUNCTION "public"."goods_works" () RETURNS trigger AS

$body$goods SET count_at_storehouse=count_at_storehouse+NEW.goods_countconsignment(goods.goods_id=NEW.goods_id);goods SET price_of_last_delivery=NEW.delivery_priceconsignment(goods.goods_id=NEW.goods_id);new;;

$body$'plpgsql'ON NULL INPUTINVOKER100;

.        reteil_dateSending (Автоматическая установка даты отправки товара (дата покупки + 5 дней))

CREATE TRIGGER "reteil_dateSending" AFTER INSERT "public"."reteil" FOR EACH ROW PROCEDURE "public"."dateSending"();OR REPLACE FUNCTION "public"."dateSending" () RETURNS trigger AS

$body$accounts SET date_of_sending=NEW.date_of_reteil+5(accounts.account_id=NEW.account_id); NEW;;

$body$'plpgsql'ON NULL INPUTINVOKER100;

4.      reteil_update_count (При осуществлении продажи - автоматическое уменьшение числа товара на складе на количество проданного товара, а также вывод на экран ошибки в случае, когда количество на продажу больше, чем количество товара на складе)

CREATE TRIGGER "reteil_update_count" BEFORE INSERT "public"."reteil" FOR EACH ROW PROCEDURE "public"."reteil_works"();OR REPLACE FUNCTION "public"."reteil_works" () RETURNS trigger AS

$body$INTEGER;into cnt g.count_at_storehouse FROM goods g WHERE (g.goods_id=NEW.goods_id);NEW.reteil_count<=cnt THENgoods SET count_at_storehouse=count_at_storehouse-NEW.reteil_countreteil(goods.goods_id=NEW.goods_id);NEW;IF;NEW.reteil_count>cnt THEN EXCEPTION 'На складе нет такого количества данного товара на продажу!';

END IF;;

$body$'plpgsql'ON NULL INPUTINVOKER100;

Результат работы:

.        user_sum_discount (Подсчет накопительной скидки для покупателя при осуществлении новой покупки)

CREATE TRIGGER "user_summ_discount" AFTER INSERT "public"."reteil" FOR EACH ROW PROCEDURE "public"."user_sum_discount"();OR REPLACE FUNCTION "public"."user_sum_discount" () RETURNS trigger AS

$body$_id INTEGER;_id INTEGER;_id INTEGER;INTEGER;DOUBLE PRECISION;DOUBLE PRECISION;:=0;INTO b_id a.buyer_id FROM accounts a WHERE a.account_id=NEW.account_id;a_id IN SELECT a.account_id FROM accounts a WHERE a.buyer_id=b_id LOOPg_id, cnt IN SELECT r.goods_id, r.reteil_count FROM reteil r WHERE (r.account_id=a_id) LOOPINTO seb g.price_of_last_delivery FROM goods g WHERE (g.goods_id=g_id);:=summ+seb*cnt;LOOP;LOOP;buyers SET summ_discount=0 WHERE (buyer_id=b_id);summ>=1000 THENbuyers SET summ_discount=1 WHERE (buyer_id=b_id);IF;summ>=5000 THENbuyers SET summ_discount=5 WHERE (buyer_id=b_id);IF;summ>=10000 THENbuyers SET summ_discount=10 WHERE (buyer_id=b_id);IF;NEW;;

$body$'plpgsql'ON NULL INPUTINVOKER100;

6.3 Хранимые процедуры

.        Final_Price (Подсчет конечной цены продажи)

CREATE OR REPLACE FUNCTION "public"."Final_Price" (id_a integer) RETURNS double precision AS

$body$double precision; sdisc double precision;INTEGER; id_g INTEGER;INTEGER; seb double precision;INTEGER; cnt INTEGER;INTEGER; tmp double precision;:=0;INTO sd b.summ_discount FROM buyers b, accounts a WHERE (b.buyer_id=a.buyer_id) and (a.account_id=id_a);INTO sdisc, apr a.discount_on_shipping, a.price_of_shipping FROM accounts a WHERE (a.account_id=id_a);id_g, disc, cnt IN SELECT r.goods_id, r.discount, r.reteil_count FROM reteil r WHERE (r.account_id=id_a) LOOPINTO seb, per g.price_of_last_delivery, g.percent_of_price_increasing FROM goods g WHERE (g.goods_id=id_g);:=(seb+ seb*per/100)*cnt;:=summ + tmp - tmp*disc/100;LOOP;:=summ-summ*sd/100;:=summ+apr-apr*sdisc/100;summ; ;

$body$'plpgsql'ON NULL INPUTINVOKER100;

Результат работы:

OrdersHistory (Вывод истории заказов заданного клиента)

CREATE OR REPLACE FUNCTION "public"."OrdersHistory" (b_id integer, out "Name" varchar, out "BuyedCount" integer, out "DateOfBuy" date) RETURNS SETOF record AS

$body$_id INTEGER;_id INTEGER;a_id IN SELECT a.account_id FROM accounts a WHERE a.buyer_id="b_id" LOOPr_id,"BuyedCount", "DateOfBuy" IN SELECT r.goods_id, r.reteil_count, r.date_of_reteil FROM reteil rr.account_id=a_id LOOPINTO "Name" g.name FROM goods g WHERE g.goods_id=r_id;NEXT;LOOP;LOOP;;

$body$'plpgsql'ON NULL INPUTINVOKER100;

Результат работы:

.        TopBuyer (Поиск покупателя, купившего товара на наибольшую сумму)

CREATE OR REPLACE FUNCTION "public"."TopBuyer" (out "Lname" varchar, out "Total" double precision) RETURNS record AS

$body$double precision; id_g INTEGER;_a INTEGER; id_b INTEGER; disc INTEGER; double precision; per INTEGER;INTEGER; tmp double precision;varchar;

"Total":=0;id_b, nname IN SELECT b.buyer_id, b.last_name FROM buyers b LOOP :=0;id_a IN SELECT a.account_id FROM accounts a WHERE (a.buyer_id=id_b) LOOP id_g, disc, cnt IN SELECT r.goods_id, r.discount, r.reteil_count FROM reteil r WHERE (r.account_id=id_a) LOOPINTO seb, per g.price_of_last_delivery, g.percent_of_price_increasing FROM goods g WHERE (g.goods_id=id_g);:=(seb+ seb*per/100)*cnt;:=summ + tmp - tmp*disc/100;LOOP;LOOP;summ>"Total" then

"Total":=summ;

"Lname":=nname;if;LOOP;;

$body$'plpgsql'ON NULL INPUTINVOKER100;

Результат работы:

.        PercentOfUsingShiping (Вывод статистики используемых способов доставки в процентном соотношении)

CREATE OR REPLACE FUNCTION "public"."PercentOfUsingShiping" (out "Type_of_shiping" varchar, out "Percentage" double precision) RETURNS SETOF record AS

$body$INTEGER;INTEGER;INTEGER;DOUBLE PRECISION;INTO cnt1 COUNT(a.type_of_shipping) FROM accounts a WHERE (upper(a.type_of_shipping)=upper('Авиапочта'));INTO cnt2 COUNT(a.type_of_shipping) FROM accounts a WHERE (upper(a.type_of_shipping)=upper('Наземная почта'));INTO cnt3 COUNT(a.type_of_shipping) FROM accounts a WHERE (upper(a.type_of_shipping)=upper('Курьер'));:=cnt1+cnt2+cnt3;

"Type_of_shiping":='Авиапочта';

"Percentage":=100*cnt1/summ;NEXT;

"Type_of_shiping":='Наземная почта';

"Percentage":=100*cnt2/summ;NEXT;

"Type_of_shiping":='Курьер';

"Percentage":=100*cnt3/summ;NEXT;;

$body$'plpgsql'ON NULL INPUTINVOKER100;

Результат работы:

4.      Profit (Подсчет общей прибыли магазина за заданный период)

CREATE OR REPLACE FUNCTION "public"."Profit" ("Begin_Date" date, "End_Date" date, out "Cost" double precision, out "Proceeds" double precision, out "Profits" double precision) RETURNS SETOF record AS

$body$double precision; id_g INTEGER;_a INTEGER; disc INTEGER; double precision; per INTEGER;INTEGER; sd INTEGER;double precision; tmp2 double precision;"Begin_Date"<"End_Date" THEN

"Proceeds":=0;

"Cost":=0;id_a IN SELECT a.account_id FROM accounts a LOOP :=0;:=0;INTO sd b.summ_discount FROM buyers b, accounts a WHERE (b.buyer_id=a.buyer_id) and (a.account_id=id_a);id_g, disc, cnt IN SELECT r.goods_id, r.discount, r.reteil_count FROM reteil r WHERE (r.account_id=id_a) and (r.date_of_reteil BETWEEN "Begin_Date" and "End_Date") LOOPINTO seb, per g.price_of_last_delivery, g.percent_of_price_increasing FROM goods g WHERE (g.goods_id=id_g);:=tmp2 + seb*cnt;:=(seb+ seb*per/100)*cnt;:=summ + tmp - tmp*disc/100;LOOP;:=summ-summ*sd/100;

"Cost":="Cost"+tmp2;

"Proceeds":="Proceeds"+summ;LOOP;

"Profits":="Proceeds" - "Cost"; NEXT;

ELSE

RAISE EXCEPTION 'Начальная дата не может быть больше конечной!';

END IF;;

$body$'plpgsql'ON NULL INPUTINVOKER100;

Результат работы:

.        Salary (Подсчет зарплаты сотрудников)

OR REPLACE FUNCTION "public"."Salary" (out "LastName" varchar, out "Post" varchar, out "Salary" double precision, out "Salary_Plus_Wage_Rate" double precision) RETURNS SETOF record AS

$body$double precision; id_g INTEGER;_a INTEGER; id_ss INTEGER;INTEGER; seb double precision;INTEGER; cnt INTEGER;INTEGER; tmp double precision;varchar;id_ss, "LastName", "Post", "Salary", wr IN SELECT ss.employee_id, ss.last_name, ss.post, ss.salary, ss.wage_rate FROM shop_staff ss LOOP :=0;id_a IN SELECT a.account_id FROM accounts a WHERE (a.employee_id=id_ss) LOOP id_g, disc, cnt IN SELECT r.goods_id, r.discount, r.reteil_count FROM reteil r WHERE (r.account_id=id_a) LOOPINTO seb, per g.price_of_last_delivery, g.percent_of_price_increasing FROM goods g WHERE (g.goods_id=id_g);:=summ + (seb+ seb*per/100)*cnt;LOOP;LOOP;

"Salary_Plus_Wage_Rate":="Salary"+ summ*wr/100;NEXT;LOOP;;

$body$'plpgsql'ON NULL INPUTINVOKER100;

Результат работы:

6.      Search(Поиск дисков по названию/разработчику/издателю)

CREATE OR REPLACE FUNCTION "public"."Search" (whattofind varchar, out id integer, out "Name" varchar, out "Developer" varchar, out "Publisher" varchar, out "Price" double precision) RETURNS SETOF record AS

$body$"id", "Name", "Developer", "Publisher", "Price" IN SELECT g.goods_id, g.name, g.developer, g.publisher, (g.price_of_last_delivery+g.price_of_last_delivery*g.percent_of_price_increasing/100), g.count_at_storehouse goods g WHERE ( (lower(g.name) LIKE lower('%' || whattofind || '%')) or

(lower(g.developer) LIKE lower('%' || whattofind || '%')) or (lower(g.publisher) LIKE lower('%' || whattofind || '%')) ) AND (g.count_at_storehouse<>0) LOOPNEXT;LOOP;;

$body$'plpgsql'ON NULL INPUTINVOKER100 ROWS 1000;

Результат работы:

.        TopGood(Поиск самого популярного товара по результатам продаж)

CREATE OR REPLACE FUNCTION "public"."TopGood" (out "NameOfGood" varchar, out "SellingCount" integer) RETURNS record AS

$body$_id INTEGER;VARCHAR;INTEGER;INTEGER;

"SellingCount":=0;g_id, gname IN SELECT g.goods_id, g.name FROM goods g LOOP:=0;cnt IN SELECT r.reteil_count FROM reteil r WHERE (r.goods_id=g_id) LOOP:=cntSumm+cnt;LOOP;cntSumm>"SellingCount" THEN

"SellingCount":=cntSumm;

"NameOfGood":=gname;IF;LOOP;;

$body$'plpgsql'ON NULL INPUTINVOKER100;

Результат работы:

8.      Top_Seller(Поиск самого активного продавца по количеству оформленных счетов)

CREATE OR REPLACE FUNCTION "public"."Top_Seller" (out "Name" varchar, out "Selled" integer) RETURNS record AS

$body$_s INTEGER;INTEGER;VARCHAR;

"Selled":=0;id_s, sname IN SELECT ss.employee_id, ss.last_name FROM shop_staff ss LOOPINTO cnt COUNT(a.account_id) FROM accounts a WHERE (a.employee_id=id_s);cnt>"Selled" THEN

"Selled":=cnt;

"Name":=sname;IF;LOOP;;

$body$'plpgsql'ON NULL INPUTINVOKER100;

Результат работы:

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

Для более удобной работы с базой данных, было разработано клиент-серверное web-приложение на языке высокого уровня Java, в котором в качестве клиента используется браузер, а в качестве сервера приложений - сервлет-контейнер Apache Tomcat 6.0, который связан с серверной частью СУБД PostgreSQL.

При разработке приложения были использованы технологии: JDBC, XML, JSP и Ajax (JQuery), а также паттерны: MVC, Command Factory, Singlton и Object Pool.

Подключение всех пользователей к БД осуществляется с полными правами к БД, а разграничение доступа для разных пользователей реализовано на уровне приложения (ограничение использования паттерна Object Pool).

Возможности приложения:

·        Регистрация покупателей (для новых пользователей)

·        Редактирование персональных данных покупателя (для покупателей)

·        Поиск игр по названию/издателю/разработчику (для всех пользователей)

·        Осуществление покупок (для покупателей)

·        Добавление новых товаров (для продавцов и администратора)

·        Вывод на экран истории заказов (для покупателей)

·        Вывод на экран зарплаты сотрудников (для администратора)

·        Вывод на экран невостребованных больше месяца товаров (для продавцов и администратора)

Рисунок 7.1. Главная страница разработанного web-приложения.

.1 Руководство пользователя

7.1.1 Регистрация

Незарегистрированные пользователи имеют доступ к функции регистрации и поиска товара.

Для регистрации нового пользователя, пройдите по ссылке «Регистрация» и заполните все поля, затем нажмите на кнопку «Зарегистрироваться» - рис. 7.1. В случае успешной регистрации, Вы сможете войти на сайт под своим логином и паролем.

Рисунок 7.1. Регистрация нового пользователя.

7.1.2 Поиск

Поиск по каталогу товаров может осуществлять любой пользователь магазина. Для осуществления поиска введите в соответствующее поле ввода искомый текст и нажмите на кнопку «Искать» - рис. 7.2. Если в поле ввода текста ничего не ввести и нажать на кнопку «Искать», то будет выведен на экран весь список товаров в каталоге, которые есть в наличии на текущий день.

Рисунок 7.2. Поиск товара.

.1.3 Авторизация и деавторизация

Если Вы являетесь зарегистрированным пользователем, то для авторизации введите свой логии и пароль в соответствующие поля и нажмите «Войти» - рис. 7.3.

Рисунок 7.3. Авторизация.

Для деавторизации достаточно нажать на кнопку «Выйти», которая появляется, после успешной авторизации на месте полей ввода логина и пароля - рис. 7.4.

Рисунок 7.4. Деавторизация.

В случае успешной авторизации, в зависимости от Вашей роли в магазине (покупатель, продавец, администратор), смотрите соответствующий раздел данного руководства пользователя.

7.1.4 Возможности для покупателей

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

Для просмотра истории своих заказов, нажмите на соответствующую кнопку в меню покупателя - рис. 7.5.

Рисунок 7.5. Меню покупателя.

Пример истории заказов представлен на рисунке 7.6.

Рисунок 7.6. Пример истории заказов.

Для редактирования своего профиля, нажмите на соответствующую кнопку в меню покупателя - рис. 7.5. Вы увидите ваши регистрационные данные с возможностью их изменить. Для сохранения результатов нажмите на кнопку «Сохранить изменения» - рис. 7.7.

Рисунок 7.7. Редактирование профиля.

Чтобы совершить покупку, с помощью функции поиска (раздел 7.1.2) найдите интересующий Вас товар, а затем в колонке «Купить» нажмите на ссылку с изображением корзины напротив интересующего Вас товара - рис 7.8.

Рисунок 7.8. Осуществление покупки.

После перехода по ссылке Вы увидите страницу оформления заказа, где можете ввести количество покупаемого товара - рис. 7.9. Для осуществления покупки нажмите на кнопку «Купить».

Рисунок 7.9. Страница оформления заказа.

7.1.5 Возможности для продавцов

Продавец с помощью меню продавца может выполнять следующие действия: добавлять в каталог новые товары, а также узнавать о невостребованном товаре в каталоге магазина (товар, который не продавался больше месяца) - рис. 7.10.

Рисунок 7.10. Меню продавца.

Чтобы добавить новый товар, нажмите на кнопку «Добавить новый товар», а затем после перехода на страницу добавления товара, заполните все поля и нажмите на кнопку «Добавить товар» - рис. 7.11.

Рисунок 7.11. Страница добавления нового товара.

Чтобы посмотреть невостребованный товар, нажмите на соответствующую кнопку в меню продавца - рис. 7.10.

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

Рисунок 7.12. Пример результата поиска невостребованного товара.

7.1.6 Возможности для администратора

Администратор может выполнять следующие действия: добавлять новый товар, просматривать невостребованный товар, а также просматривать зарплату сотрудников магазина. Добавление товара и просмотр невостребованного товара осуществляется аналогично с данными действиями для продавцов (раздел 7.1.5), только из меню администратора - рис. 7.13.

Рисунок 7.13. Меню администратора.

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

Пример отчета по зарплате сотрудников представлен на рисунке 7.14.

Рисунок 7.14. Пример отчета по зарплате сотрудников.

7.2 Листинг программы

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

7.2.1 Регистрация нового покупателя

public class Registration implements Command {void execute(HttpServletRequest request, HttpServletResponse response, ServletContext sc, HttpSession session) throws IOException {s = "";c = ((JDBCConnectionPool) sc.getAttribute("ConPool")).checkOut();login = request.getParameter("Rlogin");pass = request.getParameter("Rpass");cpass = request.getParameter("RCpass"); email = request.getParameter("email");lastname = request.getParameter("Rlastname");firstname = request.getParameter("Rfirstname");thirdname = request.getParameter("Rthirdname");webmoney = request.getParameter("Rwebmoney");address = request.getParameter("Raddress");phone = request.getParameter("Rphone");(!phone.equals("") && !cpass.equals("") && !email.equals("") && !login.equals("") && !pass.equals("") && !lastname.equals("") && !firstname.equals("") && !thirdname.equals("") && !webmoney.equals("") && !address.equals("")){{stmt = c.createStatement();.executeUpdate("INSERT INTO buyers (last_name,first_name,third_name,login, password, email, webmoney_account_number,home_address, phone) " +

"VALUES ('" + lastname + "','" + firstname + "','" + thirdname + "','" + login + "','" + pass + "','" + email + "','" + webmoney + "','" + address + "','" + phone + "');");

s = "Регистрация прошла успешно. Теперь Вы можете войти на сайт под своим логином и паролем.";

} catch (SQLException e) {= "К сожалению регистрация невозможна!";

}

}else{= "<center><h3>Заполенены не все поля!</h3><p><p> <a href=\"javascript:history.go(-1)\">Назад</a></center>";.setAttribute("answer", s);dispatcher = sc.getRequestDispatcher("/index.jsp");{.forward(request, response);

} catch (ServletException e) {.printStackTrace();

}

((JDBCConnectionPool) sc.getAttribute("ConPool")).checkIn(c);

}

}

}

7.2.2 Редактирование профиля покупателя

public class EditBuyerProfile implements Command {void execute(HttpServletRequest request, HttpServletResponse response, ServletContext sc, HttpSession session) throws IOException {s = "";c = ((JDBCConnectionPool) sc.getAttribute("ConPool")).checkOut();login = request.getParameter("Rlogin");pass = request.getParameter("Rpass");email = request.getParameter("email");lastname = request.getParameter("Rlastname");firstname = request.getParameter("Rfirstname");cpass = request.getParameter("RCpass");thirdname = request.getParameter("Rthirdname");webmoney = request.getParameter("Rwebmoney");address = request.getParameter("Raddress");phone = request.getParameter("Rphone");b_id = (Integer) session.getAttribute("id");(session.getAttribute("role") != null).setAttribute("succ", true);(!phone.equals("") && !cpass.equals("") && !email.equals("") && !login.equals("") && !pass.equals("") && !lastname.equals("") && !firstname.equals("") && !thirdname.equals("")

&& !webmoney.equals("") && !address.equals("")) {{stmt = c.createStatement();.executeUpdate("UPDATE buyers SET last_name='" + lastname + "',first_name='" + firstname + "',third_name='" + thirdname + "',login='" + login + "', password='" + pass + "', email='" + email + "', webmoney_account_number='" + webmoney + "',home_address='" + address + "', phone='" + phone + "' " +

" WHERE buyers.buyer_id=" + b_id + ";");= "Данные успешно изменены!";

} catch (SQLException e) {= "<center><h3>К сожалению данные изменить невозможно!</h3><p><p> <a href=\"javascript:history.go(-1)\">Назад</a></center>";

}

} else= "<center><h3>Заполенены не все поля!</h3><p><p> <a href=\"javascript:history.go(-1)\">Назад</a></center>";.setAttribute("answer", s);dispatcher = sc.getRequestDispatcher("/index.jsp");{.forward(request, response);

} catch (ServletException e) {.printStackTrace();

}

((JDBCConnectionPool) sc.getAttribute("ConPool")).checkIn(c);

}

}

7.2.3 Вывод на экран истории заказов клиента

public class OrdersHistory implements Command {void execute(HttpServletRequest request, HttpServletResponse response, ServletContext sc, HttpSession session) throws IOException {s = "";{c = ((JDBCConnectionPool) sc.getAttribute("ConPool")).checkOut();stmt = c.createStatement();= FindBadGoods(stmt, session);

((JDBCConnectionPool) sc.getAttribute("ConPool")).checkIn(c);

} catch (SQLException e) {.printStackTrace();

}(session.getAttribute("role") != null).setAttribute("succ", true);.setAttribute("search", s);dispatcher = sc.getRequestDispatcher("/index.jsp");{.forward(request, response);

} catch (ServletException e) {.printStackTrace();

}

}String FindBadGoods(Statement stmt, HttpSession session) {rs;Name;Count;DateOfReteil;sb = new StringBuffer();b_id;_id = (Integer) session.getAttribute("id");(b_id != null && (session.getAttribute("role").equals("buyer"))) {.append("<table border=\"1\">\n" +

" <tr>\n" +

" <td><b>Название</b></td>\n" +

" <td><b>Куплено штук</b></td>\n" +

" <td><b>Дата покупки</b></td>\n" +

" </tr>\n");{= stmt.executeQuery("SELECT * FROM \"OrdersHistory\"('" + b_id + "');");(rs.next()) {= rs.getString(1);= rs.getInt(2);= rs.getDate(3);formatter = new SimpleDateFormat("dd.MM.yyyy");formattedDate = formatter.format(DateOfReteil);.append("<tr>\n" +

" <td align=\"center\">" + Name + "</td>\n" +

" <td align=\"center\">" + Count + "</td>\n" +

" <td align=\"center\">" + formattedDate + "</td>\n" +

" </tr>\n");

}.append("</table><p><p><center><a href=\"javascript:history.go(-1)\">Назад</a></center>");.close();.out.println();

} catch (SQLException e) {.delete(0, sb.length() - 1);.append("Ошибка! В БД отсутствуют запрашиваемые данные!<p><p><center><a href=\"javascript:history.go(-1)\">Назад</a></center>");

}

} else.append("<h3>Для осуществления данной операции Вам необходимо войти на сайт или зарегестрироваться.</h3></center><p><p><center><a href=\"javascript:history.go(-1)\">Назад</a></center>");sb.toString();

}

String getURL() {

return null;

}

}

Заключение

В данном курсовом проекте была создана база данных «On-line магазин», которая содержит информацию о работе интернет-магазина. В ходе выполнения данного курсового проекта были выполнены все пункты задания для курсового проекта:

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

выделение сущностей и атрибутов;

установление связей между сущностями;

проектирование логического и физического уровней, используя CASE-средство ERwin;

реализация базы данных в СУБД PostgreSQL;

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

создание клиентского приложения, работающего с базой данных.

В ходе проектирования и реализации БД были получены знания об основах работы с СУБД PostgreSQL, написании подпрограмм на языке plpgSQL, получен практический опыт самостоятельного проектирования и реализации баз данных в архитектуре «клиент-сервер» на языке высокого уровня Java.

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

1. Методические указания к выполнению курсового проекта по дисциплинам базы данных, управление данными: учебное пособие НГТУ, 2007 г..

. Трипутина, В.В. Проектирование баз данных с помощью Case-средства ErWin. Методические указания к выполнению лабораторных работ: учебное пособие

. Кренке, Д. Теория и практика построения баз данных / Д. Кренке. - СПб: ПИТЕР, 2005 г.

4. Википедия - Свободная энциклопедия [электронный ресурс]. - Режим доступа: <ru.wikipedia.org>