assessment title: Database systems semester 2 coursework
module title: Database systems
Module CRN: 32741
1)
A–
(i) Size of each tuple = 24 + 15 + 10 + 20 + 2 = 71 bytes
Tuples that fit in a each block = (1024 – 24)/71 = 14.8 (14)
750/14 = 53.57 (54)
Answer = 54 blocks would be required to store the whole relation
(ii) Size of tuple = 24 + 15 + 10 + 2 = 51 bytes
Tuples that fit in each block = (1024 – 24) / 51 = 19.25(19)
750/19 = 39.47(39)
(iii) A projection operation can improve a database query by creating a new relation that
shares some of the attributes of the original relation. By selecting just the relevant
attributes, a projection can reduce the amount of data that must be read or delivered,
the size of the relation, and the number of I/O operations. When a query does not
require a certain property, leaving it out of the projection may increase query speed,
especially for large relations where the attribute takes up a lot of store space.
2)
A – The ACID properties should be employed to ensure the consistency and integrity of the
database in stock exchange transactions. If the transaction fails, all prior database changes
must be reversed. Transactions are in charge of maintaining the database's consistency by
shifting it from one consistent state to another. Concurrent transactions must be segregated
in order to avoid interfering with one another. Finally, all database changes must be
documented, and system outages or crashes must be allowed. These attributes are crucial to
the integrity and legitimacy of the stock exchange database.
B – A database management system (DBMS) cannot ensure the consistency of the stock
market's database. Extrinsic factors such as network failures, system breakdowns, and
human error can all have an influence on data consistency, despite the need for a database
management system (DBMS). As a result, additional mechanisms like as backup and
recovery procedures, redundancy, and disaster recovery plans are required to offset these
impacts and ensure that the database can be restored to a consistent state even when
external causes are present.
C – Using a database management system (DBMS) in the stock market provides benefits
such as data consistency, integrity, scalability, security, and recovery. A DBMS guarantees
that concurrent transactions do not conflict with one another and employs data integrity
rules to assure the database's consistency and dependability. It is crucial for the stock
market since it can manage enormous volumes of data and rapid transaction rates. A
database management system (DBMS) includes security features such as user authentication
and access control in addition to backup and recovery options. Overall, a database
management system (DBMS) provides a dependable and efficient foundation for stock
market data administration.
D–
(i) The two transactions collide when they read and write the same data items in the
table. Transaction 1 reads, updates, and writes the quantity of trader B1 back to the
table. In transaction 2, Trader B2 reads the same amount before updating the
database with the changed value. Transaction 2 reads an inaccurate quantity value
and adds it to the total as a consequence. As a result, there is a data consistency
violation, and the total amount computed in transaction 2 is incorrect. When two
transactions are not separated from one another and access and update the same
data objects at the same time, a race condition occurs.
(ii)
3)
A – When all users are just checking to see whether seats are available, concurrent
access to the train company's seat reservation system database is simple. This is
because users only perform read operations on the database, which do not change
the data and hence do not cause conflicts between concurrent transactions. As a
consequence, there is no competition for database resources, and several users can
access the database concurrently to perform read operations. Indexing and caching
technologies can further improve system performance by reducing response time
for read workloads.
B – To assure conflict serializable schedules, the railway sector may use a two-phase
locking system in which transactions acquire locks on database resources before
using them. Growing (when transactions may acquire but not release locks) and
shrinking (when transactions can release but not acquire new locks) are the two
stages of the protocol. By prohibiting concurrent transactions from competing for
the same resource, this protocol provides conflict serializability. Other concurrency
control strategies, such as timestamp ordering or optimistic concurrency control,
may also be used by the organisation.
D – (i) The recovery manager would take the following steps to recover from the
failure. It would examine the transaction log first to determine which transactions
were active at the time of the failure. In this situation, T1 was in the works. The
older record image would then be used by the recovery manager to undo any
modifications done by T1 since the last checkpoint at 20:05, which is S44 updated by
T1. As a result, seat S44 will be given the pre-image value 'Adams' rather than the
updated value 'Ali'.
Furthermore, the recovery manager would reverse all changes made by committed
transactions since the most recent checkpoint, which in this example is T2 and T3. T2
committed at 19:57 and created a new record for seat S44, requiring the recovery
manager to restart the procedure in order to enter the record for S44 with the value
'Adams'. Similarly, because T3 committed at 20:01 and removed seat S89, the
recovery manager would do the same. The system would be consistent once all
procedures were repeated and undone.
(iii) If the recovery attempt failed, the recovery manager would use the recovery log to
restore the database to a consistent state. It would start by looking for the most
recent checkpoint and the transactions that were active at the moment of failure in
the log. It would then reverse the T1 and T4 updates in reverse order, using past
pictures of the relevant processes, to undo the incomplete transactions. After then,
the completed transactions would be reproduced by reversing the T2 and T3
modifications and applying their after images in the sequence in which they were
committed.
