DBMS Exam Preprations sheet

1 Question: What is a database and how is it different from a DBMS?

• Answer: A database is a structured collection of data, while a DBMS (Database Management System) is software that manages and facilitates the storage, retrieval, and manipulation of data in a database. The DBMS provides an interface for users and applications to interact with the database.

2. Question: Explain the ACID properties in the context of database transactions.

• Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability.

• Atomicity: Ensures that a transaction is treated as a single, indivisible unit.

• Consistency: Guarantees that the database remains in a consistent state before and after a transaction.

• Isolation: Ensures that transactions are isolated from each other until they are committed.

• Durability: Once a transaction is committed, its changes are permanent.

3. Question: What is the purpose of normalization in a relational database?

• Answer: Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. The main purposes are:

• Minimizing data redundancy.

• Reducing update anomalies.

• Improving data consistency.

4. Question: Differentiate between primary key and foreign key.

• Answer:

• Primary Key: It uniquely identifies each record in a table and ensures data integrity by enforcing uniqueness.

• Foreign Key: It establishes a link between two tables by referencing the primary key of another table. It enforces referential integrity.

5. Question: Explain the concept of a join in the context of database queries.

• Answer: A join combines rows from two or more tables based on related columns. Types of joins include INNER JOIN (returns only matching rows), LEFT OUTER JOIN (returns all rows from the left table and the matched rows from the right table), and RIGHT OUTER JOIN (vice versa).

6. Question: What is a trigger in a database, and how does it differ from a stored procedure?

• Answer:

• Trigger: It is a set of instructions that automatically executes in response to certain events (like INSERT, UPDATE, DELETE) on a specific table.

• Stored Procedure: It is a precompiled collection of one or more SQL statements that can be executed with a single call. Triggers are event-driven and execute automatically, while stored procedures are called explicitly.

7. Question: Describe the concept of normalization forms. Provide examples of at least two normalization forms.

• Answer: Normalization forms are stages of organizing data in a database. Examples include:

• 1NF (First Normal Form): Ensures that each column contains atomic values, and there are no repeating groups.

• 2NF (Second Normal Form): In addition to 1NF, all non-key attributes are fully functionally dependent on the primary key.

8. Question: How does a distributed database differ from a centralized database?

• Answer: A distributed database is spread across multiple locations or computers connected by a network, offering advantages such as improved performance and fault tolerance. In contrast, a centralized database is located in a single location.

9. Question: Explain the concept of generalization and aggregation in the context of database design.

• Answer:

• Generalization: It involves forming a general class from a group of more specialized classes in a database schema.

• Aggregation: It represents a relationship where the child can exist independently of the parent.

10. Question: What is a subquery, and how is it used in a database query?

• Answer: A subquery is a query nested within another query. It can be used to retrieve data that will be used as a condition in the main query, allowing for more complex and dynamic queries.

• Next Preprations Topics

1. Join (Outer Join):

• Join: In database management, a join combines rows from two or more tables based on a related column between them.

• Outer Join: Retrieves records when there is a match in one of the tables and includes unmatched rows from one or both tables.

2. Subquery:

• A subquery is a query nested within another query. It can be used to retrieve data that will be used in the main query as a condition.

3. ER Diagram (Entity-Relationship Diagram):

• ER diagrams are graphical representations of the logical structure of a database. They depict entities, attributes, relationships, and cardinality.

4. Timestamp Protocol:

• A timestamp protocol is used in concurrency control to order and manage transactions based on timestamps assigned to each transaction.

5. Conflict and View Serializable:

• Conflict Serializable: A schedule is conflict serializable if it is equivalent to some serial schedule that could be obtained by interleaving the operations of transactions.

• View Serializable: A schedule is view serializable if it is equivalent to some serial schedule that could be obtained by rearranging the non-conflicting operations of transactions.

6. ACID Properties:

• ACID: Stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure the reliability of database transactions.

• Atomicity: Transactions are treated as a single, indivisible unit.

• Consistency: Database should remain in a consistent state before and after the transaction.

• Isolation: Transactions are isolated from each other until they are committed.

• Durability: Once a transaction is committed, its changes are permanent.

7. Two-Phase Locking Protocol:

• A concurrency control method that ensures that transactions follow a strict protocol of acquiring and releasing locks in two phases: growing phase and shrinking phase.

8. Cursor:

• A database cursor is a pointer used to iterate over a result set from a database query. It allows the programmer to traverse and manipulate the data.

9. Trigger:

• A trigger is a set of instructions that are automatically executed or "triggered" in response to certain events on a particular table or view in a database.

10. Distributed Database:

• A database that is spread across different locations or multiple computers, connected by a network. It offers advantages like improved performance and fault tolerance.

11. Generalization and Aggregation:

• Generalization: The process of forming a general class from a group of more specialized classes in a database schema.

• Aggregation: A relationship where the child can exist independently of the parent.

12. Normalization and Types:

• Normalization: The process of organizing data in a database to reduce redundancy and improve data integrity.

• Types of Normal Forms: Includes 1NF (First Normal Form), 2NF (Second Normal Form), 3NF (Third Normal Form), BCNF (Boyce-Codd Normal Form), etc.