Database Planning And Deployment practice questions

First Normal Form (1NF)

Why it fails: 1NF focuses on atomicity and removing repeating groups.

Boyce-Codd Normal Form (BCNF)

Why it fails: BCNF addresses more complex redundancy issues involving overlapping candidate keys.

Third Normal Form (3NF)

Why it fails: 3NF addresses transitive dependencies.

Implementing a striped RAID 0 volume

Why it fails: RAID 0 improves speed but does not provide the logical separation required for data and logs.

Enabling FileStream on the target volume

Why it fails: FileStream is for storing BLOB data outside the database file, not for separating log files.

Increasing the recovery model to Full

Why it fails: The recovery model defines how logs are maintained, not where they are stored.

A database trigger

Why it fails: Triggers are typically used for audit logging or data integrity, not row-level filtering.

A stored procedure

Why it fails: Stored procedures don't automatically enforce security on base table SELECT statements.

A database view

Why it fails: Views can restrict access, but they do not provide the same centralized policy enforcement as RLS.

Using a shared table with a TenantID column

Why it fails: While common, this is less secure than schema isolation because it relies on code-level filtering.

Creating separate database instances for every tenant

Why it fails: This is often too resource-intensive and expensive for many multi-tenant scenarios.

Implementing row-level security on all tables

Why it fails: RLS is a valid secondary control, but schema-level isolation is a more fundamental architectural choice for multi-tenancy.

Switching to an asynchronous commit model

Why it fails: Asynchronous commit affects replication, not local lock contention.

Increasing the memory allocation for the buffer pool

Why it fails: Memory increases don't resolve row-level lock contention.

Dropping all non-clustered indexes

Why it fails: Dropping indexes would cause severe performance degradation for queries.

Testing of backup restoration procedures

Why it fails: While good practice, it is not a component of the backup plan itself, but a validation step.

Automatic database schema updates

Why it fails: Schema management is a deployment task, not a backup task.

Installation of the latest OS patches

Why it fails: OS patching is security maintenance, not a backup task.

Full recovery model enabled

Why it fails: This is a log management setting, not a hardware redundancy feature.

SQL Server Always On

Why it fails: This provides database-level failover, but not physical storage controller redundancy.

Database partitioning

Why it fails: Partitioning is for performance and manageability, not hardware fault tolerance.

Set the synchronous commit mode to 'Asynchronous' on all nodes.

Why it fails: Asynchronous commit governs data synchronisation and potential data loss, not read access; it does nothing to enable read-only routing on the secondary. It is tempting because asynchronous replicas are often readable, and it would be correct when the requirement is to minimise primary transaction latency across distant replicas.

Set to 'Yes' and force all writes through the secondary replica.

Why it fails: A readable secondary is read-only; it cannot accept writes, so forcing writes through it contradicts Always On's replication model and breaks the workload. It is tempting because offloading traffic from the primary is the stated goal, and it would be correct if the requirement were to offload read-only queries only.

Set to 'No' and route traffic via a secondary listener.

Why it fails: Setting Readable Secondary to No disables read access entirely, so the secondary cannot serve the read-only workloads the scenario requires. It is tempting because a secondary listener still routes connections, and this configuration would be correct when the secondary exists purely for high availability and disaster recovery.

Write barriers enabled on the filesystem mount

Why it fails: Write barriers enforce ordering of journal commits and can improve crash consistency, but they do not guarantee atomic 8 KB writes on filesystems that use block sizes smaller than the database page. Barriers alone still allow torn pages if the underlying filesystem writes in 4 KB sectors, so they are insufficient for the stated concern.

Data checksumming at the filesystem layer

Why it fails: Filesystem-level data checksums detect silent corruption but do not prevent torn 8 KB page writes during a crash. PostgreSQL already applies page checksums optionally within the cluster, so enabling a second checksum layer adds CPU overhead without addressing the torn-page risk described. The administrator needs atomic write guarantees, not detection of corrupted data after the fact.

Full-page writes enabled in the PostgreSQL cluster

Why it fails: Full-page writes protect against torn pages after a crash by logging whole pages on first modification after a checkpoint, but they increase WAL volume and do not configure the filesystem itself. The scenario asks specifically about filesystem configuration to avoid partial-page writes, so this PostgreSQL-level setting is not the filesystem safeguard being requested.

Set the buffer pool size to 100% of total RAM.

Why it fails: Allocating 100% of RAM to the buffer pool leaves no memory for the operating system, which can cause severe swapping, out-of-memory errors, and system crashes. This is never recommended, as the OS requires memory for its own operations, file system caching, and other essential services. It would degrade performance drastically.

Set the buffer pool size to 80% of total RAM.

