Übung 2 - CS Programmierung
Werbung
Übung 2 Interaktive Abfragen auf eine Firebird-Datenbank unter Verwendung der IBOConsole WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 1 Übersicht zur Übung Schritte zum Entwurf einer Datenbank Beispiele für DBMS (Kommerziell und Open Source) SQL-Anweisungen in der Übersicht SFW-Block (SELECT – FROM – WHERE) Umgang mit der IBO-Console Aufgabenstellungen des Labors Weiterführender Beipsiele für SQL-Anweisungen WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 2 Übersicht zum Entwurf einer DB WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 3 Phasenmodell des DB-Entwurfs Anwendungsdaten sollen aus den Anforderungsanalyse in der DB gespeicherten Informationen abgleitet werden Konzeptueller Entwurf können. Verteilungsentwurf Es sind nur sinnvolle bzw. vernünftige Anwendungsdaten zu Logischer Entwurf speichern, daher ist der Informationsbedarf einer Datendefinition Physischer Entwurf Anwendung zu ermitteln. Anwendungsdaten sind möglichst redundanzfrei zu speichern. Quelle: Heuer, A. et al: Datenbanken kompakt, mitp-Verlag, Bonn 2003 WS06/07 – Labor C/S-Programmierung Implementierung & Wartung (Vermeidung von Anomalien) Prof. Dr. Andreas Schmietendorf 4 Entity-Relationship-Modell Entity-Relationship-Modell (kurz ER-Modell) Grundlage ist eine Arbeit von P.P. Chen aus dem Jahr 1976 Standardmodell für frühe Phasen der Datenbankentwicklung Verständlich für Fach- und DV-Abteilungen Basiert auf folgenden Grundkonzepten: - Entity als zu modellierende Informationseinheit - Relationship zur Modellierung von Beziehungen zwischen Entities - Attribut als Eigenschaft von einem Entity oder Relationship Grafische Notation zur ER-Modellierung WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 5 Beispiel eines ER-Modells Bestell_ID Datum Bestellung Anzahl Produkt umfaßt Preis Bezeichnung löst aus Versand Produkt_ID Telefon Name Kunde Name Adresse Quelle: Heuer, A. et al: Datenbanken kompakt, mitp-Verlag, Bonn 2003 Kunden_ID WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 6 Normalisierung Ziel der Normalisierung ist es, Attribute so zu Relationen zuzuordnen, dass innerhalb der Relation keine Redundanzen auftreten. Redundanz ist dann vorhanden, wenn Teile ohne Informationsverlust weggelassen werden können. Unnötige Redundanz impliziert Nachteile hinsichtlich der Ressourcenauslastung und so genannten Veränderungsanomalien (Update-, Insert-, Delete-Anomalien). WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 7 Normalisierung Merkmale des Normalisierungsprozesses: - Primärschlüsselkonzept - Erkennen von Abhängigkeiten - Schrittweise Vorgehensweise Normalisierungsformen - Funktionale Abhängigkeiten (1 NF und 2 NF) - Transitive Abhängigkeiten (3 NF und BCNF) - Mehrwertige Abhängigkeiten (4 NF) - Verbundabhängigkeiten (5 NF) WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 8 Beispiele für Datenbank-ManagementSysteme WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 9 Beispiele konkreter DBMS I Gemeinsamkeiten aktuell angebotener DBMS: Drei-Ebenen Architektur nach ANSI SPARC SQL als Datenbankabfragesprache Einbettung von SQL in Programmiersprachen Diverse Tools für: - Entwurf von Datenbanken - Definition, Anfrage und Darstellung von Daten Kontrollierter Mehrbenutzerbetrieb WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 10 Beispiele konkreter DBMS II Kommerzielle Produkte Oracle Database IBM DB2 Universal Database MS SQL Server Informix (zumeist in Altsystemen eingesetzt) IBM IMS DB (ca. 60% aller unternehmenskritischen Daten) WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 11 Oracle (hier der Enterprise Manager) WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 12 SQL Server (hier der Enterprise Manager) WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 13 Beispiele konkreter DBMS III Open Source Produkte (erhältliche Systeme im Quelltext) Im Rahmen der LINUX-Distribution - My SQL mit eingeschränkten Funktionsumfang (www.mysql.com) - PostgreSQL mit objektrelationalen Features (www.postgresql.org) Weiterentwicklung von InterBase 6.0 (Borland) - Firebird (www.firebirdsql.org) - Verfügbar für Linux und Windows WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 14 Firebird-DBMS (hier IBOConsole) WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 15 SQL-Anweisungen in der Übersicht WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 16 Datenbanksprachen Storage Structure Language (SSL) - Dateiorganisation (Systemadministrator) Data Definition Language (DDL) - Erzeugen des DB-Schemas (Datenbankadminstrator – DBA) View Definition Lanaguage (VDL) - Sichten erzeugen (Anwendungsadministrator) Interactive Query Language (IQL) Data Manipulation Language (DML) - Daten im Dialog abfragen und ggf. verändern (erfahrene Endanwender) Data Base Programming Language (DBPL) - Anwendungen erstellen (Programmierer) Schnittstellen der Anwendungen (Menüs, Masken, usw.) - Daten abfragen und editieren (Endanwender ohne DB-Kenntnisse) WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 17 Unterstützte Datentypen Integer (auch integer4, int) smallint (auch integer2) float (p) auch kurz float decimal (p,q) und numeric (p,q) mit jeweils q als Nachkommastellen character (n) oder char varying bzw. varchar (n) date, time für Datums und Zeitangaben WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 18 SQL Anweisungen – Übersicht 1 CREATE (DROP) SCHEMA – Definition (Entfernen) eines DB-Schemas CREATE (DROP) DOMAIN - Definition (Entfernen) eines Datentyps CRATE (DROP) TABLE - Definition (Entfernen) einer Basistabelle CREATE (DROP) VIEW - Definition (Entfernen) einer View ALTER TABLE – Umstrukturieren einer Basistabelle GRANT, REVOKE – Vergabe und Entzug von Zugriffsrechten WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 19 SQL Anweisungen – Übersicht 2 SELECT FROM WHERE– Datenbankabfrage INSERT INTO – Einfügen von Zeilen DELETE FROM – Löschen von Zeilen TRUNCATE TABLE – Löschen aller Datensätze UPDATE – Aktualisieren von Zeilen WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 20 SQL Anweisungen – Übersicht 3 Transaktionssteuerung - COMMIT - ROLLBACK - SAVEPOINT Data Control Language (DCL) - GRANT (Rechtevergabe auf Tabellen oder Sichten) - REVOKE (Rücknahme von Rechten auf Tabellen oder Sichten) WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 21 Vergleichsoperatoren Operator Bedeutung = Equal to > Greater than >= Greater than or equal to < Less than <= Less than or equal to <> Not equal to WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 22 SELECT … FROM … WHERE der SFW-Block WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 23 SELECT (SFW Block) SELECT Klausel - Gibt die Projektionsliste an, die das Ergebnisschema bestimmt - Integriert auch arithmetische Operationen und Aggregatfunktionen FROM Klausel - Spezifiziert zu verwendende Relationen (Basisrelationen oder Sichten) - Führt ggf. Umbenennungen durch (Tupelvariablen oder „alias“-namen) - Verwendete Relationen werden mittels kartesischen Produkts verknüpft WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 24 SELECT (SFW Block) WHERE Klausel - Einschränkung der von der Datenbank zurückgegebenen Zeilen (Tupel) - Spezifiziert Selektionsbedingungen der Relationenalgebra - Verbundbedingungen um aus dem kartesischen Produkt z.B. ein Gleichverbund (auch Equi-Join) zu berechnen. - Geschachtelte Anfragen innerhalb der WHERE Klausel ! Bei Zeichenketten ist auf Groß- und Kleinschreibung zu achten WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 25 SQL-Anfrage auf einzelne Tabelle Tabelle: Kunde KNr Name Vorname PLZ Ort Strasse Telefon 101 Meyer Joachim 13509 Berlin Wittestr. 30 030 43577 385 102 Schmidt Reiner 12487 Berlin Probsthof 9 030 634 5685 100 Wipprecht Michael 38855 Wernigerode Schillerstr. 7 03943 62 75 11 104 Dimitrov Evgeni 38855 Wernigerode Lindenstr. 8 03943 62 33 11 105 … … … … … … select * from Kunde where Ort = ‚Berlin‘ Zeige alle Kunden die In Berlin wohnen. KNr Name Vorname PLZ Ort Strasse Telefon 101 Meyer Joachim 13509 Berlin Wittestr. 30 030 43577 385 102 Schmidt Reiner 12487 Berlin Probsthof 9 030 634 5685 WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 26 SQL-Anfrage auf einzelne Tabelle Tabelle: Kunde KNr Name Vorname PLZ Ort Strasse Telefon 101 Meyer Joachim 13509 Berlin Wittestr. 30 030 43577 385 102 Schmidt Reiner 12487 Berlin Probsthof 9 030 634 5685 100 Wipprecht Michael 38855 Wernigerode Schillerstr. 7 03943 62 75 11 104 Dimitrov Evgeni 38855 Wernigerode Lindenstr. 8 03943 62 33 11 105 … … … … … … select Name, Vorname, Telefon from Kunde where PLZ = 38855 WS06/07 – Labor C/S-Programmierung Zeige Name, Vorname und Telefon aller Kunden welche die PLZ 38855 haben Name Vorname Telefon Meyer Joachim 030 43577 385 Schmidt Reiner 030 634 5685 Prof. Dr. Andreas Schmietendorf 27 SQL-Anfrage auf einzelne Tabelle Tabelle: Kunde KNr Name Vorname PLZ Ort Strasse Telefon 101 Meyer Joachim 13509 Berlin Wittestr. 30 030 43577 385 102 Schmidt Reiner 12487 Berlin Probsthof 9 030 634 5685 100 Wipprecht Michael 38855 Wernigerode Schillerstr. 7 03943 62 75 11 104 Dimitrov Evgeni 38855 Wernigerode Lindenstr. 8 03943 62 33 11 105 … … … … … … select * from Kunde where Name = ‚Schmidt‘ Zeichenketten und Datumswerte Werden in Anführungszeichen gesetzt. KNr Name Vorname PLZ Ort Strasse Telefon 102 Schmidt Reiner 12487 Berlin Probsthof 9 030 634 5685 WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 28 Umgang mit der IBOConsole WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 29 Umgang mit der IBOConsole Zugriff auf Datenbanken - Interbase (Borland) - Firebird (Open Source) Funktionsumfang - Datenbanken erzeugen - Datenbanken registrieren - Ausführen von SQL-Anweisungen • Interaktiv SQL • Gespeicherte SQL-Skripte - DB-Administration • Nutzerverwaltung • Backup & Recovery WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 30 Anmeldeprozedur Benutzername - SYSDBA Kennwort - masterkey Datenbank verwenden - DB-Registrieren - DB-Anmeldung WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 31 Datenbank registrieren WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 32 Anmelden an der Datenbank WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 33 Neue Datenbank anlegen WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 34 Aufgabenstellungen im Labor WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 35 Verwendung des Eigenschaften-Dialogs Verwenden Sie „Properties“ (Tabellennamen markieren – rechte Maustaste – Properties oder Doppelklick auf den Tabellennamen) um sich über die Eigenschaften der folgenden Tabellen zu informieren. Speichern Sie die Metadaten jeweils im Protokoll. - COUNTRY - CUSTOMER - DEPARTMENT - EMPLOYEE - JOB - PROJECT - PROJ_DEPT_BUDGET WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 36 Anfragen in SQL - SELECT 1. Wählen Sie alle Dateneinträge der vorhergehend mit Hilfe des Properties-Dialoges analysierten Tabellen aus. 2. Wählen Sie aus der Tabelle DEPARTMENT die Attribute department, location und phone_no aus. 3. Wählen Sie aus der Tabelle DEPARTMENT die Attribute dept_no, department und budget aus, für den Fall das das budget größer als 600000 ist. WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 37 Anfragen in SQL - SELECT 4. Wählen Sie aus der Tabelle DEPARTMENT das Attribut location aus, verhindern Sie dabei die Ausgabe doppelter Tupel mittels der distinct-Anweisung. 5. Wählen Sie aus der Tabelle EMPLOYEE die Attribute emp_no, first_name, last_name und job_code aus, für den Fall das die dept_no gleich 623 ist. WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 38 Daten einfügen - INSERT WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 39 Daten einfügen - INSERT 6. Fügen Sie in die Tabelle COUNTRY weitere 3 Länder und die entsprechenden Währungseinheit ein. 7. Prüfen Sie nach Eingabe eines weiteren Landes den Inhalt der Tabelle COUNTRY mittels select-Anweisung. 8. Geben Sie jeweils 5 neue Mitarbeiter in die Tabelle EMPLOYEE ein, verwenden Sie die Vorlage innerhalb dieser Versuchsanleitung! Belegung aller not null Attribute Datumsangabe entsprechend des folgenden Formats – ‘11.12.2005‘ Berücksichtigung von Abhängigkeiten zu anderen Tabellen! WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 40 Verbundoperationen (Join) 9. Ermitteln Sie das Kreuzprodukt für die Relationen DEPARTMENT und EMPLOYEE. Wie viele Tupel (Zeilen) und Attribute (Spalten) enthält ihre Ergebnisrelation? 10. Ermitteln Sie einen natürlichen Verbund der Relationen DEPARTMENT und EMPLOYEE unter Verwendung des Attributs dept_no. Wie viele Tupel (Zeilen) und Attribute (Spalten) enthält ihre Ergebnisrelation jetzt? WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 41 Weiterführende Beispiele WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 42 Duplikatsunterdrückung mittels distinct WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 43 Beispiel: UPDATE-Anweisung - vorher - WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 44 Beispiel: UPDATE-Anweisung - nachher - WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 45 Beispiel: DELETE-Anweisung WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 46 Beispiel: INSERT-Anweisung WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 47 Beispiel: Kreuzprodukt WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 48 Beispiel: Natürlicher Verbund WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 49 Organisation WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 50 Organisation des Labors Bearbeitung der Themenstellung – Gruppen zu je 4 Stundenten Abgabe eines Protokolls (Bitte eine komplette Word-Datei!!) - Enthält die Namen aller beteiligten Studenten - Enthält alle erstellen SQL-Skripte - Enthält alle Ergebnisrelationen Das Protokoll (Dateiname: „CS_ue2_gruppe_x“) liefern Sie per attachment an [email protected]. In das "Betreff" (Subject)-Feld schreiben Sie bitte: „ CS_ue2_gruppe_x " WS06/07 – Labor C/S-Programmierung Prof. Dr. Andreas Schmietendorf 51
