/*************************************************************************************
 * 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;

/* Tabellen erstellen */

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,
 typ         VARCHAR(30) NOT NULL DEFAULT 'Sonstiges',
 bezeichnung VARCHAR(50) NOT NULL,

 CONSTRAINT pk_ware
   PRIMARY KEY (id),

 CONSTRAINT unique_ware_typ_bezeichnung
   UNIQUE (typ, bezeichnung)
);
                
CREATE TABLE liefert
(h_id       INTEGER       NOT NULL,
 w_id       INTEGER       NOT NULL,
 preis      NUMERIC(8,2)  NOT NULL,
 lieferzeit SMALLINT,     /* Tage */

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

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

 CONSTRAINT fk_liefert_ware
   FOREIGN KEY (w_id) REFERENCES ware (id),

 CONSTRAINT check_liefert_lieferzeit
   CHECK (lieferzeit > 0)
);


/* Daten einfügen */
 
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, 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(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),
(2, 5,   5.00, 1),
(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)
;