Why it fails: Setting the buffer pool to 80% of total RAM may leave insufficient memory for the operating system and other processes, leading to swapping and performance degradation. Best practice is to allocate a moderate percentage, such as 50-70%, depending on the workload and available memory. This option over-allocates and risks system instability.

Set the buffer pool size to 25% of total RAM.

Why it fails: Allocating only 25% of RAM (16 GB) to the buffer pool may underutilize available memory, leading to more disk I/O and slower query performance. While it is safe for the OS, it does not optimize caching for a high-traffic OLTP database. A higher allocation, such as 50%, is typically more effective.

Configure the remote secondary with asynchronous commit and automatic failover

Why it fails: Asynchronous commit avoids delaying the primary, but it permits the remote secondary to lag behind. Promoting an asynchronous secondary during a primary failure risks losing committed transactions, which contradicts the requirement that failover must occur without data loss in this scenario.

Configure the remote secondary with synchronous commit and automatic failover

Why it fails: Synchronous commit guarantees zero data loss, but it forces the primary to wait for the remote secondary to harden the log before acknowledging commits. That introduces cross-data-center latency into every transaction, directly violating the requirement that the remote secondary must not delay commits on the primary.

Configure the remote secondary with synchronous commit and manual failover

Why it fails: Synchronous commit still forces the primary to wait for the remote replica to acknowledge each commit, so commit latency is affected. Manual failover also fails to meet the implied need for a controlled promotion path without data loss, and it does not solve the latency problem the scenario describes.

Enable sharding on the primary collection before deployment

Why it fails: Sharding addresses horizontal scaling of data volume and write throughput, not read distribution or data center survivability. Enabling sharding adds significant operational complexity and requires config servers and mongos routers, which are unnecessary for the stated goals of read offloading and single-data-center failure tolerance.

Configure a majority write concern on all application writes

Why it fails: Majority write concern ensures writes are acknowledged by a majority of replica set members, improving durability, but it does not address the read-distribution requirement. It also increases write latency, which is not requested in this scenario, so it is not one of the two configurations the administrator should implement.

Deploy an odd number of voting members across at least three data centers

Why it fails: An odd number of voting members is good practice for election quorum, but the scenario requires surviving the loss of a single data center, not necessarily three. Distributing voting members across three data centers exceeds the stated requirement and adds unnecessary operational complexity and cost.

Implement a write-behind cache for frequently updated tables.

Why it fails: A write-behind cache defers writes to the database, which can improve write performance but introduces a risk of data loss if the cache fails before flushing. For OLTP workloads requiring immediate consistency, this is unacceptable. It may be suitable for some analytical workloads, but not for frequently updated tables in a mixed environment.

Implement a cache eviction policy based on Least Recently Used (LRU).

Why it fails: While LRU is a common and effective eviction policy, it is not a caching strategy in itself. The question asks for caching strategies, not eviction policies. LRU can be used with either read-through or write-through caches, but it does not address the specific needs of mixed OLTP and reporting workloads on its own.

Implement a distributed cache across multiple nodes.

Why it fails: A distributed cache can improve scalability and availability, but it is not specifically tailored to the mixed workload requirements of consistency and read performance. It is an architectural choice rather than a caching strategy for handling both OLTP and reporting. The core strategies are read-through and write-through, which address data access patterns.

Enable innodb_dedicated_server to auto-tune buffer pool and log sizes

Why it fails: innodb_dedicated_server automatically sizes the buffer pool, log file, and flush method based on detected memory, which can improve performance but does not specifically change the per-commit flush semantics. It also assumes the server is dedicated to MySQL, which may not match the deployment, and it does not target the described I/O pattern.

Set innodb_flush_log_at_trx_commit to 0

Why it fails: Setting innodb_flush_log_at_trx_commit to 0 flushes the log to disk only once per second and does not flush at commit, which reduces I/O, but it risks losing up to one second of committed transactions on a crash. The scenario requires reducing I/O while still committing, and a value of 2 achieves that with a different durability trade-off.

Increase innodb_log_file_size to reduce checkpoint frequency

Why it fails: Larger redo log files reduce checkpoint frequency and can smooth I/O, but they do not change the per-commit flush behavior that is causing the bottleneck. The administrator's stated goal is to avoid forcing a log flush to disk on every commit, which this setting does not address.

Write concern w:0 with read concern available, so the application never waits for a server response.

Why it fails: Fire-and-forget writes return no error information and can be lost silently during a failover or network partition, which is unacceptable for customer-facing transactional data. Read concern available can return orphaned documents from a secondary that has not caught up. This setting favors throughput at the cost of both durability and read consistency.

Write concern w:1 with read concern local, so writes acknowledge as soon as the primary applies them.

