Aufgabe 2 (SQL)

(1)

___________________________________________________________________________

Seite 1/8

Prüfungsfach: Datenbanken Arbeitszeit: 120 Minuten Zugelassene Hilfsmittel: Skript Name:

Prüfungstermin: 05.02.2004 Vorname: Sem.:

Matr.Nummer:

Aufgabe 1 (relationale Datenbanken)

1. Bitte betrachten Sie die Tabelle SBOOK der Flugreservierungen im Anhang. Die Tabelle enthält mehrere Millionen Einträge. Die Reisebüros benutzen folgendes Programm, um auf die Buchungen ihrer Kunden zuzugreifen:

Was würden Sie machen, um einen schnellen Zugriff auf die Daten zu ermöglichen? Bitte erläutern Sie anhand eines Beispiels, welche Auswirkungen Ihre Maßnahmen haben!

Einführung eines passenden Sekundärschlüssels.

z.B. Hier Primärschlüssel: Kunde + Sekundärschlüssel: Abflugdatum

Dadurch ist bei einer Suche über diese beiden Schlüssel eine höhere Performance erziehlbar.

Jedoch, ist der Aufwand an Rechenzeit und Speicherbedarf höher – da die dazugehörigen Indextabellen immer mit aktualisiert werden müssen.

2. Das Feld AGENCYNUMBER in der Tabelle SBOOK ist ein Fremdschlüssel auf die Tabelle STRAVELAG der Reisebüros. Was bedeutet das? Warum macht man so etwas?

In SBOOK steht nicht bei jeder Buchung alle Daten des Reisebüros, sondern lediglich eine Nummer für das jeweilige Reisebüro.

Dies verhindert Datenredundanzen. D.h. in einer Tabelle werden nicht die gleichen Daten immer wieder wiederholt.

Des weiteren, müssen Änderungen eines Wertes eines Reisebüros ( Anschrift ) nur einmal gemacht werden und nicht 100tausend x mal – UPDATE Anomalie.

3. Ist die folgende Aussage richtig? Wenn eine Tabelle T1 einen Fremdschlüssel auf eine einspaltige Tabelle T2hat, sind die Werte von T2 in T1enthalten. Folglich kann man auf die einspaltige Tabelle T2verzichten. Bitte begründen Sie Ihre Aussage mit einem passenden Beispiel!

Mann kann NICHT darauf verzichten, da T1 nicht unbedingt belegt sein muss!

z.B. Studiengänge: Es können Studiengänge angelegt werden, später kann dann auch ein Student eingetragen werden (INSERT).

4. Kann das Problem der Änderungsanomalie bei einspaltigen Tabellen vorkommen? Bitte begründen Sie Ihre Aussage!

Nein, da die Änderung nur in dieser gemacht wird. Andere nehmen Bezug (Fremdkey) darauf.

___________________________________________________________________________

Seite 2/8 Aufgabe 2 (SQL)

Bitte betrachten Sie die Tabellen vom Anhang.

1. Schreiben Sie ein Programm, das alle Flugkombinationen ( = zwei Flüge) ausgibt (Fluggesellschaft, Flugverbindung, Ablug- und Ankunftsstadt), die Frankfurt als Umsteigeflughafen benutzen.

SELECT SC1.CARRNAME, SP1.CONNID, SP1.CITYFROM, SC2.CARRNAME, SP2.CONNID, SP2.CITYTO

FROM (

( SPFLI AS SP1 INNER JOIN SPFLI AS SP2

ON ( SP1.CITYTO="Frankfurt" AND SP2.CITYFROM="Frankfurt" ) )

INNER JOIN SCARR AS SC1 ON ( SP1.CARRID=SC1.CARRID ) )

INNER JOIN SCARR AS SC2 ON ( SP2.CARRID=SC2.CARRID ) ;

2. Schreiben Sie ein Programm, das alle Flüge der Lufthansa ausgibt (Flug- gesellschaft, Flugverbindung), die die maximale Flugdistanz unter allen Lufthansaflügen aufweisen. Gibt es Flüge anderer Fluggesellschaften, die über noch längere Distanzen gehen?

SELECT SC.CARRNAME, SP.CONNID

FROM SPFLI AS SP INNER JOIN SCARR AS SC ON ( SP.CARRID=SC.CARRID ) WHERE SP.DISTANCE=( SELECT MAX(DISTANCE)

FROM SPFLI

WHERE CARRID="LH") ;

3. Bitte geben Sie eine Liste der Fluggesellschaften mit der Summe der Buchungen in der Hauswährung der Fluggesellschaft aus.

SELECT SC.CARRNAME, COUNT(SB.BOOKID) AS Buchungen FROM ( SFLIGHT AS SF INNER JOIN SBOOK AS SB

ON ( SF.CARRID=SB.CARRID AND SF.CONNID=SB.CONNID ) )

INNER JOIN SCARR AS SC ON ( SF.CARRID=SC.CARRID ) GROUP BY SC.CARRNAME ;

4. Schreiben Sie ein Programm, das eine Liste mit der Anzahl der überbuchten Flüge für jede Fluggesellschaft ausgibt.

SELECT COUNT(CONNID) AS Anzahl_Ueberbuchungen FROM SFLIGHT AS SF

WHERE SF.SEATSMAX <= SEATSOCC;

___________________________________________________________________________

Seite 3/8 Aufgabe 3 (Datenmodellierung, SQL)

Der folgende Text beschreibt die Grundlage für ein Informationssystem der zuständi- gen Behörden:

Bundesstraßen besitzen eine eindeutige Kurzbezeichnung (z. B. B8) und einen Namen. Sie führen durch mehrere Ortschaften, wobei durch eine Ortschaft auch mehrere Bundesstraßen führen können. Ortschaften sind eindeutig einem Bundes- land zugeordnet. Bundesländer besitzen einen Namen und eine Einwohnerzahl.

Jedes Bundesland hat eine Hauptstadt.

1. Bitte modellieren Sie diesen Sachverhalt mit Hilfe eines Entity-Relationship- Diagramms. Bitte geben Sie anschließend das relationale Datenmodell an, das diesem ER-Diagramm entspricht.

___________________________________________________________________________

Seite 4/8

2. Wie verändert sich das ER-Modell und das zugehörige rel. Datenbankmodell, wenn es Ortschaften in verschiedenen Bundesländern mit dem gleichen Namen gibt?

3. Wie verändert sich das ER-Modell und das zugehörige rel. Datenbankmodell, wenn man auch Autobahnen berücksichtigt?

4. Die nachfolgende Tabelle soll zur Darstellung der Sachverhalte der Aufgabe 3 dienen.

Bundesstraße Kurzbezeichnung

Bundesstraße Beschreibung

Orts- kennziffer

Orts- name

Bundes- land

Bundesland Name

Einwohner Haupt- stadt

Bitte überführen Sie die Tabelle in die dritte Normalform! Bitte geben Sie dabei alle Zwischenergebnisse an (1. NF, 2. NF und 3. NF)! Geben Sie bitte insbesondere die funktionalen Abhängigkeiten zwischen den Attributen an! Bitte unterstreichen Sie die Primärschlüssel Ihrer Tabellen und machen Sie Fremdschlüsselbeziehungen kenntlich!

(2)

___________________________________________________________________________

Seite 5/8 5. Bitte geben Sie eine SQL-Anweisung, die

a. in alphabetischer Reihenfolge alle Ortschaften auflistet, durch die die Bundesstaraße 8 (B8) fährt.

