· 9 years ago · Nov 10, 2016, 12:24 PM
1USE Praktikum;
2
3IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[FE-NutzerKauftProdukt]') AND type in (N'U'))
4DROP TABLE [FE-NutzerKauftProdukt]
5
6IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Zahlung]') AND type in (N'U'))
7DROP TABLE [Zahlung]
8
9IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[FH-AngehörigeSchreibtBewertungFürProdukt]') AND type in (N'U'))
10DROP TABLE [FH-AngehörigeSchreibtBewertungFürProdukt] --NEU!!!!
11
12IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Bewertung]') AND type in (N'U'))
13DROP TABLE [Bewertung]
14
15IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Preis]') AND type in (N'U'))
16DROP TABLE [Preis]
17
18IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ProduktHatZutat]') AND type in (N'U'))
19DROP TABLE [ProduktHatZutat]
20
21IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Zutat]') AND type in (N'U'))
22DROP TABLE [Zutat]
23
24IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Produkt]') AND type in (N'U'))
25DROP TABLE [Produkt]
26
27IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Kategorie]') AND type in (N'U'))
28DROP TABLE [Kategorie]
29
30IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Bild]') AND type in (N'U'))
31DROP TABLE [Bild]
32
33IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Student]') AND type in (N'U'))
34DROP TABLE [Student]
35
36IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Mitarbeiter]') AND type in (N'U'))
37DROP TABLE [Mitarbeiter]
38
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]
41
42IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Gast]') AND type in (N'U'))
43DROP TABLE [Gast]
44
45IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[FE-NutzerMitFE-Nutzer]') AND type in (N'U'))
46DROP TABLE [FE-NutzerMitFE-Nutzer]
47
48IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[FE-Nutzer]') AND type in (N'U'))
49DROP TABLE [FE-Nutzer]
50
51IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[BE-NutzerHatRechte]') AND type in (N'U'))
52DROP TABLE [BE-NutzerHatRechte]
53
54IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Rechte]') AND type in (N'U'))
55DROP TABLE [Rechte]
56
57IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[BE-Nutzer]') AND type in (N'U'))
58DROP TABLE [BE-Nutzer]
59
60GO
61
62CREATE TABLE [dbo].[BE-Nutzer] (
63 [ID] INT PRIMARY KEY IDENTITY(1,1),
64 [E-Mail] VARCHAR (320) NOT NULL,
65 [LetzterLogin] DATETIME DEFAULT GETDATE(),
66 [Admin] BIT DEFAULT 0, --flag
67 [Algo] varchar (6) NOT NULL,
68 [Stretch] INT NOT NULL,
69 [Hash] CHAR (24) NOT NULL,
70 [Salt] CHAR (32) NOT NULL
71);
72
73CREATE TABLE [dbo].[Rechte] (
74 [ID] INT PRIMARY KEY IDENTITY(1,1),
75 [Tabelle] VARCHAR (9) NOT NULL,
76 CONSTRAINT [chk_Tabelle] CHECK ([Tabelle] IN ('Kategorie','Produkt','Bild','Preis','Zutat'))
77);
78
79CREATE TABLE [dbo].[BE-NutzerHatRechte] (
80 [BE-NutzerID] INT REFERENCES [BE-Nutzer](ID),
81 [RechteID] INT REFERENCES [Rechte](ID),
82 CONSTRAINT [pk_BE-NutzerHatRechte] PRIMARY KEY ([BE-NutzerID],[RechteID])
83);
84
85CREATE TABLE [dbo].[FE-Nutzer] (
86 [ID] INT PRIMARY KEY IDENTITY(1,1),
87 [Aktiv] BIT DEFAULT 0,
88 [LetzterLogin] DATETIME DEFAULT GETDATE(),
89 [Algo] varchar (6) NOT NULL,
90 [Stretch] INT NOT NULL,
91 [Hash] CHAR (24) NOT NULL,
92 [Salt] CHAR (32) NOT NULL,
93 [BE-NutzerID] INT REFERENCES [BE-Nutzer](ID) NOT NULL --FE-Nutzer wurde angelegt von BE-Nutzer
94);
95
96CREATE TABLE [dbo].[FE-NutzerMitFE-Nutzer] ( --Zwischentabelle
97 [FE-Nutzer1ID] INT ,
98 [FE-Nutzer2ID] INT ,
99 CONSTRAINT [pk_FE-NutzerMitFE-Nutzer] PRIMARY KEY ([FE-Nutzer1ID],[FE-Nutzer2ID])
100);
101
102CREATE TABLE [dbo].[Gast] (
103 [ID] INT PRIMARY KEY REFERENCES [FE-Nutzer](ID) ON DELETE CASCADE,
104 [Name] VARCHAR(100) NOT NULL,
105 [Von] DATE DEFAULT CAST(GETDATE() AS DATE),
106 [Bis] DATE NOT NULL,
107 CONSTRAINT [chk_Von] CHECK ([Von] >= CAST(GETDATE() AS DATE)),
108 CONSTRAINT [chk_Bis] CHECK ([Bis] >= CAST(GETDATE() AS DATE)) --Nur den Datums-Teil von GETDATE() nutzen.
109);
110
111CREATE TABLE [dbo].[FH-Angehörige](
112 [ID] INT PRIMARY KEY REFERENCES [FE-Nutzer](ID) ON DELETE CASCADE,
113 [Name] VARCHAR(100) NOT NULL,
114 [Fachbereich] INT NOT NULL,
115 [E-Mail] VARCHAR(320) UNIQUE NOT NULL
116);
117
118CREATE TABLE [dbo].[Mitarbeiter](
119 [ID] INT PRIMARY KEY REFERENCES [FH-Angehörige](ID) ON DELETE CASCADE,
120 [MA-Nummer] INT NOT NULL,
121 [Büro] VARCHAR(10) DEFAULT ''
122);
123
124CREATE TABLE [dbo].[Student](
125 [ID] INT PRIMARY KEY REFERENCES [FH-Angehörige](ID) ON DELETE CASCADE,
126 [Matrikelnummer] INT UNIQUE NOT NULL,
127 [Studiengang] VARCHAR(100) NOT NULL,
128 CONSTRAINT [chk_Matrikelnummer] CHECK ([Matrikelnummer] >= 10000 AND [Matrikelnummer] <= 9999999)
129);
130
131CREATE TABLE [dbo].[Bild](
132 [ID] INT PRIMARY KEY IDENTITY(1,1),
133 [Binärdaten] VARBINARY(max) NULL,
134 [Alt-Text] VARCHAR (50) NOT NULL,
135 [Title] VARCHAR (100) NOT NULL,
136 [Unterschrift] VARCHAR (100) DEFAULT ''
137);
138
139CREATE TABLE [dbo].[Kategorie](
140 [ID] INT PRIMARY KEY IDENTITY(1,1),
141 [Bezeichnung] VARCHAR (320) DEFAULT '',
142 [Oberkategorie] INT REFERENCES [Kategorie](ID) NULL,
143 [BildID] INT REFERENCES [Bild](ID) NULL --Zum Testen NULL-Wert erlauben
144);
145
146CREATE TABLE [dbo].[Produkt](
147 [ID] INT PRIMARY KEY IDENTITY(1,1),
148 [Beschreibung] VARCHAR (500) NOT NULL,
149 [Name] VARCHAR (100) NOT NULL,
150 [Von] DATE DEFAULT CAST(GETDATE() AS DATE),
151 [Bis] DATE NOT NULL,
152 [KategorieID] INT REFERENCES [Kategorie](ID) NOT NULL,
153 [BildID] INT REFERENCES [Bild](ID) NULL --Zum Testen NULL-Wert erlauben
154);
155
156CREATE TABLE [dbo].[Zutat](
157 [ID] INT PRIMARY KEY IDENTITY(1,1),
158 [Bezeichnung] VARCHAR (50) NOT NULL
159);
160
161CREATE TABLE [dbo].[ProduktHatZutat](
162 [ProduktID] INT REFERENCES [Produkt](ID),
163 [ZutatID] INT REFERENCES [Zutat](ID),
164 CONSTRAINT [pk_ProduktHatZutat] PRIMARY KEY (ProduktID,ZutatID)
165);
166
167CREATE TABLE [dbo].[Preis](
168 [ID] INT PRIMARY KEY IDENTITY(1,1),
169 [Preis] DECIMAL(4,2) NOT NULL,
170 [Rolle] VARCHAR (11) NOT NULL,
171 [ProduktID] INT REFERENCES [Produkt](ID) NOT NULL,
172 CONSTRAINT [chk_Rolle] CHECK ([Rolle]='Gast' OR [Rolle]='Mitrbeiter' OR [Rolle]='Student')
173);
174
175CREATE TABLE [dbo].[Bewertung](
176 [ID] INT PRIMARY KEY IDENTITY(1,1),
177 [Sichtung] INT DEFAULT 0,
178 [Note] INT NOT NULL,
179 [Bemerkung] VARCHAR(300) NOT NULL,
180 [BE-NutzerID] INT REFERENCES [BE-Nutzer](ID) NOT NULL,
181 [FH-AngehörigeID] INT REFERENCES [FH-Angehörige](ID) NOT NULL,
182 [ProduktID] INT REFERENCES [Produkt](ID) NOT NULL
183);
184
185CREATE TABLE [dbo].[FH-AngehörigeSchreibtBewertungFürProdukt]( --NEU!!!!
186 [FH-AngehörigeID] INT REFERENCES [FH-Angehörige](ID) NOT NULL,
187 [BewertungID] INT REFERENCES [Bewertung](ID) NOT NULL,
188 [ProduktID] INT REFERENCES [Produkt](ID) NOT NULL
189 CONSTRAINT [pk_FH-AngehörigeSchreibtBewertungFürProdukt] PRIMARY KEY ([FH-AngehörigeID],[BewertungID],[ProduktID])
190);
191
192CREATE TABLE [dbo].[Zahlung](
193 [ID] INT PRIMARY KEY IDENTITY(1,1),
194 [Betrag] DECIMAL(6,2) NOT NULL,
195 [Aut-Server] CHAR (15) NOT NULL,
196 [Zeitstempel] DATETIME DEFAULT GETDATE(),
197 [Status] INT NOT NULL
198);
199
200CREATE TABLE [dbo].[FE-NutzerKauftProdukt](
201 [FE-NutzerID] INT REFERENCES [FE-Nutzer](ID),
202 [ProduktID] INT REFERENCES [Produkt](ID),
203 [ZahlungID] INT REFERENCES [Zahlung](ID),
204 CONSTRAINT [pk_FE-NutzerKauftProdukt] PRIMARY KEY ([FE-NutzerID],[ProduktID], [ZahlungID])
205);
206
207GO
208
209ALTER TABLE [Zahlung] ADD CONSTRAINT [chk_Status] CHECK ([Status] IN (10,20,30));