Prüfungsfach: Datenbanken Arbeitszeit: 120 Minuten Zugelassene Hilfsmittel: Skript Name:
Prüfungstermin: 11.07.2003 Vorname: Sem.:
Aufgabensteller: Prof. Dr. A. Tsakpinis Bitte deutlich schreiben!
Wenn du Fehler findest, oder eine bessere Lösung hast!
Zurück an eike@ehringer.de 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!
Aufgabe 2 SQL
Bitte betrachten Sie folgende Tabellen:
SPFLI: Flugverbindungen
SFLIGHT: Flugplan
SBOOK: Flugbuchung
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;
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:
Bestellung: Bestellnummer | Bestelldatum | Rabatt | PreisGesamt | Kundennummer Artikel: Artikelnummer | Artikelname | Menge | PreisNetto | Bestellnummer
Kunde: Kundennummer | Name | Ort | Bestellnummer
(n:c) AK: Bestellnummer | Kundennummer 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äl t
1 cn
Zuge - ordn
et
n
c
Lieferadresse
Ansprechpartner Telefonnummer
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
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
StellplatzRoute
fährt
1
n 1
Ist zustä
ndig
n
1
Ist eing esetz t
1
n
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.
