Technische Daten Organisation

RELATIONALE DATENBANKEN
RELATIONAL DATABASE


= Datenbank in Tabellenform / besteht aus Tabellen


Schüler





SQL

stuctured query language
(strukturierte Abfrage Sprache)




select bestimmt, welche Felder anzuzeigen sind.
from wählt aus der Datenbank eine Tabelle aus
where gibt die Bedingung an, die die Datensätze erfüllen müssen



Bsp: Alle Zunamen aus der Tabelle Schüler, die älter als 16 sind.

select Zn
from schüler
where A>16

Bsp: Alle Zunamen aus der Tabelle Schüler, die älter als 16 sind und 'Josef' heißen.

select Zn
from schüler
where A>16 and Vn='Josef'













Bsp:




I) Alle Zunamen aus der Tabelle Piloten, deren Flugstunden mehr als 1000 beträgt.

select Zn
from pilot
where Fst>1000

II) Alle Zunamen aus der Tabelle Piloten, die weniger als 500 Flugstunden haben
und 'Franz' heißen.

select Zn
from pilot
where Fst<500 and Vn='Franz'

III) Alle Zunamen aus der Tabelle Piloten, bei denen Vor - und Zuname gleich sind oder mehr als 2000 Flugstunden haben.

select Zn
from pilot
where Vn=Zn or Fst>2000






bilden eines KARTESISCHEN PRODUKTS

from S, V




Datensatz 2, 3 unnötig, deshalb:

where S.Verein=V.Verein JOIN






Bsp:




(from SF, PF)




I) Alle Stew. und Piloten mit gleicher Flugzeugnummer und mit älterer Stew. (als Pilot).

select SF.Stew, PF.Pilot
from SF, PF
where (SF.Flugzeug=PF.Flugzeug) and (SF.A>PF.A)

Bsp:




I) Autotypen (und Land) die aus England kommen.

select A.Autotyp, S.Land
from A, S
where A.Stadt=S.Stadt and S.Land='England'

II) Alle Autotypen die aus einer französischen Kleinstadt (EW<20000) kommen.

select A.Autotyp
from A, S
where A.Stadt=S.Stadt and S.Land='Frankreich' and S.EW<20000

Bsp:




I) Alle Schüler aus der 3HBb.

select Schüler
from Schüler
where Klasse='3HBb'



II) Alle Schüler die als Klassenvorstand den Schellner haben.

select Schüler
from Schüler, Klassen
where Schüler.Kc=Klassen.Kc and Klassen.Kv='Schellner'

III) Alle Schüler die einen Chemielehrer als Klassenvorstand haben.

select Schüler
from Schüler, Klassen, Lehrer
where Schüler.Kc=Klassen.Kc and Klassen.Kv=Lehrer.Lehrer and Fach='Chemie'

IV) Alle Schüler aus der tech. Abteilung mit einem Physiklehrer als Klassenvorstand.

select Schüler
from Schüler, Klassen, Lehrer
where Abt='tech.' and Schüler.Kc=Klassen.Kc and Klassen.Kv=Lehrer.Lehrer and Fach='Physik'

Bsp:




I) Alle Automarken aus amerikanischen Großstädten.

select Marke
from A, S, L
where A.Stadt=S.Stadt and EWS>20000 and S.Land=L.Land and Kontinent='Amerika'

II) Alle Automarken, die aus Städte kommen, in denen mehr als 1/10 der jeweiligen Landesbevölkerung wohnt.

select Marke
from A, S, L
where A.Stadt=S.Stadt and S.Land=L.Land and EWS>(0.1*EWL)


SYNONYM


select L, S
from Landesschulinspektor L, Schu... S
where L.xxxx S.xxxx














Bsp: Alle Mitarbeiter samt Stellvertreter.









select M.Zun, S.Zun
from Mitarbeiter M, Mitarbeiter S EQUIJOIN (Verknüpfung innerhalb einer Tabelle)
where M.Stv=S.MNr



Bsp:







I) Kind mit Großvater.

select M1.Kind, M2.Vater
from Mensch M1, Mensch M2
where M1.Vater=M2.Kind

II) Geschwister.

select M1.Kind, M2.Kind
from Mensch M1, Mensch M2
where M1.Vater=M2.Vater and M1.Kind not M2.Kind



Gruppenfunktionen (nur in select):

15, 5, 25

sum 45 Summe
max 25 Maximalwert
min 5 Minimalwert
avg 15 Durchschnitt (=sum/count)
count 3 Anzahl

Bsp:

Schüler

S# Vn Zn A Kl Schnitt


I) Alter des ältersten Schülers der 3HBc.

select max(A)
from Schüler
where Kl='3HBc'

II) Zunamen aller 17 - jährigen der 3HBc.

select Zn
from Schüler
where Kl='3HBc' and A=17

III) Durchschnittsalter der Schüler aus der 3HBc.

select avg(A)
from Schüler
where Kl='3HBc'

IV) größte Schülernummer der 3HBc.

select max(S#)
from Schüler
where Kl='3HBc'

V) Zunamen der ältesten Schüler der 3HBc.

select Zn
from Schüler
where A= (select max(A)
from Schüler SUBSELECT
where Kl='3HBc')










Bsp:



I) Maximaleinkommen.

select max(Einkommen)
from Konzern

II) Mitarbeiter mit dem maximalsten Einkommen.

select MName
from Konzern
where Einkommen= (select max(Einkommen)
from Konzern)

III) Mitarbeiter mit Land.

select MName, Land
from Konzern, Ort
where Konzern.Unternehmen=Ort.Unternehmen


GRUPPENFUNKTION

macht aus Gruppe von Werten EINEN Wert.

