WwN3LNWM

1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
4
5
6
7-- -----------------------------------------------------
8DROP DATABASE IF EXISTS db1;
9CREATE DATABASE IF NOT EXISTS db1;
10
11CREATE SCHEMA IF NOT EXISTS db1 DEFAULT CHARACTER SET utf8 ;
12USE db1 ;
13
14
15-- -----------------------------------------------------
16-- Kunde
17-- -----------------------------------------------------
18CREATE TABLE Kunde (
19  kid INT NOT NULL AUTO_INCREMENT,
20  vorname VARCHAR(255) NOT NULL,
21  nachname VARCHAR(45),
22  email VARCHAR(45),
23  telefonnummer INT,
24  PRIMARY KEY (kid))
25  ENGINE = InnoDB;
26
27
28-- -----------------------------------------------------
29-- Auftrag
30-- -----------------------------------------------------
31CREATE TABLE Auftrag (
32  aid INT NOT NULL AUTO_INCREMENT,
33  beschreibung VARCHAR(255),
34  strasse VARCHAR(45),
35  hausnummer VARCHAR(45),
36  plz VARCHAR(45),
37  datum DATE,
38  uhrzeit TIME(6),
39  Kunde_kid INT NOT NULL,
40  PRIMARY KEY (aid),
41  CONSTRAINT fk_Auftrag_Kunde
42    FOREIGN KEY (Kunde_kid)
43    REFERENCES Kunde (kid)
44    ON DELETE NO ACTION
45    ON UPDATE NO ACTION)
46	ENGINE = InnoDB;
47
48
49-- -----------------------------------------------------
50--Dienstleistung
51-- -----------------------------------------------------
52CREATE TABLE Dienstleistung (
53  did INT NOT NULL AUTO_INCREMENT,
54  livemusik TINYINT NOT NULL,
55  tontechnik TINYINT,
56  dj TINYINT,
57  fotograf TINYINT,
58  PRIMARY KEY (did))
59  ENGINE = InnoDB;
60
61
62-- -----------------------------------------------------
63--Dienstleistung2Auftrag
64-- -----------------------------------------------------
65CREATE TABLE Dienstleistung2Auftrag (
66  did INT NOT NULL,
67  aid INT NOT NULL,
68  PRIMARY KEY (did, aid),
69  CONSTRAINT fk_Dienstleistung_has_Auftrag_Dienstleistung1
70    FOREIGN KEY (did)
71    REFERENCES Dienstleistung (did)
72    ON DELETE NO ACTION
73    ON UPDATE NO ACTION,
74  CONSTRAINT fk_Dienstleistung_has_Auftrag_Auftrag1
75    FOREIGN KEY (aid)
76    REFERENCES Auftrag (aid)
77    ON DELETE NO ACTION
78    ON UPDATE NO ACTION)
79	ENGINE = InnoDB;
80
81
82-- -----------------------------------------------------
83-- Rezensionen
84-- -----------------------------------------------------
85CREATE TABLE Rezensionen (
86  rid INT NOT NULL AUTO_INCREMENT,
87  vorname VARCHAR(45) NOT NULL,
88  ort VARCHAR(45),
89  beschreibung VARCHAR(255),
90  Dienstleistung_did INT NOT NULL,
91  PRIMARY KEY (rid),
92  CONSTRAINT fk_Rezensionen_Dienstleistung1
93    FOREIGN KEY (Dienstleistung_did)
94    REFERENCES mydb.Dienstleistung (did)
95    ON DELETE NO ACTION
96    ON UPDATE NO ACTION)
97	ENGINE = InnoDB;
98	
99	
100-------------------------------------------------------
101
102
103
104SET SQL_MODE=@OLD_SQL_MODE;
105SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
106SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;