Проектирование и реализация базы данных "Оn-line магазин"

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

Министерство образования и науки Российской Федерации

Федеральное агентство по образованию

ИРКУТСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ

Кафедра Автоматизированных Систем

Допускаю к защите

Руководитель Серышева

Ирина Анатольевна

И.О. Фамилия

Проектирование и реализация базы данных «On-line магазин»

ПОЯСНИТЕЛЬНАЯ ЗАПИСКА

к курсовому проекту по дисциплине

Базы данных

Выполнил студент группы АСУ-06-1 ________ Семигузов Е.Ю.

Иркутск 2010 г



ИРКУТСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ

ЗАДАНИЕ

НА КУРСОВОЕ ПРОЕКТИРОВАНИЕ (КУРСОВУЮ РАБОТУ)

По курсу Базы данных

Студенту Семигузову Е.Ю.

Тема проекта: Проектирование и реализация базы данных «On-line магазин»

Исходные данные:

1. Выбрать и изучить предметную область самостоятельно.

2. Разработать модель данных.

3. Выполнить проектирование логического и физического уровней с помощью CASE-средства ERwin.

4. Выполнить реализацию базы данных в СУБД.

5. Разработать запросы, хранимые процедуры, триггеры.

6. Разработать клиентское приложение.

Рекомендуемая литература: Дорофеев A.С. «Методические указания к выполнению курсового проекта по дисциплинам базы данных, управление данными»; Трипутина В.В. «Проектирование баз данных с помощью Case-средства ErWin. Методические указания к выполнению лабораторных работ»; Кренке Д. «Теория и практика построения баз данных».

Графическая часть на _________листах

Дата выдачи задания “16” ноября 2009 г.

Дата представления проекта руководителю “11” января 2010 г.

Руководитель курсовой работы Серышева Ирина Анатольевна



Оглавление

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

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

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

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

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

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

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

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

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

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

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

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

3.7 Инфологическая модель данных в нотации Чена.

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

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

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

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

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

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

5.2 Триггеры.

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

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

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

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

6.2 Триггеры.

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

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

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

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

7.1.2 Поиск.

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

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

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

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

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

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

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

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

8. Заключение.

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



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

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



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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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



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. Описание сущностей и атрибутов

Сущность	Описание сущности	Атрибут	Описание атрибута
Покупатели	Содержит информацию о покупателях	ФИО покупателя (РК)	Фамилия, имя, отчество покупателя (первичный ключ)
		Логин покупателя	Логин покупателя для входа в информационную систему
		Пароль покупателя	Пароль покупателя для входа в информационную систему
		Адрес электронной почты покупателя	Адрес электронной почты для обратной связи с покупателем
		Полный домашний адрес покупателя	Адрес места проживания покупателя, куда будут оправлены покупки
		Номер счета «WebMoney» покупателя	Счет «WebMoney» покупателя для осуществления безналичных расчетов
		Размер накопительной скидки покупателя	Накопительная скидка покупателя, зависящая от общей суммы его покупок
		Контактный телефон покупателя	Контактный телефон для обратной связи с покупателем
Сотрудники	Содержит информацию о сотрудниках	ФИО сотрудника (РК)	Фамилия, имя, отчество сотрудника (первичный ключ)
		Логин сотрудника	Логин сотрудника для входа в информационную систему
		Пароль сотрудника	Пароль сотрудника для входа в информационную систему
		Адрес электронной почты сотрудника	Адрес электронной почты для обратной связи с сотрудником
		Полный домашний адрес сотрудника	Адрес, где прописан сотрудник
		Номер счета «WebMoney» сотрудника	Счет «WebMoney» сотрудника для осуществления безналичных расчетов
		Должность сотрудника	Должность, занимаемая сотрудником
		Оклад сотрудника	Оклад, ежемесячно получаемый сотрудником
		Процентная ставка к зарплате с продажи товара сотрудника	Процент от стоимости оформленных продавцом продаж, прибавляемый к его окладу
		Контактный телефон сотрудника	Контактный телефон для обратной связи с сотрудником
