Database Management Systems (DBMS) — practice questions

Using a DBMS is far more suitable for an online shop. Here are three key reasons:

1. Concurrency Control: Multiple customers may try to buy the last item in stock simultaneously. A CSV file system cannot manage this; it could lead to overselling the item. A DBMS uses concurrency control (e.g., record locking) to ensure that only one transaction can complete the purchase of the last item, preventing data inconsistency and customer dissatisfaction. [1 mark]
2. Data Integrity: The DBMS can enforce rules to ensure data quality. For example, it can ensure that every OrderID in the OrderItems table corresponds to a valid OrderID in the Orders table (referential integrity). It can also validate data types, ensuring a price is always a number. A simple CSV file offers no such built-in validation, risking data corruption. [1 mark]
3. Security and Access Control: The online shop's database will contain sensitive customer information. A DBMS provides robust security features through its DCL, allowing the creation of different user roles. For instance, a customer service agent might only have read-only access to order history, while an administrator has full rights. This level of granular security is not possible with a simple file system, which would be a major security risk. [1 mark]

This is a classic concurrency problem that a DBMS is designed to solve.

1. Initial State: The database shows SeatsAvailable for flight BA2491 is 1. Both Agent A and Agent B's applications read this value. [1 mark]
2. Transaction Start & Locking: Agent A initiates the booking. The DBMS starts a transaction and places a lock (e.g., an exclusive write lock) on the record for flight BA2491. This prevents any other transaction from modifying this specific record until Agent A's transaction is complete. [1 mark]
3. Second Attempt Blocked: When Agent B attempts to start their booking transaction for the same seat, the DBMS sees the lock. Agent B's transaction is made to wait; their screen might show a 'processing' message. It cannot read or write to the locked record. [1 mark]
4. Transaction Commit & Release: Agent A's transaction successfully completes. The SeatsAvailable count is updated to 0. The transaction is 'committed', making the change permanent. The lock on the record is then released. [1 mark]
5. Second Transaction Resumes: The DBMS now allows Agent B's waiting transaction to proceed. It reads the SeatsAvailable record, which is now 0. The application can then inform Agent B that the flight is full, preventing the seat from being double-booked and maintaining data integrity. [1 mark]