Teil IV Datenbankentwurf - dbse.ovgu.de · Logischer Entwurf Sprachmittel:Datenmodell des ausgewählten „Realisierungs“-DBMS z.B. relationales Modell Vorgehensweise: 1 (automatische)

Teil IV

Datenbankentwurf

Datenbankentwurf

Datenbankentwurf

1 Phasen des Datenbankentwurfs

2 Weiteres Vorgehen beim Entwurf

3 Kapazitätserhaltende Abbildungen

4 ER-auf-RM-Abbildung

Sattler / Saake Datenbanksysteme Letzte Änderung: Okt. 2016 4–1

Datenbankentwurf

Lernziele für heute . . .

Kenntnisse über Ziele und Ablauf desDatenbankentwurfsprozessesKenntnisse der Regeln zur Abbildung vonER-Schemata auf Relationenschemata


Datenbankentwurf Phasen des Datenbankentwurfs

Entwurfsaufgabe

Datenhaltung für mehrere Anwendungssysteme und mehrereJahredaher: besondere BedeutungAnforderungen an Entwurf

I Anwendungsdaten jeder Anwendung sollen aus Daten derDatenbank ableitbar sein (und zwar möglichst effizient)

I nur „vernünftige“ (wirklich benötigte) Daten sollen gespeichertwerden

I nicht-redundante Speicherung



Phasenmodell

Anforderungsanalyse

KonzeptionellerEntwurf

Verteilungsentwurf

Logischer Entwurf

Datendefinition

Physischer Entwurf

Implementierung &Wartung



Anforderungsanalyse

Vorgehensweise: Sammlung des Informationsbedarfs in denFachabteilungenErgebnis:

I informale Beschreibung (Texte, tabellarische Aufstellungen,Formblätter, usw.) des Fachproblems

I Trennen der Information über Daten (Datenanalyse) von denInformation über Funktionen (Funktionsanalyse)

„Klassischer“ DB-Entwurf:I nur Datenanalyse und Folgeschritte

Funktionsentwurf:I siehe Methoden des Software Engineering



Konzeptioneller Entwurf

erste formale Beschreibung des FachproblemsSprachmittel: semantisches DatenmodellVorgehensweise:

I Modellierung von Sichten z.B. für verschiedene FachabteilungenI Analyse der vorliegenden Sichten in Bezug auf KonflikteI Integration der Sichten in ein Gesamtschema

Ergebnis: konzeptionelles Gesamtschema, z.B. ER-Diagramm



Phasen des konzeptionellen Entwurf

Sichtenentwurf

Sichtenanalyse

Sichtenintegration

konzeptioneller Entwurf


Datenbankentwurf Weiteres Vorgehen beim Entwurf

Weiteres Vorgehen beim Entwurf

ER-Modellierung von verschiedenen Sichten aufGesamtinformation, z.B. für verschiedene Fachabteilungen einesUnternehmens konzeptueller Entwurf

I Analyse und Integration der SichtenI Ergebnis: konzeptionelles Gesamtschema

Verteilungsentwurf bei verteilter SpeicherungAbbildung auf konkretes Implementierungsmodell (z.B.Relationenmodell) logischer EntwurfDatendefinition, Implementierung und Wartung physischerEntwurf



Sichtenintegration

Analyse der vorliegenden Sichten in Bezug auf KonflikteIntegration der Sichten in ein Gesamtschema

Sicht #1 Sicht #2

Sicht #3

GlobalesSchema

Konsoli-

dierung



Integrationskonflikte

Namenskonflikte: Homonyme / SynonymeI Homonyme: Schloss; KundeI Synonyme: Auto, KFZ, Fahrzeug

Typkonflikte: verschiedene Strukturen für das gleiche ElementWertebereichskonflikte: verschiedene Wertebereiche für einElementBedingungskonflikte: z.B. verschiedene Schlüssel für einElementStrukturkonflikte: gleicher Sachverhalt durch unterschiedlicheKonstrukte ausgedrückt



Verteilungsentwurf

sollen Daten auf mehreren Rechnern verteilt vorliegen, muss Artund Weise der verteilten Speicherung festgelegt werdenz.B. bei einer RelationKUNDE (KNr, Name, Adresse, PLZ, Konto)

I horizontale Verteilung:KUNDE_1 (KNr, Name, Adresse, PLZ, Konto)where PLZ < 50.000KUNDE_2 (KNr, Name, Adresse, PLZ, Konto)where PLZ >= 50.000

I vertikale Verteilung (Verbindung über KNr Attribut):KUNDE_Adr (KNr, Name, Adresse, PLZ)KUNDE_Konto (KNr, Konto)



Logischer Entwurf

Sprachmittel: Datenmodell des ausgewählten„Realisierungs“-DBMS z.B. relationales ModellVorgehensweise:

1 (automatische) Transformation des konzeptionellen Schemas z.B.ER→ relationales Modell

2 Verbesserung des relationalen Schemas anhand von Gütekriterien(Normalisierung, siehe Kapitel 5):Entwurfsziele: Redundanzvermeidung, . . .

Ergebnis: logisches Schema, z.B. Sammlung vonRelationenschemata



Datendefinition

Umsetzung des logischen Schemas in ein konkretes SchemaSprachmittel: DDL und DML eines DBMS z.B. Oracle, DB2, SQLServer

I Datenbankdeklaration in der DDL des DBMSI Realisierung der IntegritätssicherungI Definition der Benutzersichten



Physischer Entwurf

Ergänzen des physischen Entwurfs um Zugriffsunterstützung bzgl.Effizienzverbesserung, z.B. Definition von IndexenIndex

I Zugriffspfad: Datenstruktur für zusätzlichen, schlüsselbasiertenZugriff auf Tupel (〈Schlüsselattributwert, Tupeladresse〉)

I meist als B*-Baum realisiert

Sprachmittel: Speicherstruktursprache SSL



Indexe in SQL

create [ unique ] index indexname

on relname (

attrname [ asc | desc ],

attrname [ asc | desc ],

...

)

Beispiel

create index WeinIdx on WEINE (Name)



Notwendigkeit für Zugriffspfade

Beispiel: Tabelle mit 100 GB Daten, Festplattentransferrate ca. 50MB/sOperation: Suchen eines Tupels (Selektion)Implementierung: sequentielles DurchsuchenAufwand: 102.400/50 = 2.048 sec. ≈ 34 min.



Implementierung und Wartung

PhasenI der Wartung,I der weiteren Optimierung der physischen Ebene,I der Anpassung an neue Anforderungen und Systemplattformen,I der Portierung auf neue DatenbankmanagementsystemeI etc.


Datenbankentwurf Kapazitätserhaltende Abbildungen

Umsetzung des konzeptionellen Schemas

Umsetzung auf logisches SchemaI Beispiel: ER→ RMI korrekt?I Qualität der Abbildung?

Erhaltung der InformationskapazitätI Kann man nach der Abbildung genau die selben Daten

abspeichern wie vorher?I ... oder etwa mehr?I ... oder etwa weniger?



Kapazitätserhöhende Abbildung

Lizenz besitzt Erzeuger

WeingutLizenzNo

Abbildung aufR = {LizenzNo, Weingut}

mit genau einem Schlüssel

K = {{LizenzNo}}mögliche ungültige Relation:

BESITZT LizenzNo Weingut

007 Helena42 Helena



Kapazitätserhaltende Abbildung


WeingutLizenzNo

korrekte AusprägungBESITZT LizenzNo Weingut

007 Helena42 Müller

korrekte Schlüsselmenge

K = {{LizenzNo}, {Weingut}}



Kapazitätsvermindernde Abbildung

Wein enthält Rebsorte

SortennameWName

Relationenschema mit einem Schlüssel {WName}als Ausprägung nicht mehr möglich:

ENTHÄLT WName Sortenname

Zinfandel Red Blossom ZinfandelBordeaux Blanc Cabernet SauvignonBordeaux Blanc Muscadelle

kapazitätserhaltend mit Schlüssel beider Entity-Typen imRelationenschema als neuer Schlüssel

K = {{WName, Sortenname}}


Datenbankentwurf ER-auf-RM-Abbildung

Beispielabbildung ER-RM: Eingabe

Rebsorte

Wein

produziertErzeuger

enthält

Anteil

SortennameFarbe

Weingut Adresse

Restsüße

Farbe

Jahrgang

WName



Beispielabbildung ER-RM: Ergebnis

1 REBSORTE = {Farbe, Sortenname} mitKREBSORTE = {{Sortenname}}

