EvA - ISQ81.de

Oberstufenzentrum
Industrie und Datenverarbeitung
Berufsschule, Berufsfachschule, Fachoberschule
Fachbereich Wirtschaftsinformatik
Entwicklung von Anwendungen
Unterrichtsmaterial
Datenbanken
Stand 2009
© oszinddv
EvA
Flotte Kiste
Datenbanken
Situationsbeschreibung:
Angefangen hat alles mit einem Citroën „Traction Avant" Baujahr 1936, den Rudi Rost von
seinem Onkel geerbt hatte. Mit diesem wunderbaren Auto entdeckte Rudi seine Leidenschaft
für Oldtimer. Um sich dieses kostspielige Hobby leisten zu können, vermietete er den Citroën an
Freunde und Bekannte. Mit dem so erwirtschafteten Überschuss war es Rudi möglich, die eine
oder andere Rarität zu erwerben und ggf. selbst zu restaurieren. Hatte er einen Oldtimer neu
aufgebaut, rief er seine Freunde und Bekannten an, um das „neue" Stück an sie zu vermieten.
Zu diesem Zweck notierte sich Rudi die Kundendaten auf Karteikarten – neben Adresse und
Telefonnummer auch Vorlieben für bestimmte Fahrzeugtypen.
Heute hat Rudi Rost 23 Oldtimer in seiner Garage stehen, die er längst nicht mehr nur an
Freunde und Bekannte vermietet. Insbesondere sein Rolls-Royce Phantom II von 1931 wird von
Hochzeitspaaren gern gemietet.
Die Arbeit macht Rudi Spaß, wenn da nicht die lästige Schreibarbeit wäre. Für jeden Kunden
wird eine Karteikarte angelegt, und in einer Liste werden die Mietvorgänge erfasst.
Insbesondere die Mahnung säumiger Kunden, die ihre Rechnung noch nicht gezahlt haben, ist
Rudi ein Graus. Alles muss doppelt und dreifach geprüft werden, damit er niemanden übersieht
oder fälschlicherweise mahnt. Früher hat Rudi, wenn er wieder einen neuen Oldtimer in seiner
Sammlung hatte, gezielt die Kunden angeschrieben, die wegen ihrer Vorlieben Interesse an
dem neuen Stück haben könnten. Die Kunden waren davon begeistert, aber heute ist es ihm
viel zu aufwändig, alle Kundenkarteikarten auf die Vorlieben hin durchzugehen. Auch das
Anschreiben der Hochzeitspaare zum ersten Hochzeitstag mit einem Spezialtarif für den RollsRoyce Phantom II spart sich Rudi heute, obwohl fast 70% der Hochzeitspaare dieses Angebot
angenommen haben.
Der Schreibarbeit überdrüssig beschließt Rudi, seinen Oldtimerverleih in Zukunft
computerunterstützt abzuwickeln und vereinbart für nächste Woche einen Termin mit einem
Software-Entwickler. Da Herr Rost weiß, dass Software-Entwickler horrende Stundensätze
haben, will er sich gut vorbereiten und überlegt, welche Daten und Informationen für das
Mietverfahren notwendig sind.
Aufgabe:
Versetzen Sie sich in die Lage von Herrn Rost und notieren Sie alle Daten und Informationen,
die für das Vermietverfahren notwendig sind. Gruppieren Sie diese Informationen zweckmäßig!
© oszinddv – 2009 / A1 Flotte Kiste.doc
1
EvA
Datenbanken
Entity-Relationship-Modell
Grundbegriffe
Das Entity-Relationship-Modell (kurz: ERM) wurde 1976 von P. Chen zur Datenmodellierung
entwickelt. Im Deutschen wird manchmal auch die Bezeichnung Gegenstands-BeziehungsModell verwendet. Im Angelsächsischen ist auch der Begriff Information Modelling gebräuchlich.
Ziel des ER-Modells ist es, die permanent gespeicherten Daten und ihre Beziehungen
untereinander zu beschreiben. Die Analyse der Information erfolgt aus fachlogischer Sicht.
Es entsteht ein konzeptionelles Modell, das gegen Veränderungen der Funktionalität
weitgehend stabil ist.
Ausgangspunkt des ER-Modells ist der Begriff der Entität. Eine Entität (entity) ist ein
individuelles und identifizierbares Exemplar von Dingen, Personen oder Begriffen der realen
oder der Vorstellungswelt und wird durch Eigenschaften beschrieben. Für Entität wird synonym
oft der Begriff Objekt oder Instanz verwendet.
Die Beziehungen zwischen den Entitäten werden grafisch in einem Entity-RelationshipDiagramm (ERD) dargestellt. Die Entität selbst wird nicht im ER-Diagramm dargestellt.
Entitätsmenge (entity set), Entitätstyp, Entitytyp, Objekttyp, Objektklasse
ist eine Zusammenfassung von Entitäten mit gleichen Eigenschaften unter
einem eindeutigen gemeinsamen Oberbegriff.
Für die Bezeichnung sollte ein Substantiv gewählt werden.
Attribut (property)
beschreibt eine fachliche Eigenschaft, die allen Entitäten einer Entitätsmenge
gemeinsam ist. Es wird durch seinen Namen, der seiner fachlichen
Bedeutung entsprechen soll, und durch seinen Wertebereich definiert.
Für die Bezeichnung sollte ein Substantiv gewählt werden.
Schlüsselattribute (ID)
bilden den Primärschlüssel einer Entitätsmenge.
Der Primärschlüssel ist die minimale identifizierende Attributkombination, d. h.
er muss über alle Entitäten einer Entitätsmenge hinweg eindeutig sein. Oft
besteht der Primärschlüssel nur aus einem Schlüsselattribut. Der Wert eines
Schlüsselattributs sollte sich über die gesamte Lebensdauer der Entität nicht
ändern und darf nicht NULL sein.
Relationship (Assoziation)
Wechselwirkungen und Abhängigkeiten zwischen Entitäten werden durch
Beziehungen dargestellt.
Für die Bezeichnung sollte ein Verb gewählt werden.
Vorteile des ER-Modells:
•
Da das ER-Modell nur mit wenigen Konstrukten auskommt, ist es auch für den Laien
schnell verständlich und erlaubt so den Einsatz in der Systemanalyse mit dem späteren
Anwender.
•
Da es sich um ein konzeptionelles Modell – ausgehend von der sachlogischen Sicht des
Anwenders – handelt, ist es unabhängig von bestimmten Datenbanksystemen.
© oszinddv – 2009 / A2 ERM-Grundbegriffe.doc
1
ERM-Übung
Kurswahl
EvA
Datenbanken
Situationsbeschreibung:
Die Wahl der Wahlpflichtfächer (kurz: Kurswahl) ist relativ kompliziert und umfangreich. Es gibt
Kurse, die im ersten Halbjahr angeboten werden, und welche für das zweite Halbjahr. Jeder
Auszubildende wählt einen Kurs pro Halbjahr, muss aber auch gleichzeitig seinen jeweiligen
Ersatzwunsch äußern. Damit Frau Köhn die Übersicht behält, will sie eine Datenbank zur
Verwaltung der Kurswahl erstellen. Natürlich überlegt sich Frau Köhn zuvor ein EntityRelationship-Modell und hat mit der Erstellung bereits angefangen.
Aufgabe:
Leider musste Frau Köhn die Arbeit an ihrem Modell zwischendurch unterbrechen. Helfen Sie
ihr und vervollständigen Sie das unten stehende Entity-Relationship-Diagramm.
a) Für das Relationship „wählt“ fehlen noch die Attribute.
b) Für die Stundenplanerstellung müssen die Lehrer den verschiedenen Kursen zugeordnet
werden. Auch das würde Frau Köhn am liebsten mit einer Datenbank machen. Erweitern Sie
das Entity-Relationship-Diagramm diesbezüglich!
Name
Vorname
Azubi-Nr
Azubi
Telefon
Straße
wählt
Kursthema
Kurs-Nr
Kurs
Halbjahr
© oszinddv – 2009 / A3 Kurswahl.doc
1
EvA
Datenbanken
ERM-Übung
CASE-Werkzeuge
Situationsbeschreibung:
Software-Systeme, die Entwickler bei der Erstellung und Wartung von Software nutzen, werden
CASE-Werkzeuge (Computer Aided Software Engineering) genannt. Sie existieren für die
gesamte Breite eines Softwareentwicklungsprozesses, beginnend bei der Analyse der
Anforderungen an die Software, über die Programmierung bis hin zur Testphase des fast
fertigen Produkts.
In einer Datenbank für CASE-Werkzeuge sind folgende Informationen über die Werkzeuge zu
speichern:
(1) Jedes Werkzeug wird von einem Anbieter vertrieben. Ein Anbieter vertreibt in der Regel
mehrere Werkzeuge.
(2) Ein Werkzeug kann auf mehreren Betriebssystemen (BS) laufen (BS-Name ist eindeutig).
Für ein Betriebssystem gibt es in der Regel mehrere Werkzeuge.
(3) Jede betriebssystem-spezifische Werkzeug-Version hat einen eigenen Preis.
(4) Pro Werkzeug sind zu speichern: Werkzeugname (eindeutig), Installationszahl und eine
Beschreibung.
(5) Zum Anbieter sind sein Name (eindeutig), die Adresse und die Anzahl der Mitarbeiter in der
Werkzeugentwicklung zu speichern.
(6) Ein Werkzeug kann Komponente einer CASE-Umgebung sein. Eine CASE-Umgebung
enthält im Allgemeinen mehrere Werkzeuge.
(7) Für jede CASE-Umgebung soll die Datenbank den Produkt-Namen (eindeutig) und das
Jahr der Erstinstallation enthalten.
Aufgabe:
Erstellen Sie das ER-Diagramm mit Attributen. Schlüssel sind zu unterstreichen!
© oszinddv – 2009 / A4 CASE-Werkzeuge.doc
1
EvA
Datenbanken
ERM-Übung
Bibliothek
Situationsbeschreibung:
Eine Bibliothek ist zu verwalten. Nach längeren Gesprächen können sich der Systemanalytiker
und ein späterer Anwender auf folgende Aussagen einigen.
(1) Ein Leser kann sich maximal 20 Bücher ausleihen. Genau betrachtet leiht sich ein Leser
Buchexemplare aus, denn er nimmt sich ein reales Exemplar eines Buches mit nach
Hause.
(2) Von einem Buch gibt es oft mehrere Exemplare in der Bibliothek.
(3) Ist ein gewünschtes Buch nicht vorhanden, so kann es von den Lesern vorbestellt werden.
(4) Pro Leser sind maximal zehn Vorbestellungen möglich.
(5) Für jedes Buch sind der Titel und bis zu drei Autoren zu speichern.
(6) Einige Bücher besitzen einen Herausgeber.
(7) Bei der Aufnahme in die Bibliothek erhält jedes Exemplar eine eindeutige Inventarnummer.
(8) Für jeden Leser werden der Name und die Adresse gespeichert.
(9) Bei der Ausleihe wird das Ausleihdatum, bei der Rückgabe das Rückgabedatum
gespeichert.
Aufgabe:
Erstellen Sie das ER-Diagramm mit Attributen. Schlüssel sind zu unterstreichen!
© oszinddv – 2009 / A5 Bibliothek.doc
1
EvA
Datenbanken
ERM-Übung
Altenpflege
Situationsbeschreibung:
Der Altenpflegedienst „Barmherzigkeit“ möchte seine Daten in einem Informationssystem
speichern. Sie sind beauftragt, eine entsprechende Datenbank zu planen. Ihr erstes Interview
mit der Auftraggeberin und Geschäftsführerin des Unternehmens ergab folgende Sachverhalte:
(1) Es gibt mehrere Standorte, die alle einen eindeutigen Namen haben und deren Anschriften
zu erfassen sind.
(2) Jeder Standort hat eine Leiterin, die nur namentlich beim Standort erwähnt werden soll.
(3) Die Altenpflegerinnen haben Mitarbeiternummern und sollen mit Name und Handynummer
erfasst werden.
(4) Jede Altenpflegerin ist genau einem Standort zugeteilt.
(5) Jede Altenpflegerin erhält einen eigenen Dienstwagen, den nur sie fährt.
(6) Der Fuhrpark des Unternehmens wird durch Kfz-Kennzeichen und Herstellerangabe jedes
einzelnen Autos gespeichert.
(7) Die pflegebedürftigen Personen (Patienten) werden in ihren eigenen Wohnungen (Name,
Anschrift) versorgt.
(8) Da im Schichtdienst gearbeitet wird, kann eine zu pflegende Person von mehreren
Altenpflegerinnen betreut werden.
Aufgabe:
Erstellen Sie das ER-Diagramm mit Attributen. Schlüssel sind zu unterstreichen!
© oszinddv – 2009 / A6 Altenpflege.doc
1
EvA
Datenbanken
ERM-Übung
Busreisen
Situationsbeschreibung:
Ein Busunternehmen bietet Busreisen an und möchte nun die vielen Buchungen, den
Kundenstamm, die Informationen über die angefahrenen Hotels und auch die Daten ihrer
Busfahrer und Busse in einer Datenbank verwalten. Die im Anforderungskatalog genannten
Attribute stellen nur eine Auswahl dar. Verwenden Sie in Ihrem ERD bitte nur die genannten
Attribute, allerdings zuzüglich weiterer Attribute, wenn sie datenbanktechnisch notwendig sind.
(1) Von einem Kunden werden Name, Wohnort und Geburtsdatum gespeichert.
(2) Wenn ein Kunde eine Reise zu einem Hotel bucht, sind das Abfahrtsdatum, die Reisedauer
in Tagen und die Personenanzahl, für die der Kunde bucht, festzuhalten.
(3) Zu den Hotels sind der Hotelname, der Ort und die Bettenanzahl im Hotel zu speichern. Sie
können davon ausgehen, dass ein Hotelname in einem Ort nur einmal vorkommt.
(4) In der Datenbank sollen der Name und die Handynummer der Fahrer gespeichert werden.
(5) Die Busse werden mit ihrem Kennzeichen, der Herstellerangabe und Sitzplatzanzahl
registriert.
(6) Wenn eine Reise beginnt (Abfahrtsdatum speichern), fährt ein Busfahrer einen beliebigen
Bus zu einem Hotel. Er steht den Reisegästen die ganze Urlaubszeit vor Ort für
Ausflugsfahrten zur Verfügung und fährt mit ihnen wieder zurück.
Aufgabe:
Erstellen Sie das ER-Diagramm mit Attributen. Schlüssel sind zu unterstreichen!
© oszinddv – 2009 / A7 Busreise.doc
1
ERM-Übung
Pauschalreisen
EvA
Datenbanken
Situationsbeschreibung:
Ein Reisebüro möchte seine Buchungen von Pauschalflugreisen mit Hilfe einer Datenbank
verwalten. Dazu werden die Entitätstypen KUNDE, REISE, HOTEL und FLUG berücksichtigt.
(1) Jede Reise führt nur an einen Ort, d. h. ein Hotelwechsel ist nicht möglich.
(2) Mit REISE werden festgelegte Pauschalreisen inklusive Flug gemeint, die feste
Reisetermine und Preise pro Person je nach Zimmerart und Verpflegung haben.
(3) Ein Kunde kann für mehrere Personen unter seinem Namen buchen.
(4) Für Kinder bis 12 Jahren wird nur der halbe Preis berechnet.
(5) Hin- und Rückflug werden getrennt verwaltet.
(6) Flüge enthalten unter einer Flugnummer Informationen über die Fluggesellschaft, den
Wochentag, die Abflugs- und Ankunftszeit sowie den Abflugs- und Ankunftsort.
Aufgabe:
Ergänzen Sie das ER-Diagramm (Relationships, Attribute). Schlüssel sind zu unterstreichen!
Kunde
Reise
Hotel
© oszinddv – 2009 / A8 Reise.doc
Flug
1
EvA
Datenbanken
ERM-Übung
Weiterbildung
Das Hildeshofer Zentrum für berufliche Weiterbildung hat in den letzten Jahren eine
bemerkenswerte Entwicklung durchgemacht und kann inzwischen mit einem breiten Angebot
an Fortbildungsveranstaltungen aufwarten. Das ist für die Mitarbeiter/innen erfreulich, bringt
aber auch Probleme mit sich, da das halbjährliche Drucken der Kataloge, das Führen der
Teilnehmerlisten etc. auf der Basis manuell geführter Listen und Kursankündigungen doch recht
mühsam geworden ist. Denn außer einer kleinen Kunden-Adressdatenbank gibt es bisher keine
technische Unterstützung.
Was wir damit zu tun haben, fragen Sie? Nun, uns ist es gelungen, einen tollen Job an Land zu
ziehen. Wir sollen der Verwaltungsangestellten, der die Arbeit mittlerweile über den Kopf
wächst, helfen, indem wir für das Kursangebot, die Kursanmeldungen und -teilnahme einen
Datenbankentwurf liefern und später daraus eine Anwendung "stricken".
Katrin Kleitz, eben jene Verwaltungsangestellte, hat uns mittlerweile folgendes verdeutlicht:
1. Das Kursangebot ist in Bereiche gegliedert, die auch den Kursprospekt strukturieren. Jeder
Bereich hat einen eindeutigen Kennbuchstaben, eine Bezeichnung sowie einen Kurz- und
einen Langtext zur näheren Erläuterung. Der Bereich "E" beispielsweise umfasst alle Kurse
zum Thema "EDV-Grundwissen". Im Kursprospekt findet sich im einführenden Teil eine
ausführliche Erläuterung dazu. Eine kurze Erläuterung leitet dagegen im Hauptteil des
Prospektes die nachfolgenden Kursankündigungen ein.
2. Auf den letzten Seiten des Kursprospektes stellen sich die Dozent/innen mit ihrem Namen,
ihrer Telefonnummer und einem kurzen Statement über ihren Kompetenzbereich vor. Das
verwaltungsintern verwendete zweistellige Namenskürzel erscheint nicht gedruckt.
3. Jeder Kurs hat zusätzlich zur Kursbezeichnung eine eindeutige Kursnummer. Weiterhin
gehören zu jedem Kurs eine Beschreibung der Kursinhalte, Halbjahr, Kursgebühr und die
verantwortlichen Dozent/innen. Die Kurse erscheinen im Prospekt unter genau einer
Bereichsüberschrift.
Der Einfachheit wegen unterschlagen wir weitere Einzelheiten wie Beschreibung der
Zielgruppen, minimale und maximale Teilnehmerzahl, Kursdauer, Anmeldeschluss,
regelmäßiger Termin u. a.
4. Die Kursteilnehmer müssen sich anmelden und bekommen nach Eingang der Zahlung ihre
Anmeldebestätigung unter Angabe des Zahlungsdatums an ihre Adresse zugeschickt.
Da bereits eine Adressdatenbank besteht und man sich über die Zahlungsverwaltung noch
nicht ganz im Klaren ist, sollen diese Aspekte (Adresse, Verwaltung des Kunden-Kontos)
zunächst ausgeklammert bleiben. Wichtig ist allerdings, dass die bisher verwendete
Kundennummer (eine fortlaufende Nummer in der Adressdatenbank) auch in der neuen
Datenbank ihren Platz haben soll.
5. Am Ende eines Kurses werden den teilnehmenden Personen Teilnahmebestätigungen
ausgehändigt. Die Kursleiter übermitteln der Verwaltung für jede Kursteilnahme eine
Textnummer, die für das Zertifikat in einen freundlichen Text über den Teilnahmeerfolg
umgesetzt wird.
Frau Sabine Sandmann-Süllenberger hat im zweiten Halbjahr 2007 an dem Kurs "Englisch
für das Büro" teilgenommen. Ihr Abschlusszertifikat liest sich etwa wie folgt:
Das Hildeshofer Zentrum für berufliche Weiterbildung bestätigt Frau Sabine SandmannSüllenberger die erfolgreiche Teilnahme am Kurs "Englisch für das Büro". Die Teilnehmerin
hat sich an den schriftlichen und mündlichen Übungen mit ausdauerndem Fleiß und recht
gutem Erfolg beteiligt.
Die zu entwickelnde Datenbank wird die Verwaltung in verschiedener Hinsicht unterstützen
müssen: Beim Drucken der Teilnehmerlisten und Teilnahmebestätigungen, bei der
Aktualisierung der Kursdaten und -beschreibungen, bei Auskünften am Telefon, bei Druck und
Versand des Prospektes. Beispielsweise wird Kunden, die in den letzen 3 Jahren an
mindestens 2 Kursen teilgenommen haben, halbjährlich unaufgefordert der jeweils neue
Prospekt zugeschickt. Nach Vervollständigung der Datenbank soll auch die Anmeldung mit
Erfassung der Personaldaten, Verwaltung der Einzahlungen und das Mahnwesen unterstützt
werden.
© oszinddv – 2009 / A9 Weiterbildung.doc
1
ERM-Rückübersetzung
Universität
EvA
Datenbanken
Quelle:
A. Kemper, A. Eickler; Datenbanksysteme; Eine Einführung; Oldenbourg 1997
Aufgabe:
Überführen Sie das ER-Modell „rückwärts“ in eine verbale Beschreibung!
Hinweise: MatrNr = Matrikelnummer (Immatrikulationsnummer) = Studentennummer
SWS = Semesterwochenstunden = Anzahl der Stunden einer Vorlesung pro Woche
AnfJahr = Jahr, in dem der Student sein Studium beginnt
setzt
voraus
Vorgänger
Student
Nachfolger
Vorlesung
hört
VorlNr
MatrNr
Titel
Name
SWS
prüft
Vorname
Note
GebDat
liest
AnfJahr
Assistent
arbeitet für
Professor
PersNr
PersNr
Name
Name
Fachgebiet
Rang
Raum
© oszinddv – 2009 / A10 Universitaet (rw).doc
1
ERM-Rückübersetzung
Zugverbindung
EvA
Datenbanken
Quelle:
A. Kemper, A. Eickler; Datenbanksysteme; Eine Einführung; Oldenbourg 1997
Aufgabe:
Überführen Sie das ER-Modell „rückwärts“ in eine verbale Beschreibung!
ZugNr
Länge
Zug
startet vom
verbindet
von
endet im
nach
Abfahrt
Ankunft
Bahnhof
liegt in
Stadt
Name
Name
Gleisanz.
Bundesl.
© oszinddv – 2009 / A11 Zugverbindung (rw).doc
1
EvA
Datenbanken
Kardinalitäten
im Entity-Relationship-Modell
Mann
ist
verheiratet
mit
Frau
Mann
ist
verheiratet
mit
Frau
Mann
ist
verheiratet
mit
Frau
© oszinddv – 2009 / B1 Kardinalitaeten.doc
1
Relationales Schema
Teil 1
EvA
Datenbanken
Transformation eines Entity-Relationship-Modells in ein relationales Schema
ist
verheiratet
mit
Mann
Regel 1
Frau
MannNr
FrauNr
Name
Name
Aus jedem Entitätstypen wird eine Relation (Tabelle) erstellt.
Eine Relation ist eine Informationseinheit, die aus einer festen Anzahl von Attributen und einer
variablen Anzahl von Datensätzen besteht. Meist wird eine Relation nur Tabelle genannt.
1. Fall: 1:1-Beziehung
Gegenwärtige Ehen
3
5
Ein Mann ist verheiratet mit einer Frau.
Eine Frau ist verheiratet mit einem Mann.
6
Mann MannNr
Name
Frau
FrauNr
Name
42
Eric
57
Claudia
94
Nico
35
Petra
007
James
89
Franziska
35
Otto
17
Eva
1
2
4
Regel 2 Sind zwei Entitätstypen A und B durch eine 1:1-Beziehung verbunden, dann
1
2
3
4
5
6
© oszinddv – 2009 / C1 Relationenschema.doc
1
Relationales Schema
Teil 2
EvA
Datenbanken
2. Fall: 1:N-Beziehung
Orientalische Ehen
Mann MannNr
Name
Ein Mann ist verheiratet mit mehreren Frauen.
Eine Frau ist verheiratet mit einem Mann.
Frau
FrauNr
Name
42
Eric
57
Claudia
94
Nico
35
Petra
007
James
89
Franziska
35
Otto
17
Eva
Regel 3 Sind zwei Entitätstypen A und B durch eine 1:N-Beziehung verbunden, dann
3. Fall: M:N-Beziehung
???
Mann MannNr
Name
Ein Mann ist verheiratet mit mehreren Frauen.
Eine Frau ist verheiratet mit mehreren Männern.
Frau
FrauNr
Name
42
Eric
57
Claudia
94
Nico
35
Petra
007
James
89
Franziska
35
Otto
17
Eva
Regel 4 Sind zwei Entitätstypen A und B durch eine M:N-Beziehung verbunden, dann
© oszinddv – 2009 / C1 Relationenschema.doc
2
EvA
Datenbanken
Datenbankplanung
Zeitschriftenabonnement
Situationsbeschreibung:
Ein Unternehmen vertreibt Zeitschriftenabonnements diverser Verlage und möchte, dass Sie
eine geeignete Datenbank zur Unterstützung ihrer Arbeit planen. Dazu erhalten Sie die
folgenden Informationen.
Anmerkung: Damit das Projekt nicht zu groß wird, werden hier bewusst einige, sonst wichtige
Daten wie Vorname, Straße o. ä. weggelassen.
(1) Von den Kunden sollen der Name, Wohnort, die abonnierten Zeitschriften, der Beginn der
Abonnements und die Bankverbindung gespeichert werden.
(2) Zur Bankverbindung gehören die Kontonummer des Kunden sowie der Name, der Sitz und
die Bankleitzahl der zugehörigen Bank. Das Unternehmen hat bereits eine Tabelle, die zu
jeder BLZ den Namen und Sitz der Bank enthält. Diese Tabelle sollen Sie in Ihre
Planungen mit einbeziehen.
(3) Zu den Zeitschriften sind der Titel, Preis, die Anzahl der pro Jahr erscheinenden Ausgaben
und der Verlag zu speichern.
(4) Zu den Verlagen müssen der Verlagsname, ein Ansprechpartner und die Telefonnummer in
der Datenbank stehen.
(5) Es kommt nicht vor, dass eine Zeitschrift von zwei Verlagen herausgegeben wird, und
keine zwei Verlage haben denselben Namen.
(6) Sollten Ihnen noch einige Informationen fehlen, können Sie zu den bekannten Arbeitszeiten
den Systembeauftragten des Unternehmens aufsuchen und befragen.
Aufgaben:
1.
Planen Sie das vollständige ERM für diese Aufgabe. Eine Schnellskizze genügt.
2.
Notieren Sie die Kardinalitäten in Ihrer Skizze.
3.
Zeichnen Sie in Word Ihr ERM einschließlich Kardinalitäten, Überschrift und Fußzeile
mit Name, Datum, Klasse. Drucken Sie das ERM aus und legen es zur Kontrolle vor.
4.
Überführen Sie Ihr ERM in ein relationales Datenbankmodell. Zeichnen Sie dieses
Modell in Excel. Geben Sie zu jeder Datenbanktabelle zwei realistische Datensätze an.
Dabei sollen einige Fremdschlüsselwerte auch als Primärschlüsselwerte in den
entsprechenden Tabellen existieren. Drucken Sie auch dieses Modell.
5.
Planen Sie die Tabellenstrukturen. Dazu müssen Sie für jedes Attribut (Feldname) den
Datentyp (Felddatentyp) und ggf. dessen Länge (Feldgröße) planen. Außerdem müssen
Sie die Primärschlüsselattribute kennzeichnen (z. B. mit einem Schlüsselsymbol) und
die NULL-Entscheidungen treffen, also ob eine Eingabe in einem Feld erforderlich ist
oder nicht. Die Tabellenstrukturplanung zeichnen Sie in Excel in derselben Datei wie
das Datenbankschema, aber auf einem anderen Tabellenblatt, und drucken es
ebenfalls.
© oszinddv – 2009 / C4 Abonnement.doc
1
EvA
Datenbanken
Phasen des
Datenbank-Entwurfs
Der Entwurf einer Datenbank gliedert sich in folgende Phasen:
1.
In der Anforderungsanalyse werden zunächst die Anforderungen der Nutzer der zu
entwerfenden Datenbank ermittelt. Diese Analyse kann mittels Interviews mit den einzelnen
Benutzern oder durch Fragebogenaktionen durchgeführt werden. Die Ergebnisse der
Anforderungsanalyse werden i. d. R. in einem Anforderungsdokument (User Requirements
Document) schriftlich festgelegt. Dieses Dokument muss von den Nutzern abgenommen
werden und bildet dann die Grundlage für den folgenden Entwurf.
2.
Im konzeptionellen Entwurf wird der Ausschnitt der realen Welt, der in der Datenbank
gespeichert werden soll, mit Hilfe eines Datenmodells (Entity-Relationship-Modell)
dargestellt. Da für das Verständnis dieses Datenmodells Vorkenntnisse notwendig sind,
kann es trotz seiner Eignung als Entwurfsmittel nur bedingt als Basis für die Verständigung
mit den Datenbanknutzern dienen.
3.
Im logischen Entwurf (Implementationsentwurf) erfolgt eine Umsetzung des erzeugten
Datenmodells in das zu verwendende Datenbankschema. Meist wird hierbei das relationale
Modell benutzt. Aus dem Entity-Relationship-Modell werden also direkt die entsprechenden
Relationen generiert. Diese Tabellen müssen nun auf Einhaltung der Normalformen
überprüft werden. Normalformen dienen der Vermeidung von Operationsanomalien, die zu
Inkonsistenzen im Datenbankinhalt führen können. Relationentabellen, die nicht den
Normalformen genügen, müssen entsprechend umgeformt werden.
4.
Im physischen Entwurf wird entsprechend zu den generierten Relationentabellen der
Datenbankcode für das zu verwendende Datenbanksystem erzeugt.
5.
Die Implementierungsphase besteht aus der tatsächlichen Implementierung und
Installation der Datenbank auf dem verwendeten Datenbanksystem.
© oszinddv – 2009 / D1 Phasen des DB-Entwurfs.doc
1
EvA
Was ist eine Datenbank?
Datenbanken
Eine Datenbank ist eine systematisch strukturierte, langfristig verfügbare Sammlung von Daten
einschließlich der zur sicheren Manipulation dieser Daten erforderlichen Hard- und Software.
Datenbank
Hardware
Datenbanksoftware
Datenbasis
Entwicklungsumgebung
Organisatorische
Daten (Schlüssel,
Index-Dateien,...)
DatenbankVerwaltungssystem
(DBMS)
reale Daten
Abb.
Hauptbestandteile
einer Datenbank
Nutzerprogramme
Datenbanksprache
SQL
Datenmanipulationssprache (DML)
Datendefinitionssprache (DDL)
Datensteuerungssprache (DCL)
Befehle zum
Einfügen, Ändern,
Löschen und
Einsehen von Daten
Befehle zum
Erstellen, Ändern,
Löschen und
Einsehen von
Tabellenstrukturen
Befehle zum
Erstellen, Löschen
und Auswählen
von Datenbanken
und
Zugriffsrechten
Gründe für die Verwendung von Datenbanken:
¾ Verschiedenen Nutzergruppen steht eine Datenbasis für eine gemeinsame Nutzung zur
Verfügung; die Nutzung erfolgt sowohl im Dialog als auch durch Programme.
¾ Die Datenbasis als Modell eines Realitätsausschnittes erlaubt verschiedenen Nutzern eine
unterschiedliche Sicht auf die Daten.
¾ Die realen Daten sind unabhängig von Nutzerprogrammen und damit von
Verarbeitungsprozessen, und die Nutzung ist unabhängig von der physischen Speicherform.
¾ Durch die zentralisierte Verwaltung (DBMS und DB-Administrator) wird Redundanz
vermieden und die inhaltliche Vollständigkeit (Integrität) sowie die logische Korrektheit
(Konsistenz) gesichert; durch eine Nutzerverwaltung lassen sich Zugriffsrechte vergeben
und überwachen (Datenschutz).
© oszinddv – 2009 / D2 DBMS.doc
1
EvA
Datenbanken
Structured Query Language
SQL
Die Structured Query Language, kurz SQL, ist eine Skriptsprache, die gleichermaßen dem
Datenbankadministrator, dem Datenbankentwickler und dem Datenbankbenutzer einen Zugriff
auf die Datenstruktur und die Daten in einer Datenbank ermöglicht. Mit SQL sind Sie in der Lage,
Datenbanken und Tabellen einzurichten und zu löschen, Relationen zu definieren oder Daten in
den Tabellen zu verwalten und abzufragen. SQL agiert als eine Art Programmschnittstelle
zwischen einem Datenbankanwender bzw. einer Anwendungssoftware (API), die auf eine
Datenbank zugreift, und dem Datenbanksystem.
Alle Datenbankoperationen können mit Hilfe der SQL-Programmschnittstelle ausgeführt werden. SQL ist eine einfache und strukturierte Skriptsprache, die eine beschreibende Vorgehensweise unterstützt. Mit SQL
beschreibt der Benutzer das Ergebnis einer Datenbankoperation, ohne
zu definieren, wie dieses Ergebnis erzielt wird. Dadurch war es möglich,
die Anweisungen in SQL an die englische Sprache anzulehnen. Beide
Kriterien führen dazu, dass sich SQL von traditionellen Programmierund Skriptsprachen wie C, PHP oder Perl unterscheidet.
SQL ist relativ leicht zu erlernen und besteht eigentlich nur aus einer
Handvoll von Basisanweisungen. Die Komplexität und Stärke von SQL
begründet sich allerdings nicht so sehr aus den Basisbefehlen wie GRANT, CREATE, DELETE
oder SELECT, sondern vielmehr aus den zahlreichen Anweisungsoptionen auch Klauseln
genannt. Je nach Datenbankhersteller ist der SQL-Standard entweder erweitert worden oder
aber nur eingeschränkt verfügbar, wie das aus Gründen der Performance bei kleinen und
mittleren Datenbanksystemen der Fall ist. Einer der mächtigsten Befehle in SQL ist die SELECTAnweisung. Mit diesem Befehl können Sie komplexe Anfragen an die Datenbank stellen und
Tabellen zu einem relationalen Datenmodell verknüpfen.
SQL-Geschichte
Die Structured Query Language wurde in den 70er Jahren in den Forschungslabors von IBM
entwickelt, um Daten in dem ersten relationalen Datenbanksystem System R verwalten zu
können. SQL ist damals speziell für eine Zielgruppe von Datenbankbenutzern aus dem Bereich
der Buchhaltung und Verwaltung entwickelt worden. Den Durchbruch als StandardKommunikationsschnittstelle schaffte SQL allerdings erst zu Beginn der 80er Jahre, als ORACLE
Corp, eines der ersten und zugleich populärsten kommerziellen Datenbanksysteme mit dem
Namen ORACLE auf den Markt brachte. Seitdem gilt SQL als Standardsprache für Datenbanken
und wurde später in andere Datenbanksysteme wie INFORMIX, SYBASE oder MS-SQL
implementiert. Es dauerte allerdings noch bis 1986, bis SQL durch die ANSI (Abk. für American
National Standard Institute] normiert, d.h. zum verbindlichen Standard SQL1 für alle Entwickler
von Datenbanksystemen erklärt wurde. Der Standard von 1986 enthielt alle wesentlichen
Anweisungen für die Definition (DDL) und die Manipulation (DML) von Daten einer relationalen
Datenbank. 1987 wurde der Standard international von der ISO (Abk. für International Standard
Organization) übernommen. Mit einer Reihe von Erweiterungen, u. a. der Einführung von
Schlüsseln und Integritätsprüfungen, wurde 1989 SQL1 in der Version SQL89 zum verbindlichen
weltweiten Industriestandard erklärt. Bis zu diesem Zeitpunkt hatten sich bereits, wie so oft in der
Softwarebranche, verschiedene Dialekte von SQL entwickelt, die letztlich eine Kompatibilität, d.h.
den uneingeschränkten Austausch von Programmen zwischen Datenbanksystemen
verschiedener Anbieter, erschwerten. Erst der 92er Standard SQL92 oder SQL2 nahm eine
Reihe weiterer Spracherweiterungen auf. Dazu gehörten der Befehl ALTER zur nachträglichen
Modifikation der Tabellenstruktur, Domänen und Transaktionen. SQL2 wird heute weitgehend als
SQL-Standard verwendet, wobei nur sehr große und kostenintensive relationale
Datenbanksysteme einen Großteil des vorgeschlagenen Sprachumfangs implementiert haben.
Mit dem zunehmenden Einsatz objektorientierter Ansätze im Datenbankbereich entstand Mitte
der 90er Jahre verstärkt die Notwendigkeit, die Verwaltung von objektorientierten Daten in einem
erweiterten SQL-Sprachkonzept zu verankern. Zurzeit wird daher intensiv an einem neuen
Standard SQL3 gearbeitet, der vor allem objektorientierte Ansätze berücksichtigen soll. Er ist
allerdings noch nicht verabschiedet.
Quelle: Das Einsteigerseminar MySQL, bhv, 2001, S. 95 ff.
© oszinddv – 2009 / D3 SQL-Info.doc
1
EvA
Datenbanken
Folgender Text ist dem MySQL 5.1 Reference Manual entnommen.
http://dev.mysql.com/doc/refman/5.1/en/index.html
Lesen Sie den Text durch und arbeiten Sie die wesentlichen Merkmale von MySQL heraus!
1.4.1. What is MySQL?
MySQL, the most popular Open Source SQL database management system, is developed,
distributed, and supported by MySQL AB. MySQL AB is a commercial company, founded by the
MySQL developers. It is a second generation Open Source company that unites Open Source
values and methodology with a successful business model.
The MySQL Web site (http://www.mysql.com/) provides the latest information about MySQL
software and MySQL AB.
MySQL is a database management system.
A database is a structured collection of data. It may be anything from a simple shopping
list to a picture gallery or the vast amounts of information in a corporate network. To add,
access, and process data stored in a computer database, you need a database
management system such as MySQL Server. Since computers are very good at handling
large amounts of data, database management systems play a central role in computing,
as standalone utilities, or as parts of other applications.
MySQL is a relational database management system.
A relational database stores data in separate tables rather than putting all the data in one
big storeroom. This adds speed and flexibility. The SQL part of “MySQL” stands for
“Structured Query Language.” SQL is the most common standardized language used to
access databases and is defined by the ANSI/ISO SQL Standard. The SQL standard has
been evolving since 1986 and several versions exist. In this manual, “SQL-92” refers to
the standard released in 1992, “SQL:1999” refers to the standard released in 1999, and
“SQL:2003” refers to the current version of the standard. We use the phrase “the SQL
standard” to mean the current version of the SQL Standard at any time.
MySQL software is Open Source.
Open Source means that it is possible for anyone to use and modify the software.
Anybody can download the MySQL software from the Internet and use it without paying
anything. If you wish, you may study the source code and change it to suit your needs.
The MySQL software uses the GPL (GNU General Public License),
http://www.fsf.org/licenses/, to define what you may and may not do with the software in
different situations. If you feel uncomfortable with the GPL or need to embed MySQL code
into a commercial application, you can buy a commercially licensed version from us. See
the MySQL Licensing Overview for more information
(http://www.mysql.com/company/legal/licensing/). …
MySQL Server works in client/server or embedded systems.
The MySQL Database Software is a client/server system that consists of a multi-threaded
SQL server that supports different backends, several different client programs and
libraries, administrative tools, and a wide range of application programming interfaces
(APIs).
We also provide MySQL Server as an embedded multi-threaded library that you can link
into your application to get a smaller, faster, easier-to-manage standalone product. …
The official way to pronounce “MySQL” is “My Ess Que Ell” (not “my sequel”), but we don't mind
if you pronounce it as “my sequel” or in some other localized way.
© oszinddv – 2009 / D4 MySQL-Info (englisch).doc
1
EvA
Datenbanken
SQL-Datentypen
Numerische Datentypen
In einer Datenbank können ganz unterschiedliche Daten gespeichert werden. Für die
verschiedenen Arten von Daten stellt das Datenbanksystem sogenannte Datentypen zur
Verfügung. Die folgende Auflistung fasst die wichtigsten SQL-Datentypen zusammen. Sie
können fünf verschiedenen Basisdatentypen zugeordnet werden.
•
Numerische Daten: ganze oder gebrochene Zahlen
•
Alphanumerische Daten: Ziffern, Buchstaben, Zeichen oder Zeichenketten
•
Datum und Zeit: je nach Art als Spezialform numerischer oder alphanumerischer Daten
•
Mengen: vordefinierte zulässige Ausprägungen der Attributwerte
•
Binärdaten (BLOB – binary large object): unstrukturierte Daten in binärer Form
Numerische Datentypen (INTEGER, FLOAT, DECIMAL)
Numerische Datentypen werden in die zwei Kategorien ganze Zahlen (engl. integer) und
Fließkommazahlen (engl. float) eingeteilt.
Der INTEGER-Datentyp wird für Daten verwendet, die durch ganze Zahlen dargestellt werden
können, z. B. für die Anzahl von Objekten, fortlaufende Nummerierungen etc. Je nach Bedarf
der größtmöglichen Zahl gibt es TINYINT (1 Byte Speicherplatzbedarf pro gespeicherter Zahl),
SMALLINT (2 Byte), MEDIUMINT (3 Byte), INTEGER (4 Byte) und BIGINT (8 Byte)
Der FLOAT-Datentyp wird für gebrochene Zahlen, d. h. Zahlen mit Nachkommastellen (z. B.
Maßzahlen) eingesetzt. Der Datentyp FLOAT entspricht der IEEE-Norm für die
Zahlendarstellung mit den damit verbundenen Fehlern in der Genauigkeit. Daher gibt es für
Anwendungen bei denen Rundungsfehler ausgeschlossen werden müssen (z. B. Geldbeträge)
den Datentyp DECIMAL (Festkommazahl). Hierbei werden die Zahlen als Zeichenketten
gespeichert, d. h. ein Byte Speicherplatz pro Ziffer, sowie Dezimalpunkt und Vorzeichen der
gespeicherten Zahl. Beachten Sie bei der Eingabe einer gebrochenen Zahl in einem SQLBefehl, dass Sie immer einen Dezimalpunkt statt eines Kommas eingeben.
Datentyp
TINYINT(M)
Bezeichnung
Ganzzahl
Wertebereich
– 128 ... + 127
(1 Byte)
INTEGER(M)
kurz: INT
Ganzzahl
– 2147483648
...
+ 2147483647
(4 Byte)
FLOAT(M,D)
Fließkommazahl
– 1.17549E-38
...
+ 3.4028E+38
(4 Byte)
DECIMAL(P,S)
Festkommazahl
beliebig
(je Stelle ein
Byte)
© oszinddv – 2009 / E1 SQL-Datentypen.doc
Bemerkungen (speziell MySQL)
Der Befehlszusatz UNSIGNED stellt auf
0 … 255 um. Das ist bei allen numerischen
Datentypen möglich.
M (maximum display size) gibt an, wie viele
Stellen angezeigt werden sollen. Weder der
zulässige Wertebereich noch die erlaubte
Stellenanzahl werden dadurch eingeschränkt.
Praktisch ergibt sich also keine Auswirkung.
In diesem Fall gibt M die Anzahl aller Ziffern
an, D jene davon nach dem Dezimalpunkt.
D bewirkt ein Runden der Zahlen beim Speichern, wenn mehr Nachkommastellen eingegeben werden als zugelassen.
M schränkt sogar den Wertebereich ein, d. h.
eine Zahl mit mehr als den möglichen Vorkommastellen wird auf die größt-darstellbare
Zahl verkleinert.
Z. B. wird bei FLOAT(4,2) aus 273.1 der Wert
99.99 und aus 37.317 der Wert 37.32
P (precision) gibt die gesamte gewünschte
Stellenanzahl an (ohne Dezimalpunkt und
neg. Vorzeichen, wodurch pos. Zahlen eine
Stelle mehr haben können). S (scale) gibt die
Anzahl der Stellen nach dem Dezimalpunkt
an. Z. B. reicht DECIMAL(6,3) von -999.999
bis 9999.999. Größere Eingaben werden wie
bei Float auf diese Werte verkleinert.
1
SQL-Datentypen
übrige Datentypen
EvA
Datenbanken
Alphanumerische Datentypen (CHAR, VARCHAR, TEXT)
Daten vom Typ CHAR belegen im Speicher immer eine vorher festgelegte Speicherplatzlänge.
Dagegen belegen Daten vom Typ VARCHAR nur so viele Bytes, wie Zeichen in einer
Zeichenkette vorhanden sind. Weil ausreichender Speicherplatz heute kein Problem mehr
darstellt, sollten Sie den Datentyp CHAR verwenden, da Tabellen mit fester Länge schneller
verarbeitet werden können als Tabellen mit variabler Länge.
Falls Sie in der Datenbank Zeichenketten speichern wollen, die mehr als 255 Zeichen enthalten,
müssen Sie den Datentyp TEXT verwenden.
Datentyp
CHAR(n)
VARCHAR(n)
TEXT
Bezeichnung
Zeichenkette mit n Stellen
Variable Zeichenkette, max. n Stellen
Text
Wertebereich
0 ... 255 Zeichen
0 ... 255 Zeichen
0 ... 65535 Zeichen
Zeichenketten müssen in SQL-Befehlen immer in Anführungszeichen eingegeben werden. Am
besten verwenden Sie die einfachen Hochkommata. Zahlen werden übrigens nicht in
Anführungszeichen gesetzt.
Datums- und Zeittypen (DATE, YEAR, TIME)
Da in Datenbanken oft zeitabhängige Prozesse abgebildet werden, wurden in SQL zusätzliche
Datentypen für Datums- und Zeitangaben aufgenommen, wobei die wichtigsten DATE und
YEAR sind. Beachten Sie bei der Eingabe eines Datums das Format JJJJ-MM-TT.
Datentyp
DATE
Bezeichnung
Datum
YEAR
TIME
Jahr
Uhrzeit
Wertebereich
1000-01-01 bis
9999-12-31
1901 bis 2155
–838:59:59 bis
+838:59:59
Format
JJJJ-MM-TT
Beispiel
"2007-03-28"
JJJJ
hh:mm:ss
1984
"15:37:12"
Mengentyp (SET)
Der Datentyp SET ist ein spezieller Zeichenkettendatentyp. Die Besonderheit gegenüber allen
anderen Datentypen besteht darin, dass der Datenbankentwickler selbst einen Wertebereich
definieren kann, indem eine SQL-Anweisung alle möglichen Werte definiert, die ein Objekt
innerhalb des Datentyps SET annehmen kann.
Datentyp
SET
Bezeichnung
Menge
Wertebereich
64 Elemente
mit 0 ... 255 Zeichen
Beispiele
"rot", "grün", "blau"
"Nord", "Süd", "Ost", "West"
"true", "false"
Binärer Datentyp (BLOB – binary large object)
Ein BLOB ist eine Art Behälter, in dem Sie binär codierte Objekte, also Text-, Bild-, Audio- oder
Videodaten (z. B. MP3-Dateien) speichern können. Da eine relationale Datenbank jedoch keine
Algorithmen zur Mustererkennung in unstrukturierten Daten zur Verfügung stellt, führt man in
der Regel in Tabellenspalten vom Datentyp BLOB keine Suche nach Objekten durch. Vielmehr
verwaltet man in Spalten vom Typ CHAR oder VARCHAR Metainformationen über spezielle
binäre Objekte.
© oszinddv – 2009 / E1 SQL-Datentypen.doc
2
EvA
Metasprache und Syntax
Datenbanken
Syntax
SQL-Befehle, die an eine Datenbank geschickt werden, müssen nach festgelegten Regeln
geschrieben werden, da sie sonst das DBMS nicht „versteht“. Diese Regeln werden als Syntax
einer Datenbanksprache (oder Programmiersprache) bezeichnet.
Metasprache
Die Syntax einer Sprache wird in einer künstlichen Sprache, der sogenannten Metasprache
notiert. Ganz allgemein ist eine Metasprache eine Sprache, mit der eine andere Sprache erklärt
oder definiert wird, mit der also die Regeln für diese Sprache festgelegt werden. Meta kommt
aus dem Griechischen (µετα) und bedeutet unter anderem soviel wie „hinter, über“.
Darstellungsarten
In der Informatik wird üblicherweise die Syntax einer Programmiersprache als Syntaxdiagramm
gezeichnet oder in der erweiterten Backus-Naur-Form (EBNF) aufgeschrieben. Die EBNF
wurde 1960 von den Wissenschaftlern Backus und Naur entwickelt und später dann erweitert.
Wir werden als Metasprache die Symbole nach EBNF verwenden.
Metasprachenelement
nach EBNF
Bedeutung
Beispiele
GROSSBUCHSTABEN
Befehlsworte und Schlüsselworte
der Sprache, die genau so im
Befehl verwendet werden müssen.
CREATE DATABASE
Befehlsteile, die vom Benutzer
eingesetzt werden müssen.
<GROSSBUCHSTABEN> Kleinbuchstaben bedeuten, dass
ein Bezeichner frei zu wählen ist;
Großbuchstaben zeigen an, dass
aus einer Liste von Schlüsselworten auszuwählen sei.
<datenbankname>
[]
Optionalsymbol
Alles in den eckigen Klammern
kann, muss aber nicht im Befehl
verwendet werden.
[NOT NULL]
Alternativentrennung
Genau eine der angegebenen
Befehlsteile muss gewählt werden.
Alternativen in eckigen Klammern
bedeutet, dass eine gewählt
werden kann.
ROT | GELB | GRÜN
Wiederholungssymbol
Das Vorangegangene ist
(mehrfach) zu wiederholen. Meist
in eckigen Klammern gesetzt als
Möglichkeit einer Wiederholung.
Die geschweiften Klammern
bedeuten automatisch keine, eine
oder mehrmalige Wiederholung.
<wert1>, <wert2>, …
<kleinbuchstaben>
|
…
oder
{}
(nach EBNF)
© oszinddv – 2009 / E2 Metasprache.doc
<DATENTYP>
[DEFAULT <standardwert>]
[FIRST | AFTER]
[, …]
<wert1> {,<wert2>}
1
SQL-Befehl
CREATE TABLE
EvA
Datenbanken
CREATE TABLE
Die CREATE TABLE-Anweisung erzeugt eine neue Tabelle unter dem angegebenen Namen.
Mit dieser Anweisung definieren Sie die Datenstruktur einer Tabelle, d. h. Sie müssen für jede
Tabellenspalte einen Bezeichner (Attributnamen), einen Datentyp und Eigenschaften angeben.
Syntax:
CREATE TABLE <tabellenname>
( <attributname1> <DATENTYP>
[DEFAULT <standardwert>]
[AUTO_INCREMENT] [NOT NULL] [,
<attributname2> <DATENTYP> [DEFAULT <standardwert>]
[AUTO_INCREMENT] [NOT NULL] [, ...]],
PRIMARY KEY ( <attributname> [, ...] ));
Eigenschaften:
DEFAULT
AUTO_INCREMENT
NOT NULL
Für ein Datenfeld wird ein Standardwert vorgegeben. Nicht anwendbar für Spalten
vom Datentyp BLOB oder TEXT.
Das Datenbanksystem fügt in diese Spalte automatisch eine Zahl ein.
AUTO_INCREMENT-Werte beginnen mit 1 und werden mit jedem neuen
Datensatz, der in die Tabelle eingefügt wird, um 1 hochgezählt. Die Spalte muss
den Datentyp Integer besitzen, als Primärschlüssel (oder Index) definiert und NOT
NULL gesetzt sein.
Die AUTO_INCREMENT-Option kann für die interaktive Eingabe von Daten von
großer Bedeutung sein, da jedem Datensatz automatisch eine eindeutige
Nummerierung zugewiesen wird. Der Benutzer der Datenbank muss sich in
diesem Fall nicht um die fortlaufende Kennzeichnung von Datensätzen kümmern.
Der Nachteil ist, dass auf diese Weise identische (redundante) Datensätze in den
Datenbestand gelangen können, die sich nur durch die automatisch generierte
Kennung unterscheiden.
Gibt an, dass die Spalte Werte besitzen muss, die nicht NULL sind. Durch diese
Option wird eine Eingabe von Werten erzwungen, da, falls keine Dateneingabe
stattfindet, ein Datenfeld in der Regel auf den Wert NULL gesetzt wird.
Beispiel:
CREATE TABLE buch
( BuchNr INTEGER AUTO_INCREMENT NOT NULL,
Autor CHAR(10),
Titel CHAR(20),
Preis DECIMAL(7,2),
Jahr
YEAR,
PRIMARY KEY (BuchNr));
Aufgabe:
Erstellen Sie die Datenbank “db_<IhreKennung>_bibliothek” und darin diese Tabelle “buch”.
DROP TABLE
Die Anweisung DROP TABLE löscht eine oder mehrere spezifizierte Tabellen einer Datenbank.
Beachten Sie, dass mit dem Löschen einer Tabelle auch alle gespeicherten Daten verloren
gehen.
Syntax:
DROP TABLE <tabellenname> [, <tabellenname2>, ...];
Aufgabe:
Löschen Sie die Tabelle “buch” und danach Ihre Bibliotheksdatenbank.
© oszinddv – 2009 / E3 CREATE TABLE.doc
1
EvA
SQL-Syntax (Auswahl)
Datenbanken
CREATE DATABASE <datenbankname>;
_________________________________________________________________________________________________________________________________________
DROP DATABASE <datenbankname>;
_________________________________________________________________________________________________________________________________________
USE <datenbankname>;
_________________________________________________________________________________________________________________________________________
CREATE TABLE <tabellenname>
( <attributname1> <DATENTYP>
[DEFAULT <standardwert>]
[AUTO_INCREMENT] [NOT NULL] [,
<attributname2> <DATENTYP> [DEFAULT <standardwert>]
[AUTO_INCREMENT] [NOT NULL] [, ...]],
PRIMARY KEY ( <attributname> [, ...] ));
_________________________________________________________________________________________________________________________________________
DROP TABLE <tabellenname> [, <tabellenname2> [, ...]];
_________________________________________________________________________________________________________________________________________
ALTER TABLE <tabellenname>
ADD <neuer_attributname> <DATENTYP>
[DEFAULT <standardwert>] [AUTO_INCREMENT] [NOT NULL]
[FIRST | AFTER <existierender_attributname>];
_________________________________________________________________________________________________________________________________________
ALTER TABLE <tabellenname>
CHANGE <alter_attributname> <neuer_attributname> <DATENTYP>
[DEFAULT <standardwert>] [AUTO_INCREMENT] [NOT NULL]
_________________________________________________________________________________________________________________________________________
ALTER TABLE <tabellenname>
DROP <attributname>;
_________________________________________________________________________________________________________________________________________
CREATE INDEX <indexname> ON <tabellenname> (<attributname>);
_________________________________________________________________________________________________________________________________________
DROP INDEX <indexname> ON <tabellenname>;
_________________________________________________________________________________________________________________________________________
INSERT INTO <tabellenname>
[(<attributname1>, <attributname2>, ... )]
VALUES (<wert1_vom_ds1>, <wert2_vom_ds1>, ...) [,
(<wert1_vom_ds2>, <wert2_vom_ds2>, ...) [, ...]];
_________________________________________________________________________________________________________________________________________
UPDATE <tabellenname>
SET <attributname1> = <wert1> [,
<attributname2> = <wert2> [, ... ]]
[WHERE <bedingung>];
_________________________________________________________________________________________________________________________________________
DELETE FROM <tabellenname>
[WHERE <bedingung>];
_________________________________________________________________________________________________________________________________________
SELECT [DISTINCT]
* | <attributname1> | <FUNCTION> [AS <alias1>] [,
<attributname2> | <FUNCTION> [AS <alias2>] [, ... ]]
FROM <tabellenname1>
[, <tabellenname2>, <tabellenname3>, ...
| INNER JOIN <tabellenname2> ON <bedingung_1_2>
INNER JOIN <tabellenname3> ON <bedingung_2_3> ... ]
[WHERE <bedingung>]
[GROUP BY <gruppierungsattributname>] [HAVING <bedingung>]
[ORDER BY <ordnungsattributname1> [ASC | DESC] [,
<ordnungsattributname2> [ASC | DESC] [, ... ]];
© oszinddv – 2009 / E4 SQL-Syntax.doc
1
EvA
SQL-Syntax (Erweiterung)
Datenbanken
Sonderoperatoren
BETWEEN
IN
LIKE
_________________________________________________________________________________________________________________________________________
Gruppierungsfunktionen (Aggregatfungktionen)
AVG (<spaltenausdruck> | <ausdruck>)
COUNT (* | <spaltenausdruck> | <ausdruck>)
MAX (<spaltenausdruck> | <ausdruck>)
MIN (<spaltenausdruck> | <ausdruck>)
SUM (<spaltenausdruck> | <ausdruck>)
_________________________________________________________________________________________________________________________________________
Zeichenkettenfunktionen
LOWER (<stringausdruck>)
SUBSTRING (<stringausdruck>, <position> [, <länge>])
TRIM (<stringausdruck>)
UPPER (<stringausdruck>)
_________________________________________________________________________________________________________________________________________
Zugriffsberechtigungen
GRANT <berechtigung_art1>
[(<attributname1> [, <attributname2> , ...)]]
[,<berechtigung_art2>
[(<attributname1> [, <attributname2> , ...)]]]| ALL
ON {<tabellenname> | *.* | <datenbankname.*>}
TO <benutzername1> [IDENTIFIED BY <passwort1>] |
[, <benutzername2> [IDENTIFIED BY <passwort2>], ...] | PUBLIC
_________________________________________________________________________________________________________________________________________
REVOKE <berechtigung_art1>
[(<attributname1> [, <attributname2>, ...)]
[, <berechtigung_art2>
[(<attributname1> [, <attributname2>, ...)]]
ON {<tabellenname> | *.* | <datenbankname.*>}
FROM <benutzername1> [, <benutzername2>, ...]
_________________________________________________________________________________________________________________________________________
© oszinddv – 2009 / E4 SQL-Syntax.doc
2
EvA
Datenbanken
SQL-Übung
Flotte Kiste
Die Vorlage für die folgenden Aufgaben ist das ER-Modell „Flotte Kiste“ mit der entsprechenden
Transformation in das.
Notieren Sie die notwendigen SQL-Befehle auf diesem Blatt oder kopieren Sie sie aus dem
SQL-Editor in ein Word-Dokument, das Sie sich am Ende ausdrucken können.
1. Erstellen Sie die Datenbank für die Flotte Kiste mit Ihrem persönlichen Datenbanknamen
„db_<IhreKennung>_flottekiste“.
2. Erstellen Sie die notwendigen Tabellen gemäß dem relationalen Datenbankschema.
Die Tabellenstrukturen können Sie sich im Frontend-Programm grafisch anzeigen lassen.
3. Tragen Sie sich als Kunden in die Datenbank ein!
4. Tragen Sie folgende Oldtimer in die Datenbank ein:
B-ET 324; Traction Avant; Citroen; 1299 ccm; 36 PS; Baujahr 1936; 34,00 € pro Tag
B-RL 9299; Phantom II; Rolls-Royce; 7688 ccm; 130 PS; Baujahr 1936; 70,00 € pro Tag
5. Mieten Sie alle eingetragenen Oldtimer zu einem frei wählbaren Datum, aber zahlen Sie nur
den Mietvorgang mit dem Citroen.
6. Haben Sie einen Mietgrund eingetragen? Nein, dann löschen Sie das Feld (Attribut, ganze
Spalte) aus der Datenbank. Wenn ja, dann auch!
7. Sorry, der Citroen hat nur 34 PS. Bitte ändern Sie dies im entsprechenden Datensatz.
8. Statt „Ja“ und „Nein“ für Bezahlt soll in Zukunft das Datum der Zahlung gespeichert werden.
Ändern Sie den Feldtyp in der Tabelle „mieten“ entsprechend! Was passiert mit den Daten?
9. Sie haben einen Totalschaden am Rolls-Royce verursacht. Löschen Sie dieses Fahrzeug
aus der Datenbank. Was passiert mit den Mietvorgängen des Rolls-Royce?
10. Nur ein Kunde, der dann noch nicht mal zahlt und das teuerste Auto zu Schrott fährt, ist für
jede Autovermietung das Ende. Die „Flotte Kiste“ ist Konkurs!
Löschen Sie zunächst die Tabelle „kunde“ und dann die ganze Datenbank!
© oszinddv – 2009 / E5 SQL FlotteKiste.doc
1
SQL-Übung
Universität
EvA
Datenbanken
Gegeben ist das folgende ER-Diagramm.
Planen Sie die Relationen für die Entitätstypen
Student und Vorlesung inkl. Datentypen und
NULL-Entscheidung.
Student
setzt
voraus
Vorgänger
Nachfolger
Vorlesung
hört
VorlNr
MatrNr
Titel
Name
SWS
prüft
Vorname
Note
GebDat
liest
AnfJahr
Assistent
arbeitet für
Professor
PersNr
PersNr
Name
Name
Rang
Fachgebiet
Raum
Notieren Sie die SQL-Befehle für die folgenden Aufgaben und testen Sie diese danach am PC.
1. Erstellen Sie die Datenbank für die Universität!
2. Erstellen Sie die Tabellen „student“ und „vorlesung“ gemäß Ihrer Planung.
3. Tragen Sie sich und zwei weitere Personen als Studenten in die Datenbank ein.
4. Planen Sie zwei Vorlesungen und tragen Sie sie ein.
5. Haben Sie daran gedacht, dass die Beziehung zwischen Vorlesung und Professor eine
1:N-Beziehung ist? Wenn nicht, dann ergänzen Sie in der Tabelle „vorlesung“ das Feld
„gelesenVon“. Wieso? Und was ist mit „setzt voraus“? – Gehen Sie dabei von 1:1 aus.
Alles berücksichtigt? Dann Hut ab, wenn ich einen hätte. Die notwendigen SQL-Befehle
können Sie dann sicherlich auch – also schnell notieren.
6. Der Rotstift hat auch die Universität erreicht! Reduzieren Sie die Semesterwochenstunden
(SWS) bei allen Vorlesungen um eine Stunde.
7. Ist der Datentyp des Feldes „AnfJahr“ vom Typ YEAR? Wenn nicht, dann ändern Sie bitte
Ihre Tabellenstruktur. Wenn ja, dann ... (s. o.)
8. Nun haben Sie alles so schön angefangen aufzubauen. Wenn Sie wollen können Sie die
komplette Datenbank zu Hause fertig stellen und 100 Professoren, 500 Assistenten, 20000
Studenten und eine für alle ausreichende Anzahl von Vorlesungen eintragen – unser Dank
wäre Ihnen sicher. Sie können aber auch alles wieder löschen. Beginnen Sie mit einem
Zelleninhalt, dann einen Datensatz, ein ganzes Feld, eine Tabelle und zum Schluss löschen
Sie die gesamte Datenbank. Und nicht vergessen: Alle SQL-Befehle vorher notieren.
© oszinddv – 2009 / E6 SQL Universitaet.doc
1
EvA
Datenbanken
SQL-Übung
Zeitschriftenabonnement
Grundlage für diese Übung ist die Tabellenstruktur der Datenbank „Zeitschriftenabonnement“,
die Sie aus dem dazugehörigen ERM und relationalen Datenbankschema entwickelt haben.
Da Sie nur die Tabelle „zeitschrift“
benötigen werden, ist deren Struktur hier
noch einmal abgebildet.
Selbstverständlich dürfen Sie alle übrigen
Tabellen als ergänzende Übung in ähnlicher
Form behandeln.
Table: zeitschrift
Name
Type
Null
znr
int
No
titel
preis
anzJahr
vName
char(30)
decimal(4,2)
int
char(20)
Yes
Yes
Yes
Yes
Notieren Sie die SQL-Befehle für die folgenden Aufgaben und testen Sie diese danach am PC.
1. Erstellen Sie die Datenbank „abonnement“ (ggf. als „db_<IhreKennung>_abo“)!
2. Erstellen Sie die Tabelle „zeitschrift“ gemäß obiger Planung.
3. Tragen Sie folgende Datensätze in die Tabelle ein:
Nr. 39, „Wissen für alle“, 6,30 € pro Ausgabe, monatliche Erscheinungsweise, „Verlag H&K“
Nr. 74, „Kochen für die Katz“, 2,95 €, 26 Ausgaben pro Jahr, „Verlag der Tierfreunde“
4. Geben Sie eine alphabetisch geordnete Liste aller Verlage aus.
5. Sie werden bemerkt haben, dass 20 Zeichen für den Verlag nicht ausreichen, erhöhen Sie
den Wert auf 30.
6. Lassen Sie sich für die Zeitschriften die Preise für ein Jahresabonnement ausgeben.
7. Erst jetzt fällt auf, dass eine Eigenschaft über Zeitschriften in der Tabelle fehlt, und zwar an
welchem Wochentag eine Zeitschrift erscheint (sofern bekannt). Tragen Sie das Attribut
„wochentag“ mit 10 Zeichen vor den Verlagsnamen ein und geben Sie bei „Kochen für die
Katz“ den Freitag an.
8. Der Verlag H&K erhöht die Preise aller seiner Zeitschriften um 6 Prozent.
9. Wenn Sie wollen, können Sie nun alle anderen Tabellen erzeugen und mit Leben füllen, also
mindestens 200 Kunden, 50 Zeitschriften, 20 Verlage und viele Abonnements. Wenn Ihnen
das doch ein wenig zu viel Arbeit ist, dann löschen Sie bitte alles wieder, aber schön
langsam – und nicht vergessen: Alle SQL-Befehle vorher notieren!
a) Beginnen Sie mit einem Zelleninhalt, indem Sie den Titel der Zeitschrift 74 löschen.
b) Danach löschen Sie das Attribut „anzJahr“.
c) Löschen Sie nun den gesamten Datensatz 39.
d) Löschen Sie die Tabelle „zeitschrift“.
e) Ganz zum Schluss entfernen Sie die gesamte Datenbank.
© oszinddv – 2009 / E7 SQL Abonnement.doc
1
EvA
Datenbanken
Tabellenstruktur der Datenbank
„Flotte Kiste“
Die folgenden Screenshots zeigen die Tabellenstrukturen der Datenbank „Flotte Kiste“.
© oszinddv – 2009 / F2 SQL-Abfragen FlotteKiste04.doc
1
EvA
SQL-Abfragen „Flotte Kiste“
Datenbanken
Teil 1: Abfragen über einzelne Tabellen
Allgemeine Hinweise für alle Teile der SQL-Abfragen zur Flotten Kiste:
• Verwenden Sie die Datenbank FlotteKiste04 als User info.
• Notieren Sie die SQL-Befehle in gewohnter Weise.
• Notieren Sie die Antworten der Datenbank, wie Namen etc. oder mind. wie viele Kunden …
1.1
Wie lautet der Vorname von Frau Ackermann?
1.2
Wer wohnt in Kleinkleckersdorf?
1.3
Von einem Kunden haben Sie nur die Postleitzahl 10889 notiert. Wer ist es?
1.4
Wer wohnt im Holzweg 7 und welche Telefonnummer hat dieser Kunde?
1.5
Wer wohnt in Berlin?
1.6
Wer wohnt in Berlin oder in Potsdam?
1.7
Wer wohnt weder in Berlin noch in Potsdam?
1.8
Welcher Oldtimer hat das Kennzeichen B-SL 300?
1.9
Wie viele PS hat der Rolls-Royce Phantom II?
1.10 Welche Oldtimer sind jünger als 50 Jahre?
1.11 Wer hat kein Telefon (angegeben)?
© oszinddv – 2009 / F2 SQL-Abfragen FlotteKiste04.doc
2
EvA
SQL-Abfragen „Flotte Kiste“
Datenbanken
Teil 2: Abfragen mit weiteren Operatoren
2.1
Wessen Vornamen beginnen mit einem R?
2.2
Geben Sie alle Kunde an, die den Nachnamen Müller, Schulze, Daniel oder Walter haben!
2.3
Sie suchen eine Person Maier/Meyer oder mit ähnlicher Schreibweise. Wo wohnt sie?
2.4
Welche Oldtimer sind jünger als 50 Jahre aber älter als 40 Jahre?
2.5
Erstellen Sie eine Telefonliste aller Kunden aus dem PLZ-Bereich 21000 bis 24000.
2.6
Geben Sie eine Liste aller Hersteller aus, von denen Rudi Rost Oldtimer besitzt!
2.7
Geben Sie diese Herstellerliste alphabetisch sortiert aus!
2.8
Sortieren Sie die Oldtimer nach der Leistung (PS), wobei der stärkste Wagen zuerst
aufgeführt werden soll.
2.9
Welche Vorlieben hat Rudi Rost erfasst?
2.10 Welche Vorlieben haben die Berliner und Potsdamer?
2.11 Ermitteln Sie die Namen und Adressen der Kunden, die als Hausnummer eine 13 haben!
© oszinddv – 2009 / F2 SQL-Abfragen FlotteKiste04.doc
3
EvA
SQL-Abfragen „Flotte Kiste“
Datenbanken
Teil 3: Aggregatfunktionen + Subselect
3.1
Wie viele Kunden hat Rudi Rost?
3.2
Wie viele Kunden haben kein Telefon (angegeben)?
3.3
Wie viele Mietvorgänge sind noch nicht bezahlt worden?
3.4
Wie hoch ist der durchschnittliche Hubraum aller Oldtimer?
3.5
Wie hoch ist die minimale Leistung (PS) aller Oldtimer?
3.6
Welches Fahrzeug (Hersteller, Typ) hat diese minimale Leistung?
3.7
Welcher Oldtimer (Hersteller, Typ) hat den größten Hubraum?
3.8
Welche Oldtimer haben mehr als die durchschnittliche Leistung aller Oldtimer? Sortieren
Sie die Liste aufsteigend nach der Leistung.
3.9
Wie viele Tage wurde der Oldtimer mit dem Kennzeichen B-EW 3478 vermietet?
© oszinddv – 2009 / F2 SQL-Abfragen FlotteKiste04.doc
4
EvA
SQL-Abfragen „Flotte Kiste“
Datenbanken
Teil 4: Gruppierung
4.1
Wie hoch ist die durchschnittliche Leistung pro Hersteller?
4.2
Erstellen Sie eine Liste mit der Anzahl der Mietvorgänge pro Oldtimer (nur Kennzeichen)
sortiert nach der Anzahl der Mietvorgänge.
4.3
Rudi Rost will expandieren. Ob sich eine weitere Ausleihstation im Raum München lohnt?
Wie viele Kunden gibt es pro Ort in den Orten, deren PLZ mit 8 beginnen? Sortieren Sie
absteigend nach der Anzahl, innerhalb gleicher Werte die Orte alphabetisch.
4.4
Für welche Vorlieben haben sich jeweils mindestens 15 Kunden entschieden?
4.5
Aus welchen Baujahren vor 1950 hat Rudi Rost mehr als einen Oldtimer?
4.6
Erstellen Sie eine Liste mit den treuesten Kunden (nur Kundennummer). Als treu zählt ein
Kunde, wenn er mindestens zehnmal einen Oldtimer gemietet hat.
4.7
Welche Oldtimer (nur Kennzeichen) fuhren diese treuesten Kunden?
4.8
Rudi Rost möchte einen Monat im Jahr Urlaub machen! In welchem Monat sind die
wenigsten Mietvorgänge?
© oszinddv – 2009 / F2 SQL-Abfragen FlotteKiste04.doc
5
EvA
SQL-Abfragen „Flotte Kiste“
Datenbanken
Teil 5: Abfragen über mehrere Tabellen
5.1
Wann wurde der Triumph TR2 vermietet?
5.2
Welche Oldtimer (Hersteller, Typ) wurden im 1. Quartal 2003 gemietet?
5.3
Erstellen Sie eine nach den Kundennamen sortierte Mahnliste, wobei jeder Mahnvorgang
einzeln aufgeführt werden soll.
5.4
Listen Sie alle Oldtimer (Hersteller, Typ) mit der Anzahl der Mietvorgänge im Jahr 2003
auf. Sortieren Sie die Liste absteigend nach der Anzahl.
5.5
Welche Kunden haben bisher einen Ferrari oder Maserati gemietet?
5.6
Welche Kunden aus Berlin haben bisher einen Ferrari oder Maserati gemietet?
5.7
Welche Kunden haben welchen Oldtimer zu ihrer Hochzeit gemietet?
5.8
Welche Oldtimer (Hersteller, Typ) werden von Kunden gemietet die in Berlin oder
Potsdam wohnen, und die „Nobelkarossen“ als Vorliebe haben? Sortieren Sie nach
Herstellern!
5.9
Listen Sie alle Kunden auf, die in 2003 einen Austin-Healey oder in 2004 einen Triumph
gemietet haben, deren Vorliebe aber nicht „Cabrio“ ist.
5.10 Sie werten das Jahr 2003 aus. Wer hat einen Austin-Healey oder Triumph gemietet,
deren Vorliebe aber nicht „Cabrio“ ist und wer hat einen Porsche gemietet, hat aber keine
Vorliebe für „Sportwagen“? Sortieren Sie nach Hersteller und Typ!
5.11 Listen Sie die Kunden mit der Anzahl ihrer bezahlten Mietvorgänge auf!
5.12 Welche Kunden haben den Ferrari bereits mehrmals gemietet?
5.13 Der „TR2“ hat einen Kratzer. Wann wurde der Wagen zuletzt vermietet? Und an wen?
5.14 Im Rahmen einer Mailingaktion sollen nur die Kunden angeschrieben werden, die mehr
als 7x einen Oldtimer gemietet haben. Wer ist das?
5.15 Da die letzte Mailingaktion so erfolgreich war, will Rudi Rost nun gezielt die Kunden
anschreiben, deren letzte Vermietung vor 2004 lag.
© oszinddv – 2009 / F2 SQL-Abfragen FlotteKiste04.doc
6
EvA
Datenbanken
Tabellenstruktur der Datenbank
„Auftragsbearbeitung“ (AuBe)
Die folgenden Screenshots zeigen die Tabellen der Datenbank „Auftragsbearbeitung“.
© oszinddv – 2009 / F3 SQL-Abfragen AuBe06.doc
1
EvA
Datenbanken
SQL-Abfragen „AuBe“
Teil 1: Abfragen über einzelne Tabellen
Teil 2: Abfragen mit weiteren Operatoren
Allgemeine Hinweise für alle Teile der SQL-Abfragen zur AuBe:
• Verwenden Sie die Datenbank Aube06 als User info.
• Notieren Sie die SQL-Befehle in gewohnter Weise.
• Notieren Sie die Antworten der Datenbank, wie Namen etc. oder mind. wie viele Kunden …
1.1
Listen Sie alle Angaben über alle Kunden auf!
1.2
Erstellen Sie eine reine Adressliste aller Kunden!
1.3
Im Rahmen einer Mailing-Aktion benötigen Sie für den Serienbrief die Daten aller Kunden
die nur einen geringen Rabatt haben (weniger als 5%).
1.4
Die Mailing-Aktion soll nur auf Berlin beschränkt werden.
1.5
Erstellen Sie eine Preisliste für alle Artikel der Artikelgruppe „Scanner“!
2.1
Verschaffen Sie sich eine Übersicht über alle Artikelgruppen!
2.2
Eine weitere Preisliste wird für die LCD-Monitore benötigt!
2.3
Für hochpreisige Drucker (über 1000,00) sollen Sonderkonditionen eingeführt werden.
Welche Artikel sind betroffen? Sortieren Sie nach dem Preis!
2.4
Eine weitere Sonderaktion soll für die Tastaturen, Joysticks und Mäuse gestartet werden.
Welche Artikel sind betroffen?
2.5
Ein Kunde fragt nach Soundkarten unter 50,00 und einfachen Lautsprechern für unter
40,00. Welche Angebote können Sie machen? Zuerst sollen die Lautsprecher aufgeführt
werden!
© oszinddv – 2009 / F3 SQL-Abfragen AuBe06.doc
2
EvA
Datenbanken
SQL-Abfragen „AuBe“
Teil 3: Aggregatfunktionen
Teil 4: Gruppierung
3.1
Wie viele Artikel führt das Unternehmen?
3.2
Wie hoch ist der höchste Rabatt, der einem Kunden gewährt wird, und wie hoch ist der
Durchschnittsrabatt aller Kunden?
3.3
Wie viele Aufträge gingen im Juni 2006 ein?
3.4
Was ist der Durchschnittspreis aller Drucker?
3.5
Zwischen welchen Beträgen liegen die Lautsprecherpreise?
4.1
Führen Sie für jede Artikelgruppe die Anzahl der Artikel auf!
4.2
Die Geschäftsführung wünscht eine Übersicht der eingegangenen Aufträge pro Monat.
4.3
Führen Sie alle Kundennummern auf, die bereits mehr als 125 Aufträge erteilt haben!
4.4
Wie viele Artikelstücke umfasste der Auftrag 11047? Klären Sie vorher die Anzahl der
Positionen dieses Auftrags.
4.5
Wenig bestellte Artikel sollen aus dem Sortiment entfernt werden.
Erstellen Sie eine Liste aller Artikel, die weniger als 25-mal bestellt wurden, sortiert nach
der Anzahl der Bestellungen!
© oszinddv – 2009 / F3 SQL-Abfragen AuBe06.doc
3
EvA
SQL-Abfragen „AuBe“
Datenbanken
Teil 5: Abfragen über mehrere Tabellen
5.1
Erstellen Sie eine Liste aller Kunden, die am 2006-10-27 einen Auftrag erteilt haben.
Sortieren Sie die Liste nach den Kundennamen.
5.2
Ermitteln Sie den Gesamtumsatz für das Jahr 2006 ohne Berücksichtigung der
Kundenrabatte!
5.3
Ermitteln Sie für das Jahr 2006 den Umsatz pro Monat sortiert nach Monaten!
5.4
Wann hat der Kunde „Wolle“ seinen letzten Auftrag aufgegeben?
5.5
Im Rahmen einer Rückrufaktion müssen alle Käufer des „INTEL PENTIUM IV 1700 PGA
423pin“ angeschrieben werden. Stellen Sie die Daten für einen Serienbrief zusammen!
5.6
Die Kundin Frau Knute soll die Rechnung für ihre im Dezember 2006 erteilten Aufträge
erhalten. Stellen Sie alle notwendigen Daten zusammen. Vergessen Sie nicht den Rabatt.
5.7
Ermitteln Sie den Umsatz je Artikelgruppe unter Berücksichtigung der Kundenrabatte!
Sortieren Sie die Ausgabe nach dem Umsatz absteigend!
5.8
Bei der Auftragsaufnahme ist versehentlich eine Artikelnummer aufgenommen worden,
die es gar nicht gibt! Welche ist es?
© oszinddv – 2009 / F3 SQL-Abfragen AuBe06.doc
4
Abfragen über mehrere Tabellen
Kartesisches Produkt
EvA
Datenbanken
produkt
Nr
Bezeichnung
12
Dübel M6
58
Preisgruppe
Gruppe
Preis
E
A
1,20
Schraube M8
B
B
1,50
70
Mutter M10
C
C
1,80
83
Scheibe M12
B
D
2,00
E
2,50
preisgruppe
Ð
Nr
Bezeichnung
12
Dübel M6
Ð
Preisgruppe
Gruppe
Preis
E
Aufgabe: Die Produkte sollen mit ihren Preisen aufgelistet werden.
© oszinddv – 2009 / F4 Kartesisches Produkt.doc
1
EvA
SQL-Abfragen „Uni“
Datenbanken
Teil 5: Abfragen über mehrere Tabellen
Formulieren Sie zu dieser Darstellung die Frage und die dazugehörige Select-Anweisung!
© oszinddv – 2009 / F5 SQL-Abfragen Uni.doc
1
EvA
SQL-Abfragen „Uni“
Datenbanken
Komplexe Abfragen
Formulieren Sie zu obiger Darstellung den SQL-Befehl!
© oszinddv – 2009 / F5 SQL-Abfragen Uni.doc
2
EvA
Datenbanken
Normalisierung
1. Normalform und Anomalien
Um schlechte Relationenschemata zu vermeiden, gibt es die sogenannten Normalformen.
Ziel ist es, Relationen so lange verlustfrei in Teilrelationen zu zerlegen, bis sie mindestens der
dritten Normalform (3. NF) entsprechen. Dadurch können Datenbankanomalien (s. u.) und
Dateninkonsistenzen weitgehend ausgeschlossen werden.
Dateninkonsistenzen sind Fehler in einer Datenbank, die durch Datenredundanz entstehen.
Daten sind redundant, wenn sie doppelt oder mehrfach gespeichert sind, obwohl darauf ohne
Informationsverlust verzichtet werden könnte.
1. Normalform Eine Relation (Tabelle) liegt in der ersten Normalform vor, wenn
Die folgende Tabelle wird genutzt, um einen Überblick darüber zu gewinnen, welcher
Mitarbeiter eines großen Unternehmens an welchen der zahlreichen Projekten beteiligt ist.
Jeder Mitarbeiter hat eine eindeutige Mitarbeiter-Nummer (MNr). Seine Adressenangaben (hier
nur teilweise dargestellt) und Abteilungszugehörigkeit werden ebenfalls in der Tabelle
gespeichert. Aus Platzgründen wurden hier weitere Informationen über die Abteilungen und
Projekte (z. B. Abteilungsleiter und Projektleiter) ebenfalls weggelassen.
MNr
Name
Adresse
AbtNr
AbtBez
ProjektNr
M19
Schweizer
Hauptstr. 75, Frenkendorf
A6
Finanz
{P4, P9}
M1
Meier
Lindenstr. 15, Liestal
A3
Informatik
{P7, P11, P9}
M7
Huber
Mattenweg 94, Basel
A5
Personal
{P4}
M4
Becker
Wasserweg 2, Liestal
A3
Informatik
{P4, P9}
© oszinddv – 2009 / G1 Normalisierung.doc
Projektstart
{01.08.,
01.06.}
{15.05.,
01.09.,
01.06.}
{01.08.}
{01.08.,
01.06.}
Eintrittsdatum
{17.09.,
01.06.}
{15.05.,
01.09.,
01.07.}
{20.08.}
{01.09.,
01.06.}
1
EvA
Datenbanken
Normalisierung
2. Normalform
Eine Relation (Tabelle) liegt in der zweiten Normalform vor, wenn
Frage:
Zerlegungsmethode
© oszinddv – 2009 / G1 Normalisierung.doc
2
EvA
Datenbanken
Normalisierung
3. Normalform
Eine Relation (Tabelle) liegt in der dritten Normalform vor, wenn
Frage:
Zerlegungsmethode
© oszinddv – 2009 / G1 Normalisierung.doc
3
Normalisierung
Funktionale Abhängigkeit
EvA
Datenbanken
Die 2. Normalform lautet:
Eine Relation liegt in der zweiten Normalform vor, wenn sie in der ersten Normalform ist und
jedes Nichtschlüsselattribut vom Primärschlüssel voll funktional abhängig ist, d. h. ein
Nichtschlüsselattribut darf sich nicht schon durch einen Teil des Primärschlüssels kennzeichnen
lassen.
Was bedeutet diese funktionale Abhängigkeit?
1. Definition
Ein Attribut B ist von einer Attributmenge A1 ... An funktional abhängig, wenn
es für jede Attributwertekombination a1 ... an genau einen Attributwert aus B
gibt.
Beispiele: {A1, A2} Æ B
2. Definition
Ein Attribut B ist von einer Attributmenge A1 ... An voll funktional abhängig,
wenn es funktional abhängig ist und die Attributmenge A1 ... An minimal
definiert ist, d. h. beim Weglassen irgendeines Attributs der Menge A1 ... An gilt
die funktionale Abhängigkeit nicht mehr.
Beispiele: {A1} =
{A2} =
{A1, A2} =
Verwenden Sie für die Beispiele die bekannte Mitarbeitertabelle in erster Normalform.
MNr
Name
Strasse
Ort
M19
Schweizer
Hauptstr. 75
M19
Schweizer
M1
Eintrittsdatum
AbtBez
Frenkendorf
A6
Finanz
P4
01.08.
17.09.
Hauptstr. 75
Frenkendorf
A6
Finanz
P9
01.06.
01.06.
Meier
Lindenstr. 15
Liestal
A3
Informatik
P7
15.05.
15.05.
M1
Meier
Lindenstr. 15
Liestal
A3
Informatik
P11
01.09.
01.09.
M1
Meier
Lindenstr. 15
Liestal
A3
Informatik
P9
01.06.
01.07.
M7
Huber
Mattenweg 94
Basel
A5
Personal
P4
01.08.
20.08.
M4
Becker
Wasserweg 2
Liestal
A3
Informatik
P4
01.08.
01.09.
M4
Becker
Wasserweg 2
Liestal
A3
Informatik
P9
01.06.
01.06.
© oszinddv – 2009 / G1 Normalisierung.doc
ProjektNr
Projektstart
AbtNr
4
Normalisierung-Übung
Dozenten
EvA
Datenbanken
Das folgende Relationenschema könnte kaum schlimmer sein, also die Ärmel hochgekrempelt
und ’ran an die Arbeit:
1. Welche Anomalien treten in einer Datenbank, die nach diesem Schema aufgebaut ist, auf?
Geben Sie jeweils ein Beispiel aus dem Schema an.
2. Welche Normalform hat dieses Relationenschema? Begründen Sie!
3. Normalisieren Sie das Schema weiter.
4. Mit einem gut geplanten ERM wäre das alles gar nicht erst passiert. Zeichnen Sie das zu
dieser Aufgabe passende ER-Diagramm.
Kurs
KursNr KursBez
Halbjahr DozKürzel DozName
DozVorname DozTel
47
Windows-Einführung
2007/2
Ra
Raben
Susanne
71485541
103
Business-English I
2007/2
Kü
Kühnel
Winfried
3397631
142
Windows-Einführung
2008/1
Ra
Raben
Susanne
71485541
144
Excel-Einführung
2008/2
Hu
Huller
Anna
47884970
153
Englisch auf Reisen
2008/2
Kü
Kühnel
Winfried
3397631
© oszinddv – 2009 / G2 Dozenten.doc
1
Normalisierung-Übung
Materiallager
EvA
Datenbanken
In einer Datenbank sind Informationen über Verbrauchsmaterialien (Bürobedarf) gespeichert.
Zweck der Datenhaltung ist eine schnelle Übersicht, was von welchem Lieferanten bezogen
werden kann. Ein Lagerbestand, getätigte Bestellungen oder gar ein Bezug zu Rechnungen ist
damit nicht möglich und nicht gewünscht.
Folgende Informationen sind gespeichert:
1. Jedes Material besitzt eine eindeutige Materialnummer (MatNr) und eine Bezeichnung
(MatBez).
2. Gleiche Materialien werden oft von verschiedenen Lieferanten bezogen.
3. Jeder Lieferant hat eine eindeutige Lieferantennummer (LiefNr) sowie einen Firmennamen
(Firma) und eine Anschrift.
4. Für jeden Lieferanten werden seine Bestellnummer des interessierenden Materials, die
Verpackungseinheit (VE) sowie der Lieferantenpreis gespeichert.
Im Folgenden sehen Sie einen Ausdruck der Datenbanktabelle (auszugsweise).
Normalisieren Sie das Relationenschema.
Material MatNr
MatBez
LiefNr
185
Papier blanco
8
185
Papier blanco
21
137
Bleistift
21
264
Radierer
8
137
Bleistift
15
186
Papier kariert
8
© oszinddv – 2009 / G3 Materiallager.doc
Firma
Anschrift
Im Tintenfleck 12
01142 Dresden
Papiergasse 1
Paperback
90384 Nürnberg
Papiergasse 1
Paperback
90384 Nürnberg
Im Tintenfleck 12
Ink GmbH
01142 Dresden
Holzweg 7
Woodstock AG
10463 Berlin
Im Tintenfleck 12
Ink GmbH
01142 Dresden
Ink GmbH
BestellNr
VE
Preis
8020
500
2,79
526
1000
4,99
155
20
9,00
8150
5
2,00
4594
10
4,20
8030
100
0,95
1
EvA
Datenbanken
Normalisierung-Übung
Lieferanten
Überführen Sie die Tabelle schrittweise in die 3. Normalform!
© oszinddv – 2009 / G4 Lieferanten.doc
1
Normalisierung-Übung
Rechnung
EvA
Datenbanken
Rechnung
Re-Nummer
56843
Ku-Nummer
2065
Ku-Name
GUBA GmbH
Datum: 16. 10. ...
Ku-Anschrift
Mittelstr. 24
12585 Belin
Artikel-
Bezeichnung
Nr
Stück-
Menge
Gesamtpreis
preis (EURO)
(EURO)
A720
Kinderset
3,20
100
320,00
K612
Handfeger
1,80
50
90,00
E007
Nussknacker
33,79
1
33,79
443,79
Netto-Gesamtwert der Bestellung
Tragen Sie alle Attribute und die dazugehörigen Attributwerte aus obenstehendem
Rechnungsauszug in das Schema ein.
RENummer
KuNummer
56843
2065
Ku-Name
GUBA
GmbH
KuAnschrift
ReDatum
ArtikelNr
Mittelstr. 24
16.10. ...
Bezeichnung
Stückpreis
Menge
Gesamtpreis
NettogesamtBestellwert
443,79
A720
Kinderset
3,20
100
320,00
12585
K612
Handfeger
1,80
50
90,00
Berlin
E007
Nussknacker
33,79
1
33,79
Führen Sie die Normalisierung bis zur 3. NF durch und zeichnen Sie anschließend das ERM.
© oszinddv – 2009 / G5 Rechnung.doc
1
EvA
Datenbanken
SQL-Befehl
CREATE INDEX
Was ist ein Index?
Im Allgemeinen versteht man unter einem Index eine geordnete Liste von Objekten, die
normalerweise in ungeordneter Reihenfolge vorliegen. Der Vorteil des Indizierens besteht darin,
dass Suchvorgänge in einer geordneten Liste wesentlich schneller durchgeführt werden
können, als es die sequenzielle Suche in einer ungeordneten Menge von Objekten erlaubt.
Ein anschauliches Beispiel ist das Stichwortverzeichnis in einem Buch. Informationen zu einem
Stichwort können an ganz verschiedenen Stellen in einem Buch auftreten. Die sequenzielle
Suche bedeutet nun, dass Sie jede Buchseite anschauen und herausfinden müssen, ob sie das
betreffende Stichwort enthält oder nicht. Schneller ist man dagegen, wenn man in einer
alphabetisch geordneten Stichwortliste zuerst das Stichwort nachschlägt und dort die
betreffenden Seitenzahlen findet, wo Informationen zu einem Stichwort zu finden sind.
In ähnlicher Weise verfährt auch das Datenbanksystem, wenn Sie für eine spezielle Tabellenspalte einen Index anlegen. Ein Index verbessert die Performance bei der Suche mit
zusammengesetzten Ausdrücken oder beim Sortieren von Suchergebnissen. Ein Index wurde
in SQL eingeführt, da das relationale Datenmodell alle Datensätze einer Tabelle als
ungeordnete Menge von Datensätzen verwaltet und standardmäßig eine sequenzielle Suche
durchgeführt wird.
Merklich werden diese Unterschiede in den Antwortzeiten natürlich erst bei einer großen Anzahl
von Datensätzen. Das Indizieren von Tabellen ist dann sinnvoll, wenn man entweder sehr
große Datenbestände verwalten möchte oder wenn man Attribute hat, die sehr oft für die Suche
nach Daten verwendet werden.
CREATE INDEX
Mit der Anweisung CREATE INDEX wird ein Index für eine bereits vorhandene Tabelle erstellt.
Syntax:
CREATE INDEX <indexname> ON <tabellenname> (<attributname>);
Beispiel:
CREATE INDEX buch_index ON buch (autor);
Unser Beispiel legt für das Attribut autor in der Tabelle buch einen Index mit der Bezeichnung
buch_index an. Dadurch ist bei einer entsprechend großen Anzahl von Datensätzen zu
erwarten, dass die Autorensuche und das alphabetische Sortieren der Autoren wesentlich
schneller abläuft.
DROP INDEX
Mit der Anweisung DROP INDEX wird ein bereits vorhandener Tabellenindex wieder gelöscht.
Syntax:
DROP INDEX <indexname> ON <tabellenname>;
Beispiel:
DROP INDEX buch_index ON buch;
In unserem Beispiel wird der Index buch_index zur Tabelle buch gelöscht.
Quelle: Das Einsteigerseminar MySQL, bhv, 2001, S. 119 ff.
© oszinddv – 2009 / H1 CREATE INDEX.doc
1
EvA
Transaktionen
Datenbanken
In einer Bankanwendung findet der Transfer von € 50,00 von Konto A nach Konto B statt.
Welche Operationen sind notwendig?
1.
2.
3.
4.
5.
6.
Mögliche Fehlerfälle:
¾ Systemabsturz nach Schritt 3.
¾ Beide Konten gehören einem Kunden, bei Abhebung von Konto A wird der
Gesamtdispositionsrahmen über alle Konten des Kunden überzogen.
¾ Im Parallelbetrieb werden 3 % Zinsen auf Konto A berechnet.
¾ Absturz des Systems kurz nach Beenden der Transaktion.
Eigenschaften des Transaktionskonzepts werden oft unter der Abkürzung ACID zusammengefasst. Das sogenannte ACID-Paradigma steht dabei für vier Eigenschaften:
Consistency (Konsistenz) Eine Transaktion hinterlässt nach Beendigung einen konsistenten
Datenbasiszustand. Anderenfalls wird sie komplett (siehe Atomarität) zurückgesetzt.
Zwischenzustände, die während der TA-Bearbeitung entstehen, dürfen inkonsistent sein,
aber
der
resultierende
Endzustand
muss
die
im
Schema
definierten
Konsistenzbedingungen erfüllen.
Durability (Dauerhaftigkeit) Die Wirkung einer erfolgreich abgeschlossenen Transaktion bleibt
dauerhaft in der Datenbank erhalten. Die Transaktionsverwaltung muss sicherstellen, dass
dies auch nach einem Systemfehler (Hardware oder Systemsoftware) gewährleistet ist. Die
einzige Möglichkeit, die Wirkungen einer einmal erfolgreich abgeschlossenen Transaktion
ganz oder teilweise aufzuheben, besteht darin, eine andere sogenannte kompensierende
Transaktion auszuführen.
Isolation Diese Eigenschaft verlangt, dass nebenläufig (parallel, gleichzeitig) ausgeführte
Transaktionen sich nicht gegenseitig beeinflussen. Jede Transaktion muss – logisch
gesehen – so ausgeführt werden, als wäre sie die einzige Transaktion, die während ihrer
gesamten Ausführungszeit auf dem Datenbanksystem aktiv ist. Mit anderen Worten, alle
anderen parallel ausgeführten Transaktionen bzw. deren Effekte dürfen nicht sichtbar sein.
Atomicity (Atomarität) Diese Eigenschaft verlangt, dass eine Transaktion als kleinste, nicht
mehr weiter zerlegbare Einheit behandelt wird, d. h. entweder werden alle Änderungen der
Transaktion in der Datenbasis fest geschrieben oder gar keine.
© oszinddv – 2009 / H2 Transaktionen.doc
1