Betrieb von Datenbanken Marco Skulschus & Marcus Wiederstein

1. Betrieb von DatenbankenMarco Skulschus & Marcus Wiederstein Datenbank-Management-Systeme(DBMS) Lehrbuch, Kapitel 1

2.  Seminar-Inhalt Grundlagen Einfache Abfragen Komplexe Abfragen Datenmanipulation Grundlagen T-SQL Programm-Module in der DB Administration

3.  Modul-Inhalt Beispieldatenbank AdventureWorks Das relationale Modell Das relationale Datenbank-System SQL – Structured Query Language

4. Beispiel-Datenbank AdventureWorks • Das Thema der Datenbank ist die fiktive Firma AdventureWorks, welche Fahrräder produziert und weltweit per Internet oder lokale Läden vertreibt. • Die Datenbank erhalten Sie in verschiedenen Versionen unter http://msftdbprodsamples.codeplex.com/.

5. Beispiel-Datenbank AdventureWorks • Innerhalb der Personaldaten werden die Angestellten in der Employee-Tabelle gespeichert. • Sie arbeiten in einer Abteilung, die in einer Department-Tabelle dargestellt ist. Da ein Angestellter nicht notwendigerweise permanent in der gleichen Abteilung arbeitet, gibt es eine Beziehungstabelle EmployeeDepartmentHistory. • Darüber hinaus arbeiten die Mitarbeiter in Schichten, wobei die Schichteinteilung in der Shift-Tabelle angegeben ist.

6. Beispiel-Datenbank AdventureWorks • Die Contact-Tabelle enthält die allgemeinen persönlichen Daten wie Vorname, Nachname etc. • Die Address-Tabelle enthält Adressinformationen mit Straße, Stadt und PLZ. Für beide Tabellen existieren jeweils zwei Type-Tabelle, d.h. eine Tabelle namens ContactType und eine AddressType-Tabelle, durch die die jeweiligen Datensätze kategorisiert werden können. • Länder und allgemeine geografische Bereiche sind in verschiedenen weiteren Tabellen wie CountryRegion oder StateProvince gespeichert.

7. Beispiel-Datenbank AdventureWorks • Die zentrale Tabelle ist Product, in der die Informationen Nummer, Bezeichnung, Farbe, Sicherheitsbestand im Lager, Standardkosten und Listenpreis enthalten sind. • Für die beiden Attribute Größe und Verkaufseinheit gibt es Tabellen mit Wertelisten. • Man unterscheidet zwei Kategorieebenen: ProductSubcategory und ProductCategory, wobei die Product-Tabelle zunächst die Unterkategorie verknüpft und diese Unterkategorie wiederum die Oberkategorien.

8. Beispiel-Datenbank AdventureWorks • Die SalesOrderHeader-Tabelle enthält die zusammenfassenden Informationen für einen Verkauf (Bestell-, Auslieferungs-, Fälligkeitsdatum, Preise (Netto, Steuer, Total, Fracht) und Referenzen wie Adressen, Kunden- und Kontaktinformationen sowie Verkaufsgebiet). • Jede Bestellung enthält einzelne Positionen, die in SalesOrderDetail gespeichert werden, welche insbesondere eine Referenz zur Product-Tabelle enthält.

9.  Fragen...

10.  Modul-Inhalt Beispieldatenbank AdventureWorks Das relationale Modell Das relationale Datenbank-System SQL – Structured Query Language

11. Das relationale Modell: Grundbegriffe • Beim relationalen Modell handelt sich um ein mathematisches Modell, dessen grundlegende Einheit die so genannte Relation ist, welche in einer Datenbank schließlich zu einer Tabelle wird. • Eine solche Relation wiederum wird als eine Menge von n-Tupelnbezeichnet. • Ein Tupel ist in einer Datenbanktabelle eine einzelne Zeile bzw. ein Datensatz. • Die Menge aller Zeilen bildet dann die gesamte Tabelle, während die Menge aller Tupel die Relation bildet.

