create database demoDB;
use demoDB;

create table Buch
  (titel       varchar(255),     
   seiten      integer,
   jahr        year,
   auflage     decimal(2),
   preis       decimal(6,2),
   isbn        decimal(13,0) NOT NULL,
   verlag_id   int,
   PRIMARY KEY(isbn)
  );

create table Autor
  (vorname     varchar(40),
   name        varchar(40),
   autor_id    integer NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(autor_id)
  );

create table Verlag
  (name         varchar(40),
   ort          varchar(40),
   verlag_id    integer NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(verlag_id)
  );

create table Schlagwort
  (wort     varchar(40),
   wort_id  integer NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(wort_id)
  ); 
   
create table BuchAutor
  (autor_id  integer NOT NULL,
   isbn      decimal(13,0) NOT NULL,
   PRIMARY KEY(isbn, autor_id)
  );

create table BuchSchlagwort
  (wort_id   integer NOT NULL,
   isbn      decimal(13,0) NOT NULL,
   PRIMARY KEY(isbn, wort_id)
  );

INSERT INTO Verlag VALUES
  ('Harri Deutsch','Frankfurt',1),
  ('Wiley','New York',2),
  ('Springer','Heidelberg',3),
  ('Hanser','München',4),
  ('Vieweg','Wiesbaden',5),
  ('Addison-Wesley','Boston',6),
  ('O\'Reilly','Cambridge',7);

INSERT INTO Buch VALUES 
  ('Fundamentals of Physics Extended',1198,1997,5,293.37,9780471105596,2),
  ('Grundlagen der Gastechnik',515,NULL,5,79.80,9783446211094,4),
  ('Einführung in die Thermodynamik',468,1999,12,59.80,9783446211100,4),
  ('Thermodynamik für Ingenieure',NULL,1999,2,54.00,9783528147853,5),
  ('Physik für Ingenieure',744,1999,7,79.00,9783540661351,3),
  ('Thermodynamics and Statistical Mechanics',463,2000,3,114.66,9783540942993,3),
  ('Physik - Der Grundkurs',426,1999,1,38.00,9783817115761,1),
  ('Taschenbuch der Physik',1092,2000,4,68.00,9783817116287,1),
  ('Relationale Datenbanken und SQL',352,2000,2,59.90,9783827315588,6),
  ('MySQL & mSQL',512,2000,1,79.00,9783897211636,7);


INSERT INTO Autor VALUES
  ('Horst','Stöcker',1),
  ('David','Halliday',2),
  ('Robert','Resnick',3),
  ('Jearl','Walker',4),
  ('Günter','Cerbe',5),
  ('Walter','Greiner',6),
  ('Ludwig','Neise',7),
  ('Klaus','Langeheinecke',8),
  ('Peter','Jany',9),
  ('Eugen','Sapper',10),
  ('Rudolf','Pitka',11),
  ('Steffen','Bohrmann',12),
  ('Günther','Terlecki',13),
  ('Hans-Joachim','Hoffmann',14),
  ('Günter','Matthiesen',15),
  ('Michael','Unterstein',16),
  ('Randy','Yarger',17),
  ('George','Reese',18),
  ('Tim','King',19),
  ('Ekbert','Hering',20),
  ('Rolf','Martin',21),
  ('Martin','Stohrer',22);

INSERT INTO Schlagwort VALUES
  ('Physik',1),
  ('Thermodynamik',2),
  ('Java',3),
  ('Quantenmechanik',4),
  ('SQL',5),
  ('Datenbank',6),
  ('Lehrbuch',7),
  ('Handbuch',8),
  ('Ingenieurwesen',9),
  ('Statistische Physik',10);

INSERT INTO BuchAutor VALUES
  (2,9780471105596),
  (3,9780471105596),
  (4,9780471105596),
  (5,9783446211094),
  (5,9783446211100),
  (14,9783446211100),
  (8,9783528147853),
  (9,9783528147853),
  (10,9783528147853),
  (20,9783540661351),
  (21,9783540661351),
  (22,9783540661351),
  (1,9783540942993),
  (6,9783540942993),
  (7,9783540942993),
  (1,9783817115761),
  (11,9783817115761),
  (12,9783817115761),
  (13,9783817115761),
  (1,9783817116287),
  (15,9783827315588),
  (16,9783827315588),
  (17,9783897211636),
  (18,9783897211636),
  (19,9783897211636);

INSERT INTO BuchSchlagwort VALUES
  (1,9780471105596),
  (2,9780471105596),
  (4,9780471105596),
  (7,9780471105596),
  (2,9783446211094),
  (7,9783446211094),
  (2,9783528147853),
  (7,9783528147853),
  (9,9783528147853),
  (1,9783540661351),
  (2,9783540661351),
  (4,9783540661351),
  (7,9783540661351),
  (1,9783540942993),
  (2,9783540942993),
  (10,9783540942993),
  (1,9783817115761),
  (2,9783817115761),
  (7,9783817115761),
  (1,9783817116287),
  (2,9783817116287),
  (4,9783817116287),
  (8,9783817116287),
  (5,9783827315588),
  (6,9783827315588),
  (7,9783827315588),
  (5,9783897211636),
  (6,9783897211636),
  (8,9783897211636);
