Datenbanksysteme am Beispiel von MICROSOFT SQL Server

1. Datenbanksysteme am Beispiel von MICROSOFT SQL Server Design, Konzepte, Applikationen und neue M�glichkeiten

2. Ziel

3. Datenbanksysteme

4. Konventionelle Datenverarbeitung Daten-Programm-Abh�ngigkeit Sehr starke Abh�ngigkeit, da Logik und Physik im Programm def. Physische Datenabh�ngigkeit Logische Datenabh�ngigkeit Datenzugriff wird von jedem Programm oder Anwender lokal gesteuert keine zentrale Koordination m�glich Datenredundanz Jedes Programm oder Anwender strukturiert die Daten nach seinen Belangen Datenkonsistenz redundante Daten m�ssen doppelt gepflegt werden

5. Konventionelle Datenverarbeitung - Beispiele

6. Konventionelle Datenverarbeitung

7. Datenbanksystem Eine Datenbank repr�sentiert den gesamten Bestand an Informationen oder Daten

8. Datenbank-Managementsystem (DBMS) Wichtigste Funktionen des DBMS Beschreibung der gespeicherten Daten Zugriffsm�glichkeiten (Prozesse) Datenschutz Physikalische Datenorganisation Mehrbenutzerbetrieb Datenintegrit�t Plausibilit�tspr�fungen Transaktionen

9. Merkmale eines Datenbanksystems (I) Daten-Programm-Abh�ngigkeit geringe Abh�ngigkeit, da Logik und Physik im DBMS def. Physische �nderungen -> keine Auswirkung auf Programm Logische �nderungen -> Programm mu� nur geringf�gig ge�ndert werden Jedes Programm erh�lt seine externe Sicht auf die Daten Die Aufbereitung ein und der selben Daten in den externen Sichten kann sehr flexibel erfolgen Datenzugriff einheitliche, zentrale Schnittstelle Zugriff von mehreren Benutzern wird synchronisiert reibungsloser Ablauf von mehreren Anwendungsprogrammen gleichzeitig Definition von unterschiedlichen externen Sichten

10. Merkmale eines Datenbanksystems (II) Datenredundanz weitgehend redundanzfrei (je nach Design !!!) Schl�sselinformationen redundant Datenkonsistenz zentrale Verwaltung -> weniger Konsistenzprobleme Transaktionen Datensicherheit ? siehe folgende Folien

11. Datenbanksysteme - Sicherheit Datenschutz Der Datenschutz ber�hrt die Probleme, die den Zugriff auf vertrauliche oder schutzw�rdige Daten betreffen, wie Lohnabrechnung oder geheime Produktdaten. rechtliche Aspekte moralische Aspekte Datensicherheit Die Datensicherheit umfa�t alle Vorkehrungen, sowohl technischer als auch organisatorischer Art, um Daten vor Zerst�rung, Verlust, Entstellung und Mi�brauch zu sichern. Datenintegrit�t Zugriffsschutz

12. Datenintegrit�t Unter Datenintegrit�t wird der Zustand verstanden, in dem die gespeicherten Daten vollkommen und korrekt mit den realen �bereinstimmen. Die Vollst�ndigkeit und Korrektheit der Daten kann durch falsche Eingabe oder durch den Ausfall von Hard- bzw. Software gef�hrdet werden. Eingabefehler m�ssen im Vorfeld durch Plausibilit�tspr�fungen vermieden werden. Bereits entstandene Fehler m�ssen mit geringem Aufwand beseitigt werden k�nnen (Recovery). Der Mehrbenutzerbetrieb muss durch Sperren (Locking) sicherstellen, dass das selbe Datum nicht von zwei Benutzern gleichzeitig bearbeitet wird.

13. Datenintegrit�t: Plausibilit�tspr�fungen Feldbedingungen Eindeutigkeit des Prim�rschl�ssels Pr�fung von Feldinhalten gegen Dom�nen (Wertebereiche) Logische / Funktionale Abh�ngigkeiten Referentielle Integrit�t Alle Schl�sselinformationen m�ssen beim Einf�gen vorhanden sein und angegeben werden. Die Schl�sselinformationen (Prim�rschl�ssel<-> Fremdschl�ssel) m�ssen konsistent sein und d�rfen nicht ge�ndert werden (Cascading Update) Oft benutztes Konzept -> Trigger Trigger f�hren vordefinierte Aktionen (-sfolgen) aus werden aktiviert auf Tabellenebene INSERT, UPDATE oder DELETE

