computer science portfolio
aajgdibn9609d7
Page: 1
Portfolio 3 – Access Total: 40 points
Start-up Instructions Locate the Portfolio3 folder from Scse148\StudentDataFiles\PortfolioAssignment folder. Rename the Portfolio3 folder as uniqueID_Portfolio3. This folder will contains UniversityDB.accdb, UniversityWorkbook.xlsm and MemoAndReflection.docx that are files you will use for your solution. The Portfolio3.pdf and Portfolio3DatabaseDesign.pdf are the assignment and database design files.
Paired Assignment ( N O T E : S u m m e r S e s s i o n s – y o u r i n s t r u c t o r w i l l i n d i c a t e i f t h i s i s a P a i r e d o r I n d i v i d u a l a c t i v i t y ) If this will be a Paired activity, it is recommended that you carefully review this entire assignment, and then
arrange a time to meet with your partner within a few days of the start of this assignment. As a team, you
should rename the 3 files noted in the Start-up as uniqueID_uniqueID_UniversityDB.accdb,
uniqueID_uniqueID_UniversityWorkbook.xlsx and uniqueID_uniqueID_MemoAndReflection.docx.
These will be the files you will update and turn-in once the assignment is complete. Please note there is an initial draft due – refer to the Daily Assignment Page for the date.
Purpose of the Assignment The assignment is designed to require students to use the software tools they have learned (Word, Excel, Access) in a simulated real-world task. This task also requires students to utilize critical thinking skills in order to complete the assignment. This task is not unlike what one will be required to do after earning a degree. The students are assigned a task by an executive (University President), and they need to think about the task and develop an approach for completing that task. In this case, the students need to think about the problem, the required analysis, and the data necessary to conduct the analysis. They will then use their skills in Access and Excel to obtain the data and perform the analysis. Students are then asked to summarize their findings and conclusions and communicate these in a memo to the executive (University President). This effort brings together what students have learned in the class along with analytical skills they have developed during their past experiences and education.
Continued on next page
Page: 2
PART I (University Memo) 30 Points Filenames: uniqueID_uniqueID_UniversityDB.accdb; uniqueID_uniqueID_UniversityWorkbook.xlsm; uniqueID_uniqueID_MemoAndReflection.docx You are provided with an Access database (UniversityDB.accdb) similar to the one we have been developing in our recent Classroom Learning Activities. The database includes Student, Course, Class, Enrollment, and Major tables with field definitions described in the file Portfolio3DatabaseDesign.pdf.
You have been requested to provide a report to the University President to address one of the following two (fictitious) areas of interest. For the area of interest selected, you will need to conduct multiple analyses of the data (see grading rubric to determine how many separate analyses to perform). The purpose of multiple analyses is to consider different alternatives or ways of looking at the data before drawing a conclusion. You will select appropriate data from the database (different analyses may or may not require different selections of data), COPY the data from Access into Excel, and use Excel to conduct the multiple analyses of the data. Once you have completed all analyses, summarize your analysis and conclusions in a memo suitable for delivery to the University President. Your memo should contain a discussion that summarizes the multiple aspects of your analysis and includes charts and tables as appropriate that support the discussion. You then need to provide a final conclusion that is supported by the analysis.
a) University students undergo a significant change during their time at Miami. Beginning with the Entry Year 2005, the university implemented programs to help students become better students with the hope that student GPA’s might improve as students move from the Freshman ranks to the upper class ranks. Provide an analysis that compares GPA’s earned by students as Freshman with scores earned by those same students as Juniors for Entry Years 2005-2009 and make an assessment whether the programs have been effective or not. Refer to the “notes” portion of the Portfolio3DatabaseDesign.pdf document to understand the relationship for class rank, entry year and academic semesters. [Hint: Don’t worry about calculating a GPA by student, rather consider calculating an aggregate GPA as Freshman for each Entry Year and aggregate GPA as Juniors for each Entry Year – this approach is similar (not the same) to CLA #16 – see your instructor if you have questions]
b) Beginning with the Entry 2005 students, a test program was implemented to help make sure that Seniors continued to make strong academic strides that they made during their Junior year. There was concern that students “took it easy” as Seniors and as a result their GPA suffered during their Senior year. The university would like to compare the performance trend of Juniors from entry years of 2005-2009 with the performance trend of those same students as Seniors. and make a recommendation as to whether the test program seemed to achieve the goal of helping Seniors maintain or improve upon their Junior year GPA. Refer to the “notes” portion of the Portfolio3DatabaseDesign.pdf document to understand the relationship for class rank, entry year and academic semesters. [Hint: Don’t worry about calculating a GPA by student, rather consider calculating an aggregate GPA as Juniors for each Entry Year and aggregate GPA as Seniors for each Entry Year – this approach is similar(not the same) to CLA #16 – see your instructor if you have questions]
See next page for a list of technical requirements
Page: 3
Your analysis must contain the following technical components: I. Using Access, create appropriate queries (may only require 1 query, or it may require multiple
queries) to select the data needed for each of your analyses. Save the queries with appropriate names. Copy/Paste the information from these queries to Excel.
II. Using Excel, analyze the data from your queries for each of your analyses. Your spreadsheet should include: a. Appropriate use of sorting, subtotals, pivot-tables, or other features in order to adequately
analyze the data in an efficient manner. b. Each worksheet is well-organized (worksheet can be understood without the student
explaining the information on the worksheet) c. Nicely formatted tables to display key portions of your analysis. d. Nicely formatted charts that clearly display key portions of your analysis. Important Notes: You will need to use a single excel file (workbook) for all of the excel analysis and charts. You should use multiple sheets that contain downloaded data, tables and charts for each analysis. Each worksheet should be nicely formatted, titled, well organized and if necessary, include comments. Any unused sheets should be deleted.
III. Using Word, write a memo with your analysis/conclusions to the University President. The memo should include: a. Appropriate business memo format. (A template is not necessary but may be used. If you are
not familiar with a memo format, do some research.) b. Appropriate titles, section headings, and other formatting to make the report attractive and
easy to read. c. The writing-style and language used would be suitable for a memo to the University President d. Your written analysis should not describe the steps you took to analyze the data, rather it
should describe (analyze) what the data indicates. e. Insert into your memo 1-3 Charts and/or tables of data from Excel to support your analysis
(use summary information – don’t paste a table that has lots of rows and columns of detail). Recall how to use Text Wrapping. [Clarification: At least 1 chart is required. A table of data from Excel may or may not be helpful. When counting both charts and tables of data, the count should range from 1-3. The purpose is to enhance the memo and support your analysis.]
f. Introduction paragraph, final conclusion paragraph and a separate discussion for each analysis that summarizes the analysis. Each discussion should be 1-2 paragraphs.
g. In total one should expect a memo that is 2-4 pages. h. The report should be well-written, easy to read, and free of grammatical errors.
Example of multiple analyses This explanation is provided to assist you in understanding what is expected from the multiple analyses required.
Suppose the area of interest to be analyzed was the student credit hour load for the 2007SP semester and to understand the percentage of students who are below 14 credit hours and those who are above 18 credit hours. One analysis might be to study this by grade level (freshman, sophomore, junior and senior). A second analysis might compare the percentages by gender. A third analysis might evaluate the percentages by major. A fourth analysis might be to analyze these percentages by in-state versus out-of-state. After conducting these 4 analyses, you will have a more holistic view of the data and are then in a position to write an informed conclusion.
Page: 4
PART II (Reflection) 10 Points Filename: uniqueID_uniqueID_MemoAndReflection.docx Each student will write their own 1-2 page (12 pt type, double-spaced) reflection on their use of Access and Excel on this assignment as well as their perspective of the collaborative effort. This reflection should be in the same document as the memo in Part I, but begin on a new page with an appropriate title. Since we are working in pairs, there will be two written reflections in this part of the document. Headings for the reflection should include the student’s name. This reflection should address the following questions:
I. Which Area of Interest (a or b) did you select for this assignment and why? II. You conducted more than 1 analysis of the data. What was the most significant thing you learned
about the data by performing the additional analyses? How did this affect your final conclusion? III. This project required you to collaborate with another student. Indicate the following about
collaboration: a. List those qualities of collaboration that were positive/beneficial when collaborating on
this assignment b. List those qualities of collaboration that were difficult/negative when collaborating on this
assignment c. You were a part of the collaborative effort, please indicate your areas of strength and
areas of weakness as a collaborator (e.g. in what ways were you helpful, and in what areas do you need to improve to be a better collaborator)
IV. In your experience, explain the strengths of Access (what it is best used for) and explain the strengths of Excel (what it is best used for)?
V. How might you use the knowledge you gained in Access and Excel in the future? This reflection should be well written, easy to read, and free of grammatical errors.
Sample Solution Problem A SampleSolution video has been created for the analysis question below. The video demonstrates how
to use Access and Excel to produce a table and chart that would be appropriate for including in the memo
to the university president. This sample solution only addresses one of the multiple analyses that should
be performed.
Example Area of Interest: Student credit hour loads for the current semester (2006-07SP
semester) are an important measure for the university. Provide an analysis of the number of credit
hours taken by grade level. Be sure to note percentages of students who are below 14 credit hours
and those above 18 credit hours. Do any of the majors contribute disproportionately to the
number of students below 14 or above 18 credit hours?
The video is available on the Daily Assignment page for Portfolio 3 under the Day 30 page.
Page: 5
Submission Instructions: If you have not already done so, create a folder named: uniqueID_uniqueID_Portfolio3 In this folder, place the following files:
uniqueID_UniqueID_UniversityDB.accdb uniqueID_UniqueID_UniversityWorkbook.xlsm uniqueID_UniqueID_MemoAndReflection.docx
By 11:59pm on Friday, April 24, a draft of your completed portfolio (uniqueID_uniqueID_Portfolio 3 folder) must be turned in to S:\EAS\turnins\cse148\yoursection\Portfolio3\Draft. This draft only requires that ONE of the multiple analyses be completed in the UniversityWorkbook.xlsm file. This would also mean that one or more queries in Access are completed. TheMemoAndReflection.docx document does not have to be started. By 11:59pm Tuesday, May 5, the final version of the portfolio is to be submitted. Submit the uniqueID_uniqueID_Portfolio 3 folder to: S:\EAS\turnins\cse148\yoursection\Portfolio3\final
Critical Thinking Levels: The portfolio assignment will be used as a sample in measuring critical thinking. This assignment is designed to support an analysis of the following three areas of critical thinking.
Organization: Organizes information in meaningful ways to encompass problem complexities
Analysis: Interpret Information; qualitatively interpret evidence from a variety of points of view; organize information in meaningful ways to encompass problem complexities.
Conclusion: Articulates a conclusion that is a logical outcome of the analysis of the data completed.
Communication: Communicate appropriately for a given audience and setting.
See next page for the Grading Rubric
Course Objectives: 1.5; 2.1; 2.2; 2.3; 2.4; 2.5; 2.6; 3.2; 3.3; 3.4; 4.1; 4.2; 4.3; 4.4; 4.5
Text Reference: Excel tutorials 1-7; Access tutorials 1-4
Problem Source: original
Page: 6
Grading Rubric
Part I – University Report (30 points) Grade Technical Analysis
Excellent (13-15)
Efficient Access queries are created and named appropriately. Appropriate Excel features are effectively used to perform the analysis. The workbook is well organized. Appropriate charts are created to support the analysis. Excel tables and charts are imbedded in the Word report. The Word report prepared in a professional manner (formatting and style), appropriate for a University President, is easy to read and free of spelling and grammatical errors.
3 or more analyses conducted. All analyses are thorough and relevant. Analysis is appropriate (correctly using counts and/or percentage calculations). Prepared a complete discussion of the analyses and conclusion for the selected area of interest. The data (tables, charts, etc.) are referenced adequately. The discussion of the analyses and the conclusion are based on the data provided.
Good (9-12)
One or more significant technical components of the exercise were missing or completed improperly. Excel sheets generally well-organized and easy to follow, although improvements could be made. Excel features used were mostly appropriate and were generally used effectively, although improvements could be made. The Word report may require minor formatting improvements, or the style could be improved for an audience of the University President. The document is generally easy to read although some improvements could be made. Document is free of spelling and grammatical errors.
Conducted only 2 analyses. Some analyses may not be thorough, or were not appropriate for the investigation. Prepared a good discussion of the analyses and conclusion for the selected area of interest. The data (tables, charts, etc.) are referenced, although not as thoroughly as desired. The discussion of analyses an conclusion are clearly articulated; however the data does not fully support the conclusions.
Needs Improvement (0-8)
Significant components of the exercise were missing or completed improperly.
Only 1 analysis was conducted. Analyses performed are incomplete and/or the conclusions are not supported by the data.
Part III – Reflection (10 points)
Grade Reflection Excellent (9-10)
Reflection is well thought-out. Reflection includes required elements. Reflection is clearly written. Reflection is free of spelling and grammatical errors. All file submission instructions and due dates (draft and final) are followed.
Good (6-8)
Made a reasonable effort to articulate the required information. Reflection includes all required elements. Not all ideas are completely clear. All file submission instructions and due dates (draft and final) are followed.
Needs Improvement (0-5)
Little effort made to articulate the required information. Reflection does not include all required elements. Ideas are not clearly written. Spelling and grammatical errors exist. Draft submission did not fully complete one analysis. 3 point deduction. Missing any of the file submission due dates (draft or final) or failure to follow submission instructions: 5 point deduction.