Databases and SQL Exercises


Exercise 1

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 2

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 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.

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 4

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
Book
Borrowed
Returned
Thorbecke
Gibbon
20 April 1861
14-06-1861
Dominicus Blankenheym
Carlyle, French Revolution
02 July 1857
24 July 1857
Van Pallant
Adam Bede
1902, 12 November
1903, 23 January
W. Schimmelpenninck
Roman Empire (Gibbon)
04 September 1878
14 December 1878
Van Pallant, W.
Gibbon
20 December 1878
15 March 1879
Van Pallant
George Eliot, Mill on the Floss
20 December 1878
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 5

Download the database treasure.mdb. 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 6

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 7

Download the database booktrade.mdb. The ERD of this database can be found here. Write queries that can returns the following information:

  1. The titles of all the books in alphabetical order.
  2. The titles and years of release of all the movies in the database.
  3. The different country codes that have been used in the table PERSON.
  4. The full names (last and first name) of all persons born after 1900.
  5. The number of people born after 1875.
  6. The number of years in between the oldest and the most recent film.
  7. The number of books each author has written. For the authors, give only the ID.
  8. The IDs of the the authors who have written two or more books.
  9. The title and the full names of the authors of all the books in the database.
  10. The full name of all the persons in the database and the name of the country in which they were born.
  11. The titles of the books whose author is born in the United States.
  12. The titles of all the movies and the full names of their directors.
  13. The titles of all the books that have been written before 1950.
  14. The full name and the year of birth of all persons born in The Netherlands before 1900. In this query, use the name of the country, and not the country code.
  15. For each country in table PERSON, select the number of persons that were born in that country. The result set should display the name of the country, followed by the the number of persons.
  16. The title and full name of the authors of the translations in the table BOOK. Translations can be recognised on the basis of the fact that there is a reference to the original they are based on. They check if a column is not empty, you can use “IS NOT NULL”.
  17. All the companies that Koenraad Fuhri has worked for. Also select the years during which Fuhri worked for these companies and arrange these periods chronologically.
  18. For all the books in the database, their title, year of publication and the name and the city of the publisher.
  19. The different nationalities of the persons in the database. Provide only the names of the countries. Each nationality must be shown only once.
  20. The titles of the books that were written by an author from the United States. Use the name of the country to find these books, and not the country code.
  21. For each city, provide the name of that city and the number of companies that are based in that city.
  22. The number of books that have been published by each company. Select the name of the publisher. Companies which have not published any books can of course be ignored.
You can run these queries in MYSQL.

Exercise 8

Below, you can find the ERD for the BTCP database:

BTCP_ERD.jpg

Write queries that can return the following information, and comment on their results:
  1. All books published by Bohn, in alphabetical order.
  2. The full names (last and first name) of all persons born after 1900, together with the country in which they were born. What do you notice about the nationalities involved? How can you check if this is a coincedental pattern in the data, or a possible error in your query?
  3. All letters which have NOT been written in the Netherlands.
  4. The number of years in between the oldest and the most recent book. Warning: the result might be a bit unexpected. How can you explain this result?
  5. A list of all the series in which Bohn has published books, together with the number of books in that series.
  6. The IDs of the authors who have written two or more books.
  7. The different languages of the letters. Also indicate how many letters there are in each language.

 

You can run these queries in MYSQL.