Institut f¨ ur Informationssysteme
Dr. C. T¨ urker
Eidgen¨ ossische Technische Hochschule Z¨ urich 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¨ur den Datenaustausch:
XML und Datenbanken WS 04/05 ¨ Ubung 8
Beispiell¨ osung
Abbildung von Datenbanken auf XML In der Computerfirma CleverSoft sind Sie f¨ur das Datenbanksystem zur Verwaltung der Kundendaten, Auftr¨age, und Bestellungen verantwortlich. Abbildung 1 zeigt die Datenbank. Die Gesch¨aftsleitung hat k¨urzlich 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¨ur 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¨oglichkeiten des neuen Systems noch nicht genau kennen, m¨ochten Sie die Daten in unterschiedlichen Darstellungen exportieren:
1
... ... ... ... b) Bilden Sie den Datenbestand auf ein XML-Dokument ab, das die Schl¨ussel-Fremdschl¨usselbeziehungen der Daten m¨oglichst ad¨aquat abbildet. Aufbauend auf dem oben schon gew¨ahlten Ansatz haben wir eine Hierarchie gew¨ahlt, die beginnend mit den Regionen ¨uber die Kunden zu den einzelnen Bestellposten f¨uhrt. Damit ergibt sich das folgende XML-Dokument: ... ... ... ... 2
... Eine wichtige Beobachtung ist hier, dass wir die Schl¨ussel-Fremdschl¨ussel-Beziehungen nicht mehr explizit mitf¨uhren m¨ussen, 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 = ’1.1.2002’ AND order.orderdate Eingaben, die der DTD nicht gen¨ugen, werden von der Steuerbeh¨orde nicht mehr akzeptiert. Wie gehen Sie nun vor, um den Vorgaben der Steuerbeh¨orde zu gen¨ugen? Um f¨ur die Abfrage XML-Dokumente zu generieren, die speziellen Formatvorgaben gen¨ugen, muss im allgemeinen auf individuelle Abbildungsvorschriften zur¨uckgegriffen werden. Hier erscheinen die Varianten mittels einer erweiterten Datenbankanfrage oder ¨uber ein XSLTStylesheet am angemessensten. Im Folgenden w¨ahlen wir den Ansatz mittels einer erweiterten Datenbankanfrage in dem aus der Vorlesung bekannten Dialekt:
3
¨ Abbildung 2 zeigt die Beispieldaten aus der ersten Ubung. 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¨atsbedingungen. 6
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 Ubung diskutiert, wird in dieser Art der Standard-Abbildung die Information ¨uber Schl¨ussel-Fremdschl¨usselbeziehungen nicht in das Dokument ¨ubernommen. Hier hilft lediglich das Wissen ¨uber die Anwendung, oder explizite Annotationen des Dokuments, die der Standard allerdings nicht vorsieht. 1 0815 Janine McDivine 100.000 Partner 7
2 0817 Dr. Heinz Kerbholz 75.000 Senior 3 4711 Crap’n’Crazy 0815 750 4 4712 InternetKiosk 0817 5 7 5522 Acquisition 0817 1
Aufgabe 4: XML-Funktionen Erstellen Sie mit den XML-Funktionen des SQL/XML-Standards XML-Sichten auf den relationalen Datenbestand aus Abbildung 2 f¨ur die im folgenden aufgef¨uhrten Problemstellungen. Verwenden Sie dazu wenn m¨oglich die XML-Funktion XMLGEN. a) In der Datenbank wird nur das Jahresgehalt der Manager angegeben. Bestimmen Sie daraus das durchschnittliche Monatsgehalt f¨ur jeden Manager und lassen Sie es f¨ur jeden Manager zusammen mit seinem Namen in XML-Format ausgeben. Dazu sei die folgende XML-DTD vorgegeben. 8
Das folgende SQL/XML-Statement bestimmt das durchschnittliche Monatsgehalt wie oben vorgegeben: SELECT XMLGEN ( ’ {$Name} {$Salary/12} ’ ) FROM Manager b) Produzieren Sie eine XML-Sicht auf Manager (Managername) mit ihren Projekten (Projektname und Anzahl der Mitarbeiter) gem¨ass folgender DTD. Da hier eine Gruppierung erforderlich ist, kann die Anfrage nicht nur mit XMLGEN allein bearbeitet werden. Wir m¨ussen stattdessen auf XMLAGG f¨ur die Gruppierung zur¨uckgreifen. 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 ( ’ {$m.Name} {$projekte} ’, 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¨utzt 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