D330 Data Systems Administration - Set 2 - Part 2

Correct Answer: Granting each account only the permissions required for its function

Least privilege limits each database account to the minimum permissions needed, reducing the damage possible from compromised credentials or user error.

Correct Answer: GRANT

The GRANT statement assigns specific privileges, such as SELECT or INSERT, on database objects to users or roles. REVOKE removes previously granted privileges.

Correct Answer: A named collection of permissions that can be assigned to users

A role groups related permissions under one name. Assigning users to roles simplifies administration and makes access rights consistent and easier to audit.

Correct Answer: An attack inserting malicious SQL through untrusted application input

SQL injection exploits applications that build queries from unvalidated user input, allowing attackers to read, modify, or destroy data they should not access.

Correct Answer: Using parameterized queries or prepared statements

Parameterized queries separate SQL code from data values so user input is never interpreted as executable SQL, which reliably prevents injection attacks.

Correct Answer: Encrypting stored data files so they are unreadable without the key

Encryption at rest protects stored database files and backups, so an attacker who obtains the physical media or files cannot read the data without the decryption key.

Correct Answer: Protecting data as it travels between client and database server

Encryption in transit, typically using TLS, protects data moving across the network between application and database from interception or tampering.

Correct Answer: Recording who accessed or changed data and when

Auditing logs database activity such as logins, queries, and data modifications, supporting security investigations, compliance requirements, and accountability.

Correct Answer: The database's chosen strategy for executing a query

An execution plan shows how the optimizer will retrieve data, including index usage and join methods, and is the primary tool for diagnosing slow queries.

Correct Answer: No suitable index exists for the filter condition

When no index supports the WHERE clause, the database must read every row. Adding an appropriate index typically eliminates the scan and improves performance dramatically.

Correct Answer: Adjusting queries, indexes, and configuration to improve responsiveness

Performance tuning analyzes workload, queries, indexing, and server configuration to reduce response times and resource consumption for the most impactful operations.

Correct Answer: Reusing a set of established database connections across requests

Connection pooling maintains a reusable set of open connections, avoiding the significant cost of establishing a new connection for every request and improving scalability.

Correct Answer: Splitting data horizontally across multiple servers

Sharding partitions data horizontally across multiple database servers, each holding a subset of rows, allowing the system to scale beyond a single server's capacity.

Correct Answer: Dividing a large table into smaller physical segments

Partitioning splits a large table into segments, often by date range, so queries scan only relevant partitions and maintenance operations can target individual segments.

Correct Answer: Analytical queries over large volumes of historical data

A data warehouse is designed for analytical processing, aggregating large volumes of historical data from multiple sources to support reporting and business intelligence.

Correct Answer: OLTP handles many short transactions; OLAP handles complex analytical queries

OLTP systems process high volumes of short read-write transactions for daily operations, while OLAP systems run complex read-heavy queries over large datasets for analysis.

Correct Answer: Extract, Transform, Load: moving and preparing data between systems

ETL extracts data from source systems, transforms it into a consistent target format, and loads it into a destination such as a data warehouse.

Correct Answer: Flexible schemas and horizontal scaling for large or varied data

NoSQL databases offer flexible or schema-less data models and scale horizontally, suiting large volumes of semi-structured data where strict relational structure is unnecessary.

Correct Answer: A database storing data as flexible documents such as JSON

Document databases store records as self-contained documents, typically JSON or BSON, allowing varied structures between records without a fixed table schema.

Correct Answer: A system cannot simultaneously guarantee consistency, availability, and partition tolerance

The CAP theorem holds that during a network partition a distributed system must choose between consistency and availability, since all three cannot be fully guaranteed at once.