RDBMS Important Questions and Answers in English

1. What is Object-Oriented Modeling and why is it useful in RDBMS?

Answer:
Object-Oriented Modeling (OOM) is a data modeling technique that represents data in the form of objects. It has features like classes, attributes, methods, encapsulation, inheritance, and aggregation. It is useful in RDBMS when we need to store and manage complex data structures, such as multimedia data, CAD systems, and scientific applications.

2. What are Class and Attributes?

Answer:

• Class: It is a template that defines objects with similar properties and behaviors.
• Attributes: These are used to store data within a class.
• Types of Attributes:
  • Simple Attribute (Single-valued) – e.g., Name, Age
  • Composite Attribute – e.g., Address (Street, City, ZIP)
  • Derived Attribute – e.g., Age (which can be derived from DOB)
  • Multi-valued Attribute – e.g., Phone Numbers

3. What is the difference between Generalization, Specialization, and Inheritance?

Answer:

• Generalization: When two or more entities are combined into a higher-level entity.
• Specialization: When one entity is divided into more specific sub-entities.
• Inheritance: When a child entity acquires the properties of the parent entity.

Example: Vehicle → (Generalization) → Car, Bike
Employee → (Specialization) → Manager, Engineer

4. What are Aggregation and Encapsulation?

Answer:

• Aggregation: It is an entity-relationship model where one entity depends on another entity but is not a part of it.
  Example: Enrollment of a Student in a Course.
• Encapsulation: It is a way to secure data where data cannot be accessed directly, but through methods.

5. What is Distributed Database Design and what are its benefits?

Answer:
Distributed Database Design is a design technique in which data is distributed across multiple servers.
Benefits:

• Fault Tolerance – If one server goes down, another remains available.
• Performance – Accessing data from a nearby server provides faster speed.
• Scalability – The ability to manage large data increases.

6. What is the Architecture of a Distributed Processing System?

Answer:
Distributed Processing Systems can be of three types:

1. Centralized Database with Remote Users
2. Federated Database System
3. Fully Distributed Database System

7. Why are Data Communication Concepts important in RDBMS?

Answer:
In RDBMS, data communication is used for data exchange. It plays an important role in the client-server architecture.

Key techniques:

• TCP/IP Protocol
• Data Encryption
• Load Balancing Techniques

8. What are Concurrency Control Techniques?

Answer:
Concurrency Control is used to keep data consistent when multiple users access data simultaneously.
Techniques:

1. Locking Protocols (Two-Phase Locking)
2. Timestamp Ordering
3. Optimistic Concurrency Control

9. What is Transaction Management and its properties?

Answer:
Transaction Management ensures that all database operations follow the ACID (Atomicity, Consistency, Isolation, Durability) properties.

10. What are the Recovery Techniques?

Answer:

1. Log-Based Recovery
2. Shadow Paging
3. Checkpoints

11. What is Serializability?

Answer:
Serializability ensures that the execution of concurrent transactions should be equivalent to a serial schedule, thereby maintaining data consistency.

12. What is Two-Phase Locking?

Answer:
Two-Phase Locking is a Concurrency Control Mechanism that has two phases:

1. Growing Phase – Locks are acquired.
2. Shrinking Phase – Locks are released.

13. What is Query Optimization and Processing?

Answer:
Query Optimization is a process that makes the execution of an SQL query faster and more efficient.

14. What are Heuristics in Query Optimization?

Answer:
Heuristic Techniques are used to improve the Execution Plan, such as:

• Join Ordering
• Predicate Pushdown

15. What is a Temporal Database?

Answer:
A Temporal Database is a database that keeps a record of data over time, like a bank statement.

16. What is a Multi-Media Database?

Answer:
A Multimedia Database stores video, image, and audio data.

17. What is the difference between Data Warehousing and Data Mining?

Answer:

• Data Warehousing – Storing data on a large scale.
• Data Mining – Extracting patterns from stored data.

18. What are Security and Integrity Constraints?

Answer:
Security Mechanisms:

• Access Control
• Flow Control
• Encryption

Integrity Constraints:

• Primary Key
• Foreign Key
• Check Constraint

19. What is the XML Tree Data Model?

Answer:
The XML Tree Model stores data in a hierarchical style, with Root, Parent, and Child Nodes.

20. What is Cryptography and what are its types?

Answer:
Cryptography is a technique for securing data.
Its types are:

1. Symmetric Key Encryption
2. Asymmetric Key Encryption

21. What are ACID Properties, and what is their importance in RDBMS?

Answer:
ACID Properties ensure that any transaction maintains the stability of the database.