12. Das relationale Modell: Grundbegriffe • Operationen, die man auf einer solchen Relation ausführen kann, werden als relationale Algebra bezeichnet. • Sie baut auf der Mengenlehre auf. • Ihre Ausprägung in einer Datenbank ist dann die SQL-Sprache, mit der diese Operationen formuliert werden können. • Grundlegende Züge der Mengen-Theorie und ihrer Operationen sind auch in SQL gut zu erkennen und bereits ein grundlegendes und allgemeines Verständnis dieser Operationen bzw. der Mengenlehre hilft sehr beim Erlenen von SQL und Formulieren von komplexen Abfragen.

13. Das relationale Modell: Grundbegriffe • Die nachfolgende Tabelle enthält einen Vergleich zwischen Begriffen des relationalen Modells und einer relationalen Datenbank:

14. Das relationale Modell: Semantisches Modell • Ein semantisches Modell versucht, die Objekte der Realität möglichst genau zu erfassen und so eine Basis für die endgültig zu erstellende Datenbank zu liefern. • Im Idealfall entsteht ein Text, der bereits alle • Objekte, • Beziehungen und • Attribute nennt. • Die Entwicklung des semantischen Modells stellt die erste analytische Phase dar. • Dieses Thema berührt sehr viele verschiedene angrenzende Bereiche der Analyse und beschreibenden Darstellung zum Zwecke der Anwendungsentwicklung.

15. Das relationale Modell: Semantisches Modell Zur Analyse zählen die folgenden groben Themen: Beschreibung von Abläufen und Prozessen Identifikation und Beschreibung von Objekten und Entitäten mit ihren Eigenschaften sowie den Beziehungen zwischen ihnen und Einschränkungen und Validierungsregeln für Eigenschaftswerte. • mit ihren Eigenschaften, die an verschiedenen Stationen wertmäßig erfasst werden, • und den Einschränkungen und Bedingungen, die für Abläufe und die Auswahl von bedingten Prozessschritten notwendig sind.

16. Das relationale Modell: Semantisches Modell • Nahezu alle Mittel zur Analyse und Präsentation ihrer Ergebnisse lassen sich denken: • Texte und schriftliche Beschreibungen von Abläufen und Objekten mit den üblichen Techniken wie • Fließtext, • Listen • Tabellen. • Zeichnungen und Abbildungen von Abläufen sowie den Objekten und ihren jeweiligen Beziehungen. • Einsatz von Darstellungs- und Analysetechniken für die Abbildung von Strukturen sowie Abläufen: • Entity-Relationship-Diagramm (ER-Diagramm) • UML (Unified Modeling Language) • Ältere Techniken sind alle Arten von Flussdiagrammen. • Sehr moderne Techniken sind Ontologien und ontologische Datenmodelle.

17. Das relationale Modell: Semantisches Modell • Es empfiehlt sich hier, Techniken des strukturierten Schreibens zu verwenden. • Bspw. gehören dazu: • einfache und klar gegliederte Aussagen • klare Hauptwörter, • Sinn tragende Verben • nur die notwendigsten Adjektive. • Es ist auch empfehlenswert, Aussagen durch Formelsprache zur Abbildung von Einschränkungen und Bedingungen zu verkürzen.

18. Das relationale Modell: Eigenschaften von Daten • Die kleinsten Einheiten in einem Datenbanksystem (DBS) sind die Daten, die in einem sinnvollen Zusammenhang stehen und von Programmen unterschiedlicher Art für Zwecke der • Kontrolle (Finanzsituation), • des Marketing (personalisierte Kundenprofile) • der Verwaltung (Stammdaten) verarbeitet werden.

19. Das relationale Modell: Eigenschaften von Daten

20. Das relationale Modell: Klassifikation von Datentypen

21. Das relationale Modell: Beziehungen zwischen Daten • Eine wichtige Eigenschaft von Daten innerhalb eines Datenmodells ist die Beziehungsstruktur. • Sie ist zunächst in der Realität vorhanden und wird durch die Abbildung im Datenmodell für die DB ersichtlich. • In der Datenbank werden sie vor allen Dingen durch die so genannte Primärschlüssel-Fremdschlüssel-Beziehung abgebildet. • Zwei hierarchische Ebenen müssen unterschieden werden: • Beziehungen innerhalb eines Objekts • Beziehungen zwischen Objekten

