Datenbank Praxis - Exam

Aufgabe 1)

Database Schema: Die folgende relationalen Schemata sind gegeben:

• Student(student_id, name, major)
• Professor(professor_id, name, department)
• Course(course_id, title, department)
• Enrollment(student_id, course_id, grade)
• Teaching(professor_id, course_id)

a)

Schreibe einen relationalen Algebra-Ausdruck, um die Namen aller Studenten (name) zurückzugeben, die im Studiengang 'Informatik' (major = 'Informatik') eingeschrieben sind.

Lösung:

Um die Namen aller Studenten zurückzugeben, die im Studiengang 'Informatik' (major = 'Informatik') eingeschrieben sind, kannst Du den folgenden relationalen Algebra-Ausdruck verwenden:

• Selektiere die Studenten, die 'Informatik' als Studienfach haben: σmajor='Informatik'(Student)
• Projiziere die Namen dieser Studenten: πname(σmajor='Informatik'(Student))

Zusammengefasst: πname(σmajor='Informatik'(Student))

b)

Verwende die Projektion und Verbund in relationaler Algebra, um die Namen und Abteilungen der Professoren zurückzugeben, die an einem Kurs beteiligt sind, den ein Student mit einer Note 'A' belegt hat.

Lösung:

Um die Namen und Abteilungen der Professoren zurückzugeben, die an einem Kurs beteiligt sind, den ein Student mit einer Note 'A' belegt hat, kannst Du den folgenden relationalen Algebra-Ausdruck verwenden:

• Finde die Studenten, die eine Note 'A' erhalten haben: σgrade='A'(Enrollment)
• Verknüpfe diese mit den Kursen: Enrollment ⨝ Course
• Weiter verknüpfe dies mit den Professoren: (σgrade='A'(Enrollment) ⨝ Course) ⨝ Teaching ⨝ Professor
• Projiziere dann die benötigten Attribute (name und department): πProfessor.name, Professor.department((σgrade='A'(Enrollment) ⨝ Course) ⨝ Teaching ⨝ Professor)

Zusammengefasst: πProfessor.name, Professor.department((σgrade='A'(Enrollment) ⨝ Course) ⨝ Teaching ⨝ Professor)

c)

Finde mithilfe von Selektion und kartesischem Produkt alle Paare von Studenten (student_id) und Professoren (professor_id), bei denen die Studenten mehr als 2 Kurse belegt haben und die Professoren in derselben Abteilung wie die Kurse der Studenten lehren. Angenommen, der Departmentsname aus Student.major und Professor.department stimmen überein.

Lösung:

Um alle Paare von Studenten (student_id) und Professoren (professor_id) zu finden, bei denen die Studenten mehr als 2 Kurse belegt haben und die Professoren in derselben Abteilung wie die Kurse der Studenten lehren, kannst Du die folgenden Schritte in relationaler Algebra verwenden:

• Finde die Studenten, die mehr als 2 Kurse belegt haben: Berechne zunächst die Anzahl der Kurse, die jeder Student belegt hat. πstudent_id, COUNT(course_id)(Enrollment) | COUNT(course_id)>2
• Selektiere die Professoren, deren Abteilung mit dem Studienfach der Studenten übereinstimmt: πstudent_id, professor_id(σStudent.major = Professor.department(Student × Professor))
• Kombiniere die beiden Ergebnisse: Verwende eine Selektion, um sicherzustellen, dass die Studenten und Professoren in derselben Abteilung sind: σStudent.student_id = Enrollment.student_id( Enrollment ⨝ Course ⨝ Teaching ⨝ Professor)

Zusammengefasst:

1. Berechne die Anzahl der Kurse, die jeder Student belegt hat.StudentCourses = γstudent_id, COUNT(course_id)→course_count(Enrollment)
2. Filtere die Studenten, die mehr als 2 Kurse belegt haben:StudentsWithMore2Courses =σcourse_count > 2(StudentCourses)
3. Führe die kartesischen Produkte und Selektionsoperationen durch, um die relevanten Paare zurückzugeben:EligiblePairs = σStudent.major = Professor.department(πstudent_id(StudentsWithMore2Courses) × πprofessor_id(Professor))

d)

Berechne die Durchschnittsnote aller Studenten pro Kurs. Verwende Aggregatfunktion (optional) und kombinierte Operationen in relationaler Algebra, um dies zu erreichen.

