Probeklausur 0

Herr Regelmann hat in seinem Vortrag zwischen einem Unternehmensdatenmodell und einem unternehmensweiten Datenmodell unterschieden. Was ist nach seiner Definition der Unterschied zwischen diesen beiden Begriffen?

Probeklausur 1

Aufgabe 1: Relationentypen (10 Punkte)

Erklären Sie, was unter den folgenden Relationentypen verstanden wird:

a) Basisrelation b) Abgeleitete Relation c) Virtuelle Relation d) Sicht e) Schnappschussrelation

Aufgabe 2: Verbundarten (12 Punkte)

Definieren Sie die folgenden Begriffe:

a) Kartesisches Produkt b) Thetaverbund c) Gleichverbund d) Natürlicher Verbund e) Äußerer Verbund f) Semiverbund

Aufgabe 3: Relative Algebra

[center]

[/center]

Aufgabe 4: SQL (32 Punkte)

Gegeben seien folgende Relationen: Lieferant (LieferantNr, Name, Status, Stadt) Teil(TeilNr, Bezeichnung, Farbe, Gewicht) Projekt (ProjektNr, Name, Stadt) Lieferung (LieferantNr, TeilNr, ProjektNr, Anzahl) 1) Geben Sie die folgenden SQL-Anweisungen an:

a) Erzeugen Sie die Tabellen Teil und Lieferung. Wählen Sie geeignete Datentypen und

berücksichtigen Sie die Schlüsselattribute. CREATE TABLE Teil( TeilNr INTEGER PRIMARY KEY, Bezeichnung VARCHAR(20), Farbe VARCHAR(10), Gewicht INTEGER); CREATE TABLE Lieferung( LieferantNr INTEGER, TeilNr INTEGER, ProjektNr INTEGER, Anzahl INTEGER, CONSTRAINT LiefPK PRIMAEY KEY (LiefNr, TeilNr, ProjektNr), CONSTRAINT TeilFK FOREIGN KEY (TeilNr) REFERENCES Teil, CONSTRAINT ProjektFK FOREIGN KEY (ProjektNr) REFERENCES Projekt, CONSTRAINT LiefFK FOREIGN KEY (LieferantNr) REFERENCES Lieferant);

b)Fügen Sie einen roten Eimer in die Relation „Teil“ ein. Der Eimer wiegt 500 Gramm

und hat die TeilNr 7. INSERT INTO Teil VALUES(7, ’Eimer’, ’rot’, 500);

c) Ändern Sie die Farbe des Eimers in gelb.

UPDATE Teil SET Farbe=’gelb’ WHERE TeilNr=7;

d) Erzeugen Sie eine Sicht, die für alle Großlieferanten aus Essen ihre Namen, und

Lieferantennummer nach dem Namen aufsteigend sortiert liefert. Als Großlieferanten werden diejenigen Lieferanten betrachtet, die an mehr als 10 Projekten beteiligt sind. CREATE VIEW GrossLieferantenAusEssen AS SELECT Name, LieferantNr FROM Lieferant NATURAL JOIN Lieferung WHERE Stadt=’Essen’ GROUP BY LieferantNr HAVING COUNT(ProjektNr)> 10 ORDER BY Name;

2) Formulieren Sie die folgenden SQL-Anfragen unter Verwendung von geeigneten Aggregatfunktionen:

a) Geben Sie die Gesamtzahl der nach Essen (d.h. für Projekte in Essen) gelieferten Teile

an. SELECT SUM(Anzahl) AS GesamtZahl FROM Projekt NATURAL JOIN Lieferung WHERE Stadt=’Essen’;

b)Geben Sie das durchschnittliche Gewicht der aus Essen (d.h. von in Essen ansässigen

Lieferanten) gelieferten Teile an. SELECT AVG(Gewicht) AS DurchschGewicht FROM Teil NATURAL JOIN Lieferung NATURAL JOIN Lieferant WHERE Stadt=’Essen’;

c) Geben Sie für jede Stadt die Anzahl der dort durchgeführten Projekte an.

SELECT Stadt, COUNT(ProjektNr) AS AnzahlProjekte FROM Projekt GROUP BY Stadt;

d) Geben Sie für jedes Projekt seine Nummer, Name die Anzahl der beteiligten

Lieferanten an. Das Ergebnis soll nach den Projektnamen aufsteigend sortiert werden. SELECT ProjektNr, Name, COUNT(LieferantNr)AS AnzahlLiefer FROM Projekt NATURAL JOIN Lieferung GROUP BY ProjektNr ORDER BY Name;

