In this phase of the project, you do further application development work. CS majors must do Option 1. Other majors may choose either option. You can earn up to a maximum of +10 bonus points by taking advantage of bonus options.
You may do the work alone, or with one other student who is in your major. Students in different majors must obtain special permission from the instructor to work together. Students who choose the option to work together must both have a class average of 70% or better and must have a good track record of submitting assignments on time. Aside from the help given by your team member (if you have one), keep track of the design / debugging help you receive, so that you can include it in your integrity report. Teams must be finalized by Tuesday, December 3, and by that date, one team member must send an email to the instructor, copying the other team member, notifying the instructor of the team formation.
Note: You may not change the structure of the database tables. However, you are encouraged to include additional sample data to help test.
Create a new MySQL database named wsoapp2. Import the script from the App 1 phase to populate it with tables and data.
Create a web application that allows the user to create a new service based on an existing service (the “template”). The Python Flask framework is suggested, but individuals may use any technology or language that they can deploy to a public web server. Teams must receive special approval to use any technology other than Python Flask framework.
Note: If you do a Flask app, put database connection information in separate file named dbconfig.py (copy the class example and modify the credentials for your environment), and name your file wsoapp.py.
The initial page should display a table of all of the services in the database that includes, the service date, time, and theme. Each service should have a link or button that navigates to a detail page with information about the selected service.
The detail page should display details about the service selected on the initial page,
including the service date/time, theme/event, and songleader name, as well as a list of
all of the service events sorted by sequence number, including song titles, person name, and notes. Below this information, display a form that
prompts for the following info to create a new service (the user must enter items with an *; the
other items may be left blank): (20 points)
*Date/time for new service – clearly indicate the format the user must follow when entering the date/time. Fill in the current date/time as the default value.
Theme (default to the theme of the selected service)
Songleader – Allow the user to select from a list of names of previous service songleaders. The list should be sorted by last name and contain no duplicates. It should default to no songleader.
When the user enters this information and clicks a “Create” button, call a stored procedure you have created named create_service that does the following, then display a page indicating either success or an appropriate error message:
Verify that there is no existing service at the date/time specified for the new service. Return an error code if there is already a service at that date/time. (10 points)
If there is no service at the specified date/time, insert a record into the Service table for the new service using the specified date/time, theme, and songleader. For values that the user left blank, use NULL. (10 points)
Enhance the create_service stored procedure to insert records into the ServiceEvent table for the new service based on the events in the selected template service, except that the specific songs, personnel, and ensembles should be left blank. For example, if the user selects 10/3/2010 10am for the date/time for the template service, the program should insert ServiceEvent records for the new service that have the same sequence numbers and event types as those for the 10/3/2010 10am service. (10 points)
Tip: The creation of all of the ServiceEvent records for the new service can be done with a single, carefully constructed INSERT statement. Review the notes to find the form of the INSERT that can generate records using a SELECT.
Bonus: In the final step of the application, allow the user to select songs to be assigned to the congregational song events in the new service. Create a view named SongUsageView that displays all of the colums in the Song table, plus one named LastUsedDate. The LastUsedDate column should contain the date of the most recent service that used that song. (Exclude choral songs, but be sure to include songs which have never been used). Using this view, display 20 of the least recently used songs, ordered by LastUsedDate, and then song title. Allow the user to select songs from this list, and assign them to the congregational song events. (10 points)
15 points. Code, including view and stored procedures, formatted neatly. Python code written using techniques that resist SQL injection.
Write a report that indicates which features you implemented, and known bugs. On the first page of your report, after the title page, include a link to a screen recording that demonstrates the tool. If you created any views or stored procedures, the report should also include those create statements (format these neatly, with good indentation). Include a signed academic integrity statement. Create a PDF of your report named report.pdf. Teams should submit a single, joint report indicating the amount of time each team member spent, and which member implemented which features.
Use the following command to export your database to a SQL script:
| mysqldump -u root -ppassw0rd -R wsoapp2 >wsoapp2.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 to include stored procedures in the dump.
Zip up your project to a file named wsoapp2.zip. Upload to Canvas, together with the wsoapp2.sql script and your report.pdf.
For teams, only one team member should do an electronic submission.
If you did your project using some technology other than Flask, deploy it to a public web server and provide the URL to your deployed application on the front page of your report.
Create views and stored procedures to solve the following problems. You may find it helpful to define additional views to solve some of these. Do 5 problems for full credit; if you do all 6, you can earn up to +10 bonus.
Create a stored procedure named AvailablePianists which takes a single parameter, for_service_id. It should contain a SELECT query that displays personnel who are available to serve as pianist for the service whose ID is for_service_id, based on the following criteria: 1) they have served as a pianist in at least one service and 2) they are not listed in the Unavailable table for that service. The query results should include the following columns: Person_ID, First_Name, and Last_Name. Sort by Last_Name, then First_Name.
Tip: The tricky part here is removing the personnel who are not available. If you can’t get this part working, for partial credit, focus on just getting personnel who have served as a pianist.
To help the songleader choose songs for each service, create a view named SongUsageView that displays all of the colums in the Song table, plus one named LastUsedDate, and one named Theme. The LastUsedDate column should contain the date of the most recent service that used that song, and the Theme should be the theme of the service that used it. Include all songs in the SongView, even those which have not been used in a service. However, the view should not include choral numbers.
Create a stored procedure named LeastUsedSongs that displays the top 10 songs from the SongUsageView, when ordered in ascending order on LastUsedDate and then Title. Use the SELECT LIMIT feature of MySQL to limit the number of rows to the top 10.
Tip: Getting the Theme correctly included is what makes this one particularly tricky (watch out: getting it incorrectly included is deceptively easy). If you can’t get it working correctly with the Theme included, omit the Theme and receive a max of 12 points.
Create a view named ServicePersonView that displays the names and e-mail addresses of all personnel involved in a service (organist, pianist, songleader, soloists, people in any ensembles, etc.). The view should include the following columns: Service_ID, Person_ID, First_Name, Last_Name, and Email. Eliminate duplicates from the list.
Create a stored procedure named ServicePersonnel that takes a parameter for_service_id. Display the information from ServicePersonView for the indicated service, sorted by last name, then first name.
Create a view named ServiceConflictsView that displays the names and e-mail addresses of all personnel involved in each service (i.e., those in the ServicePersonView) who are also listed in the Unavailable table for that service. The view should include the following columns: Service_ID, Person_ID, First_Name, Last_Name, and Email.
Create a stored procedure named ServiceConflicts that takes a parameter for_service_id. Display the information from ServiceConflictsView for the indicated service, sorted by last name, then first name.
Create a view named UnconfirmedPersonnelView that displays the names of personnel involved in a service (organist, pianist, songleader, people associated with individual service events, ignoring people in ensembles) who have not been marked confirmed for the service. This view should include the following columns: Service_ID, Person_ID, First_Name, Last_Name, and Role. The Role column should read as follows: “Organist” for an unconfirmed organist; “Pianist” for unconfirmed pianist; “Songleader” for unconfirmed songleader; and for someone associated with an unconfirmed service event, display the Description of the EventType related to that event (“Prayer”, “Prelude”, etc.).
Create a stored procedure named UnconfirmedPersonnel that takes a parameter for_service_id. Display the information from UnconfirmedPersonnelView for the indicated service, sorted by last name, then first name.
Create a small flask web application that displays the output of one of the stored procedures above. The application should display a form that allows the user to specify the parameter value for the stored procedure. On submission of the form, the application should pass the parameter to the stored procedure, then display the results of the stored procedure. Review the lab 4 assignment for an example of how this should work.
Take time to do a final check of all of your stored procedures and views. Look at the results they produce and check the data in the tables to verify that you are getting correct results. You will receive additional deductions if your solutions produce incorrect results and you fail to note the issues.
Write a report that contains the view and stored procedure create statements (format these neatly, with good indentation). Paste output into the report showing that the views and procedures work properly. If you did the web application, include a screenshot showing the web application output. Mention known bugs, and include a signed academic integrity statement. Create a PDF of the report named report.pdf. A team should submit a (single) joint report, indicating the amount of time for each team member, and which member implemented which features.
Use the following command to export your database to a SQL script:
| mysqldump -u root -ppassw0rd -R wsoapp2 >wsoapp2.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 in the dump. Double-check your wsoapp.sql to verify that it contains your stored procedures.
Submit wsoapp2.sql, report.pdf, and your python web application program (if you did it) to Canvas.
For teams, only one team member should do an electronic submission. If team members divided up the work instead of working on it together, make sure that the team member doing the submission has all of the views and stored procedures in the submitted database.