Subversion Repositories public

Rev

Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed

/*
Created         13.10.2004
Modified        02.04.2007
Project         TheoPlan
Model           pm
Company         TheoSys
Author          Andreas Theofilu
Version         2.6
Database        PostgreSQL 7.4
*/


/* Create Schemas */



/* Create Tables */


Create table "public"."allocation"
(
        "al_num" Int8 Not Null,
        "al_task" Integer Not Null,
        "al_ressource" Integer Not Null,
        "al_hours" Double precision,
        "al_role" Integer,
        "al_phase" Integer,
        "al_pstart" Integer,
        "al_pduration" Smallint,
constraint "allocation_pkey" primary key ("al_num")
)
WITHOUT OIDS;

Create table "public"."counter"
(
        "co_mitarbeiter" Int8,
        "co_plan" Int8,
        "co_task" Int8,
        "co_wdone" Int8,
        "co_allocation" Int8,
        "co_ptemp" Int8,
        "co_tempidx" Int8,
        "co_charge" Int8,
        "co_kgroup" Int8,
        "co_kunde" Int8,
        "co_histanteil" Int8,
        "co_calendar" Int8,
        "co_ejournal" Int8,
        "co_abschluss" Int8,
        "co_planstatus" Int8,
        "co_dration" Int8,
        "co_tagsatz" Int8,
        "co_kgsammel" Int8,
        "co_fixanteil" Int8,
        "co_matopr" Int8,
        "version" Varchar(10)
)
WITHOUT OIDS;

Create table "public"."wdone"
(
        "wd_num" Int8 Not Null,
        "wd_prnum" Int8 Not Null,
        "wd_minum" Int8 Not Null,
        "wd_datum" Int8,
        "wd_hours" Double precision,
        "wd_task" Int8 Not Null,
        "wd_rk" Smallint,
        "wd_phase" Integer,
        "wd_edit" Boolean,
        "wd_text" Varchar(8192),
        "wd_verr" Boolean,
constraint "wdone_pkey" primary key ("wd_num")
)
WITHOUT OIDS;

Create table "public"."key_sgf"
(
        "ks_num" Int8 Not Null,
        "ks_sgf" Varchar(80),
constraint "key_sgf_pkey" primary key ("ks_num")
)
WITHOUT OIDS;

Create table "public"."key_abt"
(
        "ka_num" Int8 Not Null,
        "ka_abt" Varchar(50),
constraint "key_abt_pkey" primary key ("ka_num")
)WITHOUT OIDS;

Create table "public"."key_role"
(
        "kr_num" Int8 Not Null,
        "kr_role" Varchar(50),
constraint "key_role_pkey" primary key ("kr_num")
)
WITHOUT OIDS;

Create table "public"."key_tmgroup"
(
        "kt_num" Int8 Not Null,
        "kt_tm" Varchar(80),
constraint "key_tmgroup_pkey" primary key ("kt_num")
)
WITHOUT OIDS;

Create table "public"."key_land"
(
        "kl_num" Int8 Not Null,
        "kl_land" Varchar(50),
constraint "key_land_pkey" primary key ("kl_num")
)
WITHOUT OIDS;

Create table "public"."task"
(
        "ta_num" Int8 Not Null,
        "ta_plnum" Int8 Not Null,
        "ta_id" Smallint Not Null,
        "ta_depend" Smallint,
        "ta_deptype" Smallint,
        "ta_name" Varchar(50) Not Null,
        "ta_color" Integer,
        "ta_shape" Varchar(40),
        "ta_meeting" Smallint,
        "ta_start" Int8,
        "ta_duration" Smallint,
        "ta_complete" Smallint,
        "ta_fixed_start" Smallint,
        "ta_priority" Smallint,
        "ta_level" Smallint,
        "ta_notiz" Varchar(4096),
        "ta_phase" Integer,
        "ta_risiko" Smallint,
        "ta_hash" Char(34) Not Null,
constraint "task_pkey" primary key ("ta_num")
)
WITHOUT OIDS;

Create table "public"."ptemp"
(
        "pt_num" Int8 Not Null,
        "pt_tnum" Int8 Not Null,
        "pt_lfd" Smallint Not Null,
        "pt_level" Smallint,
        "pt_name" Varchar(50),
        "pt_color" Integer,
        "pt_meeting" Boolean,
        "pt_phase" Integer,
constraint "ptemp_pkey" primary key ("pt_num")
)
WITHOUT OIDS;

Create table "public"."project"
(
        "pr_num" Int8 Not Null,
        "pr_name" Varchar(100) Not Null Constraint "idx_project_name" UNIQUE,
        "pr_pl" Int8 Not Null,
        "pr_plav" Int8 Not Null,
        "pr_sgf" Int8 Not Null,
        "pr_tmgroup" Int8 Not Null,
        "pr_status" Smallint Not Null,
constraint "project_pkey" primary key ("pr_num")
)
WITHOUT OIDS;

Create table "public"."mitarbeiter"
(
        "mi_num" Int8 Not Null,
        "mi_vname" Varchar(50) Not Null,
        "mi_nname" Varchar(50) Not Null,
        "mi_land" Int8 Not Null,
        "mi_abt" Int8 Not Null,
        "mi_tel" Varchar(50),
        "mi_mail" Varchar(80),
        "mi_userid" Varchar(16) Constraint "Rel_mi_userid" UNIQUE,
        "mi_passwd" Varchar(50),
        "mi_rstufe" Smallint,
        "mi_hacker" Smallint,
        "mi_plan" Bool,
constraint "mitarbeiter_pkey" primary key ("mi_num")
)
WITHOUT OIDS;

CREATE TABLE "public"."key_whg"
(
   wh_num int4 NOT NULL, 
   wh_whg char(5) NOT NULL, 
   wh_bez varchar(60), 
   CONSTRAINT pk_whg PRIMARY KEY (wh_num)
) WITHOUT OIDS;

Create table "public"."tagsatz"
(
        "tg_num" int8 NOT NULL, -- Interne laufende Nummer
        "tg_valid_from" date NOT NULL, -- Datum gueltig ab
        "tg_valid_to" date, -- Datum gueltig bis
        "tg_amount" float8, -- Tagsatz
        "tg_nightr" float8, -- Naechtigungspauschale
        "tg_ration" float8, -- Tagesdiaeten
        "tg_kmcar" float8, -- Kilometerpauschale PKW
        "tg_kmdrain" float8, -- Kilometerpauschale Zug
        "tg_kmplain" float8, -- Kilometerpauschale Flugzeug
        "tg_whg" int8 NOT NULL, -- Verknuepfung zur Waehrung
        "tg_minum" int8 NOT NULL, -- Verknuepfung zum Mitarbeiter
        CONSTRAINT "pk_tgnum" PRIMARY KEY ("tg_num"),
        CONSTRAINT "fk_minum_10" FOREIGN KEY ("tg_minum")
            REFERENCES "public"."mitarbeiter" ("mi_num") MATCH SIMPLE
            ON UPDATE RESTRICT ON DELETE RESTRICT,
        CONSTRAINT "fk_whg_10" FOREIGN KEY ("tg_whg")
        REFERENCES "public"."key_whg" ("wh_num") MATCH SIMPLE
            ON UPDATE RESTRICT ON DELETE RESTRICT
) 
WITHOUT OIDS;

Create table "public"."key_phase"
(
        "kp_num" Int8 Not Null,
        "kp_phase" Varchar(60) Not Null,
constraint "key_phase_pkey" primary key ("kp_num")
)
WITHOUT OIDS;