MAIER
MÃœLLER DISTINCT MAIER
MAIER → MÃœLLER
ROTH ROTH
ROTH

select distinct ZUNAME
KEINE KLAMMERN

Bsp: Welche Klassen gibt es an der Schule?

select distinct Kl
from Schule

I) Alter der Ältesten der Schule.

select max(A)
from Schule
where Geschlecht='weiblich'

II) Wie heißen die 19 - jährigen an der Schule?

select Zun
from Schule
where A=19




III) Wie heißen die ältesten Schüler der Schule?

select Zun
from Schüler
where A= (select max(A)
from Schüler)

IV) Welche Schüler sind älter als der Schuldurchschnitt?

select Zun
from Schüler
where A> (select avg(A)
from Schüler)

V) Welche Schüler der 3HBb sind jünger als der Jüngste der 4HBb?

select Zun
from Schüler
where Kl='3HBb' and A< (select min(A)
from Schüler
where Kl='4HBb')

Bsp:

select Zun
from Schüler
where Kl='4HBa' and
A< (select avg(A)
from Schüler
where Kl='4HBa')

a) Was ergibt dieses select - Statement?
Alle Schüler der 4HBa, die jünger sind als ihr Klassendurchschnitt.

b) Was ergibt es, wenn der rote Teil weggelassen wird?
Alle Schüler der 4HBa, die jünger sind als der Schuldurchschnitt.

c) Was ergibt es, wenn nur der blaue Teil fehlt?
Alle Schüler der Schule, die jünger sind als der Durchschnitt der 4HBa.

Bsp: Schüler (Name, Alter, Größe, Klasse)
Ort (Klasse, Stock)

I) Wie groß ist der größte Schüler der Schule?

select max(Größe)
from Schüler

II) Wie heißt er?

select Name
from Schüler
where Größe= (select max(Größe)
from Schüler)



III) Was passiert, wenn mehrere Schüler mit 2,03m am größten sind?

Abfrage1 ergibt 2,03
Abfrage2 ergibt die Namen dieser Schüler

Es werden also alle 2,03m große Schüler (und nicht bloss einer davon) ausgegeben.

IV) In welcher Klasse sitzt der größte Schüler der Schule?

select Klasse
from Schüler
where Größe=(select max(Größe)
from Schüler)
KÖNNEN NATÜRLICH AUCH MEHRERE SEIN!

V) In welchem Stock/welchen Stöcken sitzen 1,77m größe Schüler?

select distinct Stock
from Schüler, Ort
where Ort.Klasse=Schüler.Klasse and Größe='1,77'

VI) In welchem Stock/welchen Stöcken sitzen die größten der Schule?

select distinct Stock
from Schüler, Ort
where Ort.Klasse=Schüler.Klasse
and Größe= (select max(Größe)
from Schüler)

VII) Angenommen, alle Schüler sind verschieden groß. Wie groß ist der Zweitgrößte?

select max(Größe)
from Schüler
where Größe<(select max(Größe)
from Schüler)

VIII) Name des zweitgrößten Schülers.

select Zun
from Schüler
where Größe=(select max(Größe)
from Schüler
where Größe<(select max(Größe)
from Schüler)
)













SUBSELECTS UND LISTEN

SUBSELECT
LIEFERT

sicher nur 1 Wert vielleicht mehrere
select max(Alter) select Alter
from Schüler from Schüler

NUR 1 WERT WEGEN SELECT HOLT SPALTE AUS
GRUPPENFUNKTION TABELLE → MEHRERE WERTE

LISTENFUNKTIONEN
VERGLEICHEN EINEN
WERT MIT LISTE VON WERTEN
Bsp:




Bsp: Alle Schüler, die so heißen wie jemand aus der 3HBc.

select Zun, Klasse
from Schüler
where Klasse not '3HBc' and Zun in (select Zun
from Schüler
where Klasse='3HBc')

Bsp: Alle Schüler die älter sind als der jüngste der 3HBa.

select Zun
from Schüler
where Alter>min (select Alter
from Schüler
where Klasse='3HBa')
min (select Alter) == select min (Alter)






GROUP BY

GROUP BY KLASSE

1) Maier 17 1,86 4HBa
Gruber 18 1,90

2) Müller 17 1,82 3HBa
Berger 18 1,70

Blaue Kästchen können am Bildschirm nicht ausgegeben werden, sondern nur Gruppenfunktionen davon.

min 17 =17 avg 1,86 =1,88
18 1,90

count 17 =2 count Maier =2
18 Gruber

avg Maier
Gruber

Zahlengruppen: alle Funktionen (min, max, count, avg, sum)
Textgruppen: nur count

Bsp: Älteste, Durchschnittsgröße, Klasse.
18 1,88 4HBa
18 1,76 3HBa

select max(Alter), avg(Größe), Klasse
from Schüler
group by Klasse

Bsp: Liste aller Klassen mit Durchschnittsalter und Größe des größten Schülers.

select Klasse, avg(Alter), max(Größe)
from Schüler
group by Klasse

Bsp: Liste aller Altersgruppen mit Durchschnittsgröße.

select Alter, avg(Größe)
from Schüler
group by Alter

Bsp: Liste aller Klassen mit Zahl der Schüler über 18 Jahre.

select Klasse, count(Name)
from Schüler
where Alter>18
group by Klasse








Bsp: Liste aller Altersgruppen in den fünften Klassen mit Durchschnittsgröße.

select Alter, avg(Größe)
from Schüler
where Klasse like ('5*') oder Klasse in ('5HBa', '5HBb', '5HBc')
group by Alter

Bsp: Liste aller Stockwerke mit Durchschnittsalter, Schülerzahl und Größe des größten Schülers.

