Prof. Dr. Stephan Karczewski Datenbanken 2 Aufgabe 2 Vorbereitungen zum Thema objekt-relationale Datenbanken In der aktuellen Version (ab 8i) bietet Oracle objekt-relationale Erweiterungen zum relationalen Datenbanksystem an, die es erlauben, komplexe Tabellen darzustellen. Der wesentliche Unterschied zu den relationalen Datenbanken besteht darin, dass Attribute mehrwertig (multi-valued) sein können. Dabei ist es möglich, record-artige (object type) und array-artige (table type) Attribute zu definieren. 1. Beispiel (object type): Folgende Tabelle soll definiert werden: Personen: PNR Pname Alt Vorname MI Nachname 1 Josef R Ewing 59 2 Franz X Unsinn 33 3 Uschi K. Glas 48 4 Hans L. Moser 72 Pname ist ein recordartiges Attribut. SQL-Befehle zur Generierung der Tabelle für das Beispiel: create type name_t as object ( Vorname varchar(30), MI char(1), Nachname varchar(30) ); / create type person_t as object ( PNR int, name name_t, Alt int ); / create table Personen of person_t ( primary key (PNR) ); / Seite 1 Prof. Dr. Stephan Karczewski Datenbanken 2 Aufgabe 2 SQL-Befehle zum Einfügen und Ändern von Werten in die (an der) objek-relationale(n) Tabelle im Beispiel: insert into personen values (1, name_t('Josef', 'R', 'Ewing'), 59); insert into personen values (2, null, null); update personen p set p.name = name_t('Franz', 'X', 'Unsinn') where PNR = 2; SQL-Befehle zum Auslesen von Werten aus objekt-relationalen Tabellen im Beispiel: select * from personen; select value(p) from personen p; -- value(p) wg. Object type select name from personen; select name.vorname from personen; -- falsch!! select p.name.vorname from personen p; -- Alias ist Pflicht!! select p.PNR, p.alt from personen p where p.name = name_t('Josef', 'R', 'Ewing'); select p.name, p.alt from personen p where p.name.vorname like 'Jo%'; Aufgaben Für die folgenden Aufgaben sind umfangreiche Befehle in Oracle zu implementieren. Um bei syntaktischen Fehlern nicht ständig neue Eingaben produzieren zu müssen, erstellen Sie Ihre Lösungen auf geeigneten ".sql"-Dateien und lassen Sie sie durch den "start"Befehl in SQL interpretieren. Die von Ihnen produzierten Dateien speichern Sie bitte am Ende auf USB-Stick, die Sie zur Abgabe bereithalten. 1. Realisieren Sie das Beispiel Person, das oben beschrieben ist, in Oracle. 1.1. Geben Sie hierzu zunächst die entsprechenden create-Befehle ein. (Tipp: Mit dem desc-Befehl können Sie überprüfen, wie die Typen bzw. Tabellen definiert sind) 1.2. Interpretieren Sie die angegebenen Select-Befehle und schreiben Sie das erwartete Ergebnis auf. 1.3. Führen Sie anschließend die select-Befehle aus und vergleichen Sie das Ergebnis mit Ihrer Erwartung. Seite 2 Prof. Dr. Stephan Karczewski Datenbanken 2 Aufgabe 2 2. Jede FIRMA besitzt eine Registrierungsnummer, einen Namen und eine Adresse (bestehend aus PLZ, ORT, Straße und Hausnummer), einen Umsatz (in Mio.) und eine Menge von Firmen, die Subunternehmen darstellen. Zeichnen Sie die Struktur von FIRMA auf (auf Papier). 3. Generieren Sie objekt-relational die Struktur der o.a. Tabelle. 4. Generieren Sie folgende Tabelleninhalte für FIRMA: 4.1. Firma 1: besitzt: und: 123, A-Firma, 65432, A-Stadt, B-Weg 17, 150 124, A1-Firma, 76543 B-Stadt, C-Str 25, 20 125, A2-Firma, 87654 C-Stadt, D-Weg 33, 30 4.2. Firma 2: besitzt: 234, B-Firma, 12345, Z-Stadt, X-Str 87, 100 235, B1-Firma, 23456 Y-Stadt, Y-Weg 92, 10 4.3. Firma 3: besitzt: und: und: 345, C-Firma, 12345, Z-Stadt, Y-Str 55, 200 346, C1-Firma, 76543 B-Stadt, D-Str 88, 100 347, C2-Firma, 12345 Z-Stadt, X-Weg 66, 50 348, C3-Firma, 65432 A-Stadt, A-Str 12, 15 5. Fragen Sie anschließend folgende Inhalte ab. Bevor Sie die Fragen in Oracle eingeben, schreiben Sie bitte die erwarteten Lösungen auf: 5.1. Alle Inhalte der erzeugten Tabelle 5.2. Alle Inhalte für die Firma mit der Nummer 123. 5.3. Die Nummer der Mutter-Firma der Subfirma mit der Nummer 347. 5.4. Alle Firmen-Nummern von den Firmen, die weniger als 3 Firmen besitzen. 5.5. Die Nummern der Zeilen (FNR), die eine Firma besitzen, die in B-Stadt residiert. 5.6. Zu jeder Firma die Nummer und die Nummern der im Besitz befindlichen Firmen. 5.7. Zu jeder Firma die die Nummer und die Summe der Umsätze der existierenden Subunternehmen. 5.8. Zu jeder Firma die Nummer und die Summe der Umsätze der existierenden Subunternehmen, wenn dieser größer als 100 ist. 5.9. Die Firmen, die weniger verdienen als irgendein Subunternehmen (auch von anderen Unternehmen). 5.10. Die Firmen, deren Subunternehmen in der Summe genau den Umsatz der Firma erzielen (und nicht weniger). Nachdem Sie Ihre Dateien auf Diskette abgespeichert haben, löschen Sie bitte die generierten Tabellen und Typen mit dem "drop table"-Befehl. Seite 3