Universiteit Leiden

Data Modelling Exercises

Exercise 1.

On the Datablog of the website of the Guardian, you can find an article about the top 100 bestselling books of all time. Develop an Entity-Relationship Diagram for a relational database which can capture the data this article refers to effectively.

Exercise 2

The website of the British Library includes pages which showcase some of the BL's most important and valuable treasures. The information that is shown on this webpage has been drawn from a database. Try to reconstruct the database underlying this website. Capture the results of your analysis in the form of an Entity-Relationship diagram.

Exercise 3

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.


 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 4

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.

Record your database design as follows:

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.

  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)