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

/* Alte Tabellen (in der richtigen Reihenfolge) löschen: */

DROP TABLE IF EXISTS liefert  CASCADE;
DROP TABLE IF EXISTS ware     CASCADE;
DROP TABLE IF EXISTS haendler CASCADE;

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

   CONSTRAINT pk_haendler
     PRIMARY KEY (hnr),

   CONSTRAINT unique_name_adresse
     UNIQUE (name, adresse)
  );

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

   CONSTRAINT pk_ware
     PRIMARY KEY (wnr),

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

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

   CONSTRAINT fk_liefert_haendler
     FOREIGN KEY (hnr) REFERENCES haendler (hnr),

   CONSTRAINT fk_liefert_ware
     FOREIGN KEY (wnr) REFERENCES ware (wnr),

   CONSTRAINT check_lieferzeit
     CHECK (lieferzeit >= 0)
  );


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

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

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