Folienadaption HS, 4/99

1. Folienadaption HS, 4/99

2. Relationen sind Tabellen! Relationales Modell = Tabellarische Repräsentation der Daten + „assoziative“ Anfragesprache Datenbank enthält auch Beschreibung der Daten: Datenbankschema (also Metadaten)

3. Oft andere Notation! Wie kommt man zu einem DB-Schema? • 1. Datenmodellierung • typisch: Entity-Relationship-Modell (datenorientiert) • zunehmend mit den Operationen (Unified Modeling Language, UML) Lies z.B: einem Kunde (customer) werden Lieferungen (shipments) gesandt, keine, eine oder mehrere (1:M) Kardinalitäten sind Invarianten: eine Lieferung kann nie an mehrere Kunden gehen

4. Schlüssel (key) Allgemein: eine identifizierende Folge von Attributen. (Invanrianzeigenschaft!) KDnr (ownerID) Name Nachname kauft Kunden (AntiqueOwners) N Handelstransaktion (Antiques) verkauft bestellen (Orders) M Antiquität Was? Bezeichner (item) (siehe Tutorial S. 6 u. 7) Beispiel zu Entity-Relationship-Modellierung

5. CREATE TABLE Orders (OwnerId INTEGER NOT NULL FOREIGN KEY REFERENCES AntiqueOwners (OWNERID), ItemDesired CHAR(40) NOT NULL); CREATE TABLE AntiqueOwners (OwnerId INTEGER NOT NULL, OwnerLastname CHAR(40) NOT NULL, OwnerFirstName CHAR(30)); CREATE TABLE Antiques (SellerID INTEGER NOT NULL FOREIGN KEY REFERENCES AntiqueOwners (OWNERID), .....); Wie kommt man DB-Schema: Datendefinition 2. Datenbankschema definieren im RDM heißt das: Tabellenstruktur festlegen, Bezeichner, Attribute und deren Typ, ggf. Invarianten

6. Das Datenbänkchen... ... benutzen wir später!

7. Leider hat diese Tabelle des Tutorials herzlich wenig mit der ersten zu tun! Was fehlt da? Eine zweite DB für einfache SQL-Anfragen

8. EBNF-Syntax Metasymbole! Ein SQL-Ausdruck braucht also kein (Selektions-)prädikat So sollte Ergebnis aussehen, (leider) werden Duplikate nicht entfernt Einfache SQL-Anfragen Grundstruktur (eine beteiligte Tabelle): SELECT [<attributBezeicher>] | *FROM <tabellenBezeichner >[WHERE <prädikat>] ; Ergebnis einer Anfrage ist immer eine Tabelle! ... auf der wieder eine Anfrage... -> Algebra auf Relationen als theoretische Basis. Relationenalgebra: eine applikative Sprache SELECT State FROM EmployeeAddressTable;

9. SELECT FirstName, LastName, Address, City, State FROM EmployeeAddressTable; Projektion SELECT LastName, Address, City, State FROM EmployeeAddressTable WHERE State = ´Ohio´; Selektion Zeichenkettenwerte in Hochkommata Beispiele

10. Selektionsprädikate (1) Engl. <condition_list> <prädikat> This is a boolean expression which each row must satisfy. Operators which may be used include AND, OR, NOT, >, >=, =, <, <= The LIKE operator permits strings to be compared using 'wild cards'. The symbols _ and % are used to represent a single character or a sequence of characters. The IN operator allows an item to be tested against a list of values. There is a BETWEEN operator for checking ranges.

11. Kann hier auch OR stehen? Einfache Anfragen (1) Programmierer, die weniger als 40000 (was??) verdienen? SELECT EmployeeIdNo FROM EmployeeStatisticsTable WHERE Salary < 40000 AND Position = ´Programmer´; Angestellte mit Gehalt > 40000 oder Bonus (benefits) von mehr als 10000? SELECT EmployeeIdNo FROM EmployeeStatisticsTable WHERE Salary < 40000 OR Benefits > 10000;

