--
-- reprozieren mit
--    mysqldump -u root literature -p > literature.sql
--

create database if not exists literature;
use literature;

create table Paper
  (title         varchar(255) NOT NULL,
   year          year NOT NULL,
   volume        integer,
   number        integer,
   page1         integer,
   page2         integer,
   doi           varchar(255),
   note          varchar(255),
   publicationId int,
   paperId       integer NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(paperId)
  );

create table Author
  (name        varchar(80) NOT NULL,
   firstName   varchar(80),
   institution varchar(80),
   authorId    integer NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(authorId)
  );

create table Publication
  (name           varchar(80) NOT NULL,
   shortForm      varchar(80) NOT NULL,
   type           varchar(80),
   address        varchar(80),
   publicationId  integer NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(publicationId)
  );

create table Keyword
  (word       varchar(40) NOT NULL,
   keywordId  integer NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(keywordId)
  ); 
   
create table PaperAuthor
  (paperId   integer NOT NULL,
   authorId  integer NOT NULL,
   position  integer,
   PRIMARY KEY(paperId, authorId)
  );

create table PaperKeyword
  (paperId    integer NOT NULL,
   keywordId  integer NOT NULL,
   PRIMARY KEY(paperId, keywordId)
  );

INSERT INTO Keyword (word) VALUES
  ('simulation'),
  ('thermofluid'),
  ('bond graphs'),
  ('parallel computing'),
  ('robotics'),
  ('artificial intelligence'),
  ('discrete events'),
  ('benchmark');

INSERT INTO Publication (name, shortForm, type, address) VALUES
  ('SNE Simulation Notes Europe', 'SNE', 'Journal', NULL),
  ('Electronics', 'Electronics', 'Journal', NULL),
  ('Master Thesis', 'Master Thesis', 'Thesis', 'ETH Zürich'),
  ('5th International Modelica Conference',
   '5th Intl Modelica Conf', 'Proceedings', 'Wien'),
  ('7. Workshop der ASIM/GI-Fachgruppen STS/GMMS',
   '7. Workshop STS/GMMS', 'Proceedings', 'Heilbronn'),
  ('8. Workshop der ASIM/GI-Fachgruppen STS/GMMS',
   '8. Workshop STS/GMMS', 'Proceedings', 'Braunschweig'),
  ('10. Workshop der ASIM/GI-Fachgruppen STS/GMMS/EDU',
   '10. Workshop STS/GMMS/EDU', 'Proceedings', 'Magdeburg'),
  ('IFAC-PapersOnLine', 'IFAC-PapersOnLine', 'Journal', NULL),
  ('PhD Thesis', 'PhD Thesis', 'Thesis', 'TU Clausthal, HS Wismar'),
  ('Transactions of the Society for Computer Simulation', 'Trans Soc Comp Sim',
    'Journal', NULL);

INSERT INTO Author (name, firstName, institution) VALUES
   ('Zimmer','Dirk','Deutsches Zentrum für Luft- und Raumfahrt (DLR)'),
   ('Cellier','François','ETH Zürich'),
   ('Junglas','Peter','PHWT Vechta/Diepholz'),
   ('Weber','Niels','Deutsches Zentrum für Luft- und Raumfahrt (DLR)'),
   ('Meißner','Michael','Deutsches Zentrum für Luft- und Raumfahrt (DLR)'),
   ('Jammer','David','HS Wismar'),
   ('Pawletta','Sven','HS Wismar'),
   ('Pawletta','Thorsten','HS Wismar'),
   ('Disselkamp','Jan Philipp','PHWT Vechta/Diepholz'),
   ('Niehüser','Alexander','PHWT Vechta/Diepholz'),
   ('Schönfelder','Phillip','PHWT Vechta/Diepholz'),
   ('Kunert','Georg','HS Wismar'),
   ('Freymann','Birger','HS Wismar'),
   ('Chow','Alex','IBM Corporation');

