· 7 years ago · Feb 27, 2019, 09:44 AM
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-- -----------------------------------------------------
6DROP DATABASE IF EXISTS db1;
7CREATE DATABASE IF NOT EXISTS db1;
8
9CREATE SCHEMA IF NOT EXISTS db1 DEFAULT CHARACTER SET utf8 ;
10USE db1 ;
11
12
13-- -----------------------------------------------------
14-- Kunde
15-- -----------------------------------------------------
16CREATE TABLE Kunde (
17 kid INT NOT NULL AUTO_INCREMENT,
18 vorname VARCHAR(255) NOT NULL,
19 nachname VARCHAR(45),
20 email VARCHAR(45),
21 Telefonnummer INT,
22 PRIMARY KEY (kid))
23 ENGINE = InnoDB;
24
25
26-- -----------------------------------------------------
27-- Auftrag
28-- -----------------------------------------------------
29CREATE TABLE Auftrag (
30 aid INT NOT NULL AUTO_INCREMENT,
31 beschreibung VARCHAR(255),
32 strasse VARCHAR(45),
33 hausnummer VARCHAR(45),
34 plz VARCHAR(45),
35 datum DATE,
36 uhrzeit TIME(6),
37 Kunde_kid INT NOT NULL,
38 PRIMARY KEY (aid),
39 CONSTRAINT fk_Auftrag_Kunde
40 FOREIGN KEY (Kunde_kid)
41 REFERENCES Kunde (kid)
42 ON DELETE NO ACTION
43 ON UPDATE NO ACTION)
44 ENGINE = InnoDB;
45
46
47-- -----------------------------------------------------
48--Dienstleistung
49-- -----------------------------------------------------
50CREATE TABLE Dienstleistung (
51 did INT NOT NULL AUTO_INCREMENT,
52 livemusik VARCHAR(45) NOT NULL,
53 tontechnik VARCHAR(45),
54 dj VARCHAR(45),
55 fotograf VARCHAR(45),
56 PRIMARY KEY (did))
57 ENGINE = InnoDB;
58
59
60-- -----------------------------------------------------
61--Dienstleistung2Auftrag
62-- -----------------------------------------------------
63CREATE TABLE Dienstleistung2Auftrag (
64 did INT NOT NULL,
65 aid INT NOT NULL,
66 PRIMARY KEY (did, aid),
67 CONSTRAINT fk_Dienstleistung_has_Auftrag_Dienstleistung1
68 FOREIGN KEY (did)
69 REFERENCES Dienstleistung (did)
70 ON DELETE NO ACTION
71 ON UPDATE NO ACTION,
72 CONSTRAINT fk_Dienstleistung_has_Auftrag_Auftrag1
73 FOREIGN KEY (aid)
74 REFERENCES Auftrag (aid)
75 ON DELETE NO ACTION
76 ON UPDATE NO ACTION)
77 ENGINE = InnoDB;
78
79
80-- -----------------------------------------------------
81-- Rezensionen
82-- -----------------------------------------------------
83CREATE TABLE Rezensionen (
84 rid INT NOT NULL AUTO_INCREMENT,
85 vorname VARCHAR(45) NOT NULL,
86 ort VARCHAR(45),
87 beschreibung VARCHAR(255),
88 Dienstleistung_did INT NOT NULL,
89 PRIMARY KEY (rid),
90 CONSTRAINT fk_Rezensionen_Dienstleistung1
91 FOREIGN KEY (Dienstleistung_did)
92 REFERENCES mydb.Dienstleistung (did)
93 ON DELETE NO ACTION
94 ON UPDATE NO ACTION)
95 ENGINE = InnoDB;
96-------------------------------------------
97INSERT INTO Kunde (vorname,nachname,email,telefonnummer ) VALUES
98('Lindenberg', 'Udo','david.lfveringhaus@gmail.com','1431515235324'),
99('Merkel', 'Angelo','david.leveringkaus@gmail.com','3231431253554245'),
100('Trump', 'Donald''david.leverhnghaus@gmail.com','1341235145425'),
101('Mueller', 'Anke''david.leeeringhaus@gmail.com','53215231235123'),
102('Seehofer', 'Horst''david.levhringhaus@gmail.com','1234324213414'),
103('Pan', 'Peter','david.leverinehaus@gmail.com','14312431241'),
104('Hernandez', 'James','david.lezeringhaus@gmail.com','123412341234'),
105('Darm', 'David','david.leveringhuus@gmail.com','4732746123467'),
106('Schroeder', 'Haci','david.levejinghaus@gmail.com','31238272626727'),
107('Meier', 'Guenther','david.levqringhaus@gmail.com','2323412412412'),
108('Baumann', 'Ralf','david.leversnghaus@gmail.com','4341341234241'),
109('Abou Hamzel', 'Martin','david.leveringhahs@gmail.com','34881242234'),
110('Schmidt', 'Paul','david.leveringhavs@gmail.com','8712374478874');
111
112
113SET SQL_MODE=@OLD_SQL_MODE;
114SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
115SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;