Hochschule für Technik und Wirtschaft
Werbung
Hochschule für Technik und Wirtschaft Fakultät Informatik / Mathematik Prof. Dr. G. Gräfe Lehrveranstaltung "Erweiterte Datenbanktechnologien / Medienarchive“ Aufgabenstellungen für die Übungen DBMS MS SQL-Server 2008 7. Aufgabenkomplex: Aufbau einer Datenbank im DBMS MS SQL-Server 2008 In einer Datenbank Verkauf werden die Informationen eines Handelsunternehmens abgelegt. Eine Tabelle Shop enthält alle Verkaufsstellen des Unternehmens. Über eine Tabelle Geographie kann eine regionale Zuordnung der Verkaufsstellen erfolgen. Alle im Handelsunternehmen angebotenen Produkte werden in einer Tabelle Artikel hinterlegt. In der Tabelle Bestand wird festgehalten welche Menge der einzelnen Artikel in welcher Verkaufsstelle noch vorrätig sind. Die Tabelle Mitarbeiter enthält die Daten für jeden Mitarbeiter. 1. Loggen Sie sich auf dem lokalen Datenbankserver (MS SQL Server 2008) ein und erstellen Sie dort eine neue Datenbank mit dem Namen „Verkauf“ und stellen Sie den Sicherungsmode der Datenbank ohne Protokollsicherung mit dem Befehl ein: ALTER DATABASE Verkauf SET RECOVERY SIMPLE 2. Erstellen Sie folgende Tabellen in der Datenbank entsprechend der angegebenen Tabellendefinition. Shop Spaltenname Shop_ID Bezeich Ort PLZ Strasse Land_ID Typ int varchar(15) varchar(25) varchar(5) varchar(25) varchar(2) Bemerkung Primärschlüssel, Not Null Artikel Spaltenname Artnr ABezeich VPreis Artgruppe Typ int varchar(30) money varchar(5) Bemerkung Primärschlüssel, Not Null Geographie Spaltenname Land_ID Bundesland Region Staat Typ varchar(2) varchar(25) varchar(25)) varchar(25) Bemerkung Primärschlüssel, Not Null DBS3_Prakt2-WS11 Fremdschlüssel zu Geographie 1 // 8 3. Mitarbeiter Spaltenname Mitnr Name Vorname Ort Gebdat Gehaltf Shop_ID Typ int varchar(15) varchar(15) varchar(15) date money int Bemerkung Primärschlüssel, Not Null Bestand Spaltenname Shop_ID Artnr Menge Typ int int int Bemerkung Primärschlüssel, Not Null; Je Fremdschlüssel Fremdschlüssel zu Shop Sichern Sie die Datenbank Verkauf durch Ausführung des Scriptes Sichern der Datenbank Verkauf nach Anlage 1. Benennen Sie die Datei um in Verkauf_Testsicherung.bak und speichern Sie die erzeugte Sicherungsdatei Verkauf_Testsicherung.bak auf Ihrem SambaVerzeichnis. 4. Löschen Sie die Datenbank Verkauf durch Ausführung des Scriptes Löschen der Datenbank Verkauf 5. nach Anlage 1. Kopieren Sie die Sicherungsdatei Verkauf_YYYYMMDD.bak aus Ihrem SambaVerzeichnis in „C:\temp“ und stellen Sie die Datenbank Verkauf wieder her durch Ausführung der Scripte Backup Informationen lesen aus Datei Verkauf_YYYYMMDD.bak und Datenbank Wiederherstellen aus Datei Verkauf_YYYYMMDD.bak nach Anlage 1. Hinweis: Das Sichern der Datenbank nach Punkt 3) ist am Ende jedes Praktikums und das Wiederherstellen der Datenbank nach den Punkten 4) und 5) zu Beginn jedes Praktikums durchzuführen. 6. Die Inhalte der einzelnen Dimensionstabellen stehen als xls-Dateien im Verzeichnis „I:/prakt/graefe/DBS3“ zur Verfügung: Tabelle xls-Datei Geographie Shop Mitarbeiter Artikel Bestand Geographie3.xls Shop3.xls Mitarbeiter3.xls Artikel3.xls Bestand3.xls Kopieren Sie die xls-Dateien nach „c:\temp“. Erzeugen Sie jeweils in der xls-Datei in der ersten Spalte hinter den Daten einen SQL INSERT-Befehl für die Tabelle. DBS3_Prakt2-WS11 2 // 8 Hinweis: Verwenden Sie die Excel-Funktion =Verketten(…) zum Erzeugen des INSERT-Befehls und die Funktion Ersetzen zum Anpassen des Dezimaltrennzeichens. Informieren Sie sich in der Excelhilfe über deren Wirkungsweise. Kopieren Sie die in Excel erzeugten INSERT-Befehle (Spalte mit den Befehlen) in ihre im SQL Server Management Studio geöffnete Scriptdatei und führen Sie die INSERT-Befehle aus. 7. Fügen Sie sich selbst als Mitarbeiter mit dem Beruf Praktikant hinzu. Überprüfen Sie die Ergebnisse: 8. Führen Sie in der Tabelle Mitarbeiter die folgenden Datendefinitionsanweisungen aus: - Erweitern Sie die Spalte Name auf eine Länge von 20! - Erstellen Sie einen zusammengesetzten Sekundärindex auf Name und Vorname! 9. Führen Sie in der Datenbank Verkauf die folgenden Abfragen aus: - Erstellen Sie einen Geburtstagskalender der Mitarbeiter. Ordnen Sie hierzu die Daten nach Monat und Tag des Geburtstagsdatums. Das Geburtsjahr soll für die Ordnung der Liste keine Rolle spielen. Legen Sie für das Erstellen des Geburtstagskalenders einen View an! - Zeigen Sie alle Wohnorte der Mitarbeiter an, bei denen der Mitarbeiter in einem anderen Ort wohnt wie er arbeitet (Anzeige von Mitarbeitername, Wohnort und Ort des Shops) - Ermitteln Sie den wertmäßigen Gesamtbestand aller Waren der Shops aller Bundesländer (nur Anzeige von Bundesland und Bestandswert [= Summe von Menge * VPreis]). 10. Schreiben Sie je eine Prozedur, die eine Analyse nach der Höhe des Bestandswertes für die einzelnen Artikel je Shop ermöglicht: - Nach Übergabe der Artikelnummer sollen die Verkaufsstellen angezeigt werden, in denen der Artikel vorrätig ist (Anzeige von Shopnummer, Ort, Bestand und Wert [Menge * VPreis]). Sollte der Artikel nirgendwo vorrätig sein oder nicht existieren, soll eine Fehlermeldung erscheinen. - Nach Übergabe eines Wertes (im Sinne von Bestandswert) sollen Artikel und Shops angezeigt werden, bei denen ein Artikel einen Bestandswert größer dem eingegebenen Wert haben(Anzeige von Artikelnummer, Shopnummer, Ort, Bestand und Wert [Menge * VPreis]). DBS3_Prakt2-WS11 3 // 8 Hochschule für Technik und Wirtschaft Fakultät Informatik / Mathematik Prof. Dr. G. Gräfe Lehrveranstaltung "Erweiterte Datenbanktechnologien / Medienarchive“ Aufgabenstellungen für die Übungen 8. Aufgabenkomplex: Replikation Unter Verwendung des Replikationsassistenten ist eine Replikation der Datenbanken der Zentrale und von zwei Verkaufsstellen des Handelsunternehmen exemplarisch zu realisieren. Dabei sollen folgende Aspekte berücksichtigt werden: Sofern nichts anderes angegeben ist für die Datenbanken aller Shops (Abonnent) die Datenbank der Zentrale die Primärdatenbank (Verleger). Die Tabellen Geographie, Shop, Mitarbeiter und Artikel dürfen nur zentral aktualisiert werden. In den Datenbanken der Shops gibt es eine vollständige Kopie der zentralen Tabelle aller Artikel, die bei Änderungen in der Zentrale aktualisiert werden. Weiterhin gibt es in den Datenbanken der Shops die Tabelle Mitarbeiter, die aber nur die Mitarbeiterdaten enthält, die dem jeweiligen Shop zugeordnet sind. Zentrale Änderungen der Mitarbeiterdaten müssen sofort nach Aktualisierung im Shop abrufbar sein. Sowohl in der Zentrale als auch in den Shops wird die Tabelle der Bestände geführt. Der Inhalt dieser Tabelle darf sowohl in der Zentrale (z.B. Hinzufügen eine im Shop neu angebotenen Artikels) als auch in den Shops(z.B. Höhe des Bestandes) aktualisiert werden, wobei hier eine Latenzzeit von einer Stunde ausreichend ist. Verleger Abonnenten Verkauf_Shop20 Artikel Mitarbeiter Bestand Verkauf_Zentrale Geographie Shop Artikel Mitarbeiter 20 Distributor 20 Bestand Verkauf_Shop75 75 75 DBS3_Prakt2-WS11 Artikel Mitarbeiter Bestand 4 // 8 Aufgaben: 1. Melden Sie sich am SQL-Server an und legen Sie zunächst eine Datenbank Verkauf_Zentrale an. Definieren Sie in dieser Datenbank die Tabellen Geographie, Shop, Mitarbeiter, Bestand sowie Artikel und füllen die Tabellen mit allen Datensätzen (Ausführen des Skripts der Aufgabe 2, 6 und 7 des vorigen Aufgabenkomplexes). 2. Richten Sie weiterhin zwei neue Datenbanken von zwei Shops ein, die Sie Verkauf_Shop20 und Verkauf_Shop75 nennen. Legen Sie in diesen beiden Datenbanken die Tabellen Mitarbeiter, Artikel und Bestand ohne die Fremdschlüsseldefinitionen an (Skript). Geben Sie keine Datensätze ein! 3. Richten Sie mit Hilfe des Replikationsassistenten eine Snapshot-Replikation („Momentaufnahmeveröffentlichung“) für die Tabelle Artikel ein. Die Datenbank der Veröffentlichung (Verleger) ist die Datenbank Verkauf_Zentrale . Richten Sie als Abonnent die Datenbanken Verkauf_Shop20 und Verkauf_Shop75 ein. 4. Prüfen Sie, ob in den Datenbanken Verkauf_Shop20 und Verkauf_Shop75 ein Snapshot der Tabelle Artikel ausgeführt wurde. 5. Richten Sie weiterhin mit Hilfe des Replikationsassistenten eine TransaktionsReplikation („Transaktionsveröffentlichung mit aktualisierbaren Abonnements“) für die Tabelle Mitarbeiter ein. Die Datenbank der Veröffentlichung (Verleger) ist die Datenbank Verkauf_Zentrale . Richten Sie als Abonnent die Datenbanken Verkauf_Shop20 und Verkauf_Shop75 je für die Verkaufsstellen mit der Shop_ID 20 und 75 ein. Für eine Zuordnung der Mitarbeiter zu den jeweiligen Shops ist als Filter die jeweilige Shop_ID anzugeben. 6. Prüfen Sie, ob in den Datenbanken Verkauf_Shop20 und Verkauf_Shop75 ein Snapshot der Tabelle Mitarbeiter mit der richtigen Shop-Zuordnung ausgeführt wurde. 7. Richten Sie außerdem mit Hilfe des Replikationsassistenten eine MergeReplikation („Merge-Veröffentlichung“) für die Tabelle Bestand ein. Die Datenbank der Veröffentlichung (Verleger) ist zunächst die Datenbank Verkauf_Zentrale . Richten Sie als Abonnent hier nur die Datenbank Verkauf_Shop20 ein. Für eine Zuordnung der Artikelnummern zu dem jeweiligen Shop ist als Filter die jeweilige Shop_ID anzugeben. Ein Snapshop („Momentaufnahme“) ist sofort auszuführen, Der Snapshot-Agent soll danach Änderungen täglich zu jeder vollen Stunde weitergeben. 8. Prüfen Sie in der Datenbank Verkauf_Shop20 den Snapshot der Tabelle Bestand mit der richtigen Shop-Zuordnung. 9. Muffins (Artnr: 1205) sollen in allen Verkaufsstellen zum Sonderpreis angeboten werden. Ändern Sie für diesen Artikel in der Zentrale den Preis auf 1,29 €. Prüfen Sie, ob die Preisänderung in den Datenbanken der Verkaufsstellen durchgeführt wurde. 10. Eine Gehaltserhöhung ist vorgesehen. Lassen Sie sich zunächst den aktuellen Stand der Summe des Gehaltes in der Datenbank Verkauf_Shop20 sowie das Gehalt des Herrn Uhr (Mitnr: 110) anzeigen. Erhöhen Sie anschließend in der Zentrale für alle Mitarbeiter das Gehalt um 3%. Lassen Sie sich danach die geänderte Summe des Gehaltes in der Datenbank Verkauf_Shop20 sowie das DBS3_Prakt2-WS11 5 // 8 Gehalt des Herrn Uhr (Mitnr: 110) anzeigen. Wurden Änderungen auch in der Datenbank Verkauf_Shop75 ausgeführt? 11. Berechnen Sie in der Zentrale den aktuellen Wert des Artikelgesamtbestandes (Summe von Preis*Menge). 12. Eine neue Lieferung von Muffins (Artnr: 1205) ist im Shop 20 eingetroffen. Erhöhen Sie in der Datenbank Verkauf_Shop20 den Bestand um 100 Stück. Führen Sie anschließend eine manuelle Aktualisierung der Merge-Replikation aus! Prüfen Sie, ob die Mengenänderung in der Datenbank der Zentrale durchgeführt wurde. Berechnen Sie in der Zentrale den neuen Wert des Artikelgesamtbestandes. Löschen Sie die 3 Datenbanken Verkauf_Zentrale, Verkauf_Shop20 und Verkauf_Shop75 wieder. DBS3_Prakt2-WS11 6 // 8 Hochschule für Technik und Wirtschaft Fakultät Informatik / Mathematik Prof. Dr. G. Gräfe Lehrveranstaltung "Erweiterte Datenbanktechnologien / Medienarchive“ Aufgabenstellungen für die Übungen 9. Aufgabenkomplex: Verwalten von Mediadaten mittels FileStream Für die Verwaltung von großen Texten und Mediendaten soll eine Datenbanklösung auf Basis der FileStream-Technologie des MS SQL Servers 2008 aufgebaut werden, die eine konsistente Verwaltung aller Daten sichert. Aufgaben: 1. Melden Sie sich am SQL-Server an und legen Sie eine FileStream-aktivierte Datenbank MediaDB an. Diese Datenbank soll zwei Dateigruppen enthalten: Primary und FileStreamGroup1. Dabei soll Primary eine normale Dateigruppe für die Verwaltung klassischer Tabellen sein, während FileStreamGroup1 für die Verwaltung der FileStream-Daten vorgesehen ist. Als Pfad für die FileStreamDateigruppe soll ´c:\temp’ angegeben werden (FILENAME). 2. Wechseln Sie in die Datenbank MediaDB und legen je eine Tabelle Texte und Media an. In beiden Tabellen wird zunächst eine Id-Spalte als ROWGUIDCOLSpalte angelegt, die zur Verwendung von FileStream-Daten mit externen Anwendungen erforderlich ist. Beide Tabellen erhalten weiterhin ein Feld für einen Primärschlüssel (Textnr bzw. Medianr) im Format INTEGER UNIQUE sowie ein Feld Bezeichnung (CHAR(30)). In die Tabelle Texte kommt noch ein Feld Lebenslauf, in die Tabelle Media eine Spalte Objekt. Beide Spalten werden als VARBINARY(MAX)-Spalte mit dem FILESTREAM-Attribut erstellt und werden benötigt, um die Texte bzw. Medienobjekte im Dateisystem zu speichern. 3. Fügen Sie in die Tabelle Texte zwei neue Datensätze mit beliebigem Inhalt ein. Füllen Sie auch das Feld Lebenslauf. Lassen Sie sich den Tabelleninhalt anzeigen! Führen Sie anschließend eine UPDATE- und eine DELETE-Operation aus! 4. Fügen Sie in die Tabelle Media ebenfalls zwei neue Datensätze ein. Der erste Datensatz soll sich auf das Bild „Koala“ beziehen, der zweite auf die AudioDatei „Sleep Away“. Beide Dateien befinden sich im Verzeichnis „I:/prakt/graefe/DBS3“. Legen Sie bitte beide Dateien im FileStream-Attribut Objekt ab! Lassen Sie sich den Tabelleninhalt anzeigen! DBS3_Prakt2-WS11 7 // 8 Anhang 1: Scripts zum Sichern und Wiederherstellen Datenbank Verkauf auf dem SQL Server der ----------------------------------------------------------------------------Sichern der Datenbank Verkauf ----------------------------------------------------------------------------DECLARE @dir varchar(50) DECLARE @today datetime DECLARE @filebak varchar(70) -- Sicherungsverzeichnis definieren select @dir = 'C:\temp\' -- Dateinamen mit Datumsangebe festlegen select @today = GetDate() -- Erzeugen der Dateinamen: Verkauf_YYYYMMDD.bak select @filebak = @dir + 'Verkauf_' + convert(varchar(8),@today,112)+'.bak' -- Sicherung durchführen BACKUP DATABASE Verkauf TO DISK = @filebak ----------------------------------------------------------------------------Löschen der Datenbank Verkauf ----------------------------------------------------------------------------USE master go DROP DATABASE Verkauf ----------------------------------------------------------------------------Backup Informationen lesen aus Datei Verkauf_YYYYMMDD.bak ----------------------------------------------------------------------------DECLARE @dir varchar(50) DECLARE @filebak varchar(70) ---Sicherungsverzeichnis definieren select @dir = 'C:\temp\' ---Dateinamen festlegen (dazu YYMMDDDD anpassen !!!) select @filebak = @dir + 'Verkauf_YYYYMMDD.bak' -- Backupinfos lesen RESTORE FILELISTONLY FROM DISK = @filebak ----------------------------------------------------------------------------Datenbank Wiederherstellen aus Datei Verkauf_YYYYMMDD.bak ------------------------------------------------------------------------------ vorher muss eine ggf. vorhandene Datenbank Verkauf gelöscht werden DECLARE @dir varchar(50) DECLARE @filebak varchar(70) ---Sicherungsverzeichnis definieren select @dir = 'C:\temp\' ---Dateinamen festlegen (dazu YYMMDDDD anpassen !!!) select @filebak = @dir + 'Verkauf_YYYYMMDD.bak' --Wiederherstellen RESTORE DATABASE Verkauf FROM DISK = @filebak WITH FILE = 1 DBS3_Prakt2-WS11 8 // 8
