Ruankao Database Systems Engineer (Intermediate) cheat sheet
Ruankao Database Systems Engineer (Intermediate) · Keep the key ideas within reach.
Data, Databases, and Information Retrieval
Primary keys are unique and non-null; foreign keys enforce referential integrity.
WHERE filters rows before grouping; HAVING filters aggregated groups.
COUNT(*) counts rows; COUNT(column) skips NULL values.
LEFT JOIN preserves unmatched left rows; INNER JOIN removes them.
Precision = relevant retrieved / retrieved; recall = relevant retrieved / all relevant.
SQL models, types and constraints
SQL tables may contain duplicate rows unless keys or other constraints prevent them.
PRIMARY KEY is unique and nonnull; CHECK accepts TRUE or UNKNOWN.
Choose exact numerics for exact decimal requirements.
DDL transaction and identity effects depend on the specified DBMS.
SQL retrieval, joins and transaction semantics
WHERE retains TRUE predicates; NULL comparisons can be UNKNOWN.
Keep optional-side filtering consistent with the intended outer-join result.
Use a guaranteed nonnull marker for an unmatched-row test.
Check join cardinalities before trusting an aggregate total.
Database theory, administration and recovery
State candidate keys and functional dependencies before normalizing.
Transactions, logs, permissions and recovery require an explicit DBMS model.
Replication is not an independent recovery history.
Use actual query plans and waits to investigate performance.
Computational Thinking and Problem Solving
Decomposition breaks problems down; abstraction removes irrelevant detail.
Binary search requires sorted data and is O(log n).
Merge sort is O(n log n) worst case; bubble sort is O(n^2).
Trace tables prevent guessing about loop output.
Update search bounds to mid + 1 or mid - 1, not mid.
Computer architecture, logic and data representation
State whether addresses refer to bytes or words before calculating capacity.
Distinguish PC, instruction register, address register and data register.
For n-bit two’s complement the range is−2^(n−1) through 2^(n−1)−1.
Cache locality and pipeline hazards matter more than a clock-rate label alone.
Operating systems, translation and concurrency
Draw a scheduling timeline before averaging waits.
Distinguish a TLB miss from a nonresident page fault.
LRU is a stack algorithm; FIFO can exhibit Belady’s anomaly.
Protect the shared invariant and analyze actual wait dependencies.
Computer Network Fundamentals and Architecture
OSI upper three layers (session, presentation, application) map to TCP/IP's single application layer
PDU order: bit → frame → packet → segment
Shannon uses S/N as a power ratio; convert dB first
Transmission delay = frame length / bandwidth; propagation delay = distance / velocity
A switch splits collision domains, not broadcast domains
802.1 Q is VLAN tagging, 802.1 X is port-based access control
Security foundations, governance and Chinese context
CIA triad plus authenticity, non-repudiation and accountability define what is protected.
Risk combines threat, vulnerability and business impact; a vulnerability alone is not a risk.
Bell-LaPadula: no read up, no write down - confidentiality.
Biba: no read down, no write up - integrity.
Governance decides acceptable risk and accountability; management decides how to achieve it
Risk appetite (board-approved) differs from tolerance (variation) and capacity (maximum bearable)
Three Lines Model: internal audit must remain independent of control ownership