12.12.01MySQL Übung1 OnLine Datenbanken MySQL Übung Datenbank für Skriptenverkauf Beim AStA

12.12.01 MySQL Übung 1

OnLine Datenbanken

MySQL Übung

Datenbank für SkriptenverkaufBeim AStA


OnLine Datenbanken Für die Durchführung der Übung wird

xampplite eingesetzt. Es beinhaltet folgende Programme: Apache (Web Server) MySQL (Datenbank Server) phpMyAdmin (Werkzeug zum Erstellen und

bearbeiten von Datenbanken)Sie können diese Software downloaden von:http://www.apachefriends.org


Aufagabenbeschreibung Der Verkauf von Skripten beim AStA

soll mit Hilfe einer Datenbank erfasst werden. Folgende Vorgänge müssen in der Datenbank abgebildet werden:

1. Erstmalige Lieferung von Skripten2. Verkauf von Skripten3. Nachlieferung von Skripten


Benötigte Tabellen

1. skripte: enthält Informationen über die Skripte (Autor, Name, Preis, Anzahl, Restbestand)

2. lieferung: enthält Informationen über die Lieferung von Skripten

3. verkauf: enthält Informationen über den Verkauf von Skripten


Tabelle skripte

Struktur der Tabelle "skripte"


Hinweise zur Tabelle Skripte

Das Feld "id" ist der Primärschlüssel in dieser Tabelle. Das Attribut "autoincrement" bedeutet, dass es von MySQL automatisch weiter gezählt wird bei jedem neuen Datensatz

Das Feld "name" hat das Attribut "Unique". Es darf nur einmal in der Tabelle vorkommen

Das Feld "autor" ist ein index-Feld. Das beschleunigt die Suche nach Autoren


Hinweise zur Tabelle Skripte

Aus der Sicht einer optimalen Auslegung der Datenbank (Normalisierung) müsste eine extra Tabelle "Autoren" angelegt werden. Aus Gründen der Vereinfachung wird jedoch darauf verzichtet.


Tabelle Skripte erstellen


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;


Tabelle Lieferung

Struktur der Tabelle "lieferung"


Hinweis zur Tabelle Lieferung

Das Feld "skript_id" ist ein

Sekundärschlüssel und bildet die Verknüpfung zur Tabelle "skripte"

Tabelle Lieferung erstellen



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 ;


Tabelle Verkauf

Struktur der Tabelle "verkauf"


Tabelle verkauf erstellen


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 ;

Beziehung zwischen Tabellen erstellen

Wählen Sie In phpMyAdmin die Registerkarte Design.

verknüpfen Sie das Feld id in der Tabelle Skripte mit dem Feld skript_id in der Tabelle "lieferung".

verknüpfen Sie das Feld id in der Tabelle Skripte mit dem Feld skript_id in der Tabelle "verkauf".

s. Abbildung auf der nächsten Folie


Beziehung zwischen Tabellen erstellen



Lieferung von SkriptenMan muss 2 Vorgänge unterscheiden1. Erstmalige Lieferung: Je ein Datensatz

wird in die Tabellen lieferung und skripte eingefügt.

2. Nachlieferung von vorhandenen Skripten: Ein Datensatz wird in die Tabelle lieferung eingefügt. In der Tabelle skripte wird nur der restbestand und evtl. der Preis geändert


Verkauf von SkriptenFolgende Aktionen werden

durchgeführt:1. Ein neuer Datensatz wird in die

Tabelle verkauf eingefügt.2. In der Tabelle skripte wird der

Restbestand um die Anzahl der verkauften Skripte vermindert


Datensatz in Tabelle Skripte einfügen

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

)



INSERT INTO skripte (name, autor, preis, beschreibung, restbestand)

VALUES ('Einführung in das Agrarrecht', 'Turner, G.; Werner, K.', '12.00',

'Besondere Kapitel des Agrarrechts: Landwirtschaftliches Eigentum, Erbrecht mit Landw. Sondererbrecht, Gebrauchsüberlassungsverträge, Sicherung von Krediten Landwirtschaftliches Arbeits-, Sozial- und Steuerrecht, Agrarrecht in Europa.', 150)




VALUES ('Einführung in Javascript', 'RRZN', '6.00',

'Programmieren von dynamischen HTML-Seiten mit JavaScript.', 100)




VALUES ('Einführung in SPSS', 'RRZN', '7.50',

'Das Statistikpaket SPSS für Anfänger.', 80)



INSERT INTO skripte( name, autor, preis, beschreibung, restbestand ) VALUES ('Word 2002 für Fortgeschrittene', 'RRZN', '6.00', 'Dokument- und Formatvorlagen, Verzeichnisse, Grafiken einfügen und bearbeiten,..', 100

)


Datensätze in Tabelle skripte


Datensätze in Tabelle lieferung einfügen

INSERT 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')

Tabelle lieferung



Datensatz in Tabelle verkauf einfügen

INSERT INTO verkauf (skript_id, datum, anzahl, erloes)

VALUES ('2', '2006-06-13', '10', '120'); UPDATE skripte SET restbestand = restbestand - 10 where id = 2



INSERT INTO verkauf (skript_id, datum, anzahl, erloes) VALUES ('1', '2006-06-13', '4', '60');

UPDATE skripte SET restbestand = restbestand - 4 where id = 1




VALUES ('3', '2005-12-13', '20', '120');





VALUES ('3', '2006-12-13', '10', '60');



Auswahl von Datensätzen

Beispiele für die SELECT-Anweisung:Ausgabe aller Skripte:

SELECT *FROM skripteORDER BY name



Ausgabe von Verkaufszahlen und Erlös:(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



Alternative Formulierung:(Verknüpfung 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



Weitere 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

(Kürzel)für die Tabellennamen benutzt

Ergebnis der Abfrage

Ausgabe von Verkaufszahlen und Erlös:



Auswahl von DatensätzenAusgabe 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:



Ausgabe der Summe von Verkaufszahlen und Erlös:

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



Ergebnis der Abfrage:Verkaufszahlen und Erlös werden pro Skript zusammengefasst



Übersicht ü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



Ergebnis der Abfrage:.



Verknüpfung 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


Ergebnis der AbfrageHinweis: Ergebnis der Abfrage enthält auch

SkripteFür die es keinen Eintrag in derTabelle verkauf gibt (s. Zeilen 4+5)



Ausgabe von Skripten, für 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


Ergebnis der Abfrage Ergebnis enthält nur Datensätze

von nicht verkauften Skripten



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


Ergebnis der Abfrage Ergebnis der Abfrage enthält auch Einträge

in der Tabelle verkauf Für 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 nächsten Folie)




Auswahl von DatensätzenAusgabe von Datensätzen aus

Skripten-Tabelle, mit Restbestand < 100SELECT id, name, autor, restbestandFROM skripteWHERE restbestand < 100ORDER BY name




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 erhöht!


Auswahl von DatensätzenAusgabe aller Liefervorgänge:SELECT s.id, s.name, s.autor,

l.datum, l.anzahl, l.preis, l.anzahl*l.preis

FROM skripte as s, lieferung as lWHERE s.id = l.skript_idORDER BY s.id



Ergebnis der Abfrage:


Auswahl von DatensätzenAusgabe aller Liefervorgänge 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


Auswahl von DatensätzenErgebnis der Abfrage:

Documents

12.12.01MySQL Übung1 OnLine Datenbanken MySQL Übung Datenbank für Skriptenverkauf Beim AStA