Digital Media Technology 2014-2015


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.



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.

  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?

The questions can be answered by making use of the field below.




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




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.



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.