Счета	Содержит информацию о счетах покупателей	Номер счета (РК)	Номер счета (первичный ключ)
		Дата отправки товара	Дата, когда будет оправлен покупателю купленный им товар
		Скидка на доставку	Скидка на товар, отправляемый покупателю
		Способ доставки	Способ доставки (наземная почта, авиапочта, курьер)
		Стоимость доставки	Стоимость перевозки товара
		ФИО сотрудника (FК)	Фамилия, имя, отчество сотрудника (внешний ключ от сущности «Сотрудники»)
		ФИО покупателя (FК)	Фамилия, имя, отчество покупателя (внешний ключ от сущности «Покупатели»)
Продажа	Содержит информацию о продажах	Номер продажи (РК)	Номер продажи (первичный ключ)
		Дата продажи	Дата осуществления продажи
		Скидка на продажу	Скидка на покупаемый товар
		Количество на продажу	Количество покупаемого товара
		Название игры (FК)	Название игры (внешний ключ от сущности «Товар»)
		Номер счета (FK)	Номер счета (внешний ключ от сущности «Счета»)
Товар	Содержит информацию о товарах	Название игры (РК)	Название игры (первичный ключ)
		Разработчик игры	Название компании-разработчика игры
		Издатель игры	Название компании-издателя игры
		Дата выпуска игры	Дата, когда игра была отправлена на прилавки магазинов
		Количество дисков на складе	Хранимое на складе количество копий данной игры
		Описание	Краткое описание сюжета и возможностей игры
		Процент надбавки на цену покупки у поставщика для получения прибыли	Процент надбавки, который суммируется с ценой последней поставки данной игры
		Цена последней поставки	Цена последней поставки
Партии товара	Содержит информацию о поступивших партиях товаров	Номер партии (РК)	Номер партии (первичный ключ)
		Количество товара в партии	Количество товара в пришедшей на склад партии
		Цена поставки партии	Цена поставки данной партии товара
		Дата поставки партии	Дата поставки данной партии товара
		Название игры (FK)	Название игры (внешний ключ от сущности «Товар»)
		Название поставщика (FK)	Название поставщика (внешний ключ от сущности «Поставщики»)
Поставщики	Содержит информацию о поставщиках	Название поставщика (РК)	Название компании поставщика (первичный ключ)
		Адрес электронной почты поставщика	Адрес электронной почты для обратной связи с поставщиком
		Адрес поставщика	Адрес компании поставщика
		Номер счета «WebMoney» поставщика	Счет «WebMoney» поставщика для осуществления безналичных расчетов
		Контактный телефон поставщика	Контактный телефон для обратной связи с поставщиком

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

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

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

Родительская сущность	Дочерняя сущность	Описание связи	Мощность связи
Покупатели	Счета	Покупатели имеют Счета	1:M
Сотрудники	Счета	Сотрудники оформляют Счета	1:M
Поставщики	Партии товара	Поставщики поставляют Партии товара	1:M
Товар	Продажа	Товар помещается в Продажу	1:M
Товар	Партии товара	Товар поставляется в Партии товара	1:M
Счета	Продажи	Счета содержат Продажи	1:M

3.7 Инфологическая модель данных в нотации Чена

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

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

Рисунок 3.1. Модель данных в нотации Чена.



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

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

Сущность	Таблица
Название	Название
Партии товара	Accounts
Персонал магазина	Buyers
Покупатели	Consignment
Поставщики	Goods
Продажа	Reteil
Счета	Shop_Staff
Товар	Vendor

Attribute(s) of "Партии товара" Entity	Column(s) of "Consignment" Table
Название	Name	Datatype	Is PK	Is FK
Номер партии	Consignment_ID	NUMERIC	Yes	No
Дата	Consignment_date	DATE	No	No
Номер поставщика	Vendor_ID	NUMERIC	No	Yes
Количество товара в партии	Goods_count	INTEGER	No	No
Цена поставки	Delivery_price	DOUBLE PRECISION	No	No
Номер товара	Goods_ID	NUMERIC	No	Yes