Aufgabe 5 Transaktionsmanagement

[center] [/center]

Aufgabe 6: JDBC (15 Punkte)

- Nur für Systems Engineering Studenten -

a) Welche Probleme treten auf, wenn SQL in einer prozeduralen Programmiersprache

verwendet werden soll? (10 Punkte) • SQL ist nicht prozedural, die gewünschten Ergebnisse werden durch die Angabe von Prädikaten ermittelt. • Der „Weg“ zu den Daten wird in SQL nicht in Form von Suchalgorithmen angegeben. • SQL verarbeitet grundsätzlich Mengen von Tupeln. Eine Abfrage bringt eine Tabelle mit 0, 1 oder N Zeilen als Ergebnis. • Prozedurale Programmiersprachen erlauben nur das Satzweise arbeiten auf Datensätzen, d. h. es kann mit einer Anweisung nur ein Datensatz gelesen, gelöscht oder geschrieben werden. • Unterschiedliche Datentypen / Datenrepräsentation • Die Problematik der unterschiedlichen Modellierungsfähigkeiten und des unterschiedlichen Verarbeitungsmodells wird häufig unter dem Begriff impedance mismatch subsumiert. Siehe auch Buch S.440 ff

b) Erläutern Sie die Unterschiede zwischen JDBC und Embedded SQL (z. B. SQLJ) und

gehen Sie insbesondere auf die Vor- bzw. Nachteile ein. (5 Punkte) • SQLJ, funktioniert wie in C, C++, ... • SQL-Anweisungen werden durch Precompiler in Aufrufe an SQLJ-Laufzeitsystem ersetzt und können danach vom „richtigen“ Compiler verarbeitet werden. • JDBC verarbeitet SQL Strings grundsätzlich erst zur Laufzeit und können somit auch erst zur Laufzeit geprüft werden. • Im Gegensatz dazu können die SQL-Anweisungen bei SQLJ bereits zur Kompilierungszeit überprüft werden. Vorteile/Nachteile: JDBC höhere Flexibilität dafür weniger performant und fehleranfälliger, weil die Abfragen erst zur Laufzeit und nicht zur compilezeit überprüft werden können. Fetter Text

Probeklausur 2

Aufgabe 1: Relationale Algebra (15 Punkte)

Relationale Algebra wird SS 2009 nicht dran kommen.

Gegeben seien die folgenden aus den Übungen bekannten Relationen:

Person(personID, vorname, nachname, geburtsdatum) Student (personID, matrikelnummer, fachbereich, studienfach, fachsemester) Mitarbeiter (personID, wochenstunden, monatsgehalt, raum) Lehrveranstaltung(lv_nr, raum, gehalten_seit, status) Leistung (personID, lv_nr, note) Betreuung (personID, lv_nr)

Formulieren Sie die folgenden Anfragen in relationaler Algebra:

a) Geben Sie die Matrikelnummer aller Studenten der Fachbereiche "Wirtschaftswissenschaften" oder "Mathematik" aus. (3 P)

b) Geben Sie die Attribute personID, matrikelnummer, studiengang und note aller Studenten mit dem Nachnamen "Meier" aus. (4 P)

c) Geben Sie Vor- und Nachnamen aller Mitarbeiter zusammen mit dem Raum an, in dessen Raum eine Lehrveranstaltung stattgefunden hat. (4 P)

d) Erstellen Sie eine Liste aus Vor- und Nachnamen aller Mitarbeiter sowie – wenn vorhanden – mit der lv_nr der betreuten Lehrveranstaltungen. (4 P)

Aufgabe 2: SQL

(50 Punkte) Gegeben seien die folgenden Relationen:

Person(personID, vorname, nachname, geburtsdatum) Student (personID, matrikelnummer, fachbereich, studienfach, fachsemester) Mitarbeiter (personID, wochenstunden, monatsgehalt, raum) Lehrveranstaltung(lv_nr, raum, gehalten_seit, status) Leistung (personID, lv_nr, note) Betreuung (personID, lv_nr)

Geben Sie die folgenden SQL-Anweisungen an:

a) Geben Sie alle Lehrveranstaltungen an, zu denen Studenten bereits eine Leistung erbracht haben. (5 Punkte)

SELECT Lehrveranstaltung.* FROM Leistung NATURAL JOIN Lehrveranstaltung;

