Which solution will meet these requirements?
Create a local secondary index (LSI) with subject_name as the partition key and top_score as the sort key.
Create a local secondary index (LSI) with top_score as the partition key and student_id as the sort key.
Create a global secondary index (GSI) with subject_name as the partition key and top_score as the sort key.
Create a global secondary index (GSI) with subject_name as the partition key and student_id as the sort key.
Explanations:
A local secondary index (LSI) allows for an alternate sort key on the same partition key. Since the goal is to retrieve the top score for each subject, usingsubject_nameas the partition key in an LSI does not provide a unique way to fetch the highest scores across subjects efficiently. It would only allow sorting ontop_scorewithin each student_id.
This option creates an LSI withtop_scoreas the partition key andstudent_idas the sort key. However,top_scoreis not a good choice for a partition key because it is not unique and does not effectively partition data for retrieval. This structure would not allow efficient queries for the top scores by subject.
A global secondary index (GSI) withsubject_nameas the partition key andtop_scoreas the sort key allows efficient querying for the highest scores across different subjects. This enables the application to retrieve all top scores for each subject quickly, as it groups items by subject and sorts them by score.
This option creates a GSI withsubject_nameas the partition key andstudent_idas the sort key. While it allows retrieval of student IDs by subject, it does not facilitate fetching the top scores directly sincestudent_iddoes not provide a sorting mechanism for scores. Therefore, it does not meet the requirement for displaying top scores efficiently.
It seems to me that the answer is:
Create a global secondary index (GSI) with subject_name as the partition key and top_score as the sort key.