Lösung:

Um die Durchschnittsnote aller Studenten pro Kurs zu berechnen, verwendest Du Aggregatfunktionen und kombinierte Operationen in relationaler Algebra. Hier sind die Schritte dazu:

• Verwende die Aggregatfunktion AVG, um die Durchschnittsnote zu berechnen: γcourse_id, AVG(grade)(Enrollment)

Zusammengefasst:

γcourse_id, AVG(grade)(Enrollment)

Aufgabe 2)

ER-Modelle und ER-DiagrammeEin fiktives Unternehmen möchte eine Datenbank entwickeln, um seine Geschäftsabläufe zu verwalten. Das Unternehmen plant, die folgenden Entitäten in der Datenbank zu verwalten:

• Kunde: Enthält Informationen über die Kunden. Attribute: Kundennummer (Schlüsselattribut), Name, Adresse, Telefonnummer.
• Produkt: Enthält Informationen über die Produkte. Attribute: Produktnummer (Schlüsselattribut), Name, Preis, Lagermenge.
• Bestellung: Enthält Informationen über die Bestellungen der Kunden. Attribute: Bestellnummer (Schlüsselattribut), Bestelldatum.

Die Beziehungen zwischen diesen Entitäten sind wie folgt:

• Ein Kunde kann mehrere Bestellungen aufgeben, aber eine Bestellung gehört zu genau einem Kunden.
• Ein Produkt kann in mehreren Bestellungen enthalten sein, und eine Bestellung kann mehrere Produkte enthalten.

a)

1. Erstelle das ER-Diagramm für die oben beschriebene Datenbank. Stelle darin alle Entitäten, Attribute (inkl. Schlüsselattribute) und die Beziehungen zwischen den Entitäten dar. Achte dabei besonders auf die Kardinalitäten.

Lösung:

ER-Modelle und ER-DiagrammeSubübung 1: Erstelle das ER-Diagramm für die beschriebenen Entitäten und Beziehungen.Das ER-Diagramm für die fiktive Unternehmensdatenbank wird wie folgt aussehen:

• KundeAttribute:
  • Kundennummer (Schlüsselattribut)
  • Name
  • Adresse
  • Telefonnummer
• ProduktAttribute:
  • Produktnummer (Schlüsselattribut)
  • Name
  • Preis
  • Lagermenge
• BestellungAttribute:
  • Bestellnummer (Schlüsselattribut)
  • Bestelldatum

Beziehungen:

• Ein Kunde kann mehrere Bestellungen aufgeben (1:N).
• Eine Bestellung gehört zu genau einem Kunde (N:1).
• Ein Produkt kann in mehreren Bestellungen enthalten sein (N:M).
• Eine Bestellung kann mehrere Produkte enthalten (N:M).

Das ER-Diagramm visualisiert die Entitäten und ihre Attribute sowie die Beziehungen zwischen den Entitäten inklusive der Kardinalitäten:

• Kunde --- (1:N) --- Bestellung --- (N:1) --- Kunde
• Bestellung --- (N:M) --- Produkt

Wir können das Diagramm in Form einer Skizze darstellen:

• Entitäten:
  • Kunde
  • Produkt
  • Bestellung
• Attribute:
  • Kundennummer, Name, Adresse, Telefonnummer (Kunde)
  • Produktnummer, Name, Preis, Lagermenge (Produkt)
  • Bestellnummer, Bestelldatum (Bestellung)
• Beziehungen:
  • Kunde ––_(1:N)–– Bestellung ––_(N:1)–– Kunde
  • Bestellung ––_(N:M)–– Produkt

Das ER-Diagramm sollte alle drei Entitäten und die Beziehungen zwischen ihnen klar zeigen, um die Geschäftslogik der Datenbank zu erleichtern.

b)

2. Definiere die relationalen Tabellen, die aus dem ER-Diagramm abgeleitet werden. Gib für jede Tabelle die Attribute und die entsprechenden Primärschlüssel an.

Lösung:

ER-Modelle und ER-DiagrammeDas fiktive Unternehmen entwickelt eine Datenbank zur Verwaltung seiner Geschäftsabläufe. Auf Basis des ER-Diagramms definieren wir nun die relationalen Tabellen mit ihren Attributen und Primärschlüsseln.Subübung 2: Definiere die relationalen Tabellen, die aus dem ER-Diagramm abgeleitet werden. Gib für jede Tabelle die Attribute und die entsprechenden Primärschlüssel an.Auf Basis des ER-Diagramms definieren wir folgende Tabellen:

