-- Projekt "kuchenbasar"
-- Voraussetzung: neue DB kuchenbasarvcs erstellt
--

IF EXISTS (SELECT * FROM sys.views WHERE name='bestellungenview')
   DROP VIEW bestellungenview;
IF EXISTS (SELECT * FROM sys.views WHERE name='kuchenview')
   DROP VIEW kuchenview;
IF EXISTS (SELECT * FROM sys.views WHERE name='gaesteview')
   DROP VIEW gaesteview;
IF EXISTS (SELECT * FROM sys.tables WHERE name='bestellungen')
   DROP TABLE bestellungen;
IF EXISTS (SELECT * FROM sys.tables WHERE name='kuchen')
   DROP TABLE kuchen;
IF EXISTS (SELECT * FROM sys.tables WHERE name='sorten')
   DROP TABLE sorten;
IF EXISTS (SELECT * FROM sys.tables WHERE name='gaeste')
   DROP TABLE gaeste;
IF EXISTS (SELECT * FROM sys.tables WHERE name='einrichtungen')
   DROP TABLE einrichtungen;
GO

--
-- Tabellen erstellen
--

CREATE TABLE kuchen (
    id                 INT IDENTITY(0,1) NOT NULL,
    anbieter           VARCHAR(50) NOT NULL,
    sorte              INT NOT NULL,
    kurzkuchen         VARCHAR(50) NOT NULL,
    beschreibung       VARCHAR(50) NOT NULL,
    abgabedatum        DATETIME NOT NULL,
    anzahl             INT NOT NULL,
    preis              DECIMAL(20,4) NOT NULL,
    diabetikergeeignet BIT NOT NULL,
    letzterzugriff     DATETIME NOT NULL);
 
ALTER TABLE kuchen
ADD  CONSTRAINT kuchen_PK 
     PRIMARY KEY (id);
GO

CREATE TABLE sorten (
    id                 INT IDENTITY(0,1) NOT NULL,
    backwarensorte     VARCHAR(50) NOT NULL);
    
ALTER TABLE sorten
ADD  CONSTRAINT sorten_PK 
     PRIMARY KEY (id);
GO

CREATE TABLE gaeste (
    id                 INT IDENTITY(0,1) NOT NULL,
    gastname           VARCHAR(50) NOT NULL,
    vorname            VARCHAR(50) NOT NULL,
    kurzgast           VARCHAR(50) NOT NULL,
    einrichtung        INT NOT NULL,
    telefon            VARCHAR(50) NOT NULL,
    letzterzugriff     DATETIME NOT NULL);
 
ALTER TABLE gaeste
ADD  CONSTRAINT gaeste_PK 
     PRIMARY KEY (id);
GO

CREATE TABLE einrichtungen (
    id                 INT IDENTITY(0,1) NOT NULL,
    einrichtung        VARCHAR(50) NOT NULL);
    
ALTER TABLE einrichtungen
ADD  CONSTRAINT einrichtungen_PK 
     PRIMARY KEY (id);
GO

CREATE TABLE bestellungen (
    id                 INT IDENTITY(0,1) NOT NULL,
    gast               INT NOT NULL,
    kuchen             INT NOT NULL,
    menge              INT NOT NULL,
    bestelldatum       DATETIME NOT NULL,
    gesamtpreis        DECIMAL(20,4) NOT NULL,
    bezahlt            BIT NOT NULL,
    geliefert          BIT NOT NULL,
    letzterzugriff     DATETIME NOT NULL);
 
ALTER TABLE bestellungen
ADD  CONSTRAINT bestellungen_PK 
     PRIMARY KEY (id);
GO

--
-- Daten einfügen
--

