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

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.

Since there is data integrity between the families table and the adult table, all of the records with family#2 will automatically be deleted; same for the child 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.

Any two tables that have a direct relationship; the introduction of a data integrity error means that a value in the FK field on one table is changed so that it doesn’t match a value in the PK field in the other table… can do an update of a foreign key or a delete from the primary key.

ANY INNER JOIN ON THE BELOW QUERIES CAN BE AN OUTER JOIN RELATIVE TO THE FOREIGN KEY TABLE SINCE THEY ARE THE SAME THING WHEN THERE IS DATA INTEGRITY

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): child options families… make sure the child table is listed here!
JOIN TYPE: inner			RELATIVE TO:
Field	Last name	type
Table	families	options
Total
Sort
Show	X
Criteria		=”trail”
OR
OR

Additional room for Expressions, if necessary

Do not grade the show line

The = in front of the criteria is optional

No other fields are allowed per the directions – especially from the child table!

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): child families
JOIN TYPE: outer				RELATIVE TO: families
Field	FamilyID	Last name	Family# - see below
Table	Families	Families	Child
Total	Group By	Group By	Count
Sort
Show	X	X	X
Criteria
OR
OR

Additional room for Expressions, if necessary

Do not grade the show line

The COUNT field can have any field from the child table, but must be from the CHILD table.

Family number MUST be from the families table.

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): adult options
JOIN TYPE: outer			RELATIVE TO: options
Field	Option	Family# - see below!
Table	Options	Adult
Total	Group by	Count
Sort
Show	X	X
Criteria
OR
OR

Additional room for Expressions, if necessary

The count can count any field from the adult table; but must be from the ADULT table.

DO grade the show line since need these values in the last query

Option number must come from the OPTIONS table

QUERY NAME: Q7B TABLE(S): options child
JOIN TYPE: outer			RELATIVE TO: options
Field	Option	Family# - see below!
Table	Options	Child
Total	Group by	Count
Sort
Show
Criteria
OR
OR

Additional room for Expressions, if necessary

The count can count any field from the child table; but must be from the CHILD table.

DO grade the show line since need these values in the last query

Option number must come from the OPTIONS table

QUERY NAME: Q7C TABLE(S): Q7A Q7B
JOIN TYPE: inner			RELATIVE TO:
Field	Option	TOTAL: see below
Table	either
Total
Sort
Show	X	X
Criteria
OR
OR

Additional room for Expressions, if necessary

Show line is graded since given resulting dynaset

Order of fields must be the same, too, since given the resulting dynaset

TOTAL: NZ([Q7A]![CountOffamily#])+NZ([Q7B]![CountOffamily#])

The name of the expression field must be Total since given the resulting dynaset

Fields here in the expression must match the ones in the above queries.

The table name is optional if the count fields from the two queries are different; the tables are required if the same field name was used.

ALTERNATE SOLUTION FOR QUESTION#7

Q7A – same but inner join (and no relative to)

Q7B – same but inner join (and no relative to)

Q7C – needs the OPTIONS table along with Q7A andQ7B; have to outer join the options table to Q7A relative to options table and outer join the options table to Q7B relative to the options table; then need the option# from the options table and the calculation is the same

SCORE ________/50