2 ENTHÄLT = {Sortenname, WName, Anteil} mitKENTHÄLT = {{Sortenname, WName}}

3 WEIN = {Farbe, WName, Jahrgang, Restsüße} mitKWEIN = {{WName}}

4 PRODUZIERT = {WName, Weingut} mit KPRODUZIERT = {{WName}}5 ERZEUGER = {Weingut, Adresse} mit KERZEUGER = {{Weingut}}



ER-Abbildung auf Relationen

Entity-Typen und Beziehungstypen: jeweils aufRelationenschemataAttribute: Attribute des Relationenschemas, Schlüssel werdenübernommenKardinalitäten der Beziehungen: durch Wahl der Schlüssel beiden zugehörigen Relationenschemata ausgedrücktin einigen Fällen: Verschmelzen der Relationenschemata vonEntity- und Beziehungstypenzwischen den verbleibenden Relationenschemata diverseFremdschlüsselbedingungen einführen



Abbildung von Beziehungstypen

neues Relationenschema mit allen Attributen des Beziehungstyps,zusätzlich Übernahme aller Primärschlüssel der beteiligten Entity-TypenFestlegung der Schlüssel:

I m:n-Beziehung: beide Primärschlüssel zusammen werdenSchlüssel im neuen Relationenschema

I 1:n-Beziehung: Primärschlüssel der n-Seite (bei der funktionalenNotation die Seite ohne Pfeilspitze) wird Schlüssel im neuenRelationenschema

I 1:1-Beziehung: beide Primärschlüssel werden je ein Schlüssel imneuen Relationenschema, der Primärschlüssel wird dann ausdiesen Schlüsseln gewählt



n:m-Beziehungen

Wein enthält Rebsorte

Sortenname Farbe

WName

Restsüße

Farbe

Jahrgang

Anteil

Umsetzung1 REBSORTE = {Farbe, Sortenname} mit KREBSORTE = {{Sortenname}}2 ENTHÄLT = {Sortenname, WName, Anteil} mit

KENTHÄLT = {{Sortenname, WName}}3 WEIN = {Farbe, WName, Jahrgang, Restsüße} mit KWEIN = {{WName}}

Attribute Sortenname und WName sind gemeinsam Schlüssel



1:n-Beziehungen

Anbaugebietsitzt inErzeuger

Adresse RegionWeingut Name

Umsetzung (zunächst)I ERZEUGER mit den Attributen Weingut und Adresse,I ANBAUGEBIET mit den Attributen Name und Region undI SITZT_IN mit den Attributen Weingut und Name und dem

Primärschlüssel der n-Seite Weingut als Primärschlüssel diesesSchemas.



Mögliche Verschmelzungen

optionale Beziehungen ([0,1] oder [0,n]) werden nichtverschmolzenbei Kardinalitäten [1,1] oder [1,n] (zwingende Beziehungen)Verschmelzung möglich:

I 1:n-Beziehung: das Entity-Relationenschema der n-Seite kann indas Relationenschema der Beziehung integriert werden

I 1:1-Beziehung: beide Entity-Relationenschemata können in dasRelationenschema der Beziehung integriert werden



1:1-Beziehungen


Weingut AdresseHektoLiterLizenzNo

Umsetzung (zunächst)I ERZEUGER mit den Attributen Weingut und AdresseI LIZENZ mit den beiden Attributen LizenzNo und HektoliterI BESITZT mit den Primärschlüsseln der beiden beteiligten Entity-

Typen jeweils als Schlüssel dieses Schemas, also LizenzNo undWeingut



1:1-Beziehungen: Verschmelzung

Umsetzung mit VerschmelzungI verschmolzene Relation:

ERZEUGER Weingut Adresse LizenzNo Hektoliter

Rotkäppchen Freiberg 42-007 10.000Weingut Müller Dagstuhl 42-009 250

I Erzeuger ohne Lizenz erfordern Nullwerte:


Rotkäppchen Freiberg 42-007 10.000Weingut Müller Dagstuhl ⊥ ⊥

I freie Lizenzen führen zu weiteren Nullwerten:


Rotkäppchen Freiberg 42-007 10.000Weingut Müller Dagstuhl ⊥ ⊥⊥ ⊥ 42-003 100.000



Abhängige Entity-Typen

NWeinJahrgang Weingehört-zu1

JahrRestsüße WName Farbe

