Daten verwalten mit einer relationalen Datenbank
Werbung
Verwaltung und Analyse digitaler Daten in der Wissenschaft Daten verwalten mit einer relationalen Datenbank Projektaufgabe Inhaltsverzeichnis 1 Einführung 3 2 Vorbereitendes 2.1 Einlesen der relationalen Nährwertdatenbank . . . . . . . . . . . . . . . . 3 3 3 Aufgaben 3.1 Aufbau der relationalen Datenbank . . . . . . . . 3.2 Abfragen . . . . . . . . . . . . . . . . . . . . . . . 3.2.1 Liste aller Nahrungsmittel . . . . . . . . . 3.2.2 Ein Nahrungsmittel finden . . . . . . . . . 3.2.3 SQL-Abfrage erstellen . . . . . . . . . . . 3.2.4 Lebensmittel für Wettkampfsportler finden 3.3 Änderung und Erweiterung der Datenbank . . . . 3.3.1 Änderung in der Datenbank . . . . . . . . 3.3.2 Erweiterung der Datenbank . . . . . . . . 3.3.3 Testen der Erweiterung . . . . . . . . . . . 4 4 4 4 5 5 5 5 6 6 6 4 Bedingungen für die Präsentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Begriffe Datenliste Beziehung Referentielle Integrität Datenbank Beziehungstyp Redundanz Relation Primärschlüssel Relationale Operatoren Attribut Fremdschlüssel Logische Operatoren Tupel Normalisierung SQL Autoren: Markus Dahinden, Lukas Fässler, Hans Hinterberger, David Sichau E-Mail: [email protected] Datum: 09 May, 2017 Version: 2.0 Hash: ac523b2 Trotz sorgfältiger Arbeit schleichen sich manchmal Fehler ein. Die Autoren sind Ihnen für Anregungen und Hinweise dankbar! Dieses Material steht unter der Creative-Commons-Lizenz Namensnennung - Nicht kommerziell - Keine Bearbeitungen 4.0 International. Um eine Kopie dieser Lizenz zu sehen, besuchen Sie http://creativecommons.org/licenses/by-nc-nd/4.0/deed.de 2 1 Einführung Sie haben im E.Tutorial® anhand einer einfachen Datenbank einige wichtige Grundlagen kennen gelernt, die beim Umgang mit Datenbanken von Bedeutung sind: • • • • Aufbau einer relationalen Datenbank Abfragen an eine Datenbank erstellen Grundelemente der Abfragesprache SQL Erweitern einer bestehenden Datenbankstruktur Die Projektaufgabe gibt Ihnen die Möglichkeit, diese Fähigkeiten zu festigen und flexibel anzuwenden. Als Datenquelle dient die im Kurs „Daten verwalten mit Listen und Tabellenëingeführte Nährwerttabelle, welche für diese Aufgabe in eine relationale Datenbank überführt wurde. Informationen über die Schweizerische Nährwertdatenbank finden Sie unter http://www.naehrwertdaten.ch. In der nachfolgenden Abbildung sehen Sie die Relationen und Beziehungen dieser relationalen Nährwertdatenbank: Die meisten Attribute wurden ohne Änderungen aus der Nährwerttabelle übernommen. Die Aufteilung der Nährwerttabelle in eine relationale Datenbank machte es jedoch notwendig, dass einige zusätzliche Attribute eingefügt werden mussten. Der Primärschlüssel jeder Tabelle, also ein Attributwert, welcher einen Tabelleneintrag (Tupel) eindeutig identifiziert, ist jeweils als id bezeichnet. Er kann in anderen Relationen als Verweis auf diese Tabelleneinträge verwendet werden. Wenn ein Attribut auf ein Attribut einer anderen Tabelle verweist (Fremdschlüssel), wird dies mit einem nachgestellten _id gekennzeichnet. Beispiel: Das Attribut kategorie_id der Relation lebensmittel verweist auf den Primärschlüssel id der Relation kategorien und definiert für jedes Nahrungsmittel eine Nahrungsmittel-Gruppe. 2 Vorbereitendes 2.1 Einlesen der relationalen Nährwertdatenbank • Laden Sie die Datenbank nwdb_ms.mdb (Microsoft Office) oder nwdb_oo.odb (OpenOffice) auf Ihren Rechner. 3 • Öffnen Sie die Datenbank mit dem entsprechenden Programm (Microsoft Access oder OpenOffice Base). 3 Aufgaben 3.1 Aufbau der relationalen Datenbank Diskutieren Sie folgende Fragen zum Aufbau der relationalen Nährwertdatenbank: • Wieso ist das Attribut id der Relation komponenten in der Beziehungs-Darstellung mit einem Schlüssel markiert? • Gibt es Fremdschlüssel in dieser Datenbank. Wenn ja, welche und wozu dienen diese? • Welche Beziehungstypen werden in der Datenbank verwendet? • In der Datenbank gibt es eine Zwischentabelle. Wie heisst sie und wozu dient sie? • Angenommen Sie müssten eine neue kategorie „Hilfsstoffeërstellen. Wieso wird dieser Gruppe der Primärschlüssel = 8 vergeben? 3.2 Abfragen Formulieren Sie folgende Abfragen an die Datenbank. Benutzen Sie den Abfrage-Assistenten und versuchen Sie die so generierte SQL-Abfrage zu verstehen. In Klammern stehen zur Selbstkontrolle die Anzahl Treffer, die Sie bei der Abfrage erreichen sollten. 3.2.1 Liste aller Nahrungsmittel Erstellen Sie eine Liste aller Nahrungsmittel (48). SELECT __________________________________________________ FROM __________________________________________________ 4 3.2.2 Ein Nahrungsmittel finden Finden Sie das Nahrungsmittel mit der id = 1000826. Nur der Lebensmittelname (name) soll angezeigt werden (1). SELECT __________________________________________________ FROM __________________________________________________ WHERE __________________________________________________ 3.2.3 SQL-Abfrage erstellen Erstellen Sie die folgende Abfrage. Wie lautet die Frage? (13). SELECT lebensmittel.name, komponenten.name, komponenten_lebensmittel.menge FROM lebensmittel INNER JOIN (komponenten INNER JOIN komponenten_lebensmittel ON komponenten.id = komponenten_lebensmittel.komponente_id) ON lebensmittel.id = komponenten_lebensmittel.lebensmittel_id WHERE (((komponenten.name)="Zucker (in g)") AND ((komponenten_lebensmittel.menge) > 10)) ORDER BY lebensmittel.name; 3.2.4 Lebensmittel für Wettkampfsportler finden Ein Sportler hat in der Vorbereitung für einen Kugelstosswettkampf erfahren, dass Sie bereits mit der Nährwertdatenbank gearbeitet haben und gelangt deshalb mit folgender Frage an Sie: „Um meine Schnellkraft und die Muskelmasse zu erhöhen, suche ich Nahrungsmittel, die viel Protein (>20g) enthalten. Welche Nahrungsmittel können Sie mir empfehlen? Welche Lebensmittel erfüllen die Vorgaben des Kugelstössers und wie lautet die Abfrage? 3.3 Änderung und Erweiterung der Datenbank 5 3.3.1 Änderung in der Datenbank Bei ersten Testabfragen der Nährwertdatenbank wurde bemängelt, dass der Eintrag „Teilentrahmte Milch, pasteurisiert"nur ungenügend sortiert werden kann. So wird er nicht nach „Milchßondern nach „Teilentrahmtßortiert. • Ändern Sie diesen Nahrungsmittelnamen in „Milch, teilentrahmt, pasteurisiert“. 3.3.2 Erweiterung der Datenbank Als Zusatz für die Online-Version der Nährwertdatenbank sollen Kontaktinformationen in die Datenbank eingefügt werden. So wird es den Anwendern ermöglicht, zu jeder Nahrungsmittelgruppe mit einem oder mehreren Experten oder Expertinnen in Verbindung zu treten. Ihre Aufgabe besteht nun darin, eine zusätzliche Tabelle (experten) zu erstellen, welche folgende Informationen über diese Personen enthält (Sie können bei Bedarf die Tabelle mit weiteren Attributen ergänzen). kategorie_id nachname vorname ausbildung 4 Geisser Angela Ernährungsberaterin 4 Brügger Tamara Kantonschemikerin 1 Müller Philipp Biologe 2 Bühler Toni Käsermeister 3 Betschart Benjamin Metzgermeister 5 Valser Antoinetta Lebensmittelchemikerin 6 Stadler Clemens Immunologe • Erstellen Sie die Tabelle und geben Sie die Daten ein. • Gibt es eine Nahrungsmittelgruppe, der keine Kontaktperson zugeordnet ist? Sie brauchen keine Abfrage zu erstellen. Es reicht, wenn Sie Ihre Antwort anhand der Tabellen aufzeigen. 3.3.3 Testen der Erweiterung • Wie viele Lebensmittel werden von Herrn Stadler betreut? • Wie heisst die zuständige Kontaktperson für das Nahrungsmittel „Spargel“? • Ihre Kollegin interessiert sich für die Auswirkungen von Nitrat auf unser Trinkwasser. Da Nitrat nicht in der Datenbank enthalten ist, möchten Sie gerne direkt mit der für Trinkwasser zuständigen Person Kontakt aufnehmen. Welche Ansprechperson können Sie Ihr gemäss unserer Datenbank empfehlen? 6 • Erstellen Sie eine Liste, welche alle in der Datenbank enthaltenen Wasserarten auflistet (Seewasser, Grundwasser, usw.) Tipp: Für diese „offeneÄbfrage eignet sich ein Wildcard-Zeichen: * (Access) resp. % (OpenOffice) (Das Wildcard-Zeichen steht dabei für kein oder mehrere Zeichen: Beispiel: *dach* liefert die Datensätze z.B. für Dachstock, überdacht etc.). Beachten Sie, dass das WildcardZeichen nur in Kombination mit LIKE verwendet werden kann (anstelle des Gleichheitszeichens =). 4 Bedingungen für die Präsentation Zeigen Sie einer Assistentin oder einem Assistenten Ihre Abfragen und Datenbankerweiterungen am Bildschirm. Überlegen Sie sich, wie Sie einem Laien folgende Fragen erklären würden: • • • • • • • • • Wie werden Daten in einer relationalen Datenbank organisiert? Was ist der Unterschied zwischen Datenlisten und einer Datenbankrelation? Welche Datenbank-Schlüssel gibt es und wozu dienen sie? Was versteht man unter referentieller Integrität? Die vorliegende Datenbank ermöglicht es, dass mehrere Kontaktpersonen für eine Nahrungsmittelgruppe (kategorie) zuständig sein können. Was müssten Sie an der Datenbank ändern, damit es in Zukunft auch möglich ist, dass jede Kontaktperson verschiedenen Kategorien zugeteilt werden kann? Welches sind die Grundelemente einer SQL-Abfrage? Wieso benötigt man in SQL-Abfragen relationale und logische Operatoren? Erkären Sie anhand eines Beispiels. Wozu dient das JOIN...ON? Zeigen Sie anhand eines Beispiels, wie JOIN...ON-Abfragen in WHERE-Abfragen umgewandelt werden können. Die Begriffe dieses Kursmoduls sollten Sie mit einfachen Worten erklären können. 7
