xmjuV8f1

1-- MySQL dump 10.13  Distrib 5.7.9, for Win64 (x86_64)
2--
3-- Host: localhost    Database: auftrag3
4-- ------------------------------------------------------
5-- Server version	5.7.9-log
6
7/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10/*!40101 SET NAMES utf8 */;
11/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
12/*!40103 SET TIME_ZONE='+00:00' */;
13/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
17
18--
19-- Table structure for table `abteilung`
20--
21
22DROP TABLE IF EXISTS `abteilung`;
23/*!40101 SET @saved_cs_client     = @@character_set_client */;
24/*!40101 SET character_set_client = utf8 */;
25CREATE TABLE `abteilung` (
26  `id_abteilung` int(11) NOT NULL AUTO_INCREMENT,
27  `abteilungsname` varchar(45) NOT NULL,
28  `abteilungsbeschreibung` varchar(300) DEFAULT NULL,
29  PRIMARY KEY (`id_abteilung`)
30) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
31/*!40101 SET character_set_client = @saved_cs_client */;
32
33--
34-- Dumping data for table `abteilung`
35--
36
37LOCK TABLES `abteilung` WRITE;
38/*!40000 ALTER TABLE `abteilung` DISABLE KEYS */;
39INSERT INTO `abteilung` VALUES (1,'ProgrammierABT',NULL),(2,'KuechenABT','Interne Firmenkueche'),(3,'SekretariatABT','Firmensekretariat'),(4,'PapiershreddereiABT',NULL),(5,'MeldestelleABT',NULL),(6,'WeltherrschaftABT','Top secret');
40/*!40000 ALTER TABLE `abteilung` ENABLE KEYS */;
41UNLOCK TABLES;
42
43--
44-- Table structure for table `buchung`
45--
46
47DROP TABLE IF EXISTS `buchung`;
48/*!40101 SET @saved_cs_client     = @@character_set_client */;
49/*!40101 SET character_set_client = utf8 */;
50CREATE TABLE `buchung` (
51  `id_buchung` int(11) NOT NULL AUTO_INCREMENT,
52  `stunden` int(11) NOT NULL,
53  `freitextfeld` varchar(300) DEFAULT NULL,
54  `eintrag_id` int(11) NOT NULL,
55  `taetigkeit_id` int(11) DEFAULT NULL,
56  PRIMARY KEY (`id_buchung`),
57  KEY `eintrag_id` (`eintrag_id`),
58  KEY `taetigkeit_id` (`taetigkeit_id`),
59  CONSTRAINT `buchung_ibfk_1` FOREIGN KEY (`eintrag_id`) REFERENCES `zwischentabelle` (`id_eintrag`),
60  CONSTRAINT `buchung_ibfk_2` FOREIGN KEY (`taetigkeit_id`) REFERENCES `taetigkeit` (`id_taetigkeit`)
61) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
62/*!40101 SET character_set_client = @saved_cs_client */;
63
64--
65-- Dumping data for table `buchung`
66--
67
68LOCK TABLES `buchung` WRITE;
69/*!40000 ALTER TABLE `buchung` DISABLE KEYS */;
70INSERT INTO `buchung` VALUES (1,10,'Erster Eintrag nur zu testzwecken',1,1),(2,1,'eine ein-stunden tätigkeit',4,2),(3,7,NULL,2,4),(4,4,NULL,3,3),(5,5,NULL,5,3),(6,6,NULL,6,5);
71/*!40000 ALTER TABLE `buchung` ENABLE KEYS */;
72UNLOCK TABLES;
73
74--
75-- Table structure for table `eingegangene_zahlungen`
76--
77
78DROP TABLE IF EXISTS `eingegangene_zahlungen`;
79/*!40101 SET @saved_cs_client     = @@character_set_client */;
80/*!40101 SET character_set_client = utf8 */;
81CREATE TABLE `eingegangene_zahlungen` (
82  `id_eingegangene_zahlungen` int(11) NOT NULL AUTO_INCREMENT,
83  `datum` date NOT NULL,
84  `betrag` int(11) DEFAULT NULL,
85  `rechnung_id` int(11) NOT NULL,
86  PRIMARY KEY (`id_eingegangene_zahlungen`),
87  KEY `rechnung_id` (`rechnung_id`),
88  CONSTRAINT `eingegangene_zahlungen_ibfk_1` FOREIGN KEY (`rechnung_id`) REFERENCES `rechnungen` (`id_rechnung`)
89) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
90/*!40101 SET character_set_client = @saved_cs_client */;
91
92--
93-- Dumping data for table `eingegangene_zahlungen`
94--
95
96LOCK TABLES `eingegangene_zahlungen` WRITE;
97/*!40000 ALTER TABLE `eingegangene_zahlungen` DISABLE KEYS */;
98INSERT INTO `eingegangene_zahlungen` VALUES (1,'2015-10-04',300,1),(2,'2015-11-03',3000,2),(3,'2015-11-05',300,1),(4,'2015-11-09',400,2),(5,'2015-12-04',7000,3),(6,'2015-12-12',1313,4),(7,'2016-01-01',2400,5);
99/*!40000 ALTER TABLE `eingegangene_zahlungen` ENABLE KEYS */;
100UNLOCK TABLES;
101
102--
103-- Table structure for table `fixprojekte`
104--
105
106DROP TABLE IF EXISTS `fixprojekte`;
107/*!40101 SET @saved_cs_client     = @@character_set_client */;
108/*!40101 SET character_set_client = utf8 */;
109CREATE TABLE `fixprojekte` (
110  `projekt_id` int(11) NOT NULL,
111  `preis` int(11) NOT NULL,
112  PRIMARY KEY (`projekt_id`),
113  CONSTRAINT `fixprojekte_ibfk_1` FOREIGN KEY (`projekt_id`) REFERENCES `projekt` (`id_projekt`)
114) ENGINE=InnoDB DEFAULT CHARSET=utf8;
115/*!40101 SET character_set_client = @saved_cs_client */;
116
117--
118-- Dumping data for table `fixprojekte`
119--
120
121LOCK TABLES `fixprojekte` WRITE;
122/*!40000 ALTER TABLE `fixprojekte` DISABLE KEYS */;
123INSERT INTO `fixprojekte` VALUES (1,5000),(3,90000),(4,10000);
124/*!40000 ALTER TABLE `fixprojekte` ENABLE KEYS */;
125UNLOCK TABLES;
126
127--
128-- Table structure for table `hirarchie`
129--
130
131DROP TABLE IF EXISTS `hirarchie`;
132/*!40101 SET @saved_cs_client     = @@character_set_client */;
133/*!40101 SET character_set_client = utf8 */;
134CREATE TABLE `hirarchie` (
135  `id_hirarchie` int(11) NOT NULL AUTO_INCREMENT,
136  `boss` int(11) DEFAULT NULL,
137  `mitarbeiter` int(11) DEFAULT NULL,
138  PRIMARY KEY (`id_hirarchie`),
139  KEY `boss` (`boss`),
140  KEY `mitarbeiter` (`mitarbeiter`),
141  CONSTRAINT `hirarchie_ibfk_1` FOREIGN KEY (`boss`) REFERENCES `mitarbeiter` (`id_mitarbeiter`),
142  CONSTRAINT `hirarchie_ibfk_2` FOREIGN KEY (`mitarbeiter`) REFERENCES `mitarbeiter` (`id_mitarbeiter`)
143) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
144/*!40101 SET character_set_client = @saved_cs_client */;
145
146--
147-- Dumping data for table `hirarchie`
148--
149
150LOCK TABLES `hirarchie` WRITE;
151/*!40000 ALTER TABLE `hirarchie` DISABLE KEYS */;
152INSERT INTO `hirarchie` VALUES (1,2,3),(2,5,7),(3,1,4),(4,1,6),(5,4,8),(6,2,9),(7,2,10),(8,7,11);
153/*!40000 ALTER TABLE `hirarchie` ENABLE KEYS */;
154UNLOCK TABLES;
155
156--
157-- Table structure for table `mitarbeiter`
158--
159
160DROP TABLE IF EXISTS `mitarbeiter`;
161/*!40101 SET @saved_cs_client     = @@character_set_client */;
162/*!40101 SET character_set_client = utf8 */;
163CREATE TABLE `mitarbeiter` (
164  `id_mitarbeiter` int(11) NOT NULL AUTO_INCREMENT,
165  `vorname` varchar(50) DEFAULT NULL,
166  `nachname` varchar(50) NOT NULL,
167  `lohn` int(11) NOT NULL,
168  `abteilung_id` int(11) NOT NULL,
169  PRIMARY KEY (`id_mitarbeiter`),
170  KEY `abteilung_id` (`abteilung_id`),
171  CONSTRAINT `mitarbeiter_ibfk_1` FOREIGN KEY (`abteilung_id`) REFERENCES `abteilung` (`id_abteilung`)
172) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
173/*!40101 SET character_set_client = @saved_cs_client */;
174
175--
176-- Dumping data for table `mitarbeiter`
177--
178
179LOCK TABLES `mitarbeiter` WRITE;
180/*!40000 ALTER TABLE `mitarbeiter` DISABLE KEYS */;
181INSERT INTO `mitarbeiter` VALUES (1,NULL,'Meier',400,1),(2,'Robert','Aschenputtel',350,4),(3,'Max','Frankenstein',200,2),(4,NULL,'Hauser',375,3),(5,NULL,'Wollishofer',250,5),(6,'Frederick','Juncker',300,3),(7,'Robin','Schumacher',400,1),(8,'Fritz','Mustermann',315,5),(9,'Thomas','Breidenbach',290,2),(10,'Severin','Flamme',345,1),(11,'Tom','Cherry',500,1);
182/*!40000 ALTER TABLE `mitarbeiter` ENABLE KEYS */;
183UNLOCK TABLES;
184
185--
186-- Table structure for table `projekt`
187--
188
189DROP TABLE IF EXISTS `projekt`;
190/*!40101 SET @saved_cs_client     = @@character_set_client */;
191/*!40101 SET character_set_client = utf8 */;
192CREATE TABLE `projekt` (
193  `id_projekt` int(11) NOT NULL AUTO_INCREMENT,
194  `projektname` varchar(45) NOT NULL,
195  `projektbeschreibung` varchar(300) DEFAULT NULL,
196  PRIMARY KEY (`id_projekt`)
197) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
198/*!40101 SET character_set_client = @saved_cs_client */;
199
200--
201-- Dumping data for table `projekt`
202--
203
204LOCK TABLES `projekt` WRITE;
205/*!40000 ALTER TABLE `projekt` DISABLE KEYS */;
206INSERT INTO `projekt` VALUES (1,'Firmenprojekt','Default-Projekt'),(2,'Sitzprojekt',NULL),(3,'Werbeprojekt','nur für werbung'),(4,'Sammelprojekt','ist das eine beschreibung'),(5,'Ideensammelprojekt','Ideen sind ausgegangen');
207/*!40000 ALTER TABLE `projekt` ENABLE KEYS */;
208UNLOCK TABLES;
209
210--
211-- Temporary view structure for view `projektkosten`
212--
213
214DROP TABLE IF EXISTS `projektkosten`;
215/*!50001 DROP VIEW IF EXISTS `projektkosten`*/;
216SET @saved_cs_client     = @@character_set_client;
217SET character_set_client = utf8;
218/*!50001 CREATE VIEW `projektkosten` AS SELECT 
219 1 AS `id_buchung`,
220 1 AS `projektname`,
221 1 AS `kosten`*/;
222SET character_set_client = @saved_cs_client;
223
224--
225-- Table structure for table `rechnungen`
226--
227
228DROP TABLE IF EXISTS `rechnungen`;
229/*!40101 SET @saved_cs_client     = @@character_set_client */;
230/*!40101 SET character_set_client = utf8 */;
231CREATE TABLE `rechnungen` (
232  `id_rechnung` int(11) NOT NULL,
233  `projekt_id` int(11) NOT NULL,
234  PRIMARY KEY (`id_rechnung`),
235  KEY `projekt_id` (`projekt_id`),
236  CONSTRAINT `rechnungen_ibfk_1` FOREIGN KEY (`projekt_id`) REFERENCES `projekt` (`id_projekt`)
237) ENGINE=InnoDB DEFAULT CHARSET=utf8;
238/*!40101 SET character_set_client = @saved_cs_client */;
239
240--
241-- Dumping data for table `rechnungen`
242--
243
244LOCK TABLES `rechnungen` WRITE;
245/*!40000 ALTER TABLE `rechnungen` DISABLE KEYS */;
246INSERT INTO `rechnungen` VALUES (1,1),(2,2),(3,3),(6,3),(4,4),(5,5);
247/*!40000 ALTER TABLE `rechnungen` ENABLE KEYS */;
248UNLOCK TABLES;
249
250--
251-- Table structure for table `regieprojekte`
252--
253
254DROP TABLE IF EXISTS `regieprojekte`;
255/*!40101 SET @saved_cs_client     = @@character_set_client */;
256/*!40101 SET character_set_client = utf8 */;
257CREATE TABLE `regieprojekte` (
258  `projekt_id` int(11) NOT NULL,
259  `kosten_pro_stunde_an_kunden` int(11) DEFAULT NULL,
260  PRIMARY KEY (`projekt_id`),
261  CONSTRAINT `regieprojekte_ibfk_1` FOREIGN KEY (`projekt_id`) REFERENCES `projekt` (`id_projekt`)
262) ENGINE=InnoDB DEFAULT CHARSET=utf8;
263/*!40101 SET character_set_client = @saved_cs_client */;
264
265--
266-- Dumping data for table `regieprojekte`
267--
268
269LOCK TABLES `regieprojekte` WRITE;
270/*!40000 ALTER TABLE `regieprojekte` DISABLE KEYS */;
271INSERT INTO `regieprojekte` VALUES (2,300),(5,375);
272/*!40000 ALTER TABLE `regieprojekte` ENABLE KEYS */;
273UNLOCK TABLES;
274
275--
276-- Table structure for table `taetigkeit`
277--
278
279DROP TABLE IF EXISTS `taetigkeit`;
280/*!40101 SET @saved_cs_client     = @@character_set_client */;
281/*!40101 SET character_set_client = utf8 */;
282CREATE TABLE `taetigkeit` (
283  `id_taetigkeit` int(11) NOT NULL AUTO_INCREMENT,
284  `taetigkeit` varchar(45) NOT NULL,
285  PRIMARY KEY (`id_taetigkeit`)
286) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
287/*!40101 SET character_set_client = @saved_cs_client */;
288
289--
290-- Dumping data for table `taetigkeit`
291--
292
293LOCK TABLES `taetigkeit` WRITE;
294/*!40000 ALTER TABLE `taetigkeit` DISABLE KEYS */;
295INSERT INTO `taetigkeit` VALUES (1,'rumsitzen'),(2,'lachen'),(3,'reden'),(4,'entwickeln'),(5,'laufen');
296/*!40000 ALTER TABLE `taetigkeit` ENABLE KEYS */;
297UNLOCK TABLES;
298
299--
300-- Table structure for table `versandte_rechnungen`
301--
302
303DROP TABLE IF EXISTS `versandte_rechnungen`;
304/*!40101 SET @saved_cs_client     = @@character_set_client */;
305/*!40101 SET character_set_client = utf8 */;
306CREATE TABLE `versandte_rechnungen` (
307  `id_versandte` int(11) NOT NULL AUTO_INCREMENT,
308  `datum` date NOT NULL,
309  `rechnung_id` int(11) NOT NULL,
310  PRIMARY KEY (`id_versandte`),
311  KEY `rechnung_id` (`rechnung_id`),
312  CONSTRAINT `versandte_rechnungen_ibfk_1` FOREIGN KEY (`rechnung_id`) REFERENCES `rechnungen` (`id_rechnung`)
313) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
314/*!40101 SET character_set_client = @saved_cs_client */;
315
316--
317-- Dumping data for table `versandte_rechnungen`
318--
319
320LOCK TABLES `versandte_rechnungen` WRITE;
321/*!40000 ALTER TABLE `versandte_rechnungen` DISABLE KEYS */;
322INSERT INTO `versandte_rechnungen` VALUES (1,'2015-04-22',1),(2,'2015-05-01',2),(3,'2015-05-02',3),(4,'2015-05-03',4),(5,'2015-08-29',5),(6,'2015-09-01',6);
323/*!40000 ALTER TABLE `versandte_rechnungen` ENABLE KEYS */;
324UNLOCK TABLES;
325
326--
327-- Table structure for table `zwischentabelle`
328--
329
330DROP TABLE IF EXISTS `zwischentabelle`;
331/*!40101 SET @saved_cs_client     = @@character_set_client */;
332/*!40101 SET character_set_client = utf8 */;
333CREATE TABLE `zwischentabelle` (
334  `id_eintrag` int(11) NOT NULL AUTO_INCREMENT,
335  `projekt_id` int(11) NOT NULL,
336  `mitarbeiter_id` int(11) NOT NULL,
337  PRIMARY KEY (`id_eintrag`),
338  KEY `projekt_id` (`projekt_id`),
339  CONSTRAINT `zwischentabelle_ibfk_1` FOREIGN KEY (`projekt_id`) REFERENCES `projekt` (`id_projekt`)
340) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
341/*!40101 SET character_set_client = @saved_cs_client */;
342
343--
344-- Dumping data for table `zwischentabelle`
345--
346
347LOCK TABLES `zwischentabelle` WRITE;
348/*!40000 ALTER TABLE `zwischentabelle` DISABLE KEYS */;
349INSERT INTO `zwischentabelle` VALUES (1,1,1),(2,2,2),(3,3,3),(4,3,2),(5,3,4),(6,4,5),(7,1,6),(8,1,8),(9,1,9),(10,1,10);
350/*!40000 ALTER TABLE `zwischentabelle` ENABLE KEYS */;
351UNLOCK TABLES;
352
353--
354-- Final view structure for view `projektkosten`
355--
356
357/*!50001 DROP VIEW IF EXISTS `projektkosten`*/;
358/*!50001 SET @saved_cs_client          = @@character_set_client */;
359/*!50001 SET @saved_cs_results         = @@character_set_results */;
360/*!50001 SET @saved_col_connection     = @@collation_connection */;
361/*!50001 SET character_set_client      = utf8 */;
362/*!50001 SET character_set_results     = utf8 */;
363/*!50001 SET collation_connection      = utf8_general_ci */;
364/*!50001 CREATE ALGORITHM=UNDEFINED */
365/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
366/*!50001 VIEW `projektkosten` AS select `buchung`.`id_buchung` AS `id_buchung`,`projekt`.`projektname` AS `projektname`,sum((`mitarbeiter`.`lohn` * `buchung`.`stunden`)) AS `kosten` from (((`buchung` join `zwischentabelle` on((`zwischentabelle`.`id_eintrag` = `buchung`.`eintrag_id`))) join `projekt` on((`zwischentabelle`.`projekt_id` = `projekt`.`id_projekt`))) join `mitarbeiter` on((`zwischentabelle`.`mitarbeiter_id` = `mitarbeiter`.`id_mitarbeiter`))) group by `projekt`.`projektname` */;
367/*!50001 SET character_set_client      = @saved_cs_client */;
368/*!50001 SET character_set_results     = @saved_cs_results */;
369/*!50001 SET collation_connection      = @saved_col_connection */;
370/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
371
372/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
373/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
374/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
375/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
376/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
377/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
378/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
379
380-- Dump completed on 2016-04-28 23:09:00