Lösung Serie 4
Werbung
VORLESUNG EI U NIVERSIT ÄT B ERN I NFORMATIK T YP UL AUSGABE HS 08 B LATT 4 (1/4) Lösungsvorschlag Serie 4 – Datenbanken 1. Dateien Als Datei (engl. File) bezeichnet man eine nach bestimmten Gesichtspunkten zusammengestellte Menge von Daten. Eine Datei besteht aus einer Folge von Datensätzen (engl. Records). Jeder Datensatz wiederum besteht aus Feldern (engl. Fields). I.d.R. wird man Dateien auf Dauerspeichern wie Harddisks oder Bändern ablegen. Bei direkten Dateien (engl. random access files) kann direkt auf ein Record an einer bestimmten physischen Adresse innerhalb des Files (engl. seek position) zugegriffen werden. Diese Adresse lässt sich mittels eines Algorithmus aus dem gegebenen Primärschlüssel des Records bestimmen. In einer Datei sind alle Records hintereinander angeordnet. Damit die Bestimmung der physischen Adresse des i-ten Records einfacher wird, können alle Records genau gleich aufgebaut sein. Dadurch werden alle Records physisch gleich lang. Um zu einem bestimmten Datensatz an der i-ten Position zu gelangen, muss der Record innerhalb des Files ab der physischen Adresse (i − 1) · RECORD LEN GT H eingelesen werden. Datei WOHNUNGEN Wie in Abbildung 1 ersichtlich ist, sind in der Datei WOHNUNGEN alle Records bestehend aus den Feldern (Wohnungs-ID, PLZ, Ort, Strasse, Nummer, Anzahl Zimmer) hintereinander abgelegt. Eine bestimmte Sortier-Reihenfolge ist nicht zwingend, da immer indirekt über Indizes zugegriffen werden kann. physische Adresse [Bytes] 0 83 Datei 1234 2768 3012 3303 Bern Jegenstorf Erpelweg Hauptstrasse 3 45 4 5 8a 2.5 .. . 173'841 1788 3005 Bern Sennweg Abbildung 1: Datei WOHNUNGEN (Aufgabe 1) Indizes auf die Datei WOHNUNGEN Würde man nur auf dem File basieren und z.B. alle Wohnungen in Bern suchen wollen, so müsste man sequentiell jeden Record des Files auslesen und auf den Ort hin überprüfen (mit Zeitkomplexität T(n)=O(n)). Oder würde man z.B. nach einer bestimmten Anzahl Zimmer suchen, so müsste man alle Records der Datei auf diesen Wert hin überprüfen. Das gleiche gilt analog auch für alle anderen Felder der Datei. Um effizient auf alle Records mit einem bestimmten Feld-Wert zugreifen zu können, kann vorgängig pro Feld je ein Index erstellt werden. Da der Index nach dem Feldinhalt sortiert ist, lassen sich Einträge mit einem bestimmten Feldwert sehr effizient im Index finden (z.B. mittels Binary Search, T(n)=O(log n)). Mit den Index-Einträgen hat man dann zugleich auch die Adressen der zugehörigen Records. Der Zugriff auf einen oder mehrere Records mit einem bestimmten Feldinhalt wird also immer indirekt via den Index ablaufen. VORLESUNG EI U NIVERSIT ÄT B ERN I NFORMATIK T YP UL B LATT 4 (2/4) AUSGABE HS 08 Abbildung 2 zeigt 3 der 6 nötigen Indizes, um effizient über jedes der 6 Felder auf die Records zugreifen zu können. Index für Wohnungs_ID Bsp: „Der Record mit der Wohnungs-ID 2768 befindet sich an der 2. Stelle in der Datei.“ 2120 .. . 2768 2 2.5 .. 2120 . 2 Jegenstorf 4 1 . .. 1633 Zürich 2120 .. . . .. 1 .. . 1 Bern Bern . .. 9785 . .. . .. . .. . .. 1788 Index für Anzahl Zimmer Index für Ort 7 .. . 8 .. . Abbildung 2: Indizes auf die Datei WOHNUNGEN (Aufgabe 1) 2. (a) In einer relationalen Datenbank entspricht jede Zeile in einer Tabelle (Tupel) einem Datensatz (Record ). (b) Einem Attribut (Spalte einer Tabelle) sagt man auch Feld (Field ). (c) Ein Attribut ist ein Primärschlüssel, wenn es einen Datensatz in einer Tabelle eindeutig identifiziert. (d) Ein Attribut ist ein Fremdschlüssel, wenn es auf ein primäres Attribut (Primärschlüssel) einer anderen Tabelle verweist. (e) Mit SQL kann man in einem Attribut des Datentyps varchar(20) Zeichenketten (Strings) mit maximal 20 Zeichen speichern. Im Gegensatz zu char(20), welches unabhängig von der Länge der Zeichenkette immer 20 Zeichen speichert, wird in einem Attribut des Typs varchar(20) nur soviel Speicherplatz wie nötig verwendet. Die gewonnene Speichereffizienz erkauft man sich aber mit einem höheren Verarbeitungsaufwand (sog. Trade-Off“ zwischen Speicherplatz ” und Laufzeit). (f) Wird das Keywort DISTINCT verwendet werden Datensätze nur einmal ausgegeben, auch wenn sie mehrfach im Resultat vorkommen. 3. Variante 1 der Bücherausleih-DB nutzt viele Vorteile von (relationalen) Datenbanken gegenüber einfachen Dateisystemen nicht: Stammdaten: In Variante 1 ist es nicht möglich, eine Liste aller Bücher (Inventar) oder eine Liste aller Ausleiher zu führen. Solche Daten, die eine lange Gültigkeit haben und immer wieder verwendet werden, bezeichnet man als Stammdaten. In Variante 2 ist es sehr einfach, alle Bücher zu verwalten (Bücher-Stammdaten) und davon unabhängig alle Ausleiher zu verwalten (Ausleiher-Stammdaten). Redundanzverminderung: In Variante 1 müssen für jede Ausleihe Name und Vorname des Ausleihers gespeichert werden, auch wenn letzterer schon andere Bücher ausgeliehen hat. Bei Variante 2 hingegen reicht es, Name und Vorname VORLESUNG EI U NIVERSIT ÄT B ERN I NFORMATIK T YP UL B LATT 4 (3/4) AUSGABE HS 08 des Ausleihers nur einmal zu speichern. Die Ausleihe ist dann nur eine Relation zwischen dem Ausleiher und dem Buch. Datenkonsistenz: Bei Variante 1 sind Dateninkonsistenzen möglich, die bei Variante 2 von vornherein ausscheiden. So kann z. B. für zwei Ausleihen derselben Person der Name einmal falsch geschrieben werden. Flexibilität: Müssen z. B. die Daten eines Ausleihers angepasst werden (Markus Müller heisst neu: Markus Müller-Kunz), so reicht es bei Variante 2, den Namen in der Tabelle AUSLEIHER einmal anzupassen. Bei Variante 1 muss der Name für jede Ausleihe von Markus Müller angepasst werden. Als einzigen Vorteil bietet die Tabelle in Variante 1 eine für Betrachter übersichtlichere Darstellung aller Ausleihen, da alle Daten je auf einer Zeile zusammengefasst stehen. Relationale DBMS bieten allerdings ein Hilfsmittel an, mit welchem die auf verschiedene Tabellen verteilten Daten ebenfalls in dieser zusammengezogenen Form betrachtet werden können. Mithilfe der sogenannten Ansichten (engl. views) werden die jeweils zusammengehörenden Datensätze in einer einzigen virtuellen Tabelle verbunden dargestellt, obwohl die Daten in mehreren physischen Tabellen gespeichert vorliegen. 4. Es sollen sicher die Daten der vorhandenen Fotos und der Kunden gespeichert werden können. Weiter will man die Beziehung Bestellungen erfassen. Weitere Daten könnten z.B. die Fotographen oder die Fakturierung betreffen. Ein erster (einfacher) Entwurf könnte deshalb wie folgt aussehen: FOTO: Foto_ID, Speicherort, Beschreibung, Aufnahmedatum, Fotograph (Person_ID) KUNDE: Kunden_ID, Firmenname, Adresse, PLZ, Ort, Kontaktperson (Person_ID) BESTELLUNG: Bestell_ID, Kunden_ID, Foto_ID, Bestelldatum, Bestellstatus PERSONEN: Person_ID, Name, Vorname, Strasse, PLZ, Adresse, Funktion RECHNUNG: Rechnungs_ID, Bestell_ID, Preis, Fälligkeit, Rabatt 5. (a) Query: SELECT * FROM Veranstaltungen Resultat: Veranstaltung P2 LA1 CG P1 EI RA Titel Programmierung 2 Lineare Algebra 1 Computergrafik Programmierung 1 Einführung in die Informatik Rechnerarchitektur ECTS 3 6 3 3 3 3 Institut IAM MI IAM IAM IAM IAM (b) Query: SELECT Name, Vorname, Hauptfach FROM STUDIERENDE Resultat: Name Zumbühl Egger Wyss Lanz Vorname Luca Annette Stefan Sonja Hauptfach Inf Math Inf Inf VORLESUNG EI U NIVERSIT ÄT B ERN I NFORMATIK T YP UL B LATT 4 (4/4) AUSGABE HS 08 (c) Query: SELECT Titel FROM VERANSTALTUNGEN WHERE ECTS>3 Resultat: Titel Lineare Algebra 1 (d) Query: SELECT Name, Vorname FROM STUDIERENDE WHERE Matrikelnummer IN (SELECT Matrikelnummer FROM NOTEN WHERE Note>=4) Resultat: Name Vorname Egger Annette oder Query: SELECT Name, Vorname FROM STUDIERENDE, NOTEN WHERE STUDIERENDE.Matrikelnummer=NOTEN.Matrikelnummer AND Note>=4 Resultat: Name Vorname Egger Annette Egger Annette (e) Query: SELECT Veranstaltung FROM DURCHFÜHRUNGEN WHERE Jahr=2001 AND DozentIn=’Bieri’ Resultat: Veranstaltung EI CG (f) Query: SELECT Vorname, Name, Titel, Note FROM STUDIERENDE, VERANSTALTUNGEN, NOTEN, DURCHFÜHRUNGEN WHERE STUDIERENDE.Matrikelnummer=NOTEN.Matrikelnummer AND NOTEN.Nummer=DURCHFÜHRUNGEN.Nummer AND DURCHFÜHRUNGEN.Veranstaltung=VERANSTALTUNGEN.Veranstaltung Resultat: Vorname Annette Annette Sonja Name Egger Egger Lanz Titel Programmierung 1 Einführung in die Informatik Einführung in die Informatik Note 4.5 4 3
