Projekt logiczny zawiera w sobie również projekt konceptualny.

Projektowanie tabel, kluczy, kluczy obcych, powiązań między tabelami, indeksów, etc. w oparciu o zdefiniowany diagram ERD zostało zawarte w podrozdziale ERD - diagramy związków encji w projekcie konceptualnym.

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
CREATE SEQUENCE tab_typy_statusu_id_statusu_seq
  START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;
CREATE TABLE tab_typy_statusu (
  id_statusu integer NOT NULL DEFAULT
    nextval('tab_typy_statusu_id_statusu_seq') PRIMARY KEY,
  opis character varying(50) NOT NULL
);

CREATE SEQUENCE tab_pracownicy_id_pracownika_seq
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;

CREATE TABLE tab_pracownicy (
  id_pracownika integer NOT NULL DEFAULT
    nextval('tab_pracownicy_id_pracownika_seq') PRIMARY KEY,
  imie character varying(25) NOT NULL,
  nazwisko character varying(50) NOT NULL,
  login character varying(25),
  password character varying(32),
  telefon integer NOT NULL CHECK (((telefon)::text ~* '[0-9]{9,}'::text)),
  email character varying(255) CHECK (((email) ~*
    '[a-zA-Z]{1}[a-zA-Z0-9.]{0,}@[a-z0-9A-Z]{1,}(.[a-z0-9A-Z]{1,}){1,}$')),
  status integer NOT NULL REFERENCES tab_typy_statusu(id_statusu) ON UPDATE RESTRICT
);

CREATE SEQUENCE tab_produkty_id_produktu_seq
  START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;
CREATE TABLE tab_produkty (
  id_produktu integer NOT NULL DEFAULT
    nextval('tab_produkty_id_produktu_seq') PRIMARY KEY,
  nazwa character varying(150) NOT NULL,
  cena_netto double precision NOT NULL,
  stawka_podatkowa smallint
);

CREATE SEQUENCE tab_kategoria_id_kategorii_seq
  START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;

CREATE TABLE tab_kategoria (
  id_kategorii integer NOT NULL DEFAULT
    nextval('tab_kategoria_id_kategorii_seq') PRIMARY KEY,
  nazwa character varying(200) NOT NULL,
  opis character varying(600)
);

CREATE TABLE tab_kategoria_produktu_pomocnicza (
  id_kategorii integer NOT NULL REFERENCES tab_kategoria(id_kategorii)
    ON UPDATE RESTRICT,
  id_produktu integer NOT NULL REFERENCES tab_produkty(id_produktu)
   ON UPDATE RESTRICT
);

CREATE SEQUENCE tab_kontrahenci_id_kontrahenta_seq
  INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;

CREATE TABLE tab_kontrahenci (
  id_kontrahenta integer NOT NULL DEFAULT
    nextval('tab_kontrahenci_id_kontrahenta_seq') PRIMARY KEY, 
  nazwa character varying(100) NOT NULL,
  nip character varying(10) NOT NULL CHECK (((nip) ~* '[0-9]{10}')),
  miasto character varying(40) NOT NULL,
  ulica character varying(40) NOT NULL,
  nr_domu character varying(10) NOT NULL,
  kod_pocztowy character varying(5) NOT NULL CHECK
  (((kod_pocztowy) ~* '[0-9]{5}'))
);

CREATE TABLE tab_email_kontrahenta (
  id_kontrahenta integer NOT NULL REFERENCES tab_kontrahenci(id_kontrahenta)
    ON UPDATE RESTRICT,
  email character varying(255) NOT NULL CHECK (((email) ~*
    '[a-zA-Z]{1}[a-zA-Z0-9.]{0,}@[a-z0-9A-Z]{1,}(.[a-z0-9A-Z]{1,}){1,}$')),
opis character varying(150)
);

CREATE SEQUENCE tab_magazyn_id_magazynowy_seq
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;

CREATE TABLE tab_magazyn (
  id_magazynowy integer NOT NULL DEFAULT
   nextval('tab_magazyn_id_magazynowy_seq') PRIMARY KEY,
  id_produktu integer NOT NULL REFERENCES tab_produkty(id_produktu)
  ON UPDATE RESTRICT,
  ilosc integer NOT NULL
);

CREATE TABLE tab_opis (
  id_produktu integer NOT NULL PRIMARY KEY REFERENCES tab_produkty(id_produktu)
    ON UPDATE RESTRICT,
  opis character varying(600)
);

CREATE TABLE tab_polozenia (
  id_magazynowy integer NOT NULL PRIMARY KEY REFERENCES tab_magazyn(id_magazynowy)
    ON UPDATE RESTRICT,
  polozenie character varying(150) NOT NULL
);

CREATE SEQUENCE tab_przedstawiciele_kontrahenta_id_przedstawiciela_seq
  INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;