b. pro Ortschaft die Anzahl der Bundesstraßen auflistet, die durch die Ortschaft fahren.

___________________________________________________________________________

Seite 6/8 Aufgabe 4 (Datenmodellierung, SQL)

Die Hochschulbibliothek verwaltet Bücher und Zeitschriften. Sowohl Bücher, als auch Zeitschriften haben einen Titel, ein Erscheinungsjahr und eine ISBN-Nummer.

Zeitschriften werden zusätzlich zum Erscheinungsjahr über eine laufende Nummer identifiziert (z. B. 1. Ausgabe, 2. Ausgabe, ..., 12. Ausgabe für monatlich erscheinende Zeitschriften). Bücher werden dagegen in verschiedenen Auflagen verlegt (z.B. „F. Jobst: Java, 2. Auflage) und haben einen, oder mehrere Autoren.

Zeitschriften enthalten eine Reihe von Artikeln, die ebenfalls von mehreren Autoren verfasst sein können. Bücher und Zeitschriften werden jeweils von einem Verlag verlegt. Bücher und Zeitschriften können durch maximal 10 Stichworte indiziert werden. Die Datenbank soll darüber hinaus den Preis von Büchern und Zeitschriften, die Adresse der Verlage, die Autorennamen sowie die Titel der in Zeitschriften veröffentlichten Artikel verwalten können.

1. Bitte modellieren Sie diesen Sachverhalt mit Hilfe eines Entity-Relationship- Diagramms. Bitte geben Sie anschließend das relationale Datenmodell an, das diesem ER-Diagramm entspricht.

___________________________________________________________________________

Seite 7/8

2. Bitte schreiben Sie ein Programm, das alle Bücher und Zeitschriften (mit Titel und Erscheinungsjahr) von einem vorgegebenen Autor auflistet.

3. Bitte geben Sie eine Liste aller Verlage aus, die entweder Bücher oder Zeitschriften verlegen, aber nicht beides.

___________________________________________________________________________

Seite 8/8 Anhang SPFLI: Flugverbindungen

SFLIGHT: Flugplan

SBOOK: Flugbuchung

STRAVELAG: Reisebüro

(3)

___________________________________________________________________________

Seite 1/7

Aufgabensteller: Prof. Dr. A. Tsakpinis Bitte deutlich schreiben!

Aufgabe 1 (relationale Datenbanken)

1. Bitte erläutern Sie den Unterschied zwischen einem Primärschlüssel und einem Schlüsselkandidaten!

Ein ausgezeichneter Schlüsselkandidat. Seine Wahl ist dem Programmierer überlassen.

Folgende zusätzliche Eigenschaften sind damit verbunden:

- Der Primärschlüssel darf keine NULL-Werte annehmen, - Über ihn ist der direkte Zugriff auf genau eine Zeile der Tabelle möglich,

2. Welche Rolle spielen Fremdschlüsselbeziehungen für die Datenkonsistenz? Bitte geben Sie ein passendes Beispiel an!

Über Fremdschlüssel werden Tabellen einer Datenbank miteinander verknüpft.

Es gibt immer einen definierten Wert des Fremdschlüssels, da immer ein dazugehöriger Primärschlüssel existiert ( Wahrung der Referentiellen Integrität durch die Datenbank ).

z.B. Bei einer Schule:

Die Tabelle „Fachbereich“ hat eine Spalte „Dekan“ der ein Fremdschlüssel auf die Tabelle

„Professor“ ist. Dadurch ist gewährleistet, dass der Dekan immer auch ein Professor des Institutes ist.

3. Ist die folgende Aussage richtig? Wenn eine Tabelle einen Fremdschlüssel auf eine einspaltige Tabellen hat, kann man auf die einspaltige Tabelle verzichten.

Bitte begründen Sie Ihre Aussage mit einem passenden Beispiel!

Nein, es kann nicht verzichtet werden.

z.B. eine einspaltige Tabelle „Stichwort“ die Fremdschlüssel in der Tabelle „Buch“ ist und zur Suche verwendet wird. Dieses Stichwort kann auch in mehreren Büchern vorkommen.

4. Bitte geben Sie ein einfaches relationales Datenbankmodell an, das die referentielle Integrität verletzt.

Tabelle: Student

Matrikelnr Name Adresse

2105345 Ehringer Eike Am Haslach 29

Tabelle: Einschreibung

Vorlesungsnr Student 100 1234567

Bei der Tabelle „Einschreibung“ ist die Spalte „Student“ Fremdschlüssel auf Tabelle „Student“.

Verletzung der referentiellen Integrität, da der Student „1234567“ nicht als Primärschlüssel in der Tabelle „Student“ definiert ist!

___________________________________________________________________________

Seite 2/7 Aufgabe 2 SQL

Bitte betrachten Sie folgende Tabellen:

SPFLI: Flugverbindungen

___________________________________________________________________________

Seite 3/7

1. Schreiben Sie ein Programm, das alle Flugverbindungen ausgibt (carrid, connid), die teurer sind, als der Durchschnitt aller Flüge. Wie verändert sich da Programm, wenn der Durchschnitt nicht über alle Flüge, sondern bezogen auf die Flüge einer Fluggesellschaft berechnet wird?

SELECT SF.CARRID AS Fluggesellschaft, SF.CONNID AS Flugnummer FROM SFLIGHT AS SF

WHERE SF.PRICE>AVG(SF.PRICE);

2. Schreiben Sie ein Programm, das alle Flüge ausgibt, die a. keine Buchungen, oder mehr als 100 Buchungen haben SELECT SF.CONNID AS FLugnummer

FROM SFLIGHT AS SF

WHERE SF.SEATSOCC = 0 OR SF.SEATSOCC > 100;

3. Schreiben Sie ein Programm, das die Reisebüros (Nummer, Bezeichnung) mit der Anzahl der durchgeführten Buchungen ausgibt, sortiert nach der Anzahl der Buchungen absteigend.

Tabelle mit den Reisebüros nicht gegeben? Jedoch aus Übungen bekannt – „STRAVELAG“

SELECT T.AGENCYNUM, T.NAME, COUNT(T.AGENCYNUM) AS Anzahl_Buchungen FROM STRAVELAG AS T INNER JOIN SBOOK AS B ON ( T.AGENCYNUM=B.AGENCYNUM ) GROUP BY T.NAME, T.AGENCYNUM

ORDER BY COUNT(T.AGENCYNUM) DESC;

4. Schreiben Sie ein Programm, das alle Verbindungen von Frankfurt nach San Francisco ausgibt, die eine Zwischenlandung benötigen (Fluggesellschaft und Verbindungsnummer für beide Verbindungen).

SELECT SC1.CARRNAME, SP1.CONNID, SC2.CARRNAME, SP2.CONNID FROM ( ( SPFLI AS SP1 INNER JOIN SPFLI AS SP2

ON ( SP1.CITYFROM="FRANKFURT" AND SP1.CITYTO=SP2.CITYFROM AND SP2.CITYTO="SAN FRANCISCO" ) )

INNER JOIN SCARR AS SC1 ON ( SP1.CARRID=SC1.CARRID ) ) INNER JOIN SCARR AS SC2 ON ( SP2.CARRID=SC2.CARRID );

5. Bitte berechnen Sie die Anzahl der Flugverbindungen der Lufthansa pro Zielland!

Berücksichtigen Sie dabei nur Länder mit mehr als 10 Flugverbindungen!

