Universitat Augsburg, Institut fur Informatik WS 2016/2017Prof. Dr. W. Kießling 23. Dez. 2016L. Rudenko, Dr. F. Wenzel Losungsblatt 9

Datenbanksysteme I

Aufgabe 1: Deklarative Anderungsoperationen in SQL (Hausaufgabe)

a) Erstellen der s candidates Relation:CREATE TABLE s c a n d i d a t e s (i d VARCHAR2( 3 ) PRIMARY KEY,l a s t n a m e VARCHAR2( 2 0 ) ,f i r s t n a m e VARCHAR2( 2 0 ) ,s a l a r y NUMBER ( 1 1 , 2 ) ) ;

b) Einfugen aller Mitarbeiter aus s emp, die nicht bereits Manager in der Hauptgesellschaft sind:INSERT INTO s c a n d i d a t e sSELECT e . id , e . l a s t n a m e , e . f i r s t n a m e , e . s a l a r yFROM s emp eWHERE e . i d NOT IN (SELECT f . i d

FROM s emp f , s emp mWHERE f . i d = m. m a n a g e r i d ) ;

c) Gehaltserhohung fur alle Kandidaten, die bereits Kunden in der Hauptgesellschaft betreuten:UPDATE s c a n d i d a t e sSET s a l a r y = 1 . 2∗ s a l a r yWHERE i d IN (SELECT e . i d

FROM s emp e , s c u s t o m e r cWHERE c . s a l e s r e p i d =e . i d ) ;

d) Loschen aller Kandidaten, deren Gehalt 30 Prozent uber dem Durchschnittsgehalt in s candidatesliegt:DELETE FROM s c a n d i d a t e sWHERE s a l a r y >(SELECT 1 . 3∗AVG( s a l a r y )

FROM s c a n d i d a t e s ) ;

e) Erstellen einer View mit Nachnamen und Vornamen der Kandidaten:CREATE VIEW c a n d l i s t ( l a s t n a m e , f i r s t n a m e ) ASSELECT l a s t n a m e , f i r s t n a m e FROM s c a n d i d a t e s ;

Aufgabe 2: Erstellen einer Multimedia-Datenbank (Hausaufgabe)

Grundlage sind wiederum bereits implementierte Methoden vom Ubungsblatt 6:

a) siehe Methode private boolean establishConnection()

b) Methodenaufruf der bereits implementierten Methode executeStatement mit entsprechenderDDL:e x e c u t e S t a t e m e n t ( ”CREATE TABLE p a p e r s ( i d i n t e g e r RPRIMARY KEY,

t i t l e v a r c h a r ( 1 0 0 ) ,pdf b lob ) ” ) ;

1

c) Neue Methode, die Batch Updates ausfuhrt:p r i v a t e void i n s e r t V a l u e s ( ) throws SQLException {

t r y {/ / s e t auto−commit t o f a l s e f o r b a t c h u p d a t e sopenConnec t ion . setAutoCommit ( f a l s e ) ;S t a t e m e n t s t m t = openConnec t ion . c r e a t e S t a t e m e n t ( ) ;

/ / add i n s e r t s t o b a t c h/ / i n i t i a l i z e b l o b s as EMPTY BLOB ( )s t m t . addBatch ( ”INSERT INTO p a p e r s VALUES( 1 , ’ ECTS−GRUNDSATZE ’ , EMPTY BLOB ( ) ) ” ) ;s t m t . addBatch ( ”INSERT INTO p a p e r s VALUES( 2 , ’ PRINCIPALES CARACTERISTIQUS

DE L´ECTS ’ , EMPTY BLOB ( ) ) ” ) ;s t m t . addBatch ( ”INSERT INTO p a p e r s VALUES( 3 , ’ CARACTERISTICAS PRINCIPALES

DE LOS ECTS ’ , EMPTY BLOB ( ) ) ” ) ;/ / e x e c u t e b a t c h and commits t m t . e x e c u t e B a t c h ( ) ;openConnec t ion . commit ( ) ;s t m t . c l o s e ( ) ;

}

catch ( B a t c h U p d a t e E x c e p t i o n e ) {/ / i n case o f b a t c h up da t e e r r o r −> r o l l b a c kopenConnec t ion . r o l l b a c k ( ) ;throw e ;

}catch ( SQLException e ) {

/ / i n case o f SQL e r r o r −> r o l l b a c kopenConnec t ion . r o l l b a c k ( ) ;throw e ;

}f i n a l l y {

/ / s e t auto−commit t o t r u e and c l o s e s t a t e m e n topenConnec t ion . setAutoCommit ( t rue ) ;

}}

