/* Mostrar las bases de datos existentes y crear una nueva 'researchpapers' */
##

SHOW DATABASES;

DROP DATABASE IF EXISTS researchpapers;
CREATE DATABASE researchpapers;

USE researchpapers;

##
/* Crear y cargar la tabla 'autores' */
##
CREATE TABLE authors (ref_author INTEGER, name VARCHAR(25), PRIMARY KEY( ref_author));
LOAD DATA INFILE 'c:\\tablas\\BDAuthors.txt'
INTO TABLE authors FIELDS TERMINATED BY ','
ENCLOSED BY ' ' LINES TERMINATED BY '\r\n';

SELECT * FROM authors;

##
/* Crear y cargar la tabla 'papersinfo' */
##
CREATE TABLE papersinfo (ref_paper INTEGER PRIMARY KEY, title VARCHAR(200), publiyear INTEGER,
publisher VARCHAR(40), url VARCHAR(200));
LOAD DATA INFILE 'c:\\tablas\\BDPaperinfo.txt'
INTO TABLE papersinfo FIELDS TERMINATED BY ','
ENCLOSED BY ' ' LINES TERMINATED BY '\r\n';

SELECT * FROM papersinfo;

##
/* Crear y cargar la tabla 'afiliations' */
##
CREATE TABLE afiliations (ref_afiliation INTEGER, university VARCHAR(65), webpage VARCHAR(30), country VARCHAR(15), PRIMARY KEY(ref_afiliation));
LOAD DATA INFILE 'c:\\tablas\\BDAfiliations.txt'
INTO TABLE afiliations FIELDS TERMINATED BY ','
ENCLOSED BY ' ' LINES TERMINATED BY '\r\n';

##
/* Crear y cargar la tabla 'papersauthors' */
##
CREATE TABLE papersauthors (ref_paper INTEGER, ref_author INTEGER, PRIMARY KEY(ref_paper,ref_author));
LOAD DATA INFILE 'c:\\tablas\\BDPapers_authors.txt'
INTO TABLE papersauthors FIELDS TERMINATED BY ','
ENCLOSED BY ' ' LINES TERMINATED BY '\r\n';

SELECT * FROM papersauthors;

##
/* Crear y cargar la tabla 'authorafiliation' */
##
CREATE TABLE authorafiliation (ref_author INTEGER, ref_afiliation INTEGER, PRIMARY KEY(ref_author,ref_afiliation));
LOAD DATA INFILE 'c:\\tablas\\BDAuthors_afiliation.txt'
INTO TABLE authorafiliation FIELDS TERMINATED BY ','
ENCLOSED BY ' ' LINES TERMINATED BY '\r\n';

SELECT * FROM authorafiliation;

##
/* Crear y cargar la tabla 'papersreferences' */
##
CREATE TABLE papersreferences (ref_paper INTEGER, biblioreference INTEGER, PRIMARY KEY(ref_paper,biblioreference));
LOAD DATA INFILE 'c:\\tablas\\BDPapers_references.txt'
INTO TABLE papersreferences FIELDS TERMINATED BY ','
ENCLOSED BY ' ' LINES TERMINATED BY '\r\n';

SELECT * FROM papersreferences;

##
/* Volcar datos a un fichero*/
##

SELECT ref_paper, title  INTO OUTFILE 'c:\\tablas\\BDpapertitle.txt'
FIELDS TERMINATED BY ','  LINES TERMINATED BY '\r\n' FROM papersinfo;
