# Normal Distribution - Implications of negative values

Consider a task, whereby we need to generate a normally distributed data set of 100 numbers. "tableFreq" is being used to hold the "frequency" data. The sum of the frequencies needs to be approx 10000.

Refer to the first image which shows a preview of "tableFreq" (14 out of 100 rows):

• "fkey" column represents the foreign keys that should eventually be inserted in another table, call it "tableX". There are 100 rows, and the range of "fkey" is 1-100.
• The frequency represents the number of times these keys should appear in the said table: "tableX". This is what should be normally distributed.

Example (refer to image): if for fkey 1, we got 90 in "tableFreq", "tableX" should have foreign key 1 in it's first 90 rows, if for fkey 2, we got 59 in "tableFreq", "tableX" should have foreign key 2 in the next 59 rows, and so on.

As already mentioned, the sum of the frequencies of the foreign keys, needs to be approx 10000, as in "tableX" we eventually need to have approx 10000 rows. PostgreSQL is being used to generate the data (if it makes any difference).

To generate "tableFreq" we are using something along these lines:

select row_number() OVER () as fkey, (floor((normal_rand)*1.9)) as frq from normal_rand(100, 50, 28.6)


normal_rand (an internal PostgreSQL function) creates 100 (supposedly) normally distributed values, with 50 as mean, and 28.6 standard deviation. We are multiplying the value retrieved from normal_rand by 1.9 to approximate towards 10000 (i.e. if SUM(floor((normal_rand)*1.9)) is replaced into the query, its value would be ≈ 10000). The mean and standard deviation are calculated from the fact that we are generating 100 numbers (mean/std of range 1-100). The problem is that "frq" is sometimes negative (this is rare, < 10%, but still > 0% every time).

We would not be able to insert, say -18 rows (refer to image above) in "tableX". So we modified the query to make sure it always returns positive values

select row_number() OVER () as fkey, (floor((normal_rand)*1.9)) as frq from normal_rand(110, 50, 28.6) where (floor(normal_rand)) > 0 LIMIT 100


In essence, generating 10 more values by the normal_rand function, restricting to > 0 values, and limiting to 100 so we stick to the range(1,100) restriction.

The final query creates approx 10k rows in "tableX" with the frequencies in "tableTemp" and as described in the Example above.

At the end we are trying to use Python/Matplotlib "hist" to prove that the foreign keys in "tableX" follow a normal distribution. The x-axis represents the foreign keys and the y-axis represents the frequencies. This does not look like a normal distribution.

Is it because of the restriction of the negative numbers (> 0)? Could generating 10 more values and then restricting to 100 be affecting in such a way? How would the negatives be handled in such a case? Or is the approach inherently wrong?

Update -

If we plot the frequencies generated by the normal_rand function as a histogram, the distribution looks "normal". However, the assignment question requires Step 2 i.e. inserting the "foreign keys" into "tableX" according to the frequencies generated in step 1. This is the misleading part.

Update with original question -

Create another two tables (e.g. tableA and tableX in the same schema). Note that table tableA is related to table tableX with a 1(p) - N(t) relationship. tableX is to have about 10k tuples. Whilst tableA has at first 1k, then 100k, and then 10m (if it does not take too much on your install). As for the foreign key distinct values, please set at 100, 2k and 7k respectively. Generate tableA and tableX for the various sizes mentioned. As for distribution, make use of the uniform, normal (average being the mid tuple by sort order of PK) and a distribution of your invention

• "whereby we need to generate a normally distributed data set for a range of numbers, e.g. 1-100" - this is impossible. The normal distribution generates data over the whole real range by definition. If you don't allow that, for example because you have a constrained value range, the data cannot be normally distributed. If you want approximate normality, you need to choose a small variance so that the probability that data leave the value range is very small. Jan 13, 2022 at 12:02
• Other than that, your description is very hard to follow. You talk about "the frequency data" as if it were clear what that is, but it isn't. How can this be negative? This is not what you mean when you say you want to generate normally distributed data, or is it? I also don't get the "multiply by 1.9" bit. If you want to be between 1 and 100, why multiply by 1.9? Jan 13, 2022 at 12:04
• Hi @ChristianHennig, thank you for your replying. Apologies, it is a bit complicated to explain and the sentence you quoted is indeed misleading. I will fix it. The range 1-100 is a range of FKs; refer to the first column of the first image. The frequency of FKs should be normally distributed and not the FK range; refer to the second column of the first image. You can see the frequencies are not bound within the 1-100 range but there are indeed negative values: the negative values are mainly what I am asking about. Jan 13, 2022 at 13:08
• Case in point, the first step is to generate 100 numbers that are normally distributed. The sum of which needs to be approx 10,000 (hence the multiplication by 1.9, based on the chosen mean & standard deviation). The second step is to create rows within "tableX" with the frequencies obtained in step 1, i.e. foreign key 1 with the frequency obtained for 1 in step 1, foreign key 2 with the frequency obtained for 2 in step 2, etc. I hope this is clearer. Thanks again! Jan 13, 2022 at 13:13
• but then in that way, you cannot expect to see a bell curve..
– Tom
Jan 13, 2022 at 17:55