Grin logo
Go to shop › Computer Science - Applied

Grundzüge der Ökonomie - Varian

Excerpt


Grundzüge der Ökonomie - Varian

 

Phase I: Projektbeschreibung Data Engineering

Es soll eine Datenbank entworfen werden, die alle relevanten Daten enthält, um eine Wertpapierorder zu verwalten. Dazu sind folgende Entitäten nötig:
Person, Kunde, Kundenberater, Depot, Wertpapier, Order, Partner...

Was nicht im Modell enthalten ist...
Dieses Modell enthält nur grundsätzliche Daten, die bei einer Wertpapierorder zur Verfügung stehen müssen. Der zeitliche Ablauf einer Order, von der Aufgabe derselben bis zur effektiven Lieferung und Buchung kann in diesem Rahmen leider nicht modelliert werden.

Entitäten und ihre Beziehungen...
Eine Person kann ein Kunde oder ein Kundenberater sein. Sie ist charakterisiert durch eine Sozialversicherungsnummer, einen Namen und eine Adresse. Eine Person kann jedoch nicht beides gleichzeitig sein, da aus Sicherheitsgründen Orders von eigenen Angestellten für sich selbst nicht zugelassen sind.

Ein Kunde hat eine Kundennummer, wird von einem Kundenberater betreut und hat eine Bonifikation. Er kann keines oder mehrere Depots lautend auf seinen Namen führen.

Ein Kundenberater hat eine Nummer, einen Titel und einen Qualifikationsgrad.

Eine Order ist charakterisiert durch eine Nummer, einen Status, eine Menge.
Jede Order enthält genau einen Wertpapiertyp und wird von einem Kundenberater betreut. Sie hat ein Aufgabedatum.

Jedes Wertpapier hat eine Wertpapierkennnummer, einen Kurs und eine kleinste Stückelung.

Ein Partner ist charakterisiert durch ein Kürzel, einen Namen und eine Adresse. Ein Wertpapier kann bei verschiedenen Partnern gekauft werden, ein Partner handelt mit mehreren Wertpapieren.

Ein Depot hat eine eindeutige Depotnummer, ein Verrechnungskonto und enthält verschiedene Wertpapiere. Ein Kunde kann mehrere Depots haben. Auf einem Depot sind keine oder mehrere Wertpapiere vorhanden.

DAS E/R Modell

Phase II: Relationenschemata


Kunde (Kundennummer, SVZnummer, Beraternummer, Bonifikation)
    

key1{Kundennummer}
key2{SVZnummer}
key3{Beraternummer}
    

Kunde.SVZnummer · Person
Kunde.Beraternummer · Kundenberater
    
               
               

Kundenberater (Beraternummer, SVZnummer, Titel)
      

key1{Beraternummer}
key2{SVZnummer}
        

Kundenberater.SVZnummer · Person
        
               
               

Order (OrderNr, Kundennummer, Beraternummer, Status, Datum, Depotnummer, Menge, WKN)
    

key1{Ordernummer}
key2{Kundennummer}
key3{Beraternummer}
key4{WKN}
key5{Depot-nummer}

Order.Kundennummer · Kunde
Order.Depotnummer · Depot
Order.Beraternummer · Kundenberater
Order.WKN · Wertpapier
               
               

Depot (DepotNr, Kundennummer, Verrkonto)
        

key1{DepotNR}
key2{Kundennummer}
        

Depot.Kundennummer · Kunde
          
               
               

Wertpapier (WKN, Kurzbez, Art, Nennwhg, kleinste Stückelung)
        

key1{WKN}
              
               
               

Partner (Kürzel, Name, Adresse)
          

key1{Kürzel}
              
               
               

liegt_auf (WKN, DepotNR, Menge)
        

key1{DepotNR}
key2{WKN}
        

liegt_auf.DepotNR · Depot
  
liegt_auf.WKN · Wertpapier
liegt_auf.WKN · Wertpapier
               
               

vertreibt (WKN, Kürzel)
            

key1{WKN}
  
key2{Kürzel}
        

vertreibt.WKN · Wertpapier
  
vertreibt.Kürzel · Partner
      
               
               

Projekt Phase II: Modellierung von Geschäftsprozessen
      
               

Ein typischer Geschäftsfall für mein Modell könnte folgendermaßen aussehen: Vom Kunden
  

sind bereits gewisse Stammdaten wie Depots, Bonifikation, Verrechnungskonten gespeichert.

Der Kunde kommt nun in die Bank und äußert einen Kaufwunsch. Daraufhin muß der Kundenberater,

der nun für den Kunden verantwortlich ist, die Order in das System eingeben. Diese besitzt einen Status

für eine getrennte Bearbeitung, ein Datum, und eine Menge eines Wertpapiers. Auch zu diesem Wertpapier

sind bereits Stammdaten vorhanden. Das Wertpapier wird vertrieben von einem Partner.
  
               

Ein komplexer Geschäftsfall könnte folgendermaßen aussehen: Der Partner hat die Wertpapiere an die

Bank verkauft, daraufhin muss der Status der Order auf ausgeführt gestellt werden und das Wertpapier muß

nun auf dem Depot aufscheinen (Beziehung liegt_auf).
      

Phase III: SQL-Datenbankschema und Datengenerierung

Überblick

· Beschreibung der Generierung der Testdaten:
Die Testdaten wurden zunächst in einer Excel-Tabelle erstellt, da es einfach war, fortlaufende Nummern zu erstellen (für SVZNummer, Depotnummer, Kundennummer usw.). Weiters konnten dort vorhandene Daten von Wertpapieren (aus Wertpapierinfosystem entnommen) und verschiedene Personen (aus dem Internet) leicht eingefügt werden. Anschließend wurde das Excel-File als Textfile exportiert.

Dieses Textfile enthält folgende Angaben zum Kunden:
· SVZNummer, Name, Adresse, Kundennummer, BeraterId, Bonifikation, DepotNr., Verrkonto (Adresse wiederholt sich periodisch, Namen sind eindeutig!)

Folgende Angaben zum Wertpapier:
· WKN, Kurzbezeichnung, Wertpapierart, Nennwhg, Kl.Stückelung, Partner (1000 Wertpapierechtdaten vorhanden, mittels unterschiedlicher Numerierung 3300 ausgeweitet)

Folgende Angaben zum Berater:
· SVZNummer, Name, Adresse, BeraterId, Titel (Nur 15 Tupel vorhanden, d.h. 15 Berater sind für 3300 Kunden zuständig)

Die ctl-Files werden von einem Java-Programm generiert, sodaß folgende Tables mit über 3300 Datensätzen entstehen:
· Person1, Kunde, Depot, Wertpapier, Vertreibt. Drei Datensätze für den Table Partner werden manuell mittels insert-Statement eingefügt (in der sql-Datei).
· Das Java-Programm generiert die ctl-Files so, daß alle Schlüsselrelationen erfüllt sind.

Format des Textfiles sieht folgendermaßen aus: Unterschiedliche Daten werden durch ,,;" getrennt. Die Datensätze sind durchnumeriert.

Position der Daten im Textfile:
1:SVZKunde, 2:Kundenname, 3: Kundenadresse, 4: Kundennummer, 5: BeraterIdbetreut, 6: Bonifikation, 7: DepotNr, 8: Verrkonto, 9: WKN, 10:Kurzbezeichnung WP, 11:WP-Art, 12:Nennwährung, 13: Kl.Stückelung, 14: Partner, 15: SVZ-Kundenberater, 16: Name Kundenberater, 17: Berateradresse, 18: BeraterId, 19: Titel des Beraters...

Dbloaden: UnixSkript, das alle Daten in die Datenbank einliest...

Das SQL-Datenbankschema

DROP TABLE VERTREIBT;
DROP TABLE LIEGT_AUF;
DROP TABLE PARTNER;
DROP TABLE ORDER1;
DROP TABLE WERTPAPIER;
DROP TABLE DEPOT;
DROP TABLE KUNDE;
DROP TABLE KUNDENBERATER;
DROP TABLE PERSON1;

CREATE TABLE PERSON1 (
SVZNummer NUMBER(10) NOT NULL,
Name VARCHAR2(50),
Adresse VARCHAR2(100),
CONSTRAINT pk_person PRIMARY KEY(SVZNummer));

/* Table Person kann nur gelöscht werden, falls auch Kunde oder Kundenberater gelöscht werden kann */

CREATE TABLE KUNDENBERATER (
SVZNummer NUMBER(10) NOT NULL,
BeraterId NUMBER(6) NOT NULL,
Titel VARCHAR2(10),
CONSTRAINT pk_kundenberater PRIMARY KEY(BeraterId),
CONSTRAINT fk_kundenberater FOREIGN KEY(SVZNummer) REFERENCES PERSON1(SVZNummer) ON DELETE CASCADE,
CONSTRAINT unq_kundenberater UNIQUE(SVZNummer),
CONSTRAINT chk_BeraterId CHECK(BeraterId>99999));

