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 21 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.
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
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.
Same as Exercise 3.
Registration of loans from the National Library, The Hague:
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 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.
- All the columns in the table CITY, and sort the rows alphabetically, by name of the city.
- The treasures that were produced after 1800. Display only the title and the year
- The creators whose first name starts with the letter “W”
- The distinct subject codes in the table TREASURE, and sort the result alphabetically.
- The year of the oldest treasure in the database.
- The number of countries in the table CREATOR.
- The most recent year of death in the table CREATOR.
- 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.
- The number of persons for each country in the table CREATOR.
- Those libraries that are represented by two or more treasures.
- Those subjects that have been assigned to two or more treasures in the database.
- The title, year, and provide a full description of the subject code of all the treasures in the database.
- The names of all the libraries and the names of the cities in which they are located.
- A list with all names of all cities and the full names of the countries in which they are situated.
- 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.
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.
- Which authors have written more than one beststeller?
- Which publishers are responsible for more than three bestsellers?
- What are the best-selling books in the genre “Popular Science”?
- What is the average number of sales of a book written by J.K. Rowling?
You can run these queries in MYSQL.
- For each genre, show the average number of sales
- For each genre, show the maximum number of books that have been sold
- For each author, show the total number of sales
- For each year, show the total number of bestsellers
You can run these queries in MYSQL.
- The titles of all the books in alphabetical order.
- The titles and years of release of all the movies in the database.
- The different country codes that have been used in the table PERSON.
- The full names (last and first name) of all persons born after 1900.
- The number of people born after 1875.
- The number of years in between the oldest and the most recent film.
- The number of books each author has written. For the authors, give only the ID.
- The IDs of the the authors who have written two or more books.
- The title and the full names of the authors of all the books in the database.
- The full name of all the persons in the database and the name of the country in which they were born.
- The titles of the books whose author is born in the United States.
- The titles of all the movies and the full names of their directors.
- The titles of all the books that have been written before 1950.
- 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.
- 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.
- 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”.
- 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.
- For all the books in the database, their title, year of publication and the name and the city of the publisher.
- The different nationalities of the persons in the database. Provide only the names of the countries. Each nationality must be shown only once.
- 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.
- For each city, provide the name of that city and the number of companies that are based in that city.
- 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.
Below, you can find the ERD for the BTCP database:
- All books published by Bohn, in alphabetical order.
- 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?
- All letters which have NOT been written in the Netherlands.
- 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?
- A list of all the series in which Bohn has published books, together with the number of books in that series.
- The IDs of the authors who have written two or more books.
- The different languages of the letters. Also indicate how many letters there are in each language.
You can run these queries in MYSQL.
As you can see in the ERD, the table CITY in the database for the BTCP (see exercise 9) 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”
- Display the locations of all the letters that were received
- Display the locations of the letters that were received in the second half of the 19th century
- Display the locations of the letters that were received from the Western hemisphere (e.g. Unites States and UK West to Greenwich).
For this question, make use of the Elsevier database. The CREATE STATEMENTS of this database can be found here.
Explain how you would go about answering the following research question:
Which subjects did the Elseviers focus on? Can you see a historical development?
Formulate the type of information you would look for, then create queries with which you could obtain that information. Also explain what the result of your query looks like. Usually, you need a succession of queries for different approaches to the research question.
This question mainly test your ability to translate open research questions to concrete SQL queries. The focus, in other words, is primarily on the method that you use to address these questions. In this question, the exact results which are returned by the database are of less importance.