Assignment 3

This assignment consists of four questions. It is due on Friday, 23 December, before 23:59. please send in via gmail.

This Assignment can be sent in as one document (PDF or DOC) including the SQL-queries for Question 1 and 3, and any diagram you wish to insert for Question 2. Please take care to name and format your document according to the BDMS-stylesheet.

Question 1 (3 points)

In this question, you need to write six queries for three different relational databases. These databases have been implemented in MySQL. More information about the structure of these databases can be found in the SQL CREATE TABLE statements that have been used during their implementation.

Sijthoff database

  1. Select all the letters which have been sent by from cities in the Southern Hemisphere (i.e. cities which have a negative latitude). Display the IDs of the letters, the names of the cities and the languages in which these letters are written. Use the full name of the language, and not the language code.
  2. The names and the locations (the city names) of all the correspondents who have sent more than two letters to A.W. Sijthoff.
    N.B. Note that there are two types of links in between LETTER and CORRESPONDENT. The first link represents the senders, and the second link represents the recipients. To solve this query, the table CORRESPONDENTĀ needs to be joined twice to table LETTER. You need to use aliases to distinguish these different roles.

Bohn database

  1. Select the books that have been published by De Erven F. Bohn about the subject “Economics”. In your result, display the title, the full name of the author (if available), and the year of publication.
  2. Displays the titles of the series that consist of more than 10 books. Select the series title, and the number of books that have appeared in this series.

Elzevier database

  1. In which formats did the Elzeviers publish books? Create a list of all formats, together with the number of books printed on these book formats.
  2. Display the full names of all the authors who have written books about the topic “French language and literature”.

Question 2 (2 points)

Analyse the data below and try to structure them in such a way that they can be used efficiently in a relational database. To do this, identify the relevant entities and attributes and determine the relationships between these entities. Write a brief paragraph in which you explain the entities and the attributes that you have chosen. It is not necessary to provide a complete entity-relationship diagram, but you may do so, of course.

Book auctions

Date: 15 May 1682
Collection: Sir Richard Smith
Auctioneer: Richard Chiswell
Location: British Library

Date: 4 July 1681
Collections: Revd. R. Button; Dr. Thankfull Owen; Revd. Dr. William Howell
Auctioneer: E. Millington
Location: British Library

Date: 30 June 1686
Collection: Edmund Castell
Auctioneer: E. Millington
Location: Cambridge University

Collection contents

Richard Smith collection includes:
J.F. Ferrandus, De nephrisis et lithiasis seu de renum et visicae calculi definitione, causis, signis, praedictione et curatione (8vo, Paris, 1570) 
J. Locke, Questions Concerning the Law of Nature (4to, London, 1664) J. Wilkins, Of the Principle and Duties of Natural Religion (8vo, London, 1670)

Edmund Castell collection includes:
E. Castell, Lexicon Heptaglotton Hebraicum, Chaldaicum, Syriacum, Samaritanum, Aethiopicum, Arabicum, et Persicum (8vo, Cambridge, 1669) 
Bonaventura Vulcanius, Callimachi Cyrenaei hymni, epigrammata et fragmenta [...] (4to, Antwerp, 1584) 
R. Percyvall, Bibliotheca Hispanica (4to, London, 1591)

Question 3 (2 points)

Discuss the two mainĀ findings of the research that you have conducted during the seminar on 13 December. Which SQL have you used to produce these results? For which research questions can these queries be relevant? What is striking about the results of these queries?

Question 4 (3 points)

Digital databases containing secondary data (data obtained from external sources) can form a useful addition to the book historian’s traditional tool, but such data collections should, in some situations, be used with caution. Which general factors must be taken into account? In which ways can these factors affect the reliability and the accuracy of data analyses?

Illustrate your answer using some of the difficulties that you have encountered during the research seminar on 13 December.