CSE 200 -  Spring 2009

Final Exam – KReeves

 

 

Other_____________          /50
Excel_____________          /150
Access____________         /150
Total Points ____________/350

 

 

Name ___________________________________

 

 

Original Seat# ________                                        Final Exam Seat#  ________

 

 

Lab time (circle one)             Friday 1:30pm              Friday 3:30pm

 

 

Instructions:

 

True/False (20 pts) – (10 problems; 2 pts each) Circle the correct response

 

T          F          The numbers 5.3 and 2.4 are stored in cells B1 and B2, respectively. The formula =B1+B2 is in cell B3.  When all 3 cells are formatted to 0 decimal places, the result in B3 is 7.

 

T          F          The average function converts blank cells to zero when finding the average of a range of values.

 

T          F          A What-if analysis allows a change in outcome if there is a change in the input values.

 

T          F          Both architecture and protocols are necessary to make data communication possible.

 

T          F          An email has to stop along the way to be routed to the next valid location; each location has the ability to read the contents of the email.

 

T          F          The LIKE criteria in Access is only used when a wildcard character is used.

 

T          F          Microsoft Excel is an example of application software.

 

T          F          All of the links on my website (http://www.cse.ohio-state.edu/~reeves) are relative links.

 

T          F          A named range can represent a single cell reference, a row, a column or a block of cells.

 

T          F          A megabyte is larger than a gigabyte.

 

Multiple Choice (30 pts) - (10 problems; 3 pts each) Write the letter of the correct response in the space provided.

 

________ The excel error value that will occur when data being referenced is not available is:

A.     #N/A            

B.     #NUM!

C.     #REF!

D.     ##########

E.      none of the above

 

 

________ When using the ROUND function, given that the second argument is -1, this rounds the first argument to the:

A. tens place

B. tenths place

C. hundreds place

D. hundredths place

E. none of the above

 

 

________ This chart shows a functional relationship between two or more variables and data points are plotted at scaled intervals.

A. pie

B. column/bar

C. xy

D. line

E. None of the above

 

 

 

 

________ To convert monthly payment, which is a result of the payment function, to quarterly payment, you have to:

A.     *3  

B.     /3   

C.     *4   

D.     /4

 

________ This PowerPoint view shows thumbnails of all slides making it easy to rearrange the order.

A. Normal

B. Slide Sorter

C. Slide Show

D. Slide Master

E. None of the Above

 

________ A language used to write web pages is:

A. HTML

B. SMTP

C. BINARY

D. RAM

E. None of the Above

 

________ Object Embedding allows you to:

A. copy the data but in a table format, not in a spreadsheet format, with rows and columns

B. copy the Excel spreadsheet but does not have any Excel features with which to modify the data.

C. edit the object using the Excel features and does change the original source.

D. edit the object using the Excel features but does not change the original source.

E. none of the above

 

________  Tags:

A. are used on email packets

B. are used to create web pages

C. can be used to define images, graphics, picture and emails

D. all of the above

E. none of the above

 

________ You can use which of the following software applications to organize memory and manage your files:

A. Internet Explorer

B. Windows Explorer

C. Webmail

D. Netscape

E. none of the above

 

________  An ISP is responsible for

A.     collecting and switching packets of information along the internet

B.     storing information - “virtual real estate”

C.     setting internet protocols

D.     creating packets for transmission

E.      none  of the above

EXCEL PROBLEM (150 points) – CIRCLE YOUR FINAL ANSWER FOR EACH QUESTION :O)

 

With bathing suit season just around the corner, you want to think about setting new goals for eating better for the summer. Looking at your current habits will help you set those goals. You’ve convinced some of your friends to also go along with eating better, so you collect the following data and will determine additional calculations in the given worksheets:

 

SERVINGS – For each meal, your friends have given you data on how many servings they have eaten of each type of food group – dairy, meat, vegetable, fruit, and grain. There are multiple lines, one for each meal for the different meals each person ate during a certain time frame. However, due to time and space limitations, not all meals are listed for all of your friends as this is just a test workbook.

 

ANSWERS – This worksheet is where a majority of the calculations are done. The first two columns, however, are input data with the names of each of your friends that collected data and their gender.

 

CALORIES – The meaning of this worksheet will become apparent when you answer the questions given below. It is named calories for a reason ;o)

 

GROUPS – Instead of knowing the actual amount of calories for food, an average is given here for each food group. Also given are the minimum and maximum recommended servings for each food group based on the recommended daily allowances.

 

UNITS – Conversion factors for mathematical data are found here. As an explanation, joules is a measure of energy based on the work done, foot-pounds is a measure of force, and calories is a measure of potential energy based on the intake of food.

 

NOTE – VERY IMPORTANT: Whenever you see the 5 food groups listed, you can assume the order of the food groups will ALWAYS be the same. This is true for the food groups listed on the answers worksheet, the servings worksheet and the groups worksheet.

 

 

1.      (15 pts) Write an Excel formula in cell answers!C3, which can be copied down and across to cell answers!G9, to determine how many servings of milk products John consumes on this particular day.

 

=SUMIF(servings!$A$3:$A$22,$A3,servings!B$3:B$22)

no extra or missing $ signs allowed

 

 

2.      (12 pts) Write an Excel formula in cell answers!H3, which can be copied down and across to cell answers!L9, to determine (true or false) if John is getting the recommended number of servings per day of milk products (see the groups worksheet).

 

=AND(C3>=groups!B$2,C3<=groups!B$3)

no extra or missing $ signs allowed; an error each time

 

 

3.      (25 pts) Although the directions say that the 5 products will always be in the same order, for this problem only, let’s assume that this is not the case on the groups worksheet only and solve for the above problem.

 

=AND(C3>=HLOOKUP(H$2, groups!$B$1:$F$3, 2, FALSE),

      C3<= HLOOKUP(H$2, groups!$B$1:$F$3, 3, FALSE))

no extra or missing $ signs allowed; an error each time

groups!$B$1:$F$3 on the first lookup only can end with cell F2

 

 

4.      (10 pts) Write an Excel formula in cell answers!M3, which can be copied down and across to cell answers!Q9, to determine the guesstimated milk product calories that John consumed based on his total servings of milk products for the day.

 

=C3*groups!B$4

no extra or missing $ signs allowed

 

 

5.      (6 pts) Write an Excel formula in cell answers!R3, which can be copied down to R9, to determine the total number of calories consumed for all products and all servings for John.

 

=SUM(M3:Q3)

Optional $ on column

 

 

6.      (18 pts) Write an Excel formula in cell answers!S3, which can be copied down to S9, to determine the quality of nutrition comment for John given the following:

·         “too low” if the total calories are less than 840.

·         “try harder” if the total calories are greater than or equal to 3200.

·         otherwise, “good”

 

=IF(R3<840,"too low",IF(R3<3200,"good","try harder"))

=IF(R3<840,"too low",IF(R3>=3200,"try harder","good"))

