Data Normalization Strategy

To bulk import a question bank into a database via a text file, the raw data must first be converted into a delimited format suitable for parsing. We will use the hash symbol (#) as the field delimiter. The final structure for each record should correspond to the following schema:

QuestionContent#OptionA#OptionB#OptionC#OptionD#CorrectAnswer

Depending on the source format, a series of string replacements is required to standardize the data. The following logic uses Java to transform raw input into the target structure.

public class QuestionParser {
    public static void main(String[] args) {
        // Simulated raw input containing the question bank
        String rawData = "...";

        // Step 1: Normalize line breaks to the target delimiter
        String delimiterNormalized = rawData.replace("\n", "#");

        // Step 2: Remove specific metadata tags (e.g., 'Answer:')
        String cleanedData = delimiterNormalized.replace("Answer:", "");

        // Step 3: Re-introduce line breaks for readability/formatting logic
        // This ensures options A, B, C, D are distinctly separated in the text file
        String formattedOutput = cleanedData
                .replace("A#", "A\n")
                .replace("B#", "B\n")
                .replace("C#", "C\n")
                .replace("D#", "D\n");

        System.out.println(formattedOutput);
    }
}

Database Import Procedure

Once the data is processed:

1. Copy the output from the console and save it as a .txt file (e.g., questions.txt).
2. Open the file and add a header row at the very top: ``` question_text#option_a#option_b#option_c#option_d#answer_key

3. Open Navicat and connect to your target database.
4. Use the Import Wizard to select the text file.
5. In the delimiter settings, specify # as the field separator.
6. Set the target table name (e.g., quiz_bank). Navicat will typically create the table automatically based on the headers if it does not exist.
7. Execute the import to populate the table.

Adding a Primary Key Column

To faciliatte random selection or indexing, an auto-incrementing integer ID is required. This can be added after the data import using SQL DDL statements.

-- Add the 'id' column as the first column
ALTER TABLE quiz_bank ADD COLUMN id INT FIRST;

-- Configure the column as the Primary Key with Auto Increment
ALTER TABLE quiz_bank
MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

Executing the above statements will sequentially number every row in the table from 1 to N, effectively indexing the imported questions.