DBMS Sample Questions and Concepts
Uploaded by
sandipDBMS Sample Questions and Concepts
Uploaded by
sandipCommon questions
Powered by AIAn ER model helps in visualizing the structure of the database by defining entities, their attributes, and relationships between these entities. It makes complex data relationships easier to understand and manage. For example, an ER model for a library system might include entities like Book, Member, and Loan, with relationships such as Members ‘borrow’ Books .
Triggers in SQL are database objects that automatically execute or fire when certain events occur. They are often used for enforcing business rules, auditing changes, and maintaining integrity. Functions in PL-SQL return a single value and can be used in SQL statements, while procedures do not return values but are used to encapsulate a series of operations for modularity and reusability .
Normalization is the process of organizing data to minimize redundancy and improve data integrity. 3NF requires that a table be in Second Normal Form and all its attributes only depend on the primary key. BCNF is a stricter version that also prevents transitive dependencies where every determinant is a candidate key. BCNF can prevent certain types of anomalies that 3NF does not .
Query optimization is the process of choosing the most efficient means of executing a database query, considering various query execution plans. This is essential for improving performance as it can reduce query execution times and resource utilization. Query optimizers typically use statistical information about the data to select indexes, join methods, and data retrieval paths .
The Two-Phase Locking Protocol ensures serializability by dividing the transaction execution phase into a growing phase, where locks are acquired, and a shrinking phase, where they are released. No lock can be acquired after releasing any lock. For example, a transaction must acquire all necessary locks before it can function, and only upon releasing those locks can others access the locked data .
Distributed database systems are characterized by multiple database systems that are connected over a network, allowing resources to be accessed and managed in a coordinated manner. They provide transparency and replication but can have complexities with consistency and availability. Parallel database systems, however, are designed to use multiple processors concurrently to perform tasks more efficiently and are often optimized for high-performance computing on queries within a single database .
Crash recovery in a DBMS is vital for maintaining data consistency and durability after system failures. For example, if a power failure occurs during a transaction, recovery mechanisms such as write-ahead logging will ensure that the effects of the transaction can be either fully restored or rolled back, keeping the database in a consistent state .
Functional dependencies are key to database normalization as they help identify redundant data and establish relationships between data attributes. For instance, if an attribute A functionally determines attribute B (denoted as A -> B), then A can be used to uniquely determine B. This allows decomposing tables to reduce redundancy without losing data integrity, such as splitting a student table into separate tables for student details and course enrollment .
A transaction manager is crucial for coordinating database operations to ensure that they are executed reliably and efficiently. The ACID properties enforce this: Atomicity guarantees that transactions are fully completed or not at all, Consistency ensures transactions bring the database from one valid state to another, Isolation ensures transactions do not interfere with each other, and Durability ensures that once a transaction has been committed, it remains in the system even in the event of a crash .
DBMS offers several advantages over traditional file systems, including improved data security, data integrity, concurrent access, data abstraction and independence, and improved data sharing. Unlike file systems, DBMS provides a systematic and efficient method for storing, retrieving, and managing data. It also enforces data integrity constraints and provides robust backup and recovery options .