| Date | November 2019 | Marks available | 4 | Reference code | 19N.2.SL.TZ0.1 |
| Level | SL | Paper | 2 | Time zone | no time zone |
| Command term | Explain | Question number | 1 | Adapted from | N/A |
Question
One-to-7 is an international organization that works with teachers and other educators. One department within this organization provides an online forum for teachers to discuss ideas for lessons and to share resources.
In order to access this forum teachers are required to submit the following information, which will be stored in a table in the database.
Figure 1: Online form to register personal details to the One-to-7 forum
Once the submit button on the online form has been selected, the personal data is input into the database.
Once the teacher is registered they can post comments on the forum.
The One-to-7 database in managed by the database administrator (DBA).
Identify one reason why the teacher’s name has been split into two fields.
Outline one reason why there may be concerns about the amount of personal information that is requested.
Outline why the transaction needs to be atomic in the context of this scenario.
Explain how transactions are managed to ensure isolation when registered teachers add comments to a discussion thread on the forum.
Identify two tasks that are carried out by the database administrator (DBA).
The DBA is considering using the email address as the primary key, but is concerned that many of the 250 000 educators who are registering for this online forum may have more than one email address. Users may create duplicate accounts, deliberately or accidently, by using different email addresses as usernames.
Explain the factors that would need to be considered in using a composite primary key instead of only using the email address.
Markscheme
Award [1 max].
atomicity;
sorting;
Award [2 max].
Not all of this information may be necessary for the purposes it is being collected for / asks for too much information;
Teachers may be concerned about issues of privacy;
If the information is shared with third parties it could be used / aggregated to identify the teacher / identity theft;
Teachers may be put off either by the excessive time required to complete the form;
This may lead to some teachers refusing to complete the application form / not as many teachers will sign up;
Award [2 max].
Atomicity in transactions ensure that the indivisible series of database operations either all occur, or nothing occurs;
This prevents updates to the database occurring only partially / this maintains data integrity / consistency;
Award [3 max].
Isolation specifies the sequence that changes is processed / specifies that any parallel processing must produce the same result as if the processes were carried out sequentially;
Each post to the thread takes place independently of others;
One post will be completed before another starts / a post will not become visible until completed;
Data and the transaction (row for the thread) is locked for that moment when the transaction is carried out;
The addition of one post to the thread must not displace another’s, regardless of the order in which they finally appear;
A transaction log is created prior to the transaction to allow rollback;
This means that should an error occur part of the way through the transaction it will be rolled back and the database will return to its original state;
Award [2 max].
Data configuring / applying patches or upgrades;
Setting permissions / passwords / access rights / ensuring security;
Back up / recovery / archiving;
Data cleansing / consistency checks on data / remove data errors;
Award [4 max].
The large number of educators is critical to the number of fields included in any composite key;
The composite key must be unique;
Composite key could be made up of several fields;
Allow suitable example (e.g. combinations of first name, last name, email, phone number etc.);
Allow example that would not be suitable;
Part of the primary key could include a random element in case of duplicated name and dates of birth;
This would require an understanding of the nature of the educators, for example, are they of a particular age which would reduce the possible number of dates of birth, are they from one particular country so certain names would be more likely to occur;
This would influence the number of fields that would be required to reduce the possibility of a duplicate entry occurring;
Examiners report
This was poorly answered. Few candidates connected the question and scenario to atomicity or sorting purposes, while the majority claimed the reason for splitting the name into two fields was to avoid any confusion.
Generally, well done, but many partial marks were achieved on this question due to incomplete answers.
Was not well answered; Candidates were not able to outline the need for atomicity in transactions and many tended to confuse the question with networking environments, and failures in data transmission.
Was not well answered with many candidates giving generic answers for isolation in the scenario, but not knowing what happens when a transaction takes place with respect to this property.
This was poorly answered. Responses were either vague or gave incorrect tasks of a DBA.
Many candidates were unable to clearly explain this question as they did not show understanding of the concept of a composite primary key. Some candidates, however, used correct examples to demonstrate its use and implications in the proposed scenario.