22. Das relationale Modell: Beziehungen zwischen Daten

23. Das relationale Modell: Beziehungen zwischen Daten • In einer eineindeutigen Beziehung zwischen zwei Wertebereichen gehört zu jedem Wert des einen Wertebereichs ein Wert des anderen Wertebereichs. • In einem Ehepaarkreis mit vier Ehepaaren kann man die beiden Objekte Ehemänner und Ehefrauen unterscheiden. • Ralf ist mit Susanne, Reinhard mit Birte, Tom mit Tina und Nils mit Anja verheiratet.

24. Das relationale Modell: Beziehungen zwischen Daten • Eine funktionale Beziehung zeichnet sich dadurch aus, dass zu den Werten des einen Wertebereichs null, einer oder mehrere Werte des anderen Wertebereichs gehören. • Susanne und Tina sind bereits Mütter, während Birte und Anja noch keine Kinder haben. Jeweils ein Kind hat genau eine Mutter. Umgekehrt kann aber eine Mutter mehrere Kinder haben. So kann also Susanne zwei Kinder haben, Tina eines und die anderen Mütter keine

25. Das relationale Modell: Beziehungen zwischen Daten • Eine komplexe Beziehung liegt vor, wenn es zu jedem Wert eines Wertebereichs null, einen oder mehrere Werte eines anderen Wertebereichs gibt und umgekehrt. • Eine Tante kann sowohl einen als auch mehrere Neffen oder Nichten haben. Umgekehrt ist es auch möglich, dass ein Kind mehrere Tanten besitzt. So ist Birte sowohl von Stefan als auch von Kristina die Tante, während Holger als Tanten Tina und Anja nennen kann.

26. Das relationale Modell: Beziehungen zwischen Daten

27. Das relationale Modell: Entity-Relationship-Modell • Bevor man sich daranmacht, für ein Datenbankprojekt sofort Tabellen und Feldnamen zu erstellen, kann man mit Hilfe eines grafischen Modells versuchen, die zu modellierenden Objekte zu finden und sich einen Überblick über die Beziehungen zu verschaffen. • Seit 1976 wird das von P.P. Chen vorgeschlagene Entity-Relationship-Modell (ER-Modell oder ERM) für eine erste Annäherung verwendet, dessen Vorteile in der grafischen Darstellung und der mathematischen Verständlichkeit liegen. • Es besteht aus den Elementen • Entity, • Entity-Typ, • Relationship und • Attribut.

28. Das relationale Modell: Entity-Relationship-Modell

29. Das relationale Modell: Entity-Relationship-Modell

30. Das relationale Modell: Entity-Relationship-Modell • Das RDM (relationale Datenmodell) beruht auf der Überführung der einzelnen Objekte im ER-Modell in Tabellen, die über die Primärschlüssel, die in der anderen Tabelle Sekundärschlüssel sind, in Beziehung stehen. • Dieses Datenmodell wurde von E.F.Codd 1970 entwickelt und bildet die Grundlage für relationale Datenbanksysteme. • In diesem Zusammenhang wird ein Datenmodell mit nur einer einzigen Tabelle und sämtlichen Informationen als Universal Relation bezeichnet.

31. Das relationale Modell: Entity-Relationship-Modell • Folgende Schritte sind dabei zu berücksichtigen: • Entwickeln Sie ein semantisches Modell. • Leiten Sie aus dem semantischen Modell die benötigten Objekte ab. • Erstellen Sie das Entity-Relationship-Diagramm. • Transformieren Sie das ER-Modell in das relationale Daten-Modell. • Spalten Sie die Beziehungen in Primärschlüssel-Fremdschlüssel (1:1 und 1:n) oder Beziehungstabellen (n:m) auf. • Verhindern Sie Dateninkonsistenzen. • Testen Sie das Datenmodell durch beispielhafte Einträge.