Umsetzung1 WEINJAHRGANG = {WName, Restsüße, Jahr} mit

KWEINJAHRGANG = {{WName, Jahr}}2 WEIN = {Farbe, WName} mit KWEIN = {{WName}}

I Attribut WName in WEINJAHRGANG ist Fremdschlüssel zur Relation WEIN



IST-Beziehung

WeinSchaumwein

WName FarbeHerstellung

Umsetzung1 WEIN = {Farbe, WName, Jahrgang, Restsüße} mit KWEIN = {{WName}}2 SCHAUMWEIN = {WName, Herstellung} mit KSCHAUMWEIN = {{WName}}

I WName in SCHAUMWEIN ist Fremdschlüssel bezüglich der Relation WEIN



Rekursive Beziehungen

Anbaugebiet grenzt-an

nachName

Regionvon

Umsetzung1 ANBAUGEBIET = {Name, Region} mit KANBAUGEBIET = {{Name}}2 GRENZT_AN = {nach, von} mit KGRENZT_AN = {{nach, von}}



Rekursive funktionale Beziehungen

Kritiker SchülerVon

MentorName

Organisation Schüler

Umsetzung1 KRITIKER = {Name, Organisation, Mentorname} mit

KKRITIKER = {{Name}}

I Mentorname ist Fremdschlüssel auf das Attribut Name der RelationKRITIKER.



Mehrstellige Beziehungen

Weinempfiehlt

Gericht

Kritiker

WName

Restsüße

Farbe

Jahrgang

Bezeichnung Beilage

Name Organisation

jeder beteiligte Entity-Typ wird nach den obigen Regeln behandeltfür Beziehung Empfiehlt werden Primärschlüssel der drei beteiligten Entity-Typen in das resultierende Relationenschema aufgenommenBeziehung ist allgemeiner Art (k:m:n-Beziehung): alle Primärschlüssel bildenzusammen den Schlüssel



Mehrstellige Beziehungen: Ergebnis

1 EMPFIEHLT = {WName, Bezeichnung, Name} mitKEMPFIEHLT = {{WName, Bezeichnung, Name}}

2 GERICHT = {Bezeichnung, Beilage} mitKGERICHT = {{Bezeichnung}}

3 WEIN = {Farbe, WName, Jahrgang, Restsüße} mitKWEIN = {{WName}}

4 KRITIKER = {Name, Organisation} mit KKRITIKER = {{Name}}

Die drei Schlüsselattribute von EMPFIEHLT sind Fremdschlüssel fürdie jeweiligen Ursprungsrelationen.



Übersicht über die TransformationenER-Konzept wird abgebildet auf relationales KonzeptEntity-Typ Ei Relationenschema Ri

Attribute von Ei Attribute von Ri

Primärschlüssel Pi Primärschlüssel Pi

Beziehungstyp RelationenschemaAttribute: P1, P2

dessen Attribute weitere Attribute1 : n P2 wird Primärschlüssel der Beziehung1 : 1 P1 und P2 werden Schlüssel der Beziehungm : n P1 ∪ P2 wird Primärschlüssel der BeziehungIST-Beziehung R1 erhält zusätzlichen Schlüssel P2

E1, E2: an Beziehung beteiligte Entity-Typen,P1, P2: deren Primärschlüssel,1 : n-Beziehung: E2 ist n-Seite,IST-Beziehung: E1 ist speziellerer Entity-Typ



Zusammenfassung

Phasen des DatenbankentwurfsDatenbankmodell, Datenbankschema, Datenbank(instanz)Entity-Relationship-ModellER-Erweiterungen: Spezialisierung, Generalisierung,Partitionierungweitere Entwurfsschritte



Kontrollfragen

Welche Schritte umfasst derDatenbankentwurfsprozess?Welche Forderungen müssen dieAbbildungen (Transformationen) zwischenden einzelnen Entwurfsschritten erfüllen?Warum?Wie werden die Konzepte des ER-Modellsauf die des Relationenmodell abgebildet?Wie werden die verschiedenenKardinalitäten von Beziehungstypen beider Abbildung berücksichtigt?


Documents

Teil IV Datenbankentwurf - dbse.ovgu.de · Logischer Entwurf Sprachmittel:Datenmodell des ausgewählten „Realisierungs“-DBMS z.B. relationales Modell Vorgehensweise: 1 (automatische)