File: csa_test_data.sql

---------------------------------------------------------------------------
-- SQL script to create and populate a test database for cascadeSelectAssoc
-- This script has been tested with PostgreSQL 7.4.x
---------------------------------------------------------------------------

\c template1
DROP DATABASE csa_test_db;
CREATE DATABASE csa_test_db;
\c csa_test_db

CREATE TABLE mothers (
	id_mother		SERIAL,
	mother		VARCHAR(25),
	PRIMARY KEY (id_mother)
);

CREATE TABLE children (
	id_child	SERIAL,
	id_mother	INTEGER,
	child		VARCHAR(50),
	PRIMARY KEY (id_child, id_mother), 
	FOREIGN KEY (id_mother)
   		REFERENCES mothers
);

INSERT INTO mothers (mother) VALUES ('Operating Systems');
INSERT INTO mothers (mother) VALUES ('Programming Languages');
INSERT INTO mothers (mother) VALUES ('Databases');
INSERT INTO mothers (mother) VALUES ('Web Browsers');

INSERT INTO children (id_mother, child) VALUES (1,'Linux');
INSERT INTO children (id_mother, child) VALUES (1,'OS X');
INSERT INTO children (id_mother, child) VALUES (1,'OpenBSD');
INSERT INTO children (id_mother, child) VALUES (1,'Solaris');
INSERT INTO children (id_mother, child) VALUES (2,'Perl');
INSERT INTO children (id_mother, child) VALUES (2,'PHP');
INSERT INTO children (id_mother, child) VALUES (2,'Python');
INSERT INTO children (id_mother, child) VALUES (2,'Ruby');
INSERT INTO children (id_mother, child) VALUES (3,'PostgreSQL');
INSERT INTO children (id_mother, child) VALUES (3,'MySQL');
INSERT INTO children (id_mother, child) VALUES (3,'Firebird');
INSERT INTO children (id_mother, child) VALUES (3,'MaxDB');
INSERT INTO children (id_mother, child) VALUES (4,'Firefox');
INSERT INTO children (id_mother, child) VALUES (4,'Opera');
INSERT INTO children (id_mother, child) VALUES (4,'Lynx');
INSERT INTO children (id_mother, child) VALUES (4,'Mozilla');