/*************************************************************************************
 * 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;
DROP TABLE IF EXISTS typ      CASCADE;

DROP DOMAIN IF EXISTS D_LIEFERZEIT CASCADE;

CREATE DOMAIN D_LIEFERZEIT
AS
SMALLINT
CHECK (value > 0);

CREATE TABLE typ
  (t_id          INTEGER     NOT NULL,
   t_name        VARCHAR(20) NOT NULL,
   t_supertyp_id INTEGER,

   CONSTRAINT pk_typ
     PRIMARY KEY (t_id),

   CONSTRAINT fk_typ_typ
     FOREIGN KEY (t_supertyp_id) REFERENCES typ (t_id),
	 
   CONSTRAINT unique_typ_t_name
     UNIQUE (t_name)
  );

CREATE TABLE haendler
  (h_id        INTEGER     NOT NULL,
   h_name      VARCHAR(30) NOT NULL,
   h_ortschaft VARCHAR(50),

   CONSTRAINT pk_haendler
     PRIMARY KEY (h_id),

   CONSTRAINT unique_haendler_name_ortschaft
     UNIQUE (h_name, h_ortschaft)
  );

CREATE TABLE ware
  (w_id          INTEGER     NOT NULL,
   t_id          INTEGER     NOT NULL DEFAULT 0,
   w_bezeichnung VARCHAR(50) NOT NULL,

   CONSTRAINT pk_ware
     PRIMARY KEY (w_id),

   CONSTRAINT fk_ware_typ
     FOREIGN KEY (t_id) REFERENCES typ (t_id),

   CONSTRAINT unique_ware_t_id_bezeichnung
     UNIQUE (t_id, w_bezeichnung)
  );
                
CREATE TABLE liefert
  (h_id         INTEGER       NOT NULL,
   w_id         INTEGER       NOT NULL,
   l_preis      NUMERIC(8,2)  NOT NULL,
   l_lieferzeit D_LIEFERZEIT,

   CONSTRAINT pk_liefert
     PRIMARY KEY (h_id, w_id, l_preis),

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

   CONSTRAINT fk_liefert_ware
     FOREIGN KEY (w_id) REFERENCES ware (w_id)
  );


INSERT INTO typ(t_id, t_name, t_supertyp_id)
VALUES
  (0, 'Sonstiges',       NULL),
  (1, 'Computerzubehör', NULL),
  (2, 'CPU',             1),
  (3, 'RAM',             1)
;

INSERT INTO haendler(h_id, h_name, h_ortschaft)
VALUES 
  (1, 'Maier',   'Königsbrunn'),
  (2, 'Müller',  'Königsbrunn'),
  (3, 'Maier',   'Augsburg'),
  (4, 'Huber',   NULL),
  (5, 'Schmidt', 'Hamburg')
;

INSERT INTO ware(w_id, t_id, w_bezeichnung)
VALUES
  (1, 2, 'Pentium IV 3,8'),
  (2, 2, 'Celeron 2,6'),
  (3, 2, 'Athlon XP 3000+'),
  (4, 3, 'SDRAM 1GB'),
  (5, 0, 'Eieruhr')
;

INSERT INTO liefert(h_id, w_id, l_preis, l_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, 160.00, NULL),
  (2, 1, 190.00, 1),
  (2, 2, 180.00, NULL),
  (2, 3, 170.00, 4),
  (3, 1, 195.00, 2),
  (3, 2, 190.00, 1),
  (4, 1, 150.00, 3),
  (4, 3, 180.00, 5),
  (4, 3, 199.99, 1)
;