Create table "public"."plan"
(
        "pl_num" Int8 Not Null,
        "pl_lfd" Integer Not Null,
        "pl_prnum" Int8 Not Null,
        "pl_status" Smallint Not Null,
        "pl_comment" Varchar(255),
        "pl_date" Timestamp Default current_timestamp,
constraint "plan_pkey" primary key ("pl_num")
)
WITHOUT OIDS;

CREATE TABLE "public"."planstatus"
(
        "ps_num" int8 NOT NULL,
        "ps_pl" int8 NOT NULL,
        "ps_prnum" int8 NOT NULL,
        "ps_plnum" int8 NOT NULL,
        "ps_status" bool NOT NULL DEFAULT true,
        "ps_comment" varchar(8192),
        "ps_datum" date,
        "ps_lfd" smallint,
        CONSTRAINT "psnum_pkey" PRIMARY KEY ("ps_num"),
        CONSTRAINT "pspl_fk" FOREIGN KEY ("ps_pl")
                REFERENCES mitarbeiter ("mi_num") MATCH SIMPLE
                ON UPDATE RESTRICT ON DELETE RESTRICT,
        CONSTRAINT "psplnum_fk" FOREIGN KEY ("ps_plnum")
                REFERENCES plan ("pl_num") MATCH SIMPLE
                ON UPDATE RESTRICT ON DELETE RESTRICT,
        CONSTRAINT "psprnum_fk" FOREIGN KEY ("ps_prnum")
                REFERENCES project ("pr_num") MATCH SIMPLE
                ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITHOUT OIDS;

Create table "public"."abschluss"
(
        "ab_num" int8 Not Null,
        "ab_datum" Int8 Not Null,
        "ab_user" Int8 Not Null,
        "ab_prnum" Int8 Not Null,
        "ab_plnum" Int8 Not Null,
constraint "abschluss_pkey" primary key ("ab_num")
)
WITHOUT OIDS;

Create table "public"."tempidx"
(
        "te_num" Int8 Not Null UNIQUE,
        "te_name" Varchar(60) Not Null,
        "te_zweck" Varchar(2048),
constraint "pk_tempidx" primary key ("te_num")
)
WITHOUT OIDS;

Create table "public"."dration"
(
        "dr_num" Int8 Not Null UNIQUE,
        "dr_date_start" Timestamp,      -- Startdatum und Zeit der Reise.
        "dr_date_end" Timestamp,        -- Endedatum und Zeit der Reise.
        "dr_target" varchar(60),        -- Zielort / Land
        "dr_purpose" varchar(60),       -- Zweck der Reise
        "dr_pnightr" bool,              -- True = Naechtigungspauschale, False = Sonderbetrag
        "dr_nightr" Double precision,   -- Sonderbetrag Naechtigung(en)
        "dr_amount" Double precision,   -- Spesen
        "dr_kmcar" Smallint,            -- Gefahrene Kilometer
        "dr_kfzkenn" char(16),          -- KFZ Kennzeichen
        "dr_kmdrain" Smallint,          -- Gefahrene Kilometer
        "dr_pkmplain" bool,             -- True = Flugpauschale, False = Einzelpreis
        "dr_kmplain" Double precision,  -- Angefallene Flugkosten
        "dr_wdnum" Int8 Not Null,       -- Verknuepfung zu IST-Eintrag
        "dr_whg" Int8 Not Null,         -- Waehrung
        "dr_remark" varchar(4096),      -- Bemerkung / Kommentar
        "dr_costloc" Int8 Not Null,     -- Kostenstelle
        "dr_permit" Bool,               -- True = Abrechnung ist genehmigt
        "dr_minum" Int8,                -- Abrechnung wurde genehmigt durch <controller X>
constraint "pk_dration" primary key ("dr_num")
)
WITHOUT OIDS;

Create table "public"."charge"
(
        "ch_num" Int8 Not Null UNIQUE,
        "ch_date" Date Not Null,
        "ch_amount" Double precision Not Null,
        "ch_category" Int8 Not Null,
        "ch_costloc" Int8 Not Null,
        "ch_prnum" Int8 Not Null,
        "ch_whg" Int8 Not Null,
constraint "pk_charge" primary key ("ch_num")
)
WITHOUT OIDS;

Create table "public"."key_category"
(
        "kc_num" Int8 Not Null UNIQUE,
        "kc_category" Varchar(60),
constraint "pk_key_category" primary key ("kc_num")
)
WITHOUT OIDS;

Create table "public"."key_costloc"
(
        "kc_num" Int8 Not Null UNIQUE,
        "kc_costloc" Varchar(60),
constraint "pk_key_costloc" primary key ("kc_num")
)WITHOUT OIDS;

Create table "public"."kunde"
(
        "ku_num" Int8 Not Null UNIQUE,
        "ku_firma" Varchar(80),
        "ku_anrede" Integer Not Null,
        "ku_vname" Varchar(60),
        "ku_nname" Varchar(60),
        "ku_strasse" Varchar(60),
        "ku_hnum" Varchar(10),
        "ku_ort" Varchar(60),
        "ku_plz" Varchar(16),
        "ku_costloc" Int8 Not Null,
constraint "pk_kunde" primary key ("ku_num")
)
WITHOUT OIDS;

Create table "public"."key_anrede"
(
        "ka_num" Int8 Not Null UNIQUE,
        "ka_anrede" Varchar(30),
constraint "pk_key_anrede" primary key ("ka_num")
)
WITHOUT OIDS;

Create table "public"."kgroup"
(
        "kg_num" Int8 Not Null UNIQUE,
        "kg_name" Varchar(60) Not Null,
        "kg_prnum" Int8,
constraint "pk_kgroup" primary key ("kg_num")
)
WITHOUT OIDS;

Create table "public"."kgsammel"
(
        "ks_num" Int8 Not Null UNIQUE,
        "ks_kgnum" Int8 Not Null,
        "ks_kunum" Int8 Not Null,
constraint "pk_kgsammel" primary key ("ks_num")
)
WITHOUT OIDS;

Create table "public"."histanteil"
(
        "hi_num" Int8 Not Null UNIQUE,
        "hi_valid_from" Date Not Null,
        "hi_valid_to" Date,
        "hi_ksnum" Int8 Not Null,
        "hi_proz" Float8 Not Null,
constraint "pk_histanteil" primary key ("hi_num")
)
WITHOUT OIDS;

Create table "public"."fixanteil"
(
        "fx_num" Int8 Not Null UNIQUE,
        "fx_valid_from" Date Not Null,
        "fx_valid_to" Date,
        "fx_kunum" Int8 Not Null,
        "fx_proz" Float8 Not Null,
constraint "pk_fixanteil" primary key ("fx_num")
)
WITHOUT OIDS;

Create table "public"."calendar"
(
        "ca_num" Int8 Not Null UNIQUE,
        "ca_prnum" Int8,
        "ca_minum" Int8,
        "ca_task" Int8,
        "ca_writer" Int8,
        "ca_date" Int8 Not Null,
        "ca_end" Int8,
        "ca_repeat" smallint,
        "ca_repanz" smallint,
        "ca_title" varchar(60),
        "ca_text" Varchar(4096),
        "ca_status" smallint,
        "ca_priority" smallint,
constraint "pk_calendar" primary key ("ca_num")
)
WITHOUT OIDS;

Create table "public"."ejournal"
(
        "ej_num" Int8 Not Null UNIQUE,
        "ej_date" Int8,
        "ej_uid" Int8,
        "ej_type" Smallint,
        "ej_text" Varchar(4096),
constraint "pk_ejournal" primary key ("ej_num")
)
WITHOUT OIDS;

Create table "public"."key_jtype"
(
        "kj_num" Int8 Not Null UNIQUE,
        "kj_text" Varchar(50),
constraint "pk_key_jtype" primary key ("kj_num")
)
WITHOUT OIDS;

CREATE TABLE "language"
(
  la_num int8 NOT NULL, -- Interne laufende Nummer
  la_tnum int4 NOT NULL, -- Manuell vergebene Nummer des Textes
  la_lang char(4) NOT NULL, -- ISO Sprachkuerzel
  la_text varchar(2048) NOT NULL, -- Beliebiger Text
  CONSTRAINT pk_lanum PRIMARY KEY (la_num)
)
WITHOUT OIDS;

CREATE TABLE matopr
(
  ma_num int8 NOT NULL, -- Laufende Nummer
  ma_minum int8 NOT NULL, -- Mitarbeiternummer
  ma_prnum int8 NOT NULL, -- Projektnummer
  ma_role int8, -- Rolle im Projekt
  CONSTRAINT pk_manum PRIMARY KEY (ma_num),
  CONSTRAINT fk_minum FOREIGN KEY (ma_minum)
    REFERENCES mitarbeiter (mi_num) MATCH SIMPLE
    ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT fk_prnum FOREIGN KEY (ma_prnum)
    REFERENCES project (pr_num) MATCH SIMPLE
    ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT fk_role FOREIGN KEY (ma_role)
    REFERENCES key_role (kr_num) MATCH SIMPLE
    ON UPDATE RESTRICT ON DELETE RESTRICT
) 
WITHOUT OIDS;

/* Create Comment on Tables */
Comment on table "public"."allocation" is 'Enthaelt Mitarbeiter die zu einem bestimmten Task verknuepft sind';
Comment on table "public"."counter" is 'Beinhaltet Zaehlervariablen fuer die automatischen Zaehler einiger Tabellen und die Versionsnummer des Programms fuer das die Datenbank erzeugt wurde';
Comment on table "public"."wdone" is 'Erfassung der geleisteten Arbeitszeit';
Comment on table "public"."key_sgf" is 'Definiert die Strategischen Geschaeftsfuehrer, sofern es solche gibt.';
Comment on table "public"."key_abt" is 'Definiert individuelle Abteilungen. Jeder Mitarbeiter muss einer der definierten Abteilungen angehoeren.';
Comment on table "public"."key_role" is 'Definiert einen individuellen Schluessel fuer die Rolle eines Mitarbeiters innerhalb eines Projekts';
Comment on table "public"."key_tmgroup" is 'Definiert individuelle Themenmanagergruppen, mit deren Hilfe die Projekte in Themengruppen gebuendelt werden koennen.';
Comment on table "public"."key_land" is 'Definiert einen individuellen Laenderschluessel';
Comment on table "public"."ptemp" is 'Beinhaltet Templates zur Erzeugung eines Plans';
Comment on table "public"."project" is 'Beinhaltet die Grunddefinition eines Projekts.';
Comment on table "public"."mitarbeiter" is 'Enthaelt die Daten eines Mitarbeiters';
Comment on table "public"."key_phase" is 'Definiert einen individuellen Schluessel fuer die einzelnen Phasen eines Projekts';
Comment on table "public"."plan" is 'Beinhaltet die Grunddaten eines Projektplans';
Comment on table "public"."dration" is 'Enthaelt die Betraege in Form einer Historie, welche den Mitarbeitern verrechnet werden, wenn Reisekosten anfallen.';
Comment on table "public"."charge" is 'Ordnet einem Projekt finanzielle Aufschlaege und/oder Belastungen zu, welche keine Standardkosten wie Reisekosten sind.';
Comment on table "public"."key_category" is 'Definiert Kategorien in die angefallene Aufwaende eingeteilt werden koennen';
Comment on table "public"."key_costloc" is 'Definiert Kostenstellen zu denen ein Kunde (und Projekt) zugeordnet werden kann.';
Comment on table "public"."kunde" is 'Enthaelt die Daten von externen Kunden, welche die Auftraggeber sind';
Comment on table "public"."key_anrede" is 'Definiert Schluessel die eine Anredeform definieren';
Comment on table "public"."kgroup" is 'Definiert eine Gruppe von Kunden. Alle Gruppenmitglieder koennen einen prozentuellen Anteil an der Umsetzung eines Projekts haben, was sich auf die Erstellung der Rechnung entsprechend auswirkt.';
Comment on table "public"."histanteil" is 'Fasst mehrere Anteile zusammen. Dadurch kann eine historie gebildet werden. Das ist notwendig da sich die Anteilsverhaeltnisse jederzeit aendern koennen und eine Historie benoetigt wird um die Projektkosten korrekt ermitteln zu koennen.';
Comment on table "public"."calendar" is 'Enthaelt die Kalendereintraege von Tasks und Ereignissen fuer jeden einzelnen Mitarbeiter';
Comment on table "public"."ejournal" is 'Enthaelt ein elektrisches Journal ueber die Vorgaenge innerhalb der Applikation';
Comment on table "public"."key_jtype" is 'Schluesseltabelle der Journaltypen (loeschen, aendern, etc)';
Comment on table "public"."abschluss" is 'Enthaelt die Datum an denen in den einzelnen Projekten bestimmte Zeitbereiche als Abgeschlossen markiert wurden. In diesen Zeitbereichen kann keine IST-Erfassung erfolgen.';
Comment on table "public"."planstatus" IS 'Enthaelt den Statusbericht zu einem Plan.';
Comment on table "public"."kgsammel" is 'Dient als Kontainer fuer mehrere Kunden.';
COMMENT ON TABLE "public"."key_whg" IS 'Waehrungsschluessel';
COMMENT ON TABLE "public"."tagsatz" IS 'Beinhaltet die Tagsaetze zu jedem einzelnen Mitarbeiter';
COMMENT ON TABLE "public"."language" IS 'Beinhaltet alle Textausgaben in verschiedenen Sprachen';
COMMENT ON TABLE "public"."matopr" IS 'Mitarbeiter zu Projekt; Ordnet einen oder mehrere Mitarbeiter einem Projekt zu und definiert deren Rolle im Projekt.';

/* Create Comment on Columns */
Comment on column "public"."allocation"."al_num" is 'Automatisch generierte Nummer';
Comment on column "public"."allocation"."al_task" is 'Task zu dem der Mitarbeiter verknuepft ist; Verknuepft mit task';
Comment on column "public"."allocation"."al_ressource" is 'Mitarbeiter; Verknuepft mit mitarbeiter';
Comment on column "public"."allocation"."al_hours" is 'Anzahl geplante Einheiten';
Comment on column "public"."allocation"."al_role" is 'Rolle des Mitarbeiters im Projekt; Verknuepft zu key_role';
Comment on column "public"."allocation"."al_phase" is 'Phase; Verknuepft zu key_phase';
Comment on column "public"."allocation"."al_pstart" is 'Startdatum (Periode) fuer die die geplante Zeit gilt';
Comment on column "public"."allocation"."al_pduration" is 'Dauer der Periode zum Planungszeitpunkt (nicht in Verwendung!)';
Comment on column "public"."counter"."co_mitarbeiter" is 'Zaehler fuer Tabelle mitarbeiter';
Comment on column "public"."counter"."co_plan" is 'Zaehler fuer Tabelle plan';
Comment on column "public"."counter"."co_task" is 'Zaehler fuer Tabelle task';
Comment on column "public"."counter"."co_wdone" is 'Zaehler fuer Tabelle wdone';
Comment on column "public"."counter"."co_allocation" is 'Zaehler fuer Tabelle allocation';
Comment on column "public"."counter"."co_ptemp" is 'Zaehler fuer Tabelle ptemp';
Comment on column "public"."counter"."co_tempidx" is 'Zaehler fuer Tabelle tempidx';
Comment on column "public"."counter"."co_charge" is 'Zaehler fuer Tabelle charge';
Comment on column "public"."counter"."co_kgroup" is 'Zaehler fuer Tabelle kgroup';
Comment on column "public"."counter"."co_kunde" is 'Zaehler fuer Tabelle kunde';
Comment on column "public"."counter"."co_histanteil" is 'Zaehler fuer Tabelle histanteil';
Comment on column "public"."counter"."version" is 'Versionsnummer des Programms und damit auch der Datenbank. Beide Versionsnummern muessen zusammenpassen, damit das Programm korrekt funktioniert.';
Comment on column "public"."counter"."co_calendar" is 'Zaehler fuer Tabelle calendar';
Comment on column "public"."counter"."co_ejournal" is 'Zaehler fuer Tabelle ejournal';
Comment on column "public"."wdone"."wd_num" is 'Automatisch generierte Nummer';
Comment on column "public"."wdone"."wd_prnum" is 'Projektnummer; Verknuepfung zu projekt';
Comment on column "public"."wdone"."wd_minum" is 'Mitarbeiter; Verknuepfung zu mitarbeiter';
Comment on column "public"."wdone"."wd_datum" is 'Datum fuer das die Zeiterfassung gilt';
Comment on column "public"."wdone"."wd_hours" is 'Anzahl gearbeitete Stunden';
Comment on column "public"."wdone"."wd_task" is 'Tast; Verknuepfung zu task';
Comment on column "public"."wdone"."wd_rk" is '1 = Reisekosten angefallen; 0 = keine Reisekosten';
Comment on column "public"."wdone"."wd_phase" is 'Phase; Verknuepfung zu key_phase';
Comment on column "public"."wdone"."wd_edit" is '0 = Datensatz ist editierbar; 1 = Datensatz ist gesperrt';
Comment on column "public"."wdone"."wd_text" is 'Beschreibung der erledigten Arbeit';
Comment on column "public"."wdone"."wd_verr" is '0 = IST-Zeit ist verrechenbar; 1 = IST-Zeit ist nicht verrechenbar';
Comment on column "public"."key_sgf"."ks_num" is 'Schluesselnummer';
Comment on column "public"."key_sgf"."ks_sgf" is 'Strategisches Geschaeftsfeld';
Comment on column "public"."key_abt"."ka_num" is 'Schluesselnummer';
Comment on column "public"."key_abt"."ka_abt" is 'Bezeichnung der Abteilung';
Comment on column "public"."key_role"."kr_num" is 'Schluesselnummer';
Comment on column "public"."key_role"."kr_role" is 'Rollenbezeichnung';
Comment on column "public"."key_tmgroup"."kt_num" is 'Schluesselnummer';
Comment on column "public"."key_tmgroup"."kt_tm" is 'Themenmanagergruppe';
Comment on column "public"."key_land"."kl_num" is 'Schluesselnummer';
Comment on column "public"."key_land"."kl_land" is 'Land, Herkunft';
Comment on column "public"."task"."ta_num" is 'Automatisch vergebene Tasknummer';
Comment on column "public"."task"."ta_plnum" is 'Plannummer; Verknuepfung zu plan';
Comment on column "public"."task"."ta_id" is 'Automatisch generierte laufende Nummer innerhalb eines Plans';
Comment on column "public"."task"."ta_depend" is 'Definiert die Abhaengigkeit eines Tasks von einem anderen. Der Inhalt dieser Variablen referenziert auf die Tabelle task innerhalb des selben Plans';
Comment on column "public"."task"."ta_deptype" is 'Schluessel fuer die Art der Abhaengigkeit. Der Schluessel bezieht sich auf das Feld ta_deptype';
Comment on column "public"."task"."ta_name" is 'Name des Tasks';
Comment on column "public"."task"."ta_color" is 'Farbe des Tasks in R G B 32 Bit kodiert';
Comment on column "public"."task"."ta_shape" is 'Darzustellendes Muster';
Comment on column "public"."task"."ta_meeting" is '1 = Meilenstein; 0 = Normaler Task';
Comment on column "public"."task"."ta_start" is 'Startdatum des Tasks in Sekunden seit 1.1.1970';
Comment on column "public"."task"."ta_duration" is 'Dauer des Tasks in "Einheiten"';
Comment on column "public"."task"."ta_complete" is 'Prozentueller Teil des asks der bereits erledigt ist.';
Comment on column "public"."task"."ta_priority" is 'Prioritaet des Tasks';
Comment on column "public"."task"."ta_level" is 'Grad der Einrueckung bei optischer Darstellung';
Comment on column "public"."task"."ta_notiz" is 'Kommentar zum Task';
Comment on column "public"."task"."ta_phase" is 'Phase; Verweis auf key_phase';
Comment on column "public"."task"."ta_risiko" is 'Definiert das Risiko fuer die Umsetzung des Tasks in Prozent';
Comment on column "public"."task"."ta_hash" is 'Hashwert des Inhalts von ta_name';
Comment on column "public"."task"."ta_notiz" is 'Optionaler Kommentar';
Comment on column "public"."ptemp"."pt_num" is 'Automatisch generierte interne laufende Nummer';
Comment on column "public"."ptemp"."pt_tnum" is 'Verknuepfung zur internen Plannummer';
Comment on column "public"."ptemp"."pt_lfd" is 'Laufende Nummer innerhalb eines Plans (Taskreihenfolge)';
Comment on column "public"."ptemp"."pt_level" is 'Grad der Einrueckung';
Comment on column "public"."ptemp"."pt_name" is 'Taskname';
Comment on column "public"."ptemp"."pt_color" is 'Farbe';
Comment on column "public"."ptemp"."pt_meeting" is 'True = Meilenstein; False = Task';
Comment on column "public"."ptemp"."pt_phase" is 'Phase (optional)';
Comment on column "public"."project"."pr_num" is 'Projektnummer.';
Comment on column "public"."project"."pr_name" is 'Projektname';
Comment on column "public"."project"."pr_pl" is 'Projektleiter; Verknuepfung zu Mitarbeiter';
Comment on column "public"."project"."pr_plav" is 'Projektlenkungsausschuss Vorsitzender; Verknuepfung zu Mitarbeiter';
Comment on column "public"."project"."pr_sgf" is 'Strategisches Geschaeftsfeld; Verknuepfung zu key_sgf';
Comment on column "public"."project"."pr_tmgroup" is 'Themenmanagergruppe; Verknuepfung zu key_tmgroup';
Comment on column "public"."project"."pr_status" is 'Projektart';
Comment on column "public"."mitarbeiter"."mi_num" is 'Automatisch generierte Nummer';
Comment on column "public"."mitarbeiter"."mi_vname" is 'Vorname';
Comment on column "public"."mitarbeiter"."mi_nname" is 'Nachname';
Comment on column "public"."mitarbeiter"."mi_land" is 'Land; Verknuepfung zu key_land';
Comment on column "public"."mitarbeiter"."mi_abt" is 'Abteilung; Verlnuepfung zu key_abt';
Comment on column "public"."mitarbeiter"."mi_tel" is 'Telefonnummer / Klappe';
Comment on column "public"."mitarbeiter"."mi_mail" is 'E-Mailadresse';
Comment on column "public"."mitarbeiter"."mi_userid" is 'UserID; Dient zum Anmelden an das Programm und muss daher eindeutig sein.';
Comment on column "public"."mitarbeiter"."mi_passwd" is 'Passwort';
Comment on column "public"."mitarbeiter"."mi_rstufe" is 'Berechtigungsstufe';
Comment on column "public"."mitarbeiter"."mi_hacker" is 'Hackerkennzeichen';
Comment on column "public"."mitarbeiter"."mi_plan" is 'Mitarbeiter ist planbar (true), oder nicht (false)';
Comment on column "public"."key_phase"."kp_num" is 'Schluesselnummer';
Comment on column "public"."key_phase"."kp_phase" is 'Phasenname';
Comment on column "public"."plan"."pl_num" is 'Automatisch vergebene Plannummer';
Comment on column "public"."plan"."pl_lfd" is 'Automatisch generierte Laufende Nummer innerhalb eines Plans';
Comment on column "public"."plan"."pl_prnum" is 'Projektnummer; Verknuepfung zu projekt';
Comment on column "public"."plan"."pl_status" is 'Status des Plans';
Comment on column "public"."tempidx"."te_num" is 'Automatisch generierter interner Schluessel';
Comment on column "public"."tempidx"."te_name" is 'Name des Templates';
Comment on column "public"."tempidx"."te_zweck" is 'Kurze Beschreibung des Zwecks des Templates';
Comment on column "public"."dration"."dr_num" is 'Automatisch generierte interne laufende Nummer';
Comment on column "public"."dration"."dr_date_start" is 'Datum gueltig von. Notwendig um eine Historie auftzubauen';
Comment on column "public"."dration"."dr_date_end" is 'Datum gueltig bis.';
Comment on column "public"."dration"."dr_target" is 'Reiseziel';
Comment on column "public"."dration"."dr_purpose" is 'Zeweck der Reise';
Comment on column "public"."dration"."dr_pnightr" is 'True = Pauschale Naechtigungskosten';
Comment on column "public"."dration"."dr_nightr" is 'Naechtigungspauschale';
Comment on column "public"."dration"."dr_amount" is 'Angefallene Spesen';
Comment on column "public"."dration"."dr_kmcar" is 'Gefahrene Kilometer mit dem PKW';
Comment on column "public"."dration"."dr_kmdrain" is 'Gefahrene Kilometer mit Bahn / Bus';
Comment on column "public"."dration"."dr_pkmplain" is 'True = pauschale Flugkosten';
Comment on column "public"."dration"."dr_kmplain" is 'Angefallene Flugkosten';
Comment on column "public"."dration"."dr_wdnum" is 'Verknuepfung zu einer IST-Erfassung. Die Verknuepfung erfolgt nur wenn Reisekosten anfallen!';
Comment on column "public"."dration"."dr_whg" is 'Waehrung';
Comment on column "public"."dration"."dr_remark" is 'Anmerkungen zur Reisekostenabrechnung';
Comment on column "public"."dration"."dr_costloc" is 'Kostenstelle';
Comment on column "public"."dration"."dr_permit" is 'True = Reisekosten wurden genehmigt';
Comment on column "public"."dration"."dr_minum" is 'Reisekosten wurden von X genehmigt';
Comment on column "public"."charge"."ch_num" is 'Automatisch generierte laufende interne Nummer';
Comment on column "public"."charge"."ch_date" is 'Datum an dem der Aufwand angefallen ist.';
Comment on column "public"."charge"."ch_amount" is 'Betrag / Hoehe des Aufwands';
Comment on column "public"."charge"."ch_category" is 'Kategorie';
Comment on column "public"."charge"."ch_costloc" is 'Kostenstelle';
Comment on column "public"."charge"."ch_prnum" is 'Projektnummer';
Comment on column "public"."charge"."ch_whg" is 'Waehrung';
Comment on column "public"."key_category"."kc_num" is 'Schluesselnummer';
Comment on column "public"."key_category"."kc_category" is 'Bezeichnung der Kategorie eines angefallenen Aufwands';
Comment on column "public"."key_costloc"."kc_num" is 'Kostenstelle';
Comment on column "public"."key_costloc"."kc_costloc" is 'Bezeichnung der Kostenstelle';
Comment on column "public"."kunde"."ku_num" is 'Kundennummer';
Comment on column "public"."kunde"."ku_firma" is 'Name der Firma';
Comment on column "public"."kunde"."ku_anrede" is 'Anrede des Kunden';
Comment on column "public"."kunde"."ku_vname" is 'Vorname des Kunden (optional)';
Comment on column "public"."kunde"."ku_nname" is 'Nachname des Kunden (optional)';
Comment on column "public"."kunde"."ku_strasse" is 'Firmenanschrift; Strasse';
Comment on column "public"."kunde"."ku_hnum" is 'Firmenanschrift; Hausnummer';
Comment on column "public"."kunde"."ku_ort" is 'Firmenanschrift; Ort';
Comment on column "public"."kunde"."ku_plz" is 'Firmenanschrift; Postleitzahl';
Comment on column "public"."kunde"."ku_costloc" is 'Kostenstelle';
Comment on column "public"."key_anrede"."ka_num" is 'Schluesselnummer';
Comment on column "public"."key_anrede"."ka_anrede" is 'Anrede des Kunden';
Comment on column "public"."kgroup"."kg_num" is 'Automatisch generierte interne laufende Nummer';
Comment on column "public"."kgroup"."kg_name" is 'Gruppenname';
Comment on column "public"."kgroup"."kg_prnum" is 'Projektnummer';
Comment on column "public"."histanteil"."hi_num" is 'Automatisch generierte interne laufende Nummer';
Comment on column "public"."histanteil"."hi_valid_from" is 'Datum ab dem der Eintrag gilt.';
Comment on column "public"."histanteil"."hi_valid_to" is 'Datum bis zu dem der Eintrag gilt.';
Comment on column "public"."histanteil"."hi_ksnum" is 'Kontainernummer';
Comment on column "public"."histanteil"."hi_proz" is 'Anteile in Prozent';
Comment on column "public"."fixanteil"."fx_num" is 'Automatisch generierte interne laufende Nummer';
Comment on column "public"."fixanteil"."fx_valid_from" is 'Datum ab dem der Eintrag gilt.';
Comment on column "public"."fixanteil"."fx_valid_to" is 'Datum bis zu dem der Eintrag gilt.';
Comment on column "public"."fixanteil"."fx_kunum" is 'Kundennummer';
Comment on column "public"."fixanteil"."fx_proz" is 'Anteile in Prozent';
Comment on Column "public"."calendar"."ca_num" is 'Laufende Nummer';
Comment on Column "public"."calendar"."ca_prnum" is 'Projektnummer';
Comment on column "public"."calendar"."ca_minum" is 'Mitarbeiternummer';
Comment on column "public"."calendar"."ca_task" is 'Tasknummer zu der die Aufgabe gehoert';
Comment on column "public"."calendar"."ca_writer" is 'Mitarbeiternummer des Erstellers des Eintrags';
Comment on column "public"."calendar"."ca_date" is 'Datum (und Uhrzeit) des Events/Tasks';
Comment on column "public"."calendar"."ca_end" is 'Datum des Endes der Aufgabe';
Comment on column "public"."calendar"."ca_repeat" is 'Art der Widerholung';
Comment on column "public"."calendar"."ca_repanz" is 'Anzahl der Widerholungen';
Comment on column "public"."calendar"."ca_title" is 'Titel / Ueberschrift des Eintrags';
Comment on column "public"."calendar"."ca_text" is 'Beschreibung der Aufgabe/Ereignis';
Comment on column "public"."calendar"."ca_status" is 'Status der Aufgabe/Ereignis';
Comment on column "public"."ejournal"."ej_num" is 'Laufende Nummer';
Comment on column "public"."ejournal"."ej_date" is 'Datum und Uhrzeit der Eintragung';
Comment on Column "public"."ejournal"."ej_uid" is 'UserID des aktuell angemeldeten Users, welcher die Aktion ausgeloest hat';
Comment on column "public"."ejournal"."ej_type" is 'Art des Eintrags';
Comment on column "public"."ejournal"."ej_text" is 'Beschreibung des Ereignisses';
Comment on column "public"."key_jtype"."kj_num" is 'Schluesselnummer des EJ-Ereignisses';
Comment on column "public"."key_jtype"."kj_text" is 'Schluessel in Klartext';
Comment on column "public"."abschluss"."ab_num" is 'Interne laufende Nummer';
Comment on column "public"."abschluss"."ab_datum" is 'IST-Buchungen bis zu diesem Datum wurden abgeschlossen.';
Comment on column "public"."abschluss"."ab_user" is 'Abschluss wurde duchgefuehrt von $USER';
Comment on column "public"."abschluss"."ab_prnum" is 'Projektnummer';
Comment on column "public"."abschluss"."ab_plnum" is 'Plannummer';
COMMENT ON COLUMN "public"."planstatus"."ps_num" IS 'Laufende Nummer';
COMMENT ON COLUMN "public"."planstatus"."ps_pl" IS 'Projektleiter';
COMMENT ON COLUMN "public"."planstatus"."ps_prnum" IS 'Projektnummer';
COMMENT ON COLUMN "public"."planstatus"."ps_plnum" IS 'Verweis auf Plan';
COMMENT ON COLUMN "public"."planstatus"."ps_status" IS 'true = Status ist gueltig, false = Status ist ungueltig';
COMMENT ON COLUMN "public"."planstatus"."ps_comment" IS 'Kommentar oder Bericht';
COMMENT ON COLUMN "public"."planstatus"."ps_datum" IS 'Zeitstempel der Erfassung';
COMMENT ON COLUMN "public"."planstatus"."ps_lfd" IS 'Laufende Nummer innerhalb eines Plans';
COMMENT ON COLUMN "public"."kgsammel"."ks_num" IS 'Interne laufende Nummer';
COMMENT ON COLUMN "public"."kgsammel"."ks_kgnum" IS 'Verknuepfung zur Kundengruppe';
COMMENT ON COLUMN "public"."kgsammel"."ks_kunum" IS 'Verknuepfung zum Kunden';
COMMENT ON COLUMN "public"."key_whg"."wh_num" IS 'ISO-Code der Waehrung';
COMMENT ON COLUMN "public"."key_whg"."wh_whg" IS 'Waehrungskuerzel';
COMMENT ON COLUMN "public"."key_whg"."wh_bez" IS 'Bezeichnung der Waehrung';
COMMENT ON COLUMN "public"."tagsatz"."tg_num" IS 'Interne laufende Nummer';
COMMENT ON COLUMN "public"."tagsatz"."tg_valid_from" IS 'Datum gueltig ab';
COMMENT ON COLUMN "public"."tagsatz"."tg_valid_to" IS 'Datum gueltig bis';
COMMENT ON COLUMN "public"."tagsatz"."tg_amount" IS 'Tagsatz';
COMMENT ON COLUMN "public"."tagsatz"."tg_nightr" IS 'Naechtigungspauschale';
COMMENT ON COLUMN "public"."tagsatz"."tg_ration" IS 'Tagesdiaeten';
COMMENT ON COLUMN "public"."tagsatz"."tg_kmcar" IS 'Kilometerpauschale PKW';
COMMENT ON COLUMN "public"."tagsatz"."tg_kmdrain" IS 'Kilometerpauschale Zug';
COMMENT ON COLUMN "public"."tagsatz"."tg_kmplain" IS 'Kilometerpauschale Flugzeug';
COMMENT ON COLUMN "public"."tagsatz"."tg_whg" IS 'Verknuepfung zur Waehrung';
COMMENT ON COLUMN "public"."tagsatz"."tg_minum" IS 'Verknuepfung zum Mitarbeiter';
COMMENT ON COLUMN "public"."language"."la_num" IS 'Interne laufende Nummer';
COMMENT ON COLUMN "public"."language"."la_tnum" IS 'Manuell vergebene Nummer des Textes';
COMMENT ON COLUMN "public"."language"."la_lang" IS 'ISO Sprachkuerzel';
COMMENT ON COLUMN "public"."language"."la_text" IS 'Beliebiger Text';
COMMENT ON COLUMN "public"."matopr"."ma_num" IS 'Laufende Nummer';
COMMENT ON COLUMN "public"."matopr"."ma_minum" IS 'Mitarbeiternummer';
COMMENT ON COLUMN "public"."matopr"."ma_prnum" IS 'Projektnummer';
COMMENT ON COLUMN "public"."matopr"."ma_role" IS 'Rolle im Projekt';

/* Create Tab 'Others' for Selected Tables */




/* Create Alternate Keys */

Create unique index "idx_ptemp_numlfd" on "public"."ptemp" ("pt_tnum","pt_lfd");


Create unique index "idx_mi_name" on "public"."mitarbeiter" ("mi_nname","mi_vname");



/* Create Indexes */
Create unique index "idx_mi_userid" on "public"."mitarbeiter" using btree ("mi_userid");
Create unique index "idx_tempidx_name" on "tempidx" using btree ("te_name");
Create index "idx_ejournal_date" on "ejournal" using btree ("ej_date");
CREATE UNIQUE INDEX "idx_text" ON "public"."language" USING btree ("la_tnum", "la_lang");

/* Create Foreign Keys */
Create index "IX_Relationship41" on "public"."dration" ("dr_wdnum");
Alter table "public"."dration" add Constraint "Relationship41" foreign key ("dr_wdnum") references "public"."wdone" ("wd_num") on update restrict on delete restrict;
Create index "IX_Relationship42" on "public"."dration" ("dr_whg");
Alter table "public"."dration" add Constraint "Relationship42" foreign key ("dr_whg") references "public"."key_whg" ("wh_num") on update restrict on delete restrict;
Create index "IX_Relationship5" on "public"."project" ("pr_sgf");
Alter table "public"."project" add Constraint "Relationship5" foreign key ("pr_sgf") references "public"."key_sgf" ("ks_num") on update restrict on delete restrict;
Create index "IX_Relationship10" on "public"."mitarbeiter" ("mi_abt");
Alter table "public"."mitarbeiter" add Constraint "Relationship10" foreign key ("mi_abt") references "public"."key_abt" ("ka_num") on update restrict on delete restrict;
Create index "IX_Relationship17" on "public"."allocation" ("al_role");
Alter table "public"."allocation" add Constraint "Relationship17" foreign key ("al_role") references "public"."key_role" ("kr_num") on update restrict on delete restrict;
Create index "IX_Relationship4" on "public"."project" ("pr_tmgroup");
Alter table "public"."project" add Constraint "Relationship4" foreign key ("pr_tmgroup") references "public"."key_tmgroup" ("kt_num") on update restrict on delete restrict;
Create index "IX_Relationship11" on "public"."mitarbeiter" ("mi_land");
Alter table "public"."mitarbeiter" add Constraint "Relationship11" foreign key ("mi_land") references "public"."key_land" ("kl_num") on update restrict on delete restrict;
Create index "IX_Relationship13" on "public"."allocation" ("al_task");
Alter table "public"."allocation" add Constraint "Relationship13" foreign key ("al_task") references "public"."task" ("ta_num") on update restrict on delete restrict;
Create index "IX_Relationship21" on "public"."wdone" ("wd_task");
Alter table "public"."wdone" add Constraint "Relationship21" foreign key ("wd_task") references "public"."task" ("ta_num") on update restrict on delete restrict;
Create index "IX_R12" on "public"."plan" ("pl_prnum");
Alter table "public"."plan" add Constraint "R12" foreign key ("pl_prnum") references "public"."project" ("pr_num") on update restrict on delete restrict;
Create index "IX_Relationship6" on "public"."wdone" ("wd_prnum");
Alter table "public"."wdone" add Constraint "Relationship6" foreign key ("wd_prnum") references "public"."project" ("pr_num") on update restrict on delete restrict;
Create index "IX_Relationship38" on "public"."charge" ("ch_prnum");
Alter table "charge" add Constraint "Relationship38" foreign key ("ch_prnum") references "public"."project" ("pr_num") on update restrict on delete restrict;
Create index "IX_Relationship39" on "public"."charge" ("ch_whg");
Alter table "charge" add Constraint "Relationship39" foreign key ("ch_whg") references "public"."key_whg" ("wh_num") on update restrict on delete restrict;
Create index "IX_Relationship40" on "public"."kgroup" ("kg_prnum");
Alter table "kgroup" add Constraint "Relationship40" foreign key ("kg_prnum") references "public"."project" ("pr_num") on update restrict on delete restrict;
Create index "IX_Relationship7" on "public"."wdone" ("wd_minum");
Alter table "public"."wdone" add Constraint "Relationship7" foreign key ("wd_minum") references "public"."mitarbeiter" ("mi_num") on update restrict on delete restrict;
Create index "IX_Relationship15" on "public"."allocation" ("al_ressource");
Alter table "public"."allocation" add Constraint "Relationship15" foreign key ("al_ressource") references "public"."mitarbeiter" ("mi_num") on update restrict on delete restrict;
Create index "IX_Relationship19" on "public"."project" ("pr_pl");
Alter table "public"."project" add Constraint "Relationship19" foreign key ("pr_pl") references "public"."mitarbeiter" ("mi_num") on update restrict on delete restrict;
Create index "IX_Relationship20" on "public"."project" ("pr_plav");
Alter table "public"."project" add Constraint "Relationship20" foreign key ("pr_plav") references "public"."mitarbeiter" ("mi_num") on update restrict on delete restrict;
Create index "IX_Relationship9" on "public"."task" ("ta_phase");
Alter table "public"."task" add Constraint "Relationship9" foreign key ("ta_phase") references "public"."key_phase" ("kp_num") on update restrict on delete restrict;
Create index "IX_Relationship12" on "public"."wdone" ("wd_phase");
Alter table "public"."wdone" add Constraint "Relationship12" foreign key ("wd_phase") references "public"."key_phase" ("kp_num") on update restrict on delete restrict;
Create index "IX_Relationship16" on "public"."allocation" ("al_phase");
Alter table "public"."allocation" add Constraint "Relationship16" foreign key ("al_phase") references "public"."key_phase" ("kp_num") on update restrict on delete restrict;
Create index "IX_Relationship18" on "public"."ptemp" ("pt_phase");
Alter table "public"."ptemp" add Constraint "Relationship18" foreign key ("pt_phase") references "public"."key_phase" ("kp_num") on update restrict on delete restrict;
Create index "IX_Relationship8" on "public"."task" ("ta_plnum");
Alter table "public"."task" add Constraint "Relationship8" foreign key ("ta_plnum") references "public"."plan" ("pl_num") on update restrict on delete restrict;
Create index "IX_Relationship22" on "ptemp" ("pt_tnum");
Alter table "public"."ptemp" add Constraint "Relationship22" foreign key ("pt_tnum") references "tempidx" ("te_num") on update restrict on delete restrict;
Create index "IX_Relationship23" on "charge" ("ch_category");
Alter table "public"."charge" add Constraint "Relationship23" foreign key ("ch_category") references "key_category" ("kc_num") on update restrict on delete restrict;
Create index "IX_Relationship24" on "charge" ("ch_costloc");
Alter table "public"."charge" add Constraint "Relationship24" foreign key ("ch_costloc") references "key_costloc" ("kc_num") on update restrict on delete restrict;
Create index "IX_Relationship26" on "kunde" ("ku_costloc");
Alter table "public"."kunde" add Constraint "Relationship26" foreign key ("ku_costloc") references "key_costloc" ("kc_num") on update restrict on delete restrict;
Create index "IX_Relationship31" on "histanteil" ("hi_ksnum");
Alter table "public"."histanteil" add Constraint "Relationship31" foreign key ("hi_ksnum") references "kgsammel" ("ks_num") on update restrict on delete restrict;
Create index "IX_Relationship32" on "fixanteil" ("fx_kunum");
Alter table "public"."fixanteil" add Constraint "Relationship32" foreign key ("fx_kunum") references "kunde" ("ku_num") on update restrict on delete restrict;
Create index "IX_Relationship25" on "kunde" ("ku_anrede");
Alter table "public"."kunde" add Constraint "Relationship25" foreign key ("ku_anrede") references "key_anrede" ("ka_num") on update restrict on delete restrict;
Create index "IX_Relationship50" on "public"."calendar" ("ca_prnum");
Alter table "public"."calendar" add Constraint "Relationship50" foreign key ("ca_prnum") references "public"."project" ("pr_num") on update restrict on delete restrict;
Create index "IX_Relationship51" on "public"."calendar" ("ca_minum");
Alter table "public"."calendar" add Constraint "Relationship51" foreign key ("ca_minum") references "public"."mitarbeiter" ("mi_num") on update restrict on delete restrict;
Create index "IX_Relationship52" on "public"."calendar" ("ca_task");
Alter table "public"."calendar" add Constraint "Relationship52" foreign key ("ca_task") references "public"."task" ("ta_num") on update restrict on delete restrict;
Create index "IX_Relationship53" on "public"."calendar" ("ca_writer");
Alter table "public"."calendar" add Constraint "Relationship53" foreign key ("ca_writer") references "public"."mitarbeiter" ("mi_num") on update restrict on delete restrict;
Create index "IX_Relationship54" on "public"."ejournal" ("ej_type");
Alter table "public"."ejournal" add Constraint "Relationship54" foreign key ("ej_type") references "public"."key_jtype" ("kj_num") on update restrict on delete restrict;
Create index "IX_Relationship55" on "public"."abschluss" ("ab_prnum");
Alter table "public"."abschluss" add Constraint "Relationship55" foreign key ("ab_prnum") references "public"."project" ("pr_num") on update restrict on delete restrict;
Create index "IX_Relationship56" on "public"."abschluss" ("ab_plnum");
Alter table "public"."abschluss" add Constraint "Relationship56" foreign key ("ab_plnum") references "public"."plan" ("pl_num") on update restrict on delete restrict;
Create index "IX_Relationship57" on "public"."kgsammel" ("ks_kunum");
Alter table "public"."kgsammel" add Constraint "Relationship57" foreign key ("ks_kunum") references "public"."kunde" ("ku_num") on update restrict on delete restrict;
Create index "IX_Relationship58" on "public"."kgsammel" ("ks_kgnum");
Alter table "public"."kgsammel" add Constraint "Relationship58" foreign key ("ks_kgnum") references "public"."kgroup" ("kg_num") on update restrict on delete restrict;

/* Create Views */
/* created on 14.10.2004 13:56:56 */

create view allocmi as
select allocation.al_num as am_num,
allocation.al_task as am_task,
allocation.al_ressource as am_ressource,
allocation.al_hours as am_hours,
allocation.al_phase as am_phase,
key_role.kr_role as am_role,
key_role.kr_num as am_krnum,
mitarbeiter.mi_vname as am_vname,
mitarbeiter.mi_nname as am_nname,
allocation.al_pstart as am_pstart,
allocation.al_pduration as am_pduration
from allocation left join mitarbeiter on mitarbeiter.mi_num = allocation.al_ressource
     left join key_role on key_role.kr_num = allocation.al_role;



/* Create Referential Integrity Triggers */





/* Create User-Defined Triggers */




/* Insert some basic values into the key tables */


COPY "counter" FROM stdin;
0        0        0        0        0        0        0        0        0        0        0        0        0        0        0        0        0        0        2.4
\.


COPY "key_jtype" FROM stdin;
100     Task Gelöscht
101     Plan gelöscht
102     Projekt gelöscht
103     IST-Eintrag gelöscht
104     Kalendereintrag gelöscht
105     Mitarbeiter aus Plan gelöscht
200     Plan kopiert
201     Neuen Mitarbeiter hinzugefügt
202     Mitarbeiter in Plan hinzugefügt
204     Neuen Plan für Konto angelegt
205     Neuen Plan hinzugefügt
300     Plan geändert
301     Projekt geändert
302     IST-Eintrag geändert
303     Mirarbeiter geändert
304     Kalendereintrag geändert
999     Unbekannter Eintrag
\.

copy "key_whg" from stdin;
1       USD     US-Dollar
2       EGP     Ägyptisches Pfund
3       ETB     Äthiopischer Birr
4       ATS     Österreichischer Schilling
5       AFN     Afghanischer Afghani
6       ALL     Albanischer Lek
7       DZD     Algerischer Dinar
8       ADP     Andorranische Pesete
9       ADF     Andorranischer Franc
10      AON     Angolanischer Kwanza
11      ARS     Argentinischer Peso
12      AWG     Aruba-Florin
13      AUD     Australischer Dollar
14      BSD     Bahama-Dollar
15      BHD     Bahrain-Dinar
16      BDT     Bangladeschischer Taka
17      BBD     Barbados-Dollar
18      BEF     Belgischer Franc
19      BZD     Belize-Dollar
20      BMD     Bermuda-Dollar
21      BTN     Bhutanischer Ngultrum
22      BOB     Bolivianischer Boliviano
23      BWP     Botsuanischer Pula
24      BRL     Brasilianischer Real
25      GBP     Britisches Pfund
26      BND     Brunei-Dollar
27      BGN     Bulgarischer Lew
28      BIF     Burundi-Franc
29      XOF     CFA-Franc BCEAO
30      XAF     CFA-Franc BEAC
31      CLP     Chilenischer Peso
32      CNY     Chinesischer Renminbi Yuan
33      CRC     Costa-Rica-Colon
34      DKK     Dänische Krone
35      DEM     Deutsche Mark
36      DOP     Dominikanischer Peso
37      DJF     Dschibuti-Franc
38      XEU     ECU
39      ECS     Ecuadorianischer Sucre
40      SVC     El-Salvador-Colon
41      EEK     Estnische Krone
42      EUR     Euro
43      FKP     Falkland-Pfund
44      FJD     Fidschi-Dollar
45      FIM     Finnische Mark
46      FRF     Französischer Franc
47      GMD     Gambischer Dalasi
48      GHC     Ghanaischer Cedi
49      GIP     Gibraltar-Pfund
50      XAU     Gold (Unze)
51      GRD     Griechische Drachme
52      GTQ     Guatemaltekischer Quetzal
53      GNF     Guinea-Franc
54      GYD     Guyana-Dollar
55      HTG     Haitianische Gourde
56      NLG     Niederländischer Gulden
57      HNL     Honduranische Lempira
58      HKD     Hongkong-Dollar
59      INR     Indische Rupie
60      IDR     Indonesische Rupiah
61      IQD     Irakischer Dinar
62      IRR     Iranischer Rial
63      IEP     Irisches Pfund
64      ISK     Isländische Krone
65      ILS     Israelischer Neuer Schekel
66      ITL     Italienische Lire
67      JMD     Jamaikanischer Dollar
68      JPY     Japanischer Yen
69      JOD     Jordanischer Dinar
70      YUN     Jugoslawischer Dinar
71      KYD     Kaiman-Dollar
72      KHR     Kambodschanischer Riel
73      CAD     Kanadischer Dollar
74      CVE     Kap-Verde-Escudo
75      KZT     Kasachstan-Tenge
76      QAR     Katar-Rial
77      KES     Kenianischer Schilling
78      COP     Kolumbianischer Peso
79      KMF     Komoren-Franc
80      HRK     Kroatische Kuna
81      CUP     Kubanischer Peso
82      KWD     Kuwaitischer Dinar
83      LAK     Laotischer Kip
84      LSL     Lesothischer Loti
85      LVL     Lettische Lats
86      LBP     Libanesisches Pfund
87      LRD     Liberianischer Dollar
88      LYD     Lybischer Dinar
89      LTL     Litauische Litas
90      LUF     Luxemburgischer Franc
91      MOP     Macauische Pataca
92      MGF     Madagaskar-Franc
93      MWK     Malawi-Kwacha
94      MYR     Malaysischer Ringgit
95      MVR     Maledivische Rufiyaa
96      MTL     Maltesische Lire
97      MAD     Marokkanischer Dirham
98      MRO     Mauretanische Ouguiya
99      MUR     Mauritius-Rupie
100     MXP     Mexikanischer Peso
101     MNT     Mongolischer Tugrik
102     MZM     Mosambikanischer Metical
103     MMK     Myanmarischer Kyat
104     ANG     NL-Antillen-Gulden
105     NAD     Namibischer Dollar
106     NPR     Nepalesische Rupie
107     NZD     Neuseeländischer Dollar
108     NIO     Nicaraguanischer Cordoba Oro
109     NGN     Nigerianische Naira
110     KPW     Nordkoreanischer Won
111     NOK     Norwegische Krone
112     OMR     Omani-Rial
113     PKR     Pakistanische Rupie
114     XPD     Palladium (Unze)
115     PAB     Panamaischer Balboa
116     PGK     Papua-Neuguinea-Kina
117     PYG     Paraguayischer Guarani
118     PEN     Peruanischer Sol
119     PHP     Philippinischer Peso
120     XPT     Platin (Unze)
121     PLZ     Polnischer Zloty
122     PTE     Portugiesischer Escudo
123     ROL     Rumänischer Leu
124     RUB     Russischer Rubel
125     STD     Sao-Tome/Principe-Dobra
126     ZAR     Südafrikanischer Rand
127     KRW     Südkoreanischer Won
128     SBD     Salomonen-Dollar
129     ZMK     Sambischer Kwacha
130     WST     Samoanischer Tala
131     SAR     Saudi-Riyal
132     SEK     Schwedische Krone
133     CHF     Schweizer Franken
134     SCR     Seychellen-Rupie
135     SLL     Sierraleonische Leone
136     XAG     Silber (Unze)
137     ZWD     Simbabwe-Dollar
138     SGD     Singapur-Dollar
139     SKK     Slovakische Krone
140     SIT     Slowenischer Tolar
141     SOS     Somalischer Schilling
142     ESP     Spanische Pesete
143     LKR     Sri-Lanka-Rupie
144     SHP     St. Helena-Pfund
145     SDD     Sudanesischer Dinar
146     SDP     Sudanesisches Pfund
147     SRG     Suriname-Gulden
148     SZL     Swasiländischer Lilangeni
149     SYP     Syrisches Pfund
150     TRL     Türkische Lira
151     TWD     Taiwanesischer Dollar
152     TZS     Tansania-Schilling
153     THB     Thailändischer Baht
154     TOP     Tongaische Pa anga
155     TTD     Trinidad/Tobago-Dollar
156     CSK     Tschechische Krone
157     TND     Tunesischer Dinar
158     UGS     Uganda-Schilling
159     UAH     Ukrainische Griwna
160     HUF     Ungarischer Forint
161     UYP     Uruguayischer Peso
162     VUV     Vanuatu-Vatu
163     VEB     Venezuelanischer Bolivar
164     AED     Vereinigte Arabische Emirate-Dirham
165     VND     Vietnamesischer Dong
166     CYP     Zypern-Pfund
\.