2 parts see guideline do access and excel

V475/I519 Fall 2015 Final Exam

Take Home: 150 points total

Honor System: Do your own work. Collaboration is not permitted on this exam. You may ask me any clarifying questions up until the due date/time regarding this exam.

Due Date/Time: Thursday, Dec. 17, by 7pm (10% penalty for every HOUR late)

This exam has two parts:

Part 1: Access DB Development using the MVCH file

Part 2: SQL and Web/Database Interaction using Oracle and Mentor

Submission: You will submit 2 files: one Access database file and one Word file in the Final Exam item in Assignments. Complete Part 2 before submitting your Part 1 Access file, so you can submit them together.

Part 1: Access DB Development (70 pts.; 10 points each)

The MVCH database that we have used in class tracks Treatments performed. We now want to track Treatments used in Facilities, and we want to record which Facilities are used for which Treatments. Download the Final Exam Version of the MVCH file from Canvas. Perform each task below. When complete, re-name the database to: yourusername_final_exam and upload your file to the Final Exam item on Canvas. Be sure to close Access before submitting your file.

1. Create a Facilites_Tbl using standard DDL. The table is to have the following 4 fields: Facility_ID, Name, Location, Facility_Manager (from employee_id). Define Facility_ID as the primary key for this new table. Facility_Manager is an FK field that references the EmployeeTbl. Save the SQL statement used to create the Facility_Tbl with an appropriate and descriptive name, such as Create_facility_table_qry.

2. Using standard DDL, add (INSERT INTO) the following Facilities into the new table (Save the “Insert Into” queries with names like Add_Data_to facilities_qry1 for all 5 additions.):

Fac_ID NAME LOCATION FACILITY_MANAGER

1 Radiology 3011 207

2 BioChemistry_Lab 1655 195

3 Surgery 2449 208

4 Pharmacy 4987 170

5 Physical Therapy 1509 199

3. Each of these Facilities are used in many Treatments at MVCH, and each Treatment may require multiple Facilities (M:M relationship). Thus, another table is required to record and track Facilities used in Treatments: Facility_Treat_Tbl . Create this table using any method you prefer. Define the relationships between this Facilities_Treat_Tbl and the FacilitiesTbl and Treatment_Tbl tables. Enforce referential integrity.

4. Now we need to assign some Treatments performed in the Facilities. Create a Form (Title: Facility Data Entry Form) with one SubForm (Treatments that use the Facility (Be sure to reference the table you created in the previous step!)) to allow data entry into the Facility_Treat_Tbl. Save the form with the name Facility_Treat_Data_Entry_Frm . See image below to see how you might structure your form. Using the form, enter the following data:

FACILITY TREATMENT_ID

Radiology 6,4,19,5

BioChem_Lab 1,8,13,14,20,6

Surgery 2,5,7,9,10,13,21,15,4

Pharmacy 1,5,8,13,16,21

Physical Therapy 3,8,9,16,17

5. Add a counter to the form that calculates the number of Treatments at each Facility. You will need a Macro that employs SETVALUE with the DLOOKUP function for this. To the main part of the form you created in item 4 above, add a Text-box labeled “Count of Treatments. ” When a Facility is selected using the record selector at the bottom of the form, the text box will then display the Count of Treatments that use the (currently displayed) Facility.

Guidance: With your Macro, you will need only one action with Item and Expression. The Item will be the text box you added. DLOOKUP may appear in the Expression. Create a simple (2 column) query that calculates the number of Treatments at each Facility. The criteria in the DLOOKUP will need to reference the Facility_ID field in your form. Save your Macro. Your form should look something like this:

6. Create a Report from Crosstab query for treatment episode data. Import all of the TreatmentEpisode.xlsx data into this Access file (BOTH the 2014 data and the substance codes) and create a crosstab that will answer the following question: A recent outbreak in HIV has occurred in rural Indiana. A grant writer needs some data for a grant proposal in progress and has asked you for Heroin and Methamphetamine treatment episode data for males and females for Scott and Jackson counties. You will need both tables to provide user friendly output for the report. Do not expect your audience to know what the codes mean for the substances. Hint: Construct your Crosstab query from a basic query that lists all of the data from the two counties and the Substance Description.

Your Report/output should look something like this:

2014 Jackson and Scott Counties
Substance	F	M	Total
Heroin	xxx	xxx	xxx
Methamphetamine	xxx	xxx	xxx

Table 1: The xxx’s will be replaced with numbers you have determined from the crosstab

Be sure to format your Report well. It should look professional.