Attribute(s) of "Сотрудники" Entity	Column(s) of "Shop_Staff" Table
Название	Name	Datatype	Is PK	Is FK
Номер сотрудника	Employee_ID	NUMERIC	Yes	No
Фамилия	Last_Name	VARCHAR(20)	No	No
Имя	First_Name	VARCHAR(20)	No	No
Отчество	Third_Name	VARCHAR(20)	No	No
Логин	Login	VARCHAR(20)	No	No
Пароль	Password	VARCHAR(20)	No	No
Должность	Post	VARCHAR(20)	No	No
email	email	VARCHAR(20)	No	No
Номер счета WebMoney	WebMoney_account_number	VARCHAR(20)	No	No
Домашний адрес	Home_address	VARCHAR(100)	No	No
Контактный телефон	Phone	VARCHAR(12)	No	No
Ставка от продажи, %	Wage_rate	NUMERIC	No	No
Оклад, руб	Salary	DOUBLE PRECISION	No	No

Attribute(s) of "Покупатели" Entity	Column(s) of "Buyers" Table
Название	Name	Datatype	Is PK	Is FK
Номер покупателя	Buyer_ID	NUMERIC	Yes	No
Фамилия	Last_Name	VARCHAR(20)	No	No
Имя	First_Name	VARCHAR(20)	No	No
Отчество	Third_Name	VARCHAR(20)	No	No
Логин	Login	VARCHAR(20)	No	No
Пароль	Password	VARCHAR(20)	No	No
email	email	VARCHAR(20)	No	No
Номер счета WebMoney	WebMoney_account_number	VARCHAR(20)	No	No
Домашний адрес	Home_address	VARCHAR(100)	No	No
Контактный телефон	Phone	VARCHAR(12)	No	No
Накопительная скидка, %	Summ_Discount	INTEGER	No	No

Attribute(s) of "Поставщики" Entity	Column(s) of "Vendor" Table
Название	Name	Datatype	Is PK	Is FK
Номер поставщика	Vendor_ID	NUMERIC	Yes	No
Название	Vendor_Name	VARCHAR(30)	No	No
Адрес	Address	VARCHAR(100)	No	No
email	email	VARCHAR(20)	No	No
Номер счета WebMoney	WebMoney_account_number	VARCHAR(20)	No	No
Телефон	Phone	VARCHAR(12)	No	No

Attribute(s) of "Продажа" Entity	Column(s) of "Reteil" Table
Название	Name	Datatype	Is PK	Is FK
Номер продажи	Reteil_ID	NUMERIC	Yes	No
Номер счета	Account_ID	NUMERIC	No	Yes
Количество	Reteil_count	INTEGER	No	No
Скидка, %	Discount	INTEGER	No	No
Номер товара	Goods_ID	NUMERIC	No	Yes
Дата продажи	Date_of_reteil	DATE	No	No

Attribute(s) of "Счета" Entity	Column(s) of "Accounts" Table
Название	Name	Datatype	Is PK	Is FK
Номер счета	Account_ID	NUMERIC	Yes	No
Номер покупателя	Buyer_ID	NUMERIC	No	Yes
Дата отправки товара	Date_of_sending	DATE	No	No
Скидка на доставку, %	Discount_on_ shipping	INTEGER	No	No
Номер сотрудника	Employee_ID	NUMERIC	No	Yes
Способ доставки	Type_of_shipping	VARCHAR(30)	No	No
Стоимость доставки	Price_of_shipping	DOUBLE PRECISION	No	No
Attribute(s) of "Товар" Entity	Column(s) of "Goods" Table
Название	Name	Datatype	Is PK	Is FK
Номер товара	Goods_ID	NUMERIC	Yes	No
Разработчик	Developer	VARCHAR(25)	No	No
Название	Name	VARCHAR(25)	No	No
Издатель	Publisher	VARCHAR(25)	No	No
Описание	Description	TEXT	No	No
Дата выпуска	Date_of_release	DATE	No	No
Процент увеличения цены	Percent_of_price_ increasing	INTEGER	No	No
Количество на складе	Count_at_storehouse	NUMERIC	No	No
Цена последней поставки	Price_of_last_ delivery	DOUBLE PRECISION	No	No
Изображение	Image	VARCHAR(50)	No	No

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

CREATE TABLE Vendor

(

Vendor_ID NUMERIC NOT NULL,

Vendor_Name VARCHAR(30),

Address VARCHAR(100),

Phone VARCHAR(12),

email VARCHAR(20),

WebMoney_account_number VARCHAR(20),

PRIMARY KEY (Vendor_ID)

);

CREATE TABLE Goods

(

Goods_ID NUMERIC NOT NULL,

Developer VARCHAR(25),

Name VARCHAR(25),

Publisher VARCHAR(25),

Description TEXT,

Percent_of_price_increasing INTEGER,

Count_at_storehouse NUMERIC,

Price_of_last_delivery DOUBLE PRECISION,

Date_of_release DATE,

Image VARCHAR(50),

PRIMARY KEY (Goods_ID)

);

CREATE TABLE Consignment

(

Consignment_ID NUMERIC NOT NULL,

Consignment_date DATE,

Vendor_ID NUMERIC NOT NULL,

Goods_count INTEGER,

Delivery_price DOUBLE PRECISION,

Goods_ID NUMERIC NOT NULL,

PRIMARY KEY (Consignment_ID),

(Vendor_ID) REFERENCES Vendor (Vendor_ID),

(Goods_ID) REFERENCES Goods (Goods_ID)

);

CREATE TABLE Buyers

(

Buyer_ID NUMERIC NOT NULL,

Last_Name VARCHAR(20),

First_Name VARCHAR(20),

Third_Name VARCHAR(20),

Login VARCHAR(20),

Password VARCHAR(20),

email VARCHAR(20),

WebMoney_account_number VARCHAR(20),

Home_address VARCHAR(100),

Phone VARCHAR(12),

Summ_Discount INTEGER,

PRIMARY KEY (Buyer_ID)

);

CREATE TABLE Shop_Staff

(

Employee_ID NUMERIC NOT NULL,

Last_Name VARCHAR(20),

First_Name VARCHAR(20),

Third_Name VARCHAR(20),

Login VARCHAR(20),

Password VARCHAR(20),

Post VARCHAR(20),

email VARCHAR(20),

WebMoney_account_number VARCHAR(20),

Home_address VARCHAR(100),

Phone VARCHAR(12),

Wage_rate NUMERIC,

Salary DOUBLE PRECISION,

PRIMARY KEY (Employee_ID)

);

CREATE TABLE Accounts

(

Account_ID NUMERIC NOT NULL,

Buyer_ID NUMERIC NOT NULL,

Date_of_sending DATE,

Discount_on_shipping INTEGER,

Employee_ID NUMERIC NOT NULL,

Type_of_shipping VARCHAR(30),

Price_of_shipping DOUBLE PRECISION,

PRIMARY KEY (Account_ID),

(Buyer_ID) REFERENCES Buyers (Buyer_ID),

(Employee_ID) REFERENCES Shop_Staff (Employee_ID)

);

CREATE TABLE Reteil

(

Reteil_ID NUMERIC NOT NULL,

Account_ID NUMERIC NOT NULL,

Reteil_count INTEGER,

Discount INTEGER,

Goods_ID NUMERIC NOT NULL,

Date_of_reteil DATE,

PRIMARY KEY (Reteil_ID),

(Account_ID) REFERENCES Accounts (Account_ID),

(Goods_ID) REFERENCES Goods (Goods_ID)

);



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

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

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

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

id_accounts - последовательность для суррогатного ключа таблицы Accounts

id_vendor - последовательность для суррогатного ключа таблицы Vendor

id_goods - последовательность для суррогатного ключа таблицы Goods

id_reteil - последовательность для суррогатного ключа таблицы Reteil

id_buyers - последовательность для суррогатного ключа таблицы Buyers

id_staff - последовательность для суррогатного ключа таблицы Shop_Staff

id_consignment - последовательность для суррогатного ключа таблицы Consignment

5.2 Триггеры

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

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

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

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

Название триггера	Соответствующая триггерная функция	Событие для срабатывания триггера	Описание
consigment_datе_check	cons_datе_check	До вставки или до изменения	Триггер для таблицы Сonsigment. Проверка даты поставки партии (она должна быть меньше или равна текущей)
goods_date_ check	goods_datecheck	До вставки или до изменения	Триггер для таблицы Goods. Проверка даты выхода игры (она должна быть меньше или равна текущей)
reteil_date_check	ret_date_check	До вставки или до изменения	Триггер для таблицы Reteil. Проверка даты продажи (она должна быть меньше или равна текущей)
goods_update_from_consig	goods_works	После вставки	Триггер для таблицы Goods. Автоматическое добавление в поле «Количество на складе» таблицы «Товары» количества товара, указанного при осуществлении поставки, а также заполнение поля «Цена последней поставки»
reteil_ dateSending	dateSending	После вставки	Триггер для таблицы Reteil. Автоматическая установка даты отправки товара (дата покупки + 5 дней)
reteil_update_ count	reteil_works	До вставки	Триггер для таблицы Reteil. При осуществлении продажи - автоматическое уменьшение числа товара на складе на количество проданного товара, а также вывод на экран ошибки в случае, когда количество на продажу больше, чем количество товара на складе
user_summ_ discount	user_sum_discount	После вставки	Триггер для таблицы Reteil. Подсчет накопительной скидки для покупателя при осуществлении новой покупки

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

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

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

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

Название	Описание задачи	Выходные параметры
Unclaimed_Goods	Вывод товаров, которые не продавались более месяца.	Name (Название товара), count_at_storehouse (количество товара на складе), date_of_last_reteil (дата последней продажи товара)



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

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

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

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

Название	Описание задачи	Входные параметры	Выходные параметры
Final_Price	Подсчет конечной цены продажи	id_a: INTEGER (Номер счета)	Summ: DOUBLE PRECISION (Конечная цена продажи)
OrdersHistory	Вывод истории заказов заданного клиента	b_id: INTEGER (Номер покупателя)	Name: VARCHAR (Название игры), BuyedCount: INTEGER (Количество купленного товара), DateOfBuy: DATE (Дата покупки)
TopBuyer	Поиск покупателя, купившего товара на наибольшую сумму	-	Lname: VARCHAR (Фамилия покупателя), Total: DOUBLE PRECISION (Общая сумма покупок)
PercentOfUsing Shiping	Вывод статистики используемых способов доставки в процентном соотношении	-	Type_of_shiping: VARCHAR (Способ доставки), Percentage: DOUBLE PRECISION (процет использования)
Profit	Подсчет общей прибыли магазина за заданный период	Begin_Date: DATE (Начальная дата подсчета прибыли) End_Date: DATE (Конечная дата подсчета прибыли)	Cost: DOUBLE PRECISION (Затраты), Proceeds: DOUBLE PRECISION (Выручка), Profits: DOUBLE PRECISION (Прибыль)
Salary	Подсчет зарплаты сотрудников	-	LastName: VARCHAR (Фамилия сотрудника), Post: VARCHAR (Должность), Salary: DOUBLE PRECISION (Оклад), Salary_Plus_Wage_ Rate: DOUBLE PRECISION (Оклад в сумме с процентом от продаж)
Search	Поиск дисков по названию/разработчику/издателю	whattofind: VARCHAR (фраза, по которой будет вестись поиск)	Id: INTEGER (Номер игры), Name: VARCHAR (Название игры), Developer: VARCHAR (Разработчик), Publisher: VARCHAR (Издатель), Price: DOUBLE PRECISION (Цена)
TopGood	Поиск самого популярного товара по результатам продаж	-	NameOfGood: VARCHAR (Название игры), SellingCount: INTEGER (Количество проданных копий)
Top_Seller	Поиск самого активного продавца по количеству оформленных счетов	-	Name: VARCHAR (Фамилия продавца), Selled: INTEGER (Количество оформленных счетов)



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

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

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

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

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

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

