12.12.01MySQL bung*OnLine Datenbanken

MySQL bung Datenbank fr SkriptenverkaufBeim AStA

MySQL bung

12.12.01MySQL bung*OnLine Datenbanken Fr die Durchfhrung der bung wird xampplite eingesetzt. Es beinhaltet folgende Programme:Apache (Web Server)MySQL (Datenbank Server)phpMyAdmin (Werkzeug zum Erstellen und bearbeiten von Datenbanken)Sie knnen diese Software downloaden von:http://www.apachefriends.org

MySQL bung

12.12.01MySQL bung*Aufagabenbeschreibung Der Verkauf von Skripten beim AStA soll mit Hilfe einer Datenbank erfasst werden. Folgende Vorgnge mssen in der Datenbank abgebildet werden:Erstmalige Lieferung von SkriptenVerkauf von SkriptenNachlieferung von Skripten

MySQL bung

12.12.01MySQL bung*Bentigte Tabellen

skripte: enthlt Informationen ber die Skripte (Autor, Name, Preis, Anzahl, Restbestand)lieferung: enthlt Informationen ber die Lieferung von Skriptenverkauf: enthlt Informationen ber den Verkauf von Skripten

MySQL bung

12.12.01MySQL bung*Tabelle skripte Struktur der Tabelle "skripte"

MySQL bung

12.12.01MySQL bung*Hinweise zur Tabelle SkripteDas Feld "id" ist der Primrschlssel in dieser Tabelle. Das Attribut "autoincrement" bedeutet, dass es von MySQL automatisch weiter gezhlt wird bei jedem neuen DatensatzDas Feld "name" hat das Attribut "Unique". Es darf nur einmal in der Tabelle vorkommenDas Feld "autor" ist ein index-Feld. Das beschleunigt die Suche nach Autoren

MySQL bung

12.12.01MySQL bung*Hinweise zur Tabelle Skripte Aus der Sicht einer optimalen Auslegung der Datenbank (Normalisierung) msste eine extra Tabelle "Autoren" angelegt werden. Aus Grnden der Vereinfachung wird jedoch darauf verzichtet.

MySQL bung

12.12.01MySQL bung*Tabelle Skripte erstellen

MySQL bung

12.12.01MySQL bung*Tabelle Skripte erstellenSQL-Befehl zur Erstellung der Tabelle Skripte:CREATE TABLE skripte ( id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY , name VARCHAR( 255 ) NOT NULL , autor VARCHAR( 100 ) NOT NULL , preis DECIMAL( 5, 2 ) NOT NULL , beschreibung TEXT NOT NULL , restbestand SMALLINT NOT NULL , INDEX (autor) , UNIQUE (name)) ENGINE = MYISAM CHARACTER SET latin1 COLLATE latin1_german2_ci;

MySQL bung

12.12.01MySQL bung*Tabelle Lieferung Struktur der Tabelle "lieferung"

MySQL bung

12.12.01MySQL bung*Hinweis zur Tabelle Lieferung Das Feld "skript_id" ist ein Sekundrschlssel und bildet die Verknpfung zur Tabelle "skripte"

MySQL bung

Tabelle Lieferung erstellen

12.12.01MySQL bung*

MySQL bung

12.12.01MySQL bung*Tabelle LieferungSQL-Befehl: CREATE TABLE lieferung ( skript_id SMALLINT UNSIGNED NOT NULL , datum DATE NOT NULL DEFAULT '0000-00-00', anzahl SMALLINT UNSIGNED NOT NULL , preis DECIMAL( 5, 2 ) UNSIGNED ZEROFILL NOT NULL ) ENGINE = MYISAM ;

MySQL bung

12.12.01MySQL bung*Tabelle Verkauf

Struktur der Tabelle "verkauf"

MySQL bung

12.12.01MySQL bung*Tabelle verkauf erstellen

MySQL bung

12.12.01MySQL bung*Tabelle Verkauf erstellenSQL-Befehl zur Erstellung der Tabelle Verkauf

CREATE TABLE verkauf (skript_id SMALLINT UNSIGNED NOT NULL , datum DATE NOT NULL DEFAULT '0000-00-00', anzahl SMALLINT UNSIGNED NOT NULL , erloes DECIMAL( 7, 2 ) UNSIGNED ZEROFILL NOT NULL ) ENGINE = MYISAM ;

MySQL bung

Beziehung zwischen Tabellen erstellenWhlen Sie In phpMyAdmin die Registerkarte Design. verknpfen Sie das Feld id in der Tabelle Skripte mit dem Feld skript_id in der Tabelle "lieferung". verknpfen Sie das Feld id in der Tabelle Skripte mit dem Feld skript_id in der Tabelle "verkauf".

s. Abbildung auf der nchsten Folie12.12.01MySQL bung*

MySQL bung

Beziehung zwischen Tabellen erstellen12.12.01MySQL bung*

MySQL bung

12.12.01MySQL bung*Lieferung von SkriptenMan muss 2 Vorgnge unterscheidenErstmalige Lieferung: Je ein Datensatz wird in die Tabellen lieferung und skripte eingefgt.Nachlieferung von vorhandenen Skripten: Ein Datensatz wird in die Tabelle lieferung eingefgt. In der Tabelle skripte wird nur der restbestand und evtl. der Preis gendert

MySQL bung

12.12.01MySQL bung*Verkauf von SkriptenFolgende Aktionen werden durchgefhrt:Ein neuer Datensatz wird in die Tabelle verkauf eingefgt.In der Tabelle skripte wird der Restbestand um die Anzahl der verkauften Skripte vermindert

MySQL bung

12.12.01MySQL bung*Datensatz in Tabelle Skripte einfgen

INSERT INTO skripte (name , autor , preis , beschreibung , restbestand ) VALUES ('Einfhrung in die Agrarinformatik', 'Doluschitz, R.', '15.00', 'Daten- und Informationsbereitstellung sowie deren Verarbeitung und Nutzung auf unterschiedlichen Einsatzebenen in der Agrarwirtschaft.', 100)

MySQL bung

12.12.01MySQL bung*Datensatz in Tabelle Skripte einfgen

INSERT INTO skripte (name, autor, preis, beschreibung, restbestand) VALUES ('Einfhrung in das Agrarrecht', 'Turner, G.; Werner, K.', '12.00', 'Besondere Kapitel des Agrarrechts: Landwirtschaftliches Eigentum, Erbrecht mit Landw. Sondererbrecht, Gebrauchsberlassungsvertrge, Sicherung von Krediten Landwirtschaftliches Arbeits-, Sozial- und Steuerrecht, Agrarrecht in Europa.', 150)

MySQL bung

12.12.01MySQL bung*Datensatz in Tabelle Skripte einfgen

INSERT INTO skripte (name, autor, preis, beschreibung, restbestand) VALUES ('Einfhrung in Javascript', 'RRZN', '6.00', 'Programmieren von dynamischen HTML-Seiten mit JavaScript.', 100)

MySQL bung

12.12.01MySQL bung*Datensatz in Tabelle Skripte einfgen

INSERT INTO skripte (name, autor, preis, beschreibung, restbestand) VALUES ('Einfhrung in SPSS', 'RRZN', '7.50', 'Das Statistikpaket SPSS fr Anfnger.', 80)

MySQL bung

12.12.01MySQL bung*Datensatz in Tabelle Skripte einfgen

INSERT INTO skripte( name, autor, preis, beschreibung, restbestand ) VALUES ('Word 2002 fr Fortgeschrittene', 'RRZN', '6.00', 'Dokument- und Formatvorlagen, Verzeichnisse, Grafiken einfgen und bearbeiten,..', 100)

MySQL bung

12.12.01MySQL bung*Datenstze in Tabelle skripte

MySQL bung

12.12.01MySQL bung*Datenstze in Tabelle lieferung einfgenINSERT INTO lieferung (skript_id, datum, anzahl, preis) VALUES ('1', '2006-06-12', 100, '15'),('2', '2006-06-12', 150, '12'),('3', '2006-06-12', 100, '6'),('4', '2006-06-12', 80, '7.50'),('5', '2006-06-12', 100, '6')

MySQL bung

Tabelle lieferung12.12.01MySQL bung*

MySQL bung

12.12.01MySQL bung*Datensatz in Tabelle verkauf einfgen

INSERT INTO verkauf (skript_id, datum, anzahl, erloes) VALUES ('2', '2006-06-13', '10', '120'); UPDATE skripte SET restbestand = restbestand - 10 where id = 2

MySQL bung

12.12.01MySQL bung*Datensatz in Tabelle verkauf einfgen

INSERT INTO verkauf (skript_id, datum, anzahl, erloes) VALUES ('1', '2006-06-13', '4', '60'); UPDATE skripte SET restbestand = restbestand - 4 where id = 1

MySQL bung

12.12.01MySQL bung*Datensatz in Tabelle verkauf einfgen

INSERT INTO verkauf (skript_id, datum, anzahl, erloes) VALUES ('3', '2005-12-13', '20', '120'); UPDATE skripte SET restbestand = restbestand - 20 where id = 3

MySQL bung

12.12.01MySQL bung*Datensatz in Tabelle verkauf einfgen

INSERT INTO verkauf (skript_id, datum, anzahl, erloes) VALUES ('3', '2006-12-13', '10', '60'); UPDATE skripte SET restbestand = restbestand - 10 where id = 3

MySQL bung

12.12.01MySQL bung*Auswahl von Datenstzen

Beispiele fr die SELECT-Anweisung:Ausgabe aller Skripte:SELECT *FROM skripteORDER BY name

MySQL bung

12.12.01MySQL bung*Auswahl von DatenstzenAusgabe von Verkaufszahlen und Erls: (Skript-ID, Name, Anzahl und Erloes)SELECT skripte.id, skripte.name, verkauf.anzahl,verkauf.erloesFROM skripte, verkauf WHERE skripte.id = verkauf.skript_idORDER BY skripte.name

MySQL bung

12.12.01MySQL bung*Auswahl von DatenstzenAlternative Formulierung:(Verknpfung der Tabellen mit JOIN)SELECT skripte.id, skripte.name, verkauf.anzahl,verkauf.erloesFROM skripte JOIN verkauf WHERE skripte.id = verkauf.skript_idORDER BY skripte.id

MySQL bung

12.12.01MySQL bung*Auswahl von DatenstzenWeitere Alternative Formulierung:SELECT s.id, s.name, v.anzahl, v.erloesFROM skripte as s, verkauf as vWHERE s.id = v.skript_idORDER BY s.id Anmerkung: in diesem Beispiel werden Aliase (Krzel)fr die Tabellennamen benutzt

MySQL bung

Ergebnis der Abfrage

Ausgabe von Verkaufszahlen und Erls:12.12.01MySQL bung*

MySQL bung

12.12.01MySQL bung*Auswahl von DatenstzenAusgabe der verkauften Skripte in 2005select s.id, s.name, s.autor, v.anzahl,v.datum, v.erloes from skripte as s, verkauf as vwhere s.id = v.skript_id AND v.datum LIKE '2005%'ORDER BY s.idErgebnis der Abfrage:

MySQL bung

12.12.01MySQL bung*Auswahl von DatenstzenAusgabe der Summe von Verkaufszahlen und Erls: SELECT s.id, s.name, SUM(v.anzahl), SUM(v.erloes) FROM skripte AS s, verkauf AS v WHERE s.id = v.skript_id GROUP BY s.id

MySQL bung

12.12.01MySQL bung*Auswahl von Datenstzen Ergebnis der Abfrage: Verkaufszahlen und Erls werden pro Skript zusammengefasst

MySQL bung

12.12.01MySQL bung*Auswahl von Datenstzenbersicht ber verkaufte Skripte im Jahr2006: SELECT s.id, s.name, s.autor, v.datum, SUM( v.anzahl ) , SUM( v.erloes ) FROM skripte AS s, verkauf AS v WHERE s.id = v.skript_id AND v.datum LIKE '2006%' GROUP BY s.id

MySQL bung

12.12.01MySQL bung*Auswahl von Datenstzen

Ergebnis der Abfrage:.

MySQL bung

12.12.01MySQL bung*Auswahl von DatenstzenVerknpfung der Tabellen mit"LEFT OUTER JOIN":SELECT s.id, s.name, v.anzahl, v.erloes FROM skripte as s LEFT OUTER JOIN verkauf as vON s.id = v.skript_idORDER BY s.id

MySQL bung

12.12.01MySQL bung*Ergebnis der AbfrageHinweis: Ergebnis der Abfrage enthlt auch SkripteFr die es keinen Eintrag in derTabelle verkauf gibt (s. Zeilen 4+5)

MySQL bung

12.12.01MySQL bung*Auswahl von Datenstzen Ausgabe von Skripten, fr die kein Eintrag in der Verkaufstabelle existiert: SELECT s.id, s.name, s.autor, v.skript_id FROM skripte as s LEFT OUTER JOIN verkauf as vON s.id = v.skript_id WHERE v.skript_id is NULLORDER BY s.id

MySQL bung

12.12.01MySQL bung*Ergebnis der Abfrage Ergebnis enthlt nur Datenstze von nicht verkauften Skripten

MySQL bung

12.12.01MySQL bung*Auswahl von Datenstzen

Ausgabe von Verkaufszahlen:SELECT s.id, s.name, v.skript_id, v.anzahl, v.erloes FROM verkauf as v LEFT OUTER JOIN skripte as sON s.id = v.skript_idORDER BY s.id

MySQL bung

12.12.01MySQL bung*Ergebnis der Abfrage Ergebnis der Abfrage enthlt auch Eintrge in der Tabelle verkauf Fr die es keinen passenden Eintrag in der Tabelle skripte gibt Konsistenzprobleme in der DB. Evtl. Fehler bei der Eingabe der skript_id (s. Abb. Auf der nchsten Folie)

MySQL bung

12.12.01MySQL bung*Ergebnis der Abfrage

MySQL bung

12.12.01MySQL bung*Auswahl von DatenstzenAusgabe von Datenstzen aus Skripten-Tabelle, mit Restbestand < 100SELECT id, name, autor, restbestandFROM skripteWHERE restbestand < 100ORDER BY name

MySQL bung

12.12.01MySQL bung*Ergebnis der Abfrage

MySQL bung

12.12.01MySQL bung*Nachlieferung von SkriptenINSERT INTO lieferung (skript_id, datum, anzahl, preis) VALUES ('1', '2006-06-13', '50', '15.50'); UPDATE skripte SET restbestand = restbestand + 50,Preis = 15.50where id = 1Hinweis: Preis hat sich um 50 Cent erhht!

MySQL bung

12.12.01MySQL bung*Auswahl von DatenstzenAusgabe aller Liefervorgnge:SELECT s.id, s.name, s.autor, l.datum, l.anzahl, l.preis, l.anzahl*l.preisFROM skripte as s, lieferung as lWHERE s.id = l.skript_idORDER BY s.id

MySQL bung

12.12.01MySQL bung*Auswahl von Datenstzen

Ergebnis der Abfrage:

MySQL bung

12.12.01MySQL bung*Auswahl von DatenstzenAusgabe aller Liefervorgnge mit Gesamtlieferkosten pro Skript:SELECT s.id, s.name, s.autor, l.datum, sum(l.anzahl), sum(l.anzahl*l.preis)FROM skripte as s, lieferung as lWHERE s.id = l.skript_idGROUP BY s.idORDER BY s.id

MySQL bung

12.12.01MySQL bung*Auswahl von Datenstzen

Ergebnis der Abfrage:

MySQL bung