cviLky21

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 webserver1;
9CREATE DATABASE IF NOT EXISTS webserver1;
10
11CREATE SCHEMA IF NOT EXISTS webserver1 DEFAULT CHARACTER SET utf8 ;
12USE webserver1 ;
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  ort VARCHAR(45),
40  Kunde_kid INT NOT NULL,
41  PRIMARY KEY (aid),
42  CONSTRAINT fk_Auftrag_Kunde
43    FOREIGN KEY (Kunde_kid)
44    REFERENCES Kunde (kid)
45    ON DELETE NO ACTION
46    ON UPDATE NO ACTION)
47	ENGINE = InnoDB;
48
49
50-- -----------------------------------------------------
51--Dienstleistung
52-- -----------------------------------------------------
53CREATE TABLE Dienstleistung (
54  did INT NOT NULL AUTO_INCREMENT,
55  livemusik BOOLEAN NOT NULL,
56  tontechnik BOOLEAN,
57  dj BOOLEAN,
58  fotograf BOOLEAN,
59  PRIMARY KEY (did))
60  ENGINE = InnoDB;
61
62
63-- -----------------------------------------------------
64--Dienstleistung2Auftrag
65-- -----------------------------------------------------
66CREATE TABLE Dienstleistung2Auftrag (
67  did INT NOT NULL,
68  aid INT NOT NULL,
69  PRIMARY KEY (did, aid),
70  CONSTRAINT fk_Dienstleistung_has_Auftrag_Dienstleistung1
71    FOREIGN KEY (did)
72    REFERENCES Dienstleistung (did)
73    ON DELETE NO ACTION
74    ON UPDATE NO ACTION,
75  CONSTRAINT fk_Dienstleistung_has_Auftrag_Auftrag1
76    FOREIGN KEY (aid)
77    REFERENCES Auftrag (aid)
78    ON DELETE NO ACTION
79    ON UPDATE NO ACTION)
80	ENGINE = InnoDB;
81
82
83-- -----------------------------------------------------
84-- Rezensionen
85-- -----------------------------------------------------
86CREATE TABLE Rezensionen (
87  rid INT NOT NULL AUTO_INCREMENT,
88  vorname VARCHAR(45) NOT NULL,
89  ort VARCHAR(45),
90  beschreibung VARCHAR(255),
91  Dienstleistung_did INT NOT NULL,
92  PRIMARY KEY (rid),
93  CONSTRAINT fk_Rezensionen_Dienstleistung1
94    FOREIGN KEY (Dienstleistung_did)
95    REFERENCES mydb.Dienstleistung (did)
96    ON DELETE NO ACTION
97    ON UPDATE NO ACTION)
98	ENGINE = InnoDB;
99	
100	
101-------------------------------------------------------
102
103
104
105SET SQL_MODE=@OLD_SQL_MODE;
106SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
107SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;