/* kann gelöscht werden, entsprechende orders und kunden sind dann unbetreut (null) */

CREATE TABLE KUNDE
(
SVZNummer NUMBER(10) NOT NULL,
Kundennummer NUMBER(6) NOT NULL,
BeraterId NUMBER(6),
Bonifikation NUMBER(3),
CONSTRAINT pk_kunde PRIMARY KEY(Kundennummer),
CONSTRAINT fk_kunde FOREIGN KEY(SVZNummer) REFERENCES PERSON1(SVZNummer) ON DELETE CASCADE,
CONSTRAINT fk_verantwortlich FOREIGN KEY(BeraterId) REFERENCES KUNDENBERATER(BeraterId) ON DELETE SET NULL,
CONSTRAINT unq_kunde UNIQUE(SVZNummer),
CONSTRAINT chk_kunde CHECK(Kundennummer>99999));

/* Kunde kann gelöscht werden, falls zugehörige Orders und Depots vorher gelöscht werden */

CREATE TABLE DEPOT (
DepotNR NUMBER(11) NOT NULL,
Kundennummer NUMBER(6) NOT NULL,
Verrkonto NUMBER(11),
CONSTRAINT pk_depot PRIMARY KEY(DepotNR),
CONSTRAINT fk_depot FOREIGN KEY(Kundennummer) REFERENCES KUNDE(Kundennummer)
);

/* Kann gelöscht werden, falls keine Orders und Positionen zu diesem Depot existieren */

CREATE TABLE WERTPAPIER
(
WKN NUMBER(5) NOT NULL,
Kurzbez VARCHAR(100),
Wertpapierart VARCHAR(50),
Nennwhg VARCHAR(8),
klStueck VARCHAR(20),
CONSTRAINT pk_wertpapier PRIMARY KEY(WKN),
CONSTRAINT chk_wertpapier CHECK(WKN>9999));

/* Kann nur gelöscht werden, falls keine Order, kein Vertriebspartner und keine Positionen existieren */

CREATE TABLE ORDER1
(
OrderNR NUMBER(5) NOT NULL,
Kundennummer NUMBER(6) NOT NULL,
WKN NUMBER(5) NOT NULL,
BeraterId NUMBER(6) NULL,
Status NUMBER(1) NOT NULL,
Datum DATE NOT NULL,
DepotNr NUMBER(11) NOT NULL,
Menge NUMBER(15) NOT NULL,
CONSTRAINT pk_order PRIMARY KEY(OrderNR),
CONSTRAINT fk_order FOREIGN KEY(Kundennummer) REFERENCES KUNDE(Kundennummer),
CONSTRAINT betreut FOREIGN KEY(BeraterId) REFERENCES KUNDENBERATER(BeraterId) ON DELETE SET NULL,
CONSTRAINT fk_order_wkn FOREIGN KEY(WKN) REFERENCES WERTPAPIER(WKN),
CONSTRAINT chk_order CHECK(OrderNr>9999),
CONSTRAINT fk_enthaelt FOREIGN KEY(DepotNR) REFERENCES DEPOT(DepotNR) ON DELETE CASCADE
);

/* Kann immer gelöscht werden... Bei der Eingabe wäre noch eine Funktion notwendig, die überprüft ob Kundennummer*/
/*und Depotnummer zusammenpassen */

CREATE TABLE PARTNER
(
Kuerzel VARCHAR2(5) NOT NULL,
Name VARCHAR2(50) NULL,
Adresse VARCHAR2(100) NULL,
CONSTRAINT pk_partner PRIMARY KEY(Kuerzel),
CONSTRAINT chk_kuerzel CHECK(Kuerzel IN(′BKAUT′, ′BKVOB′, ′BKINT′)));

/* Kann gelöscht werden, zugehöriger table vertreibt auch gelöscht */

CREATE TABLE LIEGT_AUF
(
WKN NUMBER(5),
DepotNR NUMBER(11),
Wpmenge NUMBER(15) NOT NULL,
CONSTRAINT pk_liegt_auf PRIMARY KEY(WKN,DepotNR),
CONSTRAINT fk_wp_liegtauf FOREIGN KEY(WKN) REFERENCES WERTPAPIER(WKN),
CONSTRAINT fk_depot_liegtauf FOREIGN KEY(DepotNR) REFERENCES DEPOT(DepotNR));
/* Kann ohne weiteres gelöscht werden */

CREATE TABLE VERTREIBT
(
WKN NUMBER(5) NOT NULL,
Kuerzel VARCHAR2(5) NOT NULL,
CONSTRAINT pk_liegt_auf PRIMARY KEY(WKN,Kuerzel),
CONSTRAINT fk_wp_vertreibt FOREIGN KEY(WKN) REFERENCES WERTPAPIER(WKN) ON DELETE CASCADE,
CONSTRAINT fk_partner_vertreibt FOREIGN KEY(Kuerzel) REFERENCES PARTNER(Kuerzel) ON DELETE CASCADE);
/* Kann ohne weiteres gelöscht werden */

column svznummer format 99,99,99,9999;
column name format a20;
column adresse format a20;
column beraterid format a20;
column qualigrad format a20;
column titel format a20;
column kundennummer format a20;
column bonifikation format a20;
column depotnr format a20;
column verrkonto format a20;
column wkn format a20;
column kurs format a20;
column klstueck format a20;
column status format a20;
column datum format a20;
column ordernummer format a20;
column kuerzel format a20;
column partner format a20;

insert into partner values(′BKVOB′,′Volksbank Oesterreich′,′Hinterm Hof 9 1080 Wien′);
insert into partner values(′BKAUT′,′Bank Austria Oesterreich′,′Julius Tandler Platz 7 / 1090 Wien′);
insert into partner values(′BKINT′,′Banca Internazionale Austria′,′Ungargasse 7/1030 Wien′);

select * from partner;

Das Java-Programm zum Generieren der ctl-Files

import java.util.Random;
import java.io.*;
import java.lang.reflect.Array;
import java.util.StringTokenizer;
import java.util.Vector;

