ACCESS reminders
·
Aggregate functions: Group By, Min, Max, Avg, Count, Sum, Where,
Expression.
·
It was not possible to list
all the data on the given tables; so assume that there are no data integrity
violations in the database.
True/False (30 pts with 15
problems at 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 Every website address has an IP
address.
T F Each
time you run a query in Access, the resulting dynaset is saved.
T F The LARGE function and the MAX function
in Excel always work the same.
T F The
CSE account you are using this quarter is part of a Client-Server architecture.
T F A megabyte is larger than a gigabyte.
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 Goal
Seek is a tool that allows you to change multiple input values to view a
calculated output.
T F Microsoft
Access is considered system software.
T F RAM
is erased when the computer is turned off.
T F The actual precision of a cell is changed
when the ROUND function is used.
T F In Access, Cascade Update can only be
applied if Referential Data Integrity is violated.
T F In Access, an inner join and an outer
join relative to the foreign key field table are the same if Referential Data
Integrity is not violated.
T F Http
is used as part of the link address when a relative link on a web page is defined.
Multiple
Choice (20 pts with10 problems at 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) the same protocols
b) the same operating systems
c) the same hardware
d) all of the above
________
Absolute addressing can be used in which of the following:
a) cell references b) named ranges c)
formulas d) all of the
above
________
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
a) $C2 + D$1 b) $A2 + D$1 c) $A2 + B$1 d) None of the above
________
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
________
You have created a query listing account balances by account number. In this query you have created the calculated
field as follows: Nz ([balance] +
[deposits] – [withdrawals]). If
for a specific account number the balance was $10, deposits were $100 and the
withdrawals were null, what value would result for that account number in this
calculated field.
a) 0
b) $110 c) Null d) Cannot be determined
________The excel error value that will occur
when the numeric value is too wide to display is:
a) #N/A b)
#NUM! c) ######## d)
none of the above
________ Search engines return different
results because:
a) the keyword syntax
is different
b) the database
information is indexed differently
c) both a and b
d) neither a or b
________
Which character is a valid wildcard in Access?
a) *
b) @ c) $ d)
%
________
Which of the following is NOT one of Excel’s data manipulation capabilities:
a) Data Sorting b) Data Querying c) Data Filtering d) Pivot Tables
EXCEL: Problem description (150 points)
Space… the final Excel frontier… at least for you this quarter ;o) Based on the input data given on the following worksheets and described below, fill out the remaining information as designated in the given problems. NOTE: On the worksheet are Q# hints to help you follow where the problems are being used to fill in the data.
SPACE This worksheet contains information pertaining to individual planets in our solar system. The following columns are considered input data: A thru E, G, I thru K, O and P. The goal here was to put a question directly after the input to which it pertains.
UNITS Here you will find conversions to aid in analyzing your data. Notice that most of this information are set conversions, however, due the learning nature of this exam, please be sure to use the cell references when using this data. Also, there is a named range specified for this worksheet called lightyear which is defined as units!$a$3. Be sure to use this named range when accessing this cell.
PROBLEMS
1. (8 pts) Write an Excel formula in cell space!G13 to determine how many planets have at least one moon.
=COUNT(D4:D11)
=COUNTIF(D4:D11,">0")
No $ allowed
2. (12 pts) Write an Excel formula in cell space!G14 to determine if only non-dwarf planets have moons. REMINDER: this type of problem is considered not updatable.
=AND(SUM(D5:D8,D10:D11)>0,D4+D9=0)
=AND(OR(D5>0,D6>0,D7>0,D8>0,D10>0,D11>0),D4=0,D9=0)
=AND(OR(D5>0,D6>0,D7>0,D8>0,D10>0,D11>0),NOT(OR(D4>0,D9>0)))
|
=AND(D4+D9=0,G13>0) |
|
=AND(D4=0, D9=0,G13>0) |
|
=AND(SUMIF(C4:C11,TRUE,D4:D11)=0,SUMIF(C4:C11,FALSE,D4:D11)>0) |
|
No $ allowed |
3. (16 pts) Write an Excel formula in cell space!F4, which can be copied down to space!F11, to determine the diameter size of ceres as compared to earth. To determine this value you use the diameter of ceres and divide that by the earth’s diameter. HINT: You cannot assume that the diameter of earth will always be in cell space!E6… you have to “look” for it ;o)
=E4/VLOOKUP("earth",B$4:E$11,4,FALSE)
Optional $ on column
4. (20 pts) Write an Excel formula in cell space!H4, which can be copied down to space!H11, to determine how long in minutes it will take the sun’s rays to reach ceres. REMINDER: the data in column G designates millions of kilometers. NOTE: You will need to use the conversion factor(s) given on the units worksheets and don’t forget to use the named range.
=G4*1000000*units!A$4/lightyear/units!A$5
Optional $ on column
5. (25 pts) Write an Excel formula in cell space!L4, which can be copied down to space!L11, to determine the orbit time in years for ceres rounded to the hundredths place. NOTE: Notice that the orbit time may be given in days, months or years according to column K.
=ROUND(IF(K4="years",J4,IF(K4="months",J4/units!A$2,J4/units!A$1)),2)
Other solutions for the nested IF i.e. “days” first, then
“years”, defaulting “months”, etc.
Optional $ on column
HAVE to put ROUND function on the outside
6. (12 pts) Write an Excel formula in cell space!M4, which can be copied down to cell space!M11, to determine if ceres is a fun planet. A fun planet is one that either has more than two moons or has an orbit time in years greater than the average orbit time of all the planets.
=OR(D4>2,L4>AVERAGE(L$4:L$11))
Optional $ on column
7. (12 pts) Write an Excel formula in cell space!Q13 to determine the total number of moons that all the fun planets have together.
=SUMIF(M4:M11,TRUE,D4:D11)
No $ allowed
No quotes allowed around TRUE value
8. (10 pts) Write an Excel formula in cell space!N4, which can be copied down to cell space!N11, to determine the rank of the orbit time in years from smallest to largest for ceres.
=RANK(L4,L$4:L$11,1)
Optional $ on column
9. (20 pts) Write an Excel formula in cell space!Q4, which can be copied down and across to cell space!R11, to determine the Celsius temperature conversion for the maximum temperature given in cell O4 for ceres. If the Celsius temperature is already given, then there is no need to change the resulting value, however, if the given type of temperature is Kelvin (as in cell P4) then a conversion is necessary FYI: the “c” in cell Q3 means that the values in this column should be Celsius temperatures and the “k” in cell R3 means that the values in this column should be Kelvin temperatures. NOTE: Cells Q2 and R2 are also considered input data and are values used for temperature conversions. In order to convert Celsius to Kelvin, you have to add 273.15 to the Celsius temperature; in order to convert Kelvin to Celsius, you have to subtract 273.15 from the Kelvin temperature.
=IF($P4=Q$3,$O4,$O4+Q$2)
No extra $ allowed
10. (15 pts) You want to build a new lab with state of the art telescope equipment for viewing space. It’s going to cost $5,000,000 to build this new facility. Currently, there is an endowment of $1,000,000 that was given toward the cost of the new structure. An additional $15,000 per month will be donated for the next 10 years. Given an annual percentage rate of 10% compounded monthly, write an excel formula in cell space!Q14 to determine (true or false) if there will be enough money to pay for the building at the end of the 10 year period, if you invest the endowment today as well as the payments along the way.
=FV(10%/12,10*12,-15000,-1000000)>=5000000
No $ allowed
ACCESS:
Problem description (150 points)
CONTINUE SCROLLING DOWN FOR THE
ANSWERS
This
is an Access database setup to keep track of space shuttle missions. The
database consists of four tables that list the following:
Shuttle – This table lists
individual mission specifications including the trip location, the start date
of the mission, the number of days the mission will last, whether or not the
shuttle returned from its mission, and the base cost in millions of dollars for the mission. NOTE: The field called “return” is a yes/no
field, NOT a text field.
People – These are individual
astronaut costs associated with each mission. Astronauts need insurance for
each mission and they get hazard pay as well.
Equipment – These are
individual equipment costs associated with each mission.
Astronauts – Each record in this
table specifies the personal information about each astronaut in the shuttle
mission program. NOTE: The field called “married” is a yes/no field, NOT a text
field.
NOTE: Some missions do
not have any astronauts (unmanned flights); and some missions do not carry any extra
equipment. There are also some astronauts listed that have not yet been assigned
to a mission. These are not problems; they are just part of the logic of the
problem (i.e. not all primary key values are given in the foreign key field).
Portions of each table in this database are shown at
the end of this exam. Assume any referential
data integrity errors are corrected prior to writing the queries.
1.(16
pts) Determine the primary and foreign key(s) for each table (if any) and draw
lines indicating the relationships between the tables.
SHUTTLE
table Primary Key: Foreign Key(s): PEOPLE table Primary Key: Foreign Key(s): EQUIPMENT
table Primary Key: Foreign Key(s): ASTRONAUT
table Primary Key: Foreign Key(s):
2.
(12 pts) Is it okay to join the SHUTTLE and EQUIPMENT tables in one query,
called Q2A, then write a second query, called Q2B, to join Q2A with the PEOPLE
table (yes or no)? Explain.
3.
(15 pts) Write an Access query that lists the mission number and location for
shuttle missions which either:
* started the mission in the month of May
2008 and stayed in space for more than 20 days
OR
* visited the moon
Query Name: Q3
|
TABLE(S) |
|
|
JOIN TYPE |
|
|
Relative TO |
|
|
Field |
|
|
|
|
|
|
Table |
|
|
|
|
|
|
Total |
|
|
|
|
|
|
Sort |
|
|
|
|
|
|
Show |
|
|
|
|
|
|
Criteria |
|
|
|
|
|
|
OR |
|
|
|
|
|
|
OR |
|
|
|
|
|
Expressions, if needed…
4. (10 pts)
What dynaset will be created as a result of the above query?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
5.
(16 pts) Write an Access query that lists the mission number, the equipment
type (field name equip), the mission location and the equipment amount/cost for
each equipment type that starts with the letter A or the letter B. Sort the
list first by location in alphabetical order, then by equipment amount/cost
from highest to lowest value.
Query Name: Q5
|
TABLE(S) |
|
|
JOIN TYPE |
|
|
Relative TO |
|
|
Field |
|
|
|
|
|
|
Table |
|
|
|
|
|
|
Total |
|
|
|
|
|
|
Sort |
|
|
|
|
|
|
Show |
|
|
|
|
|
|
Criteria |
|
|
|
|
|
|
OR |
|
|
|
|
|
|
OR |
|
|
|
|
|
Expressions, if needed…
6.
(25 pts) Write an Access query that summarizes by astronaut number, ALL of the
married astronauts and the total number of missions they flew. NOTE: the number of missions should be zero if
the married astronaut has not yet been assigned to a shuttle mission.
Query Name: Q6
|
TABLE(S) |
|
|
JOIN TYPE |
|
|
Relative TO |
|
|
Field |
|
|
|
|
|
|
Table |
|
|
|
|
|
|
Total |
|
|
|
|
|
|
Sort |
|
|
|
|
|
|
Show |
|
|
|
|
|
|
Criteria |
|
|
|
|
|
|
OR |
|
|
|
|
|
|
OR |
|
|
|
|
|
Expressions, if needed…
7.
(21 pts) Write an Access query that lists the astronaut’s first and last name,
as well as the total hours in space per mission. NOTE: Not all the astronauts
should be listed; only the ones who flew a mission. Also, an astronaut will
have a record in the resulting dynaset for each mission they flew.
Query Name: Q7
|
TABLE(S) |
|
|
JOIN TYPE |
|
|
Relative TO |
|
|
Field |
|
|
|
|
|
|
Table |
|
|
|
|
|
|
Total |
|
|
|
|
|
|
Sort |
|
|
|
|
|
|
Show |
|
|
|
|
|
|
Criteria |
|
|
|
|
|
|
OR |
|
|
|
|
|
|
OR |
|
|
|
|
|
Expressions, if needed…
8.
(35 points) Use the following query design views to summarize by mission
number, the total cost for ALL the given shuttle missions. This total cost
should include the total amount of equipment, the insurance and hazard pay per
person, as well as the base cost. NOTE: Remember that the base cost is given
“in millions”. You are given the resulting dynaset below as well as on the
Access table page.
|
Query
Name: Q8A |
TABLE(S)
|
|
JOIN TYPE |
Relative
TO |
|
Field |
|
|
|
|
|
|
Table |
|
|
|
|
|
|
Total |
|
|
|
|
|
|
Sort |
|
|
|
|
|
|
Show |
|
|
|
|
|
|
Criteria |
|
|
|
|
|
Expressions, if needed…
|
Query
Name: Q8B |
TABLE(S)
|
|
JOIN TYPE |
Relative
TO |
|
Field |
|
|
|
|
|
|
Table |
|
|
|
|
|
|
Total |
|
|
|
|
|
|
Sort |
|
|
|
|
|
|
Show |
|
|
|
|
|
|
Criteria |
|
|
|
|
|
Expressions, if needed…
|
Query
Name: Q8C |
TABLE(S)
|
|
JOIN TYPE |
Relative
TO |
|
Field |
|
|
|
|
|
|
Table |
|
|
|
|
|
|
Total |
|
|
|
|
|
|
Sort |
|
|
|
|
|
|
Show |
|
|
|
|
|
|
Criteria |
|
|
|
|
|
Expressions, if needed…
Q#1
Notice the “key” next to the fields on the shuttle and
astronaut tables; these are the primary key values. The fields being connected
on the equipment table and the two fields on the people table are foreign key
values.
Q#2
No, whether you inner or outer join them, it’s still a
many-to-1-to-many join. This means that the result of Q2A will not have a
primary key and then trying to join the foreign key from Q2A with the other
table that also only has a foreign key violates the first rule of creating a
relationship between two tables which is that one must be a primary key. THUS, the answer is NO because it creates duplicate data.
However, can say
YES if explain that they used the TOTAL line with a Group By
*** FOR THE REST OF THE QUERIES: Some students will put
OUTER JOIN RELATIVE TO THE FOREIGN KEY TABLE; this is the same as an INNER JOIN
Q#3
Must have # signs around the date
Can do >=#5/1/08# and <=#5/31/08#; Can do
>#4/30/08# and <#6/1/08#
Q#4
|
Q3 |
|||
|
mission# |
location |
date |
#
days |
|
1 |
moon |
5/31/2008 |
5 |
|
2 |
mars |
5/14/2008 |
365 |
|
3 |
space station |
5/30/2008 |
180 |
|
4 |
moon |
1/15/2008 |
8 |
|
5 |
moon |
10/5/2007 |
10 |
Watch that the show boxes match – I put all the fields here
just in case
If they show a field above then must have in the
resulting dynaset
ORDER MATTERS – check the order from the query to the
dynaset – should match
Q#5 INNER join
Location field must to the left of amount field
Criteria can be on separate lines
Okay if mission# is from the equipment table (since an
inner join)
Okay if used “a??” for the criteria instead of the “a*”,
etc.
Q#6 OUTER join Relative to: ASTRONAUT
Must use anut# from the astronaut table - can’t use the
astro# from the people table
Okay if yes criteria has quotes or doesn’t have quotes
Married field can have GroupBy in total field instead of
Where
Have to COUNT the correct field – must be any field from
the PEOPLE table
Q#7 INNER join
Can use any field name for the calculated field; the
shuttle table name is optional
Okay if used 2-query problem instead of 3 as long as
results in dynaset as given
Q#8A OUTER join Relative to: SHUTTLE
Must have missions# from shuttle table
Q#8B OUTER join Relative to: SHUTTLE -
Must have missions# from shuttle table
Q#8C SHUTTLE table optional INNER or OUTER join Relative to: any table but only if choose outer join
Can use mission# field from any of the tables
Grade show line (since the resulting dynaset was given)
Must have all 3 fields listed (since the resulting
dynaset was given) IN ORDER
The last field must be named TOTAL (since the resulting
dynaset was given)
All of the TOTAL fields must be NZ’d individually except
the basecost field which is optional NZ
None of the table names are required on the equation
OPTIONAL SOLUTIONS
Don’t necessarily need the SHUTTLE table on the last
query if put the location field with a group by on one of the previous queries
AND put the base coast on one of the previous queries as well.