/*************************************************************************************
 * 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
  (id          INTEGER     NOT NULL,
   name        VARCHAR(20) NOT NULL,
   supertyp_id INTEGER,

   CONSTRAINT pk_typ
     PRIMARY KEY (id),

   CONSTRAINT fk_typ_typ
     FOREIGN KEY (supertyp_id) REFERENCES typ (id),
	 
   CONSTRAINT unique_typ_name
     UNIQUE (name)
  );

CREATE TABLE haendler
  (id        INTEGER     NOT NULL,
   name      VARCHAR(30) NOT NULL,
   ortschaft VARCHAR(50),

   CONSTRAINT pk_haendler
     PRIMARY KEY (id),

   CONSTRAINT unique_haendler_name_ortschaft
     UNIQUE (name, ortschaft)
  );

CREATE TABLE ware
  (id          INTEGER     NOT NULL,
   t_id        INTEGER     NOT NULL DEFAULT 0,
   bezeichnung VARCHAR(50) NOT NULL,

   CONSTRAINT pk_ware
     PRIMARY KEY (id),

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

   CONSTRAINT unique_ware_id_bezeichnung
     UNIQUE (id, bezeichnung)
  );
                
CREATE TABLE liefert
  (h_id       INTEGER       NOT NULL,
   w_id       INTEGER       NOT NULL,
   preis      NUMERIC(8,2)  NOT NULL,
   lieferzeit D_LIEFERZEIT,

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

   CONSTRAINT fk_lieferhaendler
     FOREIGN KEY (h_id) REFERENCES haendler (id),

   CONSTRAINT fk_lieferware
     FOREIGN KEY (w_id) REFERENCES ware (id)
  );


INSERT INTO typ(id, name, supertyp_id)
VALUES
  (0, 'Sonstiges',       NULL),
  (1, 'Computerzubehör', NULL),
  (2, 'CPU',             1),
  (3, 'RAM',             1)
;

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

INSERT INTO ware(id, t_id, 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, 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, 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)
;