DROP TABLE IF EXISTS liegt_in    CASCADE;
DROP TABLE IF EXISTS region      CASCADE;
DROP TABLE IF EXISTS land        CASCADE;
DROP TABLE IF EXISTS unternehmen CASCADE;

CREATE TABLE unternehmen 
( u_id    INTEGER       NOT NULL,
  name    VARCHAR(100)  NOT NULL,
  web     VARCHAR(100)  NOT NULL,
  email   VARCHAR(100),
  tel     VARCHAR(30),
  branche VARCHAR(30),
  
  PRIMARY KEY (u_id),
  UNIQUE      (name, branche),
  UNIQUE      (tel),
  UNIQUE      (email)
);

CREATE TABLE land 
( l_id    INTEGER      NOT NULL,
  name    VARCHAR(30)  NOT NULL,
  anz_unt INTEGER      NOT NULL DEFAULT 0,

  PRIMARY KEY (l_id),
  UNIQUE      (name)
);

CREATE TABLE region 
( r_id    INTEGER       NOT NULL,
  l_id    INTEGER       NOT NULL,
  name    VARCHAR(100)  NOT NULL,
  anz_unt INTEGER       NOT NULL DEFAULT 0,
 
  PRIMARY KEY (r_id),
  UNIQUE      (name, l_id),
  FOREIGN KEY (l_id) REFERENCES land (l_id)
);

CREATE TABLE liegt_in 
( u_id INTEGER  NOT NULL,
  r_id INTEGER  NOT NULL,
  von  DATE,
  bis  DATE,
  art  VARCHAR(100),
  
  PRIMARY KEY (u_id, r_id),
  FOREIGN KEY (u_id) REFERENCES unternehmen (u_id),
  FOREIGN KEY (r_id) REFERENCES region      (r_id)
);


INSERT INTO unternehmen(u_id, name, web, email, tel, branche)
VALUES
( 1, 'Hochschule Augsburg',      'https://www.hs-augsburg.de/',        'info@hs-augsburg.de',                   '+49 89 1265-0',   'Hochschule'),
( 2, 'Hochschule Kempten',       'https://www.hochschule-kempten.de',  'post@hs-kempten.de',                    '+49 831 2523-0',  'Hochschule'),
( 3, 'Hochschule München',       'https://www.hm.edu/',                'webmaster@hm.edu',                      NULL,              'Hochschule'),
( 4, 'Hochschule Neu-Ulm',       'https://www.hs-neu-ulm.de/',         'info@hs-neu-ulm.de',                    '+49 731/9762-0',  'Hochschule'),
( 5, 'Team23',                   'https://www.team23.de/',             NULL,                                    NULL,              'Digitalagentur'),
( 6, 'MAN',                      'https://www.man.eu/de/',             NULL,                                    '+49 89 36098-0',  'Fahrzeugbau'),
( 7, 'BMW',                      'https://www.bmwgroup.com/',          'bmwgroup.customerservice@bmwgroup.com', '+49 89 382-0',    'Fahrzeugbau'),
( 8, 'Audi',                     'https://www.audi.de/',               NULL,                                    '+49 841 89-0',    'Fahrzeugbau'),
( 9, 'Bäckerei Bauer',           'https://www.baeckerei-bauer.info/',  NULL,                                    NULL,              'Bäckerei'),
(10, 'Augsburger Kattunfabrik',  'https://de.wikipedia.org/wiki/NAK',  NULL,                                    NULL,              'Kattunfabrik'),
(11, 'Augsburg Airways',         'https://de.wikipedia.org/wiki/IQ',   NULL,                                    NULL,              'Fluggesellschaft')
;

INSERT INTO land(l_id, name)
VALUES
(1, 'Deutschland'),
(2, 'Österreich')
;

INSERT INTO region(r_id, l_id, name)
VALUES
(1, 1, 'Augsburg'),
(2, 1, 'Kempten'),
(3, 1, 'München'),
(4, 1, 'Ulm'),
(5, 1, 'Ingolstadt'),
(6, 1, 'Illertissen'),
(7, 2, 'Salzburg'),
(8, 1, 'Nürnberg'),
(9, 1, 'Hallbergmoos')
;

INSERT INTO liegt_in(u_id, r_id, von, bis, art)
VALUES
( 1, 1, DATE '1670-01-01', NULL,              'Campus'),
( 1, 6, DATE '2010-01-01', NULL,              'Tagungszentrum'),
( 2, 2, DATE '1977-01-01', NULL,              'Campus'),
( 2, 6, DATE '2010-01-01', NULL,              'Tagungszentrum'),
( 3, 3, DATE '1823-01-01', NULL,              'Campus'),
( 4, 4, DATE '1994-01-01', NULL,              'Campus'),
( 4, 6, DATE '2010-01-01', NULL,              'Tagungszentrum'),
( 5, 1, DATE '1999-05-05', NULL,              'Zentrale'),
( 6, 3, DATE '1986-01-01', NULL,              'Zentrale'),
( 6, 1, DATE '1840-01-01', NULL,              'Produktion'),
( 6, 8, DATE '1908-01-01', DATE '1968-12-31', 'Schienenfahrzeuge'),
( 7, 3, DATE '1916-01-01', NULL,              'Zentrale'),
( 7, 7, NULL,              NULL,              'Vertrieb'),
( 8, 5, DATE '1949-01-01', NULL,              'Zentrale'),
( 8, 7, NULL,              NULL,              'Vertrieb'),
(10, 1, DATE '1885-01-12', DATE '1996-06-30', 'Zentrale'),
(11, 9, DATE '1981-01-01', DATE '2013-12-31', 'Zentrale')
;