14. Datenintegrit�t: Recovery Logische Fehler beziehen sich auf Anwendungsfehler (z.B. Daten von anderem User gesperrt) Reaktion mit Rollback-Recovery Hardwarefehler beziehen sich auf alle Anwendungsprogramme Reaktion mit Rollforward-Recovery, Transaktionsprotokoll manuell notwendig! System- oder Stromausfall bezieht sich auf alle Anwendungsprogramme Reaktion mit Auto-Recovery, Transaktionsprotokoll notwendig! Reaktion mit Rollforward-Recovery, Transaktionsprotokoll manuell notwendig!

15. Datenintegrit�t: Locking Im Mehrbenutzerbetrieb wird der konkurrierende Zugriff �ber Sperren geregelt Die Lockingalgorithmen sind von DBMS zu DBMS unterschiedlich exclusive Lockskein Zugriff anderer Benutzer auf die gesperrten Daten shared Locksandere Benutzer haben nur lesenden auf die gesperrten Daten Die Granularit�t kann auf der Datenbank, Tabelle, Zeile, Attribut und DB-Seite liegen Verschiedene Strategien optimistisch pessimistisch

16. Zugriffsschutz Ebenso wichtig wie die Datenintegrit�t ist die Absicherung, des gesamten Systems aber auch von Teilen des Systems, vor nichtberechtigten Anwendern. Zutrittskontrolle zu bestimmten R�umen und Rechnern Integrierte Benutzerverwaltung im DBMS (auch mit BS) Benutzerbezogene Berechtigungsvergabe auf Datenbanken, Tabellen und Spalten Funktionsbezogener Zugriffsschutz (Select, Insert, Update, Delete Berechtigungen)

17. ??? Fragen ???

18. Datenbanksysteme - Konzepte CODASYL (1969)Datenbankkonzept auf zwei Ebenen, unabh�ngig von der Programmiersprache ANSI/SPARC (1975) Datenbankkonzept auf drei Ebenen, unabh�ngig von Sprache und Hardware==> hat sich als Standard f�r die Betrachtungsweise von Datenbanksystemen durchgesetzt

19. ANSI/SPARC - Datenbankkonzept Dieses Konzept untergliedert sich in die Externe EbeneHier wird eine logische Beschreibung der Daten aus der Perspektive des Benutzers durchgef�hrt (technikspezifisch) Konzeptuelle bzw. konzeptionelle EbeneIn dieser Ebene wird aus Unternehmenssicht eine technikunabh�ngige, logische Beschreibung der realen Welt durchgef�hrt Interne EbeneDie interne oder physische Ebene beschreibt die Daten aus der Sicht der Datenbank Man spricht entsprechend dieser Ebenen von Modellen, die von den Datenbankadministratoren (DBA) entwickelt werden.

20. ANSI/SPARC 3 Ebenen Modell

21. ANSI/SPARC: Interne Ebene

22. Interne Ebene / Internes Modell

23. Datenbanksysteme - Internes Modell Hier werden zwei grunds�tzliche Speicherungsformen besprochen Sequentieller Speicherungsform (kaum verwendet) Index Sequentieller Speicherungsform Das physische Datenmodell (internes Modell) legt fest, in welcher Form die Daten auf die externen Datentr�ger geschrieben werden und welche Zugriffsm�glichkeiten bestehen Ziel: Minimale Zugriffszeit bei optimaler Speichernutzung

24. Sequentielle Speicherungsform Die einzelnen S�tze einer Tabelle werden physisch sequentiell gespeichert Die rein sequentielle Speicherungsform kommt allerdings selten zum Einsatz? index-sequentielle Speicherung

25. Index-Sequentielle Speicherungsform

26. Index-Sequentielle Speicherungsform

27. ANSI/SPARC : Konzeptuelle Ebene

