Datenbanksysteme 1 - Fachbereich Informatik und Sprachen

Bernburg
Dessau
Köthen
Hochschule Anhalt (FH)
Hochschule für
angewandte Wissenschaften
Prof. Dr.-Ing. Detlef Klöditz
Fachbereich Informatik
Lohmannstraße 23
06366 Köthen / Anhalt
Telefon: 03496 67-3118
Fax:
03496 67-3199
email:
[email protected] -anhalt.de
Datenbanksysteme 1
Aufgaben für Übungen und Praktika
Nur zum internen Gebrauch
an der Hochschule Anhalt (FH)
Wintersemester 2004/2005
i
Datenbanksysteme 1
Lehrveranstaltung für Studierende in den Diplom-Studiengängen
Informatik und Information Management im 3. Semester
sowie als Wahlpflichtfach für Studierende technischer Studiengänge
mit 2 SWS Vorlesung, 1 SWS Übung und 1 SWS Praktikum
im Wintersemester 2004/2005
1.
1.1.
1.2.
1.3.
1.4.
Einführung
Was ist ein Datenbanksystem?
Anwendungsgrundsätze für Datenbanksysteme
Betrieb von Datenbanksystemen
Entwicklung von Datenbanksystemen
2.
2.1.
2.2.
2.3.
2.4.
Das relationale Datenmodell
Konzepte
Attribute, Relationenschemata, Datenbankschemata
Konsistenzbedingungen
Beispiel-Datenbank
3.
Die relationale Abfragesprache SQL (Structured Query Language)
4.
4.1.
4.2.
4.3.
Relationenalgebra
select-Operation, project-Operation, join-Operation
Folgen von Operationen
Mengenoperationen
5.
5.1.
5.2.
5.3.
Das Entity-Relationship-Modell (ERM)
Konzepte: Objekte, Objekttypen, Beziehungen, Beziehungstypen
Entity-Relationship-Diagramme
Das Erweiterte ER-Modell:
Subklassen, Superklassen, Spezialisierung, Generalisierung
6.
6.1.
6.2.
6.3.
Abhängigkeiten und Normalisierung
Abhängigkeiten als Ursachen für Inkonsistenzen
Der Normalisierungsprozess
Normalformen (1NF, 2NF, 3NF)
7.
7.1.
7.2.
Übertragung des ER-Modells in das relationale Datenmodell
Abbilden von ER-Schemata auf relationale Datenbank-Schemata
Primärschlüssel und Fremdschlüssel
8.
8.1.
8.2.
8.3.
Die SQL-Datendefinitionssprache (DDL)
Tabellen definieren
Einschränkungen
Sichten, Sequenzen, Indexe, Synonyme
3.1.
3.2.
3.3.
3.4.
3.5.
3.6.
3.7.
3.8.
Übersicht
Einfache Abfragen
SQL*Plus
Auswahlbedingungen
SQL-Funktionen
Gruppierung von Daten
Subqueries
Abfragen über mehrere Tabellen
ii
9.
9.1.
9.2.
9.3.
Die SQL-Datenmanipulationssprache (DML)
Tabellen füllen
Daten ändern
Dateninhalte aus Dateien laden
10.
10.1.
10.2.
Rechteverwaltung
Arten von Rechten
Vergabe und Entzug von Rechten
11.
11.1.
11.2.
11.3.
11.4.
11.5.
Transaktionen
Begriff, Eigenschaften
Konkurrierende Transaktionen
Transaktionsstatus, Zustandsdiagramm
Systemprotokoll, commit-Punkt, Sicherungspunkt
Transaktionsverwaltung mit SQL
12.
12.1.
12.2.
12.3.
Weitere Datenmodelle
Netzwerk-Datenmodell
Hierarchisches Datenmodell
Vergleich der Datenmodelle
13.
13.1.
13.2.
13.3.
13.4.
Architektur moderner Datenbankbetriebssysteme
Datenbank -Architektur
Software-Architektur
Datenbank -Prozesse
Data Dictionary
14.
14.1.
14.2.
14.3.
Offene Probleme
DB-Entwicklungstechnologie und DB-Programmierung
DB-Betrieb und DB-Administration
Neue und Nichtstandard-DB-Systeme / OODBS
Übungen
1.
2.
3.
4.
5.
6.
7.
Praktika
1.
2.
3.
4.
5.
6.
SQL: Abfragen auf eine Tabelle
Abfragen über mehrere Tabellen
Komplexe Abfragen, Gruppierung
Datenbank -Modellierung / Entity-Relationship-Modell
Normalformen / Normalisierung
Von der Realwelt zur Datenstruktur:
ER-Modell / Relationales Datenbankmodell / Datendefinition
Relationale Algebra
Handhabung von Oracle, SQL*Plus, Einfache Abfragen
Abfragen über mehrere Tabellen
Gruppierungen und Subqueries (Testat)
Datenbank -Entwurf (Testat)
Implementieren einer Datenbank, Füllen mit Daten
Nutzen der Datenbank, Abfragen (Testat)
Zulassungsvoraussetzungen für die Prüfung (Schein)
§
Übungen: ein bestandener Test (Kurzarbeit)
§
Hausaufgaben: drei abgelieferte (und akzeptierte) Lösungen
§
Praktikum: drei Testate
Fachprüfung
§
Abschlussklausur (120 min)
iii
Ausgewählte Literatur-Empfehlungen
Elmasri / Navathe: Grundlagen von Datenbanksystemen.
2002, Pearson Studium
Kemper / Eickner: Datenbanksysteme. 1996, Oldenbourg-Verlag München
Schicker: Datenbanken und SQL. 2000, Teubner-Verlag
Heuer / Saake: Datenbanken – Konzepte und Sprachen.
1997, Internat. Thompson Publ. Bonn
Heuer: Objektorientierte Datenbanken – Konzepte, Modelle, Systeme.
1992, Addison-Wesley Bonn
Date / Darwen: SQL – Der Standard. 1998, Addison Wesley Bonn
Misgeld: SQL – Einstieg und Anwendung. 1991, Hanser-Verlag München
Rolland: Datenbanksysteme im Klartext. 2003, Pearson Studium
Vossen: Datenmodelle, Datenbanksprachen und DB-Managementsysteme.
1994, Addison Wesley Bonn
Wedekind: Datenbanksysteme, Bd. 1. 1991, BI-Wissenschaftsverlag Mannheim
Zehnder: Informationssysteme und Datenbanken. 1989, Teubner-Verlag
Meier: Relationale Datenbanken – Eine Einführung für die Praxis.
1992, Springer-Verlag
Lang / Lockemann: Datenbankeinsatz. 1995, Springer-Verlag
Abbey / Corey: Oracle8 – Beginners Guide. 1998, Addison-Wesley
Eirund / Kohl: Datenbanken – leicht gemacht. 2003, Teubner-Verlag
Steiner: Grundkurs Relationale Datenbanken. 2003, Vieweg-Verlag
Jarosch: Grundkurs Datenbankentwurf. 2003, Vieweg-Verlag
iv
Hochschule Anhalt (FH)
Fachbereich Informatik
Prof. Dr.-Ing. Klöditz
Datenbanksysteme 1
Wintersemester 2004/2005
Seite 1
0. Übung:
Wir diskutieren folgende Fragen:
1.
Was verstehen Sie unter einer Datenbank?
2.
Auf welche Art können große Datenmengen verwaltet werden?
3.
Wie kann man auf einzelne Datensätze und Datenwerte zugreifen?
4.
Welche Funktionen werden für die Verwaltung eines Datenbestandes benötigt?
5.
Wie sollte die Benutzungsschnittstelle aussehen?
6.
Kann man die Benutzer in verschiedenen Gruppen einteilen? In welche?
7.
Was geschieht, wenn mehrere Nutzer „gleichzeitig“ auf die gleichen Daten zugreifen
wollen?
8.
Welche Probleme können dabei entstehen?
9.
Wie könnte man einen zuverlässigen Dauerbetrieb 7/24 sichern?
10.
Wie kann man einen definierten Zugriffsschutz organisieren?
Hochschule Anhalt (FH)
Fachbereich Informatik
Prof. Dr.-Ing. Klöditz
Datenbanksysteme 1
Wintersemester 2004/2005
Seite 2
1. Übung:
Gegeben sei folgende Datenbanktabelle Telefon für die Abrechnung privater
Telefongespräche innerhalb eines Unternehmens:
Name
Sauer
Ehrig
Ahrend
Mehlig
Gabriel
Becker
null
Müller
Schulze
Schulze
Köhler
Müller
Müller
Müller
Schulz
Bauer
Lehmann
Vorname
Erika
Richard
Martha
Karl-Heinz
Anna
Silvia
null
Alfred
Jutta
Jutta
Horst
Erich
Sabine
Anton
Rolf
Siegfried
Hannelore
TelNr
143
215
271
143
211
256
302
301
261
700
260
425
425
850
333
702
362
seit
letzteAbr
12.02.01
04.02.02
04.09.01
04.07.02
01.03.98
12.07.02
12.02.01
18.07.02
23.09.98
23.08.02
03.03.01
12.09.02
null
null
24.11.01
12.09.02
06.06.01
23.08.02
04.09.00
04.07.02
01.10.00
12.07.02
22.09.00
23.08.02
03.10.99
20.08.00
01.10.00
12.07.02
14.07.00
12.07.02
12.04.00
23.08.02
11.05.00
04.07.02
Einheiten
0
287
122
21
78
0
null
29
292
361
57
123
0
13
0
53
328
Interpretieren Sie den Inhalt der Tabelle:
1.
Wie verstehen Sie die Attribute TelNr, seit, letzteAbr und Einheiten?
2.
Sind MitarbeiterInnen mit gleichen Namen auch gleiche Personen?
3.
Gibt es MitarbeiterInnen, die die gleiche Telefonnummer haben?
4.
Haben MitarbeiterInnen gleichzeitig die gleiche Telefonnummer?
5.
Worin besteht der Unterschied zwischen 0 und null Einheiten?
Formulieren Sie folgende SQL-Abfragen:
6.
Wer hat die Telefonnummer 333.
7.
Seit wann haben die Müllers ihre Telefonanschlüsse.
8.
Erzeugen Sie eine Liste aller Mitarbeiter mit 200-er Telefonnummern.
9.
Erzeugen Sie eine Liste aller Mitarbeiter, alphabetisch sortiert.
10.
Erzeugen Sie eine Liste aller Mitarbeiter, nach den Telefonnummern sortiert.
11.
Wer hat die höchste, wer die niedrigste Telefonrechnung.
12.
Wie viele verschiedene Telefonnummern gibt es.
13.
Wie viele Mitarbeiter sind in der Tabelle erfasst.
14.
Wie hoch sind die Telefongebühren der einzelnen Mitarbeiter, wenn die Einheit mit
0,15 € abgerechnet wird.
15.
Wer telefoniert pro Tag am meisten, wer am wenigsten?
Hochschule Anhalt (FH)
Fachbereich Informatik
Prof. Dr.-Ing. Klöditz
Datenbanksysteme 1
Wintersemester 2004/2005
Seite 3
16.
Sind die Telefonanschlüsse, die die gleiche Telefonnummer haben auch am gleichen
Tag eingerichtet worden?
17.
Erzeugen Sie eine Liste aller Müllers, nach dem Vornamen sortiert.
Welche Ergebnisse liefern die folgenden SQL-Abfragen:
18.
select count (name) from telefon;
19.
select count distinct (telnr) from telefon;
20.
select sum (einheiten) from telefon;
21.
select avg (einheiten) from telefon;
22.
select name, telnr, einheiten*0.15
from telefon
where einheiten > 50;
Denken Sie sich selbständig weitere Fragen aus und formulieren Sie sie in SQL.
1. Hausaufgabe:
Gegeben sei eine Tabelle mit folgender Struktur:
Kunde (KNr, Name, Vorname, Straße, PLZ, Ort, GebDatum, Kunde_seit)
Formulieren Sie die folgenden Abfragen in SQL:
1.
Erzeugen Sie eine Liste aller Kunden, die in Dessau wohnen.
2.
Erzeugen Sie eine Liste aller Kunden, die schon länger als 3 Jahre Kunde sind, nach
Orten und innerhalb der Orte alphabetisch nach Namen sortiert.
3.
Erzeugen Sie eine Adressenliste der Kunden im PLZ-Bereich 034.
4.
Wie viele Kunden wohnen im PLZ-Bereich 03.
5.
Wie viele Kunden sind jünger als 30 Jahre.
6.
Wer ist die längste Zeit Kunde, wer die kürzeste Zeit.
7.
Wie groß ist das Durchschnittsalter der Kunden.
8.
Erzeugen Sie eine Liste der Kunden, bei denen das Geburtsdatum nicht eingetragen
ist.
Schicken Sie Ihre Lösung per E-Mail an [email protected]
Abgabetermin: 25.10.2004
Hochschule Anhalt (FH)
Fachbereich Informatik
Prof. Dr.-Ing. Klöditz
Datenbanksysteme 1
Wintersemester 2004/2005
Seite 4
2. Übung:
Bei einem Händler für EDV-Gerätetechnik und -Verbrauchsmaterial erfolgt die
Lagerverwaltung durch ein Datenbanksystem. Es enthält folgende Relationen:
Lieferant (LNr, LName, Straße, PLZ, Ort, Telefon, Ansprechpartner)
Kunde (KNr, KName, Straße, PLZ, Ort, Telefon)
Artikel (ANr, ArtBezeichnung, Bestand, MinBest, MaxBest, AbgabePreis)
WarenEingang (WENr, WE_Datum, Menge, EinkaufsPreis, LNr, ANr)
WarenAusgang (WANr, WA_Datum, Menge, VerkaufsPreis, KNr, ANr)
Interpretieren Sie, was durch diese Tabellen beschrieben wird. Beantworten Sie dazu auch
folgende Fragen:
1.
Welcher Zusammenhang besteht zwischen Bestand, MinBestand und
MaxBestand in der Tabelle Artikel?
2.
Welcher Zusammenhang besteht zwischen AbgabePreis in der Tabelle Artikel und
VerkaufsPreis in der Tabelle WarenAusgang?
3.
Welche Bedeutung haben LNr und ANr in der Tabelle WarenEingang?
4.
Welche Bedeutung haben KNr und ANr in der Tabelle WarenAusgang?
5.
Was muss in den Tabellen eingetragen werden beim Wareneingang, was beim
Warenausgang?
6.
Wie entsteht eine Rechnung für den Warenausgang?
Formulieren Sie in SQL folgende Anfragen an die Datenbank:
7.
Erzeugen Sie eine Liste aller Kunden aus dem Postleitzahlenbereich 1.
8.
Geben Sie die Anzahl Lieferanten an, die im Jahre 2002 den Händler beliefert haben.
9.
Ermitteln Sie den Wert des aktuellen, des Mindest- und des Maximalbestandes des
Lagers.
10.
Welche Artikel haben einen aktuellen Lagerbestand, der kleiner als der
Mindestbestand ist.
11.
Erzeugen Sie für jeden Kunden eine Liste aller Artikel, die er im Jahre 2003 gekauft
hat, nach Kundennamen und innerhalb der Kundennamen nach Artikelbezeichnung
sortiert.
12.
Geben Sie für jeden Lieferanten an, welche Artikel er bisher geliefert hat.
Denken Sie sich selbständig weitere Fragen aus und formulieren Sie sie in SQL.
Hochschule Anhalt (FH)
Fachbereich Informatik
Prof. Dr.-Ing. Klöditz
Datenbanksysteme 1
Wintersemester 2004/2005
Seite 5
2. Hausaufgabe:
Gegeben sei das folgende Relationenstruktur:
Kunde (KNr, Name, Vorname, Strasse, PLZ, Ort)
Ferienhaus (HausIdent, Kategorie, Adresse, Betten, Preis)
mietet (MietvertragNr, KNr, HausIdent, KalWoche, Mietpreis)
Formulieren Sie folgende Abfragen in SQL:
1.
Welcher Kunde (Name und Adresse) hatte in KalWoche 2004/27 das Ferienhaus mit
dem HausIdent SEL024 gemietet.
2.
Erzeugen Sie eine Liste aller Ferienhäuser mit Swimmingpool (Kategorie = 'P'), die in
KalWoche 2004/29 vermietet waren.
3.
Ermitteln Sie, wie oft das Ferienhaus mit dem HausIdent BNZ169 im Jahr 2003
vermietet war.
4.
Ermitteln Sie das Ferienhaus, das bisher am häufigsten vermietet war.
Schicken Sie Ihr Ergebnis per E-Mail an [email protected]
Abgabetermin: 08.11.2004
Hochschule Anhalt (FH)
Fachbereich Informatik
Prof. Dr.-Ing. Klöditz
Datenbanksysteme 1
Wintersemester 2004/2005
Seite 6
3. Übung:
Bei einem Händler für EDV-Gerätetechnik und -Verbrauchsmaterial (bekannt aus der 2.
Übung) erfolgt die Lagerverwaltung durch ein Datenbanksystem. Es enthält folgende
Relationen:
Lieferant (LNr, LName, Straße, PLZ, Ort, Telefon, Ansprechpartner)
Kunde (KNr, KName, Straße, PLZ, Ort, Telefon)
Artikel (ANr, ArtBezeichnung, Bestand, MinBest, MaxBest, AbgabePreis)
WarenEingang (WENr, WE_Datum, Menge, EinkaufsPreis, LNr, ANr)
WarenAusgang (WANr, WA_Datum, Menge, VerkaufsPreis, KNr, ANr)
Formulieren Sie in SQL folgende Anfragen an die Datenbank:
1.
Erzeugen Sie eine Liste der Umsätze der einzelnen Kunden im Jahr 2002.
2.
Welcher Kunde brachte bisher den größten Umsatz?
3.
Geben Sie für jeden Lieferanten an, welche Artikel er bisher in welcher Menge
geliefert hat.
4.
Welcher Lieferant lieferte im Jahr 2003 wertmäßig am meisten?
5.
Welche Lieferanten liefern welche Artikel, nach Artikelbezeichnung sortiert?
6.
Gibt es Lieferanten, die noch nie etwas geliefert haben?
7.
Welches war der niedrigste Preis, zu dem die einzelnen Artikel jemals geliefert wurden
und wann war das?
8.
Welcher Lieferant lieferte die einzelnen Artikel jeweils zum niedrigsten Einkaufspreis?
Denken Sie sich selbständig weitere Fragen aus und formulieren Sie sie in SQL.
Hochschule Anhalt (FH)
Fachbereich Informatik
Prof. Dr.-Ing. Klöditz
Datenbanksysteme 1
Wintersemester 2004/2005
Seite 7
3. Hausaufgabe:
Gegeben sei das folgende Relationenstruktur:
Kunde (KNr, Name, Vorname, Strasse, PLZ, Ort)
Ferienhaus (HausIdent, Kategorie, Adresse, Betten, Preis)
mietet (MietvertragNr, KNr, HausIdent, vonKalWoche, bisKalWoche, Mietpreis)
Formulieren Sie folgende Abfragen in SQL:
1.
Wie viele Häuser gibt es in den einzelnen Kategorien.
2.
Ermitteln Sie den Kunden mit Namen und Adresse, der am häufigsten Ferienhäuser
gemietet hat.
3.
Ermitteln Sie, wie viel Mietertrag jedes Ferienhaus bisher erbracht hat, und geben Sie
dazu HausIdent und die Kategorie an.
4.
Welche Ferienhäuser waren im Jahr 2003 weniger als 8 Wochen vermietet.
5.
Welche Ferienhäuser mit mehr als 4 Betten waren in der den Kalenderwochen
2004/23 bis 2004/25 frei.
Schicken Sie Ihr Ergebnis per E-Mail an [email protected]
Abgabetermin: 22.11.2004
Hochschule Anhalt (FH)
Fachbereich Informatik
Prof. Dr.-Ing. Klöditz
Datenbanksysteme 1
Wintersemester 2004/2005
Seite 8
4. Übung
Ein Versandhaus für Bekleidung will seine Bestellungen mit einem Datenbanksystem
organisieren. Es besitzt Kunden. Diese können Bestellungen für Artikel vornehmen. Mit der
Lieferung wird eine Rechnung verschickt. Deren Bezahlung erfolgt auf ein für den Kunden
eingerichtetes Konto.
1.
Bestimmen Sie geeignete Entity-Typen!
2.
Legen Sie Beziehungen zwischen den Entity-Typen fest und bestimmen Sie die zur
Konsistenzsicherung notwendigen Abhängigkeiten.
3.
Stellen Sie das Ergebnis im Entity-Relationship-Diagramm dar.
4.
Beschreiben Sie die auszuführenden Geschäftsprozesse und die dabei entstehenden
Dokumente.
4. Hausaufgabe
Die Verwaltung der Kunden einer Bank und deren Konten soll mit einem Datenbanksystem
erfolgen. Folgende Funktionen sollen realisiert werden:
Ÿ
Einzahlung
Ÿ
Auszahlung
Ÿ
Überweisung
Ÿ
Kontoauszug
1.
Entwickeln Sie ein Entity-Relationship-Modell.
2.
Legen Sie die Datenbankstruktur fest.
3.
Entwerfen Sie Formulare für die angegebenen Funktionen.
4.
Stellen Sie die Verbindung zwischen der Datenbankstruktur und den Formularen her
(welche Daten kommen woher und gehen wohin).
Schicken Sie Ihr Ergebnis per E-Mail an [email protected]
Abgabetermin: 06.12.2004
Hochschule Anhalt (FH)
Fachbereich Informatik
Prof. Dr.-Ing. Klöditz
Datenbanksysteme 1
Wintersemester 2004/2005
Seite 9
5. Übung
In einem Kfz-Betrieb mit Werkstatt, Neu- und Gebrauchtwagenverkauf werden die
Geschäftsprozesse bisher mit einem Tabellen-Verwaltungsprogramm organisiert. Dazu
werden folgende Daten gemeinsam in ein und derselben Tabelle gespeichert:
• bei Werkstatt-Aufträgen:
Kfz-Typ, polizeiliches Kennzeichen, Fahrzeughalter (Name, Vorname, Adresse),
Reparaturdatum, Mechanikername, Name des verantwortlichen Schichtmeisters, RepArt
für die Art der Reparatur (Garantiedurchsicht 1/2/3, kleine/große Durchsicht, TÜV/ASU,
Reparatur von ..., Unfallschaden, ...), Preis, Zahlungsart bar/Scheck;
• bei Neuwagenkauf:
Kfz-Typ, Fahrzeug-Identnummer, Motorleistung, Ausstattung (Airbag, Servolenkung, ABS,
Klimaanlage, Zentralverriegelung, Radio / Kassettenrecorder, ...), Käufer (Name, Vorname,
Adresse), Verkaufsdatum, Verkäufer-Name, Verkaufspreis, Zahlungsart bar/Scheck/
Finanzierung/Leasing, Finanzierungskonditionen;
• bei Gebrauchtwagenkauf:
Kfz-Typ, Fahrzeug-Identnummer, Erstzulassungstermin, gefahrene Kilometer, Käufer
(Name, Vorname, Adresse), Verkaufsdatum, Verkäufer-Name, Verkaufspreis, Zahlungsart
bar/Scheck/Finanzierung/Leasing, Finanzierungskonditionen;
Die Tabelle hat folgenden Aufbau:
Kfz-Typ
poliz. Kennz. Name Halter
Vorname
Adresse
Rep.Datum
Mechaniker
Meister
RepArt
Preis
Zahlung
FahrzeugIdentNr.
Motorleistung
Name Käufer
Vorname
Adresse
Ausstattung
VerkDatum
Verkäufer
Preis
Erstzulassung
gefahrene
km
Zahlungsart Finanzierungskonditionen
Normalisieren Sie die vorliegende Tabelle zur 3. Normalform.
Hochschule Anhalt (FH)
Fachbereich Informatik
Prof. Dr.-Ing. Klöditz
Datenbanksysteme 1
Wintersemester 2004/2005
Seite 10
5. Hausaufgabe:
Normalisieren Sie die im folgenden beschriebene Tabelle, in der der Buchbestand einer
Bibliothek verwaltet wird, zur 3. Normalform.
Das Beispiel in der rechten Spalte gibt mögliche Eintragungen an.
Bibliothek
Inventar-Nr.
CS3456/1
Autoren
Andreas Heuer, Gunter Saake
Buchtitel
Datenbanken – Konzepte und Sprachen
Verlag
International Thomson Publications
ISBN-Nummer
3-8266-0349-4
Auflage, Jahr
1. korrig. Nachdruck 1997
Sachbezug
Relationale Datenbanksysteme, SQL,
Datenmodellierung, Entity-RelationshipModell
1.
Prüfen Sie für jede neu entstehende Tabelle, ob sie sich in der 3. Normalform befindet.
2.
Zeichnen Sie das Entity-Relationship-Diagramm der normalisierten DB-Struktur.
Schicken Sie Ihr Ergebnis per E-Mail an [email protected]
Abgabetermin: 20.12.2005
Hochschule Anhalt (FH)
Fachbereich Informatik
Prof. Dr.-Ing. Klöditz
Datenbanksysteme 1
Wintersemester 2004/2005
Seite 11
6. Übung
Gegeben sei das folgende Entity-Relationship-Diagramm (ERD):
hat
Kunde
# KNr
* Name
° Vorname
° GebDatum
* Straße
* PLZ
* Ort
gehört
Konto
# KontoNr
* Kontostand
macht
stammt von
ist enthalten in
Bestellung
# BNr
* BDatum
enthält
LMenge
LPreis
Artikel
# ANr
* ABezeichng
* Größe
* VerkPreis
1.
Diskutieren Sie die in diesem ERD darstellbaren Sachverhalte.
2.
Geben Sie die Entity-Typen und die Beziehungstypen an.
3.
Haben die Beziehungen Attribute?
4.
Beschreiben Sie die Tabellenstruktur im relationalen Datenmodell.
5.
Formulieren Sie die SQL-Anweisungen zur Definition der Datenbank.
Berücksichtigen Sie auch die notwendigen Einschränkungen.
6.
Gibt es andere Möglichkeiten zur Darstellung der gleichen Sachverhalte?
Hochschule Anhalt (FH)
Fachbereich Informatik
Prof. Dr.-Ing. Klöditz
Datenbanksysteme 1
Wintersemester 2004/2005
Seite 12
6. Hausaufgabe
Ein Krankenhaus will mit Hilfe eines Datenbank systems seine Patientenverwaltung
rationalisieren.
Es behandelt Patienten, die die Eigenschaften Name, Vorname, Geschlecht, Geburtsdatum,
PLZ, Wohnort und Straße besitzen. Sie werden stationär auf den Stationen für eine
bestimmte Zeit wegen bestimmter Erkrankungen aufgenommen.
Sie erhalten während dieses Aufenthaltes Leistungen (Laboruntersuchungen, Röntgen,
EKG, Medikamente, ...).
Die Stationen haben ein bestimmtes Profil von Erkrankungen (Innere, Kardiologie, Urologie,
...), einen Stationsarzt und einen Oberarzt sowie eine maximale Bettenkapazität.
1.
Zeichnen Sie das ER-Diagramm.
2.
Definieren Sie die Tabellen im relationalen Datenmodell.
3.
Formulieren Sie die SQL-Anweisungen für die Definition der Tabellen. Berücksichtigen
Sie auch notwendige Einschränkungen.
Schicken Sie Ihr Ergebnis per E-Mail an [email protected]
Abgabetermin: 24.01.2005
Hochschule Anhalt (FH)
Fachbereich Informatik
Prof. Dr.-Ing. Klöditz
Datenbanksysteme 1
Wintersemester 2004/2005
Seite 13
7. Übung:
Bei einem Händler für EDV-Gerätetechnik und -Verbrauchsmaterial erfolgt die
Lagerverwaltung durch ein Datenbanksystem. Es enthält folgende Relationen:
Lieferant (LNr, LName, Straße, PLZ, Ort, Telefon, Ansprechpartner)
Kunde (KNr, KName, Straße, PLZ, Ort, Telefon)
Artikel (ANr, ArtBezeichnung, Bestand, MinBest, MaxBest, AbgabePreis)
WarenEingang (WENr, WE_Datum, Menge, EinkaufsPreis, LNr, ANr)
WarenAusgang (WANr, WA_Datum, Menge, VerkaufsPreis, KNr, ANr)
Formulieren Sie für die in SQL angegebenen Anfragen an die Datenbank, mit welchen
Zugriffsoperationen der Relationen-Algebra diese ausgeführt werden können:
1.
Erzeugen Sie eine Liste aller Kunden aus dem Postleitzahlenbereich 1.
select KName, Straße, Ort
from Kunde
where PLZ like ´1%´;
2.
Geben Sie die Lieferanten an, die im Jahre 2003 den Händler beliefert haben.
select LName, Straße, PLZ, Ort
from Lieferant, WarenEingang
where WE_Datum like ´%03´
and Lieferant.LNr = WarenEingang.LNr;
3.
Geben Sie an, welche Artikel die einzelnen Lieferanten bisher geliefert haben.
select Lieferant.LNr, LName,
Artikel.ANr, ArtBezeichnung
from Lieferant, Artikel, WarenEingang
where Lieferant.LNr = WarenEingang.LNr
and Artikel.ANr = WarenEingang.ANr;
Hochschule Anhalt (FH)
Fachbereich Informatik
Prof. Dr.-Ing. Klöditz
Datenbanksysteme 1
Wintersemester 2004/2005
Seite 14
7. Hausaufgabe:
Gegeben sei folgendes ER-Modell
Telefon
# TelNr
° verbrEinheiten
gehört
seit
hat
Kunde
# KNr
* Name
° Str
° PLZ
° Ort
mit der Relationenstruktur:
Telefon (TelNr, verbrEinh, KNr, seit)
Kunde (KNr, Name, Straße, PLZ, Ort)
Geben Sie für folgende Anfragen in SQL die Operationen der Relationen-Algebra an, mit
denen die Abfragen realisiert werden können:
1.
Es ist eine Liste zu erzeugen, die für alle Rufnummern die verbrauchten Einheiten
enthält.
select TelNr, Name, verbrEinheiten
from Telefon T, Kunde K
where K.KNr=T.KNr;
2.
Erzeugen Sie eine Liste aller Rufnummern, die nach dem 01.01.2003 angeschlossen
wurden, mit dem Namen und der Adresse des jeweiligen Kunden.
select TelNr, seit, Name, Str, PLZ, Ort
from Telefon T, Kunde K
where K.KNr=T.KNr
and seit >= ´01-jan-00´;
Schicken Sie Ihr Ergebnis per E-Mail an [email protected]
Abgabetermin: 31.01.2005
Hochschule Anhalt (FH)
Fachbereich Informatik
Prof. Dr.-Ing. Klöditz
Datenbanksysteme 1
Wintersemester 2004/2005
Seite 15
1.- 3. Praktikum
Gegeben ist eine Datenbank mit den Tabellen LESER, BUCHHANDEL und KATALOG.
Benutzen Sie zur Protokollierung der Sitzung das spool-Kommando von SQL*Plus.
1.
Melden Sie sich beim Datenbanksystem Oracle (SQL*Plus) mit Ihrem Namen und
dem gleichlautenden Password an. Ändern Sie Ihr Password mit der SQL-Anweisung
alter user name identfied by new_password;
2.
Richten Sie für die Protokollierung Ihrer Sitzung eine Protokolldatei ein:
spool dateispezifikation
3.
Ermitteln Sie die Struktur der Tabellen der Bibliotheksdatenbank mit
describe leser
describe buchhandel
describe katalog
Gleiche Spaltenbezeichnungen in unterschiedlichen Tabellen signalisieren gleiche
Bedeutung (Fremdschlüssel).
4.
Beschreiben Sie die durch die Tabellen widergespiegelten Sachverhalte mit Ihren
eigenen Worten. Für welche Geschäftsprozesse können die Tabellen wie benutzt
werden?
Ermitteln sie mit Hilfe geeigneter SQL-Anweisungen:
5.
Wie viele Leser sind eingetragen?
6.
Wie viele Leser sind aus Halle, aus Dessau und aus Wittenberg?
7.
Aus wie vielen verschiedenen Orten sind die Leser?
8.
Ermitteln Sie die Namen, Vornamen und Orte derjenigen Leser, deren Name mit L
beginnt.
9.
Ermitteln Sie alle Leser mit Namen, Vornamen und Ort, deren Vorname Jürgen lautet,
nach dem Namen sortiert.
10.
Ermitteln Sie die kleinste und die größte Lesernummer.
11.
Wie viele Einträge enthält die Tabelle BUCHHANDEL.
12.
Geben Sie Autor, Titel und Preis aus der Tabelle BUCHHANDEL aus, nach dem Preis
sortiert und innerhalb gleicher Preise nach dem Autor sortiert.
13.
Wie lautet der Titel des Buches mit der ISBN 3-540-65429-1?
14.
Wie viele Bücher besitzt die Bibliothek (gemäß Tabelle KATALOG)?
15.
Wie viele Bücher sind nicht ausgeliehen?
16.
Welches Buch (BNr, ExNr) hätte die längste Zeit schon zurückgegeben werden
müssen?
17.
Von welchem Buch sind die meisten Exemplare im Katalog vorhanden?
Hochschule Anhalt (FH)
Fachbereich Informatik
Prof. Dr.-Ing. Klöditz
Datenbanksysteme 1
Wintersemester 2004/2005
Seite 16
18.
Welches Buch (Autor, Titel, RDatum) hätte die längste Zeit schon zurückgegeben
werden müssen?
19.
Welches Buch (Titel, Name, Vorname, RDatum) hätte die längste Zeit schon von wem
zurückgegeben werden müssen?
20.
Wer (Name, Vorname, Ort) hat das Buch Graph Theory and Algorithms
ausgeliehen und wann soll es zurückgegeben werden?
21.
Welcher Leser (Name, Vorname, Ort) hat die meisten Bücher ausgeliehen?
22.
Geben Sie eine Liste (Autor, Titel) der ausgeliehenen Bücher aus, nach Lesern (Name,
Vorname, Ort) sortiert.
23.
Welche Leser (Name, Vorname, Ort) haben keine Bücher ausgeliehen?
24.
Welche Titel aus der Tabelle BUCHHANDEL (ISBN, Autor, Titel) sind in der Bibliothek
(Tabelle KATALOG) nicht vorhanden?
25.
Aus welchem Ort stammen die meisten Leser?
26.
In welchen Ort sind die meisten Bücher ausgeliehen?
27.
Von welchem Buch besitzt die Bibliothek die meisten Exemplare?
28.
Von welchem Buch sind in der Bibliothek zur Zeit die meisten Exemplare nicht
ausgeliehen?
29.
Von welchem Buch sind alle Exemplare ausgeliehen?
30.
Bei welchen Büchern sind die Exemplare nicht fortlaufend numeriert?
Denken Sie sich weitere Fragen an die Datenbank aus und formulieren Sie sie in SQL.
Hochschule Anhalt (FH)
Fachbereich Informatik
Prof. Dr.-Ing. Klöditz
Datenbanksysteme 1
Wintersemester 2004/2005
Seite 17
4.-6. Praktikum
Entwerfen Sie eine Datenbank für folgende Aufgabenstellung:
Ein Heimwerker-Ausleihdienst hält verschiedene Gerätearten (Bohrmaschinen,
Bohrhämmer, Rasenmäher, Heckenscheren, Schubkarren, Tapeziertische, Leitern, ...)
vor, die von Kunden ausgeliehen werden können. Innerhalb der einzelnen Gerätearten
gibt es verschiedene Gerätetypen (unterschiedliche Größe, Leistung, Hersteller, ...).
Von den verschiedenen Gerätetypen hat der Aus leihdienst mindestens je ein
Exemplar, meist jedoch mehrere.
Kunden können ein oder mehrere Geräte einmalig oder beliebig oft ausleihen. Die
Ausleihe ist befristet. Für die Ausleihe muss der Kunde bezahlen.
Um kontrollieren zu können, wer ein bestimmtes Gerät wann ausgeliehen hatte, sollen
die Ausleihdaten gespeichert werden.
1.
Zeichnen Sie das ER-Diagramm.
2.
Geben Sie die Tabellenstruktur an.
3.
Formulieren Sie die Einschränkungen.
4.
Definieren Sie die Datenbank mit Hilfe von SQL.
5.
Füllen Sie die Datenbanktabellen mit repräsentativen Datensätzen, so dass die Fragen
in Aufgabe 5. auch entsprechende Ergebnisse liefern.
6.
Formulieren Sie Abfragen an die Datenbank wie z.B.:
Ÿ Wie viele Ausleihvorgänge haben in einem Jahr stattgefunden?
Ÿ Wie viele Geräte sind gerade ausgeliehen?
Ÿ Wer hat zur Zeit ein bestimmtes Gerät?
Ÿ Ist von einem bestimmten Gerätetyp zur Zeit eines nicht ausgeliehen?
Ÿ Wer hatte jemals ein bestimmtes Gerät?
Ÿ Wie oft ist ein bestimmtes Gerät ausgeliehen worden?
Ÿ Wie lange war ein bestimmtes Gerät insgesamt ausgeliehen?
Ÿ Welcher Kunde hat bisher bzw. im vergangenen Jahr den meisten Umsatz
gebracht?
Ÿ Welcher Gerätetyp wird am häufigsten ausgeliehen?
Ÿ ...