SET IDENTITY_INSERT kuchen ON 
INSERT kuchen (id, anbieter, sorte, kurzkuchen, beschreibung, abgabedatum, anzahl, preis, diabetikergeeignet, letzterzugriff) 
VALUES (0, 'noch nicht bekannt', 0, 'noch nicht bekannt','' , CAST('27.11.2014' AS DATE), 0, CAST(0.00 AS DECIMAL(20,4)), 1, CAST('05.12.2014' AS DATE)),
       (1, 'Frau Schmidt', 4, 'Hefekuchen', 'selbstgebacken', CAST('27.11.2014' AS DATE), 35, CAST(0.45 AS DECIMAL(20,4)), 0, CAST('01.12.2014' AS DATE)),
       (2, 'Grubenbäcker', 2, 'Brötchen', 'nicht belegt', CAST('27.11.2014' AS DATE), 18, CAST(1.25 AS DECIMAL(20,4)), 1, CAST('02.12.2014' AS DATE)),
       (3, 'Konditorei', 3, 'Sahnetorte', '', CAST('27.11.2014' AS DATE), 12, CAST(2.30 AS DECIMAL(20,4)), 0, CAST('01.12.2014' AS DATE)),
       (4, 'Herr Müller', 3, 'Erdbeertorte', 'mit frischen Erdbeeren', CAST('27.11.2014' AS DATE), 20, CAST(1.85 AS DECIMAL(20,4)), 0, CAST('01.12.2014' AS DATE)),
       (10, 'Mecklenburger Backstube', 2, 'Käsebrötchen', '', CAST('27.11.2014' AS DATE), 25, CAST(1.10 AS DECIMAL(20,4)), 0, CAST('01.12.2014' AS DATE)),
       (15, 'Lila Bäcker', 5, 'Punschkuchen', 'Vorsicht Alkohol', CAST('27.11.2014' AS DATE), 10, CAST(1.99 AS DECIMAL(20,4)), 1, CAST('02.12.2014' AS DATE)),
       (16, 'Cafe Wien', 3, 'Sachertorte', 'original Wienerisch', CAST('27.11.2014' AS DATE), 12, CAST(3.00 AS DECIMAL(20,4)), 0, CAST('01.12.2014' AS DATE)),
       (18, 'Frau Krüger', 1, 'Kirschkuchen', 'Spuckkuchen', CAST('27.11.2014' AS DATE), 25, CAST(0.5 AS DECIMAL(20,4)), 1, CAST('01.12.2014' AS DATE)),
       (19, 'Frau Meier', 1, 'Birne Helene', '', CAST('27.11.2014' AS DATE), 10, CAST(1.00 AS DECIMAL(20,4)), 1, CAST('01.12.2014' AS DATE)),
       (20, 'Herr Schulz', 1, 'Teufelskuchen', '', CAST('27.11.2014' AS DATE), 16, CAST(2.22 AS DECIMAL(20,4)), 1, CAST('02.12.2014' AS DATE)),
       (23, 'Frau Schubert', 4, 'Zuckerkuchen', 'mit Glasur', CAST('27.11.2014' AS DATE), 35, CAST(0.75 AS DECIMAL(20,4)), 0, CAST('02.12.2014' AS DATE)),
       (24, 'Wagner', 2, 'Brötchen belegt', 'mit Käse/Schinken', CAST('27.11.2014' AS DATE), 16, CAST(2.25 AS DECIMAL(20,4)), 1, CAST('01.12.2014' AS DATE)),
       (25, 'Cosel', 1, 'Quarkkeulchen', 'mit Puderzucker', CAST('27.11.2014' AS DATE), 100, CAST(0.4 AS DECIMAL(20,4)), 1, CAST('01.12.2014' AS DATE)),
       (26, 'Grubenbäcker', 5, 'Lebkuchenhaus', 'zum selber Abbauen und Essen', CAST('04.12.2014' AS DATE), 3, CAST(18.00 AS DECIMAL(20,4)), 0, CAST('05.12.2014' AS DATE)); 
SET IDENTITY_INSERT kuchen OFF 
GO

SET IDENTITY_INSERT sorten ON 
INSERT sorten (id, backwarensorte) 
VALUES        (0, 'noch nicht bekannt'),
              (1, 'Kuchenstücke'),	
              (2, 'Brot und Brötchen'),
              (3, 'Torten'),
              (4, 'Blechkuchen'),
              (5, 'Lebkuchen');
SET IDENTITY_INSERT sorten OFF 
GO

SET IDENTITY_INSERT gaeste ON 
INSERT gaeste (id, gastname, vorname, kurzgast, einrichtung, telefon, letzterzugriff) 
VALUES (0, 'noch nicht bekannt', 'noch nicht bekannt', 'noch nicht bekannt', 0, '', CAST('28.11.2014' AS DATE)), 
       (1, 'Meier', 'Hans', 'Hans M', 2, '0381 381', CAST('28.11.2014' AS DATE)), 
       (2, 'Müller', 'Jana', 'Jana M', 1, '', CAST('28.11.2014' AS DATE)), 
       (3, 'Olsen', 'Egon', 'Egon O', 3, '007 1234', CAST('28.11.2014' AS DATE)), 
       (4, 'Meier', 'Frieda', 'Frieda M', 1, '0381 333', CAST('28.11.2014' AS DATE)), 
       (5, 'Holm-Jensen', '', 'Holm', 3, '555 897', CAST('28.11.2014' AS DATE)), 
       (10, 'Marx', 'Karl', 'Karl M', 4, '777', CAST('01.12.2014' AS DATE)), 
       (11, 'Bolte', 'Witwe', 'Witwe B', 5, '', CAST('01.12.2014' AS DATE)), 
       (12, 'Buben', 'Max', 'Max', 5, '', CAST('01.12.2014' AS DATE)), 
       (13, 'Buben', 'Moritz', 'Moritz', 5, '', CAST('01.12.2014' AS DATE)), 
       (15, 'Lempel', 'Lehrer', 'Lehrer Lempel', 2, '123', CAST('01.12.2014' AS DATE)), 
       (16, 'Karlsen', 'Lisa', 'Lisa K', 2, '456', CAST('01.12.2014' AS DATE)), 
       (17, 'Schubert', 'Bernd', 'Bernd S', 1, '0381 555666777', CAST('04.12.2014' AS DATE));
