CIS300 FINAL EXAM

absolute

$B$1 is an example of a(n) ____ reference.

query

(Access) A ________ is a question you ask about data stored in a database.

related tables.

(Access) A relational database is a collection of:

The birthday should be a stored field and the age should be a calculated field.

(Access) Ben is working in a database that stores student information. He wants to determine each student's age based on the student's birthday. Which of the following statements accurately describe how the fields should be designed?

FIELD

(Access) Closely examine the accompanying table image. The column labeled Last Name is an example of which of the following?

RECORD

(Access) Closely examine the accompanying table image. The last row of data shown is known as which of the following?

CLIENT ID

(Access) Closely examine the accompanying table image. Which of the following fields would be the best to use for the primary key?

ListingAge: IIf(Date-[HomeListings]![ListingDate]<30,"New Listing","Old Listing")

(Access) Craig is working in a database that stores homes for sale for a Realtor. The database contains a table called HomeListings which contains a field called ListingDate that holds the date that a home was listed. Craig is designing a query that will identify houses on the market for less than 30 days as new listings, and houses on the market 30 days or more as old listings. This information will be included in a query column called ListingAge. Which of the following expressions will achieve these results?

encrypted

(Computer Security) Organizations should secure their sensitive data by storing it in _______ form?

Disable related user ID accounts and change administrator passwords

(Computer Security) When an employee is terminated, system administrators should receive notification in advance so that they can do which of the following?

All of the answers provided are correct

(Computer Security) Which of the following are common examples of clever schemes created by con artists to scam millions of people around the world into parting with their money or sensitive personal information?

=IF(A5 < 650, "Rejected", IF(A5 > 750, 5.5%, 6.5%))

(Excel) A customer applies for a loan and the bank reviewing the application uses the following rules: If the applicant's FICO score (entered in cell A5) is less than 650, then the application is rejected. However, if the applicant's FICO score is greater than 750, then the applicant is approved and the interest rate is set at 5.5%. Otherwise, the interest rate is set at 6.5%. Which of the following formulas will give the correct answer?

=(4*125-4*125*20%)*1.06

(Excel) A local tire store is offering 20% off on a set of four radial tires that cost $125 each (including balancing, installation, and disposal fee). Provide a formula in the answer space below to calculate the total amount that the customer will have to pay for a set of these new tires, including a sales tax of 6% on the sub-total.

=IF(A1>=50,A1*B1*98%,A1*B1)

(Excel) A tollway truck plaza gives a discount on diesel fuel to truckers based on the total quantity they purchase. Which of the formulas below calculates the correct total price to charge a customer for the diesel pumped based on the following conditions: •no discount (zero dollars off the total due) if the trucker purchases less than 50 gallons of diesel •a 2% discount on all diesel purchased if the trucker buys at least 50 gallons of fuel Assume that cell A1 shows the total quantity (in gallons) of diesel purchased and cell B1 is the price per gallon. The correct formula will calculate the total amount that the trucker should pay, after discount (if any) has been applied.

=$G$4+MAX($D7:D12)*C$6

(Excel) Assume that cell C8 in a worksheet contains the following formula: =$G$4+MAX($D10:F15)*E$6 What formula will result from copying it into cell A5?

A1

(Excel) Carefully review the accompanying worksheet image. Based on the information provided, what appears to be the active cell in this worksheet?

D4

(Excel) Closely review the accompanying worksheet image. What is the result of evaluating the following formula? =IF(AVERAGE(E5:E7)<=MIN(B5:C7),D7,"D4")

Part A: 55 Part B: $92,763 Part C: 7

(Excel) Download the attached template file which contains U.S. Presidential data to the PC Desktop and then open it in Excel. Use one or more of the functions, calculations, and/or features that you have learned thus far in CIS300 to answer each of the three questions below. (1 point each) Note: Each of your answers must be rounded to the nearest whole number. A) What is the average age for all U.S. presidents whose Religious Affiliation is designated as Quaker? [PartA] B) What is the average salary of all U.S. presidents who were not affiliated with either the Federalist or Whig parties? [PartB] C) How many U.S. Presidents between the ages of 45 and 59 were affiliated with the Episcopalian religion? [PartC]

