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