40 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.