public class createfiles
{

private static String infile, outfile;

public static void main(String[] args)
{
if (Array.getLength(args) == 1)
{
infile = args[0];
}
else
{
System.out.println("usage: java createfiles <inputfile>");
System.exit(0);
}

String Zeile_in = null;
String Datenarray[] = null;
BufferedReader in = null;
BufferedWriter out_person1 = null;
BufferedWriter out_kundenberater = null;
BufferedWriter out_kunde = null;
BufferedWriter out_depot = null;
BufferedWriter out_wertpapier = null;
BufferedWriter out_vertreibt = null;

try
{
in = new BufferedReader (new FileReader (infile));
}
catch (Exception e)
{
System.out.println("Error in opening Inputfile: " + e);
}

try
{
out_person1 = new BufferedWriter (new FileWriter ("out_person.ctl"));
out_kundenberater = new BufferedWriter (new FileWriter ("out_kundenberater.ctl"));
out_kunde = new BufferedWriter (new FileWriter ("out_kunde.ctl"));
out_depot = new BufferedWriter (new FileWriter ("out_depot.ctl"));
out_wertpapier = new BufferedWriter (new FileWriter ("out_wertpapier.ctl"));
out_vertreibt = new BufferedWriter (new FileWriter ("out_vertreibt.ctl"));
}
catch (Exception e)
{
System.out.println("Fehler bei Outfile " + e);
}

try
{

out_person1.write("LOAD DATA\n" +
"INFILE *\n" +
"INTO TABLE person1\n" +
"FIELDS TERMINATED BY ′;′ OPTIONALLY ENCLOSED BY ′\"′\n" +
"(SVZNummer,Name,Adresse)\n" +
"BEGINDATA\n");

out_kundenberater.write("LOAD DATA\n" +
"INFILE *\n" +
"INTO TABLE kundenberater\n" +
"FIELDS TERMINATED BY ′;′ OPTIONALLY ENCLOSED BY ′\"′\n" +
"(SVZNummer,BeraterId,Titel)\n" +
"BEGINDATA\n");

out_kunde.write("LOAD DATA\n" +
"INFILE *\n" +
"INTO TABLE kunde\n" +
"FIELDS TERMINATED BY ′;′ OPTIONALLY ENCLOSED BY ′\"′\n" +
"(SVZNummer,Kundennummer,BeraterId,Bonifikation)\n" +
"BEGINDATA\n");

out_depot.write("LOAD DATA\n" +
"INFILE *\n" +
"INTO TABLE depot\n" +
"FIELDS TERMINATED BY ′;′ OPTIONALLY ENCLOSED BY ′\"′\n" +
"(DepotNR,Kundennummer,Verrkonto)\n" +
"BEGINDATA\n");

out_wertpapier.write("LOAD DATA\n" +
"INFILE *\n" +
"INTO TABLE wertpapier\n" +
"FIELDS TERMINATED BY ′;′ OPTIONALLY ENCLOSED BY ′\"′\n" +
"(WKN,Kurzbez,Wertpapierart,Nennwhg,klStueck)\n" +
"BEGINDATA\n");

out_vertreibt.write("LOAD DATA\n" +
"INFILE *\n" +
"INTO TABLE vertreibt\n" +
"FIELDS TERMINATED BY ′;′ OPTIONALLY ENCLOSED BY ′\"′\n" +
"(WKN,Kuerzel)\n" +
"BEGINDATA\n");

out_person1.flush();
out_kundenberater.flush();
out_kunde.flush();
out_depot.flush();
out_wertpapier.flush();
out_vertreibt.flush();

try
{
int i = 0;

while ((Zeile_in = in.readLine()) != null)
{
String Daten[] = new String[20];
String help = ";";
StringTokenizer token = new StringTokenizer (Zeile_in,";",false);
int j = 0;
while (token.hasMoreTokens())
{
Daten[j] = token.nextToken();
j++;
}

//Verarbeitungsteil
out_person1.write(Daten[1] + ";" + Daten[2] + ";" + Daten[3] +"\n");
if (Daten[15] != null)
{
out_kundenberater.write(Daten[15] + ";" + Daten[18] + ";" + Daten[19] + "\n");
out_person1.write(Daten[15] + ";" + Daten[16] + ";" + Daten[17] +"\n");
out_person1.flush();
}
else{}
out_kunde.write(Daten[1] + ";" + Daten[4] + ";" + Daten[5] + ";" + Daten[6] + "\n");
out_depot.write(Daten[7] + ";" + Daten[4] + ";" + Daten[8] + "\n");
out_wertpapier.write(Daten[9] + ";" + Daten[10] + ";" + Daten[11] + ";" + Daten[12] + ";" + Daten[13] + "\n");
out_vertreibt.write(Daten[9] + ";" + Daten[14] + "\n");
out_person1.flush();
out_kundenberater.flush();
out_kunde.flush();
out_depot.flush();
out_wertpapier.flush();
out_vertreibt.flush();

i++;
}
}
catch (Exception e) {System.out.println("End of file reached");}

in.close();
out_person1.close();
out_kundenberater.close();
out_kunde.close();
out_depot.close();
out_wertpapier.close();
out_vertreibt.close();

}

catch (Exception e)
{
System.out.println("Error:" + e);
}
}

}

Ein Auszug aus den Testdaten (Textdatei und ctl-Files)

Textdatei Gesamt Auszug der ersten 4 Datensätze

1;1002761000;Za-Zong Zo;Grosse Augasse 3/1030 Wien;134257;243125;0;00703501000;00703601000;10152;7.625% CA PF. R 52/92-02;Pfandbrief;ATS;10.000;BKAUT;2902602000;Herbert Berater;Grosse Augasse 3/1030 Wien;243125;Mag Dr.
2;1002761001;Zaanen Adriaan C.;Kleine Augasse 10/1100 Wien;134258;243126;1;00703501001;00703601001;10153;7.125% CA PF. R 53/93-03;Pfandbrief;ATS;10.000;BKVOB;2902602001;Manfred Berater;Kleine Augasse 10/1100 Wien;243126;Mag
3;1002761002;Zabala Estrella M.;Ligusterweg 15/1150 Wien;134259;243127;2;00703501002;00703601002;10155;6.875% CA PF. R 55/93-03;Pfandbrief;ATS;10.000;BKINT;2902602002;Norbert Berater;Ligusterweg 15/1150 Wien;243127;Matura
4;1002761003;Zabalbeascoa Anatxu;Hoettinger Au/62100 Innsbruck;134260;243128;0;00703501003;00703601003;10157;6.875% CA PF. R 57/93-03;Pfandbrief;ATS;10.000;BKAUT;2902602003;Simon Berater;Hoettinger Au/62100 Innsbruck;243128;Dr. Dr.

Depot ctl-File

LOAD DATA
INFILE *
INTO TABLE depot
FIELDS TERMINATED BY ′;′ OPTIONALLY ENCLOSED BY ′"′
(DepotNR,Kundennummer,Verrkonto)
BEGINDATA
00703501000;134257;00703601000
00703501001;134258;00703601001

Kunde ctl-File

LOAD DATA
INFILE *
INTO TABLE kunde
FIELDS TERMINATED BY ′;′ OPTIONALLY ENCLOSED BY ′"′
(SVZNummer,Kundennummer,BeraterId,Bonifikation)
BEGINDATA
1002761000;134257;243125;0
1002761001;134258;243126;1

Kundenberater ctl-File

LOAD DATA
INFILE *
INTO TABLE kundenberater
FIELDS TERMINATED BY ′;′ OPTIONALLY ENCLOSED BY ′"′
(SVZNummer,BeraterId,Titel)
BEGINDATA
2902602000;243125;Mag Dr.
2902602001;243126;Mag

Person ctl-File

LOAD DATA
INFILE *
INTO TABLE person1
FIELDS TERMINATED BY ′;′ OPTIONALLY ENCLOSED BY ′"′
(SVZNummer,Name,Adresse)
BEGINDATA
1002761000;Za-Zong Zo;Grosse Augasse 3/1030 Wien
2902602000;Herbert Berater;Grosse Augasse 3/1030 Wien

Vertreibt ctl-File

LOAD DATA
INFILE *
INTO TABLE vertreibt
FIELDS TERMINATED BY ′;′ OPTIONALLY ENCLOSED BY ′"′
(WKN,Kuerzel)
BEGINDATA
10152;BKAUT
10153;BKVOB

Wertpapier ctl-File

LOAD DATA
INFILE *
INTO TABLE wertpapier
FIELDS TERMINATED BY ′;′ OPTIONALLY ENCLOSED BY ′"′
(WKN,Kurzbez,Wertpapierart,Nennwhg,klStueck)
BEGINDATA
10152;7.625% CA PF. R 52/92-02;Pfandbrief;ATS;10.000
10153;7.125% CA PF. R 53/93-03;Pfandbrief;ATS;10.000

Unix-Script Datensätze in Datenbank laden

