Database and SQL Exercises

N.B. The answers to these exercises can be found here

Exercise 1

The Treasures exhibition, created for the website of the European Library, displays a number of artifacts from the national libraries of Europe. Try to reconstruct the database underlying this website.

Exercise 2

Open the database treasure.accdb, look at the design of the various tables in this database and specify which relations have been defined between objects in these tables. Also note the cardinality of these relations. The ERD of this database can be found here.

Exercise 3

During the seminar on 6 November, you have worked with an export from the Short Title Catalogue of the Netherlands in XML. Create a design for a database which can capture the same data in the form of a relational database.

Create a text file which gives information about the various entities you would like to propose:

ENTITY: [Name Table]
ATTRIBUTES:
[Attribute1] (PK)
[Attribute2]
[Attribute3]

If one of the attributes serves as a foreign key, indicate this by placing the text "FK, connected to table [TABLE NAME]" in brackets, after the name of the attribute.

Exercise 4

Below, you can find some data about books published by De Erven F. Bohn and about letters from the Bohn archive. In addition, two fragments are given from transcriptions of two letters from this archive. Analyse the data and structure them in such a way that the model can be mapped to a relational database. To do this, identify the relevant entities and determine the relationships between them. Also name the attributes which need to be recorded for each entity. During the analysis, bear in mind that we are ultimately going to use the database to build a website that offers functionalities that are similar to those found at the ePistolarium website.

Title: the Tragedy of Julius Caesar
 Author/Creator: Shakespeare, William 1564-1616
 Translated by: Kok, A.S. 1831-1915
 Description: Note: Rugtitel: Julius Caesar.
 Note: Oorspr. uitg.: 1623.
 Publisher: Haarlem : Bohn
 Date: 1872
 Form: 175 p. ; 19 cm.
 Language: Dutch

  Title: Syrlin
  Author/Creator: Ouida
  Translated by: Huygens, Cornelie 1848-1902
  Publisher: Haarlem : Erven F. Bohn
  Date: 1891
  Form: 2 dl. ; 23 cm.
  Language: Dutch

  Title: Letter from De Erven F. Bohn Haarlem to Cornélie Huygens (1848-1902)
  Author/Creator: De Erven F. Bohn (Haarlem).
  Recipient: Huygens, Cornélie, 1848-1902
  Shelfmark: BOH C 6
  Note: fol. 403, 406, 438.
  Publisher: Haarlem
  Date: 1881-1882
  Language: Dutch

  Title: Letter from Ouida to De Erven F. Bohn Haarlem
  Author/Creator: Ouida
  Recipient: De Erven F. Bohn (Haarlem)
  Shelfmark: BOH C 109
  Publisher: S.l.
  Date: 1894
  Language: English
Transcription of UBL BOH C 80 / Ouida

Dear Sirs
 I will ask Mrs Chatto to
 send you The
 Village Commune
 at once [...]
 Ouida

Transcription of BOH C 71 / Lytton (2)

Dear Sirs,
 Last year I accepted the offer I had received from
 you by telegraph of 40 £
 for the purchase of the
 right of translating my
 father's novel Kenelm
 Chillingly of which a
 copy was forwarded to
 you [...]
 Lord Lytton
Exercise 5

Below, you can find some unstructured data items. Analyse the data and structure them in such a way that the model can be mapped to a relational database. To do this, identify the relevant entities and determine the relationships between them. Also name the attributes which need to be recorded for each entity, and the datatype of each attibute.

When you are satisfied with your analysis, create your database in Microsoft Access and fill it with the data.

Kirberger, Willem Hendrik
 lived:1824-1897
 born in: Amsterdam
 died in: Velp

 Kruseman, Arie Cornelis 1818-1894
 Born in: Haarlem
 Died in: Haarlem
 Author of:
 Bouwstoffen voor een geschiedenis van den Nederlandschen boekhandel gedurende de halve eeuw 1830-1880. 2 vols.
 Published by P.N. van Kampen & Zoon, Amsterdam in 1886-1887.
 Aanteekeningen betreffende den boekhandel van Noord-Nederland in de 17e en 18e eeuw.
 655 p. Amsterdam : Van Kampen, 1893

 Louis D. Petit
 Lived: 1847-1918
 born in: Amsterdam
 died in: Leiden

 Motley, John Lothrop
 Lived: 1814-77
 Born: Dorchester, Mass., USA
 Died: Dorchester, UK
 Author of: The Rise of the Dutch Republic. 3 vols.
 published by John Murray Publishers Ltd., 338 Euston Road in London in 1856
 The life and death of John of Barneveld, advocate of Holland : with a view of the primary causes and movements of the Thirty years' war.
 Published by Martinus Nijhoff, The Hague, 1874

 LTK 2044
 From: Kruseman, Arie Cornelis, 1818-1894
 To: Petit, Louis D., 1847-1918
 Place: The Hague Inclusive
 dates: 1886-1889
 Language: Dutch
 Number of letters: 5

 LTK 1504
 Letters from Motley, John Lothrop, 1814-1877
 To: Kruseman, Arie Cornelis, 1818-1894
 Place: The Hague
 Date: 1872
 Language: Dutch
 Number of letters: 2

 LTK 1509: 1
 Letters from W.H. Kirberger
 To: A.C. Kruseman
 Place: Amsterdam
 Inclusive dates: 1879-80
 Language: Dutch
 Number of letters: 2
Exercise 6

