From Datasets to Decryption: A Statistical Approach to Cryptanalysis (second homework)

From Raw Data to Distributions

The foundation of any statistical analysis is the dataset, a structured collection of data typically organized in a matrix format.
Statistical Units (U_i): these are the individual entities being studied (e.g., people, objects, events). They form the rows of the dataset, often called records.
Attributes and Variables: For each unit, we observe various characteristics, or attributes (A_i) such as eye color or height. The way we measure an attribute is called a variable (X_i). Variables can be measured on different scales, being either quantitative (numerical) or qualitative (categorical).
Structure: The columns of the dataset represent these variables (also called fields). Each cell X_ik contains the specific value of variable k for unit i.

Practical Application: Building a Database and Computing Distributions

The Database Management System (DBMS) used for this analysis is PostgreSQL. The following steps were executed to create the dataset and compute the univariate distributions.

1. Creating the “student” Table
   The main table was created with an auto-incrementing primary key (id) and attributes for a student’s first name, last name, course, and grade.
   
2. Populating the Table with Sample Data
   The table was populated with sample records to form the initial dataset. The following image displays the SQL query.
   
3. The resulting table is presented below.

The next step is to compute the univariate frequency distributions for the variables in the dataset. This process transforms the raw data from the student table into a summarized format that reveals the frequency and percentage of each unique value for a given variable. The univariate frequency distributions will be calculated for the course and grade variables. For each variable, the process involves:

1. Grouping the records by the unique values of the variable.
2. Counting the number of occurrences (frequency) for each value.
3. Calculating the relative percentage of each value.

This enables the identification of patterns, such as the most common courses or the distribution of grades across the student population. The following SQL queries will create permanent tables to store these distributions for analysis and reporting.

The SQL query for course distribution analysis is as follows:

EXPLANATION OF THE QUERY:

1. “CREATE TABLE course_distribution AS” creates a table named “course_distribution” with the results of SELECT.
2. “SELECT course, COUNT() AS frequency, ROUND(COUNT() * 100.0 / (SELECT COUNT(*) FROM student), 2) AS percentage” calculates course statistics. The ‘frequency’ attribute counts how many students are enrolled in each course. The ‘percentage’ attribute calculates the percentage relative to the total number of students.
3. “FROM student GROUP BY course ORDER BY frequency DESC“. The GROUP BY clause groups students by course, while ORDER BY sorts the results by frequency in descending order from most popular to the least.

The SELECT query returns the following result by including all attributes from the table:

EXPLANATION OF THE QUERY:

1. “CREATE TABLE grade_distribution AS” creates a table named “grade_distribution ” with the results of SELECT.
2. “SELECT grade, COUNT() AS frequency, ROUND(COUNT() * 100.0 / (SELECT COUNT(*) FROM student), 2) AS percentage” calculates grade statistics. The ‘frequency’ attribute counts how many students received each grade. The ‘percentage’ attribute calculates the percentage relative to the total number of students.
3. “FROM student GROUP BY grade ORDER BY grade“. The GROUP BY clause groups students by grade, while ORDER BY sorts the results by grade value in ascending order.

The SELECT query returns the following result by including all attributes from the table:

BIVARIATE DISTRIBUTION

A bivariate frequency distribution is subsequently computed to investigate the relationship between the course and grade variables.

This analysis reveals the joint distribution of the two variables, showing the frequency of each grade-course combination.

The following SQL query will create a contingency table that cross-tabulates these two variables:

The SELECT query returns the following result by including all attributes from the table:

Case Study: Cryptanalysis of a Caesar Cipher via Frequency Analysis

The following section implements a statistical frequency analysis using JavaScript. The analysis processes a sample text from Oscar Wilde’s ‘The Picture of Dorian Gray’, performing a case-insensitive count of alphabetical characters. The program analyzes the text and provides a frequency breakdown for each letter, offering insight into the character distribution throughout the passage. The results are presented below:

The frequency distribution derived from the sample text reveals several key patterns that align with established statistical norms of the English language.

1. Dominance of Vowels and Common Consonants: The results confirm that vowels and a limited number of consonants account for the majority of the textual data. The letters E, T, A, O, I, N are, as expected, the most frequent characters.
2. The Prevalence of ‘E’: The letter ‘E’ is by far the most common character, with a count of 2051. This is not just the highest in this sample; it is a fundamental characteristic of English and is often the most frequent letter in any substantial English text.
3. Mid-Frequency Letters: Letters like S, R, H, D, L, and U appear with a significant but moderate frequency, forming the core body of the language’s structure.
4. Rare Letters: On the other end of the spectrum, the letters Z, J, Q, and X are the least frequent. Their very low counts (7, 10, and 22 respectively) are entirely consistent with standard English usage.

When comparing the percentage distribution of each letter against standard English alphabet frequencies, the sample text demonstrates strong alignment with expected linguistic patterns. This exceptional adherence to expected frequency patterns reinforces the reliability of this statistical profile. The following table presents the complete character frequency distribution for the English language sample:

In essence, this frequency distribution represents a characteristic pattern of the English language. This predictable pattern is a powerful tool, as any transformation of the text that preserves this distribution can potentially be reversed through analysis.

Here is the JavaScript code for analyzing the frequency distribution of the plaintext:

Encryption

The plaintext is now encrypted using the Caesar Cipher, a foundational encryption technique. This method is a type of substitution cipher where each letter is shifted a fixed number of positions within the alphabet. For this implementation, a shift of 5 positions is applied. The corresponding JavaScript code is presented below:

A frequency analysis was subsequently performed on the encrypted text using the same algorithm applied to the plaintext. The results are as follows:

Frequency Analysis: Ciphertext vs Standard English Distribution

The chart displaying the percentage distributions is presented below:

Ciphertext Top Frequencies:

1. J – 12.00% (2051 occurrences)
2. Y – 8.88% (1517 occurrences)
3. F – 8.12% (1388 occurrences)
4. T – 7.93% (1356 occurrences)
5. N – 7.64% (1306 occurrences)

Standard English Top Frequencies:

1. E – 12.702%
2. T – 9.056%
3. A – 8.167%
4. O – 7.507%
5. I – 6.966%

Pattern Recognition

The ciphertext distribution reveals a systematic shift from English norms:

Ciphertext → Expected English:
J (12.00%) corresponds to E (12.702%)
Y (8.88%) corresponds to T (9.056%)
F (8.12%) corresponds to A (8.167%)
T (7.93%) corresponds to O (7.507%)
N (7.64%) corresponds to I (6.966%)

Shift Calculation

The most revealing comparison:

• J (ciphertext most frequent: 12.00%) vs E (English most frequent: 12.702%)
• Letter positions: J = 9, E = 4
• Shift = 9 – 4 = 5 positions

Rare Letter Confirmation

Ciphertext rare letters:

• O: 0.06% (should be J in English: 0.153%)
• V: 0.13% (should be Q in English: 0.095%)
• E: 0.04% (should be Z in English: 0.074%)

Pattern: All rare letters are also shifted by 5 positions from their English counterparts.

Cryptographic Conclusion

The frequency analysis conclusively demonstrates:

1. Caesar Cipher with Shift = 5 confirmed by multiple letter correspondences
2. Perfect distribution preservation – percentages maintain identical relative values

This analysis proves that simple substitution ciphers cannot hide the inherent statistical patterns of natural language, making them vulnerable to frequency analysis attacks even without knowing the original plaintext.

Let’s verify the correctness of the frequency analysis using a mathematical test – “The Chi-Squared Test”.

What is the Chi-Squared Test?

The chi-squared (χ²) test is a statistical method that measures how well an observed distribution matches an expected distribution. In cryptography, it is used to compare:

• Observed distribution: Letter frequencies in the decrypted text with a specific shift
• Expected distribution: Letter frequencies in the English language

For each letter in the alphabet, the following calculation is performed:

(OBSERVED - EXPECTED)²/ (EXPECTED)

Where:

• OBSERVED = Actual count of each letter in the text decrypted with a specific shift
• EXPECTED = Expected count based on standard English letter frequencies

These values are summed across all 26 letters to obtain the total χ² value.

The Decryption Strategy

1. Try all 26 possible shifts (0-25)
2. For each shift:
   • Decrypt the ciphertext using that shift
   • Calculate χ² between the decrypted text and English frequencies
3. The shift with the lowest χ² value indicates the correct decryption

Why This Works

The Caesar cipher preserves frequency patterns – it only shifts them. When the correct shift is applied:

• The decrypted text’s frequency distribution will closely match standard English
• The χ² value will be low (indicating good match)

When the wrong shift is applied:

• The frequency distribution will be random
• The χ² value will be high (indicating poor match)

This mathematical approach automatically finds the shift that makes the ciphertext’s frequencies most similar to natural English patterns, providing a scientific verification of our frequency analysis. For scientific validation, the chi-squared test was implemented in JavaScript. The result is

The computational results confirm the initial frequency analysis. The chi-squared test successfully identified the correct shift value of 5, validating the cryptographic findings.

Frequency Analysis: Ciphertext vs Plaintext

By comparing the frequency distributions of the ciphertext and plaintext, we can observe a clear and revealing pattern that confirms the use of a Caesar cipher.

Most Frequent Letter Shift:

• In the plaintext, letter E is the most frequent (12.00%, count: 2051)
• In the ciphertext, letter J is the most frequent (12.00%, count: 2051)
• This indicates a shift of 5 positions since E (position 4) → J (position 9)

Distribution Pattern Preservation:

The entire frequency distribution has been systematically shifted while maintaining identical percentage values:

Plaintext → Ciphertext Mapping:
E (12.00%) → J (12.00%)
T (8.88%) → Y (8.88%)
A (8.12%) → F (8.12%)
O (7.93%) → T (7.93%)
I (7.64%) → N (7.64%)

RARE LETTERS CONFIRMATION:

• Plaintext rare letters: J (0.06%), Q(0.13%), Z(0.04%)
• Ciphertext rare letters: O (0.06%), V (0.13%), E (0.04%)
• This follows the same 5-position shift pattern

Cryptographic Analysis:

The identical percentage values with shifted letter assignments clearly demonstrate that this is a simple substitution cipher that preserves the relative frequency distribution. The Caesar shift value can be definitively determined as 5 based on the displacement of the most frequent letter from E to J.

Conclusions

This analysis has demonstrated the fundamental principles of statistical data analysis and their practical application in cryptography. Through frequency analysis, we demonstrated that the Caesar cipher preserves the relative frequencies and percentage distributions of letters.
The chi-squared test provided mathematical validation of our findings, confirming that the correct decryption key produces a distribution closely matching standard English frequencies.

Sources and Further Reading

The picture of Dorian Gray by Oscar Wilde, pdf

Chi-squared test, Wikipedia