/*Sebastian Bergner		FS24*/
/*MSDNAA Datenbank*/


CREATE DATABASE MSDNAA;

USE MSDNAA;

CREATE TABLE Rechtezugehoerigkeit
(
	RZ_ID			INT PRIMARY KEY AUTO_INCREMENT,
	Bezeichnung		VARCHAR(30) NOT NULL
)ENGINE=INNODB;

CREATE TABLE Produkt
(
	P_ID			INT PRIMARY KEY AUTO_INCREMENT,
	Bezeichnung		VARCHAR(30) NOT NULL
)ENGINE=INNODB;

CREATE TABLE Klassenlehrer
(
	KL_ID			INT PRIMARY KEY AUTO_INCREMENT,
	Vorname			VARCHAR(30) NOT NULL,
	Nachname		VARCHAR(30) NOT NULL,
	Kuerzel			VARCHAR(4)
)ENGINE=INNODB;

CREATE TABLE PLZ
(
	PLZ_ID			INT PRIMARY KEY AUTO_INCREMENT,
	Postleitzahl	INT NOT NULL,
	Ort				VARCHAR(50) NOT NULL
)ENGINE=INNODB;

CREATE TABLE RZP
(
	RZP_ID			INT PRIMARY KEY AUTO_INCREMENT,
	RZ_ID			INT,
	P_ID			INT,

	FOREIGN KEY (RZ_ID) REFERENCES Rechtezugehoerigkeit (RZ_ID),
	FOREIGN KEY (P_ID) REFERENCES Produkt (P_ID)
)ENGINE=INNODB;

CREATE TABLE Bildungsgang
(
	BG_ID			INT PRIMARY KEY AUTO_INCREMENT,
	Bezeichnung		VARCHAR(30) NOT NULL,
	Kuerzel			VARCHAR(4),
	RZ_ID			INT,

	FOREIGN KEY (RZ_ID) REFERENCES Rechtezugehoerigkeit (RZ_ID)
)ENGINE=INNODB;

CREATE TABLE Lizenz
(
	L_ID			INT PRIMARY KEY AUTO_INCREMENT,
	Bezeichnung		VARCHAR(30) NOT NULL,
	P_ID			INT,

	FOREIGN KEY (P_ID) REFERENCES Produkt (P_ID)
)ENGINE=INNODB;

CREATE TABLE Klasse
(
	K_ID			INT PRIMARY KEY AUTO_INCREMENT,
	Bezeichnung		VARCHAR(30) NOT NULL,
	Kuerzel			VARCHAR(6) NOT NULL,
	KL_ID			INT,
	BG_ID			INT,

	FOREIGN KEY (KL_ID) REFERENCES Klassenlehrer (KL_ID),
	FOREIGN KEY (BG_ID) REFERENCES Bildungsgang (BG_ID)
)ENGINE=INNODB;

CREATE TABLE Benutzer
(
	B_ID			INT PRIMARY KEY AUTO_INCREMENT,
	Vorname			VARCHAR(30) NOT NULL,
	Nachname		VARCHAR(30) NOT NULL,
	Geburtsdatum	DATE NOT NULL,
	PLZ_ID			INT,
	K_ID			INT,

	FOREIGN KEY (PLZ_ID) REFERENCES PLZ (PLZ_ID),
	FOREIGN KEY (K_ID)	 REFERENCES Klasse (K_ID)
)ENGINE=INNODB;

CREATE TABLE Download
(
	DL_ID			INT PRIMARY KEY AUTO_INCREMENT,
	B_ID			INT,
	P_ID			INT,
	Datum			Date,
	Uhrzeit			Time,
	Verwendung		VARCHAR(30),

	FOREIGN KEY (B_ID) REFERENCES Benutzer (B_ID),
	FOREIGN KEY (P_ID) REFERENCES Produkt (P_ID)
)ENGINE=INNODB;

CREATE TABLE Einkauf
(
	E_ID			INT PRIMARY KEY AUTO_INCREMENT,
	Datum			DATE,
	Uhrzeit			TIME,
	B_ID			INT,
	L_ID			INT,

	FOREIGN KEY (B_ID) REFERENCES Benutzer (B_ID),
	FOREIGN KEY (L_ID) REFERENCES Lizenz (L_ID)
)ENGINE=INNODB;

CREATE TABLE Datenversand
(
	D_ID			INT PRIMARY KEY AUTO_INCREMENT,
	Datum			DATE,
	Uhrzeit			TIME,
	B_ID			INT,

	FOREIGN KEY (B_ID) REFERENCES Benutzer (B_ID)
)ENGINE=INNODB;


/*----------------------------------------------------------*/

INSERT INTO Rechtezugehoerigkeit (Bezeichnung) VALUE ('Fachinformatiker');

INSERT INTO Produkt (Bezeichnung) VALUES ("Word 2010");

INSERT INTO Klassenlehrer (Vorname, Nachname, Kuerzel) VALUES ('Hans','Maier','HMai');

INSERT INTO PLZ (Postleitzahl, Ort) VALUES ('40878','Ratingen');

INSERT INTO Bildungsgang (Bezeichnung, Kuerzel) VALUES ('Fachinformatiker Systemintegration','FS');

INSERT INTO Lizenz (Bezeichnung) VALUES ('Lizenz Word 2010');

INSERT INTO Klasse (Bezeichnung, Kuerzel) VALUES ('Fachinformatiker Systemintegration 2. Jahr','FS24');

INSERT INTO Benutzer (Vorname, Nachname, Geburtsdatum) VALUES ('Peter','Maurer','1990-02-16');

INSERT INTO Download (Datum, Uhrzeit , Verwendung ) VALUES ('2011-08-14','14:51:45','Onlinekauf von Word 2010');

INSERT INTO Einkauf (Datum, Uhrzeit) VALUES ('2011-08-14','14:50:53');

INSERT INTO Datenversand (Datum, Uhrzeit) VALUES ('2011-08-14','14:51:45');