28. Konzeptuelle Ebene / Konzeptuelles Modell Mittelpunkt des Datenbankentwurfs -> Datenbankdesign Beschreibung aller Daten und Beziehungen zwischen ihnen Erfordert detaillierte Kenntnisse �ber das Unternehmen (Logik, Abl�ufe, Zusammenh�nge) Datenschutz und Datensicherheit (Zugriffsrechte, Kennw�rter, Transaktionen)

29. ANSI/SPARC : Externe Ebene

30. Externe Ebene / Externes Modell Verschieden Sichten auf die Daten, wie die Anwender bzw. Programme sie ben�tigen. Zum Erstellen muss das konzeptionelle Modell und das Anwendungsgebiet bekannt sein und welche Daten in welcher Form ben�tigt werden. Datenschutz und Datensicherheitsaspekte k�nnen so realisiert werden (View).

31. Sprachen des konzeptuellen und externen Modells

32. Aufgaben der Sprachen Aufgaben die Datenbank kreieren Tabellen erstellen Felder definieren, inkl. Datentyp, L�nge und Verschl�sselung (Key) Regelung des Datenzugriffs Plausibilit�tspr�fungen Transaktionsmechanismus mit Recovery Funktion Daten abfragen, einf�gen, �ndern und l�schen Umsetzung dieser Aufgaben durch eigenst�ndige Sprachen; f�r relationale Datenbanken ist das SQL Programmiersprachen;die durch die Integration der SQL Sprache die datenverarbeitenden Teile des Programms realisieren. Man spricht dann von Embedded SQL.

33. Datenbanksysteme - Logisches Datenmodell Innerhalb der logischen Ebene im ANSI/SPARC Konzept unterscheidet man zwischen verschiedenen Datenmodellen Hierarchisches Datenmodell Netzwerk Datenmodell Relationales Datenmodell Objekt Datenmodell Mit der Festlegung auf ein bestimmtes Datenbanksystem ist das relevante Datenmodell vorgegeben SQL Server -> Relationales Modell

34. ??? Fragen ???

35. MS SQL Server Relationales Datenmodell

36. Relationales Datenmodell Entwickelt von E.F. Codd, 1970 auf Basis der Relationentheorie Die Daten werden in sogenannten Tabellen (Relationen) verwaltet Tabellen bestehen aus einzelnen Datenfeldern (Attributen) Attribute definiert durch Datentypen (bzw. Dom�nen) Attribute werden in Spalten dargestellt Jede Tabelle kann eine oder mehrere Zeilen (Tupel) enthalten Die einzelnen Tupel (Datens�tze) k�nnen �ber einen Schl�ssel angesprochen werden Zwischen den Tabellen bestehen Beziehungen, die aber nur zur Laufzeit existieren

37. Grundbegriffe (I)

38. Grundbegriffe (II)

39. Relationale Operationen

40. Selektion

41. Projektion

42. Kombination Selektion/Projektion

43. Join (I)

44. Join (II)

45. Union

46. Intersect

47. Minus

48. Eigenschaften des Relationales Datenmodells Vorteile einfaches Datenmodell durch zweidimensionale Tabellen flexibel einheitliche Sprache (SQL, DDL, DML, DCL) Dynamische DB Definitionen und �nderungen Integrierter Systemkatalog (Dictionary) Nachteile Bei komplexen Abfragen sind SQL Statements komplex (3 NF) Bei komplexen Datenstrukturen relativ langsam

49. Normalisierung Sinnvoller Datenaufbau durch Normalisierung Designtechnik um Relationale Datenbanken zu strukturieren Durch die Normalisierung wird ein Proze� bzw. Informationseinheit in Tabellen zerlegt Regelsatz von E.F. Codd Legt nur die logische Datenbeschreibung fest externe Ebene konzeptuelle Ebene Zweck der Normalisierung: Vermeidung von Datenredundanz Drei sinnvolle Stufen der Normalisierung in der Praxis

50. Normalisierung

51. 1. Normalform (1NF)

