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

 

 

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;

 

 

 

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

 

 

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;

 

 

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

 

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

• Регистрация покупателей (для новых пользователей)
• Редактирование персональных данных покупателя (для покупателей)
• Поиск игр по названию/издателю/разработчику (для всех пользователей)
• Осуществление покупок (для покупателей)
• Добавление новых товаров (для продавцов и администратора)
• Вывод на экран истории заказов (для покупателей)
• Вывод на экран зарплаты сотрудников (для администратора)
• Вывод на экран невостребованных больше месяца товаров (для продавцов и администратора)