Geben Sie den Vor- und Nachnamen und das Fachsemester aller Studierenden aus, die im Fachbereich Wirtschaftswissenschaften studieren und mindestens bereits im 4. Fachsemester sind. Sortieren Sie die Ausgabe aufsteigend nach dem Nachnamen und Vornamen. (5 Punkte)

b) Geben Sie jeden Raum der von mehr als einem Mitarbeiter belegt wird an. Geben Sie auch an, wie viele Mitarbeiter sich den Raum teilen. (5 Punkte) SELECT raum, COUNT(personID) FROM Mitarbeiter GROUP BY Raum HAVING count(personID)>1; SELECT Vorname, Nachnamen, Fachsemester FROM Student NATURAL JOIN Person WHERE Fachbereich=’Wirtschaftswissenschaften’ AND Fachsemester>=4 ORDER BY nachname,vorname;

c) Geben Sie den Durchschnittsstundenlohn aller Mitarbeiter an die keine Lehrveranstaltung betreuen. Vereinfachend kann angenommen werden, dass jeder Monat vier Wochen hat. (7 Punkte)

SELECT avg(monatsgehalt/(4*wochenstunden)) FROM Mitarbeiter WHERE personid not in (select personid from student)

e) Geben Sie eine SQL-Anfrage an, die die Durchschnittsnote der studentischen Hilfskräfte ermittelt. Ein weiteres Attribut soll die Durchschnittsnote der Studierenden angegeben werden, die keine Hilfskräfte sind. Geben Sie als drittes zusätzlich das Verhältnis dieser beiden Werte zueinander an. Anmerkung: Studentische Hilfskräfte sind Mitarbeiter die gleichzeitig Studenten sind. Bitte beachten Sie, dass genau ein Statement verlangt ist. (14 Punkte) select note1, note2, note1/note2 from (SELECT avg(note) as note1 from student natural join mitarbeiter natural join leistung) as tab1, (SELECT avg(note)as note2 from student natural join leistung where personid not in (select personid from mitarbeiter)) as tab2

f) Geben Sie zu jeden Studierenden die Anzahl der erbrachten Leistungen an.

Leistungen, bei denen das Attribut Note mit der Nullmarke belegt ist, sollen dabei mitgezählt werden. Weiterhin sollen in dem Abfrageergebnis die Mitarbeiter mit der Anzahl der betreuten Lehrveranstaltungen enthalten sein. Bitte beachten Sie, dass nur genau ein Statement gefordert ist. Im Abfrageergebnis soll es zudem eine Spalte geben, die kennzeichnet, ob ein Tupel einen Studenten oder einen Mitarbeiter beschreibt. Für studentische Hilfskräfte sollen zwei Tupel, eins für jede Rolle, angezeigt werden. (14 Punkte)

SELECT *, 'Student' as Art FROM ((select personid, count(*) as anzahl from student natural join leistung group by personid) as tab1 natural join person)

union

(SELECT *,'Mitarbeiter' as art FROM (select personid, count(*) as anzahl from mitarbeiter natural join betreuung group by personid) as tab2 natural join person)

Aufgabe 3: SQL-Theorie (5 Punkte)

Die folgende Abfrage wurde auf der Basis des Schemas aus Aufgabe 2 definiert:

SELECT COUNT(*) FROM Mitarbeiter WHERE monatsgehalt >ANY (SELECT monatsgehalt from mitarbeiter where monatsgehalt < 0);

Geben Sie an, was die oben angegebene Abfrage zurückliefert unter der Voraussetzung, dass alle Gehälter positiv sind.

Begründen Sie Ihre Antwort.

Da die Unterabfrage die leere Menge zurückliefert, wird 0 zurückgegeben, da ANY bei der leeren Menge immer zu FALSE evaluiert.

Aufgabe 4: Synchronisationsverfahren (15 Punkte)

1) Gegeben sei das folgende Schedule:

R4(y) R2(x) W3(x) W2(z) W4(y) W3(z) R3(y) W1(x) W3(y) R5(y) W1(z) W5(x)

a) Erstellen Sie für diese Schedule den Abhängigkeitsgraphen.

b) Ist sie serialisierbar (begründen Sie Ihre Antwort)?

Geben Sie, wenn möglich, die äquivalente serielle Schedule an

serialisierbar: T4 T2 T3 T1 T5 oder T2 T4 T3 T1 T5 R4(y) W4(y) R2(x) W2(z) W3(x) W3(z) R3(y) W3(y) W1(x) W1(z) R5(y) W5(x)