Zur Ausfuhrung der Batch-Updates wird Auto-Commit deaktiviert. Anschließend werden dieWerte eingefugt und eventuell auftretende Fehler mit einem Rollback ruckgangig gemacht.

d) Erstellen der Stored Procedure via Methodenaufruf von executeStatement():S t r i n g procedurePDF = new S t r i n g ( ”CREATE OR REPLACE PROCEDURE i n s e r t p d f

( i d i i n t e g e r , p d f i b lob )AS BEGIN UPDATE p a p e r s SET pdf = p d f iWHERE i d = i d i ; END i n s e r t p d f ; ” ) ;

e x e c u t e S t a t e m e n t ( procedurePDF ) ;

e) Methode zum Einfugen der Pdfs:p r i v a t e s t a t i c vo id i n s e r t D a t a ( I n t e g e r id , S t r i n g pdf ) throws E x c e p t i o n {

t r y {F i l e p d f F i l e = new F i l e ( pdf ) ;F i l e I n p u t S t r e a m p d f F i s = new F i l e I n p u t S t r e a m ( p d f F i l e ) ;

/ / p r e p a r e c a l l f o r t h e s t o r e d p r o c e d u r eC a l l a b l e S t a t e m e n t c s t m t = openConnec t ion . p r e p a r e C a l l ( ”{CALL i n s e r t p d f ( ? , ? ) } ” ) ;

/ / s e t t i n g t h e i dc s t m t . s e t I n t ( 1 , i d ) ;/ / s e t t i n g t h e PDFc s t m t . s e t B i n a r y S t r e a m ( 2 , p d f F i s , ( i n t ) p d f F i l e . l e n g t h ( ) ) ;

2

/ / e x e c u t ec s t m t . e x e c u t e U p d a t e ( ) ;

/ / c l o s i n g f i l e i n p u t s t r e amp d f F i s . c l o s e ( ) ;c s t m t . c l o s e ( ) ;}

ca tch ( E x c e p t i o n e ){throw e ;

}}

Die Methode ist mit den entsprechenden Pfadangaben als Parameter pdf aufzurufen. Sind dieDateien direkt im Eclipse Projekt eingebunden, so lautet der entsprechende Methodenaufruf:i n s e r t D a t a ( 1 , ” key de . pdf ” ) ;i n s e r t D a t a ( 2 , ” k e y f r . pdf ” ) ;i n s e r t D a t a ( 3 , ” k e y e s . pdf ” ) ;

Ob das Einfugen der Blobs erfolgreich war, kann mittels SQL-Developer mit folgendem State-ment uberpruft werden, welches die Dateigroßen der Blobs anzeigt:SELECT ID , DBMS LOB .GETLENGTH(PDF) FROM P a p e r s ;

Aufgabe 3: Volltextsuche mit Oracle (Prasenzaufgabe)

Erstellen des Volltextindexes:CREATE INDEX p d f i n d e x ON p a p e r s ( pdf )INDEXTYPE IS CTXSYS .CONTEXT;

a) Informationen uber ECTS samt Ausgabe der entsprechenden SCORESELECT SCORE ( 1 ) , t i t l e FROM p a p e r sWHERE CONTAINS ( pdf , ’ECTS ’ , 1 ) > 0 ;