select Stock, avg(Alter), count(Name), max(Größe)
from Schüler, Ort
where Schüler.Klasse=Ort.Klasse
group by Stock

Bsp: Liste aller Klassen und deren Durchschnittsalter, die über 20 Schüler haben.

select Klasse, avg(Alter)
from Schüler
group by Klasse
having count(*)>20 having == where

WHERE: KEINE GRUPPENFUNKTIONEN!
HAVING: SCHON!

Bsp: Altersklassen (nur die, in denen die Schüler im Durchschnitt größer 1,80 sind) mit Schülerzahl.

select Alter, count(*)
from Schüler
group by Alter
having avg(Größe)>1,80

Bsp: Alter sortiert ausgeben.

select Name
from Schüler
order by Alter (desc) Alter aufsteigend sortiert

Bsp:




I) Name aller Piloten, mit maximaler Flugstundenanzahl.

select Name
from Pilot
where Fstd= (select max(Fstd)
from Pilot)

II) Name des Piloten mit Copilot.

select P1.Name, P2.Name
from Pilot P1, Pilot P2
where P1.CNr=P2.PNr



III) Alle Flugzeugtypen, dessen Piloten Berger heißen.

select Typ
from Pilot, KF
where Pilot.PNr=KF.PNr and Name='Berger'

IV) Namen der Piloten, die eine B747 fliegen können.

select Name
from Pilot, KF
where Pilot.PNr=KF.PNr and Typ='B747'

V) Flugzeugtyp mit erfahrensten Pilot.

select Typ, Name
from Pilot, KF
where Pilot.PNr=KF.PNr
group by Typ
having max(FStd)

VI) Zu jedem Flugzeugtyp die maximalste Flugstundenanzahl.

select max(Fstd), Typ
from Pilot, KF
where Pilot.PNr=KF.PNr
group by Typ


HAVING

ist die WHERE - BEDINGUNG für Gruppen.

Bsp: Alle Klassen, in denen das Durchschnittsalter>16 Jahre ist.

select Klasse
from Schüler
group by Klasse
having avg(Alter)>16
↑
HAVING kann nur Gruppenfunktionen enthalten,
WHERE darf keine enthalten.

Bsp: Alle Altersgruppen, in denen es mind. einen über 2 Meter großen Schüler gibt
und Größe (des größten) dieses Schülers.

select max(Größe), Alter
from Schüler
group by Alter
having max(Größe)>2.00








ORDER BY

Bsp: Gib die Schüler der 3HBc nach dem Alter geordnet aus.

select Name
from Schüler
where Klasse='3HBc'
order by Alter

Im ORDER - BY Teil können die Attribute oder Gruppenfunktionen von Attributen stehen,die auch im SELECT - Teil stehen oder stehen können.

select Name avg(Alter) könnte auch nicht
from Schüler FALSCH !! im SELECT stehen,
order by avg(Alter) weil nicht gruppiert wird.

Bsp: Gib alle Klassen nach dem Durchschnittsalter geordnet aus.

select Klasse
from Schüler
group by Klasse
order by avg(Alter)



Man kann im ORDER - BY Teil auch mehrere Attribute angeben.

select Name 1HBa 14
from Schüler 1HBa 15
order by Klasse, Alter 1HBb 14
1HBb 15

→ Liste aller Schüler nach Klassen geordnet, innerhalb einer Klasse sind sie nach ihrem
Alter geordnet.

select Name 1HBa 14
from Schüler 1HBb 14
order by Alter, Klasse 1HBa 15
1HBb 15

→ Liste aller Schüler nach dem Alter geordnet. Gleichalte werden nach der Klasse geordnet.

















NULL - VALUES

NULL bedeutet "GIBT ES NICHT"


Mitarb# Name Chef#

3 Maier 5 Müller hat keinen
5 Müller NULL Chef.
2 Huber 0
0 Berger 3 Huber hat einen Chef.
Er hat Nummer 0.

NULL ist nicht dasselbe wie 0!

Bsp: Alle Mitarbeiter ohne Chef.

select Name
from Mitarbeiter es gibt auch:
where Chef# is null ... is not null

Bsp: Alle Untergebenen von Berger.

select Name
from Mitarbeiter
where Chef#=0



select count(*) select count(Chef#)
from Mitarbeiter from Mitarbeiter
↓ ↓
4 3

Datensätze mit NULL nicht gezählt
NULL gezählt























SQL - REIHENFOLGE





1.) BILDE KARTESISCHES PRODUKT ALLER TABELLEN
AUS DEM FROM.


2.) ENTFERNE ALLE ZEILEN, DIE DAS WHERE NICHT ERFÃœLLEN.


3.) FASSE ALLE DATENSÄTZE, FÜR DIE DAS GROUP BY ATTRIBUT DENSELBEN WERT HAT IN JE EINEN ZUSAMMEN.


4.) ENTFERNE ALLE DIESER GRUPPENDATENSÄTZE,
DIE DIE HAVING BEDINGUNG NICHT ERFÃœLLEN.


5.) SORTIERE DAS ERGEBNIS NACH ORDER BY.


6.) GIB NUR DIE ATTRIBUTE / GRUPPENFUNKTIONEN VON
ATTRIBUTEN AUS, DIE IM SELECT VORKOMMEN.










SUBSELECTS, DIE PAARE ERGEBEN

Bsp: Größe + Alter aller Schüler der 3HBb.

select Größe, Alter 1,87 16 1 Schüler
from Schüler 1,72 16 1 Schüler
where Klasse='3HBb' 1,78 15 1 Schüler

Bsp: Welche Schüler sind genauso groß und alt wie jemand aus der 3HBb?

