====== Projekt logiczny ======
Projekt logiczny zawiera w sobie również [[pl:dydaktyka:sbd:2009:projekty:produkcja:projekt_konceptualny|projekt konceptualny]].
===== -. Projektowanie tabel w oparciu o diagram ERD =====

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

=== Skrypt SQL tworzący baze do projektu: ===
  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
  );

===== -. Słownik danych =====

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:

^ id_rabatu   ^         opis         ^
|     1       | Upust jednostkowy    |
|     2       | Upust procentowy     |
|     3       | Upust kwotowy        |
Tablica 1: //tab_typy_rabatu//


^ id_statusu  ^         opis         ^
|     1       | Administrator              |
|     2       | Sprzedawca                 |
|     3       | Magazynier  ?              |
|     4       | Pracownik działu produkcji |
Tablica 2: //tab_typy_statusu//

===== -. Analiza zależności funkcyjnych i normalizacja tabel =====

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.

===== -. Denormalizacja struktury tabel =====

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

===== -. Zdefiniowanie kwerend dla realizacji funkcji wyspecyfikowanych w projekcie  =====

=== 1.1.3. Wyświetlanie spisu zamówień ===
  
  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)

=== 5.2. Podgląd statystyk (porównanie przychodów z produktów) ===
  
  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"

=== 5.1 Podgląd raportów (pracownik miesiąca) ===

  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

=== 1.4.2 Edycja danych kontrahenta ===

  * //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 =====
[[pl:dydaktyka:sbd:2009:projekty:produkcja:start|Powrót do strony projektu.]]