• Kunde Tabelle:Attribute:
  • Kundennummer (Primärschlüssel)
  • Name
  • Adresse
  • Telefonnummer
• Produkt Tabelle:Attribute:
  • Produktnummer (Primärschlüssel)
  • Name
  • Preis
  • Lagermenge
• Bestellung Tabelle:Attribute:
  • Bestellnummer (Primärschlüssel)
  • Bestelldatum
  • Kundennummer (Fremdschlüssel zu Kunde.Kundennummer)
• Bestellungsprodukt Tabelle (Join-Tabelle):Attribute:
  • Bestellnummer (Primärschlüssel, Fremdschlüssel zu Bestellung.Bestellnummer)
  • Produktnummer (Primärschlüssel, Fremdschlüssel zu Produkt.Produktnummer)
  • Anzahl (die Menge des Produkts in der Bestellung)

Die relationalen Tabellen können wie folgt definiert werden:

CREATE TABLE Kunde (   Kundennummer INT PRIMARY KEY,   Name VARCHAR(100),   Adresse VARCHAR(150),   Telefonnummer VARCHAR(15) );CREATE TABLE Produkt (   Produktnummer INT PRIMARY KEY,   Name VARCHAR(100),   Preis DECIMAL(10, 2),   Lagermenge INT );CREATE TABLE Bestellung (   Bestellnummer INT PRIMARY KEY,   Bestelldatum DATE,   Kundennummer INT,   FOREIGN KEY (Kundennummer) REFERENCES Kunde(Kundennummer) );CREATE TABLE Bestellungsprodukt (   Bestellnummer INT,   Produktnummer INT,   Anzahl INT,   PRIMARY KEY (Bestellnummer, Produktnummer),   FOREIGN KEY (Bestellnummer) REFERENCES Bestellung(Bestellnummer),   FOREIGN KEY (Produktnummer) REFERENCES Produkt(Produktnummer) );

c)

3. Implementiere einen SQL-Befehl, der eine neue Bestellung in der Datenbank anlegt. Die Bestellung beinhaltet einen neuen Kunden sowie zwei Produkte. Stelle sicher, dass dabei alle notwendigen Referenzen korrekt gesetzt werden. Gehe davon aus, dass die Produkte bereits in der Datenbank existieren.

INSERT INTO Kunde (Kundennummer, Name, Adresse, Telefonnummer) VALUES (1, 'Max Mustermann', 'Musterstraße 1, 12345 Musterstadt', '0123456789'); INSERT INTO Bestellung (Bestellnummer, Bestelldatum, Kundennummer) VALUES (1, '2023-10-01', 1); INSERT INTO Bestellungsdetails (Bestellnummer, Produktnummer, Menge) VALUES (1, 10, 1), (1, 20, 2);

Lösung:

ER-Modelle und ER-DiagrammeDas fiktive Unternehmen entwickelt eine Datenbank zur Verwaltung seiner Geschäftsabläufe. In dieser Übung werden wir nun SQL-Befehle schreiben, um eine neue Bestellung hinzuzufügen, die einen neuen Kunden und zwei existierende Produkte beinhaltet.Subübung 3: Implementiere einen SQL-Befehl, der eine neue Bestellung in der Datenbank anlegt. Die Bestellung beinhaltet einen neuen Kunden sowie zwei Produkte. Stelle sicher, dass dabei alle notwendigen Referenzen korrekt gesetzt werden. Gehe davon aus, dass die Produkte bereits in der Datenbank existieren.Die Implementierung der SQL-Befehle könnte wie folgt aussehen:

• Füge den neuen Kunden in die Tabelle Kunde ein.
• Lege die neue Bestellung in der Tabelle Bestellung an.
• Füge die Produkte zur Bestellung in der Tabelle Bestellungsprodukt hinzu.

Hier sind die erforderlichen SQL-Befehle:

-- Füge den neuen Kunden hinzuINSERT INTO Kunde (Kundennummer, Name, Adresse, Telefonnummer)VALUES (1, 'Max Mustermann', 'Musterstraße 1, 12345 Musterstadt', '0123456789');-- Lege die neue Bestellung anINSERT INTO Bestellung (Bestellnummer, Bestelldatum, Kundennummer)VALUES (1, '2023-10-01', 1);-- Füge Produkte zur Bestellung hinzuINSERT INTO Bestellungsprodukt (Bestellnummer, Produktnummer, Anzahl)VALUES (1, 10, 1), (1, 20, 2);

