access f
pd535402620
V475-I519FinalAppliedPracticeTakeHome.docxSpring 2019 V475/I519 Final Applied Practice
200 points total
Honor System: Do your own work. Collaboration is not permitted. You may ask me any clarifying questions up until the due date/time regarding this exercise.
Due Date/Time: Thursday, May 2nd, by 12pm (10% penalty for every HOUR late—I must send grades to Registrar!) Accommodations made for special circumstances.
This activity has two parts:
Part 1: Access DB Development using the yourname_BH_Final_Practice_Version file (90 pts.)
Part 2: SQL DB Development using MySQL (110 pts.)
Submission: You will submit 2 files: one Access database file and one Word file in the Final Applied Practice item in Assignments. Submit both sections together at the same time.
Part 1: Access DB Development (90 pts.; 10 points each)
The BH_Final_Practice Access 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 BH_Final_Practice file from Canvas. Perform each task below. When complete, re-name the database to: yourusername_final_practice and upload your file to the Final Practice item on Canvas. Be sure to close Access before submitting your file.
1. Create a Facilites_Tbl using standard DDL (you must write the SQL code to create the table). The table is to have the following 4 fields: Facility_ID, Name, Location, Facility_Manager (referencing 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 code used to create the Facility_Tbl with an appropriate and descriptive name, such as Create_facility_Tbl_qry.
2. Using standard DDL, add (INSERT INTO) the following Facilities data into the new table (Save each of the “Insert Into” queries with names like Add_Data_to facilities_qry1 for all 5 additions[footnoteRef:1]): [1: There should be 5 append queries visible in the navigation pane when done each with the data provided above. ]
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 perform many Treatments at BH, and each Treatment may require multiple Facilities (a 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. {Note--There are no requirements on HOW you do this—writing the SQL code or Design View are both acceptable. Just create the table correctly.}
4. Now we need to assign some Treatments performed in the Facilities. Create a Form called 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,6,8,13,14,20
Surgery 2,4,5,7,9,10,13,21,15
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 textbox labeled “Treatment Count.” When a Facility is selected using the record selector at the bottom of the form, the text box will then display theTreatment Count that for the Facility (that is currently displayed).
Guidance : With your Macro, you will need only one action with Item and Expression. The Item will be the textbox you added. The DLOOKUP may appear in the Expression section of the macro. 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:
You have two options to display the Treatment Count. Try to have it update automatically when you select a new facility. If you can’t get that to work, just add a Count button {as shown}.
Also, be sure to include navigation buttons to select the next record.
6. Create a Report from a Crosstab Query for treatment episode data. Import all of the 2017_TreatmentEpisode.xlsx data into this Access file (BOTH the 2017 data and the substance codes) and create a crosstab that will answer the following question: A recent overdose outbreak has occurred in rural Indiana. A grant writer needs some data for a grant proposal in progress and has asked you for Heroin and Other Opiates and Synthetics treatment episode data for males and females for Orange , Daviess , Scott, Vigo and Jackson counties. You will need both tables from the Excel file 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:
|
2017 Orange, Daviess, Scott, Vigo & Jackson Counties |
|||
|
Substance |
F |
M |
Total |
|
Heroin |
xxx |
xxx |
xxx |
|
Other Opiates & Synthetics |
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. Add a button to close the report and return to the Main Menu.
7. Produce lists of Referred Patients and Treatment Names when a Physician is selected. Create a form called Physician Referrals & Treatments. Add a combo box that lists all Physicians by name. Add two buttons. One button will open a list of referred Patients ( not treated patients!). The other will open a list of Treatment Names that a doctor has performed. You may use the query design to construct your queries. Each button when pressed should open the query and display the results in datasheet view.
Bonus points option: Add a Tab Control to the form and display the query results in the subforms when the user selects a doctor. {This is optional. Bonus points only.}
8. Create a Parameter Query. You do not need a form for this. Create a query that lists Patient First and Last names and prompts the user to enter a type of Treatment Name. This is to be a wild card search. For example, if the user enters “coro”, a list of all patients should display who have had coronary related treatments. Be sure to test your query. Name the query Patient_Treatment_parameter_qry.
9. Create a Main Menu form that opens when Access starts.
Requirements:
1. Add one image of your choosing.
2. Appropriately centered title: Bloomington Hospital Data Portal. Add YOUR NAME to the Main Menu anywhere you like.
3. On one side of the menu screen, add a rectangle with buttons. Inside that rectangle box, add buttons to OPEN the following objects:
a. The Facility Data Entry Form (step 4 above)
b. The TreatmentCount query
c. The Physician Referrals form you created in step #7 above.
4. On the other side of the main menu, add buttons that open
a. The heroin/opiates report
b. The crosstab query
c. The parameter query (#8).
5. Add a button to Exit the database on the main menu.
6. Main Menu MUST open on start up.
7. Must be reasonably pleasing to look at (ie., no typos, all text showing, easy to navigate—No sloppiness!)
8. Follow proper navigation rules. Be sure to add a button on all forms you create that return to the user to the Main Menu.
Make sure buttons are labeled clearly. Don’t make the user guess!
Note: If you are not successful with the previous steps, you may substitute other objects to receive full points for #9 above. The point is to have a complete menu with navigation to the objects you created earlier. Please --no message pop ups!
Save your Access file as yourname_BH_Final_Practice_Version.accdb. Submit on Canvas in the Final Applied Practice item under Assignments after you have completed the MySQL Part 2 below. Submit all work at the same time!
Part 2: SQL and Database Development (110 pts.)
In this section, you will import data into MySQL and write SQL scripts that display answers to the questions provided. Partial credit will be given for unsuccessful “in the ball park” attempts at answering the questions below. Don’t sweat it if you think you have the correct code but get no results. I will be looking for evidence that you know what you are doing. A single misplaced comma can break the whole thing!
Use the Final_Applied_Practice_Answer_Template file to copy and paste your code for further review. Do NOT submit this file.
Obtain the files from Canvas located with this Assignment:
MOVIE.csv
DIRECTOR.csv
City.sql
Countrylanguage.sql
Nations.sql
Section 1: The Movies and Directors DB
IMPORTANT--Do this first!!!:
->Edit the Directors.csv in Excel and enter your name and cntry for the last record #999. <-
Be sure to save your file. If you use a non-US country, be sure it matches other data!
Task 1: Data Import and Table Setup (10 pts.)
Using the Data Table Import Wizard in MySQL Workbench, import the data in the MOVIE.CSV and DIRECTOR.csv files. MAKE SURE you have entered your name as a director first at record #999!
1) Properly select the data type for each field during the import process. Pay attention to that! 2) Set the primary key of each table after import.
Screen capture evidence that you have properly imported the data from both tables and save that to the answer template. If you are not sure about this, ask.
Task 2: Add Movies (5 pts.)
Add two movies that have been directed by you. Here is the required info:
Movie 1—Title: The Education of Yourname; Yr: 2018; Category: Drama; MPAA: G; Len: 120
Movie 2—You make up a Title, Category, MPAA. Set the Length to 90 and a USGROSS of 388668. The other field data (ie., awards) are optional.
Screen capture your SQL code that you used to add the data for both movies and display the results of the SELECT query to show you were successful.
Task 3: Enforce Referential Integrity Between the Tables (5 pts.)
Use the proper SQL command to setup the PK-FK relationship between the two tables to enforce referential integrity. Screen capture the results and paste to the answer template. Your screen capture should show that you have done this successfully.
Task 4: Write a SELECT statement that answers the following question (4 pts.)
What movies have you directed? Include your name in the results. There should be two columns—the director’s name (You!) and the title of the movies. The results will be more than 2. Be sure to use the PK for your name in the query criteria.
Task 5: SELECT statement that answers the following question (4 pts.)
Which movie directors have directed Drama movies? List the director names and titles of the movies in your results.
Task 6: SELECT statement that answers the following question (5 pts.)
Using the query you created in Task 5, which directors have directed MORE THAN 2 drama movies? List their names and the number of movies directed.
Task 7: SELECT statement that answers the following question (5 pts.)
Which directors, sorted from highest to lowest, have had worldwide gross ticket sales greater than $200,000,000 (ie., 200 MILLION $$)? Important!--Notice how gross sales is saved in the data. What is going on here? You may screen capture the first 10 or 15 to show you have it.
Task 8: SELECT statement that answers the following question (7 pts.)
Which movies have some form of the word “Love” in the title? Format the output so that the title is followed by the text “—directed by”. It should look like this:
Task 9: SELECT statement that answers the following question (8 pts.)
Steven Spielberg has directed quite a few movies in various categories. In one SELECT query statement, calculate both the total number of minutes and the average number of minutes that Mr. Spielberg has directed for each category. There should be three columns of output: the category, the total number of minutes and the average number of minutes for all of the movies he has directed. Use the PK value for Mr. Spielberg in the criteria, just like you did for yourself!
Task 10: What can you do? Create your own SELECT statement. Write your question on the answer template. (7 pts.)
Be sure to state your question on the answer template and show your results. Your score will be based on applying what you have learned. A simple query will not receive full credit.
Section 2: The World DB
You should have downloaded and saved the three .SQL files (City.sql, Countrylanguage.sql,
Nations.sql).
Note: Be careful not to confuse the Countries table that we used with the Human Resources database some time ago with these queries!
This is going to be a bit trickier. You have to know how your data relates between the various tables. Study your tables carefully to determine how the data from one table matches the data in the other tables. PK – FK fields in relationships are not all named the same. You might want to write these down or sketch out a diagram.
Task 1: Non-African nations winning independence after 1970. (6 pts.)
Which non-African nations have won their independence after the year of 1970. Include the name of the country and the year of independence in your results.
Task 2: Create a VIEW called totalmetropop (12 pts.)
Calculate the total metropolitan population for all of the cities in each nation. Your output should have three columns—The code and the name from the Nation table and the total city population from the City table. Be sure to save your VIEW. You will use it in the next question.
Display the first 8 or 10 rows of results to show you successfully created the view after selecting all the rows in the table. Hint: use an alias for your total population. It will make the next task easier.
Task 3: Use the VIEW to calculate the difference in non-metropolitan populations for each country. (12 pts.)
What is the difference between the city populations and those living outside of the city? You will need to JOIN the view you created in Task 2 with the Nations table. Two columns of output—the name of the nation and the population difference should be seen. Aruba should be 73966.
Task 4: List the Official Language (12 pts.)
Which cities in Europe have French as their official language? Your output should include three columns and look like this:
Task 5: What can you do? Create your own select statement. Write your question in the answer template. (8 pts.)
8
