| Date | May 2019 | Marks available | 1 | Reference code | 19M.2.SL.TZ0.1 |
| Level | SL | Paper | 2 | Time zone | no time zone |
| Command term | State | Question number | 1 | Adapted from | N/A |
Question
Marble Reading Book Stores (MRBS) is a chain of bookstores based in London. The stores want to keep information about the books they sell, the authors of the books and the publishers they work with. The assumptions made when the database was created were:
- a publisher can publish books from one or more authors
- an author can write one or more books.
Three of the tables in the MRBS database are shown below:
PUBLISHER
AUTHOR
BOOK
The MRBS database undergoes many transactions.
Construct the entity-relationship diagram (ERD) for this scenario.
Outline why data validation is difficult for the Book_Title attribute.
State the result from the following query:
SELECT Book_Title
FROM BOOK
WHERE Genre = "Non-fiction"
AND ISBN = '0-98124-612-2'
Construct a query to find the titles of the books published by “Orlando Crux”.
Outline why atomicity is important within a database.
Outline how data consistency can be maintained in transactions in this database system.
Some data in the MRBS database is redundant.
Outline one problem caused by redundant data.
Markscheme
Award [2 max].
Award [1] for correct relationship “publishes books from”.
Award [1] for correct relationship “writes”.
Award [2 max].
As Book_Title attribute is String/Varchar;
It does not allow many validation checks (like range check etc.);
Award [1 max].
Seeking the truth;
Award [4 max].
Award [1] for selection of both Publisher_Name and Book_Title from the two respective tables.
Award [1] for making INNER JOIN of the Publisher_Name from both tables-Publisher and Author.
Award [1] for the correct test ON Author_Num from both tables-Author and Book.
Award [1] for the correct test of equity of Publisher_Name.
SELECT Publisher.Publisher_Name, Book.Book_Title FROM Author
INNER JOIN Book ON PUBLISHER. Publisher_Name =
Author.Publisher_Name, ON Author.Author_Num = Book.Author_Num
WHERE Publisher.Publisher_Name = "Orlando Crux";
Award [2 max].
Helps the searching of a family name for example rather than a “like” name search;
So, making data into the smallest possible unit helps quicker searches;
Award [2 max].
Each user sees a fixed view of the data;
This may include visible changes made by the user / the user's own transactions and transactions of other users;
Award [2 max].
When a field value changes multiple occurrences must be updated;
For example, if a publisher moves, we’ll need to change the values for City and Country in multiple records;
Problem occurs if we forget to change the values in any of the records;
The database would then have data inconsistency;
Examiners report
Most students knew how to sketch an ER diagram though some of them don’t seem to know the various relationships in it and how to apply. Some of them can draw the entities but setting the relationship was not done correctly.
Most of the students understood the validations in String attributes and its challenges.
The query was well understood.
Most students were able to get the basic structure of the query well though some of them could not set the two tables and their relationship properly while writing the query. Also many students searched for the publisher or author code not the publisher name.
Most students were able to explain atomicity though only very few students were able to refer to rollback as well.
‘Data consistency’ was understood though they could not relate to the given scenario of the bookstore database.
Most students gave generic answers and most of them incorrectly referred to the space redundancy that is not appropriate to this question clearly.