SELECT SP.COUNTRYTO AS Zielland,SP.CONNID AS Verbindung, Count(SP.CONNID) AS AnzahlvonCONNID

FROM SPFLI AS SP WHERE SP.CARRID="LH"

GROUP BY SP.CONNID, SP.COUNTRYTO HAVING COUNT(CONNID) > 10;

___________________________________________________________________________

Seite 4/7

Aufgabe 3 (Datenmodellierung, SQL, relationale Algebra, Normalisierung) Bei der Datenanalyse zur Erstellung einer Kundendatenbank für ein Versandhaus wurde eine EXCEL- Tabelle mit folgenden Spalten vorgefunden:

Bestellnummer, Bestelldatum, Rabatt, PreisGesamt, ArtikelNummer, Artikelname, Menge, PreisNetto, Kundennummer, Name, Ort, Lieferadresse, Ansprechpartner, Telefonnummer

Folgende Fakten müssen berücksichtigt werden:

- Eine Bestellung wird durch eine Bestellnummer eindeutig identifiziert.

- Eine Bestellung enthält mehrere Artikel. Ein Artikel wird in einer Bestellung maximal einmal mit der bestellten Menge aufgeführt.

- Eine Bestellung wird eindeutig einem Kunden zugeordnet. Ein Kunde kann natürlich mehrere Bestellungen tätigen.

- Der Rabattsatz bei jeder Bestellung neu ausgehandelt wird.

- Der Ansprechpartner bei jeder Bestellung eine andere Person sein kann.

- Ein Kunde kann mehrere Lieferadressen haben, so dass in jeder Bestellung die Lieferadresse angegeben werden muss.

- Die Telefonnummer ist dem angegebenen Ansprechpartner zugeordnet.

1. Bitte führen Sie die oben angegebene Tabelle in die dritte Normalform! Beginnen Sie bitte mit der Bestimmung eines Primärschlüssels für die angegebene Tabelle und der Überführung in die 2. Normalform.

Primärschlüssel: Bestellnummer, ArtikelNummer, Kundennummer, 2. NF.:

Objekte: -> { Attribute }

Bestellung -> { Bestellnummer, Bestelldatum, Rabatt, PreisGesamt } Artikel -> { ArtikelNummer, Artikelname, Menge, PreisNetto } Kunde -> { Kundennummer, Name, Ort, Lieferadresse, Ansprechpartner, Tel } 2. Bitte geben Sie ein ER-Diagramm an, aus dem sich die unter 1. angegebenen

Tabellen resultieren.

Relationales Datenmodell:

L-Adr: Kundennummer | Lieferadresse A-Partner: Kundennummer | Ansprechpartner | Tel

??? würde dieses Modell stimmen – insbesondere in Bezug auf Kunde | L-Adr | A-Partner ???

Bestellung Artikel

Kunde

entält

1 cn

Zuge- ordnet

n

c

Lieferadresse

Ansprechpartner Telefonnummer

(4)

___________________________________________________________________________

Seite 5/7

Bitte formulieren Sie folgende Anfragen als SQL-Anweisung und mit Hilfe von relationalen Operatoren:

a) Gesucht sind alle Bestellungen von Kunden (mit Namen und Wohnort), die einen Rabatt von mehr als 10% bekommen haben.

T1 := JOIN ( Bestellung, Bestellung.Kundennr=Kunde.Kundennr, Kunde ) T2 := REST ( T1, ( Rabatt > 10% ) )

T3 := PROJ ( T2, Name, Ort ) SELECT k.Name, k.Ort

FROM Bestellung AS b INNER JOIN Kunde AS k ON ( b.Kundennr=k.Kundennr ) WHERE b.Rabatt > 0.1;

b) Gesucht sind alle Artikel (ArtikelNummer, Menge, Bestelldatum), die im Jahr 2002 von Regensburger Kunden bestellt wurden.

??? Hier bin ich mir nicht sicher ob dass stimmt ???

SELECT A.Artikelnummer, A.Menge, B.Bestelldatum

FROM ( ( AK INNER JOIN Kunde AS K ON ( AK.Kundennummer=K.Kundennummer ) ) INNER JOIN Bestellung AS B ON ( AK.Bestellnummer=B.Bestellnummer ) ) INNER JOIN Artikel AS A ON ( AK.Bestellnummer=A.Bestellnummer ) WHERE K.Ort=“Regensburg“ AND B.Bestelldatum LIKE `*2002`;

3. Wie verändert sich das Datenmodell (das ER-Diagramm anpassen!) und das relationale Datenmodell, wenn einem Kunden stets der gleiche Rabattsatz einge- räumt wird?

Mein ER-Diagramm gibt den Kunden bereits immer nur den gleichen Rabattsatz…

Bemerkung

Bitte legen Sie alle Angaben willkürlich fest, die Sie meinen zu brauchen und in der Aufgabenstellung nicht finden!

Aufgabe 4 (Datenmodellierung, SQL)

Die Datenanalyse in einem Speditionsunternehmen hat für seinen Fuhrpark folgendes ergeben:

Ein Wagen hat ein Kennzeichen, das ihn identifiziert, und einen Typ. Es gibt mehrere Stellplätze.

Jeder Wagen steht auf genau einem Stellplatz, auf dem auch kein anderer Wagen stehen darf. Ein Fahrer wird durch seinen Namen identifiziert und bekommt ein Gehalt. Er darf mehrere Wagen fahren.

Ein Wagen darf auch von mehreren Fahrern gefahren werden. Es gibt verschiedene Routen. Für jede Route ist nur ein Fahrer zuständig, die Fahrer dürfen jedoch für mehrere Routen zuständig sein.

Wagen werden für mehrere Routen eingesetzt, für eine Route wird jedoch genau ein Wagen eingesetzt.

1. Bitte modellieren Sie diesen Sachverhalt mit Hilfe eines Entity-Relationship- Diagramms. Bitte geben Sie anschließend das relationale Datenmodell, das diesem Diagramm entspricht.

___________________________________________________________________________

Seite 6/7

Primärschlüssel: Kennzeichen, Name, StellplatzNummer, RoutenNummer KFZ -> { Kennzeichen | Typ }

Fahrer -> { Name | Gehalt } Stellplatz -> { Stellplatznummer } Route -> { RoutenNummer } ER-Diagramm:

Relationales Datenmodell:

Tabelle: KFZ

Kennzeichen | Typ

Tabelle: Fahrer

Name | Gehalt | Kennzeichen | RoutenNummer Tabelle: Stellplatz

Stellplatznummer

Tabelle: Route

RoutenNummer | Kennzeichen

2. Wie verändert sich das Modell und das zugehörige rel. Datenmodell,wenn eine Route nicht ein bestimmter Fahrer, sondern ein beliebiger Fahrer fahren kann?

Bitte geben Sie nur die Veränderungen an!

D.h. dass zwischen „Route“ (n:m) „Fahrer“ wird.

-> in der Tabelle „Route“ wird noch ein Fremdschlüssel von Faher (Name) eingefügt.

-> Zusätzlich ergibt sich auch noch eine Tabelle: RoutenNummer | Name KFZ

steht

Fahrer 1 Stellplatz

Route

fährt 1

n 1

Ist zustän

dig

n

1

Ist einges

etzt

1

n

___________________________________________________________________________

Seite 7/7

