In this project, you will design a database and create some application artifacts (forms, reports, etc.) to assist in the music planning for church worship services.
Hilldale Baptist Church is an established assembly with a membership of 450. The music pastor, Alan Kennedy, has the responsibility for planning the music for the services. Alan currently does his planning using Microsoft Word and on paper, as follows:
Alan creates a Monthly Schedule document in Word (see sample schedule) that shows the music, personnel, and special events for the next 2-3 months.
When Alan begins planning a month, he checks the Church calendar for any special events (baptisms, Lord’s supper, Christmas cantata, special meetings) and notes those in his document. For each service, Alan also notes key personnel who will be away, and thus cannot be assigned to lead / play instruments.
Next, for Sunday morning services, Alan plans a worship theme to unify the music for the service (ex. “The Blood of Jesus”; or “God’s Holiness”) and notes that on the document. Then, he selects congregational songs, offertories, and choral arrangements that support the theme. To avoid repeating congregational songs and choral arrangements too frequently, he keeps track of the dates these songs were last used in an Excel spreadsheet. For Sunday evening and Wednesday evening services, Alan does not plan the music around a worship theme.
Alan schedules various music personnel to fulfill various functions for each service (pianist, organist, song leader, soloist, offertory, etc.). These assignments are tentative until confirmed with the personnel.
Alan also schedules Church ensembles to perform special opening numbers for various services. These ensembles include youth choir, adult choir, orchestra, handbell choir, ladies’ ensemble, mens’ ensemble, etc.
At the beginning of each week, Alan prepares a specific order for the Sunday services (see sample service order) that lists each element of the service, along with supporting details needed to help plan and present the service.
Typical examples of items on a service order, with their accompanying info, include the following:
There is a somewhat standard order for these items, but it varies from service to service, and special services can radically depart from the standard order. After reviewing the service order with the other church staff, Alan e-mails the service order out to all of the personnel involved in the various items on the service order.
In your discussions with Alan, you uncover the following additional requirements:
The database needs to keep track of the details for service plans for past, present, and future services. As part of this requirement, the database should keep track of which personnel are assigned to each item in a service, and whether the assignment is tentative or confirmed.
The database should be able to generate service order documents using standard database reporting mechanisms.
The database needs to facilitate the sending of service orders to all personnel involved in a given service. To support this, the database needs to keep track of the e-mail addresses of all personnel. The database also needs to keep track of which personnel are in each music ensemble.
The database needs to keep track of all of the songs in the hymnal, as well as all of the choir numbers in the choral library, and the last date each was used in a service.
The database needs to be able to generate a report showing which personnel are available to minister in a given service.