CREATE OR REPLACE VIEW "public"."Unclaimed_Goods" (

name,

count_at_storehouse,

date_of_last_reteil)

AS

SELECT g.name,

g.count_at_storehouse,

max(r.date_of_reteil) AS date_of_last_reteil

FROM goods g,

reteil r

WHERE g.goods_id = r.goods_id AND

(((SELECT max(r.date_of_reteil) AS max FROM reteil r WHERE g.goods_id =

r.goods_id)) + 30) <= now()

GROUP BY g.name,

g.count_at_storehouse;

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

6.2 Триггеры

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

CREATE TRIGGER "consigment_date_check" BEFORE INSERT OR UPDATE

ON "public"."consignment" FOR EACH ROW

EXECUTE PROCEDURE "public"."cons_date_check"();

CREATE OR REPLACE FUNCTION "public"."cons_date_check" () RETURNS trigger AS

$body$

BEGIN

If NEW.consignment_date>now() THEN

RAISE EXCEPTION 'Введенная дата больше текущей!';

end if;

RETURN new;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

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

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

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

CREATE TRIGGER "goods_update_from_consig" AFTER INSERT

ON "public"."consignment" FOR EACH ROW

EXECUTE PROCEDURE "public"."goods_works"();

CREATE OR REPLACE FUNCTION "public"."goods_works" () RETURNS trigger AS

$body$

BEGIN

UPDATE goods SET count_at_storehouse=count_at_storehouse+NEW.goods_count

FROM consignment

WHERE (goods.goods_id=NEW.goods_id);

UPDATE goods SET price_of_last_delivery=NEW.delivery_price

FROM consignment

WHERE (goods.goods_id=NEW.goods_id);

RETURN new;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

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

CREATE TRIGGER "reteil_dateSending" AFTER INSERT

ON "public"."reteil" FOR EACH ROW

EXECUTE PROCEDURE "public"."dateSending"();

CREATE OR REPLACE FUNCTION "public"."dateSending" () RETURNS trigger AS

$body$

BEGIN

UPDATE accounts SET date_of_sending=NEW.date_of_reteil+5

WHERE (accounts.account_id=NEW.account_id);

RETURN NEW;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

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

CREATE TRIGGER "reteil_update_count" BEFORE INSERT

ON "public"."reteil" FOR EACH ROW

EXECUTE PROCEDURE "public"."reteil_works"();

CREATE OR REPLACE FUNCTION "public"."reteil_works" () RETURNS trigger AS

$body$

DECLARE

cnt INTEGER;

BEGIN

SELECT into cnt g.count_at_storehouse FROM goods g WHERE (g.goods_id=NEW.goods_id);

IF NEW.reteil_count<=cnt THEN

UPDATE goods SET count_at_storehouse=count_at_storehouse-NEW.reteil_count

FROM reteil

WHERE (goods.goods_id=NEW.goods_id);

RETURN NEW;

END IF;

IF NEW.reteil_count>cnt THEN

RAISE EXCEPTION 'На складе нет такого количества данного товара на продажу!';

END IF;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

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

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

CREATE TRIGGER "user_summ_discount" AFTER INSERT

ON "public"."reteil" FOR EACH ROW

EXECUTE PROCEDURE "public"."user_sum_discount"();

CREATE OR REPLACE FUNCTION "public"."user_sum_discount" () RETURNS trigger AS

$body$

DECLARE

b_id INTEGER;

a_id INTEGER;

g_id INTEGER;

cnt INTEGER;

seb DOUBLE PRECISION;

summ DOUBLE PRECISION;

BEGIN

summ:=0;

SELECT INTO b_id a.buyer_id FROM accounts a WHERE a.account_id=NEW.account_id;

FOR a_id IN SELECT a.account_id FROM accounts a WHERE a.buyer_id=b_id LOOP

