uebung gesamt

Übung Informationssysteme 1
rupi
ss 2005
nachdem die übungen nicht wirklich kontrolliert werden sind die lösungen
hier generell nur eingeschränkt als richtig zu betrachten. manchmal korrigiere
ich fehler während der besprechung.. manchmal nicht ;>
Inhaltsverzeichnis
1. Datenbanken – Einführung
1.1. Programmsystem Hotelverwaltung . . . . . . . . . . . . .
1.2. Physische Datenabhängigkeit . . . . . . . . . . . . . . . .
1.3. Redundanz und Inkonsistenz . . . . . . . . . . . . . . . .
1.4. 3-Schichten Architektur . . . . . . . . . . . . . . . . . . .
1.5. Physische und logische Datenunabhängigkeit . . . . . . .
1.6. Physische, logische, externe Ebene . . . . . . . . . . . . .
1.7. Datenbanksysteme . . . . . . . . . . . . . . . . . . . . . .
1.8. Begriffe aus dem Bereich Datenmodelle und Datenbanken
1.9. 4 Phasen des Datenbankentwurfs . . . . . . . . . . . . . .
1.10. konzeptuelle Modellbildung und logisches Design . . . . .
.
.
.
.
.
.
.
.
.
.
2
2
3
4
4
4
4
5
5
6
6
2. Entity-Relationship-Modell
2.1. ER-Diagramm: Produktionsunternehmen . . . . . . . . . . . . . . . . . .
2.2. ER Diagramm: Autoverleih . . . . . . . . . . . . . . . . . . . . . . . . . .
2.3. ER Diagramm: Stundenaufzeichnung . . . . . . . . . . . . . . . . . . . . .
7
7
8
9
3. Das
3.1.
3.2.
3.3.
3.4.
3.5.
3.6.
3.7.
3.8.
3.9.
Relationale Datenmodell
Begriffe . . . . . . . . . . . . . . . . . . . . . . . . .
Relationale Algebra: Universitätsdatenbank . . . . .
Relationale Algebra: Selektion, Projektion, Verbund
Operationen auf Relationen . . . . . . . . . . . . . .
Relationale Algebra: Beispiel Warengruppe . . . . .
Operationen auf Relationen: Ableitung . . . . . . . .
Schlüssel . . . . . . . . . . . . . . . . . . . . . . . . .
funktionale Abhängigkeiten . . . . . . . . . . . . . .
Anomalien im Relationenschema von 3.8 . . . . . . .
1
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
9
9
9
9
10
10
11
11
11
12
3.10. Hülle von funktionalen Abhängigkeiten und kanonische Überdeckung
3.11. Methoden zur Überprüfung der Äqivalenz von Schemata . . . . . . .
3.12. Anwendung dieser Methoden . . . . . . . . . . . . . . . . . . . . . .
3.13. Armstrongaxiome . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.14. funktionale Abhängigkeiten . . . . . . . . . . . . . . . . . . . . . . .
3.15. Überführung von ER-Diagramm in Relationales Modell . . . . . . .
3.16. Äquivalente Abhängikeitsmengen . . . . . . . . . . . . . . . . . . . .
3.17. funktionale Abhängigkeiten und kanonische Überdeckung . . . . . .
3.18. Bestimmung von Normalformen . . . . . . . . . . . . . . . . . . . . .
3.19. Schlüsselkandidaten und Normalformen . . . . . . . . . . . . . . . .
3.20. Verlustlose Zerlegung in 3NF . . . . . . . . . . . . . . . . . . . . . .
3.21. Relation Zoo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.22. ER Diagramm zu Relationenschema . . . . . . . . . . . . . . . . . .
3.23. Verlustlose Zerlegung in BCNF . . . . . . . . . . . . . . . . . . . . .
3.24. Zerlegung in 4NF . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.25. Qualitätskriterien für 4NF und mehrwertige Abhängigkeiten . . . . .
4. Abfragesprachen für Relationale Datenbanken, SQL
4.1. Liste aller Repräsentationsräume . . . . . . . . .
4.2. Liste aller Praktika . . . . . . . . . . . . . . . . .
4.3. LVA LeiterInnen des FAW . . . . . . . . . . . . .
4.4. Belegungsplan . . . . . . . . . . . . . . . . . . . .
4.5. Informatikabteilungen . . . . . . . . . . . . . . .
4.6. LVAs ohne Raum . . . . . . . . . . . . . . . . . .
4.7. Terminplan von Wagner Roland . . . . . . . . . .
4.8. freie Räume . . . . . . . . . . . . . . . . . . . . .
4.9. Vergleich der Informatik-Institute . . . . . . . . .
4.10. die letzten Informatik LVAs . . . . . . . . . . . .
4.11. LVA-Summen View . . . . . . . . . . . . . . . . .
4.12. LVA mit den wenigsten Terminen . . . . . . . . .
4.13. Abhaltungs Prozente . . . . . . . . . . . . . . . .
4.14. Raumauslastung . . . . . . . . . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
12
12
12
12
12
13
13
13
13
13
13
14
15
15
15
16
.
.
.
.
.
.
.
.
.
.
.
.
.
.
16
16
17
17
18
18
19
19
19
20
20
21
22
23
24
A. Verwendete Datenbank
26
B. Einspielen der Daten
27
1. Datenbanken – Einführung
1.1. Programmsystem Hotelverwaltung
Die Daten werden als xml Files mit utf8 Encoding in einer unten beschriebenen Directory Struktur abgelegt. Das Documentroot des Baumes ist in einem Configfile (ebenfalls
2
xml) abgelegt dass von den Anwendungsprogrammen über einen Suchpfad (/etc, /usr/local/etc, . ) gefunden wird.
Parsing und Verifizierung der xml Datein erfolgt über xml-Schemata die ebenfalls über
einen konfigurierbaren Pfad erreichbar sind. Falls die Programme nicht über einen zentralen Server und Terminals betrieben wird muss darauf geachtet werden dass der Fileserver
für das Documentroot korrekte Writelocks verwaltet.
Auf die Daten wird mit einem wie folgt konstruiertem Pfad zugegriffen:
$documentroot/$entitytype/$id.xml
Datenstruktur Folgende Entitytypen werden realisiert:
gast enthält den Namen und eine Referenz auf das Ausweisdokument dass hinterlegt
wurde (als id wird Name.Ausweisnummer verwendet, falls noch kein Ausweis vorliegt (zb. telefonische Reservierung) wird dem Namen eine laufende Nummer zugewiesen, das Dokument wird ggf. umbenannt)
zimmer mit beschreibung. (als id wird die Zimmernummer verwendet).
belegung enthält die id’s der Gäste. (als id wird Zimmernummer.checkin.checkout verwendet, das Datum wird als YYYYMMDD codiert und 00000000 für den checkout
verwendet falls der Gast noch anwesend ist).
reservierung enthält die id des Gastes der reserviert hat. (id analog zur belegung)
goods enthält den Namen und den Preis des jeweiligen Konsumgutes. (id ist eine laufende Nummer)
konsumtion Enthält eine Liste der goods id’s die Konsumiert wurden und einen Flag
der anzeigt ob schon bezahlt wurde (id ist gastid.YYYYMMDD.nummer)
Benutzung
Rezeption muss die entsprechenden Gast, Belegungs und Reservierungsdaten eingeben,
Reservierungen können über ein Kalenderinterface getätigt werden. Bei Checkout
wird eine Liste der offenen Konsumtionen angezeigt, diese kann für Gäste ausgedruckt werden.
Bedienung legt die Konsumtionsdaten an und kann ggf. den bezahlt Flag setzen falls
gleich bezahlt wurde.
1.2. Physische Datenabhängigkeit
Ein Programm greift auf Daten (Preisliste) mit folgender Charaktaristik zu:
• sie sind in dem File /home/herbert/foobar gespeichert
• die ersten 8 byte einer Zeile werden als Float gelesen
3
• der Rest der Zeile als Produktbeschreibung (String, koi8 codiert)
Sowohl Speicherort als auch die Struktur sind fix codiert, somit liegt physische Datenabhängigkeit vor.
1.3. Redundanz und Inkonsistenz
Redundanz bezeichnet die mehrfache Speicherung der selben Information.
Inkonsistenz ist ein Widerspruch in den vorliegenden Informationen wenn eine Kopie
einen unterschiedlichen Informationsstand aufweist.
Beispiel Die Preisliste eines Ladens wird auf der Webseite des selben als HTML Tabelle
veröffentlicht und intern über eine Tabellenkalkulation verwaltet. Damit sind die selben
Daten redundant gespeichert, wenn die Liste in der Tabellenkalkulation geändert wird
und nicht auf der Webseite liegt eine Inkonsistenz vor.
1.4. 3-Schichten Architektur
Externes Schema (Sicht) Teilmengen der Gesamtinformation oder von der Gesamtinformation abgeletite Daten werden auf den jeweiligen Anwendungsbereich zugeschnitten dargestellt.
Konzeptionelles Schema (logische Ebene) In einem sog. Datenbankschema wird festgelegt welche Daten abgespeichert sind.
Internes Schema (physische Ebene) Auf dieser Ebene wird festgelegt wo die Daten
gespeichert sind (Festplatte. . . ).
1.5. Physische und logische Datenunabhängigkeit
logische Datenunabhängigkeit Anwendungen sprechen Daten mit ihrer logischen Bezeichnung (zb. Name) an. Eine Änderung auf dieser Ebene kann über Sichten vor
der Anwendung versteckt werden. Sichten realisieren somit logische Datenunabhängigkeit.
physische Datenunabhängigkeit Modifikation der physischen Speicherstruktur belässt
die logische Ebene invariant. Beispiel ist das Anlegen eines DB Index. Die Trennung
von logischer und physischer Ebene realisiert somit physische Datenunabhängigkeit.
1.6. Physische, logische, externe Ebene
physische Ebene Autorenkarten haben den Karteikartentyp B und liegen alphabetisch
sortiert in Laden die zu 2/3 aufgefüllt sind. Auf den Laden stehen die Buchstaben.
Belege über den Ankauf liegen in Ordnern im Archivraum sequenziell geordnet.
4
logische Ebene Die Karten enthalten spezielle Informationen (siehe Angabe).
Buchhaltungsbelege enthalten einen Zusammenhang zur Inventarnummer des Buchs.
externe Ebene Die Buchhaltung hat Zugriff auf die Ankaufsbelege, BesucherInnen auf
die Freihandbibliothek, nicht aber auf das Archiv.
1.7. Datenbanksysteme
charakteristische Eigenschaften
• physische und logische Datenunabhängigkeit
• sicherstellen von Datenintegrität und Vermeidung von Redundanz
• flexible Abfragemöglichkeit
• korrekter MehrbenuzerInnenzugriff
• Datensicherheit
geeignete Anwendungen
• grosse Datenmengen
• mehrere BenutzerInnen
ungeeignete Anwendungen
• kleine Datenmengen
• zeitkritische Anwendungen
1.8. Begriffe aus dem Bereich Datenmodelle und Datenbanken
Datenmodell umfasst die Konzepte die die Struktur einer Datenbank beschreiben wie
Datentypen und Beziehungen. Das Datenmodell enthält noch keine konkreten Einzelheiten der Abspeicherung. Akademisch genau ist ein Datenmodell der ”Werkzeugkasten” zur Beschreibung der DAtentypen und ihrer Beziehungen. D.h. das
ER-Modell ist ein Datenmodell.
Datenbankschema legt die Struktur der der abgespeicherten Daten(objekte) fest. Das
Schema enthält die Daten über die abgespeicherten Daten.
Datenbank Management System sind die Programme zum Zugriff auf die Datenbasis,
die Konsistenzprüfung usw.
Datenbasis sind mit einander in Beziehung stehende Daten die zur Erfüllung einer bestimmten Aufgabe notwendig sind.
Datenbanksystem ist die Gesamtheit aus Datenbasis und Datenbank Management System.
5
1.9. 4 Phasen des Datenbankentwurfs
Anforderungsanalyse Die Informationsanforderungen und Datenverarbeitungsvorgänge der zu modellierenden Welt werden gemeinsam mit den zukünftigen AnwenderInnen
analysiert und ein Pflichtenheft für das zukünftige System erstellt.
Konzeptueller Entwurf Die Informationsstruktur wird auf anwedungsorientierter Ebene festgelegt und eine Informationsstrukturbeschreibung (z.B. in Form eines ER-Schemas)
erstellt.
Implementationsentwurf
Physischer Entwurf
1.10. konzeptuelle Modellbildung und logisches Design
In der konzeptuellen Modellbildung wird ein anwendungsorientiertes Modell der Daten
(Informationsstrukturbeschreibung) erstellt. Dabei wird z.B. das ER-Modell verwendet
um ein abstraktes Modell der Daten zu erstellen, dass im logischen Design in ein logisches
Datenbankschema übergeführt wird (meist relationales Modell).
Hier wird im Unterschied zum konzeptuellen Entwurf schon auf das einzusetzende DBMS
eingegangen.
6
2. Entity-Relationship-Modell
2.1. ER-Diagramm: Produktionsunternehmen
7
2.2. ER Diagramm: Autoverleih
8
2.3. ER Diagramm: Stundenaufzeichnung
3. Das Relationale Datenmodell
3.1. Begriffe
Relationenschema ist die Menge der Attribute und ihrer Domains einer Relation und
ihre Schlüsselattribute.
Relation Teilmenge R ⊆ D0 × D1 × . . . × Dn der Domains (Di ). Eine Tabelle”.
”
Zusammengesetztes Attribut Attribute die aus mehreren anderen Attributen zusammengesetzt/gebildet werden.
Schlüsselattribut Attribut dass ein Tupel einer Relation eindeutig bestimmt.
Domain bezeichnet den Wertebereich den ein Attribut haben kann. Diese müssen atomar
sein.
3.2. Relationale Algebra: Universitätsdatenbank
1. σSWS=3 (Vorlesungen)
2. πName (Studenten)
3. πName (σFachgebiet=Sprachtheorie (Assistenten))
4. πVorlNr (Vorlesungen) − πVorlNr (Hören)
5. πAssistenten.Name, Professoren.Name (σBoss=P rof essoren.P ersN r (Assistenten × Professoren))
3.3. Relationale Algebra: Selektion, Projektion, Verbund
A
a2
a2
R1 = σC=c3 (R)
B C D E
b1 c3 d3 e2
b1 c3 d2 e2
R2
C
c1
c1
c3
c3
c2
c1
= πCDE (R)
D
E
d1
e1
d3
e2
d3
e2
d2
e2
d1
e1
d2
e2
Hier in R2 taucht ein Tupel doppelt auf und muss eliminiert werden.
9
R3
A
a1
a1
a2
a2
a1
a3
a1
C
c1
c1
c1
c1
c1
c3
c3
c3
c3
c3
c2
c2
c1
c1
c1
c1
c1
= πABD (R)
B
D
b1
d1
b1
d3
b1
d3
b1
d2
b2
d1
b3
d2
b3
d3
R2 o
n R3
E D A
e1 d1 a1
e1 d1 a1
e2 d3 a1
e2 d3 a2
e2 d3 a1
e2 d3 a1
e2 d3 a2
e2 d3 a1
e2 d2 a2
e2 d2 a3
e1 d1 a1
e1 d1 a1
e2 d2 a2
e2 d2 a3
e2 d3 a1
e2 d3 a2
e2 d3 a1
B
b1
b2
b1
b1
b3
b1
b1
b3
b1
b3
b1
b2
b1
b3
b1
b1
b3
Auffallend ist hier: beim Join R2 o
n R3 entstehen mehr Tupel als in R waren, das Attribut
D ist nicht eindeutig.
3.4. Operationen auf Relationen
3.5. Relationale Algebra: Beispiel Warengruppe
Alle Artikel der Warengruppe Laptop:
πARTIKELNR (σWGNR=πWGNR (σWARENGRUPPE = Laptop (WARENGRUPPE)) ARTIKEL)
Join ist meist effizienter:
πArtikelNr (σWARENGRUPPE=Laptop (WARENGRUPPE o
n ARTIKEL)
10
3.6. Operationen auf Relationen: Ableitung
R ∩ S = R − (R − S)
Mengendurchschn
Ro
nθ S = σθ (R × S)
Theta Join
Ro
nR.Ai =S.Bi S = σR.Ai =S.Bi (R × S)
Equi Join
R | × S = πsch(R) (σR.Ai =S.Ai ∧R.Bi =S.Bi ... (R × S))
(linker) Semi Join
= πSch(R) (R o
n S)
mit gegegenem J
R ] × S = σR.Ai =S.Ai ∧R.Bi =S.Bi ... (R × S)∪
((R × {[−, −, · · · , −]}) − ({[−, −, · · · , −]} × S))
linker äusserer Jo
= (R o
n S) ∪ ((R − (R | × S)) × {[−, −, · · · , −]})
R × [S = S] × R
rechter äusserer Join
R ] × [ S = (R] × S) ∪ (R × [S)
äusserer Join
3.7. Schlüssel
• Jeder Primärschlüssel ist ein Superschlüssel: JA
• Jeder Superschlüssel ist ein Kandidatenschlüssel: NEIN
• Jeder Kandidatenschlüssel ist ein Superschlüssel: JA
• Jeder Kandidatenschlüssel ist ein Primärschlüssel: NEIN
• Die Vereinigung aller Kandidatenschlüssel ergibt die Attributmenge: NEIN
• Es gibt Relationsenschemata ohne Kandidatenschlüssel: NEIN
• Ein Relationenschema mit mindestens einer nicht-trivialen funktionalen Abhängigkeit hat mindestens 2 Superschlüssel: JA
• Heit eine Attributmenge n Elemente, so gibt es maximal 2n und minimal 2 Superschlüssel: NEIN (minimal 1, maximal 2n − 1
3.8. funktionale Abhängigkeiten
{LVA Nr, Semester} →{LVA Titel}
{Matr Nr} →{S Vorname, S Nachname, S GebDat}
{Prüfer Nr} →{Prüfer Name}
{Matr Nr, LVA Nr, Semester, P Termin, Prüfer Nr} →Note
ein Schlüsselkandidat wäre dh. {LVA Nr, Matr Nr, Prüfer Nr, Semester, P Termin}
11
3.9. Anomalien im Relationenschema von 3.8
Insert Eine PrüferIn die keine StudentIn geprüft hat für eine kann nicht (sauber) eingefügt werden.
Update kann bei Änderung von zb. PrüferInnennummer auftreten.
Delete Das Löschen eines Prüfers würde auch alle StudentInnendaten löschen.
3.10. Hülle von funktionalen Abhängigkeiten und kanonische Überdeckung
Hülle von funktionalen Abhängigkeiten F + ist die aus einer gegebenen Menge von funktionalen Abhängigkeiten F herleitbare Menge F + von funktionalen Abhängigkeiten
kanonische Überdeckung FC ist quasi eine minimale Variante einer Menge von funktionalen Abhängigkeiten F in der weder überflüssige Attribute vorhanden sind noch
eine ’linke Seite’ mehrfach vorkommt.
3.11. Methoden zur Überprüfung der Äqivalenz von Schemata
3.12. Anwendung dieser Methoden
3.13. Armstrongaxiome
unvollständig
TransitivitätAB → EGH
AB → C ∧ B → D ⇒ AB → CD
AB → CD ∧ CD → EGH ⇒ AB → EGH
G → A ∧ AB → C ⇒ BG → C
3.14. funktionale Abhängigkeiten
sehr unvollständig. . .
Aufnahme
Aufnz. → Aufn.Dat/Zeit, P.Name, P.Adr., SozNr.
SozNr. → P.Name, P.Adr.
Befunde
BefID → AufnZ, Bef Dat/Zeit, BefTyp, Bef.Text
12
3.15. Überführung von ER-Diagramm in Relationales Modell
Zeitung
Redakteur
Artikel
Schlagwort
Art-Schlagw
schreibt
:
:
:
:
:
:
{ [ Z-Name:String; Z-Adresse:String ] }
{[R-Kurzzeichen:String; R-Name:String; Z-Name:String ] }
{[ A-Datum:float; A-Titel:String ]}
{[S-Nr:Integer; S-Wort:String]}
{[ S-Nr:Integer; A-DAtum:float; A-Titel:String ] }
{[ R-Kurzzeichen:String; A-Datum: float; A-Titel:String ] }
3.16. Äquivalente Abhängikeitsmengen
3.17. funktionale Abhängigkeiten und kanonische Überdeckung
• Prod ({ Pr, Ma, M },{ Pr → M, Pr → Ma, M → Ma } )
• kandidatenschlüssel: Pr
• verletzung 3NF: M → Ma
3.18. Bestimmung von Normalformen
• RS1 ({F, D, P, G}, {F, D → P, G},{F → G}
kandidatenschlüssel: FD
verletzung der 2NF: F → G
• RS2 ({F,D,P,N},{FD → PN, P → N, N → P } )
kandidatenschlüssel: FD
verletzung der 3NF: P → N, N → P
3.19. Schlüsselkandidaten und Normalformen
3.20. Verlustlose Zerlegung in 3NF
RS1
• kanonische überdeckung: D → B, D → C, D → F, BC → C, D → F
⇒ D → BCF
• kandidatenschlüssel: ADE
• 2. NF verletzt wegen D → B
• 3. NF zerlegung:
RS11({B,C,D,F},{D → BCF})
RS12({ADE},{})
RS2
• kanonische überdeckung: C → A, E → F, B → D
• kandidatenschlüssel: BCE
• 2. NF verletzt wegen: C → A
13
• zerlegung in 3NF:
RS21({A,C},{C → A })
RS22({E,F},{E → F })
RS23({B,D},{B → D })
RS24({BCE},{})
RS3
• kanonische überdeckung: AC → BDEF
• kandidatenschlüssel: AC
• das ist in BCNF (auf der linken seite der k.ü. steht nur der kandidatenschlüssel)
RS4
• kanonische überdeckung: E → AF, B → C
• kandidatenschlüssel: BDE
• 2. NF verletzt: B → C
• zerlegung in 3NF:
RS41({A,E,F},{E → AF } )
RS42({B,E,F},{B → C } )
RS43({B,D,E},{} )
RS5
• kanonische überdeckung: AB → D, DE → ABCF
• kandidatenschlüssel: ABE, DE (non key: C,F)
• BCNF verletzt wegen AB → D
schummler für normalformen
BCNF X → A ⇒ X ist superkey
3NF X → A ∧ A ist non key ⇒ X is superkey
2NF jedes non-key attribut ist voll funktional abhängig von jedem kandidatenschlüssel
3.21. Relation Zoo
• Funktionale Abhängigkeiten:
Art TierNr → Box
Box → Art TierNr
Art → Behausung
Behausung → Bereich Wärter
Bereich → Leiter
• Kandidatenschlüssel: Box, Art TierNr
• Art → Behausung (zb.) widerspricht der 2NF, die Relation ist dh. in 1NF
14
• Folgende Abhängikeiten sind redundant in dieser Relation:
Art → Behausung
Behausung → Bereich Wärter
Bereich → Leiter
Die Entsprechenden Informationen sind mehrfach abgespeichert und können zu
Updateanomalien führen. Einfügeanomalien treten auf wenn ein neuer Bereich ohne Behausungen, eine Behausung ohne Wärter oder Tiere eingefügt werden soll.
Löschanomalien treten analog dazu auf (zb. alle Bären sterben und alle Informationen über den Bereich Ost, Käfig, Sam und Smith sind futsch).
• Zerlegung in 3NF:
Box:({Box, Art, TierNr},{Box → Art TierNr})
Art:({Art, Behausung},{})
Behausung:({Behausung,Bereich,Wärter},{Behausung → Bereich, Wärter })
Bereich:({Bereich,Leiter},{})
3.22. ER Diagramm zu Relationenschema
3.23. Verlustlose Zerlegung in BCNF
• RS1:({A,B,C,D,E}, {BD → ACE, BC → ADE})
ist in BCNF
• RS2:({A,B,C,D,E}, {BC → E, AC → BD, B → D })
RS21:({B,D},{B → D })
RS22:({A,B,C,D},{AC → BD })
RS23:({B,C,E},{BC → E})
3.24. Zerlegung in 4NF
• mvds:
FahrerNr →→ BusNr
FahrerNr →→ Linie
BusNr →→ Line
Linie →→ HaltestellenId
Linie →→ BusNr Start-Datum Start-Uhrzeit
FahrerNr Start-Datum Start-Uhrzeit → BusNr Linie
• KS: Fahrer BusNr Start-Datum Start-Uhrzeit Linie
• zerlegung in 4NF:
Linenplan:({Linie, HatestellenId},{Linie →→ HaltestellenId})
Fahrten:({Fahrer, BusNr, Start-Datum, Start-Uhrzeit, Linie},{ FahrerNr StartDatum Start-Uhrzeit → BusNr Linie } )
15
3.25. Qualitätskriterien für 4NF und mehrwertige Abhängigkeiten
Eine Relation R ist dann in 4NF wenn jede MVD α →→ β eine der folgenden Bedingungen erfüllt:
• die MVD α →→ β ist trivial (β ⊆ α oder β = R − α)
• α ist ein Superkey von R
Eine MVD (multivalued dependency oder tupelgenerierte Abhängigkeit) α →→ β ist
dann gebeben wenn ein bestimmtes α eine bestimmte, endliche Menge (n ∈ N) von β
Werten zugeordnet ist.
Das Qualitätskriterium der 4NF ist nun dass solche MVDs α →→ β nur dann zulässig
sind wenn α Superkey ist oder die MVD trivial ist.
4. Abfragesprachen für Relationale Datenbanken, SQL
Die Tabellen für diese Übung wurden anhand der Angabe erstellt, siehe dazu auch das
SQL Listing 1 im Anhang. Beim Einspielen der Daten (siehe auch Perl Listing 2) traten
allerdings bei folgenden Statements Fehler auf:
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
Abhaltung
Abhaltung
Abhaltung
Abhaltung
Abhaltung
Abhaltung
Abhaltung
Abhaltung
Abhaltung
Abhaltung
Abhaltung
Abhaltung
Abhaltung
Abhaltung
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
(’330211’,’3993’,’08.03.96’,’8’,’30’,’10’,’0’,’VE
(’330211’,’3993’,’15.03.96’,’8’,’30’,’10’,’0’,’VE
(’330211’,’3993’,’22.03.96’,’8’,’30’,’10’,’0’,’VE
(’330211’,’3993’,’29.03.96’,’8’,’30’,’10’,’0’,’VE
(’330211’,’3993’,’19.04.96’,’8’,’30’,’10’,’0’,’VE
(’330211’,’3993’,’26.04.96’,’8’,’30’,’10’,’0’,’VE
(’330211’,’3993’,’03.05.96’,’8’,’30’,’10’,’0’,’VE
(’330211’,’3993’,’10.05.96’,’8’,’30’,’10’,’0’,’VE
(’330211’,’3993’,’17.05.96’,’8’,’30’,’10’,’0’,’VE
(’330211’,’3993’,’24.05.96’,’8’,’30’,’10’,’0’,’VE
(’330211’,’3993’,’31.05.96’,’8’,’30’,’10’,’0’,’VE
(’330211’,’3993’,’14.06.96’,’8’,’30’,’10’,’0’,’VE
(’330211’,’3993’,’21.06.96’,’8’,’30’,’10’,’0’,’VE
(’330211’,’3993’,’28.06.96’,’8’,’30’,’10’,’0’,’VE
Ursache dieser Fehler sind mehrfach auftretende Tupel.
4.1. Liste aller Repräsentationsräume
SELECT ∗ from Raum where Name l i k e ’ Rep%’ ;
raum id
M 103A
...
name
Rep.-R A
anzpers
130
16
gebaeude
Mensagebaeude
005’);
005’);
005’);
005’);
005’);
005’);
005’);
005’);
005’);
005’);
005’);
005’);
005’);
005’);
...
M 103B
Rep.-R
M 103C
Rep.-R
M 202D
Rep.-R
M 202E
Rep.-R
M 202H
Rep.-R
M 202F
Rep.-R
M 202G
Rep.-R
8 Reihen gesamt
B
C
D
E
H
F
G
50
130
45
45
45
1
120
Mensagebaeude
Mensagebaeude
Mensagebaeude
Mensagebaeude
Mensagebaeude
Mensagebaeude
Mensagebaeude
4.2. Liste aller Praktika
SELECT LVANr, Name , Std from LVA where Std > 10 and Typ = ’PR ’ ;
lvanr
name
317103 Anleitung zu wissenschaftlichen Arbeiten
317095 Praktikum Chem. Technologie Anorgan. Stoffe II
316417 Wissenschaftliche Arbeiten für Dipl. u. Diss.
316142 Analytisch-Chemisches Praktikum IV
316440 Anorganisch chemisches Praktikum II für WITECH
316441 Anorganisch chemisches Praktikum II f.Techn.Chemie
6 Reihen gesamt
std
20
12
20
12
12
12
4.3. LVA LeiterInnen des FAW
SELECT DISTINCT Abhaltung . Persnr , Person . Name from Person ,
Abhaltung where LVANr l i k e ’ 3127% ’ AND Abhaltung . PersNr =
Person . PersNr ORDER BY Person . Name ASC;
persnr
2461
1259
472
786
2176
1321
1686
7 Reihen
name
Gierlinger Christian
Küng Josef
Marik Vladimir
Schmollmüller Walter
Tjoa A Min
Wagner Roland
Wöß Wolfram
gesamt
17
4.4. Belegungsplan
SELECT Tag , v o n s t u n d e as von , von minute as m, b i s s t u n d e as b i s
, b i s m i n u t e as m1, a b h a l t u n g . l v a n r , LVA. Name as LVA Name ,
Person . Name as L e i t e r I n FROM Raum, Abhaltung , lva , Person
WHERE Raum . Name = ’HS 13 ’ AND Abhaltung . Raum Id = Raum . Raum ID
AND Abhaltung . Tag = ’ 0 9 . 0 5 . 1 9 9 6 ’ AND Abhaltung . LVANr = LVA.
LVANr AND Person . PersNr = Abhaltung . PersNr ORDER BY von stunde
, von minute ASC;
tag
1996-05-09
1996-05-09
1996-05-09
1996-05-09
1996-05-09
1996-05-09
von
8
10
10
11
11
12
m
30
0
15
0
15
0
bis
10
10
11
11
12
13
m1
0
15
0
15
0
30
lvanr
312240
311152
311152
311153
311153
315320
1996-05-09
12
0
13
30
315320
1996-05-09
1996-05-09
13
17
45
0
15
19
15
0
312705
102035
lva name
Programmiersprachen
Übung Statistik 1
Übung Statistik 1
Übung Statistik 1
Übung Statistik 1
Überblick Symbolic Computation
Überblick Symbolic Computation
Übung Informationssysteme 1
Österreichisches und Europäisches Kartellrecht
leiterin
Rechenberg Peter
Pölz Werner
Pölz Werner
Pölz Werner
Pölz Werner
Winkler Franz
Buchberger Bruno
Wöß Wolfram
Keinert Heinz
9 Reihen gesamt
4.5. Informatikabteilungen
SELECT substring (LVANr from 1 f o r 4 ) as AbteilungsNr , Count(LVANr
) as Anzahl , sum( Std ) as Stunden from LVA WHERE LVANr l i k e ’
311% ’ OR LVANr l i k e ’ 312% ’ OR LVANr l i k e ’ 321% ’ GROUP BY
A b t e i l u n g s N r HAVING Count(LVANr) < 10 ORDER BY Stunden DESC;
abteilungsnr anzahl
3116
9
3111
8
3125
5
3 Reihen gesamt
18
stunden
20
14
9
4.6. LVAs ohne Raum
SELECT DISTINCT Abhaltung . LVANr, LVA. Name from Abhaltung ,LVA
where Abhaltung . LVANr l i k e ’ 3127% ’ AND LVA. LVANr = Abhaltung .
LVANr AND Abhaltung . Raum Id = ’ ’ ;
lvanr
name
0 Reihen gesamt
4.7. Terminplan von Wagner Roland
SELECT tag , v o n s t u n d e as von h , von minute as von m , b i s s t u n d e
as b i s h , b i s m i n u t e as bis m , r . name , l . l v a n r , l . name FROM
a b h a l t u n g a , l v a l , raum r , p e r s o n p WHERE t a g between ’
01−06−1996 ’ AND ’ 30−06−1996 ’ and p . name l i k e ’ Wagner Roland%’
and a . l v a n r = l . l v a n r and a . raum id = r . raum id and a . p e r s n r =
p . p e r s n r order by tag , von stunde , von minute ;
tag
von h
1996-06-04 13
1996-06-11 13
1996-06-18 13
1996-06-21 9
1996-06-25 13
1996-06-28 9
6 Reihen gesamt
von m
30
30
30
0
30
0
bis h
15
15
15
13
15
13
bis m
15
15
15
0
15
0
name
HS 10
HS 10
HS 10
T 811
HS 10
T 811
lvanr
312704
312704
312704
312714
312704
312714
name
Informationssysteme 1
Informationssysteme 1
Informationssysteme 1
Diplomandenseminar 2
Informationssysteme 1
Diplomandenseminar 2
4.8. freie Räume
SELECT raum id , name FROM raum WHERE gebaeude l i k e ’ Kepl%’ and
raum id != a l l ( SELECT raum id from a b h a l t u n g WHERE t a g= ’
15−05−1996 ’ AND ( ( v o n s t u n d e ∗60+ von minute < 12∗60 AND
b i s s t u n d e ∗60+ b i s m i n u t e > 1 2 ∗ 6 0 ) OR ( v o n s t u n d e ∗60+ von minute
>= 12∗60 AND v o n s t u n d e ∗60+ von minute < 12∗60+90) ) ) ;
raum id
K 009D
...
19
name
K 009D
...
KH 007
KH 008
K 153C
K 167D
Gang A
Gang D
K 121B
K 166D
K 171D
K 207A
K 236C
K 266D
K 267D
K 001A
15 Reihen
KEP 2
KEP 3
K 153C
K 167D
Gang A
Gang D
K 121B
K 166D
K 171D
K 207A
K 236C
K 266D
K 267D
K 001A
gesamt
4.9. Vergleich der Informatik-Institute
SELECT substring (LVANr, 1 , 3 ) as InstNr , sum( Std ) as Std
FROM l v a
WHERE LVANr l i k e ’ 311% ’
OR LVANr l i k e ’ 312% ’
OR LVANr l i k e ’ 321% ’
GROUP BY I n s t N r
ORDER BY sum( Std ) DESC;
instnr std
312
316
311
162
321
40
3 Reihen gesamt
4.10. die letzten Informatik LVAs
SELECT a . tag , a . LVANr, l . name , v o n s t u n d e | | ’ : ’ | | von minute as von ,
b i s s t u n d e | | ’ : ’ | | b i s m i n u t e as b i s
FROM Abhaltung a , l v a l
WHERE l . l v a n r = a . l v a n r
20
AND ( l . l v a n r l i k e ’ 311% ’ OR l . LVANr l i k e ’ 312% ’ OR l . LVANr l i k e
’ 321% ’ )
AND a . Tag between ’ 2 9 . 4 . 9 6 ’ AND ’ 4 . 5 . 9 6 ’
AND ( v o n s t u n d e ∗60+ von minute ) = (
SELECT max( v o n s t u n d e ∗60+ von minute )
FROM a b h a l t u n g
WHERE ( l v a n r l i k e ’ 311% ’ OR LVANr l i k e ’ 312% ’ OR LVANr l i k e
’ 321% ’ )
AND a . t a g = t a g
)
ORDER BY a . t a g ;
tag
lvanr
1996-04-29 321155
1996-04-30 312937
1996-04-30 312937
1996-05-01 312935
1996-05-02 312284
1996-05-02 312301
1996-05-03 312913
1996-05-04 311128
8 Reihen gesamt
name
von
bis
Übungen zu Didaktik der Informatik
Praktikum aus praktischer Informatik
Praktikum aus praktischer Informatik
Oberon 2
Praktische Informatik II
Objektorientierte Informationssysteme
Betriebssysteme 2
Technologiefolgenabschätzung
18:0
19:30
19:30
17:30
18:0
18:0
19:0
10:0
18:45
21:0
21:0
18:0
19:30
21:15
20:30
15:0
4.11. LVA-Summen View
Bei dieser Aufgabe gab es leichte Probleme mit den Mehrfacheinträgen:
Dieser View sollte eigentlich mehrfache Abhaltungen durch mehrere Personen eliminieren. Das ist aber nicht wirklich einfach, die vorliegende Variante eliminiert nur gleichzeitige Abhaltungen von mehreren Personen, verschobene Abhaltung durch mehrere Personen
und mehrere Gruppen mit einer LVA-LeiterIn werden nicht erfasst.
CREATE VIEW lvasummen AS
SELECT l . l v a n r , l . name , l . typ , l . std ,
count ( ∗ ) as anzahl ,
round (sum( b i s s t u n d e ∗60 + b i s m i n u t e − v o n s t u n d e ∗60 − von minute
) / 60 , 2)
as stunden
FROM l v a l , (
SELECT DISTINCT l v a n r , tag , von stunde , von minute ,
bis stunde ,
bis minute
FROM a b h a l t u n g
) a
WHERE l . l v a n r = a . l v a n r
21
GROUP BY l . l v a n r , l . name , l . typ , l . s t d ;
Beispiel zu oben beschriebenen Phänomen:
SELECT ∗ FROM lvasummen order by a n z a h l desc l i m i t 2 0 ;
lvanr
name
700145 Mathematik II und III
241126 VO-VII: Rechtliche Aspekte im Marketing
315156 Mathematik für Informatiker II Übungen
316305 Organisch-Chemisches Praktikum I
316310 Organisch-Chemisches Praktikum III
316340 Organisch-Chemisches Praktikum I
317105 Privatissimum Chemische Technologie Anorg.Stoffe
315062 Mathematik I für SOWI
311456 Übung Nachrichtentechnik 2 für Informatiker
313201 VO Theoretische Physik II (Elektrodynamik)
314303 Halbleiterbauelemente
312521 Übungen zu Operations Research II
312531 Übung Modellbildung und Simulation
318105 Privatissimum Organische Technologie
315101 Analysis II
137017 Klausurenkurs Verfassungsrecht
241117 Exportlehrgang 2. Abschnitt
312518 PR zu Objektorientiertes Programmieren
315604 Mathematik II für Mechatroniker
241119 Exportlehrgang 2. Abschnitt
20 Reihen gesamt
typ
VO
VO
UE
PR
PR
PR
PV
UE
UE
VO
VO
UE
UE
PV
VO
RE
SE
PR
VO
SE
std
4
2
2
7
10
8
4
1
1
4
4
1
1
5
5
4
10
3
5
10
anzahl
75
70
70
70
70
70
70
68
65
63
59
58
56
56
55
54
54
50
46
45
4.12. LVA mit den wenigsten Terminen
SELECT l v a n r , name , a n z a h l from lvasummen
WHERE ( l v a n r l i k e ’ 311% ’ or l v a n r l i k e ’ 312% ’ or l v a n r l i k e ’ 321% ’ )
AND a n z a h l = (
SELECT min( a n z a h l )
FROM lvasummen
WHERE l v a n r l i k e ’ 311% ’ or l v a n r l i k e ’ 312% ’ or l v a n r l i k e ’
321% ’
);
22
stunden
245.50
264.50
111.50
280.00
560.00
280.00
70.00
52.50
68.00
97.25
93.00
43.50
42.00
56.00
72.00
88.00
232.75
81.25
59.25
175.50
lvanr
name
312164 Diplomandenseminar 2
312314 Projektpraktikum
2 Reihen gesamt
anzahl
1
1
4.13. Abhaltungs Prozente
SELECT l . l v a n r ,
l . name ,
l . typ ,
l . std ,
l . s t d ∗ 14 as s o l l s t d ,
count ( ∗ ) ∗ s t d as i s t s t d ,
round ( ( count ( ∗ ) ∗ s t d ) / ( s t d ∗ 1 4 ) ∗ 1 0 0 , 2 ) as p r o z e n t
FROM l v a l ,
( s e l e c t d i s t i n c t l v a n r , t a g from a b h a l t u n g ) a
WHERE l . l v a n r = a . l v a n r
AND l . l v a n r l i k e ’ 3127% ’
GROUP BY l . l v a n r , l . name , l . std , l . typ
UNION
SELECT l v a n r ,
name ,
typ ,
std ,
s t d ∗ 14 as s o l l s t d ,
’ 0 ’ as i s t s t d ,
’ 0 ’ as p r o z e n t
FROM l v a
WHERE l v a n r l i k e ’ 3127% ’
AND l v a n r not in ( s e l e c t l v a n r from a b h a l t u n g ) ;
lvanr
312703
312704
312705
312706
312707
312708
312712
312713
312714
312718
...
name
Privatissimum Wissensbasierte Systeme
Informationssysteme 1
Übung Informationssysteme 1
Praktikum aus Informationssysteme
Dissertantenseminar 4
Ausgewählte Kapitel aus Informationssysteme
Projektpraktikum Informationssysteme
Seminar Informationssysteme
Diplomandenseminar 2
Datenmodellierung
23
typ
PV
VO
UE
PR
SE
VO
PR
SE
SE
VO
std
2
2
2
5
3
1
10
2
3
1
soll std
28
28
28
70
42
14
140
28
42
14
ist std
0
28
26
10
0
2
0
4
21
14
prozent
0
100.00
92.86
14.29
0
14.29
0
14.29
50.00
100.00
...
312719 Übung Datenmodellierung
312720 Programmierpraktikum 3: Smalltalk
312725 Praktikum aus Informationssysteme
312727 Projektpraktikum Informationssysteme
312728 Diplomandenseminar 3
312729 Seminar Informationssysteme
312730 Dissertantenseminar 1
17 Reihen gesamt
UE
PR
PR
PR
SE
SE
SE
2
2
5
10
3
2
3
28
28
70
140
42
28
42
26
18
0
20
0
8
0
4.14. Raumauslastung
Es scheint nicht schlüssig zu sein in Bezug auf die Raumauslastung den View aus 4.11 zu
verwenden, da für diese Aufgabenstellung mehrfache Abhaltung gezählt werden sollte.
CREATE VIEW i n f a u s l a s t u n g AS
SELECT r . raum id ,
r . name ,
sum( ( b i s s t u n d e ∗60+ b i s m i n u t e − v o n s t u n d e ∗60 − von minute )
/ 6 0 ) as
infstunden
FROM raum r ,
abhaltung a
WHERE a . raum id = r . raum id
AND ( l v a n r l i k e ’ 311% ’ OR
l v a n r l i k e ’ 312% ’ OR
l v a n r l i k e ’ 321% ’
)
GROUP BY r . raum id , r . name ;
CREATE VIEW a u s l a s t u n g AS
SELECT r . raum id ,
r . name ,
sum( ( b i s s t u n d e ∗60+ b i s m i n u t e − v o n s t u n d e ∗60 − von minute )
/ 6 0 ) as
gesamt
FROM raum r ,
abhaltung a
WHERE a . raum id = r . raum id
GROUP BY r . raum id , r . name ;
SELECT a . raum id ,
a . name ,
round ( a . gesamt , 2 ) as gesamt ,
round ( i . i n f s t u n d e n , 2 ) as i n f o r m a t i k ,
round ( i . i n f s t u n d e n / a . gesamt ∗ 1 0 0 , 2 ) as p r o z e n t
24
92.86
64.29
0
14.29
0
28.57
0
FROM a u s l a s t u n g a ,
infauslastung i
WHERE i . raum id = a . raum id ;
raum id
J 311B
KG 318
K 223B
KH 004
K 034D
KHG I
T 112
HT 182E
SR 101
HT 172H
T 008
MZ 003B
T 1010
KHG II
T 911
K 033C
HT 175G
MZ 412A
K 153C
HG 001
MZ 001A
K 112A
T 111
MZ 009A
T 211
K 041B
K 08
K 04
K 009D
K 012D
T 857
KG 712
P 004
HT 179F
MZ 005B
K 12
T 811
...
name
J 311B
KG 318
K 223B
KEP 1
K 034D
KHG I
T 112
HS 2
SR 101
HS 7
HS 14
MZ 003B
T 1010
KHG II
T 911
K 033C
HS 5
MZ 412A
K 153C
HS 9
HS 15
K 112A
T 111
HS 16
T 211
HS 1
ESH 2
ESH 3
K 009D
K 012D
T 857
KG 712
P 004
HS 4
MZ 005B
ESH 1
T 811
gesamt
713.75
587.50
470.25
435.75
818.25
221.00
759.75
661.50
330.50
834.50
800.50
1368.00
1006.25
170.25
824.75
715.25
789.00
1048.50
784.50
707.50
821.50
723.75
1370.50
925.50
1045.50
490.50
323.75
217.00
775.25
790.00
534.25
688.50
1209.75
910.25
645.75
262.50
968.25
25
informatik
37.75
37.00
3.00
16.50
49.50
138.50
99.50
100.25
192.00
56.00
284.00
5.00
174.25
8.00
152.25
113.00
9.00
3.75
86.25
77.50
36.75
8.00
58.50
46.00
87.75
47.50
79.50
32.50
128.50
96.00
195.75
99.00
96.00
117.00
3.00
15.50
478.75
prozent
5.29
6.30
0.64
3.79
6.05
62.67
13.10
15.15
58.09
6.71
35.48
0.37
17.32
4.70
18.46
15.80
1.14
0.36
10.99
10.95
4.47
1.11
4.27
4.97
8.39
9.68
24.56
14.98
16.58
12.15
36.64
14.38
7.94
12.85
0.46
5.90
49.44
...
HG 101
HS 10
K 224B
K 224B
MZ 001B
HS 17
KH 008
KEP 3
T 212
T 212
HT 177F
K 177F
MZ 005A
MZ 005A
K 269D
K 269D
T 007
HS 13
K 001A
K 001A
HT 174H
HS 6
T 005
HS 11
T 711
T 711
LEKT-ZIM LEKT-ZIM
K 123A
K 123A
MZ 407A
MZ 407A
P 013
HS 8
VE 005
VE 005
P 215
P 215
T 006
HS 12
HT 180E
HS 3
58 Reihen gesamt
733.75
772.00
800.50
436.75
1119.75
605.50
834.25
734.75
660.00
440.75
850.75
728.75
1308.00
252.00
652.00
633.75
727.25
196.50
733.00
704.25
893.00
258.50
39.00
41.25
25.00
391.75
124.00
3.00
25.50
142.25
52.00
150.75
167.50
204.25
6.00
82.50
21.50
50.00
9.75
5.00
222.50
185.00
35.23
5.05
5.15
5.72
34.99
20.48
0.36
3.47
21.55
11.80
17.72
22.98
15.62
2.38
12.65
3.39
6.88
4.96
0.68
31.59
20.72
A. Verwendete Datenbank
Für die Übung wird eine PostgreSQL DBMS Version 7.4.7 verwendet, die Tabellen wurden wie folgt erstellt:
/∗ i n f o s y s 1 − uebung
∗ database create statements
∗ t a b l e d e f i n i t i o n s l i k e s t a t e d i n Infosys1 UE SQL1 . p d f
∗
∗ ruprechtsberger , sose05
∗
∗ n o t e s : o r a c l e s p e c i f i s c h e d a t e n t y p e n wurden mit
∗
entsprechenden p o s t g r e s q l analoga ver t a us ch t
∗
w a r s c h e i n l i c h n i c h t immer o p t i m a l . .
∗
v a r c h a r 2 => v a r c h a r
∗
number
=> numeric
∗/
−− l v a t a b l e
DROP TABLE LVA;
26
CREATE TABLE LVA (
LVANr
varchar ( 6 ) PRIMARY KEY, −− 312704
Name
varchar ( 7 0 ) ,
−− 50 i s not enough f o r used
values
Std
numeric ( 2 ) ,
Typ
char ( 2 )
−− VO, UE, SE , . . .
);
−− p e r s o n t a b l e
DROP TABLE Person ;
CREATE TABLE Person (
PersNR varchar ( 4 ) PRIMARY KEY, −− Personal −Nummer
Name
varchar ( 5 0 )
−− Name
);
−− a b h a l t u n g t a b l e
DROP TABLE Abhaltung ;
CREATE TABLE Abhaltung (
LVANr
varchar ( 6 ) ,
PersNr
varchar ( 4 ) ,
Tag
date ,
−− Kalender−Tag
Von Stunde numeric ( 2 ) ,
Von Minute numeric ( 2 ) ,
B i s S t u n d e numeric ( 2 ) ,
Bis Minute numeric ( 2 ) ,
Raum Id
varchar ( 8 ) ,
PRIMARY KEY(LVANr, PersNr , Tag , Von Stunde , Von Minute ,
Bis Stunde , Bis minute , Raum Id
)
);
−− raum t a b l e
DROP TABLE Raum ;
CREATE TABLE Raum (
Raum Id
varchar ( 8 ) ,
Name
varchar ( 3 0 ) ,
AnzPers
numeric ( 4 ) ,
Gebaeude
varchar ( 2 0 ) ,
PRIMARY KEY( Raum Id )
);
−− Raum−Nummer
−− Raumbezeichnung
Listing 1: createtables.sql
B. Einspielen der Daten
Die zur Verfügung gestellten Daten wurden mit folgendem Script in die Datenbank
eingespielt:
#! / u s r / b i n / p e r l −w
27
#
#
#
#
#
i n f o s y s 1 uebung s o s e 05
ruprechtsberger
populate t a b l e s with given values
not t h a t e f f i c i e n t , b u t p r e t t y f a i l s a v e
use s t r i c t ;
use Pg ;
use open ’ : e n c o d i n g ( i s o −8859−1) ’ ;
# db c o n n e c t i o n h a n d l e ( o t h e r c o n n e c t i o n parameter h a n d l e d v i a
environment )
my $con = Pg : : connectdb ( ”dbname=i n f o s y s 1 ”) ;
die ” c o n n e c t i o n e r r o r o c c u r e d \n ” i f ( $con−>s t a t u s != Pg : :
PGRES CONNECTION OK) ;
# tables
my @ t a b l e s = ( ”Person ” , ”Raum” , ”Lva ” , ”Abhaltung ” ) ;
foreach my $ t a b l e ( @ t a b l e s ) {
# empty t a b l e
my $ r e s = $con−>exec ( ”DELETE from $ t a b l e ”) ;
die ( ” d e l e t e from $ t a b l e f a i l e d ”)
i f ( $ r e s −>r e s u l t S t a t u s != Pg : : PGRES COMMAND OK ) ;
# open f i l e
open ( FILE , ” $ t a b l e . l s t ”) | | die ( ”can ’ t open $ t a b l e . l s t ”) ;
while (<FILE>) {
s /\ s ∗ $ / / ; # g e t r i d o f t a i l i n g w h i t e s p a c e s ( i n c l . CR)
s / ’ /\\ ’ / g ; # e s c a p e ’ c h a r a c t e r s
my @values = s p l i t ( / ; / ) ;
# generate i n s e r t statement
my $ i n s e r t = ”INSERT INTO $ t a b l e VALUES ( ” ;
f or (my $ i = 0 ; $ i < @values ; $ i ++) {
$ i n s e r t .= ” ’ $ v a l u e s [ $ i ] ’ ” ;
$ i n s e r t .= ” , ” i f ( $ i != $#values ) ;
}
$ i n s e r t .= ”) ” ;
$ r e s = $con−>exec ( ” $ i n s e r t ”) ;
print STDERR ( ” i n s e r t s t a t e m e n t : $ i n s e r t f a i l e d ! \ n ”)
i f ( $ r e s −>r e s u l t S t a t u s == Pg : : PGRES FATAL ERROR ) ;
}
c l o s e FILE ;
28
}
Listing 2: populate tables.pl
29