| 0,0 → 1,1035 |
|---|
| /* |
| 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 |
| \. |
| |