FOR g_id, cnt IN SELECT r.goods_id, r.reteil_count FROM reteil r WHERE (r.account_id=a_id) LOOP

SELECT INTO seb g.price_of_last_delivery FROM goods g WHERE (g.goods_id=g_id);

summ:=summ+seb*cnt;

END LOOP;

END LOOP;

UPDATE buyers SET summ_discount=0 WHERE (buyer_id=b_id);

IF summ>=1000 THEN

UPDATE buyers SET summ_discount=1 WHERE (buyer_id=b_id);

END IF;

IF summ>=5000 THEN

UPDATE buyers SET summ_discount=5 WHERE (buyer_id=b_id);

END IF;

IF summ>=10000 THEN

UPDATE buyers SET summ_discount=10 WHERE (buyer_id=b_id);

END IF;

RETURN NEW;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;



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

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

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

$body$

DECLARE

summ double precision; sdisc double precision;

apr INTEGER; id_g INTEGER;

disc INTEGER; seb double precision;

per INTEGER; cnt INTEGER;

sd INTEGER; tmp double precision;

BEGIN

summ:=0;

SELECT INTO sd b.summ_discount FROM buyers b, accounts a WHERE (b.buyer_id=a.buyer_id) and (a.account_id=id_a);

SELECT INTO sdisc, apr a.discount_on_shipping, a.price_of_shipping FROM accounts a WHERE (a.account_id=id_a);

FOR id_g, disc, cnt IN SELECT r.goods_id, r.discount, r.reteil_count FROM reteil r WHERE (r.account_id=id_a) LOOP

SELECT INTO seb, per g.price_of_last_delivery, g.percent_of_price_increasing FROM goods g WHERE (g.goods_id=id_g);

tmp:=(seb+ seb*per/100)*cnt;

summ:=summ + tmp - tmp*disc/100;

END LOOP;

summ:=summ-summ*sd/100;

summ:=summ+apr-apr*sdisc/100;

RETURN summ;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

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

модель данное триггер серверный

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

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

$body$

DECLARE

a_id INTEGER;

r_id INTEGER;

BEGIN

FOR a_id IN SELECT a.account_id FROM accounts a WHERE a.buyer_id="b_id" LOOP

FOR r_id,"BuyedCount", "DateOfBuy" IN SELECT r.goods_id, r.reteil_count, r.date_of_reteil FROM reteil r

WHERE r.account_id=a_id LOOP

SELECT INTO "Name" g.name FROM goods g WHERE g.goods_id=r_id;

RETURN NEXT;

END LOOP;

END LOOP;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

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

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

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

$body$

DECLARE

summ double precision; id_g INTEGER;

id_a INTEGER; id_b INTEGER; disc INTEGER;

seb double precision; per INTEGER;

cnt INTEGER; tmp double precision;

nname varchar;

BEGIN

"Total":=0;

FOR id_b, nname IN SELECT b.buyer_id, b.last_name FROM buyers b LOOP

summ:=0;

FOR id_a IN SELECT a.account_id FROM accounts a WHERE (a.buyer_id=id_b) LOOP

FOR id_g, disc, cnt IN SELECT r.goods_id, r.discount, r.reteil_count FROM reteil r WHERE (r.account_id=id_a) LOOP

SELECT INTO seb, per g.price_of_last_delivery, g.percent_of_price_increasing FROM goods g WHERE (g.goods_id=id_g);

tmp:=(seb+ seb*per/100)*cnt;

summ:=summ + tmp - tmp*disc/100;

END LOOP;

END LOOP;

if summ>"Total" then

"Total":=summ;

"Lname":=nname;

end if;

END LOOP;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

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

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

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

$body$

DECLARE

cnt1 INTEGER;

cnt2 INTEGER;

cnt3 INTEGER;

summ DOUBLE PRECISION;

BEGIN

SELECT INTO cnt1 COUNT(a.type_of_shipping) FROM accounts a WHERE (upper(a.type_of_shipping)=upper('Авиапочта'));

