wintersemester 2010. ist das derzeit wichtigste datenmodell wurde von codd e.f. in den 70ern...
TRANSCRIPT
Datenmodellierung, Datenbanksysteme,
Anwendungen
Wintersemester 2010
Ist das derzeit wichtigste Datenmodell Wurde von Codd E.F. in den 70ern
entwickelt Konsistenzprobleme werden durch einen
Normalisierungsprozess beseitigt
Logische Datenmodellierung
Das Relationenmodell
MNR Name Vorname Gehalt
1 Müller Fritz € 2000
2 Meyer Franz € 2500
Zeile/ Tupel
Spalte/ Attribut
Relation Mitarbeiter
Mengen sind die Wertebereiche (Domains) der beteiligten Attribute
R dom (A1) x dom (A2) x dom (A3)
…
…
…
...
………….
2500 €FranzMeyer2
2000 €FritzMüller1
GehaltVornameNameMNR
Mitarbeiter= {(1, Müller, Fritz, 2000 €, …), (2, Meyer, Franz, 2500 €, …),…}.
Vorname+ = (Fritz, Otto, …) Qualifikation* = (Administrator, Designer) Adresse = (Straße, Hausnummer, PLZ, Ort)
Daraus folgt: Mitarbeiter = {(1, Müller, (Fritz, Otto), 2000
€, (Weg, 3, 12345, Stadt), (Administrator, Designer) )}.
Wiederholungsgruppenund Zusammensetzungen
* = Wiederholungsgruppe (0,1,2,…)+= Wiederholungsgruppe (1,2,3,…), mind. einmal( )= Zusammengesetztes Attribut
Ai ist i-tes Attribut mit i=1, …, n.
◦ R=(A1, A2, …, An) Primärschlüssel kann auch durch eine Raute dargestellt
werden:◦ R=(A1, A2, A3, A4,…., An)◦ R=(#(A1,A3), A2, A4, …, An)
Wiederholungsgruppen und Zusammensetzungen:
◦ R = (A1, A2, A3*, A4)Mit: A3 = (A31, A32) und A4 =(A41, A42).
Darstellung von Relationen außerhalb einer Tabelle
* = Wiederholungsgruppe (0,1,2,…)+= Wiederholungsgruppe (1,2,3,…), mind. einmal( )= Zusammengesetztes Attribut
Vorgang wird auch Unnormalisierung genannt.◦ RE = (A1, …, An)
Wiederholungsgruppen und Zusammensetzungen werden zunächst in identischer Notation auf die Relation übertragen.
TransformationÜbertragung eines ER-Modells
E1 B E2
RE1 = (S1, …) RE2 = (S2, …) RB = (S1, S2, BA1, …, BAn) –
Primärschlüssel offen
Transformation Übertragung der Beziehungstypen
E1 B E2
Entities und Beziehungstypen können strukturgleich auf Relationen übertragen werden◦ Bauteil = (BNR, …)◦ Firma = (FNR, …)◦ Lieferant = (LNR, …)◦ L = (BNR, FNR, LNR, Preis, Menge) – Primärschlüssel offen
TransformationSonderfall des dreistelligen Beziehungstyps
Bauteil L
Menge Preis
Lieferant
Firma
Lieferanten beliefern Firmen mit Bauteilen
Voraussetzung: Jede Postleitzahl entspricht einem Ort
Transformation des 1:1 Beziehungstyps
PLZ Ort1 1gehört zu
PLZ
S1
Ort
S2
Auflösung
S1 PLZ
1 8010
2 1010
3 6020
S2 Ort
1 Graz
2 Wien
3 Innsbruck
Identifikation durch S1, oder S2 aber ein Schlüssel (S1, S2) verletzt die geforderte Minimaleigenschaft. (Attribut kann entfernt werden!)
PLZ = (S1, PLZ, S2 (Fremdschlüssel)) Ort = (S2, Ort)
oder:
PLZ = (S1, PLZ)Ort = (S2, Ort, S1(Fremdschlüssel) )
Transformation des 1:c-Beziehungstyps
Mitarbeiter Leitet Abteilung1 C
Name
MNR
Abtname
ABTNR
Anschrift
Auflösung Mitarbeiter = (MNR, …) Abteilung = (ABTNR, …) L = (MNR, ABTNR). –> eines von
beiden ist Primärschlüssel◦ oder:
Mitarbeiter = (MNR, …, ABTNR) Abteilung = (ABTNR, ….)
◦ oder: Mitarbeiter = (MNR, …) Abteilung = (ABTNR, …, MNR).
Möglichkeit 1:◦ Mitarbeiter = (MNR, …) ◦ Abteilung = (ABTNR, …) ◦ gehört zu = (MNR, ABTNR)
Möglichkeit 2◦ Mitarbeiter = (MNR, …, Gehört_zu_Abteilung
(AbtNr))◦ Abteilung = (ABTR, …).
Transformation des 1:n-Beziehungstyps
Mitarbeiter Abteilungn 1gehört zu
Semantisch verständlicher Fremdschlüssel
Kunde = (KNR, …) Artikel = (ArtNR, …) Kauft = (ArtNR, KNR; Anzahl, Datum, LNR)
◦ Kunde kann gleichen Artikel an verschiedenen Tagen kaufen: #(ArtNr, KNR, Datum)
◦ Kunde kann einen speziellen Artikel auch mehrmals am Tag kaufen: #(ArtNr, KNR, Datum, Lfd.Nr)
Transformation des nc:mc-Beziehungstyps
Kunde Artikelnc mc
kauft
Transformation des rekursiven Beziehungstyps
Zusatzaufgabe:1.) Steht V für verwandt oder verheiratet?2.) Wie wird dieses ERM in der Bachmann-Notation dargestellt?
Auflösung
Abteilung = (PNR, …) Verwand = (Wer, Mitwem,
Art_der_Verwandtschaft) Beides Fremdschlüssel, beides Personalnummern
Transformation:◦ Mitarbeiter=(MNR, Name, …)◦ Leit =(MNR, Zulage, Position, …)◦ Azubi =(MNR, Ausb, Berufsschule, …)
Relationenschema:◦ RE = (S, …) -> Supertyp, Primärschlüssel S◦ RSE1 = (S, …) -> Subtyp, gleicher Schlüssel◦ RSEn = (S, …) -> Subtyp, gleicher Schlüssel
Transformation des HilfsmittelsGeneralisierung/Spezialisierung
Subtyp wird in den Supertyp mit aufgenommen
Super-Typ: Person (PNR, …) Sub-Typ: Männer, Frauen
Person (PNR, …, Geschlecht).
Nur wenn disjunkt.
Transformation vonGeneralisierungen/Spezialisierungen
Spezialisierung wird als Information in den Supertyp aufgenommen
Mitarbeiter = (MNR, Name, …, Mitarbeiterstatus)
Leit = (MNR, Zulage, Position, …) Azubi = (MNR, Ausb, Berufsschule, …)
Transformation vonGeneralisierungen/Spezialisierungen
Nachteil ist die Redundanz.
Beziehungen: Jede Person kann einen „Kurs für beide“ buchen, Frauen können Kurse für Frauen buchen, Männer Kurse für Männer.
Entities: Personen (Sub-Typen: Männer, Frauen), Kurs (Sub-Typen: Kurse für beide, Kurse für Frauen, Kurse für Männer)
Attribute: ◦ Personennummer, Name, Vorname, Adresse,
Straße, Hausnummer, Ort, PLZ, Tel, Internetadresse, Kursnummer, Kursbezeichnung, Preis, Termin.
Transformation des Beispiels Volkshochschule
Beispiel: Volkshochschule
DD: Entity-Typ Person◦ Attribute: Personennummer (PNR), Name,
Vorname,Adresse=(Str, HNR, Ort, PLZ, Tel*), Internetadr*.
◦ Sub-Typen (d + t) Sup-Typ 1: Frau (F) Sub-Typ 2: Mann(M)
◦ Primärschlüssel: #(PNR) Beziehungstyp A-Buchung
◦ Beteiligte Entity-Typen: Person, KF◦ Komplexitätsgrad: nc:mc
Data Dictionary
* = Wiederholungsgruppe (0,1,2,…)+= Wiederholungsgruppe (1,2,3,…), mind. einmal( )= Zusammengesetztes Attribut
Person = (PNR, Name, Vorname, Adr = (Str, HNR, Ort, PLZ, Tel*), Inter*)
Frauen = (PNR) Männer = (PNR) Kurs = (KNR, Kbez, Preis, Termine+) KA = (KNR) KF = (KNR) KM =(KNR) A-Buchung=(KA-KNR, Person-PNR) F-Buchung=(KF-KNR, Frauen-PNR) M-Buchung=(KM-KNR, Männer-PNR)
Relationale Transformationeines ERM
Gewährleistung und Erhaltung der Korrektheit (Konsistenz) des
Datenbestandes
zB wie wirkt sich eine Änderung auf die Qualität der Datenbank aus
Konsistenzprobleme dieser Art werden Defekte oder Anomalien genannt.◦ Änderungsanomalie◦ Einfügeanomalie◦ Löschanomalie
Normalisierung
Bestimmte Informationen liegen in einer Relation mehrfach vor
oder:
Beispiele zu Redundanzen
MNr. Name Vorname Abteilung
1 Huber Heinz Einkauf
2 Müller Anton Einkauf
KNr. Name PLZ ORT
1 Huber 8055 Graz-Mariatrost
2 Müller 8055 Graz-Mariatrost
3 Meyer 3100 St. Pölten
Beispiele zu AnomalienBsp. Änderungsanomalie:Name der Abteilung kann nicht einheitlich geändert werden
Bsp. Einfügeanomalie:Projekt kann nicht ohne Mitarbeiter eingefügt werden
Bsp. Löschanomalie:Wird ein Formular gelöscht, könnte ein Abteilungsname verloren gehen.
Mitarbeiter = (MNR, Name, Vorname, Quali*, AbtNr, AbtName, PrArbeit+),mit PrArbeit = (PrNr, PrBez, Std)
Jede Relation enthält nur mehr atomare, also nicht weiter zerlegbare Attribute
Definition erste Normalform
Elimination von Zusammensetzungen und Wiederholungsgruppen.
Beispiel:◦ R= (A1,A2, A3*, A4 = (A41, A42), A5, A6, A7)
◦ R = (A1, A2, A4.A41, A4.A42, A5, A6, A7)◦ R-A3 = (A1, A2, A3)
Überführung in die erste Normalform
wird zu
Eine Relation ist in der zweiten Normalform, wenn sie in der ersten Normalform ist und wenn es keine funktionalen Abhängigkeiten zwischen Nichtprimärschlüsselattributen und Teilen des Primärschlüssel gibt.
Definition zweite Normalform
Elimination von funktionalen Abhängigkeiten mit Teilen des Primärschlüssels (partiellen funktionalen
Abhängigkeiten)
Beispiel:◦ PrArbeit = (MNR, PRNR, LNR, PrBez, Std)
Überführungin die zweite Normalform
Funktional abhängig: Für einen X-Wert (zB Schlüsselattribut) gibt es genau einen Y-Wert. Voll funktional abhängig: Alle Nichtschlüssel müssen vom gesamten Schlüssel abhängig sein, nicht nur von einem Teil.Nur 1 Primärschlüsselattribut: Alle Nichtschlüsselattribute hängen automatisch vom (gesamten) Primärschlüssel ab.
=voll funktional abhängig
Projektbezeichnung hängt funktional nur von der Projektnummer ab
PrArbeit = (MNR, PRNR, LNR, PrBez, Std)
2. Normalform:
◦ PrArbeit = (MNR, PRNR, LNR, Std) und◦ Proj = (PRNR, PrBez).
Auflösung
=partielle funktionale Abhängigkeit
#Auftr. Nr. #Artikel Nr.
Menge Hersteller
4711 2234 2 A AG
4711 3367 3 B AG
4711 1236 4 B AG
4812 2234 4 A AG
4812 4432 6 C GmbH
Beispiel zur 2. Normalform
http://reeg.junetz.de/DSP/node7.html#SECTION03342000000000000000
Der ganze Schlüssel darf nicht verloren gehen, es dürfen auch keine Funktionen verloren gehen.
#Auftr. Nr.
#Artikel Nr.
Menge
4711 2234 2
4711 3367 3
4711 1236 4
4812 2234 4
4812 4432 6
Auflösung
#Artikel Nr.
Hersteller
2234 A AG
3367 B AG
1236 B AG
4432 C GmbH
2. Normalform: jedes Attribut hängt vom ganzen Primärschlüssel ab.
Jedes Attribut sollte vom ganzen Primärschlüssel abhängen?
Aufgabe/ Mitarbeiter = (PersNr, Aufgabenbereich, Name, Vorname, Abteilung)
Beispiel zur 2. Normalform
Aufgabe/ Mitarbeiter = (PersNr, Aufgabenbereich, Name, Vorname, Abteilung)
Mitarbeiter = (PersNr, Name, Vorname, Abteilung)
Aufgabenbereich = (PersNr, AufgBereich)
Auflösung
Mehrere Aufgabenbereiche sind möglich.
Eine Relation ist automatisch in der 2. Normalform wenn sie in der ersten ist und ihr Primärschlüssel nicht zusammengesetzt ist.
Eine Relation ist in der dritten Normalform, wenn sie sich in der zweiten Normalform befindet und keine transitiv abhängigen Nichtprimärschlüsselattribute existieren.
Definition dritte Normalform
Elimination von transitiven Abhängigkeiten (A1 -> A3 oder A1-> A2
-> A3) ZB
◦ Mitarbeiter = (MNR, Name, Vorname, AbtNr, AbtName)
◦ Mitarbeiter = (MNR, Name, Vorname, AbtNr) und◦ Abt = (AbtNr, AbtName).
Überführungin die dritte Normalform
wird zu
Beispiel Mitarbeiterformular ( 1 Entity-Typ):
MNr, Name, Vorname, Quali*, AbtNr, AbtName, PrArbeit+
mit PrArbeit = (PRNr, PrBez, Std).
Zusammenfassung
Transformation
In diesem Fall eine Relation:
MITARBEITER = (MNR; Name, Vorname, Quali*, AbtNr, AbtName, PrArbeit+)
mit PrArbeit = (PRNR, PrBez, Std)
Zusammenfassung
Überführung in die erste Normalform◦ Zusammensetzungen und Wiederholungsgruppen
werden aufgelöst:
MITARBEITER = (MNR, Name, Vorname, ABTNR, AbtName) Quali = (MNR, LNR, Qualifikation) PRARBEIT = (MNR, PRNR, LNR, PrBez, Std)
Zusammenfassung
Ein Mitarbeiter kann mehrere Qualifikationen haben
Projekt kann pro Mitarbeiter mehrmals auftreten
Überführung in die zweite Normalform◦ 2. Normalform: jedes Attribut hängt vom ganzen
Primärschlüssel ab.
MITARBEITER = (MNR, Name, Vorname, ABTNR, AbtName) Quali = (MNR, LNR, Qualifikation) PRARBEIT = (MNR, PRNR, LNR, Std) PROJ = (PRNR, PrBez)
Zusammenfassung
Überführung in die dritte Normalform◦ Keine Nichtschlüsselattribute hängen transitiv
voneinander ab.
MITARBEITER = (MNR, Name, Vorname, ABTNR) ABT = (ABTNR, AbtName) Quali = (MNR, LNR, Qualifikation) PRARBEIT = (MNR, PRNR, LNR, Std) PROJ = (PRNR, PrBez)
Zusammenfassung
Entitäten◦ ARTIKEL = (Artikelnummer, Artikelbezeichnung,
Stückpreis)◦ KUNDE = (Kundennummer, Firmenname,
Anschrift) Beziehungstypen
◦ FAKTURA = (Rechnungsnummer, Rechnungsdatum, Positionsnummer+ (lfd. Nummer auf einer Rechnung, pro Rechnung mehrere Artikel möglich), Menge, Artikelnummer, Kundennummer)
Übung
ERM-Diagramm
ARTIKEL: in der 1. Normalform FAKTURA: in der 1. Normalform KUNDE: nicht in der 1. Normalform, da die
Anschrift nicht atomar zerlegt ist.◦ Kunde:
Kundennummer Firmenbezeichnung Straße PLZ Ort Staat
Erste Normalform
Schlüssel:◦ ARTIKEL: Artikelnummer◦ KUNDE: Kundennummer◦ FAKTURA: Rechnungsnummer u. Positionsnummer
(auch Artikelnummer wäre statt Positionsnummer möglich)
Funktionale Abhängigkeiten in ARTIKEL:◦ Artikelnummer -> Artikelbezeichnung◦ Artikelnummer -> Stückpreis
Zweite Normalform
Alle Schlüsselattribute sind vom ganzen Schlüssel funktional abhängig(voll funktional abhängig): 2. Normalform
Funktionale Abhängigkeiten in KUNDE:◦ Kundennummer -> Firmenbezeichnung◦ Kundennummer -> Straße◦ Kundennummer -> Ort◦ Kundennummer -> PLZ◦ Kundennummer -> Staat
◦ PLZ -> Ort◦ PLZ -> Staat
Zweite Normalform
Alle Schlüsselattribute sind vom ganzen Schlüssel funktional abhängig (voll funktional abhängig): 2. Normalform
Funktionale Abhängigkeiten in FAKTURA:◦ Rechnungsnummer -> Rechnungsdatum◦ Rechnungsnummer -> Kundennummer◦ Rechnungsnummer + Positionsnummer -> Menge◦ Rechnungsnummer + Positionsnummer -> ArtNr.
FAKTURA: Rechnungsposten:◦ Rechnungsnummer
Rechnungsnummer◦ Rechnungsdatum Positionsnummer◦ Kundennummer Menge◦ Artikelnummer
Zweite Normalform
Rechnungsdatum und Kundennumer sind nicht vom ganzen Schlüssel funktional abhängig: nicht in der 2. Normalform
ARTIKEL: ist in 3. Normalform Faktura: ist in 3. Normalform Rechnungsposten: ist in 3. Normalform
KUNDE: transitive Abhängigkeiten◦ KNr. -> Ort oder: KNr. -> PLZ-> Ort◦ KNr. -> Staat oder: KNr. -> PLZ -> Staat
KUNDE: ORTE:◦ Kundennummer Postleitzahl◦ Firmenname Ort◦ Straße Staat◦ PLZ
3. Normalform
In welcher Normalform befinden sich die nachfolgenden Relationen zur Verwaltung von Leihautos. Begründen Sie Ihre Aussage?
Übung II
WagenNr. Wagentyp Kunden-Nr.
2030 Kleinwagen 301
2040 Transporter 301
2070 Limousine 301
2090 SUV 301Kunden-Nr. Name Vorname Geb-
Datum
301 Hurtig Anton 20 04 73
302 Meyer Katrin 18 09 68
303 Hurtig Heidrun 24 10 80
304 Sommer Gerda 03 01 90
Primärschlüssel:WagenNr.
Primärschlüssel:KundenNr.
Prüfung erste Normalform:◦ Die erste Normalform ist gegeben, da alle
Attribute in atomarer Form vorliegen. Prüfung zweite Normalform:
◦ Die 2. NF ist gegeben, die die erste vorliegt und alle Attribute voll Funktional vom Primärschlüssel abhängig sind.
Prüfung dritte Normalform:◦ Die 3. NF ist gegeben, da die zweite vorliegt und
da keine transitiven Abhängigkeiten zwischen Nichtschlüsselattributen vorliegen.
Auflösung:
Schlüssel◦ Attributgruppe, über deren Werte ein Tupel eindeutig identifiziert
werden kann. (Namen können mehrfach vorkommen) Schlüsselkanditat
◦ Kein Attribut des Schlüssels kann entfernt werden, ohne dass die eindeutige Identifizierung verloren gehen kann. (Minimaleigenschaft)
Primärschlüssel◦ Ausgewählter Schlüsselkanditat
Sekundärschlüssel◦ Beliebige Attributgruppe, die aber unter Umständen nicht
eindeutig identifiziert werden kann. Fremdschlüssel
◦ Primärschlüssel einer anderen Relation MNR ist ein künstlicher Schlüssel
Der Schlüsselbegriff
Eine Relation stellt eine zweidimensionale Tabelle (Matrix) dar, sie hat einen Namen.
Eine Tabellenzeile entspricht einem Tupel (Erweiterung des geordneten Paares)
Spalten entsprechen den Attributen, Attribute erhalten einen Namen. Attribute nehmen Werte aus einem Wertebereich an (Domain) Im Kreuzungspunkt von Tupel und Spalte steht der Attributwert. Alle Tupel müssen paarweise verschieden sein, d. h. es darf keine zwei
gleichen Tupel geben. Die Reihenfolge der Tupel und Spalten innerhalb einer Relation spielt
keine Rolle. Die Anzahl der Attribute heißt Grad oder Ordnung einer Relation. Ein Tupel wird über einen (Primär-) Schlüssel eindeutig identifiziert. Attribute können strukturiert sein, d. h. sie können
Zusammensetzungen und/ oder Wiederholungsgruppen sein. Eine Relation kann leer sein. Attributwerte einzelner Tupel können leer sein. Das ist dann ein Null-
Wert, im Gegensatz zum Wert 0.
Eigenschaft einer Relation