Erklärung der SQL-Befehle:

• INSERT INTO Kunde: Fügt einen neuen Kunden in die Tabelle Kunde ein. In diesem Fall hat der Kunde die Kundennummer 1, den Namen 'Max Mustermann', die Adresse 'Musterstraße 1, 12345 Musterstadt' und die Telefonnummer '0123456789'.
• INSERT INTO Bestellung: Legt eine neue Bestellung in der Tabelle Bestellung an. Die Bestellung hat die Bestellnummer 1, das Bestelldatum '2023-10-01' und gehört zum Kunden mit der Kundennummer 1.
• INSERT INTO Bestellungsprodukt: Fügt Produkte mit ihren Mengen zur Bestellung in der Tabelle Bestellungsprodukt hinzu. Es werden zwei Produkte mit den Produktnummern 10 und 20 zur Bestellnummer 1 hinzugefügt, wobei die Mengen je 1 und 2 betragen.

d)

4. Schreibe eine SQL-Abfrage, die alle Kunden auflistet, die mindestens ein Produkt mit einem Preis von über 50 Euro bestellt haben.

SELECT DISTINCT Kunde.Name FROM Kunde INNER JOIN Bestellung ON Kunde.Kundennummer = Bestellung.Kundennummer INNER JOIN Bestellungsdetails ON Bestellung.Bestellnummer = Bestellungsdetails.Bestellnummer INNER JOIN Produkt ON Bestellungsdetails.Produktnummer = Produkt.Produktnummer WHERE Produkt.Preis > 50;

Lösung:

ER-Modelle und ER-DiagrammeDas fiktive Unternehmen entwickelt eine Datenbank zur Verwaltung seiner Geschäftsabläufe. In dieser Übung wirst Du eine SQL-Abfrage schreiben, die alle Kunden auflistet, die mindestens ein Produkt mit einem Preis von über 50 Euro bestellt haben.Subübung 4: Schreibe eine SQL-Abfrage, die alle Kunden auflistet, die mindestens ein Produkt mit einem Preis von über 50 Euro bestellt haben.Hier ist die SQL-Abfrage:

SELECT DISTINCT Kunde.NameFROM KundeINNER JOIN Bestellung ON Kunde.Kundennummer = Bestellung.KundennummerINNER JOIN Bestellungsprodukt ON Bestellung.Bestellnummer = Bestellungsprodukt.BestellnummerINNER JOIN Produkt ON Bestellungsprodukt.Produktnummer = Produkt.ProduktnummerWHERE Produkt.Preis > 50;

Erklärung der SQL-Abfrage:

• SELECT DISTINCT Kunde.Name: Wählt die Namen der Kunden aus der Tabelle Kunde aus, wobei DISTINCT dafür sorgt, dass jeder Name nur einmal erscheint.
• FROM Kunde: Gibt die Tabelle Kunde als Ausgangspunkt der Abfrage an.
• INNER JOIN Bestellung ON Kunde.Kundennummer = Bestellung.Kundennummer: Verknüpft die Tabelle Kunde mit der Tabelle Bestellung basierend auf der Kundennummer.
• INNER JOIN Bestellungsprodukt ON Bestellung.Bestellnummer = Bestellungsprodukt.Bestellnummer: Verknüpft die Tabelle Bestellung mit der Tabelle Bestellungsprodukt basierend auf der Bestellnummer.
• INNER JOIN Produkt ON Bestellungsprodukt.Produktnummer = Produkt.Produktnummer: Verknüpft die Tabelle Bestellungsprodukt mit der Tabelle Produkt basierend auf der Produktnummer.
• WHERE Produkt.Preis > 50: Filtert die Ergebnisse, sodass nur diejenigen Produkte berücksichtigt werden, deren Preis größer als 50 Euro ist.

Aufgabe 3)

Du bist als Datenbankadministrator für ein großes E-Commerce-Unternehmen tätig. Deine Aufgabe ist es, die Transaktionen in dem Datenbanksystem zu verwalten und zu steuern. Angenommen, du hast es mit einem System zu tun, das mehrere Isolationsebenen unterstützt, und du musst sicherstellen, dass die ACID-Prinzipien eingehalten werden. Ebenfalls müssen Mechanismen zur gleichzeitigen Bearbeitung und zur Fehlerbehandlung implementiert werden.

