static async Task <int> Main(string[] args)
{
try
{
var options = GetOptions();
Untisdata data = await Untisdata.Load("Data", "2019-2020");
using (SchuleContext db = new SchuleContext(options))
{
db.Database.EnsureDeleted();
db.Database.EnsureCreated();
db.SeedDatabase(data, 2019);
}
}
catch (SchulDb.SchulDbException e)
{
Console.Error.WriteLine(e.Message);
Console.Error.WriteLine(e?.InnerException?.Message);
#if DEBUG
Console.Error.WriteLine(e.StackTrace);
#endif
return(1);
}
catch (Exception e)
{
Console.Error.WriteLine(e.Message);
Console.Error.WriteLine(e?.InnerException?.Message);
#if DEBUG
Console.Error.WriteLine(e.StackTrace);
#endif
return(2);
}
return(0);
}
static async Task <int> TestGetPruefung(long?pruefId = null, bool onlyNegative = false)
{
int points = 0;
string url = $"{baseUrl}/api/pruefung"
+ (onlyNegative ? "/negative" : "")
+ (pruefId != null ? $"/{pruefId}" : "");
HttpResponseMessage response = await client.GetAsync(url);
if (response.IsSuccessStatusCode)
{
points++;
try
{
string data = await response.Content.ReadAsStringAsync();
if (pruefId != null)
{
data = "[" + data + "]";
}
JsonDocument doc = JsonDocument.Parse(data);
var pruefungen = doc.RootElement.EnumerateArray()
.Select(elem => new
{
P_ID = elem.GetProperty("P_ID").GetInt64(),
P_Note = elem.TryGetProperty("P_Note", out JsonElement noteElem) ? noteElem.GetInt64() : (long?)null
})
.Where(p => (!onlyNegative || p.P_Note == 5) && (pruefId == null || p.P_ID == pruefId));
using (SchuleContext db = new SchuleContext())
{
if (pruefungen.Count() == db.Pruefung.Where(p => (!onlyNegative || p.P_Note == 5) && (pruefId == null || p.P_ID == pruefId)).Count())
{
points++;
}
}
}
static void Main(string[] args)
{
var options = new DbContextOptionsBuilder <SchuleContext>()
.UseSqlite("DataSource=../Schule.db")
.Options;
using var db = new SchuleContext(options);
@"
Nutzen Sie die erstellte View *vAbteilungsstatistik* in den folgenden Abfragen. Achtung: Die korrekten
Lösungen zeigen nur die ersten 10 bzw. 20 Datensätze an.".Write();
@"
Welche Abteilung hat mehr als 200 Schüler? Ordnen Sie nach der Schüleranzahl absteigend.".WriteItem();
(from a in db.Abteilungens
let schuelerGesamt = a.Klassens.Sum(k => k.Schuelers.Count)
where schuelerGesamt > 200
select new
{
a.AbtNr,
a.AbtName,
SchuelerGesamt = schuelerGesamt
}).Take(10).WriteMarkdown();
@"
Welche Abteilung hat mehr als 27 Schüler pro Klasse (im Durchschnitt,
also Schüleranzahl / Klassenanzahl)? Achten Sie auf die Kommastellen in der Berechnung.".WriteItem();
(from a in db.Abteilungens.Include(a => a.Klassens).ThenInclude(k => k.Schuelers).ToList()
let avgAnz = (decimal)a.Klassens.Sum(k => k.Schuelers.Count) / a.Klassens.Count()
where avgAnz > 27
select new
{
a.AbtNr,
SchuelerProKlasse = Math.Round(avgAnz, 2)
}).Take(10).WriteMarkdown();
@"
Für die folgenden Beispiele erstellen Sie eine View *vPruefungsstatistik* mit folgenden Spalten:
| P_Pruefer | L_Name | L_Vorname | P_Gegenstand | P_Note | S_Nr | S_Zuname | S_Vorname | K_Nr | K_Abteilung |
| --------- | ------ | --------- | ------------ | ------ | ---- | -------- | --------- | ---- | ----------- |
Erstellen Sie eine View *vNotenspiegel*, die ausgehend von *vPruefungsstatistik* die Anzahl
der vergebenen Noten pro Lehrer und Fach ermittelt. Die Spalte *KeineNote* zählt alle mit NULL
eingetragenen Prüfungen. Die Spalte *Beurteilt* gibt die Anzahl der beurteilten Prüfungen (Note ist
nicht NULL) an. Beantworten Sie mit Hilfe der View *vNotenspiegel* die folgenden Beispiele.".Write();
(from p in db.Pruefungens.Include(p => p.PPrueferNavigation).ToList()
group p by new { p.PPrueferNavigation, p.PGegenstand } into g
orderby g.Key.PPrueferNavigation.LNr, g.Key.PGegenstand
select new
{
P_Pruefer = g.Key.PPrueferNavigation.LNr,
L_Name = g.Key.PPrueferNavigation.LName,
L_Vorname = g.Key.PPrueferNavigation.LVorname,
P_Gegenstand = g.Key.PGegenstand,
Note1 = g.Count(pr => pr.PNote == 1),
Note2 = g.Count(pr => pr.PNote == 2),
Note3 = g.Count(pr => pr.PNote == 3),
Note4 = g.Count(pr => pr.PNote == 4),
Note5 = g.Count(pr => pr.PNote == 5),
KeineNote = g.Count(pr => pr.PNote == null),
Beurteilt = g.Count(pr => pr.PNote != null),
}).Take(20).WriteMarkdown();
@"
Wie viele Prüfungen gab es pro Prüfer?".WriteItem();
(from l in db.Lehrers
orderby l.LNr
select new
{
Pruefer = l.LNr,
l.LName,
l.LVorname,
AnzPruefungen = l.Pruefungens.Count()
}).Take(10).WriteMarkdown();
@"
Wie viel Prozent negative Prüfungen (Prüfungen mit 5 in Relation zu den beurteilten Prüfungen)
gab es bei jedem Lehrer pro Gegenstand?".WriteItem();
(from p in db.Pruefungens.ToList()
group p by new { p.PPruefer, p.PGegenstand } into g
orderby g.Key.PPruefer, g.Key.PGegenstand
select new
{
g.Key.PPruefer,
g.Key.PGegenstand,
ProzentNegativ = Math.Round(100M * g.Count(pr => pr.PNote == 5) / Math.Max(1, g.Count(pr => pr.PNote != null)), 2)
}).Take(20).WriteMarkdown();
@"
Wie viel Prozent negative Prüfungen (Prüfungen mit 5 in Relation zu den beurteilten Prüfungen)
gab es bei jedem Lehrer über alle Gegenstände hinweg? Summieren Sie mit einer Unterabfrage
in FROM vorher die Prüfungen des Prüfers in *vNotenspiegel* in allen seinen Gegenständen auf.".WriteItem();
(from p in db.Pruefungens.ToList()
group p by new { p.PPruefer } into g
orderby g.Key.PPruefer
select new
{
g.Key.PPruefer,
ProzentNegativ = Math.Round(100M * g.Count(pr => pr.PNote == 5) / Math.Max(1, g.Count(pr => pr.PNote != null)), 2)
}).Take(10).WriteMarkdown();
}
static void Main(string[] args)
{
var options = new DbContextOptionsBuilder <SchuleContext>()
.UseSqlite("DataSource=../Schule.db")
.Options;
using var db = new SchuleContext(options);
@"
In welchen Klassen der Abteilung HIF kommt das Fach NW2 nicht im Stundenplan vor? Hinweis:
Arbeiten Sie mit der Menge der Klassen, in denen NW2 unterrichtet wird.".WriteItem();
(from k in db.Klassens
where k.KAbteilung == "HIF" && !k.Stundens.Any(k => k.StGegenstand == "NW2")
orderby k.KNr
select new
{
k.KNr
}).WriteMarkdown();
@"
Welche Gegenstände werden gar nicht geprüft? Lösen Sie die Aufgabe mit einem LEFT JOIN und danach
mit einer Unterabfrage. Hinweis: Arbeiten Sie mit der Menge der Gegenstände, die in der
Prüfungstabelle eingetragen sind.".WriteItem();
(from g in db.Gegenstaendes
where !g.Pruefungens.Any()
select new
{
g.GNr,
g.GBez
}).WriteMarkdown();
@"
Welche Gegenstände werden nur praktisch geprüft (*P_Art* ist p)? Können Sie die Aufgabe auch mit
LEFT JOIN lösen? Begründen Sie wenn nicht. Hinweis: Arbeiten Sie mit der Menge der Gegenstände,
die NICHT praktisch geprüft werden. Betrachten Sie außerdem nur Gegenstände, die überhaupt geprüft
werden. Würden Gegenstände, die gar nicht geprüft werden, sonst aufscheinen? Macht das einen
(aussagenlogischen) Sinn?".WriteItem();
(from g in db.Gegenstaendes
where g.Pruefungens.Any() && !g.Pruefungens.Any(p => p.PArt != "p")
orderby g.GNr
select new
{
g.GNr,
g.GBez
}).WriteMarkdown();
@"
Gibt es Prüfungen im Fach BWM, die von Lehrern abgenommen wurden, die die Klasse gar nicht
unterrichten? Hinweis: Arbeiten Sie über die Menge der Lehrer, die den angezeigten Schüler unterrichten.".WriteItem();
(from p in db.Pruefungens
where p.PGegenstand == "BWM" && !p.PPrueferNavigation.Stundens.Any(s => s.StKlasse == p.PKandidatNavigation.SKlasse)
orderby p.PPruefer, p.PDatumZeit
select new
{
p.PPruefer,
p.PDatumZeit,
p.PKandidatNavigation.SNr,
p.PKandidatNavigation.SZuname,
p.PKandidatNavigation.SVorname,
p.PGegenstand,
p.PNote
}).WriteMarkdown();
@"
Für die Maturaaufsicht in POS werden Lehrer benötigt, die zwar in POS (Filtern nach POS%) unterrichten,
aber in keiner 5. HIF Klasse (*K_Schulstufe* ist 13 und *K_Abteilung* ist HIF) sind.".WriteItem();
(from l in db.Lehrers
where
l.Stundens.Any(s => s.StGegenstand.StartsWith("POS")) &&
!l.Stundens.Any(s =>
s.StKlasseNavigation.KSchulstufe == 13 &&
s.StKlasseNavigation.KAbteilung == "HIF")
orderby l.LNr
select new
{
l.LNr,
l.LName,
l.LVorname
}).WriteMarkdown();
@"
Lösen Sie das vorige Beispiel mit anderen Bedingungen: Geben Sie die Lehrer aus, die weder in einer
5. Klasse (*K_Schulstufe* ist 13) noch in einer HIF Klasse (*K_Abteilung* ist HIF) unterrichten.
Wie ändert sich Ihre Abfrage?".WriteItem();
(from l in db.Lehrers
where
l.Stundens.Any(s => s.StGegenstand.StartsWith("POS")) &&
!l.Stundens.Any(s => s.StKlasseNavigation.KSchulstufe == 13) &&
!l.Stundens.Any(s => s.StKlasseNavigation.KAbteilung == "HIF")
orderby l.LNr
select new
{
l.LNr,
l.LName,
l.LVorname
}).WriteMarkdown();
@"
Welche Klassen der HIF Abteilung haben auch in den Abendstunden (*Stundenraster.Str_IstAbend* = 1)
Unterricht?".WriteItem();
(from k in db.Klassens
where k.KAbteilung == "HIF" && k.Stundens.Any(s => s.StStundeNavigation.StrIstAbend)
orderby k.KNr
select new
{
k.KNr
}).WriteMarkdown();
@"
Welche Lehrer haben Montag und Freitag frei, also keinen Unterricht an diesen Tagen in der
Stundentabelle? Anmerkung, die nichts mit der Lösung zu tun hat: Religion und die Freifächer
wurden - in Abweichung zu den Realdaten - nicht importiert.".WriteItem();
(from l in db.Lehrers
where !l.Stundens.Any(s => s.StTag == 1 || s.StTag == 5)
orderby l.LNr
select new
{
l.LNr,
l.LName,
l.LVorname
}).WriteMarkdown();
@"
Wie das vorherige Beispiel, allerdings sollen nur Lehrer, die auch Stunden haben (also in der
Tabelle Stunden überhaupt vorkommen), berücksichtigt werden? Anmerkung, die nichts mit der Lösung
zu tun hat: Religion und die Freifächer wurden - in Abweichung zu den Realdaten - nicht importiert.".WriteItem();
(from l in db.Lehrers
where l.Stundens.Any() && !l.Stundens.Any(s => s.StTag == 1 || s.StTag == 5)
orderby l.LNr
select new
{
l.LNr,
l.LName,
l.LVorname
}).WriteMarkdown();
@"
Schwer, sozusagen ein SQL Hyperstar Problem: Welche Klassenvorstände unterrichten nur
in Abteilungen, die auch der Klasse
entsprechen, von der sie Klassenvorstand sind? Diese Abfrage hat eine besondere Schwierigkeit: Da
Lehrer auch von mehreren Klassen Klassenvorstand sein können, die in verschiedenen Abteilungen sein
können (z. B. Tag und Abend) brauchen Sie hier geschachtelte Unterabfragen.
1. Das Problem ist durch eine Negierung zu lösen, da IN den Existenzquantor darstellt, und wir hier
einen Allquantor brauchen.
2. Finden Sie zuerst heraus, in welchen Abteilungen der Lehrer KV ist.
3. Finden Sie die Lehrer heraus, die nicht in der Liste der Abteilungen aus (2) unterrichten.
4. Der Lehrer darf nicht in der Liste von (3) vorkommen.
5. Betrachten Sie zum Schluss nur die Lehrer, die auch KV sind. Lehrer, die kein KV sind, würden
nämlich aussagenlogisch auch nur in Abteilungen unterrichten, von denen sie KV sind.
Korrekte Ausgabe:".WriteItem();
(from l in db.Lehrers
where
l.Klassens.Any() &&
!l.Stundens.Any(s => !l.Klassens.Any(k => k.KAbteilung == s.StKlasseNavigation.KAbteilung))
orderby l.LNr
select new
{
l.LNr,
l.LName,
l.LVorname
}).WriteMarkdown();
}
/// <summary>
/// Konstruktor.
/// </summary>
/// <param name="context">
/// Wird durch Depenency Injection in services.AddDbContext()
/// in ConfigureDatabase() übergeben.
/// </param>
public PupilController(SchuleContext context)
{
this.db = context;
}
static void Main(string[] args)
{
var options = new DbContextOptionsBuilder <SchuleContext>()
.UseSqlite("DataSource=../Schule.db")
.Options;
using var db = new SchuleContext(options);
@"
Geben Sie die Klassen der Abteilung HIF und die Anzahl der männlichen und weiblichen Schüler aus.".WriteItem();
(from k in db.Klassens
where k.KAbteilung == "HIF"
orderby k.KNr
select new
{
Klasse = k.KNr,
AnzGesamt = k.Schuelers.Count(),
AnzMaennl = k.Schuelers.Where(k => k.SGeschlecht == 1).Count(),
AnzWeibl = k.Schuelers.Where(k => k.SGeschlecht == 2).Count()
}).WriteMarkdown();
@"
In welchen Klassen gibt es mehr als doppelt so viel weibliche wie männliche Schüler?".WriteItem();
(from k in db.Klassens
let anzMaennl = k.Schuelers.Where(k => k.SGeschlecht == 1).Count()
let anzWeibl = k.Schuelers.Where(k => k.SGeschlecht == 2).Count()
where anzWeibl > 2 * anzMaennl
orderby k.KNr
select new
{
Klasse = k.KNr,
AnzGesamt = k.Schuelers.Count(),
AnzMaennl = anzMaennl,
AnzWeibl = anzWeibl
}).WriteMarkdown();
@"Wie viele Stunden pro Woche sehen die Klassen der Abteilung AIF ihren Klassenvorstand? Lösen Sie
das Beispiel zuerst mit einem klassischen JOIN in Kombination mit einer Gruppierung. Danach lösen Sie
das Beispiel mit einer Unterabfrage ohne JOIN. Betrachten Sie nur Klassen mit eingetragenem Klassenvorstand.".WriteItem();
(from k in db.Klassens
let anzKvStunden = k.Stundens.Where(s => s.StLehrer == k.KVorstand).Count()
where k.KVorstand != null && k.KAbteilung == "AIF"
orderby k.KNr
select new
{
k.KNr,
AnzKvStunden = anzKvStunden
}).WriteMarkdown();
@"Wie viele Wochenstunden haben die Klassen der Abteilung AIF?".WriteItem();
(from k in db.Klassens.Include(k => k.Stundens).ToList()
where k.KAbteilung == "AIF"
select new
{
k.KNr,
AnzStunden = k.Stundens.Count()
}).WriteMarkdown();
@"
Wie viel Prozent der Stunden verbringen die Schüler der Abteilung KKU (Kolleg für Design) in ihrem
Stammraum? Für diese Anzahl werden einfach die Anzahl der Datensätze in der Stundentabelle gezählt.".WriteItem();
(from k in db.Klassens
let anzStunden = k.Stundens.Count()
let anzStundenStammraum = k.Stundens.Count(s => s.StRaum == k.KStammraum)
where k.KStammraum != null && k.KAbteilung == "KKU"
orderby k.KNr
select new
{
k.KNr,
k.KStammraum,
AnzStundenGesamt = anzStunden,
AnzStundenStammraum = anzStundenStammraum,
ProzentImStammraum = Math.Round(100M * anzStundenStammraum / anzStunden, 0)
}).WriteMarkdown();
@"Welche Lehrer verdienen 50% mehr als der Durchschnitt von den Lehrern, die nachher in
die Schule eingetreten sind (Eintrittsjahr > Eintrittsjahr des Lehrers)?".WriteItem();
var lehrerLocal = db.Lehrers.ToList();
(from l in lehrerLocal
let avgGehalt = lehrerLocal.Where(le => l.LEintrittsjahr < le.LEintrittsjahr).Average(le => le.LGehalt)
where l.LGehalt > avgGehalt * 1.5M
orderby l.LEintrittsjahr, l.LNr
select new
{
l.LNr,
l.LName,
l.LVorname,
l.LGehalt,
l.LEintrittsjahr,
AvgGehaltAeltere = Math.Round(avgGehalt ?? 0, 2)
}).WriteMarkdown();
@"Welche Schüler haben im Gegenstand POS1 schlechtere Noten als der Durchschnitt der Prüfungen
bei diesem Prüfer in POS1?".WriteItem();
(from p in db.Pruefungens
let mittel = p.PPrueferNavigation.Pruefungens.Where(pr => pr.PGegenstand == "POS1").Average(pr => pr.PNote)
where p.PNote > mittel && p.PGegenstand == "POS1"
orderby p.PPruefer, p.PNote
select new
{
p.PKandidatNavigation.SNr,
p.PKandidatNavigation.SZuname,
p.PKandidatNavigation.SVorname,
p.PKandidatNavigation.SKlasse,
p.PPruefer,
p.PNote,
p.PGegenstand,
PrueferMittel = Math.Round(mittel ?? 0, 2)
}).WriteMarkdown();
@"Verallgemeinern Sie das vorige Beispiel auf beliebige Fächer: Welche Schüler der 1AHIF
haben schlechtere Noten als der Prüfer im Mittel für diesen Gegenstand vergibt?".WriteItem();
(from p in db.Pruefungens
let mittel = p.PPrueferNavigation.Pruefungens.Where(pr => pr.PGegenstand == p.PGegenstand).Average(p => p.PNote)
where p.PNote > mittel && p.PKandidatNavigation.SKlasse == "1AHIF"
orderby p.PPruefer, p.PNote
select new
{
p.PKandidatNavigation.SNr,
p.PKandidatNavigation.SZuname,
p.PKandidatNavigation.SVorname,
p.PKandidatNavigation.SKlasse,
p.PPruefer,
p.PNote,
p.PGegenstand,
PrueferMittel = Math.Round(mittel ?? 0, 2)
}).WriteMarkdown();
@"Geben Sie die letzte Stunde der 3BAIF für jeden Wochentag aus. Beachten Sie, dass
auch mehrere Datensätze für die letzte Stunde geliefert werden können (wenn 2 Lehrer dort unterrichten).".WriteItem();
(from s in db.Stundens
let letzteStunde = db.Stundens.Where(st => st.StKlasse == s.StKlasse && st.StTag == s.StTag).Max(st => st.StStunde)
where s.StKlasse == "3BAIF" && s.StStunde == letzteStunde && s.StKlasseNavigation.KAbteilung == "AIF"
orderby s.StKlasse, s.StTag
select new
{
s.StKlasse,
s.StTag,
s.StStunde,
s.StGegenstand,
s.StLehrer
}).WriteMarkdown();
}
/// <summary>
/// Konstruktor.
/// </summary>
/// <param name="context">Wird über Depenency Injection durch services.AddDbContext() übergeben.</param>
/// <param name="configuration">Wird über Depenency Injection übergeben.</param>
public UserService(SchuleContext context, IConfiguration configuration)
{
this.configuration = configuration;
this.db = context;
}
static DbContextOptions <SchuleContext> GetOptions()
{
var builder = new DbContextOptionsBuilder <SchuleContext>();
Console.Write("Welche Datenbank soll erstellt werden? [1]: SQLite (Default) [2]: LocalDb [3]: Oracle ");
string dbType = Console.ReadLine();
dbType = string.IsNullOrEmpty(dbType) ? "1" : dbType;
if (dbType == "1")
{
Console.Write("Dateiname? Hinweis: Relative Pfade (..) sind möglich. Default: Schule.db ");
string dbName = Console.ReadLine();
dbName = string.IsNullOrEmpty(dbName) ? "Schule.db" : dbName;
builder.UseSqlite($"DataSource={dbName}");
}
else if (dbType == "2")
{
Console.Write("Wie soll die Datenbank heißen? Default: Schule ");
string dbName = Console.ReadLine();
dbName = string.IsNullOrEmpty(dbName) ? "Schule" : dbName;
builder.UseSqlServer($"Server=(localdb)\\mssqllocaldb;" +
$"AttachDBFilename={System.Environment.CurrentDirectory}\\{dbName}.mdf;" +
$"Database={dbName};" +
$"Trusted_Connection=True;MultipleActiveResultSets=true");
}
else if (dbType == "3")
{
Console.Write("Wie soll der Benutzer heißen? Default: Schule ");
string dbName = Console.ReadLine();
dbName = string.IsNullOrEmpty(dbName) ? "Schule" : dbName;
builder.UseOracle($"User Id={dbName};Password=oracle;Data Source=localhost:1521/orcl");
var oracleSystemOptions = new DbContextOptionsBuilder <SchuleContext>()
.UseOracle("User Id=System;Password=oracle;Data Source=localhost:1521/orcl")
.Options;
Console.WriteLine($"Lege Benutzer {dbName} mit Passwort oracle an...");
try
{
using (SchuleContext db = new SchuleContext(oracleSystemOptions))
{
// Warning wegen möglicher SQL Injections. Da dies aber kein Produktionscode
// ist, wird sie deaktiviert. Außerdem funktioniert keine andere Variante
// (OracleParameter, Interpolated String, ...).
#pragma warning disable EF1000
try { db.Database.ExecuteSqlCommand("DROP USER " + dbName + " CASCADE"); }
catch { }
db.Database.ExecuteSqlCommand("CREATE USER " + dbName + " IDENTIFIED BY oracle");
db.Database.ExecuteSqlCommand("GRANT CONNECT, RESOURCE, CREATE VIEW TO " + dbName);
db.Database.ExecuteSqlCommand("GRANT UNLIMITED TABLESPACE TO " + dbName);
}
}
catch
{
throw new SchulDb.SchulDbException("Fehler beim Löschen und neu Anlegen des Oracle Benutzers. " +
"Mögliche Ursachen: Der Benutzer ist gerade aktiv oder die VM läuft nicht.");
}
}
else
{
throw new SchulDb.SchulDbException("Ungültige Eingabe.");
}
return(builder.Options);
}
static void Main(string[] args)
{
var options = new DbContextOptionsBuilder <SchuleContext>()
.UseSqlite("DataSource=../Schule.db")
.Options;
using var db = new SchuleContext(options);
@"Welche Lehrer sind neu bei uns, haben also das maximale Eintrittsjahr?".WriteItem();
(from l in db.Lehrers
let maxJahr = db.Lehrers.Max(l => l.LEintrittsjahr)
where l.LEintrittsjahr == maxJahr
orderby l.LNr
select new
{
l.LNr,
l.LName,
l.LVorname,
l.LEintrittsjahr
}).WriteMarkdown();
@"
Geben Sie die Klassen der Abteilung AIF und die Anzahl der gesamten Klassen und Schüler der Schule aus.".WriteItem();
(from k in db.Klassens
where k.KAbteilung == "AIF"
orderby k.KNr
select new
{
Klasse = k.KNr,
KlassenGesamt = db.Klassens.Count(),
SchuelerGesamt = db.Schuelers.Count()
}).WriteMarkdown();
@"
Geben Sie bei allen Lehrern, die 2018 eingetreten sind (Spalte *L_Eintrittsjahr*), das Durchschnittsgehalt
(gerechnet über alle Lehrer der Schule) aus.".WriteItem();
var avgLehrerGehalt = db.Lehrers.ToList().Average(l => l.LGehalt);
(from l in db.Lehrers
where l.LEintrittsjahr == 2018
orderby l.LNr
select new
{
l.LNr,
l.LName,
l.LVorname,
l.LEintrittsjahr,
l.LGehalt,
AvgGehalt = Math.Round(avgLehrerGehalt ?? 0, 2)
}).WriteMarkdown();
@"
Als Ergänzung geben Sie nun bei diesen Lehrern die Abweichung vom Durchschnittsgehalt
aus. Zeigen Sie dabei nur die Lehrer an, über 1000 Euro unter diesem Durchschnittswert verdienen.".WriteItem();
(from l in db.Lehrers.ToList()
let abw = l.LGehalt - avgLehrerGehalt
where l.LEintrittsjahr == 2018 && abw < -1000
orderby l.LNr
select new
{
l.LNr,
l.LName,
l.LVorname,
l.LEintrittsjahr,
l.LGehalt,
AvgGehalt = Math.Round(avgLehrerGehalt ?? 0, 2),
Abweichung = Math.Round(abw ?? 0, 2)
}).WriteMarkdown();
@"
Geben Sie die Prüfungen aus, die maximal 3 Tage vor der letzten Prüfung stattfanden.".WriteItem();
var letztePruef = db.Pruefungens.Max(p => p.PDatumZeit);
(from p in db.Pruefungens.Include(p => p.PKandidatNavigation).ToList()
where p.PDatumZeit >= letztePruef.AddDays(-3)
orderby p.PDatumZeit descending
select new
{
p.PDatumZeit,
p.PPruefer,
p.PNote,
Zuname = p.PKandidatNavigation.SZuname,
Vorname = p.PKandidatNavigation.SVorname
}).WriteMarkdown();
@"
Geben Sie die Räume mit der meisten Kapazität (Spalte *R_Plaetze*) aus. Hinweis: Das können auch
mehrere Räume sein.".WriteItem();
var maxPlaetze = db.Raeumes.Max(r => r.RPlaetze);
(from r in db.Raeumes
where r.RPlaetze == maxPlaetze
orderby r.RId
select r).WriteMarkdown();
@"
Gibt es Räume, die unter einem Viertel der Plätze als der größte Raum haben?".WriteItem();
(from r in db.Raeumes
where r.RPlaetze < maxPlaetze / 4
orderby r.RId
select r).WriteMarkdown();
@"
Welche Klasse hat mehr weibliche Schüler (S_Geschlecht ist 2) als die 5BAIF? Hinweis: Gruppieren Sie
die Schülertabelle und vergleichen die Anzahl mit dem ermittelten Wert aus der 5BAIF.".WriteItem();
var anzWeibl5BAIF = db.Schuelers.Count(s => s.SKlasse == "5BAIF" && s.SGeschlecht == 2);
(from k in db.Klassens
let anzWeibl = k.Schuelers.Where(s => s.SGeschlecht == 2).Count()
where anzWeibl > anzWeibl5BAIF
select new
{
Klasse = k.KNr,
AnzWeibl = anzWeibl
}).WriteMarkdown();
@"
Geben Sie die Klassen der Abteilung BIF sowie die Anzahl der Schüler in dieser Abteilung aus.
Hinweis: Verwenden Sie GROUP BY, um die Schüleranzahl pro Klasse zu ermitteln. Achten Sie auch
darauf, dass Klassen mit 0 Schülern auch angezeigt werden. Danach schreiben Sie
eine Unterabfrage, die die Schüler der BIF Abteilung zählt.".WriteItem();
(from k in db.Klassens
where k.KAbteilung == "BIF"
orderby k.KNr
select new
{
Klasse = k.KNr,
SchuelerKlasse = k.Schuelers.Count(),
SchuelerBIF = k.KAbteilungNavigation.Klassens.Sum(k => k.Schuelers.Count())
}).WriteMarkdown();
}
public static void SeedDatabase(this SchuleContext db, SchulDb.Untis.Untisdata data, int schuljahr)
{
// Wir schreiben nur die Informatikklassen in die Datenbank, sonst ist das Ergebnis der
// Abfragen unnötig groß.
var echteKlassen = (from k in data.Klassen
where Regex.IsMatch(k.Nr ?? "", @"^[0-9][A-Z_]{2,}$", RegexOptions.IgnoreCase)
select k).ToList();
if (!echteKlassen.Any())
{
throw new SchulDb.SchulDbException("Der Untis Export enthält keine Klassen.");
}
// Im Datenmodell darf ein Lehrer zu selben Zeit nur eine Klasse haben. Durch die
// Gruppierung stellen wir das sicher.
var echteStunden = (from s in data.Stundenplan
join k in echteKlassen on s.Klasse equals k.Nr
where Regex.IsMatch(s.Fach ?? "", @"^[^FR]", RegexOptions.IgnoreCase)
group s by new { s.Stunde, s.Tag, s.Lehrer, s.Klasse } into g
select g.FirstOrDefault()).ToList();
if (!echteStunden.Any())
{
throw new SchulDb.SchulDbException("Der Untis Export enthält keine Stunden im Stundenplan.");
}
// Alle Fächer, die unterrichtet werden. Das schließt Kunstfächer wie Sprechstunde, ... aus.
var echteFaecher = (from f in data.Faecher
join s in echteStunden on f.Nr equals s.Fach into sg
where sg.Any()
select f).ToList();
if (!echteFaecher.Any())
{
throw new SchulDb.SchulDbException("Der Untis Export enthält keine Fächer im Stundenplan.");
}
Randomizer.Seed = new Random(987);
DateTime current = new DateTime(schuljahr, 9, 1);
Faker fkr = new Faker();
// *********************************************************************************
// STATISCHE DATEN
// *********************************************************************************
db.Geschlechters.Add(new Geschlecht {
GesId = 1, GesMw = "m", GesMaennlichweiblich = "männlich", GesSchuelerschuelerin = "Schüler", GesLehrerlehrerin = "Lehrer"
});
db.Geschlechters.Add(new Geschlecht {
GesId = 2, GesMw = "w", GesMaennlichweiblich = "weiblich", GesSchuelerschuelerin = "Schülerin", GesLehrerlehrerin = "Lehrerin"
});
db.SaveChanges();
db.Religionens.Add(new Religion {
RelId = 10, RelNr = "ob", RelName = "Ohne Bekenntnis", RelGesetzlichanerkannt = false, RelStaatlicheingetragen = false
});
db.Religionens.Add(new Religion {
RelId = 11, RelNr = "evab", RelName = "evangelisch A.B.", RelGesetzlichanerkannt = true, RelStaatlicheingetragen = false
});
db.Religionens.Add(new Religion {
RelId = 12, RelNr = "islam", RelName = "islamisch", RelGesetzlichanerkannt = true, RelStaatlicheingetragen = false
});
db.Religionens.Add(new Religion {
RelId = 13, RelNr = "rk", RelName = "römisch - katholisch", RelGesetzlichanerkannt = true, RelStaatlicheingetragen = false
});
db.SaveChanges();
db.Schuljahres.Add(new Schuljahr {
SjaNr = 20180, SjaBezeichnung = "Schuljahr 2018/19", Wintersemester = true, Sommersemester = true, SjaDatumvon = new DateTime(2018, 9, 3), SjaDatumbis = new DateTime(2019, 6, 28)
});
db.Schuljahres.Add(new Schuljahr {
SjaNr = 20181, SjaBezeichnung = "Wintersemester 2018/19", Wintersemester = true, Sommersemester = false, SjaDatumvon = new DateTime(2018, 9, 3), SjaDatumbis = new DateTime(2019, 2, 10)
});
db.Schuljahres.Add(new Schuljahr {
SjaNr = 20182, SjaBezeichnung = "Sommersemester 2018/19", Wintersemester = false, Sommersemester = true, SjaDatumvon = new DateTime(2019, 2, 11), SjaDatumbis = new DateTime(2019, 6, 28)
});
db.Schuljahres.Add(new Schuljahr {
SjaNr = 20190, SjaBezeichnung = "Schuljahr 2019/20", Wintersemester = true, Sommersemester = true, SjaDatumvon = new DateTime(2019, 9, 2), SjaDatumbis = new DateTime(2020, 7, 3)
});
db.Schuljahres.Add(new Schuljahr {
SjaNr = 20191, SjaBezeichnung = "Wintersemester 2019/20", Wintersemester = true, Sommersemester = false, SjaDatumvon = new DateTime(2019, 9, 2), SjaDatumbis = new DateTime(2020, 2, 9)
});
db.Schuljahres.Add(new Schuljahr {
SjaNr = 20192, SjaBezeichnung = "Sommersemester 2019/20", Wintersemester = false, Sommersemester = true, SjaDatumvon = new DateTime(2020, 2, 10), SjaDatumbis = new DateTime(2020, 7, 3)
});
db.Schuljahres.Add(new Schuljahr {
SjaNr = 20200, SjaBezeichnung = "Schuljahr 2020/21", Wintersemester = true, Sommersemester = true, SjaDatumvon = new DateTime(2020, 9, 7), SjaDatumbis = new DateTime(2021, 7, 2)
});
db.Schuljahres.Add(new Schuljahr {
SjaNr = 20201, SjaBezeichnung = "Wintersemester 2020/21", Wintersemester = true, Sommersemester = false, SjaDatumvon = new DateTime(2020, 9, 7), SjaDatumbis = new DateTime(2021, 2, 7)
});
db.Schuljahres.Add(new Schuljahr {
SjaNr = 20202, SjaBezeichnung = "Sommersemester 2020/21", Wintersemester = false, Sommersemester = true, SjaDatumvon = new DateTime(2019, 2, 8), SjaDatumbis = new DateTime(2021, 7, 2)
});
db.Schuljahres.Add(new Schuljahr {
SjaNr = 20210, SjaBezeichnung = "Schuljahr 2021/22", Wintersemester = true, Sommersemester = true, SjaDatumvon = new DateTime(2021, 9, 6), SjaDatumbis = new DateTime(2022, 7, 1)
});
db.Schuljahres.Add(new Schuljahr {
SjaNr = 20211, SjaBezeichnung = "Wintersemester 2021/22", Wintersemester = true, Sommersemester = false, SjaDatumvon = new DateTime(2021, 9, 6), SjaDatumbis = new DateTime(2022, 2, 13)
});
db.Schuljahres.Add(new Schuljahr {
SjaNr = 20212, SjaBezeichnung = "Sommersemester 2021/22", Wintersemester = false, Sommersemester = true, SjaDatumvon = new DateTime(2022, 2, 14), SjaDatumbis = new DateTime(2022, 7, 1)
});
db.SaveChanges();
db.Staatens.Add(new Staat {
StaNr = "A", StaName = "Österreich", StaStaatsb = "österreichisch", StaEuland = true
});
db.Staatens.Add(new Staat {
StaNr = "TR", StaName = "Türkei", StaStaatsb = "türkisch", StaEuland = false
});
db.Staatens.Add(new Staat {
StaNr = "SBM", StaName = "Serbien", StaStaatsb = "serbisch", StaEuland = false
});
db.Staatens.Add(new Staat {
StaNr = "CRO", StaName = "Kroatien", StaStaatsb = "kroatisch", StaEuland = false
});
db.Staatens.Add(new Staat {
StaNr = "D", StaName = "Deutschland", StaStaatsb = "Deutschland", StaEuland = true
});
db.Staatens.Add(new Staat {
StaNr = "SQ", StaName = "Slowakei", StaStaatsb = "slowakisch", StaEuland = true
});
db.Staatens.Add(new Staat {
StaNr = "SLO", StaName = "Slowenien", StaStaatsb = "slowenisch", StaEuland = true
});
db.SaveChanges();
db.Stundenrasters.Add(new Stundenraster {
StrNr = 1, StrBeginn = new TimeSpan(8, 00, 0), StrEnde = new TimeSpan(8, 50, 0), StrIstAbend = false
});
db.Stundenrasters.Add(new Stundenraster {
StrNr = 2, StrBeginn = new TimeSpan(8, 50, 0), StrEnde = new TimeSpan(9, 40, 0), StrIstAbend = false
});
db.Stundenrasters.Add(new Stundenraster {
StrNr = 3, StrBeginn = new TimeSpan(9, 55, 0), StrEnde = new TimeSpan(10, 45, 0), StrIstAbend = false
});
db.Stundenrasters.Add(new Stundenraster {
StrNr = 4, StrBeginn = new TimeSpan(10, 45, 0), StrEnde = new TimeSpan(11, 35, 0), StrIstAbend = false
});
db.Stundenrasters.Add(new Stundenraster {
StrNr = 5, StrBeginn = new TimeSpan(11, 45, 0), StrEnde = new TimeSpan(12, 35, 0), StrIstAbend = false
});
db.Stundenrasters.Add(new Stundenraster {
StrNr = 6, StrBeginn = new TimeSpan(12, 35, 0), StrEnde = new TimeSpan(13, 25, 0), StrIstAbend = false
});
db.Stundenrasters.Add(new Stundenraster {
StrNr = 7, StrBeginn = new TimeSpan(13, 25, 0), StrEnde = new TimeSpan(14, 15, 0), StrIstAbend = false
});
db.Stundenrasters.Add(new Stundenraster {
StrNr = 8, StrBeginn = new TimeSpan(14, 25, 0), StrEnde = new TimeSpan(15, 15, 0), StrIstAbend = false
});
db.Stundenrasters.Add(new Stundenraster {
StrNr = 9, StrBeginn = new TimeSpan(15, 15, 0), StrEnde = new TimeSpan(16, 05, 0), StrIstAbend = false
});
db.Stundenrasters.Add(new Stundenraster {
StrNr = 10, StrBeginn = new TimeSpan(16, 15, 0), StrEnde = new TimeSpan(17, 05, 0), StrIstAbend = false
});
db.Stundenrasters.Add(new Stundenraster {
StrNr = 11, StrBeginn = new TimeSpan(17, 10, 0), StrEnde = new TimeSpan(17, 55, 0), StrIstAbend = true
});
db.Stundenrasters.Add(new Stundenraster {
StrNr = 12, StrBeginn = new TimeSpan(17, 55, 0), StrEnde = new TimeSpan(18, 40, 0), StrIstAbend = true
});
db.Stundenrasters.Add(new Stundenraster {
StrNr = 13, StrBeginn = new TimeSpan(18, 50, 0), StrEnde = new TimeSpan(19, 35, 0), StrIstAbend = true
});
db.Stundenrasters.Add(new Stundenraster {
StrNr = 14, StrBeginn = new TimeSpan(19, 35, 0), StrEnde = new TimeSpan(20, 20, 0), StrIstAbend = true
});
db.Stundenrasters.Add(new Stundenraster {
StrNr = 15, StrBeginn = new TimeSpan(20, 30, 0), StrEnde = new TimeSpan(21, 15, 0), StrIstAbend = true
});
db.Stundenrasters.Add(new Stundenraster {
StrNr = 16, StrBeginn = new TimeSpan(21, 15, 0), StrEnde = new TimeSpan(22, 00, 0), StrIstAbend = true
});
db.SaveChanges();
// *********************************************************************************
// DATEN AUS UNTIS
// *********************************************************************************
// Die Räume der Schule lesen und eintragen.
db.Raeumes.AddRange(from r in data.Raeume
where
r.Nr[0] == 'A' || r.Nr[0] == 'B' ||
r.Nr[0] == 'C' || r.Nr[0] == 'D'
select new Raum
{
RId = r.Nr,
RPlaetze = r.Kapaz,
// Stammklasse sorgt für Verwirrung, da auch der Stammraum
// der Klasse gemeint sein kann.
RArt = r.Art == "Stammklasse" ? "Klassenraum" : r.Art
});
db.SaveChanges();
// Die Lehrer der Schule lesen und eintragen.
db.Lehrers.AddRange(from l in data.Lehrer
join s in data.Stundenplan.Where(s => s.Klasse == "SPR") on l.Nr equals s.Lehrer into sg
let gebdat = fkr.Date.Between(
current.AddYears(-65),
current.AddYears(-25)).Date
let eintrittsjahr = fkr.Random.Int(
gebdat.Year + 24,
current.Year)
let spr = sg.FirstOrDefault()
select new Lehrer
{
LNr = l.Nr,
LName = l.Zuname,
LVorname = l.Vorname,
LGebdat = gebdat.OrNull(fkr, 0.2f),
LGehalt = Math.Round(2000M +
(current.Year - eintrittsjahr) * 100M +
fkr.Random.GaussianDecimal(0, 100)).OrNull(fkr, 0.2f),
LEintrittsjahr = eintrittsjahr.OrNull(fkr, 0.2f),
LSprechstunde = spr != null ? SchulDb.Untis.Untisdata.Wochentage[spr.Tag - 1] +
spr.Stunde.ToString() : null
});
db.SaveChanges();
// Die Abteilungen mit den oben eingetragenen Lehrern verknüpfen. Die ID ist die
// 3. - 5. Stelle (3AFITM --> FIT)
db.Abteilungens.Add(new Abteilung {
AbtNr = "O", AbtName = "Übergangsstufe"
});
db.Abteilungens.Add(new Abteilung {
AbtNr = "FIT", AbtName = "Fachschule für Informationstechnik", AbtLeiterNavigation = db.Lehrers.Find("HEB")
});
db.Abteilungens.Add(new Abteilung {
AbtNr = "HBG", AbtName = "Höhere Lehranstalt für Biomedizin- und Gesundheitstechnik", AbtLeiterNavigation = db.Lehrers.Find("HEB")
});
db.Abteilungens.Add(new Abteilung {
AbtNr = "HIF", AbtName = "Höhere Lehranstalt für Informatik", AbtLeiterNavigation = db.Lehrers.Find("JEL")
});
db.Abteilungens.Add(new Abteilung {
AbtNr = "HMN", AbtName = "Höhere Lehranstalt für Medien", AbtLeiterNavigation = db.Lehrers.Find("PRW")
});
db.Abteilungens.Add(new Abteilung {
AbtNr = "HKU", AbtName = "Höhere Lehranstalt für Kunst", AbtLeiterNavigation = db.Lehrers.Find("PRW")
});
db.Abteilungens.Add(new Abteilung {
AbtNr = "HWI", AbtName = "Höhere Lehranstalt für Wirtschaftsingenieure", AbtLeiterNavigation = db.Lehrers.Find("ZLA")
});
db.Abteilungens.Add(new Abteilung {
AbtNr = "AIF", AbtName = "Aufbaulehrgang Tagesform", AbtLeiterNavigation = db.Lehrers.Find("STH")
});
db.Abteilungens.Add(new Abteilung {
AbtNr = "BIF", AbtName = "Aufbaulehrgang Abendform", AbtLeiterNavigation = db.Lehrers.Find("STH")
});
db.Abteilungens.Add(new Abteilung {
AbtNr = "CIF", AbtName = "Kolleg Abendform", AbtLeiterNavigation = db.Lehrers.Find("STH")
});
db.Abteilungens.Add(new Abteilung {
AbtNr = "KIF", AbtName = "Kolleg Tagesform", AbtLeiterNavigation = db.Lehrers.Find("STH")
});
db.Abteilungens.Add(new Abteilung {
AbtNr = "VIF", AbtName = "Vorbereitungslehrgang", AbtLeiterNavigation = db.Lehrers.Find("STH")
});
db.Abteilungens.Add(new Abteilung {
AbtNr = "KKU", AbtName = "Kolleg für Design", AbtLeiterNavigation = db.Lehrers.Find("PRW")
});
db.Abteilungens.Add(new Abteilung {
AbtNr = "CMN", AbtName = "Kolleg für Medien", AbtLeiterNavigation = db.Lehrers.Find("PRW")
});
db.Abteilungens.Add(new Abteilung {
AbtNr = "BKU", AbtName = "Kolleg für Audivisuelles Mediendesign", AbtLeiterNavigation = db.Lehrers.Find("PRW")
});
db.SaveChanges();
// Gegenstände, die in den Informatikklassen unterrichtet werden, eintragen.
db.Gegenstaendes.AddRange(from f in echteFaecher
select new Gegenstand
{
GNr = f.Nr,
GBez = f.Langname
});
db.SaveChanges();
// Klassen aus den Informatikabteilungen eintragen.
db.Klassens.AddRange(from k in echteKlassen
let sjahr = db.Schuljahres.Find(current.Year * 10 + k.Jahresform)
let abt = k.Nr.Substring(2, Math.Min(3, k.Nr.Length - 2))
select new Klasse
{
KNr = k.Nr,
KSchuljahrNavigation = sjahr,
KBez = k.Beschreibung,
// Bei Jahresformen wird die Schulstufe (9, 10, ...) eingetragen.
KSchulstufe = k.Jahresform == 0 ? k.Schulstufe : null,
KStammraumNavigation = db.Raeumes.Find(k.Stammraum),
KVorstandNavigation = db.Lehrers.Find(k.Kv),
KAbteilungNavigation = db.Abteilungens.Find(abt) ?? throw new SchulDb.SchulDbException($"Abteilung {abt} nicht vorhanden!"),
KDatumbis = k.Abschlussklasse ? new DateTime(current.Year + 1, 5, 1) : (DateTime?)null
});