sqlldr userid=a9726510/***** control=out_person.ctl log=out_person.log

sqlldr userid=a9726510/***** control=out_kundenberater.ctl log=out_kundenberater.log

sqlldr userid=a9726510/***** control=out_kunde.ctl log=out_kunde.log

sqlldr userid=a9726510/***** control=out_depot.ctl log=out_depot.log

sqlldr userid=a9726510/***** control=out_wertpapier.ctl log=out_wertpapier.log

sqlldr userid=a9726510/***** control=out_vertreibt.ctl log=out_vertreibt.log

Phase IV: BCNF und SQL-Abfragen

Person(SVZNummer,Name, Adresse)
Schlüssel: {SVZNummer}
Funkt. Abhängigkeiten: {SVZNummer _ NameAdresse}

Kunde (Kundennummer, SVZnummer, BeraterId, Bonifikation)
Schlüssel: {Kundennummer}
Funkt. Abhängigkeiten: {Kundennummer _ SVZnummerBeraterIdBonifikation}

Kundenberater (BeraterId, SVZnummer, Qualigrad, Titel)
Schlüssel: {BeraterId}
Funkt. Abhängigkeiten: {BeraterId _ SVZnummerQualigradTitel}

Order (OrderNr, Kundennummer, WKN, BeraterId, Status, Datum, DepotNr, Menge)
Schlüssel: {OrderNr}
Funkt. Abhängigkeiten: {OrderNr _ KundennummerWKNBeraterIdStatusDatum DepotNrMenge}

Depot (DepotNr, Kundennummer, Verrkonto)
Schlüssel: {DepotNr}
Funkt. Abhängigkeiten: {DepotNr _ KundennummerVerrkonto}

Wertpapier (WKN, Kurzbez, Wertpapierart, Nennwhg, klStueck)
Schlüssel: {WKN}
Funkt. Abhängigkeiten: {WKN _ KurzbezWertpapierartNennwhgklStueck}

Partner (Kuerzel, Name, Adresse)
Schlüssel: {Kuerzel}
Funkt. Abhängigkeiten: {Kuerzel _ NameAdresse}

liegt_auf (WKN, DepotNR, Wpmenge)
Schlüssel: {WKN, DepotNR}
Funkt. Abhängigkeiten: {WKNDepotNr _ Wpmenge}

vertreibt (WKN, Kuerzel)
Schlüssel: {WKN,Kuerzel}
Funkt. Abhängigkeiten: {keine}

Alles in BCNF und 3. Normalform

SQL-Abfragen:

1. Es soll von allen Kunden, die ,Zabel Hermann` heissen, deren Depots und zugehörige Verrechnungskonten angezeigt werden...
Für Zabel Hermann werden noch zwei Depots hinzugefügt...
{
insert into depot values(′00701501018′,′134276′,′00703601019′);
insert into depot values(′00701501025′,′134276′,′00703601019′);
}

_Name, DepotNr,Verrkonto(_kunde.kundennummer = depot.kundennummer ^ person1.SVZNummer = kunde.SVZNummer ^ kunde.name = `Zabel Hermann`(kunde * depot * person1))

Select Name, DepotNr, Verrkonto
From Depot,Kunde,Person1
Where kunde.kundennummer = depot.kundennummer AND Person1.SVZNummer = Kunde.SVZNummer AND Person1.name = `Zabel Hermann`;

Ausgabe in SQLPlus...
NAME DEPOTNR VERRKONTO
-------------------------------------------------- ---------- ----------
Zabel Hermann 703501019 703601019
Zabel Hermann 701501018 703601019
Zabel Hermann 701501025 703601019

2. Zu allen Wertpapieren, die auf dem Depot 00701501018 liegen, soll die jeweilige Wertpapierart und Kurzbezeichnung angezeigt werden...
Folgende Werte werden in die Relation liegt_auf und somit auf das Depot übertragen...
{
insert into liegt_auf values(′10543′,′00701501018′,′100′);
insert into liegt_auf values(′10648′,′00701501018′,′1000′);
insert into liegt_auf values(′11558′,′00701501018′,′10000′);
insert into liegt_auf values(′16852′,′00701501018′,′100′);
insert into liegt_auf values(′31202′,′00701501018′,′10′);
insert into liegt_auf values(′65208′,′00701501018′,′10000′);
insert into liegt_auf values(′65381′,′00701501018′,′10000′);
}

_Kurzbez, Wertpapierart(_Depot.DepotNr = liegt_auf.DepotNr ^ liegt_auf.WKN = Wertpapier.WKN ^ Depot.DepotNr = `00701501018`(depot * liegt_auf * Wertpapier))

Select Kurzbez, Wertpapierart
From Depot, liegt_auf, Wertpapier
Where Depot.DepotNR = liegt_auf.DepotNR AND liegt_auf.WKN = Wertpapier.WKN AND Depot.DepotNR = `00701501018`;

Ausgabe in SQLPlus...
KURZBEZ
--------------------------------------------------------------------------------
WERTPAPIERART
--------------------------------------------------
6% CA KB. R 43/96-03
Kommunalbrief

FRN VBG. PF R 48/79-09 VA
Pfandbrief

7.5% BGLD. KB R 58/86-01
Kommunalbrief

4,75% RAIFF.WOHN.WANDEL.99-09/3
Wohnbauwandelanleihe

5.375% BA WOHN.WANDEL.96-08/3
Wohnbauwandelanleihe

ERSTE BANK ST.(IPO) 100,-
Stammaktie

3-E HOLDING NAM.AKT.S1000
Stammaktie

3. Es soll zu einem Wertpapierberater (seiner Beraternummer) die Anzahl seiner betreuten Kunden angezeigt werden... (Auch Beispiel dafür, wo Relationenalgebra nicht geeignet ist...)

Select count(Name) AS BetreuteKunden
From Kundenberater, Kunde, Person1
Where Kundenberater.BeraterId = Kunde.BeraterId AND Person1.SVZNummer = Kundenberater.SVZNummer AND Kundenberater.BeraterId = `243135`;

BETREUTEKUNDEN
--------------
257

4. Zu einem bestimmten Wertpapier sollen alle vorhandenen Orders abgefragt werden...
Folgende Werte werden in die Relation Order1 eingetragen...
{
insert into Order1 values(′20011′,′134276′,′10543′,`243135`,`1`,`28-MAY-2001`,′00701501018′,`100`);
insert into Order1 values(′20012′,′134277′,′10543′,`243135`,`1`,`14-MAY-2001`,′00701501025′,`1000`);
}

_OrderNr, Kundennummer, WKN, BeraterId, Status, Datum, DepotNr, Menge(_WKN = `10543` (Order1))

select * from Order1
where WKN = `10543`;

ORDERNR KUNDENNUMMER WKN BERATERID STATUS DATUM DEPOTNR MENGE
---------- ------------ ---------- ---------- ---------- --------- ---------- ---------

20011 134276 10543 243135 1 28-MAY-01 701501018 100

20012 134277 10543 243135 1 28-MAY-01 701501025 1000

5. Die Summe aller Orders (Menge) zu einem Wertpapier(WKN 10543) auf einem Depot und weitere Daten, soll angezeigt werden...

Folgende Werte werden in die Relation Order1 eingetragen...
{
insert into Order1 values(′20013′,′134277′,′10543′,`243135`,`1`,`2-MAY-2001`,′00701501025′,`1000`);
insert into Order1 values(′20014′,′134276′,′10543′,`243135`,`1`,`13-MAY-2001`,′00701501018′,`150`);
}

select distinct DepotNr, Kundennummer, Kurzbez, SUM(Menge) AS Summe
from Order1,Wertpapier
where Order1.WKN = Wertpapier.WKN AND Order1.WKN = `10543`
group by DepotNr, Kundennummer, Kurzbez;

DEPOTNR KUNDENNUMMER KURZBEZ SUMME
---------- --------------------------------------------------------------------------------------------------

701501018 134276 6% CA KB. R 43/96-03 250

701501025 134277 6% CA KB. R 43/96-03 2000

6. Die Summe aller Orders (Menge) zu einem Wertpapier(WKN 10543) die von 1-15 Mai eingegangen sind...

Folgende Werte werden in die Relation Order1 eingetragen...
{
insert into Order1 values(′20015′,′134276′,′10543′,`243135`,`1`,`10-MAY-2001`,′00701501018′,`399`);
}

select distinct DepotNr, Kundennummer, Kurzbez, SUM(Menge) AS Summe
from Order1,Wertpapier
where Order1.WKN = Wertpapier.WKN AND Order1.WKN = `10543` AND Datum >=`01-MAY-2001` AND Datum < `15-MAY-2001`
group by DepotNr, Kundennummer, Kurzbez;

DEPOTNR KUNDENNUMMER KURZBEZ SUMME

701501018 134276 6% CA KB. R 43/96-03 549

701501025 134277 6% CA KB. R 43/96-03 1000

7. Selektiert die Order eines Wertpapiers mit der größten Menge und listet alle nur erdenklichen zugehörigen Kundendaten auf...

Folgende Werte werden in die Relation Order1 eingetragen...
{
insert into Order1 values(′20016′,′137331′,′10543′,`243131`,`1`,`28-MAY-2001`,`00703504074`,`100000`);
}

select Name,Adresse, WKN, Menge
from Person1,Kunde,Order1
where Order1.Kundennummer = Kunde.Kundennummer AND Kunde.SVZNummer = Person1.SVZNummer AND WKN = `10543` AND Menge = (
select MAX(Menge) from Order1);

NAME ADRESSE WKN MENGE
------------------------------------------------------------------------------------------- ----------
Zhong Grace Yi Qiu Schubertgasse 74/1050 Wien 10543 100000

8. Zeigt zu einer bestimmten Wertpapierkennnummer die Anzahl der aufgegebenen Orders an...

Select WKN, count(OrderNr) AS AufgegebeneOrders
From Order1
Where WKN = `10543`
Group by WKN;

WKN AUFGEGEBENEORDERS
---------- -----------------
10543 6

Die Beispiele 5-8 sind deshalb nicht für Relationenalgebra geeignet, da diese select′s gewisse Funktionen wie MAX oder SUM enthalten und diese mit Relationenalgebra nur schwer zu realisieren sind (siehe Übung)...

Phase V: WWW-basierte Applikation

Meine WWW-basierte Applikation findet sich auf der Internetseite

http://joplin.pri.univie.ac.at/~a9726510/index.html


Designentscheidungen: Bei meinem Wertpapierverwaltungssystem bot es sich an, eine Unterteilung des Ganzen in vier Einzelteile vorzunehmen. Diese wären:
· Kundenverwaltungssystem
· Depotverwaltungssystem
· Wertpapier/Partnerverwaltungssystem
· Orderverwaltungssystem

Alle Prozeduren bieten Exception-Handling und prüfen die eingegeben Daten auf Gültigkeit. Tritt ein Fehler auf wird der User durch eine individuelle Fehlernachricht verständigt.

Der Übersicht halber wird auf den Html-Code verzichtet und stattdessen Screenshots verwendet.
Es folgt eine detaillierte Beschreibung der einzelnen Teile...

Kundenverwaltungssystem

Hier besteht die Möglichkeit, Kundendaten zu suchen und zwar nach Name und/oder Kundennummer. Die Ergebnisseite wird daraufhin dynamisch generiert.


Hier werden alle relevanten Kundendaten, wie Name, Adresse, verschiedene Depotnummern angezeigt. Es besteht die Möglichkeit, durch den Link Datensatz ändern einen dieser Datensätze zu ändern...


Auch diese Seite wird dynamisch generiert und mit Ändern wird die Prozedur ,,kvwchange" aufgerufen, die die Änderung in der Datenbank vornimmt...

Das Interface

CREATE OR REPLACE PACKAGE kundenverwaltung AS

PROCEDURE kvwsearch(suchname IN VARCHAR2, kundenr IN VARCHAR2);
PROCEDURE kvwchange(kundenr IN VARCHAR2, suchname IN VARCHAR2, suchadr IN VARCHAR2, berat IN VARCHAR2, boni IN VARCHAR2);
PROCEDURE kvwupdate(kundenr IN VARCHAR2, suchname IN VARCHAR2, suchadr IN VARCHAR2, berat IN VARCHAR2, boni IN VARCHAR2);

END kundenverwaltung;
/
SHOW ERRORS

Die Prozeduren

CREATE OR REPLACE PACKAGE BODY kundenverwaltung AS

PROCEDURE kvwsearch(suchname IN VARCHAR2, kundenr IN VARCHAR2) IS

CURSOR Atn IS

Select name, adresse, bonifikation, depotnr, kunde.kundennummer, kunde.beraterid
From Kunde, Depot, Person1
Where Kunde.kundennummer = Depot.kundennummer
AND Kunde.svznummer = person1.svznummer
AND (name = suchname OR suchname IS NULL)
AND (kunde.kundennummer = kundenr OR kundenr IS NULL);

BEGIN

IF (suchname IS NULL AND kundenr IS NULL) THEN
htp.p(′<H2> Diese Abfrage bringt zu viele Ergebnisse... Schraenken Sie Ihre Suche ein! </H2>′);
ELSE
htp.p(′<HTML><HEAD><TITLE> Kunden Abfrage-Ergebnis</TITLE></HEAD><BODY>′);
htp.p(′<body background="http://joplin.pri.univie.ac.at/~a9726510/comp010.jpg">′);
htp.p(′<H2> <font color="#008080">′ || ′Treffer fuer die Suche′ || ′</font> </H2>′);
htp.p(′<H3>′ || suchname || ′ ′ || kundenr || ′</H3>′);
htp.p(′<TABLE BORDER=1>′);
htp.p(′<TR>′);
htp.p(′<TD>′ || ′NAME′);
htp.p(′<TD>′ || ′ADRESSE′);
htp.p(′<TD>′ || ′BONIFIKATION′);
htp.p(′<TD>′ || ′DEPOTNR′);
htp.p(′<TD>′ || ′KUNDENNUMMER′);

FOR Etn IN Atn LOOP
htp.p(′<TR>′);
htp.p(′<TD>′ || Etn.name);
htp.p(′<TD>′ || Etn.adresse);
htp.p(′<TD>′ || Etn.bonifikation);
htp.p(′<TD>′ || Etn.depotnr);
htp.p(′<TD>′ || Etn.kundennummer);
htp.p(′<TD> <a href="http://tupac.pri.univie.ac.at:8080/pls/de/a9726510.kundenverwaltung.kvwchange?kundenr=′ || Etn.kundennummer || ′&′ || ′suchname=′ || Etn.name || ′&′ || ′suchadr=′ || Etn.adresse || ′&′ || ′berat=′ || Etn.beraterid || ′&′ || ′boni=′ || Etn.bonifikation || ′"> Datensatz aendern </a>′);

END LOOP;
htp.p(′</TABLE>′);
END IF;

EXCEPTION
WHEN OTHERS THEN
htp.p(′<H2> Ein Fehler ist aufgetreten... Bitte ueberpruefen Sie Ihre Angaben! </H2>′);
htp.p(′</BODY>′);

END kvwsearch;

PROCEDURE kvwchange(kundenr IN VARCHAR2, suchname IN VARCHAR2, suchadr IN VARCHAR2, berat IN VARCHAR2, boni IN VARCHAR2) IS

BEGIN
htp.p(′<HTML><HEAD><TITLE> Kunden Abfrage-Ergebnis</TITLE></HEAD><BODY>′);
htp.p(′<body background="http://joplin.pri.univie.ac.at/~a9726510/comp010.jpg">′);

htp.p(′<form ACTION="http://tupac.pri.univie.ac.at:8080/pls/de/a9726510.kundenverwaltung.kvwupdate?kundenr=′ || kundenr || ′" METHOD="POST" name>′);

/* htp.p(′<p><em>Kundennummer (Nicht aendern!!)</em><br>′);*/
/* htp.p(′<input type="text" size="6" maxlength="6" name="kundenr" value="′ || kundenr || ′" > <br>′);*/

