

CREDIT CARD DATABASE

         ____________                        ______________
         |          |                        |            |
         |  Region  |                        |  Category  |
         |___[10]___|                        |_____[9]____|
             | | |_______________________            |
             | |____________             |           |
             |              |            |           |
         _________________  |            |           |
         |               |  |            |           |
         |  Corporation  |  |            |           |
         |____[1000]_____|  |            |           |
                            |            |           |
                |_________  |            |           |
                          | |            |           |
                      __________   ____________      |
                      |        |   |          |      |
                      | Member |   | Provider |      |
                      |[10,000]|   |__[500]___|      |
                                                     |
                 _______|  | |           |           |
                |          | |           |           |
                |          | |           |__________ |
         ______________    | |                     | |
         |            |    | |                     | |
         |  Statement |    | |                     | |
         |__[20,000]__|    | |                     | |
                 __________| |___________________  | |
                |                                | | |
         _____________                       ____________
         |           |                       |          |
         |  Payment  |                       |  Charge  |
         |_[15,554]__|                       |[100,000] |



Notes.
    The number beneath the table name is the number of rows 
        in the table.
    The member/corporation and the charge/statement 
        relationships are the only optional relationships in
        the database.  
    All other relationships are manditory relationships.  
        That is, it IS NOT manditory that a member belong to
        a corporation, but it IS manditory that every charge
        have some member who is responsible for that charge.
    The CREATE TABLE statements are shown on the following 
        pages for your convenience.



CREATE TABLE region
  (
     region_no        numeric_id   NOT NULL IDENTITY
  ,  name             shortstring  NOT NULL
  ,  street           shortstring  NOT NULL
  ,  city             shortstring  NOT NULL
  ,  state_prov       statecode    NOT NULL
  ,  country          countrycode  NOT NULL
  ,  mail_code        mailcode     NOT NULL
  ,  phone_no         phonenumber  NOT NULL
  ,  region_code      status_code  NOT NULL
  )

CREATE TABLE category
  (
     category_no      numeric_id   NOT NULL IDENTITY
  ,  category_desc    normstring   NOT NULL
  ,  category_code    status_code  NOT NULL
  )

CREATE TABLE corporation
  (
     corp_no          numeric_id   NOT NULL IDENTITY
  ,  name             shortstring  NOT NULL
  ,  street           shortstring  NOT NULL
  ,  city             shortstring  NOT NULL
  ,  state_prov       statecode    NOT NULL
  ,  country          countrycode  NOT NULL
  ,  mail_code        mailcode     NOT NULL
  ,  phone_no         phonenumber  NOT NULL
  ,  expr_dt          datetime     NOT NULL
  ,  region_no        numeric_id   NOT NULL
  ,  corporation_code status_code  NOT NULL
  )

CREATE TABLE member
  (
     member_no        numeric_id   NOT NULL IDENTITY
  ,  lastname         shortstring  NOT NULL 
  ,  firstname        shortstring  NOT NULL 
  ,  middleinitial    letter           NULL
  ,  street           shortstring  NOT NULL
  ,  city             shortstring  NOT NULL
  ,  state_prov       statecode    NOT NULL
  ,  country          countrycode  NOT NULL
  ,  mail_code        mailcode     NOT NULL
  ,  phone_no         phonenumber      NULL
  ,  photograph       image            NULL
  ,  issue_dt         datetime     NOT NULL
  ,  expr_dt          datetime     NOT NULL
  ,  region_no        numeric_id   NOT NULL
  ,  corp_no          numeric_id       NULL
  ,  prev_balance     money            NULL
  ,  curr_balance     money            NULL
  ,  member_code      status_code  NOT NULL
  )

CREATE TABLE provider
  (
     provider_no      numeric_id   NOT NULL IDENTITY
  ,  name             shortstring  NOT NULL
  ,  street           shortstring  NOT NULL
  ,  city             shortstring  NOT NULL
  ,  state_prov       statecode    NOT NULL
  ,  mail_code        mailcode     NOT NULL
  ,  country          countrycode  NOT NULL
  ,  phone_no         phonenumber  NOT NULL
  ,  issue_dt         datetime     NOT NULL
  ,  expr_dt          datetime     NOT NULL
  ,  region_no        numeric_id   NOT NULL
  ,  provider_code    status_code  NOT NULL
  )

CREATE TABLE statement
  (
     statement_no     numeric_id   NOT NULL IDENTITY
  ,  member_no        numeric_id   NOT NULL
  ,  statement_dt     datetime     NOT NULL
  ,  due_dt           datetime     NOT NULL
  ,  statement_amt    money        NOT NULL
  ,  statement_code   status_code  NOT NULL
  )

CREATE TABLE payment
  (
     payment_no       numeric_id   NOT NULL IDENTITY
  ,  member_no        numeric_id   NOT NULL
  ,  payment_dt       datetime     NOT NULL
  ,  payment_amt      money        NOT NULL
  ,  statement_no     numeric_id       NULL
  ,  payment_code     status_code  NOT NULL
  )

CREATE TABLE charge
  (
     charge_no        numeric_id   NOT NULL IDENTITY
  ,  member_no        numeric_id   NOT NULL
  ,  provider_no      numeric_id   NOT NULL
  ,  category_no      numeric_id   NOT NULL
  ,  charge_dt        datetime     NOT NULL
  ,  charge_amt       money        NOT NULL
  ,  statement_no     numeric_id       NULL
  ,  charge_code      status_code  NOT NULL
  )

