Application Development I

Estimated time to complete: 7-9 hours

Overview

In this phase of the project, you will create queries, forms, and reports.

Instructions

1. Create a MySQL database called wsoapp, and execute the official script provided by your instructor to populate it with official test data.

2. Create an ODBC data source named wsoapp that connects to your MySQL database. Create an Access database named wsoapp.accdb and create linked tables in your Access database that link to the tables in your MySQL database.

3. Create an Access form named ServiceForm. It should be a master-detail form (see example of a master-detail form in Figure 2 of Lab 2) that allows the user to edit / create Service records and their related records in the Fills_Role and Service_Item tables. The form should display a single record from the Service table in the top section, together with a subform with related Fills_Role records, and in the bottom section, a subform with related Service_Item records (5 points). All foreign key fields and code-type fields in both master and detail sections should have dropdowns that facilitate data entry, as in lab 3; be sure the dropdowns contain descriptive labels/names, rather than foreign key or code values (10 points). Format the form attractively, and use space efficiently to allow as much information as possible to be displayed (5 points). (total: 20 points)

   Tip: Review the videos on Microsoft Access and your work on lab 2 if you need a refresher on creating master-detail forms and dropdowns. Also, watch this supplementary video on dropdowns that contains helpful bonus material.

4. Create a MySQL view named Service_View that pulls data from Service, Service_Item, and related tables and presents it like this (15 points):

   

   Notes:

   • The Name column should display the Ensemble name related to the Service_Item, if there is a related Ensemble. Otherwise, it should display the related Person’s first and last name, if there is a related Person. Otherwise, it should be NULL. Note that you must use the CONCAT function to concatenate the Person’s first and last name, because MySQL does not support the standard || operator. 3 points.
   • The Title column should display the song number and title if there is a related congregational song. It should display the title from the song table if there is a related choral song. Otherwise, it should display the Title column from the Service_Item table. 3 points.
   • Tip: The songleader, organist, and pianist name columns are tricky, and are worth 4 points. Although it’s possible to define a single view that generates all of this data, you can considerably simplify the problem by creating three separate views named songleader, organist, and pianist. Make the songleader view have two columns: service_id and songleader_name. It should contain a list of service ID’s and the songleaders assigned for those services. Organist and pianist views should have a similar structure. Then, make your Service_View join with your songleader, organist, and pianist views to pull in the correct names. Note that the screenshot shows only the last name of the songleader, organist, and pianist, but your view should include both first and last name.

5. Create an Access master/detail report (not form) named ServiceOrders that generates service order documents. The report should be formatted like the sample service order documents. Link to the Service_View you created in the previous step to facilitate writing the query for the report (Access will allow you to link to the view as if it were a table in MySQL; you will need to select the Service_ID and Seq_Num columns as the “primary key” when you do so). There should be a page break at the end of each service order. (15 points)

Academic Integrity

You are expected to do your own work on this project, although you may ask others for help as you debug your work. If you did not complete lab 2, you must complete that assignment before you may ask for help on the Access portion of this project. (You may get all of the help you need on lab 2.)

Submission

Write a brief report discussing any special features or known problems with the form, view, or report. Include a screen shot showing your Access form with the data from the Sunday a.m. Service order, and sample pages from your reports. Also, include all CREATE VIEW statements and any stored functions you may have created, formatted neatly, together with a screen shot of the output of your view.

Use the following command to export your MySQL database to a SQL script:

mysqldump -u root -ppassw0rd -R wsoapp >wsoapp.sql

Replace passw0rd in the above command with your database password (note there is no space between the -p and the password). Note the -R option, which includes stored procedures and functions in the dump.

Submit your report.pdf, wsoapp.sql, and your wsoapp.accdb, to the Canvas DB App 1 folder.