12. SELECT EmployeeIdNo FROM EmployeeStatisticsTable WHERE Position IN (´Manager´, ´Programmer´, ´Staff´, ´Engineer´) ; Vermeidet lästiges OR, was bedeutet: NOT IN ? Einfache Anfragen (2) Manager mit Gehalt > 40000 oder Bonus (benefits) von mehr als 10000? SELECT EmployeeIdNo FROM EmployeeStatisticsTable WHERE POSITION = ´Manager´ AND (SALARY < 40000 OR BENEFITS > 10000; Manager, Programmierer, Stabsmitglieder oder Ingenieure!

13. Natürlich nur, wenn die Attribute gleichen Typ haben! Einfache Anfragen (3) Manager mit Gehalt zwischen 50000000 und 100000000 ? SELECT EmployeeIdNo FROM EmployeeStatisticsTable WHERE Position = ´MANAGER´ AND Salary BETWEEN 50000000 AND 100000000 ; Etwas ´verbose´ ! Manager mit Gehalt < Bonus (benefits) ? SELECT EmployeeIdNo FROM EmployeeStatisticsTable WHERE Position = ´MANAGER´ AND Salary < Benefits ;

14. Einfache Anfragen (4) Angestellte mit Anfangsbuchstabe ´S´ SELECT EmployeeIdNo FROM EmployeeAddressTable WHERE Lastname LIKE ´S%´; In regulären Ausdrücken ist das der *

15. Diese Subqueries sind unabhängig voneinander Einfache Subqueries Personen mit Vornamen, die auch als Nachnamen auftauchen SELECT EmployeeIdNo FROM EmployeeAddressTable WHERE Firstname IN (SELECT Lastname FROM EmployeeAddressTable) ; Beachte Unterschied zu ´Vorname = Nachname´

16. Alias manchmal nötig, immer nützlich SELECT Lastname FROM EmployeeStatisticsTable ES, EmployeeAddressTable EAWHERE Position = ´Manager´ AND Salary > 1000000 AND EA.EmployeeIdNo = ES. EmployeeIdNo; Verbund (genauer: Gleichheitsverbund) Mehrere Tabellen Anfrage: Namen der Manager, die mehr als 1000000 verdienen. Problem: Verbindung zwischen der Information in verschiedenen Tabellen kann nicht hergestellt werden. Annahme:EmployeeAddressTablebesitzt zusätzlich SpalteEmployeeNo

17. Andere Join-Typen Voriges Beispiel: Join-Prädikat Teil der WHERE-Klausel: muss nicht so sein. Unterschied: Gültigkeitsbereich der Bezeichner in WHERE - Klausel SELECT Lastname FROM EmployeeStatisticsTable ES JOIN EmployeeAddressTable EA ON EA.EmployeeIdNo = ES. EmployeeIdNo WHERE Position = ´Manager´ AND Salary > 1000000;

18. Natürlicher Verbund Gleichverbund (Equijoin) auf gleich benannten Attributen verschiedener Tabellen. SELECT Lastname FROM EmployeeStatisticsTable ES NATURAL JOIN EmployeeAddressTable EA WHERE Position = ´Manager´ AND Salary > 1000000;

19. Erstelle Liste aller Namen und Adressen und (ggf.) des Gehalts SELECT EA.LastName, EA.Address, ES.Salary FROM EmployeeStatisticsTable ES RIGHT OUTERJOIN EmployeeAddressTable EA ON EA.EmployeeIdNo = ES.EmployeeIdNo ; ID 010 400 Right Outer Join: auch die Tupel des rechten Relationsarguments ohne Korrespondenzwert im ersten gehören zum Ergebnis! Äußerer Verbund Wo bleibt die Adresse der nicht angestellten Personen?

20. SELECT State FROM EmployeeAddressTable; liefert .... Mehrfach auftretende Werte (Duplikate) unterdrücken durch Schlüsselwort DISTINCT . SELECT DISTINCT State FROM EmployeeAddressTable ORDER BY State; hier sogar sortiert Duplikate eliminieren

21. Aggregierungsfunktionen · SUM () gives the total of all the rows, satisfying any conditions, of the given column, where the given column is numeric. · AVG () gives the average of the given column. · MAX () gives the largest figure in the given column. · MIN () gives the smallest figure in the given column. · COUNT(*) gives the number of rows satisfying the conditions. Bsp:Gehaltssumme und -durchschnitt aller Mitarbeiter! SELECT SUM(Salary), AVG(Salary) FROMEmployeeStatisticsTable ;

22. Zielliste und Prädikate können Arithmetik enthalten Bsp:Angestellte, die das Doppelte des Durchschnitts verdienen Arithmetisch SELECT EmployeeIdNo FROMEmployeeStatisticsTable WHERE Salary > (SELECTAVG(Salary) FROMEmployeeStatisticsTable);

23. Gruppierung Maximaler Verdienst aller Berufsgruppen SELECT Position MAX(Salary) FROM EmployeeStatisticsTable GROUPBY Position; Maximaler Verdienst aller Berufsgruppen mit Durchschnittsgehalt > 50000 SELECT Position MAX(Salary) FROM EmployeeStatisticsTable GROUPBY Position HAVINGAVG(Salary) > 50000;

24. Daten ändern Adding Data To insert rows into a table, do the following: INSERT INTO Antiques VALUES (21, 01, 'Ottoman', 200.00); This inserts the data into the table, as a new row, column-by-column, in the pre-defined order. Instead, let's change the order and leave Price blank: INSERT INTO Antiques (BuyerId, SellerId, Item) VALUES (01, 21, 'Ottoman');

25. Beliebige Prädikate möglich Ändern und Löschen Ändere Preis für alle Positionen ´chair´ auf 500. UPDATE Antiques SET Price = 500.00 WHERE Item = 'Chair'; Lösche alle Ottomanen DELETE FROM Antiques WHERE Item = 'Ottoman';

26. SELECTCOUNT(Lastname) FROM MoneyMakers GROUPBYCity, State; Anfrage auf VIEW Nochmal: Datendefinition Mit Hilfe von Anfragen können Tabellen definiert werden, die künftig(fast) wie Basistabellen behandelt werden. Diese Tabellen heißen VIEW . Sie werden normalerweise nicht materialisiert. CREATE VIEW MoneyMakers ASSELECT Lastname, City, State FROM EmployeeStatisticsTable ES, EmployeeAddressTable EAWHERE Salary > 1000000 AND EA.EmployeeIdNo = ES.EmployeeIdNo;

27. Nullwert != 0 NULL : undefiniert Schemaänderungen erleichtern Rapid Prototyping Hinzufügen des Attributs (der Spalte) EmployeeIdNo zur EmployeeAddressTable ALTER TABLE EmployeeAddressTable ADD (EmployeeIdNo INTEGERNULL); Nicht behandelt: Mengenoperationen, physikalisches Schema, Einbettung in Pogrammiersprachen.