A III. Datenbanksysteme und Modellierung

1. A III. Datenbanksysteme und Modellierung • Datenbanken als Basis betrieblicher Informationssysteme • Datenmodellierung - Konzepte und Methoden • Aufbau und Anwendung relationaler Datenbanksystem

2. Einsatz von Datenbanksystemen:Motivation und Ziele • Daten und Informationen als Grundlage betrieblicher Entscheidungs- und Führungsprozesse • Daten für Markt- und Wettbewerbsanalysen • Daten zur Abweichungsanalysen mit dem Ziel der Planung, Steuerung und Kontrolle • Kennzahlen in einem Führungsinformationssystem • Daten als Grundlage der operativen Massenverarbeitung, z.B. • Daten für die Fakturierung • Daten für die Erstellung des Jahresabschlusses

3. Warum DV-gestützte Datenverwaltung? • Beispiel Verwaltung eines Studentensekretariats: • notwendige Informationen wie beispielsweise Name, Studienrichtung, Alter, Krankenversicherung usw. lassen sich auch mit einem Karteikasten verwalten • Problem der “Karteikastenorganisation”

4. Traditionelle Dateiverarbeitung • Vorläufer der heutigen Datenbanktechnik war die traditionelle Dateiverarbeitung • Anwendungsprogramme greifen auf Dateien zu, in denen die zu verarbeitenden Daten enthalten sind • Datenverwaltung wird vollständig im Rahmen der Anwendungsprogramme durchgeführt, beispw. Einfügen, Löschen oder Ändern von Daten, Speicherverwaltung etc.

5. Phasen des Datenbankentwurfs • Grobe Vorgehensweise beim Datenbankentwurf • Informationsbedarfsanalyse: • Ermittlung, welche Informationen für die zu erfüllende Fachaufgabe relevant und in der Datenbank abzubilden sind • Entwurf des konzeptuellen Schemas: • formalisierte Beschreibung der relevanten Informationen unabhängig vom Datenbankmodell und -system • Entwurf des logischen Schemas: • Transformation des konzeptuellen Schemas in das vorliegende Datenbankmodell

6. Grundlagen der Datenmodellierung • Vorab allgemeine Definitionen: • Daten: Standardisierte Repräsentanten realer oder fiktiver Sachverhalte. • Modell: Zeitpunktbezogenes Abbild eines Realitätsausschnittes. Ein Modell kann nicht die ganze Realität abbilden. Von Details, die für den Zweck des Modells unrelevant erscheinen, wird abstrahiert.

7. Grundlagen der Datenmodellierung • Darstellung/Beschreibung der relevanten Daten eines Unternehmens • aus Unternehmensgesamtsicht • unabhängig vom verwendeten Datenbanksystem bzw. Datenmodell • unabhängig von der externen Sicht und von spezifischen Anwendungen • Datenmodelle beschreiben anwendungsübergreifend • Objekte und ihre Eigenschaften auf der Basis von Fachbegriffen • die Beziehungen zwischen diesen Fachbegriffen

8. Ziele der Datenmodellierung • Datenmodelle haben das Ziel, die Informationsverarbeitung einer Unternehmung in bezug auf ihre Informationsobjekte verbindlich zu regeln, um • Zusammenhänge im Unternehmen besser zu verstehen • den Informationsaustausch zu verbessern durch eine standardisierte Kommunikationsbasis • mehrere Bedeutungen eines Begriffs: zum Beispiel Ertrag, Erfolg, Gewinn, Umsatz etc. • Synomyme: "Rechnung" und "Offener Posten"

9. Ziele der Datenmodellierung • Aber: "Kulturelle Probleme" bei der Durchsetzung einer standardisierten Kommunikationsbasis! • eine Basis für die Anwendungsentwicklung sowie das Datenbankdesign zu schaffen, die sich an dem generierten Datenmodell orientieren.

10. Methoden der Datenmodellierung • Entity Relationship Model (ERM) basiert auf einer Entwicklung von Chen • ERM ist die wohl bekannteste und auch in der Praxis am häufigsten verwendete Methode zur Datenmodellierung • Abbildung eines Realitätsausschnittes mittels eines ERM basiert auf folgenden Elementen • Entity (Entität) • Relationsship (Beziehung) • Property (Attribut, Eigenschaft)

