02/2008 - FH Dortmund

Klausur PI Datenbanken I vom 24.2.2008
Praktische Informatik (Krägeloh)
Name:
MatrikelNr.:
Versehen Sie jedes Aufgabenblatt mit Ihrem Namen und geben es mit ab!
Aufgabe 1: (30 Punkte)
Seite 1
a) Beschreiben Sie die mittlere Ebene des 3-Ebenen-Konzeptes nach ANSIISparc ?
Konzeptuelle Ebene, Repräsentiert die logische Gesamtsicht aller relevanter
Daten und ihrer Beziehungen untereinander. Benötigt Abbildung der realen
Welt auf ein Datenmodell mit Abstraktion von den konkreten Dateninhalten
b) Wann ist ein Trigger rekursiv ?
Wenn er im Trigger-Rumpf ein Ereignis auslöst, auf das er selbst reagiert.
c) Welche Vergleichsformen/Operatoren sind erlaubt, wenn eine Sub-Query in
einer Where-Klausel ein mengenwertiges Ergebnis zurückgeben kann ?
in , <VGLOP> any, <vglop> all, exists
d) Welche Standard-Privilegien kannte Oracle bis zur Version 7.0 ?
DBA, Resource, Connect
e) Was bedeutet es, wenn man einem Nutzer durch einen grant-Befehl ein Recht
erteilt und den Befehl mit der Option „with grant option“ versieht
Er darf dieses Recht an anderer Nutzer weitergeben.
f) Wann müssen Informationen zu einem Objekt der Umwelt in Relationalen
Modell zwangsweise auf mehrere Relationen verteilt werden ?
Wenn einem Objekt mehrwertige Attribute zugeordnet werden können.
g) Was beinhalten die Redo-Informationen im Logbuch ?
Die Redo-Information gibt an, wie abgeschlossene, jedoch verlorene
Änderungen nachvollzogen werden können
Klausur PI Datenbanken I vom 15.2.2007
Name:
Praktische Informatik (Krägeloh)
MatrikelNr.:
Versehen Sie jedes Aufgabenblatt mit Ihrem Namen und geben es mit ab!
Seite 2
h) Welche Aufgabe hat der Data-Manager in einem DBMS ?
Der Data-Manager bildet die Schnittstelle zu den Geräte- und
Sekundärspeichermanager und führt die physischen Zugriffe aus
i) Kennzeichnen Sie die Eigenschaften dynamischer und statischer
Integritätsbedingungen
Statische Bedingungen müssen zu jedem Zeitpunkt bei jeder Datenänderung
eingehalten werden.
Dynamische Integritätsbedingungen müssen am Ende von
Zustandsänderungen wieder eingehalten werden.
j) Wann darf man constraints nicht als Attribut-Constraints schreiben ?
Generell: wenn sie sich auf mehr als ein Attribut beziehen. Zusätzlich bei
Oracle: alle check-constraints
Beantworten Sie die nachfolgenden Fragen mittels SQL-Anweisungen auf der Basis
der anhängenden Datenbank .
Aufgabe 2: (14 Punkte)
Ermitteln Sie je Artikel die Artikelnummer, die Bezeichnung und die Anzahl seiner
Vorkommnisse in Auftragspositionen des Jahres 2007 (Basis auftrag_datum). Wenn
ein Artikel nicht vorkommt, geben Sie stattdessen die Zeichenfolge „Leermeldung“
aus. Nennen Sie diese Spalte „Anzahl Verkäufe“
Select a.artikel_nr, a.bezeichnung, case when count(*)=0 then ‚Leermeldung’ else
cast(count(*) as char(12)) end ‚Anzahl Verkäufe’
from artikel a join
(auftraege b join auftragspositionen c on b.auftrag_nr=c.auftrag_nr)
on a.artikel_nr=c.artikel_nr
where extract (year from b.auftrag_datum) = 2007
group by 1,2
Aufgabe 3: (10 Punkte)
Geben Sie den (die) Kunden mit ihren Adressdaten aus, die im Jahr 2007 die
Rechnung mit dem höchsten Rechnungsbetrag erhalten haben.
Select kunden.kunde_nr, name, strasse, plz_strasse, land, ort
From kunden join rechnungen on kunden.kunde_nr = rechnungen.kunde_nr
Where extract (year from rechnungdatum)=2007 and rechnung_betrag =
(select max(rechnung_betrag) from rechnungen where extract (year from
rechnungdatum) = 2007)
Klausur: Relationale Datenbanksysteme/SQL
15.02.2007
Seite 2
Klausur PI Datenbanken I vom 15.2.2007
Name:
Praktische Informatik (Krägeloh)
MatrikelNr.:
Versehen Sie jedes Aufgabenblatt mit Ihrem Namen und geben es mit ab!
Seite 3
Aufgabe 4: (15 Punkte)
Geben Sie die durchschnittliche Bestellmenge (Feld Menge in Tabelle Bestellung) je
Warengruppe aller Artike der Warengruppen 1,2 und 7 zusammen mit der
Warengruppe aus. Berücksichtigen Sie nur Bestellungen der Jahre 2005-2007.
Warengruppen, deren Artikel nicht bestelt wurden, sollen nicht ausgegeben werden.
Select a.warengruppe, avg(menge)
from artikel a join bestellung l on a.artikel_nr=l.artikel_nr
where a.warengruppe in (1,2,7) and extract(year from bestelldatum between 2005
and 2007)
group by 1
having avg(menge) > 0
Aufgabe 5: (14 Punkte)
Führen Sie je Artikel die Artikelnummer, die Bezeichnung sowie den Lieferanten
(Nummer, Name) mit dem größten und dem kleinsten Preis (Tabelle Lieferprogramm)
zusammen mit diesen Preisen auf. Verwenden Sie Views und keine Subselects
(keine Punkte) !!!
Create view minpreis (artikel_nr, lieferant_nr, name, minp) as
select artikel-nr, lieferant_nr , name , preis from lieferprogramm l1 join lieferanten l
on l.lieferantennummer= l1.lieferant_nr where preis = (select min(preis) from
lieferprogramm l2 where l1.artikel_nr=l2.artikel_nr)
Create view maxpreis (artikel_nr, lieferant_nr, name, maxp) as select artikel-nr,
lieferant_nr , name, preis from lieferprogramm l1 l1 join lieferanten l on
l.lieferantennummer= l1.lieferant_nr where preis = (select max(preis) from
lieferprogramm l2 where l1.artikel_nr=l2.artikel_nr)
Select a.artikel_nr, a.bezeichnung, i.lieferant_nr, i.name, i.minp, m.lieferant_nr,
m.name, m.maxp from (artikel a join minpreis i on i.artikel_nr=a.artikel_nr) join
maxpreis m on m.artikel_nr=a.artikel_nr
Klausur: Relationale Datenbanksysteme/SQL
15.02.2007
Seite 3
Klausur PI Datenbanken I vom 15.2.2007
Name:
Praktische Informatik (Krägeloh)
MatrikelNr.:
Versehen Sie jedes Aufgabenblatt mit Ihrem Namen und geben es mit ab!
Seite 4
Aufgabe 6: (7 Punkte)
Legen Sie zwei Rollen (rolle1, rolle2) für die Datenbank an. Ferner die Nutzer N1, N2
und N3 (ohne Passwörter). Rolle1 darf bei Kunden alles außer Löschen, Lieferanten
alles außer Löschen und Ändern und bei Rechnungen nur Lesen. Rolle2 darf
Rechnung lesen, einfügen, nicht löschen aber ändern.
Ordnen Sie N1 und N2 der rolle1 zu N3 der rolle2. N2 darf aber keine Kunden
ändern.
Create role rolle1
Create role rolle2
Grant all on kunde, lieferant to rolle1
Revoke delete on kunde from rolle1
Revoke delete,update on lieferant from rolle1
Grant select on rechnungen to rolle1
Grant select, insert, update on rechnungen to rolle2
Grant rolle1 to n1,n2
Grant rolle2 to n3
Revoke update on kunden from n3
Klausur: Relationale Datenbanksysteme/SQL
15.02.2007
Seite 4
Klausur PI Datenbanken I vom 15.2.2007
Name:
Praktische Informatik (Krägeloh)
MatrikelNr.:
Versehen Sie jedes Aufgabenblatt mit Ihrem Namen und geben es mit ab!
Seite 5
Aufgabe 7: (10 Punkte)
Seien die folgenden Daten einer Vereinsanwendung gegeben. Implementieren Sie
die Tabellen (create table) und die genannten Integritäten (alter table add constraint
...)
Mitglied
Nummer
Name
Geburtsdatum
Eintrittsdatum
KrankenVSNR
Monatsbeitrag
number
char(20)
date
date
number
number
primärschlüssel
nicht leer
> 01.01.1940
> Geburtsdatum
eindeutig
nicht leer
Beitragszahlung
Mitgliedsnummer
Relative Zahlnummer
Zahldatum
BLZ
Kontonummer
number
number
date
number
number
Fremdschlüssel Mitglied
Nicht leer
Nicht leer
>0
>0
Primärschlüssel: Mitgliedsnummer + Relative Zahlnummer
Eindeutig: BLZ + Kontonummer
Löschen, wenn Mitglied gelöscht wird.
Create table mitglied
(nummer number,
name char(20) not null,
geburtsdatum date,
krankenvsnr number not null,
monatsbeitrag number not null)
alter table mitglied add constraint c1 primary key (nummer)
alter table mitglied add constraint c2 check(geburtsdatum > ’01.01.1940’)
alter table mitglied add constraint c3 check (eintrittsdatum > geburtsdatum)
alter table mitglied add constraint c4 unique (krankenvsnr)
create table beitragszahlung
(mitgliedsnummer number,
relative_zahlnummer number not null,
zhaldatum date not null,
blz number,
kontonummer number)
alter table beitragszahlung add constraint c5 primary key (mitgliedsnummer,
relative_zahlnummer)
alter table mitglied add constraint c6 foreign key (mitgliedsnummer) references
mitglied(nummer) on delete cascade;
Klausur: Relationale Datenbanksysteme/SQL
15.02.2007
Seite 5
Klausur PI Datenbanken I vom 15.2.2007
Name:
Praktische Informatik (Krägeloh)
MatrikelNr.:
Versehen Sie jedes Aufgabenblatt mit Ihrem Namen und geben es mit ab!
alter table mitglied add constraint c7 check(blz > 0)
alter table mitglied add constraint c8 check (kontonummer > 0)
alter table mitglied add constraint c9 unique (blz, kontonummer)
Klausur: Relationale Datenbanksysteme/SQL
15.02.2007
Seite 6
Seite 6
Klausur PI Datenbanken I vom 15.2.2007
Name:
Praktische Informatik (Krägeloh)
MatrikelNr.:
Versehen Sie jedes Aufgabenblatt mit Ihrem Namen und geben es mit ab!
Datenbank (durch create-table definiert):
create table lieferanten
(lieferantennummer int,
name varchar(30),
strasse varchar(30),
plz varchar(5),
ort varchar(30));
create table auftraege
(auftrag_nr int,
auftrag_datum datetime,
kunde_nr int,
vertreter_nr int,
rabatt float);
create table artikel
(artikel_nr varchar(27),
bezeichnung varchar(40),
warengruppe int,
verkaufspreis numeric,
einkaufspreis numeric,
bestand_lager int,
bestand_minimum int,
jahresumsatz numeric,
vorjahresumsatz numeric);
create table auftragspositionen
(auftrag_nr int,
position_nr int,
artikel_nr varchar(27),
menge int,
preis numeric,
termin datetime,
gelieferte_menge int,
berechnete_menge int);
create table artikellager
(artikel_nr varchar(27),
lager_nr int,
bestand_lager int);
create table rechnungen
(rechnung_nr int,
rechnung_datum datetime,
kunde_nr int,
auftrag_nr int,
rechnung_betrag numeric,
datum_faellig datetime,
datum_mahnung datetime,
zahlbetrag_gesamt numeric,
zahldatum datetime,
bezahlt smallint);
create table kunden
(kunde_nr int,
name varchar(30),
strasse varchar(30),
plz_strasse varchar(5),
land varchar(3),
ort varchar(30),
kundengruppe int,
branche varchar(30),
vertreter_nr int,
datum_kreditauskunft datetime,
kreditlimit numeric,
saldo_offene_rechnungen numeric,
jahresumsatz numeric,
vorjahresumsatz numeric,
gesperrt bit);
Klausur: Relationale Datenbanksysteme/SQL
Seite 7
create table lieferprogramm
(Lieferant_nr int,
artikel_nr varchar(27),
preis numeric,
lieferzeit int, /* in Arbeitstagen */
datum_letzte_lieferung date)
create table bestellung (
lieferant_nr int,
artikel_nr varchar(27),
menge int,
bestelldatum date)
15.02.2007
Seite 7