Fundamentals of Primary, Partition, and Clustering Keys Quiz Quiz

Explore the essential concepts of primary keys, partition keys, and clustering keys used in database design and data modeling. This quiz covers key definitions, differences, and practical uses to strengthen your understanding of unique identification and organization of records.

1. Primary Key Uniqueness

   In a relational database table, what is the main purpose of a primary key column such as 'student_id' in a 'Students' table?

   1. It defines the display order of rows.
   2. It holds foreign key references from another table.
   3. It guarantees each row in the table is uniquely identified.
   4. It allows duplicate values across rows.

   Explanation: A primary key uniquely identifies each row, ensuring no two rows have the same value in the primary key column. Allowing duplicate values would defeat its purpose (option B). Determining display order (option C) is not its role, and holding foreign key references (option D) is done by foreign keys, not primary keys.

2. Partition Key Functionality

   What is the function of a partition key in a distributed database storing 'orders' data?

   1. It determines which node or partition the data is stored on.
   2. It provides a description for the data entry.
   3. It is always used as a secondary key for sorting.
   4. It generates a random number for each order.

   Explanation: The partition key is used to distribute or locate data across different nodes or partitions. It does not randomly generate numbers (option B), nor offer a description (option C). Although used for partitioning, it is not specifically a sorting key (option D).

3. Clustering Key Usage

   When designing a composite primary key, what role does the clustering key play in an 'events' table with 'event_date' and 'location'?

   1. It determines the order of records within a partition.
   2. It encrypts data automatically.
   3. It prevents null values in all columns.
   4. It guarantees all records are unique globally.

   Explanation: A clustering key dictates how rows are ordered logically within a partition, aiding in efficient querying. It does not enforce null constraints (option B), uniqueness globally (option C), or encryption (option D), which are managed by other mechanisms.

4. Primary Key Characteristics

   Which characteristic must be true for any primary key defined in a table such as 'Employee'?

   1. It cannot contain null values.
   2. It is optional for the table.
   3. It can only reference another table.
   4. It must always be a numeric field.

   Explanation: Primary keys must be non-null to ensure every row is identifiable. They are not required to be numeric (option B). References to other tables are handled with foreign keys (option C), and primary keys are not optional if data integrity is essential (option D).

5. Partition and Clustering Key Difference

   In a table with a composite primary key consisting of 'user_id' and 'order_id', what is typically the difference between the partition key and the clustering key?

   1. Both keys always serve the same function.
   2. Partition keys are used solely for sorting rows.
   3. The partition key determines data distribution, while the clustering key determines row order within the partition.
   4. Clustering keys are used to ensure global uniqueness across tables.

   Explanation: Partition keys distribute data across storage nodes, while clustering keys sort data within partitions. They do not serve the same function (option B), clustering keys do not manage global uniqueness (option C), and partition keys are not just for sorting (option D).

6. Identifying the Best Primary Key Choice

   Which column(s) would be the best choice for a primary key in a 'Countries' table with columns: name, code, population, and region?

   1. The 'code' column, such as 'US' or 'FR'.
   2. The 'region' column.
   3. The 'name' and 'population' columns combined.
   4. The 'population' column.

   Explanation: Country codes are globally unique identifiers, fitting the primary key requirement. Population values are not unique (option B), regions can repeat (option C), and mixing name with population can't guarantee uniqueness either (option D).

7. Handling Duplicate Values

   What will most commonly happen if you try to insert two rows with the same primary key value in a database table?

   1. The database randomly selects one row to keep.
   2. It silently updates the first row with the new data.
   3. Both rows are inserted without any warnings.
   4. The database will reject the second row and throw an error.

   Explanation: Primary keys must be unique, so duplicates result in an error. Databases do not allow silent acceptance (option B), random selection (option C), or silent updates without notice (option D), as these undermine data integrity.

8. Purpose of Composite Keys

   Why might a table for 'CourseEnrollments' use a composite primary key made up of 'student_id' and 'course_id'?

   1. To ensure each student can enroll in a course only once.
   2. To limit the number of students per course.
   3. To guarantee course_id is unique across all enrollments.
   4. To allow duplicate rows for the same enrollment.

   Explanation: A composite key of student_id and course_id ensures the same student cannot enroll in the same course more than once. It does not make course_id unique overall (option B). The key itself doesn't limit student counts (option C), nor does it permit duplicates (option D).

9. Incorrect Usage of Clustering Key

   Which scenario demonstrates an incorrect use of a clustering key in a table partitioned by 'user_id'?

   1. Using a nearly constant value like 'status' as the clustering key.
   2. Using 'order_number' to order transactions for each user.
   3. Utilizing 'message_id' to sort messages sent by each user.
   4. Setting 'timestamp' as the clustering key to order user activity.

   Explanation: A clustering key should provide variety to order data meaningfully; a constant value like 'status' is unsuitable as it cannot effectively sort or organize data. 'Timestamp', 'order_number', and 'message_id' all introduce variety and usefulness for sorting (options B, C, and D).

10. Difference from Foreign Key

    How does a primary key differ from a foreign key in the context of database relationships?

    1. Both keys are always required in every table.
    2. A foreign key is always made of numeric values, but a primary key is not.
    3. A primary key uniquely identifies a row, while a foreign key references a primary key in another table.
    4. A primary key allows duplicate values; a foreign key does not.

    Explanation: The primary key provides unique identification within its table, while a foreign key establishes a link to a primary key in another table to maintain relational integrity. Both keys are not required everywhere (option B), and primary keys do not allow duplicates (option C). Numeric-only requirements (option D) are not accurate for either key.