Beispiellösung

Institut für Informationssysteme
Dr. C. Türker
Eidgenössische Technische Hochschule Zürich
Swiss Federal Institute of Technology Zurich
a) Bilden Sie jede Tabelle des Datenbestands mittels einer Standardabbildung auf XMLElemente ab. Skizzieren sie die Abbildung der Tabellen jeweils am Beispiel einer Tabellenzeile.
Wir verwenden im Folgenden die Standardabbildung mittels Elementen und Attributen.
Dadurch erhalten wir das folgende XML-Dokument für den Datenaustausch:
XML und Datenbanken WS 04/05
Übung 8
Beispiellösung
Abbildung von Datenbanken auf XML
In der Computerfirma CleverSoft sind Sie für das Datenbanksystem zur Verwaltung der Kundendaten, Aufträge, und Bestellungen verantwortlich. Abbildung 1 zeigt die Datenbank. Die
Geschäftsleitung hat kürzlich den Wechsel auf ein Datenbanksystem eines anderen Herstellers
beschlossen.
lineitem
region
nation
orderkey
linenumber
part
supplier
extendedprice
quantity
0815
1
PlayStation
Sony
255.23
34
0815
2
XBox
Microsoft
369.10
13
4711
1
Moorhuhn 3
MediaSoft
15.00
1
regionkey
name
orderkey
custkey
orderdate
totalprice
1
Europe
order
0815
HJS
22.12.2001
13476.12
2
Africa
4711
TG
6.12.2002
15.00
3
North America
nationkey
name
regionkey
CH
Switzerland
1
DE
Germany
1
USA
United
States of
America
customer
3
custkey
name
address
nationkey
acctbal
HJS
Hans-Jörg
Schek
ETH
Zentrum
CH
15’000.00
TG
Torsten
Grabs
ETH
Zentrum
CH
-1’500.00
Abbildung 1: Ausschnitt aus der Kundendatenbank von CleverSoft
Aufgabe 1: Standardabbildung von Datenbanken
Sie sind nun dafür verantwortlich, die Daten aus dem Altsystem zu exportieren und in das
neue System einzubringen. Hierzu wollen Sie das Austauschformat XML einsetzen. Da Sie
die Möglichkeiten des neuen Systems noch nicht genau kennen, möchten Sie die Daten in
unterschiedlichen Darstellungen exportieren:
1
<CleverSoftDB>
<lineitem
orderkey="0815" linenumber="1" part="PlayStation"
supplier="Sony" extendedprice="255.23" quantity="34" />
...
<region regionkey="1" name="Europe" />
...
<order orderkey="0815" custkey="HJS"
orderdate="22.12.2001" totalprice="13476.12" />
...
<nation
nationkey="CH" name="Switzerland" regionkey="1" />
...
<customer
custkey="HJS" name="Hans-Jörg Schek"
address="ETH Zentrum" nationkey="CH"
acctbal="15’000.00" />
</CleverSoftDB>
b) Bilden Sie den Datenbestand auf ein XML-Dokument ab, das die Schlüssel-Fremdschlüsselbeziehungen der Daten möglichst adäquat abbildet.
Aufbauend auf dem oben schon gewählten Ansatz haben wir eine Hierarchie gewählt, die
beginnend mit den Regionen über die Kunden zu den einzelnen Bestellposten führt. Damit
ergibt sich das folgende XML-Dokument:
<CleverSoftDB>
<region regionkey="1" name="Europe">
<nation nationkey="CH" name="Switzerland">
<customer custkey="HJS" name="Hans-Jörg Schek"
address="ETH Zentrum"
acctbal="15’000.00">
<order orderkey="0815" orderdate="22.12.2001"
totalprice="13476.12">
<lineitem linenumber="1"
part="PlayStation" supplier="Sony"
extendedprice="255.23" quantity="34" />
...
</order>
...
</customer>
...
</nation>
...
</region>
2
...
</CleverSoftDB>
Eine wichtige Beobachtung ist hier, dass wir die Schlüssel-Fremdschlüssel-Beziehungen nicht
mehr explizit mitführen müssen, da diese Information schon von der Hierarchie und der
Schachtelung der XML-Elemente abgedeckt wird.
SELECT region.name AS regionname, sum(order.totalprice) AS regionrevenue
FROM order, customer, nation, region
WHERE order.custkey = customer.custkey
AND
customer.nationkey = nation.nationkey
AND
nation.regionkey = region.regionkey
AND
order.orderdate >= ’1.1.2002’
AND
order.orderdate <= ’31.12.2002’
GROUPBY region.name
Aufgabe 2: Abbildung von Anfrageergebnissen
Auf dem Datenbestand aus Abbildung 1 muss für die Steuerbehörde der Umsatz des Jahres
2002 nach Regionen gegliedert bestimmt werden. Dazu führen Sie den folgenden SQL-Befehl
aus:
SELECT region.name AS regionname, sum(order.totalprice) AS regionrevenue
FROM order, customer, nation, region
WHERE order.custkey = customer.custkey
AND
customer.nationkey = nation.nationkey
AND
nation.regionkey = region.regionkey
AND
order.orderdate >= ’1.1.2002’
AND
order.orderdate <= ’31.12.2002’
GROUP BY region.name
construct
<UmsatzPerRegion>
<Region>{$regionname}</Region>
<Umsatz>{$regionrevenue}<Umsatz>
</UmsatzPerRegion>
Aufgabe 3: Abbildung von SQL-Tabellen und SQL-Schemata
Manager
OID
StaffNr
Name
Salary
Status
1
0815
Janine McDivine
100.000
Partner
2
0817
Dr. Heinz Kerbholz
75.000
Senior
a) Setzen Sie das Ergebnis der Anfrage mittels einer Standardabbildung in das Dokumentformat XML um.
Wir wählen eine Abbildung mit XML-Elementen wie in den Vorlesungsunterlagen.
<rowset>
<row no="1">
<regionname> Europe </regionname>
<regionrevenue> 15.00 </regionrevenue>
</row>
</rowset>
Project
OID
ProjectNr Name
3
4711
Manager
Crap‘n‘Crazy 0815
StaffCount
750
4
4712
InternetKiosk 0817
5
7
5522
Acquisition
1
0817
Abbildung 2: Die Manager-Projekte-Datenbank aus der ersten Übung
b) Durch die Steuerbehörde wird folgende DTD vorgegeben:
<?xml version="1.0" encoding="UTF-8"?>
<!ELEMENT UmsatzPerRegion (Region, Umsatz>
<!ELEMENT Region
(#PCDATA)>
<!ELEMENT Umsatz
(#PCDATA)>
Eingaben, die der DTD nicht genügen, werden von der Steuerbehörde nicht mehr akzeptiert.
Wie gehen Sie nun vor, um den Vorgaben der Steuerbehörde zu genügen?
Um für die Abfrage XML-Dokumente zu generieren, die speziellen Formatvorgaben genügen,
muss im allgemeinen auf individuelle Abbildungsvorschriften zurückgegriffen werden. Hier
erscheinen die Varianten mittels einer erweiterten Datenbankanfrage oder über ein XSLTStylesheet am angemessensten. Im Folgenden wählen wir den Ansatz mittels einer erweiterten Datenbankanfrage in dem aus der Vorlesung bekannten Dialekt:
3
Abbildung 2 zeigt die Beispieldaten aus der ersten Übung. Das Schema dieser Datenbank sei
mit den folgenden SQL-DDL-Befehlen gegeben:
CREATE TABLE Manager
(
OID
INTEGER PRIMARY KEY,
StaffNr
INTEGER UNIQUE,
Name
VARCHAR(50),
Salary
DECIMAL(12,2),
Status
VARCHAR(20)
);
4
CREATE TABLE Project
(
OID
INTEGER PRIMARY KEY,
ProjectNr
INTEGER UNIQUE,
Name
VARCHAR(50),
Manager
INTEGER,
StaffCount INTEGER,
FOREIGN KEY(Manager) REFERENCES Manager(StaffNr)
ON DELETE CASCADE
);
a) Bilden Sie das Schema der Datenbank mittels des SQL/XML-Standards auf ein XMLSchema ab. Welche Informationen aus der SQL-DDL konnten Sie nicht umsetzen?
Das folgende XML-Schema entspricht der Umsetzung mittels SQL/XML. Verloren gehen
dabei die in der DDL definierten Integritätsbedingungen.
<!-- Definition der Datenbankdatentypen -->
<xsd:simpleType name="INTEGER">
<xsd:annotation>
<xsd:appinfo>
<sqlxml:sqltype
kind="PREDEFINED"
name="INTEGER"
</xsd:appinfo>
</xsd:annotation>
<xsd:restriction base="xsd:integer">
<xsd:minInclusive value="-2147483648"/>
<xsd:maxInclusive value="2147483647"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="VARCHAR_50">
<xsd:annotation>
<xsd:appinfo>
<sqlxml:sqltype
kind="PREDEFINED"
name="VARCHAR"
maxlength="50"
</xsd:appinfo>
</xsd:annotation>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="50"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="NUMERIC_12_2">
5
<xsd:annotation>
<xsd:appinfo>
<sqlxml:sqltype
kind="PREDEFINED"
name="NUMERIC"
userPrecision="12"
scale="2"/>
</xsd:appinfo>
</xsd:annotation>
<xsd:restriction base="xsd:numeric">
<xsd:length value="13"/>
<xsd:scale value="2"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="VARCHAR_20">
<xsd:annotation>
<xsd:appinfo>
<sqlxml:sqltype
kind="PREDEFINED"
name="VARCHAR"
maxlength="20"
</xsd:appinfo>
</xsd:annotation>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="20"/>
</xsd:restriction>
</xsd:simpleType>
<!-- Definition der ROW-Typen für die Tabellen -->
<xsd:complexType name="ROW.MANAGER">
<xsd:sequence>
<xsd:element name="OID"
type="INTEGER"/>
<xsd:element name="STAFFNR"
type="INTEGER"/>
<xsd:element name="NAME"
type="VARCHAR_50"/>
<xsd:element name="SALARY"
type="NUMERIC_12_2"/>
<xsd:element name="STATUS"
type="VARCHAR_20"/>
</xsd:sequence>
</xsd:complexType>
<!-- Definition der Tabellen selbst -->
<xsd:complexType name="TABLE.MANAGER">
<xsd:annotation>
<xsd:appinfo>
6
<sqlxml:sqlname type="BASE TABLE" localName="MANAGER"/>
</xsd:appinfo>
</xsd:annotation>
<xsd:sequence>
<xsd:element name="row" type="ROW.MANAGER"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="ROW.PROJECT">
<xsd:sequence>
<xsd:element name="OID"
<xsd:element name="PROJECTNR"
<xsd:element name="NAME"
<xsd:element name="MANAGER"
<xsd:element name="STAFFCOUNT"
</xsd:sequence>
</xsd:complexType>
type="INTEGER"/>
type="INTEGER"/>
type="VARCHAR_50"/>
type="INTEGER"/>
type="INTEGER"/>
<xsd:complexType name="TABLE.PROJECT">
<xsd:annotation>
<xsd:appinfo>
<sqlxml:sqlname type="BASE TABLE" localName="PROJECT"/>
</xsd:appinfo>
</xsd:annotation>
<xsd:sequence>
<xsd:element name="row" type="ROW.PROJECT"/>
</xsd:sequence>
</xsd:complexType>
b) Bilden Sie den Datenbestand mittels des SQL/XML-Standards und des von Ihnen oben
erstellten SQL-Schemas auf ein XML-Dokument ab. Welche Information geht bei der Abbildung verloren?
Das folgende XML-Fragment zeigt die Standard-Abbildung der Tabellen. Wie schon in
der letzten Übung diskutiert, wird in dieser Art der Standard-Abbildung die Information
über Schlüssel-Fremdschlüsselbeziehungen nicht in das Dokument übernommen. Hier hilft
lediglich das Wissen über die Anwendung, oder explizite Annotationen des Dokuments, die
der Standard allerdings nicht vorsieht.
<MANAGER>
<row>
<OID> 1 </OID>
<STAFFNR> 0815 </STAFFNR>
<NAME> Janine McDivine </NAME>
<SALARY> 100.000 </SALARY>
<STATUS> Partner </STATUS>
</row>
7
<row>
<OID> 2 </OID>
<STAFFNR> 0817 </STAFFNR>
<NAME> Dr. Heinz Kerbholz </NAME>
<SALARY> 75.000 </SALARY>
<STATUS> Senior </STATUS>
</row>
</MANAGER>
<PROJECT>
<row>
<OID> 3 </OID>
<PROJECTNR> 4711 </PROJECTNR>
<NAME> Crap’n’Crazy </NAME>
<MANAGER> 0815 </MANAGER>
<STAFFCOUNT> 750 </STAFFCOUNT>
</row>
<row>
<OID> 4 </OID>
<PROJECTNR> 4712 </PROJECTNR>
<NAME> InternetKiosk </NAME>
<MANAGER> 0817 </MANAGER>
<STAFFCOUNT> 5 </STAFFCOUNT>
</row>
<row>
<OID> 7 </OID>
<PROJECTNR> 5522 </PROJECTNR>
<NAME> Acquisition </NAME>
<MANAGER> 0817 </MANAGER>
<STAFFCOUNT> 1 </STAFFCOUNT>
</row>
</PROJECT>
Aufgabe 4: XML-Funktionen
Erstellen Sie mit den XML-Funktionen des SQL/XML-Standards XML-Sichten auf den relationalen Datenbestand aus Abbildung 2 für die im folgenden aufgeführten Problemstellungen.
Verwenden Sie dazu wenn möglich die XML-Funktion XMLGEN.
a) In der Datenbank wird nur das Jahresgehalt der Manager angegeben. Bestimmen Sie daraus
das durchschnittliche Monatsgehalt für jeden Manager und lassen Sie es für jeden Manager
zusammen mit seinem Namen in XML-Format ausgeben. Dazu sei die folgende XML-DTD
vorgegeben.
<!ELEMENT Manager
(Name, Monatsgehalt)>
<!ELEMENT Name
(#PCDATA)>
<!ELEMENT Monatsgehalt (#PCDATA)>
8
Das folgende SQL/XML-Statement bestimmt das durchschnittliche Monatsgehalt wie oben
vorgegeben:
SELECT XMLGEN
(
’<Manager>
<Name> {$Name} </Name>
<Monatsgehalt> {$Salary/12} </Monatsgehalt>
</Manager>’
)
FROM Manager
b) Produzieren Sie eine XML-Sicht auf Manager (Managername) mit ihren Projekten (Projektname und Anzahl der Mitarbeiter) gemäss folgender DTD.
<!ELEMENT Manager
(Name, Projekt*)>
<!ELEMENT Name
(#PCDATA)>
<!ELEMENT Projekt
(Name, Mitarbeiter)>
<!ELEMENT Mitarbeiter (#PCDATA)>
Da hier eine Gruppierung erforderlich ist, kann die Anfrage nicht nur mit XMLGEN allein
bearbeitet werden. Wir müssen stattdessen auf XMLAGG für die Gruppierung zurückgreifen.
Der Aufruf von XMLAGG liefert einen SQL-Wert vom Typ XML. Diesen Wert weisen wir der
XMLGEN-Variablen projekte zu, auf welchen wir uns im XQuery-Konstruktor von XMLGEN
beziehen.
SELECT XMLGEN
(
’<Manager>
<Name> {$m.Name} </Name>
{$projekte}
</Manager>’,
XMLAGG
(
XMLELEMENT
(
NAME "Projekt",
XMLCONCAT
(
XMLELEMENT(NAME "Name", p.Name),
XMLELEMENT(NAME "Mitarbeiter", p.StaffCount)
)
)
) AS projekte
)
FROM Manager m JOIN Project p ON (m.StaffNr = p.Manager) GROUP BY
m.OID, m.Name
9
In Oracle kann diese Aufgabe mitdem folgenden Skript umgesetzt werden (Oracle unterstützt
noch kein XQuery wie in SQL/XML):
SET LONG 4000;
CREATE TABLE Manager (
OID
INTEGER PRIMARY KEY,
StaffNr
INTEGER UNIQUE,
Name
VARCHAR(50),
Salary
DECIMAL(12,2),
Status
VARCHAR(20)
);
CREATE TABLE Project (
OID
INTEGER PRIMARY KEY,
ProjectNr
INTEGER UNIQUE,
Name
VARCHAR(50),
Manager
INTEGER,
StaffCount INTEGER,
FOREIGN KEY(Manager) REFERENCES Manager(StaffNr)
ON DELETE CASCADE
);
INSERT INTO Manager VALUES (1, 815, ’Janine McDivine’,
100000,’Partner’); INSERT INTO Manager VALUES (2, 817, ’Dr. Heinz
Kerbholz’, 75000, ’Senior’); INSERT INTO Project VALUES (3, 4711,
’Crap´n´Crazy’, 815, 750); INSERT INTO Project VALUES (4, 4712,
’InternetKiosk’, 817, 5); INSERT INTO Project VALUES (7, 5522,
’Acquisition’, 817, 1);
SELECT XMLELEMENT
(
NAME "Manager",
XMLCONCAT
(
XMLELEMENT(NAME "Name", m.Name),
XMLELEMENT(NAME "Monatsgehalt", m.Salary/12)
)
) AS XMLRESULT
FROM Manager m;
10
SELECT XMLELEMENT
(
NAME "Manager",
XMLELEMENT(NAME "Name", m.Name),
XMLAGG
(
XMLELEMENT
(
NAME "Projekt",
XMLCONCAT
(
XMLELEMENT(NAME "Name", p.Name),
XMLELEMENT(NAME "Mitarbeiter", p.StaffCount)
)
)
)
) AS XMLRESULT
FROM Manager m JOIN Project p ON (m.StaffNr = p.Manager) GROUP BY
m.OID, m.Name;
11