Marie Curie Math & Science Center
 
Thomas H. Brennan
Working Group Number: 9

Subject: Computer Science
Level: Undergraduate
Course: CIS200 - Software Topics
Lesson: Music Store Analysis
Commencement Content Standards  

Standard 3: Mathematics
Students will understand mathematics and become mathematically confident by communicating and reasoning mathematically, by applying mathematics in real-world settings, and by solving problems through the integrated study of number systems, geometry, algebra, data analysis, probability and trigonometry.
 

Standard 5: Technology
Students will apply technological knowledge and skills to design, construct, use, and evaluate products and systems to satisfy human and environmental needs.

Benchmark Standards Content
Mathematics: Mathematical Reasoning
 

  • Students use mathematical reasoning to analyze mathematical situations, make conjectures, gather evidence, and construct an argument.

Technology: Engineering Design

  • Engineering design is an iterative process involving modeling and optimization used to develop technological solutions to problems with given constraints.


Performance

Mathematics: Mathematical Reasoning

  • Students will use dimensional analysis techniques.

Technology: Engineering Design

  • Students will, in a group setting, test their solution against design specifications, present and evaluate results, describe how the solution might have been modified for different or better results, and discuss tradeoffs that might have to be made.

Content Standards

Mathematics

  • Students will be able to understand the multi-dimensional interfaces of an electronic spreadsheet.
  • The Cartesian coordinate system will be represented by a grid of columns and rows, and a three dimensional system will be represented by a Cartesian grid in combination with different sheets within a spreadsheet file.
  • Students will understand the differences between data and formulas in a spreadsheet.
  • Students will differentiate among different types of numerical data.
  • Students will be able to construct appropriate arithmetic formulas.
  • Students will be able to select appropriate mathematical and statistical formulas.
  • Students will be able to answer correctly specific questions based on calculations.

Technology

  • Students will design the appropriate solution to a problem which contains detailed specifications. The solution will incorporate the use of an electronic spreadsheet and will utilize the multi-dimensional interfaces of the software.

Performance Measures

  Mathematics

  • Students will create a new spreadsheet file, name it, and save it on appropriate secondary storage. The spreadsheet design will be modeled according to a Cartesian coordinate systems and will contain appropriate text (labels), data, and formulas for a given set of specifications.
  • Students will assign appropriate names to different sheets in the spreadsheet file and will be modeled according to a three dimensional coordinate system.
  • Students will insert several different types of data, including dummy data, test data and real data.
  • Students will select mathematical and statistical formulas to calculate sums, averages, minimums, maximums, and tallies of different sets of data.
  • Students will construct arithmetic formulas to calculate percentages.

Technology

  • Students will submit a written report of approximately 250 words describing how the spreadsheet was used to solve a particular problem. This report will list the results of the spreadsheet calculations for the problem specifications and describe changes to the spreadsheet model for new or modified specifications.

Rubrics

  • The completed spreadsheet will be evaluated using an appropriate set of test data. All spreadsheets calculations will be checked for correct results. Grades will not be determined for each individual item but rather by a portfolio of assignments given in the course.

The course portfolio will be judged as follows:

Assignments will have due dates. Assignments will be evaluated in one of three ways: accepted without reservations, accepted with reservations, and not accepted. Students may resubmit assignments within a specific period of time to have them accepted without reservations.

Course letter grades will be assigned on a schedule of the percentage of completed assignments submitted by the due date and a percentage of assignments completed with accepted evaluations. Attendance at lecture classes and labs will also be included in student evaluations.


 Enabling Activities Class sessions are 75 minutes in length. Class #1: Classroom Lecture

  • Review and discuss spreadsheet terminology.
  • Explain the mathematical nature of the spreadsheet interface including grid and sheet concepts.
  • Make associations between spreadsheet interface design and the mathematical concepts of multi-dimensional coordinate systems.
  • Define the three different types of mathematical data: dummy, test, and real.
  • Explain how spreadsheets can incorporate dummy data from edit and fill menus.
  • Differentiate between the meanings of percentage: percentages as data values (7% tax) and percentages as the results of calculations (calculate the percent of 2 parts out of 7).

Class #2 and #3: Computer Lab

Design and implement a spreadsheet solution for the following set of specifications. The Music Company Spreadsheet.

A music company has three outlet stores. Each store sells music in three different formats: classical, pop, and country. Each of the formats can be purchased in three different media: record, compact disk and cassette. The price for a record is $8.95, a compact disk is $14.95 and the price for a cassette is $11.95.

1. Construct a two-dimension spreadsheet design which includes the following:

  • the number of items of each format in each media,
  • the total number of items of each format in each media,
  • the average number of items of each format in each media,
  • the minimum number of items of each format in each media,
  • the maximum number of items of each format in each media,
  • the percentage of items of each format in each media,
  • the dollar amount of sales of each format in each media,
  • the percentage of the dollar amount of sales of each format in each media

2. Construct a three-dimensional spreadsheet design which inserts part 1 in a different sheet for each store. Each store will have three sheets. One sheet will be for dummy data, one sheet for test data, and one sheet for real data.

3. Using a word processor write a report about the design and how it was implemented. In the report address the issues of the following modified specifications.

  • How would the design be modified if there were another type of media to be added for each type of music?
  • How would the design be modified if there were another type of music to be added for each type of media?
  • How would the design be modified if both items immediately above were implemented.
  • At the end of the report answer the following questions from the real data supplied by your instructor:


For each store:
  • What type of music has the most sales?
  • What media of music has the least sales?
  • What is the total number of classical items sold?
  • What is the average number of cassettes sold?
  • What percent of all sales is pop on cassette?
  • What type of music has the greatest dollar amount of sales?
  • What media of music has the least percentage dollar amount of sales?
  • If the price of a compact disk was lowered to $10.95 what effect would this have on the percentages of compact disks sold?
  • If the price of a cassette was raised to $12.95 what effect would this have on the percentage of the dollar amount of sales of classical music?
  • Based on the lowest percentage of dollar amount of sales what type of music in which type of media should be eliminated

4. How would the design be modified if there were different prices of records, compact disks, and cassettes for each type of music?



Class #4: Classroom Presentations

Students will present their respective reports.

Class #5: Computer Lab

Students will implement the four modifications in the specification as noted above.

Students will submit a disk with the spreadsheet files and a printout of the original and modified spreadsheets by a due date. Note: A sample of a portion of the spreadsheet is attached.

 

St. Thomas Aquinas College, 125 Route 340, Sparkill NY 10976-1050