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äß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: 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#, Szfuß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, 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 Ä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 (220)
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
| Anmerkungen: |
| impressum | datenschutz
© Copyright Artikelpedia.com
