PMG2224 4/DB Beleg/Soraya

Wiki-Quelltext dieser Seite.

<language>SQL</language>

==Datenbank-Beleg:==
<document>SorayaM_ERM-Modell.pdf</document>
<document>SorayaM_RM-Modell.pdf</document>

'''''Thema der Datenbank:'''''
Fahrschule 

'''''Ziel/Begründung:''''' 
* verschiedene Datenpunkte, die effizient organisiert werden können
* erleichterter Zugriff auf:

** Schülerinformationen 
** Theoriestunden
** Fahrstunden 
** Prüfungstermine
** Fahrlehrerdaten 
** Fahrzeuge
<code id="65f6bce623267">create database Fahrschule;

use Fahrschule;

CREATE TABLE Fahrzeug
(
  id INT NOT NULL auto_increment,
  Name VARCHAR(128) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE Fahrlehrer
(
  id INT NOT NULL auto_increment,
  Vorname VARCHAR(128) NOT NULL,
  Nachname VARCHAR(128) NOT NULL,
  Telefonnummer VARCHAR(16) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE Fahrschueler
(
  id INT NOT NULL auto_increment,
  Vorname VARCHAR(128) NOT NULL,
  Nachname VARCHAR(128) NOT NULL,
  Telefonnummer VARCHAR(16) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE Fahrstunde
(
  id INT NOT NULL auto_increment,
  Start DATETIME NOT NULL,
  Ende DATETIME NOT NULL,
  istSonderfahrt INT NOT NULL,
  kostenProStunde FLOAT NOT NULL,
  istPruefung INT NOT NULL,
  FahrschuelerID INT NOT NULL,
  FahrzeugID INT NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (FahrschuelerID) REFERENCES Fahrschueler(id),
  FOREIGN KEY (FahrzeugID) REFERENCES Fahrzeug(id)
);

CREATE TABLE TheorieUnterricht
(
  Start DATETIME NOT NULL,
  Ende DATETIME NOT NULL,
  istPruefung INT NOT NULL,
  id INT NOT NULL auto_increment,
  FahrlehrerID INT NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (FahrlehrerID) REFERENCES Fahrlehrer(id)
);

CREATE TABLE nimmtTeil
(
  FahrschuelerID INT NOT NULL,
  TheorieUnterrichtID INT NOT NULL,
  PRIMARY KEY (FahrschuelerID, TheorieUnterrichtID),
  FOREIGN KEY (FahrschuelerID) REFERENCES Fahrschueler(id),
  FOREIGN KEY (TheorieUnterrichtID) REFERENCES TheorieUnterricht(id)
);

CREATE TABLE fuertDurch
(
  istPruefer INT NOT NULL,
  FahrlehrerID INT NOT NULL,
  FahrstundeID INT NOT NULL,
  PRIMARY KEY (FahrlehrerID, FahrstundeID),
  FOREIGN KEY (FahrlehrerID) REFERENCES Fahrlehrer(id),
  FOREIGN KEY (FahrstundeID) REFERENCES Fahrstunde(id)
);
</code>

<run id="65f6d5123b40b">#Wie lautet die Telefonnummer von Fahrlehrer Schmidt?
SELECT Telefonnummer FROM Fahrlehrer WHERE Nachname = "Schmidt";

#Wie viele Fahrschulautos besitzt die Fahrschule?
SELECT COUNT(id) FROM Fahrzeug;

#Welche Fahrzeuge besitzt die Fahrschule?
SELECT Name FROM Fahrzeug;

#Welche Fahrschüler sind in der Fahrschule angemeldet?
SELECT Nachname, Telefonnummer FROM Fahrschueler;

#Wie viele Schüler sind am 5.3. da?
SELECT COUNT(nimmtTeil.FahrschuelerID) as Anzahl
FROM TheorieUnterricht,nimmtTeil
WHERE TheorieUnterricht.id = nimmtTeil.TheorieunterrichtID
	AND Date(TheorieUnterricht.Start) = '2024-03-05';

#Was muss Kevin bezahlen?
SELECT  SUM((Ende - Start)/10000 * kostenProStunde) AS Kosten
FROM Fahrstunde
WHERE FahrschuelerID = (SELECT id FROM Fahrschueler WHERE Vorname = 'Kevin');

#Wann hat Chantal praktische Prüfung?
SELECT Fahrstunde.Start as Prüfungstermin
FROM Fahrstunde
WHERE Fahrstunde.Fahrschuelerid = (SELECT id FROM Fahrschueler WHERE Vorname = 'Chantal')
	AND Fahrstunde.istPruefung;

#Wann hat Chantal theoretische Prüfung?
SELECT TheorieUnterricht.Start AS Prüfungstermin
FROM TheorieUnterricht,nimmtTeil
WHERE TheorieUnterricht.id = nimmtTeil.TheorieunterrichtID
    AND TheorieUnterricht.istPruefung
	AND nimmtTeil.Fahrschuelerid = (SELECT id from Fahrschueler where Vorname = 'Chantal');

#Welches Fahrzeug wird am meisten genutzt?
SELECT Count(Fahrstunde.id) AS Anzahl,
	Fahrzeug.Name
FROM Fahrstunde,Fahrzeug
WHERE Fahrstunde.FahrzeugID = Fahrzeug.id
GROUP BY Fahrzeug.id
ORDER BY Anzahl DESC;
</run>