Database theory, administration and recovery
Core concepts
Master the relational model: relations, tuples, attributes, keys (primary, candidate, foreign, composite), and integrity constraints (entity and referential). Understand normalization through 1 NF, 2 NF, 3 NF, 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 2 NF removes partial dependencies on part of a composite key, while 3 NF removes transitive dependencies on non-key attributes. DELETE changes rows while DROP removes the object; rollback and implicit-commit behavior depend on the stated DBMS and transaction context.
Worked example
Table Student(id, name, dept, dept_head). Here id determines name and dept, and dept determines dept_head - a transitive dependency, assuming id is the sole candidate key and the stated functional dependencies hold, it is in 2 NF but violates 3 NF. 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.
Operational recovery and assurance
Database administration begins with an inventory of data, owners, privileges and recovery requirements. A backup job reporting success is not proof of a usable restore. Match a base backup with the required consistent log history and test recovery to the specified point. Replication can improve availability but may also copy accidental deletion or corruption, so it does not replace an independently retained recovery history. State consistency and durability assumptions, storage failures, key access and replay requirements before promising any recovery point or duration.
Least privilege applies to application accounts, administrators and maintenance automation. Separate ordinary query access from schema changes and grant only the actions required for the actual role. Parameterized values prevent their interpretation as SQL syntax; they do not substitute for authorization checks on the selected records. Audit evidence should identify relevant actor, action, object, outcome and trustworthy time without indiscriminately logging secrets. For performance work, inspect actual row estimates, access paths, selectivity, waits and representative workload. An index improves some access patterns but costs storage and maintenance; a replica can have lag and cannot automatically provide fresh reads. These are conceptual administration foundations, not commands to run on a production database or a promise that one vendor’s locking, isolation or backup syntax works unchanged elsewhere.