N.B. The answers to these exercises can be found here
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.
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.
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]
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.
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 LyttonExercise 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
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:
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.