FunctionX Logo

The US Senate Database
 

Planning
 

Introduction

This is one of the exercises intended to help you build some experience with databases and Microsoft Access. We are going to create a database that will need to be updated regularly, or at least whenever there is a new election or a new change in the US senate. Even if you don't live in the US, this exercise can help you with this type of application.

  • Start Microsoft Access and create a blank database named US Senate1

Valuable Information

To build this type of database, you should first design it. In this case, you can start by visiting the US Senate web site at http://www.senate.gov. From there, you can see that the primary list you will need to create is about senators. Therefore, you need to make a summary of the types of information you need about each senator. Valuable pieces of information include:

  • Name: At a minimum, you should be able to identify a senator
  • State: Each state has two senators and at one time in your database, you may need to know the names of senators from a certain state
  • Party: It is not unusual that a party a senator belongs to influences his or her voting records. In some cases, a senator from one state may hold a view that is traditionally held by the other (abortion, civil rights, affirmative action, etc)
  • Gender: This can be a valuable piece of information for statistics. For example, at the time of this writing, around 80% of the senators are male
  • Race: This also helps with the statistics. For example, at the time of this writing, 99% of senators are white, no African-American, no Hispanic-American, no Native-American, etc
  • Religion: Religion can play tremendously on the voting records of a senator
  • Address: In case you want to communicate with a senator, you would need to know where to join him or her. Every senator also has a web site
  • Telephone Number
  • Home State Contact

The Tables

General Tables

On a database, there are tables whose primary function is to provide fields to other tables. This reduces redundancy by allowing information to flow among objects and at the same time avoiding duplicate data. Such tables are create with regular fields and their cells receive direct input from the user. For our database, such table would include the gender, religion, marital status, etc. We will create most of these tables but we left some out. This could be an exercise for you to add any table we may have left out, such as one identifying the races of senators.
  1. From the above indicative list, create a table in Design View with the following fields
     
    Field Name Data Type Additional Properties
    GenderID AutoNumber Primary Key
    Caption: Gender ID
    Gender Text Field Size: 20
  2. Save it as Genders and switch to Datasheet View
  3. In the first cell under Gender, type Female and press the down arrow key. Type Male and close the table
  4. In Design View, create another table with the following fields
     
    Field Name Data Type Additional Properties
    PartyID AutoNumber Primary Key
    Caption: Party ID
    Party Text Field Size: 32
    Description Memo  
  5. Save it as Parties and switch it to Datasheet View
  6. Enter the first party as Democrat, the second as Republican, the third as Greens, the fourth as Independent, and the last as Other
  7. Close the table
  8. Create another table in Design View with the following fields...
     
    Field Name Data Type Additional Properties
    MaritalStatusID AutoNumber Primary Key
    Caption: Marital Status ID
    MaritalStatus Text Field Size: 20
    Caption: Marital Status
    Description Memo  
  9. Save it as MaritalStatus and switch to Datasheet View. Fill it up as follows:
      
    Marital Status ID Marital Status Notes
    1 Single This is for somebody who has never been married
    2 Married This is for somebody who is currently married
    3 Divorced  
    4 Widow  
    5 Separated  
  10. Close the table
  11. Create another table in Design View with the following fields:
      
    Field Name Data Type Additional Properties
    ReligionID AutoNumber Primary Key
    Caption: Religion ID
    Religion Text Field Size: 40
    Default Value: "Unknown"
    Comments Memo  
  12. Save it as Religions and switch it Datasheet View
  13. Set the first religion to Unknown, the second to Mormon, the third to Jewish, the fourth to Catholic, the fifth to Baptist, and the sixth to Methodist
  14. Close the table
  15. Create another table in Design View with the following fields:
      
    Field Name Data Type Additional Properties
    StateID AutoNumber Primary Key
    Caption: State ID
    State Text Field Size: 2
    Input Mask: >LL
    Indexed: Yes (No Duplicates)
    StateName Text Caption: State Name
    Indexed: Yes (No Duplicates)
  16. Save it as States and switch it to Datasheet View
  17. Fill it up as follows:
      
    State ID State State Name
    1 AL Alabama
    2 AK Alaska
    3 AZ Arizona
    4 AR Arkansas
  18. Close the table
  19. Create another table in Design View with the following fields:
      
    Field Name Data Type Additional Properties
    CommitteeID AutoNumber Primary Key
    Caption: Committee ID
    Committee Text  
    Description Memo  
  20. Save it as Committees and switch it to Datasheet View
  21. Fill it up as follows:
      
    Committee ID Committee Description
    1 Agriculture, Nutrition, and Forestry  
    2 Appropriations  
    3 Armed Forces  
    4 Banking, Housing, and Urban Affairs  
  22. Close the table
  23. Create another table in Design View with the following fields:
      
    Field Name Data Type Description Additional Properties
    CMStatusID AutoNumber Committee Membership Status ID Primary Key
    Caption: CMStatus ID
    CMStatus Text Committee Membership Status Field Size: 30
  24. Save it as CMStatus and switch it to Datasheet View
  25. Set the first committee membership status to Chairman, the second to Ranking Member, and the third to Member
  26. Close the table
  27. Create another table in Design View with the following fields:
      
    Field Name Data Type Description Additional Properties
    SCMStatusID AutoNumber Subcommittee Membership Status ID Primary Key
    Caption: SCMStatus ID
    SCMStatus Text Subcommittee Membership Status Field Size: 30
  28. Save it as SCMStatus and switch it to Datasheet View
  29. Set the first committee membership status to Chairman. the second to Ranking Member, and the third to Member
  30. Close the table

