---
# System prepended metadata

title: Abschluss Projekt SQL

---

# Abschluss Projekt SQL
## Hausautomatisierungssystem SmartHome

[Dokumentation - HachMD](https://hackmd.io/@jO377qgzQsuX14T1WLnsZA/S1Ktgr8bkg/edit)
## Erarbeitet durch
* Kenan
* Marcel
* Philip
* Maria

## Abstrakt

Abstrakt zum Thema Hausautomation "SmartHome" mit Fokus auf SQL-Datenbank-Projekt

Das Projekt "SmartHome", setzt eine SQL-Datenbank um, die die Verwaltung von Personen, Räumen, Geräten, Herstellern und Zuständen innerhalb eines intelligenten Wohnumfelds unterstützt. 

Die SQL-Datenbank wird so konzipiert, dass jeder Person ein Profil zugewiesen wird. Diese Profile ermöglichen die individuelle Anpassung von Zugriffsrechten für die Steuerung einzelner Geräte und Gerätegruppen im Haushalt. Gleichzeitig wird eine Raumverwaltung implementiert, die es ermöglicht, unterschiedliche Wohnbereiche zu definieren und spezifische Geräte zuzuordnen. So können Personen, abhängig von ihrer Berechtigung, sowohl auf komplette Gerätegruppen und/oder einzelne Räume und deren Geräte zugreifen.

Ein zentrales Element des Projekts ist die Geräteverwaltung, die die Integration von Smart-Devices verschiedener Hersteller ermöglicht. Hierbei werden relevante Informationen zu jedem Gerät, wie Typ, Hersteller und aktuelle Zustände, in der Datenbank erfasst. Dies erlaubt eine umfassende Überwachung und Kontrolle der Geräte über eine zentrale Schnittstelle.

Darüber hinaus wird ein Zustandsmanagement implementiert, das es den Personen ermöglicht, den Status jedes Geräts in Echtzeit zu überwachen und zu steuern. Dies umfasst Funktionen wie das Ein- und Ausschalten von Geräten, das Anpassen von Einstellungen und das Erstellen von Automatisierungsregeln basierend auf bestimmten (zeitspezifischen) Bedingungen.

Es können einzelne Geräte hinzugefügt werden, was automatisch das Hinzufügen von Herstellern inkludiert, sollte der Hersteller des Gerätes noch nicht im Smarthome präsent sein. 
Zudem können neue Personen hinzugefügt werden, die dann, abhängig von ihren jeweiligen Zugriffsrechten, auf bestimmte Gerätegruppen zugreifen können. 

Ein Gerät gehört einer Gerätegruppe an, hat einen Hersteller, eine Farbe (vor allem für Lichter relevant), einen Betriebszustand (z.B. eingeschaltet) und kann mehrere Funktionen (z.B. Licht und Sicherheitsüberwachung) und Zeitpläne haben.




## Schema

|Tabellen|Beschreibung|
|--|--|
| Räume                            | Enthält lediglich Raumbezeichnung. Ein Raum kann mehrere Geräte aus unterschiedlichen Gruppen beinhalten, was über Tabelle VerbindungRaumGruppe aufgelöst wird. |
| Gruppen                          | Sammlung von Geräten über Räume hinweg. Eine Gruppe kann in mehrere Räumen existieren, was über Tabelle VerbindungRaumGruppe aufgelöst wird.|
| Geräte                           | Eine steuerbare Entität. Kann hinzugefügt werden. Hat BerechtigungsID, GruppenID, FarbenID, EinschaltZustand und Bezeichnung. Wird ein Gerät ausgeschaltet, ändert sich die FarbenID auf 13 (Schwarz).|
| Hersteller                       | Bezugsquelle von Geräten. Hat Bezeichnung und Adresse.|
| Zustände| Aktueller Funktionszustand und ein zugehöriger Zeitplan. Ein Gerät kann mehrer Funktionen und Zeitpläne haben, weshalb diese Tabelle als Verbindungstabelle zwischen Geräte, Funktionen und Zeitplan fungiert.|
| Farben                           | Numerischer Wert eines Farbmodus|
| Funktionen                       | Beschreibung und ein Individueller Wert zugehörig zu einem Gerät|
| EinschaltZustand                 | Wert zur Art des Betriebsmodus |
| Personen                         | Entität welche Berechtigung besitzt und Geräte manipulieren kann. Hat Vorname, Nachname und Alter. |
| Berechtigungen                   | Numerische Bedingung zur manipulation der Zustands-Werte von Geräte |

|Views                             |Beschreibung|
|--                                |--|
| VW_BerechtigungenEinerPerson     | Zeigt Übersicht mit welchen Geräten in einem Raum eine Personen interagieren darf |
| VW_GeraeteImRaum                 | Gibt alle Geräte in einem selektierten Raum aus. Zudem wird der Raum, der Einschaltzustand, die Gruppenbezeichnung ausgegeben. |
| VW_GeraeteImRaumEinRaum          | Gibt Spalte RaumID zurück mit genau so vielen Einträgen wie es Geräte gibt |
| VW_RauemeGeraete                 | Gibt alle Räume und ihre dazugehörigen Geräte aus. |
| VW_GeraeteInAllenRaeumen         | Gibt die Anzahl der Geräte in allen Räumen aus |
| VW_BerechtigungenAllerPersonen   | Gibt alle Geräte die eine Person bedienen darf zurück. **(Tabellenwert-Funktion)** |

|Funktionen-Scalarwert             |Beschreibung|
|--                                |--|
| SF_AnzahlGeraeteImRaum           | Gibt die Anzahl der Geräte in einem Raum Zurück als Scalar-Wert **(Prozedur Hinzufügen Geräte)** |
| SF_Existiert                     | Prüft ob HerstellerID bereits existiert.**(Prozedur Hinzufügen Geräte)** |
| SF_Anzahl                        | Gibt die Anzahl der Geräte zurück. **(Prozedur Hinzufügen Geräte)**|

|Funktionen-Tabellenwert           |Beschreibung|
|--                                |--|
| TF_PersonenBerechtigungAllerPersonen | Gibt von allen Personen deren Berechtigungen zurück |

|Prozeduren                        |Beschreibung|
|--                                |--|
| PR_PersonHinzufügen              | Fügt ein Gerät hinzu. Wird eine HerstellerID übergeben, wird davon ausgegangen, dass dieser Hersteller bereits existiert. Dies wird zusätzlich überprüft. Wird keine HerstellerID angegeben, wird davon ausgegangen, dass der Hersteller nicht existiert und wird dann von der Prozedur angelegt. Dazu muss die Herstellerbezeichnung und die Herstellerbezugsadresse übergeben werden. Das gleiche gilt für die Gerätegruppe.|
| PR_GeraetHinzufuegen             | Fügt eine neue Person hinzu. |


|Trigger                           |Beschreibung|
|--                                |--|
| TR_LampeAus                      | Schaltet Licht auf Schwarz, wenn Lampe ausgeschaltet wird. Ändert entsprechende Werte in  der Geräte Tabelle. |
| TR_LampeAn                       | Schaltet Licht auf einen zufälligen Wert zwischen 1 und 16 ohne 13 (schwarz), wenn Lampe eingeschaltet wird. |


## Diagramm
<div style="page-break-after: always;"></div>

<img src="./Diagramm_2.png" width="1000" alt="Diagramm" title="SQL-Diagramm" />


<p>Please download the PDF to view it: <a href="./Diagramm_2.pdf">Download PDF</a>.</p>



<div style="page-break-after: always;"></div>

## Create-Script
* [x] Erzeugt Datenbank
* [x] Erzeugt 11 Tabellen
* [x] Bevölkert diese mit den nötigen Daten
* [x] Redundanzen und Inkonsistenzen beseitigen
    * [x] !Zustände Liste indizieren
    * [x] VerbindungRaumGruppe
* [x] Dynamische Daten
* [x] Forein Key verknüpfungen erstellt
* [x] Primär-Indexes erstellt
* [x] Sekundär-Indizes erstellt
* [x] Beziehungen zwischen Tabellen erstellt
* [x] Min eine m:n-Verbindung
* [x] Standart-Wert festlegen
* [x] Einschränkungen festlegen
* [x] Diagramm erstellen
* [x] Vews erstellt
    * [x] mehrere Tabellen mit ```INNER JOIN``` verknüpft
    * [x] mehrere Tabellen, mit ```GROUP BY```, ```SUM```, ```COUNT```, ```HAVING```
    * [x] Mehrere Tabellen, mit ```LEFT|RIGHT|FULL OUTER JOIN``` oder ```SELF JOIN```
    * [x] Test erstellen
    * [x] Skripte erstellen
* [x] Funktionen ertellt
    * [x] Scalrwert
    * [x] Tabellenwert
    * [x] Test erstellen
    * [x] Skripte erstellen
* [x] Prozeduren
    * [x] mit Parameter
    * [x] Buisness-Logik
    * [x] Soll Eingangs-Parameter prüfen und Daten in Tabelle ändern
    * [x] eine Gespeicherte Funktion wieder verwenden
    * [x] OUTPUT-Parameter für Fhelermeldung verwenden
    * [x] Test erstellen
    * [x] Scripte erstellen
* [x] Trigger
    * [x] ein Trigger (INSERT, UPDATE oder DELETE)
    * [x] Trigger soll Buissnes-Prozesse automatisieren
    * [x] !Tests erstellen
        * [x] Update auf Gerät(Lampe)
    * [x] Skript erstellen
* [x] !Backup-Prozeduren
    * [x] DB sichern
        * [x] alle Tabellen,Sichten, Prozeduren
        * [x] alle Logins,User, Rollen
        * [x] alle Daten
        * [ ] ~~.mdf und .ldf speichern~~
        * [x] Skripte erstellen
* [ ] ~~Anmeldung~~
    * [ ] ~~DB-Benutzer Berechtigungen~~
    * [ ] ~~Skripte eerstellen~~
* [ ] Für ```SEHR GUT```
    * [ ] ~~Frontend erstellen~~
    * [x] Import Tabelle Personen
    * [ ] ~~oder 1:1 Beziehung~~
    * [ ] ~~oder 1 Zirkelbezug + Views~~
    * [x] oder mehr Funktionen, Prozeduren und/oder Trigger schreiben
* [x] Projekt Doku
    * [x] PDF/HTML
    * [x] Diagramm
* [x] !Kommentare
* [x] Daten aufbereiten
* [x] Tests
    * [x] View 05_VW_RaeumeGeraete
    * [x] Skalarwert-Funktion SF_Existiert


## User Story

1. Ein Gerät hinzufügen 
    1. Gruppe Hinzufügen
    1. Hersteller hizufügen
    1. ~~Zeitplan hinzufügen~~

2. Person hinzufügen 
3. Lichter ausschalten
4. ~~Raum Hinzufügen~~


## Master-Skript

```sql=
-- Erstellen der Datenbank SmartHome
CREATE DATABASE SmartHome
ON PRIMARY
(
    NAME = 'SmartHome_Data',
    FILENAME = 'C:\SQL-Kurs\Projekt\SmartHome_Data.mdf',
    SIZE = 10MB,
    MAXSIZE = 500MB,
    FILEGROWTH = 10%
)
LOG ON
(
    NAME = 'SmartHome_Log',
    FILENAME = 'C:\SQL-Kurs\Projekt\SmartHome_log.ldf',
    SIZE = 5MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 5MB
);
GO
-- Wähle die Datenbank SmartHome
USE SmartHome;

GO

-- RÄUME
CREATE TABLE Raeume
(
 RaumID INTEGER NOT NULL PRIMARY KEY IDENTITY(1,1),
 --GruppenID INTEGER NOT NULL,
 BezeichnungRaum nvarchar(255) NOT NULL
 );
GO

--ALTER TABLE [Raeume]
--(
--FOREIGN KEY (GruppenID) REFERENCES Gruppen(GruppenID) ON DELETE CASCADE
--) und Indezes


INSERT INTO Raeume (BezeichnungRaum)
VALUES 
    ('Kueche'),
    ('Wohnzimmer'),
    ('Kinderzimmer1'),
	('Kinderzimmer2'),
	('Badezimmer'),
	('Garten'),
	('Keller'),
	('Schlafzimmer'),
	('Dachboden');
-- 
  
GO 

-- VerbindungRaumGruppe
CREATE TABLE VerbindungRaumGruppe(
 GruppenID INT NOT NULL,
 RaumID INT NOT NULL
);

INSERT INTO VerbindungRaumGruppe (GruppenID, RaumID)
VALUES 
    (1, 1),
	(2, 1),
	(2, 2),
	(2, 3),
	(2, 4),
	(2, 5),
	(2, 6),
	(2, 7),
	(2, 8),
	(2, 9),
	(3, 2),
	(3, 3),
	(3, 4),
	(3, 8);

GO
-- Personen
CREATE TABLE Personen
(
 PersonenID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
 BerechtigungsID INT NOT NULL DEFAULT 3,
 Vorname NVARCHAR(255) NOT NULL,
 Nachname NVARCHAR(255) NOT NULL,
 Age INT 
);

BULK INSERT Personen
FROM 'C:\SQL-Kurs\Projekt\daten\personendaten.csv'
WITH (
    FIELDTERMINATOR = ',',  -- Trennzeichen, z.B. Komma
    ROWTERMINATOR = '\n',   -- Zeilenumbruch
    FIRSTROW = 1,            -- falls die erste Zeile Spaltennamen enthält
	CODEPAGE = '65001'
);

    
GO
-- Gruppen (alt Geraetegruppen)
CREATE TABLE Gruppen(
 GruppenID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
 BezeichnungGruppe VARCHAR(255) NOT NULL
);

INSERT INTO Gruppen (BezeichnungGruppe)
VALUES 
    ('Küchengeräte'),
	('Lichter'),
	('Media'),
	('Garten'),
	('Fitness'),
	('Werkzeuge'),
	('Klima');
--
GO
-- Berechtigungen
CREATE TABLE Berechtigungen(
 BerechtigungsID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
 Beschreibung NVARCHAR(255) NOT NULL,
 BerechtigungsNummer INT NOT NULL
)

INSERT INTO Berechtigungen (Beschreibung, BerechtigungsNummer)
VALUES 
    ('Admin', 1),
    ('User', 2),
    ('Gast', 3);
--
GO
-- Geraete
CREATE TABLE Geraete
(
 GeraeteID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
 BerechtigungsID INT NOT NULL,
 GruppenID INT NOT NULL,
 HerstellerID INT NOT NULL, 
 FarbenID INT NOT NULL,
 EinschaltZustandID INT NOT NULL,
 BezeichnungGeraet NVARCHAR(255) NOT NULL
);

INSERT INTO Geraete (BerechtigungsID, GruppenID, HerstellerID, FarbenID, EinschaltZustandID, BezeichnungGeraet)
VALUES 
    (2, 1, 1, 1, 1, 'Smart Kühlschrank mit WLAN'),
	(2, 1, 1, 1, 2, 'Intelligenter Backofen'),
	(2, 1, 5, 1, 2, 'Smart Küchenmaschine'),
	(2, 1, 2, 1, 3, 'Smart Wasserkocher'),
	(3, 2, 3, 2, 3, 'Smart LED-Lampe'),
	(3, 2, 3, 3, 4, 'Philips Hue Bewegungssensor'),
	(3, 2, 4, 4, 4, 'Smart Lichtschalter'),
	(3, 2, 4, 2, 4, 'Amazon Echo Glow'),
	(2, 3, 1, 1, 2, 'Smart TV 4K'),
	(2, 3, 2, 1, 6, 'Google Chromecast'),
	(2, 3, 1, 1, 6, 'Samsung Soundbar'),
	(2, 4, 6, 1, 5, 'Smart Bewässerungssystem'),
	(2, 4, 7, 1, 5, 'Intelligenter Rasenmäher'),
	(2, 4, 8, 1, 7, 'Smart Gartenbeleuchtung'),
	(2, 5, 9, 1, 7, 'Smart Fitness Tracker'),
	(2, 5, 9, 1, 2, 'Intelligente Waage'),
	(2, 5, 6, 1, 2, 'Smart Wasserflasche'),
	(1, 6, 10, 1, 2, 'Smart Akku-Bohrmaschine'),
	(1, 6, 10, 1, 4, 'Intelligenter Schraubendreher'),
	(1, 7, 11, 1, 3, 'Smart Klimaanlage mit App-Steuerung'),
	(1, 7, 11, 1, 6, 'Intelligenter Luftentfeuchter');
--
GO

-- Hersteller
CREATE TABLE Hersteller
(
    HerstellerID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    --GeraeteID INT,
    BezeichnungHersteller NVARCHAR(255) NOT NULL,
    HerstellerBezugsAdresse NVARCHAR(255) NOT NULL,
);

INSERT INTO Hersteller (BezeichnungHersteller, HerstellerBezugsAdresse)
VALUES 
    ('Samsung Electronics', 'Samsung Town, 129 Samsung-ro, Yeongtong-gu, Suwon-si, Gyeonggi-do, Südkorea'),
    ('Google Nest', '1600 Amphitheatre Parkway, Mountain View, CA 94043, USA'),
    ('Philips Hue', 'High Tech Campus 48, 5656 AE Eindhoven, Niederlande'),
    ('Amazon', '410 Terry Ave N, Seattle, WA 98109, USA'),
    ('Apple HomeKit', '1 Apple Park Way, Cupertino, CA 95014, USA'),
    ('TP-Link', '5500 South 1st Street, Suite 100, Austin, TX 78704, USA'),
    ('Ecobee', '75 Sherbourne St, Suite 300, Toronto, ON M5A 2P9, Kanada'),
    ('Ring', '1523 26th St, Santa Monica, CA 90404, USA'),
    ('Netatmo', '14-16 Rue de la République, 75011 Paris, Frankreich'),
    ('Honeywell Home', '1151 Maplewood Drive, Itasca, IL 60143, USA'),
    ('Xiaomi', 'No.006, Middle Xierqi Road, Haidian District, Beijing');
--
GO
-- Zustaende
CREATE TABLE Zustaende
(
 --ZustandID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
 GeraeteID INT NOT NULL,
 FunktionenID INT NOT NULL,
 ZeitplanID INT NOT NULL
 --Beschreibung NVARCHAR(255) NOT NULL
);

BULK INSERT Zustaende
FROM 'C:\SQL-Kurs\Projekt\daten\zustaende.csv'
WITH (
    FIELDTERMINATOR = ',',       -- Spaltentrennzeichen, z. B. Komma
    ROWTERMINATOR = '\n',        -- Zeilentrennzeichen, z. B. Zeilenumbruch
    FIRSTROW = 1,                -- Falls die erste Zeile Spaltennamen enthält
    CODEPAGE = '65001'           -- Setzt das Encoding auf UTF-8
);
GO

-- EinschaltZustand
CREATE TABLE EinschaltZustand
(
    EinschaltZustandID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    Zustand BIT NOT NULL,
    Beschreibung NVARCHAR(255) NOT NULL
);

INSERT INTO EinschaltZustand (Zustand, Beschreibung)
VALUES 
    (1, 'Eingeschaltet'),
    (0, 'Ausschaltet'),
	(1, 'Aktiv'),
    (0, 'Inaktiv'),
    (0, 'Energiesparmodus'),
    (0, 'Fehlerzustand'),
    (1, 'Systemstart'),
    (0, 'System heruntergefahren');
--
GO
-- Funktionen
CREATE TABLE Funktionen
(
    FunktionenID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    Beschreibung NVARCHAR(255) NOT NULL,
    Wert NVARCHAR(255) NOT NULL
);

BULK INSERT Funktionen
FROM 'C:\SQL-Kurs\Projekt\daten\Funktionen.csv'
WITH (
    FIELDTERMINATOR = ',',       -- Spaltentrennzeichen, z. B. Komma
    ROWTERMINATOR = '\n',        -- Zeilentrennzeichen, z. B. Zeilenumbruch
    FIRSTROW = 1,                -- Falls die erste Zeile Spaltennamen enthält
    CODEPAGE = '65001'           -- Setzt das Encoding auf UTF-8
);
GO

-- Farben (Optional)
CREATE TABLE Farben(
	FarbenID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    Rot INT NOT NULL,
    Gruen INT NOT NULL,
    Blau INT NOT NULL,
    Alpha INT NOT NULL,
	Farben nvarchar(50) NOT NULL
);

INSERT INTO Farben (Rot,Gruen,Blau,Alpha,Farben)
VALUES  (0,255,255,1,'Aqua'),
        (0,0,255,1,'Blau'),
        (165,42,42,1,'Braun'),
        (0,255,255,1,'Cyan'),
        (255,255,0,1,'Gelb'),
        (11,11,11,0.2,'Grau'),
        (0,255,0,1,'Grün'),
        (75,0,130,1,'Lila'),
        (255,0,255,1,'Magenta'),
        (255,160,122,1,'Orange'),
        (255,192,203,1,'Pink'),
        (255,0,0,1,'Rot'),
        (0,0,0,1,'Schwarz'),
        (64,224,208,1,'Türkis'),
        (238,130,238,1,'Violett'),
        (255,255,255,1,'Weiß');
GO

-- Zeitplan
CREATE TABLE Zeitplan(
    ZeitplanID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    DatumStart datetime NOT NULL,
    DatumEnd datetime NOT NULL,
    Funktion NVARCHAR(255) NOT NULL
);

BULK INSERT Zeitplan
FROM 'C:\SQL-Kurs\Projekt\daten\zeitplan.csv'
WITH (
    FIELDTERMINATOR = ',',       -- Spaltentrennzeichen, z. B. Komma
    ROWTERMINATOR = '\n',        -- Zeilentrennzeichen, z. B. Zeilenumbruch
    FIRSTROW = 1,                -- Falls die erste Zeile Spaltennamen enthält
    CODEPAGE = '65001'           -- Setzt das Encoding auf UTF-8
);
GO
-- Personen
ALTER TABLE Personen
ADD CONSTRAINT FK_Personen_Berechtigungen
FOREIGN KEY (BerechtigungsID)
REFERENCES Berechtigungen(BerechtigungsID);


GO

-- ALTER TABLE Berechtigungen

-- Geraete
ALTER TABLE Geraete
ADD CONSTRAINT FK_Geraete_Berechtigungen FOREIGN KEY (BerechtigungsID) REFERENCES Berechtigungen(BerechtigungsID);

ALTER TABLE Geraete
ADD CONSTRAINT FK_Geraete_Gruppen FOREIGN KEY (GruppenID) REFERENCES Gruppen(GruppenID);

ALTER TABLE Geraete
ADD CONSTRAINT FK_Geraete_Hersteller FOREIGN KEY (HerstellerID) REFERENCES Hersteller(HerstellerID);

ALTER TABLE Geraete
ADD CONSTRAINT FK_Geraete_Farben FOREIGN KEY (FarbenID) REFERENCES Farben(FarbenID);

ALTER TABLE Geraete
ADD CONSTRAINT FK_Geraete_EinschaltZustand FOREIGN KEY (EinschaltZustandID) REFERENCES EinschaltZustand(EinschaltZustandID);

GO
-- Zustaende

ALTER TABLE Zustaende
ADD CONSTRAINT FK_Zustaende_Funktionen FOREIGN KEY (FunktionenID) REFERENCES Funktionen(FunktionenID);

ALTER TABLE Zustaende
ADD CONSTRAINT FK_Zustaende_Zeitplan FOREIGN KEY (ZeitplanID) REFERENCES Zeitplan(ZeitplanID);

ALTER TABLE Zustaende
ADD CONSTRAINT FK_Zustaende_Geraete FOREIGN KEY (GeraeteID) REFERENCES Geraete(GeraeteID);

ALTER TABLE VerbindungRaumGruppe
ADD CONSTRAINT FK_VerbindungRaum FOREIGN KEY (RaumID) REFERENCES Raeume(RaumID);

ALTER TABLE VerbindungRaumGruppe
ADD CONSTRAINT FK_VerbindungGruppe FOREIGN KEY (GruppenID) REFERENCES Gruppen(GruppenID);


-- INDEX

CREATE UNIQUE NONCLUSTERED INDEX IX_Berechtigungen_Berechtigung
ON Berechtigungen (BerechtigungsNummer, Beschreibung); 

CREATE UNIQUE NONCLUSTERED INDEX IX_VerbindungRaumGruppe
ON VerbindungRaumGruppe (RaumID, GruppenID); 

CREATE UNIQUE NONCLUSTERED INDEX IX_Hersteller_Bezugsadresse 
ON Hersteller (BezeichnungHersteller, HerstellerBezugsAdresse);

CREATE UNIQUE NONCLUSTERED INDEX IX_EinschaltBeschreibung 
ON EinschaltZustand (Beschreibung);


CREATE UNIQUE NONCLUSTERED INDEX IX_Zeitplan_DatumStart 
ON Zeitplan (DatumStart, Funktion);

CREATE UNIQUE NONCLUSTERED INDEX IX_Zustaende_GeraeteFunktionZeitplan 
ON Zustaende (GeraeteID, FunktionenID, ZeitplanID);
--


-- Einschränkungen --

ALTER TABLE Personen
ADD CONSTRAINT CK_age CHECK (Age BETWEEN 0 AND 150);

ALTER TABLE Farben
ADD CONSTRAINT CK_rot CHECK (Rot BETWEEN 0 AND 255);

ALTER TABLE Farben
ADD CONSTRAINT CK_gruen CHECK (Gruen BETWEEN 0 AND 255);

ALTER TABLE Farben
ADD CONSTRAINT CK_blau CHECK (Blau BETWEEN 0 AND 255);

ALTER TABLE Farben
ADD CONSTRAINT CK_alpha CHECK (Alpha BETWEEN 0 AND 255);

ALTER TABLE Zeitplan
ADD CONSTRAINT CK_DatumEnde CHECK (DatumEnd > DatumStart);


GO



```

## Testumgebung

```sql=
USE SmartHome;
GO
-- VIEWS
SELECT * FROM VW_BerechtigungenEinerPerson;
SELECT * FROM VW_GeraeteImRaum;

SELECT * FROM VW_GeraeteInAllenRaeumen;
SELECT * FROM VW_GeraeteImRaum;
SELECT * FROM VW_BerechtigungenEinerPerson;


--SCALAR
SELECT dbo.SF_AnzahlGeraeteImRaum(1) AS AnzahlGeraeteImRaum;


--TABELLENWERT
SELECT * FROM dbo.TF_PersonenBerechtigungAllerPersonen(1); 
-- Ausgabe:: Tabelle mit Namen Max Mustermann seiner Berechtigung und allen Geräten die er bedienen darf
SELECT * FROM dbo.TF_PersonenBerechtigungAllerPersonen(2);
-- Ausgabe:: Tabelle mit Namen Anna Müller ihrer Berechtigung und allen Geräten die sie bedienen darf
SELECT * FROM dbo.TF_PersonenBerechtigungAllerPersonen(10);
-- Ausgabe:: Tabelle ohne Namen da die Person nicht existiert

--PROZEDUR PERSONEN
EXEC PR_PersonHinzufügen @Vorname= 'Paul', @Nachname= 'Laub', @BerechtigungsID= 2, @Age=94;
-- Ausgabe:: Person Paul Laub mit der Berechtigung User und dem	Alter 94 wurde hinzugefügt
SELECT * 
FROM Personen 
WHERE PersonenID = (
    SELECT MAX(PersonenID) FROM Personen
    );
--Ausgabe:: Die Betreffende Zeile mit Paul Laub der Tasbelle Personen wurde ausgegeben
    
EXEC PR_PersonHinzufügen @Vorname= 'Paul', @Nachname= 'Laub', @BerechtigungsID= 2, @Age=94;
--Ausgabe:: Error:50000 Die Person gibt es schon.

--PROZEDUR GERAETE 
EXEC dbo.PR_GeraetHinzufuegen @BerechtigungsID = 1, 
							  @GruppenID = 1, 
							  @HerstellerID = 1, 
							  @RaeumeID = 2, 
							  @BezeichnungGeraet = 'Handschuhwärmer';
-- Ausgabe:: Gerät wurde hinzugefügt.
SELECT * 
FROM Geraete 
WHERE GeraeteID = (
    SELECT MAX(GeraeteID) FROM Geraete
    );	
--Ausgabe:: Gibt die betreffende Zeile aus Geraete zurück.



EXEC dbo.PR_GeraetHinzufuegen @BerechtigungsID = 1, 
							  @GruppenID = 1, 
							  @RaeumeID = 2, 
							  @BezeichnungGeraet = 'Smart Kulli', 
							  @BezeichnungHersteller='Grosshandel', 
							  @HerstellerBezugsadresse='Vodere Gasse 1,873925 Irgendwo';
-- Ausgabe:: Gerät wurde hinzugefügt.
SELECT * 
FROM Geraete 
WHERE GeraeteID = (
    SELECT MAX(GeraeteID) FROM Geraete
    );
-- Ausgabe:: Entsprechende neue Zeile der Tabelle Geräte
SELECT * 
FROM Hersteller 
WHERE HerstellerID = (
    SELECT MAX(HerstellerID) FROM Hersteller
    );	
-- Ausgabe:: Entsprechende neue Zeile der Tabelle Hersteller


EXEC dbo.PR_GeraetHinzufuegen @BerechtigungsID = 1, 
							  @HerstellerID=4, 
							  @BezeichnungGeraet = 'Smart Uhr3', 
							  @RaeumeID = 2, 
							  @BezeichnungGruppe='Chronometer';
-- Ausgabe:: Gerät wurde hinzugefügt.
SELECT * 
FROM Geraete 
WHERE GeraeteID = (
    SELECT MAX(GeraeteID) FROM Geraete
    );
-- Ausgabe:: Entsprechende neue Zeile der Tabelle Geräte
SELECT * 
FROM Gruppen 
WHERE GruppenID = (
    SELECT MAX(GruppenID) FROM Gruppen
    );	
-- Ausgabe:: Entsprechende neue Zeile der Tabelle Gruppe


EXEC dbo.PR_GeraetHinzufuegen @BerechtigungsID = 1,@HerstellerID=4, @BezeichnungGeraet = 'Smart Kühlschrank mit WLAN';
-- Ausgabe:: Error:50000 Gerät gibt es schon.
SELECT * 
FROM Geraete 
--WHERE GeraeteID = (
  --  SELECT MAX(GeraeteID) FROM Geraete
  --  );
-- Ausgabe::Die Letzte Zeile der Tabelle Geräte, inder das neue Gerät nicht zu finden ist.



SELECT *
FROM Geraete
WHERE GeraeteID = 5
-- Ausgabe:: Vorherzustand Gerät nummer 5
--	EinschaltZustand = 3
--  Farbe = 2

UPDATE Geraete 
	SET EinschaltZustandID = 2
	WHERE GeraeteID = 5 
-- Ausgabe:: Gerät 5 EinschaltZustand(2) 'aus'-> Trigger verändert Farbe auf Schwarz(13)
SELECT *
FROM Geraete
WHERE GeraeteID = 5
-- Ausgabe:: Vorherzustand Gerät nummer 5
--	EinschaltZustand = 2
--  Farbe = 13


UPDATE Geraete 
	SET EinschaltZustandID = 1
	WHERE GeraeteID = 5 
-- Ausgabe:: Gerät 5 EinschaltZustand(1) 'an'-> Trigger verändert Farbe auf Zufall
SELECT *
FROM Geraete
WHERE GeraeteID = 5
-- Ausgabe:: Vorherzustand Gerät nummer 5
--	EinschaltZustand = 1
--  Farbe = Zwischen 1 und 16

```


## Backup

```sql=
BACKUP DATABASE DataBasename
TO DISK = './backup/DataDasename.bak'
WITH DIFFERENTIAL;

SET @backupname =
	(
        -- Zur fachgerechten ausführung den entsprechenden Pfad  wählen!
        -- 
        --'C:\SQL-Kurs\Projekt\SmartHome'
	    'D:\uni\Weiterbildung\SQL-Kurs\SQL-Kurs\projekt\backup\SmartHome'
        + CONVERT(NVARCHAR, GETDATE(), 105) + '.bak'
	)

    -- Insert statements for trigger here
	BACKUP DATABASE SmartHome
	TO DISK = @backupname;
	--WITH DIFFERENTIAL;

```


## ~~Anmeldung, Benutzer, Rechte~~
 * [ ] Admin
   * [ ] besitzt alle Rechte der Datenbank


<div style="page-break-after: always;"></div>

## StyleGuide
* Sprache Deutsch
  
 |Bezeichner|Umsetzung| 
|--|--|
| Variablen | PascalCase |
| Funktionen | PascalCase |
| Tabellen | PascalCase |
| Felder | PascalCase  |


<!--
## Frontend über statisches HTML und JS
<details>
   <summary>Singlepager, Abfrage über JS-Fetch, Node.js, Übergabe an Webserver </summary>
<br>


Vorbereiten des Servers:
* aufsetzen eines MS SQL Servers in einer vorhandenen Docker Umgebung
    * zugänglich Machung vom Web
    * Ports nach außen reichen
    * Bind an Domain

```bash
   docker pull mcr.microsoft.com/mssql/server:2022-latest

   docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=yourStrong(!)Password" -e "MSSQL_PID=Evaluation" -p 1433:1433  --name sqlpreview --hostname sqlpreview -d mcr.microsoft.com/mssql/server:2022-preview-ubuntu-22.04

   docker exec -it <container_id|container_name> /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <your_password>


   #sqlcmd -S localhost -U mssql -P <yourpassword> -No

```

Vorbereiten des Node Environments:

```bash
mkdir mssql-nodejs-app
cd mssql-nodejs-app
```
```bash
npm init -y
````
```bash
npm install tedious mssql
```
Javascript-Fetch Datei auf dem Server:

```javascript
  // app.js
const sql = require('mssql');

// Connection configuration
const config = {
    user: 'your_username',
    password: 'your_password',
    server: 'your_server_address', // e.g., 'localhost'
    database: 'your_database_name',
    options: {
        encrypt: true, // Use this if you're on Azure
        trustServerCertificate: true // Use this if you're on a self-signed certificate
    }
};

// Function to connect to the database and execute a query
async function runQuery() {
    try {
        // Connect to the database
        await sql.connect(config);

        // Execute a simple query
        const result = await sql.query`SELECT * FROM your_table_name`;

        // Log the results
        console.log(result.recordset);
    } catch (err) {
        // Handle errors
        console.error('Error:', err);
    } finally {
        // Close the connection
        sql.close();
    }
}

// Run the query
runQuery();
```
Starten des Node-Servers:
```bash
node app.js
```
</details>
-->