Generating sample data in Oracle
--------------------------------

Rasmus Pagh, IT University of Copenhagen
September 2006

In this example we create two tables Students(id,first,last)
and Teachers(id,first,last) corresponding to entity sets, and
a table TakesCourseWith(sid,tid) corresponding to a many-many
relationship. The same principles can be applied in general to
create large sample data sets with a relatively small effort.

First we create two tables with some "base data". The data
generated will be put together from this.

CREATE TABLE FirstNames (name VARCHAR(20));
INSERT INTO FirstNames VALUES ('Arne');
INSERT INTO FirstNames VALUES ('Bruno');
INSERT INTO FirstNames VALUES ('Claus');
INSERT INTO FirstNames VALUES ('Dennis');
INSERT INTO FirstNames VALUES ('Erik');
INSERT INTO FirstNames VALUES ('Freddy');
INSERT INTO FirstNames VALUES ('Georg');
INSERT INTO FirstNames VALUES ('Hans');
INSERT INTO FirstNames VALUES ('Ingvar');

CREATE TABLE LastNames (name VARCHAR(20));
INSERT INTO LastNames VALUES (' Andersen');
INSERT INTO LastNames VALUES (' Berg');
INSERT INTO LastNames VALUES (' Clausen');
INSERT INTO LastNames VALUES (' Dambo');
INSERT INTO LastNames VALUES (' Ericsson');
INSERT INTO LastNames VALUES (' Frandsen');
INSERT INTO LastNames VALUES (' Garbo');
INSERT INTO LastNames VALUES (' Hansen');
INSERT INTO LastNames VALUES (' Immermann');


We then create a (rather big) table, AllNames, consisting of all ways to form names of one first name and two last names:

CREATE TABLE AllNames AS (SELECT F.name AS first, CONCAT(L1.name,L2.name) AS last FROM FirstNames F, Lastnames L1, Lastnames L2);


To polulate the Students and teachers relations we take a sample of 10% of the names in AllNames, using Oracle's SAMPLE keyword:

CREATE TABLE Students (id INT, firstname VARCHAR(20), lastname VARCHAR(20));
INSERT INTO Students (SELECT rownum, first AS firstname, last AS lastname FROM AllNames SAMPLE(10));

CREATE TABLE Teachers (id INT, firstname VARCHAR(20), lastname VARCHAR(20));
INSERT INTO Teachers (SELECT rownum, first AS firstname, last AS lastname FROM AllNames SAMPLE(10));

The "rownum" attribute gives each value of id a unique (numerical) value, namely the row number in AllNames.


To create the relation TakesCourseWith we first create the relation consisting of all possible combinations of id values from Students and Teachers (care must be taken to make sure that this result is not enormous!):

CREATE TABLE AllRel AS (SELECT Students.id AS sid, Teachers.id AS tid FROM Students, Teachers);


Similar to before, the contents of TakesCourseWith is a sample of a suitable percentage of AllRel:

CREATE TABLE TakesCourseWith (sid INT, tid INT);
INSERT INTO TakesCourseWith (SELECT * FROM AllRel SAMPLE(10));


All that remains is to clean up the temporary tables:

DROP TABLE FirstNames;
DROP TABLE LastNames;
DROP TABLE AllNames;
DROP TABLE AllRel;