Question
Billetmania is an online company that sells tickets for theatre performances and music concerts. After a customer has chosen their seats, they can pay for the tickets through a secure online payment system. Once the transaction has been completed, the customer receives an email receipt.
An information system and a database are used for Billetmania’s day-to-day operations.
a. Define the term database transaction.[1]
b. Outline how the Billetmania information system would utilize a database.[2]
c. Explain the importance of transaction durability to Billetmania when clients book tickets.[3]
d. Explain how the Billetmania database management system ensures that a seat is not booked by two people simultaneously.[4]
▶️Answer/Explanation
Ans:
a. )
A logical unit of work that is executed in full or not at all;
A transaction is a single logical operation that comprises of a sequence of database operations;
A transation satisfies the ACID (Atomicity, Consistency, Isolation, Durability) properties;
b. )
The database would store details of concerts, tickets sold, customers;
An information system would access the database to present data it in a way that aids informs managers / aids decision making (e.g. charts of ticket sales);
Accept any other reasonable example.
c. )
Durability ensures that transactions are saved permanently and do not accidentally disappear or get erased;
Billetmania does not need to worry the transaction being lost even in the event of power loss, crashes, or errors;
A seat in a theatre is guaranteed even if the database crashes;
d. )
Concurrency allows multiple users try to book tickets at the same time / complete a transaction at the same time;
Concurrency prevents access by more than one user to the same row/record;
Concurrency uses row locking;
Select seats are locked for a short period of time to ensure that they aren’t double booked/overwritten;
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).
a. Identify one reason why the teacher’s name has been split into two fields.[1]
b. Outline one reason why there may be concerns about the amount of personal information that is requested.
c. Outline why the transaction needs to be atomic in the context of this scenario.[2]
d. Explain how transactions are managed to ensure isolation when registered teachers add comments to a discussion thread on the forum.
e. Identify two tasks that are carried out by the database administrator (DBA).[2]
f. The DBA is considering using the email address as the primary key, but is concerned that many of the 250000 educators who are registering for
$[4]$
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.
▶️Answer/Explanation
Ans:
a.)
atomicity;
sorting;
b.)
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;
c.)
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;
d.)
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;
e.)
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;
f.)
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;