P.S.: (wenn statt Schedule nur Transaktionsfolge T2 T4 T3 T5: nur 0,5P)

2) Gegeben sei die folgende Schedule (10 Punkte):

R4(y) R2(x) W3(x) W2(z) W4(y) W3(z) R3(y) W1(x) W3(y) R5(y) W1(z) W5(x)

1. Welche (um die Sperren erweiterte) Ergebnisschedule würde mit Preclaiming erzeugt? (Geben Sie die vollständige Schedule an!)

2. Kann es zu einem Deadlock kommen? Begründen Sie Ihre Antwort.

Beachten Sie, dass die Anzahl der Zeilen in der folgenden Tabelle KEINEN Hinweis auf die richtige Lösung gibt.

Aufgabe 4 No.2 Bild einfügen

Aufgabe 5: JDBC

(15 Punkte)

a) Erweitern Sie den unten angegebenen Programmausschnitt um die Behandlung einer

Nullmarke. Weisen Sie der Variablen number die Zahl -1 zu, falls es sich um eine Nullmarke handelt.

(...) int number=rs.getInt(“Number“);

2 Punkte if (rs.wasNull()) number=-1

b) Aus welchem Grund ist die Behandlung der Nullmarke bei primitiven Datentypen notwendig?

c) Erläutern Sie kurz aus welchem Grund Connection Pooling es eingesetzt wird. Gehen Sie hierbei kurz auf die generelle Funktionsweise ein.

b) Erweitern Sie das folgende Programm dahingehend, das die beiden angegebenen PreparedStatements pstmt1 und pstmt2 die Abfrage mit dem Parameter 3 innerhalb einer Transaktion ausführen. Die ID wird dabei als Integer repräsentiert. Fügen Sie dazu den benötigten Programmcode zwischen die Kommentare //BOT und //EOT ein. Stellen Sie dabei sicher, dass im Fehlerfall die Transaktion zurückgesetzt wird. Schreiben Sie den hierfür benötigten Programmcode unter den Kommentar //FOT.

public class Beispiel { public static void main(String[] args) { try { Class.forName("COM.ibm.db2.jdbc.net.DB2Driver"); Connection con=null; con=DriverManager.getConnectionurl,"MrX","Secret");

PreparedStatement pstmt1=con.prepareStatement(“DELETE FROM ersteTab WHERE ID=?“);

PreparedStatement pstmt2=con.prepareStatement(„DELETE FROM zweiteTab WHERE ID=?“);

//BOT

Zusatzaufgabe: Praktikeraufgabe (5 Punkte)

Diese Aufgabe ist durch die Inhalte des IBM-Vortrages zu ersetzen

Herr Regelmann hat in seinem Vortrag zwischen einem Unternehmensdatenmodell und einem unternehmensweiten Datenmodell unterschieden. Was ist nach seiner Definition der Unterschied zwischen diesen beiden Begriffen?

//EOT

catch (SQLException e) {

//FOT

}

Probeklausur 4

Aufgabe 1: Die Drei-Ebenen-Schemaarchitektur (10 Punkte)

Stellen Sie die Drei-Ebenen-Schemaarchitektur nach ANSI graphisch dar und erklären Sie die einzelnen Schichten.

Internes Schema Legt fest, in welcher Form die Daten der DB gespeichert werden (Speicherungsstrukturen). Konzeptionelles Schema Auf Basis des Datenbankmodells entwickelte logische Beschreibung des Realweltausschnitts. Abstraktion von der internen Darstellung. Anwendungsneutrale Darstellung.

Externes Schema (Sichten) Extrahiert aus dem konzeptionellen Schema einen anwendungsspezifischen Ausschnitt. Ebene, auf der normalerweise Benutzer und Anwendungen arbeiten. Nichtrelevante Daten werden ggf. ausgeblendet bzw. anwendungsspezifische Darstellung möglich. Dient außerdem dem Datenschutz.

Aufgabe 2: Relationale Algebra (30 Punkte)

Relationale Algebra kommt in der Klausur nicht vor.

Aufgabe 3: SQL (30 Punkte)

Gegeben seien folgende Relationen:

Produkt (produktNr, produktName, preis, hersteller) Verkaeufer (verkaeuferNr, verkaeuferName, gehalt, abteilung) Kunde (kundenNr, kundenName, stadt) Rechnung (rechnungNr, kundenNr, verkaeuferNr, produktNr, anzahl, datum)

1) Geben Sie die folgenden SQL-Anweisungen an:

a) Erzeugen Sie die Tabellen Produkt und Rechnung

