/*************************************************************************************
 * 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
(h_id        UUID        NOT NULL DEFAULT gen_random_uuid(),
 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          UUID        NOT NULL DEFAULT gen_random_uuid(),
 w_typ         VARCHAR(30) NOT NULL DEFAULT 'Sonstiges',
 w_bezeichnung VARCHAR(50) NOT NULL,

 CONSTRAINT pk_ware
   PRIMARY KEY (w_id),

 CONSTRAINT unique_ware_typ_bezeichnung
   UNIQUE (w_typ, w_bezeichnung)
);
                
CREATE TABLE liefert
(h_id         UUID          NOT NULL,
 w_id         UUID          NOT NULL,
 l_preis      NUMERIC(8,2)  NOT NULL,
 l_lieferzeit SMALLINT,     /* Tage */

 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),

 CONSTRAINT check_liefert_lieferzeit
   CHECK (l_lieferzeit > 0)
);


-- Daten einfügen
BEGIN TRANSACTION;

CREATE TEMPORARY TABLE 
ids
(id   INTEGER NOT NULL PRIMARY KEY,
 h_id UUID    NOT NULL DEFAULT gen_random_uuid(),
 w_id UUID    NOT NULL DEFAULT gen_random_uuid()
) 
ON COMMIT DROP
;

INSERT INTO ids
VALUES (1), (2), (3), (4), (5)
;

SELECT *
FROM   ids
;

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

INSERT INTO ware(w_id, w_typ, w_bezeichnung)
VALUES
((SELECT w_id FROM ids WHERE id=1), 'CPU',       'Pentium IV 3,8'),
((SELECT w_id FROM ids WHERE id=2), 'CPU',       'Celeron 2,6'),
((SELECT w_id FROM ids WHERE id=3), 'CPU',       'Athlon XP 3000+'),
((SELECT w_id FROM ids WHERE id=4), 'RAM',       'SDRAM 1GB'),
((SELECT w_id FROM ids WHERE id=5), 'Sonstiges', 'Eieruhr')
;

INSERT INTO liefert(h_id, w_id, l_preis, l_lieferzeit)
VALUES 
((SELECT h_id FROM ids WHERE id=1), (SELECT w_id FROM ids WHERE id=1), 200.00, 1),
((SELECT h_id FROM ids WHERE id=1), (SELECT w_id FROM ids WHERE id=1), 194.00, 6),
((SELECT h_id FROM ids WHERE id=1), (SELECT w_id FROM ids WHERE id=2), 100.00, NULL),
((SELECT h_id FROM ids WHERE id=1), (SELECT w_id FROM ids WHERE id=3), 150.00, 7),
((SELECT h_id FROM ids WHERE id=1), (SELECT w_id FROM ids WHERE id=4),  10.00, 1),
((SELECT h_id FROM ids WHERE id=1), (SELECT w_id FROM ids WHERE id=5),   5.00, 1),
((SELECT h_id FROM ids WHERE id=2), (SELECT w_id FROM ids WHERE id=1), 160.00, NULL),
((SELECT h_id FROM ids WHERE id=2), (SELECT w_id FROM ids WHERE id=1), 190.00, 1),
((SELECT h_id FROM ids WHERE id=2), (SELECT w_id FROM ids WHERE id=2), 180.00, NULL),
((SELECT h_id FROM ids WHERE id=2), (SELECT w_id FROM ids WHERE id=3), 170.00, 4),
((SELECT h_id FROM ids WHERE id=2), (SELECT w_id FROM ids WHERE id=5),   5.00, 1),
((SELECT h_id FROM ids WHERE id=3), (SELECT w_id FROM ids WHERE id=1), 195.00, 2),
((SELECT h_id FROM ids WHERE id=3), (SELECT w_id FROM ids WHERE id=2), 190.00, 1),
((SELECT h_id FROM ids WHERE id=4), (SELECT w_id FROM ids WHERE id=1), 150.00, 3),
((SELECT h_id FROM ids WHERE id=4), (SELECT w_id FROM ids WHERE id=3), 180.00, 5),
((SELECT h_id FROM ids WHERE id=4), (SELECT w_id FROM ids WHERE id=3), 199.99, 1)
;

COMMIT;

SELECT * 
FROM   haendler JOIN liefert USING (h_id)
                JOIN ware    USING (w_id)
;