3. Wie verändert sich das Modell und das zugehörige rel. Datenmodell, wenn das Fuhrunternehmen Wagen verschiedener Größen (bis 7,5 t und größere) einsetzt, die jeweils eine passende Führerscheinklasse des Fahrers (Fahrerlaubnis für Fahrzeuge bis 7,5 t, oder Fahrerlaubnis für alle Fahrzeugtypen) voraussetzen?

Bitte geben Sie nur die Veränderungen an!

Eine Einspaltige Tabelle: Führerscheinklasse die als Primärschlüssel alle verfügbaren Klasen beinhalten.

Diese Tabelle wird dann (n:m) -> Fremdschlüssel dementsprechend gelegt - mit dem Fahrer gekoppelt und (n:1) mit der Tabelle KFZ ( Vorraussetzung )…

4. Was muss man im Datenmodell der Teilaufgabe 3 verändern, um einen Dienst- plan aufzustellen, auf dem tagesbezogen ersichtlich ist, welcher Wagen von welchem Fahrer gefahren wird? Wie wird sichergestellt, dass nur gültige Paare (Fahrer, Wagen) bzgl. der Führerscheinklasse berücksichtigt werden?

Ich würde dazu (n:m) Relation mit de Namen „Dienstplan“ kreieren zwischen der Tabelle: „Fahrer“

und Tabelle „KFZ“. Dadurch erhält Tabelle „KFZ“ als Fremdschlüssel den „Name“ des Fahrers.

Es entsteht auch noch eine weitere Tabelle: „Plan“ die sowohl den Primärschlüssel von „Fahrer“

und „KFZ“ beinhaltet.

Die Vorraussetzung der Führerscheinklasse würde ich wie in der Lösung von Aufgabe 3.

realisieren…

5. Bitte geben Sie eine SQL-Anweisung, die

a. den Dienstplan der kommenden Woche ausgibt.

b. Wie würden Sie sicherstellen, dass speziell für die Route 10 ein kleiner Wagen (< 7,5 t) eingesetzt wird? Bitte geben Sie eine verbale Beschreibung und keine SQL-Lösung an!

Ein JOIN von Tabelle „Route“ mit „KFZ“ über den Fremdschlüssel „Kennzeichen“.

Dann ein muss ich über die Führerscheinklasse bestimmen, dass das KFZ auch wirklich kleiner 7,5t hat.

___________________________________________________________________________

Seite 1/8

Aufgabensteller: Prof. Dr. A. Tsakpinis Bitte deutlich schreiben!

Aufgabe 1 Relationenmodell

Gegeben sei folgendes relationale Schema einer Studentendatenbank:

Student

Matrikelnummer Name Geschlecht Wohnort

123456 Kop M Regensburg

234567 Jannach W Regenstauf

NULL Meier M Lappersdorf

331123 Zanker W Regensburg

Vorlesung

Volesungsnummer Name Fachbereich

100 Datenbanken Informatik

101 Programmiersprachen Informatik

200 Rechnungswesen BW

Einschreibung

Volesungsnummer Student

100 123456 100 234567 101 332211 200 222312 105 123456

a) Besitzen die Relationen (Tabellen) einen Fremdschlüssel? Falls ja: Geben Sie den Fremdschlüssel an.

Tabelle: Einschreibung Fremdschlüssel: Student

b) Welche Zeilen in den Tabellen verstoßen gegen die Entitätsregel (Integritäts- bedingung!)? Geben Sie die Zeilen an und begründen Sie Ihre Entscheidung.

Tabelle: Student Spalte – Matrikelnummer - Primärschlüssel: NULL Tabelle: Einschreibung Spalte – Vorlesungsnr., Primärschlüssel nicht eindeutig 100,100

c) Welche Zeilen in den Tabellen verstoßen gegen die referentielle Integrität?

Geben Sie die Zeilen an und begründen Sie Ihre Entscheidung.

Tabelle: Einschreibung Spalte: Student Zeile: 332211,222312

Beide Matrikelnummern sind Fremdschlüssel, die nicht durch den Primärschlüssel in der Tabelle: Student definiert sind.

Tabelle: Einschreibung Spalte: Vorlesungsnr. Zeile: 105 Diese Forlesungsnr. ist nicht in der Tabelle: Vorlesung definiert.

(5)

___________________________________________________________________________

Seite 2/8

d) Wird eine Integrität verletzt, wenn man folgenden Befehl ausführt (Begründen Sie Ihre Antwort):

INSERT INTO Einschreibung VALUES (102, 331123);

Ja, da keine Vorlesung „102“ als Primärschlüssel in der Tabelle: Vorlesung definiert ist.

Aufgabe 2 Normalisierung

Gegeben sei folgendes Relationenschema mit Informationen über Vorlesungen und Studierende:

Vorlesung

Vorlesungsnummer Student Vorlesung Studentenname Fachbereich

100 123456 Datenbanken Kop Informatik

100 234567 Datenbanken Jannach Informatik

101 332211 Programmiersprachen Zanker BW

101 123456 Programmiersprachen Kop

Student

Matrikelnummer Name 123456 Kop 234567 Jannach NULL Meier 331123 Zanker

1. Erfüllt diese Relation die 1. und die 2. Normalform? Wenn nicht, dann führen Sie bitte die Relation in die 2. Normalform! Begründen Sie Ihre Aussagen!

1. Normalform: Ja

2. Normalform: Nein, da die Vorlesung nur von der Vorlesungsnummer abhängig ist.

Vorlesungsnummer -> { Vorlesung , Fachbereich }

Student -> { Studentenname }

Vorlesungsnummer , Matrikelnr

2. Existieren Redundanzen bei der Abspeicherung von Daten im Relationalen Datenmodell, so kann es zu sog. Anomalien kommen. Welche Anomalien kennen Sie? Erklären Sie diese anhand des hier angegebenen Beispiels Vorlesung.

UPDATE-A.: Wenn sich der Vorlesungsname ändert, muss der öfters geändert werden.

INSERT-A: Mann kann keine Vorlesung anlegen für die noch kein Student eingeschrieben ist.

DELETE-A: Wenn man die Einschreibungen der Studenten in der Vorlesung löscht, gibt es keine Vorlesung mehr.

___________________________________________________________________________

Seite 3/8 Aufgabe 3 SQL

1. Welches Ergebnis liefert die folgende SQL-Abfrage:

SELECT Vorlesungsnummer, Vorlesung, Fachbereich FROM Student

WHERE Fachbereich = ‚Informatik’ OR Fachbereich = ‚BW’.

Diese Abfrage gibt gar nichts aus, da in der Tabelle Student die WHERE Klausel mit ‚Fachbereich’

absolut ins leere greift!

2. Formulieren Sie eine SQL-Abfrage, die für alle Studenten die Matrikelnummer und die Anzahl der Vorlesungen auflistet, die ein Student belegt hat.

SELECT Student, COUNT(Vorlesungsnummer) AS Anzahl_Vorlesung

FROM Vorlesung

GROUP BY Student;

3. Formulieren Sie eine SQL-Abfrage, die für alle Vorlesungen die eingeschriebenen Studenten herausfindet. Das Ergebnis soll alphabetisch nach dem Vorlesungs- namen sortiert werden.

SELECT Vorlesung, Studentenname

FROM Vorlesung

GROUP BYVorlesung;

4. Der Student Zanker will sich für die Vorlesung Datenbanken einschreiben.

Vervollständigen Sie den folgenden SQL-Ausdruck, so dass diese Daten in die Datenbank eingefügt werden.

INSERT INTOVorlesung VALUES (100,331123,Datenbanken,Zanker,BW)