=IF(R3>=3200,”try harder",IF(R3<840,"too low","good"))

=IF(R3>=3200,”try harder",IF(R3>=840,"good","too low"))

=IF(AND(R3>=840,R3<3200),”good”,IF(R3<840,”too low”,”try harder”))

=IF(AND(R3>=840,R3<3200),”good”,IF(R3>=3200,”try harder”,”too low”))

 

Optional $ on column

 

 

7.      (12 pts) If you want to use a reference function for the above problem, what would the values be for A1, A2 and A3 on the calories worksheet (give them below)? Now, write an Excel formula using a reference function to solve for the above problem using the calories worksheet data that you just filled in.

 

A1=0, A2=840, A3=3200       =VLOOKUP(R3,calories!A$1:B$3,2) optional $ on column

 

 

 

 

8.      (15 pts) Write an Excel formula in calories!C1, which can be copied down to calories!C3, to determine the percentage of people with the “too low” nutrition comment.

 

=COUNTIF(answers!S$3:S$9,B1)/COUNT(answers!C$3:C$9)

Optional $ on column

The COUNT function needs to have a range that is numeric

 

 

9.      (12 pts) Write an Excel formula (somewhere on the answers worksheet) to determine (true or false) if none of the females have to “try harder”. NOTE: the answer, not shown, is TRUE. Also, this problem is not updatable.

 

=AND(S4<>"try harder",S6<>"try harder",S9<>"try harder")

=NOT(OR(S4="try harder",S6="try harder",S9="try harder"))

=AND(NOT(S4="try harder"),NOT(S6="try harder"),NOT(S9="try harder"))

Okay if use calories!b3 instead of “try harder”

No $ allowed

 

 

10.  (12 pts) Write an Excel formula in cell answers!T3, which can be copied down to cell answers!T9, to convert total calories to force (i.e. foot-pounds).

 

=R3*units!B$2/units!B$1

Optional $ on column

 

 

11.  (12 pts) A company delivers meals to guarantee nutritional guidelines for $1200 per year. You have decided to try out the program for only 6 months (instead of the whole year) and have paid for the cost of meals with your credit card which has a 21% annual interest rate compounded monthly (and a current balance of zero). Write an Excel formula (not shown) to determine the monthly payment that your credit card company will be charging you if you want to pay off the cost of 6 months of food in 3 months.

 

=PMT(21%/12,3,1200/2)

No $ allowed

1200/2 can also just be 600

 

ACCESS PROBLEM (150 points)

 

AGGREGATE FUNCTIONS è GroupBy, Sum, Min, Max, Avg, Count, Expression, Where

 

NAME – This table has the list of all the people trying to eat better. The fourth field, “female”, is a Boolean yes/no field where “Yes” means that the person is a female and “No” means that the person is male. The field “maxcals” designates the total calories that the person can intake and still be eating healthy with no weight gain.

 

WORKOUT – This table defines the types of workouts that people can participate in. For each different type of exercise, a different amount of calories can be burned.

 

EXERCISE – Each person should exercise to stay healthy. Each record shows the type of exercise the person is doing as well as the number of intervals i.e. “#intervals”. An interval is defined to be the time it takes to burn the number of calories designated on the workout table for that exercise. For example, since this is different for each person, a specified time is not set for the amount of time a person has to run to burn 200 calories; the person works out at that exercise until the interval is such that 200 calories have been burned. After 2 intervals, 400 calories have been burned running, etc.

 

FOOD – Each record in this table designates a person, a food group and the number of servings they ate of that food group. Do NOT assume that every person will be on this list at any given time - the person may have forgotten to enter their data, they may have quit, or they may be fasting.

 

CALORIES – This table defines the average calories per serving (“avgcps”) for each different food group.

 

REMINDER: Only a portion of the data is present on the tables.

 

1.      (15 pts) Draw the relationship diagram for the given database. Be sure to list all the primary and foreign keys for each table and draw a line between tables to designate the relationship between the tables.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


2. (5 pts) Name 2 of the 3 factors necessary to allow a relationship to be made between two fields on two different tables.

  1. pk to fk 
  2. same type
  3. same meaning
  4. optional – can have same or different field names

 

3.  (5 pts) Name all the valid 4 table combinations that can be used when writing a single design view query. 

There are no valid 4-table combinations that can be put on a single query.     

***ASSUME DATA INTEGRITY ERRORS, IF ANY, HAVE BEEN CORRECTED AT THIS POINT ***

 

 

4.  (25 pts – 20 for design view, 5 for dynaset) Write a query in the design view given below to summarize by nutritional number the total number of servings for each female. Be sure to list the nutritional number, first name and the total number of servings per female person.

 

Query Name: Q4

TABLE(S)

Food, name

JOIN TYPE

inner

Relative TO

 

Field

Nutrinum

First

Numservings

female

 

Table

food

Name

Food

name

 

Total

GroupBy

GroupBy

Sum

Where

 

Sort

 

 

 

 

 

Show

Criteria

 

 

 

Yes

 

OR

 

 

 

 

 

OR

 

 

 

 

 

  Expressions, if needed…

Can also use nutrition# from the name table for the first field; must match below dynaset

 

Show the  resulting dynaset values for  Q4 (above). Don’t forget to include the field names.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

nutrinum

first

SumOfnumservings

2

Jane

12

6

Mary

15

7

Jill

8

5.      (20 pts) Write a query in the design view given below to summarize the following data by listing the nutrition number, the first name and the exercise description (not just the letter) for all the different exercises each person does. Be sure to list only the people who exercise. The dynaset for the given data looks like:

 

Q5

nutrition#

first

exercise

1

John

run

1

John

weights

2

Jane

walk

2

Jane

weights

3

Jack

bike

3

Jack

walk

3

Jack

weights

4

Peter

run

6

Mary

bike

7

Jill

bike

7

Jill

run

 

 

Query Name: Q5

TABLE(S)

Name, exercise, workout

JOIN TYPE

inner

Relative TO

 

Field

Nutrition#

First

exercise

 

 

Table

Name

Name

workout

 

 

Total

GroupBy

GroupBy

GroupBy

 

 

Sort

 

 

 

 

 

Show

Criteria

 

 

 

 

 

OR

 

 

 

 

 

OR

 

 

 

 

 

  Expressions, if needed…

 

Okay if put something on the sort line like ascending for nutrition# and/or exercise fields

 

6.  (25 pts) List the first name and the maximum calories (only and in that order) for every male who can consume more than 2000 calories and every female who must consume less than 1500 calories. Be sure to list the data in gender order (female then male) then alphabetically by first name.

 

Query Name: Q6

TABLE(S)

Name

JOIN TYPE

 

Relative TO

 

Field

First

Maxcals

Female

first

 

Table

Name

Name

Name

Name

 

Total

 

 

 

 

 

Sort

 

 

Descending

Ascending

 

Show

Criteria

 

<1500

yes

 

 

OR

 

>2000

no

 

 

OR

 

 

 

 

 

  Expressions, if needed…

Remember female field must be to the left of first field. Can put female field first and not list the 4th column (the second listing of first); the descending for first would then go under the original first field.

 

7.  (20 pts) This question gives you two queries which you will need for the last 3-query problem. The first query, Q7A, is already done for you ;o) It gives the total calories burned for each record in the exercise table based on the number of intervals completed.  The second query, Q7B, you must write and should list the nutrition number as well as the total calories for each record on the food table based on the number of servings consumed.

 

Query Name: Q7A (GIVEN)

TABLE(S)

Exercise, Workout

JOIN TYPE

inner

Relative TO

 

Field

Nut#

Totburn:[#intervals]*[calsburned]

 

Table

Exercise

 

 

Total

 

 

 

Sort

 

 

 

Show

Criteria

 

 

 

OR

 

 

 

OR

 

 

 

  Expressions, if needed…

Query Name: Q7B

TABLE(S)

Food, calories

JOIN TYPE

inner

Relative TO

 

Field

nutrinum

Totservcals:

 

 

 

Table

food

 

 

 

 

Total

 

 

 

 

 

Sort

 

 

 

 

 

Show

Criteria

 

 

 

 

 

OR

 

 

 

 

 

OR

 

 

 

 

 

  Expressions, if needed…

Totservcals: [numservings]*[avgcps]

Optional table names on above calculation

 

8.  (35 pts – 10, 10, 15) For all the people on the NAME table, create a list summarizing by the nutrition number and including the first name as well as the number of calories over or under the maximum calorie defined for them based on their exercise and food intake.  For example, for nutrition#1, the total calories burned by exercise per record (Q7A) then summed up per person is 560; the total calories earned by the food servings per record (Q7B) then summed  up per person is 2130; the maximum calories are 2000. The equation used is either: 2000-(2130-560) or its equivalent 2000 + 560 – 2130. The resulting value is positive if the person has not yet reached the maximum number of calories as specified in the name table and can intake more servings; if the number is negative if they are over the maximum calorie limit.

 

Query Name: Q8A

TABLE(S)

Name, q7a

JOIN TYPE

outer

Relative TO

name

Field

Nutrition#

totburn

 

 

 

Table

name

Q7a

 

 

 

Total

Group by

sum

 

 

 

Sort

 

 

 

 

 

Show

Criteria

 

 

 

 

 

OR

 

 

 

 

 

OR

 

 

 

 

 

  Expressions, if needed…

 

 

Query Name: Q8B

TABLE(S)

Name, q7b

JOIN TYPE

outer

Relative TO

name

Field

Nutrition#

Totservcals

 

 

 

Table

Name

Q7b

 

 

 

Total

Group by

Sum

 

 

 

Sort

 

 

 

 

 

Show

Criteria

 

 

 

 

 

OR

 

 

 

 

 

OR

 

 

 

 

 

  Expressions, if needed…

The totservcals field name should match whatever name in q7b

 

 

 

 

Query Name: Q8C

TABLE(S)

q8a, q8b, name (if first name not included in q8a or q8b)

JOIN TYPE

inner

Relative TO

 

Field

Nutrition#

first

Calsundover:

 

Table

Any table listed

Name or might be included in q8a or q8b

 

 

Total

 

 

 

 

Sort

 

 

 

 

Show

Criteria

 

 

 

 

OR

 

 

 

 

OR

 

 

 

 

  Expressions, if needed…

calsundover: [maxcals]+nz([sumoftotburn])-nz([sumoftotservcals])