· 6 years ago · Nov 12, 2019, 04:16 PM
1-- data definition language
2-- sql stub
3-- wird erweitert am 31.10.
4
5-- Tabellen löschen zu Beginn des Skripts
6-- beachten Sie die Lösch-Reihenfolge
7
8
9IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Gast]') AND type in (N'U'))
10DROP TABLE [Gast]
11
12IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Produkt_hat_Zutat]') AND type in (N'U'))
13DROP TABLE [Produkt_hat_Zutat]
14
15IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[BE-Nutzer_hat_Rechte]') AND type in (N'U'))
16DROP TABLE [BE-Nutzer_hat_Rechte]
17
18IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Bewertung_Schreiben]') AND type in (N'U'))
19DROP TABLE [Bewertung_Schreiben]
20
21IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[FE-Nutzer_Freund]') AND type in (N'U'))
22DROP TABLE [FE-Nutzer_Freund]
23
24IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Student]') AND type in (N'U'))
25DROP TABLE [Student]
26
27IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Mitarbeiter]') AND type in (N'U'))
28DROP TABLE [Mitarbeiter]
29
30IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[FH-Angehörige]') AND type in (N'U'))
31DROP TABLE [FH-Angehörige]
32
33IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Zutat]') AND type in (N'U'))
34DROP TABLE [Zutat]
35
36IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Rechte]') AND type in (N'U'))
37DROP TABLE [Rechte]
38
39IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Kategorie]') AND type in (N'U'))
40DROP TABLE [Kategorie]
41
42IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Kaufen]') AND type in (N'U'))
43DROP TABLE [Kaufen]
44
45IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[FE-Nutzer]') AND type in (N'U'))
46DROP TABLE [FE-Nutzer]
47
48IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Zahlung]') AND type in (N'U'))
49DROP TABLE [Zahlung]
50
51IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Preis]') AND type in (N'U'))
52DROP TABLE [Preis]
53
54IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Produkt]') AND type in (N'U'))
55DROP TABLE [Produkt]
56
57IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Bewertung]') AND type in (N'U'))
58DROP TABLE [Bewertung]
59
60IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Bild]') AND type in (N'U'))
61DROP TABLE [Bild]
62
63IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[BE-Nutzer]') AND type in (N'U'))
64DROP TABLE [BE-Nutzer]
65
66IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Auth]') AND type in (N'U'))
67DROP TABLE [Auth]
68
69
70
71-- Tabellen definieren
72--Auth
73--Algo, Stretch, Hash, Salt
74CREATE TABLE [Auth] (
75 ID INT IDENTITY(1,1) PRIMARY KEY,
76 Algo VARCHAR(6) NOT NULL CHECK(Algo='Sha1' OR Algo='Sha256'),
77 Stretch INT NOT NULL,
78 Salt CHAR(32) NOT NULL,
79 [Hash] CHAR(24) NOT NULL,
80 )
81
82-- BE-Nutzer
83-- ID, EMail, Lastlogin smalldatetime, Admin
84CREATE TABLE [BE-Nutzer](
85 ID INT IDENTITY (1,1) PRIMARY KEY,
86 EMail VARCHAR(50) NOT NULL,
87 Lastlogin smalldatetime NOT NULL,
88 [Admin] BIT NOT NULL,
89 Auth_ID INT NOT NULL REFERENCES Auth(ID),
90 )
91
92-- Rechte
93-- ID, Tabelle
94CREATE TABLE [Rechte](
95 ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
96 Tabelle VARCHAR(5000) NOT NULL CHECK (Tabelle='Kategorie' OR Tabelle='Produkt' OR Tabelle='Bild' OR Tabelle='Preis' OR Tabelle='Zutat'),
97 )
98
99-- Bewertung
100-- ID, Note, Sichtungen, Bemerkungen
101CREATE TABLE [Bewertung](
102 ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
103 Note INT NOT NULL,
104 Sichtungen INT NOT NULL,
105 Bemerkungen VARCHAR(250) NOT NULL,
106 [BE-Nutzer_ID] INT NOT NULL REFERENCES [BE-Nutzer](ID),
107 )
108
109-- Bild
110-- ID, Binärdaten, AltText, Title, Unterschrift
111CREATE TABLE [Bild](
112 ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
113 Binärdaten [VARBINARY](MAX) NOT NULL,
114 AltText VARCHAR(50) NOT NULL,
115 Title VARCHAR(50) NOT NULL,
116 Unterschrift VARCHAR(50),
117 )
118
119-- Kategorie
120-- ID, Bezeichnung
121CREATE TABLE [Kategorie](
122 ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
123 Bezeichnung VARCHAR(50),
124 Bild_ID INT NOT NULL REFERENCES BILD (ID),
125 Oberkategorie INT NOT NULL REFERENCES Kategorie(ID),
126 )
127-- Produkte
128-- ID, Kategorie_ID-FK, Name, Beschreibung, von timestamp, bis timestamp
129
130CREATE TABLE [Produkt] (
131 ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
132 Name VARCHAR(50) NOT NULL,
133 Beschreibung VARCHAR(50) NOT NULL,
134 von DATETIME NOT NULL,
135 bis DATETIME NOT NULL,
136 Bild_ID INT NOT NULL REFERENCES Bild(ID),
137)
138
139-- Zutat
140-- ID, Bezeichnung
141CREATE TABLE [Zutat](
142 ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
143 Bezeichnung VARCHAR(50) NOT NULL,
144 )
145-- Preis
146-- ID, Rolle, Preis
147CREATE TABLE [Preis](
148 ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
149 Rolle VARCHAR(50) NOT NULL CHECK(Rolle='Gast' OR Rolle='Student' OR Rolle='Mitarbeiter'),
150 Preis INT NOT NULL,
151 Produkt_ID INT NOT NULL REFERENCES Produkt(ID),
152 )
153
154-- Zahlung
155-- ID, Status, Zeitstempel, Betrag
156CREATE TABLE [Zahlung](
157 ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
158 [Status] VARCHAR(50) NOT NULL CHECK ([Status]>=10 AND [STatus]<=30),
159 Zeitstempel TIME NOT NULL,
160 Betrag INT NOT NULL,
161 AuthServer VARCHAR(50) NOT NULL,
162 )
163
164-- FE-Nutzer
165-- ID, lastlogin smalldatetime, Aktiv??, Auth??
166CREATE TABLE [FE-Nutzer](
167 Aktiv BIT NOT NULL,
168 ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
169 lastlogin smalldatetime NOT NULL,
170 Benutzer_ID INT NOT NULL REFERENCES [BE-Nutzer](ID),
171 Auth_ID INT NOT NULL REFERENCES Auth(ID),
172 )
173
174-- FH Angehörige
175-- Email, Name, Fachbereich
176CREATE TABLE [FH-Angehörige](
177 EMail VARCHAR(50) NOT NULL UNIQUE,
178 Name VARCHAR(50) NOT NULL,
179 Fachbereich INT,
180 ID INT NOT NULL PRIMARY KEY REFERENCES [FE-Nutzer](ID),
181 )
182
183-- Gast
184-- Name, von DATETIME, bis DATETIME
185CREATE TABLE [Gast](
186 Name VARCHAR(50) NOT NULL,
187 von DATETIME NOT NULL,
188 bis DATETIME NOT NULL,
189 ID INT NOT NULL PRIMARY KEY REFERENCES [FE-Nutzer](ID),
190 )
191
192-- Mitarbeiter
193-- MA_Nummer, Büro
194CREATE TABLE [Mitarbeiter](
195 MA_Nummer INT NOT NULL,
196 Büro VARCHAR(50) NOT NULL,
197 [FH-Angehörige_ID] INT NOT NULL PRIMARY KEY REFERENCES [FH-Angehörige](ID) ON DELETE CASCADE,
198 )
199
200--Student
201-- Matrikelnummer, Studiengang
202CREATE TABLE [Student](
203 Matrikelnummer INT NOT NULL CHECK(Matrikelnummer>='99999' AND Matrikelnummer <='999999999') UNIQUE,
204 Studiengang VARCHAR(50) NOT NULL,
205 [FH-Angehörige_ID] INT NOT NULL PRIMARY KEY REFERENCES [FH-Angehörige](ID) ON DELETE CASCADE,
206 )
207
208--Referenztabellen
209CREATE TABLE [BE-Nutzer_hat_Rechte] (
210 [BE-Nutzer_ID] INT NOT NULL REFERENCES [BE-Nutzer](ID),
211 Rechte_ID INT NOT NULL REFERENCES Rechte(ID),
212 )
213
214CREATE TABLE [Produkt_hat_Zutat] (
215 Produkt_ID INT NOT NULL REFERENCES Produkt(ID),
216 Zutat_ID INT NOT NULL REFERENCES Zutat(ID),
217 )
218
219CREATE TABLE [FE-Nutzer_Freund] (
220 ID INT NOT NULL PRIMARY KEY,
221 [FE-Nutzer1] INT NOT NULL REFERENCES [FE-Nutzer](ID),
222 [FE-Nutzer2] INT NOT NULL REFERENCES [FE-Nutzer](ID),
223 )
224
225CREATE TABLE [Bewertung_Schreiben] (
226 ID INT NOT NULL PRIMARY KEY,
227 [FH-Angehörige_ID] INT NOT NULL REFERENCES [FH-Angehörige](ID),
228 Bewertung_ID INT NOT NULL REFERENCES Bewertung(ID),
229 Produkt_ID INT NOT NULL REFERENCES Produkt(ID),
230 )
231
232CREATE TABLE [Kaufen] (
233 ID INT NOT NULL PRIMARY KEY,
234 Endpreis MONEY,
235 [FE-Nutzer_ID] INT NOT NULL REFERENCES [FE-Nutzer](ID),
236 Produkt_ID INT NOT NULL REFERENCES Produkt(ID),
237 Zahlung_ID INT NOT NULL REFERENCES Zahlung(ID),
238 )
239
240-- Tabellen ändern (fügen Sie hier CHECK und andere Constraints, falls Sie sie nicht bei der Definition verwenden können)
241
242-- ALTER TABLE [beispiel] ADD CONSTRAINT [constrname] CHECK (1=1)
243
244ALTER TABLE [Zahlung] ADD CONSTRAINT [Status] Check(Status='10' OR Status='20' OR Status='30')