SELECT

___________________________________________________________________________

Seite 4/8 Aufgabe 4 Relationale Operatoren

Gegeben sei folgendes Relationenmodell einer Bank:

Filiale

FNR BLZ Filialname Leiter

Konto

KTNR Saldo Dispo Kunde Filiale

Kredit

DispoNr Betrag Rate Zins Kunde Filiale Sparbuch

SNR Guthaben Zinssatz Kunde Filiale

Stellen Sie an die Datenbank folgende Anfragen mittels Relationenalgebra:

1. Listen Sie die Kontonummern auf, die Kunden mit dem Namen ‚Meier’ gehören.

T1 := REST ( Konto, Kunde=“Meier“ )

T2 := PROJ ( T1, KTNR )

??? ob das reicht ???

2. Bitte beschreiben Sie die Fragenstellung, die im folgenden Ausdruck codiert ist:

T1 = Rest (Sparbuch, (Guthaben > 5000)).

T2 = Join (T1, (Filiale = FNR), Filiale).

T3 = PROJ (T2, Filialname).

Ausgegeben wird, eine Liste mit Filialen bei denen Sparbücher mit mehr als 5000 geführt werden.

3. Bitte geben Sie eine semantisch äquivalente SQL-Anweisung an!

SELECT FI.Filialname

FROM Sparbuch AS SP INNER JOIN Filiale AS FI ON ( Filiale = FNR) WHERE Guthaben > 5000;

___________________________________________________________________________

Seite 5/8 Aufgabe 5 (Datenunabhängigkeit)

Bitte kreuzen Sie die richtigen Antworten an (es kann mehrere richtige Antworten geben!). Falsche Angaben werden negativ benotet.

1. Um ein möglichst großes Maß an Datenunabhängigkeit zu erreichen, sollen drei verschiedene Datenebenen realisiert werden:

Externes, konzeptionelles und internes Schema Attribute, Relationen und Konsistenzbedingungen Dateisystem, Speichersystem und Zugriffskontrolle 2. Eine Transaktion

Wird entweder ganz oder gar nicht ausgeführt Ist eine atomare Datenbankoperation

Ist eine Menge von Datenbankoperationen, die zu einem Zeitpunkt parallel ausgeführt werden können

Ist eine Folge von Datenbankoperationen, die die Datenbank von einem konsistenten (d.h. korrekten) Zustand zum nächsten konsistenten Zustand überführen.

Aufgabe 6 ER-Diagramme, relationales Datenbankmodell, SQL

Die Datenanalyse bei einer Luftverkehrgesellschaft hat folgendes ergeben: Der ge- samte Luftverkehr ist in Flugverbindungen unterteilt. Eine Flugverbindung stellt die Verbindung zwischen zwei Orten mit einer Fluggesellschaft zu einer bestimmten Abflug- und Ankunftszeit her. Für jede Flugverbindung wird täglich ein Flugplan gefertigt. Dieser enthält alle Flüge für ein bestimmtes Datum. Flügen ist eindeutig ein Flugzeugtyp zugeordnet. Jedes Flugzeug hat abhängig von seinem Typ eine bestimmte Anzahl von Sitzplätzen der 1. und der 2. Klasse und wird mit Begleit- personal ausgestattet. Für die Bemannung der Flugzeuge existieren verschiedene Typen, die jeweils die Anzahl der Piloten und Stewardessen festlegen. Um die Reservierung von Flügen zu erleichtern, werden alle noch offenen Flüge gesondert geführt, mit der noch frei verfügbaren Kapazität an Plätzen der 1. und 2. Klasse. Bei Buchungen muss der Flug und die Agentur angegeben werden. Passagiere werden mit ihrem Namen, Adresse und Telefonnummer registriert.

Das Personal der Fluggesellschaft ist unterteilt in Flugpersonal und Bodenpersonal.

Flugpersonal muss über eine Flugerlaubnis verfügen und darf eine maximale Anzahl von Flugstunden nicht übersteigen. Bodenpersonal hat im Gegensatz zu Flug- personal einen bestimmten Einsatzort.

Für das Flugpersonal wird ein Flugdienstplan gefertigt, in dem für jeden Flug das Flugpersonal mit jeweils einem Vertreter aufgeführt sind.

Identifizieren Sie die im Anwendungsbereich vorkommenden Objekte und Objekttypen, ihre Merkmale sowie ihre Beziehungen zueinander.

Zeichen Sie die ER-Diagramme

Führen Sie das ER-Modell in ein relationales Datenbankschema über.

(6)

___________________________________________________________________________

Seite 6/8

___________________________________________________________________________

Seite 7/8 Aufgabe 6 Datenmodellierung (Vorlesungsbetrieb)

Gegeben sei folgendes relationales Datenbankschema, das Informationen zum Vorlesungsbetrieb einer Hochschule verwaltet. Folgende Informationen sollen berücksichtigt werden:

Studenten: Matrikelnummer, Name, Vorname, Semester Assistenten: Personalnummer, Name, Vorname, Fachbereich, Raum Professor: Personalnummer, Name, Vorname, Fachbereich, Raum Vorlesung: Vorlesungsnummer, Anzahl Semesterwochenstunden, Titel Zusatzinformationen

Ein Assistent ist einem oder mehreren Professoren (jeweils zu einem Prozentsatz seiner Arbeitszeit) zugeordnet. Professoren können mehrere Assistenten haben.

Professoren halten i.d.R. mehrere Vorlesungen, es gibt aber auch Professoren, die Ämter begleiten bzw. forschungsfreies Semester haben und keine Vorlesungen halten. Eine Vorlesung wird eindeutig einem Professor zugeschlagen.

Studenten belegen i.d.R. mehrere Vorlesungen, es gibt aber auch Studenten, die keine Vorlesungen belegen wollen bzw. dürfen (z.B. wegen ausstehender Prüfungen).

Prüfungen sind mündlich. Die Studenten werden einzeln geprüft. Die Prüfung wird von einem Professor gehalten, der nicht notwendigerweise die Vorlesung selbst hält. Während der Prüfung ist ein Assistent anwesend, der ein Prüfungs- protokoll anfertigt. Nach der Prüfung wird die Note festgelegt.

1. Bitte entwerfen Sie ein ER-Diagramm für diese Aufgabenstellung!

2. Führen Sie das ER-Diagramm in ein relationales Datenbankmodell über!

___________________________________________________________________________

Seite 8/8

3. Formulieren Sie SQL-Anweisungen, die folgende Fragen beantworten:

a. Prüfungsliste: Liste aller Prüfungen mit Angabe der Vorlesungsnummer, des Studenten (Matrikelnummer, Name), des Professors (Name, Fach- bereich) sowie des Prüfers (Name, Fachbereich) und seines Assistenten (Name, Fachbereich) sortiert nach dem Studentennamen.

b. Statistik: Liste der Prüfungen mit Anzahl der Prüfungsteilnehmer pro Prüfung.

c. Liste der Professoren, die mehr als 30 Prüfungen halten müssen.

___________________________________________________________________________

1

Gegeben sind die beiden Tabellen Vertreter und Kunde. Kunden sind eindeutig einem Vertreter zugeordnet. Die erste Spalte bildet jeweils den Primärschlüssel.

Vertreter

Vertreternummer Name Gehalt

123 Kop 20000

234 Jannach 21000

NULL Meier 25000

331 Zanker 22000 Kunde

Kundennummer Name Ort Vertreter

