pdf-5 - Universität Bremen

Entwurf eines Datenbanksystems für
eine Schule
Datenbanksysteme
Wintersemester 2004/05
Patrice Calvin Taffou Happi
([email protected])
Ruben Rothaupt
([email protected])
Inhalt
1. Einleitung
2. Anwendungsbeschreibung
3. Entity- Relationship- Modell
3.1. Entity- Relationship- Diagramm
3.2. Grundkonzepte des Entity- Relationship- Modells
3.2.1. Entities
3.2.2. Relationships
3.2.3. Attribute
3.3. Weitere Konzepte des Entity- Relationship- Modells
3.3.1. Funktionale Beziehungen
3.3.2. Schlüssel
3.3.3. Ist- Beziehungen
3.3.4. Kardinalitäten
3.4. Textuelle Notation
3.4.1. Entity- Typen
3.4.2. Relationship- Typen
4. Relationenmodell
4.1. Relationenschemata
4.1.1. Repräsentation der Entity- Typen
4.1.2. Repräsentation der Relationship- Typen
4.2. Integritätsbedingungen
5. SQL (Standard Query Language)
5.1. Relationenschemata
5.2. Änderungsoperationen
5.3. Anfragen
6. Literatur
2
1. Einleitung
Diese Hausarbeit beschäftigt sich mit dem Entwurf eines Datenbanksystems für eine Schule.
Beim Entwurf von Datenbanken kann man drei Abstraktionsebenen unterscheiden. Dies sind
die externe, die konzeptuelle und die interne Ebene. Die externe Ebene dient zur Darstellung
der Daten aus Sicht einer konkreten Anwendung, wobei es mehrere verschiedene externe
Sichten auf eine Datenbank geben kann. Die konzeptuelle Ebene erzeugt nun eine logische
und einheitliche Gesamtsicht auf den Datenbestand und definiert, wie die zu speichernden
Daten konzeptuell angeordnet werden sollen. Die interne Ebene beschäftigt sich schließlich
damit, wie die Speicherung der Daten tatsächlich intern realisiert wird. Auf die interne Ebene
wird in dieser Hausarbeit allerdings nicht weiter eingegangen.
Im zweiten Abschnitt wird aus Sicht eines Anwenders beschrieben, welche Anforderungen
die Datenbank erfüllen soll bzw. welche Informationen in der Datenbank enthalten sein
sollen. Anhand der Anwendungsbeschreibung wird im dritten Abschnitt ein EntityRelationship- Modell (ER- Modell) erstellt. Dies ist ein abstraktes Datenbankmodell, welches
beim Entwurf von Datenbanken sehr häufig eingesetzt wird. Das ER- Modell wird im vierten
Abschnitt ins Relationenmodell übersetzt. Dazu werden Relationenschemata erstellt sowie
bestimmte Integritätsbedingungen formuliert, die eingehalten werden müssen.
Schließlich folgen im fünften Abschnitt noch die Umsetzung der zuvor erstellten
Relationenschemata in der relationalen Datenbanksprache SQL und einige SQL- Anfragen.
3
2. Anwendungsbeschreibung
In der zu entwickelnden Datenbank sollen die im Folgenden angegebenen Informationen
enthalten sein.
Es sollen Stundenplaninformationen gespeichert werden. Dabei wird zwischen dem
allgemeinen Stundenplan und dem täglichen Stundenplan, in dem Unterrichtsausfall und
Vertretung berücksichtigt werden, unterschieden. Die Stundenplaninformationen umfassen
Angaben darüber, an welchem Tag in welcher Stunde welcher Lehrer welche Klasse in
welchem Fach in welchem Raum unterrichtet.
Für jede Klasse sollen Informationen darüber verfügbar sein, wie die Bezeichnung der Klasse
lautet, wer der Klassenlehrer ist, welches der Klassenraum ist und wie viele Schüler der
Klasse angehören. Zudem soll enthalten sein, in welchem Fach eine bestimmte Klasse in
welchem Halbjahr wie viele Stunden Unterricht bekommt bzw. bekommen hat.
Für Räume sollen Raumnummer und Kapazität des Raumes gespeichert werden.
Über Personen sollen ebenfalls unterschiedliche Informationen in der Datenbank enthalten
sein. Für jede Person werden zunächst allgemeine Informationen wie Name, Anschrift,
Geburtsdatum und Telefonnummer gespeichert. Die Personen können entweder Schüler oder
Mitarbeiter sein. Dabei können Mitarbeiter wiederum unterschiedliche Funktionen an der
Schule haben; sie können z.B. Lehrer, Schulleiter oder anderes Personal (Hausmeister,
Sekretärin,...) sein.
Für jeden Schüler werden der Name der Mutter, der Name des Vaters und die Anschrift des
Erziehungsberechtigten benötigt. Außerdem gehört jeder Schüler zu einer bestimmten Klasse
und wird demzufolge in bestimmten Fächern unterrichtet. Für jedes dieser Fächer bekommt
der Schüler eine Note für das jeweilige Halbjahr. Schließlich können über Schüler noch
zusätzliche Informationen unterschiedlicher Art gespeichert werden, wie beispielsweise
Förderungsmaßnahmen oder Fehlzeiten.
Über alle Mitarbeiter sollen noch weitere Informationen wie Gehaltsinformationen,
Einstellungsdatum oder Stundenanzahl enthalten sein. Zudem können noch
Zusatzinformationen gespeichert werden. Dies könnten beispielsweise Informationen über
Weiterbildungen, Zusatzqualifikationen oder Fehlzeiten sein.
Jeder Lehrer darf außerdem nur für bestimmte Fächer in bestimmten Schulstufen eingesetzt
werden.
Schließlich werden noch Informationen über getätigte Investitionen gespeichert. Dies
beinhaltet, wie viel Geld wofür ausgegeben wurde.
4
3. Entity- Relationship- Modell
3.1. Entity- Relationship- Diagramm
5
3.2. Grundkonzepte des Entity- Relationship- Modells
3.2.1. Entities
Entities sind die Informationseinheiten, die in der Datenbank dargestellt werden sollen. Ein
Entity ist also ein Objekt, über das bestimmte Informationen gespeichert werden sollen.
Entities werden in unterschiedliche Entity- Typen eingeteilt. Für einen Entity- Typen E lässt
sich die Semantik folgendermaßen definieren:
(1)
(E) Menge der möglichen Entities vom Typ E
(2)
(E) Menge der aktuellen Entities vom Typ E
Dabei muss gelten: (E) ⊆ (E).
Im ER- Modell werden Entity- Typen graphisch als Rechtecke dargestellt. Ein Beispiel dafür
ist der Entity- Typ KLASSE im zuvor angegebenen ER- Diagramm:
KLASSE
3.2.2. Relationships
Relationships dienen dazu, die Beziehungen zwischen Entities darzustellen.
Relationships werden in Relationship- Typen eingeteilt. An einem Relationship- Typ können
n
2 Entity- Typen beteiligt sein, die zueinander in Beziehung stehen. Für einen
Realtionship- Typen R lassen sich die möglichen bzw. die aktuellen Relationships
folgendermaßen definieren:
(1)
(R) Menge der möglichen Relationships vom Typ R
Es gilt: (R) =
(2)
(E1) x … x (En).
(R) Menge der aktuellen Relationships vom Typ R
Es gilt: (R) =
(E1) x … x (En).
Es muss gelten: (R) ⊆ (R).
Im ER- Modell werden Relationship- Typen graphisch als Raute dargestellt. Ein RelationshipTyp ist beispielsweise „bekommt_Note“, an dem die Entity- Typen SCHÜLER und FACH
beteiligt sind. Dies sieht in der graphischen Notation so aus:
6
3.2.3. Attribute
Attribute sind die Eigenschaften von Entities oder von Relationships.
Alle Entities desselben Typs bzw. alle Relationships desselben Typs besitzen dieselben
Attribute. Die Deklaration von Attributen erfolgt also für Entity- Typen bzw. für
Relationship- Typen. Die Semantik einer Attributdeklaration sieht nun folgendermaßen aus:
Im Zustand
wird jedem aktuellen Entity bzw. Relationship für ein Attribut A ein Wert eines
Datentyps D zugeordnet
(A):
(E)
(D) bzw.
(A):
(R)
(D)
Die graphische Darstellung sieht z.B. für den Entity- Typ PERSON mit den Attributen Name,
Geburtsdatum, Anschrift und Telefon folgendermaßen aus:
7
3.3. Weitere Konzepte des Entity- Relationship- Modells
3.3.1. Funktionale Beziehungen
Funktionale Beziehungen sind spezielle Relationship- Typen, die eine eindeutige Zuordnung
eines Entity- Typs zu einem anderen Entity- Typ darstellen. Jeder Instanz eines Entity- Typen
E1 ist maximal eine Instanz eines Entity- Typen E2 zugeordnet
Man unterscheidet partiell funktionale und total funktionale Beziehungen. Bei partiell
funktionalen Beziehungen ist jeder Instanz eines Entity- Typen E1 eine oder keine Instanz
eines Entity- Typen E2 zugeordnet ist. Bei einer total funktionalen Beziehung ist jeder Instanz
eines Entity- Typen E1 genau eine Instanz eines Entity- Typen E2 zugeordnet.
In der graphischen Darstellung werden funktionale Beziehungen durch einen Pfeil dargestellt.
Für den Entity- Typ SCHÜLER, der zu dem Entity- Typ KLASSE durch den RelationshipTyp „gehört_zu“ in einer funktionalen Beziehung steht, sieht dies nun so aus:
3.3.2. Schlüssel
Als Schlüssel bezeichnet man eine Attributmenge, die eine eindeutige Identifizierung eines
Entities eines bestimmten Entity- Typs darstellt. Es dürfen also keine zwei Entities existieren,
die sich in allen Werten ihrer Schlüsselattribute gleichen.
Für einen Entity- Typ gibt es teilweise mehrere Schlüssel. In diesem Fall kann einer der
Schlüssel ausgewählt werden, der als Primärschlüssel bezeichnet wird.
Schlüsselattribute werden dadurch gekennzeichnet, dass sie unterstrichen werden. Der EntityTyp PERSON soll z.B. als Primärschlüssel die Attribute Name und Anschrift haben:
8
3.3.3. IST- Beziehungen
Die IST- Beziehung ist eine spezielle funktionale Beziehung. Von IST- Beziehung spricht
man, wenn ein Entity- Typ Teilmenge eines anderen Entity- Typs ist. Beispielsweise ist in
dem zuvor angegebenen ER- Diagramm jeder Mitarbeiter eine Person. Jeder
MITARBEITER- Instanz ist genau eine PERSON- Instanz zugeordnet. Allerdings ist nicht
jede Person ein Mitarbeiter.
Wenn ein Entity- Typ A zu einem anderen Entity- Typ B in einer IST- Beziehung steht, erbt
A zudem alle Attribute von B. In diesem Fall bezeichnet man A als Spezialisierung von B,
während B eine Generalisierung von A ist. Deshalb spricht man auch von Spezialisierungs-/
Generalisierungsbeziehung.
In der graphischen Notation werden IST- Beziehungen durch einen Pfeil dargestellt:
PERSON
ist
MITARBEITER
3.3.4. Kardinalitäten
Kardinalitäten geben an, wie oft eine Instanz eines Entity- Typen an einer Relationship
teilnehmen kann. Dazu werden ein minimaler sowie ein maximaler Wert angegeben.
Ein Lehrer soll z.B. minimal ein Fach und maximal beliebig viele Fächer unterrichten dürfen
und ein Fach soll von null (wenn keine Lehrer für dieses Fach verfügbar ist) bis beliebig
vielen Lehrern unterrichtet werden dürfen. Dies sieht in der textuellen Notation dann
folgendermaßen aus:
darf_unterrichten (LEHRER[1, *], FACH[0, *])
9
3.4. Textuelle Notation
3.4.1. Entity- Typen
3.4.1.1. PERSON (Name, Anschrift, Geburtsdatum, Telefonnummer)
Für jede Person werden der Name, die Anschrift, das Geburtsdatum und die Telefonnummer
gespeichert. Dabei sind Name und Anschrift die Schlüsselattribute, durch die sich ein Entity
vom Entity- Typ PERSON eindeutig identifizieren lassen muss.
3.4.1.2. SCHÜLER (Name, Anschrift, Geburtsdatum, Telefonnummer, Schülernummer,
Name_Mutter, Name_Vater, Anschrift_Sorgeberechtigter)
Der Entity- Typ SCHÜLER erbt die Attribute Name, Anschrift, Geburtsdatum und
Telefonnummer von PERSON, da jeder Schüler auch eine Person ist. Zudem werden für
jeden Schüler noch eine Schülernummer, der Name der Mutter, der Name des Vaters und die
Anschrift des Sorgeberechtigten gespeichert. SCHÜLER erbt den durch die Schlüsselattribute
Name und Anschrift definierten Primarschlüssel von PERSON.
3.4.1.3. MITARBEITER (Name, Anschrift, Geburtsdatum, Telefonnummer,
Personalnummer, Funktion, Einstellungsdatum, Gehaltsinformation, Stundenanzahl)
Der Entity- Typ MITARBEITER erbt ebenfalls die Attribute Name, Anschrift, Geburtsdatum
und Telefonnummer von PERSON, da auch jeder Mitarbeiter eine Person ist. Zudem werden
für jeden Mitarbeiter eine Personalnummer, seine Funktion an der Schule (z.B. Lehrer,
Hausmeister, Sekretärin,…), das Datum der Einstellung, Informationen über das Gehalt und
die Anzahl der Arbeitsstunden gespeichert. MITARBEITER erbt analog zu SCHÜLER den
Primärschlüssel in Form der Schlüsselattribute Name und Anschrift von PERSON.
3.4.1.4. LEHRER (Name, Anschrift, Geburtsdatum, Telefonnummer, Personalnummer,
Funktion, Einstellungsdatum, Gehaltsinformation, Stundenanzahl)
Der Entity- Typ LEHRER erbt alle seine Attribute von MITARBEITER, da jeder Lehrer
gleichzeitig ein Mitarbeiter ist. Zusätzliche Attribute gibt es für LEHRER nicht.
3.4.1.5. FACH (Fachname)
Für jedes Fach, das an der Schüle unterrichtet wird, soll der Fachname gespeichert werden.
Dies ist gleichzeitig das Schlüsselattribut.
10
3.4.1.6. RAUM (Raumnummer, Kapazität)
Für jeden Raum sollen die Raumnummer und die mögliche Kapazität des Raumes gespeichert
werden. Das Schlüsselattribut soll dabei die Raumnummer sein.
3.4.1.7. KLASSE (Klassenbezeichnung, Schüleranzahl)
Für jede Klasse sollen eine Bezeichnung und die Anzahl der Schüler der Klasse gespeichert
werden. Das Schlüsselattribut soll die Klassenbezeichnung sein.
3.4.1.8. INVESTITION (Eintragsnummer, Betrag, Zweck, Bereich)
Für an der Schule getätigte Investitionen sollen eine Eintragsnummer, der Betrag, der Zweck
der Investition und der Bereich, unter den die Investition fällt, gespeichert werden. Als
Schlüsselattribut soll die Eintragsnummer ausgewählt werden.
3.4.1.9. INFORMATIONEN (Eintrag)
Einträge können beispielsweise Fehleinträge, Weiterbildungen oder zusätzliche
Qualifikationen für Lehrer oder Förderungsmaßnahmen für Schüler sein.
3.4.1.10. HALBJAHR (Halbjahrname)
Alle Halbjahre, für die z.B. Unterrichts- oder Noteninformationen gespeichert werden,
werden durch einen Namen identifiziert.
3.4.1.11. SCHULSTUFE (Schulstufenname)
Alle benötigten Schulstufen werden durch einen Namen identifiziert.
3.4.1.12. ZEIT (Zeitangabe)
Die betrachteten Zeiten werden durch eine Zeitangabe identifiziert.
3.4.2. Relationship- Typen
3.4.2.1. bekommt_Note (SCHÜLER[0, *], FACH[0,*], HALBJAHR[0,*], Note)
Schüler bekommen in einem jeweiligen Fach für ein bestimmtes Halbjahr eine Note.
11
3.4.2.2. gehört_zu (SCHÜLER[1,1], KLASSE[0,*])
Jeder Schüler gehört zu genau einer bestimmten Klasse; „gehört_zu“ ist also eine total
funktionale Beziehung.
3.4.2.3. darf_unterrichten (LEHRER[1,*], FACH[0,*], SCHULSTUFE[0,*])
Ein Lehrer darf bestimmte Fächer in bestimmten Schulstufen unterrichten. Jeder Lehrer soll
dabei allerdings mindestens ein Fach unterrichten dürfen.
3.4.2.4. unterrichtet (LEHRER[0,*], KLASSE[0,*], FACH[0,*], RAUM[0,*], ZEIT[0,*])
Lehrer unterrichten Klassen in Fächern in Räumen zu bestimmten Zeiten. Bei diesem
Relationship- Typ werden Unterrichtsausfall und Vertretung berücksichtigt. Es werden also
für jeden Tag die Informationen über den erteilten Unterricht gespeichert.
3.4.2.5. unterrichtet_planmäßig (LEHRER[0,*], KLASSE[0,*], FACH[0,*],
RAUM[0,*], ZEIT[0,*])
Lehrer unterrichten Klassen in Fächern in Räumen zu bestimmten Zeiten. Dieser
Relationship- Typ dient zur Darstellung des offiziellen Stundenplans. Es muss also bei der
Zeitangabe statt Datum und Stunde nur Wochentag und Stunde gespeichert werden.
3.4.2.6. hat_Unterricht (KLASSE[0,*], FACH[0,*], HALBJAHR[0,*], Stundenanzahl)
Eine bestimmte Klasse hat in einem bestimmten Fach in einem bestimmten Halbjahr eine
bestimmte Anzahl an Stunden.
3.4.2.7. hat_Klassenlehrer (KLASSE[1,1], LEHRER[0,1])
Eine Klasse hat genau einen Klassenlehrer und jeder Lehrer ist entweder in einer oder in
keiner Klasse als Klassenlehrer eingesetzt.
3.4.2.8. hat_Klassenraum (KLASSE[1,1], RAUM[0,1])
Eine Klasse hat genau einen Klassenraum und jeder Raum ist entweder Klassenraum einer
einzigen Klasse oder kein Klassenraum.
3.4.2.9. wird_autorisiert (INVESTITION[1,1], MITARBEITER[0,*])
Eine Investition wird von einem Mitarbeiter (z.B. dem Schulleiter) autorisiert.
12
3.4.2.10. info (PERSON[0,*], INFORMATIONEN[0,*])
Über Personen werden bestimmte Informationen gespeichert.
3.4.2.11. ist (MITARBEITER[1,1], PERSON[0,1])
Jeder Mitarbeiter ist gleichzeitig eine Person. MITARBEITER ist also eine Spezialisierung
von PERSON.
3.4.2.12. ist (SCHÜLER[1,1], PERSON[0,1])
Jeder Schüler ist gleichzeitig eine Person. SCHÜLER ist also auch eine Spezialisierung von
PERSON.
3.4.2.13. ist (LEHRER[1,1], MITARBEITER[0,1])
Jeder Lehrer ist gleichzeitig ein Mitarbeiter. LEHRER ist also eine Spezialisierung von
MITARBEITER.
13
4. Relationenmodell
Im Relationenmodell werden die Daten als Tabellen dargestellt. Dazu werden verschiedene
Relationenschemata erstellt. Jedes Relationenschema besteht aus einer Menge von Attributen.
Diese Attribute sind die Spaltenüberschriften der jeweiligen Tabelle und jedem Attribut wird
ein Wertebereich zugeordnet.
Die Menge der Zeilen der Tabelle bezeichnet man als Relation und eine Tabellenzeile als
Tupel. Für ein Tupel wird jedem Attribut ein Attributwert zugeordnet.
Das Datenbankschema besteht nun aus einer Menge von Relationenschemata und die
Datenbank aus einer Menge von Relationen.
Im Folgenden werden nun die Relationenschemata mit den dazugehörigen Attributen erstellt.
4.1. Relationenschemata
4.1.1. Repräsentation der Entity-Typen
4.1.1.1. SCHÜLER (Name, Anschrift, Geburtsdatum, Telefonnummer, Schülernummer,
Name_Mutter, Name_Vater, Anschrift_Sorgeberechtigter, Klassenbezeichnung)
Die Attribute des Entity- Typs SCHÜLER werden für dieses Relationenschema übernommen.
Da jedem Schüler durch die total funktionale Beziehung „gehört_zu“ genau eine Klasse
zugeordnet ist, wird zudem das Attribut Klassenbezeichnung hinzugefügt.
Als Primärschlüssel für SCHÜLER soll das Attribut Schülernummer ausgewählt werden, da
die Attributmenge für diesen Schlüssel nur ein Attribut enthält und somit kleiner ist als die
Attributmenge des durch die Attribute Name und Anschrift definierten Schlüssels.
4.1.1.2. MITARBEITER (Name, Anschrift, Geburtsdatum, Telefonnummer,
Personalnummer, Funktion, Einstellungsdatum, Gehaltsinformation, Stundenanzahl)
Für dieses Relationenschema werden alle Attribute des Entity- Typs MITARBEITER
übernommen.
Analog zu SCHÜLER wird als Primärschlüssel die Personalnummer anstelle von Name und
Anschrift ausgewählt.
14
4.1.1.3. KLASSE (Klassenbezeichnung, Klassenraum, Personalnummer, Schüleranzahl)
Die Attribute Klassenbezeichnung und Schüleranzahl werden vom Entity- Typ KLASSE
übernommen.
Zudem werden die Attribute Klassenraum und Personalnummer eingeführt. Personalnummer
identifiziert einen Entity vom Typ LEHRER, der mit dem Entity- Typ KLASSE durch den
Relationship- Typ „hat_Klassenlehrer“ in Beziehung steht. Klassenraum repräsentiert das
Schlüsselattribut Raumnummer des Entity- Typs RAUM, der mit dem Entity- Typ KLASSE
durch den Relationship- Typ „hat_Klassenraum“ in Beziehung steht. Dadurch, dass diese
beiden Attribute in dem Relationenschema auftauchen, werden für „hat_Klassenlehrer“ sowie
„hat_Klassenraum“ keine eigenen Relationenschemata mehr benötigt.
4.1.1.4. RAUM (Raumnummer, Kapazität)
Die Attribute Raumnummer und Kapazität werden vom Entity- Typ RAUM übernommen.
4.1.1.5. INVESTITION (Eintragsnummer, Betrag, Zweck, Bereich, Personalnummer)
Die Attribute Eintragsnummer, Betrag, Zweck und Bereich werden vom Entity- Typ
INVESTITION übernommen.
Jeder Investition wird durch den Relationship- Typ „wird_autorisiert“ genau ein Mitarbeiter
zugeordnet. Dieser Mitarbeiter wird durch das Attribut Personalnummer identifiziert.
4.1.1.6. Folgende Entity- Typen wurden nicht ins Relationenmodell übernommen:
- PERSON wird nicht aufgenommen, da alle Personen, über die Informationen gespeichert
werden, entweder Schüler oder Mitarbeiter sind.
- LEHRER wird dadurch repräsentiert, dass ein Entity vom Typ MITARBEITER für das
Attribut Funktion den Wert Lehrer hat.
- FACH
- INFORMATIONEN
- HALBJAHR
- SCHULSTUFE
- ZEIT
Diese Entity- Typen werden nur zur Darstellung von Relationships benötigt. Sie besitzen
jeweils nur ein Attribut. Dieses Attribut taucht in Relationenschemata auf, welche
Relationship- Typen repräsentieren.
15
4.1.2. Repräsentation der Relationships
4.1.2.1. BEKOMMT_NOTE (Schülernummer, Fachname, Halbjahrname, Note)
BEKOMMT_NOTE enthält zum einen das Attribut Note und zum anderen die Attribute
Schülernummer, Fachname und Halbjahrname, die Entities der drei am Relationship- Typ
„bekommt_Note“ beteiligten Entity- Typen SCHÜLER, FACH und HALBJAHR jeweils
eindeutig identifizieren. Ein Tupel von BEKOMMT_NOTE lässt sich durch die
Schülernummer, den Fachnamen und den Halbjahrnamen eindeutig bestimmen.
4.1.2.2. DARF_UNTERRICHTEN (Personalnummer, Fachname, Schulstufenname)
DARF_UNTERRICHTEN enthält die Attribute Personalnummer, Fachname und
Schulstufenname, durch die Entities der am Relationship- Typ „darf_unterrichten“ beteiligten
Entity- Typen LEHRER, FACH und SCHULSTUFE jeweils eindeutig identifiziert werden
können. Ein Tupel der Relation lässt sich durch die Personalnummer, den Fachnamen und den
Schulstufennamen eindeutig bestimmen.
4.1.2.3. UNTERRICHTET (Personalnummer, Klassenbezeichnung, Fachname,
Raumnummer, Zeitangabe)
UNTERRICHTET enthält die Schlüsselattribute der am Relationship- Typ „unterrichtet“
beteiligten Entity- Typen KLASSE (Klassenbezeichnung), FACH (Fachname), RAUM
(Raumnummer) und ZEIT (Zeitangabe) sowie das Attribut Personalnummer, durch das sich
ein Lehrer eindeutig bestimmen lässt. Ein Tupel der Relation wird durch die Raumnummer
und die Zeitangabe eindeutig identifiziert.
4.1.2.4. UNTERRICHTET_PLANMÄSSIG (Personalnummer, Klassenbezeichnung,
Fachname, Raumnummer, Zeitangabe)
UNTERRICHTET_PLANMÄSSIG enthält die Schlüsselattribute der am Relationship- Typ
„unterrichtet_planmäßig“ beteiligten Entity- Typen KLASSE (Klassenbezeichnung), FACH
(Fachname), RAUM (Raumnummer) und ZEIT (Zeitangabe) sowie das Attribut
Personalnummer, durch das sich ein Lehrer eindeutig bestimmen lässt. Ein Tupel der Relation
wird durch die Raumnummer und die Zeitangabe eindeutig identifiziert.
16
4.1.2.5. HAT_UNTERRICHT(Klassenbezeichnung, Fachname, Halbjahrname,
Stundenanzahl)
HAT_UNTERRICHT enthält die Schlüsselattribute der am Relationship- Typ
„hat_Unterricht“ beteiligten Entity- Typen KLASSE, FACH und HALBJAHR sowie das
Attribut Stundenanzahl. Ein Tupel der Relation wird durch die Klassenbezeichnung, den
Fachnamen und den Halbjahrnamen eindeutig identifiziert.
4.1.2.6. SCHÜLERINFO (Schülernummer, Eintrag)
Schülerinfo enthält die Attribute Schülernummer und Eintrag. Schülernummer repräsentiert
den betreffenden Schüler, während Eintrag die Information repräsentiert.
4.1.2.7. MITARBEITERINFO (Personalnummer, Eintrag)
Mitarbeiterinfo enthält die Attribute Mitarbeiternummer und Eintrag. Mitarbeiternummer
repräsentiert den betreffenden Mitarbeiter, während Eintrag die Information repräsentiert.
4.1.2.8. Folgende Relationship- Typen wurden nicht ins Relationenmodell übernommen:
- gehört_zu (SCHÜLER, KLASSE) wird dadurch repräsentiert, dass in SCHÜLER das
Attribut Klassenbezeichnung enthalten ist.
- hat_Klassenlehrer (KLASSE, LEHRER) wird dadurch repräsentiert, dass in KLASSE das
Attribut Personalnummer enthalten ist, durch welches der Klassenlehrer identifiziert wird.
- hat_Klassenraum (KLASSE, RAUM) wird dadurch repräsentiert, dass in KLASSE das
Attribut Klassenraum enthalten ist.
- wird_autorisiert (INVESTITION, MITARBEITER) wird dadurch repräsentiert, dass in
INVESTITION das Attribut Personalnummer enthalten ist, durch welches der Mitarbeiter
identifiziert wird, der die Investition autorisiert hat.
Ebenfalls nicht zu übernehmen braucht man die drei IST- Beziehungen:
- ist (MITARBEITER, PERSON)
- ist (SCHÜLER, PERSON) und
- ist (LEHRER, MITARBEITER)
17
4.2. Integritätsbedingungen
Es existieren bestimmte zusätzliche Anforderungen an Datenbanken, die gewährleistet
werden müssen. Diese bezeichnet man als Integritätsbedingungen.
Folgende Integrationsbedingungen sollen in dem erstellten Relationenmodell gelten:
4.2.1. SCHÜLER
a) Keine zwei Schüler dürfen dieselbe Schülernummer haben.
∀ S1, S2 ∈ SCHÜLER (S1.Schülernummer = S2.Schülernummer
S1 = S2)
b) Keine zwei Schüler dürfen denselben Namen und dieselbe Anschrift haben.
∀ S1, S2 ∈ SCHÜLER (S1.Name = S2.Name ∧ S1.Anschrift = S2.Anschrift
S1 = S2)
c) Jede Klassenbezeichnung, die in SCHÜLER vorkommt, muss auch in KLASSE vorkommen.
∀ S ∈ SCHÜLER ∃ K ∈ KLASSE (S.Klassenbezeichnung = K.Klassenbezeichnung)
4.2.2. MITARBEITER
a) Keine zwei Mitarbeiter dürfen dieselbe Personalnummer haben.
∀ M1, M2 ∈ MITARBEITER (M1.Personalnummer = M2.Personalnummer
M1 = M2)
b) Keine zwei Mitarbeiter dürfen denselben Namen und dieselbe Anschrift haben.
∀ M1, M2 ∈ MITARBEITER (M1.Name = M2.Name ∧ M1.Anschrift = M2.Anschrift
M1 = M2)
c) Jeder Lehrer ist maximal Klassenlehrer einer Klasse.
∀ M ∈ MITARBEITER ∀ K1, K2 ∈ KLASSE (M.Personalnummer = K1.Personalnummer ∧
M.Personalnummer = K2.Personalnummer
K1 = K2)
d) Jeder Lehrer darf mindestens ein Fach unterrichten.
∀ M ∈ MITARBEITER mit M.Funktion = Lehrer ∃ DU ∈ DARF_UNTERRICHTEN
(M.Personalnummer = DU.Personalnummer)
e) Die Stundenanzahl eines Mitarbeiters muss größer als 0 sein.
∀ M ∈ MITARBEITER (M.Stundenanzahl > 0)
4.2.3. KLASSE
a) Keine zwei Klassen dürfen dieselbe Klassenbezeichnung haben.
∀ K1, K2 ∈ KLASSE (K1.Klassenbezeichnung = K2.Klassenbezeichnung
K1 = K2)
b) Es dürfen nur Mitarbeiter Klassenlehrer sein, deren Funktion die eines Lehrers ist.
∀ K ∈ KLASSE ∀ M ∈ MITARBEITER (K.Personalnummer = M.Personalnummer
M.Funktion = Lehrer)
c) Jede Personalnummer, die in KLASSE vorkommt, muss auch in MITARBEITER vorkommen.
∀ K ∈ KLASSE ∃ M ∈ MITARBEITER (K.Personalnnummer = M.Personalnummer)
d) Jeder Klassenraum, der in KLASSE vorkommt, muss auch in RAUM als Raumnummer existieren.
∀ K ∈ KLASSE ∃ R ∈ RAUM (K.Klassenraum = R.Raumnummer)
e) Die Kapazität des Klassenraums muss mindestens so groß wie die Schüleranzahl der Klasse sein.
∀ K ∈ KLASSE ∀ R ∈ RAUM (K.Klassenraum = R.Raumnummer
R.Kapazität
K.Schüleranzahl)
18
f) Die Schüleranzahl einer Klasse muss größer als 0 sein.
∀ K ∈ KLASSE (K.Schüleranzahl > 0)
4.2.4. RAUM
a) Keine zwei Räume dürfen dieselbe Raumnummer haben.
∀ R1, R2 ∈ RAUM (R1.Raumnummer = R2.Raumnummer
R1 = R2)
b) Jeder Raum ist maximal Klassenraum einer Klasse.
∀ R ∈ RAUM ∀ K1, K2 ∈ KLASSE (R.Raumnummer = K1.Klassenraum ∧
R.Raumnummer = K2.Klassenraum
K1 = K2)
c) Die Raumkapazität muss größer als 0 sein.
∀ R ∈ RAUM (R.Kapazität > 0)
4.2.5. INVESTITIONEN
a) Keine zwei Investitionen dürfen dieselbe Eintragsnummer haben.
∀ I1, I2 ∈ INVESTITION (I1.Eintragsnummer = I2.Eintragsnummer
I1 = I2)
b) Jede Personalnummer, die in INVESTITION vorkommt, muss auch in MITARBEITER als Personalnummer
vorkommen.
∀ I ∈ INVESTITION ∃ M ∈ MITARBEITER (I.Personalnnummer = M.Personalnummer)
4.2.6. BEKOMMT_NOTE
a) Keine zwei Einträge in BEKOMMT_NOTE dürfen sich in Schülernummer, Fachname und Halbjahr gleichen.
∀ BN1, BN2 ∈ BEKOMMT_NOTE (BN1.Schülernummer = BN2.Schülernummer ∧
BN1.Fachname = BN2.Fachname ∧ BN1.Halbjahr = BN2.Halbjahr
BN1 = BN2)
b) Jede Schülernummer, die in BEKOMMT_NOTE vorkommt, muss auch in SCHÜLER vorkommen.
∀ BN ∈ BEKOMMT_NOTE ∃ S ∈ SCHÜLER (BN.Schülernummer = S.Schülernummer)
c) Jeder Schüler, der in einem Fach eine Note bekommt, muss in dem Fach auch unterrichtet werden.
∀ BN ∈ BEKOMMT_NOTE ∃ S ∈ SCHÜLER ∃ HU ∈ HAT_UNTERRICHT (BN.Schülernummer =
S.Schülernummer ∧ S.Klassenbezeichnung = HU.Klassenbezeichnung ∧ BN.Fachname = HU.Fachname)
d) Die Note muss zwischen 1 und 6 liegen.
∀ BN ∈ BEKOMMT_NOTE (1
BN.Note
6)
4.2.7. DARF_UNTERRICHTEN
a) Keine zwei Einträge in DARF_UNTERRICHTEN dürfen sich in Personalnummer, Fachname und Schulstufe
gleichen.
∀ DU1, DU2 ∈ DARF_UNTERRICHTEN (DU1.Personalnummer = DU2.Personalnummer ∧
DU1.Fachname = DU2.Fachname ∧ DU1.Halbjahr = DU2.Halbjahr
DU1 = DU2)
b) Es dürfen nur Mitarbeiter unterrichten, deren Funktion die eines Lehrers ist.
∀ DU ∈ DARF_UNTERRICHTEN ∀ M ∈ MITARBEITER
(DU.Personalnummer = M.Personalnummer
M.Funktion = Lehrer)
c) Jede Personalnummer in DARF_UNTERRICHTEN muss auch in MITARBEITER vorkommen.
19
∀ DU ∈ DARF_UNTERRICHTEN ∃ M ∈ MITARBEITER (DU.Personalnummer = M.Personalnummer)
4.2.8. UNTERRICHTET
a) Keine zwei Einträge in UNTERRICHTET dürfen sich in Raumnummer und Zeitangabe gleichen.
∀ U1, U2 ∈ UNTERRICHTET (U1.Raumnummer = U2.Raumnummer ∧
U1.Zeitangabe = U2.Zeitangabe
U1 = U2)
b) Keine zwei Einträge in UNTERRICHTET dürfen sich in Personalnummer und Zeitangabe gleichen.
∀ U1, U2 ∈ UNTERRICHTET (U1.Personalnummer = U2.Personalnummer ∧
U1.Zeitangabe = U2.Zeitangabe
U1 = U2)
c) Jede Personalnummer, die in UNTERRICHTET vorkommt, muss auch in MITARBEITER vorkommen.
∀ U ∈ UNTERRICHTET ∃ M ∈ MITARBEITER (U.Personalnummer = M.Personalnummer)
d) Es dürfen nur Mitarbeiter unterrichten, deren Funktion die eines Lehrers ist.
∀ U ∈ UNTERRICHTET ∀ M ∈ MITARBEITER (U.Personalnummer = M.Personalnummer
M.Funktion = Lehrer)
e) Jede Klassenbezeichnung, die in UNTERRICHTET vorkommt, muss auch in KLASSE vorkommen.
∀ U ∈ UNTERRICHTET ∃ K ∈ KLASSE (U.Klassenbezeichnung = K.Klassenbezeichnung)
f) Jede Raumnummer, die in UNTERRICHTET vorkommt, muss auch in RAUM existieren.
∀ U ∈ UNTERRICHTET ∃ R ∈ RAUM (U.Raumnummer = R.Raumnummer)
g) Jeder Raum muss eine jeweils ausreichende Kapazität haben.
∀ U ∈ UNTERRICHTET ∀ R ∈ RAUM ∀ K ∈ KLASSE (U.Raumnummer = R.Raumnummer ∧
U.Klassenbezeichnung = K.Klassenbezeichnung
R.Kapazität > K.Schüleranzahl)
4.2.9. UNTERRICHTET_PLANMMÄSSIG
Die Bedingungen für UNTERRICHTET_PLANMÄSSIG gleichen denen für UNTERRICHTET. Zusätzlich gilt
noch folgende Bedingung:
h) Ein Lehrer, der ein Fach nach Plan unterrichtet, soll das Fach auch unterrichten dürfen.
∀ UP ∈ UNTERRICHTET_PLANMÄSSIG ∃ DU ∈ DARF_UNTERRICHTEN
(UP.Personalnummer = DU.Personalnummer ∧ UP.Fachname = DU.Fachname)
4.2.10. HAT_UNTERRICHT
a) Keine zwei Einträge in HAT_UNTERRICHT dürfen sich in Klassenbezeichnung, Fachname und Halbjahr
gleichen.
∀ HU1, HU2 ∈ HAT_UNTERRICHT (HU1.Klassenbezeichnung = HU2.Klassenbezeichnung ∧
HU1.Fachname = HU2.Fachname ∧ HU1.Halbjahr = HU2.Halbjahr
HU1 = HU2)
b) Jede Klassenbezeichnung, die in HAT_UNTERRICHT vorkommt, muss auch in KLASSE vorkommen.
∀ HU ∈ HAT_UNTERRICHT ∃ K ∈ KLASSE (HU.Klassenbezeichnung = K.Klassenbezeichnung)
4.2.11. SCHÜLERINFO
a) Keine zwei Schülerinformationen dürfen dieselbe Schülernummer und denselben Eintrag haben.
∀ SI1, SI2 ∈ SCHÜLERINFO (SI1.Schülernummer=SI2.Schülernummer ∧ SI1.Eintrag=SI2.Eintrag
SI1=SI2)
20
b) Jede Schülernummer, die in SCHÜLERINFO vorkommt, muss auch in SCHÜLER vorkommen.
∀ SI ∈ SCHÜLERINFO ∃ M ∈ SCHÜLER (SI.Schülernnummer = M.Schülernummer)
4.2.12. MITARBEITERINFO
a) Keine zwei Mitarbeiterinformationen dürfen dieselbe Personalnummer und denselben Eintrag haben.
∀ MI1, MI2 ∈ MITARBEITERINFO (M1.Personalnummer = MI2.Personalnummer ∧
MI1.Eintrag = MI2.Eintrag
MI1 = MI2)
b) Jede Personalnummer, die in MITARBEITERINFO vorkommt, muss auch in MITARBEITER vorkommen.
∀ MI ∈ MITARBEITERINFO ∃ M ∈ MITARBEITER (MI.Personalnummer = M.Personalnummer)
21
5. SQL (Standard Query Language)
5.1. Relationenschemata
Auf der konzeptionellen Ebene lassen sich mit SQL Relationenschemata für Basisrelationen
definieren. Hier sollen nun die zuvor entworfenen Relationenschemata in SQL erstellt
werden.
Das create table- Kommando erzeugt ein Relationenschema mit einem bestimmten Namen
sowie die zu diesem Schema gehörigen Attribute, denen jeweils ein bestimmter Wertebereich
zugeordnet wird.
a) SCHÜLER
create table Schüler
( Schülernummer char(6) ,
Name varchar(50) ,
Anschrift varchar(50) ,
Geburtsdatum date ,
Telefonnummer varchar(10) ,
Name_Mutter varchar(50) ,
Name_Vater varchar(50) ,
Anschrift_Sorgeberechtigter varchar(50) ,
Klassenbezeichnung varchar(6) ,
primary key (Schülernummer) ,
foreign key (Klassenbezeichnung) references Klasse (Klassenbezeichnung) )
b) MITARBEITER
create table Mitarbeiter
( Personalnummer char(6) ,
Name varchar(50) ,
Anschrift varchar(50) ,
Geburtsdatum date ,
Telefonnummer varchar(10) ,
Funktion varchar(30) ,
Einstellungsdatum date ,
Gehaltsinformation varchar(10) ,
Stundenanzahl smallint check (Stundenanzahl > 0) ,
primary key (Personalnummer) )
22
c) KLASSE
create table Klasse
( Klassenbezeichnung varchar(6) ,
Personalnummer char(6) ,
Klassenraum varchar(6) ,
Schüleranzahl smallint check (Schüleranzahl > 0) ,
primary key (Klassenbezeichnung) ,
foreign key (Personalnummer) references Mitarbeiter (Personalnummer),
foreign key (Klassenraum) references Raum (Raumnummer) )
d) RAUM
create table Raum
( Raumnummer varchar(6) ,
Kapazität smallint check (Kapazität > 0) ,
primary key (Raumnummer) )
e) INVESTITION
create table Investition
( Eintragsnummer varchar(6) ,
Betrag int ,
Zweck varchar(80) ,
Bereich varchar(30) ,
Personalnummer char(6) ,
primary key (Eintragsnummer) ,
foreign key (Personalnummer) references Mitarbeiter (Personalnummer) )
f) BEKOMMT_NOTE
create table bekommt_Note
( Schülernummer char(6) ,
Fachname varchar(25) ,
Halbjahr varchar(6) ,
Note smallint check (Note between 1 and 6) ,
primary key (Schülernummer, Fachname, Halbjahr) ,
foreign key (Schülernummer) references Schüler (Schülernummer) )
g) DARF_UNTERRICHTEN
create table darf_unterrichten
( Personalnummer char(6) ,
Fachname varchar(25) ,
Schulstufe varchar(10) ,
primary key (Personalnummer, Fachname, Schulstufe) ,
23
foreign key (Personalnummer) references Mitarbeiter (Personalnummer) )
h) UNTERRICHTET
create table unterrichtet
( Personalnummer char(6) ,
Klassenbezeichnung varchar(6) ,
Fachname varchar(25) ,
Raumnummer varchar(6) ,
Zeitangabe varchar(25) ,
primary key (Raumnummer, Zeitangabe) ,
foreign key (Personalnummer) references Mitarbeiter (Personalnummer) ,
foreign key (Klassenbezeichnung) references Klasse (Klassenbezeichnung) ,
foreign key (Raumnummer) references Raum(Raumnummer) )
i) UNTERRICHTET_PLANMÄßIG
create table unterrichtet_planmäßig
( Personalnummer char(6) ,
Klassenbezeichnung varchar(6) ,
Fachname varchar(25) ,
Raumnummer varchar(6) ,
Zeitangabe varchar(25) ,
primary key (Raumnummer, Zeitangabe) ,
foreign key (Personalnummer) references Mitarbeiter (Personalnummer) ,
foreign key (Klassenbezeichnung) references Klasse (Klassenbezeichnung) ,
foreign key (Raumnummer) references Raum(Raumnummer) )
j) HAT_UNTERRICHT
create table hat_Unterricht
( Klassenbezeichnung varchar(6) ,
Fachname varchar(25) ,
Halbjahr varchar(6) ,
Stundenanzahl smallint ,
primary key (Klassenbezeichnung, Fachname, Halbjahr) ,
foreign key (Klassenbezeichnung) references Klasse (Klassenbezeichnung) )
k) SCHÜLERINFO
create table Schülerinfo
( Schülernummer char(6) ,
Eintrag varchar(200) ,
primary key (Schülernummer, Eintrag)
24
foreign key (Schülernummer) references Schüler (Schülernummer) )
l) MITARBEITERINFO
create table Schülerinfo
( Personalnummer char(6) ,
Eintrag varchar(200) ,
primary key (Personalnummer, Eintrag)
foreign key (Personalnummer) references Mitarbeiter (Personalnummer) )
Mit dem alter table- Kommando lässt sich eine Spalte zu einem Relationenschema
hinzufügen, z.B.:
alter table Mitarbeiter
add Stundenanzahl
Mit dem drop table- Kommando lässt sich ein Relationenschema wieder löschen, z.B.:
drop table Raum
25
5.2. Änderungsoperationen
Um nun auch Daten in die Datenbank einzufügen, bzw. die eingefügten Daten zu verändern
oder wieder zu löschen, werden gewisse Operationen benötigt. Um die Basisrelationen zu
verändern, existieren folgende Kommandos:
-
mit dem insert- Kommando können Tupel eingefügt werden
-
mit dem update- Kommando können Tupel verändert werden
-
mit dem delete- Kommando können Tupel gelöscht werden
Es soll beispielhaft ein Tupel in KLASSE eingefügt werden. Die Klasse hat die Bezeichnung
4a, den Lehrer mit der Personalnummer P-0045 als Klassenlehrer, den Raum mit der
Raumnummer R-127 als Klassenraum und 27 Schüler.
insert
into Klasse
values ( ’4a’ , ’P-0045’ , ’R-127’ , 27 )
Nun soll das eingefügte Tupel verändert werden, indem die Schüleranzahl auf 29 erhöht wird.
update Klasse
set Schüleranzahl = 29
where Klassenbezeichnung = ’4a’
Schließlich soll das Tupel wieder gelöscht werden.
delete from Klasse
where Klassenbezeichnung = ’4a’
26
5.3. Anfragen
Um aus der Datenbank bestimmte Daten zu erhalten, müssen Anfragen formuliert werden.
In SQL geschieht dies mit Hilfe der select- from- und where- Klauseln. Die select- Klausel
dient dazu, die jeweiligen Attribute auszuwählen, von denen Werte im Anfrageergebnis
geliefert werden sollen. In der from- Klausel werden die Relationen angegeben, aus denen
Attribute benötigt werden. Mit der where- Klausel lassen sich schließlich Bedingungen
formulieren, unter denen bestimmte Tupel ausgewählt werden sollen.
1. Mit folgender Anfrage würde die komplette Relation Klasse geliefert werden.
select *
from Klasse
2. Es sollen Name, Personalnummer, Funktion und Einstellungsdatum aller Mitarbeiter
ausgewählt werden.
select Name, Personalnummer, Funktion, Einstellungsdatum
from Mitarbeiter
3. Das Ergebnis der folgenden Anfrage liefert die Anzahl der Schüler an der Schule. Dazu
wird die Aggregatfunktion count verwendet.
select count (*)
from Schüler
4. Es sollen Name, Anschrift und Telefonnummer aller Schüler der Klasse 4a ausgewählt
werden.
select Name, Anschrift, Telefonnummer
from Schüler
where Klassenbezeichnung = ´4a´
5. Durch diese Anfrage werden die Namen aller Schüler, die einen bestimmten Fehleintrag im
Januar 2005 haben, ausgewählt. Mit Hilfe der like- Klausel und des Prozentzeichens wird die
Zeichenfolge im Eintrag gesucht. Die zweite Bedingung in der where- Klausel dient dazu,
den Namen des entsprechenden Schülers zu ermitteln.
select Schüler.Schülernummer, Name, Eintrag
from Schüler, Schülerinfo
where Eintrag like ´%Fehleintrag%01.2005%´ and Schüler.Schülernummer = Schülerinfo.Schülernummer
27
6. Hier werden alle Investitionen ausgewählt.
select Mitarbeiter.Name, Eintragsnummer, Zweck, Betrag
from Mitarbeiter, Investition
where Mitarbeiter.Personalnummer = Investition.Personalnummer
7. Die Anfrage wählt die Investition mit dem größten Betrag aus. Dazu wird die
Aggregatfunktion max benutzt.
select *
from Investition
where Betrag = ( select max (Betrag) from Investition )
8. Die Summe der Beträge, die für einen jeweiligen Zweck ausgegeben werden, soll ermittelt
werden.
select Zweck, sum (Betrag)
from Investition
group by Zweck
9. Mit dieser Anfrage werden alle Mitarbeiter ausgewählt, die kein Lehrer sind.
select Personalnummer, Name, Funktion
from Mitarbeiter
where not Funktion = ´Lehrer´
10. Der am längste an der Schule arbeitende Mitarbeiter wird ausgewählt.
select Personalnummer, Name, Funktion
from Mitarbeiter
where Einstellungsdatum <= all ( select Einstellungsdatum from Mitarbeiter )
11. Es wird ermittelt, welche Note welcher Schüler der Klasse 4a in Mathe bekommen hat.
select bekommt_Note.Schülernummer, Name, Note
from bekommt_Note, Schüler
where Klassenbezeichnung = ´4a´ and Fachname = ´Mathe´ and Halbjahr = ´2004/05: 1´
and bekommt_Note.Schülernummer = Schüler.Schülernummer
12. Es werden die Lehrer ausgewählt, die Sport unterrichten dürfen.
select darf_unterrichten.Personalnummer, Name
from darf_unterrichten, Mitarbeiter
where Mitarbeiter.Personalnummer = darf_unterrichten.Personalnummer and
darf_unterrichten.Fachname = ´Sport´
28
13. Durch diese Anfrage werden für jedes Fach die Lehrer zusammengefasst, die dieses Fach
unterrichten dürfen.
select Mitarbeiter.Personalnummer, Name, Fachname
from Mitarbeiter, darf_unterrichten
where Mitarbeiter.Personalnummer = darf_unterrichten.Personalnummer
group by Fachname
14. Diese Anfrage gibt die Liste alle Fächer zurück, die der Mitarbeiter mit der
Personalnummer ‘P-0102’ unterrichten darf.
select Fachname, Stufenname
from darf_unterrichten
where Personalnummer = ‘P-0102’
15. Diese Anfrage liefert den Stundenplan des Lehrers mit der Personalnummer ´P-0102´.
select Zeitangabe, Klassenbezeichnung, Fachname, Raumnummer
from unterrichtet_planmässig
where Personalnummer = ´P-0102´
order by Zeitangabe
16. Die Anfrage wählt den Name und die Personalnummer des Lehrers aus, der die Klasse 4a
am Datum 2005-03-04 in der dritten Stunde unterrichtet hat.
select Name, unterrichtet.Personalnummer
from Mitarbeiter, unterrichtet
where Mitarbeiter.Personalnummer = unterrichtet.Personalnummer
and Klassenbezeichnung = ´4a´ and Zeitangabe = ´2005-03-04; 3.Stunde´
17. Die Raumnummer des Raums mit der größten Kapazität soll ausgewählt werden.
select Raumnummer
from Raum
where Kapazität = (select max(Kapazität) from Raum)
18. Das Ergebnis der Anfrage sind die Nummern der Räume, die eine größere Kapazität als
35 haben. Diese Räume werden nach Kapazität gruppiert.
select Raumnummer
from Raum
where Kapazität > 35
group by Kapazität
29
6. Literatur
Andreas Heuer, Gunter Saake:
Datenbanken: Konzepte und Sprachen, 2 Auflage,
Bonn: mitp-Verlag, 2000.
Martin Gogolla:
Material zur Vorlesung Datenbanksysteme im WS 2004/05 an der Universität Bremen,
http://db.informatik.uni-bremen.de/teaching/courses/ws2004_dbs/
30