AMCAT - Aspiring Minds Computer Adaptive Computer Science: DBMS Concepts Questions and Answers 1 — Questions and Answers
Question 1: A database administrator notices that updating a customer's address requires changes in multiple tables, leading to inconsistencies when one of the updates fails. This is an example of what kind of anomaly?
- Insertion Anomaly
- Deletion Anomaly
- Update Anomaly (Correct answer)
- Selection Anomaly
Correct answer: Update Anomaly
An Update Anomaly occurs when a single piece of data is stored in multiple places. If that data is updated, it must be changed in all locations. If one or more of these updates fail, it leads to data inconsistency, which is the exact scenario described.
Question 2: Which of the following SQL JOIN clauses is most appropriate for retrieving all records from the 'Customers' table and only the matching records from the 'Orders' table?
- INNER JOIN
- FULL OUTER JOIN
- RIGHT JOIN
- LEFT JOIN (Correct answer)
Correct answer: LEFT JOIN
A LEFT JOIN (or LEFT OUTER JOIN) returns all records from the left table (in this case, 'Customers') and the matched records from the right table ('Orders'). If there is no match, the result is NULL on the side of the right table. This fits the requirement perfectly.
Question 3: In the context of the Three-Schema Architecture of a DBMS, which level describes the physical storage structure of the database?
- External Schema
- Conceptual Schema
- Internal Schema (Correct answer)
- Logical Schema
Correct answer: Internal Schema
The Three-Schema Architecture consists of the External, Conceptual, and Internal levels. The Internal Schema, also known as the Physical Level, is responsible for describing how the data is actually stored on physical storage devices.
Question 4: Which ACID property ensures that a transaction is either fully completed, or not at all, preventing partial updates to the database?
- Durability
- Atomicity (Correct answer)
- Isolation
- Consistency
Correct answer: Atomicity
Atomicity guarantees that all operations within a transaction are treated as a single, indivisible unit. The transaction must be fully completed ('all') or fully rolled back ('nothing'). This prevents the database from being left in a state with partial updates.
Question 5: A table is in First Normal Form (1NF). To achieve Second Normal Form (2NF), what additional condition must be met?
- All attributes must be dependent on the primary key.
- It must not have any transitive dependencies.
- It must not have any partial dependencies. (Correct answer)
- All columns must contain atomic values.
Correct answer: It must not have any partial dependencies.
A table is in 2NF if it is already in 1NF and every non-prime attribute is fully functionally dependent on the primary key. This means there should be no partial dependencies, where a non-key attribute depends only on a part of a composite primary key.
Question 6: Which data structure is most commonly used for database indexing because it stores all data pointers only in leaf nodes, which are linked for efficient sequential access and range queries?
- B-Tree
- B+ Tree (Correct answer)
- AVL Tree
- Red-Black Tree
Correct answer: B+ Tree
A B+ Tree is a variant of the B-Tree where all data records or pointers are stored only at the leaf nodes. Additionally, these leaf nodes are linked together, which makes sequential access and range queries highly efficient. This structure is preferred for most database indexing systems.
AMCAT - Aspiring Minds Computer Adaptive Computer Science: DBMS Concepts Questions and Answers 1
A database administrator notices that updating a customer's address requires changes in multiple tables, leading to inconsistencies when one of the updates fails.
This is an example of what kind of anomaly?