select Name
from Schüler
where (Größe, Alter) in ( select ...




Bsp:




I) Die Namen der Sportler mit der Disziplin Weitsprung sollen geordnet ausgegeben werden.

select Name
from Sportler
where Disziplin='Weitsprung'
order by Name

II) Namen aller 100m - Läufer mit Bestleistung unter 10 sek. nach Bestleistung geordnet.

select Name
from Sportler
where Disziplin='100m Lauf' and Bestleistung <10
order by Bestleistung

III) Nach Disziplin geordnet, die Ausgabe der besten & schlechtesten Leistung der über 40 - jährigen.

select Disziplin, max(Bestleistung), min(Bestleistung)
from Sportler
where Alter>40
group by Disziplin

IV) Namen aller Sportler die in Oslo dabei waren.

select Name
from Sportler, Teilnahme
where Sportler.Nr=Teilnahme.Nr and Ort='Oslo'




V) Alle Orte gruppiert ausgeben, dessen Teilnehmerdurchschnittsalter größer als 30 ist und
dessen Meeting in der 52 - sten Woche stattgefunden hat.

select Ort
from Sportler, Meetings, Teilnahme
where Sportler.Nr=Teilnahme.Nr and Teilnahme.Ort=Meetings.Ort and Datum=52
group by Ort
having avg(Alter)>30

VI) Namen aller Weitspringer, dessen Leistung von der Bestleistung maximal 1m abweicht.

select Name
from Sportler
where Disziplin='Weitsprung' and Bestleistung+1>(select max(Bestleistung)
from Sportler
where Disziplin='Weitsprung')

Bsp:
nicht Katnr., sondern eindeutig
Schüler (S#, Zuname, Vorname, Alter, Geschlecht, Klassencode)
M/W
Klasse (Klassencode, Stock, Sprecher#)
eine S#

I) Liste aller Klassensprecher (Namen !) aus dem 2. Stock, geordnet nach dem Alter.

select Name
from Schüler, Klasse
where Schüler.S#=Sprecher.S# and Stock=2
group by Alter

- ) Im Subselect.

select Name
from Schüler
where Schüler.S# in (select Sprecher.S#
from Klasse
where Stock=2
order by Alter)

II) Wieviele Klassensprecherinnen gibt es?

select count(*)
from Schüler, Klasse
where Geschecht='W' and Schüler.S#=Klasse.Sprecher#

III) Wer ist um 1 Jahr älter als der Klassensprecher?

select S.Zuname
from Schüler S, Schüler Spr, Klasse K
where Spr.S#=K.Sprecher# and
S.Klassencode=K.Klassencode and
S.Alter=Spr.Alter+1




IV) Klassen mit mehr als 3 Volljährige.

select Klassencode
from Schüler
where Alter>18
group by Klaseencode
having count(*)>3

V) Klasse mit 5 gleiche Vornamen.

select distinct S1.Klassencode
from Schüler S1, Schüler S2, Schüler S3, Schüler S4, Schüler S5
where S1.Vorname=S2.Vorname and S2.Vorname=S3.Vorname and
S3.Vorname=S4.Vorname and S4.Vorname=S5.Vorname

VI) In welchen Klassen sitzen mehr als 3 Minderjährige?

select Klassencode
from Schüler
where Alter <= 18
group by Klassencode
having count (*)>3

VII) Liste aller Klassenschülerzahlen.

select count(*) ♣
from Schüler
group by Klasse

VIII) Welche Klasse ist am größten?

select Klassencode ♥
from Schüler
group by Klassencode
having count (*) = max( ♣ )

IX) Welche Nummer hat der Sprecher der größten Klasse?

select Sprecher# ♠
from Klasse
where Klassencode = ( ♥ )

X) Wie heißt der Sprecher der größten Klasse?

select Zuname, Vorname
from Schüler
where S# = ( ♠ )










VERGLEICHE IN SQL


= (nicht == wie in C)> <>= <=

<> (nicht != wie in C) AND OR NOT


BETWEEN: where Alter between 16 and 17

IN: where Klasse in ('5a', '5b', '5c')

LIKE: % beliebig viele Zeichen (DOS: *)
_ 1 beliebiges Zeichen (DOS: ?)

where Name like '%MANN'

→ BAUMANN, AMANN, MANN

where Name like '_MANN'

→ AMANN


FUNKTIONEN IN SQL


+, - , /, *

|| hängt Zeichenfolgen zusammen

NVL (a, b) = a, falls a nicht NULL
↓
b, sonst ≠0 !!!

LENGTH ('ROSE') = 4

ABS ( - 7) = 7

SIGN ( - 7) = - 1

SUBSTR ('VOLLMOND', 5, 4) = 'MOND'
↓
5. 4













SQL DETAILS


- select count(*)
from Schüler

Anzahl aller Schüler


- select count (Religion)
from Schüler

Anzahl aller religiösen Schüler
(NULL - Values werden nicht gezählt)


- select count (distinct Religion)
from Schüler

Anzahl aller Religionen an der Schule
(NULL - Values werden nicht, alle anderen nur 1x gezählt)






- select avg (sum (Alter)), sum (avg (Alter))
from Schüler
group by Klasse


avg (sum (Alter)):

1.) pro Klasse Gesamtalter

2.) davon Durchschnitt über alle Klassen


sum (avg (Alter)):

1.) pro Klasse Durchschnittsalter

2.) davon Summe über alle Klassen











OUTER JOIN







select *
from S, V
where S.Verein=V.Verein






3. Datensatz aus S hat keinen Partner aus V → wird ignoriert


select *
from S, V
where S.Verein=V.Verein (+)






(+) auch Datensätze ohne Partner werden ausgegeben






















DATENBANKENTWURFSREGEL #1

KEINE VERSTECKTEN JOINS !!!




