Database Specialist ExaminationStudy guide

Covers the five roles a database management system (DBMS) plays in an integrated way—data definition, data manipulation, concurrency control, failure recovery, and data independence—and the judgment skill of determining, when a failure occurs, what the DBMS does and does not guarantee.

Covers the structure of the three-level schema—the external schema, conceptual schema, and internal schema—and logical data independence and physical data independence, whereby a change at one level does not propagate to another, along with the judgment skill of determining which level a given schema change will affect.

Covers the core concepts of the relational model—relation, tuple, attribute, domain, degree, and cardinality—and their correspondence to tables, the distinctions among candidate key, primary key, and foreign key, and the judgment skill of choosing keys appropriately in table design.

Covers the traditional data models—hierarchical, network, the relational model, and the object-oriented model—and the characteristics of each NoSQL style—key-value store (KVS), document-oriented, column-oriented, and graph database—along with the judgment skill of choosing the optimal data model for a given requirement.

Covers the fundamentals of the E-R diagram, which models a business domain as entities, relationships, and attributes; cardinality, which expresses the multiplicity of a relationship; and resolving many-to-many relationships, which cannot be represented directly in a relational database—framed as the practical judgment of deriving a relational schema from an E-R diagram.

Covers functional dependency, which expresses a dependency between attributes; partial functional dependency, where an attribute depends on only part of a composite key; transitive functional dependency, where an attribute depends indirectly via a non-key attribute; and first, second, and third normal form, which progressively eliminate these dependencies to prevent update anomalies—framed as the practical design judgment of removing update anomalies.

Covers Boyce-Codd normal form (BCNF), which eliminates anomalies that survive even third normal form (every determinant of every functional dependency is a candidate key); fourth normal form, which eliminates multivalued dependencies; fifth normal form, which eliminates join dependencies; and the trade-off between higher normalization and reversibility of decomposition (lossless decomposition)—framed as a practical decomposition judgment.

Covers the primary key and candidate key that uniquely identify an entity, the foreign key and referential constraint that reference another table's primary key, and the check constraint (CHECK) that restricts values with an arbitrary condition—framed as design judgments for maintaining integrity.

Covers physical design, which translates a normalized logical design into an actual storage scheme; denormalization, which intentionally introduces redundancy for performance; and the design judgment of balancing the trade-off between normalization and denormalization, and between JOIN cost and redundancy.

Covers the relational algebra operators selection (sigma), projection (pi), join (join symbol) (natural/equi/outer join), union, difference, Cartesian product, and division, and how SQL queries can be interpreted as combinations of these operators, together with practical query-design decisions.

Covers schema definition via DDL (CREATE/ALTER/DROP), design decisions around constraints (primary key, foreign key, CHECK, NOT NULL, unique constraint), privilege management via DCL (GRANT/REVOKE), and transaction finalization via COMMIT/ROLLBACK, from the perspective of practical schema-change and privilege-design work.

Covers when to use inner/outer/self joins, the performance and semantic differences between correlated and uncorrelated (non-correlated) subqueries, set operations (UNION/INTERSECT/EXCEPT), and how NULL and three-valued logic affect conditional expressions and join results, together with judgment calls for query correctness in practice.

Covers grouping with GROUP BY versus filtering groups with HAVING, how aggregate functions (COUNT/SUM/AVG, etc.) handle NULL, and window functions (analytic functions) that compute rankings or running totals without collapsing rows, together with judgment calls for correct aggregation design.

Covers the conditions for an updatable view and how WITH CHECK OPTION preserves its constraints, the cursor and dynamic SQL mechanisms applications use to execute SQL, and stored procedures/triggers that complete processing on the DBMS side, together with practical application-design decisions.

Covers the ACID properties—atomicity, consistency, isolation, and durability—that make up a transaction, and how transaction boundaries are designed with BEGIN/COMMIT/ROLLBACK, building the judgment needed to preserve business-process integrity.

Covers the choice of lock granularity via shared locks (S locks) and exclusive locks (X locks), and the growing phase and shrinking phase of the two-phase locking protocol (2PL), which guarantees serializability, building the judgment needed to choose the optimal concurrency-control approach from the tradeoff between throughput and consistency.

Covers detecting deadlock via the wait-for graph and countermeasures, the four isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—and how they correspond to the three read anomalies dirty read, non-repeatable read, and phantom read, plus MVCC, building the judgment needed to choose the optimal isolation level from the balance between consistency and throughput.

Covers the WAL (write-ahead logging) principle of recording updates to the log before the data itself, checkpoints, which shorten the recovery starting point, rollback/roll-forward via UNDO/REDO after a failure, and the recovery approach for each failure type—transaction failure/media failure/system failure—building the judgment needed to preserve durability.

Covers the prepare (commit-request) phase and commit phase of two-phase commit (2PC), which guarantees atomicity for a transaction spanning multiple database sites, the three-phase commit (3PC) that addresses its weakness, and the blocking problem in 2PC, building the judgment needed to decide how to ensure consistency in a distributed environment.

Covers when to use a B-tree index for fast lookups, a hash index for equality search, and a bitmap index for low-selectivity columns; the relationship between column order in a composite index and selectivity; and the trade-off an index imposes on update performance.

Covers how a DBMS automatically chooses how to execute a query via cost-based optimization, the statistics it relies on to judge this, the choice among join methods (nested loop join, sort-merge join, hash join), and how to judge whether a full table scan or an index scan is more advantageous.

Covers denormalization, intentionally introducing redundancy for performance; partitioning, splitting a large table; SQL tuning, revisiting the SQL statement itself; and bottleneck diagnosis—isolating the true cause of performance degradation and identifying the cause of a slow query.

Covers horizontal partitioning and vertical partitioning for distributing data across sites, replication (synchronous replication, asynchronous replication) for duplicating data, the six transparencies that realize distribution transparency, sharding for scale-out, and the CAP theorem, BASE, and eventual consistency that describe the limits of distributed systems.

Covers star schema and snowflake schema (fact tables, dimension tables) optimized for analytics, ETL and ELT for integrating data into an analytics platform, OLAP (drill-down, roll-up, slice and dice) for multidimensional analysis, data mining for pattern discovery, and big data/data lakes for handling large volumes of data.

Covers backups (full/differential/incremental) and recovery protecting data from failures, RAID guarding against disk failure, hot standby improving availability, access control (GRANT/REVOKE) governing privileges, encryption (TDE) protecting confidentiality, audit logging ensuring traceability, and countermeasures against SQL injection, a representative application-layer threat.