SELECT INTO cnt2 COUNT(a.type_of_shipping) FROM accounts a WHERE (upper(a.type_of_shipping)=upper('Наземная почта'));

SELECT INTO cnt3 COUNT(a.type_of_shipping) FROM accounts a WHERE (upper(a.type_of_shipping)=upper('Курьер'));

summ:=cnt1+cnt2+cnt3;

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

"Percentage":=100*cnt1/summ;

RETURN NEXT;

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

"Percentage":=100*cnt2/summ;

RETURN NEXT;

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

"Percentage":=100*cnt3/summ;

RETURN NEXT;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

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

5. 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$

DECLARE

summ double precision; id_g INTEGER;

id_a INTEGER; disc INTEGER;

seb double precision; per INTEGER;

cnt INTEGER; sd INTEGER;

tmp double precision; tmp2 double precision;

BEGIN

IF "Begin_Date"<"End_Date" THEN

"Proceeds":=0;

"Cost":=0;

FOR id_a IN SELECT a.account_id FROM accounts a LOOP

summ:=0;

tmp2:=0;

SELECT INTO sd b.summ_discount FROM buyers b, accounts a WHERE (b.buyer_id=a.buyer_id) and (a.account_id=id_a);

FOR 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") LOOP

SELECT INTO seb, per g.price_of_last_delivery, g.percent_of_price_increasing FROM goods g WHERE (g.goods_id=id_g);

tmp2:=tmp2 + seb*cnt;

tmp:=(seb+ seb*per/100)*cnt;

summ:=summ + tmp - tmp*disc/100;

END LOOP;

summ:=summ-summ*sd/100;

"Cost":="Cost"+tmp2;

"Proceeds":="Proceeds"+summ;

END LOOP;

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

RETURN NEXT;

ELSE

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

END IF;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

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

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

CREATE 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$

DECLARE

summ double precision; id_g INTEGER;

id_a INTEGER; id_ss INTEGER;

disc INTEGER; seb double precision;

per INTEGER; cnt INTEGER;

wr INTEGER; tmp double precision;

nname varchar;

BEGIN

FOR 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

summ:=0;

FOR id_a IN SELECT a.account_id FROM accounts a WHERE (a.employee_id=id_ss) LOOP

FOR id_g, disc, cnt IN SELECT r.goods_id, r.discount, r.reteil_count FROM reteil r WHERE (r.account_id=id_a) LOOP

SELECT INTO seb, per g.price_of_last_delivery, g.percent_of_price_increasing FROM goods g WHERE (g.goods_id=id_g);

summ:=summ + (seb+ seb*per/100)*cnt;

END LOOP;

END LOOP;

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

RETURN NEXT;

END LOOP;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

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



7. 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$

BEGIN

FOR "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

FROM 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) LOOP

RETURN NEXT;

END LOOP;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100 ROWS 1000;

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

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

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

$body$

DECLARE

g_id INTEGER;

gname VARCHAR;

cnt INTEGER;

cntSumm INTEGER;

BEGIN

"SellingCount":=0;

FOR g_id, gname IN SELECT g.goods_id, g.name FROM goods g LOOP

cntSumm:=0;

FOR cnt IN SELECT r.reteil_count FROM reteil r WHERE (r.goods_id=g_id) LOOP

cntSumm:=cntSumm+cnt;

END LOOP;

IF cntSumm>"SellingCount" THEN

"SellingCount":=cntSumm;

"NameOfGood":=gname;

END IF;

END LOOP;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

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

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

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

$body$

DECLARE

id_s INTEGER;

cnt INTEGER;

sname VARCHAR;

BEGIN

"Selled":=0;

FOR id_s, sname IN SELECT ss.employee_id, ss.last_name FROM shop_staff ss LOOP

SELECT INTO cnt COUNT(a.account_id) FROM accounts a WHERE (a.employee_id=id_s);

IF cnt>"Selled" THEN

"Selled":=cnt;

"Name":=sname;

END IF;

END LOOP;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

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



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

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

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

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

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

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

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