AG Datenbanken und Informationssysteme 5. Übungsblatt

Datenbankanwendung
AG Datenbanken und Informationssysteme
WS 2008/2009 – Lösungsvorschläge zu Übungsblatt 5
a) E/R-Diagramm für das Anwendungsszenario
Wintersemester 2008 / 2009
Bezeichnung
Prof. Dr.-Ing. Dr. h. c. Theo Härder
Fachbereich Informatik
Technische Universität Kaiserslautern
http://wwwlgis.informatik.uni-kl.de/cms/
KID
Name
5. Übungsblatt
Altersgruppe
n
1
Kunde
n
n
Aufgabe 1: Entwurf eines Data Warehouse
Name
Für den aus dem folgenden Anwendungsszenario hervorgehenden Datenbestand eines Unternehmens
soll ein Data Warehouse zur Analyse der Verkaufzahlen erstellt werden.
Preis
Das Unternehmen erfasst alle Kunden mit einer eindeutigen ID, deren Namen und Geburtsdatum. Jeder Kunde wird einer Altersgruppe (z. B. Teenager) zugeteilt. Die angebotenen Produkte werden mit
einer Produkt-ID, Namen und einem Preis gespeichert. Jedes Produkt wird zusätzlich mit einer Produktgruppe klassifiziert. Das Unternehmen besitzt zahlreiche Filialen. Für jede Filiale ist der Ort gespeichert, der wiederum einem der Gebiete Süd, Ost, West oder Nord zugeordnet ist. Der Verkauf
eines Produkts an einen Kunden in einer Filiale wird zusammen mit dem Datum in der Unternehmensdatenbank abgelegt.
a) Erstellen Sie für das beschriebene Anwendungsszenario ein E/R-Diagramm.
b) Bilden Sie das E/R-Diagramm mit SQL auf ein relationales Datenbankschema ab.
c) Erstellen Sie mit SQL das Stern-Schema für ein Data Warehouse, in das die Daten aus der Datenbank von b) geladen und mit dem die in d) aufgeführten Fragen beantwortet werden können.
d) Formulieren Sie die folgenden Anfragen in SQL auf dem Datenbankschema des Data Warehouse:
(1) Wieviele Produkte wurden im ersten Quartal verkauft?
(2) Wieviele Produkte wurden davon in der fünften Kalenderwoche verkauft?
Bezeichnung
Name
1
Geburtsdatum
m
Für die Übung am Donnerstag, 27. N ovember 2008
von 15:30 bis 17:00 Uhr in 13/222.
PID
Ort
1
p
Filiale
Datum
n
Gebiet
Name
FID
Produkt
1
n
Produktgruppe
b) Abbildung des E/R-Diagramms auf ein SQL-Schema
CREATE TABLE altersgruppe
(
bezeichnung VARCHAR(25) PRIMARY KEY
)
CREATE TABLE kunde
(
kid INTEGER PRIMARY KEY,
name VARCHAR(50) NOT NULL,
geburtsdatum DATE NOT NULL,
gruppe VARCHAR(25) NOT NULL REFERENCES altersgruppe(bezeichnung)
)
(3) Wieviele Produkte haben Kunden der Altersgruppe ‘Teenager’ im Gebiet ‘Süd’ gekauft?
(4) Wie hoch ist das Durchschnittsalter aller Kunden der Altersgruppe ‘Rentner’, die im Gebiet
‘Nord’ Produkte der Gruppe ‘Mobiltelefon’ außerhalb der Weihnachtszeit gekauft haben?
Lösung:
CREATE TABLE produktgruppe
(
bezeichnung VARCHAR(25) PRIMARY KEY
)
CREATE TABLE produkt
(
pid INTEGER PRIMARY KEY,
name VARCHAR(50) NOT NULL,
preis DECIMAL(6,2) NOT NULL,
gruppe VARCHAR(25) NOT NULL REFERENCES produktgruppe(bezeichnung)
)
CREATE TABLE gebiet
(
name VARCHAR(20) PRIMARY KEY
Seite 1
Seite 2
! Es kann durchaus vorkommen, dass die Lösungsvorschläge fehlerhaft oder unvollständig sind !
! Es kann durchaus vorkommen, dass die Lösungsvorschläge fehlerhaft oder unvollständig sind !
Datenbankanwendung
WS 2008/2009 – Lösungsvorschläge zu Übungsblatt 5
Datenbankanwendung
WS 2008/2009 – Lösungsvorschläge zu Übungsblatt 5
)
)
CREATE TABLE ort
(
name VARCHAR(50) PRIMARY KEY,
gebiet VARCHAR(20) NOT NULL REFERENCES gebiet(name)
)
CREATE TABLE dwfiliale
(
fid INTEGER PRIMARY KEY,
ort VARCHAR(50) NOT NULL,
gebiet VARCHAR(20) NOT NULL
)
CREATE TABLE filiale
(
fid INTEGER PRIMARY KEY,
ort VARCHAR(50) NOT NULL REFERENCES ort(name)
)
CREATE TABLE kauf
(
datum DATE NOT NULL,
kid INTEGER NOT NULL REFERENCES kunde(kid),
pid INTEGER NOT NULL REFERENCES produkt(pid),
fid INTEGER NOT NULL REFERENCES filiale(fid)
)
CREATE TABLE dwkauf
(
datum DATE NOT NULL REFERENCES dwzeit(datum),
kid INTEGER NOT NULL REFERENCES dwkunde(kid),
pid INTEGER NOT NULL REFERENCES dwprodukt(pid),
fid INTEGER NOT NULL REFERENCES dwfiliale(fid)
)
d) Anfragen an das Data Warehouse
(1) SELECT COUNT(*)
FROM dwkauf k, dwzeit z
WHERE k.datum=z.datum AND z.quartal=1
(2) SELECT COUNT(*)
FROM dwkauf k, dwzeit z
WHERE k.datum=z.datum AND z.kw=5
c) SQL-Schema für das Data Warehouse
dwzeit
dwkunde
datum
kid
dwkauf
pid
dwprodukt
fid
dwfiliale
(3) SELECT COUNT(*)
FROM dwkauf ka, dwkunde ku, dwfiliale f
WHERE ka.kid=ku.kid AND ka.fid=f.fid AND ku.gruppe=’Teenager’ AND f.gebiet=’Süd’
(4) SELECT AVG(ku.alter)
FROM dwkauf ka, dwzeit z, dwkunde ku, dwfiliale f, dwprodukt p
WHERE ka.datum=z.datum AND ka.kid=ku.kid AND ka.fid=f.fid AND ka.pid=p.pid
AND ku.gruppe=’Rentner’ AND f.gebiet=’Nord’ AND p.gruppe=’Mobiltelefon’
AND z.saison <> ‘Weihnachten’
CREATE TABLE dwzeit
(
datum DATE PRIMARY KEY,
kw INTEGER NOT NULL,
quartal INTEGER NOT NULL,
saison VARCHAR(30) NOT NULL
)
CREATE TABLE dwkunde
(
kid INTEGER PRIMARY KEY,
alter INTEGER NOT NULL,
gruppe VARCHAR(25) NOT NULL
)
CREATE TABLE dwprodukt
(
pid INTEGER PRIMARY KEY,
gruppe VARCHAR(25) NOT NULL
Seite 3
Seite 4
! Es kann durchaus vorkommen, dass die Lösungsvorschläge fehlerhaft oder unvollständig sind !
! Es kann durchaus vorkommen, dass die Lösungsvorschläge fehlerhaft oder unvollständig sind !
Datenbankanwendung
WS 2008/2009 – Lösungsvorschläge zu Übungsblatt 5
Aufgabe 2: SQL-Anfragen und Views am Beispiel „Personal-DB“
Gegeben sei die folgende Datenbank, die von der Finanzabteilung zur Berechnung der Löhne und Gehälter der Mitarbeiter (MA) der verschiedenen Abteilungen (ABT) genutzt wird.
MA
(MANR, MANAME, MAVORNAME, ABTNR, FIRMENZUGEHOERIGKEIT,
KINDER, STEUERKLASSE, GEHALT, KRANKENKASSE, BEITRAGSSATZ)
ABT (ABTNR, ABTNAME, ABTLEITER, ABTORT)
ABTLEITER hat denselben Wertebereich wie MANR und ist Fremdschlüssel. Zur Erstellung verschiedener Statistiken sollen dynamische Sichten erzeugt werden, und zwar:
a) Eine Sicht, die die Abteilungsnummer, den Abteilungsnamen, die Anzahl der Mitarbeiter der Abteilung, den Durchschnitt der Firmenzugehörigkeit und des Gehalts, das höchste Gehalt der Abteilung und die Differenz zwischen dem höchsten und niedrigsten Gehalt in der Abteilung umfasst.
b) Eine Sicht, die, gestaffelt nach Krankenkasse und Kinderzahl, den durchschnittlichen Beitragssatz
für Mitglieder von Abteilungen in Frankfurt, München oder Stuttgart beinhaltet.
c) Eine Sicht, die Name, Vorname und Gehalt der Mitarbeiter enthält, die in Abteilungen arbeiten,
deren Durchschnittsgehalt größer als 50.000 ist.
d) Eine Sicht, die die Daten der Mitarbeiter in Steuerklasse 1 enthält, und eine weitere, die nur Mitarbeiter in Steuerklasse 1 mit mehr als 5 Jahren Firmenzugehörigkeit enthält.
e) Formulieren Sie auf der ersten der beiden letzten Sichten die Anfrage nach den Daten aller Mitarbeiter, deren Abteilungsleiter ’Müller’ heißt.
f) Was passiert bei Änderungen auf Sichten, die Aggregatfunktionen beinhalten?
Datenbankanwendung
WS 2008/2009 – Lösungsvorschläge zu Übungsblatt 5
Lösung:
a) Eine Sicht, die die Abteilungsnummer, den Abteilungsnamen, die Anzahl der Mitarbeiter der Abteilung, den Durchschnitt der Firmenzugehörigkeit und des Gehalts, das höchste Gehalt der Abteilung und die Differenz zwischen dem höchsten und niedrigsten Gehalt in der Abteilung umfasst.
CREATE VIEW ABTSTATISTIK
(ABTNR, ABTNAME, MITANZ, DFIRMENZUGEHOERIGKEIT, DGEHALT, MAXGEHALT,
DIFFGEHALT )
AS
SELECT ABT.ABTNR, ABTNAME, COUNT(MANR),
AVG (FIRMENZUGEHOERIGKEIT), AVG(GEHALT),
MAX (GEHALT), MAX(GEHALT) - MIN(GEHALT)
FROM
MA, ABT
WHERE MA.ABTNR = ABT.ABTNR
GROUP BY ABT.ABTNR, ABTNAME
b) Eine Sicht, die, gestaffelt nach Krankenkasse und Kinderzahl, den durchschnittlichen Beitragssatz
für Mitglieder von Abteilungen in Frankfurt, München oder Stuttgart beinhaltet.
CREATE VIEW KK_KINDER_BEITRAGSSATZ_STAEDTE
( KRANKENKASSE, KINDER, DBEITRAGSSATZ )
AS
SELECT KRANKENKASSE, KINDER, AVG(BEITRAGSSATZ)
FROM MA, ABT
WHERE MA.ABTNR = ABT.ABTNR
AND ABT.ABTORT IN (’Frankfurt’, ’München’, ’Stuttgart’ )
GROUP BY KRANKENKASSE, KINDER
c) Eine Sicht, die Name, Vorname und Gehalt der Mitarbeiter enthält, die in Abteilungen arbeiten,
deren Durchschnittsgehalt größer als 50.000 ist.
CREATE VIEW ANG_IN_TOP_ABT ( MANAME, MAVORNAME, GEHALT )
AS
SELECT MANAME, MAVORNAME, GEHALT
FROM MA
WHERE ABTNR IN
( SELECT ABT.ABTNR
FROM MA, ABT
WHERE MA.ABTNR = ABT.ABTNR
GROUP BY ABT.ABTNR
HAVING AVG(GEHALT) > 50000 )
d) Eine Sicht, die die Daten der Mitarbeiter in Steuerklasse 1 enthält, und eine weitere, die nur Mitarbeiter in Steuerklasse 1 mit mehr als 5 Jahren Firmenzugehörigkeit enthält.
CREATE
SELECT
FROM
WHERE
VIEW STKL1 AS
*
MA
STEUERKLASSE = 1
CREATE
SELECT
FROM
WHERE
VIEW STKL1_FZ5 AS
*
MA
STEUERKLASSE = 1 AND
FIRMENZUGEHOERIGKEIT > 5
Seite 5
Seite 6
! Es kann durchaus vorkommen, dass die Lösungsvorschläge fehlerhaft oder unvollständig sind !
! Es kann durchaus vorkommen, dass die Lösungsvorschläge fehlerhaft oder unvollständig sind !
Datenbankanwendung
CREATE
SELECT
FROM
WHERE
WS 2008/2009 – Lösungsvorschläge zu Übungsblatt 5
VIEW STKL1_FZ5 AS
*
STKL1
FIRMENZUGEHOERIGKEIT > 5
e) Formulieren Sie auf der ersten der beiden letzten Sichten die Anfrage nach den Daten aller Mitarbeiter, deren Abteilungsleiter ’Müller’ heißt.
SELECT STKL1.*
FROM
STKL1, ABT, MA
WHERE STKL1.ABTNR = ABT.ABTNR AND
ABT.ABTLEITER = MA.MANR AND
MA.MANAME = ’MÜLLER’
f) Was passiert bei Änderungen auf Sichten, die Aggregatfunktionen beinhalten?
Sichten mit Aggregatfunktionen lassen sich nicht ändern.
Datenbankanwendung
WS 2008/2009 – Lösungsvorschläge zu Übungsblatt 5
Aufgabe 3: Kostenmodelle für die Selektionsoperation
Gegeben sei eine Tabelle R mit den Attributen A1, A2, A3, ..., An, die zusammenhängend in den Seiten des Segments S gespeichert ist.
R ( A1, A2, A3, ..., An )
Das Segment S habe MS=104 Seiten. Die Tabelle R habe NR=105 Sätze und ggf. (für die entsprechenden Aufgabenstellungen) einen Cluster-Faktor cR=50.
Weiterhin seien die Indizes IR(A1) mit jA1=100 und IR(A2) mit jA2=10 für die Attribute A1 und A2
angelegt. Bei Indizes sind jeweils als B*-Bäume mit der Höhe hB=2 und NB=100 Blattseiten realisiert.
a) Wie teuer (Anzahl der Seitenzugriffe) ist die Auswertung der SQL-Anfrage
SELECT *
FROM
R
WHERE A3=’x’
(1) bei einem Tabellen-Scan?
(2) bei Nutzung des Indexes IR(A1)?
(3) bei Nutzung des Indexes IR(A2) mit Cluster-Bildung?
(4) wenn die Tabelle als Hash-Struktur mit A3 als Primärschlüssel angelegt ist?
b) A1 habe 100 Werte, die von 1 bis 100 gleichverteilt vorkommen (jA1=100). Wie teuer ist die Auswertung der SQL-Anfrage
SELECT *
FROM
R
WHERE A1>50
(1) bei Nutzung des Indexes IR(A1)?
(2) bei Annahme einer Cluster-Bildung bei IR(A1)?
(3) ohne Indexnutzung?
c) Welche Kosten verursacht die SQL-Anfrage
SELECT *
FROM
R
WHERE A1=50 AND A2=10
(1) bei Nutzung von IR(A1) und IR(A2) jeweils ohne Cluster-Bildung?
(2) bei gemeinsamer Nutzung von IR(A1) und IR(A2) mit Cluster-Bildung?
(3) bei Zugriff nur über IR(A2) mit Cluster-Bildung?
(4) bei Zugriff nur über IR(A1) mit Cluster-Bildung?
Seite 7
Seite 8
! Es kann durchaus vorkommen, dass die Lösungsvorschläge fehlerhaft oder unvollständig sind !
! Es kann durchaus vorkommen, dass die Lösungsvorschläge fehlerhaft oder unvollständig sind !
Datenbankanwendung
WS 2008/2009 – Lösungsvorschläge zu Übungsblatt 5
Lösung:
Datenbankanwendung
WS 2008/2009 – Lösungsvorschläge zu Übungsblatt 5
Aufgabe 4: CHECK OPTION bei Sichten in SQL
a) Wie teuer (Anzahl der Seitenzugriffe) ist die Auswertung der SQL-Anfrage
SELECT *
FROM
R
WHERE A3=’x’
Gegeben seien folgende SQL-Anweisungen:
CREATE TABLE T (S1 INT, S2 INT, S3 INT, S4 INT, S5 INT);
CREATE VIEW V1 AS
SELECT * FROM T WHERE S1=1;
CREATE VIEW V2 AS
SELECT * FROM V1 WHERE S2=2 WITH LOCAL CHECK OPTION;
(1) bei einem Tabellen-Scan?
Ca1 = MS = 104 (Seiten)
CREATE VIEW V3 AS
SELECT * FROM V2 WHERE S3=3;
(2) bei Nutzung des Indexes IR(A1)?
Ca2 = hB + NB - 1+ NR = 2 + 99 + 105 (Seiten)
CREATE VIEW V4 AS
SELECT * FROM V3 WHERE S4=4 WITH CASCADED CHECK OPTION;
(3) bei Nutzung des Indexes IR(A2) mit Cluster-Bildung?
Ca3 = hB + NB - 1 + NR / cR = 2 + 99 + 2 ⋅ 103 (Seiten)
CREATE VIEW V5 AS
SELECT * FROM V4 WHERE S5=5;
(4) wenn die Tabelle als Hash-Struktur mit A3 als Primärschlüssel angelegt ist?
Ca4 = 1
b) A1 habe 100 Werte, die von 1 bis 100 gleichverteilt vorkommen (jA1=100). Wie teuer ist die Auswertung der SQL-Anfrage
SELECT *
FROM
R
WHERE A1>50
Ist die Ausführung der nachfolgenden INSERT-Anweisungen erfolgreich? Geben Sie jeweils eine
kurze Begründung an.
a) INSERT INTO V1 VALUES (2, 1, 3, 2, 5);
b) INSERT INTO V2 VALUES (2, 1, 3, 2, 5);
c) INSERT INTO V2 VALUES (2, 2, 3, 2, 5);
d) INSERT INTO V3 VALUES (2, 2, 4, 2, 5);
e) INSERT INTO V3 VALUES (1, 3, 3, 2, 5);
(1) bei Nutzung des Indexes IR(A1)?
Cb1 = hB + NB / 2 + (NR / jA1) ⋅ jA1 / 2 = 2 + 50 + 105/2
2)
(NB-1 vernachlässigt wg. /
(2) bei Annahme einer Cluster-Bildung bei IR(A1)?
Cb2 = hB + NB / 2 + (NR / jA1) ⋅ jA1 / (2 ⋅ cR) = 2 + 50 + 103
f) INSERT INTO V4 VALUES (2, 2, 3, 2, 5);
g) INSERT INTO V4 VALUES (2, 1, 3, 4, 5);
h) INSERT INTO V4 VALUES (1, 2, 2, 4, 5);
i) INSERT INTO V4 VALUES (2, 2, 3, 4, 5);
j) INSERT INTO V5 VALUES (1, 2, 3, 4, 6);
(3) ohne Indexnutzung?
Cb3 = MS = 104
k) INSERT INTO V5 VALUES (1, 2, 4, 4, 5);
c) Welche Kosten verursacht die SQL-Anfrage
SELECT *
FROM
R
WHERE A1=50 AND A2=10
(1) bei Nutzung von IR(A1) und IR(A2) jeweils ohne Cluster-Bildung?
Cc1 = hB + NB / jA1 + hB + NB / jA2 + NR / (jA1 ⋅ jA2) = 2 + 1 + 2 + 10 + 105 / 103 = 115
(2) bei gemeinsamer Nutzung von IR(A1) und IR(A2) mit Cluster-Bildung?
Cc2 = hB + NB / jA1 + hB + NB / jA2 + NR / (jA1 ⋅ jA2 ⋅ cR) = 2 + 1 + 2 + 10 + 105 / (103 ⋅ 50)
= 17
(3) bei Zugriff nur über IR(A2) mit Cluster-Bildung?
Cc3 = hB + NB / jA2 + NR / (jA2 ⋅ cR) = 12 + 105 / (5 ⋅ 102) = 212
(4) bei Zugriff nur über IR(A1) mit Cluster-Bildung?
Cc4 = hB + NB / jA1 + NR / (jA1 ⋅ cR) = 3 + 20 = 23
Seite 9
Seite 10
! Es kann durchaus vorkommen, dass die Lösungsvorschläge fehlerhaft oder unvollständig sind !
! Es kann durchaus vorkommen, dass die Lösungsvorschläge fehlerhaft oder unvollständig sind !
Datenbankanwendung
WS 2008/2009 – Lösungsvorschläge zu Übungsblatt 5
Lösung:
a) INSERT INTO V1 VALUES (2, 1, 3, 2, 5);
Erfolgreich, da CHECK OPTION in V1 nicht spezifiziert ist.
b) INSERT INTO V2 VALUES (2, 1, 3, 2, 5);
Nicht erfolgreich aufgrund der CHECK OPTION in V2 (S2=2).
c) INSERT INTO V2 VALUES (2, 2, 3, 2, 5);
Erfolgreich aufgrund der LOCAL CHECK OPTION in V2 (S2=2), die Einschränkung in V1 wird
nicht berücksichtigt.
d) INSERT INTO V3 VALUES (2, 2, 4, 2, 5);
Erfolgreich, da CHECK OPTION in V3 nicht definiert ist, die Bedingung in V2 erfüllt ist und die
Bedingung in V1 aufgrund der CHECK OPTION in V2 nicht berücksichtigt wird.
e) INSERT INTO V3 VALUES (1, 3, 3, 2, 5);
Nicht erfolgreich, da die Bedingung in V2 nicht erfüllt ist.
f) INSERT INTO V4 VALUES (2, 2, 3, 2, 5);
Nicht erfolgreich, da die Bedingung in V4 nicht erfüllt ist und CHECK OPTION definiert ist.
g) INSERT INTO V4 VALUES (2, 1, 3, 4, 5);
Nicht erfolgreich, da die Bedingung in V2 aufgrund der CASCADED CHECK OPTION in V4
nicht erfüllt ist.
h) INSERT INTO V4 VALUES (1, 2, 2, 4, 5);
Nicht erfolgreich, da die Bedingung in V3 aufgrund der CASCADED CHECK OPTION in V4
nicht erfüllt ist.
i) INSERT INTO V4 VALUES (2, 2, 3, 4, 5);
Nicht erfolgreich, da die Bedingung in V1 aufgrund der CASCADED CHECK OPTION in V4
nicht erfüllt ist.
j) INSERT INTO V5 VALUES (1, 2, 3, 4, 6);
Erfolgreich, da CHECK OPTION in V5 nicht definiert ist und alle Bedingungen in V4, V3, V2,
und V1 erfüllt sind.
k) INSERT INTO V5 VALUES (1, 2, 4, 4, 5);
Nicht erfolgreich, da die Bedingung in V3 aufgrund der CASCADED CHECK OPTION in V4
nicht erfüllt ist.
Seite 11
! Es kann durchaus vorkommen, dass die Lösungsvorschläge fehlerhaft oder unvollständig sind !