SET IDENTITY_INSERT gaeste OFF 
GO

SET IDENTITY_INSERT einrichtungen ON 
INSERT einrichtungen (id, einrichtung) 
VALUES (0, 'noch nicht bekannt'),
       (1, 'Alfatraining'),
       (2, 'Schule'),
       (3, 'Olsenbande'),
       (4, 'Volkshochschule'),
       (5, 'Wilhelm Busch Kindergarten');
SET IDENTITY_INSERT einrichtungen OFF 
GO

SET IDENTITY_INSERT bestellungen ON 
INSERT bestellungen (id, gast, kuchen, menge, bestelldatum, gesamtpreis, bezahlt, geliefert, letzterzugriff) 
VALUES (1, 15, 2, 2, CAST('01.12.2014' AS DATE), CAST(2.90 AS DECIMAL(20,4)), 1, 1, CAST('02.12.2014' AS DATE)),
       (2, 3, 23, 5, CAST('01.12.2014' AS DATE), CAST(3.75 AS DECIMAL(20,4)), 1, 0, CAST('02.12.2014' AS DATE)),
       (6, 10, 20, 4, CAST('01.12.2014' AS DATE), CAST(8.88 AS DECIMAL(20,4)), 0, 1, CAST('02.12.2014' AS DATE)),
       (7, 2, 4, 1, CAST('02.12.2014' AS DATE), CAST(1.00 AS DECIMAL(20,4)), 0, 0, CAST('02.12.2014' AS DATE)),
       (8, 11, 25, 10, CAST('02.12.2014' AS DATE), CAST(5.00 AS DECIMAL(20,4)), 1, 0, CAST('02.12.2014' AS DATE)),
       (9, 12, 15, 2, CAST('02.12.2014' AS DATE), CAST(1.50 AS DECIMAL(20,4)), 1, 0, CAST('02.12.2014' AS DATE)),
       (10, 13, 1, 3, CAST('02.12.2014' AS DATE), CAST(2.25 AS DECIMAL(20,4)), 0, 1, CAST('02.12.2014' AS DATE)),
       (12, 16, 4, 1, CAST('02.12.2014' AS DATE), CAST(1.40 AS DECIMAL(20,4)), 0, 1, CAST('02.12.2014' AS DATE)),
       (13, 3, 18, 3, CAST('02.12.2014' AS DATE), CAST(0.60 AS DECIMAL(20,4)), 1, 1, CAST('02.12.2014' AS DATE)),
       (14, 4, 19, 1, CAST('02.12.2014' AS DATE), CAST(1.00 AS DECIMAL(20,4)), 0, 0, CAST('02.12.2014' AS DATE)),
       (15, 3, 23, 2, CAST('02.12.2014' AS DATE), CAST(4.00 AS DECIMAL(20,4)), 1, 0, CAST('02.12.2014' AS DATE)),
       (16, 4, 23, 1, CAST('02.12.2014' AS DATE), CAST(1.50 AS DECIMAL(20,4)), 0, 1, CAST('02.12.2014' AS DATE)),
       (17, 15, 24, 3, CAST('02.12.2014' AS DATE), CAST(12.00 AS DECIMAL(20,4)), 0, 1, CAST('02.12.2014' AS DATE)),
       (23, 16, 3, 2, CAST('02.12.2014' AS DATE), CAST(1.20 AS DECIMAL(20,4)), 1, 0, CAST('02.12.2014' AS DATE)),
       (24, 5, 16, 2, CAST('02.12.2014' AS DATE), CAST(6.00 AS DECIMAL(20,4)), 0, 1, CAST('02.12.2014' AS DATE)),
       (25, 17, 26, 3, CAST('04.12.2014' AS DATE), CAST(61.60 AS DECIMAL(20,4)), 1, 0, CAST('05.12.2014' AS DATE)),
       (26, 11, 26, 2, CAST('05.12.2014' AS DATE), CAST(36.00 AS DECIMAL(20,4)), 0, 0, CAST('05.12.2014' AS DATE));
