CREATE DATABASE nnotebook;
USE nnotebook;

CREATE TABLE kunden(
	K_Nr int PRIMARY KEY AUTO_INCREMENT,
	K_Vorname text NOT NULL,
	K_Nachname text NOT NULL,
	K_Geburtstag date NOT NULL,
	K_Strasse text NOT NULL,
	K_HausNr text NOT NULL,
	K_Ort int NOT NULL,
	K_Telefon text NOT NULL,
	K_EMail text NOT NULL,
	K_Notiz text NULL
);

CREATE TABLE orte(
	Ort_Nr int PRIMARY KEY AUTO_INCREMENT,
	Ort text NOT NULL,
	PLZ char(5) NOT NULL
);

CREATE TABLE geraete(
	G_Nr int PRIMARY KEY AUTO_INCREMENT,
	G_Bezeichnung text NOT NULL,
	G_Kaufdatum date NOT NULL,
	G_Seriennummer text NOT NULL,
	G_CPU int NOT NULL,
	G_RAM int NOT NULL,
	G_HDD int NOT NULL,
	G_TFT float NOT NULL,
	G_Preis float NOT NULL,
	G_Ausgeliehen bool NOT NULL
);

CREATE TABLE rechnungen(
	R_Nr int PRIMARY KEY AUTO_INCREMENT,
	R_Datum date NOT NULL,
	R_Kunde int NOT NULL,
	R_Bezahlt bool NOT NULL,
	R_Bermerkungen text NULL
);

CREATE TABLE rechnung_pos(
	RP_Nr int PRIMARY KEY AUTO_INCREMENT,
	R_Nr int NOT NULL,
	G_Nr int NOT NULL,
	Stck int NOT NULL,
	Einzelpreis float NOT NULL
);