Wählen Sie geeignete Datentypen und berücksichtigen Sie die Primär- und Fremdschlüsselattribute. (8 Punkte)

b) Fügen Sie ein Mobiltelefon von Siemens mit dem Namen SL65 in die Produkt-Relation ein. Es soll 240 Euro kosten und die Produktnummer 7 haben. (2 Punkte)

2) Formulieren Sie die folgenden beiden SQL-Anfragen unter Verwendung von Unteranfragen und geeigneten mengen-orientierten Prädikaten:

a) Geben Sie die Kundennummer aller Kunden aus, die ausschließlich Produkte von Siemens bestellt (bzw. in Rechnung gestellt bekommen) haben. (4 Punkte)

b) Geben Sie das (die) billigsten Produkt(e) vom Hersteller „Siemens“ an. (4 Punkte)

3) Formulieren Sie die folgenden SQL-Anfragen unter Verwendung von geeigneten Aggregatfunktionen:

a) Geben Sie das minimale und das maximale Gehalt aller Verkäufer der Elektroabteilung an. (3 Punkte)

b) Geben Sie für jede Abteilung die Anzahl der dort angestellten Verkäufer an. (4 Punkte)c) Geben Sie für alle Verkäufer der Elektroabteilung, die weniger als 100 unterschiedliche Kunden bedient haben, ihre Verkäufernummern, Verkäufernamen und die Anzahl der von ihnen bedienten Kunden an. (5 Punkte)

Aufgabe 4: Synchronisationsverfahren (15 Punkte)

1) Gegeben sei das folgende Schedule:

R1(z) R4(y) W5(x) R2(x) R3(z) W1(x) R4(z) W3(x) W4(y) W2(y) W3(z) (serialisierbar)

a) Erstellen Sie für diese Schedule den Abhängigkeitsgraphen.b) Ist sie serialisierbar (begründen Sie Ihre Antwort)?

Wenn ja, geben Sie die äquivalente serielle Schedule an.

(Beachten Sie auch Aufgabe 4.2 weiter unten!)

2) Gegeben sei das folgende Schedule (10 Punkte):

R1(z) R4(y) W5(x) R2(x) R3(z) W1(x) R4(z) W3(x) W4(y) W2(y) W3(z)

Welche (um die Sperren erweiterte!) Ergebnisschedule würde vom Zweiphasensperrprotokoll Preclaiming erzeugt? (Geben Sie die vollständige Schedule an!)

Aufgabe 5: JDBC (15 Punkte)

Achtung: Beachten Sie auch die Teilaufgabe auf der folgenden Seite!

1) Beim Auslesen welcher Java-Datentypen muss die Nullmarke besonders behandelt werden? Was passiert wenn dies nicht gemacht wird? Wie und wann im Programmablauf müssen die Nullmarken behandelt werden?

Lösung für Praktikeraufgabe

Kurs:Wirtschaftsinformatik SS09 Datenbankmanagementsysteme/Teil 2

Inhaltsverzeichnis

Probeklausur 0

Relationentypen (10 Punkte)

Die Drei-Ebenen-Schemaarchitektur (10 Punkte)

Relationale Algebra

SQL

Transaktionen und Deadlocks (12 Punkte)

Synchronisationsverfahren

JDBC

Zusatzaufgabe: Praktikeraufgabe (5 Punkte)

Probeklausur 1

Aufgabe 1: Relationentypen (10 Punkte)

Aufgabe 2: Verbundarten (12 Punkte)

Aufgabe 3: Relative Algebra

Aufgabe 4: SQL (32 Punkte)

Aufgabe 5 Transaktionsmanagement

Probeklausur 2

Aufgabe 1: Relationale Algebra (15 Punkte)

Aufgabe 2: SQL

Aufgabe 3: SQL-Theorie (5 Punkte)

Aufgabe 4: Synchronisationsverfahren (15 Punkte)

Aufgabe 4 No.2 Bild einfügen

Aufgabe 5: JDBC

Zusatzaufgabe: Praktikeraufgabe (5 Punkte)

Probeklausur 4

Aufgabe 1: Die Drei-Ebenen-Schemaarchitektur (10 Punkte)

Aufgabe 2: Relationale Algebra (30 Punkte)

Aufgabe 3: SQL (30 Punkte)

Aufgabe 4: Synchronisationsverfahren (15 Punkte)

Aufgabe 5: JDBC (15 Punkte)