03 SQL-neu - Bildungsportal Sachsen
Werbung
WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung Programmierung und Datenbanken II Sommersemester Wiederholung – Modellierung WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung Kunde(E-Mail, Name) Entwurf mietet(E-Mail, von_bis, Kennz.) Entwurfsmodell Auto(Kennz., Max_Pers.) p Im Abstraktion Analysemodell un An r tie aly en se lem SQL-Statements g Problem Ausgangszustand Lösung Transformation Zielzustand • Konzeptuelle Modellierung als Instrument der Abstraktion (Komplexitäsreduktion), Dokumentation und Kommunikation WI Systementwicklung Programmierung & Datenbanken II Folie 2 Wiederholung – ERM & SERM WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung • Datenmodellierung mit dem ERM – Struktursicht – Abbildung von Datenobjekttypen und deren Beziehungen – EntityTyp, RelationshipTyp, Attribut • Schwachstellen des ERM – Unübersichtlichkeit, kein Ordnungsprinzip – Keine Visualisierung von Existenzabhängigkeiten – Redundante Relationen, Gefahr der Modellierung von Kreisen • SERM – Gerichtet entsprechend der Existenzabhängigkeiten – Neu Typ: EntityRelationshipTyp !! – Multipliziäten-Semantik Teil der konkreten Syntax der Kanten WI Systementwicklung Programmierung & Datenbanken II Folie 3 Wiederholung – ERM & SERM WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung WI Systementwicklung Programmierung & Datenbanken II Folie 4 Agenda WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung • Motivation • Relationale Sprachen – Entstehung • SQL - Datendefinition – DB-Strukturen definieren, ändern, löschen • SQL – Datenabfrage – Datensätze abfragen • SQL – Datenmanipulation – Datensätze einfügen, ändern, löschen WI Systementwicklung Programmierung & Datenbanken II 5 Motivation SQL – Semantische Lücke WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung Menschliche Weltsicht • Dinge • Handlungen • Kalkuliere Angebot • Löse Bestellung aus • Stelle Rechnung Begriffswelt der Informatik • Daten • Algorithmen • 0110100010110 Deklaration Interpreter Computerverständliche Beschreibungssprache WI Systementwicklung Programmierung & Datenbanken II 6 Motivation SQL WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung CD_I D Datenbank WI Systementwicklung Track Titel CD_I D Album Interpret 7385 1 Himmelblau 7385 Jazzistanders DieÄrzte 7385 2 LiedvomScheitern 4936 Nevermind Nirvana 7385 3 Breit 3856 Sticky Fingers RollingStones 4936 1 Smells Like TeenSpirit 9234 Geräusch DieÄrzte 4936 2 InBloom 4936 3 Come AsYou Are 3856 1 BrownSugar 3856 2 Sway 3856 3 WildHorses Interpret Band_Gründung DieÄrzte 1982 Nirvana 1987 RollingStones 1961 Programmierung & Datenbanken II 7 Anforderungen WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung • Ad-hoc-Formulierung – Benutzer soll eine Anfrage formulieren können, ohne ein vollständiges Programm schreiben zu müssen. • Deklarativität – Benutzer soll formulieren „Was will ich haben?“ und nicht „Wie komme ich an das, was ich haben will?“ – Deklarative Sprache (beschreibend) • Mengenorientiertheit – Operationen auf Mengen von in Tabellen organisierten Daten – Nicht navigierend, nur eine Ergebnismenge („tuple-at-a-time“) • Abgeschlossenheit – Ergebnis ist wieder eine Relation und kann ebenso als Relation für die nächste Anfrage verwendet werden. WI Systementwicklung Programmierung & Datenbanken II 8 Entstehung relationale DB-Sprachen WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung • SQL Mitte der 70er Jahre für „System R“ eingeführt – System R erstes Relationale DBMS – Nutzte SEQUEL als Abfragesprache, die von IBM für Flugzeugbau entwickelt und lizensiert wurde • Standardisierung durch ANSI und ISO 1986 unter Namen „Structured Query Language (SQL)“ • Erweiterung – Integration von XML-bezogene Spezifikationen (XML Datentyp und Erweiterungen von SQL um XQuery) • Trotz Standardisierung, bestehen Inkompatibilitäten zwischen verschiedenen DBMS der einzelnen Hersteller (MySql, MSSql, OracleSql, PostgreSql etc.) WI Systementwicklung Programmierung & Datenbanken II 9 Exkurs – Wichtigste DBMS WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung Datenbanksystem Hersteller Oracle Database Oracle Microsoft SQL Server Microsoft MySQL Oracle* DB2 IBM Informix IBM Teradata Teradata PostgreSQL PostgreSQL Global Development Group* SAP Sybase SAP ... *Open Source WI Systementwicklung Programmierung & Datenbanken II 10 Zusammenfassung - SQL WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung • Meist-verbreitete Datenbankanfragesprache • Ad-hoc und einfach einsetzbar (auch ohne Programmieroverhead) • Deklarativ – Nicht prozedural bzw. imperativ – Optimierbar • Very-High-Level Language (Hoher Domänenbezug, sehr hohe Abstraktion) • Anfragen an relationale Algebra angelehnt (Hinzukommt DML) • Achtung: Syntax kann sich von System zu System leicht unterscheiden • Achtung: Funktionalität kann sich von System zu System leicht unterscheiden WI Systementwicklung Programmierung & Datenbanken II 11 Agenda WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung • Motivation • Relationale Sprachen – Entstehung • SQL – Datendefinition (DDL) – DB-Strukturen definieren, ändern, löschen • SQL – Datenabfrage (DML) – Datensätze abfragen • SQL – Datenmanipulation (DML) – Datensätze einfügen, ändern, löschen WI Systementwicklung Programmierung & Datenbanken II 12 SQL – Datenbankstrukturen WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung Schema Table Table.A1 Table Table.A2 Table.A3 Table.A4 Table.A1 Table.A2 Table.A3 Table.A4 Table Table.A1 WI Systementwicklung Table.A2 Table.A3 Table.A4 Programmierung & Datenbanken II 13 DB-Strukturen definieren WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung WI Systementwicklung Programmierung & Datenbanken II 14 SQL Datentypen* WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung INT | Integer GanzeZahl -2^31bis2^31-1 DEC | Decimal | Decimal (p, [q]) DezimalzahlpStellen,qDezimalstellen FLOAT Fließkomma-Zahl REAL ReelleZahl5*10^-324bis1,7*10^308 CHAR(n) ZeichenkettederLängen VARCHAR(n) VariableZeichenkettederLängen BOOLEAN Wahrheitswert,true false unknown DATE DatumJJJJ-MM-TT TIME UhrzeitHH:MM:SS TIMESTAMP ZeitstempelJJJJ-MM-TTHH:MM:SS BLOB Binäres Objekt *Auswahl WI Systementwicklung Programmierung & Datenbanken II 15 SQL Integritätsbedingungen WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung UNIQUE Werte fürAttributmüsseneindeutigsein NOT NULL AttributdarfkeineleerenZellenbesitzen DEFAULT <Wert> Standard-Wert,wennnichtsangegeben wird PRIMARY KEY Attributist(Teildes)Primärschlüssel(s) FOREIGN KEY REFERENCES <Tabellen-Name>(<Attribut-Name>) AttributistFremdschlüsselund referenziertdasangegebene Attributder angegebenenTabelle ON DELETE CASCADE | SET NULL | SET DEFAULT Aktion,diebeimlöschenvonDS ausgeführtwerdensoll ON UPDATE CASCADE | SET NULL | SET DEFAULT Aktion,diebeimändernvonDS ausgeführtwerdensoll WI Systementwicklung Programmierung & Datenbanken II 16 SQL – DB-Struktur erzeugen – Beispiel Exkurs - Relationenmodell WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung Attribute Relationenname R A1 An Tabelle/ Relation Relationenname R (Attribut A1,... An) (Primärschlüssel unterstrichen, Fremdschlüssel Linie als Dach) TAlbum CD_ID 4 Name Interpret 13 Die Ärzte TAlbum (CD_ID, Name, Interpret) WI Systementwicklung Programmierung & Datenbanken II Folie 17 SQL – DB-Struktur erzeugen – Beispiel WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung Interpret Band_Gründung TInterpret CD_ID Album Track TAlbum Titel TTitel Ausgangssituation - Relationenschema • TInterpret (Interpret, Band_Gründung) • TAlbum (CD_ID, Album, Interpret) • TTitel (CD_ID, Track, Titel) WI Systementwicklung Programmierung & Datenbanken II 18 SQL – DB-Struktur erzeugen – Beispiel WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung CD_I D CD_I D Track Titel Album Interpret 7385 1 Himmelblau 7385 Jazzistanders DieÄrzte 7385 2 LiedvomScheitern 4936 Nevermind Nirvana 7385 3 Breit 3856 Sticky Fingers RollingStones 4936 1 Smells Like TeenSpirit 9234 Geräusch DieÄrzte 4936 2 InBloom 4936 3 Come AsYou Are 3856 1 BrownSugar 3856 2 Sway 3856 3 WildHorses WI Systementwicklung Interpret Band_Gründung DieÄrzte 1982 Nirvana 1987 RollingStones 1961 Programmierung & Datenbanken II 19 Schema + Tabelle Interpret WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung Schema CREATE SCHEMA Audiodatenbank; Interpret Interpret Band_Gründung CREATE TABLE TInterpret ( Interpret VARCHAR(50) PRIMARY KEY, Band_Gründung INT); WI Systementwicklung Programmierung & Datenbanken II 20 Schema + Tabelle Interpret WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung Album CREATE TABLE TAlbum( CD_ID INT PRIMARY KEY, Album VARCHAR(50), Interpret VARCHAR(50)) FOREIGN KEY (Interpret) REFERENCES TInterpret(Interpret); CD_I D Album Interpret CD_I D Track Titel CREATE TABLE TTitel( CD_ID INT NOT NULL, Track INT NOT NULL, Titel VARCHAR(50) NOT NULL) PRIMARY KEY (CD_ID, Track), FOREIGN KEY (CD_ID) REFERENCES TAlbum(CD_ID); WI Systementwicklung Programmierung & Datenbanken II Titel 21 DB-Strukturen ändern WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung Attribut (Spalte) hinzufügen ALTER TABLE <Tabellen-Name> ADD COLUMN <Attribut-Name> <Datentyp> [<Integritätsbedingungen>]; • Notwendig für sich gegenseitig referenzierende Tabellen Attribut (Spalte) löschen ALTER TABLE <Tabellen-Name> DROP COLUMN <Attribut-Name> CASCADE; • Cascade löscht ebenfalls Attribute, die sich auf das zu löschende Attribut beziehen. CREATE TABLE Angestellter (PersNr CHAR(10) PRIMARY KEY); CREATE TABLE Abteilung( AbtNr INT PRIMARY KEY, Leiter CHAR(10) FOREIGN KEY REFERENCES Angestellter(PersNr)); ALTER TABLE Angestellter ADD COLUMN AbtNr INT FOREIGN KEY REFERENCES Abteilung(AbtNr); WI Systementwicklung Programmierung & Datenbanken II 22 Agenda WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung • Motivation • Relationale Sprachen – Entstehung • SQL – Datendefinition (DDL) – DB-Strukturen definieren, ändern, löschen • SQL – Datenabfrage (DML) – Datensätze abfragen • SQL – Datenmanipulation (DML) – Datensätze einfügen, ändern, löschen WI Systementwicklung Programmierung & Datenbanken II 23 SQL – Datenabfrage WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung • Projektion – Auswahl bestimmter Attribute einer Relation • Selektion – Auswahl bestimmter Datensätze einer Relation • Verbund (Join) – Verknüpfung zweier Relationen zu einer Projektion CD_I D Selektion Interpret Interpret Band_Gründung 7385 Jazzistanders DieÄrzte DieÄrzte 1982 4936 Nevermind Nirvana Nirvana 1987 3856 Sticky Fingers RollingStones RollingStones 1961 9234 Geräusch DieÄrzte WI Systementwicklung Album Verbund Programmierung & Datenbanken II 24 Einfache Datenabfrage WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung • Natürliche Sprache: – Ermittle alle Alben-Namen von den Ärzten! • Deskriptive Sprache SQL: SELECT Album FROM TAlbum WHERE Interpret = ´Die Ärzte´; Projektion Selektion • Ergebnis: CD_I D Album Interpret Album 7385 Jazzistanders DieÄrzte Jazzistanders 4936 Nevermind Nirvana Geräusch 3856 Sticky Fingers RollingStones 9234 Geräusch DieÄrzte WI Systementwicklung Programmierung & Datenbanken II 25 Erweiterte Datenabfrage WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung SELECT [DISTINCT] <Attribut-Name> [AS <neuer Attribut-Name>], ... FROM <Tabellen-Name> [AS <neuer Tabellen-Name>], ... [WHERE <Bedingung>] [GROUP BY <Attribut-Liste> [HAVING <Auswahl-Bedingung>]] [ORDER BY <Attribut-Name> [ASC|DESC]]; • Reihenfolge der Bearbeitung innerhalb der DB: 1 FROM Für dieAbfrageerforderlicheTabelle(n) 2 WHERE Selektionsbedingung(en) 3 GROUP BY GruppierungnachAttribut-Werten 4 HAVING AuswahlvonGruppenausGROUPBY 5 SELECT Projektion (Auswahl)vonAttributenderErgebnistabelle 6 ORDER BY Sortierreihenfolgeder Ergebnismenge WI Systementwicklung Programmierung & Datenbanken II 26 Beispiele – Datenabfragen WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung TAlbum CD_I Album D Interpret 7385 Jazzistanders DieÄrzte 4936 Nevermind Nirvana 3856 Sticky Fingers RollingStones 9234 Geräusch DieÄrzte Result 1 CD_I Album D 1. SELECT * FROM TAlbum; 2. SELECT Album, Interpret FROM TAlbum; 3. SELECT DISTINCT Interpret FROM TAlbum; Result 3 Result 2 Interpret Interpret 7385 Jazzistanders DieÄrzte Jazzistanders DieÄrzte DieÄrzte 4936 Nevermind Nirvana Nevermind Nirvana Nirvana 3856 Sticky Fingers RollingStones Sticky Fingers RollingStones RollingStones 9234 Geräusch DieÄrzte Geräusch DieÄrzte WI Systementwicklung Interpret Album Programmierung & Datenbanken II 27 Beispiele – Datenabfragen WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung TTitel CD_I Track Titel D 7385 1 Himmelblau 4. SELECT Titel FROM TTitel WHERE CD_ID = 4936; 5. SELECT TAlbum.Interpret AS Band, TTitel.Titel AS Song FROM TTitel, TAlbum WHERE TAlbum.CD_ID = TTitel.CD_ID AND TAlbum.Album = `Nevermind`; 7385 2 LiedvomScheitern 7385 3 Breit 4936 1 Smells Like TeenSpirit 4936 2 InBloom 4936 3 Come AsYou Are 3856 1 BrownSugar 3856 2 Sway Result 4 Result 5 3856 3 WildHorses Titel Band Nirvan a Nirvan a Nirvan a TAlbum CD_I Album D 7385 Jazzistanders 4936 Nevermind 3856 Sticky Fingers 9234 Geräusch WI Systementwicklung Smells Like TeenSpirit Interpret InBloom DieÄrzte Nirvana RollingStones DieÄrzte Come AsYou Are Programmierung & Datenbanken II Song Smells Like TeenSpirit InBloom Come AsYou Are 28 Verbund – Join WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung 6. SELECT 1 2 3 FROM WHERE AND TAlbum.Album AS CD, TAlbum.Interpret AS Band, TInterpret.Band_Gründung AS seit TAlbum, TInterpret TAlbum.Interpret = TInterpret.Interpret seit <= 1985; 4 CD CD_I D 7385 4936 3856 9234 7385 4936 3856 9234 7385 4936 3856 9234 Band seit Album TAlbum.Interpret TInterpret.Interpret Band_Gründung Jazzistanders Nevermind Sticky Fingers Geräusch Jazzistanders Nevermind Sticky Fingers Geräusch Jazzistanders Nevermind Sticky Fingers Geräusch DieÄrzte Nirvana RollingStones DieÄrzte DieÄrzte Nirvana RollingStones DieÄrzte DieÄrzte Nirvana RollingStones DieÄrzte WI Systementwicklung DieÄrzte DieÄrzte DieÄrzte DieÄrzte Nirvana Nirvana Nirvana Nirvana RollingStones RollingStones RollingStones RollingStones Programmierung & Datenbanken II 1982 1982 1982 1982 1987 1987 1987 1987 1961 1961 1961 1961 29 Agenda WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung • Motivation • Relationale Sprachen – Entstehung • SQL – Datendefinition (DDL) – DB-Strukturen definieren, ändern, löschen • SQL – Datenabfrage (DML) – Datensätze abfragen • SQL – Datenmanipulation (DML) – Datensätze einfügen, ändern, löschen WI Systementwicklung Programmierung & Datenbanken II 30 SQL – Einfügen von Datensätzen WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung • Pro Anweisung immer nur Datensätze EINER Relation manipulieren • Ohne Angabe von Attributen gelten alle Attribute als angegeben • Nicht angegebene Attribute erhalten <Default> oder NULL-Werte • Reihenfolge der VALUES entspricht Reihenfolge der TAlbum Attribute INSERT INTO VALUES INSERT INTO VALUES <Tabellen-Name> [(<Attribut-Name>,...)] (<Wert>,...); TAlbum (1234, `Stadtaffe`, `Peter Fox`); WI Systementwicklung CD_I D 7385 4936 3856 9234 1234 Programmierung & Datenbanken II Album Interpret Jazzistanders Nevermind Sticky Fingers Geräusch Stadtaffe DieÄrzte Nirvana RollingStones DieÄrzte PeterFox 31 SQL – Ändern von Datensätzen WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung UPDATE SET [WHERE <Tabellen-Name> <Attribut-Name 1> = <Wert-Ausdruck 1> [,..., <Attribut-Name n> = <Wert-Ausdruck n>] <Bedingung>]; • Begrenzt auf eine Relation • Mehre Tupel gleichzeitig änderbar UPDATE SET WHERE TInterpret ... ... ... ... Interpret = `Baste Band dar Walt` Interpret = `Die Ärzte`; Interpret Band_Gründung DieÄrzte 1982 ... BasteBanddarWalt DieÄrzte 1982 Nirvana 1987 Nirvana 1987 RollingStones 1961 RollingStones 1961 WI Systementwicklung Interpret ... Programmierung & Datenbanken II Band_Gründung ... ... 32 SQL – Löschen von Datensätzen WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung CD_I D Album Interpret 7385 Jazzistanders DieÄrzte WI Systementwicklung 4936 Nevermind Nirvana 3856 Sticky Fingers RollingStones 9234 Geräusch DieÄrzte Programmierung & Datenbanken II 33 Zusammenfassung WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung • Structured Query Language – Deklarative Sprache zur Arbeit mit relationalen Datenbanksystemen – Gliederung in 4 Bereiche: • Data Definition Language (DDL) à CREATE, ALTER, DROP • Data Manipulation Language (DML) à SELECT, INSERT INTO, UPDATE, DELETE FROM • Data Control Language (DCL) à GRANT, REVOKE ->Festlegung von Zugriffsrechten • Transaction Control Language (TCL) à COMMIT, ROLLBACK WI Systementwicklung Programmierung & Datenbanken II 34 Literatur WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung • [ElNa02] Elmasri, Navathe: Grundlagen von Datenbanksystemen, Person Studium, Kap.8 • [Meie07] Meier: Relationale und postrelationale Datenbanken, Springer, Kap. 3 • [Prei07] Preiß: Entwurf und Verarbeitung relationaler Datenbanken, Oldenbourg, Kap. 4 WI Systementwicklung Programmierung & Datenbanken II 35 Hausaufgabe WISE Fakultät Wirtschaftswissenschaften, Lehrstuhl für Wirtschaftsinformatik, insbes. Systementwicklung Wiederholung: • Grundbegriffe der Mengenalgebra => z.B: Hirscher (2002 )Grundlegende Begriffe der Mathematik: Entstehung und Entwicklung : Struktur - Funktion Zahl WI Systementwicklung Programmierung & Datenbanken II 36