52. 1. Normalform Ein Prim�rschl�ssel identifiziert eindeutig eine Zeile und kann sein einfach z.B. bei einer Tabelle Kunden die Spalte KundeNr zusammengesetzt (Gesamtschl�ssel / Composite Key) z.B. in der Tabelle Bestellwesen die Spalten BestNr und ArtNr Nichtschl�ssel �normale� Attribute z.B. bei der Tabelle Bestellwesen die Spalten BestDatum, LieferNr, LieferName, ArtName, BestMenge und BestPreis

53. 2. Normalform (2NF)

54. 2. Normalform Tabelle Bestellungen Die Nichtschl�sselspalten BestDatum, LieferNr und LieferName sind vom Schl�sselteil BestNr, jedoch nicht vom Gesamtschl�ssel abh�ngig. Daraus resultiert die Tabelle Bestellungen mit dem Prim�rschl�ssel BestNr. Tabelle Bestelldaten Die Spalten BestMenge und BestPreis sind von der BestNr und ArtNr abh�ngig. Damit ergibt sich die Tabelle Bestelldaten. Tabelle Artikel Die Nichtschl�sselspalte ArtName ist vom Schl�sselteil ArtNr abh�ngig. Sie wird deshalb in eine eigene Tabelle Artikel mit der Spalte ArtNr als Prim�rschl�ssel ausgelagert.

55. 3. Normalform (3NF)

56. 3. Normalform Tabelle Lieferanten Die Nichtschl�sselspalte LieferName ist ausschlie�lich von der Nichtschl�sselspalte LieferNr abh�ngig. Deshalb wird eine autonome Tabelle Lieferanten mit dem Prim�rschl�ssel LieferNr erstellt

57. �bung

58. Zusammenfassung Datenbanksysteme -Konzepte Logische Modelle Interne Modelle Datenschutz und Datensicherheit Relationales Datenmodell

59. ??? Fragen ???

60. MS SQL Server Tools I

61. MS SQL Server Tools I Historie SQL Server Management Studio Datenbank Verwaltung Sicherheit Interaktives Arbeiten

62. MS SQL Server Historie Gemeinsame Basis mit Sybase bis zur Version 4.21 Fehlende deklarative refenzielle Integrit�t (DRI) Komplizierte Verwaltung (viele verschiedene Tools) Ab Version 6.0 unabh�ngig von Sybase Aber immer noch die gleiche Basis Mit DRI Einf�hrung des Enterprise Managers zur Verwaltung Version 6.5 Neue Funktionen: Sperren von Zeilen, Internet/Intranet Unterst�tzung, Distributed Transaction Coordinator Version 7.0, 2000 (intern 8.0), 2005 (intern 9.0) Vollst�ndige Neuentwicklung Skalierbarkeit (PocketPC, Desktop, Server, Cluster, �) Webf�higkeit (HTML, XML, �) Datawarehousing (Analysis Services, MDX, �)

63. Aktuell SQL Server 2005: Management Studio

64. Management Studio: Sicherheit

65. Management Studio: Datenbanken

66. Management Studio: Abfragefenster

67. Management Studio: Abfragefenster

68. ??? Fragen ???

69. Wo finde ich das Management Studio

70. Management Studio: Server registrieren

71. Management Studio: Server registrieren

72. ??? Fragen ???

73. Management Studio: Neue Datenbank anlegen

74. Management Studio: Neue Datenbank anlegen

75. ??? Fragen ???

76. Aufgaben Melden Sie sich mit dem Management Studio an dem SQL Server DBKURS anRegistrieren Sie den Server DBKURS mit Ihrer [UserID] Legen Sie f�r sich eine neue Datenbank anVerwenden Sie als Namen Ihre [UserID] �ffnen Sie ein Abfragefenster in Ihrer Datenbank F�hren Sie die SQL Dateien basis_daten_modell.sql ausdownload von der WebSite http://dbkurs �berpr�fen Sie welche Tabellen es gibt Sehen Sie sich die Beschreibung der Tabellen an

77. Basis Datenmodell

78. MS SQL Server Die Sprache SQL