• Atomicity: Either the entire transaction happens, or nothing happens.
  • Example: If ₹500 is being transferred in a bank transaction, either the full amount will be transferred or it won’t; nothing will be left in between.
• Consistency: Data must be in a valid state after the transaction.
  • Example: If ₹500 is debited from Account A, then ₹500 must be credited to Account B.
• Isolation: One transaction cannot affect another transaction.
• Durability: Once a transaction is successful, it will always remain in the database, even if the system crashes.

22. What is Data Redundancy and how can it be reduced?

Answer:
Data Redundancy means unnecessary repetition of data. It is reduced through Normalization.

Example (Before Normalization):

Solution (After Normalization - Splitting into Two Tables):

Employee Table:

Department Table:

23. What is SQL Injection, and what are the ways to prevent it?

Answer:
SQL Injection is a hacking technique in which we can access unauthorized data by manipulating an SQL query.

Example (Vulnerable Code):

$query = "SELECT * FROM users WHERE username = '$user' AND password = '$pass'";

If ' OR '1'='1 is entered as the user, then every user will be logged in!

Ways to prevent it:

1. Use Prepared Statements.
2. Implement Input Validation.
3. Limit User Privileges.

24. What is the difference between a Primary Key and a Foreign Key?

Answer:

• Primary Key: It is a Unique Identifier for a table.
• Foreign Key: It refers to the Primary Key of another table.

Example:

CREATE TABLE Department (
Dept_ID INT PRIMARY KEY,
Dept_Name VARCHAR(50)
);
CREATE TABLE Employee (
Emp_ID INT PRIMARY KEY,
Emp_Name VARCHAR(50),
Dept_ID INT,
FOREIGN KEY (Dept_ID) REFERENCES Department(Dept_ID)
);

This ensures that the Dept_ID of an Employee is always available in the Department table.

25. What is Relational Algebra and what are its important operations?

Answer:
Relational Algebra is a mathematical way to query data in a database.

Main operations:

1. Selection (σ) – Filters records.
   σ (Salary > 50000) (Employee)
2. Projection (π) – Selects some columns.
   π (Name, Salary) (Employee)
3. Union (∪) – Combines two tables.
4. Intersection (∩) – Extracts common records.
5. Difference (-) – Subtracts one table from another.

26. What is the difference between B-Trees and B+ Trees?

Answer:

• B-Tree: It is a Balanced Search Tree, in which both data and pointers reside in internal and leaf nodes.
• B+ Tree: In this, data is only in the leaf nodes, which makes it perform better Range Queries.

27. What is Data Fragmentation?

Answer:
Data Fragmentation means dividing data into smaller parts and storing them on different servers.

Types:

1. Horizontal Fragmentation – Division based on rows.
2. Vertical Fragmentation – Division based on columns.

28. What is a Deadlock and how can it be prevented?

Answer:
A Deadlock occurs when two or more transactions wait for each other's resources while holding their own.

Prevention methods:

1. Timeouts – If the time limit is exceeded, cancel the transaction.
2. Wait-Die Scheme – Wait or abort according to priority.

29. What are Stored Procedures?

Answer:
Stored Procedures are blocks of SQL code stored in the database.

Example:

CREATE PROCEDURE GetEmployeeSalary (IN EmpID INT)
BEGIN
SELECT Salary FROM Employee WHERE Emp_ID = EmpID;
END;

This is used to avoid writing the same SQL code repeatedly.

30. Explain 1NF, 2NF, 3NF, BCNF of Normalization.

Answer:

• 1NF (First Normal Form) – There should be no Repeating Group.
• 2NF (Second Normal Form) – There should be no Partial Dependency.
• 3NF (Third Normal Form) – There should be no Transitive Dependency.
• BCNF (Boyce-Codd Normal Form) – There must be dependency on a Candidate Key.

31. What is the difference between a Temporal Database and a Traditional Database?

Answer:

• Temporal Database: Saves data with a time-stamp.
• Traditional Database: Stores only the current data.

Example: A bank statement is an example of a Temporal Database.

32. What is the Two-Phase Commit Protocol?

Answer:
The Two-Phase Commit Protocol (2PC) is used to securely complete a transaction in a Distributed Database.

Steps:

1. Prepare Phase – The transaction is locked.
2. Commit Phase – If all nodes say OK, it is committed.

33. What is the difference between XML and JSON?

Answer:

• XML (Extensible Markup Language) – Stores data in a hierarchical format.
• JSON (JavaScript Object Notation) – Is used for simple, efficient, and fast data access.

34. What is Multi-Version Concurrency Control (MVCC)?

Answer:
MVCC is used to store different time-stamp versions in the database, allowing multiple users to access data at the same time.