INSERT INTO Paper (title, year, volume, number, page1, page2, doi, note,
  publicationId) VALUES 
('Solving ARGESIM Benchmark CP2 \’Parallel and Distributed Simulation\’ with'
   ' Open MPI and Matlab PCT – Lattice Boltzmann Simulation', 
   2023, 33, 2, 93, 100, '10.11128/sne.33.bncp2.10646', NULL, 1),
('Implementing Standard Examples with NSA-DEVS', 
   2022, 32, 4, 195, 202, '10.11128/sne.32.tn.10623', NULL, 1),
('Implementing the Argesim C21 benchmark with Modelica components',
   2018, NULL, NULL, 197, 202, '10.11128/arep.54', NULL, 5),
('Non-standard Queuing Policies: Definition of ARGESIM Benchmark C22',
   2019, 29, 3, 111, 115, '10.11128/sne.29.bn22.10481', NULL, 1),
('Beschleunigung eines Reinforcement-Learning-Algorithmus durch'
  ' Parallelverarbeitung für Robotikanwendungen',
   2019, NULL, NULL, 49, 52, '10.11128/arep.57', NULL, 6),
('A Modelica Library for MultiBond Graphs and its Application in 3D-Mechanics',
   2006, NULL, NULL, 1, 156, NULL, 'Adviser: Prof. François E. Cellier', 3),
('The DLR ThermoFluid Stream Library',
   2022, 11, 22, 3790, 3810, '10.3390/electronics11223790', NULL, 2),
('Regularization of the Logarithmic Mean for Robust Numerical Simulation',
   2022, 55, 20, 529, 533, '10.1016/j.ifacol.2022.09.149',
	'10th Vienna International Conference on Mathematical Modelling', 8),
('Implementing Thermodynamic Cyclic Processes Using the DLR Thermofluid'
 ' Stream Library',
   2023, NULL, NULL, 3, 10, '10.11128/arep.21.a2102', NULL, 7),
('The Modelica Multi-bond Graph Library',
   2006, NULL, NULL, 559, 568, NULL, NULL, 4),
('Aufgabenorientierte Multi-Robotersteuerungen auf Basis des SBC-Frameworks'
  ' und DEVS',
   2020, NULL, NULL, 1, 181, '10.11128/fbs.40',
	'Adviser:  Thorsten Pawletta, Umut Durak, Sven Hartmann', 9),
('Parallel DEVS: A parallel, hierarchical, modular modeling formalism and its'
  ' distributed simulator', 
   1996, 13, 2, 55, 68, NULL, NULL, 10),
('NSA-DEVS: Combining Mealy behaviour and Causality', 
   2021, 31, 2, 73, 80, '10.11128/sne.31.tn.10564', NULL, 1);
	
INSERT INTO PaperAuthor (paperId, authorId, position) VALUES
  (1,6,1), (1,3,2), (1,7,3),
  (2,6,1), (2,3,2), (2,8,3), (2,7,4),
  (3,9,1), (3,3,2), (3,10,3), (3,11,4),
  (4,3,1), (4,8,2),
  (5,6,1), (5,7,2), (5,12,3), (5,8,4),
  (6,1,1),
  (7,1,1), (7,5,2), (7,4,3),
  (8,4,1), (8,1,2),
  (9,3,1),
  (10,1,1), (10,2,2),
  (11,13,1),
  (12,14,1),
  (13,3,1);

INSERT INTO PaperKeyword (paperId, keywordId) VALUES
  (1,8), (1,4),
  (2,1), (2,7),
  (3,1), (3,8),
  (4,1), (4,7), (4,8),
  (5,4), (5,5), (5,6),
  (6,1), (6,3),
  (7,1), (7,2),
  (8,1), (8,2),
  (9,1), (9,2),
  (10,1), (10,3),
  (11,1), (11,7), (11,5),
  (12,1), (12,7),
  (13,1), (13,7);
  