htp.p(′<em>Name</em><br>′);
htp.p(′<input type="text" size="30" maxlength="50" name="suchname" value="′ || suchname || ′"> <br>′);
htp.p(′<em><br>′);

htp.p(′<em>Adresse</em><br>′);
htp.p(′<input type="text" size="30" maxlength="50" name="suchadr" value="′ || suchadr || ′"> <br>′);
htp.p(′<em><br>′);

htp.p(′<em>BeraterId</em><br>′);
htp.p(′<input type="text" size="6" maxlength="6" name="berat" value="′ || berat || ′"> <br>′);
htp.p(′<em><br>′);

htp.p(′<em>Bonifikation</em><br>′);
htp.p(′<input type="text" size="2" maxlength="2" name="boni" value="′ || boni || ′"> <br>′);
htp.p(′<em><br>′);

htp.p(′</em><input type="submit" value="Aendern"> <br>′);
htp.p(′<br>′);
htp.p(′Mit Aendern bestaetigen Sie die Angaben... </p>′);
htp.p(′</form>′);

END kvwchange;

PROCEDURE kvwupdate(kundenr IN VARCHAR2, suchname IN VARCHAR2, suchadr IN VARCHAR2, berat IN VARCHAR2, boni IN VARCHAR2) IS

n INTEGER;

not_null_verletzt EXCEPTION;
PRAGMA EXCEPTION_INIT(not_null_verletzt, -1400);

BEGIN

htp.p(′<HTML><HEAD><TITLE> Kunden Daten-Aenderung</TITLE></HEAD><BODY>′);
htp.p(′<body background="http://joplin.pri.univie.ac.at/~a9726510/comp010.jpg">′);

select count(*) into n from kunde
where kundennummer = kundenr;

IF n = 0 THEN

htp.p(′<H3>′ || ′Trotz des ausdruecklichen Verbots konnten Sie nicht die Finger von der Kundennummer lassen!!′ || ′</H3>′);

ELSE

UPDATE person1 SET name = suchname, adresse = suchadr
WHERE svznummer =
(SELECT person1.svznummer
FROM person1, kunde
WHERE kunde.svznummer = person1.svznummer AND kunde.kundennummer = kundenr);

UPDATE kunde SET beraterid = berat, bonifikation = boni
WHERE kundennummer = kundenr;

htp.p(′<H2> <font color="#008080">′ || ′Kundendatenaenderung fuer Kunden ′ || kundenr || ′ erfolgreich! </font> </H2>′);

END IF;

EXCEPTION

WHEN dup_val_on_index THEN
htp.p(′<H2> <font color="#008080">′ || ′Kundendatenaenderung fuer Kunden ′ || kundenr || ′</font> </H2>′);
htp.p(′<H3>′ || ′Kundendaten konnten nicht geaendert werden, da ein UNIQUE-CONSTRAINT verletzt wurde′ || ′</H3>′);
htp.p(′</BODY>′);

WHEN not_null_verletzt THEN
htp.p(′<H2> <font color="#008080">′ || ′Kundendatenaenderung fuer Kunden ′ || kundenr || ′</font> </H2>′);
htp.p(′<H3>′ || ′Einem Not-Null Attribut wurde kein Wert zugewiesen′ || ′</H3>′);
htp.p(′</BODY>′);

