2 Entity-Relationship-Modell.key
Werbung
Datenbanken Entity-Relationship-Modell und Datenbankentwurf 1 Andreas Heß Hochschule Furtwangen Inhalte heute • Einführung ins Entity-Relationship-Modell • Einführung ins relationale Modell • Umsetzung vom E/R- ins relationale Modell • Erste SQL-Statements Begriffsklärung Name Vorname Straße PLZ Ort Mail Kemper Alfons Schillerstr. 15 55116 Mainz [email protected] Meier Hans Goethestr. 23a 12345 Mönchengladbach [email protected] Heß Andreas Lessingstr. 25 78120 Furtwangen Zeilen [email protected] Tupel Heß Andreas Goethestr. 39 60318 Frankfurt [email protected] Albrecht Alexandra Marktplatz 1 67543 Heidelberg [email protected] Spalten / Attribute Adressdaten in Datei Name Vorname Straße PLZ Ort Eine Person mit mehreren Schillerstr. 15- wie? 55116 Adressen Mainz Mail Kemper Alfons [email protected] Meier Hans Goethestr. 23a 12345 Mönchengladbach [email protected] Heß Andreas Lessingstr. 25 78120 Furtwangen [email protected] Heß Andreas Goethestr. 39 60318 Frankfurt [email protected] Albrecht Alexandra Marktplatz 1 67543 Heidelberg [email protected] ➔ Manche Informationen doppelt ➔ unklar, ob es sich um die selbe Person handelt Personen identifizieren P-Nr Name Vorname Straße PLZ Ort Mail 1 Kemper Alfons Schillerstr. 15 55116 Mainz 2 Meier Hans Goethestr. 23a 12345 Mönchengladbach [email protected] 3 Heß Andreas Lessingstr. 25 78120 Furtwangen Redundanz! [email protected] [email protected] 3 Heß Andreas Goethestr. 39 60318 Frankfurt [email protected] 4 Albrecht Alexandra Marktplatz 1 67543 Heidelberg [email protected] ➔ Person jetzt eindeutig identifiziert Primärschlüssel? Konzeptuelles Modell • Wildes „Herumschrauben“ an Tabellen ist nicht zielführend • Was wir brauchen ist ein sauberer Entwurf! ONE DOES NOT SIMPLY CREATE A DATABASE Modell: Abbild der Welt • Überlegungen: • Welche Dinge gibt es in der realen Welt? • Welche Eigenschaften haben sie? • Wie stehen sie miteinander in Beziehung? Entity-RelationshipModell nach Chen, 1976; siehe auch Kemper, 7. Aufl., S. 37ff Attribut Name Vorname n Person Entitätstyp Straße wohnt m Hausnr Wohnung n Beziehungstyp PLZ liegt in Schlüssel identifiziert die Entität eindeutig 1 Ortsname Ort Abbildung auf Tabellen • Welche Tabellen braucht man? • Wie sehen sie aus? Person PNr 1 2 3 4 5 Name Kemper Meier Heß Meyer-Heß Albrecht Vorname zusammengesetzter Primärschlüssel WNr Alfons Straße Hausnr PLZ 1 Schillerstraße 15 55116 2 Goethestraße 23a 12345 3 Lessingstraße 25 78120 4 Goethestraße 39 60318 5 Marktplatz 1 78120 Hans Andreas Anke Alexandra (künstlicher) Primärschlüssel Beziehungstabelle Wohnung Fremdschlüssel wohnt PNr WNr 1 2 2 1 3 3 3 (natürlicher) Primärschlüssel Ort PLZ Ortsname 55116 Mainz 4 12345 M‘gladbach 4 4 78120 Furtwangen 5 5 60318 Frankfurt Primärschlüssel • Primärschlüssel (primary key; PK) identifiziert eine Zeile der Tabelle eindeutig • Bei zusammengesetztem PK: Kombination der Werte aller Spalten des PK muss eindeutig sein Fremdschlüssel • Fremdschlüssel (foreign key; FK) stellen Beziehungen zu Zeilen anderer Tabellen her • FK-Spalte einer Tabelle bezieht sich auf Spalte einer anderen Tabelle* • Für FK nur Werte erlaubt, die auch in der Spalte, auf die er sich bezieht, existieren *) fast immer eine PK-Spalte Umwandlung vom E/RModell in Tabellen • E/R-Modell rein konzeptuell • Für DBMS Umwandlung in Tabellen nötig • Entitätstypen ➜ Tabellen • Attribute ➜ Spalten • 1:N-Beziehungstypen ➜ FK auf N-Seite • N:M-Beziehungstypen ➜ Beziehungstabelle Beziehungstabellen • Bei N:M-Beziehungen nötig • Spalten: • FK für jeden beteiligten Entitätstyp • PK zusammengesetzt aus den FK Datenbankentwurf • Drei Ebenen des Entwurfs: • konzeptuelle Ebene • Implementationsebene • physische Ebene Auf welchen Ebenen haben wir bisher gearbeitet? Entwurfsebenen • Konzeptuelle Ebene: • Entity-Relationship-Modell • Implementationsebene: • Tabellen (Relationenmodell) • Physische Ebene • vom DBMS verwaltet Entity-Relationship-Modell • Modellierung von Entitäten (Gegenständen) und Beziehungen zwischen diesen Entitäten • Konzeptuelles Modell Als konzeptuelles Modell komplett unabhängig von einem konkreten Typ von DBMS! Datenbank-Architektur Sicht 1 Sicht 2 ... logische Ebene Sicht n ANSI-SPARC- Architektur physische Ebene logische Ebene ANSI/SPARC-Modell • Auf welcher Ebene spielt sich das alles ab? • Welche Rolle könnten Sichten spielen? ANSI/SPARC-Modell • Tabellen auf der logischen Ebene • physische Ebene: vom DBMS verwaltet • Sichten: stellen für Anwendung aufbereitete Version der Daten zur Verfügung (hier z.B. vollständige Adresse mit Name und Ort) Relationales Modell • Modellierung als Tabellen (Relationen) • Implementierungsebene Die meisten aktuellen DBMS sind relationale DBMS (RDBMS), speichern also Daten in Tabellen ! Relationen ? • Verwechslungsgefahr! • Relationen ≠ Beziehungen! • Bißchen Mathematik zur Erklärung... Relationen R ⊆ D1 × D2 × ... × Dn • Relation ist Teilmenge des kartesischen Produkts (Kreuzprodukts) der Mengen D1 bis Dn • Mengen D bis Dn heißen Domänen oder Wertebereiche 1 Relationen: Beispiel R ⊆ D1 × D2 × ... × Dn Personen ⊆ integer × string × string • Relation = Tabelle • Domänen = Datentypen • Elemente von R = Tupel = Zeilen PersNr Personen Name Vorname 1 Kemper Alfons 2 Meier Hans 3 Heß Andreas Übung • Umwandlung eines E/R-Modells in Tabellen E-Commerce Name Vorname Person 1 Zeit beauftragt n Bestellformular m Produkt ArtNr Preis n enthält Menge Bezeichnung Farbe Beziehungen können auch Attribute haben! Person PNr Bestellformular Name Vorname AuftrNr PersNr Zeit 1 Kemper Alfons 34 3 2017-03-27 13:37 2 Meier Hans 35 4 2017-03-28 20:25 3 Heß Andreas 36 4 2017-03-29 12:05 4 Meyer-Heß Anke 5 Albrecht Alexandra enthaelt Produkt ArtNr 21 22 23 24 25 Bezeichnung Zahnbürste iPhone 7 Gummientchen Laufband Laufschuhe Farbe rosa schwarz gelb schwarz blau Preis 0,99 800,00 4,50 ArtNr AuftrNr Menge 21 34 5 23 34 1 22 35 1 24 36 1 25 36 1 1250,00 123,75 Attribute in Beziehungstabellen • Falls Beziehungstyp Attribute hat: • zusätzliche Spalte für jedes Attribut • gleiches Vorgehen wie bei Entitätstypen Datentypen • Welche Datentypen sollen die Spalten der Tabellen bekommen? Datentypen in DBMS INTEGER ganze Zahlen NUMERIC(n,m), DECIMAL(n,m) Dezimalzahlen FLOAT(n), DOUBLE, REAL Gleitkommazahlen CHARACTER(n), VARCHAR(n) Zeichenketten DATE, TIME, TIMESTAMP Datum und Zeit BLOB, CLOB große Daten BOOLEAN wahr / falsch Datentypen in DBMS INTEGER ganze Zahlen NUMERIC(n,m), DECIMAL(n,m) Dezimalzahlen FLOAT(n), DOUBLE, REAL Gleitkommazahlen CHARACTER(n), VARCHAR(n) Zeichenketten NUMERIC/DECIMAL DATE, TIME, TIMESTAMP Dezimalzahlen Datum und Zeit FLOAT/DOUBLE/REAL binäre große Gleitkommazahlen BLOB, CLOB Daten BOOLEAN wahr / falsch bei binären Gleitkommazahlen: Gefahr von Rundungsungenauigkeiten Datentypen in DBMS INTEGER ganze Zahlen NUMERIC(n,m), DECIMAL(n,m) Dezimalzahlen FLOAT(n), DOUBLE, REAL Gleitkommazahlen CHARACTER(n), VARCHAR(n) Zeichenketten DATE, TIME, TIMESTAMP Datum und Zeit BOOLEAN wahr / falsch CHAR immer feste Länge BLOB, CLOB große Daten VARCHAR variabel bis zur festgelegten Maximallänge Datentypen in DBMS INTEGER ganze Zahlen NUMERIC(n,m), DECIMAL(n,m) Dezimalzahlen FLOAT(n), DOUBLE, REAL Gleitkommazahlen CHARACTER(n), VARCHAR(n) Zeichenketten DATE, TIME, TIMESTAMP Datum und Zeit BLOB, CLOB große Daten BOOLEAN wahr / falsch Werden vom DBMS nur gespeichert und gelesen, aber können nicht verarbeitet werden. NULL • Undefinierte bzw. unbekannte Werte können mit NULL gekennzeichnet werden • NULL ist nicht 0 ! n i Darf ein Primärschlüssel NULL sein? e N Relationales Modell • Schreibweise für relationales Modell: Relation : {[ Primärschlüssel : Datentyp, Attribut : Datentyp, Fremdschlüsselattribut : Datentyp ( ➚ Relation) ]} Beispiel: Adresse : {[ AdressID : INTEGER, PLZ : DECIMAL(5,0) ( ➚ Orte), Strasse : VARCHAR(50) ]} SQL • Structured Query • ANSI / ISO standardisiert • DDL und DML für RDBMS 1 Language 1) früher; heute steht laut ANSI der Name für sich SQL: DDL Tabellen erstellen CREATE TABLE tabelle ( spalte DATENTYP, spalte DATENTYP, ... ); SQL: DDL Tabellen erstellen - Beispiel CREATE TABLE personen ( nummer INTEGER, name VARCHAR(100), vorname VARCHAR(100) ); SQL: DDL Primärschlüssel CREATE TABLE personen ( nummer INTEGER PRIMARY KEY, name VARCHAR(100), vorname VARCHAR(100) ); Generierte Schlüssel • Manchmal bietet sich von den Attributen keines als natürlicher Schlüssel an. • Dann fügt man einen künstlichen Schlüssel bzw. Surrogatschlüssel hinzu. • Besser als einen eindeutigen Wert selber auszudenken ist, einen Schlüssel vom DBMS generieren zu lassen! Generierte Schlüssel • r e Manchmal bietet sich von den Attributen z t u n e B e n r e e d r h keines als natürlicher Schlüssel an. g i e t i M e z n i h e c i e e t l g n ! n n n ö e l e k t h n ä ? n n w i ö Was Dann e t k s r fügt man einen künstlichen Schlüssel i e e b W a d n e m b e l l e s Prob bzw. Surrogatschlüssel hinzu. • • Besser als einen eindeutigen Wert selber auszudenken ist, einen Schlüssel vom DBMS generieren zu lassen! SQL: DDL Primärschlüssel generieren lassen CREATE TABLE personen ( nummer INTEGER PRIMARY KEY AUTO_INCREMENT, L Q S name VARCHAR(100), y M r vorname VARCHAR(100) nu ); SQL: DDL Primärschlüssel generieren lassen CREATE TABLE personen ( nummer INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, SQ L-S tan name VARCHAR(100), dar d vorname VARCHAR(100) ); SQL: DDL Primärschlüssel generieren lassen CREATE TABLE personen ( nummer INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, SQ L-S tan name VARCHAR(100), dar d vorname VARCHAR(100) ); SQL: Zusammengesetzte Primärschlüssel CREATE TABLE Tabelle ( …, PRIMARY KEY(spalte1, spalte2) ); SQL: DDL NULL-Werte zulassen / verbieten CREATE TABLE personen ( nummer INTEGER PRIMARY KEY, name VARCHAR(100) NOT NULL, vorname VARCHAR(100) NOT NULL ); PRIMARY KEY impliziert NOT NULL SQL: DDL Fremdschlüssel SQ CREATE TABLE adressen ( L-S t and person INTEGER ard REFERENCES personen, strasse VARCHAR(100) NOT NULL, plz CHAR(5) von MySQL REFERENCES orte ignoriert! ); SQL: DDL Fremdschlüssel SQ L-S tan dar d auch MySQ CREATE TABLE adressen ( L person INTEGER NOT NULL, strasse VARCHAR(100) NOT NULL, plz CHAR(5) NOT NULL, FOREIGN KEY (person) REFERENCES Person(PNr) ); SQL: DML Daten einfügen INSERT INTO personen VALUES ( 1, 'Kemper', 'Alfons'); INSERT INTO personen(Name, Vorname) VALUES ('Kemper', 'Alfons'); (z.B. falls Spalten leer bleiben sollen oder generierte Schlüssel sind) SQL: DML Daten abfragen SELECT * FROM personen WHERE nummer = 1; WHERE kann auch weggelassen werden, dann werden alle Zeilen ausgegeben. SQL: DML Daten ändern UPDATE personen SET vorname = 'Hans' WHERE nummer = 1; SQL: DML Daten löschen DELETE FROM personen WHERE nummer = 1; Was passiert wohl, wenn man WHERE weglässt? WHERE kann auch weggelassen werden, dann werden alle Zeilen gelöscht! SQL Konsistenz bei Fremdschlüsseln CREATE TABLE adressen ( person INTEGER REFERENCES personen, ...); INSERT INTO adressen VALUES ( 12345, 'Hauptstr. 5', '78120'); 12345 sei kein Primärschlüssel einer Zeile in Tabelle personen - Was passiert? SQL Konsistenz bei Fremdschlüsseln CREATE TABLE adressen ( person INTEGER REFERENCES personen, ...); INSERT INTO adressen VALUES ( 12345, 'Hauptstr. 5', '78120'); Fehlermeldung, wenn 12345 kein Primärschlüssel einer Zeile in Tabelle personen BIS GLEICH IM PRAKTIKUM!
