A church wishes to construct a database to store information about parishioners and their church-related interests, their contributions, and their families. The following information is to be included in the database:
The parish is organized by envelope holders. An envelope holder may be a single adult, a married couple, or a married couple with children. When an envelope holder is a couple or family, only one envelope number is assigned to the entire family. The parish keeps track of the following for every envelope holder: envelope number (a unique identifier), name of head of household (sometimes the wife, sometimes the husband), birth date, address, phone number, spouse name (the spouse's last name may not be the same as the head of household), birth date, children's names (first and last) and birth dates, church-related interests, and miscellaneous notes. Most envelope holders are married. Most envelope holders have children. Envelope holders make contributions by placing the contribution in a numbered envelope, checking the appropriate categories, and writing the amount to go towards each category on the outside of the envelope. There are only three categories: dues, building, and special intention. The parish must keep track of the amount and category and amount of each donation (e.g. if they wish to split a $100 contribution between dues and building, this would be considered two donations) and the date on which the donation was made. A parishioner can make many contributions on many different dates in each of the three categories. A parishioner will never give more than one gift per category on any given date. A parishioner may be interested in many ministries. The church ministries are: hospital visits, teacher aide, soup kitchen volunteer, youth ministry, usher, choir, lector, gift bearer, and scripture reflections. The only information that we keep track of is whether the parishioner is interested or not. Note that it is possible for a head of household to be interested in some ministries while his/her spouse may be interested in completely different ministries. The parish needs to know whether the head of the house is the person who is interested in a given ministry or if it is the spouse who is interested.
The parish needs to be able to do the following:
· Enter new parishioners (including spouses and children) into the database and edit information for existing parishioners.
· Enter contribution information into the database and edit existing contribution information.
· Create a report of all people who have expressed an interest in a particular ministry.
· Create an individual report for an envelope holder summarizing all of his contributions by category between any two given dates.
The project is to be done individually. The database is to be implemented using Microsoft Access. The project will be done in 4 parts:
1. Create the conceptual design (see project overview)
2. Create the relational design (see project overview)
3. Build test database and queries
4. Build forms and reports
Build a test database in Access for the relational design created in the
part 2 of the project. Ensure that there is sufficient
data in the database to provide reasonable answers to test queries (
Design in SQL, and implement using Access, the following test queries. All queries must use parameters entered by the user. The fields that are underlined and red below represent the parameters in a query. Use the names given in bold for your queries.
all envelope holders and spouses who are interested in a particular ministry.
Input will be the name of the ministry. Return the name and phone number of
each parishioner and sort alphabetically by last name, first name. (
Two parishioners with the same last name should be listed in order by their
first names. NOTE that this query is easier if you have one table for
parishioner interests rather than two. Name this query 01MinistryInterest.
the total contributions (in each contribution category) for a specific envelope holder between any two dates (including both dates). Input will be
the envelope number and the two dates. Return the envelope number, last name,
first name, and totals for the three categories. This will return a single
4%5%) Name this query 02EnvelopHolderSummary.
all children, (child first name, chile last name, parent (head of household
first and last name), head of household phone number) born between two dates (including both) specified by the user.
Input will be the two dates. Return the last name, first name, and parent's
name (the name of the envelope holder), and phone number in alphabetical order
by last name, first name. Two children with the same last name should be in
order by first name. (
4%5%) Name this query 03ChildrenByBirthday.
the week-by-week total Sunday contribution for the entire parish in each
category for each Sunday between two dates
(including both dates) specified by the user. Input will be the two dates. Return
the data and the three totals for each Sunday, in ascending order by date. (
Name this query 04WeekByWeekTotals.
the week-by-week contributions for one adult
between two dates (including both dates)
specified by the user. Input will be the envelope number and the two dates. Return
the date and the contribution in each of the three categories, in ascending
order by date. (
4%5%) Name this query 05EnvelopeHolderWeekByWeek. Hand in a report that includes the following (all in a single
Put the database file in the drop box.
Design, and implement using Access, two of the forms needed for the Parish Database:
1. A form to view envelope holder information, to enter a new envelope holder, and to update an existing envelope holder's information. Children should be entered using a sub-form. (6%) Name this form 01EnvelopeHolder.
2. A form to enter contributions using the envelope as a source document. When the user enters the envelope number, the name of the envelope holder should pop up on the screen so that the user can visually verify that the number and name agree. (6%) Name this form 02EnterContributions.
Design, and implement using Access, the following reports:
1. A letter to a parishioner telling how much he has contributed in each of the three categories between any two dates specified by the user (including both dates). (6%) You will have to write a parameterized query to get the two dates specified by the user. Name your report 01ParishionerContributionSummary.
2. A set of mailing labels for the entire parish. (6%) Name your report 02MailingLabels.
Hand in a Word document
(hard copy) which includes the following: 1. An
example of each form and description of how the form is to be used. 2. A
printout of each report.
Put the database file in the drop box.