1999-1 Müller München 331

1999-3 Huber München 331

1999-3 Maier München 200

1999-4 Felfernig Berlin 234

1999-5 Erkollar Berlin 212

a) Besitzen die Relationen (Tabellen) einen Fremdschlüssel? Falls ja: Geben Sie den Fremdschlüssel an.

Tabelle: Kunde Fremdschlüssel: Vertreter

b) Welche Zeilen in den Tabellen verstoßen gegen die Entitätsregel (Integritätsbedingung!)? Geben Sie die Zeilen an und begründen Sie Ihre Entscheidung.

Tabelle: Vertreter Spalte: Vertreternummer, NULL Eintrag im Primärschlüssel Tabelle: Kunde Spalte: Kundennummer, Doppelte Tupel: 1999-3 im Primärschlüssel c) Welche Zeilen in den Tabellen verstoßen gegen die referentielle Integrität?

Geben Sie die Zeilen an und begründen Sie Ihre Entscheidung.

Tabelle: Kunde Spalte: Vertreter, Eintrag-Fremdschlüssel: 200,212 nicht im Primärschlüssel der Tabelle „Vertreter“ definiert.

d) Wird eine Integrität verletzt, wenn man folgenden Befehl ausführt (Begründen Sie Ihre Antwort):

INSERT INTO Kunde VALUES ('1994-6', 'Schwarzenegger', 'Berlin', 205);

Ja, der Wert Vertreter „205“ ist nicht in der Tabelle: Vertreter im Primärschlüssel definiert.

(7)

___________________________________________________________________________

2 Aufgabe 2 Relationale Operatoren, SQL

Einem Flugbuchungssystem liegen u.a. folgende Tabellen zugrunde:

SPFLI: Flugverbindungen SFLIGHT: Flugplan

Betrachten Sie bitte die folgenden Ausdrücke:

T1 := JOIN (SPFLI, SFLIGHT).

T2 := REST (T1, (seatsocc = 0)).

T3 := PROJ (T2, CARRID, CONNID, CITYFROM, CITYTO, FLDATE).

1. Formulieren Sie eine dafür passende Aufgabenstellung!

Gib alle Flüge aus ( mit CARRID,CONNID,CITYFROM,CITYTO,FLDATE) die noch keine Buchungen haben.

2. Geben Sie eine semantisch äquivalente SQL-Anweisung an!

SELECT SP.CARRID, SP.CONNID, SP.CITYFROM, SP.CITYTO, SF.FLDATE FROM SPFLI AS SP, SFLIGHT AS SF

WHERE seatsocc = 0;

Aufgabe 3 SQL

Dieser Aufgabe liegen folgende Tabellen zugrunde:

Personal Auftrag

Bitte berechnen Sie das Ergebnis der folgenden Anweisungen:

a. SELECT COUNT(*) FROM Personal Group by Ort.

Ausgabe: 6

___________________________________________________________________________

3 b. SELECT COUNT(*) FROM Personal

Group by Ort HAVING count (*) > 2.

Ausgabe: Regensburg

c. Select count(*) FROM Personal INNER JOIN Auftrag ON Auftrag.PersNr = Personal.PersNr.

Ausgabe: 5

Aufgabe 4 Normalisierung

Gegeben sei folgende Tabelle, die Daten von Bankkunden verwaltet:

KNR Name Vorname GebDatum Sparbuch Guthaben Zinssatz Filiale BLZ Filialenname Leiter

Bemerkungen

Ein Kunde kann mehrere Sparbücher haben. Jedes Sparbuch hat einen festgelegten Zinssatz.

In einem Sparbuch können mehrere Inhaber eingetragen sein (z. B. Eheleute).

Ein Sparbuch wird von einer Filiale der Bank ausgestellt.

a. Diese Tabelle befindet sich offensichtlich in der 1. Normalform. Befindet sich die Tabelle auch in der 2. Normalform? Begründen Sie Ihre Aussage!

1. Normalform: JA,

2. Normalform: NEIN, da das Sparbuch nicht von der Kundennummer abhängig ist.

b. Bitte überführen Sie die Tabelle in die 3. Normalform!

Primärschlüssel: KNR, Sparbuch, Filiale KNR -> { Name, Vorname, GebDatum } Sparbuch -> { Guthaben, Zinssatz, Filiale } Filiale -> { BLZ, Lilialenname, Leiter }

Aufgabe 5 ER-Diagramme, relationales Datenbankmodell, SQL Gegeben sei folgendes relationales Datenbankschema, das Kunden-, und Mitarbeiterdaten sowie Daten von Autoreparaturaufträgen verwaltet. Folgende Informationen sollen berücksichtigt werden:

Kundendaten: Kundenidentifikation, Name, Vorname, Telefonnummer Mitarbeiterdaten: Personalnummer, Name, Vorname, Abteilung (eindeutige Zuordnung

von Mitarbeitern zu Abteilungen)

Autodaten: Identifikation (wird bei der Erteilung des ersten Auftrags vergeben und bleibt für künftige Aufträge gleich), Modell und KFZ-Nummer Reparaturaufträge: Datum des Reparaturauftrags, Reparaturkosten.

___________________________________________________________________________

4 Zusatzinformationen

Ein Reparaturauftrag bezieht sich auf ein einziges Auto.

Einem Mitarbeiter werden i.d.R. mehrere Reparaturaufträge zugeordnet, für einen Auftrag ist aber immer nur ein Mitarbeiter zuständig.

Ein Kunde kann mehrere Autos zur Reparatur bringen.

Ein Auto kann mehrere Reparaturaufträge verursachen.

1. Bitte entwerfen Sie ein für die Aufgabenstellung geeignetes ER-Diagramm!

Primärschlüssel: Kunden-ID, Personal-Nr, KFZ-ID, RAuftrag-ID

Tabelle: Kunde

Kunden-ID | Name | Vorname | Tel Tabelle: KFZ

KFZ-ID | Model | KFZ-Nr | Kunden-ID Tabelle: Auftrag

RAuftrag-ID | Datum | Kosten | KFZ-ID | Personal-Nr Tabelle: Personal

Personal-Nr | Name | Vorname | Abteilung

3. Geben Sie die Definition eines Sekundärschlüssels an! Geben Sie ein Beispiel für einen sinnvollen Sekundärschlüssel für die oben angegebene Datenbank!

Aus Performancegründen kann es notwendig sein, für weitere Attribute bzw.

Attributkombinationen Zugriffspfade einzurichten, um schnell an die gespeicherten Informationen zu kommen.

z.B. Hier dürfte die Tabelle mit den Aufträgen am schnellsten sehr sehr groß werden.

Darum kann es notwendig werden, einen Sekundärschlüssel zur Suche zu definieren.

Anbieten würde sich hierbei: R-Auftrag-ID | Datum 4. Formulieren Sie auf Basis folgende Anfragen in SQL:

a. Zeigen Sie den Reparaturauftrag für das Auto mit der Identifikation ‚SS625000‘.

SELECT * FROM Auftrag AS A WHERE A.RAuftrag-ID=“SS62500“;

Kunde

entält

1

Personal

KFZ Auftrag

Be- zieht-

sich n

zustän dig

1

n n

1

___________________________________________________________________________

5

b. Zeigen Sie alle Reparaturaufträge, die zwischen 1.5.1999 und 10.5.1999 abgeschlossen worden sind.

SELECT * FROM Autrag AS A

WHERE A.Datum >= #1/5/1999# AND A.Datum <= #10/5/1999#;

