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 |

Syntax	Example	Reference
# Header	Header	基本排版
- Unordered List	Unordered List
1. Ordered List	Ordered List
- [ ] Todo List	Todo List
> Blockquote	Blockquote
Bold font	Bold font
Italics font	Italics font
~~Strikethrough~~	~~Strikethrough~~
19^th^	19^th
H~2~O	H₂O
++Inserted text++	Inserted text
==Marked text==	Marked text
[link text](https:// "title")	Link
![image alt](https:// "title")	Image
`Code`	`Code`	在筆記中貼入程式碼
```javascript var i = 0; ```	`var i = 0;`
:smile:		Emoji list
{%youtube youtube_id %}	Externals
$L^aT_eX$	L^aT_eX
:::info This is a alert area. :::	This is a alert area.