b) Artikel, in denen ein Credit und 30 Arbeitsstunden entspricht nahe beieinander stehen. Dabeiwerden nur Ergebnisse betrachtet, deren Score hoher als 10 ist:SELECT SCORE ( 1 ) , t i t l e FROM p a p e r sWHERE CONTAINS ( pdf , ’NEAR( ( e i n C r e d i t , 30 A r b e i t s s t u n d e n e n t s p r i c h t ) , 20)> 10 ’ , 1 ) > 0 ;

c) Anzahl der Dokumente (Hit Count) zum Thema ECTS, die nicht transferencia enthalten:SELECT c o u n t ( i d ) FROM p a p e r sWHERE CONTAINS ( pdf , ’ECTS NOT t r a n s f e r e n c i a ’ , 1 ) > 0 ;

Aufgabe 4: Thesauruswissen (Prasenzaufgabe)

a) Alle Spezialisierungen von Leistungsbewertung:SELECT t i t l e FROM P a p e r sWHERE CONTAINS( pdf , ’NT( L e i s t u n g s b e w e r t u n g , 2 , d b i s t h e s ) ’ , 1 ) > 0 ;

Die Spezialisierung mit Level 1 liefert kein Ergebnis, da der nachste Unterbegriff in der Hierar-chie Leistungspunkte ist, der in keinem der Texte enthalten ist. Erst eine erneute Spezialisierung,also auf Level 2 von Leistungsbewertung aus gesehen liefert das Ergebnis.

3

b) Alle Dokumente, die Synonyme von usage enthalten:SELECT t i t l e FROM P a p e r sWHERE CONTAINS( pdf , ’SYN( usage , d b i s t h e s ) ’ , 1 ) > 0 ;

c) Titel aller Dokumente, in denen mit dem Ausdruck Studienorientierung verwandte Ausdruckevorkommen:SELECT t i t l e FROM P a p e r sWHERE CONTAINS( pdf , ’RT( S t u d i e n o r i e n t i e r u n g , d b i s t h e s ) ’ , 1 ) > 0 ;

Aufgabe 5: Algebraische Query-Optimierung (Hausaufgabe)

a) πa,d,e(σb<10(T ◃▹ σa>c(R ◃▹ S)))

Ausgangsbaum optimierter Baumπa,d,e

σb<10

◃▹a,c

������

BBBB

BBB

T σa>c

◃▹b

<<<<

<

zzzzzzz

R S

πa,d,e

◃▹a,c

������

FFFF

FFF

T σa>c

(πa,c,d)

◃▹b

HHHHHH

HH

vvvvvv

vv

σb<10 σb<10

R S

Die Selektion b < 10 ist jeweils auf R und S anwendbar und wird deshalb ganz nach untengeschoben. Nach dem Natural Join auf R und S wird das Attribut b nicht mehr benotigt unddeshalb uber eine zusatzliche Projektion eliminiert.

4

b) πa,b,f (σa=10 ∨ f=5(R ◃▹ (U ◃▹ (T ◃▹ S))))

Augsgangsbaum optimierter Baumπa,b,f

σa=10 ∨ f=5

◃▹a,b

wwwwwww

HHHHHH

HH

R ◃▹c

vvvvvv

vvv

::::

::

U ◃▹c

8888

8

�����

T S

σa=10 ∨ f=5

◃▹a,b

wwwwwww

JJJJJJ

JJJ

R πa,b,f

◃▹c

sssssssss

@@@@

@@@

U ◃▹c

�������

<<<<

<<<

πa,c πb,c

T S

Die Selektion bezieht sich auf a, welches in R und T auftaucht. f ist nur in U zu finden, kannaber wegen der oder-Verknupfung nicht nach unten verschoben werden. Die Attribute d und esind ohne Bedeutung und konnen deshalb ’ausgeblendet’ werden. Sobald S, T und U gejointwurden, ist auch C irrelevant.

c) πe,f (σf=14 ∧ c=27(R ◃▹ T ◃▹ U))