c. Zeigen Sie den Durchschnittswert der Reparaturkosten aller bisher abgeschlossenen Reparaturaufträge.

SELECT AVG(A.Kosten) AS Durchschnitts_Reperatur_Kosten FROM Auftrag AS A;

d. Zeigen Sie alle Reparaturaufträge, deren Kosten über dem Durchschnitt liegt.

SELECT * FROM Auftrag AS A

WHERE A.Kosten > ( SELECT AVG(Kosten) FROM Auftrag );

Aufgabe 6 Datenmodellierung (Vorlesungsbetrieb)

Gegeben sei folgendes relationales Datenbankschema, das Informationen zum Vorlesungsbetrieb einer Hochschule verwaltet. Folgende Informationen sollen berücksichtigt werden:

Studenten: Matrikelnummer, Name, Vorname, Semester Assistenten: Personalnummer, Name, Vorname, Fachbereich, Raum Professor: Personalnummer, Name, Vorname, Fachbereich, Raum Vorlesung: Vorlesungsnummer, Anzahl Semesterwochenstunden, Titel Zusatzinformationen

Ein Assistent ist einem oder mehreren Professoren (jeweils zu einem Prozentsatz seiner Arbeitszeit) zugeordnet. Professoren können mehrere Assistenten haben.

Professoren halten i.d.R. mehrere Vorlesungen, es gibt aber auch Professoren, die Ämter begleiten bzw. forschungsfreies Semester haben und keine Vorlesungen halten.

Eine Vorlesung wird eindeutig einem Professor zugeschlagen.

Studenten belegen i.d.R. mehrere Vorlesungen, es gibt aber auch Studenten, die keine Vorlesungen belegen wollen bzw. dürfen (z.B. wegen ausstehender Prüfungen).

Das Belegen einer Vorlesung kann von der erfolgreicher Prüfungsteilnahme an maximal einer anderen Vorlesungen abhängig sein.

Prüfungen sind mündlich. Die Studenten werden einzeln geprüft. Die Prüfung wird von einem Professor gehalten, der nicht notwendigerweise die Vorlesung selbst hält.

Während der Prüfung ist ein Assistent anwesend, der ein Prüfungsprotokoll anfertigt.

Nach der Prüfung wird die Note festgelegt.

Primärschlüssel: Matrikelnr, Personalnr, Vorlesungsnr

(8)

___________________________________________________________________________

6

Tabelle: Student

MNr | Matrikelnr | Semester | Vorlesungsnr Tabelle: Professor

MNr | Personalnr

Tabelle: Assistent

MNr | Personalnr

Tabelle: Arbeitszeit_Assi_Prof Personalnr | Personalnr | Prozent_Zeit Tabelle: Personal

MNr | Fachbereich | Raum Tabelle: Mensch MNr | Name | Vorname Tabelle: Vorlesung

Vorlesungnr | AnzahlStd | Titel | MNr(Prof) | MNr(Stud) Tabelle: Vorles_Stud

Vorlesungnr | MNr(Stud) Tabelle: Prüfung

et für

Personal Fachbereich Raum

Mensch Name Vorname Student

Semester Professor

Personalnr

Assistent Personalnr

%

n m

Vorlesung hält

1

cn

belegt

cn

m

Prüfung Ist vor-

rausset

1 zung 1

protok olliert hält

1

n n

1

macht

1

Ist für

1 n

cn

___________________________________________________________________________

7

3. Formulieren Sie SQL-Anweisungen, die folgende Fragen beantworten:

a. Prüfungsliste: Liste aller Prüfungen mit Angabe der Vorlesungsnummer, des Studenten (Matrikelnummer, Name), des Professors (Name, Fachbereich) sowie des Prüfers (Name, Fachbereich) und seines Assistenten (Name, Fachbereich) sortiert nach dem Studentennamen.

SELECT PRU.Vorlesungnr, MS.Name AS Stud, MP.Name AS Prof, MA.Name AS Assi FROM ( ( Prüfung AS PRU INNER JOIN Mensch AS MS ON (PRU.MNr(Stud)=MS.MNr) )

INNER JOIN Mensch AS MP ON (PRU.MNr(Prof)=MP.MNr) ) INNER JOIN Mensch AS MA ON (PRU.MNr(Assi)=MA.MNr) GROUP BY MS.Name;

b. Statistik: Liste der Prüfungen mit Anzahl der Prüfungsteilnehmer pro Prüfung.

SELECT P.Name, COUNT(P.Name) FROM Prüfung AS P

GROUP BY P.Name;

c. Liste der Professoren, die mehr als 30 Prüfungen halten müssen.

SELECT MP.Name AS Prof, COUNT(P.MNr(Prof))

FROM Prüfung AS P INNER JOIN Mensch AS MP ON (P.MNr(Prof)=MP.MNr) GROUP BY P.MNr(Prof)

HAVING COUNT(P.MNr(Prof)) > 30;

___________________________________________________________________________

1

Aufgabe 1 3-Schichten-Modell

1. Geben Sie den grundsätzlichen Aufbau eines Datenbanksystems nach dem 3-Schichten-Modell an!

Aufbau nach ANSI-SPARC:

a. Externes Schema:

Beschreibung der Daten und deren Beziehungen – insb. Konsistenzbedingungen.

b. Konzeptionelles Schema:

Hier sind die Einzelheiten der Implementierung angegeben:

- Aufteilung der DB auf verschiedene Platten, - Plattenspiegelung / Striping (RAID-Level), - Anlegen von Indizes um Zugriff zu beschleunigen,

Konfiguration der Verwendung von Speicher zur Datenpufferung.

c. Internes Schema:

Definition der verschiedenen Schichten. Es vermittelt zwischen den Programmen, die von außen auf die DB zugreifen und den Strukturen in denen die Daten gespeichert werden.

2. Welcher der drei Schichten ordnen Sie die folgende Maßnahme zu?

„Die Daten einer Applikation werden so auf die Platten des Plattensystems verteilt, dass sich möglichst viele parallele Zugriffe ergeben“.

Internes Schema

1. Erläutern Sie anhand eines Beispiels den Unterschied zwischen einem Primärschlüssel und einem Schlüsselkandidaten!

Ein Primärschlüssel ist ein ausgezeichneter Schlüsselkandidat.

Jedoch mit besonderen – zusätzlichen – Eigenschaften:

- Keine Nullwerte,

- Über ihn ist der direkte Zugriff auf genau eine Zeile der Tabelle möglich.

z.B. Tabelle: Kunde

ID Name URL

Primärschlüssel Wert Eindeutig/Schlüsselkandidat 2. Erläutern Sie anhand eines Beispiels den Unterschied zwischen einem

Schlüsselkandidaten und einem Sekundärschlüssel!

Ein Sekundärschlüssel kann aus Performance gründen eingeführt werden, um in Kombination mit dem Primärschlüssel eine schnellere Suche zu erreichen.

z.B. Tabelle: Kunde

ID Name Geb URL

Primärschlüssel Sekundärschlüssel Schlüsselkandidat

___________________________________________________________________________

2

3. Was spricht für die Verwendung von Sekundärschlüsseln, was dagegen?

Dafür: Höhere Such-Performance bei großen Datenbanken.

Dagegen: Aufwand an Rechenzeit & Speicherbedarf da Indextabellen immer aktualisiert werden müssen.

