1 / 119

Teil 3: Relationenmodell

Teil 3: Relationenmodell. Einleitung Daten in Tabellen Definitionen Schlüssel Fremdschlüssel Null-Werte gute Relationenschemata Abbildung des E-R-Modells. Einleitung. Grundlage der meisten derzeitigen Datenbanken vorgestellt von E.F. Codd 1970 Ziel: Daten-Unabhängigkeit

paige
Download Presentation

Teil 3: Relationenmodell

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Teil 3:Relationenmodell • Einleitung • Daten in Tabellen • Definitionen • Schlüssel • Fremdschlüssel • Null-Werte • gute Relationenschemata • Abbildung des E-R-Modells

  2. Einleitung • Grundlage der meisten derzeitigen Datenbanken • vorgestellt von E.F. Codd 1970 • Ziel: Daten-Unabhängigkeit • in kommerziellen DBMS seit 1981 • basiert auf einer Variante des mathematischen Konzepts der Relation • Relationen können auf einfache Weise als Tabellen interpretiert werden • rein wertbasierte Verweise (keine Pointer)

  3. Daten in Tabellen

  4. Mathematische Relation • D1, D2, ..., Dn : n Mengen • kartesisches Produkt: D1 x D2 x ... x Dn • Menge aller n-Tupel (d1, d2, ..., dn), sodaß • d1  D1, d2  D2, ..., dn  Dn • Eine mathematische Relation auf D1, D2, ..., Dn : • Teilmenge des kartesischen Produktes D1 x D2 x ... x Dn • D1, D2, ..., Dn: Wertebereiche (Domänen) der Relation • n ist der Grad der Relation • Anzahl der Tupel wird Kardinalität der Relation genannt

  5. Mathematische Relation(2) • Beispiel: • konto  integer x integer x string x number x number • {(2317, 12345, „Otto Huber“,20.000, 12.375), ...} • Attribute positional

  6. Relationen in DB • Jeder Domain erhält eindeutigen Attributnamen • beschreibt die „Rolle“ des Domains • in Tabellen-Darstellung sind die Attributnamen die Spaltenüberschriften • Bsp.:

  7. Definitionen • Relationen-Schema: • Name einer Relation mit einer Menge von Attributen A1, ..., An R(A1, ..., An) • Datenbank-Schema: • Menge von Relationen mit unterschiedlichen Namen R = { R1(X1), R2(X2), ..., Rn(Xn)} • Relation(Instanz) r mit einem Schema R(X): • Menge r von Tupel gemäß X • Datenbank (Instanz) mit einem Schema R = { R1(X1), R2(X2), ..., Rn(Xn)}: • Menge von Relationen r = {r1, ..., rn} mit ri Relation auf Ri

  8. Datenbank-Schema (Bsp.) • Mitarbeiter(MNr, Mname, GebDatum, AbtNr) • Abteilung(AbtNr, Bez, Ort, Leiter) • Kurs(KursBez, Leiter, Anzahl_Tage) • Zertifikat(MNr, KursBez, Punkte, Datum)

  9. Datenbank (Beisp.) Mitarbeiter Abteilung

  10. Vorteile Wertbasiert: • Jede Information in relationalen DBs wird durch Werte ausgedrückt. • Referenzen (Verweise) ebenfalls nur durch Werte • logisches Modell unabhängig von physischer Struktur • enthält nur Information, die aus Anwendersicht relevant ist, keine Implementierungsdetails • Leichte Transferierbarkeit von Daten zwischen Systemen • keine Pointer!

  11. Relationen mit einem Attribut • Sind möglich (allerdings selten) • Beisp.:

  12. Oberschlüssel • Relation ist Menge von Tupeln - Zeilen müssen verschieden sein. • In Tabelle könnten 2 Zeilen gleich sein - Probleme beim Zugriff auf eine bestimmte Zeile • Wenn keine Zeile mehrfach vorhanden sein kann - dann enthält die Tabelle einen Schlüssel • Oberschlüssel: • identifizierende Attributkombination:

  13. Schlüssel • Mitarbeiter(MNr, Name, SV-Nummer, AbtNr, Geb.datum) • Bsp. Oberschlüssel: • (MNr, Name, Geb.datum), • (SV-Nummer) • Schlüssel: minimaler Oberschlüssel • (d.h. wenn Attribut gestrichen wird, dann kein Oberschlüssel mehr) • Bsp. Schlüssel • MNr • SVNr

  14. Schlüssel (2) • Schlüssel kann aus mehreren Attributen bestehen • Zertifikat(MNr, KursBez, Punkte, Datum) • ein Mitarbeiter kann mehrere Zertifikate haben, auch am selben Tag • zu einem Kurs können an einem Tag mehrere Zertifikate ausgestellt werden • Schlüssel: (Mnr, Kursbez, Datum)

  15. Primärschlüssel • Mitarbeiter (MNr, Mname, SVNr, GebDatum, AbtNr) • in einer Relation kann es mehrere Schlüssel geben • Bsp.: SVNr, MNr • Primärschlüssel: ausgewählter Schlüssel mit dem ein Tupel referenziert werden soll • Bsp: MNr ist Primärschlüssel • ein guter Primärschlüssel: • Wert immer bekannt • Wert ändert sich nie • ... deshalb: SVNr, MNr, KontoNr, ....

  16. Fremdschlüssel • Mitarbeiter (MNr, Mname, SVNr, GebDatum, AbtNr) • Abteilung(AbtNr, Bez, Ort, Leiter) • Fremdschlüssel: • Attribut einer Relation, das in einer anderen Relation Schlüssel ist • Bsp.: Attribut Leiter in Abteilung ist Attribut Mnr in Mitarbeiter • Attribut AbtNr in Mitarbeiter • realisiert Verweise auf eine andere Relation

  17. UML - Klassendiagramm -> Relationen • UML-Klassendiagramm für konzeptuellen Entwurf • Relationenmodell ist semantisch ärmer • nicht alles was im UML-Modell ausgedrückt werden kann, kann auch im Relationenmodell ausgedrückt werden • Integritätsbedingungen im Datenmodell • Vorgaben für Programmierer und Benutzer • Abbildung notwendig • Schritte: • 1. Vorbereiten des konzeptuellen Modells • 2. Abbilden auf Relationen

  18. Vorbereiten Klassen-Diagramm • 1. Eliminieren von Mehrfach-Attributen • eigenes Entity • 2. Eliminieren von komplexen Attributen • „ausflachen“ Person PID Name Hobbies 0..* Adresse Ort Person PID Name PLZ Ort Hobby Bezeichnung * hat Hobby * PLZ Ort

  19. Vorbereiten(2) • mehrwertiges strukturiertes Beziehungsattribut liefert liefert Firma Artikel Firma Lieferort PLZ Ort * * * Lieferung Adresse * PLZ Ort Artikel

  20. Abbildungsregeln (1) • Jede Klasse wird auf eine eigene Relation abgebildet • Name der Relation ist Name der Klasse • Attribute der Klasse sind Attribute der Relation Mitarbeiter MNr Name Job Mitarbeiter( MNr, Name, Job)

  21. Abbildungsregeln (2) Mitarbeiter Mnr Name Job • Jede m:n Assoziation wird auf eine eigene Relation abgebildet • Name der Relation ist der Name der Assoziation • Attribute der Relation sind • Schlüsselattribute der beteiligten Klasse (bei rekursiven Beziehungen erweitert um die Rollenbezeichnung) • Attribute der Beziehung Projektmitarbeit( MNr, ProjNr, Prozent) m Projektmitarbeit Prozent n Projekt ProjNr Bez

  22. Abbildungsregeln (3) Mitarbeiter Mnr Job • 1:n Assoziation • Hier muß keine eigene Relation gebildet werden • Die Relation der mehrfach vorkommenden Klasse wird um den Schlüssel der (Relation der) einfach vorkommenden Klasse und der Assoziations-Attribute erweitert. Kunde(KNr, Kname, Betreuer-MNr, seit) 1 Betreuer betreut seit n Kunde KNr Name

  23. Abbildungsregeln (4) Mitarbeiter Mnr Name Job • 1:1 Assoziationen • Ebenfalls keine eigene Relation • eine der beiden Relationen wird um den Schlüssel und um die Assoziationsatribute der anderen erweitert. Projekt( ProjNr, Bez, Leiter-MNr, seit) 1 Leiter leitet seit 0..1 Projekt ProjNr Bez

  24. Abbildungsregeln (5) • Assoziationen höheren Grades • eigene Relation • besteht aus Schlüsseln der beteiligten Entities • und den Beziehungsattributen Lieferung(LiefNr,AbtNr,ArtikelNr) Artikel Lieferung Lieferant Abteilung

  25. Abbildung berichtet an Chef Kunde KNr. key Name Anschrift 0..1 Mitarbeiter MNr. key Name Job betreut * 0..1 0..n Mitarbeiter 1 gibt auf * 1 * arbeitet in Bestellung Best.Nr. key Daten Anteil leitet * 0..1 * Projekt Proj.Nr. key Bezeichnung umfaßt * Produkt Preis Prod.Nr. key Bezeichnung

  26. Abbildung (Ergebnis) • Mitarbeiter( MNr, Name, Job, Chef-MNr) • Projekt( ProjNr, Bez, Leiter-MNr) • Kunde( Knr, Name, Anschrift, Betreuer-MNr) • Bestellung( BestNr, Datum, Besteller-KNr) • Produkt(ProdNR, Bez, Preis) • Projektarbeit(MNr, ProjNr, Prozent) • Bestellumfang(BestNr, ProdNr)

  27. Teil 4:SQL • Einleitung • Tabellendefinition • Projektion • Selektion • Join • Null-Werte • Komplexe Bedingungen • Abfragegraph

  28. Einleitung • SQL: Structured Query language • Standard für relationale Datenbanken • standardisiert: ANSI, ISO • SQL-89, SQL-92, SQL-99 • Teile „implementor defined“ • herstellerspezifische Erweiterungen

  29. SQL-Umfang • Datendefinitionssprache • Wertebereiche • Relationen • Sichten (externe Modelle) • Datenmanipulationssprache • Select, Insert, Update, Delete • Application Programming Interface • embedded SQL • physisches Modell • tablespace, index, cluster • Operationen • prozedurale Erweiterungen

  30. Schema • Mitarbeiter (MNr, Mname, AbteilungsNr, GebDatum, GebOrt, Chef, Punkte, Klappe) • Abteilung (AbteilungsNr, Bez, Ort, Leiter) • Kunde (KNr, Kname, Ort) • KundenKontakt (KNr, MNr) • Bestellung (BNr, BestDatum, Betreuer_MNr, KNr) • Artikel (ANr, Abez, Preis) • BestellPos (BNr, ANr, Anzahl)

  31. DatendefinitionsspracheDefinition von Tabellen (Relationenschema) • allgemeine Form (vereinfacht) CREATE TABLE tableName ({columnName Datatype [{constraints}]}) • tableName, columnName: übliche Bezeichner; nicht sensitiv auf Groß/Kleinschreibung

  32. DatendefinitionsspracheDateitypen • Datentypen (Wertebereiche) CHAR(size), VARCHAR(size), Zeichenketten mit maximaler VARCHAR2(size) Länge size LONG Zeichenketten bis zu einer Länge von 231 - 1 Bytes (2GB) RAW(size) BLOBS (binary large objects) Byteketten LONG RAW analog CHAR und LONG NUMBER [(precision [, scale])] Zahlen (Fest- u. Gleitpunkt) DATE Datum ROWID eindeutiger Tupelidentifikator aus Kompatibilitätsgründen: INTEGER, FLOAT [(p)], SMALLINT, REAL, DECIMAL (p, s) Subtypen von NUMBER

  33. Constraints (1) • Einschränkung der zulässigen Datenbankzustände • Alle Insert, Update und Delete Operationen bewirken die Evaluation der relevanten Einschränkungen • Operationen werden nur dann ausgeführt, wenn sie die Constraints nicht verletzen • Bedingungen: NOT NULL Attributwert muß ungleich NULL sein UNIQUE Attributwert darf in einer Spalte nur einmal vorkommen PRIMARY KEY Primärschlüssel (NOT NULL notwendig) FOREIGN KEY Fremdschlüssel CHECK Angabe einer Bedingung

  34. Column Constraints andTable Constraints Bsp.: CREATE TABLE Flug (Flugnr NUMBER (3) NOT NULL, Linie CHAR (3) NOT NULL, DATUM Date, Abflug Time with Time Zone, Ankunft Time with Time Zone, Preis Number, CONSTRAINT Schlüssel_Flug PRIMARY KEY (FlugNr, Linie, Datum, Sitz), CONSTRAINT vor_nach CHECK (Abflug < Ankunft), CONSTRAINT ok CHECK (Preis >= (SELECT ...));

  35. Tabellendefinition create table Abteilung (AbteilungsNr integer primary key not null, Bez varchar(15), Ort varchar(10), Leiter integer ); create table Mitarbeiter (MNr integer primary key not null, Mname varchar(20), AbteilungsNr integer references Abteilung, GebDatum date, GebOrt varchar(10), Chef integer references Mitarbeiter(MNr), Punkte integer, Klappe varchar(4) );

  36. Tabellendefinition(2) create table Kunde (KNr integer primary key not null, Kname varchar(20), Ort varchar(10) ); create table KundenKontakt (KNr integer not null references Kunde, MNr integer not null references Mitarbeiter constraint Kkkey primary key(KNr, MNr) );

  37. Tabellendefinition(3) create table Bestellung (BNr integer primary key not null, BestDatum date, Betreuer_MNr integer references Mitarbeiter, Knr integer references Kunde); create table Artikel (Anr integer not null primary key, ABez varchar(15), Preis integer ); create table bestellPos (BNr integer not null references Bestellung, Anr integer not null references Artikel, Anzahl integer constraint bpkey primary key (BNr, Anr));

  38. DatendefinitionsspracheEntfernen von Tabellen DROP TABLE tableName Bsp.: DROP TABLE Mitarb

  39. Assertionen (Zusicherungen) i. d. R. Bedingungen, die mehrere Tabellen betreffen Bsp.: CREATE ASSERTION positiv CHECK ((SELECT SUM (Betrag) FROM Ausgaben) < (SELECT SUM (Betrag) FROM Einnahmen))

  40. (Benannte) Referentielle Integrität Jeder Wert eines Fremdschlüsselattributs muß in der referenzierten Tabelle (als Schlüssel) vorkommen. Bsp.: Alle Werte des Attributes AbtNo in der Tabelle Mitarb müssen auch als Werte des Attributes AbtNo in der Tabelle Abteilung vorkommen. CREATE TABLE Mitarb ... CONSTRAINT M_Abt_fk FOREIGN KEY (AbtNo) REFERENCES Abteilung (AbtNo) ...

  41. Referentielle Constraint Aktionen geben an, was passieren soll, wenn ref. Integrität verletzt wird Anlässe: Delete, Update • Aktion zurückweisen: durch Definition des Foreign Key • Auf Default setzen, auf Null setzen ... AbtNo Number (3) DEFAULT '10' REFERENCES Abteilung ON DELETE SET DEFAULT, ON UPDATE SET NULL ... • Änderung fortpflanzen • AbtNo Number (3) REFERENCES Abteilung • ON UPDATE CASCADE • Automatisches Löschen von abhängigen Fremdschlüsseln • ON DELETE CASCADE • keine Aktion • ON DELETE NO ACTION • Cascade kann über mehrere Fremdschlüssel- Verbindungen definiert sein

  42. Basisoperationen • Projektion: • Auswählen von Spalten aus einer Tabelle • Selektion: • Auswählen von Zeilen aus einer Tabelle • Kartesisches Produkt • jedes Tupel mit jedem erweitern • Verbund • Vereinigen zweier Tabellen aufgrund gemeinsamer Werte • Vereinigung • Mengenvereinigung der Tupelmengen • Durchschnitt • Komplement

  43. Grundform der Select-Anweisung • Select t1.a1, t1.a2, ..., tm.an Zielattribute From R1 t1, R2 t2, ..., Rm tm Relationen Where P1 AND P2, ... AND Pk Selektionsbedingung • entspricht Ausdruck im Tupelkalkül {(t1.a1, t1.a2, ..., tm.an) | t1R1,t2 R2, ..., tm Rm, P1, P2, ..., Pk }

  44. Projektion • Auswählen von Spalten aus einer Tabelle • „Name und Geburtsdatum aller Mitarbeiter“ • Select MName, GebDatum From Mitarbeiter; MNAME GEBDATUM -------- --------- Huber 03-NOV-52 Neumann 01-FEB-66 Novak 05-DEC-41 Willy 07-JUL-70 Horvat 29-OCT-59 Asthobl 11-NOV-69 Baumer 10-SEP-65 Zuder 27-APR-43

  45. Projektion(2) • Alle Daten einer Tabelle • Select * From Mitarbeiter; MNR MNAME ABTEILUNGSNR GEBDATUM GEBORT CHEF PUNKTE KLAP ------ -------- ------------ --------- -------- ---- ------ ---- 27 Huber 10 03-NOV-52 Wien 11 60 547 45 Neumann 20 01-FEB-66 Linz 27 51 655 23 Novak 30 05-DEC-41 Wels 11 20 877 22 Willy 40 07-JUL-70 Wien 27 67 124 1777 Horvat 20 29-OCT-59 Linz 45 78 56 Asthobl 20 11-NOV-69 Wien 45 43 1566 Baumer 30 10-SEP-65 Villach 23 65 765 11 Zuder 27-APR-43 Villach 30 523

  46. Eliminieren von Duplikaten Select Distinct Ort From Abteilung; Select Ort From Abteilung; Distinct ORT -------- Linz Wien ORT -------- Wien Linz Wien Linz

  47. Selektion • Auswahl von Zeilen einer Tabelle • „Die Daten aller Abteilungen in Wien“ • Select * From Abteilung Where Ort = ´Wien´ ABTEILUNGSNR BEZ ORT LEITER ------------ --------------- -------- ---------- 10 Strickwaren Wien 27 30 Eisenwaren Wien 23

  48. Selektion (2) • Mehrere Bedingungen • „Alle Mitarbeiter der Abteilung 20 mit mehr als 50 Punkten“ • Select * From Mitarbeiter Where AbteilungsNr = 20 And Punkte > 50; MNR MNAME ABTEILUNGSNR GEBDATUM GEBORT CHEF PUNKTE KLAP ------ -------- ------------ --------- -------- ---- ------ ---- 45 Neumann 20 01-FEB-66 Linz 27 51 655 1777 Horvat 20 29-OCT-59 Linz 45 78

  49. Projektion und Selektion • Name und Telefonnummer aller Mitarbeiter in den Abteilungen 20 und 30 mit mehr als 50 Punkten • Select Name, Klappe From Mitarbeiter Where Punkte > 50 And (AbteilungsNr = 20 Or AbteilungsNr = 30); MNAME KLAP -------- ---- Neumann 655 Horvat Baumer 765

  50. Selektion(3) • Selektion nach Schlüssel: • Name des Mitarbeiters mit der MNr 27 • Select Mname as Name From Mitarbeiter Where Mnr = 27; NAME -------- Huber

More Related