MCS-207 – DBMS Quick Revision: 75 Questions with Answers

UNIT-1: DBMS Basics (Q1-15)

1. What is DBMS?

A DBMS is software that stores, retrieves, and manages data in a structured way while ensuring security, consistency, and concurrency.

2. Define Data.

Raw facts stored in the system.

3. Define Information.

Processed and meaningful data.

4. What is Metadata?

Data about data; example: table names, column types.

5. Difference between Data and Database.

• Data → raw facts

• Database → organized structured collection stored electronically

6. Characteristics of DBMS

Data independence, security, concurrency control, backup & recovery, reduced redundancy.

7. What is a Database System?

DBMS + Database + Application Programs + Users.

8. Who is DBA?

Person responsible for managing database environment (authorization, backup, tuning).

9. Explain Data Abstraction Levels.

1. External Level

2. Conceptual Level

3. Internal Level

10. What is Data Independence?

Ability to change schema at one level without affecting higher levels.

11. Logical vs Physical Data Independence.

12. What is a Data Model?

Framework that defines how data is stored. Examples: ER, Relational, Object-Oriented.

13. What is ER Model?

Conceptual model using entities, attributes, relationships.

14. What is Cardinality?

Defines relationship count e.g., 1:1, 1:N, M:N.

15. Strong vs Weak Entity

UNIT-2: Relational Model & SQL (Q16-40)

16. What is a Relation?

A table consisting of rows (tuples) and columns (attributes).

17. Properties of Relation

No duplicate rows, atomic values, order irrelevant.

18. Types of Keys

Super, Candidate, Primary, Alternate, Composite, Foreign.

19. What is Referential Integrity?

Foreign key must match primary key in referenced table.

20. Define Domain Constraint.

Specifies allowed values for an attribute.

21. What is Relational Algebra?

Formal query language with operations such as Select, Project, Join, Union.

22. Write syntax of SELECT with WHERE.

SELECT * FROM Employee WHERE Salary > 40000;

23. What is LIKE operator?

Used for pattern searching.

SELECT Name FROM Students WHERE Name LIKE 'A%';

24. What is ORDER BY?

Sorts results ascending or descending.

25. What is GROUP BY?

Groups rows based on a column for aggregation.

26. Write SQL to find count of employees per department.

SELECT Dept, COUNT(*) FROM Employee GROUP BY Dept;

27. Difference between DELETE, TRUNCATE, DROP

28. What is a View?

Virtual table based on query result.

29. Advantages of Views

Security, simplified queries, logical independence.

30. Write SQL to create view.

CREATE VIEW HighSalary AS 
SELECT Name, Salary FROM Employee WHERE Salary > 60000;

31. What is JOIN?

Combines rows from multiple tables.

32. Types of JOINS

Inner, Left, Right, Full, Natural, Cross.

33. Write SQL for INNER JOIN

SELECT E.Name, D.DeptName
FROM Employee E JOIN Department D 
ON E.DeptID = D.DeptID;

34. What is DISTINCT?

Removes duplicate rows.

35. What is Constraint?

Rule applied to columns (PK, FK, CHECK, NOT NULL).

36. What is Normalization?

Organizing data to reduce redundancy and anomalies.

37. First Normal Form (1NF).

No repeating groups; atomic values only.

38. Second Normal Form (2NF).

No partial dependency on composite key.

39. Third Normal Form (3NF).

No transitive dependency.

40. Boyce-Codd Normal Form (BCNF).

Every determinant must be a candidate key.

UNIT-3: Transaction & Concurrency Control (Q41-60)

41. What is Transaction?

Logical unit of work consisting of operations like read/write.

42. ACID Properties

Atomicity, Consistency, Isolation, Durability.

43. What is Commit?

Saves work permanently.

44. What is Rollback?

Undo uncommitted changes.

45. What is Concurrency Control?

Manages simultaneous transactions to prevent conflicts.

46. Problems without Concurrency Control

Dirty read, lost update, phantom read, unrepeatable read.

47. Lock Types

Shared Lock (read), Exclusive Lock (write).

48. Two-Phase Locking (2PL)

Growing phase (acquire locks) + Shrinking phase (release locks).

49. Deadlock

Two transactions wait for each other’s lock forever.

50. Deadlock Handling Techniques

Detection, Prevention, Timeout, Avoidance.

51. Timestamp Ordering

Transactions arranged by timestamps to avoid conflicts.

52. Serializability

Ensures concurrent execution equals serial schedule.

53. What is Log-Based Recovery?

Changes recorded in log before applying (Write-Ahead Logging).

54. Checkpoint

Save state to reduce recovery time.

55. Shadow Paging

Recovery method keeping shadow copy of pages.

56. Cascading Rollback

One rollback forces others to rollback.

57. Savepoint

Intermediate point for partial rollback.

58. Isolation Levels

Read Uncommitted, Read Committed, Repeatable Read, Serializable.

59. Phantom Read

New rows appear in repeated queries during transaction.

60. Write-Ahead Logging (WAL).

Logs stored before applying actual changes to DB.

UNIT-4: File Structure & Indexing (Q61-75)

61. What is File Organization?

Method of storing records: Sequential, Indexed, Hashed.

62. Sequential File

Records stored in sorted order by key.

63. Hashed File

Address computed using hash function.

64. What is Index?

Structure that speeds up searching.

65. Primary vs Secondary Index

Primary → On primary key
Secondary → On attributes other than PK

66. Dense vs Sparse Index

Dense → entry for every record
Sparse → entry for block

67. What is B-Tree?

Balanced tree index allowing fast insert/delete/search.

68. What is B+ Tree?

Leaf nodes store actual pointers; widely used in DBMS.

69. What is RAID?

Disk architecture improving performance and fault tolerance.

70. Data Warehouse

Central repository storing historical data for analysis.

71. OLTP vs OLAP

OLTP → real-time transactions
OLAP → analytics and reporting

72. Big Data vs Traditional DBMS

Big Data handles unstructured distributed data; DBMS handles structured centralized data.

73. Distributed Database

Database spread across multiple sites but appears unified.

74. NoSQL

Schema-less database for big data (Types: Key-value, Document, Graph).

75. CAP Theorem

Cannot achieve all 3 simultaneously: Consistency, Availability, Partition tolerance.