statt dieser Tabelle könnte man auch





entwerfen.


SCHLÃœSSEL
(engl.: KEY)

Steht die Postleitzahl fest, so ergibt sich daraus eindeutig der Bezirk PLZ → BEZIRK
Bezirk ist von PLZ funktional abhängig.

Sozialversicherungs# → Geburtsdatum
Katalognr. → Name, aber
Schuljahr, Schule, Klasse, Kat# → Name
Schuljahr, Schule, Klasse, Woche → Klassenordner
Schuljahr, Schule, Klasse, Fach → Lehrer
Bestellnr., Produkt → Menge
Datum, Ort → Niederschlagsmenge

Beistrich bedeutet: UND

Ein Attribut oder eine UND - Verknüpfung von Attributen, von dem/der alle anderen Attribute
der Tabelle abhängen, heißt:

Schlüsselkandidat (candidate key)

Mitarb# Name Adresse Abteilung







Der Schlüsselkandidat, der gewählt wird, heißt:

Primärschlüssel (primary key)

Tabelle "Mitarbeiter" ist Join von "Employee" und "Abteilung"

Mitarbeiter = select Mitarb#, Mitarbname, ...
from Employee, Abteilung
where Employee.Abt# = Abteilung.Abt#

Eine informationsverlustfreie Zerlegung (lossless decomposition / nonloss decomposition)
von "Mitarbeiter" in "Employee" und "Abteilung" ist daher möglich.

→ Nachteile von zerlegbaren Tabellen

I)
Bürohilfe vertippt sich und schreibt bei Fischer "Einkaufsabt" statt "Verkaufsabt"
→ laut Datenbank ist Abt. 3 mal die Einkaufs - , mal die Verkaufsabt.
→ Widerspruch (inconsistency). Vertippt sie sich in Tabelle "Abteilung",
so ist die Datenbank falsch, aber nicht widersprüchlich
II)
Zu hoher Speicherverbrauch (redundancy)
III)
Werden alle Mitarbeiter der Produktion gelöscht, so weiss man nicht mehr,
welche Abteilungs# sie hat. (deletion anomaly)

→ lässt sich Tabelle x verlustfrei in die Tabellen y und z zerlegen, so wirft man x weg und
nimmt y und z in die Datenbank auf.

Angenommen, es kommt noch Berger - Cessna - Hong Kong zu Flug1 dazu, wie kann Flug1 dann zerlegt werden?

GAR NICHT !

Wie sollen wir beim Entwurf entscheiden, ob die Datenbank Flug1 oder die 3 anderen Tabellen enthalten soll?

Wir fragen Kunden:

wenn Pilot x Flugzeug y fliegen darf
und Pilot x Flughafen z kennt
und Flugzeug y auf Flughafen z landen darf

Darf dann x mit y automatisch auf z landen?

JA → 3 Tabellen NEIN → 1 Tabelle






Sport und Fremdsprachen haben nichts miteinander zu tun → man kann die Tabelle ohne
Informationsverlust aufspalten.



DATENBANKENTWURF
(engl.: DATA BASE DESIGN)


ENTITY:

Alle Menschen, Sachen, Orte, ... über die wir Informationen speichern wollen.

Bsp: Schüler Maier, 3hbc, TDO, Schüler Müller, 2. Stock, 5hba, 1.Stock, PR


ENTITY TYPE:

Zusammenfassung gleichartiger Entities

Bsp: Schüler, Klasse, Fach, Stock


RELATIONSHIP:

Beziehung zwischen Entities

Bsp: 3hbc hat_Klassenzimmer_in 2. Stock
Hasitschka unterrichtet 4hba in TDO
4hba hat_Klassenzimmer_in 2. Stock


RELATIONSHIP TYPE:

Beziehung zwischen Entitiytypes

Bsp: Klasse hat_Klassenzimmer_in Stock
Lehrer unterrichtet Klasse in Fach


ATTRIBUTE:

Eigenschaft einer Entity

Bsp: Maier: 17 Jahre, katholisch, Eishockey


ATTRIBUTE TYPE:

Zusammenfassung der Attributes

Bsp: Schüler: Alter, Religion, Sport


ENTITY TYPE → TABELLE
ENTITY → DATENSATZ = TABELLENZEILE
ATTRIBUTE TYPE → SPALTE
ATTRIBUTE → TABELLENZELLE




Bsp: Schraubenhersteller

I) Suche alle Entity Types, über die Information gespeichert werden soll.

Schraubentyp, Mitarbeiter, Kunde, Fabrik

II) Suche zu jedem die zu speichernden Attribute.

Schraubentyp (Typbezeichnung, Länge, Ganghöhe)
Mitarbeiter (Name, Einstellungsdatum)
Kunde (Name, Adresse)
Fabrik (Adresse, Kapazität)

III) Wähle oder erfinde Schlüssel.

Schraubentyp (Typbez., Länge, Ganghöhe)
Mitarbeiter (M#, Name, Einstellungsdatum)
Kunde (K#, Name, Adresse)
Fabrik (Adresse, Kapazität)

IV) Mache aus jeder Entity Type eine Tabelle.

SQL: create table Schraubentyp ...

V) Suche alle Relationship Types, über die Information gespeichert werden soll.

Mitarbeiter arbeitet_in Fabrik
Schraubentyp wird_hergestellt_in Fabrik
Kunde bestellt Schraubentyp bei Mitarbeiter

VI) Suche speicherungswürdige Attribute der Beziehungen:

arbeitet_in: Dienstbeginn, Funktion
wird_hergestellt_in: max. Tagesproduktion
bestellt_bei: Bestellmenge, Datum

VII) Welche der Beziehungen sind 1:n ?

