Informationssysteme I ¨Ubungsblatt 3

Informationssysteme I
Übungsblatt 3
Christine Pries, Jonas Jacobi, Felix Oppermann (Gruppe 42)
24. November 2004
Übungsblatt 1
1
Aufgabe 1 (Relationsalgebra) (50 Punkte)
1.1 RESU LT AT ←Region =COU N T (V kN r) (V erkaeuf er)
1.2 BEST ELLU N G OKT 2003 ← σDatum≥0 01.10.20030 AN DDatum≤0 31.10.20030 (Bestellung)
RESU LT AT ← πN ame,KdN r (Kunde ./KdN r=Kunde BEST ELLU N G OKT 2003)
1.3 RESU LT AT ← =SU M (V erkauf spreis) (Bestellliste ∗ (σKunde=522 (Bestellung)))
1.4 BEST ELLU N G KU N DE ← Bestellung ∗ Bestellliste ./Artikel=ArtN r. (σBestand≤50 (Artikel))
RESU LT AT ← πKdN r,N ame,Saldo (Kunde ./Kunde=KdN r BEST ELLU N G KU N DE)
1.5 RESU LT AT ← =SU M (M enge) (Bestellliste ./Artikel=ArtN r (σArtKlasse=0 SG0 (Artikel)))
1.6 LET ZT ES DAT U M ← P reis ∗ (ArtN r =M AX(GueltigAb) (σGueltigAb≤0 24.10.20030 (P reis)))
RESU LT AT ← πArtN r,Einkauf spreis (σGueltigAb=M AX GueltigAb (LET ZT ES DAT U M ))
1.7 BEST ELLU N G M AR2003 ← σDatum≥0 01.03.20030 AN DDatum≤0 31.03.20030 (Bestellung)
V ERKAEU F ER M AR2003 ← πV kN r (V erkaeuf er ./V kN r=V erkaeuf er BEST ELLU N G M AR2003)
RESU LT AT ← =COU N T (V kN r) (V ERKAEU F ER M AR2003)
Aufgabe 2 (SQL) (50 Punkte)
Skript
CREATE TABLE Region (
BereichsID
CHAR( 2 )
Name
VARCHAR( 2 0 ) ,
R e g i o n a l l e i t e r INT,
NOT NULL,
PRIMARY KEY ( B e r e i c h s I D )
);
CREATE TABLE V e r k a e u f e r (
VkNr
INT
Name
VARCHAR( 3 5 )
Provision
DECIMAL( 6 , 2 ) ,
Region
CHAR( 2 ) ,
NOT NULL,
NOT NULL,
PRIMARY KEY ( VkNr ) ,
FOREIGN KEY ( Region ) REFERENCES Region ( B e r e i c h s I D )
);
ALTER TABLE Region ADD CONSTRAINT F K R e g i o n V e r k a e u f e r
FOREIGN KEY ( R e g i o n a l l e i t e r ) REFERENCES V e r k a e u f e r ( VkNr ) ;
CREATE TABLE Kunde (
KdNr
INT
Name
VARCHAR( 3 5 )
Kredit
DECIMAL( 5 , 2 ) ,
Saldo
INT,
NOT NULL,
NOT NULL,
PRIMARY KEY ( KdNr)
);
CREATE TABLE A r t i k e l (
ArtNr
CHAR( 4 )
Name
VARCHAR( 3 5 ) ,
Bestand
INT,
ArtKlasse
CHAR( 2 ) ,
LagerNr
INT,
NOT NULL,
PRIMARY KEY ( ArtNr )
);
CREATE TABLE P r e i s (
1
Übungsblatt 1
2
ArtNr
GueltigAb
Einkaufspreis
CHAR( 4 )
DATE
DECIMAL( 5 , 2 ) ,
NOT NULL,
NOT NULL,
PRIMARY KEY ( ArtNr , GueltigAb ) ,
FOREIGN KEY ( ArtNr ) REFERENCES A r t i k e l ( ArtNr )
);
CREATE TABLE B e s t e l l u n g (
BestNr
INT
Datum
DATE,
Verkaeufer
INT,
Kunde
INT,
NOT NULL,
PRIMARY KEY ( BestNr ) ,
FOREIGN KEY ( V e r k a e u f e r ) REFERENCES V e r k a e u f e r ( VkNr ) ,
FOREIGN KEY ( Kunde ) REFERENCES Kunde (KdNr)
);
CREATE TABLE B e s t e l l i s t e (
BestNr
INT
Artikel
CHAR( 4 ) ,
Menge
INT,
Verkaufspreis
DECIMAL( 5 , 2 ) ,
NOT NULL,
PRIMARY KEY ( BestNr , A r t i k e l ) ,
FOREIGN KEY ( BestNr ) REFERENCES B e s t e l l u n g ( BestNr ) ,
FOREIGN KEY ( A r t i k e l ) REFERENCES A r t i k e l ( ArtNr )
);
INSERT INTO Region VALUES( ’SW ’ , ’ Suedwest ’ , NULL) ;
INSERT INTO Region VALUES( ’SO ’ , ’ Suedost ’ , NULL) ;
INSERT INTO Region VALUES( ’OL ’ , ’ Oldenburg ’ , NULL) ;
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
Verkaeufer
Verkaeufer
Verkaeufer
Verkaeufer
VALUES( 3 , ’ Ingrid Richter ’ , 2 1 5 0 . 0 0 , ’SW ’ ) ;
VALUES( 6 , ’ Herbert Winkler ’ , 4 9 1 2 . 5 0 , ’SO ’ ) ;
VALUES( 1 2 , ’ Isaak Große ’ , 2 1 5 0 . 0 0 , ’OL ’ ) ;
VALUES( 2 4 , ’ Franz Fischle ’ , 2 5 0 0 . 0 0 , ’OL ’ ) ;
UPDATE Region SET R e g i o n a l l e i t e r = 3 WHERE B e r e i c h s I D = ’SW ’ ;
UPDATE Region SET R e g i o n a l l e i t e r = 6 WHERE B e r e i c h s I D = ’SO ’ ;
UPDATE Region SET R e g i o n a l l e i t e r = 2 4 WHERE B e r e i c h s I D = ’OL ’ ;
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
Kunde
Kunde
Kunde
Kunde
Kunde
Kunde
Kunde
Kunde
Kunde
Kunde
Kunde
VALUES( 1 2 4 ,
VALUES( 2 5 6 ,
VALUES( 3 1 1 ,
VALUES( 3 1 5 ,
VALUES( 4 0 5 ,
VALUES( 4 1 2 ,
VALUES( 5 2 2 ,
VALUES( 5 6 7 ,
VALUES( 5 8 7 ,
VALUES( 6 2 2 ,
VALUES( 9 1 1 ,
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
Artikel
Artikel
Artikel
Artikel
Artikel
Artikel
Artikel
Artikel
Artikel
’S . Fleischer ’ , 4 1 8 . 7 5 , 5 0 0 ) ;
’C . Richter ’ , 1 0 . 7 5 , 8 0 0 ) ;
’A Naß ’ , 2 0 0 . 1 0 , 3 0 0 ) ;
’K . Luck ’ , 3 2 0 . 7 5 , 3 0 0 ) ;
’J . Klein ’ , 2 0 1 . 7 5 , 8 0 0 ) ;
’S . Fleischer ’ , 9 0 8 . 7 5 , 1 0 0 0 ) ;
’E . Venske ’ , 4 9 . 5 0 , 8 0 0 ) ;
’E . Escher ’ , 2 0 1 . 2 0 , 3 0 0 ) ;
’U . Kahle ’ , 5 7 . 7 5 , 5 0 0 ) ;
’B . Ewald ’ , 5 7 5 . 5 0 , 5 0 0 ) ;
’R . Baron ’ , 6 0 0 , 1 0 ) ;
VALUES( ’ AX12 ’
VALUES( ’ AZ52 ’
VALUES( ’ BA74 ’
VALUES( ’ BH22 ’
VALUES( ’ BT04 ’
VALUES( ’ BZ66 ’
VALUES( ’ CA14 ’
VALUES( ’ CB03 ’
VALUES( ’ CX11 ’
,
,
,
,
,
,
,
,
,
’ Bügeleisen ’ , 1 0 4 , ’HG ’ , 3 ) ;
’ Schlittschuhe ’ , 2 0 , ’SG ’ , 2 ) ;
’ Ball ’ , 4 0 , ’SG ’ , 1 ) ;
’ Toaster ’ , 9 5 , ’HW ’ , 3 ) ;
’ Herd ’ , 1 1 , ’AP ’ , 2 ) ;
’ Waschmaschine ’ , 5 2 , ’AP ’ , 3 ) ;
’ Grill ’ , 2 , ’HW ’ , 3 ) ;
’ Fahrrad ’ , 4 4 , ’SG ’ , 1 ) ;
’ Mixer ’ , 1 1 2 , ’HW ’ , 3 ) ;
2
Übungsblatt 1
3
INSERT INTO A r t i k e l VALUES( ’ CZ81 ’ , ’ Hantel ’ , 2 0 8 , ’SQ ’ , 2 ) ;
INSERT INTO A r t i k e l VALUES( ’ XY01 ’ , ’ Gelbe Gummihandschuhe ’ , 2 , ’SG ’ , 1 ) ;
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
Preis
Preis
Preis
Preis
Preis
Preis
Preis
Preis
Preis
Preis
Preis
Preis
Preis
Preis
Preis
Preis
Preis
Preis
Preis
Preis
VALUES( ’ AX12 ’
VALUES( ’ AX12 ’
VALUES( ’ AX12 ’
VALUES( ’ AX12 ’
VALUES( ’ AZ52 ’
VALUES( ’ AZ52 ’
VALUES( ’ BA74 ’
VALUES( ’ BA74 ’
VALUES( ’ BH22 ’
VALUES( ’ BT04 ’
VALUES( ’ BT04 ’
VALUES( ’ BT04 ’
VALUES( ’ BZ66 ’
VALUES( ’ CA14 ’
VALUES( ’ CB03 ’
VALUES( ’ CB03 ’
VALUES( ’ CX11 ’
VALUES( ’ CZ81 ’
VALUES( ’ CZ81 ’
VALUES( ’ XY01 ’
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
INTO
INTO
Bestellung
Bestellung
Bestellung
Bestellung
Bestellung
Bestellung
Bestellung
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
Bestelliste
Bestelliste
Bestelliste
Bestelliste
Bestelliste
Bestelliste
Bestelliste
Bestelliste
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
TO DATE( ’ 01.01.2001 ’
TO DATE( ’ 01.04.2001 ’
TO DATE( ’ 01.10.2001 ’
TO DATE( ’ 01.10.2003 ’
TO DATE( ’ 01.07.2000 ’
TO DATE( ’ 01.03.2001 ’
TO DATE( ’ 15.02.2000 ’
TO DATE( ’ 15.02.2001 ’
TO DATE( ’ 01.01.1999 ’
TO DATE( ’ 01.01.2001 ’
TO DATE( ’ 01.03.2001 ’
TO DATE( ’ 01.05.2001 ’
TO DATE( ’ 01.10.2000 ’
TO DATE( ’ 15.01.1996 ’
TO DATE( ’ 01.10.2000 ’
TO DATE( ’ 01.02.2001 ’
TO DATE( ’ 01.01.2001 ’
TO DATE( ’ 01.01.2001 ’
TO DATE( ’ 01.01.2003 ’
TO DATE( ’ 01.01.2004 ’
VALUES( 1 2 4 8 9 ,
VALUES( 1 2 4 9 1 ,
VALUES( 1 2 4 9 4 ,
VALUES( 1 2 4 9 5 ,
VALUES( 1 2 4 9 8 ,
VALUES( 1 2 5 0 0 ,
VALUES( 1 2 5 0 4 ,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
TO DATE( ’ 03.07.2003 ’
TO DATE( ’ 12.07.2003 ’
TO DATE( ’ 04.08.2003 ’
TO DATE( ’ 04.10.2003 ’
TO DATE( ’ 01.10.2003 ’
TO DATE( ’ 11.10.2003 ’
TO DATE( ’ 21.11.2003 ’
VALUES( 1 2 4 8 9 ,
VALUES( 1 2 4 9 1 ,
VALUES( 1 2 4 9 4 ,
VALUES( 1 2 4 9 5 ,
VALUES( 1 2 4 9 8 ,
VALUES( 1 2 4 8 9 ,
VALUES( 1 2 5 0 0 ,
VALUES( 1 2 5 0 4 ,
’ AX12 ’
’ BZ66 ’
’ CB03 ’
’ CX11 ’
’ AZ52 ’
’ BA74 ’
’ BT04 ’
’ CZ81 ’
,
,
,
,
,
,
,
,
11
1,
4,
2,
2,
4,
1,
2,
Ausgabe
Table c r e a t e d .
Table c r e a t e d .
Table a l t e r e d .
Table c r e a t e d .
Table c r e a t e d .
Table c r e a t e d .
Table c r e a t e d .
Table c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
3
, 14.95);
311.95);
175.00);
57.95);
22.95);
4.95);
402.99);
8.99);
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
17.95);
18.45);
18.95);
20.40);
24.95);
28.95);
4.95);
5.05);
34.95);
402.99);
404.99);
429.99);
311.95);
19.95);
187.50);
189.50);
57.95);
8.99);
7.99);
1.99);
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
’DD . MM . YYYY ’ )
,
,
,
,
,
,
,
3,
6,
12
12
6,
3,
6,
124);
311);
, 315);
, 256);
522);
124);
522);
Übungsblatt 1
4
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row updated .
1 row updated .
1 row updated .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
4
Übungsblatt 1
5
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
1 row c r e a t e d .
5