· 9 years ago · Dec 04, 2016, 09:04 AM
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'[BE-NutzerRechteX]') AND TYPE IN (N'U'))
10DROP TABLE [BE-NutzerRechteX]
11GO
12IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[FH-AngehörigeBewertungProduktX]') AND TYPE IN (N'U'))
13DROP TABLE [FH-AngehörigeBewertungProduktX]
14GO
15IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[FE-NutzerX]') AND TYPE IN (N'U'))
16DROP TABLE [FE-NutzerX]
17GO
18IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ProduktZahlungFE-NutzerX]') AND TYPE IN (N'U'))
19DROP TABLE [ProduktZahlungFE-NutzerX]
20GO
21IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ProduktZutatX]') AND TYPE IN (N'U'))
22DROP TABLE [ProduktZutatX]
23GO
24IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Rechte]') AND TYPE IN (N'U'))
25DROP TABLE [Rechte]
26GO
27IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Bewertung]') AND TYPE IN (N'U'))
28DROP TABLE [Bewertung]
29GO
30IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Student]') AND TYPE IN (N'U'))
31DROP TABLE [Student]
32GO
33IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Mitarbeiter]') AND TYPE IN (N'U'))
34DROP TABLE [Mitarbeiter]
35GO
36IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Gast]') AND TYPE IN (N'U'))
37DROP TABLE [Gast]
38GO
39IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[FH-Angehörige]') AND TYPE IN (N'U'))
40DROP TABLE [FH-Angehörige]
41GO
42IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[FE-Nutzer]') AND TYPE IN (N'U'))
43DROP TABLE [FE-Nutzer]
44GO
45IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[BE-Nutzer]') AND TYPE IN (N'U'))
46DROP TABLE [BE-Nutzer]
47GO
48IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Zahlung]') AND TYPE IN (N'U'))
49DROP TABLE [Zahlung]
50GO
51IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Preis]') AND TYPE IN (N'U'))
52DROP TABLE [Preis]
53GO
54IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Zutat]') AND TYPE IN (N'U'))
55DROP TABLE [Zutat]
56GO
57IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Produkt]') AND TYPE IN (N'U'))
58DROP TABLE [Produkt]
59GO
60IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Kategorie]') AND TYPE IN (N'U'))
61DROP TABLE [Kategorie]
62GO
63IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Bild]') AND TYPE IN (N'U'))
64DROP TABLE [Bild]
65GO
66
67
68
69-- Tabellen definieren
70
71
72-- Produkte
73-- ID, Kategorie_ID-FK, Name, Beschreibung, von timestamp, bis timestamp
74
75-- Tabellen ändern (fügen Sie hier CHECK und andere Constraints, falls Sie sie nicht bei der Definition verwenden können)
76
77-- ALTER TABLE [beispiel] ADD CONSTRAINT [constrname] CHECK (1=1)
78
79CREATE TABLE [Bild] (
80 [ID] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
81 [Binärdaten] VARBINARY,
82 [Alt-Text] VARCHAR(255),
83 [Titel] VARCHAR(50),
84 [Unterschrift] VARCHAR(124)
85)
86 GO
87
88CREATE TABLE [Kategorie] (
89 [ID] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
90 [Oberkategorie_ID] INT REFERENCES [Kategorie](ID),
91 [Bild_ID] INT REFERENCES [Bild](ID),
92 [Bezeichnung] VARCHAR(50)
93)
94 GO
95
96-- Produkte
97-- ID, Kategorie_ID-FK, Name, Beschreibung, von timestamp, bis timestamp
98CREATE TABLE [Produkt] (
99 [ID] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
100 [Bild_ID] INT REFERENCES [Bild](ID),
101 [Kategorie_ID] INT REFERENCES [Kategorie](ID) NOT NULL,
102 [Name] VARCHAR(50),
103 [Beschreibung] VARCHAR(50),
104 [von] DATETIME,
105 [bis] DATETIME
106)
107 GO
108
109CREATE TABLE [Zutat] (
110 [ID] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
111 [Bezeichnung] VARCHAR(50)
112)
113 GO
114
115CREATE TABLE [Preis] (
116 [ID] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
117 [Produkt_ID] INT REFERENCES [Produkt](ID) NOT NULL,
118 [Rolle] VARCHAR(50) CHECK (Rolle = 'Gast' OR Rolle = 'Mitarbeiter' OR Rolle = 'Student'),
119 [Preis] MONEY NOT NULL
120)
121 GO
122
123CREATE TABLE [Zahlung] (
124 [ID] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
125 [Betrag] INT,
126 [Autserver] VARCHAR(50),
127 [Zeitstempel] TIMESTAMP,
128 [STATUS] INT
129)
130 GO
131
132 ALTER TABLE [Zahlung] ADD CHECK ([STATUS] = '10' OR [STATUS] = '20' OR [STATUS] = '30')
133 GO
134
135CREATE TABLE [BE-Nutzer] (
136 [ID] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
137 [E-Mail] VARCHAR(50) UNIQUE,
138 [Letzter Login] TIMESTAMP,
139 [Admin] BIT,
140 [Hash] CHAR(24),
141 [Salt] CHAR(32),
142 [Stretch] INT,
143 [Algo] VARCHAR(6) CHECK (Algo = 'sha1' OR Algo = 'sha256'),
144)
145 GO
146
147CREATE TABLE [FE-Nutzer] (
148 [ID] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
149 [BE-Nutzer_ID] INT REFERENCES [BE-Nutzer](ID),
150 [Aktiv] BIT,
151 [Hash] CHAR(24),
152 [Salt] CHAR(32),
153 [Stretch] INT,
154 [Algo] VARCHAR(6) CHECK (Algo = 'sha1' OR Algo = 'sha256'),
155 [Letzter Login] TIMESTAMP
156)
157 GO
158
159CREATE TABLE [FH-Angehörige] (
160 [FE-Nutzer_ID] INT REFERENCES [FE-Nutzer](ID) ON DELETE CASCADE PRIMARY KEY NOT NULL,
161 [E-Mail] VARCHAR(50) UNIQUE,
162 [Name] VARCHAR(50),
163 [Fachbereich] INT
164)
165 GO
166
167CREATE TABLE [Gast] (
168 [FE-Nutzer_ID] INT REFERENCES [FE-Nutzer](ID) ON DELETE CASCADE NOT NULL PRIMARY KEY,
169 [Name] VARCHAR(50) NOT NULL,
170 [von] DATETIME,
171 [bis] DATETIME
172)
173 GO
174
175
176CREATE TABLE [Mitarbeiter] (
177 [FE-Nutzer_ID] INT FOREIGN KEY REFERENCES [FH-Angehörige]([FE-Nutzer_ID]) ON DELETE CASCADE PRIMARY KEY NOT NULL,
178 [MA-Nummer] INT NOT NULL,
179 [Büro] INT NOT NULL
180)
181 GO
182
183CREATE TABLE [Student] (
184 [FE-Nutzer_ID] INT REFERENCES [FH-Angehörige]([FE-Nutzer_ID]) ON DELETE CASCADE PRIMARY KEY NOT NULL,
185 [Matrikelnummer] INT CHECK ([Matrikelnummer] > 10000 OR [Matrikelnummer] < 9999999) UNIQUE NOT NULL,
186 [Studiengang] VARCHAR(50)
187)
188 GO
189
190CREATE TABLE [Bewertung] (
191 [ID] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
192 [BE-Nutzer_ID] INT REFERENCES [BE-Nutzer](ID) NOT NULL,
193 [Sichtungen] INT DEFAULT(0),
194 [Note] INT,
195 [Bemerkung] VARCHAR(255)
196)
197 GO
198
199CREATE TABLE [Rechte] (
200 [ID] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
201 [Test] VARCHAR(50),
202 [Tabelle] VARCHAR(9) CHECK (Tabelle = 'Produkt' OR Tabelle = 'Kategorie' OR Tabelle = 'Bild' OR Tabelle = 'Preis' OR Tabelle = 'Zutat')
203)
204 GO
205
206CREATE TABLE [ProduktZutatX] (
207 [Produkt_ID] INT REFERENCES Produkt(ID) NOT NULL,
208 [Zutat_ID] INT REFERENCES Zutat(ID) NOT NULL
209)
210 GO
211
212ALTER TABLE ProduktZutatX ADD CONSTRAINT [ProdutZutatX-PK] PRIMARY KEY (Produkt_ID, Zutat_ID)
213 GO
214
215CREATE TABLE [ProduktZahlungFE-NutzerX] (
216 [Produkt_ID] INT REFERENCES Produkt(ID) NOT NULL,
217 [Zahlung_ID] INT REFERENCES Zahlung(ID) NOT NULL,
218 [FE-Nutzer_ID] INT REFERENCES [FE-Nutzer](ID) NOT NULL
219)
220GO
221
222ALTER TABLE [ProduktZahlungFE-NutzerX] ADD CONSTRAINT [ProduktZahlungFE_NutzerX-PK] PRIMARY KEY ([Produkt_ID], [Zahlung_ID], [FE-Nutzer_ID])
223 GO
224
225CREATE TABLE [FE-NutzerX] (
226 [Freundschafts_ID1] INT REFERENCES [FE-Nutzer](ID) NOT NULL,
227 [Freundschafts_ID2] INT REFERENCES [FE-Nutzer](ID) NOT NULL
228 )
229 GO
230
231 ALTER TABLE [FE-NutzerX] ADD CONSTRAINT [FE-NutzerX-PK] PRIMARY KEY ([Freundschafts_ID1], [Freundschafts_ID2])
232 GO
233
234 CREATE TABLE [FH-AngehörigeBewertungProduktX] (
235 [FH-Angehörige_ID] INT REFERENCES [FH-Angehörige]([FE-Nutzer_ID]) NOT NULL,
236 [Bewertung_ID] INT REFERENCES [Bewertung](ID) NOT NULL,
237 [Produkt_ID] INT REFERENCES [Produkt](ID) NOT NULL
238 )
239 GO
240
241 ALTER TABLE [FH-AngehörigeBewertungProduktX] ADD CONSTRAINT [FH-AngehörigeBewertungProduktX-PK] PRIMARY KEY ([FH-Angehörige_ID], [Bewertung_ID], [Produkt_ID])
242 GO
243
244 CREATE TABLE [BE-NutzerRechteX] (
245 [BE-Nutzer_ID] INT REFERENCES [BE-Nutzer](ID) NOT NULL,
246 [Rechte_ID] INT REFERENCES [Rechte](ID) NOT NULL,
247 )
248 GO
249
250 ALTER TABLE [BE-NutzerRechteX] ADD CONSTRAINT [BE-NutzerRechteX-PK] PRIMARY KEY ([BE-Nutzer_ID], [Rechte_ID])
251 GO