11. Entity, Beziehung • Entity • Definition: "a thing which can be distinctly identified" (reales Objekt, abstraktes Konzept, Ereignis) • Beispiele: Mitarbeiter, Abteilung, Produkt, Vorlesung DWM, Konzertbesuch usw. • Beziehung • Definition: "an association among entities" • Beispiele: Bestellung (Entity: Produkt, Kunde)

12. Attribut • Attribut • Definition: Eigenschaft, die ein Entity oder eine Beziehung beschreibt • Beispiele: Alter einer Person, Professor einer Vorlesung, Orchester oder Gruppe eines Konzerts

13. Kardinalitäten des ERM nach Chen • 1:1-Beziehungstyp • 1:n-Beziehungstyp • n:m-Beziehungstyp

14. Übertragung eines ERM in Tabellen • 1:n-Beziehung Mitarbeiter Abteilung

15. Übertragung eines ERM in Tabellen • n:m-Beziehung Kunde Artikel Kauft

16. Grundlagen relationaler Datenbanksysteme • Entwicklung von Codd [Codd 70] • Daten werden in Tabellen (=Relationen) dargestellt • Tabelle besteht aus • Name der Tabelle • Menge von Datensätzen (=Tupel) • Menge von Attributen; die Wertebereiche dieser Attribute werden als Domänen bezeichnet

17. Beispiel: Relation Kunde Kontonr Rabatt Kunden- Name Strasse PLZ Wohnort BLZ nummer (%) Friedenstr. 34 25 Honeywell 5450 Neuwied 739495 45090000 20 Mainz 64 IBM Kaiser-Ring 10 6500 94588324 35070000 10 35 Müller AG Waldstr. 123 6200 Wiesbaden 5632734 51090000 20 Frankfurt 61000000 46 Epson Rheinstr. 73 6000 63744 10 66 Schmidt KG Sonnenstr. 243 6000 Frankfurt 8377402 87000000 15 67 Kühne&Nagel Industriestr. 12 6200 Wiesbaden 748288 51090000 10 Flachstr. 45 6500 Mainz 8474700 47700000 99 Köbig 12

18. Spezieller Attributtyp: Schlüssel • Primärschlüssel (Primary Key): • Attribut mit eindeutig identifizierender Eigenschaft; kann sich aus einem oder mehreren Attributen zusammensetzen. In unserem Beispiel ist die Kundennummer Primärschlüssel der Relation Kunde (Kennzeichnung durch Unterstreichung). • Sekundärschlüssel (Secondary Key): • jedes beliebige Attribut, das nicht Primärschlüssel ist und klassifizierende Eigenschaften haben kann.

19. Spezieller Attributtyp: Schlüssel • Schlüsselkandidat(Candidate Key): • Attribut, das nicht als Primärschlüssel verwendet wird, diese Funktion aber übernehmen könnte • Fremdschlüssel (Foreign Key): • Ein Attribut einer Relation B wird als Fremdschlüssel bezeichnet, wenn es nicht Primärschlüssel von B ist, aber Primärschlüssel der Relation A ist.

20. Beispiel: Fremdschlüssel BLZ in Relation KUNDE

21. Beispiel für einen Datenbankentwurf Tabelle: KUNDE KNr Firma Straße Postleitzahl Ort Status Tabelle: AUFTRAG ANr KNr ADatum LDatum Tabelle: BESTELLT ANr PNr Menge VPreis Tabelle: PRODUKT PNr BezeichnungPreis Tabelle: LAGER PNrOrt Menge

22. Normalisierung von Relationen • Ziele der Normalisierung • Integritätssicherung • Redundanzfreie Speicherung • "lesbare" Relationen generieren • Normalisierung wird durch Zerlegung von Relationen erreicht.

