wintersemester 2010. ist das derzeit wichtigste datenmodell wurde von codd e.f. in den 70ern...

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