db alt

-- data definition language
-- sql stub
-- wird erweitert am 31.10.

-- Tabellen löschen zu Beginn des Skripts
-- beachten Sie die Lösch-Reihenfolge


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Gast]') AND type in (N'U'))
DROP TABLE [Gast]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Produkt_hat_Zutat]') AND type in (N'U'))
DROP TABLE [Produkt_hat_Zutat]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[BE-Nutzer_hat_Rechte]') AND type in (N'U'))
DROP TABLE [BE-Nutzer_hat_Rechte]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Bewertung_Schreiben]') AND type in (N'U'))
DROP TABLE [Bewertung_Schreiben]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[FE-Nutzer_Freund]') AND type in (N'U'))
DROP TABLE [FE-Nutzer_Freund]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Student]') AND type in (N'U'))
DROP TABLE [Student]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Mitarbeiter]') AND type in (N'U'))
DROP TABLE [Mitarbeiter]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[FH-Angehörige]') AND type in (N'U'))
DROP TABLE [FH-Angehörige]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Zutat]') AND type in (N'U'))
DROP TABLE [Zutat]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Rechte]') AND type in (N'U'))
DROP TABLE [Rechte]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Kategorie]') AND type in (N'U'))
DROP TABLE [Kategorie]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Kaufen]') AND type in (N'U'))
DROP TABLE [Kaufen]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[FE-Nutzer]') AND type in (N'U'))
DROP TABLE [FE-Nutzer]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Zahlung]') AND type in (N'U'))
DROP TABLE [Zahlung]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Preis]') AND type in (N'U'))
DROP TABLE [Preis]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Produkt]') AND type in (N'U'))
DROP TABLE [Produkt]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Bewertung]') AND type in (N'U'))
DROP TABLE [Bewertung]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Bild]') AND type in (N'U'))
DROP TABLE [Bild]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[BE-Nutzer]') AND type in (N'U'))
DROP TABLE [BE-Nutzer]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Auth]') AND type in (N'U'))
DROP TABLE [Auth]



-- Tabellen definieren
--Auth
--Algo, Stretch, Hash, Salt
CREATE TABLE [Auth] (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Algo VARCHAR(6) NOT NULL CHECK(Algo='Sha1' OR Algo='Sha256'),
    Stretch INT NOT NULL,
    Salt CHAR(32) NOT NULL,
    [Hash] CHAR(24) NOT NULL,
    )

-- BE-Nutzer
-- ID, EMail, Lastlogin smalldatetime, Admin
CREATE TABLE [BE-Nutzer](
	ID INT IDENTITY (1,1) PRIMARY KEY,
	EMail VARCHAR(50) NOT NULL,
	Lastlogin smalldatetime NOT NULL,
	[Admin] BIT NOT NULL,
	Auth_ID INT NOT NULL REFERENCES Auth(ID),
	)

-- Rechte
-- ID, Tabelle
CREATE TABLE [Rechte](
	ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Tabelle VARCHAR(5000) NOT NULL CHECK (Tabelle='Kategorie' OR Tabelle='Produkt' OR Tabelle='Bild' OR Tabelle='Preis' OR Tabelle='Zutat'),
	)

-- Bewertung
-- ID, Note, Sichtungen, Bemerkungen
CREATE TABLE [Bewertung](
	ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Note INT NOT NULL,
	Sichtungen INT NOT NULL,
	Bemerkungen VARCHAR(250) NOT NULL,
	[BE-Nutzer_ID] INT NOT NULL REFERENCES [BE-Nutzer](ID),
	)

-- Bild
-- ID, Binärdaten, AltText, Title, Unterschrift
CREATE TABLE [Bild](
	ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
	Binärdaten [VARBINARY](MAX) NOT NULL,
	AltText VARCHAR(50) NOT NULL,
	Title VARCHAR(50) NOT NULL,
	Unterschrift VARCHAR(50),
	)

-- Kategorie
-- ID, Bezeichnung
CREATE TABLE [Kategorie](
	ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
	Bezeichnung VARCHAR(50),
	Bild_ID INT NOT NULL REFERENCES BILD (ID),
	Oberkategorie INT NOT NULL REFERENCES Kategorie(ID),
	)
-- Produkte
-- ID, Kategorie_ID-FK, Name, Beschreibung, von timestamp, bis timestamp

CREATE TABLE [Produkt] (
	ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Name VARCHAR(50) NOT NULL,
	Beschreibung VARCHAR(50) NOT NULL,
	von DATETIME NOT NULL,
	bis DATETIME NOT NULL,
	Bild_ID INT NOT NULL REFERENCES Bild(ID),
)

-- Zutat
-- ID, Bezeichnung
CREATE TABLE [Zutat](
	ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
	Bezeichnung VARCHAR(50) NOT NULL,
	)