32. Das relationale Modell: Entity-Relationship-Modell • Leiten Sie aus dem semantischen Modell die benötigten Objekte ab. • Es ist leicht, sich mehr und mehr Attribute auszudenken, sodass man sich zunächst auf die wichtigsten beschränken sollte. • Normalerweise findet man die benötigten Attribute über eine Tätigkeitsanalyse oder eine Objektanalyse. • In einer Tätigkeits- oder Prozessanalyse durchläuft man die gesamten Tätigkeiten, die ein Objekt ausführt. Sie verlaufen teilweise auch parallel zu den Tätigkeiten anderer Objekte, die entweder schon von Anfang an bestehen oder sich erst im Prozessverlauf herausbilden. • Die Objektanalyse fokussiert mehr die Beschreibung von Objekten, die real vorhanden sind.

33. Das relationale Modell: Entity-Relationship-Modell • Transformieren Sie das ER-Modell in das relationale Daten-Modell. • Ein Objekt bildet eine eigene Tabelle. • Die Datensätze sind die einzelnen Zeilen einer solchen Tabelle. • Die Attribute stellen die Spalten (Feldnamen) dar. • Ein Schlüssel ist ein spezielles Attribut, das einen Datensatz eindeutig identifiziert. Oft können die übrig bleibenden Attribute bei Entfernung eines Attributes einen Datensatz nicht mehr korrekt oder eindeutig identifizieren. • Der Wertebereich einer Spalte entspricht der Domäne, die dem Attribut zugeordnet wurde. • Eine Beziehung wird entweder über Fremdschlüssel oder über eine eigene Beziehungstabelle ausgedrückt.

34. Das relationale Modell: Entity-Relationship-Modell • Verhindern Sie Dateninkonsistenzen. • Hierbei müssen entsprechende Konventionen aus realen Gegebenheiten abgeleitet werden, um die Daten konsistent zu machen. • Nur so können Sie garantieren, dass Abfragen auch tatsächlich korrekt ablaufen. • Zwei typische Konzepte helfen bei diesem Ziel: • Wertebereiche oder Domänen und • Namensdefinitionen.

35. Das relationale Modell: Normalisierung • Ein in einer Universal-Relation (nicht normalisierte Struktur/Tabelle) vorliegendes Datenmodell kann über Normalisierung in ein korrektes relationales Schema überführt werden. • Dabei werden • für die 1NF (erste Normalform) alle Attribute atomisiert, • für die 2NF die funktionalen Abhängigkeiten zwischen Primärschlüssel und anderen Attributen elementar gemacht und • für die 3NF die funktionalen Abhängigkeiten der Attribute untereinander direkt gemacht.

36. Das relationale Modell: Normalisierung • Definitionen der ersten drei Normalformen: • Eine Tabelle ist in der 1. Normalform, wenn sie einen Schlüssel besitzt und alle Attribute atomisiert sind. Atomisiert sind Attribute genau dann, wenn sie nicht weiter aufgeteilt werden können. • ´ • Eine Tabelle ist in 2. Normalform, wenn sie in der 1. Normalform ist und die funktionalen Abhängigkeiten zwischen Schlüssel und den anderen Attributen elementar ist. • Eine Tabelle ist in der 3. Normalform, wenn sie in der 2. Normalform ist und die Eigenschaft und die funktionalen Abhängigkeiten zwischen dem Schlüssel und den anderen Attributen direkt sind.

37. Das relationale Modell: Normalisierung

38. Das relationale Modell: Normalisierung Für die 1. Normalform gilt, dass sie einen Schlüssel besitzt und alle Attribute atomisiert sind.

39. Das relationale Modell: Normalisierung

40. Das relationale Modell: Normalisierung Gemäß Definition ist ein Objekt in der 2. Normalform, wenn es in der 1. Normalform ist und die funktionalen Abhängigkeiten zwischen Schlüssel und den anderen Attributen elementar sind.

41. Das relationale Modell: Normalisierung

