Assignment 3

This assignment consists of four questions. It is due on Friday, 22 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 (4 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 (eninity-relationship diagram can be found here)

  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 (ERD can be found here)

  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 (ERD can be found here)

  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 (3 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 (3 points)

For this question, make use of the BTCP database. The ERD of this database can be found below:


Explain how you would go about answering the following research question:

Which subjects did De Erven F. Bohn focus on in the early twentieth century? Does this differ substantially from the subjects De Erven F. Bohn focused on in theĀ second half of the nineteenth century?

Formulate the type of information you would look for, then create queries with which you could obtain that information. Also explain what the result of your query looks like. You may also work with a succession of queries for different approaches to the research question.

This question mainly test your ability to translate open research questions to concrete SQL queries. The focus, in other words, is primarily on the method that you use to address these questions. In this question, the exact results which are returned by the database are of less importance.

You can run your queries in MYSQL.