Jeder Mitarbeiter arbeitet_in (<=) einer Fabrik
In jeder Fabrik arbeiten (<=) mehrere Mitarbeiter

Einer : Mehrere = 1 : n Beziehung

Welche Beziehungen sind m : n ?

Jeder Schraubentyp wird_hergestellt_in (<=) mehreren Fabriken
In jeder Fabrik werden_hergestellt (<=) mehrere Schraubentypen

Mehrere : Mehrere = m : n Beziehung









Welche sind Mehrfachbeziehungen ?

Kunde bestellt Schraubentyp bei Mitarbeiter


3 Entity Types an Beziehung beteiligt

3: TRITÄR 4: QUATERNÄR

VII) Trage 1 : n Beziehungen in die Datenbank ein.

Wie halte ich in der Datenbank fest, wer in welcher Fabrik arbeitet ?

a)
Spalte "Mitarbeiter" in Tabelle Fabrik.
schlecht:
können beliebig viele sein → wieviel Byte sollen für dieses Attribut
pro Zeile reserviert werden ?

b)
Spalte "Fabrik" in Tabelle Mitarbeiter.
gut:
kann immer nur eine sein

Aber was wird dort eingetragen ?
Kapazität, Adresse, beides ?
Etwas, was eindeutig klarmacht, in welcher Fabrik der Mitarbeiter arbeitet
→ Mitarbeiter bekommt neue Spalte mit dem Schlüssel von Fabrik.




IX) Wie halte ich m : n Beziehungen fest ?

(z.B.: welcher Schraubentyp wird in welcher Fabrik gefertigt ?)

a)
Ich speichere bei Fabrik die Schraubentypen. schlecht: siehe VIII)
b)
umgekehrt. schlecht: siehe VIII) → neue Tabelle Fertigung.






X) Mehrfachbeziehungen → eigene Tabelle



↑ ↑ ↑
Schlüssel Schlüssel Schlüssel
aus aus aus
Kunde Mitarbeiter Schraubentyp

XI) Füge Beziehungsattribute zur Datenbank.

Bestellmenge, Datum neue Spalten der Tabelle "Bestellung"
max. Tagesproduktion Spalte von "Fertigung"
Dienstbeginn, Funktion neue Spalten unter "Mitarbeiter"

XII) Entferne

- ableitbare Beziehungen

DERIVABLE RELATIONSHIP (Spitalsbeispiel)

- ableitbare Attribute

DERIVABLE ATTRIBUTE (Geburtsdatum, Alter) → Geburtsdatum

- zerlegbare Tabellen

XIII)
TABELLEN BAUEN:

CREATE TABLE KUNDE (KNR NUMBER(4) NOT NULL, NAME CHAR(30))

DATENSÄTZE EINFÜGEN:

INSERT INTO KUNDE VALUES (3276, "MAIER")

DATENSÄTZE ÄNDERN:

UPDATE KUNDE
SET KNR = 5000
WHERE NAME = 'BERGER'

DATENSÄTZE LÖSCHEN:

DELETE FROM KUNDE
WHERE NAME = 'MÃœLLER'

TABELLEN LÖSCHEN:

DROP TABLE KUNDE




ENTITY - RELATIONSHIP DIAGRAMME
(ERD)


Helfen / Stören beim Datenbankdesign.
Zeigen keine Entities & Relationships, sondern Entity Types und Relationship Types.














KARDINALITÄTEN:




1 : n, m : n Höchstkardinalitäten entscheiden

Jeder Lehrer unterrichtet mind. 0, höchstens mehrere Schüler. Jeder Schüler wird von mind. 1,
höchstens mehreren Lehrern unterrichtet.


ERD - SCHRAUBENHERSTELLER
(ohne Attribute)






1 kann_bedienen
2 steht_in
3 arbeitet_in

Wichtigste ERD - Regel: keine ableitbaren Beziehungen !







1 liegt_in
2 steht_in
3 belegt

1 ist überflüssig: 2, 3 rechen, um herauszufinden, in welchem Spital Patient x liegt
→ 1 könnte gestrichen werden.
Könnte man auch 1 lassen und 2 streichen ? JA
1 3 NEIN


VORÃœBUNGEN DATENBANKENTWURF

Man gebe einen Schlüssel an für

- eine Tabelle der Schüler der 4 Hbc Kat#

- eine Tabelle aller derzeitigen Wiener Schüler Kat#, Schulcode, Klassencode

- eine Tabelle aller Schüler, die jemals die Ungargasse besucht haben

Eintrittsjahr, Klassencode i. d. 1. Kl., Kat# in der 1. Klasse

Man gebe Kardinalitäten, Beziehungstyp an

- Mieter bewohnen Haus
m : n

- Patient bekommt vom Arzt eine Medizin verschieben




- Mutter hat Kinder
1 : n

- Programmierer schreibt Programm in Sprache

Programmierer
Programm
Sprache

Das sind 2 m : n Beziehungen → neue Tabellen

- Mensch übt Beruf aus
m : n











Bsp: Firma beliefert Baustelle mit Bauteile. Baustelle und Lieferfirma sind in einer bestimmten Stadt.
Die Bauteile können auch aus mehrere Bauteiltypen bestehen.




