💡SQL Projektarbeit

Datenbank für einen Filmverleih implementieren

Die RentAMovie GmbH

Die RentAMovie GmbH verleiht Filme an diverse Kinos in der ganzen Schweiz. Um die Übersicht über alle Filme im Sortiment zu behalten, soll eine Datenbank angelegt werden. In dieser Datenbank müssen alle Filme mit dem Filmtitel, Regisseur sowie dem Erscheinungsjahr verwaltet werden können. Ausserdem muss jederzeit ersichtlich sein, welche Filme sich bei welchen Kinos befinden.

Von jedem Film gibt es mehrere DVDs im Sortiment, so dass ein Filmtitel gleichzeitig an diverse Kinos verliehen werden kann. Ein Kino kann zudem verschiedene Filme auf einmal ausleihen. Nach spätestens drei Monaten muss das Kino den ausgeliehenen Film wieder zurückgeben.

Projektaufgaben

AUFGABE 1 Erstelle die Datenbank Filmverleih, indem du diese mit dem File filmverleih_backup.bak wiederherstellst. Du stellst fest, dass bei der Tabelle Ausleihe die Primär und Fremdschlüssel fehlen und auch bei der Tabelle Inventar_Ausleihe fehlt der Fremdschlüssel, welche auf die Tabelle Ausleihe referenziert. Bitte füge diese nachträglich hinzu. (kurze Syntax)

AUFGABE 2 Bei der Tabelle Inventar-Ausleihe muss zudem gewährleistet sein, dass das Rückgabedatum gleich oder grösser als das Ausleihedatum ist, auch wenn das Rückgabedatum leer ist.

AUFGABE 3 Im Rahmen eines Inventarisierungs-Projekts wird in der Tabelle Inventar eine neue Spalte gefordert. Die neue Spalte soll Regal heissen und Text-Eingaben ermöglichen. Bitte füge die neue Spalte mit dem Standard-Wert 'nicht zugewiesen' hinzu.

AUFGABE 4 Der Einkaufsleiter der GmbH beauftragt dich, zwei neue Filme deiner Wahl in die Datenbank aufzunehmen. Überlege, in welchen Tabellen Einträge nötig sind und führe diese Einträge aus.

AUFGABE 5 Du fragst dich aus Neugier, ob in der Datenbank Regisseure erfasst sind, zu denen es keine Einträge in der Filmtabelle gibt. Wie findest du das heraus? Versuche, die Problemstellung auf zwei verschiedene Arten zu lösen.

AUFGABE 6 Die Marketingabteilung plant eine PR-Aktion und benötigt dazu eine Adressliste aller Kinos aus der Datenbank. Die Vorgaben sind: Name des Kinos in Grossbuchstaben, Kontaktname, Adresse, PLZ und Ort müssen konkateniert sein.

AUFGABE 7 Für das Verfassen eines Beitrags im Jahresbericht erlegt dir die Sekretärin des Geschäftsführers die Aufgabe auf, eine Liste zu erstellen, die aufzeigt, welche Kinos bisher wie viele Filme ausgeliehen haben. Bitte sortiere die Ausgabe nach der Anzahl absteigend.

AUFGABE 8 Der Inventarverantwortliche möchte wissen, welche Filme aktuell von welchem Kino seit wann ausgeliehen sind. Erstelle hierzu eine View, die ihn mit den notwendigen Informationen versorgt.

AUFGABE 9 Damit der Inventarverantwortliche auf, die eben erstellte View zugreifen kann, musst du für ihn einen Datenbank-Zugang bereitstellen (Login lager mit einem von dir bestimmten Passwort) und ihm anschliessend für die View Lese-Rechte erteilen. Stelle aber sicher, dass er sonst keine anderen Datenbanktabellen auslesen kann!

AUFGABE 10 Für den Fall, dass der Datenbankserver aussteigt und wichtige Daten möglicherweise verloren gehen, musst du im Auftrag des Geschäftsführers ein Backup der Datenbank erstellen. Fertige zu diesem Zweck ein SQL-Skript an, welches du einfach ausführen kannst.

AUFGABE 11 Dem Sicherheitsbeauftragten des Filmverleihs ist es ein Anliegen, dass die Adress- und Kino-Informationen in ein separates Datenbankschema ausgelagert werden. Erstelle hierzu ein neues Schema mit dem Namen cinema und verschiebe die betroffenen Tabellen in das neue Schema. Stelle zudem sicher, dass nur der dbo-User Zugriffs-Rechte auf das gesamte neue Schema hat und dem User lager sämtliche DQL- und DML-Statements in diesem Schema verweigert werden.

AUFGABE 12 Dir ist aufgefallen, dass in der Tabelle Film die Information über die Produktionsfirma fehlt. Füge die neue Spalte an zweitletzter (!) Stelle hinzu. Überlege genau, wie du dabei vorgehst.

AUFGABE 13 Im Sekretariat wurde festgestellt, dass die Einträge vom Jahr 2015 Betreffend Rückgabedatum um einen Tag nach vorne geschoben wurden.

Du wirst gebeten alle diese Änderungen wieder zu korrigieren.

AUFGABE 14 Die Ausleihe mit der InventarAusleiheID 26 hat gar nie stattgefunden. Du musst diese Ausleihung nun löschen. Überlege Dir, wo Daten überall gelöscht werden müssen und in welcher Reihefolge dies gemacht werden soll. Beachte, dass diese Löschung in einer Transaktion zuerst simuliert werden muss, bevor sie wirklich ausgeführt wird.

AUFGABE 15 Erstelle eine Liste aller Orte, welche ein Kino haben. Jeder Ort darf nur einmal aufgeführt werden, auch wenn dieser Ort mehrere Kinos zählt.

AUFGABE 16 In welchem Jahr wurden am meisten Filme gemietet. Die Ausgabe soll nur eine Spalte und eine Zeile anzeigen.

AUFGABE 17 Wie hoch ist die durchschnittliche Mietdauer? Ausgewertet werden nur diese Vermietungen, welche schon zurückgegeben wurden.

AUFGABE 18 Erstelle eine Abfrage, welche alle Filme zurückgibt. Die Ausgabe soll einen String ausgeben, welcher den Filmnamen und in Klammer (Regisseur-Team) falls nicht nur ein Regisseur am Film beteiligt war. Bitte erstelle diese Abfrage mit einem CASE.

AUFGBE 19 Du musst immer wieder eine Abfrage über die ganze Datenbank machen. Du entscheidest Dich nun eine View mit 8 Spalten und dem Namen Übersicht zu erstellen. Teste die View anschliessend

AUFGABE 20 Für die PR-Aktion von Aufgabe 6 erhältst du nun den Auftrag ein Schreiben an alle Kinos zu erstellen, in welchem du die Kunden auf die beiden neuen von dir erfassten Filme aufmerksam machen sollst. Setze dazu einen Serienbrief (Word) auf, in dem du die benötigten Daten (Adresse des Kinos, Anrede, neue Filme) direkt von der Datenbank beziehst. Wie du den Brief gestaltest und formulierst, ist ganz allein dir überlassen.

AUFGABE 21 Damit der Jahresbericht auch optisch was hergibt, möchte die Sekretärin die Liste, welche du in Aufgabe 7 für sie erstellt hast, als Diagramm in den Bericht einfliessen lassen. Nutze hierzu die Möglichkeiten von Excel, entscheide dich für das geeignete Diagramm und speichere dieses mit der entsprechenden Legende als Bild ab.