42. Das relationale Modell: Normalisierung Damit ein Datenmodell in der 3. Normalform ist, muss es zunächst in der 2. Normalform sein und die Eigenschaft besitzen, dass die funktionalen Abhängigkeiten zwischen dem Schlüssel und den anderen Attributen direkt ist.

43. Das relationale Modell: Normalisierung

44.  Fragen...

45.  Modul-Inhalt Beispieldatenbank AdventureWorks Das relationale Modell Das relationale Datenbank-System SQL – Structured Query Language

46. Das relationale DB-System: Begriffe • Datenbanksystem heißt eine Struktur, die aus Datenbank und Datenbankmanagementsystem besteht. • Datenbank heißt der Speicherort strukturierter Daten. Die Datenbank als solche stellt den Speicherort sämtlicher Daten auf einem physikalischen Datenträger dar. • Datenbankmanagementsystem heißt die Komponente eines DBS, das Datenverwaltung und Datensicherheit bereitstellt.

47. Das relationale DB-System: Sichten • Mit dem Begriff Sicht (engl. View) sollen unterschiedliche Betrachtungsweisen einer Datenbankkonzeption bezeichnet werden. • Es entspricht einem Drei-Ebenen-Modell, das von ANSI/SPARC (engl. American National Standards Institute/Standards PlanningandRequirementsCommittee) in den 70er Jahren empfohlen wurde.

48. Das relationale DB-System: Anforderungen • Gute Repräsentation der realen Welt: Die Datenbank muss ein getreues Abbild der Realität mit aktuellen Daten liefern. • Keine Datenredundanz: Die Informationen einer Datenbank sollen in ihrer Beschreibung und Speicherung einzigartig sein. • Unabhängigkeit der Daten mit Blick auf Benutzung: Die Programme zur Benutzung der Datenbank sollen auf Hard- und Software-Seite (physische Unabhängigkeit) wie auf organisatorischer Seite (logische Unabhängigkeit) unabhängig sein. • Leichter Datenzugriff: Auch mit Datenbanken nicht vertrauten Benutzern muss ein einfacher Informationszugriff möglich sein. • Sicherheit und Vertraulichkeit: Sowohl auf physischer (Datenverlust durch Hardwarebeschädigung oder Mutwillen) wie auch auf logischer Seite (inhaltliche Zugriffskontrolle) müssen Sicherheitsstandards bestehen. • Leistung der Abfragen und Anwendungsprogramme: Auch bei gleichzeitigem Zugriff auf die Daten muss eine akzeptable Antwortzeit gegeben sein.

49. Das relationale DB-System: Bestandteile einer Tabelle • Die gesamte Tabelle heißt Relation. • Ein Datensatz der Relation / Tabelle nennt sich Tupel. • Die Breite der Tabelle / die Anzahl der Spalten oder Felder heißt Grad der Relation. • Ein einzelnes Feld oder eine einzelne Spalte heißt Attribut der Tabelle. • Mit Domänen werden die Wertebereiche bezeichnet, die für ein Attribut bzw. ein Feld der Relation vorgegeben wurden. • Kardinalität bedeutet die Höhe / Länge der Relation / Tabelle.

50. Das relationale DB-System: Inhalte • Tabelle: Eine Tabelle stellt den Speicherort der Daten eine relationalen Datenbank dar. • Die Daten liegen in für den schnellen Zugriff optimierten Datei- und Datenstrukturen auf der Festplatte. • Elemente: • Spalten oder Felder, in welchem unter einem Namen und unter Berücksichtigung der Einschränkungen durch einen Datentyp einzelnen Daten einer Zeile getrennt voneinander verfügbar gemacht werden. • Schlüssel: Der Primärschlüssel identifiziert einen Datensatz eindeutig, während der Fremdschlüssel einen Datensatz in einer anderen Tabelle referenziert. • Einschränkungen: Spalten können Prüfmechanismen und Validierungsregeln aufweisen. • Index: Ein Index oder auch mehrere Indizes, die sich jeweils auf eine oder mehrere Spalten beziehen, ermöglichen eine beschleunigte Abfrage von großen Tabellen.