Database Systems and Data Management
Core concepts
Master the relational model: relations, tuples, attributes, keys (primary, candidate, foreign, composite), and integrity constraints (entity and referential). Understand normalization through 1NF, 2NF, 3NF, and BCNF, and the anomalies each removes. Know SQL categories: DDL (CREATE, ALTER, DROP), DML (SELECT, INSERT, UPDATE, DELETE), DCL (GRANT, REVOKE), and TCL (COMMIT, ROLLBACK). Be able to read joins (inner, left, right, full), aggregate functions with GROUP BY and HAVING, subqueries, and views. Understand transactions and ACID properties, concurrency control, and the difference between a DBMS and a file system. Know ER modelling and cardinality notation.
Traps
A frequent trap is the WHERE versus HAVING distinction: WHERE filters rows before grouping, HAVING filters groups after aggregation. Another is assuming a primary key may contain NULL - it cannot. Remember 2NF removes partial dependencies on part of a composite key, while 3NF removes transitive dependencies on non-key attributes. Do not confuse DELETE (DML, rollback-able) with DROP (DDL, removes structure).
Worked example
Table Student(id, name, dept, dept_head). Here id determines name and dept, and dept determines dept_head - a transitive dependency, so the table is in 2NF but not 3NF. Decompose into Student(id, name, dept) and Department(dept, dept_head). Rule of thumb: ask whether a non-key attribute depends on another non-key attribute; if yes, split it out.