AU09 CSE200 QUIZ#5

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.

· You can always assume that any data integrity violations have been corrected prior to writing your first query.

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 ADULT table is a list of all the trips that adults (parents and individuals) 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 CHILD table is a list of all the trips that children 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.

NOTE: The difference between the ADULT and CHILD tables is that the people on the ADULT table are over 18 and can legally be responsible for themselves, whereas the people listed on the CHILD table cannot be legally responsible for themselves. Thus, there will always be at least one ADULT for every record listed on the FAMILIES table. However, there is not always a child associated with each record on the FAMILIES table i.e. not every parent or individual has a child!

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).

	FAMILIES table Primary Key Foreign Key(s)
ADULT table Primary Key Foreign Key(s)			CHILD table Primary Key Foreign Key(s)
		OPTIONS table Primary Key Foreign Key(s)

2. (5 pts) Can I inner join each of the following sets of tables in one query design view and have the resulting dynaset of the query be correct?

a) Child Families Adult Yes______________ No________________

b) Families Adult Option Yes______________ No________________

c) Adult Options Child Yes______________ No________________

d) Options Child Families Yes______________ No________________

e) Adult Families Options Child Yes______________ No________________

3. (3 pts) One of the families in the FAMILIES table has canceled their plans so I need to delete familyID 2 from the FAMILIES table. How will this affect the rest of the database? Be specific by mentioning the tables affected and what happens to the records in that table.

4. (4 pts) Give an example of how to introduce a data integrity error into the relationship between two tables on the given database. State the two tables and any changes you would make to create a data integrity violation.

5. (7 pts) Create an Access query in the design view given below to list the last name only of the families who have children that like to go on trail trip options. Family names can show up multiple times. NOTE: Do not list any unnecessary fields to solve this query i.e. I don’t ask for the family number so do NOT include it.

TABLE(S):
JOIN TYPE:	RELATIVE TO:
Field
Table
Total
Sort
Show
Criteria
OR
OR

Additional room for Expressions, if necessary

6. (7 pts) Create an Access query in the design view given below to list ALL of the families (family number and last name) currently taking adventures and the number of children in each family

TABLE(S):
JOIN TYPE:	RELATIVE TO:
Field
Table
Total
Sort
Show
Criteria
OR
OR

Additional room for Expressions, if necessary

7. (16 pts) Create an Access query in the design view given below that will generate the given resulting dynaset for Q7C (see the tables on the first page). The query should list the ALL of the options and the total number of people (adults and children) who are taking that trip.

QUERY NAME: Q7A TABLE(S):
JOIN TYPE:	RELATIVE TO:
Field
Table
Total
Sort
Show
Criteria
OR
OR

Additional room for Expressions, if necessary

QUERY NAME: Q7B TABLE(S):
JOIN TYPE:	RELATIVE TO:
Field
Table
Total
Sort
Show
Criteria
OR
OR

Additional room for Expressions, if necessary

Additional room for Expressions, if necessary

QUERY NAME: Q7C TABLE(S):
JOIN TYPE:	RELATIVE TO:
Field
Table
Total
Sort
Show
Criteria
OR
OR

SCORE ________/50