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