Augsgangsbaum optimierter Baumπe,f

σf=14 ∧ c=27

◃▹c

tttttt

ttt

IIII

IIII

◃▹a

yyyyyyy

JJJJJJ

JJJU

R T

πe,f

◃▹c

|||||||

LLLLLL

LLLL

πc,e σf=14 ∧ c=27

◃▹a

yyyyyyyy

@@@@

@@@ U

R σc=27

TDa die Bedingungen in der Selektion durch ein und verknupft werden, konnen diese zerlegtund nach unten geschoben werden. Nach dem Join von R und T ist das Attribut a nicht mehrnotwendig, eine Projektion eliminiert a und b.

5

Aufgabe 6: Queryoptimierung (Prasenzaufgabe)

a) Geben Sie fur folgende Anfrage eine einfache SQL-Query an:Bestimmen Sie die Autoren (Nachname und Vorname), die ein Buch mit dem Titel ’DB’veroffentlicht haben.SELECT DISTINCT a . Nachname , a . VornameFROM Buch b , V e r o e f f e n t l i c h u n g v , Autor aWHERE b . ISBN = v . ISBNAND v . AutorID = a . AutorID AND b . T i t e l = ’DB’ ;

b) Transformieren Sie Ihre SQL-Anfrage in unoptimierte Relationale Algebra. Verwenden Sie kar-tesische Produkte anstelle von Join-Operatoren.

πa.Nachname,a.V orname(σb.ISBN = v.ISBN ∧ v.AutorID = a.AutorID ∧ b.T itel=′DB′(b× v × a))

c) Geben Sie fur den Ausdruck aus Teilaufgabe b) den initialen Operatorbaum an.πa.Nachname,a.V orname

σb.ISBN = v.ISBN ∧ v.AutorID = a.AutorID ∧ b.T itel=′DB′

×

eeeeeeeeeeeeee

eeeeeeeeeeeee

YYYYYYYYYYYY

YYYYYYYYYYYY

YYY

×wwwwww

YYYYYYYYYYYY

YYYYYYYYYYYY

YYY a

b v

d) Fuhren Sie die Optimierung der Anfrage mit Hilfe des Hill-Climbing Algorithmus aus. Siemussen keine Zwischenschritte angeben.

Schritt 1: Aufspalten von Selektionenπa.Nachname,a.V orname

σb.ISBN = v.ISBN

σv.AutorID = a.AutorID

σb.T itel=′DB′

×kkkk

kkkkkkkk

SSSSSSSS

SSSS

×wwwwww

SSSSSS

SSSSSS a

b v

6

Schritt 2: Push Selektionπa.Nachname,a.V orname

σv.AutorID = a.AutorID

×SSSS

SSSSSSSS

ffffffffff

fffffff

σb.ISBNN = v.ISBN a

×ggggg

gggggggggg

g

XXXXXXXXXX

XXXXXXXXXX

σb.T itel=′DB′ v

b

Schritt 3: Push Projectionπa.Nachname,a.V orname

σv.AutorID = a.AutorID

×YYYYYY

YYYYYYYYYYYY

YYY

gggggggggg

ggggggg

πv.AutorID πa.Nachname,a.V orname,a.AutorID

σb.ISBNN = v.ISBN a

×ggggg

gggggggggg

g

XXXXXXXXXX

XXXXXXXXXX

πb.ISBN v

σb.T itel=′DB′

πb.T itel,b.ISBN

b

7

Schritt 5: Kombinantion von Selektion und kartesischem Produkt (Endbaum)πa.Nachname,a.V orname

◃▹v.AutorID = a.AutorID

YYYYYYYYYYYYYY

Y

gggggggggg

gg

πv.AutorID πa.Nachname,a.V orname,a.AutorID

◃▹b.ISBN = v.ISBN

gggggggggg

g

XXXXXXXXXX

XXXXXa

πb.ISBN v

σb.T itel=′DB′

πb.T itel,b.ISBN

b

8