Lösung zum Drucken

Prof. Dr. Dr. h.c. mult. A.-W. Scheer
Institut für Wirtschaftsinformatik (IWi)
im Deutschen Forschungszentrum für künstliche Intelligenz (DFKI)
Sommersemester 2005
Wirtschaftsinformatik II
Dipl.-Kfm. Björn Simon
Dipl.-Kfm. Dominik Vanderhaeghen
Dipl.-Wirt.Inform. Jörg Ziemann
([email protected])
Sprechstunde: nach Absprache, Geb. 43.8, Raum 0.16 und 0.26
Lösungsblatt 4
Aufgabe 7 : ERM
Bauer Kahl möchte für seinen Betrieb auch ein Datenbanksystem einführen, mit dem er alle
relevanten Informationen verwalten kann. Die folgenden Angaben stellen einen Auszug aus
der Ist-Erhebung der Datenstruktur dar:
Jede Kuh muss eindeutig identifizierbar sein. Dies ist auch wichtig, um die Herkunft
bestimmen zu können. Bei selbst gezüchteten Tieren wird zu jedem Tier die Mutterkuh
festgehalten. Bei gekauften Tieren wird der Herkunftshof notiert. Daneben ist jeder Kuh ein
Stall und eine Weide zugeordnet. Während die Zuordnung einer Kuh zu einem Stall eher
selten geändert wird, kann die Zuordnung zu einer Weide jährlich mehrfach wechseln.
Ebenfalls wird für jedes Tier das Futter notiert, das es erhält. Eine Kuh kann durchaus
mehrere verschiedene Futter erhalten. Das Futter wird auch noch nach selbst hergestelltem
Futter und zugekauftem Futter unterschieden. Das zugekaufte Futter kann pro Futtertyp
mehrere Lieferanten haben, von denen der jeweils günstigste ausgewählt wird. Gleichzeitig
kann ein Lieferant auch mehrere Futter in seinem Sortiment führen. Dem selbst hergestellten
Futter ist ein Feld zugeordnet, von wo es gewonnen wurde. Um einen Überblick über alle
Felder und Weiden zu behalten, werden diese als Gelände verwaltet.
Modellieren Sie basierend auf diesen Angaben ein ERM. Berücksichtigen Sie auch die
Kardinalitäten in min,max-Notation und geben Sie die Schlüsselattribute an. Den Entitytypen
ist darüber hinaus mindestens ein weiteres sinnvolles Attribut zuzuordnen! (Primär- und
Fremdschlüssel sind entsprechend zu kennzeichnen!)
Allgemeines zur Datensicht :
Fachkonzeptebene : Modellierung mit ERM
KUNDE
KNR, NAME, ADRESSE
DV-Konzept-Ebene : Relationenmodell
Kundennummer
001
002
003
Name
Mayer
Schmitt
Müller
Implementierungsebene : SQL (Structured Query Language)
CREATE TABLE Kunde
(Kundennummer COUNTER NOT NULL,
Name VARCHAR (20) NOT NULL,
Adresse VARCHAR (100) NOT NULL,
PRIMARY KEY (Kundennummer));
Adresse
Schulstr. 3, 12345 SB
Kirchweg 7, 21324 HH
Stadtallee 9, 43242 HB
Lösung zu Aufgabe 7 :
Aufgabe 8: Relationenmodell, Normalisierung
Neben den Tieren soll die Datenbank von Bauer Kahl auch die Maschinen seines Hofes
verwalten. Das folgende ERM zeigt den Ausschnitt des Datenmodells, der die Verwaltung der
Traktoren und des passenden Zubehörs wie Pflug oder Saatmaschine beschreibt:
TNr., Modell, Baujahr, PS
0, n
Traktor
TNr., ZNr., Kompatibilität
verwendet
1, n
ZNR., Bezeichnung
Zubehör
1, 1
1, 1
Z.-H.Zuord
T.- H.Zuord.
0, n
0, n
Hersteller
1, 1
H.-O.Zuord.
HNr., Firma, Anschrift
Ort
0, n
PLZ, Ort
a) Erstellen Sie das zugehörige Relationenmodell!
Lösung zu 8a :
R. Traktor (TNr, Modell, BJ, PS, HNr)
R. Zubehör (ZNr, Bez, HNr)
R. Hersteller (HNr, Firma, Anschrift, PLZ)
R. Ort (PLZ, Ort)
R. Verwendet (TNr, ZNr, Kompatibilität)
Anmerkungen :
Primärschlüsselattribute : unterstrichen
Fremdschlüsselattribute : kursiv
b) Nehmen Sie eine Relation „Traktordatenbank“ an, die wie folgt implementiert wurde:
TNr. Model Baujahr PS
l
1
4711 1978
150
2
8911
1992
220
ZNr. Bezeichnung
Kombatibil
ität
Saatmaschine
Direkt
Pflug „Schafttnix“
Adapter
Düngemaschine
Direkt
Pflug „Butterweich“ Direkt
HNr. Firma
Anschrift
PLZ
Ort
1
2
3
4
1
2
1
1
Hussarenweg 17
Rüsselweg 12
Hussarenweg 17
Hussarenweg 17
11235
47986
11235
11235
Bauernglück
Ferkelbrunn
Bauernglück
Bauernglück
Ackermann
Steiggut
Ackermann
Ackermann
Überprüfen Sie, ob die Relation in der 3. Normalform vorliegt. Sollte dies nicht der Fall sein
überführen Sie die Tabelle in die 3. Normalform!
Vorbemerkungen zu Datenbankdesign und Normalisierung :
Bei ungünstigem Design der Datentabellen können Anomalien auftreten :
- Einfügeanomalie
- Löschanomalie
- Updateanomalie
Relationen sollen so entworfen werden, dass :
- keine untergeordneten Relationen enthalten sind
- das Datenmodell möglichst redundanzfrei ist
- keine Inkonsistenzen auftreten
- die Datenpflege problemlos zu bewerkstelligen ist
Æ Analyse und Modifikation eines initialen Relationenmodells durch Normalisierung
1. Normalform : Eine Relation R ist dann in der ersten Normalform (1. NF), wenn jeder
Attributwert elementar ist. Die 1.NF wird durch Elimination von Wiederholungsgruppen
erreicht Æ alle Attribute sind funktional abhängig.
2. Normalform : Eine Relation R ist dann in der zweiten Normalform (2. NF), wenn sie die 1.
NF erfüllt und jedes Nichtschlüsselattribut von jedem Schlüsselattribut voll funktional
abhängig ist. Wenn eine Relation 1. NF ist und nur einen Schlüssel hat, ist sie automatisch
auch 2. NF.
3. Normalform : Eine Relation R ist in der dritten Normalform (3. NF), wenn sie die 2. NF
erfüllt und kein Nichtschlüsselattribut transitiv von einem Schlüsselattribut abhängig ist.
Wenn eine Relation 2. NF ist und nur ein Nichtschlüsselattribut besitzt, ist sie automatisch in
der 3. NF.
Vorteile der Normalisierung :
- Redundanzfreiheit : Jedes Faktum ist nur einmal in der Datenbank gespeichert
- Integritätssicherung: Widerspruchsfreie Datenhaltung bei Vorliegen der 3. NF
Nachteile der Normalisierung :
- Normalisierung kann aufwändig werden
- Laufzeitverhalten (Zusammenfügen von vielen Relationen ist „teuer“)
Æ Normalisierungsentscheidungen müssen je nach Anwendungsfall getroffen werden
Integritätsbedingungen sorgen dafür, dass die Datenbank stets ein korrektes Abbild der
Wirklichkeit darstellt :
- IBs beziehen sich auf die Sicherung des semantischen Gehalts eines Datenmodells.
- IBs können in der Anwendungslogik oder in der Datenbank definiert werden.
Integritätsbedingungen :
Lösung zu 8b :
1. NF :
R.1 (TNr, Modell, BJ, PS)
R.2 (ZNr, Bez, Komp, HNr, Firma, Anschrift, PLZ, Ort)
R.3 (TNr, ZNr)
2. NF :
R.1 (TNr, Modell, BJ, PS)
R.2 (ZNr, Bez, HNr)
R.3 (TNr, ZNr, Komp)
R.4 (HNr, Firma, Anschrift, PLZ, Ort)
3.NF :
R.1 (TNr, Modell, BJ, PS)
R.2 (ZNr, Bez, HNr)
R.3 (TNr, ZNr, Komp)
Transitive Abhängigkeit : Ort
R.4 (HNr, Firma, Anschrift, PLZ)
R.5 (PLZ, Ort)
Aufgabe 9: SQL
a) Legen Sie die 5 im Relationenmodell definierten Tabellen aus Aufgabe 8 a) mittels
SQL an!
b) Fügen Sie in die Tabelle Hersteller die Spalte „Test“ ein.
c) Entfernen Sie aus der Tabelle Hersteller die Spalte „Test“ wieder!
d) Fügen Sie mittels SQL die folgenden Daten in die entsprechenden Tabellen ein:
i .Traktor 8210, Baujahr 1995, 200 PS, Hersteller John Deere, John-Deere-Str. 8,
76646 Bruchsal,
ii. Traktor 1532, Baujahr 2004, 300 PS, Hersteller MAN, Schulzenstraße 82, 66758
Glücksdorf,
iii. Traktor 7895, Baujahr 1999, 150 PS, Hersteller Peugeot, Motorring 11, 84613
Sulzheim.
e) Ändern Sie die PS des Modell „7895“ von 150 PS auf 156 PS!
f) Formulieren Sie eine SQL-Abfrage, welche Modell, Baujahr und PS aller vor 2000
gebauten Traktoren anzeigt, absteigend sortiert nach der PS-Zahl!
g) Formulieren Sie eine SQL-Abfrage, deren Ergebnis das Modell, den Herstellernamen
und den Hersteller-Ort des Traktors mit der TNr. 2 enthält!
h) Erstellen Sie eine Abfrage, die neben dem Traktormodell, Baujahr und der PS-Zahl
auch die Leistung in kW anzeigt! (1PS = 0,735 kW)
i) Ermitteln Sie die durchschnittliche Leistung der Traktoren in PS!
j) Der Traktor 1532 fällt nach nur einem Jahr mit einem Totalschaden aus. Entfernen
Sie den Traktor, den Hersteller und alle betroffenen Datensätze aus der Datenbank!
k) Löschen Sie die Tabellen der Datenbank!
Allgemeines zu SQL :
Es gibt verschiedene SQL (Structured/Standard Query Language) Dialekte :
DDL (Data Description Language)
Sprache zur Beschreibung einer Datenbank (z.B. Anlegen von Tabellen)
DML (Data Manipulation Language)
Sprache, die das Arbeiten mit einer DB unterstützt (z.B. Veränderungen von Daten, Hinzufügen,
Löschen)
QL (Query Language)
Sprache, um Daten aufzufinden und selektiv darzustellen (z.B. Selektionen wie z.B. Kunden mit
Umsatz > 10.000 Euro auflisten)
DDL-Datentypen :
VARCHAR (Größe) = Zeichenkette. (z.B. VARCHAR (10): „ab!“ => 3 Zeichen Speicherplatz)
CHAR (Größe) = feste Zeichenkette. (z.B. CHAR (10): „ab!“ => 10 Zeichen Speicherplatz)
INT integer = ganzzahliger Wert (z.Bsp. „8“)
FLOAT = Gleitkommazahl
COUNTER = Inkrementwert, Zähler (Wert wird hochgezählt)
DATETIME = Datum
BLOB = Binary Large Object (für Binärdaten)
Lösung zu Aufgabe 9 :
9a) Allgemein :
CREATE TABLE Tabellenname
(Feld1 DATENTYP (Größe) NOT NULL,
Feld2 DATENTYP (Größe) NOT NULL
[…] PRIMARY KEY (Schlüsselattribute)]);
Tabelle “Traktor” :
Tabelle “Hersteller” :
CREATE TABLE Traktor
(TNr COUNTER NOT NULL,
Modell VARCHAR(20) NOT NULL,
Baujahr INT NOT NULL,
HNr INT NOT NULL
PRIMARY KEY (TNr));
CREATE TABLE Hersteller
(HNr COUNTER NOT NULL,
Firma VARCHAR(50),
Anschrift VARCHAR(100),
PLZ INT
PRIMARY KEY (HNr),
FOREIGN KEY (PLZ) REFERENCES ORT (PLZ));
Tabelle “Ort” :
Tabelle “Zubehör” :
CREATE TABLE Ort
(PLZ INT(5) NOT NULL,
Ort VARCHAR(40) NOT NULL
PRIMARY KEY(PLZ));
CREATE TABLE Zubehoer
(ZNr COUNTER,
Bezeichnung VARCHAR(100) NOT NULL,
HNr INT NOT NULL
PRIMARY KEY (ZNr));
Tabelle “kompatibel” :
CREATE TABLE kompatibel
(TNr INT NOT NULL,
ZNr INT NOT NULL
PRIMARY KEY (TNr, ZNr));
9b) ALTER TABLE Hersteller ADD Test INT;
9c) ALTER TABLE Hersteller DROP Test;
9d) Allgemein :
INSERT INTO Tabellenname (Spaltenname1, Spaltenname2) VALUES (Wert1, Wert2);
# (i.)
INSERT INTO Ort (PLZ, Ort) VALUES (76646, 'Bruchsal');
INSERT INTO Hersteller (Firma, Anschrift, PLZ) VALUES ('John Deere', 'John-Deere-Str', 76646);
INSERT INTO Traktor (Modell, Baujahr, PS, HNr) VALUES (‘8210’, 1995, 200, 1);
# (ii.)
INSERT INTO Ort (PLZ, Ort) VALUES (66758, 'Glücksdorf');
INSERT INTO Hersteller (Firma, Anschrift, PLZ) VALUES ('MAN', 'Schulzenstraße 82', 66758);
INSERT INTO Traktor (Modell, Baujahr, PS, HNr) VALUES (‘1532’, 2004, 300, 2);
# (iii.)
INSERT INTO Ort (PLZ, Ort) VALUES (84613, 'Sulzheim');
INSERT INTO Hersteller (Firma, Anschrift, PLZ) VALUES ('Peugeot', 'Motorring 11', 84613);
INSERT INTO Traktor (Modell, Baujahr, PS, HNr) VALUES (‘7895’, 1999, 150, 3);
Anmerkungen :
- Bei Datentyp VARCHAR Hochkommata setzten, Bsp. : 'Bruchsal'.
- COUNTER braucht nicht eingefügt zu werden, wird automatisch hochgezählt.
9e) UPDATE Traktor SET PS=156 WHERE Modell=’7895’;
9f) SELECT Modell, Baujahr, PS FROM Traktor WHERE Baujahr < 2000 ORDER BY PS DESC;
Anmerkungen :
- desc = descending
- es gibt auch “asc” = ascending
9g) SELECT Modell, Firma, Ort FROM Traktor, Hersteller, Ort
WHERE Traktor.HNR = Hersteller.HNR
AND Hersteller.PLZ = Ort.PLZ
AND Traktor.TNR = 2;
9h) SELECT Modell, Baujahr, PS, (PS * 0.735) AS Leistung FROM Traktor;
9i) SELECT AVG(PS) AS Leistung FROM Traktor;
9j) DELETE FROM Ort WHERE PLZ = 84613;
DELETE FROM Hersteller WHERE HNr = 2;
DELETE FROM Traktor WHERE Modell = 1532;
Anmerkungen : Der gesamte Datensatz muss gelöscht werden !
9k) DROP TABLE kompatibel;
DROP TABLE Traktor;
DROP TABLE Zubehoer;
DROP TABLE Hersteller;
DROP TABLE Ort;