Need help on BD for school project...please (1 Viewer)

FNG

New member
Local time
Today, 16:02
Joined
Feb 21, 2013
Messages
4
Hi everyone, new member here. I am not a COMPLETE Access 2010 noob, but I am at best an intermediate user. We have to create a database based on Voter information. I am having a bit of trouble figuring out how to create this DB. I have imported the data from the proper site and the table is good. Now I am having trouble with the relationships and queries. For example, the many-to-many between voters and promotions, how to go about designing the survey collection/organization method. I really appreciate the help, thanks! Below is the assignment:

County Voter promotion tracking database (
ERD Team Assignment)


Your team has been assigned the task of designing the voter promotional campaign tracking database for a newly formed county political action group (PAG).
At the next meeting (class) your team must present yourentity relationship diagram(ERD) created to identify/organize the data necessary to meet the PAG’s needs/purposes.
The PAG would like to use the database that you design for the following purposes (at a minimum):
To identify which registered voters should be sent promotions by selecting voters based on:
Voter's year of birth
When voter previously voted.
Voter's declared political affiliation when they previously voted.
Voter's Congressional District
Voter's Precinct
etc.(based on available info from the state source)
The PAG would like to use the database to:
track which registered voters were sent which promotions
track responses to survey type campaign promotions
Other examples of data they would like to capture in the database:
Campaign promotion number
Campaign promotion description
Campaign promotion type (post card, survey, Letter, brochure, etc)
Campaign promotion date
Campaign survey promotion responses

Discuss why the PAG should use a database and not a spreadsheet.
 

jzwp22

Access Hobbyist
Local time
Today, 16:02
Joined
Mar 15, 2008
Messages
2,629
Since this is an assignment, I will just provide some guidance.

First, it sounds like a promotion can have many questions so that describes a one-to-many relationship.

I will assume that to each question, there is only 1 response.

Now as to the responses, since the responses to a promotion are dependent on both the voter and the promotion and assuming that there are many responses (since there are many questions), you have a one-to-many relationship between the voter/promotion combination and the responses. Further, a response is also related to a particular question. So you will need a table that has a foreign key back to the junction table as well as a foreign key back to the question table as well as a field to hold the response.
 

FNG

New member
Local time
Today, 16:02
Joined
Feb 21, 2013
Messages
4
Thanks for the guidance. Although not an IS major, I am very interested in relational databases and appreciate the time for guidance rather than just an answer. I made (my best shot at) an ERD diagram in Dia. How does this look?
 

Attachments

  • MorganERDv1.jpeg
    MorganERDv1.jpeg
    38.2 KB · Views: 559

jzwp22

Access Hobbyist
Local time
Today, 16:02
Joined
Mar 15, 2008
Messages
2,629
The tblResponses does not show a foreign key field to the tblVoters_Promotion, just having the voterID field is not enough since the response is dependent upon both the voter AND the promotion.

tblResponses
-ResponseID primary key, autonumber
-fkVoter_PromotionID foreign key to tblVoter_Promotion
-fkQuestionID foreign key to tblQuestions
-ResponseValue

Also, your question table needs a foreign key to the promotions table
 

FNG

New member
Local time
Today, 16:02
Joined
Feb 21, 2013
Messages
4
I made a simple version of this so I can understand (conceptually) how this all works before I complicate things. Below I have the txt version of my basic design:
Table/FieldDataTypeKeyReferenceVoterInfoVoterIDTxtPKFirstNameTxtLastNameTxtPromoPromoIDAuto#PKPromoTypeCode#FKPromoTypePromoDateDateVoterIDTxtFKVoterInfoPromoTypePromoTypeCodeAuto#PKPromoTypeTxtTxtResponseResponseIDAuto#PKQuestionID#FKQuestionVoter_PromotionID#FKVoter_PromoResponseValueTxtQuestion QuestionIDAuto#PKQuestion##QuestionTxtTxtSurveySurveyIDAuto#PKSurveyNameTxtVoter_PromoVoter_PromoIDAuto#PKVoterIDTxtFKVoterInfoPromoID#FKPromo

If this looks good, my next task is to make this more complex.
-The group will send out different types of promotions (hence promotype table). The surveys are really the only ones that require data collection. The rest will be along the lines of post cards, flyers, etc.
-The group will send many surveys to many voters (not always in the same combo) and each survey may contain different questions based on the version of the survey they send. So can I use the Questions table as a question bank and each survey version will draw from that bank?
-So logically, a survey survey will be a type of promo, that survey version will be sent to many voters, some of those voters will respond, and those responses need to relate to both the proper question, voter, and instance where that voter was selected for that promo-->survey-->survey version.

Does this make sense? Im sorry if im not communicating with proper vocabulary, Im still learning. Thanks again for the help!
 

jzwp22

Access Hobbyist
Local time
Today, 16:02
Joined
Mar 15, 2008
Messages
2,629
It was difficult to read your schema, I assume you tried to format it but it did not get carried over when you actually posted the thread.

This is what I was able to make of it. Could you please confirm whether the following is correct?

VoterInfo
-VoterIDTxtPK
-FirstNameTxt
-LastNameTxt

Promo
-PromoIDAuto#PK
-PromoTypeCode#FK
-PromoType
-PromoDateDate
-VoterIDTxtFKVoterInfo

PromoType
-PromoTypeCodeAuto#PK
-PromoTypeTxtTxt

Response
-ResponseIDAuto#PK
-QuestionID#FKQuestionVoter_PromotionID#FK
-Voter_PromoResponseValueTxt

Question
-QuestionIDAuto#PK
-Question##
-QuestionTxtTxt

Survey
-SurveyIDAuto#PK
-SurveyNameTxt

Voter_Promo
-Voter_PromoIDAuto#PK
-VoterIDTxtFK
-VoterInfoPromoID#FKPromo
 

jzwp22

Access Hobbyist
Local time
Today, 16:02
Joined
Mar 15, 2008
Messages
2,629
track which registered voters were sent which promotions

A voter can get many promotions and a promotion can be sent to many voters. This describes a many-to-many relationship between voters and promotions. As such, the following table is not correct:

Promo
-PromoIDAuto#PK
-PromoTypeCode#FK
-PromoType
-PromoDateDate
-VoterIDTxtFK
-VoterInfo

There should be no reference to the voter in the promotion table. The many-to-many relationship is handled by this table:

Voter_Promo
-Voter_PromoIDAuto#PK
-VoterIDTxtFK
-VoterInfoPromoID#FK

Also, I'm not sure why you have both a table question and a survey table; I believe they accomplish the same thing

Question
-QuestionIDAuto#PK
-Question##
-QuestionTxtTxt

Survey
-SurveyIDAuto#PK
-SurveyNameTxt
 

Users who are viewing this thread

Top Bottom