--************************************************************************************
-- Beispiel aus der Vorlesung "Multimedia-Datenbanken" von Wolfgang Kowarschick
--************************************************************************************/

-- Beim allerersten Aufruf: Zweimal den Inhalt der Datei ausführen.
DROP TABLE KOWA.Haendler.liefert;
DROP TABLE KOWA.Haendler.ware;
DROP TABLE KOWA.Haendler.haendler;

CREATE TABLE KOWA.Haendler.haendler
  (hnr       INTEGER     NOT NULL,
   name      VARCHAR(30) NOT NULL,
   adresse   VARCHAR(50),

   CONSTRAINT pk_KOWA_Haendler_haendler
     PRIMARY KEY (hnr),

   CONSTRAINT unique_name_address
     UNIQUE (name, adresse)
  );

CREATE TABLE KOWA.Haendler.ware
  (wnr          INTEGER     NOT NULL,
   typ          VARCHAR(30) NOT NULL DEFAULT 'Sonstiges',
   bezeichnung  VARCHAR(50) NOT NULL,

   CONSTRAINT pk_KOWA_Haendler_ware
     PRIMARY KEY (wnr),

   CONSTRAINT unique_typ_bezeichnung
     UNIQUE (typ, bezeichnung)
  );
                
CREATE TABLE KOWA.Haendler.liefert
  (hnr         INTEGER       NOT NULL,
   wnr         INTEGER       NOT NULL,
   preis       NUMERIC(8,2)  NOT NULL,
   lieferzeit  SMALLINT,     -- Tage

   CONSTRAINT pk_KOWA_Haendler_liefert
     PRIMARY KEY (hnr,wnr,preis),

   CONSTRAINT fk_KOWA_Haendler_liefert_KOWA_Haendler_haendler
     FOREIGN KEY (hnr) REFERENCES KOWA.Haendler.haendler (hnr),

   CONSTRAINT fk_KOWA_Haendler_liefert_KOWA_Haendler_ware
     FOREIGN KEY (wnr) REFERENCES KOWA.Haendler.ware (wnr),

   CONSTRAINT check_lieferzeit
     CHECK (lieferzeit >= 0)
  );


INSERT INTO KOWA.Haendler.haendler(hnr, name, adresse)
VALUES (1, 'Maier',   'Königsbrunn');
INSERT INTO KOWA.Haendler.haendler(hnr, name, adresse)
VALUES (2, 'Müller',  'Königsbrunn');
INSERT INTO KOWA.Haendler.haendler(hnr, name, adresse)
VALUES (3, 'Maier',   'Augsburg');
INSERT INTO KOWA.Haendler.haendler(hnr, name, adresse)
VALUES (4, 'Huber',   NULL);

INSERT INTO KOWA.Haendler.ware(wnr, typ, bezeichnung)
VALUES (1, 'CPU',       'Pentium IV 3,8');
INSERT INTO KOWA.Haendler.ware(wnr, typ, bezeichnung)
VALUES (2, 'CPU',       'Celeron 2,6');
INSERT INTO KOWA.Haendler.ware(wnr, typ, bezeichnung)
VALUES (3, 'CPU',       'Athlon XP 3000+');
INSERT INTO KOWA.Haendler.ware(wnr, typ, bezeichnung)
VALUES (4, 'RAM',       'SDRAM 1GB');

INSERT INTO KOWA.Haendler.liefert(hnr, wnr, preis, lieferzeit)
VALUES (1, 1, 200.00, 1);
INSERT INTO KOWA.Haendler.liefert(hnr, wnr, preis, lieferzeit)
VALUES (1, 1, 194.00, 6);
INSERT INTO KOWA.Haendler.liefert(hnr, wnr, preis, lieferzeit)
VALUES (1, 2, 100.00, NULL);
INSERT INTO KOWA.Haendler.liefert(hnr, wnr, preis, lieferzeit)
VALUES (1, 3, 150.00, 7);
INSERT INTO KOWA.Haendler.liefert(hnr, wnr, preis, lieferzeit)
VALUES (1, 4,  10.00, 1);
INSERT INTO KOWA.Haendler.liefert(hnr, wnr, preis, lieferzeit)
VALUES (1, 5,   5.00, 1);
INSERT INTO KOWA.Haendler.liefert(hnr, wnr, preis, lieferzeit)
VALUES (2, 1, 190.00, NULL);
INSERT INTO KOWA.Haendler.liefert(hnr, wnr, preis, lieferzeit)
VALUES (2, 3, 170.00, 4);
INSERT INTO KOWA.Haendler.liefert(hnr, wnr, preis, lieferzeit)
VALUES (2, 1, 160.00, 1);
INSERT INTO KOWA.Haendler.liefert(hnr, wnr, preis, lieferzeit)
VALUES (2, 2, 180.00, NULL);
INSERT INTO KOWA.Haendler.liefert(hnr, wnr, preis, lieferzeit)
VALUES (3, 1, 195.00, 2);
INSERT INTO KOWA.Haendler.liefert(hnr, wnr, preis, lieferzeit)
VALUES (3, 2, 190.00, 1);
INSERT INTO KOWA.Haendler.liefert(hnr, wnr, preis, lieferzeit)
VALUES (4, 1, 150.00, 3);
INSERT INTO KOWA.Haendler.liefert(hnr, wnr, preis, lieferzeit)
VALUES (4, 3, 180.00, 5);
INSERT INTO KOWA.Haendler.liefert(hnr, wnr, preis, lieferzeit)
VALUES (4, 3, 199.99, 1);
