DBMS - Very Short
1. Define DBMS.
A DBMS (Database Management System) is software used to store, manage, and organize data in a structured
way. It allows users to easily insert, update, delete, and fetch data while ensuring security, consistency, and
efficiency. Examples: MySQL, Oracle, MongoDB.
2. What is a primary key?
A primary key is a special column (or a set of columns) that uniquely identifies each record in a table.
• It cannot be null
• It cannot repeat
Every table should have one primary key to avoid duplicate records.
3. Define candidate key.
A candidate key is any attribute or group of attributes that can uniquely identify a record in a table.
A table may have many candidate keys, but only one becomes the primary key.
4. What is a foreign key?
A foreign key is a field in one table that refers to the primary key of another table.
It is used to maintain relationships between two tables and ensure referential integrity (data remains
consistent).
5. Define tuple and attribute.
• Tuple: A row in a table (single record).
• Attribute: A column in a table (describes data type of a value).
Example: In a "Students" table,
• Each student is a tuple,
• Columns like Name, Roll No, Age are attributes.
6. What is a schema?
A schema is the blueprint or design of the database.
It shows:
• Table names
• Attributes
• Data types
• Relationships
It describes how data is structured in the database.
7. Define data independence.
Data independence means changes in the database structure do not affect the application programs.
Two types:
• Logical Data Independence: Change in schema does not affect programs.
• Physical Data Independence: Change in storage/ hardware does not affect schema.
8. What is normalization?
Normalization is the process of organizing data to reduce redundancy (duplicate data) and avoid update
anomalies.
It breaks large tables into smaller, related tables while maintaining data integrity.
9. Define 1NF.
A table is in First Normal Form (1NF) if:
• All values are atomic (no multiple values in a single cell).
• Each column has a unique name.
• Order of rows does not matter.
Example of NOT 1NF:
Phone = {9876, 9999}
(It has multiple values)
10. What is functional dependency?
Functional dependency shows a relationship between attributes.
If attribute A uniquely determines attribute B, we write:
A→B
Meaning B depends on A.
Example:
StudentID → StudentName
(If you know StudentID, you can find StudentName)
11. What is DDL?
DDL (Data Definition Language) is used to define the structure of database objects such as tables.
Commands: CREATE, ALTER, DROP, TRUNCATE.
12. Define DML.
DML (Data Manipulation Language) is used to interact with and modify data inside tables.
Commands: SELECT, INSERT, UPDATE, DELETE.
13. What is ACID property?
ACID ensures reliability in transactions:
• Atomicity: Complete or nothing happens.
• Consistency: Data remains valid.
• Isolation: Transactions do not interfere with each other.
• Durability: Once committed, data is permanent.
14. Define transaction.
A transaction is a sequence of operations performed as a single logical unit.
Example: Money transfer involves
1. Deducting from one account
2. Adding to another
Both must succeed or fail together.
15. What is a lock?
A lock controls access to data by multiple users.
It prevents conflicts by restricting others from reading or writing data until the current operation is finished.
16. Define deadlock.
Deadlock occurs when two or more transactions wait for each other forever.
Example:
• T1 holds Lock A and needs Lock B
• T2 holds Lock B and needs Lock A
Both wait and nothing proceeds.
17. What is indexing?
Indexing improves the speed of data retrieval.
It works like a book index—helps find data without scanning the entire table.
Indexes are created on frequently searched columns.
18. What is a view?
A view is a virtual table created using a SELECT query.
• It does not store data physically
• It shows data from one or more tables
Useful for security: you can show limited data to users.
19. Define BCNF.
BCNF (Boyce–Codd Normal Form) is an advanced form of 3NF.
A table is in BCNF if:
For every functional dependency A → B, A must be a candidate key.
20. What is relational algebra?
Relational algebra is a procedural query language used to retrieve data in a mathematical way.
It uses operations like:
• SELECT
• PROJECT
• JOIN
• UNION
• INTERSECTION
21. What is 3NF?
A table is in Third Normal Form (3NF) if:
1. It is in 2NF.
2. There should be no transitive dependency (non-key attribute depending on another non-key
attribute).
22. Define BCNF with example.
BCNF Rule:
Every determinant must be a candidate key.
(A determinant is an attribute that determines another attribute.)
Example:
Table: Course | Teacher | Room
Dependencies:
Teacher → Room
If Teacher is not a candidate key, BCNF is violated.
Solution: Split the table.
23. What is partial dependency?
Partial dependency occurs when a non-key attribute depends on part of a composite primary key, not the
whole key.
Occurs in 2NF violations.
Example:
Primary key = (StudentID, CourseID)
Marks depend only on CourseID → Partial dependency.
24. Define transitive dependency.
Transitive dependency means one non-key attribute depends on another non-key attribute.
Example:
A → B and B → C
Then C is transitively dependent on A.
25. What is super key?
A super key is a set of attributes that can uniquely identify a row.
It may contain extra attributes.
Example:
(StudentID, Name, Phone) → super key
But only StudentID is sufficient.
26. What is composite key?
A composite key is a primary key made up of two or more attributes.
Example:
(OrderID, ProductID) together form the primary key.
27. Define ER diagram.
An ER Diagram (Entity Relationship Diagram) represents the database structure through:
• Entities (objects)
• Attributes (properties)
• Relationships (connections between entities)
Used in database design.
28. What is cardinality?
Cardinality describes how many instances of one entity relate to another entity.
Types:
• One-to-One (1:1)
• One-to-Many (1:N)
• Many-to-Many (M:N)
29. Define join in relational algebra.
Join combines rows from two tables based on a related column.
It helps retrieve meaningful data stored across tables.
30. What is left outer join?
Left outer join returns:
• All rows from the left table
• Only matching rows from the right table
If no match, NULL is returned for right table fields.
31. Define serializability.
Serializability ensures that even when multiple transactions run at the same time, the final result is the same
as if they were executed one by one in some order.
Ensures correctness in concurrency.
32. What is cascading rollback?
Cascading rollback occurs when one transaction fails and all other transactions depending on it also roll
back.
Like a chain reaction.
33. Define phantom read.
Phantom read happens when a transaction reads a set of rows, but another transaction inserts new records
into that range.
So when the first transaction reads again, it sees "phantom" (new) rows.
SHORT ANSWER AND MEDIUM ANSWER
1. What is strict two-phase locking?
Strict Two-Phase Locking (Strict 2PL) is a concurrency control protocol where a transaction holds all
exclusive (write) locks until it commits.
It has two phases:
1. Growing Phase – Locks are acquired.
2. Shrinking Phase – Locks are released only after commit.
It prevents dirty reads and ensures serializability and recoverability.
2. Define hash indexing.
Hash indexing uses a hash function to convert a search key into a location (address) in memory.
It provides very fast data lookup.
Used mainly for equality searches (e.g., = queries).
Example: Searching for a student ID using a hashing algorithm.
It reduces searching time to nearly constant time O(1).
3. Explain the types of keys in DBMS with examples.
• Primary Key: Unique identifier (e.g., Roll_No).
• Candidate Key: All possible keys that can be primary keys.
• Super Key: Primary key + extra attributes (e.g., Roll_No + Name).
• Foreign Key: References primary key of another table.
• Composite Key: Combination of attributes (e.g., OrderID + ProductID).
4. Differentiate between DBMS and RDBMS.
DBMS stores data as files or hierarchical structures; RDBMS stores data in tables (relations).
DBMS doesn’t enforce relationships; RDBMS supports keys and constraints.
RDBMS uses normalization; DBMS may not.
Examples: DBMS–File System; RDBMS–MySQL, Oracle.
RDBMS supports ACID properties; DBMS usually doesn’t fully.
5. Explain 1NF, 2NF, and 3NF with suitable examples.
1NF: No repeating groups; atomic values. (Phone = 9876,9999 )
2NF: No partial dependency; depends on full composite key.
3NF: No transitive dependency; non-key attributes should not depend on other non-key attributes.
Example: If A → B and B → C, remove C to another table.
6. What is ER model? Explain its components.
ER model represents real-world objects (entities) and relationships.
Components:
• Entity: Object (Student, Teacher).
• Attributes: Properties (Name, Roll No).
• Relationship: Connection (Student–Enrolls–Course).
• Cardinality: 1:1, 1:N, N:M.
Used in database design.
7. Explain ACID properties of a transaction.
• Atomicity: All or none.
• Consistency: Database remains valid.
• Isolation: Transactions run independently.
• Durability: Changes remain after commit.
Ensures reliability of transactions.
8. Describe different types of joins in SQL with examples.
• INNER JOIN: Shows matching rows.
• LEFT JOIN: All left rows + matching right.
• RIGHT JOIN: All right rows + matching left.
• FULL JOIN: All rows from both tables.
Example: SELECT * FROM A JOIN B ON [Link] = [Link];
9. Explain functional dependencies and their role in normalization.
A → B means attribute A determines B.
Functional dependencies help identify keys and detect anomalies.
Used to decide normal forms (1NF, 2NF, 3NF, BCNF).
Example: Roll_No → Name.
They reduce redundancy and maintain consistency.
10. Describe the different levels of data abstraction.
Three levels:
• Physical Level: How data is stored.
• Logical Level: What data is stored.
• View Level: User-specific views.
It hides unnecessary details and simplifies access.
11. Explain indexing and its types.
Indexing speeds up data searching.
Types:
• Primary Index (on primary key)
• Secondary Index (on non-primary attributes)
• Clustered Index (reorders table)
• Non-clustered Index (separate index table)
Makes search faster than full scan.
12. What is a schedule? Explain serial and non-serial schedules.
A schedule is the sequence of operations of multiple transactions.
Serial Schedule: Transactions run one after another.
Non-serial Schedule: Operations interleave.
Serial is always safe; non-serial must be checked for correctness (serializability).
13. Explain deadlock and deadlock prevention techniques.
Deadlock occurs when two transactions wait forever for each other’s lock.
Prevention techniques:
• Timeout
• Resource ordering
• Preemption
• Wait-Die & Wound-Wait schemes
Goal: Avoid circular waiting.
14. Difference between DDL, DML, DCL, and TCL
• DDL: Defines structure (CREATE, ALTER).
• DML: Manipulates data (SELECT, INSERT).
• DCL: Controls access (GRANT, REVOKE).
• TCL: Manages transactions (COMMIT, ROLLBACK).
Used for database operations.
15. Explain partial, full, and transitive dependencies with examples.
Partial: Non-key depends on part of composite key.
Full: Non-key depends on full composite key.
Transitive: Non-key depends on another non-key.
Example: A→B, B→C gives transitive A→C.
16. Compare 2NF and 3NF with examples.
2NF: No partial dependency.
3NF: No transitive dependency.
Example: A,B → C but B → D violates 3NF.
3NF is stricter than 2NF.
Both reduce redundancy.
17. Explain BCNF and how it differs from 3NF.
BCNF is stronger than 3NF.
BCNF condition: Left side of every dependency must be a candidate key.
3NF allows exceptions if right side is a prime attribute.
BCNF removes more anomalies.
18. Types of SQL subqueries with examples.
• Single-row: Returns one value.
• Multi-row: Returns multiple rows (IN, ANY).
• Correlated: Executes for each row.
• Nested: Subquery inside subquery.
Example: SELECT * FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);
19. Explain conflict serializability with precedence graph.
Two schedules are conflict serializable if they produce the same result as a serial schedule.
Precedence graph: Nodes represent transactions; edges represent conflicts.
If the graph has no cycle, schedule is conflict-serializable.
Used in concurrency control.
20. Explain view in SQL and its advantages.
View is a virtual table created using SELECT.
Advantages:
• Security
• Simplifies complex queries
• Shows specific data to users
• Saves time
Does not store data physically.
21. Differentiate between clustered and non-clustered indexing.
Clustered Index: Rearranges table order. One per table.
Non-clustered Index: Separate index table. Many allowed.
Clustered is faster for range queries.
Non-clustered is best for frequent lookups.
22. Explain types of anomalies before normalization.
• Insertion anomaly
• Update anomaly
• Deletion anomaly
These occur due to redundancy and poor structure.
Normalization removes these issues.
23. Discuss types of constraints in DBMS.
• Primary Key
• Foreign Key
• Unique
• Not Null
• Check
• Default
Constraints protect data integrity.
24. Explain multi-valued dependencies and 4NF.
A multi-valued dependency exists when one attribute determines multiple independent attributes.
Example: Student →→ Phone, Student →→ Skill.
4NF removes multi-valued dependencies.
A table in 4NF has no non-trivial multi-valued dependency.
25. Explain normalization in detail with examples.
Normalization organizes data to remove redundancy.
Steps:
• 1NF: Atomic values.
• 2NF: Remove partial dependency.
• 3NF: Remove transitive dependency.
• BCNF/4NF: Remove advanced dependencies.
Improves consistency and reduces anomalies.
26. Explain concurrency control and locking protocols.
Controls simultaneous access to maintain consistency.
Methods:
• Two-Phase Locking
• Timestamp ordering
• Optimistic concurrency
Ensures serializability and prevents conflicts.
27. Describe ER model and ER-to-relational mapping.
ER model shows entities, attributes, relations.
Mapping:
• Entity → Table
• Attributes → Columns
• Relationships → Foreign keys
Converts ER design into database schema.
28. Explain transaction management and recovery techniques.
Transaction management ensures correctness using ACID.
Recovery methods:
• Log-based recovery
• Checkpoints
• Shadow paging
Helps restore data after failure.
29. Discuss indexing in DBMS and B-tree/B+ tree structures.
Indexes improve search speed.
B-Tree: Balanced tree storing keys and data.
B+Tree: Stores keys in internal nodes, data in leaf nodes.
Used in most databases for fast lookups.
30. Explain relational algebra operations with examples.
Main operations:
• SELECT
• PROJECT
• UNION
• JOIN
• INTERSECT
They help manipulate and retrieve data mathematically.
31. Describe SQL with examples of major commands.
SQL manages data with:
• DDL: CREATE TABLE
• DML: INSERT INTO
• DCL: GRANT
• TCL: COMMIT
Used for database operations.
32. Discuss DBMS architecture and components.
DBMS has three levels: external, conceptual, internal.
Components: query processor, storage manager, buffer manager, transaction manager.
Provides abstraction and security.
33. Explain deadlock handling techniques.
Techniques:
• Deadlock detection
• Deadlock prevention
• Deadlock avoidance
• Recovery by rollback
Used for concurrency safety.
34. Describe distributed DBMS with advantages and architecture.
A distributed DBMS stores data across multiple locations.
Advantages: reliability, faster access, scalability.
Architecture: Client-server, peer-to-peer, multi-database.
Ensures transparency across sites.
35. Describe functional dependencies in detail and their role.
Functional dependency shows attribute relationships.
Helps find keys and normalize tables.
Example: Roll → Name.
Guides 1NF to BCNF.
36. Explain ER-to-relational mapping step-by-step.
• Entity → Table
• Simple attribute → Column
• Composite → Break into parts
• Relationship → Foreign key
• Weak entity → Table with partial key
Used for schema design.
37. Discuss SQL joins in detail with diagrams.
Types: inner, left, right, full.
Join combines rows from tables.
Used for related data retrieval.
(If needed I can also add diagrams.)
38. Explain types of schedules in DBMS.
• Serial
• Non-serial
• Serializable
• Conflict-serializable
Schedules determine order of operations.
39. Explain ACID properties of a transaction.
ACID ensures reliable transaction processing.
• A – Atomicity: All steps complete or none.
• C – Consistency: Database remains valid after transaction.
• I – Isolation: Transactions do not affect each other.
• D – Durability: Changes remain even after failure.
It guarantees safe and correct transactions.
40. Describe different types of joins in SQL with examples.
• INNER JOIN: Shows matching rows only.
• LEFT JOIN: All from left + matching right.
• RIGHT JOIN: All from right + matching left.
• FULL JOIN: All rows from both tables.
Example: SELECT * FROM A INNER JOIN B ON [Link] = [Link];
41. Explain functional dependencies and their role in normalization.
A functional dependency A → B means A uniquely determines B.
Used to find keys and identify redundancy.
Helps decide normal forms like 2NF, 3NF, BCNF.
Example: Roll_No → Name.
Makes database consistent and well-structured.
42. Describe the different levels of data abstraction.
Three levels:
• Physical Level: How data is stored physically.
• Logical Level: What data is stored and relations.
• View Level: What users see.
It hides complexity and improves security.
Makes DBMS easier to use.
43. Explain indexing and its types.
Indexing speeds up data searching.
Types:
• Primary Index
• Secondary Index
• Clustered Index
• Non-clustered Index
Indexes work like a book index for faster lookup.
44. What is a schedule? Explain serial and non-serial schedules.
A schedule is the order of operations from multiple transactions.
Serial Schedule: Transactions run one-by-one.
Non-Serial Schedule: Operations interleave.
Serial is guaranteed correct; non-serial must be checked for serializability.
45. Explain deadlock and deadlock prevention techniques.
Deadlock occurs when two transactions wait for each other forever.
Prevention techniques:
• Resource ordering
• Timeout
• Preemption
• Wait-Die / Wound-Wait
Goal: avoid circular waiting.
46. Difference between DDL, DML, DCL, and TCL.
• DDL: Structure commands (CREATE, ALTER).
• DML: Data manipulation (INSERT, UPDATE).
• DCL: Access control (GRANT, REVOKE).
• TCL: Transaction control (COMMIT, ROLLBACK).
Used for managing database and data.
47. Explain partial, full, and transitive dependencies with examples.
• Partial: Non-key depends on part of composite key.
• Full: Non-key depends on whole composite key.
• Transitive: Non-key depends on another non-key.
Example: A→B, B→C gives transitive A→C.
48. Compare 2NF and 3NF with examples.
2NF: Remove partial dependency.
3NF: Remove transitive dependency.
Example: Roll, Subject → Marks ✔
Dept → HOD (3NF violation)
3NF is stronger and cleaner than 2NF.
49. Explain BCNF and how it differs from 3NF.
In BCNF, left side of every dependency must be a candidate key.
3NF allows some exceptions for prime attributes.
BCNF removes more anomalies than 3NF.
Used in complex functional dependency situations.
50. Describe the types of SQL subqueries with examples.
• Single-row: Returns one row.
• Multi-row: Returns many rows (IN, ANY).
• Correlated: Runs for each row.
• Nested: Subquery inside subquery.
Example: SELECT name FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);
51. Explain conflict serializability with precedence graph.
A schedule is conflict serializable if it results in the same outcome as a serial schedule.
Precedence graph: nodes = transactions; edges = conflicts.
If graph has no cycle, schedule is serializable.
Ensures correctness in concurrency.
52. Explain view in SQL and its advantages.
A view is a virtual table created from a SELECT query.
Advantages:
• Security
• Simplifies complex queries
• Hides sensitive data
• Saves time
Does not store data physically.
53. Differentiate between clustered and non-clustered indexing.
Clustered Index: Changes the physical order of table; one per table.
Non-clustered Index: Separate index structure; many allowed.
Clustered is faster for range queries.
Non-clustered is better for lookups.
54. Explain types of anomalies before normalization.
• Insertion anomaly
• Update anomaly
• Deletion anomaly
They occur due to data redundancy.
Normalization removes these problems.
55. Discuss types of constraints in DBMS.
Types:
• Primary Key
• Foreign Key
• Unique
• Not Null
• Check
• Default
Constraints maintain data accuracy and integrity.
56. Explain multi-valued dependencies and 4NF.
A multi-valued dependency A →→ B means A determines multiple independent values of B.
Example: A student may have multiple phones and multiple skills independently.
4NF removes multi-valued dependency.
Prevents redundancy.
57. Explain normalization in detail with examples.
Normalization organizes data to reduce redundancy.
Steps:
• 1NF: Atomic values
• 2NF: No partial dependency
• 3NF: No transitive dependency
• BCNF/4NF: Complex dependency removal
Creates clean and efficient tables.
58. Explain concurrency control and locking protocols.
Ensures correctness when multiple users access data.
Techniques:
• Two-phase locking
• Timestamp ordering
• Optimistic concurrency
Prevents conflicts and maintains consistency.
59. Describe ER model and ER-to-relational mapping.
ER model shows real-world objects.
Mapping:
• Entity → Table
• Attributes → Columns
• Relationship → Foreign key
It converts conceptual design into relational schema.
60. Explain transaction management and recovery techniques.
Transaction management ensures ACID.
Recovery techniques:
• Log-based recovery
• Undo/Redo
• Checkpoints
Used after system failure to restore data safely.
61. Discuss indexing in DBMS and B-tree/B+tree structures.
Indexes speed up search operations.
B-Tree: Keys + data in all nodes.
B+Tree: Keys in internal nodes, data only in leaf nodes.
B+Tree is standard for DBMS indexing.
62. Explain relational algebra operations with examples.
Operations:
• SELECT (σ)
• PROJECT (π)
• JOIN
• UNION
Example: σ(age > 20)(Student).
Used to query relational databases.
63. Describe SQL with examples of major commands.
Examples:
• DDL: CREATE TABLE student(...)
• DML: INSERT INTO student VALUES(...)
• DCL: GRANT SELECT ON student TO user
• TCL: COMMIT;
SQL manages database and data.
64. Discuss DBMS architecture and components.
Three-level architecture: external, conceptual, internal.
Components:
Query processor, storage manager, buffer manager, transaction manager.
Provides abstraction, security, and efficiency.
65. Explain deadlock handling techniques.
Techniques:
• Deadlock Prevention
• Deadlock Avoidance
• Deadlock Detection
• Deadlock Recovery
Used to maintain concurrency safety.
66. Describe distributed DBMS with advantages and architecture.
Stores data across multiple sites.
Advantages:
• Faster access
• Reliability
• Scalability
Architectures: client-server, peer-to-peer, multi-database.
Provides location transparency.
67. Describe functional dependencies in detail and their role.
A→B means A determines B.
Used to identify keys and remove redundancy.
Guides normalization from 1NF to BCNF.
Ensures consistent and meaningful data.
68. Explain ER-to-relational mapping step-by-step.
Steps:
• Entity → Table
• Attributes → Columns
• Composite attributes → Sub-attributes
• Relationships → Foreign keys
• Weak entity → Table + partial key
Makes conceptual model into real database.
69. Discuss SQL joins in detail with diagrams.
Types: inner, left, right, full, cross join.
Joins combine rows from related tables.
Used for multi-table queries.
(If needed, I can add diagrams.)
70. Explain types of schedules in DBMS.
• Serial
• Non-Serial
• Serializable
• Conflict-Serializable
Schedules decide the order of transaction operations.
71. Discuss concurrency control techniques.
• Two-phase locking
• Timestamp ordering
• Multiversion concurrency
• Optimistic concurrency
Ensures safe concurrent access without conflicts.
72. Explain deadlock detection and recovery.
Detection: system checks for cycles in wait-for graph.
Recovery:
• Rollback one or more transactions
• Restart transactions
• Kill lowest priority transaction
Used after deadlock occurs.
73. Explain file indexing methods: B-tree, B+ tree, hashing.
• B-Tree: Balanced, stores keys + data.
• B+Tree: Data only in leaf nodes.
• Hashing: Uses hash function for fast equality search.
Used for efficient file organization.
74. Normalize a complex table step-by-step up to BCNF.
Steps:
• Remove repeating groups → 1NF
• Remove partial dependencies → 2NF
• Remove transitive dependencies → 3NF
• Ensure determinant is candidate key → BCNF
Result: clean, redundancy-free schema.
75. Explain ACID properties with real-life examples.
Example: ATM money transfer.
• Atomicity: Money deducted only if added.
• Consistency: Balance matches rules.
• Isolation: Two users withdrawing don’t clash.
• Durability: Transaction saved even after power failure.
76. Describe relational algebra in detail (all operations).
Operations:
• SELECT
• PROJECT
• JOIN
• UNION
• INTERSECT
• DIFFERENCE
Used to form queries in mathematical way.