YLpbTyED

1DROP DATABASE IF EXISTS praktikum;
2CREATE DATABASE IF NOT EXISTS praktikum;
3USE praktikum;
4
5DROP TABLE IF EXISTS firma;
6CREATE TABLE IF NOT EXISTS firma (
7	Firmenname VARCHAR(45) NOT NULL,
8	Bereich VARCHAR(45) NOT NULL,
9	Adresse VARCHAR(60) NOT NULL,
10	Fimagröße ENUM('Klein','Mittel','Groß'),
11	Bewertung INT(6) NOT NULL,
12	TelNR INT NOT NULL,
13    Email VARCHAR(60) NOT NULL,
14	Ansprächpartner VARCHAR(45),
15	PRIMARY KEY (Firmenname)
16);
17
18DROP TABLE IF EXISTS betreuer;
19CREATE TABLE IF NOT EXISTS betreuer (
20	id INT auto_increment NOT NULL,
21	Vorname VARCHAR(45) NOT NULL,
22    Nachname VARCHAR(45) NOT NULL,
23    TelNR INT NOT NULL,
24    Email VARCHAR(60) NOT NULL,
25    Adresse VARCHAR(45) NOT NULL,
26    primary key(id)
27       
28);
29
30DROP TABLE IF EXISTS schüler;
31CREATE TABLE IF NOT EXISTS 	schüler (
32	id INT auto_increment NOT NULL,
33	Vorname VARCHAR(45) NOT NULL,
34    Nachname VARCHAR(45) NOT NULL,
35    Klasse VARCHAR(45) NOT NULL,
36    TelNR INT NOT NULL,
37    Email VARCHAR(60) NOT NULL,
38    Adresse VARCHAR(45) NOT NULL,
39    Geburtsdatum DATETIME NOT NULL,
40    primary key(id)
41);
42
43DROP TABLE IF EXISTS user;
44CREATE TABLE IF NOT EXISTS user (
45	kontotyp ENUM('Lehrer','Schüler'),
46    username VARCHAR(60),
47    passwort VARCHAR(60),
48    primary key(username)
49);
50
51DROP TABLE IF EXISTS praktikum;
52CREATE TABLE IF NOT EXISTS praktikum (
53	id INT auto_increment primary key NOT NULL
54);
55
56ALTER TABLE betreuer 
57ADD FOREIGN KEY (username) REFERENCES User (username) on delete cascade;
58
59ALTER TABLE schüler 
60ADD FOREIGN KEY (username) REFERENCES User (username) on delete cascade;
61
62ALTER TABLE praktikum 
63ADD FOREIGN KEY (betreuer) REFERENCES betreuer(id) on delete cascade,
64ADD FOREIGN KEY (firma) REFERENCES firma(Firmenname) on delete cascade,
65ADD FOREIGN KEY (schüler) REFERENCES schüler(username) on delete cascade;
66
67
68/*
69CREATE VIEW v1 AS SELECT spalte1 from t1 where t2 = '2';
70CREATE USER schuler identified by '12345';
71GRANT SELECT ON v1 TO TESTUSER;
72*/