Travel By Bus
Thomas Handke Anja Plikat Barbara Buhrke Marco C. Wiese
Übersicht
• Zielstellung • ERM • DB-Schema
• 1. - 3. NF
• Implementierung • Abfragen
Zielstellung
Analyse des Geschäftes zur Optimierung und
gezielten Kundenwerbung
1. Welche Reisen werden momentan angeboten ? 2. Welche ist die teuerste / günstigste Reise ? 3. Wie groß sind unsere Reisegruppen a) durchschnittlich ? b) maximal ? c) Minimal ? 4. Eine Rechnung muss an Kunden versandt werden. 5. Gesucht sind die Reisen, die im Wegpunkt 1 Berlin anfahren, um dort Kunden abzuholen. 6. Wieviele Reisen beginnen in Berlin? 7. Wie viele Plätze besitzt der Bus, der die Weihnachtstour durchführt?
ERM
DB-Schema
Kunden # Kd_id Mitarbeiter # Pers_id Reisen # Bez_id Busse # Fz_id ermoeglichen # Id_ermoeglichen buchen # Id_buchen organisieren # Id_org # Pers_id # Bez_id # Bez_id # Kd_id Datum # Fz_id # Bez_id Plaetze Status Strecke Preis Beschreibung Name Tel Anschrift Name Anz_pers Kat_gr Bankdaten Anschrift Email
1. NF
Kunden # Kd_id Mitarbeiter # Pers_id Reisen # Bez_id Busse # Fz_id ermoeglichen # Id_erm buchen # Id_buchen # Bez_id organisieren # Id_org # Pers_id # Bez_id # Kd_id Datum # Fz_id # Bez_id Plaetze Status Bezeichnung Start Wp_1 Wp_2 Ziel Preis Beschreibung Name Vorname Tel Strasse HN PLZ Ort Name Vorname Anz_pers Kinder Jugendliche Erwachsene Rentner Bank Kto_nr BLZ Strasse HN PLZ Ort
2. NF
Kunden # Kd_id Mitarbeiter # Pers_id Reisen # Bez_id Busse # Fz_id ermoeglichen # Id_erm buchen # Id_buchen # Bez_id organisieren # Id_org # Pers_id # Bez_id # Kd_id Datum # Fz_id # Bez_id Plaetze Status Bezeichnung Start Wp_1 Wp_2 Ziel Preis Beschreibung Name Vorname Tel Strasse HN PLZ Ort Name Vorname Anz_pers Kinder Jugendliche Erwachsene Rentner Bank Kto_nr BLZ Strasse HN PLZ Ort
3. NF
Kunden # Kd_id Mitarbeiter # Pers_id Reisen # Bez_id Busse # Fz_id ermoeglichen # Id_erm buchen # Id_buchen organisieren # Id_org Orte # Ort_id Bank # Blz Kto_nr Bank PLZ Strasse HN Ort # Pers_id # Bez_id # Bez_id # Kd_id Datum # Fz_id # Bez_id Plaetze Status Preis Beschreibung Start Wp_1 Wp_2 Ziel Name Vorname Tel # Ort_id Name Vorname Anz_pers Kinder Jugendliche Erwachsene Rentner Email # Blz
Implementierung (MySQL)
Erstellen der Datenbank Betreten der DB
create database tbb; use tbb;
Erstellen der Relationen und festlegen der Datentypen sowie Schlüsseldefinition
Erfassen von Datensätzen Ändern von Datensätzen
create table buchen (Id_buchen int, Kd_id int, Datum date, Bez_id char(30), primary key(Id_buchen), foreign key(Kd_id) references Kunden(Kd_id), foreign key(Bez_id) references Reisen(Bez_id));
insert into buchen values (1, 5, 20091206); update buchen set Datum=20081206 where Id_buchen like 1;
Abfragen
1. (PROJEKTION) select Beschreibung from Reisen;
2. a) teuerste (VERSCHACHTELTER JOIN) select from where in R.Beschreibung Reisen R R.Preis (select max(R.Preis) from Reisen R);
b) günstigste (VERSCHACHTELTER JOIN) select R.Beschreibung from Reisen R where R.Preis in (select min(R.Preis) from Reisen R);
Abfragen
3. a) durchschnittlich (SPALTENFUNKTION) select avg(Anz_pers) from Kunden; b) maximal (VERSCHACHTELTER JOIN) select Anz_pers as groesste_Reisegruppe from Kunden where Anz_pers In (select max(Anz_pers) from Kunden); c) minimal (VERSCHACHTELTER JOIN) select from where in Anz_pers as kleinste_Reisegruppe Kunden Anz_pers (select min(Anz_pers) from Kunden);
Abfragen
4. Rechnung --> Wie viel muss Kunde Anja Schulz für welche Reise? Bis wann muss sie diese Reise bezahlen? (JOIN) select from where and and and 5. (SELEKTION) select from where R.Preis, R.Beschreibung, b.Datum Reisen R, Kunden K, buchen b K.Name'=Schulz’ K.Vorname'=Anja’ K.Kd_id = b.Kd_id R.Bez_id = b.Bez_id;
R.Beschreibung Reisen R Wp_1 ='Berlin’;
Abfragen
6. (SPALTENFUNKTION) select count(*),start as Reisen_von_Berlin from Reisen R where R.start='Berlin' group by R.start;
7.
select from where and and
(JOIN) F.Busse Busse F, ermoeglichen e, Reisen R R.Bez_id`=Weihnachtstour’ R.Bez_id = e.Bez_id e.Fz_id = F.Fz_id;
Danke
Anja Plikat Thomas Handke Barbara Buhrke Marco Christian Wiese