r ckblick n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Rückblick PowerPoint Presentation
Download Presentation
Rückblick

Loading in 2 Seconds...

play fullscreen
1 / 151

Rückblick - PowerPoint PPT Presentation


  • 105 Views
  • Updated on

Rückblick. CREATE TABLE Statement. Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002. CREATE TABLE. Allgemein: CREATE TABLE table_name (spaltendefinitionsliste [,tabellenintegritätsregelliste]); spaltendefinition ::= spaltenname typangabe [default-Klausel]

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

Rückblick


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
    Presentation Transcript
    1. Rückblick CREATE TABLE Statement Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    2. CREATE TABLE Allgemein: CREATE TABLE table_name(spaltendefinitionsliste[,tabellenintegritätsregelliste]); spaltendefinition ::=spaltenname typangabe [default-Klausel] [spaltenintegritätsregelliste] tabellenintegritätsregel ::= check-klausel | primary_key-klausel | unique-klausel | foreign-key-klausel Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    3. CREATE TABLE Beispiel CREATE TABLE bestellung ( bestellnr INTEGER NOT NULL, kundennr INTEGER NOT NULL, bestelldatum DATE NOT NULL, lieferdatum DATE, rechnungsbetrag DECIMAL(8,2), CHECK(bestelldatum < lieferdatum), PRIMARY KEY (bestellnr), FOREIGN KEY (kundennr) REFERENCES Kunde ON UPDATE CASCADE ON DELETE CASCADE ); Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    4. SQL-Datentypen INTEGER (4 Byte) 123456, -653437, +12 SMALLINT (2 Byte) 1234. –6354 NUMERIC(p,q) (Dezimalzahl genau p Stellen, q hinter „.“) DECIMAL(p,q) (Dezimalzahl mind. p Stellen, q hinter „.“) FLOAT(p) (Gleitpunktzahlen mindestens p Stellen) 2.96E+8 CHARACTER(n) Zeichenketten mit genau n Zeichen CHARACTER VARYING(n) (Zeichenketten mit höchstens n) DATE DATE'1995-06-22' TIME(p) TIME'09:18:05.23, (p: Nachkommastellen für Sekunden) TIMESTAMP(p) Datum + Uhrzeit TIMESTAMP'1995-06-06 10:00' BOOLEAN TRUE, FALSE, UNKNOWN BIT(n) Bitketten mit genau n Bits Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    5. Übungsaufgabe Schemadefinition Vertriebsdatenbank (Web-Shop) in SQL Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    6. Preis Anzahl BDatum PNr BNr KuNr n 1 n m Posten bestellt Produkt Bestellung Kunde m n n 1 n n Anzahl LDATUM Preis offeriert LPreis liefert aus bearbeitet liefert OPosten beschreibt 1 n 1 Spediteur Mitarbeiter n Offerte 1 n 1 1 SNr MNr ONr bearbeitet Offerte Lieferant Kategorie KaNr LNr

    7. Tabellen Vertriebsdatenbank Kunde(KuNr, Name, Adresse, Rabatt) Produkt(PNr, P-Name, KaNr, LNr, Preis, LPreis) Bestellung(BNr,KuNr,MNr,SNr,Bestelldatum, Lieferdatum) Lieferant(LNr,Name, Adresse) Kategorie(KaNr,Name) Spediteur(SNr, Name, Adresse) Mitarbeiter(MNr,Name, Adresse) Offerte(Onr, KuNr, MNr) Posten(PNr,BNr,Anzahl) Oposten(PNr,ONr,Anzahl,Preis) Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    8. Lösungsbeispiel 1 CREATE TABLE Kunde ( KuNr integer PRIMARY KEY, Name char(20) NOT NULL, Adresse char(50), Rabatt Decimal(3,1) ); Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    9. Lösungsbeispiel 2 CREATE TABLE Bestellung ( BNr integer PRIMARY KEY, KuNr integer NOT NULL, MNr integer, SNr integer, Bestelldatum Date DEFAULT CURRENT_DATE, Lieferdatum Date, FOREIGN KEY (KuNr) REFERENCES Kunde ON DELETE NO ACTION ON UPDATE CASCADE, FOREIGN KEY (MNr) REFERENCES Mitarbeiter ON DELETE SET NULL ON UPDATE CASCADE, FOREIGN KEY (SNr) REFERENCES Spediteur ON DELETE SET NULL ON UPDATE CASCADE ); Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    10. Lösungsbeispiel 3 CREATE TABLE Posten ( BNr integer, PNr integer, Anzahl integer NOT NULL, PRIMARY KEY (BNr, PNr), FOREIGN KEY (BNr) REFERENCES Bestellung ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (PNr) REFERENCES Produkt ON DELETE NO ACTION ON UPDATE CASCADE ); Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    11. Aufgabe Bibliothek • Modellieren Sie eine Bibliothek (Bücher/ Ausleihe / Autoren / Benutzer (Ausleihende) / Vormerkungen) im E/R-Modell (möglichst redundanzfrei).Folgendes sollte dabei berücksichtigt werden: • Ein Buch kann mehrere Autoren haben. • Ein Buch kann in verschiedenen Auflagen vorliegen. • Jede Auflage eines Buches kann in mehreren Exemplaren in der Bibliothek vorhanden sein. • Bücher sollten nach explizit zugeordneten Schlagworten gesucht werden können. • Übersetzen Sie das E/R-Modell möglichst redundanzfrei ins relationale Modell (Datenbankschema in Tabellenform) • Geben Sie alle SQL-Kommandos an, die zur Anlage der Tabellen notwendig sind (inklusive aller sinnvollen Integritätsregeln). Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    12. Bibliotheks-anwendung

    13. ISBN Titel ANr Name ? m n Autor von Buch Autor Verlag Vorname Ort 1 n SNr hatAufl von Schlagwort m Wort n Auflage Buch- auflage Preis Datum 1 Jahr hatExemp leiht aus BNr Name 1 n m Datum Benutzer Buch- exemplar Vorname BibNr hat vorgemerkt m n Standort Adresse

    14. Bibliothek - Tabellenmodell Autor(Anr, Name, Vorname) Buch(ISBN, Titel, Verlag, Ort) Autor-von(Anr,ISBN) Schlagwort(SNr, Wort) Schlagwort-von(SNr,ISBN) Buchauflage(Auflage, Preis, Jahr, ISBN) Buchexemplar(BibNr, Standort, ISBN, Auflage) Benutzer(BNr, Name, Vorname, Adresse) Leiht-aus(BibNr,BNr,Datum) Hat-vorgemerkt(BNr,BibNr, Datum)

    15. Bibliothek Datenbankschema CREATE TABLE Statements

    16. Autor CREATE TABLE Autor ( ANr INTEGER PRIMARY KEY, Name CHAR(30) NOT NULL, Vorname CHAR(30) );

    17. Buch CREATE TABLE Buch ( ISBN INTEGER PRIMARY KEY, Verlag CHAR(30) NOT NULL, Titel CHAR(30) NOT NULL, Ort CHAR(30) );

    18. Autor-von CREATE TABLE Autor-von ( ISBN INTEGER, ANr INTEGER, PRIMARY KEY ( ISBN, ANr), FOREIGN KEY ISBN REFERENCES Buch ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY ANr REFERENCES Autor ON DELETE CASCADE ON UPDATE CASCADE );

    19. Schlagwort CREATE TABLE Schlagwort ( SNr INTEGER PRIMARY KEY, Wort CHAR(30) NOT NULL );

    20. Schlagwort von CREATE TABLE Schlagwort-von ( ISBN INTEGER FOREIGN KEY REFERENCES Buch, SNr INTEGER FOREIGN KEY REFERENCES Schlagwort, PRIMARY KEY ( ISBN, SNr) );

    21. Buchauflage CREATE TABLE Buchauflage ( ISBN INTEGER, Auflage INTEGER, Preis NUMERIC(8,2), Jahr CHAR(4), PRIMARY KEY(ISBN, Auflage), FOREIGN KEY ISBN REFERENCES Buch ON DELETE CASCADE ON UPDATE CASCADE);

    22. Buchexemplar CREATE TABLE Buchexemplar ( BibNR INTEGER PRIMARY KEY, Standort CHAR(10), ISBN INTEGER NOT NULL, Auflage INTEGER NOT NULL, FOREIGN KEY ISBN REFERENCES Buchauflage ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY Auflage REFERENCES Buchauflage ON DELETE CASCADE ON UPDATE CASCADE );

    23. Benutzer CREATE TABLE Benutzer ( BNr INTEGER PRIMARY KEY, Name CHAR(30) NOT NULL, Vorname CHAR(30) NOT NULL, Adresse CHAR(60) NOT NULL );

    24. Leiht-aus CREATE TABLE leiht-aus ( BibNr INTEGER FOREIGN KEY REFERENCES Buchexemplar, BNr INTEGER FOREIGN KEY REFERENCES Benutzer, PRIMARY KEY (BNr, BibNr) );

    25. Hat-vorgemerkt CREATE TABLE hat-vorgemerkt ( BibNr INTEGER FOREIGN KEY REFERENCES Buchexemplar, BNr INTEGER FOREIGN KEY REFERENCES Benutzer, Datum DATE, PRIMARY KEY (BNr, BibNr) );

    26. SELECT Anweisung

    27. SELECT mit einer Tabelle

    28. SELECT - Anweisung • Die SELECT-Anweisung dient der Definition und Ausgabe einer virtuellen Tabelle auf der Basis vorhandener Tabellen und Abfragen • Die SELECT-Klausel realisiert die Projektion • Die Selektion erfolgt in den WHERE- und HAVING-Klauseln Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    29. SELECT - Anweisung Unterscheidung: • SELECT-Anweisung • Abfrage-Anweisungist eine SELECT-Anweisung ohne ORDER BY-Klausel Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    30. SELECT - Anweisung allgemeinste Form: SELECT [DISTINCT | ALL] A1, ..., Ak, <AGG>(Ak+1), ..., <AGG>(Ak+n)FROM R1, ..., Rm[WHERE <condition1>][GROUP BY Ai1, ..., Ail][HAVING <condition2>][ORDER BY Ap1, ..., Apm ] Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    31. SELECT -Reihenfolge Syntaktische Reihenfolge der Klauseln SELECT [DISTINCT | ALL] A1, ..., Ak, <AGG>(Ak+1), ..., <AGG>(Ak+n)FROM R1, ..., Rm[WHERE <condition1>][GROUP BY Ai1, ..., Ail][HAVING <condition2>][ORDER BY Ap1, ... Apm ] Die angegebene Reihenfolge der Klauseln ist zwingend! Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    32. SELECT -Reihenfolge „Zeitliche“ Abarbeitung der Klauseln 1. FROM-Klausel 2. [WHERE-Klausel] 3. [GROUP BY - Klausel] 4. [HAVING -Klausel] 5. SELECT -Klausel 6. [ORDER BY ... ] Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    33. SELECT - [DISTINCT | ALL] SELECT-Kommando ([DISTINCT | ALL]): SELECT [DISTINCT | ALL] A1, ..., Ak, FROM R1, ..., Rm DISTINCT : alle identische Zeilen in der Ergebnistabelle werden zusammengefasst. ALL : alle auftretenden Tupel werden angezeigt (Voreinstellung) Die Angabe ist optional. Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    34. SELECT - Klauseln SELECT-Klausel leistet die Projektion auf die gewünschten Attribute. Es werden die Attribute aufgelistet, die die Ergebnisliste enthalten soll. • „*“ listet alle Attribute der Tabelle auf. • Integriert auch arithmetische Operationen und Aggregatfunktionen Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    35. SELECT - Klauseln FROM-Klausel hier werden die Tabellen aufgelistet, aus denen die Daten entnommen werden sollen. ermöglicht Umbenennungen durch „Tupelvariablen“ bzw. ALIAS-Namen die verwendeten Relationen werden mittels des kartesischen Produktes verknüpft Verbünde (JOINS) können direkt definiert werden. Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    36. SELECT - Klauseln WHERE-Klausel • leistet die Selektion der gewünschten Tupel aus den betrachteten Tabellen, durch Angabe einer Bedingung. • Geschachtelte Unterabfragen sind in der WHERE-Klausel möglich. • Erlaubt die Formulierung von Verbundbedingungen um z.B. aus einem kartesischen Produkt einen Gleichverbund (EQUI-JOIN) zu machen. Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    37. SELECT - Klauseln GROUP BY-Klausel • fasst alle Tupel gemäss Gleichheit bezüglich der Werte der angegebenen Attribute jeweils in einer Gruppe zusammen und erzeugt eine Tabelle, in der Attribute auftreten, die für jede Gruppe einen Wert haben. Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    38. SELECT - Klauseln HAVING-Klausel leistet die Selektion der gewünschten Tupel aus der Ergebnistabelle, die durch die GROUP BY Klausel erzeugt wurde. ORDER BY-Klausel sorgt für eine sortierte Ausgabe der Ergebnistabelle gehört nicht zur Abfrage-Anweisung Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    39. SELECT - WHERE-Klausel [WHERE <condition1>] condition 1 ist dabei logischer Ausdruck logischer Ausdruck • atomarer logischer Ausdruck • (a AND b), (a OR b), NOT b wobei a und b logische Ausdrücke sind. Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    40. SELECT - WHERE-Klausel Alle Tupel, für die der Vergleich das Resultat FALSE oder UNKNOWN (NULL) ergibt, erscheinen nicht im Ergebnis. Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    41. SELECT - WHERE-Klausel [WHERE <condition1>] atomarer logischer Ausdruck • Term1 Vergleichsoperator Term 2 Vergleichsoperatoren: {=, <, >, <>,<=, >=} Beispiel: kundennr*24 = 19+7 Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    42. SELECT - WHERE-Klausel [WHERE <condition1>] Term • Spaltenname (z.B.: „name“, „kundennr“) • Vergleichswert ( 24, NULL, Peter) • Funktion auf Termen (kundennr*24 / 19+7) Numerische Operatoren: {+. -, *, /} viele zusätzliche Funktionen Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    43. SELECT - WHERE-Klausel Zusätzliche Funktionen • Aggregatfunktionen (COUNT, SUM, AVG, MAX, MIN etc.) • Datums- und Zeitfunktionen (LAST_DAY, NEXT_DAY, etc.) • Arithmetische Funktionen (ABS, LN, LOG, COS SIN etc) • Zeichenfunktionen (LENGTH, CONCAT, etc.) • Umwandlungsfunktionen (TO-CHAR, TO-NUMBER) Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    44. SELECT - WHERE-Klausel Atomarer logischer Ausdruck (Beispiel): kundennr = 103 ort <> ‘Basel‘ lieferdatum IS NULL lieferdatum IS NOT NULL mindestbestand = 400 mindestbestand / 2 = 12*24 Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    45. SELECT - WHERE-Klausel logischer Ausdruck (Beispiel): kundennr = 103 AND ort <> ‘Basel‘ lieferdatum IS NULL OR mindestbestand = 400 lieferdatum IS NOT NULL AND kundennr > 100 NOT mindestbestand / 2 = 12*24 Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    46. SELECT - NULL-Werte NULL-Werte in Vergleichen liefermenge = NULL hat als Ergebnis „unknown“ (NULL) Deshalb muss die Abfrage anders aussehen: liefermenge IS NULL bzw. liefermenge IS NOT NULL Diese Abfragen sind wahr oder falsch! Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    47. SELECT Beispiel Null-Werte: SELECT bestellnr, artikelnr, bestellmenge, liefermenge FROM position WHERE liefermenge IS NULL; Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    48. SELECT - Projektion und Selektion Beispiel 1: SELECT kundennr, name, status FROM kunde WHERE status = ‘S‘; Gibt alle Kundeneinträge mit status =„S“ aus, projiziert auf die Attribute „kundennr, name, status“, der Tabelle kunde. Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    49. SELECT - Projektion und Selektion Beispiel 2: SELECT * FROM kunde WHERE status = ‘S‘; Gibt alle Kundeneinträge mit status =„S“ aus, projiziert auf alle Attribute der Tabelle kunde. Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002

    50. SELECT Beispiel 3: SELECT bestellnr, artikelnr, bestellmenge, liefermenge FROM position WHERE liefermenge <= bestellmenge * 0.9; Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002