CREATE TABLE tab_przedstawiciele_kontrahenta (
  id_przedstawiciela integer NOT NULL PRIMARY KEY DEFAULT
    nextval('tab_przedstawiciele_kontrahenta_id_przedstawiciela_seq'),
  id_kontrahenta integer NOT NULL REFERENCES tab_kontrahenci(id_kontrahenta)
    ON DELETE RESTRICT,
  imie character varying(25) NOT NULL,
  nazwisko character varying(50) NOT NULL,
  telefon integer NOT NULL CHECK
    (((telefon)::text ~* '[0-9]{9,}'::text)),
  email character varying(255) CHECK (((email) ~*
    '[a-zA-Z]{1}[a-zA-Z0-9.]{0,}@[a-z0-9A-Z]{1,}(.[a-z0-9A-Z]{1,}){1,}$')),
  opis character varying(600)
  );

CREATE TABLE tab_zamowienie (
  id_zamowienia integer NOT NULL PRIMARY KEY,
  id_kontrahenta integer NOT NULL REFERENCES tab_kontrahenci(id_kontrahenta)
    ON UPDATE RESTRICT,
  data_zamowienia timestamp without time zone NOT NULL,
  data_realizacji timestamp without time zone NOT NULL,
  id_pracownika integer NOT NULL REFERENCES tab_pracownicy(id_pracownika)
    ON UPDATE RESTRICT,
  id_przedstawiciela integer NOT NULL REFERENCES
  tab_przedstawiciele_kontrahenta(id_przedstawiciela) ON UPDATE RESTRICT
   );

CREATE SEQUENCE tab_spis_zamowien_id_szczegolowego_zamowienia_seq
  START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;

CREATE TABLE tab_spis_zamowien (
  id_szczegolowego_zamowienia integer NOT NULL PRIMARY KEY DEFAULT
    nextval('tab_spis_zamowien_id_szczegolowego_zamowienia_seq'),
  id_zamowienia integer NOT NULL REFERENCES tab_zamowienie(id_zamowienia)
    ON UPDATE RESTRICT,
  id_produktu integer NOT NULL REFERENCES tab_produkty(id_produktu)
    ON UPDATE RESTRICT,
  ilosc integer NOT NULL
);

CREATE SEQUENCE tab_typy_rabatu_id_rabatu_seq
  START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;

CREATE TABLE tab_typy_rabatu (
  id_rabatu integer NOT NULL
    DEFAULT nextval('tab_typy_rabatu_id_rabatu_seq') PRIMARY KEY,
  opis character varying(50) NOT NULL
);

CREATE TABLE tab_rabat (
  id_szczegolowego_zamowienia integer NOT NULL PRIMARY KEY REFERENCES
  tab_spis_zamowien(id_szczegolowego_zamowienia) ON UPDATE RESTRICT,
  wartosc_upustu integer NOT NULL,
  typ integer NOT NULL REFERENCES tab_typy_rabatu(id_rabatu) ON UPDATE RESTRICT
);

CREATE TABLE tab_produkcja (
  id_szczegolowego_zamowienia integer NOT NULL PRIMARY KEY
    REFERENCES tab_spis_zamowien(id_szczegolowego_zamowienia)
    ON UPDATE RESTRICT,
  status character varying(30) NOT NULL
);

CREATE TABLE tab_magazyn_rezerwacje (
  id_magazynowy integer NOT NULL REFERENCES tab_magazyn(id_magazynowy)
    ON UPDATE RESTRICT,
  id_szczegolowego_zamowienia integer NOT NULL
    REFERENCES tab_spis_zamowien(id_szczegolowego_zamowienia) ON UPDATE RESTRICT
);

CREATE TABLE tab_telefon_kontrahenta (
  id_kontrahenta integer NOT NULL REFERENCES tab_kontrahenci(id_kontrahenta)
    ON UPDATE RESTRICT,
  telefon integer NOT NULL CHECK (((telefon)::text ~* '[0-9]{9,}'::text)),
  opis character varying(150),
);

CREATE TABLE tab_uwagi (
  id_zamowienia integer NOT NULL PRIMARY KEY,
  uwagi character varying(400) NOT NULL
);

CREATE TABLE tab_zamowienia_archiwum (
  id_zamowienia integer NOT NULL PRIMARY KEY,
  id_pracownika integer NOT NULL REFERENCES tab_pracownicy(id_pracownika)
    ON UPDATE RESTRICT,
  id_przedstawiciela integer NOT NULL REFERENCES
  tab_przedstawiciele_kontrahenta(id_przedstawiciela) ON UPDATE RESTRICT,
  data_zamowienia timestamp without time zone NOT NULL
);

CREATE TABLE tab_data_wyslania (
  id_zamowienia integer NOT NULL PRIMARY KEY
    REFERENCES tab_zamowienia_archiwum(id_zamowienia) ON UPDATE RESTRICT,
  id_produktu integer NOT NULL REFERENCES tab_produkty(id_produktu)
    ON UPDATE RESTRICT,
  ilosc integer NOT NULL,
  cena double precision NOT NULL,
  data_wyslania timestamp without time zone NOT NULL
);

CREATE SEQUENCE tab_faktury_vat_id_faktury_seq
  START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;