4. Geben Sie ein Beispiel an (Tabelle, Kardinalität(=Anzahl der Zeilen der Tabelle), Sekundärschlüssel) und erläutern Sie anhand des Beispiels die Effektivität des Sekundärschlüssels!

Es bedeutet, das 5% als Erbegnis von allen Daten kommt.

Aufgabe 3 Relationale Operatoren, SQL

Einem Flugbuchungssystem liegen u.a. folgende Tabellen zugrunde:

SPFLI: Flugverbindungen

(9)

___________________________________________________________________________

3 Betrachten Sie bitte die folgenden Ausdrücke:

T1 := PRODUKT (SPFLI, SFLIGHT).

T2 := REST (T1, (seatsocc = seatsmax)).

T3 := PROJ (T2, CARRID, CONNID, CITYFROM, CITYTO, FLDATE).

1. Formulieren Sie eine dafür passende Aufgabenstellung!

Es soll eine Liste ( Fluggesellschafts ID, Verbindungs ID, Abflugort, Ankunftsort, Datum ) aller Verbindungen ausgegeben werden, die ausgebucht sind.

2. Geben Sie eine semantisch äquivalente SQL-Anweisung an!

SELECT SF.CARRID, SF.CONNID, SP.CITYFROM, SP.CITYTO, SF.FLDATE FROM SFLIGHT AS SF INNER JOIN SPFLI AS SP

ON ( SF.CONNID=SP.CONNID AND SF.CARRID=SP.CARRID ) WHERE SF.SEATSOCC=SF.SEATSMAX ;

Aufgabe 4 Relationale Operatoren, SQL

Bitte verwenden Sie für diese Aufgabe die Tabellen aus der Aufgabe 3 und zusätzlich noch folgende Tabellen:

SCARR: Fluggesellschaft

1. Geben Sie mit Hilfe einer SQL-Anweisung eine Liste der Buchungen aus sortiert nach Fluggesellschaft mit Angabe der Kundennummer, des Abflugorts und des Abflugdatums. In der Ausgabe sollte der Name der Fluggesellschaft enthalten sein.

SELECT SC.CARRNAME, SB.CUSTOMID, SP.CITYFROM, SB.FLDATE FROM ( SBOOK AS SB INNER JOIN SPFLI AS SP

ON ( SB.CONNID=SP.CONNID AND SB.CARRID=SP.CARRID ) ) INNER JOIN SCARR AS SC ON ( SB.CARRID=SC.CARRID ) ORDER BY SC.CARRNAME;

___________________________________________________________________________

4

2. Formulieren Sie Ihre Lösung auch mit Hilfe von relationalen Operatoren!

T1 := JOIN ( SBOOK, SBOOK.CONNID=SPFLI.CONNID AND SBOOK.CARRID=SPFLI.CARRID, SPFLI ) T2 := JOIN ( T1, SBOOK.CARRID=SCARR.CARRID, SCARR ) T3 := PROJ ( T2, CARRNAME, CUSTOMID, CITYFROM, FLDATE )

??? Wie bekommt man das ORDER BY rein ???

Aufgabe 5 SQL

Dieser Aufgabe liegen folgende Tabellen zugrunde:

Personal Auftrag

1. Bitte berechnen Sie das Ergebnis der folgenden Anweisungen:

SELECT COUNT(*) FROM Personal; = 9

SELECT COUNT(Ort) FROM Personal; = 9

SELECT COUNT(Vorgesetzt) FROM Personal; = 7 SELECT COUNT(DISTINCT Ort) FROM Personal; = 6 SELECT COUNT(DISTINCT PersNr) FROM Auftrag; = 2

2. Bitte finden Sie mit Hilfe einer SQL-Anweisung alle Mitarbeiter mit Namen und Adresse, die Aufträge geholt haben. Bitte geben Sie die Ergebnistabelle an!

SELECT P.Name, P.Ort

FROM Personal AS P INNER JOIN Auftrag AS A ON ( P.Persnr=A.Persnr ) GROUP BY P.Name

HAVING COUNT(A.AuftrNr) > 0;

Ergebnistabelle:

Name Ort

Anna Kraus Regensburg

Johanna Köster Nürnberg

___________________________________________________________________________

5 Aufgabe 6 Normalformenlehre

1. Sie müssen eine Datenbank aufbauen, die Studenten und Prüfungsdaten verwaltet. Im ersten Entwurf haben Sie sich für folgende Tabelle entschieden:

Prüfungsgeschehen

PNR Fach Prüfer MatrNr Name GebDatum Adresse Fachbereich FBName Dekan Note 3 Inf Reis 123456 Huber 111169 .... 11 Ing.-Wiss. Walter 3

321 Mathe Wurm 876543 Schmidt 201165 .... 11 Ing.-Wiss. Walter 2

Erläutern Sie anhand dieser Tabelle die Probleme, die bei redundanter Datenhaltung vorkommen können!

2. Bitte überführen Sie die folgende Excel-Tabelle in die dritte Normalform!

Entwerfen Sie anschließend ein Entity-Relationship-Diagramm!

carrid Name Sitz Niederlassung Flugzeug Typ #Sitze Hersteller Wartung in Wartungstermine LH Lufthansa München München 123 B 737 137 Boing Hamburg alle vier Monate am 1.

des Monats Berlin 124 B 737 137 Boing München alle vier Monate am 2.

des Monats Hamburg 125 A310 145 Airbus Berlin alle vier Monate am 3.

des Monats

126 A320 167 Airbus Hamburg alle vier Monate am 3.

des Monats

Bemerkung

In der Tabelle sehen Sie Einträge zu einer Fluggesellschaft. Selbstverständlich sind in der Tabelle Einträge zu weiteren Fluggesellschaften enthalten, die alle analog aufgebaut sind.

___________________________________________________________________________

6

(10)

___________________________________________________________________________

7 Aufgabe 7 Datenmodellierung

Sie müssen für die Bibliothek der Hochschule eine Datenbank entwerfen, in der die Ausgabe der Bücher verwaltet wird. Bücher können Studenten, Mitarbeiter aber auch Fachbereiche für ihren „Handapparat“ ausleihen. Studenten werden mit ihrer Matrikelnummer registriert, Mitarbeiter mit ihrer Personalnummer und Fachbereiche mit der hochschulweit eindeutigen Identifikation des Fachbereichs. Darüber hinaus wird eine Telefonnummer hinterlassen. Jeder Ausleiher kann mehrere Bücher ausleihen. Ein Buch kann zeitlich nicht überlappend von mehreren Ausleihern ausgeliehen werden. Jeder Ausleihvorgang wird zeitlich terminiert. Um das Auffinden von Büchern zu erleichtern, werden Büchern Schlagwörter zugeordnet. Ein Buch kann unter mehreren Schlagwörtern registriert werden. Zu einem Schlagwort kann es mehrere Bücher geben. Ihre Datenbank muss darüber hinaus den Standort der Bücher in der Bibliothek selbst enthalten. Ist ein Buch nicht am Standort vorhanden, kann aber über Fernleihe von einem anderen Standort ausgeliehen werden, so verwaltet die Datenbank alle Bibliotheken, über die das Buch ausgeliehen werden kann.

___________________________________________________________________________

8

2. Entwerfen Sie mit Hilfe des ER-Diagramms Ihre Tabellen!

3. Formulieren Sie eine SQL-Anweisung, die Ihnen bereits überfällige

Abgabetermine ermittelt mit Angabe des Ausleihers, seiner Telefonnummer, des vereinbarten Rückgabetermins und des Buchtitels!