CSCI345: Parish Project

Updated 2020.04.10

Problem Description

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.

Project Requirements

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

3. Build test database and queries

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 (2%1%).

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.

1.    Find 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. (4%5%) 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.

2.    Find 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 row. (4%5%) Name this query 02EnvelopHolderSummary.

3.    Find 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.

4.    Find 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. (4%5%) Name this query 04WeekByWeekTotals.

5.    Find 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 Word document):

  1. (3%) A printout of each of the above queries in SQL followed by a dump of the result of each of the queries on the test database (put the results below their corresponding query) and specify the parameter(s) that you used and display the results for that parameter value. There should be at least two tests of each query
  2. A printout of the relationships in the database (Database Tools | Relationships). (1%)

Put the database file in the drop box.

4. Build forms and reports

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.