Top DBMS Interview Questions & Answers (2026)
Follow Quizer
Interview Questions and Answers
Expand a question to view the answer and interview tip.
1. Limitations of File-Based Systems
File-based systems store data in individual files for each application. Limitations include:
-
Data Redundancy & Inconsistency
-
Same data may be stored in multiple files, causing duplication and inconsistencies.
-
-
Difficulty in Data Access
-
Accessing data requires writing custom programs. Complex queries are hard to implement.
-
-
Data Isolation
-
Data is scattered across files, making it difficult to access data across multiple files.
-
-
Integrity Problems
-
Hard to enforce data integrity (like unique IDs or constraints) in file-based systems.
-
-
Concurrent Access Issues
-
Multiple users cannot safely access or update files at the same time.
-
-
Security Issues
-
File-based systems have limited support for user authentication and access control.
-
2. How Database Approach Resolves Limitations
| File-Based System Limitation | DBMS Solution |
|---|---|
| Data redundancy & inconsistency | DBMS stores centralized data, reducing duplication. |
| Difficulty in data access | Provides query languages (like SQL) for easy data retrieval. |
| Data isolation | Unified database structure allows access to related data across tables. |
| Integrity problems | DBMS enforces constraints like primary keys, foreign keys, and rules. |
| Concurrent access issues | DBMS handles multi-user concurrency control and transactions. |
| Security issues | DBMS provides user authentication and access control mechanisms. |
3. Physical Architecture of a DBMS
The physical architecture defines how data and DBMS components are organized and accessed:
Components:
-
Hardware
-
Servers, storage devices, CPU, memory where DBMS and data reside.
-
-
DBMS Software
-
Core system managing database operations:
-
Storage Manager: Manages data storage, buffering, and retrieval.
-
Query Processor: Parses and executes queries.
-
Transaction Manager: Ensures ACID properties.
-
-
-
Data
-
Stored in files or tables on storage devices.
-
Organized logically into tables, indexes, views.
-
-
Users / Applications
-
End Users: Use applications to interact with DBMS.
-
Application Programs: Connect via APIs (like JDBC) to perform operations.
-
Physical Architecture Diagram (Simplified)
-
Queries from users pass through the query processor → transaction manager ensures consistency → storage manager handles reading/writing to physical storage.
Summary:
-
File-based systems have redundancy, inconsistency, poor security, and limited access.
-
DBMS centralizes data, enforces constraints, supports concurrency, and provides easy access.
-
Physical architecture includes users, DBMS software, and storage hardware interacting to manage data efficiently.
(i) Key Constraints
-
A key is an attribute (or set of attributes) that uniquely identifies a tuple (row) in a relation (table).
-
Constraint: No two tuples can have the same value for the key.
Example:
Table: Student(ID, Name, Age)
-
Key:
ID→ ensures each student has a unique ID.
| ID | Name | Age |
|---|---|---|
| 101 | John | 20 |
| 102 | Alice | 21 |
(ii) Domain Constraints
-
A domain constraint specifies that values of an attribute must come from a predefined domain (type or set of allowed values).
Example:
-
AgeinStudenttable → must be an integer between 18 and 25. -
Gender→ onlyMaleorFemale.
(iii) Candidate Key
-
A candidate key is a minimal set of attributes that can uniquely identify a tuple.
-
A relation can have multiple candidate keys; one is chosen as the primary key.
Example:
Table: Student(ID, RollNo, Email, Name)
-
Candidate keys:
ID,RollNo,Email→ any of these can uniquely identify a student.
(iv) Select Operation (σ)
-
Select (σ) returns tuples that satisfy a given condition.
-
Notation:
σ_condition(Relation)
Example:
Table: Student(ID, Name, Age)
-
Query:
σ_Age>20(Student)→ Students with Age > 20
| ID | Name | Age |
|---|---|---|
| 102 | Alice | 21 |
(v) Project Operation (π)
-
Project (π) returns specific columns (attributes) from a relation.
-
Notation:
π_attributeList(Relation)
Example:
Table: Student(ID, Name, Age)
-
Query:
π_Name(Student)→ Only names of students
| Name |
|---|
| John |
| Alice |
(vi) Equijoin Operation
-
Combines tuples from two relations based on equality of attributes.
Example:
Table1: Student(ID, Name)
Table2: Marks(ID, Score)
-
Query: Join on
ID→Student ⨝ Student.ID = Marks.ID Marks
| ID | Name | Score |
|---|---|---|
| 101 | John | 80 |
| 102 | Alice | 90 |
(vii) Set Difference Operation (-)
-
Returns tuples in one relation but not in another.
Example:
Table1: AllStudents(ID) → {101, 102, 103}
Table2: PassedStudents(ID) → {101, 102}
-
AllStudents - PassedStudents→ {103}
(viii) Referential Integrity Constraint
-
Ensures foreign key values in one table exist as primary key values in another table.
-
Prevents orphan records.
Example:
Table1: Student(ID, Name) → Primary Key: ID
Table2: Marks(StudentID, Score) → StudentID is foreign key referencing Student(ID)
-
Marks.StudentIDmust exist inStudent.ID.