a)

Frage 1: Erläutere das ACID-Prinzip und beschreibe, warum jedes dieser Prinzipien für die Verwaltung von Transaktionen im E-Commerce-System wichtig ist. Vergleiche anschließend kurz, wie sich eine Nichteinhaltung der ACID-Prinzipien auf das System auswirken könnte.

Lösung:

Das ACID-Prinzip:

• Atomicity (Atomarität): Eine Transaktion muss als unteilbare Einheit behandelt werden. Entweder werden alle Operationen einer Transaktion vollständig durchgeführt, oder keine. Dies ist wichtig, um sicherzustellen, dass keine unvollständigen Transaktionen in der Datenbank existieren, was inkonsistente Zustände verhindern würde.
• Consistency (Konsistenz): Eine Transaktion führt von einem konsistenten Zustand in einen anderen. Die Geschäftsregeln und Constraints der Datenbank dürfen zu keinem Zeitpunkt verletzt werden. Dies ist entscheidend, um die Integrität der Daten und die Einhaltung der Geschäftslogik zu gewährleisten.
• Isolation (Isolation): Transaktionen müssen so ausgeführt werden, als ob sie unabhängig voneinander ablaufen. Eine Transaktion sollte keine Auswirkungen auf andere laufende Transaktionen haben. Dies ist wichtig, um zu verhindern, dass konkurrierende Transaktionen zu Inkonsistenzen führen.
• Durability (Dauerhaftigkeit): Sobald eine Transaktion abgeschlossen ist, müssen ihre Ergebnisse dauerhaft in der Datenbank gespeichert werden, selbst bei Systemausfällen. Dies stellt sicher, dass abgeschlossene Transaktionen nicht verloren gehen.

Warum ist jedes dieser Prinzipien für die Verwaltung von Transaktionen im E-Commerce-System wichtig?

• Atomicity: Im E-Commerce ist es notwendig, dass eine Transaktion vollständig ausgeführt wird oder gar nicht. Stellt man sich vor, ein Kunde hat bestellt, aber die Zahlung wurde nicht abgeschlossen, dann kann das Unternehmen entweder Geld verlieren oder der Kunde erhält Produkte ohne Bezahlung.
• Consistency: Daten wie Lagerbestände, Kundenkonten und Bestellungen müssen stets konsistent sein. Inkonsistenz könnte zu doppelten Abbuchungen, falschen Lagerbeständen oder verpassten Bestellungen führen.
• Isolation: Bei hohen Transaktionsvolumina (z.B. während eines Ausverkaufs) ist es wichtig, dass parallele Transaktionen sich nicht gegenseitig beeinflussen, um sicherzustellen, dass jede Transaktion korrekt und konsistent abgeschlossen wird.
• Durability: Die Ergebnisse einer getätigten Transaktion müssen auch bei einem Systemausfall (z.B. Stromausfall) erhalten bleiben. Andernfalls könnte ein Kunde eine bestätigte Bestellung verlieren.

Auswirkungen der Nichteinhaltung der ACID-Prinzipien:

Wenn das ACID-Prinzip nicht eingehalten wird, kann dies zu verschiedenen schwerwiegenden Problemen führen:

• Verlust oder Korruption von Daten: Ohne Atomicity und Durability könnten Daten verloren gehen oder beschädigt werden.
• Inkonsistente Daten: Ohne Consistency und Isolation könnten Inkonsistenzen auftreten, die den Betrieb und die Zuverlässigkeit des Systems beeinträchtigen.
• Vertrauensverlust: Kunden könnten das Vertrauen in das System verlieren, wenn Transaktionen fehlschlagen oder falsche Ergebnisse liefern.
• Finanzielle Verluste: Unternehmen könnten finanzielle Verluste erleiden, wenn Transaktionen nicht korrekt verarbeitet werden.

b)

Frage 2: Ein Kunde sendet eine Bestellung ab, die jedoch aufgrund eines Fehlers im Netzwerk unterbrochen wird. Beschreibe, wie das System unter Verwendung der Fehlerbehandlungsmechanismen (Rollback, Recovery) diesen Vorfall behandeln soll, um die Konsistenz der Datenbank zu gewährleisten.

Lösung:

Fehlerbehandlungsmechanismen (Rollback, Recovery):

• Rollback: Ein Rollback wird verwendet, um eine Transaktion rückgängig zu machen, die nicht vollständig erfolgreich war. Wenn ein Kunde eine Bestellung absendet und diese aufgrund eines Fehlers im Netzwerk unterbrochen wird, sollte das System automatisch einen Rollback ausführen, um alle Änderungen, die während dieser Transaktion gemacht wurden, rückgängig zu machen. Dies stellt sicher, dass die Datenbank in ihren letzten konsistenten Zustand zurückversetzt wird und keine unvollständigen oder inkonsistenten Daten verbleiben.
• Recovery: Die Recovery-Mechanismen kommen ins Spiel, nachdem ein Fehler aufgetreten ist, um das System wieder in einen konsistenten Zustand zu bringen. In einem E-Commerce-System könnten Recovery-Mechanismen wie Write-Ahead Logging (WAL) verwendet werden. Hierbei werden alle Änderungen zuerst in ein Log geschrieben, bevor sie in die Hauptdatenbank übernommen werden. Wenn ein Fehler auftritt und die Transaktion gestoppt wird, verwendet das System das Log, um entweder die Transaktion vollständig zu wiederholen (Redo) oder rückgängig zu machen (Undo), je nachdem, in welchem Stadium die Transaktion unterbrochen wurde.

Beispielprozess zur Behandlung des Vorfalls:

• 1. Ermittlung des Fehlers: Das System erkennt, dass eine Netzwerkstörung die Bestelltransaktion unterbrochen hat.
• 2. Initiierung des Rollbacks: Das System führt sofort ein Rollback der Transaktion durch, um alle bisher vorgenommenen Änderungen rückgängig zu machen.
• 3. Benachrichtigung des Kunden: Der Kunde wird über den Fehler informiert und gebeten, die Bestellung erneut abzuschicken.
• 4. Recovery-Prozess: Das System verwendet die Recovery-Mechanismen, um sicherzustellen, dass alle anderen Transaktionen und Daten konsistent bleiben. Falls erforderlich, werden Daten aus dem Log wiederhergestellt.
• 5. Überprüfung der Konsistenz: Das System überprüft nach Abschluss des Rollbacks und des Recovery-Prozesses, ob die Datenbank konsistent ist.

Durch den Einsatz dieser Mechanismen stellt das System sicher, dass die Datenbank konsistent und zuverlässig bleibt, selbst wenn Fehler auftreten, und dass keine unvollständigen oder inkorrekten Transaktionen die Integrität des Systems beeinträchtigen.

c)

Frage 3: Angenommen, zwei Transaktionen, A und B, versuchen gleichzeitig auf dieselben Datensätze zuzugreifen. Beschreibe anhand der verschiedenen Isolationsebenen (Read Uncommitted, Read Committed, Repeatable Read, Serializable), wie das Datenbanksystem die Concurrency Control handhaben sollte. Erkläre, wie die Verwendung von Sperrmechanismen (Locks) und Zeitstempelverfahren dabei helfen kann.

Lösung:

Concurrency Control und Isolationsebenen:

• Read Uncommitted: Diese Isolationsebene erlaubt Transaktionen, auch nicht abgeschlossene Änderungen (dirty reads) zu lesen. Dies kann zu inkonsistenten Daten führen, wenn eine der Transaktionen einen Rollback durchführt.
• Read Committed: Hier dürfen Transaktionen nur die Daten lesen, die von anderen Transaktionen bereits committed wurden. Dirty Reads sind ausgeschlossen, aber Non-Repeatable Reads (d.h. ein Wert, der sich innerhalb einer Transaktion ändert) können vorkommen.
• Repeatable Read: Diese Ebene garantiert, dass einmal gelesene Daten innerhalb einer Transaktion unverändert bleiben. Es verhindert Non-Repeatable Reads, kann jedoch Phantom Reads (Einfügen oder Löschen von Datensätzen) nicht komplett verhindern.
• Serializable: Die höchste Isolationsebene. Sie stellt sicher, dass Transaktionen so ausgeführt werden, als wären sie seriell (nacheinander). Dies verhindert Dirty Reads, Non-Repeatable Reads und Phantom Reads, ist aber auch die am wenigsten performante Ebene aufgrund der umfangreichen Sperren.

Verwendung von Sperrmechanismen (Locks):