=FV(6%,40,0,-2000,0)

(Excel) Enter a formula to determine how much money you will have in your IRA investment account 40 years from now if you deposit $2,000 today and gains interest at a rate of 6% annually.

=PV(8%,45,-70000)

(Excel) Enter a formula to determine the amount of money you would need to accumulate to be able to retire for the rest of your life on $70,000 per year if your money earns 8% per year and you expect to live for another 45 years.

=RATE(24,-500,6000,-6000,0)

(Excel) Enter a formula to determine the monthly interest rate you are paying to Joe the Shark if you borrow $6,000 today and pay back $500 each month for 23 months and then pay back $6,000 two years from now.

=PMT(6.5%/12,20*12,-190000,0,0)

(Excel) Enter a formula to determine the monthly payment on a 20-year fixed rate residential mortgage with an initial balance of $190,000 at 6.5% per year.

=NPER(6%/12,75000,-4500000,0,0)

(Excel) Enter a formula to determine the number of months a new company could last if it raised $4,500,000 and had a burn rate (spending rate) of $75,000 per month, assuming it kept its cash in an account earning 6% per year.

3

(Excel) How many commas are contained within a single function that has four arguments within the set of accompanying parenthesis?

22

(Excel) How many total cells are included in the following formula, including all of the arrays (or cell ranges) that are being referenced: =MIN(COUNT(A7:F8),MAX(B4:C6,O1),SUM(B9:D9))

50

(Excel) How many unique cells are included in the array (or range) that is being referenced in the following formula? =SUM(B19:F24,D4,A3:B11,K9)

#NAME? :Unrecognized text in a formula #REF! : Invalid cell reference #VALUE! : Wrong argument type or operand #NUM! : Invalid numeric values in a formula or function

(Excel) Match the following types of error messages that may occur when working with formulas in a worksheet with their respective descriptions.

1. Parentheses 2. Exponentiation 3. Multiplication and/or Division 4. Addition and/or Subtraction

(Excel) Match the following types of operations with their order of precedence (1, 2, 3, or 4) when performing calculations within an Excel worksheet.

Part A: =ROUND(42.7357,2) Part B: =ROUND(65.18%,3)

(Excel) Provide a formula in each of the answer spaces below that will satisfy the following three conditions, respectively. Do not use any cell references. (1 point each) A) Round a water bill calculation that results in the value 42.7357 to the nearest penny. [PartA] B) Round the value 65.18% to the number 0.652 precisely. [PartB]

PART A: WEDNESDAY PART B: 6/14/2002 PART C: 4422 PART D: DATEDIF

(Excel) Read each of the following questions and provide a response for the first two using a properly formatted day of the week or date, respectively, and then a whole number for the latter two. (1 point each) A) What day of the week did the date represented serial number 12345 in a given worksheet? [PartA] B) What is the date that is 999 days after September 19th of the year 1999? [PartB] C) What is the number of days that Franklin D. Roosevelt served as President of the United States? Hint: FDR was inaugurated on March 4, 1933 and then died in office on April 12, 1945.? [PartC] D) Which undocumented function should be used to return an integer value to determine the age of any person given their birth date? [PartD]

=VLOOKUP(C4,$F$4:$G$8,2)

(Excel) Review the accompanying worksheet image and then answer the question below. The first row and column in the table refer to Excel column and row labels, respectively. The worksheet contains a list of students and each student's final score (out of 100 possible) in the course. It also contains a Grade Lookup Table which shows how the professor will assign letter grades to students in her course. For example, students earning a score less than 40 will receive an F; students earning a score higher than or equal to 40 but less than 70 will receive a D. What formula should be entered in cell D4 to assign a letter grade to Adams? You must use a table lookup function in the formula. (Note: Your formula will need to work properly for all other students when copied down through cell D16.)