WHEN OTHERS THEN
htp.p(′<H2> <font color="#008080">′ || ′Kundendatenaenderung fuer Kunden ′ || kundenr || ′</font> </H2>′);
htp.p(′<H3>′ || ′Ein Fehler ist bei der Anlage aufgetreten. Bitte ueberpruefen Sie Ihre Eingaben′ || ′</H3>′);
htp.p(′</BODY>′);

END kvwupdate;

END kundenverwaltung;
/
SHOW ERRORS

Depotverwaltungssystem


Im Depotverwaltungssystem besteht die Möglichkeit, Depotstände anzuzeigen oder zu einem Kunden ein neues Depot anzulegen.

Beispiel Depotstand anzeigen...

Das Interface

CREATE OR REPLACE PACKAGE depotverwaltung AS

PROCEDURE dvwshow(depnr IN VARCHAR2);
PROCEDURE dvwnew(depnr IN VARCHAR2, kundenr IN VARCHAR2, konto IN VARCHAR2);

END depotverwaltung;
/
SHOW ERRORS

Die Prozeduren

CREATE OR REPLACE PACKAGE BODY depotverwaltung AS

PROCEDURE dvwshow (depnr IN VARCHAR2) IS

CURSOR Ctn IS

Select Kurzbez, Wertpapierart,Wpmenge,Wertpapier.WKN
From Depot, liegt_auf, Wertpapier
Where Depot.DepotNR = liegt_auf.DepotNR
AND liegt_auf.WKN = Wertpapier.WKN
AND Depot.DepotNR = depnr;

name person1.name%TYPE;
adresse person1.adresse%TYPE;
verrkonto depot.verrkonto%TYPE;
n INTEGER;

BEGIN
htp.p(′<HTML><HEAD><TITLE>Depotstand Abfrage-Ergebnis</TITLE></HEAD><BODY>′);
htp.p(′<body background="http://joplin.pri.univie.ac.at/~a9726510/comp010.jpg">′);
SELECT count(*) INTO n
FROM depot
WHERE depotnr = depnr;

IF n = 0 THEN
htp.p(′<H2>Leider ist dieses Depot nicht vorhanden... Probieren Sie es bitte nocheinmal!</H>′);
ELSE
Select name, adresse, verrkonto
Into name, adresse, verrkonto
From Depot,Kunde,Person1
Where kunde.kundennummer = depot.kundennummer
AND Person1.SVZNummer = Kunde.SVZNummer
AND depot.depotnr = depnr;

htp.p(′<H2> <font color="#008080">′ || ′Depotstand zu Depotnummer ′ || depnr || ′</font> </H2>′);
htp.p(′<H3>′ || ′Verrechnungskonto ′ || verrkonto || ′</H3>′);
htp.p(′<H3>′ || ′Herr/Frau ′ || name || ′, wohnhaft ′ || adresse || ′</H3>′);

htp.p(′<TABLE BORDER=1>′);
htp.p(′<TR>′);
htp.p(′<TD>′ || ′KURZBEZEICHNUNG′);
htp.p(′<TD>′ || ′WERTPAPIERART′);
htp.p(′<TD>′ || ′MENGE′);
htp.p(′<TD>′ || ′KENNNUMMER′);

FOR Rtn IN Ctn LOOP
htp.p(′<TR>′);
htp.p(′<TD>′ || Rtn.kurzbez);
htp.p(′<TD>′ || Rtn.wertpapierart);
htp.p(′<TD>′ || Rtn.wpmenge);
htp.p(′<TD>′ || Rtn.wkn);

END LOOP;
htp.p(′</TABLE>′);
END IF;

EXCEPTION
WHEN OTHERS THEN
htp.p(′<H2> Ein Fehler ist aufgetreten... Bitte ueberpruefen Sie Ihre Angaben! </H2>′);
htp.p(′</BODY>′);

END dvwshow;

PROCEDURE dvwnew (depnr IN VARCHAR2, kundenr IN VARCHAR2, konto IN VARCHAR2) IS

not_null_verletzt EXCEPTION;
PRAGMA EXCEPTION_INIT (not_null_verletzt, -1400);

BEGIN
htp.p(′<HTML><HEAD><TITLE>Depotanlage</TITLE></HEAD><BODY>′);
htp.p(′<body background="http://joplin.pri.univie.ac.at/~a9726510/comp010.jpg">′);

IF (depnr > 700000000 AND depnr < 800000000 AND konto > 700000000 AND konto < 800000000) THEN

INSERT INTO depot (depotnr, kundennummer, verrkonto)
VALUES (depnr, kundenr, konto);

htp.p(′<H2> <font color="#008080">′ || ′Depotanlage des Depots ′ || depnr || ′</font> </H2>′);
htp.p(′<H3>′ || ′Das Depot wurde erfolgreich angelegt′ || ′</H3>′);
htp.p(′</BODY>′);

ELSE
htp.p(′<H3>′ || ′Bitte beachten Sie bei der Eingabe von Konto und Depotnummer folgendes Format:′ || ′</H3>′);
htp.p(′<H3>′ || ′Konto und Depotnummer muessen zwischen 700 000 000 und 800 000 000 liegen′ || ′</H3>′);
htp.p(′<H3>′ || ′Sie koennen Konto und Depotnummer vorne optional mit zwei Nullen angeben′ || ′</H3>′);
htp.p(′<H3>′ || ′Beispiel: Gueltige Nummer -> 00701501018 oder 701501018′ || ′</H3>′);
htp.p(′</BODY>′);

END IF;

EXCEPTION

WHEN dup_val_on_index THEN
htp.p(′<H2> <font color="#008080">′ || ′Depotanlage des Depots ′ || depnr || ′</font> </H2>′);
htp.p(′<H3>′ || ′Depot konnte nicht angelegt werden, da ein UNIQUE-CONSTRAINT verletzt wurde′ || ′</H3>′);
htp.p(′</BODY>′);

WHEN not_null_verletzt THEN
htp.p(′<H2> <font color="#008080">′ || ′Depotanlage des Depots ′ || depnr || ′</font> </H2>′);
htp.p(′<H3>′ || ′Einem Not-Null Attribut wurde kein Wert zugewiesen′ || ′</H3>′);
htp.p(′</BODY>′);

WHEN OTHERS THEN
htp.p(′<H2> <font color="#008080">′ || ′Depotanlage des Depots ′ || depnr || ′</font> </H2>′);
htp.p(′<H3>′ || ′Ein Fehler ist bei der Anlage aufgetreten. Bitte ueberpruefen Sie Ihre Eingaben′ || ′</H3>′);
htp.p(′</BODY>′);

END dvwnew;

END depotverwaltung;
/
SHOW ERRORS

Wertpapier/Partnerverwaltungssystem


Es können alle Wertpapiere der Datenbank und zugehörige Handelspartner abgefragt werden...

Das Interface

CREATE OR REPLACE PACKAGE wpverwaltung AS

PROCEDURE wpvwshow(wertkn IN VARCHAR2, wertart IN VARCHAR2, whg IN VARCHAR2);

END wpverwaltung;
/
SHOW ERRORS

Die Prozeduren

CREATE OR REPLACE PACKAGE BODY wpverwaltung AS

PROCEDURE wpvwshow (wertkn IN VARCHAR2 DEFAULT NULL, wertart IN VARCHAR2 DEFAULT NULL, whg IN VARCHAR2 DEFAULT NULL) IS

CURSOR Dtn IS

Select vertreibt.WKN, kurzbez, partner.name, partner.adresse
From Wertpapier, vertreibt, Partner
Where Wertpapier.WKN = vertreibt.WKN
AND vertreibt.Kuerzel = Partner.Kuerzel
AND (Wertpapier.WKN = wertkn OR wertkn IS NULL)
AND (Wertpapierart = wertart OR wertart IS NULL)
AND (Nennwhg = whg OR whg IS NULL);

BEGIN

IF (wertkn IS null AND wertart IS NULL AND whg IS null) THEN
htp.p(′<H2> Diese Abfrage bringt zu viele Ergebnisse... Schraenken Sie Ihre Suche ein! </H2>′);
ELSE
htp.p(′<HTML><HEAD><TITLE> Wertpapier Abfrage-Ergebnis</TITLE></HEAD><BODY>′);
htp.p(′<body background="http://joplin.pri.univie.ac.at/~a9726510/comp010.jpg">′);
htp.p(′<H2> <font color="#008080">′ || ′Treffer fuer die Suche′ || ′</font> </H2>′);
htp.p(′<H3>′ || wertkn || ′ ′ || wertart || ′ ′ || whg ||′</H3>′);
htp.p(′<TABLE BORDER=1>′);

htp.p(′<TR>′);
htp.p(′<TD>′ || ′WKN′);
htp.p(′<TD>′ || ′BEZEICHNUNG′);
htp.p(′<TD>′ || ′HANDELSPARTNER′);
htp.p(′<TD>′ || ′ADRESSE′);

