Fwd: Sample databases

---------- Forwarded message ----------
From: Vraj Mohan <r(dot)vrajmohan(at)gmail(dot)com>
Date: Thu, Jan 17, 2013 at 7:22 AM
Subject: Re: [GENERAL] Sample databases
To: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>

On Wed, Jan 16, 2013 at 10:32 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> What do you mean by decent data volumes? Numbers and units are wonderful
> things!

Sorry, I should have been more specific. I was looking for data with
enough richness to explore corner cases in SQL and volume to be able
to study different execution plans. I am guessing here, but would
shoot for something like:
- columns with NULLs
- columns that are foreign keys with not all the values from the
referenced table for exploring outer joins
- tables with ~500,000 rows
- columns with values such that the indexes are highly selective. I
would assume that having over a 100 different values for a column
should achieve this.

> What things are you looking to learn and test automatically? I like to
> learn about and test performance, and I find pgbench often quite good for
> doing that.

My goal is for a database that can be used for:
- learning SQL, specifically postgresql SQL; going beyond simple
queries and joins to using subqueries, correlated subqueries, window
functions and so on
- writing about postgresql
- testing applications developed for postgresql; I would think that
many of the pgfoundry projects would benefit from this.

> Is there a specific thing about the completeness that concerns you?

I tried pagila and found that it did not create any rows in the film
table when I used pagila-insert-data.sql, After my original post, I
have discovered that pagila-data.sql (which used COPY) works
correctly.

> Have you looked at the Dell DVD store? It is also on that site you linked
> above, but it seems to be more up to date on Dell's own site.
>

Thanks, I'll take a look at the Dell DVD store.

I plan to contribute to the http://pgfoundry.org/projects/dbsamples/
project. For starters, I will add the suppliers-parts-projects
database from C J Date's "An Introduction to Database Systems".

--Vraj Mohan