Projekt Busunternehmen
Die Aufgabenstellung
Ein Busunternehmen bietet Tagesausflüge an. Stammkunden erhalten halbjährlich das Ausflugsprogramm, neue Kunden werden durch Annoncen in der Tageszeitung geworben. Für jede Fahrt wird höchstens ein Bus eingesetzt. Die Organisation der Ausflüge soll mit Hilfe einer Datenbank erfolgen, in der folgende Daten enthalten sein müssen: von jedem Bus, den das Unternehmen besitzt, das Kfz-Kennzeichen, der Bustyp, die Anzahl der Sitzplätze und das Baujahr, von jeder Fahrt das Reiseziel, das Datum, die Dauer in Stunden und der Preis pro Person, von jedem Fahrer des Unternehmens Name und Anschrift (Plz, Ort, StrasseNr, Telefon), dabei sei vorausgesetzt, dass es nicht zwei Fahrer mit demselben Namen in dem Busunternehmen gibt, von den Kunden Name und Anschrift (Plz, Ort, StrasseNr, Telefon) und ihre gebuchten Fahrten mit Angabe der für sie reservierten Plätze.
Das ER-Modell
Zum Editieren: Die Dia-Datei Datei:Busunternehmen.zip
Die Geschäftsregeln
- Für eine Fahrt wird genau ein Bus eingesetzt.
- Ein Bus hat mehrere Fahrten, vorrausgesetzt die Fahrten sind an verschiedenen Tagen.
- Ein Fahrer fährt mehrere Busse zu verschiedenen Zeiten.
- Eine Fahrt wird von einem Fahrer erledigt.
- Eine Fahrt benötigt mindestens einen Kunden, der dieselbe bucht.
- Ein Kunde kann mehrere Fahrten buchen
Das Relationenmodell
Fahrer (Fahrer_ID, Vorname, Nachname, Plz, Ort, StraßeNr, Telefon);
Bus (Kfz, Bustyp, Sitzplätze, Baujahr);
Fahrt (Fahrt_ID, Dauer, Datum, Reisziel, Preis, Kfz, Fahrer_ID);
Kunden (Kunde_ID, Vorname, Nachname, Plz, Ort, StraßeNr, Telefon);
Buchungen (Kunde_ID, Fahrt_ID, reservierte Plätze);
DB Aufbau (SQL)
Alles in SQL - Form Datei:Busunternehmen SQL.zip
- Buchungen
DROP TABLE IF EXISTS Buchungen; CREATE TABLE IF NOT EXISTS Buchungen ( ID_Kunde int(11) NOT NULL, ID_Fahrt int(11) NOT NULL, `reservierte Plätze` int(11) NOT NULL, PRIMARY KEY (ID_Kunde,ID_Fahrt) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
- Buchungen mit Werten füllen:
INSERT INTO Buchungen (ID_Kunde, ID_Fahrt, reservierte Plätze) VALUES (3, 7, 2),(4, 1, 2), ... usw.
- Bus
DROP TABLE IF EXISTS Bus; CREATE TABLE IF NOT EXISTS Bus ( Kfz varchar(15) NOT NULL, Typ tinytext NOT NULL, Sitzplätze int(11) NOT NULL, Baujahr year(4) NOT NULL, PRIMARY KEY (Kfz) ) ENGINE=MyISAM DEFAULT CHARSET=latin1
- Bus mit Werten füllen:
INSERT INTO Bus (Kfz, Typ, Sitzplätze, Baujahr) VALUES ('FD-BU-101', 'Großraumbus', 8, 1995), ... usw.
- Fahrer
DROP TABLE IF EXISTS Fahrer; CREATE TABLE IF NOT EXISTS Fahrer ( ID_Fahrer int(11) NOT NULL auto_increment, Vorname varchar(20) NOT NULL, Nachname varchar(20) NOT NULL, Plz int(8) NOT NULL, Ort varchar(40) NOT NULL, Straße varchar(40) NOT NULL, Telefon int(11) default NULL, PRIMARY KEY (ID_Fahrer) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;
- Fahrer mit Werten füllen:
INSERT INTO Fahrer (ID_Fahrer, Vorname, Nachname, Plz, Ort, Straße, Telefon) VALUES (1, 'Klaus', 'Müller', 41567, 'Unterhausen', 'Bergmannallee 4a', 2147483647), ... usw.
- Fahrt
DROP TABLE IF EXISTS Fahrt; CREATE TABLE IF NOT EXISTS Fahrt ( ID_Fahrt int(11) NOT NULL auto_increment, Dauer float NOT NULL, Reiseziel varchar(40) NOT NULL, Preis float NOT NULL, Kfz varchar(15) NOT NULL, Datum date NOT NULL, ID_Fahrer int(11) NOT NULL, PRIMARY KEY (ID_Fahrt) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;
- Fahrt mit Werten füllen:
INSERT INTO Fahrt (ID_Fahrt, Dauer, Reiseziel, Preis, Kfz, Datum, ID_Fahrer) VALUES (1, 14, 'Paris', 820, 'FD-MU-101', '2006-05-23', 5), ... usw.
- Kunden
DROP TABLE IF EXISTS Kunden; CREATE TABLE IF NOT EXISTS Kunden ( ID_Kunde int(11) NOT NULL auto_increment, Vorname varchar(20) NOT NULL, Nachname varchar(20) NOT NULL, Plz int(8) NOT NULL, Ort varchar(40) NOT NULL, StraßeNr varchar(40) NOT NULL, Telefon int(11) default NULL, PRIMARY KEY (ID_Kunde) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;
- Kunden mit Werten füllen:
INSERT INTO Kunden (ID_Kunde, Vorname, Nachname, Plz, Ort, StraßeNr, Telefon) VALUES (1, 'Matthias', 'Matschke', 31047, 'Findelbingen', 'Fuchsweg 1a', 123876), ... usw.
SQL Abfragen
- Gib die Anzahl der Tage aus, die jeder Fahrer zu fahren hat!
SELECT Vorname, Nachname, SUM(Dauer) AS 'Tage zu Fahren'
FROM Fahrer
NATURAL JOIN Fahrt
GROUP BY Nachname
Geben Sie alle Busfahrer alphabetisch aus.
SELECT DISTINCT Nachname, Vorname FROM fahrer ORDER BY Nachname
Geben Sie die Namen (Vorname, Nachname) von den Kunden aus, die bereits etwas bestellt haben. Ordnen Sie diese alphabetisch an.
SELECT DISTINCT Nachname, Vorname FROM kunden NATURAL JOIN buchungen ORDER BY Nachname
Geben Sie die Namen und die Tel. Nr. fuer alle Kunden aus, die am 05 29 2006 nach Berlin fahren.
SELECT Vorname, Nachname, Telefon FROM kunden NATURAL JOIN fahrt WHERE Reiseziel = 'Berlin' AND Datum = '2006-05-29'
Geben Sie die Anzahl der Buchungen aller Kunden.
SELECT Vorname, Nachname, Preis, COUNT(Preis) AS 'Anzahl der Buchungen'
FROM kunden
NATURAL JOIN buchungen
NATURAL JOIN fahrt
WHERE Vorname = 'Maria' AND Nachname = 'Briest'
GROUP BY Vorname
Geben Sie den minimalen bzw. maximalen Preis der Fahrten nach Berlin, als auch deren durchschnittlichen Dauer.
SELECT Reiseziel, MIN(Preis) AS 'Min Preis', MAX(Preis) AS 'Max Preis', AVG(Dauer) AS 'Durchschnitlicher Dauer' FROM fahrt WHERE Reiseziel = 'Berlin' GROUP BY Reiseziel
Geben Sie alle Kunden aus, die (Summe aller Bestellungen) auf 2000 Euro und mehr bestellt haben.
SELECT Vorname, Nachname, Preis, SUM(Preis) AS 'Bezahlt'
FROM kunden
NATURAL JOIN buchungen
NATURAL JOIN fahrt
GROUP BY Vorname
HAVING Bezahlt > 2000
Geben Sie den Typ und die Anzahl der Sitzplaetze von den Fahrzeugen, deren Anzahl der Sitzplaetze groesser als der Durschnitt ist. (Mit Hilfe einer Unterabfrage)
SELECT Typ, Sitzplätze
FROM bus AS b
WHERE b.Sitzplätze >=
(SELECT AVG(b.Sitzplätze)
FROM bus AS b)
Geben Sie eine virtuelle Tabelle aus, die das Reiseziel, Reisedauer und der Preis beinhalten.
CREATE VIEW Katalog AS SELECT reiseziel, dauer, preis FROM fahrt
