PCDE Design and implement database schemas Practice Test 5 — 15 Questions

Question 1

A Cloud SQL for PostgreSQL database experiences lock contention during heavy concurrent writes on a single table. Which schema design change can most effectively reduce contention?

Accepted Answer

Partition the table by a key that spreads write load. Option C is correct: table partitioning splits data into smaller physical pieces, reducing lock conflicts because writes target different partitions. Option A (read replicas) does not reduce write contention. Option B (connection pooling) improves connection management but not locking. Option D (materialized views) does not affect write locking.

Answer

Deploy read replicas to offload reads

Answer

Use a connection pooler like PgBouncer

Answer

Create materialized views for read queries

Question 2

A Cloud Spanner database needs to add a column 'discount' to the 'Products' table without any downtime. The table is actively used. What is the correct approach?

Accepted Answer

Execute ALTER TABLE Products ADD COLUMN discount FLOAT64. Option A is correct: Spanner supports online schema updates via ALTER TABLE ADD COLUMN, which does not block reads or writes. Option B (new table and copy) would require downtime or at least double-write logic. Option C (secondary index) is unrelated. Option D (generated column) could be used but is unnecessary.

Answer

Create a new table with the column and copy data over

Answer

Create a secondary index that includes the new column

Answer

Define a generated column based on an existing column

Question 3

Which TWO are best practices for designing a Cloud Spanner schema?

Accepted Answer

Use commit timestamp columns to track row versions. Option A is incorrect because monotonically increasing keys cause hotspotting. Option B is correct: interleaved tables optimize parent-child joins. Option C is incorrect: secondary indexes are often needed for non-primary key queries. Option D is correct: commit timestamp columns enable versioning without storing explicit timestamps. Option E is incorrect: storing all data in a single row leads to large rows and contention.

Answer

Avoid secondary indexes to keep writes faster

Answer

Use monotonically increasing primary keys

Answer

Store all related data in a single row to avoid joins

Question 4

Which THREE are considerations when designing a schema for Cloud Firestore?

Accepted Answer

Use subcollections to organize related data. Option A is correct: denormalization is common in Firestore to avoid expensive reads. Option B is not a best practice: deeply nested maps are hard to query and can cause contention. Option C is correct: large arrays cause document bloat and index limits. Option D is correct: subcollections allow scalable data organization. Option E is incorrect: Firestore supports transactions but they are not the only way to ensure consistency.

Answer

Use nested maps for deeply structured data

Answer

Always use transactional writes to ensure consistency

Question 5

Which TWO techniques can help avoid hot spotting in a Cloud Spanner table?

Accepted Answer

Add a hash of the primary key as the first part of the key. Option A is correct: hash prefix distributes writes across splits. Option B is correct: using a random prefix also spreads writes, though hash prefix is more common. Option C is incorrect: monotonically increasing keys cause hotspotting. Option D is incorrect: interleaving does not prevent hotspotting on the parent key. Option E is incorrect: secondary indexes can cause their own hotspotting.

Answer

Use a monotonically increasing integer as the key

Answer

Use interleaved tables to distribute writes

Answer

Create a secondary index on a high-cardinality column

Question 6

Based on the exhibit, what is the primary key of the Readings table?

Accepted Answer

(SensorId, Timestamp). Option A is correct: the DDL shows PRIMARY KEY (SensorId, Timestamp DESC). Option B is incorrect because SensorType is not a column. Option C is incorrect because there is no ReadingsId column. Option D is incorrect as the key includes both columns.

Answer

(SensorId, SensorType)

Answer

(SensorId)

Answer

(ReadingsId)

Question 7

Refer to the exhibit. Which BigQuery SQL query correctly flattens the items into rows?

Accepted Answer

SELECT * FROM orders CROSS JOIN UNNEST(items) AS items. Option A is correct: using CROSS JOIN UNNEST(items) expands the repeated record into separate rows. Option B only filters null items. Option C is invalid syntax. Option D is missing CROSS JOIN.

Answer

SELECT * FROM orders WHERE items IS NOT NULL

Answer

SELECT * FROM orders, UNNEST(items) AS items

Answer

SELECT * FROM orders INNER JOIN items ON true

Question 8

Refer to the exhibit. What is the most likely performance issue with this schema?

Accepted Answer

Hotspotting on TransactionId due to monotonically increasing values. Option A is correct: TransactionId is monotonically increasing (likely auto-generated), causing write hotspotting on the last split. Option B is incorrect because UserId is not the primary key. Option C is incorrect: there are no secondary indexes shown. Option D is incorrect because hotspotting is a likely issue.

Answer

No performance issue; the schema is optimal

Answer

Hotspotting on UserId due to frequent queries

Answer

Too many secondary indexes causing write amplification

Question 9

A company is designing a Cloud Spanner database for a global user base. They need to support strong consistency and low-latency reads across multiple regions. Which schema design practice is most important?

