MCAE23 Advanced Database Management Systems MCA Question Bank : bbdnitm.ac.in
Name of the Institute : Babu Banarasi Das National Institute of Technology & Management
Degree : MCA
Department : Computer Applications
Subject Code/Name : (MCA-E23) Advanced Database Management Systems
Year : 3rd
Semester : 5th
Document Type : Question Bank
Website : bbdnitm.ac.in
Download Model/Sample Question Paper :https://www.pdfquestion.in/uploads/bbdnitm.ac.in/3161-MCA-E23-AdvancedDatabaseManagementSystem.pdf
BBDNITM Database Management Systems Question Paper
Unit-I
1. List the ACID properties. Explain the usefulness of each.
2. Suppose that there is a database system that never fails. Is a recovery manager required for this system?
3. Consider a file system such as the one on your favorite operating system.
Related : Babu Banarasi Das National Institute of Technology & Management BCA504 Design Analysis & Algorithms BCA Question Bank : www.pdfquestion.in/3173.html
a. What are the steps involved in creation and deletion of files, and in writing data to a file?
b. Explain how the issues of atomicity and durability are relevant to the creation and deletion of files, and to writing data to files.
4. Database-system implementers have paid much more attention to the ACID properties than have file-system implementers. Why might this be the case?
5. During its execution, a transaction passes through several states, until it finally commits or aborts. List all possible sequences of states through which a transaction may pass. Explain why each state transition may occur.
6. Justify the following statement : Concurrent execution of transactions is more important when data must be fetched from (slow) disk or when transactions are long, and is less important when data is in memory and transactions are very short.
7. Explain the distinction between the terms serial schedule and serializable schedule.
8. Since every conflict-serializable schedule is view serializable, why do we emphasize conflict serializability rather than view serializability?
9. What is a cascadeless schedule? Why is cascadelessness of schedules desirable? Are there any circumstances under which it would be desirable to allow noncascadeless schedules? Explain your answer.
10. In multiple-granularity locking, what is the difference between implicit and explicit locking?
Unit-II
1. Show that the two-phase locking protocol ensures conflict serializability, and that transactions can be serialized according to their lock points.
3. What benefit does strict two-phase locking provide? What disadvantages result?
4. What benefit does rigorous two-phase locking provide? How does it compare with other forms of two-phase locking?
5. Most implementations of database systems use strict two-phase locking. Suggest three reasons for the popularity of this protocol.
6. Consider a database organized in the form of a rooted tree. Suppose that we insert a dummy vertex between each pair of vertices. Show that, if we follow the tree protocol on the new tree, we get better concurrency than if we follow the tree protocol on the original tree.
7. Show by example that there are schedules possible under the tree protocol that are not possible under the two-phase locking protocol, and vice versa.
8. Consider the following extension to the tree-locking protocol, which allows both shared and exclusive locks :
** A transaction can be either a read-only transaction, in which case it can request only shared locks, or an update transaction, in which case it can request only exclusive locks.
** Each transaction must follow the rules of the tree protocol. Read-only transactions may lock any data item first, whereas update transactions must lock the root first.
Show that the protocol ensures serializability and deadlock freedom.
9. Consider the following graph-based locking protocol, which allows only exclusive lock modes, and which operates on data graphs that are in the form of a rooted directed acyclic graph.
? A transaction can lock any vertex first.
? To lock any other vertex, the transaction must be holding a lock on the majority of the parents of that vertex. Show that the protocol ensures serializability and deadlock freedom.
Unit-III
1. Discuss the relative advantages of centralized and distributed databases.
2. Explain how the following differ : fragmentation transparency, replication transparency, and location transparency.
3. How might a distributed database designed for a local-area network differ from one designed for a wide-area network?
4. When is it useful to have replication or fragmentation of data? Explain your answer.
5. Explain the notions of transparency and autonomy. Why are these notions desirable from a human-factors standpoint?
6. To build a highly available distributed system, you must know what kinds of failures can occur.
a. List possible types of failure in a distributed system.
b. Which items in your list from part a are also applicable to a centralized system?
7. Consider a failure that occurs during 2PC for a transaction. For each possible failure that you listed in Exercise 19.6a, explain how 2PC ensures transaction atomicity despite the failure.
8. Consider a distributed system with two sites, A and B. Can site A distinguish among the following?
** B goes down.
** The link between A and B goes down.
** B is extremely overloaded and response time is 100 times longer than normal. What implications does your answer have for recovery in distributed systems?
9. The persistent messaging scheme described in this chapter depends on timestamps combined with discarding of received messages if they are too old. Suggest an alternative scheme based on sequence numbers instead of timestamps.
10. Give an example where the read one, write all available approach leads to an erroneous state.
11. Under what conditions is it less expensive to avoid deadlock than to allow deadlocks to occur and then to detect them?
12. If deadlock is avoided by deadlock avoidance schemes, is starvation still possible? Explain your answer.
Unit-IV
1. If we apply a distributed version of the multiple-granularity protocol to a distributed database, the site responsible for the root of the DAG may become a bottleneck. Suppose we modify that protocol as follows :
** Only intention-mode locks are allowed on the root.
** All transactions are given all possible intention-mode locks on the root automatically.
2. Explain the difference between data replication in a distributed system and the maintenance of a remote backup site.
3. Give an example where lazy replication can lead to an inconsistent database state even when updates get an exclusive lock on the primary (master) copy.
4. Study and summarize the facilities that the database system you are using provides for dealing with inconsistent states that can be reached with lazy propagation of updates.
5. Discuss the advantages and disadvantages of the two methods for generating globally unique timestamps.
6. Consider a relation that is fragmented horizontally by plant-number : employee (name, address, salary, plant-number) Assume that each fragment has two replicas: one stored at the New York site and one stored locally at the plant site. Describe a good processing strategy for the following queries entered at the San Jose site.
a. Find all employees at the Boca plant.
b. Find the average salary of all employees.
c. Find the highest-paid employee at each of the following sites : Toronto, Edmonton, Vancouver,Montreal.
d. Find the lowest-paid employee in the company.
7. Consider the relations
employee (name, address, salary, plant-number)
machine (machine-number, type, plant-number)
Assume that the employee relation is fragmented horizontally by plant-number, and that each fragment is stored locally at its corresponding plant site. Assume that the machine relation is stored in its entirety at the Armonk site. Describe a good strategy for processing each of the following queries.
a. Find all employees at the plant that contains machine number 1130.
b. Find all employees at plants that contain machines whose type is “milling machine.”
c. Find all machines at the Almaden plant.
d. Find employee 8 machine.
8. For each of the strategies of Exercise 19.18, state how your choice of a strategy depends on :
a. The site at which the query was entered
b. The site at which the result is desired
9. Is ri SJ rj necessarily equal to rj SJ ri? Under what conditions does ri SJ rj = rj SJ ri hold?
10. Given that the LDAP functionality can be implemented on top of a database system, what is the need for the LDAP standard?
11. Describe how LDAP can be used to provide multiple hierarchical views of data, without replicating the base level data.
Unit-V
1. Explain the difference between the three storage types—volatile, nonvolatile, and stable—in terms of I/O cost.
2. Stable storage cannot be implemented.
a. Explain why it cannot be.
b. Explain how database systems deal with this problem.
3. Compare the deferred- and immediate-modification versions of the log-based recovery scheme in terms of ease of implementation and overhead cost.
4. Assume that immediate modification is used in a system. Show, by an example, how an inconsistent database state could result if log records for a transaction are not output to stable storage prior to data updated by the transaction being written to disk.
5. Explain the purpose of the checkpoint mechanism. How often should checkpoints be performed? How does the frequency of checkpoints affect
** System performance when no failure occurs
** The time it takes to recover from a system crash
** The time it takes to recover from a disk crash
6. When the system recovers from a crash, it constructs an undo-list and a redo-list. Explain why log records for transactions on the undolist must be processed in reverse order, while those log records for transactions on the redo-list are processed in a forward direction.
7. Compare the shadow-paging recovery scheme with the log-based recovery schemes in terms of ease of implementation and overhead cost.
8. Consider a database consisting of 10 consecutive disk blocks (block 1, block 2, . . ., block 10). Show the buffer state and a possible physical ordering of the blocks after the following updates, assuming that shadow paging is used, that the buffer in main memory can hold only three blocks, and that a least recently used (LRU) strategy is used for buffer management.
read block 3
read block 7
read block 5
read block 3
read block 1
modify block 1
read block 10
modify block 5