Same as Exercise 3.

  Edward Gibbon (Putney, 1737 – London January 16, 1794)
  The history of the decline and fall of the Roman Empire / Edward Gibbon.
  London, Baynes, 1782-1788

  Thomas Carlyle (Ecclefechan, 4 december 1795 - Londen, 5 februari 1881)
  The French Revolution: A History.
  Leipzig : Tauchnitz, 1851

  George Eliot (Nuneaton, 22 November 1819 – London, 22 December 1880)
  Adam Bede.
  Edinburgh : William Blackwood, 1859.

  The Mill on the Floss.
  Edinburgh [etc.] : Blackwood, 1878

  Registration of loans from the National Library, The Hague:
  Borrowed by: Thorbecke
  Book: Gibbon
  Borrowed: 20 April 1861
  Returned: 14-06-1861

  Borrowed by: Dominicus Blankenheym
  Book: Carlyle, French Revolution
  Borrowed: 02 July 1857
  Returned: 24 July 1857

  Borrowed by: Van Pallant
  Book: Adam Bede
  Borrowed: 1902, 12 November
  Returned: 1903, 23 January

  Borrowed by: W. Schimmelpenninck
  Book: Roman Empire (Gibbon)
  Borrowed: 04 September 1878
  Returned: 14 December 1878

  Borrowed by: Van Pallant, W.
  Book: Gibbon
  Borrowed: 20 December 1878
  Returned: 15 March 1879

  Borrowed by: Van Pallant
  Book: George Eliot, Mill on the Floss
  Borrowed: 20 December 1878
  Returned: 29 January 1879

  Visitors:
  Dr.Mr. Johan Rudolf Thorbecke (Zwolle, 14 January 1798 - Den Haag, 4 June 1872)
  D. Blankenheym (Rotterdam, 25 March1797 - Rotterdam, 6 September 1872)
  Mr. W.A.A.J. baron Schimmelpenninck van der Oye (Voorst, 18 september 1834 - Voorst, 31 augustus 1889) Mr. W.C. baron van Pallandt van Waardenburg (The Hague, 12 October 1836 - Waardenburg, 22 May 1905)

Exercise 7

Download the database treasure.mdb.The ERD of this database can be found here. For questions (f) and following, you need to work with so-called aggregate functions, such as COUNT() or MIN(). In MySQL, the opening bracket needs to be placed directly after the name of the function.

Write queries that can return the following information:
  1. The last name, the first name and the year of birth of all the persons in the table CREATOR. Sort the rows by year of birth. The most recent year should appear first.
  2. All the columns in the table CITY, and sort the rows alphabetically, by name of the city.
  3. The treasures that were produced after 1800. Display only the title and the year
  4. The creators whose first name starts with the letter "W"
  5. The distinct subject codes in the table TREASURE, and sort the result alphabetically.
  6. The year of the oldest treasure in the database.
  7. The number of countries in the table CREATOR.
  8. The most recent year of death in the table CREATOR.
  9. The oldest treasure from each library. For this query, you only need to work with the identifiers of the libraries and not with their full names.
  10. The number of persons for each country in the table CREATOR.
  11. Those libraries that are represented by two or more treasures.
  12. Those subjects that have been assigned to two or more treasures in the database.
  13. The title, year, and provide a full description of the subject code of all the treasures in the database.
  14. The names of all the libraries and the names of the cities in which they are located.
  15. A list with all names of all cities and the full names of the countries in which they are situated.
  16. The title, year, and a full description of the subject code of all treasures, and the full names of their creators. Sort the list alphabetically by the last name of the creator.
You can test your queries in MS Access. Alternatively, your queries can also be tested in MySQL.

 

Exercise 8

The database that was created for this exercise is based on a list of UK's 100 best-selling books, which was published by the Guardian. Information about the design of this database can be found here. Use this database to answer the following questions. The queries can be entered in the field below. These queries will then be executed in an implementation of this database in MySQL.

  1. Which authors have written more than one beststeller?
  2. Which publishers are responsible for more than three bestsellers?
  3. What are the best-selling books in the genre "Popular Science"?
  4. What is the average number of sales of a book written by J.K. Rowling?
You can run these queries in MYSQL. The results of SQL queries can also be visualised. The results of the queries that are given in the text field below will be visualised as a bar chart. The field that you mention first after "SELECT" will be shown on the Y-Axis, and the lengths of the bars will be determined by the values for the field that you mentions secondly. Create bar charts which can display the following information:
  1. For each genre, show the average number of sales
  2. For each genre, show the maximum number of books that have been sold
  3. For each author, show the total number of sales
  4. For each year, show the total number of bestsellers
You can run these queries in MYSQL.  

Exercise 9

The Sijthoff database contains data about all the correspondence in the Sijthoff archive. It is based on metadata downloaded from the catalogue of Leiden Unibersity Libraries. As you can see in the ERD, the table PLACE has two attributes named LONGITUDE and LATITUDE. These specify the geographic coordinates of the city that is described in this table. Using these geographic coordinates, the results of SQL queries can also be visualised on a map. Try to do this using the exercises below. In these queries, the select list must contain (1) The name of the city, (2) The longitude and (3) the latitude. Or more concretely, the Select-list needs to be be "SELECT CITY.NAME, LATITUDE, LONGITUDE". N.B. The locations of the incoming mail are stored in column "PLACE_RECEIVED". The locations of the outcoming mail can be found in "PLACE_SENT"
  1. Display the locations of all the letters that were received
  2. Display the locations of the letters that were received in the second half of the 19th century
  3. Display the locations of the letters that were received from the Western hemisphere (e.g. Unites States and UK West to Greenwich).