18

(Excel) Review the accompanying worksheet image and then answer the question below. The first row and column in the table refer to Excel column and row labels, respectively. Evaluate the function below to determine what value will result. =HLOOKUP(SMALL(C9:D10,3), $B$3:$G$27, 3, FALSE)

44

(Excel) Review the accompanying worksheet image and then answer the question below. The first row and column in the table refer to Excel column and row labels, respectively. Evaluate the function below to determine what value will result. =VLOOKUP(42, $B$3:$G$20, 3, FALSE)

=HLOOKUP(C6,C10:F11,2,FALSE)

(Excel) Review the accompanying worksheet image and then answer the question below. The first row and column in the table refer to Excel column and row labels, respectively. The Price Table in the accompanying worksheet image lists the unit price of four (4) items sold by a company. What formula should be entered in cell C7 to determine the unit price of any item entered in cell C6? (Note: You must use a table lookup function in your formula.)

Part A: =COUNTIF($C4:$C14,">55000") Part B: =SUMIF($C$4:$C$14,">"&AVERAGE($C$4:$C$14)) Part C: =AVERAGEIF(C4:C14,"<45000")

(Excel) Review the accompanying worksheet image and then answer the questions below. The first row and column in the worksheet refer to worksheet column and row labels, respectively. The worksheet shows the annual salaries for several employees in a company. Provide responses for the following three questions. (2 points each) A) Using mixed cell references only, what is the formula to determine the total number of employees who earn more than $55,000 in annual salary? [PartA] B) Using absolute cell references only, what is the formula to determine the sum of the salaries of employees who earn above the average annual salary for this group of employees? [PartB] C) Using relative cell references only, what is the formula to determine the average salary of all employees who earn less than $45,000? [PartC]

Part A: =MEDIAN($A3:$A15) Part B: =ROUND($A$16,-3)

(Excel) Review the spreadsheet image which shows names for property owners and the respective value of their residence. (1 point each) A) Using mixed cell references only, what formula containing one unique function should be used in cell A16 to determine the middle value of prices for all homes listed? [PartA] B) Using absolute cell references only, what formula containing one unique function should be used in cell A17 to round the result in cell A16 to the nearest thousand dollars? [PartB]

A. =FV(8%,20,15000,-250000,0) B. $478,809.82

(Excel) You recently won a $250,000 prize in one of the Kentucky Lottery Scratch-Off games. You decide to invest the money in a safe investment that earns 8% per year for the next 20 years while you continue to work. At the end of each year for the next 20 years, you withdraw $15,000 from the investment to spend on yourself and your family. Using the information answer the following: (3 points each) A. Write a formula that will calculate the amount of money you will have at the end of 20 years given the conditions above. [PartA] B. What is the amount of money you calculated for Part A? [PartB]

TRUE

(Extra Credit) Following its impressive win against two past Jeopardy! champions, the IBM Watson computing platform was applied to solving some complex challenges in both the healthcare and financial services industries.

TRUE

(IIS, Ch 04) If you have copied a proprietary software package (computer application program) from a friend or your business without paying for it, then you are guilty of software piracy.

FALSE

(IIS, Ch 04) Low level employees pose the greatest threat to information security within an organization.

TRUE

(IIS, Ch 04) Risk management identifies, controls, and minimizes the impact of threats to the organization's information security.

FALSE

(IIS, Ch 04) The emergence of the World Wide Web has significantly decreased the threats to information security.

Computer viruses are introduced into the electrical company's systems resulting in a shutdown

(IIS, Ch 04) Which of the following is a prime example of a SCADA attack?

TLS

(IIS, Ch 04) Which of the following is an encryption standard used for secure transactions such as credit card processing and online banking?

Risk analysis

(IIS, Ch 04) Which of the following is not a common risk mitigation strategy?

SPAMWARE

(IIS, Ch 04) Which of the following types of alien software uses your personal computer to send email messages that appear to have come from you using the contact list in your address book?

spamware

(IIS, Ch 04) Which of the following types of alien software uses your personal computer to send email messages that appear to have come from you using the contact list in your address book?

denial of service

(IIS, Ch 04) Which of the following types of remote software attacks does not require any action by an end user?

TRUE

(IIS, Ch 06) Affinity portals support communities such as specific hobby groups or a given political party.

TRUE

(IIS, Ch 06) Analog signals have two parameters, namely: amplitude and frequency

PAN

(IIS, Ch 06) Bluetooth is considered to be within which of the following network classifications?

FALSE

(IIS, Ch 06) Digital signals have two parameters, namely: amplitude and frequency.

TRUE

(IIS, Ch 06) Extranets offer limited accessibility to the intranets of participating companies.

TRUE

(IIS, Ch 06) File sharing is a type of peer-to-peer (P2P) network processing.

All of the answers provided are correct

(IIS, Ch 06) Many organizations have implemented corporate portals for which of the following reasons?

Telecommunications and cable companies are in favor of net neutrality.

(IIS, Ch 06) Referring to the chapter's Opening Case - The Network Neutrality Wars, which of the following is not a challenge for achieving net neutrality?

FiOS

(IIS, Ch 06) Which of the following is a bundled Internet access, telephone, and television service offered by Verizon that operates over a fiber-optic communications network?

Bridge

(IIS, Ch 06) Which of the following is not a component of a local area network?

Customer touching

(IIS, Ch 11) A check-in kiosk at an airport is an example of what type of CRM application? (IIS, Ch 11) A check-in kiosk at an airport is an example of what type of CRM application?

TCP/IP

(IIS, Ch 6) What is the most important and pervasive set of protocols that enable computers to communicate with each other over the Internet?

Packet

(IIS, Ch 6) Which of the following is defined as a group of bits transmitted together over a network?

optical fiber (OC)

(IIS, Ch 6) Which of the following types of networking media is the least susceptible to electromagnetic interference (EMI) and provides some of the fastest data transmission speeds possible?

=D3*B$13

*Judson Ford Realty* (Excel) Closely examine the accompanying spreadsheet image which shows property sales for the month of October. What formula should be entered into cell G3 and copied down the entire column to properly calculate the amount of commission earned for any of the residential properties that are listed?

primary key

A field, or a combination of fields, that has a unique value is a:

TRUE

A foreign key in a table must have a corresponding primary key found in another table of the same database.

A. =PV(0.8%*12*10,10,-2000,1000000,0) B. 885.65

A recent College of Business graduate landed an amazing job right out of college and allows her to save $2,000 per month because she is a bit more frugal than her boyfriend who tends to foolishly spend his money. She does not have any savings right now because she has had to pay for college. However, her goal is to have saved one million dollars ($1,000,000) by the end of ten (10) years at a rate of 0.8% on her money each month. A. Write a formula that will calculate how much money she should use as an opening deposit for a savings account now to help her reach her goal? B. What is the value of your formula's result in Part A?

(IIS, Ch 06) A protocol is:

A standard set of rules and procedures for the control of communications in a network.

=VLOOKUP($C12, $B$4:$D$8, 3)

At the Louisville Resort Camp, each child is assigned to a specific group based on age according to the Group Table shown in the worksheet above. For example, children ages 2 or 3 years old are assigned to the Jaguar Group; children who are at least 6 years but no older than 9 years old are assigned to the Tigers group. However, teenagers (i.e., children 13 years and older) are not assigned to a group; instead, they are assigned to "None". Which of the following formulas, when entered into cell D12, will determine the correct group to assign each child?

=AND(C5:E5)

Chloe is creating an Excel spreadsheet using a number of formulas to help make some business decisions regarding her company's customers. Please refer to the figure above as you answer the question below. Chloe also intends to send out a letter congratulating those valued customers who have the value TRUE in all three columns of C, D, and E. She first wants to write a simple formula to generate a TRUE if a customer matches this criteria, demonstrating their strong creditworthiness. IF a customer doesn't meet her high credit expectations, then a FALSE should be displayed. Which of the following Boolean logical constructs should Chloe use as a formula in cell F5, then copied down that column to correctly determine whether a letter should be sent (or not) to all of the customers on her list?

Part A: =VLOOKUP(C10,$B$4:$E$6,IF(D10="Manager",2,IF(D10="Staff",3,4)),FALSE) Part B: =HLOOKUP(D10,$C$3:$E$6,IF(C10="Chicago",2,IF(C10="Louisville",3,4)),FALSE)

DeepSix Legal Services has offices in Chicago, Louisville, and Nashville. In the worksheet above the Employee List lists the employees, their office locations, and their job titles. In addition, the Bonus Lookup Table shows that the annual bonus awarded to an employee is based on the location and the job title of the employee. For example, each Manager in Chicago gets a bonus of $10,000, each Staff employee in Louisville gets a bonus of $5,000, and each Clerical employee in Nashville gets a bonus of $2,000. (A) What VLOOKUP formula may be entered in cell E10 to determine the bonus for Allisa, the first employee listed in the Employee List? (Note: Your formula will need to work properly for all other employees when copied down though cell E24.) [PartA] (B) What HLOOKUP formula may be entered in cell E10 to determine the bonus for Allisa, the first employee listed in the Employee List? (Note: Your formula will need to work properly for all other employees when copied down though cell E24.) [PartB]

TLS

IIS, Ch 04) Which of the following is an encryption standard used for secure transactions such as credit card processing and online banking?

=TODAY()-B2

In the worksheet below, cell B2 contains the date January 1, 2008. What is the formula to determine the number of days that have passed since January 1, 2008?

=YEARFRAC(DATE(1989,1,5),TODAY())

John was born on January 5, 1989. What is the formula to determine how many years old John is?

B: =IF(C5=NOT(TRUE), "Call", "Don't Call") =IF(C5, "Don't Call", "Call") B only

Nathan is in charge of Collections, and is putting together a list of customers with poor credit to call. Please refer to the figure above as you answer the question below. Nathan wants to fill in the words "Call" and "Don't Call" on the worksheet in the adjacent column D based on the value shown in column C. He will list "Call" if the value is FALSE because the customer has a poor credit rating. Otherwise, the display will show "Don't Call" if the value is TRUE. Which of the following pairs of formulas could Nathan write in cell D5 (and then copy down the column) to properly show this?

TRUE

The TODAY function returns the current date's serial number (based on the computer's internal clock).

YEARFRAC

The ____ function calculates the number of years between two dates.

YEARFRAC

The ____________________ function calculates the number of years between the two dates

TODAY

The ____________________ function returns the current date's serial number (based on the computer's internal clock)

TRUE

The result of the formula =B3>=C3 where cell B3 contains the date 1/1/2010 and cell C3 contains the date 12/31/2009 is ____.

TRUE

The syntax for the YEARFRAC function is as follows: =YEARFRAC(start_date,end_date,basis).

Wednesday

What day of the week did the date represented serial number 12345 in a given worksheet?

6/14/2002

What is the date that is 999 days after September 19th of the year 1999?

=DATE(2020,7,4) - TODAY()

What is the formula to determine the number of days until Independence Day (July 4th) of 2020?

4422

What is the number of days that Franklin D. Roosevelt served as President of the United States? Hint: FDR was inaugurated on March 4, 1933 and then died in office on April 12, 1945.?

=DATEDIF

Which undocumented function should be used to return an integer value to determine the age of any person given their birth date?

(Access) A form can be used to:

enter, edit, and view records in a database.

A primary key:

must contain a unique value for each record within the table.