Spring 2003 Kreeves MW1:30Pm

CIS200 FINAL

Spring 2003 KReeves MW1:30pm

Name ______Seat# ______

Other ______/50 Excel ______/150 Access ______/150

Lab day (circle one) Wednesday Thursday

Instructions:

Do not open your exam until it is time to begin. You have 1 hour and 48 minutes to complete this exam.
You cannot share your cheat sheet with anyone (or any other page of your exam!)
Put away all books, papers, and calculators. Turn off all beepers and cell phones.
Raise your hand if you have a questions. Do not leave your seat until you have finished the exam.
Read each question carefully and fill in the answer. Answers must be legible or they will be marked incorrect.
All sheets must be turned in when handing in the exam or it will not be graded (i.e. you will receive a grade of zero on the exam).
Please make sure you have all pages before you start this exam.

KREEVES CIS200 FINAL EXAM SPRING 2003 AFTERNOONPage 1

Matching - (2 pts each) Write the letter of the item which best matches each question in the space provided. Each item may be used only once.

A)Arguments
B)Absolute URL
C)Binary
D)Cascade Update
E)Data-Subtotals
F)Fields
G)Filtering
H)GUI
I)HTTP / J)Hyperlink
K)IP address
L)LAN
M)Microsoft Windows
N)Monitor
O)One–To–One Relationship
P)Packet / Q)Picture
R)Power Point
S)Protocols
T)Query
U)Record
V)Referential Data Integrity
W)Relative URL
X)Routers / Y)Sort
Z)Spider
AA)Tag
BB)UNIX
CC)WAN

1._____ An example of a packet based protocol.

2._____ The numeric form of a domain name.

3._____ Physical links that are the building blocks of data communication.

4._____ Exists when the fields used to join tables is the primary key in both tables.

5._____Building blocks for each record.

6._____ Program that is used by some search engines to populate their database.

7._____ A network architecture whose nodes are physically not very far apart.

8._____ Type of computer interface which frees users from memorizing complex syntax by allowing the user to point and click on commands.

9._____ One piece of a set of information that is being communicated across a network

10._____ A typical output device.

11._____ Uses a fully expressed path name to link a picture on a web page.

12._____ Invisible on a web page, but you can see their effects; tells the browser what to do.

13._____ A pointer which references documents and images that reside in another file.

14._____ A search routine that repeatedly halves the number of records it is considering

15._____ An example of application software.

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

16.______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

17.______In order for computers to effectively communicate they require

CIS200-SP03 1:30pm KReeves

a)protocols of communication and transmission links

b)the same operating systems

c)the same modem hardware

d)all of the above

CIS200-SP03 1:30pm KReeves

18.______What kind of data can NOT be used in a primary key field?

CIS200-SP03 1:30pm KReeves

a) Date

b) Numerical

c) Character

d) Null

CIS200-SP03 1:30pm KReeves

19.______Cell D3 contains the formula = $A2 + B$1. What is the Excel formula that will result if you copy the contents of cell D3 into cell F4?

CIS200-SP03 1:30pm KReeves

a)$C2 + D$1

b)$A2 + D$1

c)$A2 + B$1

d)None of the above

20.______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 _____ feature(s).

a)link

b)embed

c)copy

d)File – Save

True/False– (1 pts each)Circle the correct response.

21.T F The full directory path and extension for a Microsoft Access file named Thrift that can be found on your Z drive in the Mail directory is: Z:\Mail\Thrift

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

23.T F E-mail addresses are structured as follows:

24.T F The Excel chart that plots two sets of data at scaled intervals is called a line chart.

25.T F A megabyte is larger than a kilobyte.

26.T F =Or( 5 >8, Not ( 5 >4 ), TRUE).

27.T F Goal Seek is a tool that allows you to find the input values needed to calculate a desired output.

28.T F Netscape Navigator is email software.

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

30.T F The precision of a value is the number of decimal places displayed in a cell.

CIS200-SP03 1:30pm KReeves

EXCEL PROBLEM (150 points – 20,20,15,15,15,15,10,15,15,10)

Jessie, who is a sophomore in high school, is trying to convince her mom and dad to let her play a musical instrument, but she doesn’t know which instrument she wants to play. Jessie decides to gather information about 5 different types of instruments (piano, flute, French horn, guitar, and violin) to help her decide which one she would like to learn how to play. The input information Jessie gathered is highlighted in gray on the Excel worksheet. FYI: the order of the instruments in each section of column A will always remain the same.

There are 3 different groups that Jessie can possibly join to play her instrument: the Marching Band, the Orchestra, and the Concert Band. Jessie’s input shows which groups contain the given instrument by having a TRUE value. The FALSE value means that the instrument is not used to play in that group. Jessie wants to play an instrument that can be used in several different groups. Write an Excel formula in cell B11, which can be copied down to B15, that displays "will play" if the instrument is used in 3 groups, displays "might play" if the instrument is used in 2 groups, and otherwise displays "not play” if the instrument is played in only 0 or 1 groups.

______

Jessie can cut the cost of the instrument if she buys a used instrument, which is 70% (value not given on the worksheet) of the average cost of a new instrument (found in column E of the input area). Write an Excel formula in cell C11, which can be copied down to C15, using a reference function that determines the new used cost of each instrument.

______

Jessie will consider playing an instrument if it costs less than $500 (ANS#2) and if it plays in at least 2 groups (ANS#1). Write an Excel formula in cell D11, which can be copied down to D15, that returns TRUE if the instrument meets these expectations and FALSE if it does not for each instrument.

______

ANS#3 decides whether or not Jessie will consider playing each instrument based on certain criteria. Write an Excel formula in cell E11 to determine (true or false) if Jessie can play the least expensive instrument.

______

Taking semi-private lessons, which means that two people share the instructor for a lesson, will cost 25% (value not found on the worksheet) less than private lessons. Input column G tells whether or not semi-private lessons are available for each instrument. If semi-private lessons are available, determine the new cost for the lessons. If semi-private lessons are not available, the original cost of lessons is the “new” cost for lessons. Write an Excel formula in cell F11, which can be copied down to F15, to determine the new cost of lessons for each instrument rounded to the nearest dollar.

______

You will take lessons for 48 weeks per year, as shown in cell L5. Input column H shows how many lessons each instrument has per 4 week period. Write an Excel formula in cell B18, which can be copied down to B22, to determine how much you will spend on lessons per year.

______

You have calculated new lesson costs for a year and instrument costs. Write an Excel formula in cell C18, which can be copied down to C22, to determine how much money you will spend on each instrument plus lessons for a year.

______

An instrument can be classified as a woodwind instrument, a brass instrument, or a string instrument. Input columns I-K show the classification of each instrument. Write an Excel formula in cell D18, which can be copied down to D22, to determine which instruments you can play (true) or not (false) if you like to play brass and woodwind instruments, but not string instruments.

______

Your parents decide to take a loan to pay for your instrument and they can afford $25 a month. Write an Excel formula in cell E18, which can be copied down to E22, to determine how long in years it will take to pay for the instrument at 6% annual interest compounded quarterly?

______

Write an Excel formula in cell F18 to determine the average number of lessons per 4 week period (column H of the input data) for all the different types of instruments if the AVERAGE function is NOT available for you to use.

______

ACCESS PROBLEM (150 points – 12,6,6,6,10,15,20,15,10,15,35)

A lot of people in the world love cats. They love their cats so much that they like to show them off. Cat owners bring their cats to cat shows to be judged. Each cat is compared against all of the other cats of their own breed for a rank or place in their breed. Then the number one cat in each breed is compared with the number one cats in all of the other breeds to give a rank for their place in the entire show. Once a cat places in the top 10 in show 3 times, they are considered Champions.

An Access relational database is being used to help keep track of all this information. Included in the database are the following tables:

CATS– General information about each cat.
CATSHOWS – Lists the cats that are entered in a show.
SHOWINFO – General information about each show. The CostPerCat is the registration fee that is paid by the owner for each cat entered in the show. Also, this information lists all of the shows for this year, 2001.
OWNERS – A list of cat owners in the Midwest region of the United States.

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. Based on only the data shown on the attached database, has referential data integrity been violated for any of the relationships in this database? Explain.

3. If Referential Data Integrity has been violated for one relationship, does that mean that we can not Cascade Update/Delete for the entire database? Explain.

4. Explain why you can’t join three tables where the PK key field is on one table and the same field is joined to the foreign keys on the other two tables.

5. Query 5 – Write a query that lists the names of all the champion cats in alphabetical order.