Baustelle (Baustellen#, Kbez, Name, Land,
Bauteiltyp (Typ#,
Stadt (Name, Land)
Lieferant (Lieferanten#, Name, Land,
LF (Lieferanten#, Typ#,
Bedarf (Baustellen#, Typ#, Menge,
Teil (Typ#1, Typ#2, Menge,

Bsp: Kinokette (siehe Kopie)




KINO : Kname, Adresse
SAAL : Kname, S#, Größe
REIHE : Kname, S#, R#, Sz_fußfr
SITZ : Kname, S#, R#, Sz#
FILM : F#, K#, Titel
MITWIRK : F#, K#





ZUSAMMENGESETZTER SCHLÃœSSEL
(engl.: COMPOSITE KEY)

1) Wieviele Pizze 23.2. ausgeliefert?
2) Was ist auf einer Margarita drauf?
3) Wieviele Pizze hat Hr. Pfannh. bestellt?
4) Wieviele kg Paradeiser wurden am 14.5. verbraucht?
5) Wer ist der fleißigste Mitarbeiter?




Mitarb. (M#, Mname,
Lieferung (L#,
.
.
.


Bsp: Bankomat

Konto BKM - KARTE

Ko# Inh Stand Ka# Code

Konto

Ko# Inh Stand

Karte

Ka# Code Ko#

K# Inh Stand Code




SCHUL - ERD (XESAS)

Um etwas vertrauter mit ERDs zu werden hier noch ein kleines Beispiel der HTL Ungargasse.












Folgende Tabellen werden erstellt:

Schüler (Name, Vname, Aufnahme#, Gebdatum, Staatsbürgerschaft, Muttersprache, Quartier, Adresse, Plz, Ort, Land, Erziehungsberechtigter, Privatnummer, Firmennummer, Status, Behinderung, Vorschule, KKZ, Abtcode, PKZ)

Laufbahn (Name, Vname, Gebdatum, Sjahr, KKZ, Abtcode, PKZ)

Klasse (KKZ, Abtcode, PKZ, KV, Raum)

Fach (Fbezl, Fbezk, Fbezaltern)

Stundentafel (KKZ, Abtcode, Fbezk, Std)

Lehrer (Lehrer#, Name, Vname, Akgrad, Staatsbürgerschaft, Muttersprache, Adresse, Plz, Land, Privnummer, Firmennummer)

Stundenplan (KKZ, Abtcode, PKZ, Fbezk, Raum, Ausweichraum, Tag, Stunde, L#, Unterrichts#)

Zeugnis (Aufnahme#, KV, Snote, Enote)

usw. usw. usw.





TRANSAKTIONEN


Filiale 1 ZENTRALE Filiale 2

B	20/10			B	20/10		10	B
L	40/50			L	40/50

read B read B
B = B - 10 B = B+5
write B ATOMAR (atomic) write B
read L Atomizität
L = L+10
write L


Die zeitliche Reihenfolge in der die Transaktionen ablaufen, heißt SCHEDULE .


SERIELLE SCHEDULE

Transaktion 1	Transaktion 2	Transaktion 1	Transaktion 2
read B			read B
.			.
.			.
.			.
write L			write B
	read B	read B
	.	.
	.	.
	.	.
	write B	write L

B → B - 5 B → B - 5
L → L+10 L → L+10















NICHT SERIELLE SCHEDULE

Transaktion 1	Transaktion 2
read B			T1:	B = 20
B = B - 10				B = 10
	read B		T2:		B = 20
write B			T1:	B = 10
read L				L = 40
	B = B+5		T2:		B = 25
	write B				B = 25
L = L+10			T1:	L = 50
write L				L = 50

B → B+5
L → L+10


Eine Schedule ist dann serialisierbar, wenn sie die Wirkung einer seriellen Schedule hat.

nicht serielle Schedule → serialisieren

Transaktion 1	Transaktion 2
read B
B = B - 10
write B
read L
	read B
	B = B+5
	write B
L = L+10
write L

B → B - 10+5
L → L+10


serialisierbar

nicht seriell









seriell






LOCKING (SPERREN)
(LOCK = SPERRE)


Flug Platz S304 ist schon vergeben, wenn 1 sonst 0






Langt Transaktion 1 vor Transaktion 2 ein, bekommt Edlauer das Ticket.
Langt Transaktion 2 vor Transaktion 1 ein, bekommt Haselberger das Ticket.

Transaktion 1	Transaktion 2	Transaktion 1	Transaktion 2
read S304	read S304	read A
if (S304==1) sorry	if ( .....	A = A+1
else {	.	write A
Buchung vornehmen	.		read A
Beleg drucken	.		A = A*2
S304=1			write A
write S304		read A
}		A = A+1
		write A

A → 2A+3

komplet hintereinander: T1, T2 A → 2A+4
T2, T1 A → 2A+2





Um zu verhindern, dass zwei Prozesse auf ein und denselben Datensatz zugreifen können, muss man locken.
Unter locking versteht man das Sperren der Daten um anderen Transaktionen den Zugriff auf den gesperrten Datensatz zu verwehren.

Ein Beispiel wären die 80x86 Prozessoren. Will man verhindern, dass Interrupts während einer bestimmten
Transaktion zugelassen werden, so wird das Interrupt enable - Flag gelöscht (cli).
Um Interrupts wieder zuzulassen, wird das Kommando (der Befehl) sti benutzt.


lock B A gelockt
read B
B = B - 10 Filiale 1 Filiale 2
write B
unlock B cli cli
A anschauen A anschauen
lock L A setzen A setzen
read L sti sti
L = L+10
write L
unlock L

Es ist möglich, dass Transaktionen nicht nach Reihenfolge des Eintrettens, sondern nach Prioritäten abgearbeitet werden. Dies ist manchmal erforderlich, wie beispielsweise bei einem Space Shuttle die Sauerstoffversorgung, die die höchste Priorität zugewiesen bekommt, da sie immer aufrecht erhalten werden muss. Bei dieser Lösung besteht jedoch die Gefahr, dass eine Transaktion mit geringer Priorität immer nach hinten gereiht wird, da wichtigere Transaktionen auszuführen sind. Ist dies der Fall, so spricht man von einem

LIVELOCK (STARVATION)

Wartet eine Transaktion T1 auf den Datensatz B und eine andere Transaktion T2 auf den Datensatz A, wobei die beiden Datensätze von der jeweilig anderen Transaktion gelockt wurden, so nennt man diese Situation einen

DEADLOCK

lock A
lock B
lock B
lock A

Eine andere Möglichkeit um Live/Deadlocks zu verhindern, ist es einen Graphen (Dead Lock Graph) zu zeichnen.


Trans -
aktion 1

Trans - Trans -
aktion 1 aktion 2
Trans - Trans -
aktion 2 aktion 3



Ein Deadlock ist dann vorhanden, wenn man einen geschlossenen Kreis zeichnen kann. Ist das der Fall, so muss ein Knoten gelöscht werden, um den Deadlock aufzulösen.





Regeln:

I) LOCKEN
II) REIHENFOLGE BEIM LOCKEN
III) 2 - PHASEN LOCKEN

!!! 2 - Phasen Locken verhindert keinen Deadlock →

Transaktion 1 Transaktion 2

lock A
lock B Trans - Trans -
lock B aktion 1 aktion 2
lock A
unlock A circle
unlock B
unlock A
unlock B


cycle





ROLLBACK

Läuft eine Transaktion nicht bis zum Ende, aus welchem Grund auch immer, so sollen die bis dahin eingegebenen Daten nicht geändert bleiben, sondern müssen zurückgesetzt werden. Dieses Zurücksetzen wird im allgemeinen Rollback bezeichnet. Ein Rollback kann aus vielen verschiedenen Gründen notwendig werden. Zum Beispiel wenn der User am Ende der Dateneingabe auf "Cancel" drückt, so sollen die Änderungen nicht gesichert werden oder wenn das System abstürzt oder andere Komplikationen auftreten.

Transaktion 1

lock E
lock H
read E CASCADING ROLLBACK
read H
H = H+1Mio. Rollback es entsteht eine
write H Rollback - Lawine
unlock H
if (E>=1Mio.) E = E - 1Mio. COMMIT POINT
else write E
unlock E = an keinem Deadlock beteiligt, wenn die OK - Taste betätigt, keine Division durch 0, keine falschen
Berechnungen, ...
2 - Phasen Commit:

I) COMMIT POINT
II) WRITES
III) UNLOCKS



PROTOKOLLE (PROTOCOLS)


Unter einem Protokoll versteht man allgemein gültige Regeln an die sich alle zu halten haben.

Transaktion 1				Z
lock A, B, C
read A, B, C			HD
A = A+12	Absturz macht nichts
B = B*2			Ich bin T1		Ich bin T3
C = C*C	Commit Point		ich plane:		ich plane:
write A			write A = 19		write X = 4
write B	Absturz fatal		write B = 30	LOG	.. .
write C			write C = 4
unlock A	Absturz macht nichts		Ende		... wenn kein Ende,
unlock B					dann ist nichts
unlock C					passiert

Um auch das Risiko noch weiter zu verringern werden sogenannte LOGs angelegt. In diesen LOGs steht welche Transaktion, welche Veränderungen plant. Tritt nun eine unvorhergesehene Unterbrechung auf, so kann man anhand des LOGs rekonstruieren was geplant, bzw. mit Vergleich der aktuellen Daten, was verändert wurde. Jedes LOG wird sicherheitshalber 2x auf lokale Speichermedien gesichert, die womöglich noch räumlich getrennt sein sollten.


GRANULARITÄT (GRANULARITY)


Dieses Kapitel beschäftigt sich damit was gesperrt werden soll (Tabelle, Datensatz, Zelle), d.h. mit der richtigen Größe der zu sperrenden Objekte (= Feinkörnigkeit).

Access Optionen zu diesem Thema sind z.B. exlusiv öffnen, Tabelle locken.
Access Basic Befehle zu diesem Thema sind z.B. Move First, Move Last (Bewegt Bleistift), Lock Edits,
Begin Trans, Commit Trans, Rollback.






... 19


19











SUCHEN (SEARCHING)


SEQUENTIELLE SUCHE BINÄRE SUCHE
(SEQUENTIAL SEARCH) (BINARY SEARCH)

	a[0]	Zelle für	halbieren		a[0]
	a[1]	Zelle	und Element		a[1]
	a[2]	durchgehen	zuordnen		a[2]
			wieder halbieren
						7
						19
						112
	a[127]				a[127]

Elemente 2 4 8 16 32 1Mio.

sequentiell 1 3 7 15 31 ca. 1Mio.

binär 1 2 3 4 5 20 (2²⁰)


INDEXDATEI


1: 7 MÃœLLER ABRAHAM 3
2: 19 MAIER BERGER 4
3: 244 ABRAHAM MAIER 2
4: 12 BERGER MÃœLLER 1
KURZ 7000001
PREM 7000002
KUNDE - BESTELLUNG

B# K#
7 MAIER 303 8 SCHRAUBEN 1000 select *
12 MÃœLLER 528 7 MUTTERN 5000 from Kunde K, Bestellung B
8 ABRAHAM 119 8 SCHEIBEN 2500 where K.K# = B.K#
404 23 NÄGEL 10000

		7	MAIER	528	7	MUTTERN	5000
		8	ABRAHAM	303	8	SCHRAUBEN	1000	INNER
		8	ABRAHAM	119	8	SCHEIBEN	2500	JOIN
LEFT -	+
OUTER JOIN		12	MÃœLLER	NULL	NULL	NULL	NULL
RIGHT -	+
		NULL	NULL	404	23	NÄGEL	10000

1821 Worte in "deutsch"  als "hilfreich"  bewertet