AU09 CSE200 QUIZ#4

Name _____________________________ Seat# ________ Lab=> Friday ______ 9:30am______ 11:30am

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.

· Leave the “join on” and “relative to” options on the query design view blank.

The tables given represent the database system for the Excel-lent Adventures Company. The following tables are a collection of information about the people who visit the facilities and the adventures (trip options) they take.

The TRIPS table is a list of all the trips that all of the people are taking. There may be multiple trips per person with no limit on the number of trips each person can take (unlike the Excel version). Each trip option has details associated with it on the OPTIONS table; and each person is associated with a family group name on the FAMILIES table.

The OPTIONS table defines the details about each of the different trip options available, including the type of trip (raft, trail or rock although more types can be added later), the difficulty level, the length in feet and the time in minutes, respectively.

The FAMILIES table is the list of families (i.e. organized groups) that are currently attending the adventure company’s facilities.

REMEMBER THAT ALL OF QUERIES ARE SINGLE TABLE QUERIES ONLY

Name ___________________________ Seat# ________ Lab=> Friday ______ 9:30am______ 11:30am

1. (8 pts) Database Relationships. Set up the relationships of this database. Using the boxes below, fill in the primary key (if any) and foreign key (s) (if any) for each table AND draw relationship lines between tables (exactly the same as you would see in Access).

2. (4 pts) I want to create a 4^th table based on the first name of the TRIPS table. Some of the fields for this table could include birth date, hair color, eye color, height, weight, etc. Assume the name of this new table is PEOPLE. How would I relate the PEOPLE table to the TRIPS table? That is, how would I create a relationship between the two tables? NOTE: Remember that on the TRIPS table, there are status and family# fields to help determine the differences between two people with the same name. Having duplicate names is a problem that you will need to consider when creating the PEOPLE table.

ANS. Put an autonumber field on the PEOPLE table as a primary key and replace the first name on the TRIPS table with this value (must assume that names could be repeated i.e. duplicates)… so would include the person’s name on the PEOPLE table instead of the TRIPS table

3. (7 pts) Create an Access query (in the design view given below) to list option number for all the raft and trail trips that last at least an hour (i.e. 60 minutes).

TABLE(S): OPTIONS
JOIN TYPE:				RELATIVE TO:
Field	option	type	time
Table	options	options	options
Total
Sort
Show	X	X	X
Criteria		“raft” OR “trail”	>=60
OR
OR

Additional room for Expressions, if necessary

Can also put one of the type criteria on a separate line (OR line) but then would need to list the time criteria again on that line as well;

>59 okay instead of >=60;

it’s not okay to put NOT “rock” since assuming there are only 3 types which is an incorrect assumption

4. (7 pts) Create an Access query (in the design view given below) to list the first name and option number for all the trips that parents are taking sorted first by option number (starting with option#1) then by first name (alphabetically).

TABLE(S): TRIPS
JOIN TYPE:				RELATIVE TO:
Field	status	option#	first name
Table	trips	trips	trips
Total
Sort		ascending	ascending
Show	X	X	X
Criteria	= “parents”
OR
OR

Additional room for Expressions, if necessary

The = on the parents criteria is optional;

option# field must be listed to the left of the first name field

5. (6 pts) Create an Access query (in the design view given below) to list the first name of all the people whose names begin with the letter C. Assume that each name is unique for this problem only and make sure that each name only appears once. NOTE: the resulting dynaset for this problem has 3 records with the first name the only field per record.

TABLE(S): TRIPS
JOIN TYPE:		RELATIVE TO:
Field	First name
Table	trips
Total	Group By
Sort
Show	X
Criteria	Like “C*”
OR
OR

Additional room for Expressions, if necessary

Must have the word like and the quotes around the C*

If put the family# and status fields, that’s okay, they will work fine with a Group By option, but none of the other fields will work correctly (they have lower detail level);

if put a field with nothing in the total line, that is an error

6. (10 pts) Create an Access query (in the design view given below) summarizing by the option#, to list the number of trips taken for each option. You do not have to list the option if no one is going on that type of trip. Show the resulting dynaset (don’t forget to include field names) in the given area below the query grid.

TABLE(S): TRIPS
JOIN TYPE:			RELATIVE TO:
Field	Option#	Trip#
Table	trips	trips
Total	Group By	Count
Sort
Show	X	X
Criteria
OR
OR

Additional room for Expressions, if necessary

Query6
option#	CountOftrip#
1	9
3	4
4	5
5	5
6	1
7	5
8	5
9	5

Order and number of fields in the query should match the result; any field on the table can be used instead of trip#, but it must match the “countof” field name in the resulting dynaset. Must grade the show line, too.

7. (8 pts) Using the above query, saved and called Query6, and assuming it costs $25 per person for each trip, create an Access query (in the design view given below) to list the option# and the total cost for each trip option.

TABLE(S): Query6
JOIN TYPE:			RELATIVE TO:
Field	Option#	TotCost: [Countoftrip#]*25
Table	Query6
Total
Sort
Show	X	X
Criteria
OR
OR

Additional room for Expressions, if necessary

make sure that the table block under the calc field is left blank;

optional [query6]! Table name on the field used in the calculated field

do not grade the show line

SCORE ________/50