h2880txp

1
2-- To Do:
3-- UNIQUE Constraint an alle PKs setzen
4-- Preise (kostet) implementieren
5-- bool mit BIT tauschen
6-- ..
7-- Zu den Buisness Rules anpassen
8
9-- neu: 1 Mitarbeiter, 2 Studenten, 1 Benutzer anlegen
10-- 
11
12-- CREATE DATABASE mensa;
13-- USE  mensa;
14
15DROP TABLE IF EXISTS Benutzer ;
16CREATE TABLE Benutzer (
17	`Benutzer_ID` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
18	`Nutzername` varchar(100) NOT NULL UNIQUE,
19	`Vorname` varchar(80) NOT NULL,
20	`Nachname` varchar(80) NOT NULL,
21	`Geburtsdatum` date,  -- optional
22--	`Alter` DATEDIFF(year, Geburtsdatum, CURDATE()), -- abgeleitetes Attribut
23	`Letzter_Login` datetime DEFAULT NULL,-- optional
24	`Salt` char(32) NOT NULL,
25	`Hash` char(24) NOT NULL,
26	`Anlegedatum` date NOT NULL,
27	`E-Mail` varchar(100) NOT NULL UNIQUE,
28	`Aktiv` bool NOT NULL
29	);
30-- Freunde Tabelle
31	
32CREATE TABLE Gäste(
33	`Gast_ID` int PRIMARY KEY AUTO_INCREMENT,
34 	`Grund` varchar(255) NOT NULL,
35	`Ablaufdatum` date NOT NULL DEFAULT ADDDATE(CURDATE(), 7),
36	CONSTRAINT FK_GastBenutzer FOREIGN KEY(`Gast_ID`)
37	REFERENCES Benutzer(Benutzer_ID) ON DELETE CASCADE
38	);
39	
40CREATE TABLE FH_Angehörige(
41	`FH_ID` int PRIMARY KEY  AUTO_INCREMENT,
42	-- braucht man noch was?
43	CONSTRAINT FK_AngehorgiBenutzer FOREIGN KEY(FH_ID)
44	REFERENCES Benutzer(Benutzer_ID)  ON DELETE CASCADE
45	);
46	
47CREATE TABLE Mitarbeiter (
48	`Mitarbeiter_ID` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
49	`Büro` varchar(50),
50	`Telefon` int,
51	CONSTRAINT FK_Mitarbeit`gäste`erBenutzer FOREIGN KEY(Mitarbeiter_ID)
52	REFERENCES FH_Angehörige(FH_ID)  ON DELETE CASCADE 
53);	
54
55DROP TABLE Studenten;
56CREATE TABLE Studenten(
57	`Student_ID` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
58	`Studiengang` enum(`ET`, `INF`, `ISE`, `MCD`, `WI`) NOT NULL,
59--	`Studiengang` int NOT NULL,
60	`Matrikelnummer` int NOT NULL UNIQUE CHECK (Matrikelnummer between 10000000 and 999999999),
61	CONSTRAINT FK_StudentenBenutzer FOREIGN KEY(Student_ID)
62	REFERENCES FH_Angehörige(FH_ID)  ON DELETE CASCADE,
63--	CONSTRAINT FK_STUDIENGANGZ FOREIGN KEY(Studiengang)
64--	REFERENCES Studiengaenge(Studiengang_ID)
65	);
66	
67CREATE TABLE Studiengaenge(
68	`Studiengang_ID` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
69	`Studiengang` varchar(20)
70	);
71	
72CREATE TABLE Fachbereiche(
73	`Fachbereich_ID` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
74	`Webseite` varchar(100) NOT NULL,
75	`Name` 	  varchar(50) NOT NULL
76);
77
78CREATE TABLE gehörtZuFachbereich (
79	`Angehörig_ID`   int NOT NULL,
80	`Fachbereich_ID` int NOT NULL,
81	CONSTRAINT PK_gehortZu PRIMARY KEY (Angehörig_ID, Fachbereich_ID),
82	FOREIGN KEY(Angehörig_ID) REFERENCES FH_Angehörige(FH_ID),
83	FOREIGN KEY(Fachbereich_ID) REFERENCES Fachbereiche(Fachbereich_ID)
84	);
85	
86	
87CREATE TABLE Bestellungen(
88	`Bestellung_ID` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
89	`Bestellzeitpunkt` datetime NOT NULL DEFAULT NOW(),
90	`Abholzeitpunkt` datetime CHECK (Abholzeitpunkt > Bestellzeitpunkt),
91	`Endpreis` float(4,2), -- abgeleitetes Attribut
92	`bestelltVon` int NOT NULL,
93	CONSTRAINT FK_BenutzerBestellung FOREIGN KEY(`bestelltVon`) REFERENCES Benutzer(Benutzer_ID)
94	);
95
96
97CREATE TABLE Bilder(
98	`Bild_ID` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
99	`Alt-Text` text NOT NULL,
100	`Titel` varchar(50),
101	`Binärdaten` blob NOT NULL
102);
103
104
105CREATE TABLE Kategorien(
106	`Kategorie_ID` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
107	`Bezeichnung` varchar(200) NOT NULL,
108	`istUnterkategorieVon` int,
109	`Bild_ID` int NOT NULL,
110	 CONSTRAINT FK_Unterkategorie FOREIGN KEY(istUnterkategorieVon) 
111	 REFERENCES Kategorien(Kategorie_ID) ON DELETE NO ACTION,
112	 CONSTRAINT FK_Bilder FOREIGN KEY(Bild_ID)
113	 REFERENCES Bilder(Bild_ID) ON DELETE NO ACTION
114);
115
116CREATE TABLE Mahlzeiten(
117	`Mahlzeit_ID` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
118	`Beschreibung` varchar(255) NOT NULL,
119	`Verfügbar` bool,
120	`Vorrat` int NOT NULL DEFAULT 0,
121	`inKategorie` int NOT NULL,
122	CONSTRAINT FK_MahlzeitKategorie FOREIGN KEY(inKategorie)
123	REFERENCES Kategorien(Kategorie_ID)
124);
125
126
127-- Funktioniert nicht
128
129CREATE TABLE BestellteMahlzeiten(
130	`Bestellung_ID` int NOT NULL,
131	`Mahlzeit_ID` int   NOT NULL,
132	`Anzahl` int NOT NULL,
133	CONSTRAINT PK_BestellteMahlzeiten PRIMARY KEY(Bestellung_ID,Mahlzeit_ID),
134	CONSTRAINT FK_Bestellung FOREIGN KEY(Bestellung_ID) REFERENCES Bestellungen(Bestellung_ID)
135	);
136	
137	
138
139CREATE TABLE Deklarationen(
140 -- richtiges datentyp for Zeichen?
141	`Zeichen` varchar(2) NOT NULL PRIMARY KEY,
142	`Beschriftung` varchar(32) NOT NULL
143);
144
145CREATE TABLE MahlzeitenDeklaration(
146	`Mahlzeit_ID` int NOT NULL AUTO_INCREMENT,
147	`Zeichen` varchar(2) NOT NULL,
148	CONSTRAINT PK_MahlzeitenDeklaration PRIMARY KEY(Mahlzeit_ID, Zeichen),
149	CONSTRAINT FK_Mahlzeiten FOREIGN KEY(Mahlzeit_ID) REFERENCES Mahlzeiten(Mahlzeit_ID),
150	CONSTRAINT FK_Deklarationen FOREIGN KEY(Zeichen) REFERENCES	 Deklarationen(Zeichen)
151);
152
153
154-- ///////
155
156-- Weak Entity in 1:1 Relationship
157--	How???
158-- ///////////////
159CREATE TABLE Preise(
160	`Mahlzeit_ID` int NOT NULL, 
161	`Jahr` int NOT NULL,   
162	`Gastpreis` float(2,2) NOT NULL,   -- here is 2,2 cuz 4,2 mean you can write 4 nummbers bevore ,
163	`MA-Preis` float(2,2),
164	`Studentpreis` float(2,2) CHECK (Studentpreis < `MA-Preis`),
165	CONSTRAINT PK_Preise PRIMARY KEY(Mahlzeit_ID, Jahr),
166	CONSTANT FK_MahlzeitPreise FOREIGN KEY(Mahlzeit_ID)
167	REFERENCES Mahlzeiten(Mahlzeit_ID)  ON DELETE CASCADE
168);
169
170
171CREATE TABLE MahlzeitenBilder(
172	`Mahlzeit_ID` int NOT NULL,
173	`Bild_ID` int NOT NULL,
174	CONSTRAINT PK_MahlzeitenBild PRIMARY KEY(Mahlzeit_ID, Bild_ID),
175	CONSTRAINT FK_Mahlzeit FOREIGN KEY(Mahlzeit_ID) REFERENCES Mahlzeiten(Mahlzeit_ID),
176	CONSTRAINT FK_Bild FOREIGN KEY(Bild_ID) REFERENCES Bilder(Bild_ID)
177);
178
179CREATE TABLE Zutaten(
180	`Zutat_ID` int(5) PRIMARY KEY NOT NULL,
181	`Name` varchar(30),
182	`Bio`  bool NOT NULL,
183	`Vegan` bool NOT NULL,
184	`Vegetarisch` bool NOT NULL,
185	`Glutenfrei` bool NOT NULL
186);
187
188CREATE TABLE MahlzeitEnthalt(
189	`Mahlzeit_ID` int NOT NULL,
190	`Zutat_ID` int NOT NULL,
191	CONSTRAINT PK_MahlzeitEnthalt PRIMARY KEY(Mahlzeit_ID, Zutat_ID),
192	CONSTRAINT FK_MahlzeitEnthalt FOREIGN KEY(Mahlzeit_ID) REFERENCES Mahlzeiten(Mahlzeit_ID),
193	CONSTRAINT FK_ZutatEnthalt FOREIGN KEY(Zutat_ID) REFERENCES Zutaten(Zutat_ID)
194);
195
196CREATE TABLE Kommentare(
197	`Komentar_ID` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
198	`Bemerkung` varchar(150),
199	`Bewertung` int NOT NULL,
200	`GeschriebenVon` int NOT NULL, -- benutzer FK
201	`zuMahlzeit` int NOT NULL, -- mahlzeit FK
202	CONSTRAINT FK_MahlzeitKommentar FOREIGN KEY(zuMahlzeit) REFERENCES Mahlzeiten(Mahlzeit_ID),
203	CONSTRAINT FK_BenutzerKommentar FOREIGN KEY(GeschriebenVon) REFERENCES Benutzer(Benutzer_ID)
204	);