SET IDENTITY_INSERT bestellungen OFF 
GO

--
-- Defaults und Foreign Keys und Indexe
--

ALTER TABLE kuchen 
ADD  CONSTRAINT kuchen_abgabedatum_DF
     DEFAULT (getdate()) FOR abgabedatum
GO

ALTER TABLE kuchen 
ADD  CONSTRAINT kuchen_letzterzugriff_DF  
     DEFAULT (getdate()) FOR letzterzugriff
GO

ALTER TABLE gaeste 
ADD  CONSTRAINT gaeste_letzterzugriff_DF  
     DEFAULT (getdate()) FOR letzterzugriff
GO

ALTER TABLE bestellungen 
ADD  CONSTRAINT bestellungen_bestelldatum_DF  
     DEFAULT (getdate()) FOR bestelldatum
GO
ALTER TABLE bestellungen 
ADD  CONSTRAINT bestellungen_letzterzugriff_DF  
     DEFAULT (getdate()) FOR letzterzugriff
GO

ALTER TABLE kuchen
WITH CHECK
ADD CONSTRAINT kuchen_sorte_FK 
    FOREIGN KEY (sorte) REFERENCES sorten (id);
GO

ALTER TABLE kuchen 
CHECK CONSTRAINT kuchen_sorte_FK
GO

ALTER TABLE gaeste
WITH CHECK
ADD CONSTRAINT gaeste_einrichtung_FK 
    FOREIGN KEY (einrichtung) REFERENCES einrichtungen (id);
GO

ALTER TABLE gaeste 
CHECK CONSTRAINT gaeste_einrichtung_FK
GO

ALTER TABLE bestellungen
WITH CHECK
ADD CONSTRAINT bestellungen_kuchen_FK 
    FOREIGN KEY (kuchen) REFERENCES kuchen (id) ON DELETE CASCADE;
GO

ALTER TABLE bestellungen 
CHECK CONSTRAINT bestellungen_kuchen_FK
GO

ALTER TABLE bestellungen
WITH CHECK
ADD CONSTRAINT bestellungen_gaeste_FK 
    FOREIGN KEY (gast) REFERENCES gaeste (id) ON DELETE CASCADE;
GO

ALTER TABLE bestellungen 
CHECK CONSTRAINT bestellungen_gaeste_FK
GO

CREATE INDEX kuchen_sorte ON kuchen
(
	sorte ASC
);
CREATE INDEX kuchen_kurz ON kuchen
(
	kurzkuchen ASC
);
GO

CREATE INDEX gaeste_einrichtung ON gaeste
(
	einrichtung ASC
);
CREATE INDEX gaeste_kurz ON gaeste
(
	kurzgast ASC
);
GO

CREATE INDEX bestellungen_gast ON bestellungen
(
	gast ASC
);
CREATE INDEX bestellungen_kuchen ON bestellungen
(
	kuchen ASC
);
GO

CREATE VIEW kuchenview
AS
SELECT  kuchen.id, kuchen.kurzkuchen, kuchen.anbieter, kuchen.beschreibung, sorten.backwarensorte, 
        kuchen.abgabedatum, kuchen.anzahl, kuchen.preis, kuchen.diabetikergeeignet, kuchen.letzterzugriff
FROM    kuchen INNER JOIN sorten ON kuchen.sorte = sorten.id
GO

CREATE VIEW gaesteview
AS
SELECT  gaeste.id, gaeste.kurzgast, gaeste.gastname, gaeste.vorname, einrichtungen.einrichtung, 
        gaeste.telefon, gaeste.letzterzugriff
FROM    gaeste INNER JOIN einrichtungen ON gaeste.einrichtung = einrichtungen.id
GO

CREATE VIEW bestellungenview
AS
SELECT bestellungen.id, bestellungen.bestelldatum, gaeste.kurzgast, kuchen.kurzkuchen, bestellungen.menge, 
       bestellungen.gesamtpreis, bestellungen.bezahlt, bestellungen.geliefert, gaeste.gastname, gaeste.vorname, 
       einrichtungen.einrichtung, kuchen.anbieter, kuchen.beschreibung, sorten.backwarensorte, 
       bestellungen.letzterzugriff, bestellungen.gast, bestellungen.kuchen
FROM   sorten INNER JOIN 
      (kuchen INNER JOIN 
      (einrichtungen INNER JOIN 
      (gaeste INNER JOIN bestellungen
       ON gaeste.ID = bestellungen.gast) 
       ON einrichtungen.ID = gaeste.einrichtung) 
       ON kuchen.ID = bestellungen.kuchen) 
       ON sorten.ID = kuchen.Sorte;
