1 ¨Ubungen zu Datenbank

Dalitz
Datenbanken: Übungen
WS 12/13
1 Übungen zu Datenbank-Kategorien
Übung 1.1 Betrachten Sie anhand der grafischen Darstellung im Skript den Unterschied zwischen
einer Desktop Datenbank und einer Client-Server Datenbank.
Geben Sie für beide Systeme an:
a) notwendige Parameter für einen Verbindungsaufbau
b) Vorteile und Nachteile
c) typische Einsatzgebiete
MS ACCESS ist ein Beispiel für eine Desktop-Datenbank. Trotzdem gibt es manche IT-Laien,
die von einem “Access-Server” sprechen. Was könnte damit gemeint sein?
Übung 1.2 Ein Unternehmen möchte ein Customer Relationship Management System (CRM) zur
Verwaltung von Kundendaten einführen. Das System soll folgende Eigenschaften haben:
• Innendienstler der zentralen Verwaltung greifen von ihren normalen Arbeitsplatzrechnern
auf die Daten zu. Außendienstler werden mit tragbaren Laptops ausgestattet, die auch
offline direkt beim Kunden eingesetzt werden sollen.
• Innendienstler haben Zugriff auf alle Kundendaten, Außendienstler nur auf die Kunden
ihres Bezirks.
Aufgaben:
a) Skizzieren Sie ein grobes Schaubild der Systemarchitektur. Machen Sie dabei kenntlich
an welchen Stellen Datenbanksysteme zum Einsatz kommen sollen.
b) In welchen Bereichen des CRM-Systems würden Sie ein Desktop DBS und in welchen
Bereichen ein Client-Server DBS einsetzen? Begründen Sie, indem Sie Vor- und Nachteile
der jeweiligen Systeme bewerten.
Dalitz
Datenbanken: Übungen
WS 12/13
2 Übungen zu SQL
Übung 2.1 Das Hersteller/Produkt Beispiel der Vorlesung enthält Integrity-Constraints. Geben Sie
eine Folge von Insert, Update und Delete SQL-Befehlen an, die aufgrund der Constraints fehlschlagen. Welche Statements schlagen warum fehl?
Hersteller
Produkt
nr#
nr#
name
name
herstellernr
Übung 2.2 Geben Sie ein SQL-Script an, das die folgenden Tabellen anlegt:
Person
Plzort
nr#
plz#
name
ort
vorname
plz
strasse
Legen Sie dabei folgende Integrity-Constraints an:
• das Feld ort in der Tabelle plzort darf nicht leer sein
• der Wert plz in der Tabelle person muss in der Tabelle plzort vorkommen
• die Kombination vorname, name soll eindeutig sein
• die plz darf nur aus Ziffern bestehen (Hinweis: SIMILAR TO)
Übung 2.3 Geben Sie für folgende Datenmanipulationen an den obigen Tabellen SQL-Statements
an:
a) Auflisten aller nr in person, bei denen mindestens eines der restlichen Felder leer ist
b) Anhängen des Strings ’bla’ an jeden namen in person (Hinweis: der Operator für
String-Konkatenation lautet in SQL ||)
Dalitz
Datenbanken: Übungen
WS 12/13
Übung 2.4 Das hersteller/produkt Beispiel der Vorlesung werde wie folgt um eine Tabelle mit Zulieferungen der Hersteller erweitert:
-- Tabellendefinitionen
create table hersteller (
hnr
varchar(4),
name varchar(30),
stadt varchar(30),
primary key (hnr)
);
create table produkt (
pnr
varchar(4),
name varchar(30),
hnr
varchar(4),
preis numeric(8,2),
primary key (pnr)
);
create table lieferung (
lnr
varchar(6),
pnr
varchar(4),
datum date,
menge numeric(8,2),
primary key (lnr)
);
-- referentielle Integrität
alter table produkt
add foreign key (hnr) references hersteller (hnr);
alter table lieferung add foreign key (pnr) references produkt (pnr);
Formulieren Sie auf diesen Tabellen die folgenden Abfragen in SQL:
a) Auflistung aller Produkte (pnr, name) mit den zugehörigen Herstellern (hnr, name).
b) Welcher Hersteller hat überhaupt kein Produkt?
c) Welches ist die Einzellieferung (alle Felder) mit der höchsten Menge?
d) Von welchen Herstellern (hnr, name) wurden im Monat 03/2002 keine Produkte geliefert?
e) Tagesweise Auflistung des angelieferten Warenwerts (preis * menge).
f) Monatsweise Auflistung des pro Hersteller angelieferten Warenwerts.
g) Löschen Sie alle Produkte, die noch nie geliefert wurden. Formulieren Sie das Statement
sowohl mit IN, als auch mit EXISTS.
Übung 2.5 In die Tabelle person haben sich Einträge mit identischem Feld name eingeschlichen:
CREATE TABLE person (
nr
INT PRIMARY KEY,
name
VARCHAR(30),
geburt
DATE,
adresse1 VARCHAR(30),
adresse2 VARCHAR(30)
);
Realisieren Sie die folgenden Fragestellungen in SQL:
a) Ermitteln Sie alle Namen die mehrfach vorkommen.
b) Zeigen Sie zusätzlich bei den Namen an, wie oft sie vorkommen.
c) Löschen Sie alle Namen die mehrfach vorkommen bis auf einen Namen.
Hinweis: Ggf. können Sie auch mehrere Statements verwenden.
d) Können Sie Ihr “Doublettenlöschverfahren” verallgemeinern auf mehrere Spalten, z.B.
auf doppelte Kombinationen name + geburt?
Dalitz
Datenbanken: Übungen
WS 12/13
3 Übungen zum relationalen Modell und Normalformen
Übung 3.1 Sei R eine Relation mit Attributen A1 , A2 , . . . , An . Geben Sie als Funktion von n an, wie
viele Superschlüssel R hat, wenn
a) A1 der einzige Schlüsselkandidat ist
b) {A1 , A2 } und A3 die einzigen Schlüsselkandidaten sind
Übung 3.2 Manche Autoren stellen das Postulat auf, dass als Primary Key niemals ein reales Attribut
genommen werden sollte, sondern immer nur ein künstlicher numerischer Schlüssel.
Geben Sie Argumente an, die für bzw. gegen diese Ansicht sprechen.
Übung 3.3 Das Relationsschema hersteller(hnr,name,strasse,plz,ort) aus der Vorlesung soll wie folgt
erweitert werden:
• ein Hersteller kann mehrere Standorte haben
• ein Standort ist als Hauptsitz zu kennzeichnen
a) Entwerfen Sie für diese Anforderung ein relationales Datenbankschema und bewerten Sie
Ihre Lösung unter dem Aspekt der Redundanzvermeidung.
b) Können Foreign Key Constraints in Ihrer Lösung ggf. zu Problemen führen?
(Hinweis: Versuchen Sie ein SQL-Statement zum Einfügen eines neuen Herstellers mit
bestimmtem Hauptsitz zu formulieren.)
Übung 3.4 Betrachten Sie die folgende “Relation” kunde und formen Sie sie in ein Datenbankschema
mit Relationen in erster Normalform um.
kunde
kdnr#
name
adresse
ansprechpartner
name telefon hobbys
Es gelten folgende Regeln:
• Ein Kunde kann beliebig viele Ansprechpartner haben.
• Pro Ansprechpartner interessieren maximal zwei Hobbys.
Übung 3.5 Als Datenmodell für die erste Praktikumsaufgabe schlägt jemand vor, alle Informationen
der Datei dbsnam.csv Eins-zu Eins in einer einzigen Relation abzulegen als
personal = (nr#, name, ort, gehalt, abtnr, abtname)
Geben Sie Beispiele für Insert-, Update- und Delete-Anomalien an, die zeigen, dass dieses
Datenmodell schlecht ist.
Dalitz
Datenbanken: Übungen
WS 12/13
Übung 3.6 Beweisen Sie die folgenden Inferenzregeln (direkt über die Definition der funktionalen
Abhängigkeit oder (einfacher) mithilfe von Armstrongs Regeln):
IR4 (Zerlegung)
X →Y ∪Z
⇒
X → Y und X → Z
IR6 (Pseudotransitivität)
X → Y und W ∪ Y → Z
IR7 (Komposition)
X → Y und V → W
⇒
⇒
W ∪X →Z
X ∪V →Y ∪W
Übung 3.8 Betrachten Sie folgendes Relationsschema:
INVENTAR (inventarnr#, bezeichnung, typnr, typname,
gebaeudenr#, gebaeudename, raum, verwalter)
Das Inventar wird pro Gebäude geführt, so dass {gebaeudenr, inventarnr} der Primärschlüssel
ist. Ferner gelten die funktionalen Abhängigkeiten
{gebaeudenr} → {gebaeudename, verwalter}
{typnr} → {typname}
a) Welche Abhängigkeiten sind unverträglich mit den Normalformen 2NF, 3NF/BCNF?
b) Formen Sie dieses Schema um (ggf. in mehreren Schritten) in ein Schema in BCNF.
c) Wieso ist das von Ihnen erhaltene Schema nicht mehr in BCNF, wenn zusätzlich die funktionale Abhängigkeit {raum} → {gebaeudenr} gilt?
Was müssen Sie am Datenbankschema ändern, um auch in diesem Fall die Boyce-Codd
Normalform zu erreichen?
Übung 3.9 Für die Relation R(a, b, c, d, e, f, g, h) gelten folgende funktionale Abhängigkeiten:
{be} → {gh}
{d} → {c}
{g} → {f a}
a) Was sind die Schlüsselkandidaten dieser Relation? Gibt es einen Schlüsselkandidaten, der
d nicht enthält?
b) Warum ist dieses Schema nicht in BCNF?
c) Formen Sie dieses Schema in ein Schema in BCNF um. Erhält diese Umformung die
funktionalen Abhängigkeiten?
Dalitz
Datenbanken: Übungen
WS 12/13
4 Übungen zum Entity-Relationship Modell
Übung 4.1 Oft sollen in einer Relation der Form R(att1 #, att2 , bla, att3 ) für das Atrribut bla nur
bestimmte Werte zulässig sein.
a) Schlagen Sie Lösungen für diese Anforderung im Rahmen des relationalen Modells vor.
Welchen Ihrer Lösungsvorschläge favorisieren Sie? Warum?
b) Wie sähe eine Lösung im Rahmen des Entity/Relationship-Modells aus?
Übung 4.2 Entwickeln Sie für die folgenden Anforderungen einer Bibliotheksverwaltung ein EntityRelationship Diagramm und leiten Sie daraus ein relationales Datenbankschema ab.
Zu verwaltende Daten:
• Bücher mit Signatur, Titel, Autor, Schlagworte, Erscheinungsjahr, Ausleihkennung (ausleihbar, nicht ausleihbar, kurzzeitig ausleihbar, ...)
• Buch-Begleitartikel mit Typ (CD, Lösungsbuch, Kurzreferenz, ...), Beschreibung
• Benutzer mit Benutzernr, Name, Vorname, Geburtsdatum, Adresse
• Ausleihen mit Buch, Benutzer, Ausleihdatum, Leihfrist
Regeln:
• Ein Buch kann beliebig viele Begleitartikel haben. Jeder Begleitartikel muss einem Buch
zugeordnet sein.
• Ein Buch kann mehrere Schlagworte haben. Die Schlagworte dürfen nicht frei wählbar
sein, sondern müssen aus einer Auswahlliste kommen.
• Ein Buch kann nur von maximal einem Benutzer ausgeliehen sein. Ein Benutzer kann
beliebig viele Bücher ausleihen.
Dalitz
Datenbanken: Übungen
WS 12/13
5 Übungen zu Views
Die Aufgaben beziehen sich auf das folgende Datenbankschema:
Person
pnr#
name
geschlecht
Film
fnr#
titel
jahr
regie
kosten
FilmStar
pnr#
fnr#
gage
Übung 5.1 Geben Sie ein mögliches Entity-Relationship Modell für dieses Datenbankschema an.
Übung 5.2 Geben Sie SQL-DDL Statements zum Anlegen folgender Views an:
a) Einen View BilligFilm mit Filmnr, Titel, Jahr, Regisseur, Kosten und Personnr der beteiligten Schauspieler aller Filme mit Kosten unter 100.000 Euro.
b) Einen View Regisseur mit Personnr, Name und Geschlecht der Personen, die bei mindestens einem Film Regisseur waren.
c) Einen View SuperStar mit Personnr, Name, Geschlecht und maximaler Gage der Personen, die für mindestens einen Film mehr als 500.000 Euro Gage bekommen haben.
Übung 5.3 Welche der Views aus Übung 5.2 sind gemäß SQL2 änderbar? Wenn nicht: warum nicht?
Gibt es für die laut SQL2 nicht änderbaren Views plausible Regeln, wie diese Views doch
änderbar wären.
Übung 5.4 Formulieren Sie die folgenden Abfragen mithilfe der in Übung 5.2 definierten Views,
ohne die Basistabellen zu verwenden:
a) Welche Superstars haben schon mal an einem Film mit Kosten unter 100.000 Euro mitgewirkt?
b) Welche Person hat schon mal eine Gage über 1 Mio Euro erhalten?
c) Welche weiblichen Superstars waren auch als Regisseur tätig?