-- Preis
-- ID, Rolle, Preis
CREATE TABLE [Preis](
	ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
	Rolle VARCHAR(50) NOT NULL CHECK(Rolle='Gast' OR Rolle='Student' OR Rolle='Mitarbeiter'),
	Preis INT NOT NULL,
	Produkt_ID INT NOT NULL REFERENCES Produkt(ID),
	)

-- Zahlung
-- ID, Status, Zeitstempel, Betrag
CREATE TABLE [Zahlung](
	ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
	[Status] VARCHAR(50) NOT NULL CHECK ([Status]>=10 AND [STatus]<=30),
	Zeitstempel TIME NOT NULL,
	Betrag INT NOT NULL,
	AuthServer VARCHAR(50) NOT NULL,
	)

-- FE-Nutzer
-- ID, lastlogin smalldatetime, Aktiv??, Auth??
CREATE TABLE [FE-Nutzer](
	Aktiv BIT NOT NULL,
	ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
	lastlogin smalldatetime NOT NULL,
	Benutzer_ID INT NOT NULL REFERENCES [BE-Nutzer](ID),
	Auth_ID INT NOT NULL REFERENCES Auth(ID),
	)

-- FH Angehörige
-- Email, Name, Fachbereich
CREATE TABLE [FH-Angehörige](
	EMail VARCHAR(50) NOT NULL UNIQUE,
	Name VARCHAR(50) NOT NULL,
	Fachbereich INT,
	ID INT NOT NULL PRIMARY KEY REFERENCES [FE-Nutzer](ID),
	)

-- Gast
-- Name, von DATETIME, bis DATETIME
CREATE TABLE [Gast](
	Name VARCHAR(50) NOT NULL,
	von DATETIME NOT NULL,
	bis DATETIME NOT NULL,
	ID INT NOT NULL PRIMARY KEY REFERENCES [FE-Nutzer](ID),
	)

-- Mitarbeiter
-- MA_Nummer, Büro
CREATE TABLE [Mitarbeiter](
	MA_Nummer INT NOT NULL,
	Büro VARCHAR(50) NOT NULL,
	[FH-Angehörige_ID] INT NOT NULL PRIMARY KEY REFERENCES [FH-Angehörige](ID) ON DELETE CASCADE,
	)

--Student
-- Matrikelnummer, Studiengang
CREATE TABLE [Student](
	Matrikelnummer INT NOT NULL CHECK(Matrikelnummer>='99999' AND Matrikelnummer <='999999999') UNIQUE,
	Studiengang VARCHAR(50) NOT NULL,
	[FH-Angehörige_ID] INT NOT NULL PRIMARY KEY REFERENCES [FH-Angehörige](ID) ON DELETE CASCADE,
	)

--Referenztabellen
CREATE TABLE [BE-Nutzer_hat_Rechte] (
    [BE-Nutzer_ID] INT NOT NULL REFERENCES [BE-Nutzer](ID),
    Rechte_ID INT NOT NULL REFERENCES Rechte(ID),
    )

CREATE TABLE [Produkt_hat_Zutat] (
    Produkt_ID INT NOT NULL REFERENCES Produkt(ID),
    Zutat_ID INT NOT NULL REFERENCES Zutat(ID),
    )

CREATE TABLE [FE-Nutzer_Freund] (
    ID INT NOT NULL PRIMARY KEY,
    [FE-Nutzer1] INT NOT NULL REFERENCES [FE-Nutzer](ID),
    [FE-Nutzer2] INT NOT NULL REFERENCES [FE-Nutzer](ID),
    )

CREATE TABLE [Bewertung_Schreiben] (
    ID INT NOT NULL PRIMARY KEY,
    [FH-Angehörige_ID] INT NOT NULL REFERENCES [FH-Angehörige](ID),
    Bewertung_ID INT NOT NULL REFERENCES Bewertung(ID),
    Produkt_ID INT NOT NULL REFERENCES Produkt(ID),
    )

CREATE TABLE [Kaufen] (
    ID INT NOT NULL PRIMARY KEY,
    Endpreis MONEY,
    [FE-Nutzer_ID] INT NOT NULL REFERENCES [FE-Nutzer](ID),
    Produkt_ID INT NOT NULL REFERENCES Produkt(ID),
    Zahlung_ID INT NOT NULL REFERENCES Zahlung(ID),
    )

-- Tabellen ändern (fügen Sie hier CHECK und andere Constraints, falls Sie sie nicht bei der Definition verwenden können)

-- ALTER TABLE [beispiel] ADD CONSTRAINT [constrname] CHECK (1=1)

ALTER TABLE [Zahlung] ADD CONSTRAINT [Status] Check(Status='10' OR Status='20' OR Status='30')