Datenbanksysteme I - Institut für Informatik

Universität Augsburg, Institut für Informatik
Dr. W.-T. Balke
M. Endres, A. Huhn, T. Preisinger
WS 2006/2007
04. Dez. 2006
Lösungsblatt 6
Datenbanksysteme I
Aufgabe 1: SQL-DDL
Erstellen der Relationen:
create table Interpret (
IIN integer primary key,
Name varchar(50),
Vorname varchar(50));
create table Kategorie (
Name varchar(20) primary key,
Umsatz integer);
create table Song (
Titel varchar(50) primary key,
Haeufigkeit integer not null);
create table SingleCDs (
IIN integer,
Titel varchar(50),
Spieldauer varchar(50),
Bestand integer not null,
Kat_Name varchar(20) not null,
constraint PK primary key (IIN, Titel),
constraint FKCK
foreign key (Kat_Name)
references Kategorie(Name),
constraint FKCI
foreign key (IIN)
references Interpret on delete cascade,
constraint FKCS
foreign key (Titel)
references Song
);
Einfügen der Daten:
insert
insert
insert
insert
insert
insert
insert
insert
insert
into
into
into
into
into
into
into
into
into
Song
Song
Song
Song
Song
Song
Song
Song
Song
values(’All I wanna do’,3 );
values(’All you zombies’, 6);
values(’Johnny B.’, 2);
values(’Satisfied’, 8);
values(’All or nothing’, 6);
values(’Keep on running’, 5);
values(’Bad boy’, 2);
values(’Day by day’, 17);
values(’Sweet Lullaby’, 1);
1
insert
insert
insert
insert
insert
insert
into
into
into
into
into
into
Interpret
Interpret
Interpret
Interpret
Interpret
Interpret
values(212
values(716
values(629
values(312
values(117
values(224
,
,
,
,
,
,
’Crow’, ’Sheryl’);
’Hooters’, null);
’Marx’, ’Richard’);
’Milli Vanilli’, null);
’Harrow’, ’Den’);
’Deep Fores’, null);
insert into Kategorie values(’Rock’ ,40 );
insert into Kategorie values(’Pop’ , 55);
insert into Kategorie values(’Ethno’ , 5);
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
into
into
into
into
into
into
into
into
into
into
SingleCDs
SingleCDs
SingleCDs
SingleCDs
SingleCDs
SingleCDs
SingleCDs
SingleCDs
SingleCDs
SingleCDs
values(212,
values(716,
values(716,
values(716,
values(629,
values(312,
values(312,
values(117,
values(117,
values(224,
’All I wanna do’, 202 , 7, ’Rock’);
’Day by day’, 197, 12, ’Rock’);
’All you zombies’, 253,15 , ’Rock’);
’Johnny B.’, 233, 19, ’Rock’);
’Satisfied’,243 , 3, ’Rock’);
’All or nothing’, 198, 7, ’Pop’);
’Keep on running’,188 , 9, ’Pop’);
’Bad boy’, null, 22, ’Pop’);
’Day by day’, 214, 4, ’Pop’);
’Sweet Lullaby’, 512, 17, ’Ethno’);
Die Daten müssen entsprechend ihrer Fremdschlüsselbeziehungen in der richtigen Reihenfolge
eingefügt werden.
Aufgabe 2: SQL-DML
a) Der Wert NULL in Spieldauer wird hier als ’neutraler Wert’ 0 angesehen. NULL-Werte dürften
aber in dieser Berechnung nicht mit einfließen. Deshalb die verbesserte Query:
select Kat_Name, sum(Spieldauer) / count(*) as DSpieldauer
from SingleCDs
where Spieldauer IS NOT NULL
group by Kat_Name;
b) Da das Löschen kaskadiert durchgeführt wird, reicht das Löschen von ’Milli Vanilli’ in der
Relation Interpret:
delete from Interpret where Name=’Milli Vanilli’;
c) Lösung:
update SingleCDs
set Spieldauer = 200
where Titel = ’Day by day’
and IIN = (
select IIN
from Interpret
where Name = ’Hooters’);
2
d) Löschen von Bestand:
alter table SingleCDs drop column Bestand;
e) Hinzufügen von Preis:
alter table SingleCDs add Preis Integer;
Aufgabe 3: SQL-Views
a) Anlegen des Views:
create view FuerKunden(Titel, Kat_Name, Preis, Name, Vorname) as
select s.titel, s.kat_Name, s.preis, i.name, i.vorname from
SingleCDs s, Interpret i where s.IIN = i.IIN;
b) Beispiel für Update:
update FuerKunden set Preis = 3 where Titel=’All I wanna do’;
c) Der View von Aufgabe a) benötigt noch ein ’WITH READ ONLY’ um sicherzustellen, das
keine Daten geändert bzw. hinzugefügt werden.
Aufgabe 4: Zyklische Fremdschlüsselbeziehungen
create table chicken(
cID integer primary key
);
create table egg(
eIC integer primary key,
cID integer
constraint FKEC
references chicken deferrable
);
alter table chicken
add eID integer
constraint FKCE
references egg deferrable;
set constraints FKEC, FKCE deferred;
insert into chicken values (1,2);
insert into egg values (2,1);
commit;
3