Relations-Based Tables

After creating general tables, you can create those whose information can be provided by those primary tables. For example, some of the tables we have created involve simple information such as gender or religion. The reason we created their own tables was to reduce the likelihood of the user trying to provide the same information but typing it differently for two records. For example, imagine two female senators for whom the user enters the gender of one as Female and the other as F or even Girl. As far as the database is concerned, these would be two different values.
  1. Create a table in Design View with the following fields:
      
    Field Name Data Type Additional Properties
    SenatorID AutoNumber Primary Key
    Caption: Senator ID
    FirstName Text Field Size: 20
    Caption: First Name
    MiddleName Text Field Size: 20
    Caption: Middle Name
    LastName Text Field Size: 20
    Caption: Last Name
    PartyID Number Field Size: Long Integer
    Caption: Party
    Default Value: None
    StateID Number Field Size: Long Integer
    Caption: State
    Default Value: None
    WebSite Hyperlink Caption: Web Site
    YearElected Number Field Size: Integer
    Caption: Year Elected
    Default Value: None
    GenderID Number Field Size: Long Integer
    Caption: Gender
    Default Value: None
    OfficeAddress Text Field Size: 240
    Caption: Office Address
    Default Value: "000 Hart Senate Office Building, Washington DC 20510"
    OfficePhone Text Field Size: 20
    Input Mask:!\(999") "000\-0000;0;_
    Caption: Office Phone
    Default Value: " (202) 224-0000"
    OfficeFax Text Field Size: 20
    Input Mask:!\(999") "000\-0000;0;_
    Caption: Office Fax
    HomeStateAddress1 Text Field Size: 240
    Caption: Home State Address 1
    HomeStatePhone1 Text Field Size: 20
    Caption: Home State Phone 1
    Input Mask:!\(999") "000\-0000;0;_
    HomeStateFax1 Text Field Size: 20
    Caption: Home State Fax 1
    Input Mask:!\(999") "000\-0000;0;_
    HomeStateAddress2 Text Field Size: 240
    Caption: Home State Address 2
    HomeStatePhone2 Text Field Size: 20
    Caption: Home State Phone 2
    Input Mask:!\(999") "000\-0000;0;_
    HomeStateFax2 Text Field Size: 20
    Caption: Home State Fax 2
    Input Mask:!\(999") "000\-0000;0;_
    HomeStateAddress3 Text Field Size: 240
    Caption: Home State Address 3
    HomeStatePhone3 Text Field Size: 20
    Caption: Home State Phone 3
    Input Mask:!\(999") "000\-0000;0;_
    HomeStateFax3 Text Field Size: 20
    Caption: Home State Fax 3
    Input Mask:!\(999") "000\-0000;0;_
    HomeStateAddress4   Field Size: 240
    Caption: Home State Address 4
    HomeStatePhone4   Field Size: 20
    Caption: Home State Phone 4
    Input Mask:!\(999") "000\-0000;0;_
    HomeStateFax4   Field Size: 20
    Caption: Home State Fax 4
    Input Mask:!\(999") "000\-0000;0;_
    HomeStateAddress5   Field Size: 240
    Caption: Home State Address 5
    HomeStatePhone5   Field Size: 20
    Caption: Home State Phone 5
    Input Mask:!\(999") "000\-0000;0;_
    HomeStateFax5   Field Size: 20
    Caption: Home State Fax 5
    Input Mask:!\(999") "000\-0000;0;_
    MaritalStatusID Number Caption: Marital Status
    Default Value: None
    NumberOfChildren Number Field Size: Byte
    Caption: Nbr of Children
    ReligionID Number Caption: Religion
    Default Value: None
    Picture OLE Object  
    CurrentlyActive Yes/No Caption: Is Currently Active?
    Default Value: 1
    Lookup -> Display Control: Combo Box
    Notes Memo  
  2. Save it as Senators and switch it to Datasheet View
  3. Fill it up with the appropriate values
  4. Create another table in Design View with the following fields:
     
    Field Name Data Type Additional Properties
    SubcommitteeID AutoNumber Primary Key
    Caption: Subcommittee ID
    CommitteeID Number Caption: Committee
    Default Value: None
    Subcommittee Text  
    Description Memo  
  5. Save it as Subcommittees and close it
  6. Create another table in Design View with the following fields:
     
    Field Name Data Type Additional Properties
    CommitteeMembershipID AutoNumber Primary Key
    Caption: Committee Membership ID
    CommitteeID Number Caption: Committee
    Default Value: None
    SenatorID Number Caption: Senator
    Default Value: None
    CMStatusID Number Caption: CMStatus
    Default Value: None
  7. Save it as CommitteesMemberships and close it
  8. Create another table in Design View with the following fields:
     
    Field Name Data Type Additional Properties
    SubommitteeMembershipID AutoNumber Primary Key
    Caption: Subcommittee Membership ID
    SubcommitteeID Number Field Size: Long Integer
    Caption: Subcommittee
    Default Value: None
    SenatorID Number Field Size: Long Integer
    Caption: Senator
    Default Value: None
    SCMStatusID Number Field Size: Long Integer
    Caption: SCMStatus
    Default Value: None
  9. Save it as SubcommitteesMemberships and close it

Relationships Management

Although you can completely create the tables relationships in the Design View of a table, Microsoft Access provides a specific window to manage them. The Relationships window allows you to specify or modify the direction of a relationship. Also, in case you didn't use the Lookup Column or the Lookup Wizard when creating the table, as we have done so far, you can use the Relationships window to actually create the relationships.

You may have realized that, for some strange reason, we avoided using a wizard to create the relationships among our tables. There is no specific reason except that it allowed us to give less instructions. On our own defense, we would state that, since most database environments, including Microsoft SQL Server and Borland/Corel Paradox don't use the wizard, we are not doing anything worse. Therefore, we will create and manage all of our relationships using the Relationships window.
  1. To manage the links among tables, on the main menu of Microsoft Access, click Tools -> Relationships...
  2. Since no relationship has been created in the database, you are presented with the Show Table dialog box.
     


    To select two tables, double-click Parties followed by Senators and click Close

  3. To create a relationship, drag PartyID from the Parties list and drop in on PartyID on the Senators list
  4. When the Edit Relationship dialog box comes up, click the Enforce Referential Integrity check box and then click the other two check boxes
  5. Click Join Type... and click the middle radio button
  6. Click OK on the Join Properties dialog box
     
  7. Click Create on the Relationships dialog box
  8. To display the Show Table dialog box, right-click anywhere in the Relationships window and click Show Table...
  9. Complete the relationships among tables as seen below
  10. If you are using Microsoft Access >= 2000, to save the diagram as a report, on the main menu, click File -> Print... Relationships...
  11. Save the the report as US Senate Database Diagram and close it

Home Next