79. SQL - Structured Query Language Mr. Codd hat bei IBM die Sprache SEQUEL entwickelt (Structured English QUery Language)- 1976 Daraus wurde dann sp�ter SQL Durch ANSI (American National Standards Institute) ANSI 1986 ? SQL 86 ANSI 1989 ? SQL 89 ANSI 1992 ? SQL 92 oder SQL2 wird bis auf wenige Ausnahmen von SQL Server 2000 unterst�tzt ANSI 1994 ? SQL3wird teilweise von MS SQL Server 2000 unterst�tzt Mengenorientierte Sprache DDL, DCL und DML Befehle

80. SQL - Structured Query Language

81. Selektion

82. Projektion

83. Kombination Selektion/Projektion

84. SQL (Teil I), Theorie SQL (Teil I) Select Insert Update Delete

85. SQL - Select Anzeigen von Daten Syntax: SELECT {Spaltenname|*}(einfach) FROM {Tabellenname} Der * gibt an, dass alle Spalten einer Tabelle angezeigt werden sollenSollen mehrere Spalten angezeigt werden, m�ssen die Spaltennamen in einer Aufz�hlung mit Komma getrennt angegeben werden.

86. SQL - Select (sortiert) Sortier-Reihenfolgen werden durch die ORDER BY Klausel festgelegt Syntax: SELECT ma_nr, ma_vorname, ma_nachname FROM mitarbeiter ORDER BY ma_nr oder: SELECT ma_nr, ma_vorname, ma_nachname FROM mitarbeiter ORDER BY 1 DESC Aufsteigend =ASC (Standard) Absteigend = DESC

87. SQL - Select (Bedingung) Mit der WHERE Bedingung k�nnen Sie die Selektion einer Tabelle einschr�nken Syntax: SELECT ma_nr, ma_vorname, ma_nachname FROM mitarbeiter WHERE ma_vorname=�Elmar� oder: SELECT ma_nr, ma_vorname, ma_nachname FROM mitarbeiter WHERE ma_vorname=�Elmar� AND ma_alter>20

88. SQL - Insert (einfach) F�gt Daten in eine Tabelle ein Syntax: INSERT INTO tabellenname ([spaltenname {,spaltenname}]) VALUES (werteliste) 

89. Aufgabe F�gen Sie 5 neue Mitarbeiter in die Tabelle Mitarbeiter ein Geben Sie eine Liste aller Mitarbeiter sortiert nach Nachname aus F�gen Sie 3 Abteilungen ein(z.B.: 13: Gesch�ftsf�hrung, 14: Vertrieb, 15: Entwicklung)

90. SQL - Update (einfach) �ndert Daten in einer Tabelle Syntax: UPDATE tabellenname SET spaltenname=ausdruck [WHERE suchbedingung] 

91. Aufgaben Ordnen Sie die Mitarbeiter den einzelnen Abteilungen zu (beliebig) Die einzelnen Mitarbeiter sollen zu ihren jeweiligen Vorgesetzten zugeordnet werden (MA_MANAGER) MA 1 ist Vorgesetzter vom MA 2, MA 2 ist Vorgesetzter von MA 3. Geben Sie eine nach Nachnamen und Vornamen sortierte Liste aus

92. SQL - Delete (einfach) L�scht Daten aus einer Tabelle Syntax: DELETE FROM tabellenname [WHERE suchbedingung] 

93. Aufgaben L�schen Sie einen Mitarbeiter L�schen Sie eine Abteilung

94. Join (I)

95. SQL - Join (kartesisches Produkt)

96. Join (II)

97. SQL - Join (einfach)

98. Aufgaben F�gen Sie zwei neue Mitarbeiter ein (ohne Abteilung) Lassen Sie sich alle Mitarbeiter der Abteilung 13 sowie den Abteilungsnamen in der Ergebnismenge anzeigen Wie viele Mitarbeiter sind einer Abteilung zugeordnet? Lassen Sie sich alle Mitarbeiter sortiert nach Abteilungsnamen und Mitarbeiternummer ausgeben �ndern Sie einen Abteilungsnamen Lassen Sie sich alle Mitarbeiternamen und den Abteilungsnamen sortiert nach Abteilungsnamen und Mitarbeiternummer ausgeben

99. ??? Fragen ???