Accepted Answer

Use interleaved tables to co-locate related rows that are queried together.. Option D is correct because interleaving tables that are frequently joined together into a parent-child hierarchy allows Spanner to co-locate the data, reducing cross-node communication and latency. Option A is wrong because using a single monolithic table would not scale. Option B is wrong because denormalization can increase write latency and complexity. Option C is wrong because indexing all columns leads to unnecessary overhead.

Answer

Denormalize data into wide tables to reduce the number of joins.

Answer

Use a single table with composite primary key to avoid joins.

Answer

Create secondary indexes on every column to optimize read queries.

Question 10

A Cloud SQL for PostgreSQL instance is used for an OLTP application. The database schema has many foreign key constraints. Which action improves write performance?

Accepted Answer

Create indexes on foreign key columns.. Option A is correct because creating indexes on foreign key columns avoids full table scans during referential integrity checks. Option B is wrong as more triggers slow writes. Option C is wrong because dropping constraints risks data integrity. Option D is wrong because increasing disk size does not directly address the performance bottleneck.

Answer

Drop all foreign key constraints.

Answer

Add more triggers to enforce integrity.

Answer

Increase the instance storage size.

Question 11

A game company uses Cloud Bigtable to store player session data. Access patterns include looking up a player's most recent sessions and scanning sessions by time range. Which row key design is most appropriate?

Accepted Answer

Use a row key of player ID followed by reversed timestamp.. Option B is correct because using reversed timestamp as part of the row key helps distribute writes and allows efficient range scans over recent data. Option A is wrong because timestamp first can cause hotspotting. Option C is wrong because sequential player IDs cause hotspotting on a single tablet. Option D is wrong because hashing alone makes range scans impossible.

Answer

Use only player ID as row key with column qualifiers for timestamps.

Answer

Prefix with timestamp and append player ID.

Answer

Use a hash of player ID as row key and store timestamps in cell versions.

Question 12

A team is migrating an on-premises MySQL database to Cloud SQL. The current schema usesMyISAM tables. What is the recommended approach?

Accepted Answer

Convert MyISAM tables to InnoDB before migration.. Option B is correct because Cloud SQL supports InnoDB which is the recommended engine for transactional workloads. MyISAM is not supported on Cloud SQL. Option A is wrong because MyISAM is not supported. Option C is wrong because simply moving the dump would fail. Option D is wrong because that would lose data integrity.

Answer

Keep the schema as is; Cloud SQL supports MyISAM.

Answer

Replicate the on-premises MySQL to Cloud SQL using Database Migration Service.

Answer

Export the database using mysqldump and import directly into Cloud SQL.

Question 13

A financial services company uses Cloud Spanner for transaction processing. They need to run analytical queries that scan large portions of the database without impacting OLTP performance. What schema design technique should they use?

Accepted Answer

Design the primary key so that analytical queries scan a small number of tablets by using interleaved tables.. Option D is correct because Cloud Spanner supports interleaved tables which allow efficient scans on parent rows without cross-node joins. Option A is wrong because secondary indexes may not be suitable for large scans. Option B is wrong because read replicas are not available in Spanner for analytical workloads. Option C is wrong because exporting to BigQuery adds latency and complexity.

Answer

Export data periodically to BigQuery and run queries there.

Answer

Create multiple secondary indexes on frequently scanned columns.

Answer

Use a read replica instance to offload analytical queries.

Question 14

A Cloud Firestore database stores documents for a mobile app. The app frequently queries for documents where a specific Boolean field is true. The field is not part of the collection group index. What should the developer do to improve query performance?

Accepted Answer

Create a composite index that includes the Boolean field and the query ordering field.. Option B is correct because creating a composite index on the Boolean field and other commonly filtered fields will allow efficient queries. Option A is wrong because single-field indexes are automatically created, but the field is Boolean so index exists but may not be sufficient. Option C is wrong because denormalization may increase complexity. Option D is wrong because adding a new field doesn't help if it's not indexed.

Answer

Add a synthetic field that combines the Boolean with a timestamp for range queries.

Answer

Denormalize the Boolean field into separate subcollections.

Answer

Rely on the automatic single-field index already created.

Question 15

A multinational corporation uses Cloud Spanner with a multi-region configuration. The schema includes a table that is updated frequently by users in two distant regions. They are experiencing high commit latencies due to distributed transactions. Which schema change would most reduce latency?

Accepted Answer

Use a table-level leader placement configuration to keep the table's splits in a single region.. Option C is correct because replicating the table across regions with leader placement can reduce the number of remote operations. Option A is wrong because interleaving does not help with distribution across regions. Option B is wrong because reducing replicas may compromise availability. Option D is wrong because horizontal scaling doesn't directly fix cross-region latency.

Answer

Reduce the number of replicas in the Spanner configuration.

Answer

Convert the table into an interleaved child of a parent table.

Answer

Increase the number of splits by using a more granular primary key.