FOR Utn IN Dtn LOOP
htp.p(′<TR>′);
htp.p(′<TD>′ || Utn.wkn);
htp.p(′<TD>′ || Utn.kurzbez);
htp.p(′<TD>′ || Utn.name);
htp.p(′<TD>′ || Utn.adresse);
END LOOP;
htp.p(′</TABLE>′);
END IF;

EXCEPTION
WHEN OTHERS THEN
htp.p(′<H2> Ein Fehler ist aufgetreten... Bitte ueberpruefen Sie Ihre Angaben! </H2>′);
htp.p(′</BODY>′);

END wpvwshow;

END wpverwaltung;
/
SHOW ERRORS

Orderverwaltungssystem

Das aufwendigste System ist das Orderverwaltungssystem. Hier bietet sich die Möglichkeit Orders zu erfassen, anschließend zu suchen und in einem weiteren Dialog dann die gefundenen Daten auszuführen (führt zu einer Änderung des Depotstandes - Depotverwaltungssystem) oder zu stornieren (führt zu einer Löschung der Order - dürfte im Echtbetrieb nicht einfach gelöscht werden...).

EIN EINFACHER GESCHÄFTSFALL

Order anlegen

Die entsprechenden Daten werden in das Formular eingegeben und anschließend wird mit neu anlegen bestätigt. Anschließend kann man die Order suchen


Anschließende Suche mit der Ordernummer (könnte auch nach Datum oder Status = 1 gesucht werden...)

Die Suche liefert alle möglichen Treffer

Klickt man auf ausführen wird der Auftrag gebucht.

Der neue Depotstand beträgt nun 65 (vorher 50).

Das Interface

CREATE OR REPLACE PACKAGE orderverwaltung AS

PROCEDURE ovwsearch(ordnr IN VARCHAR2, knr IN VARCHAR2, wertkn IN VARCHAR2, depnr IN VARCHAR2, stat IN VARCHAR2, dat IN VARCHAR2, amount IN VARCHAR2);
PROCEDURE ovwproceed(ordnr IN VARCHAR2);
PROCEDURE ovwcancel(ordnr IN VARCHAR2);
PROCEDURE ovwnew(ordnr IN VARCHAR2, wertkn IN VARCHAR2, amount IN VARCHAR2, knr IN VARCHAR2, depnr IN VARCHAR2, beratid IN VARCHAR2);

END orderverwaltung;
/
SHOW ERRORS

Die Prozeduren

CREATE OR REPLACE PACKAGE BODY orderverwaltung AS

PROCEDURE ovwsearch(ordnr IN VARCHAR2, knr IN VARCHAR2, wertkn IN VARCHAR2, depnr IN VARCHAR2, stat IN VARCHAR2, dat IN VARCHAR2, amount IN VARCHAR2) IS

CURSOR Atn IS

Select ordernr, name, adresse, depotnr, WKN, Menge, Datum, Status
From Kunde, Person1, Order1
Where Kunde.SVZNummer = Person1.SVZNummer
AND Kunde.Kundennummer = Order1.Kundennummer
AND (orderNR = ordnr OR ordnr IS NULL)
AND (kunde.kundennummer = knr OR knr IS NULL)
AND (WKN = wertkn OR wertkn IS NULL)
AND (Order1.depotnr = depnr OR depnr IS NULL)
AND (status = stat OR stat IS NULL)
AND (datum = dat OR dat IS NULL)
AND (menge = amount OR amount IS NULL);


BEGIN

htp.p(′<HTML><HEAD><TITLE> Order Abfrage-Ergebnis</TITLE></HEAD><BODY>′);
htp.p(′<body background="http://joplin.pri.univie.ac.at/~a9726510/comp010.jpg">′);
htp.p(′<H2> <font color="#008080">′ || ′Treffer fuer die Suche′ || ′</font> </H2>′);
htp.p(′<H3>′ || ordnr || ′ ′ || knr || ′ ′ || wertkn || ′ ′ || depnr || ′ ′ || stat || ′ ′ || dat || ′ ′ || amount ||′</H3>′);
htp.p(′<TABLE BORDER=1>′);
htp.p(′<TR>′);
htp.p(′<TD>′ || ′NAME′);
htp.p(′<TD>′ || ′ADRESSE′);
htp.p(′<TD>′ || ′DEPOTNR′);
htp.p(′<TD>′ || ′WKN′);
htp.p(′<TD>′ || ′MENGE′);
htp.p(′<TD>′ || ′DATUM′);
htp.p(′<TD>′ || ′STATUS′);

FOR Etn IN Atn LOOP
htp.p(′<TR>′);
htp.p(′<TD>′ || Etn.name);
htp.p(′<TD>′ || Etn.adresse);
htp.p(′<TD>′ || Etn.depotnr);
htp.p(′<TD>′ || Etn.WKN);
htp.p(′<TD>′ || Etn.menge);
htp.p(′<TD>′ || Etn.datum);
htp.p(′<TD>′ || Etn.status);

IF (Etn.status = 1) THEN
htp.p(′<TD> <a href="http://tupac.pri.univie.ac.at:8080/pls/de/a9726510.orderverwaltung.ovwproceed?ordnr=′ || Etn.ordernr || ′"> Ausfuehren </a>′);
htp.p(′<TD> <a href="http://tupac.pri.univie.ac.at:8080/pls/de/a9726510.orderverwaltung.ovwcancel?ordnr=′ || Etn.ordernr || ′"> Stornieren </a>′);
END IF;

END LOOP;
htp.p(′</TABLE>′);

EXCEPTION
WHEN OTHERS THEN
htp.p(′<H2> Ein Fehler ist aufgetreten... Bitte ueberpruefen Sie Ihre Angaben! </H2>′);
htp.p(′</BODY>′);

END ovwsearch;

PROCEDURE ovwproceed(ordnr IN VARCHAR2) IS

ordernr1 order1.ordernr%TYPE;
wknorder order1.WKN%TYPE;
wknliegtauf liegt_auf.wkn%TYPE;
depotnrorder order1.DepotNr%TYPE;
depotnrliegtauf liegt_auf.DepotNr%TYPE;
ordermenge order1.menge%TYPE;
liegtaufmenge liegt_auf.Wpmenge%TYPE;
n INTEGER;
m INTEGER;
Summe INTEGER;

BEGIN
htp.p(′<HTML><HEAD><TITLE> Auftrag Ausführung</TITLE></HEAD><BODY>′);
htp.p(′<body background="http://joplin.pri.univie.ac.at/~a9726510/comp010.jpg">′);

select count(*) into n from order1
where ordernr = ordnr and status = ′1′;

IF n = 0 THEN
htp.p(′<H3>′ || ′Die Order ist verschwunden oder bereits ausgefuehrt′ || ′</H3>′);

ELSE

Select ordernr, order1.WKN, order1.Depotnr, order1.menge
Into ordernr1, wknorder, depotnrorder, ordermenge
From order1
Where Ordernr = ordnr;

Select count(*) into m from liegt_auf
Where WKN = wknorder AND DepotNr = depotnrorder;

IF (m = 0) THEN

insert into liegt_auf (WKN, DepotNr, WpMenge)
values(wknorder, depotnrorder, ordermenge);
UPDATE order1 SET status = ′2′
WHERE ordernr = ordernr1;
htp.p(′<H2> <font color="#008080">′ || ′Der Auftrag Nummer ′ || ordnr || ′ wurde erfolgreich ausgefuehrt und liegt nun am Depot des Kunden! </font> </H2>′);

ELSE

Select Wpmenge
Into liegtaufmenge
From liegt_auf
Where WKN = wknorder AND DepotNr = depotnrorder;

Summe := ordermenge + liegtaufmenge;
UPDATE liegt_auf SET WpMenge = Summe
WHERE DepotNr = depotnrorder AND WKN = wknorder;
UPDATE order1 SET status = ′2′
WHERE ordernr = ordernr1;
htp.p(′<H2> <font color="#008080">′ || ′Der Auftrag Nummer ′ || ordnr || ′ wurde erfolgreich ausgefuehrt und der Depotstand aktualisiert! </font> </H2>′);

END IF;

END IF;

EXCEPTION

WHEN dup_val_on_index THEN
htp.p(′<H3>′ || ′Order konnte nicht verarbeitet werden, da ein UNIQUE-CONSTRAINT verletzt wurde′ || ′</H3>′);
htp.p(′</BODY>′);

WHEN OTHERS THEN
htp.p(′<H3>′ || ′Ein sehr kritischer Fehler ist aufgetreten! Erbitte debugging′ || ′</H3>′);
htp.p(′</BODY>′);

END ovwproceed;

