One Interesting Table!

I have some thoughts on a number of topics, and this table will serve as a good example for all of them. Let's first start with some DDL:

CREATE TABLE PROSPECTS (
PROSPECT_ID NUMBER PRIMARY KEY,
FIRST_NAME VARCHAR2(25),
LAST_NAME VARCHAR2(25),
SSN VARCHAR2(9),
STREET VARCHAR2(30),
CITY VARCHAR2(30),
STATE_CD VARCHAR2(2),
PHONE VARCHAR2(10)
)
/

And let's fill it with some data:

PROSPECT   FIRST      LAST        SSN       STREET         CITY     STATE      PHONE        
  ID       NAME       NAME                                                                                                                                                 CD
-----   ----------  ----------- --------- -------------  ---------  ----   ----------
  1     Judi        Singer      135792468 6216 Kevin     Brighton     MI   8102222222
  2     Mega        Hertz       148273423 810 Main       Howell       MI   5174234243
  3     Judy        Singer      987131312 6216 Kevin Dr  Brighton     MI   8102222222
  4     Judi        Singer      145792468 6216 Kevin     Brighton     MI   8102222222
  5     Mega        Hertz       148273423 810 Main       Howell       MI   5174234243
  6     Junior      Hertz                 810 Main       Howell       MI   5174234243
  7     Mighty      Moose       184726423 333 Third St.  Brighton     MI   8102223423  

Before I go any further, let me add that this is not my idea of a great design. In any sort of a decent prospect database, this table should be split off into at least two separate tables, and a (large) number of constraints added. This will become clear in the following discussion

(And yes, although some of the names and addresses are real, the Social Security Numbers and telephone numbers have been changed to protect the innocent).

What we are trying to have is a table which holds potential customers in some sort of marketing database. And we can start out with these questions (which relate to the whole issue of 1NF and surrogate keys): How many different prospects do we have in the database? What are the candidate keys in this table? Is it in 1NF? And we will close with these questions (which relate to a different issue): How many people is our system aware of? Suppose I added the business rule that any children in the system had to have a parent in the system?

To Be Continued: