60 questions / 10 random questions
Random questions, instant feedback, and review for missed questions.
View recommended Database Specialist resources →
In a relational database, which is the horizontal unit representing one record (entity)?
Answer: A row (record / tuple)
A relational table consists of rows (records) and columns (attributes); one row represents one record.
Which column (or set) is defined to uniquely identify each row in a relational database?
Answer: Primary key
A primary key enforces uniqueness and non-NULL (entity integrity) to identify each row uniquely.
Which column references another table's primary key to maintain relationships and integrity?
Answer: Foreign key
A foreign key references another table's primary key and enforces referential integrity.
Which normal form removes repeating groups so each cell holds a single value?
Answer: First normal form
First normal form removes repeating groups so each attribute holds a single value; it is the starting point.
To reach second normal form, which kind of functional dependency is removed?
Answer: Partial dependencies on only part of the primary key
Second normal form, building on 1NF, removes partial dependencies on part of a composite primary key.
To reach third normal form, which kind of functional dependency is removed?
Answer: Transitive dependencies where a non-key attribute depends on another non-key attribute
Third normal form, building on 2NF, removes transitive dependencies among non-key attributes.
Which basic SQL statement retrieves rows matching a condition from a table?
Answer: SELECT ... WHERE
SELECT chooses columns and WHERE filters rows; CREATE defines structures and GRANT assigns privileges.
Which SQL operation combines multiple tables into one result using related columns?
Answer: JOIN
JOIN combines tables on related columns; an inner join returns only rows matching in both tables.
Which SQL clause groups rows by a column to aggregate sums or counts?
Answer: GROUP BY
GROUP BY groups rows by columns and works with aggregate functions like SUM and COUNT.
What is the main purpose of creating an index?
Answer: To speed up searches (reads)
Indexes speed up reads but add maintenance cost on writes and consume extra storage.
Which pair of operations enforces the atomicity property of a transaction?
Answer: Commit and rollback
Atomicity guarantees all-or-nothing: commit finalizes on success, rollback undoes on failure.
Which describes the isolation property among the ACID properties?
Answer: Concurrent transactions do not interfere with each other
Isolation ensures concurrently executing transactions do not interfere with one another.
Which state occurs when transactions wait on each other's locks and none can proceed?
Answer: Deadlock
A deadlock is mutual lock-waiting that halts progress; it is resolved by detection and aborting one transaction.
Which mechanism prevents inconsistencies when multiple users update the same data concurrently?
Answer: Concurrency control (locking)
Concurrency control coordinates simultaneous updates with locks to keep data consistent; shared and exclusive locks exist.
Which virtual table is defined from base tables and appears as a table to users?
Answer: A view
A view provides query results as a virtual table, simplifying complex queries and restricting access.
Which database object runs automatically in response to events like table updates?
Answer: A trigger
A trigger runs automatically on events like INSERT or UPDATE, used for integrity maintenance and logging.
Which technique diagrams entities and their relationships in database design?
Answer: An E-R diagram (entity-relationship diagram)
An E-R diagram depicts entities, attributes, and relationships, used in conceptual data modeling.
Which recovery process uses logs to reapply committed updates after a failure?
Answer: Roll-forward (forward recovery)
Roll-forward restores by reapplying committed log updates to a backup; rollback undoes incomplete work.
In a distributed database, which procedure ensures all sites either all commit or all roll back?
Answer: Two-phase commit
Two-phase commit gathers agreement in a prepare phase, then commits or aborts all sites together, preserving atomicity.
Which system integrates and stores data from multiple systems for large-scale analysis?
Answer: A data warehouse
A data warehouse integrates and stores data for analysis to support decision making.
Which is the umbrella term for databases that avoid fixed schemas and suit large-scale distribution?
Answer: NoSQL
NoSQL covers flexible models like key-value and document stores, suiting large-scale, distributed, unstructured data.
Which describes a candidate key?
Answer: A column (or set) that can uniquely identify rows and could serve as a primary key
A candidate key uniquely identifies rows and could be the primary key; unchosen ones are alternate keys.
Which condition is used in SQL to test whether a column contains NULL?
Answer: IS NULL
NULL represents unknown/absent and cannot be tested with comparison operators; use IS NULL / IS NOT NULL.
What is a main benefit of applying normalization?
Answer: Reducing redundancy and preventing update anomalies
Normalization removes redundancy to prevent anomalies, though joins increase and denormalization is sometimes used for performance.
Which is an example of a one-to-many relationship between entities?
Answer: One customer has many orders
One customer with many orders is a classic one-to-many; many-to-many is modeled via a junction table as two one-to-many.
Which mechanism keeps and synchronizes database copies on other servers for availability and load distribution?
Answer: Replication
Replication synchronizes database copies, distributing read load and improving availability during failures.
Which predefined routine stored in the database lets a set of operations be invoked together?
Answer: A stored procedure
A stored procedure defines logic in the database for reuse, reducing round-trips and standardizing processing.
Which SQL clause sorts retrieved results in ascending or descending order by a column?
Answer: ORDER BY
ORDER BY sorts results by a column; GROUP BY groups for aggregation and HAVING filters after aggregation.
A large orders table is frequently slow when filtering by order date and customer ID. Which measure should be considered first?
Answer: Design a composite index matching the predicates and verify the effect with the execution plan
Query performance depends on indexes that match predicates, joins, and ordering, plus execution-plan verification. Indexes can speed reads but increase update cost.
In transaction isolation, what is the phenomenon where a transaction reads another transaction's uncommitted update?
Answer: Dirty read
A dirty read occurs when uncommitted data is read. If that data is later rolled back, dependent processing may become inconsistent.
When rerunning the same aggregate condition, another transaction's inserted rows change the result set. What is this phenomenon?
Answer: Phantom read
A phantom read occurs when the set of rows matching the same predicate changes due to another transaction's insert or delete.
Two transactions keep waiting for each other's locks to be released, and neither can proceed. What is this state?
Answer: Deadlock
A deadlock occurs when processes hold resources needed by each other and cannot leave the wait state. A DBMS may detect it and abort one transaction.
Which design splits a table by month or range so searches and maintenance can target only relevant parts?
Answer: Partitioning
Partitioning divides a large table physically or logically by range, list, hash, and similar methods, helping prune searches and maintain old data.
In production database backup design, you want a small RPO. Which design is most closely related?
Answer: Shorten backup frequency and transaction-log capture intervals
RPO indicates the point in time to which data must be recoverable. Smaller RPO requires shorter log capture and differential or incremental backup intervals.
You want to show columns containing personal data partially masked to unauthorized users. Which measure is appropriate?
Answer: Control displayed values using column masking or views
Sensitive data should be protected through least privilege, views, column masking, audit logs, and encryption where appropriate.
Which common modeling approach uses fact tables and dimension tables for easier data warehouse analysis?
Answer: Star schema
A star schema places a fact table such as sales at the center and surrounds it with dimensions such as date, product, and customer.
Which description best characterizes an OLTP system?
Answer: Processes many short transactions with high consistency
OLTP suits many short transactions such as orders, payments, and inventory updates, requiring consistency and responsiveness.
As a SQL injection countermeasure, what is the most basic implementation when an application queries a database?
Answer: Bind values using prepared statements with placeholders
Prepared statements separate SQL syntax from values and are a fundamental SQL injection defense, alongside input validation and least privilege.
What is the most appropriate main purpose of defining a foreign-key constraint?
Answer: Prevent child rows from referencing nonexistent parent rows and maintain referential integrity
A foreign-key constraint ensures values in a child table exist in the parent table's key, preventing orphan records.
When improving a slow query in an operational database, what investigation should be done first?
Answer: Check the execution plan, statistics, rows read, and wait events
Slow-query tuning starts by checking execution plans and measured data to isolate missing indexes, stale statistics, join order, I/O waits, and similar causes.
A contract is either personal or corporate, never both, and only corporate contracts have a billing contact. What is the best conceptual data model?
Answer: Model Contract as a supertype with mutually exclusive Personal and Corporate subtypes
Common attributes belong in the supertype and type-specific attributes in subtypes. An exclusivity constraint captures the rule that a contract cannot be both types.
Product prices must reproduce historical orders at the price valid then, while future price changes can be registered in advance. Which design is appropriate?
Answer: Store effective start and end dates in price history and prevent overlapping periods
An effective-dated history model represents prices at any point and future schedules. Constraints and update procedures define how overlaps and gaps are handled.
Two screens update the same customer, and the later save silently overwrites the first change. Which measure can notify the user of the conflict?
Answer: Optimistic locking with a version number in the update condition
Include the version read by the user in the UPDATE predicate. If zero rows update, another transaction changed the row and the user can be asked to reload or reconcile.
Under timestamp ordering, newer transaction T2 has already updated a row when older T1 attempts to write it. What is the basic handling?
Answer: Reject T1's write for violating timestamp order and retry if needed
Timestamp ordering preserves a logical execution order. If an older write would invalidate a newer update, it is aborted and may be retried in a valid order.
In an MVCC database, a long-running read transaction prevents cleanup of obsolete row versions and the table is bloating. What is the best response?
Answer: Identify and shorten long transactions and monitor garbage-collection progress
MVCC cannot remove old row versions while an old snapshot may need them. Fix the long transaction and restore normal product-specific cleanup such as vacuuming.
An orders table frequently runs WHERE customer_id = ? AND ordered_at >= ? ORDER BY ordered_at. Which composite index should be considered first?
Answer: (customer_id, ordered_at)
Placing equality column customer_id first and range/order column ordered_at second commonly supports the customer's time-range scan and ordering. Verify with real data and plans.
A join estimated at 100 rows actually returns one million, causing an inefficient join algorithm. What should be checked first?
Answer: Statistics freshness, data skew, column correlation, and actual row counts
The optimizer estimates cardinality from statistics. Stale statistics, skew, or correlated columns can cause large errors and poor join order or algorithm choices.
A frequently updated table must be paged without duplicates or gaps in (created_at, id) order. Which condition is preferable to OFFSET pagination?
Answer: Fetch rows after the previous page's last (created_at, id) using the same ordering
Keyset pagination uses a unique ordering and the prior last key. It reduces effects of concurrent changes and the scan cost of deep offsets.
A nightly batch updates 100 million rows. On failure it restarts from the beginning and misses its window. What is the best improvement?
Answer: Commit in restartable units and resume from checkpoints
Split work into business-consistent units and persist progress. Design idempotency to avoid double updates and define business rules for partial commits.
A required column must be added with near-zero downtime while old application versions still run. Which migration sequence is safest?
Answer: Add it nullable, deploy dual-compatible code, backfill data, then tighten the constraint
The expand-and-contract pattern first extends the schema compatibly, then migrates data and code, and only later removes temporary compatibility.
A multi-terabyte production database must migrate to another product with only minutes of downtime. Which method is most appropriate?
Answer: Take an initial full copy, continuously apply changes with CDC, validate, then cut over
CDC follows changes during the bulk load. Once lag is small, a short freeze allows final sync and cutover, with count, hash, and business validation.
During two-phase commit, a participant is prepared but loses contact with the coordinator. Why must it not commit independently?
Answer: It could disagree with other participants and violate distributed atomicity
A prepared participant must learn the final decision from the coordinator's durable record or recovery procedure. Independent action can split commit and rollback outcomes.
A read replica may return stale data immediately after a write. How can a user be guaranteed to see their own update right away?
Answer: Read from the primary until a time or replication-position condition is met
Asynchronous replicas lag. For read-your-writes consistency, pin reads to the primary or wait until the replica reaches the required log position.
During a network partition, two primary candidates accepted writes and data conflicted after recovery. What is the core prevention?
Answer: Use quorum and fencing so only one primary can accept writes
To prevent split brain, quorum selects one legitimate primary and fencing isolates the old primary from storage or network access.
Critical data was deleted accidentally at 10:15. The team needs the 10:14 state for comparison with other databases. What is the best method?
Answer: Perform point-in-time recovery to an isolated environment using backups and transaction logs
PITR replays to just before deletion. Validate affected and dependent data in isolation, then choose selective repair or full cutover based on impact.
Database encryption keys are being rotated. Why is storing plaintext keys beside database backups dangerous?
Answer: A storage breach would expose both encrypted data and its decryption key
Keys should be protected in a separate KMS or HSM, with access control, audit, rotation, and retention for decrypting older backups.
In a multitenant database, the database itself must prevent cross-tenant rows even if an application predicate is omitted. Which measure is appropriate?
Answer: Enforce row-level security from authenticated identity and separate administrative privileges
Row-level security makes the DBMS enforce tenant boundaries on queries. Trusted connection identity, bypass privileges, and administrative auditing must also be designed.
A customer requests personal-data deletion. The live database is cleared, but backups retain data for a period. Which operation is appropriate?
Answer: Define retention and re-deletion after restore, isolate backups, and expire them on schedule
When backups cannot be edited safely, define justified retention, access restrictions, and expiration. Reapply deletion records after restore so deleted data is not revived.
A materialized view accelerates complex aggregation, but users require data no more than five minutes stale. What is most important in the design?
Answer: Define refresh method, interval, failure monitoring, and freshness measurement
Because materialized views persist results, full versus incremental refresh, duration, retries, and last-success monitoring directly determine the freshness SLA.
A hash join spills heavily to temporary storage and slows a nightly aggregation. What is the best investigation before changing it?
Answer: Check actual plan rows, estimation error, memory limit, temporary I/O, and pre-join filtering
Spills can result from cardinality errors, insufficient filtering, or memory limits. Measurements guide whether to fix statistics, SQL, indexes, batching, or memory.