Teaching » WebCourse » Cut & Paste

Username:
Password:

Publications Database
Publications Database Entries
Publications Database Queries

Publications Database

Save this into a text file (e.g. pubs.sql) in your home directory. At the command line, navigate to the directory with this file in it, enter the MySQL command line interpreter (type practice_db) and type “\. pubs.sql” to load these commands.

DROP TABLE IF EXISTS papers;
CREATE TABLE papers (
  id int(6) NOT NULL auto_increment,
  title varchar(255) NOT NULL,
  abstract text default NULL,
  journal_id int(6) default NULL,
  year date default NULL,
  volume int(6) default NULL,
  startpage int(6) default NULL,
  endpage int(6) default NULL,
  doi varchar(50) default NULL,
  status enum('published', 'in press', 'under review', 'submitted', 'in prep','abandoned') default 'in prep',
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS authors;
CREATE TABLE authors (
  id int(6) NOT NULL auto_increment,
  firstname varchar(255) default NULL,
  initials varchar(5) default NULL,
  lastname varchar(255) NOT NULL,
  affiliation varchar(255) default NULL, 
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS journals;
CREATE TABLE journals (
  id int(6) NOT NULL auto_increment,
  longname varchar(255) NOT NULL,
  abbreviation varchar(20) default NULL,
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS author_paper;
CREATE TABLE author_paper (
  paper_id int(6) NOT NULL,
  author_id int(6) NOT NULL,
  authorder tinyint(2) UNSIGNED default NULL,
  PRIMARY KEY (paper_id, author_id)
);

top

Publications Database Entries

Do the same as above (giving this file a different name) to load in these entries. Add your own entries to this file.

INSERT INTO papers (title, journal_id, status)
VALUES ('Integrating gaze direction and expression in preferences for attractive faces', 1, 'in press');

INSERT INTO authors (firstname, initials, lastname, affiliation)
VALUES 
  ('Claire', 'C', 'Conway', 'University of Aberdeen'),
  ('Lisa', 'LM', 'DeBruine', 'University of St Andrews'),
  ('Benedict', 'BC', 'Jones', 'University of Aberdeen'),
  ('Anthony', 'AC', 'Little', 'University of Liverpool'),
  ('David', 'DR', 'Feinberg', 'Harvard University')
;

INSERT INTO journals (longname, abbreviation) VALUES ('Psychological Science', 'Psych Sci');

INSERT INTO author_paper (author_id, paper_id, authorder) 
  VALUES (1,1,4), (2,1,2), (3,1,1), (4,1,3), (5,1,5);

top

Publications Database Queries

Here are some sample queries to get information about a paper or author. Try writing a query that lists all the authors who have ever published in a particular journal or the number of authors on each paper.

-- Select title, journal and first author's name for all papers

SELECT
 papers.title as title,
 journals.longname as journal,
 CONCAT(authors.initials, ' ', authors.lastname) as firstauthor,
 IF(status='published', year, status) as year_status
FROM papers
LEFT JOIN author_paper ON paper_id=papers.id
  AND authorder=1
LEFT JOIN authors ON author_id=authors.id
LEFT JOIN journals ON journal_id=journals.id
ORDER BY status DESC, year DESC;

-- Select all papers by a particular author

SELECT 
  title,
  IF(year IS NULL, status, year) as year_status
FROM papers
LEFT JOIN author_paper ON paper_id=papers.id
WHERE author_id=1
ORDER BY  status DESC, year DESC;

-- Show how many papers each author has using the COUNT(*) function
SELECT 
  firstname, 
  lastname, 
  COUNT(*) as num_papers
FROM authors
LEFT JOIN author_paper ON authors.id=author_id
GROUP BY authors.id
ORDER BY num_papers, lastname, firstname;

top

Return to WebCourse

Disclaimer: The information found and the views expressed in these homepages are not the responsibility of the University of Aberdeen nor do they reflect institutional policy.