Basics of DBMS: Full Concept Set with Worked Examples for GATE and Interviews

DBMS terms become useful only when a table or transaction forces a choice. File-system failures motivate schemas; schemas define keys and relationships; normalization repairs redundancy; SQL, indexes and transactions keep the design useful under load. After this foundation, test normal forms with DBMS Normalization MCQs: 12 Solved (1NF to BCNF) and connect the topic to GATE CS Exam Preparation.

Why a DBMS beats plain files

Suppose a college stores Rahul Verma's address in admissions, hostel, and library files. Rahul moves, but only admissions updates its copy. One fact now has three copies that disagree.

This creates redundancy, because the address is repeated; inconsistency, because copies disagree; and an update anomaly, because one change requires several edits.

A database management system, or DBMS, is software for defining, storing, querying, and controlling access to structured data. It adds one controlled store, data-type rules, concurrency, recovery, and SQL.

Schema, instance, and the three-schema architecture

A schema is the design, such as STUDENT(roll_no, name, cgpa). An instance is the data at one moment: (101, Rahul, 8.2), (102, Sana, 9.1), and (103, Imran, 7.4). The schema changes rarely; the instance changes with each insert, update, or delete.

The three-schema architecture separates user views from disk storage:

• The view or external level can show a clerk only roll_no and name, while faculty can also see cgpa.

• The logical or conceptual level contains the complete STUDENT table and its relationships.

• The physical level decides how rows, data blocks, and indexes are stored.

Logical data independence means changing the conceptual schema, such as adding a column, without breaking views. Physical data independence means changing storage or indexes without changing logical tables. Logical independence is harder because conceptual changes sit closer to applications.

The relational model and the family of keys

In STUDENT(roll_no, email, name), assume roll_no and email are unique. A super key uniquely identifies a row. {roll_no}, {email}, {roll_no, name}, and {email, name} all qualify.

A candidate key is a minimal super key. Thus {roll_no} and {email} are the two candidate keys. {roll_no, name} is not minimal because roll_no works alone. Choose roll_no as the primary key, and email becomes an alternate key.

In ENROLLMENT(roll_no, course_id, grade), neither roll_no nor course_id is unique alone because both sides repeat. The composite primary key is {roll_no, course_id}. The foreign key ENROLLMENT.roll_no references STUDENT.roll_no, preventing enrollment of a missing student.

To count candidate keys from functional dependencies, compute attribute closures and retain only minimal sets. The linked practice set develops this drill.

Turning an ER design into tables

In an ER model, STUDENT and COURSE are entities. ENROLLS is many-to-many: each student can take many courses, and each course can contain many students. ENROLLS carries grade.

Each entity becomes a table. A many-to-many relationship becomes a table containing both entity keys and its own attributes. We get STUDENT(roll_no, name), COURSE(course_id, title), and ENROLLMENT(roll_no, course_id, grade), keyed by {roll_no, course_id}. A one-to-many relationship instead places the foreign key on the many side.

Sample rows are STUDENT(101, Rahul), COURSE(CS101, DBMS), and ENROLLMENT(101, CS101, A). This bridges an ER diagram and its relations.

Normalization, worked from a visible anomaly

Start with SCORES(roll_no, course_id, marks, student_name):

Rahul's name repeats. Every cell is atomic, so the table is in first normal form, or 1NF. First normal form bans multivalued or composite cells; it does not remove redundancy.

The candidate key is {roll_no, course_id}, but roll_no -> student_name. Because student_name depends on only part of the key, SCORES fails second normal form, or 2NF.

Decompose it into STUDENT(roll_no, student_name) with (101, Rahul) and (102, Sana), plus SCORES(roll_no, course_id, marks) with the three score rows. Rahul's name appears once, and every score remains.

For third normal form, or 3NF, remove transitive dependencies between non-key attributes. If course_id -> instructor and instructor -> instructor_phone, split again so each non-key fact depends on the key, the whole key, and nothing but the key.

Transactions and ACID on a bank transfer

A transaction is one logical unit that happens completely or not at all. Account A has 2000, B has 1000, and we transfer 500:

1. Debit A: 2000 - 500 = 1500.

2. Credit B: 1000 + 500 = 1500.

Atomicity rolls back the debit if a crash occurs before the credit, so A returns to 2000. Consistency preserves the invariant: before, 2000 + 1000 = 3000; after, 1500 + 1500 = 3000. Isolation hides the half-finished state (A = 1500, B = 1000). Durability makes the committed (A = 1500, B = 1500) survive a power failure.

The final state is A = 1500, B = 1500, total = 3000. Dirty reads and lost updates test whether you can apply ACID, not merely recite it.

SQL, indexing, concurrency, and recovery complete the DBMS foundation

SQL operates on the relational design. With STUDENT rows (101, Rahul) and (102, Sana), SELECT name FROM STUDENT WHERE roll_no = 102; returns Sana. Data definition language creates the schema, data manipulation language changes rows, and SELECT reads the required projection and selection.

An index stores a search structure, commonly a B+ tree, beside the table. An index on roll_no can locate 102 without scanning every STUDENT row, but inserts and updates must maintain both the table and the index. The query optimizer chooses between an index scan and a table scan, while the schema designer accepts the maintenance cost of each useful index.

Concurrency control orders overlapping transactions so that their result matches a valid serial order. Under serializable isolation, locks or timestamp rules prevent another transaction from reading the transfer after A is debited but before B is credited. Recovery complements that control: write-ahead logging records a change before the data page is written, checkpoints limit the log range to inspect, and undo or redo restores a correct state after a crash.

Common traps in GATE and interviews

• Mistake: Calling {roll_no, name} a candidate key because it is unique. Correction: It is only a super key because it is not minimal.

• Mistake: Confusing schema with instance. Correction: Schema is the design; instance is the current data.

• Mistake: Assuming 1NF removes redundancy. Correction: 1NF only requires atomic cells.

• Mistake: Saying a foreign key must be the primary key of its own table. Correction: It need not be unique in the referencing table.

• Mistake: Treating ACID consistency as normalization. Correction: ACID consistency means a transaction preserves declared database rules and invariants.

GATE questions commonly give a relation and functional dependencies, then ask for candidate keys, highest normal form, or lossless decomposition. Interviews ask why files fail, how a foreign key works, or how ACID protects a transfer.

For current scope and weightage, check the official GATE CS syllabus for the relevant cycle from the organizing IIT or IISc. The official syllabus and notification remain the source of truth.

The short version and where to go next

A DBMS stores facts under controlled rules. Keys identify rows, ER mapping creates relations, normalization removes harmful dependencies, and ACID keeps changes safe. The rest builds on these ideas.

Next, read B+ Trees and Database Indexing for storage. For broader preparation, use the GATE CS track above, the Mera Placement Hoga complete placement course for interviews, or the CS Fundamentals category to connect DBMS with core computer science.