PROCEDURE ovwcancel(ordnr IN VARCHAR2) IS

ordernr1 order1.ordernr%TYPE;
n INTEGER;

BEGIN
htp.p(′<HTML><HEAD><TITLE> Auftrag Storno</TITLE></HEAD><BODY>′);
htp.p(′<body background="http://joplin.pri.univie.ac.at/~a9726510/comp010.jpg">′);

select count(*) into n from order1
where ordernr = ordnr and status = ′1′;

IF n = 0 THEN
htp.p(′<H3>′ || ′Die Order ist verschwunden oder bereits ausgefuehrt′ || ′</H3>′);

ELSE

Select ordernr
into ordernr1
From Order1
Where Ordernr = ordnr;

delete order1
where ordernr = ordnr;

htp.p(′<H2> <font color="#008080">′ || ′Der Auftrag Nummer ′ || ordnr || ′ wurde storniert! </font> </H2>′);

END IF;

EXCEPTION

WHEN OTHERS THEN
htp.p(′<H3>′ || ′Ein sehr kritischer Fehler ist aufgetreten! Erbitte debugging′ || ′</H3>′);
htp.p(′</BODY>′);

END ovwcancel;

PROCEDURE ovwnew (ordnr IN VARCHAR2, wertkn IN VARCHAR2, amount IN VARCHAR2, knr IN VARCHAR2, depnr IN VARCHAR2, beratid IN VARCHAR2) IS

not_null_verletzt EXCEPTION;
PRAGMA EXCEPTION_INIT (not_null_verletzt, -1400);

n INTEGER;
m INTEGER;
o INTEGER;
datumformat DATE;

BEGIN
htp.p(′<HTML><HEAD><TITLE>Orderanlage</TITLE></HEAD><BODY>′);
htp.p(′<body background="http://joplin.pri.univie.ac.at/~a9726510/comp010.jpg">′);

select count(*) into n from wertpapier
where wkn = wertkn;
select count(*) into m from kunde, depot
where depot.kundennummer = kunde.kundennummer
AND depot.depotnr = depnr AND kunde.kundennummer = knr;
select count(*) into o from kundenberater
where beraterid = beratid;

IF (n = 1 AND m = 1 AND o = 1) THEN

SELECT to_date(SYSDATE,′DD-MM-YY′)
into datumformat
from dual;

INSERT INTO order1(ordernr, kundennummer, WKN, BeraterId, Status, Datum, DepotNr, Menge)
VALUES (ordnr, knr, wertkn, beratId, ′1′, datumformat, depnr, amount);

htp.p(′<H2> <font color="#008080">′ || ′Depotanlage der Order ′ || ordnr || ′</font> </H2>′);
htp.p(′<H3>′ || ′Die Order wurde erfolgreich angelegt′ || ′</H3>′);
htp.p(′</BODY>′);

ELSE
htp.p(′<H3>′ || ′Bitte beachten Sie bei der Eingabe der Daten folgende Hinweise:′ || ′</H3>′);
htp.p(′<H3>′ || ′Kundennummer, Wertpapierkennnummer, Beraterid und Depotnummer muessen vorhanden sein′ || ′</H3>′);
htp.p(′<H3>′ || ′Obgenannte Attribute muessen zusammenpassen d.h. sie koennen keine Order′ || ′</H3>′);
htp.p(′<H3>′ || ′fuer Kunden XY anlegen und das Depot des Kunden Z angeben!!′ || ′</H3>′);
htp.p(′</BODY>′);

END IF;

EXCEPTION

WHEN dup_val_on_index THEN
htp.p(′<H2> <font color="#008080">′ || ′Depotanlage des Depots ′ || depnr || ′</font> </H2>′);
htp.p(′<H3>′ || ′Order konnte nicht angelegt werden, da Ordernummer schon vorhanden′ || ′</H3>′);
htp.p(′</BODY>′);

WHEN not_null_verletzt THEN
htp.p(′<H2> <font color="#008080">′ || ′Depotanlage des Depots ′ || depnr || ′</font> </H2>′);
htp.p(′<H3>′ || ′Einem Not-Null Attribut wurde kein Wert zugewiesen′ || ′</H3>′);
htp.p(′</BODY>′);

WHEN OTHERS THEN
htp.p(′<H2> <font color="#008080">′ || ′Depotanlage des Depots ′ || depnr || ′</font> </H2>′);
htp.p(′<H3>′ || ′Ein Fehler ist bei der Anlage aufgetreten. Bitte ueberpruefen Sie Ihre Eingaben′ || ′</H3>′);
htp.p(′</BODY>′);

END ovwnew;

END orderverwaltung;
/
SHOW ERRORS

THE END

Häufig gestellte Fragen

Was ist der Zweck dieses Dokuments?

Dieses Dokument beschreibt ein Datenbankprojekt zur Verwaltung von Wertpapieraufträgen. Es enthält die Projektbeschreibung, das E/R-Modell, Relationenschemata, SQL-Datenbankschema, Datengenerierung, Normalisierung, SQL-Abfragen und eine WWW-basierte Applikation.

Welche Entitäten werden im E/R-Modell berücksichtigt?

Die wichtigsten Entitäten sind Person, Kunde, Kundenberater, Depot, Wertpapier, Order und Partner.

Welche Beziehungen bestehen zwischen den Entitäten?

Eine Person kann ein Kunde oder ein Kundenberater sein. Ein Kunde wird von einem Kundenberater betreut und hat Depots. Eine Order enthält ein Wertpapier und wird von einem Kundenberater betreut. Ein Wertpapier kann von verschiedenen Partnern gekauft werden und liegt in einem Depot.

Was sind die Primärschlüssel der einzelnen Relationen?

  • Kunde: Kundennummer
  • Kundenberater: Beraternummer
  • Order: OrderNr
  • Depot: DepotNr
  • Wertpapier: WKN
  • Partner: Kuerzel
  • liegt_auf: (WKN, DepotNR)
  • vertreibt: (WKN, Kuerzel)
  • PERSON1: SVZNummer

Wie wurden die Testdaten generiert?

Die Testdaten wurden zunächst in einer Excel-Tabelle erstellt und anschließend als Textdatei exportiert. Ein Java-Programm generiert dann die ctl-Files für den Dbload.

Welche Normalform erfüllen die Relationen?

Alle Relationen erfüllen die BCNF (Boyce-Codd Normal Form) und die 3. Normalform.

Welche SQL-Abfragen werden im Dokument demonstriert?

Das Dokument demonstriert verschiedene SQL-Abfragen, darunter:

  • Anzeigen von Depots und Verrechnungskonten von Kunden mit einem bestimmten Namen
  • Anzeigen von Wertpapierart und Kurzbezeichnung für Wertpapiere auf einem bestimmten Depot
  • Anzeigen der Anzahl der betreuten Kunden eines Wertpapierberaters
  • Abfragen aller Orders zu einem bestimmten Wertpapier
  • Anzeigen der Summe aller Orders (Menge) zu einem Wertpapier auf einem Depot
  • Anzeigen der Summe aller Orders (Menge) zu einem Wertpapier, die in einem bestimmten Zeitraum eingegangen sind
  • Selektieren der Order eines Wertpapiers mit der größten Menge und Auflisten der zugehörigen Kundendaten
  • Anzeigen der Anzahl der aufgegebenen Orders zu einer bestimmten Wertpapierkennnummer

Was beinhaltet die WWW-basierte Applikation?

Die WWW-basierte Applikation ist eine Wertpapierverwaltungssystem unterteilt in:

  • Kundenverwaltungssystem
  • Depotverwaltungssystem
  • Wertpapier/Partnerverwaltungssystem
  • Orderverwaltungssystem

Welche Funktionen bietet das Orderverwaltungssystem?

Das Orderverwaltungssystem bietet die Möglichkeit, Orders zu erfassen, zu suchen, auszuführen (führt zu einer Änderung des Depotstandes) oder zu stornieren (führt zu einer Löschung der Order).

Excerpt out of 39 pages  - scroll top

Buy now

Title: Grundzüge der Ökonomie - Varian

Script , 2000 , 39 Pages

Autor:in: Andreas Unterweger (Author)

Computer Science - Applied
Look inside the ebook

Details

Title
Grundzüge der Ökonomie - Varian
Author
Publication Year
2000
Pages
39
Catalog Number
V105905
ISBN (eBook)
9783640041848
Language
German
Tags
Data Engineering Datenbanken oracle pl sql er diagramm system verwaltungssystem html
Product Safety
GRIN Publishing GmbH
Quote paper
Andreas Unterweger (Author), 2000, Grundzüge der Ökonomie - Varian, Munich, GRIN Verlag, https://www.grin.com/document/105905