The Foundations of Schema Design in CockroachDB Exam Answers:
Quiz 1:
Question 1: Which data type would be useful if you wanted to store the time of day with an offset from UTC?
- now()
- TIME
- TIMESTAMPTZ
Question 2: When working with the DECIMAL data type, which of the following represents the count of digits to the right of the decimal point?
- Scale
- Measure
- Precision
Question 3: In CockroachDB, which of these data types is useful if you need to limit the number of characters stored in a given column?
- STRING(n)
- BYTES
- VARCHAR
Quiz 2:
Question 1: Which feature of CockroachDB lets the database set the current time rather than requiring the client to set this information?
- TIMESTAMPTZ
- TIME
- now()
Question 2: Which feature of CockroachDB helps sort STRING values based on language-and country-specific rules?
- STRING(n)
- GEOGRAPHY
- Collation
Question 3: Which data type would be useful if you were looking to store airport locations in North America in latitude and longitude?
- GEOGRAPHY
- TIMETZ
- GEOMETRY
Quiz 3:
Question 1: Which of the following would be a good use case for a JSON column?
- You need to import JSON data from a third party API
- You want to store a record of every single video game action ever taken by a player in one JSON object
- You’re storing data from an app and you know all of the fields and data types ahead of time
Question 2: Which of the following would be a good use case for an ARRAY column?
- You need to store 3-dimensional coordinates for player locations in a game in sub-ARRAYs like so: [ [23, 18, 5], [19, 4, 10], [1, 25, 8] ]
- You need to store a user’s name as a STRING, age as an INT, and test score as a FLOAT all together in one column
- You need to store a list of phone numbers associated with a user’s account
Question 3: What’s one advantage of using an inverted index?
- They can help avoid the need for full table scans when querying for a matching key: value pair in a JSON column.
- Inverted indexes are extremely compact, increasing the efficiency of writing to a JSON column.
- They increase the efficiency of queries that use less than or greater than
Question 4: What is the purpose of a computed column?
- They expose data generated from other columns by an expression.
- They store a homogeneous list of data in every row.
- They separate each field in a JSON object into a unique index entry.
Quiz 4: Hash
Question 1: When should you hash shard an index? Check all that apply.
- When all candidate index keys are monotonically increasing
- When you’re using a composite index with a TIMESTAMPTZ column as the second column
- When you’re running a 3-node cluster but are ready to scale out
- When there are many null values in the column
- When the column has only a few possible values (such as a BOOLEAN)
Final Assessment:
Question 1: You have a table that stores status updates from an autonomous vehicle with the following schema:
moving BOOLEAN,
check_in TIMESTAMPTZ,
status JSON
Here are the first three rows of this table:
moving | check_in | status
——————+—————————+——————————————-
TRUE | 2021-07-10 06:19:03-03:00 | ‘{“velocity”: “10”, “heading”: “145.5”}’
TRUE | 2021-07-10 06:19:07-03:00 | ‘{“velocity”: “12”, “heading”: “147”}’
TRUE | 2021-07-10 06:19:11-03:00 | ‘{“velocity”: “15”, “heading”: “149”}’
Of the following options, what would be the best primary key for this table?
- Use the status column as the primary key
- Create a hash sharded primary key with the check_in column
- Create a compound primary key that includes the status column as the leading column and the check_in column
Question 2: When working with the DECIMAL data type, which of the following represents the maximum count of digits both to the left and right of the decimal point?
- Precision
- Scale
- Accuracy
- (n)
Question 3: You have a table that stores a history of alerts received for a monitoring system with the following schema:
alert_code INT,
alert_time TIMESTAMPTZ,
device_id STRING
Here are the first three rows of this table:
alert_code | alert_time | device_id
—————–+—————————+—————–
404 | 2021-08-26 12:39:35-05:00 | A300-432
502 | 2021-08-26 12:39:46-05:00 | A300-432
404 | 2021-08-26 12:39:48-05:00 | C202-859
Of the following options, what would be the best primary key for this table?
- Create a compound key with device_id as the leading column and the alert_time column
- Create a hash sharded primary key with just the alert_time column
- Create a separate UUID column to use
Question 4: You have a 7 node cluster with 1,000,000 rows between 9 tables, one of which has a TIMESTAMP column. How many buckets should the hash sharded index on that column have?
- 3
- 5
- 7
- 9
- 11
Question 5: Which of these is a benefit of using the now() function for a default column value?
- It can display a TIMESTAMP with more digits than the application
- It provides accuracy through the implementation of atomic clocks on the cluster
- It allows the database to be the source of truth for date and time
Question 6: What data type or feature of CockroachDB type would be useful if you are looking to display green space in a city as polygons on a city map, which is small enough that you don’t need to worry about the curvature of the Earth?
- GEOMETRY
- Collation
- GEOGRAPHY
Question 7: What data type or feature of CockroachDB might you use instead of a definite table schema if you were experimenting with (and frequently revising) a table’s structure during application development?
- The EXPERIMENTAL flag
- BYTES
- JSON
Question 8: Which of the following should be avoided (or otherwise mitigated) when choosing an index key with CockroachDB?
- Letting the database assign UUIDs to a row, which are then used as the primary key
- Using a compound index key
- Using the TIMESTAMP data type as the first column of an index
Question 9: What is a feature of CockroachDB that you might use if you needed to regularly find purchases less than $100 and the purchase information was stored in a field in a JSON column?
- Computed Column with a secondary index
- A regular secondary index on the JSON column
- Inverted Index
Question 10: Which feature of CockroachDB would be helpful if you were building a language learning app and needed a way to sort words from a given language based on the language’s alphabetical sorting rules?
- STRING
- Collation
- GEOGRAPHY
- VARCHAR