IB DP Computer Science Option A: Databases -: A.1 – Basic concepts SL Paper 2

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

The Driving Licensing Agency stores information about individuals who hold a driving license and/or own vehicles.
The following rules apply:

Each individual may only hold one driving license.
Each individual may own more than one vehicle.
Each vehicle may be owned by one individual only.
When an individual applies for a driving license, they have to complete a license application form. The following is an extract from that form:

The data in the form shown above is stored in the Person table. The license application form also requires an individual’s medical information. This is stored in a table called PersonMedical.

The following extract is a sample of the medical questions that are asked.

                                        Figure 2: A sample of the medical questions asked on the license application form

                                                   

a.Construct the entity-relationship diagram (ERD) that shows the relationship between the individual, their driving license, and their vehicle(s).[2]

b.Explain why Date of birth has been separated into three fields.[3]

c.Explain two reasons why medical information should not be stored in the Person table.[6]

d.Outline two issues caused by storing redundant data.[4]

e.Outline two situations where data stored by the Driving Licensing Agency may need to be open to interrogation by other parties. [4]

▶️Answer/Explanation

Ans:

a.)

Accept either diagram. License, Individual, and Vehicle correctly positioned
Award [1] for 1 to 1.
Award [1] for 1 to m.

b.)

The order that a date is written varies by country, e.g. dd/mm/yyyy in UK, mm/dd/yyyy in US;
Some dates, such as 01 June 2018 (01/06/2018) could be confused with 06 January 2018 (06/01/2018);
leading to the incorrect information being stored and consequent decisions, e.g. license expired at the wrong time due to incorrect age;

The Date/String datatype will make analysing slower;
integers can be analysed more quickly than Date/String datatypes;
because functions will be used to split/extract the data;

The Date datatype will make validating data more difficult.
because it is easier to validate separate integer fields;
rather than one entire data that will need to be split by functions;

c.)

Normalisation
Medical information is only stored if the person has a medical condition / some people will have no medical information stored / some people may have more than one medical conditions;
this requires an additional table / normalisation requires a separate table / one to many relationship;
leaving in the person table will increase storage capacity / empty fields take up space;

Privacy
All employees will need access to the person table and that would include sensitive medical information;
Protects people’s privacy / data needs to comply with the Data Protection Act / private medical data seen by non-authorised personnel may cause harm to the licence applicant / potentially result in legal action;
having confidential data in a separate table allows that table to be only available to people with a certain permission level;

Updates
Person table is also likely to be less permanent and need updating more often;
medical information rarely gets updated so is more permanent;
the person table may be useful in other applications, as it offers a way of identifying citizens/the medical data is not likely to be used by other applications;

d.)
May lead to update/deletion anomalies;
Address changes may result in duplicate addresses;
so letters / fines may go to the wrong address;
Storing data multiple times wastes storage space;
and may slow down data retrieval / data entry;

e.)
Accept any suitable example

Police access
It is a legal requirement for the VDLA to give access to the police;
e.g. a speeding vehicle / an accident / linked to a police investigation;
so the police will need to look up the details of the person that owns the car;
facial scanning software may be cross-referenced with the car owner’s driving license photo;
so that police have a way to check the identity of the person driving the car;

Insurance company access
A person taking out car insurance signs a consent form to give VDLA access to their records;
this would allow the insurance company to build up a profile e.g., check for driving offences, see how long that person had owned a car;
thus, a quote could be created quickly / minimal effort;
provides proof of driver eligibility (i.e., not serving a driving ban);

Medical access
Doctors may require access to an accident victim’s records;
To check blood type etc.;

Scroll to Top