7. Create a Main Menu form that opens when Access starts. Add one image of your choosing to the menu with an appropriately centered title. On one side of the menu screen, add a rectangle with buttons inside that open the Facility Data Entry Form you created and the TreatmentCount query. On the other side of the menu, add buttons that open the heroin/meth report and crosstab query.

Note: If you are not successful with the previous exercises, you may substitute other objects to receive points for this item.

Save your Access file as yourname_FinalExamMVCH. Submit on Canvas in the Final Exam item under Assignments after you complete Part 2 below.

Part 2: SQL and Web/Database Interaction (80 pts.; 9 tasks)

In this section, you will import data into Oracle and write PHP scripts that display answers to the questions provided. Make sure your SQL code displays the results without error in SQL Developer prior to your adding the code to your PHP script. Partial credit will be given for unsuccessful “in the ball park” attempts at answering the questions below.

Use the Part2_Answers_Template file to copy and paste your code for further review.

Obtain the file DonorsData.xlsx from Canvas. Examine the data carefully.

Do this first:

->Locate the people worksheet and enter your name for the last record #9999. <-

_____(10 pts.) Task 1: Using SQL Developer, import all tables into your Oracle account. BE CAREFUL!!

Suggestion: I recommend using a public STC computer and running SQL Developer from the local computer to complete the imports. You may use IUAnyware to execute the queries after that.

Follow these guidelines when importing data for the Donations, Events, and Volunteershift tables:

SQL Developer tends to want to assign the VARCHAR data type to every field during import. As such, the data type for fields that contain DATES and NUMBERS should be manually assigned during the import.

· Set the ID fields (both PK and FK) to INT for every table.

· For the AMOUNT field in Donations select the NUMBER data type.

· For the HOURS field in Volunteershift, be sure to specify the FLOAT data type, since these contain decimals. See image:

You must tell SQL Developer what format the date is when you import it. You will need to type YYYY-MM-DD in the Format field as follows:

Note the date format of YYYY-MM-DD. This is important.

It is imperative that you import all of the data correctly. The queries you create will not work properly if not. Be sure to check the tables to ensure that the data match what was provided in the Excel file.

You will submit your URL on Canvas and on the Part2_Answers_Template Word document in the designated location.

Your PHP script should execute from your Mentor account. I will test this using the URL you submit.

**Create ONE file that contains all of your links to each of the PHP scripts below. Do NOT submit each URL individually. You may use any format you wish (ie., HTML or PHP). Suggestion: Recycle your code from Assignment 15.

Read each question carefully. Be sure to copy and paste your SQL code into the answer template and submit that file on Canvas.

____(5 pts.) Task 2: Create a list of Donors (people) who live in the zip code 47401. Include their first and last names, address, city, state, zip and phone numbers. Sort by PersonID in descending order. Your name should appear in row 1. Add the code to a PHP script and save on Mentor. Name the script final_q2.php .

____(9 pts.) Task 3: Using concatenation of the last name and first name separated by a comma, create a query that shows your lastname (comma) , firstname and each donation you donated. Name this final_q3.php

The output should look like this:

(Reminder: You will need to use an escape character ( \ )on the single quote to make this work.)

____(9 pts.) Task 4: Create a list of people (firstname and lastname) who worked at the Pledge Drive event. Name this final_q4.php . Your output should include “Pledge Drive” in the results:

(Hint: You will need three tables to obtain these results.)

____(9 pts.) Task 5: Display all people by PersonID, first and last names and their total amount donated in descending order by PersonID. Do not list their donations individually. The display should have one number showing the grand total of each person’s donations. Name this final_q5.php

Output should look like this:

____(9 pts.) Task 6: Create a list of all events and the total number of participants for each event ordered with the largest number of participants at the top. (2 columns displayed) Name this final_q6.php. Your output should look like this:

____(9 pts.) Task 7: Display Levi Strauss by name along with the total number of hours he volunteered. The output should look like this:

Levi

Strauss

xx

Name this final_q7.php

____(10 pts.) Task 8: Use the “nested query” technique (no table JOINs) to list the names of People that attended the Food Drive event (EventID 10) Name this final_q8.php. You must use nested query in this question to receive full points. Output should produce a table as follows:

The results should include your name and the bottom row should be “du Plessis”

Hint: Get the PersonID from the Participation table for that particular event first.

____(10 pts.) Task 9: Create a View. All people who made a Memorial or Honorary Gift need to be contacted by phone. Create a view called MemGift that includes their first name, last name, phone number, and donation type (displaying only Memorial or Honorary Gift). Display all contents of this view in your web page. Name this final_q10.php.

7