D330 Data Systems Administration - Set 1 - Part 2

Correct Answer: Combines rows from two or more tables based on a related column

A JOIN combines rows from multiple tables using a related column, allowing queries to return data spanning several tables in a single result set.

Correct Answer: Only rows with matching values in both tables

An INNER JOIN returns only the rows where the join condition is satisfied in both tables, excluding unmatched rows from either side.

Correct Answer: All rows from the left table plus matching rows from the right

A LEFT OUTER JOIN returns every row from the left table, with matching data from the right table where available and NULL values where no match exists.

Correct Answer: Organizing data to reduce redundancy and improve integrity

Normalization structures tables to minimize redundant data and dependency problems, reducing anomalies during insert, update, and delete operations.

Correct Answer: Each column contains atomic values with no repeating groups

First Normal Form requires that every column contain single, indivisible (atomic) values and that there be no repeating groups or arrays within a row.

Correct Answer: Eliminating transitive dependencies on the primary key

Third Normal Form requires that non-key attributes depend only on the primary key and not on other non-key attributes, eliminating transitive dependencies.

Correct Answer: Deliberately adding redundancy to improve read performance

Denormalization intentionally introduces redundancy, often to reduce costly joins and speed up read-heavy workloads, accepting some storage overhead and update complexity.

Correct Answer: A structure that speeds up data retrieval at the cost of extra storage

An index is a data structure that allows the database to locate rows quickly without scanning the whole table, improving read speed but adding storage and write overhead.

Correct Answer: Write operations become slower because indexes must be maintained

Every INSERT, UPDATE, and DELETE must update all affected indexes, so excessive indexing slows write operations and consumes additional storage.

Correct Answer: A unit of work that must complete entirely or not at all

A transaction groups one or more operations into a single logical unit that either commits completely or rolls back entirely, preserving data consistency.

Correct Answer: Atomicity, Consistency, Isolation, Durability

ACID describes the guarantees of reliable transactions: atomicity (all or nothing), consistency (valid state), isolation (concurrent transactions do not interfere), and durability (committed data persists).

Correct Answer: A transaction either fully completes or has no effect at all

Atomicity ensures a transaction is indivisible: if any part fails, the entire transaction is rolled back so the database is never left in a partially updated state.

Correct Answer: Committed changes survive system failures

Durability guarantees that once a transaction commits, its changes are permanently recorded and will survive crashes or power loss, typically through write-ahead logging.

Correct Answer: Undoing the changes made by an uncommitted transaction

ROLLBACK reverses all changes made during the current uncommitted transaction, returning the database to its state before the transaction began.

Correct Answer: Permanently saves the changes made in the current transaction

COMMIT makes all changes within the current transaction permanent and visible to other sessions, ending the transaction successfully.

Correct Answer: Two or more transactions each waiting for locks the other holds

A deadlock occurs when transactions each hold a lock the other needs, so none can proceed. The database detects this and terminates one transaction to break the cycle.

Correct Answer: Controlling concurrent access to preserve data consistency

Locking manages concurrent access so simultaneous transactions do not interfere with each other, preserving consistency at the cost of potential contention.

Correct Answer: Precompiled SQL code stored in the database and executed on demand

A stored procedure is a named set of SQL statements stored in the database. It promotes reuse, reduces network traffic, and can encapsulate business logic and security.

Correct Answer: A virtual table defined by a stored query

A view is a named query that behaves like a virtual table. It simplifies complex queries and can restrict which columns or rows users can access.

Correct Answer: Code that executes automatically in response to a table event

A trigger is procedural code that fires automatically when a specified event occurs on a table, such as an INSERT, UPDATE, or DELETE, often used for auditing or enforcing rules.