28 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 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:
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:
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 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:
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:
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 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 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 groups rows by columns and works with aggregate functions like SUM and COUNT.
What is the main purpose of creating an index?
Answer:
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:
Atomicity guarantees all-or-nothing: commit finalizes on success, rollback undoes on failure.
Which describes the isolation property among the ACID properties?
Answer:
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:
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 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 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 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 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 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 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 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 covers flexible models like key-value and document stores, suiting large-scale, distributed, unstructured data.
Which describes a candidate key?
Answer:
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:
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:
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 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 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 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 sorts results by a column; GROUP BY groups for aggregation and HAVING filters after aggregation.