23. Normalisierung von Relationen • im folgenden: Demonstration des Prozesses der Normalisierung anhand eines Beispiels • Die Tabelle zur Darstellung von Lieferanten einer Unternehmung sei nun durch folgende Attribute beschrieben. • Lieferant (Lieferanten#, Lieferantenname, Straße, PLZ, Wohnort, Kontonummer, BLZ, Bank, Teilenummern, Teilenamen)

24. Die 1. Normalform (1NF) • Eine Relation befindet sich in 1. Normalform (1NF), wenn alle Attributwerte atomar sind. • Bedingung erfüllt? • Lösungsmöglichkeiten

25. Zerlegung der Relation • Überführung der Relation in 1NF durch Zerlegung der Relation LIEFERANT in zwei Relationen • Relation Lieferant (Lieferanten#, Lieferantenname, Straße, PLZ, Wohnort, Kontonummer, BLZ, Bank). • Relation Lieferung (Lieferanten#, Teil#, Teilname,Menge, Datum) • Diese Relationen genügen der ersten Normalform (1NF).

26. Die 2. Normalform (2NF) • Eine Relation befindet sich in der zweiten Normalform, wenn sie sich in 1NF befindet und jedes Attribut vom gesamten Primärschlüssel (und nicht nur von Teilen des Primärschlüssels) voll funktional abhängig ist. • Bedingung erfüllt?

27. Weitere Zerlegung der Relation • Es ist also eine dritte Relation TEILE einzuführen. Das Relationenschema hat nun folgendes Aussehen und befindet sich in 2NF: • Relation Lieferant (Lieferanten#, Lieferantenname, Strasse, PLZ, Wohnort, Kontonummer, BLZ, Bank). • Relation Lieferung (Lieferanten#, Teil#, Menge, Datum). • Relation Teile (Teil#, Teilname).

28. Die 3. Normalform (3NF) • Ein Relationenschema befindet sich in der dritten Normalform (3NF), wenn es sich in 2NF befindet und zudem keine transitiven Abhängigkeiten aufweist. • Unter transitiver Abhängigkeit versteht man die funktionale Abhängigkeit eines Nicht-Schlüsselattributes einer Relation R von einem anderen Nicht-Schlüsselattribut in R. • Eine solche funktionale Abhängigkeit besteht in der Relation LIEFERANT bezüglich BLZ => Bank. Da BLZ nicht Primärschlüssel der Relation LIEFERANT ist, handelt es sich um eine transitive Abhängigkeit.

29. Ergebnis:Relationsschema in 3NF • Wir erhalten nun folgendes Relationenschema, das in 3NF vorliegt: • Relation Lieferant (Lieferanten#, Lieferantenname, Strasse, PLZ, Kontonummer, BLZ). • Relation Lieferung (Lieferanten#, Teil#,Menge, Datum). • Relation Teile (Teil#, Teilname). • Relation Bank (BLZ, Bankname) • Relation Ort (PLZ, Wohnort)

30. Beurteilung der Normalisierung • Vorteile der Normalisierung • Redundanzfreie Speicherung, • d.h. jedes Faktum ist nur einmal in der Datenbank gespeichert • Sicherung der Integrität: • Integrität bedeutet, daß keine sich widersprechenden Fakten in der Datenbank gespeichert sind.

31. Beurteilung der Normalisierung • Nachteile der Normalisierung • Normalisierungsaufwand • Schlechteres Laufzeitverhalten bei der Zusammenfügung (Join) mehrerer Relationen • beim Datenbankentwurf sind somit die "Kosten" und "Nutzen" der Normalisierung abzuwägen • Normalisierung bis 3NF (oder weniger!) ist in der Regel ausreichend

32. Datenmanagement auf der Basis der relationalen Sprache SQL • SQL (Structured Query Language) ist die Standardsprache für relationale Datenbanken • Normierung von SQL durch ISO und ANSI • bildet die Basis aller gängigen relationalen Datenbanksysteme

33. Die relationale Sprache SQL • SQL als • Data Definiton Language: • Anlegen von Tabellen/Relationen • Data Manipulation Language: • Ändern der Daten in Tabellen/Relationen • Query Language: • Generierung von Abfragen zur Extraktion von Daten aus einer oder mehreren Tabellen/Relationen

34. SQL als Data Definition Language • Aufgabe der Data Definition Language (DDL; synonym: Data Description Language): Generierung des intensionalen Relationenschemas • SQL als DDL beinhaltet drei grundlegende Befehle • Anlegen einer Relation (create) • Ändern des Schemas einer Relation (alter) • Löschen einer Relation (drop)

35. Anlegen einer Relation • SQL-Befehl zum Anlegen des intensionalen Schemas einer Relation: • CREATE TABLE <tablename> • (<attributname_1> <datentyp_1>, <attributname_2> <datentyp_2>, ...<attributname_n> <datentyp_n>); • Beispiel: Anlegen einer Relation Auftrag, die aus den Attributen Auftragsnummer, Kundennummer, Datum und Mitarbeiternummer des Kundenbetreuers besteht: • CREATE TABLE AUFTRAG • (auftrags_no number(4) not null, • kunden_no number(4), • datum date, • mitarbeiter_no number(4));

36. Allgemeine Datentypen • number (x) = ganzzahliger numerischer x- stelliger Wert • number (x,y) = numerischer Wert mit x-y Vorkomma- und y Nachkommastellen • char(x) = alphanumerische Zeichenkette mit x Zeichen • date = Datum

37. SQL als Data Manipulation Language • SQL als DML dient der Veränderung der extensionalen Datensicht und beinhaltet drei grundlegende Befehle: • Einfügen von Tupeln (insert) • insert into <tablename> values (<wert_1>, <wert_2>, ..., <wert_n>); • Ändern von Tupeln (update) • update <tablename> set <attributsname> = <arithmetischer Ausdruck> • Löschen von Tupeln (delete) • delete from <tablename> where <bedingung>

38. Beispiele für SQL-Befehle:Anlegen einer Tabelle • Anlegen einer Tabelle Mitarbeiter mit den Attributen • Mitarbeiter • M_Nr number (3) • Name char (20) • Abt_Nr number (3) • Gehalt number (5) • Geschlecht char (10) • SQL> create table Mitarbeiter (M_Nr number (3), Name char (20), Abt_Nr number (3), Gehalt number (5), Geschlecht char (10))

39. Beispiele für SQL-Befehle:Einfügen von Datensätzen insert into mitarbeiter values (1, “Müller”, 1, 2500, “männlich”)

40. Beispiele für SQL-Befehle:Ändern von Tabellen Beispiel 1: Alle Mitarbeiter bekommen von jetzt an 1 Prozent mehr Gehalt SQL>update mitarbeiter set gehalt = gehalt*1,01

41. Beispiele für SQL-Befehle:Ändern von Tabellen Beispiel 2: Alle Mitarbeiter der Abteilung 1 bekommen 10 Prozent mehr Gehalt SQL>update mitarbeiter set gehalt = gehalt*1,1 where Abt_Nr=1

42. Beispiele für SQL-Befehle:Ändern von Tabellen Beispiel 3: Alle Mitarbeiter der Abteilung 1, die weiblich sind, bekommen 100 DM mehr Gehalt SQL>update mitarbeiter set gehalt = gehalt + 100 where Abt_Nr=1 and Geschlecht="weiblich"

43. Beispiele für SQL-Befehle:Löschen von Datensätzen Beispiel: Abteilung 1 wird geschlossen und keiner der Mitarbeiter wird weiterbeschäftigt SQL>delete from mitarbeiter where Abt_Nr=1

44. Spezielle Zusätze für Datentypen zur Integritätssicherung • not null => Attribut muß für jeden Tupel einen Wert haben • unique => Attribut darf für verschiedenen Tupel keine identischen Werte haben • primary key => not null und unique • references => sichert Integrität bei Fremdschlüsselbeziehungen • check => definiert Wertebereiche einer Domäne

45. Beispiel SQL> create table bankleitzahl (blz number(10) primary key, bankname char (20)); SQL > create table mitarbeiter( pers_nr number(5) primary key, nachname char(20), vorname char(15), geschlecht char(10), strasse char(20), plz number(5), wohnort char(20), blz number(10) references bankleitzahl, abt_nr number(2), gehalt number(7) check(gehalt between 2000 and 1000000));

46. SQL als Query Language (QL) • Query Language: Abfragesprache zur Extraktion von Daten bzw. Informationen aus der Datenbank • => Anwendung des Select-Befehls • Einfachster Select-Befehl: Vollständige Anzeige aller Tupel einer Relation • => select * from <tablename>

47. Beispiele für SQL-Befehle • Beispiel: Anzeige der kompletten Relation Mitarbeiter • SQL > select * from mitarbeiter; • Mitarbeiter

48. Selektionsoperatoren • Select-Grundoperatoren • Projektion • Selektion • Join

49. Allgemeine Operatoren: Projektion • Projektion: generiert aus einer Relation B eine Relation A, indem eine Teilmenge der Attribute der Relation B in A eingehen. • SQL> select <attributname_1>, <attributname_2>,<attributname_n> from <tablename> • Beispiel: Auswahl der Attribute pers_nr, vorname, nachname und gehalt aus der Relation Mitarbeiter

50. Projektion • SQL >select pers_nr, vorname, nachname, gehalt from mitarbeiter; • Werden durch eine Projektion mehrere identische Tupel generiert, so sind die identischen Tupel nach der relationenlehre zu entfernen. Diese Forderung wird von den meisten relationalen Datenbanken jedoch nicht unterstützt.