Database Management Systems
Core concepts
Understand the relational model: relations, tuples, attributes, keys (primary, candidate, foreign, composite), and integrity constraints (entity and referential). Learn relational algebra basics (select, project, join, union) and SQL - DDL (CREATE, ALTER, DROP), DML (SELECT, INSERT, UPDATE, DELETE), and DCL (GRANT, REVOKE). Normalization is central: 1NF (atomic values), 2NF (no partial dependency), 3NF (no transitive dependency), BCNF. Know transactions and ACID properties, concurrency control (locking, two-phase locking), and recovery. Distinguish DBMS from file systems, and understand indexing, views, and the role of the DBA.
Traps to watch
A primary key is unique and not null; a candidate key may be multiple, and one becomes primary. Foreign keys enforce referential integrity but may allow nulls. 2NF requires 1NF plus no partial dependency - many questions omit the 1NF prerequisite. ACID: Atomicity (all or nothing), Consistency, Isolation, Durability - do not swap Isolation and Integrity. DELETE removes rows and can be rolled back; TRUNCATE is faster and usually not rollback-friendly; DROP removes structure. COUNT(*) counts nulls-free rows; COUNT(column) ignores nulls.
Worked example / rule of thumb
Given a table Student(RollNo, Name, Course, Instructor), if RollNo→Name and Course→Instructor, then Course→Instructor is transitive through RollNo, so the table is not in 3NF. Rule of thumb: "the key, the whole key, and nothing but the key" - 1NF, 2NF, 3NF respectively.