Wednesday, October 18, 2006

A sample DB to illustrate some joins (WIP)

The example here supposes that we are building a database of political theory. We are trying to classify the arguments that people try to make to support their political or social positions. This could be build up into a social studies learning tool.

We may have to tinker with the basic setup from time to time to make some of the examples applicable. For example, outer joins wouldn’t make sense if you had already enforced referential integrity with foreign keys.

Future blog entries may refer to this setup.

DECLARE TABLE(DB01.ARGUMENTS)
LIBRARY(DB01.DCGLGENS(ARGUMENT))
ACTION(REPLACE)
LANGUAGE(COBOL)
STRUCTURE(ARGUMENT-ROW)
QUOTE

EXEC SQL DECLARE DB01.ARGUMENT TABLE
(TOPIC CHAR(8) NOT NULL,
SUBTOPIC CHAR(8) NOT NULL,
BIBID CHAR(8) NOT NULL,
ARGDATE DATE,
ARGID SMALLINT,
COUNTERARGID SMALLINT,
ARGTEXT VARCHAR(1024))
PRIMARY KEY (ARGID),
FOREIGN KEY ITEMID (BIBID)
REFERENCES DB01.BIBLIO
ON DELETE CASCADE)

(Varchar should normally be at the end of a table)

DECLARE TABLE(DB01.BIBLIO)
LIBRARY(DB01.DCGLGENS(ARGUMENT))
ACTION(REPLACE)
LANGUAGE(COBOL)
STRUCTURE(ARGUMENT-ROW)
QUOTE

EXEC SQL DECLARE DB01.BIBLIO TABLE
(ITEMID CHAR(8) NOT NULL,
AUTHOR CHAR(32) NOT NULL,
TITLE CHAR(32) NOT NULL,
SERIES CHAR(32) NOT NULL,
URL CHAR (64) NOT NULL,
PUBDATE DATE
PRIMARY KEY (ITEMID))

Imagine the tables to contain these entries:

ARGUMENT

1amend,Internet,00000001,2006-01-01,11,12, “Children should be protected on the Internet”
1amend,Internet,00000002,2006-01-01,12,11, “filters will protect children”
1amend,Internet,00000003,2006-01-01,13,11, “content labeling will protect children”
service,draft,00000011,2006-02-28,21,22, “military service is am obligation of citizenship”
service,draft,00000012,2006-02-28,22,21, “the draft is involuntary servitude”

BIBLIO

00000001,”Bill Boushka” “DADT” “DADT”, “http://www.doaskdotell.com”, 1997-07-11
00000002, “John Doe” “Autobiography of John Doe”,,2001-01-01
00000003, “John Smith”, “Auto JS”, ,2001-01-01
00000011, “Mary Smith”, “Auto MS”, ,2002-01-01
00000012, “Ellen Smith”, “Auto ES”, ,2003-01-01
00000013, “Jerry Smith”, “Auto KS”, ,2002-01-01
00000014, “Dan Smith”, “Auto DS”, ,2003-01-01

An inner join:

SELECT AUTHOR ARGTEXT
FROM DB01.BIBLIO
INNER JOIN DB01.ARGUMENT
ON ITEMID = BIBID
Produces arguments where there are matching authors, and only names the authors that have arguments.

An outer join is useful, in practical terms, in identifying potential future matches. For this example, pretend that there is no foreign key clause forcing every argument to have an author.

SELECT A.AUTHOR, B.ARGTEXT
FROM DB01.BIBLIO A
LEFT JOIN DB01.ARGUMENT B
ON A.ITEMID = B.BIBID

This join would return all authors as potential matches but only the arguments authored by matched authors. If you coded “right” you would get the same result if the tables were switched in the select statement. Full outer joins allow only an equality operation, but left and right joins can use any comparison operator.

A good reference is this:

No comments: