CSE 200 - Spring 2006

Final Exam – Lecturer: K. Reeves

Other_____________ /50

Excel_____________ /150

Access____________ /150

Total Points ____________/350

Name ______KEY__________________________ Seat Number ________

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

Instructions:

• Do not open your exam until it is time to begin. You have 1 hour and 48 minutes to complete exam.
• Put away all books and calculators. You should have only a writing utensil (or two), your cheat sheet and your picture ID out on the desk.
• Read each question carefully and fill in the answer. Answers must be legible or they will be marked incorrect.
• Be sure to make it clear which work is your solution! Circle your answer if necessary. If more than one answer is available, the question will not be graded and full points will be deducted.
• All sheets must be turned in when handing in the exam or it will not be graded - including the cheat sheet!
• Be sure to use cell references whenever possible.
• Please make sure you have all pages before you start this exam.

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

________ An IPP is responsible for

a) collecting and switching packets of information along the internet

b) storing information - “virtual real estate”

c) setting internet protocols

d) all of the above

________ In order for computers to effectively communicate they require

a) protocols of communication

b) the same operating systems

c) the same hardware

d) all of the above

________ What kind of data can NOT be used in a primary key field?

a) date b) number c) text d) null

________The excel error value that will occur when the numeric value is too wide to display is:

a) #N/A b) #NUM! c) #REF! d) none of the above

________ This type of chart shows a trend with each data point being plotted at equal intervals.

a) Line b) XY c) Pie d) Bar

________ The formula =round(1.5,0)*2 results in what value?

a) 1 b) 2 c) 3 d) 4

________ The formula AND(C5,NOT(OR(C4,C6:C10))) is an example of what type of logical problem.

a) None b) Only c) All d) At least one

________ Which of the following holds a cell reference constant in Excel:

a) named range b) using $ c) neither a or b d) both a and b

________ To copy data from an Excel Spreadsheet into your PowerPoint presentation such that you can still use the Excel features but edit it independently from the original source data you would use the which of the following feature(s).

a) link

b) embed

c) copy

d) File – Save

________ The view used to show presentation slides (as done in class) is called the ______ view.

a) normal b) slide sorter c) slide show d) master

True/False (30 pts) – (15 problems; 2 pts each) Circle the correct response.

T F When breaking up an email into packets, all the packets travel along the same path to get to their destination.

T F Each time you run a query in Access, the resulting SQL view is saved.

T F All PC monitors are basically the same, so it doesn’t matter which one you choose to use/buy.

T F The CSE account you are using this quarter is part of a Client-Server architecture.

T F A kilobyte is larger than a megabyte.

T F In an Access query design view, you have to have a field from every table listed in order for the query to run correctly.

T F Tags are directions in HTML that help define the layout and look of a web page.

T F The What-If analysis is used when you want to change a one or more input values to view a calculated output.

T F Windows explorer is a web browser software application package.

T F Secondary memory is erased when the computer is turned off.

T F The precision of a cell is changed when the ROUND function is used.

T F In Access, an inner join and an outer join relative to the foreign key field table are the same if data integrity is enforced.

T F Http is used when a relative link on a web page is defined.

T F Each protocol layer always adds a tag to a packet that travels across the internet.

T F You can use a form in Access to either input a record or modify an existing record.

EXCEL WORKSHEET DESCRIPTIONS

FAMILIES – This worksheet has both input and calculated data. The input data is in gray and includes ranges A4:E9 and J2:L9. The number of phones needed, column C, is the same thing as the number of people in the family plan. The accessories, J2:L9, are designated as Boolean values where TRUE means that the accessory is free for the given accessory and family. The costs for the three different accessories are given in J2:L2 of this range. Remember that additional accessories can be added and the formulas should still work!

COST – This worksheet shows the correlation between the number of shared minutes for a plan and the monthly cost.

PHONE – This worksheet shows the available phone types as well as the description and cost for each phone type.

UNITS – This worksheet gives unit information. Although this data will actually not change in real life, you must use cell references for solution purposes (i.e. use of worksheet names and dollar signs).

1. (14 pts) Write an Excel formula in cell F4, which can be copied down to F9, to determine the monthly cost for the Reeves’ phone plan based on the number of shared minutes.

=HLOOKUP(B4,cost!B$1:F$2,2) optional $ on column; true default for 4^th argument

2. (8 pts) Write an Excel formula in cell G4, which can be copied down to G9, to determine the number of minutes that each person in the Reeves family can use (assuming everyone in the family gets to use an equal amount of minutes) rounded to the nearest minute.

=ROUND(B4/C4,0)

3. (14 pts) Write an Excel formula in cell H4, which can be copied down to H9, to determine the number of free phones given to the Reeves family given the following criteria:

· 2 free phones when number of phones needed is at least 5

· 0 free phones when number of phones is less than or equal to 1

· otherwise, 1 free phone

=IF(C4>=5,2,IF(C4>=2,1,0))

=IF(C4>=5,2,IF(C4<=1,0,1))

=IF(C4<=1,0,IF(C4>=5,2,1))

=IF(C4<=1,0,IF(C4<5,1,2))

=IF(and(C4>=2,C4<5),1,IF(C4>=5,2,0))

=IF(and(C4>=2,C4<5),1,IF(C4<=1,0,2))

4. (18 pts) Write an Excel formula in cell I4, which can be copied down to I9, to determine the cost of the remaining phones that need to be bought. That is, you just determined the number of free phones, so of the ones that remain, how much is the total cost of those phones?

=(C4-H4)*VLOOKUP(D4,phone!$A$2:$C$6,3,FALSE) optional $ on column

5. (6 pts) Write an Excel formula in cell F10, which can be copied across to I10, to determine the average value of the plan costs per month for the given family plans.

=AVERAGE(F4:F9)

6. (12 pts) Write an Excel formula to determine, true or false, if only the Reeves family has rollover. This problem is NOT updatable i.e. the answer will only work for the given data. The answer to this question for the given data is FALSE.

=AND(E4,NOT(OR(E5:E9)))

=AND(E4,NOT(E5),NOT(E6),NOT(E7),NOT(E8),NOT(E9))

7. (10 pts) Write an Excel formula to determine, true or false, if all of the families get all of the accessories for free. The answer to this question for the given data is FALSE.

=AND(J4:L9) no $ allowed

8. (18 pts) Write an Excel formula to determine what percent of accessories are given away for free. The answer to this question for the given data is 61.1% where the cell is already formatted to be a percent.

=COUNTIF(J4:L9,TRUE)/(COUNTIF(J4:L9,TRUE)+COUNTIF(J4:L9,FALSE))

=COUNTIF(M4:O9,0)/COUNT(M4:O9) ok even though not answered yet

no $ allowed

9. (12 pts) Write an Excel formula in cell M4, which can be copied down and across to O9, to determine the cost of accessory descA for the Reeves family. Notice that the costs are given in cells J2:L2 and that the cost of the accessory is zero if the family receives that accessory for free.

=IF(J4,0,J$2) no extra $ allowed

10. (10 pts) Write an Excel formula in cell P4, which can be copied down to P9, to determine the one time up-front costs for phones and accessories for the Reeves family.

Remember, should still work if add more accessories, so must use function!

=SUM(I4,M4:O4)

=I4 + SUM(M4:04)

11. (18 pts) Write an Excel in cell Q4, which can be copied down to Q9, to determine the number of years it will take to pay off the one time up-front costs for the Reeves family given that the interest rate for the loan is 4.5% annual rate compounded monthly with a weekly payment of $20.

=NPER(0.045/units!B$3,-20*units!B$1/units!B$3,P4)/units!B$3 optional $ on column

12. (10 pts) Write an Excel formula in cell R4, which can be copied down to R9, to determine the first year cost for the Reeves family, which includes the one time up front costs as well as the plan cost per month.

=F4*units!B$3+P4 optional $ on column

ACCESS PROBLEM (150 points)

COMPANIES – The companies listed are cell phone providers that people use to obtain cell phone service.

PACKAGES – Options (i.e. packages) that people can choose for their cell phone service bill. The packages include the number of free minutes for a given monthly fee, as well as, overage charges for minutes logged over the free minute amount.

FAMILIES – This is a list of families and their package preference, company provider and the number of phones that they will use with this service.

PARENTS – This list contains the parents of the families and all the phone calls, including number of minutes per phone call, they have made this month.

KIDS – This list contains the kids in the families and all the phone calls, including number of minutes per phone call, they have made this month.

1. (12 pts) Draw the relationship diagram for this database. A partial list of records for each table is given on the attached sheet. Indicate the primary and foreign keys (if any) for the corresponding tables. Draw lines between the fields in the boxes to indicate relationships.

-2 PER ERROR

2. (8 pts) If I wanted to change the familyid field for the Johnson family from 2 to 12, what other records in the database would be affected. Specify the fields in each record that would change, if any, along with the table the record is in and EXPLAIN why the changes occurred.

ANSWER: The 2^nd and 3^rd records in the PARENTS table only will have the familyID field updated from 2 to 12. This occurs because referential data integrity is enforced so updates will cascade.

Not required, but can also mentioned the fact that the 3^rd and 4^th records from the KIDS table will NOT be updated because referential data integrity is NOT enforced!

MUST mention the updates for 2^nd record and 3^rd record on PARENTS table due to data integrity being enforced

3. (10 pts) Are the following table joins valid? Yes or No. -1 per error

parents, families, packages Yes No

parents, families, companies Yes No

parents, families, kids Yes No

kids, families, packages Yes No

kids, families, companies Yes No

parents, families, packages, companies Yes No

parents, families, packages, kids Yes No

parents, families, companies, kids Yes No

kids, families, packages, companies Yes No

all five tables Yes No

Note: do not count off for show except on last query; order of fields only matters on sort and query6C

Query 1. (15 pts) List the names of the families who have more than 2 cell phones or whose cell phone service company is from Ohio.

TABLES	Families, companies
JOIN TYPE	inner
Relative TO

Field	Familyname	State	#phones
Table	Families	Companies	families
Total
Sort
Show
Criteria		“OH”
OR			>2
OR

Expressions, if needed…

Query 2. (15 pts) List the family id, the family name, the package type and the company number for all families. Sort the dynaset by company number first, from smallest to largest, then alphabetically by package type.

TABLES	Families
JOIN TYPE
Relative TO

Field	Familyid	Familyname	Company	package
Table	Families	Families	Families	Families
Total
Sort			Ascending	Ascending
Show
Criteria
OR
OR

Expressions, if needed…

Query 3. (20 pts) The phone companies think that families that have more than 150 free minutes will have at least 3 phones and families that have 150 free minutes or less will have less than 3 phones. List the names of the families (id, name) who would match the phone companies’ criteria.

TABLES	Packages, families
JOIN TYPE	inner
Relative TO

Field	Familyid	Familyname	Freeminutes	#phones
Table	Families	Families	Packages	families
Total
Sort
Show
Criteria			>150	>=3
OR			<=150	<=2
OR

Expressions, if needed…

Query 4. (20 pts) Summarize a list, for all the packages, the number of families that use each package.

TABLES	Families, packages
JOIN TYPE	outer
Relative TO	packages

Field	Type	Familyid
Table	Packages	families
Total	GroupBy	Count
Sort
Show
Criteria
OR
OR

Expressions, if needed…

Query 5. (15 pts) List the state and package type for all the packages used by the families given in this database.

*** PA does not show up on the dynaset. Explain.

There’s no company #5 on the families table, so no ability to make a join

worth 3 pts – can take off 2 if sort of right, but not quite!

TABLES	Packages, families, companies
JOIN TYPE	inner
Relative TO

Field	State	type
Table	Companies	packages
Total
Sort
Show
Criteria
OR
OR

Expressions, if needed…

Query 6. (35 pts) Generate the following dynaset which specifies for each family (i.e for ALL families), what package they are using, the total minutes of phone calls they used, as well as the minutes over they were compared to the free minutes for the package used. That is, for the overmin column, the value is negative if they didn’t use all the free minutes, and positive if they used more than the free minutes specified by the package.

QUERY 6A

TABLES	Families, kids
JOIN TYPE	Outer
Relative TO	Families

Field	Familyid	Package	#minutes
Table	families	Families	kids
Total	Groupby	Groupby	sum
Sort
Show
Criteria
OR
OR

Expressions, if needed…

Package field does not have to be here… can put on next query or even wait to put on last query instead
QUERY 6B

TABLES	Families, parents
JOIN TYPE	Outer
Relative TO	Families

Field	Familyid	#minutes
Table	Families	parents
Total	Groupby	Sum
Sort
Show
Criteria
OR
OR

Expressions, if needed…

QUERY 6C

TABLES	Query6A, Query6B, Packages (need for freeminutes in overmin equation!)
JOIN TYPE	inner
Relative TO

Field	Familyid	Package	Totalmin:	Overmin:
Table	Query6A or Query6B	Packages or Query above
Total
Sort
Show
Criteria
OR
OR

Expressions, if needed…

Fields must be in this order with these names since gave dynaset… if not, it is an error!

totalmin: nz([Query6A]![SumOf#minutes]) + nz([Query6B]![SumOf#minutes])

overmin: [totalmin] - [freeminutes]

must have query name for fields on totalmin equation

optional table/query names on overmin equation