• Shared Lock (S-Sperre): Wird verwendet, wenn eine Transaktion Daten liest. Andere Transaktionen dürfen ebenfalls einen Shared Lock setzen, aber keine Änderungen vornehmen (keine Exclusive Locks).
• Exclusive Lock (X-Sperre): Wird verwendet, wenn eine Transaktion Daten ändert. Solange diese Sperre aktiv ist, dürfen keine anderen Transaktionen die gesperrten Daten lesen oder ändern.
• Intention Locks: Diese helfen dabei, die Sperrstrukturen effizienter zu gestalten, insbesondere bei hierarchischen Datenmodellen.

Verwendung von Zeitstempelverfahren:

• Timestamp Ordering: Jeder Transaktion wird ein eindeutiger Zeitstempel zugewiesen beim Start. Die Datenbank verwaltet zwei Zeitstempel für jeden Datensatz: Einen für die zuletzt lesende Transaktion (Read Timestamp) und einen für die zuletzt schreibende Transaktion (Write Timestamp).
• Concurrency Control with Timestamps: Wenn eine Transaktion auf Daten zugreifen will, vergleicht das System ihre Zeitstempel mit den Zeitstempeln der zuletzt lesenden/schreibenden Transaktionen, um sicherzustellen, dass es keine Konflikte gibt. Bei Konflikten kann ein Rollback ausgelöst werden.

Beispiel für die Handhabung von Transaktionen A und B:

Angenommen, Transaktionen A und B greifen auf dieselben Datensätze zu:

• Read Uncommitted: Beide Transaktionen könnten der Reihe nach ohne Sperren lesen, aber es besteht das Risiko, dass A unkommittierte Änderungen von B liest oder umgekehrt.
• Read Committed: Transaktion A setzt nach einem Commit von B einen Shared Lock zum Lesen und wartet, wenn B einen Exclusive Lock hat.
• Repeatable Read: A setzt einen Shared Lock, der bis zum Ende der Transaktion bestehen bleibt. B wird blockiert, wenn es versucht, einen Exclusive Lock zu setzen.
• Serializable: A und B setzen umfassendere Sperren (entweder Shared oder Exclusive), um sicherzustellen, dass sie nacheinander agieren, was Konflikte vollständig verhindert.

Durch den Einsatz solcher Mechanismen stellt das E-Commerce-System sicher, dass die Datenintegrität und Konsistenz während gleichzeitiger Transaktionen gewährleistet bleiben.

d)

Frage 4: Angenommen, du hast die Wahl zwischen zwei verschiedenen Isolationsebenen für die Implementierung des Warenkorbsystems: Repeatable Read oder Serializable. Führe eine Berechnung durch, um die Auswirkungen auf die Leistung des Systems (z.B. Durchsatz, Latenz) zu quantifizieren. Nimm realistische Annahmen für die Anzahl der gleichzeitig durchgeführten Transaktionen und die durchschnittliche Dauer jeder Transaktion an.

Lösung:

Berechnung der Auswirkungen auf die Leistung des Systems:

Um die Auswirkungen der Isolationsebenen Repeatable Read und Serializable auf die Leistung des Warenkorbsystems zu quantifizieren, müssen wir einige realistische Annahmen treffen und entsprechende Berechnungen durchführen:

• Anzahl der gleichzeitig durchgeführten Transaktionen: 100
• Durchschnittliche Dauer jeder Transaktion: 500 Millisekunden
• Überkopfkosten für Sperren bei Repeatable Read: 10 Millisekunden pro Transaktion
• Überkopfkosten für Sperren bei Serializable: 40 Millisekunden pro Transaktion

Repeatable Read:

• Durchsatz: Der Durchsatz ist die Anzahl der Transaktionen, die pro Sekunde abgeschlossen werden können. Bei Repeatable Read beträgt die durchschnittliche Dauer einer Transaktion 500 Millisekunden + 10 Millisekunden (Überkopf) = 510 Millisekunden.
• Berechnung: Pro Transaktion: 510 ms = 0,51 Sekunden Durchsatz: \frac{100}{0,51} = 196,08 \text{ Transaktionen pro Sekunde}(100 Transaktionen, geteilt durch 0,51 Sekunden pro Transaktion)
• Latenz: Die Latenz ist die Zeit, die benötigt wird, um eine Transaktion abzuschließen. Bei Repeatable Read beträgt die Latenz 510 Millisekunden.

