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):

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 Altesten 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

count =2 count Maier =2

Gruber

avg Maier

Gruber

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

Textgruppen: nur count

Bsp: Alteste, 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

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 DATENSATZE, FÜR DIE DAS GROUP BY ATTRIBUT DENSELBEN WERT HAT IN JE EINEN ZUSAMMEN.

4.) ENTFERNE ALLE DIESER GRUPPENDATENSATZE,

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# S

from Klasse

where Klassencode = (

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

select Zuname, Vorname

from Schüler

where S# = ( 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

LENGTH ('ROSE') = 4

ABS

SIGN

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

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äßt 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: TRITAR 4: QUATERNAR

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))

DATENSATZE EINFÜGEN:

INSERT INTO KUNDE VALUES (3276, 'MAIER')

DATENSATZE ANDERN:

UPDATE KUNDE

SET KNR = 5000

WHERE NAME = 'BERGER'

DATENSATZE 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.

KARDINALITATEN:

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

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

B B-5 B B-5

L L+10                                 L L+10

NICHT SERIELLE SCHEDULE

B B+5

L L+10

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

nicht serielle Schedule serialisieren

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.

A 2A+3

komplet hintereinander: T1, T2 A 2A+4

T2, T1 A 2A+2

Um zu verhindern, daß zwei Prozesse auf ein und denselben Datensatz zugreifen können, muß 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, daß 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, daß 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 muß. Bei dieser Lösung besteht jedoch die Gefahr, daß 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 muß 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 Anderungen 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.

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.

GRANULARITAT (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 BINARE SUCHE

(SEQUENTIAL SEARCH) (BINARY SEARCH)

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 NAGEL 10000