CREATE TABLE tab_faktury_vat (
  id_faktury integer NOT NULL DEFAULT nextval('tab_faktury_vat_id_faktury_seq')
    PRIMARY KEY,
  id_zamowienia integer NOT NULL
    REFERENCES tab_zamowienia_archiwum(id_zamowienia) ON UPDATE RESTRICT,
  data_wystawienia_faktury timestamp without time zone NOT NULL
);

W projekcie użyto dwóch słowników danych: tab_typy_statusu, określający status pracownika oraz tab_typy_rabatu, określający rodzaj przyznanego rabatu. Przykład wypełnienia słowników prezentują tabele 1 i 2:

Tablica 1: tab_typy_rabatu

Tablica 2: tab_typy_statusu

Tabele spełniaja założenia normalizacji. Wątpliwości budzi tab_produkcja. Wydawać by się mogło, że należałoby utworzyć dodatkowy słownik danych np. tab_postep_produkcji, jednak po głębszej analizie dochodzimy do wniosku, że postępy produkcji są atrybutami, których przestrzeń jest zbyt duża, aby wszystkie zapamiętać w tabeli. Za pewne, często wystąpi atrybut „nie rozpoczęto”, „w trakcie” albo „zrealizowano”, jednakże zachodzi potrzeba użycia atrybutów innych, np. „odłożono do przyszłego tygodnia”, „przerwa z powodu braku materiałów”, itp.

W celu optymalizacji przechowywania danych, wydzielono tabele:

• tab_uwagi,
• tab_opis,
• tab_polozenia,
• tab_produkcja.

oraz tabele przeznaczone stricte na archiwum danych:

• tab_zamowienia_archiwum,
• tab_faktury_vat,
• tab_data_wyslania.

select k.nazwa, z.data_zamowienia, pk.nazwisko || ' ' || pk.imie,
       p.nazwisko || ' ' || p.imie, z.id_zamowienia
from tab_kontrahenci as k, tab_zamowienie as z,
     tab_przedstawiciele_kontrahenta as pk, tab_pracownicy as p
where z.id_kontrahenta = k.id_kontrahenta and
      pk.id_przedstawiciela = z.id_przedstawiciela and
      p.id_pracownika = z.id_pracownika and
      z.id_zamowienia not in (select id_zamowienia from tab_zamowienia_archiwum)

select p.nazwa, sum(sz.ilosc*p.cena_netto)
from tab_spis_zamowien sz inner join tab_produkty p on p.id_produktu = sz.id_produktu
where (select z.data_zamowienia from tab_zamowienie z
      where sz.id_zamowienia = z.id_zamowienia)
   between ? and ?
group by p.nazwa"

select p.imie || ' ' || p.nazwisko as nazw, count(z.id_pracownika) as cnt
from tab_zamowienie z inner join tab_pracownicy p on p.id_pracownika = z.id_pracownika
where data_zamowienia BETWEEN ? AND ?
group by nazw
order by cnt desc

• zmiana danych przedstawiciela kontrahenta:

  select imie, nazwisko, telefon, email, opis
  from tab_przedstawiciele_kontrahenta
  where id_kontrahenta =
    (select id_kontrahenta from tab_kontrahenci where nazwa = 'nowy');

• przetwarzania danych kontrahenta:

  update tab_przedstawiciele_kontrahenta set (imie, nazwisko, telefon, email, opis) =
    ('kazek', 'nowy','1234567890', 'asd@asd.pl','asdfg')
  where imie = 'Dwa' and id_kontrahenta =
    (select id_kontrahenta from tab_kontrahenci where nazwa = 'nowy')

• zmiana adresu e-mail kontrahenta:

  select email, opis
  from tab_email_kontrahenta
  where id_kontrahenta =
    (select id_kontrahenta from tab_kontrahenci where nazwa = 'nowy');

• przetwarzania danych kontrahenta:

  update tab_email_kontrahenta set ( email, opis) = ( 'asd@asd.pl','asdfg')
  where email = 'nowy1@email.pl' and id_kontrahenta =
    (select id_kontrahenta from tab_kontrahenci where nazwa = 'nowy')

• zmiana nr telefonu kontrahenta:

  select telefon, opis
  from tab_telefon_kontrahenta
  where id_kontrahenta =
  (select id_kontrahenta from tab_kontrahenci where nazwa = 'nowy');

• przetwarzania danych kontrahenta:

  update tab_telefon_kontrahenta set ( telefon, opis) = ( '1234567890','asdfg')
  where telefon = '100100100' and id_kontrahenta =
    (select id_kontrahenta from tab_kontrahenci where nazwa = 'nowy')

• zmiana danych kontrahenta:

  select nazwa, nip, miasto, ulica, nr_domu, kod_pocztowy
  from tab_kontrahenci
  where nazwa = 'kont1'

• przetwarzania danych kontrahenta:

  update tab_kontrahenci set (nazwa, nip, miasto, ulica, nr_domu, kod_pocztowy) =
    ('nowy', '1234567890','krak', 'dluga','123a','01234')
  where nazwa = 'kont1

Powrót do strony projektu.