Sortieren Sie alle Bücher/Medien nach den
Werbung
SQL – TABELLEN N ORMALISIEREN UND MIT JOIN VERKNÜPFEN Tim Rosenbecker und Tobias Spangenberg Berufliche Schule des Wetteraukreises in Butzbach Emil-Vogt-Straße 8 35510 Butzbach Butzbach, den 06.04.2017 SQL – TABELLEN NORMALISIEREN UND MIT JOIN VERKNÜPFEN Inhalt Arbeitsauftrag.......................................................................................................................................... 3 Vorbereitung ........................................................................................................................................... 4 1. Normalisierung ................................................................................................................................ 4 2. Normalisierung ................................................................................................................................ 6 Datenbanken und Tabellen erstellen ...................................................................................................... 9 1. Datenbank erstellen ........................................................................................................................ 9 2. Tabellen erstellen .......................................................................................................................... 10 Einfügen der Werte ............................................................................................................................... 13 Ausgabe der Werte................................................................................................................................ 16 Seite 2 von 16 © Tim Rosenbecker und Tobias Spangenberg SQL – TABELLEN NORMALISIEREN UND MIT JOIN VERKNÜPFEN Arbeitsauftrag „Planen Sie den Umfang der Bücher-Datenbank: Legen Sie die Anzahl und die Bezeichnung aller Attribute (Merkmale) fest, die zu jedem Buch/ jedem zu erfassenden Druckmedium etc. notwendig sind. Ordnen Sie alle Attribute zunächst mit Hilfe einer einzigen Datenbanktabelle und teilen Sie diese Schritt für Schritt in Untertabellen auf, so dass sie der dritten Normalform (3NF) entsprechen. Vergeben Sie für jede Tabelle einen Primärschlüssel und legen Sie die erforderlichen Fremdschlüssel fest. Stellen Sie die Relationen innerhalb der Datenbank mit Hilfe des E/R-Modells grafisch dar. Denken Sie dabei auch an die Zwischentabellen (Brückentabellen), wenn n:m-Relationen vorliegen. Weisen Sie jedem Attribut einen geeigneten Datentyp zu. Achtung: unmittelbar miteinander verknüpfte Schlüsselattribute müssen denselben Datentyp aufweisen! Legen Sie die Datenbank nun mit Hilfe von phpMyAdmin an und befüllen Sie diese mit den auf der CD vorhandenen Daten. Überprüfen Sie die Funktionalität der Datenbank mit Hilfe einfacher Abfragen: Sortieren Sie alle Bücher/Medien nach den Verlagen. Sortieren Sie alle Bücher/Medien nach Themengebieten. Lassen Sie sich die in einem bestimmten Jahr erschienenen Bücher/ Medien jeweils gesammelt anzeigen usw.“ Seite 3 von 16 © Tim Rosenbecker und Tobias Spangenberg SQL – TABELLEN NORMALISIEREN UND MIT JOIN VERKNÜPFEN Vorbereitung 1. Normalisierung Als Vorlage, die drei Schritte der Normalisierung von Datenbanken durchzuführen, dient eine Excel – Tabelle. Ausgangspunkt ist der folgende: Bild 1: Ausgangspunkt Seite 4 von 16 © Tim Rosenbecker und Tobias Spangenberg SQL – TABELLEN NORMALISIEREN UND MIT JOIN VERKNÜPFEN Zuerst werdend die Einträge atomarisiert. Erst wurden in der Spalte „Verlag“ das Erscheinungsjahr entfernt und eine seperate Spalte mit dem Titel „Erscheinungsjahr“ angelegt. Die Spalte „Medienart“ besitzt in manchen Fällen mehrere Attribute, diese haben wir einzeln aufgelistet. Bild 2: 1. Normalform Seite 5 von 16 © Tim Rosenbecker und Tobias Spangenberg SQL – TABELLEN NORMALISIEREN UND MIT JOIN VERKNÜPFEN 2. Normalisierung Jeder Titel eines Buches ist eindeutig und ist durch die dazugehörige „id“ mit einem Primärschlüssel gekennzeichnet. Die Spalten „Medienart“ ; „Verlag“ und „Erscheinungsjahr“ werden in eigene Tabellen ausgelagert. Diese Spalten sind nicht vom Primärschlüssel abhängig. Bild 3: 2. Normalform Seite 6 von 16 © Tim Rosenbecker und Tobias Spangenberg SQL – TABELLEN NORMALISIEREN UND MIT JOIN VERKNÜPFEN 3.Normalisierung Zuletzt haben wir eine Tabelle mit dem Namen „buecher“ erstellt. Dort wird die Spalte „Medienart“ der Tabelle „buecher“mit „id“ der Tabelle „Medienart“ verknüpft. So ergibt sich ein Bezug zu den Buchtiteln und den Medienarten. Desweiteren wird „id“ der Tabelle „Verlag“ und „id“ der Tabelle „Erscheinungsjahr“ mit den zugehörigen Spalten in der Tabelle „buecher“ verknüpft. Seite 7 von 16 © Tim Rosenbecker und Tobias Spangenberg SQL – TABELLEN NORMALISIEREN UND MIT JOIN VERKNÜPFEN Dies sieht dann wie folgt aus: Tabelle „buecher“ id Autor Titel Verlag Medienart Erscheinungsjahr 1 …. …. 1 3 2 2 … …. 2 1 3 Tabelle „verlag“ id Verlag 1 Verlag 1 2 Verlag 2 3 Verlag 3 4 Verlag 4 Tabelle „medienart“ id Medienart 1 Medienart 1 2 Medienart 2 3 Medienart 3 4 Medienart 4 Tabelle „Erscheinungsjahr“< id Erscheinungsjahr 1 …. 2 …. 3 …. 4 …. Seite 8 von 16 © Tim Rosenbecker und Tobias Spangenberg SQL – TABELLEN NORMALISIEREN UND MIT JOIN VERKNÜPFEN Nach der Verknüpfung sollte dies so aussehen. Id Autor Titel Verlag Medienart Erscheinungsjahr 1 …. …. Verlag 1 Medienart Erscheinungsjahr 2 3 2 … …. Verlag 2 Medienart Erscheinungsjahr 3 1 Datenbanken und Tabellen erstellen 1. Datenbank erstellen Die Datenbank „buecherhandel“ wurde mit den folgenden Befehlen erstellt: mysql> create database buecherhandel; Im Anschluss daran wurde diese mit dem Befehl: mysql> use buecherhandel; zur Nutzung ausgewählt. Seite 9 von 16 © Tim Rosenbecker und Tobias Spangenberg SQL – TABELLEN NORMALISIEREN UND MIT JOIN VERKNÜPFEN 2. Tabellen erstellen Nun werden in der Datenbank „buecherhandel“ 4 Tabellen angelegt, dazu zählen: buecher verlage medienart erscheinungsjahr Die Erstellung der Tabelle „buecher“ erfolgte mit folgendem Befehl: mysql> create table buecher (id integer auto_increment, autor varchar(120), titel varchar(255), verlag varchar(120), medienart int, erscheinungsjahr int, primary key (id)); Bild 4: Tabelle buecher Die Tabelle “verlage” wurde durch den folgenden Befehl angelegt: mysql> create table verlage (id integer auto_increment, verlag varchar(255), primary key(id), unique(verlag)); Bild 5: Tabelle verlage Seite 10 von 16 © Tim Rosenbecker und Tobias Spangenberg SQL – TABELLEN NORMALISIEREN UND MIT JOIN VERKNÜPFEN Die Tabelle “medienart” wurde durch den folgenden Befehl angelegt: mysql> create table medienart (id integer auto_increment, medienart varchar(120) , primary key (id),unique(medienart)); Bild 6: Tabelle Medienart Seite 11 von 16 © Tim Rosenbecker und Tobias Spangenberg SQL – TABELLEN NORMALISIEREN UND MIT JOIN VERKNÜPFEN Die Tabelle “erscheinungsjahr” wurde durch den folgenden Befehl angelegt: mysql> create table erscheinungsjahr (id integer auto_increment, erscheinungsjahr int, primary key (id),unique(erscheinungsjahr)); Bild 7: Tabelle Erscheinungsjahr Seite 12 von 16 © Tim Rosenbecker und Tobias Spangenberg SQL – TABELLEN NORMALISIEREN UND MIT JOIN VERKNÜPFEN Einfügen der Werte Nun wurden in die angelegten Tabellen mit Befehl oder über phpmyadmin eingefügt. Wir habend dies per Befehl gemacht. Die Befehle sind die folgenden: INSERT INTO buecher VALUES (1, 'Mark Gatter', 'Frei zum Druck: Was Grafikdesigner über Druck und Vorstufe wissen sollten', '7', '1', '1'); INSERT INTO buecher VALUES (2, 'Christian Fries', 'Aufs Ganze: Mediengestaltung im Zeitalter der Unaufmerksamkeit: Basiswissen Medien: Funktion und Wirkung Konzeption und Gestaltung für alle Sinne im Zeitalter der Aufmerksamkeit', '4', '1', '2'); INSERT INTO buecher VALUES (3, 'Rainer Witt', 'Aufs Ganze: Mediengestaltung im Zeitalter der Unaufmerksamkeit: Basiswissen Medien: Funktion und Wirkung Konzeption und Gestaltung für alle Sinne im Zeitalter der Aufmerksamkeit', '4', '1', '2'); INSERT INTO buecher VALUES (4, 'David Bann', 'Die moderne Druckproduktion: Ein Leitfaden', '7', '1', '2'); INSERT INTO buecher VALUES (5, 'Gavin Ambrose', 'Druck & Veredelung: Moderne Methoden der Bild und Textreproduktion und der Aufwertung von Printprodukten', '7', '1', '2'); INSERT INTO buecher VALUES (6, 'Paul Harris', 'Druck & Veredelung: Moderne Methoden der Bild und Textreproduktion und der Aufwertung von Printprodukten', '7', '1', '2'); INSERT INTO buecher VALUES (7, 'Karl Giesriegel', 'Druckwerke und Werbemittel herstellen. Was Sie schon immer über Print wissen wollten', '9', '1', '2'); INSERT INTO buecher VALUES (8, 'Annja Weinberger', 'Flyer optimal texten, gestalten, produzieren', '7', '1', '2'); INSERT INTO buecher VALUES (9, 'Corel Corporation', 'Ulead Photo ImpactXS', '12', '2', '3'); Seite 13 von 16 © Tim Rosenbecker und Tobias Spangenberg SQL – TABELLEN NORMALISIEREN UND MIT JOIN VERKNÜPFEN INSERT INTO buecher VALUES (10, 'Nick Weschkalnies', 'Adobe Flash CS4: Das umfassende Handbuch', '1', '1', '4'); INSERT INTO buecher VALUES (11, 'Richard Beer', 'Adobe Dreamweaver CS4: Webseiten programmieren und gestalten mit (X)HTML, Ajax, CSS, PHP und MySQL: Webseiten entwickeln mit (X)HTML, Ajax, CSS, PHP und MySQL', '1', '1', '4'); INSERT INTO buecher VALUES (12, 'Susanne Gailus', 'Adobe Dreamweaver CS4: Webseiten programmieren und gestalten mit (X)HTML, Ajax, CSS, PHP und MySQL: Webseiten entwickeln mit (X)HTML, Ajax, CSS, PHP und MySQL', '1', '1', '4'); INSERT INTO buecher VALUES (13, 'Monika Gause', 'Adobe Illustrator CS4: Das Praxisbuch zum Lernen und Nachschlagen', '1', '1', '4'); INSERT INTO buecher VALUES (14, 'Sibylle Mühlke', 'Adobe PhotoShop CS4Das Praxisbuch zum Lernen und Nachschlagen', '3', '1', '4'); INSERT INTO buecher VALUES (15, 'Andreas Mylius', 'Animation mit Flash CS 4', '3', '1', '4'); INSERT INTO buecher VALUES (16, 'Heinz Kessler', 'Animation mit Flash CS 4', '8', '1', '4'); INSERT INTO buecher VALUES (17, 'Susanne Rupp', 'Dreamweaver CS4: Professionelle Webseiten entwickeln', '10', '1', '4'); INSERT INTO buecher VALUES (18, 'Böhringer', 'Kompendium der Mediengestaltung Digital und Print: Konzeption Gestaltung Produktion Technik', '10', '1', '4'); INSERT INTO buecher VALUES (19, 'Bühler', 'Kompendium der Mediengestaltung Digital und Print: Konzeption Gestaltung Produktion Technik', '10', '1', '4'); INSERT INTO buecher VALUES (20, 'Schlaich', 'Kompendium der Mediengestaltung Digital und Print: Konzeption Gestaltung Produktion Technik', '13', '1', '5'); INSERT INTO buecher VALUES (21, 'Tom Striewisch', '100 clevere Tipps: Digitalfotografie: Aufnahmetechnik Bildgestaltung Bildbearbeitung', '5', '1', '5'); INSERT INTO buecher VALUES (22, 'LG Electronics Deutschland GmbH', 'Bedienungsanleitung: LG Flatron W2220P, 22"', '5', '3', '5'); INSERT INTO buecher VALUES (23, 'LG Electronics Deutschland GmbH', 'Bedienungsanleitung: LG Flatron W2220P, 22"', '6', '4', '5'); Seite 14 von 16 © Tim Rosenbecker und Tobias Spangenberg SQL – TABELLEN NORMALISIEREN UND MIT JOIN VERKNÜPFEN INSERT INTO buecher VALUES (24, 'Dell', 'Bedienungsanleitung: Dell UltraSharp U2410, 24"', '6', '3', '5'); INSERT INTO buecher VALUES (25, 'Dell', 'Bedienungsanleitung: Dell UltraSharp U2410, 24"', '1', '4', '5'); INSERT INTO buecher VALUES (26, 'Tobias Gräning', 'Einstieg in Adobe Flash CS5: Eigene FlashFilme erstellen. Schritt für Schritt', '1', '1', '5'); INSERT INTO buecher VALUES (27, 'Patrick Jean', 'Einstieg in Adobe Flash CS5: Eigene FlashFilme erstellen. Schritt für Schritt', '11', '1', '5'); INSERT INTO buecher VALUES (28, 'Beste vom Hove', 'Medien gestalten, Lernsituationen und Fachwissen zur Gestaltung und Produktion von Digital und Printmedien', '11', '1', '5'); INSERT INTO buecher VALUES (29, 'Reif', 'Medien gestalten, Lernsituationen und Fachwissen zur Gestaltung und Produktion von Digital und Printmedien', '11', '1', '5'); INSERT INTO buecher VALUES (30, 'Werth', 'Medien gestalten, Lernsituationen und Fachwissen zur Gestaltung und Produktion von Digital und Printmedien', '4', '1', '5'); INSERT INTO buecher VALUES (31, 'Herwig Hörn', 'Spickzettel: Prüfungswissen für Mediengestalter digital & print. 660 Lernkarten mit aktuellen Fragen und kompakten Antworten', '4', '5', '5'); INSERT INTO buecher VALUES (32, 'Inka.Gabriela Schmidt', 'Spickzettel: Prüfungswissen für Mediengestalter digital & print. 660 Lernkarten mit aktuellen Fragen und kompakten Antworten', '4', '5', '5'); INSERT INTO buecher VALUES (33, 'Henrik Wolf', 'Spickzettel: Prüfungswissen für Mediengestalter digital & print. 660 Lernkarten mit aktuellen Fragen und kompakten Antworten', '4', '5', '5'); INSERT INTO buecher VALUES (34, 'Adobe', 'Adobe Creative Suite 5 Master Collection deutsch', '2', '2', '6'); INSERT INTO buecher VALUES (35, 'Adobe', 'Adobe Creative Suite 5 Master Collection deutsch', '2', '6', '6'); Seite 15 von 16 © Tim Rosenbecker und Tobias Spangenberg SQL – TABELLEN NORMALISIEREN UND MIT JOIN VERKNÜPFEN Ausgabe der Werte Nachdem nun alle Werte erfolgreich eingetragen wurden können wir die Tabellen verknüpfen lassen und uns deren Inhalte ausgeben lassen, dies wird mit folgendem Befehl getan: mysql> select buecher.autor, buecher.titel, verlage.verlag, medienart.medienart, erscheinungsjahr.erscheinungsjahr from buecher join verlage on buecher.verlag = verlage.id join medienart on buecher.medienart = medienart.id join erscheinungsjahr on buecher.erscheinungsjahr = erscheinungsjahr.id; Bild 8: Ausgabe der verknüpften Tabellen Seite 16 von 16 © Tim Rosenbecker und Tobias Spangenberg