Why it fails: Acknowledging on the primary alone means a failover can roll back writes that had not replicated, and read concern local can return data that is later undone. If the primary's data center is lost, any write not yet copied to the surviving secondaries disappears. This setting maximizes speed but does not provide the durability the customer-facing application implies.

Write concern w:5 with read concern linearizable, so every write reaches all members before acknowledging.

Why it fails: Requiring all five members to acknowledge means a single unavailable secondary blocks every write, so the loss of one data center stops the application entirely. Read concern linearizable adds further latency by confirming the read against the primary and a majority. This combination trades availability for an unnecessary guarantee and directly conflicts with the stated goal.

Configure a RAID 1 array of solid-state drives to provide redundancy and improve write performance.

Why it fails: RAID 1 mirrors data across two drives, which provides redundancy but does not increase sequential read throughput beyond what a single drive can deliver in a simple mirror. It also does not improve write performance; writes must be duplicated to both drives. For a reporting workload dominated by large sequential reads, RAID 1 is not the best choice.

Configure a single large solid-state drive to avoid RAID overhead and simplify management.

Why it fails: A single drive limits sequential read throughput to that one device and provides no redundancy. While it simplifies management, it does not maximize performance for large analytical scans, and a single drive failure would cause an outage. This configuration does not meet the performance goal of the reporting workload.

Configure a RAID 5 array of solid-state drives to balance read performance with parity protection.

Why it fails: RAID 5 offers good read performance and parity protection, but write performance suffers due to parity calculation, and read throughput is generally lower than RAID 0 because of parity overhead. For a read-heavy analytical workload where maximum sequential read speed is the priority, RAID 5 is not the optimal choice.

Configure Oracle Data Guard in maximum availability mode with a physical standby database.

Why it fails: Oracle Data Guard provides disaster recovery and can be used for rolling upgrades in some cases, but it does not inherently support zero-downtime patching of the primary database. Maximum availability mode ensures zero data loss but still requires downtime for primary patching unless combined with other features.

Configure Oracle Real Application Clusters (RAC) with a single instance.

Why it fails: Oracle RAC with a single instance does not provide the benefits of RAC, such as high availability and rolling upgrades. RAC requires multiple instances to allow rolling patches and instance-level failover. A single instance is essentially a standalone database and cannot meet the 24/7 requirement.

Configure Oracle Active Data Guard with a logical standby database for rolling upgrades.

Why it fails: Oracle Active Data Guard with a logical standby can be used for rolling upgrades, but it is more complex and may not support all data types. It also does not provide the same level of seamless high availability as RAC for continuous application availability during patching.

Use the mysql_native_password authentication plugin to ensure secure authentication.

Why it fails: The mysql_native_password plugin is deprecated in MySQL 8.0 and does not provide encryption for data at rest or in transit. It only handles authentication and is less secure than caching_sha2_password. It does not meet the encryption requirements.

Enable transparent data encryption (TDE) using the built-in MySQL Enterprise Encryption functions.

Why it fails: MySQL does not have a built-in feature called transparent data encryption (TDE) in the same way as other databases. The MySQL Enterprise Encryption functions provide cryptographic functions for application use, not transparent tablespace encryption. This option is misleading and does not meet the requirement for data at rest encryption.

Enable binary log encryption using the keyring plugin.

Why it fails: Binary log encryption encrypts the binary logs, which contain changes to data. While it adds protection for data at rest in logs, it does not encrypt the actual tablespace data and does not address data in transit. It is not one of the two required configurations for this scenario.

It prevents the insertion of duplicate records

Why it fails: Unique constraints and primary keys prevent duplicates, but the type of key does not change this capability.

It ensures that the database complies with ACID requirements

Why it fails: ACID compliance is a function of transaction management, not primary key selection.

It automatically creates a clustered index on the table

Why it fails: While often used as a clustered index, a surrogate key does not automatically create one without an explicit definition.

Composite partitioning

Why it fails: Composite partitioning combines two methods, such as range then hash, so the hash sub-partitions scatter old records and prevent dropping them as one unit. It suits balancing range queries with even distribution, not straightforward age-based archival.

List partitioning

Why it fails: List partitioning groups rows by explicit discrete values, so a continuous date range such as older than five years cannot be expressed as a single list. It suits categorical columns like region or status, not rolling time-based archival.

Hash partitioning

Why it fails: Hash partitioning distributes rows evenly across partitions by hashing the key, so records older than five years are scattered and cannot be dropped as a unit. It suits load balancing across equally sized partitions, not time-based archival.

Round-robin partitioning

Why it fails: Round-robin partitioning distributes rows evenly across partitions without reference to any column value, so records older than five years cannot be isolated or dropped as a unit. It suits balancing I/O across identical storage in symmetric workloads, where even spread matters and no range-based archival is required.