Serializable:

• Durchsatz: Bei Serializable beträgt die durchschnittliche Dauer einer Transaktion 500 Millisekunden + 40 Millisekunden (Überkopf) = 540 Millisekunden.
• Berechnung: Pro Transaktion: 540 ms = 0,54 Sekunden Durchsatz: \frac{100}{0,54} = 185,19 \text{ Transaktionen pro Sekunde}(100 Transaktionen, geteilt durch 0,54 Sekunden pro Transaktion)
• Latenz: Die Latenz ist die Zeit, die benötigt wird, um eine Transaktion abzuschließen. Bei Serializable beträgt die Latenz 540 Millisekunden.

Vergleich der beiden Isolationsebenen:

• Repeatable Read: Durchsatz: 196,08 Transaktionen pro Sekunde Latenz: 510 Millisekunden
• Serializable: Durchsatz: 185,19 Transaktionen pro Sekunde Latenz: 540 Millisekunden

Daraus ergibt sich, dass Repeatable Read einen höheren Durchsatz und eine geringere Latenz als Serializable aufweist. Dies liegt daran, dass der zusätzliche Überkopf für die Sperren bei Serializable höher ist. Da jedoch Serializable eine stärkere Isolation bietet, könnte es in Szenarien mit strengen Konsistenzanforderungen bevorzugt werden. Wenn Performance eine größere Rolle spielt, ist Repeatable Read die bessere Wahl.

Aufgabe 4)

Gegeben sind zwei Tabellen, 'students' und 'courses', in einer Datenbank eines Universitätsverwaltungssystems. Die 'students' Tabelle enthält die Spalten 'student_id', 'student_name' und 'major', während die 'courses' Tabelle die Spalten 'course_id', 'course_name' und 'student_id' enthält, die darstellt welcher Student welchen Kurs belegt hat. students Tabelle:| student_id | student_name | major ||------------|--------------|-------------|| 1 | Alice | Informatik || 2 | Bob | Mathematik || 3 | Carol | Informatik || 4 | Dave | Physik | courses Tabelle:| course_id | course_name | student_id ||-----------|-------------|------------|| 101 | Datenbanken | 1 || 102 | Statistik | 2 || 103 | Algebra | 3 || 104 | Physik I | 4 || 105 | Datenbanken | 2 | Verwende die JOIN-Operationen in SQL, um die folgenden Aufgaben zu lösen,

a)

Erstelle eine SQL-Abfrage, um eine Liste aller Kurse und der dazugehörigen Studenten anzuzeigen. Verwende dafür eine INNER JOIN Operation.

Lösung:

Um eine Liste aller Kurse und der dazugehörigen Studenten anzuzeigen, können wir die INNER JOIN Operation verwenden, um die Tabellen 'students' und 'courses' zu verknüpfen. Wir werden die 'student_id' Spalte in beiden Tabellen verwenden, um die Verknüpfung zu erstellen.

Hier ist die SQL-Abfrage dafür:

SELECT courses.course_id, courses.course_name, students.student_id, students.student_nameFROM coursesINNER JOIN students ON courses.student_id = students.student_id;

Diese Abfrage ergibt eine Liste aller Kurse zusammen mit den zugehörigen Studenten.

b)

Schreibe eine SQL-Abfrage, die eine Liste aller Studenten mit ihren belegten Kursen anzeigt, einschließlich der Studenten, die keine Kurse belegt haben. Nutze dafür eine LEFT JOIN Operation.

Lösung:

Um eine Liste aller Studenten mit ihren belegten Kursen anzuzeigen, einschließlich der Studenten, die keine Kurse belegt haben, verwenden wir die LEFT JOIN Operation. Diese Art von JOIN stellt sicher, dass alle Datensätze aus der 'students' Tabelle angezeigt werden, auch wenn keine entsprechenden Einträge in der 'courses' Tabelle vorhanden sind.

Hier ist die SQL-Abfrage dafür:

SELECT students.student_id, students.student_name, students.major, courses.course_id, courses.course_nameFROM studentsLEFT JOIN courses ON students.student_id = courses.student_id;

Diese Abfrage ergibt eine Liste aller Studenten zusammen mit ihren belegten Kursen. Wenn ein Student keinen Kurs belegt hat, werden die entsprechenden Felder aus der 'courses' Tabelle als NULL angezeigt.