Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

sqldatasource dal, wwwxxxenden, convert raid5 to raid 10 mdadm, apache force chunked, nrao wwwxxx, xxxxxdup, procmail change subject header, wwwXxx not20, Wwwxxx.doks sas, linux raid resync after reboot

Links

XODOX
Impressum

#1: General Help on Query Writing, With Defined Schema

Posted on 2008-09-09 17:44:18 by Jeff Nyman

Greetings all.

Let's say I've got a database schema like this (with the all-capital terms
in [ ] being the table name) in a PostgresSQL DB:

= = = = = = = = = = = = = = = = = = = = = = =
[PARTY]
PartyID (primary key)
Description
Location

[PARTY_THEME]
Party_ThemeID (primary key)
ThemeID (foreign key 1)
PartyID (foregith key 2)

[PARTY_HOST]
Party_HostID (primary key)
PartyID (foreign key 1)
HostID (foreign key 2)

[THEME]
ThemeID (primary key)
Name

[HOST]
HostID (primary key)
Name
= = = = = = = = = = = = = = = = = = = = = = =

The context here is that I'm a guy with little SQL experience that has to
test this schema for various things before go-live. (Writing what will
essentially be data-integrity test cases as we go forward.) So what I did is
break up the requirement for how the data gets treated and then what I
believe my "test SQL" needs to be:

Requirement: Each party can have one or more themes.
Test SQL: Verify that each PARTY entry has one or more themes after being
entered.

Requirement: 10 parties will be added per week.
Test SQL: Verify that ten parties are in the table at the end of the week.

Requirement: Each party will be assigned one or more host names.
Test SQL: Write a query that checks this after a party is entered.

Requirement: Each party will be assigned one (and only one) location.
Test SQL: Write a query taht checks this after a party is entered.

So what I have is the basic ideas for what I think I should be testing. My
question: is there a resource that can help me figure out how to do this? (I
do know how to write a really basic SELECT statement but what I'm not so hot
at yet is going between tables or knowing how to cross-reference
information, which I think is what I mostly need to do.)

Any help (whether general or specific) is much appreciated.

- Jeff

Report this message