CSE4194 Access Exam Spring 2015
Name ______Score ______/100
Instructions:
- Put away all books, papers, and calculators. Turn off all beepers and cell phones.
- Answers must be legible or they will be marked incorrect.
- Be sure that all answers are SYNTACTICALLY correct i.e. as you would see them prior to running the query. Remember to put quotes on the like and text values and pound signs around dates.
- Aggregate functions: Group By, Sum, Avg, Min, Max, Count, Where, Expression.
- REMEMBER that all the data is not shown. Be sure your queries will work with additional records.
- You can always assume that any data integrity violations have been corrected prior to writing your first query.
ACCESS: Problem description
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.(12pts) Determine the primary and foreign key(s) for each table (if any) and draw lines indicating the relationships between the tables.
2a. (8 pts) In access, on the relationship diagram, what does it mean when there is a 1 on the relationship line by the primary key field and an infinity symbol on the relationship line by the foreign key field?
Data Integrity has been enforced for that relationship
2b. (8 pts) Using one of the relationships you defined above (in the relationship diagram), give an example of how cascade update works.
Changing the anut# value from 1 to 11 (the PK value) in the ASTRONAUT table would automatically change (i.e. update) the one record in the related table (PEOPLE) from 1 to 11 as well. Could also give the example of changing a PK value in the SHUTTLE table, say mission#1 to 10, then 1 record from the EQUIPMENT table and 3 records from the PEOPLE table would each have the mission# 1 change to 10. Only happens if the cascade update box is checked; which can only be checked if data integrity is enforced.
3. (8 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.
Yes, you can do it, but the resulting dynaset will contain multiple values such that the data will not be represented correctly OR
No, it cannot be done correctly because (1) once Q2A is run, there is no PK field in the resulting dynaset so no relationship can be made between Q2A and PEOPLE or (2) still a many to one to many relationship or (3) results in duplicate data
Asking to explain if it’s okay or not, vice how to fix it; you will show how to fix it in the 3-query problem later in the test.
4. (10 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: Q4
TABLE(S) / SHUTTLEJOIN TYPE / must leave blank
Relative TO / must leave blank
Field / Mission# / location / date / # days
Table / shuttle / shuttle / shuttle / shuttle
Total
Sort
Show / / / / /
Criteria / “moon”
OR / See below* / >20
OR
Expressions, if needed…*between #5/1/2008# and #5/31/2008#
5. (10 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) / SHUTTLE, EQUIPMENTJOIN TYPE / INNER
Relative TO / must leave blank
Field / mission# / location / equip / amount
Table / Shuttle or equipment / shuttle / equipment / equipment
Total
Sort / Ascending / Descending
Show / / / / /
Criteria / See below*
OR
OR
Expressions, if needed…* Like “a*” or Like “b*”
6. (10 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) / ASTRONAUT, PEOPLEJOIN TYPE / OUTER
Relative TO / ASTRONAUT
Field / Anut# / Mission# (any field from this table) / married
Table / astronaut / people / astronaut
Total / GroupBy / Count / Where (also GroupBy ok)
Sort
Show / / / / /
Criteria / “yes”
OR
OR
Expressions, if needed…
7. (10 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) / ASTRONAUT, PEOPLE, SHUTTLEJOIN TYPE / INNER
Relative TO / must be blank
Field / First name / Last name / Numhrs: *
Table / Astronaut / Astronaut
Total
Sort
Show / / / / /
Criteria
OR
OR
Expressions, if needed…* [shuttle]![# days]*24
8. (24 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.
JOIN TYPE / OUTER Relative TO SHUTTLE
Field / Mission# / amount
Table / Shuttle / equipment
Total / GroupBy / Sum
Sort
Show / / / / /
Criteria
Expressions, if needed…
Query Name: Q8B / TABLE(S) SHUTTLE, PEOPLEJOIN TYPE / OUTER Relative TO SHUTTLE
Field / Mission# / Insurance / Hazard
Table / Shuttle / People / People
Total / GroupBy / Sum / Sum
Sort
Show / / / / /
Criteria
Expressions, if needed…
Query Name: Q8C / TABLE(S) SHUTTLE, Q8A, Q8BJOIN TYPE / INNER Relative TO
Field / Mission# / location / TOTAL: *see below
Table / Any of the listed tables / shuttle
Total
Sort
Show / / / / /
Criteria
Expressions, if needed…* TOTAL: Nz([Q8A]![SumOfamount]) + Nz([Q8B]![SumOfinsurance]) + Nz([Q8B]![SumOfhazard]) + [shuttle]![base cost] * 1000000